Any record may have as many indexes as wanted and each index may have multiple record variables (plus any case id if wanted) as keys. Each key can be in ascending or descending sequence and strings may be indexed as case sensitive or as purely upper case. An index may only allow unique values for the key or may allow duplicates. Indexes are independent of any case structure on the database.
Indexes are created with the CREATE DBINDEX command:
This creates the index and adds data from all existing records to the index.
Indexes are deleted with the DROP DBINDEX command.
Secondary indexes are maintained automatically as records are added, amended or deleted by any means i.e. whether through database batch utilities, VisualPQL, Forms, or SQL and whether Master is used or not.
Index details are listed by LIST STATS and there are new schema definition dialogs in the menu system to allow index maintenance. Indexes are rebuilt if the database is rebuilt by an Import or a Reload.
VisualPQL can retrieve data using an index using PROCESS RECORDS or the new LOOKUP command. The new PQLForms SCREEN command and the SPREADSHEET utility can also use database secondary indexes.
The extended syntax for PROCESS RECORD is:
PROCESS RECORD recname [INDEXED BY indexname]All existing record selection clauses such as
AFTER, FROM, THRU, etc. can be used with secondary indexes.
The PROCESS RECORD ... INDEXED BY does not have to be within a CASE block. The record can be updated within the PROCESS RECORD block in a retrieval update and any common vars are available as if the record block were within the correct case block.
There are a number of new VisualPQL schema functions relating to secondary indexes which return information about any indexes.
FORM program type (instead of RETRIEVAL or PROGRAM) allows all standard VisualPQL together with the new commands which include:SCREEN Begins a set of commands for a screen
PAGE Begins a new page of fields within a screen
FIELD Defines a field
GENERATE Creates a default set of fields from the schema
CALL SCREEN Passes control to a different screenForms can be saved as compiled executables or can be saved as sub-routines and used as components in a large form or other retrieval.
A very simple PQLForms program which allows the user to browse through employees, would be as follows:
FORM . SCREEN RECORD employee . GENERATE . END SCREEN END FORMA slightly more customized form:
FORM
SCREEN RECORD employee
FIELD id PROMPT 'Employee'
FIELD name NOPROMPT
DATA AT 1,30
WIDTH 20
FIELD educ PROMPT AT 2,1
LABELS WIDTH 10
FIELD salary PROMPT 'Salary:'
IF (EDUC EQ 1)
CALL SCREEN occup ONCALL FIRST
CALL SCREEN review ONCALL FIRST
END SCREEN
SCREEN RECORD occup
. GENERATE
END SCREEN
SCREEN RECORD review
. GENERATE
END SCREEN
END FORM
A default PQLForm can be generated with the WRITE SCHEMA command or through the database/schema/write schema menu.
The system displays how a dialog or screen will eventually look and lets you select elements or groups of elements and move these around on the screen. The overall size of the dialog or screen is maintained to accommodate all the individual elements. The elements can also be viewed and manipulated as a list if this is more convenient.
You can associate appropriate processing with controls specifying program logic for dialogs and field associations for screens. Using the painter integrates the design process for a dialog with the specification of processing logic. The elements on a form can be associated with record or table variables and appropriate choices.
The two SIR2000 features that use the spreadsheet interface, the VisualPQL Procedure SPREADSHEET and the database/tabfile utility SPREADSHEET are identical in specification and operation in SIR2002 other than the appearance and user interaction with the graphical control.
There is a new VisualPQL command GRID which allows a program or retrieval to display data in a spreadsheet-like manner and to receive back input from the user and to redisplay data as necessary.
REDEFINE ARRAY command. This allows you to grow, shrink or redimension any array programmatically. Array warning messages that might be generated for subscript references outside the current definition can be suppressed with the NOARRAYMSG keyword on the PROGRAM, RETRIEVAL or SUBROUTINE command.
SORT command. Arrays can be sorted according to the values in the elements or according to the values in a second array matched positionally to the first.
HEX Format
When records with an auto increment key are created, the value of the key specified by the utility or program is tested. If this key value is zero, then the system automatically finds the last occurrence of the record type in the case and takes the value from that record, increments this by 1 and uses this value as the key. If a record of this type does not exist, the value 1 is used as the key. Auto increment keys must be integer variables. If a key is an auto increment key, it must be the final key in a record type.
LOOKUP command accesses a single database record or table row, tests if one exists that matches keys and/or WHERE conditions and returns data as specified. The command may be specified in a PROGRAM, RETRIEVAL or SUBROUTINE at any point. It does not affect other database or table access processes.
SHOW ITEM and HIDE ITEM commands alter the appearance of a dialog while it is active. Items can be hidden and other items shown. Items can thus appear to be on different pages within a dialog or emulate tabbed dialogs.
The SET DIALOG TITLE command sets the dialog title, enabling different pages within a dialog to have appropriate titles.
DISPLAY POPUP LIST command displays a pop up menu which remains on the screen until the user either selects an item or clicks at a point off the menu, thus cancelling the menu. This command can be issued in menus, in standard dialogs or in DEDIT dialogs.
MESSAGE HELP id is specified in a dialog, then a small ? is displayed in the top right corner of the dialog. If the user clicks on this it becomes a floating ? and the user can position this to a control to request help. When the user clicks again, a message is passed to the MESSAGE HELP block which should display appropriate help for the identified control.
SIR2002 can also act as an ODBC client (using VisualPQL) and there is a new BIND STATEMENT command to support parameterized queries.
CTRL-R to submit a command typed in this way.
DEDIT dialog editor is a full screen dialog which allows the program and user to interact to place controls of various types and to visually edit these controls. The dialog visually resizes to accommodate controls. This is the basis for the dialog painter and PQLForms painter and can also be used to develop custom painting style applications.Controls are placed on the dialog through commands and functions rather than through any definitions. There is no separate message definition command. Control is returned to this block each time a message is generated.
BINDPARM Binds an ODBC parameter
DBINDN Name of nth index
DBINDR Number of record type indexed
DBINDS Number of indexes
DBINDT Name of the nth variable in index
DBINDU Returns 1 if index is unique
DBINDV Number of variables in index
DEFTFN Name of the nth database
DELDIR Deletes the named directory
DITEMCOL Returns the column the nth DEDIT item is positioned at
DITEMH Returns the height of the nth DEDIT item
DITEMID Returns the id of the nth DEDIT item
DITEMROW Returns the row the nth DEDIT item is positioned at
DITEMS Returns the number of items on DEDIT dialog
DITEMSEL Returns the number of items selected on DEDIT dialog
DITEMSID Returns the id of the nth selected DEDIT item
DITEMTXT Returns the text of the nth DEDIT item
DITEMTYP Returns the type of control of the nth DEDIT item
DITEMW Returns the width of the nth DEDIT item
FILECNT Returns a count of files in named directory
FILEIS Returns if name exists as file or directory
FILEN Returns the nth file in directory
FILESTAT Returns various data about named file e.g. size
FILETIME Returns various dates/times (creation/access) of file in SIR formats
MAKEDIR Creates a directory
NVALLAB Synonym for NLABELS
NVARLAB Returns number of lines in variable label
NVARSC Number of variables in a record including common vars
RECDOC Number of lines of documentation
RECDOCN Nth line of documentation
TABINDU Returns if index unique
TABRECS Returns number of rows on table
TABVINFN Returns various numeric data about a table column
TABVINFS Returns various character data about a table column
TABVRANG Returns valid or missing value ranges
TABVVALI Validates a table variable
TABVVLAB Returns label for value label n for table col
TABVVVAL Returns value for value label n for table col
VALLABSN Returns label for value label n for variable
VALLABSP Returns position of value label associated with specified value
VALLABSV Returns value for value label n for variable
VARLABSN Returns nth line of variable label
VARNAMEC Returns name of variable using counts including common vars
WINCNT Returns the number of lines in the output window
WINLIN Returns the nth line from the output window
WINMOVE Moves and resizes the main window
WINPOS Moves to line in output window and highlights line
WINSELL Returns the line selected
WINSELP Returns the position in the line selected
The COLUMN and ROW functions are obsolete and have been deleted. These returned information about the size of the execution window which is now fixed at 80 columns and 30 rows.
SIR2002 databases are NOT backward compatible with SIR2000 and earlier but can be exported and re-imported if needed to be used with older versions.
The old non-graphical menu system from version 3 is no longer supported. Old text style screen commands in VisualPQL continue to be supported. Forms is still supported although it is not being developed further and does not have constructs to use the new database secondary indexes. (Indexes on records updated in Forms will be maintained correctly.)