SIR/XS Workshop

1
SIR/XS Workshop
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 Whats 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
Whats 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
Whats 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 , , _at_ 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,'Employe
  e 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
• HHMM 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

51
Task

• Display person and ancestors as tree control

52
XML

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

53
Example XML

• ltcompanygt
• ltpersongt
• ltnamegtJohn D Joneslt/namegt
• ltsalarygt2150lt/salarygt
• ltbirthdaygt1956lt/ birthday gt
• lt/persongt
• ltpersongt
• ltnamegtJames A Arblasterlt/namegt
• ltsalarygt1500lt/salarygt
• lt birthday gt1961lt/ birthday gt
• lt/persongt
• lt/companygt

54
XML SAVE FILE Procedure

• Standard Clauses
• FILENAME filename BOOLEAN (logical
  expression) MISSCHAR character SAMPLE
  fractionSORT variable,....
• Special Clauses
• ROOT 'string'BREAK variable ( TAG
  'string', ATTRIBUTES (varname (format) ),...)
  
  ELEMENTS (varname (format)
  ),...))DTD filenameSCHEMA filename

55
Example XML

• ltcompanygt - ROOT people
• ltpersongt - BREAK ( id tag person -
  ELEMENTS (name,salary,birthday))
• ltnamegtJohn D Joneslt/namegt
• ltsalarygt2150lt/salarygt
• ltbirthdaygt1956lt/birthdaygt
• lt/persongt
• ltpersongt
• ltnamegtJames A Arblasterlt/namegt
• ltsalarygt1500lt/salarygt
• lt birthday gt1961lt/ birthday gt
• lt/persongt
• lt/companygt

56
Task

• Simple list of people and children in XML
• Hint - GET VARS PREFIX xxx_
• GET VARS from a RECORD does not get the Case
  Identifier
• Full set of descendants for a person
• Hint. XML needs the value identifying a break
  level as an XML valid name so it is the name of
  the variable NOT the value in the variable

57
Standard summary variable list

• Variable names or three keywords
• ALL All (with regard to INCLUDE/EXCLUDE)
• AS Use alternate name
• S(1) AS SALARY or S(1) 'Salary
• TO Creates list of selected variables A to B
• end of the list when
• List starts and stops with brackets ()
• End of input e.g. next command (not next clause
  on same command)
• Special character - slash / is valid, other may
  be
• Name that is not a variable if also not valid
  keyword gives Error 4 Keyword is invalid'

58
Procedures

• Standard
• BMDP, CONDESCRIPTIVE,CSV (format), DBASE(old
  rename),DIF (header),FREQUENCIES,MINITAB,REPORT
  (options),SAS,SAVE TABLE,SIR SAVE FILE,SPREAD
  SHEET,SPSS, SYSTAT,WRITE RECORDS
• Non-Standard
• DESCRIPTIVE, GRAPH, PLOT, TABULATE,XML

59
Tabulate

• TABULATE Wafer,StubHeader/ OPTIONS
• New syntax for TABULATE
• TABULATE HEADER (EXP) STUB
  (EXP) WAFER (EXP)
  OPTIONS

60
Task

• Produce a tabulation grouping population by
  gender and age at death

61
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

• Update Record
• Before update
• Before delete
• After insert
• After update
• Existing record previous update level
• User (in multi-user mode)

62
Journal Processing

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

63
Journal Commands

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

64
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

65
Task

• Produce Audit Trail of Updates
• Record Type
• Amendment Type
• Person Updated
• Date/Time
• Turn Journaling On
• Should be off when creating
• Make a couple of updates

66
SIR/XS Workshop