SIR/XS Workshop

1. SIR/XS Workshop Dublin Conference 2008

2. Approach • One single project • Range of tasks • Your choice to ‘mix and match’ • Plenty of tasks to keep everyone busy • No need to try to do all

3. Project • Define a Database • Create Data Input Forms • Add some data from external source • Develop basic retrievals • Debug Program using debugger • Display selected data using GUI • Create XML output • Produce html table with new Tabulate syntax • Use Journal Processing to produce Audit

4. Workgroups • Work on own if you want • Work in pairs or threes • If not confident, join with very experienced • Start with group discussion of project and any questions • Start with schema design and data entry forms • Further details on other requirements as we progress

5. Objectives • Practical Exposure to features in SIR/XS • Tasks that promote understanding • Range of tasks to allow for current knowledge of SIR/XS

6. Options • Complexity of Database design • Complexity of data input process • Customising of PQLForms • Effort in populating • Complexity of reports

7. Project • Genealogy Database • People • Ancestors / Descendants • Family Tree

8. Review of What’s New • Extended syntax allowing names up to 32 characters • Non-standard names • Enhanced standardised syntax • STANDARD SCHEMA and STANDARD VARS • New Common Vars definition • New variable documentation • New write schema options • Upgraded record schema modification • ADD VARS, DELETE VARS, MODIFY VARS, RENAME VARS

9. What’s New • Multiple Data Files • New Batch Data Utility features including CSV Input/Output • New Journaling and Recovery • New XML Procedure • New GUI Debugger • New PQL Server • Regular Expressions • SEEK function to control file position

10. What’s New • Timestamp functions • Extended syntax on PROCESS CASE • CAT VARS in VisualPQL • PQLForms update • Encryption • Enhanced date and time format specification • Enhanced picture specification on WRITE and PFORMAT • HTML on WINDOW OUTPUT • New GUI Controls • VARMAP shows AUTOSET variables • Default String Length 32

11. SIR/XS New Names • Standard Names • from 1-32 characters • do not start with a number • can contain letters, numbers and the four characters $, #, @ and _ • e.g. NAME and EMPLOYEE_NAME are valid standard names • a name in lower or mixed case, e.g. Employee_Name, is translated to upper case

12. SIR/XS New Names • Non-Standard Names • To specify a non-standard name, enclose from 1 to 30 characters in curly braces {} • Name can contain any characters except curly braces and no translation is done on it • Non-standard names are stored without the braces and appear in sorted lists at the appropriate sort sequence • E.g. {$100 dollars} and {Employee_Name} are valid references to non-standard names

13. SIR/XS – New Names • Schema Screens • All uppercase / special characters/ numbers - Standard • All lowercase / allowed characters/ numbers - Standard (translated to uppercase) • Mixed case – Non-standard • Breaks rule – Non-standard (Starts with number/contains not allowed character)

14. Names v Expressions • Some commands require names • PROCESS REC record_name • Some commands require expressions • CREATE BUFFER buffer_name_exp • Existing inconsistencies • Buffers retain non-standard name • Functions uppercase names from expressions • For compatibility do not need curly brackets in buffer commands • Returned non-standard names have brackets (except (BUFNAME function)

15. Using Names • If you are using non-standard names as strings in VisualPQL functions, wrap the name in curly brackets within the quoteseg VARLABSC(1,'{Employee Name}'), • There may also be occasions when you want to create commands or other output where you need the brackets • PQL Functions that return names, wrap curly braces automatically if non-standard (except BUFNAME) • New STDNAME function checks a name and wraps curly brackets around if it is a non-standard name. This function does not uppercase input

16. GET VARS • GET VARS allows generation of new local variable names using a prefix or suffix • GET VARS ALL PREFIX 'EMPLOYEE_‘ • GET VARS ALL SUFFIX ‘_EMPLOYEE'

17. Schema in XS - CIR RECORD SCHEMA 0 CIR DATA LIST ID * (I2) MISSING VALUES ID (BLANK) VAR LABEL ID 'Identification Number' END SCHEMA • Put common vars in data list in individual record type • Do not repeat detail definition • Batch Data Input does not process CIR directly - data expected to be extracted from a record type

18. Schema in XS - STANDARD SCHEMA • STANDARD SCHEMA command • similar to a RECORD SCHEMA • signifies the start of a set of variable definitions • set is ended with END SCHEMA command • Define Variables using DATA LIST command • Use any of the normal variable definition commands such as MISSING VALUES, VALUE LABELS or VAR RANGES

19. STANDARD SCHEMA STANDARD SCHEMA DATA LIST POSITION * (I1) SALARY * (I2) SALDATE * (DATE'YYYY/MM/DD') VAR RANGES POSITION (1 18) SALARY (600 9000) VAR SECURITY SALARY (30,30) MISSING VALUES POSITION TO SALDATE (BLANK) VALUE LABELS POSITION (1)'Clerk' (2)'Secretary' ............ VAR LABEL POSITION 'Position' SALARY 'Salary' SALDATE 'Date Salary Set' END SCHEMA

20. STANDARD VARS • STANDARD VARS command • A variable defined in the standard schema can be referenced in record definitions • benefit of this is that coding does not have to be repeated for the variable when it occurs in multiple records • optionally allows a variable to be renamed when used in a record

21. STANDARD VARS RECORD SCHEMA 1 EMPLOYEE DATA LIST ID 1 - 4 (I2) NAME 6 - 30 (A25) ………………… CURRPOS 55 - 56 (I1) STANDARD VARS CURRPOS AS POSITION

22. Schema in XS - Modifying • RECORD SCHEMA • No DATA LIST modifies existing record definition • All standard schema commands e.g. • MISSING VALUES • VALUE LABELS • CLEAR BOOLEANS/COMPUTES/RECODES/VALUE LABELS • ADD VAR • DELETE VAR • MODIFY VAR (for input columns) • Use DATA LIST for complete redefinition of record

23. Labels • Label lengths 78 characters • Value Labels • Variable labels

24. Record Label • RECORD SCHEMA allows a short label (up to 78 chs) for the record type • Enclose the label in quotes e.g. RECORD SCHEMA 3 OCCUP 'Position Details'

25. VAR DOC • Multiple lines of text about variable RECORD SCHEMA 1 EMPLOYEE VAR DOC CURRPOS The current position is a coded field using a copy of the standard var POSITION.It is the most recent permanent position of the employee.

26. WRITE SCHEMA • Default: • Variables grouped by command • Use ‘TO’ format for identical specification • Suppresses repeated definition of common variables • NOTO suppresses TO option • VARSEQ groups commands by variable • COMMON repeats common definitions • EXPORT - Backward compatible with slashes • No REC 0, STANDARD SCHEMA, DATA FILES, TO

27. Date & Time Formats • Existing format specifications • Input oriented • Ignore separator – default on output • DDIMMIYYYY HHIMMISS • Output format on WRITE, DATEC, TIMEC • Maintains separator characters • Allows days of week • 12 Hour time • Wwww DD/Mmmm/YYYY • HH:MM PP

28. New Date & Time Formats • Schema Format • PQL Variable Definition • Format as per output formats • Two special formats – no months • YYYY DDD • YYYY WW • Input Processing • No separators – must match exactly • Separators – variable length components • Month number or name • ‘E’ as first character – must match Exactly

29. Example New Formats Format Possible Inputs Displayed Date ------------------ ---------------- ---------------- 'mmddyyyy' 05312006 05312006 'MMIDDIYY' 5/31/6 or 5 31 2006 05 31 06 'MM/DD/YYYY' 5-31-6 or 5 31 06 05/31/2006 'DD-MM-YY' 31/5/2006 or 31 May 6 31-05-06 'YYYY' 2006 or 6 2006 'MMM DD, YY' As per other M/D/Y formats MAY 31, 06 'WWW, DD MMM YYYY' 31/05/06 or xxx 31/5/2006 FRI, 31 MAY 2006 'Mmm/DD/YYYY' As per other M/D/Y formats May/31/2006 'Www, Mmm dddd' xxxx 05/31 Fri, May 31st 'yyyy/ww 6/13 2006/13 (weeks) 'ddd/yy' 85/2006 085/06 (days) ‘Eddd/yy' 085/06 085/06 (days)

30. Task • Database Design • Schema Creation

31. Database Design • Person Data • Names • Surname, Given Name, Middle Name • Dates • Places • Events • Birth, Death, Marriage, ….. • Adopted, Buried, Census, Divorced, Emigrated, Engaged, Occupation, Residence,…. • Link to other people • Notes, Pictures

32. Things to allow for • Uncertainty of information • Dates • Places • Identifying a person • Multiple Names • Finding parents from person • Finding children from person

33. Names in Workshop • Advise using long standard names • Be aware when typing in Schema Screens • All upper or lower – Standard upper • Mixed case – kept Non-Standard

34. Suggestions • Use a numeric id as main key • Easier to use to link records • Use secondary indexes for names • Use secondary indexes to find related people • Once design done identify standard variable types and create ‘template’ variables first

35. One Approach • Record Types & Indexes • Birth • Indexes on Father, Mother, Surname • Death • Marriage • Indexes on Husband, Wife • Text • Files

36. Data Entry • Generate default screens • Customise • Do all your variable names/labels fit • Enough space for data fields • Automatically assign next id when adding new person • Automatically get Father/Mother names • List existing Ids

37. GRID • Utility SIR SPREADSHEET • Menu • Displays Database or Tabfile • Used to update • SPREADSHEET Procedure • GRID Control • Use Arrays • Can sort and resize arrays

38. Task • Generate Default Screens • Use Screen Painter to customise • Elaborate/simple – your choice

39. Batch Data Input • Set of utilities that take data from file and update database • File Input on menu • ADD REC/REPLACE/READ/UPDATE/EVICT • 'Classic' BDI • fixed format records • correspond to input columns on schema • FILE DUMP produces compatible file • Useful maintenance tool • Problem with * input in schema

40. Batch Data Input • Auto I/O columns • Assigns calculated columns for * vars • Only need to assign columns if real file • CSV option • Order as per schema • FILE DUMP encloses in “” • Record Type as first field • Own CSV file • Omit record type and specify to utility • May have to SKIP 1 to skip header

41. CIR & BDI • SIR FILE DUMP and the batch data input utilities support a separate input for CIR • SIR FILE DUMP can write a record 0 in an appropriate format and the batch data input utilities can process that record. • Specify input format definitions for the CIR or utilities can use defaults • Does not support the extended BDI definitions of ACCEPT REC,REJECT REC,COMPUTE,IF or RECODE.

42. Adding Data • From Excel • ODBC • CSV • Set of test data

43. Task • Add set of test data from Excel

44. Retrievals • Generic Retrieval • Get Ancestors • Get Descendants • Produce Family Tree • Use Debugger

45. Descendants

46. New PQL Debugger • GUI Interface • Step through source • Set breakpoints at lines • See values in variables • Set values in variables • Set watchpoints (break when value changes) • Step into/over subroutines

47. Routine to debug • PROGRAM/RETRIEVAL/SUBROUTINE • DEBUG {= name} • Stores as a subroutine • If name not specified • Real subroutine uses subroutine name • Other SYSTEM.DEBUG • Generates debug source code • Can be useful even if not debugging • Specify NOEXECUTE if just want to debug

48. Run Debugger • Choose which module to debug • Module starts • Lists source • Lists variables • Look at other stuff • Members/Files/Buffers

49. Task • Develop generic program to get ancestors from single person • Develop generic program to get descendants from single person • Use simple procedure as test e.g. CSV SAVE FILE

50. TREE Control TREE id,row,height,col,width,read • A tree control can display a hierarchical list of items such as the structure of a database or the data within that database. • The items in the tree list can have child items and, if they do then they will also have a button that can show or hide that items child items • Set values with BRANCH function X = BRANCH (id,parent,node,text) • No message when user showing/hiding • Message when clicked on entry • M_ARG1 or GETPOS (IDTREE) for node