SIR User Conference Manchester July 4th 2003

1
SIR User ConferenceManchesterJuly 4th 2003
2
SIR Product Development

• Where are we now?
• Where are we going?

3
Current Release SIR2002

• Data Management
• Multiple Databases
• Integrated Master Modules
• SQLServer for ODBC
• Secondary Indexes
• Long Strings

4
Current Release SIR2002

• Application Development
• GUI Based
• Screen Painters
• Unlimited PQL Program Size
• (No Table Space)
• Spreadsheet
• PQLForms
• Internet Enabled

5
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

6
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

7
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

8
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

9
Feature Used

• Visual PQL 86
• Old Forms 38
• PQLForms 25
• Painter 20
• Master 30
• ODBC 33
• SQL 38

• Sec.Indexes 30
• Web Interface 20
• Multiple DB 30
• Tabfiles 33
• Debugger 30
• Exec Window 30

10
Feature Wanted

• SQL in PQL 40
• Improved Recovery 60
• Multiple Data file 33
• PQL Server 20
• XML 52
• GUI PQL Debugger 33
• 32 character names 76

11
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

12
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 ?

13
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

14
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

15
Standard Names

• 1 32 characters
• Start with alpha
• Capitalized
• Contain letters, numbers, four special characters
  _at_ _

16
Non-Standard Names

• Enclosed in curly braces
• From 1 to 30 characters (plus braces)
• No translation
• Any characters
• Stored without braces so sort normally

17
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

18
Improved Recovery

• Current Features
• Journal
• Unload
• Reload
• Rollforward
• Improvements
• Rollback
• Transaction processing
• Automatic Recovery
• Journal as Audit Trail
• Incremental Backup

19
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

20
Improved Recovery

• Journaling Rewritten
• Itemize rewritten
• Roll back
• Roll forward
• Auto recover

21
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)

22
Journal Processing

• New PQL commands to process journals
• Access to header data
• Access to record values
• Audit Trail
• Other recovery strategies

23
Journal Commands

• PROCESS JOURNAL
• JOURNAL RECORD IS
• PQL access to record variables
• END JOURNAL RECORD IS
• END PROCESS JOURNAL

24
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

25
Example

• program
• integer4 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

26
Transaction Processing

• Start Transaction
• End Transaction (Commit)
• Abort Transaction
• Journal Based
• Rollback on Abort

27
Incremental Unload

• Creates Journal of Updates
• Add to end of Unload
• Automatic processing of levels
• Automatic restore by reload
• Optional separate file

28
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

29
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)

30
Example XML

• John D Jones
• 2150
• 1956
• James A Arblaster
• 1500
• 1961

31
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

32
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

33
Example XML Read

• program
• string256 tagin datain
• string25 name
• integer4 salary
• date born_year ('YYYY')
• open input dsn'example.xml' XMLTAG tagin
  errorendlab
• loop
• . read (input,errorendlab) 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

34
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

35
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

36
SQL in PQL

• PROCESS SELECT variable_list ALL
• FROM tabfile. table_name
• database. record_name
• INDEXED BY index_name
• WHERE condition
• END PROCESS SELECT

37
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

38
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

39
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

40
Existing Data Structure
Top Level Index
Other Index Levels
Data Level
41
New Data Structure
FILE INDEX
Top Level Index
Other Index Levels
Data Level
42
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

43
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

44
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

45
SIR/XS

• Feature Wanted
• 32 character names
• Improved Recovery
• XML
• SQL in PQL
• Multiple Data files
• GUI PQL Debugger
• PQL Server

46
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

47
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

48
Client Facilities

• Logon
• Send Request (check error codes)
• Get output line at a time
• Logoff

49
Connect to SIRServer

• CONNECT conid SIRSERVER 'b1
• USER 'dad
• PASSWORD zzzz
• ERROR errid

50
Send Request

• Request is only ever a CALL
• Familypassword.memberpassword (parameter list)
• SEND REQUEST CONNECT conidCOMMAND
  myprocs.report1RESULT lines
• Positive result lines returned
• Negative result error with request

51
Get Results

• RETURN LINE mylineCONNECT conidERROR 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

52
Training

• New Version of Training available for download
• Still at stage where looking to refine approach
  NOT content
• Feedback very welcome

53
Other XS Features

• PQL based Integrated Editor
• Files as Members
• Master for Databases not Session

54
Suggestions

• Kathys 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

55
SIR User ConferenceManchester2003