660 likes | 775 Views
SIR/XS Workshop. 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. Project. Define a Database Create Data Input Forms Add some data from external source Develop basic retrievals
E N D
SIR/XS Workshop Dublin Conference 2008
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
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
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
Objectives • Practical Exposure to features in SIR/XS • Tasks that promote understanding • Range of tasks to allow for current knowledge of SIR/XS
Options • Complexity of Database design • Complexity of data input process • Customising of PQLForms • Effort in populating • Complexity of reports
Project • Genealogy Database • People • Ancestors / Descendants • Family Tree
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
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
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
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
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
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)
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)
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
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'
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
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
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
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
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
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
Labels • Label lengths 78 characters • Value Labels • Variable labels
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'
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.
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
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
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
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)
Task • Database Design • Schema Creation
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
Things to allow for • Uncertainty of information • Dates • Places • Identifying a person • Multiple Names • Finding parents from person • Finding children from person
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
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
One Approach • Record Types & Indexes • Birth • Indexes on Father, Mother, Surname • Death • Marriage • Indexes on Husband, Wife • Text • Files
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
GRID • Utility SIR SPREADSHEET • Menu • Displays Database or Tabfile • Used to update • SPREADSHEET Procedure • GRID Control • Use Arrays • Can sort and resize arrays
Task • Generate Default Screens • Use Screen Painter to customise • Elaborate/simple – your choice
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
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
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.
Adding Data • From Excel • ODBC • CSV • Set of test data
Task • Add set of test data from Excel
Retrievals • Generic Retrieval • Get Ancestors • Get Descendants • Produce Family Tree • Use Debugger
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
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
Run Debugger • Choose which module to debug • Module starts • Lists source • Lists variables • Look at other stuff • Members/Files/Buffers
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
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