Transcript sir2001

SIR User Conference
Manchester
July 4th 2003
Manchester Conference 2003
SIR Product Development
 Where
are we now?
 Where are we going?
Current Release SIR2002

Data Management
– Multiple Databases
– Integrated Master Modules
– SQLServer for ODBC
– Secondary Indexes
– Long Strings
Current Release SIR2002

Application Development
– GUI Based
– Screen Painters
– Unlimited PQL Program Size

(No ‘Table Space’)
– Spreadsheet
– PQLForms
– Internet Enabled
Current Release SIR2002




Single Generic Source on Windows XP
Releases on Windows, Sun, HP, IBM, Linux
SPRs fixed on demand - revisions on internet
for download
Following Last Conference
– Upgraded PQLForms
– Corrected Secondary Index Issues
– Current version 20.23
Where are we going?




Support Multiple Platforms
Compatibility from earlier versions
Ease of Use
Integrate as much as possible
–
–
–
–
No ‘M’ modules
PQLForms
Incorporate SQL into PQL
Extend database features to remove need for
tabfiles
Where are we going?

VisualPQL is primary tool
– Sub-routines, sub-procedures, program in
program, extended size, pre-compile, functions
– Other features use VisualPQL e.g. menu interface,
painters, PQLForms

Extend GUI Features
– Fonts (colour, size, etc) , Other Controls
– No text style screens

Old Forms, old editor, PQL debugger
Suggested new features








SQL in VisualPQL
Extended & Improved backup/restore
Multiple database data files
PQL Server
XML Input/Output
GUI PQL Debugger??
32 character names??
Sent questionnaire
Feature Used
Visual PQL
Old Forms
PQLForms
Painter
Master
ODBC
SQL
86%
38%
25%
20%
30%
33%
38%
Sec.Indexes
Web Interface
Multiple DB
Tabfiles
Debugger
Exec Window
30%
20%
30%
33%
30%
30%
Feature Wanted
SQL in PQL
 Improved Recovery
 Multiple Data file
 PQL Server
 XML
 GUI PQL Debugger
 32 character names

40%
60%
33%
20%
52%
33%
76%
Write In Features













Users Passwords Improved
New Tabulate
Multilingual labels
PQL Design Studio
Better ODBC
Better error messages
Anchor column in spreadsheet
Timestamp data type
Basic PQLForm
Report Painter
Editor that knows about members
Audit Trails, More security
Extra GUI things, Colours in PQL
Name for Next Version
SIR2000 (Windows 2000 – year 2000)
 SIR2002 (SIR2000 ver 2 - year 2002)
 SIR2003 (SIR2000 ver 3) ?
 SIR2004 (release year) ?
 SIR/XP (Cross Platform)?
 SIR/XS (eXtended System)?
 SIRDBXS ?

SIR/XS

Feature Wanted
– 32 character names
–
–
–
–
–
–
Improved Recovery
XML
SQL in PQL
Multiple Data files
GUI PQL Debugger
PQL Server
32 character names

Database Schema
–
–
–
–
–


Database Names
Record Names
Variable Names
Index Names
Passwords
Tabfiles & Tables
Families & Members

VisualPQL
–
–
–
–
–
–
Variables
Sub-Routines
Sub-Procedures
Labels
Buffer Names
Filenames
Standard Names
– 1 – 32 characters
– Start with alpha
– Capitalized
– Contain letters, numbers, four special
characters $ # @ _
Non-Standard Names
– Enclosed in curly braces { }
– From 1 to 30 characters (plus braces)
– No translation
– Any characters
– Stored without braces so sort normally
SIR/XS

Feature Wanted
– 32 character names
– Improved Recovery
–
–
–
–
–
XML
SQL in PQL
Multiple Data files
GUI PQL Debugger
PQL Server
Improved Recovery

Current Features
–
–
–
–

Journal
Unload
Reload
Rollforward
Improvements
–
–
–
–
–
Rollback
Transaction processing
Automatic Recovery
Journal as Audit Trail
Incremental Backup
Journal Utilities

Journal Recover (New)
– Rolls forward from given place

Journal Rollback
– Rolls back from given place

Itemize
– Lists contents (headers)

Upload
– Creates machine independent text file

Download
– Applies output from upload
Improved Recovery
Journaling Rewritten
 Itemize rewritten
 Roll back
 Roll forward
 Auto recover

Journal


File Header
– Database Name
– Version of Software
Update Header
– Data/Schema
– Update Level Started
– Date/Time Started
– Date/Time Finished
(for data updates)
– User Name

Each update record
–
–
–
–
–
Before update
Before delete
After insert
After update
Existing record
previous update level
– User (in multi-user
mode)
Journal Processing

New PQL commands to process journals
– Access to header data
– Access to record values
Audit Trail
 Other recovery strategies

Journal Commands
PROCESS JOURNAL
JOURNAL RECORD IS
PQL access to record variables
END JOURNAL RECORD IS
END PROCESS JOURNAL
PROCESS JOURNAL





[FROM = updlevel | START = date [,time] ]
[THRU = updlevel | END = date [,time] ]
[REVERSE]
[FILENAME= fname ] (sr5 is the default)
Return Data
–
–
–
–
–
–
[DATE = varname] [ENDDATE = varname]
[TIME = varname] [ENDTIME = varname]
[LEVEL = varname]
[RECORD = varname]
[TYPE = varname]
[USER = varname]
Example
program
integer*4 jtype
value labels jtype (1) 'Record written‘ (2) 'Before rewrite‘
(3) 'After rewrite‘
(4) 'Deleted record‘
(-1) ‘Data header‘ (-4) ‘Schema header‘ (-5) ‘User header'
process journal TYPE= JTYPE RECORD = jrec level = jlevel FROM = 13
ifthen (jtype gt 0)
. write [vallab(JTYPE)] 'Record Type ' jrec
.else
. write [vallab(JTYPE)] 'Update level ' jlevel
fi
journal record employee
write id name salary
end journal record
end process journal
end program
Transaction Processing
Start Transaction
 End Transaction (Commit)
 Abort Transaction
 Journal Based
 Rollback on Abort

Incremental Unload

Creates Journal of Updates
– Add to end of Unload
– Automatic processing of levels
– Automatic restore by reload
– Optional separate file
SIR/XS

Feature Wanted
–
–
–
–
–
–
–
32 character names
Improved Recovery
XML
SQL in PQL
Multiple Data files
GUI PQL Debugger
PQL Server
XML


Still at concept stage – input welcomed
XML File structure
–
–
–
–

Text File
Hierarchical
Tags enclose
Resembles HTML with own tags
XML Names
– Begin with character (or _ :)
– Case sensitive
– Allow letters, numbers - _ : . (No spaces)
Example XML
<people>
<person>
<name>John D Jones</name>
<salary>2150</salary>
<born-year>1956</born-year>
</person>
<person>
<name>James A Arblaster</name>
<salary>1500</salary>
<born-year>1961</born-year>
</person>
</people>
XML Design Issues

How to relate data to tags?
– Use record name
– Use variable name
– Use variable label

How to generate XML?
– Utility / PQL Procedure/ WRITE

How to read XML?
– Utility / READ / New PQL

Unicode – UTF-8 ISO-8859-1
UTF-16
Example XML Proc
retrieval
process case
process rec employee
get vars name salary birthday
perform procs
end process rec
end process case
xml tag = 'people' filename = ‘example.xml’
detail block tag = 'person'
. write name salary
[datec(birthday,yyyy')] tag = 'born_year'
end xml
end retrieval
Example XML Read
program
string*256 tagin datain
string*25 name
integer*4 salary
date born_year ('YYYY')
open input dsn='example.xml' XMLTAG = tagin error=endlab
loop
. read (input,error=endlab) datain(a*)
. if (tagin eq 'person') set name salary born_year (missing)
. if (tagin eq 'Name') compute name = datain
. if (tagin eq 'Salary') compute salary = numbr(datain)
. if (tagin eq 'born_year') compute born_year = datain
. if (tagin eq '/person') write name salary
pool
endlab:
end program
Example XML PROCESS
program
process xml filename='example.xml'
. process xmltag 'person'
. gettags name salary born_year
. write name salary
. end process xmltag
end process xml
end program
SIR/XS

Feature Wanted
– 32 character names
– Improved Recovery
– XML
– SQL in PQL
– Multiple Data files
– GUI PQL Debugger
– PQL Server
SQL in PQL
PROCESS SELECT variable_list | * | ALL
FROM
[ tabfile. ] table_name
[ database.] record_name
[INDEXED BY index_name]
[WHERE condition]
…
END PROCESS SELECT
SQL in PQL

Processes from multiple data sources
–
–
–
–

Joins
Use WHERE to restrict
Full PQL conditions/expressions
No subqueries in WHERE
Returns one set of named variables at a time
–
–
–
–
Sequence as specified in command
No aggregation
No GROUP BY
No UNION
SIR/XS

Feature Wanted
–
–
–
–
32 character names
Improved Recovery
XML
SQL in PQL
– Multiple Data files
– GUI PQL Debugger
– PQL Server
Multiple Data Files
Split the .sr3 data file
 No overhead for existing single file
databases
 Split based on serial key ranges

– Case structure – case values
– Caseless – record type/key value
Existing Data Structure
Top Level Index
Other Index Levels
Data Level
New Data Structure
FILE INDEX
Top Level Index
Other Index Levels
Data Level
File Index
Contains key ranges
 Contains filenames
 Contains pointer to top level index for file
 New schema command
DATA FILES
FILENAME = ‘ ‘ /
FILENAME = ‘ ‘ FROM keyvalue /
FILENAME = ‘ ‘ FROM keyvalue
 Before any data added
 Requires restructure

SIR/XS

Feature Wanted
–
–
–
–
–
32 character names
Improved Recovery
XML
SQL in PQL
Multiple Data files
– GUI PQL Debugger
– PQL Server
GUI Improvements


Main Menu Replace ‘On-Fly’
Child Windows
– GUI PQL Debugger

DISPLAY TIPBOX "text"
– shows a box with text that disappears




Clipboard COPY/PASTE functions
Synchronized Lists
Right Mouse click & Mouse over messages
Image buttons - tool bar buttons on dialogs
SIR/XS

Feature Wanted
–
–
–
–
–
–
32 character names
Improved Recovery
XML
SQL in PQL
Multiple Data files
GUI PQL Debugger
– PQL Server
SIR Server

Similar in concept to SQLServer
–
–
–
–

Started on network
Waits for users to logon
Process requests
Returns results
Requests
– Logon
– Run named procedure
– Logoff
Server Procedures
Single System Procedure File
 Requests construct text output

– All output to standard output goes back to
client

Procedures
– Contain SIR Commands
– Run PQL
Client Facilities
Logon
 Send Request (check error codes)
 Get output line at a time
 Logoff

Connect to SIRServer
CONNECT conid SIRSERVER 'b1‘
USER 'dad’
PASSWORD ‘zzzz’
ERROR errid
Send Request

Request is only ever a CALL
– Family[password].member[password (parameter
list)
SEND REQUEST
CONNECT conid
COMMAND ‘myprocs.report1‘
RESULT lines


Positive result – lines returned
Negative result – error with request
Get Results
RETURN LINE myline
CONNECT conid
ERROR errid
e.g.
Loop (or FOR I = 1,lines / ROF)
. return line myline
connect conid
error errorid
. if (errorid ne 0) exit loop
. write myline
pool
Training
New Version of Training available for
download
 Still at stage where looking to refine
approach NOT content
 Feedback very welcome

Other XS Features
PQL based Integrated Editor
 Files as Members
 Master for Databases not Session

Suggestions

Kathy’s List
– Allow ‘filename’ as synonym for ‘dsn’ in open
– Amend cross record functions to work with string
values CNT CNTR MAX MAXR MIN MINR
– PWRITE to have last amended date and time
written on procedure command
– PREAD to have option to overwrite based on
date/time
– Compare two PWRITE to spot differences
– Facility to rename family
SIR User Conference
Manchester
2003
Manchester Conference 2003