DBSMNG D A T A B A S E M A N A G E M E N T P A C K A G E Version 3.1 Written by Ray Di Marco Product version 211184 Document version 221184 Target environment DEC PDP-11 (with EIS) TSX-Plus V5.1 (or equivalent) Approved environments TSX-plus RT11 RSTS/E under RT11 emulator. Development system PDP11/73 under TSX-plus V5.1 The DBSMNG package developed from a suite of modules used to facilitate file I/O and complex data manipulation. As the number of modules in the package expanded, management routines were added to combine the individual modules into programs to perform commonly used functions especially those needed to create, maintain and exploit small databases. DBSMNG is designed to support small, homogeneous databases (ie of approximately 100-10000 records), and allows databases to be quickly established and easily maintained. Included in the package are MENU ... a menu management program DBSEDT ... a screen oriented editor REPORT ... a report writer INSKEY ... an online query program BACKUP ... an incremental backup utility other utility programs and FORTRAN/ASSEMBLER callable routines that allow user application programs to interface with a database. TABLE OF CONTENTS The summary Table of Contents on this page identifies chapters and the pages on which they start. The full Table of Contents listing all chapters, sections and subsections starts on the next page. 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . 1 2 Setting up a Database - the Hard Way . . . . . . . . . . . 11 3 Setting up a Database - The Easy Way . . . . . . . . . . . 25 4 MENU: CUSP Chain Manager . . . . . . . . . . . . . . . . . 30 5 DBSEDT: Database Editor . . . . . . . . . . . . . . . . . 40 6 SELECT: Selective Information Extractor . . . . . . . . . 49 7 SORTER: Sorting Utility . . . . . . . . . . . . . . . . . 60 8 INSKEY: Multi-key Database Inspection CUSP . . . . . . . . 62 9 INSPECT: Single-key Online Database Query CUSP . . . . . . 70 10 REPORT: Report Writer . . . . . . . . . . . . . . . . . . 73 11 BACKUP: Backup Utilities . . . . . . . . . . . . . . . . 93 12 Other CUSPs . . . . . . . . . . . . . . . . . . . . . . 99 13 DBSFOR: FORTRAN Interface . . . . . . . . . . . . . . . . 102 14 XXXFOR: General FORTRAN Support Routines . . . . . . . . 115 15 Installation Guide . . . . . . . . . . . . . . . . . . . 121 16 Additional Information . . . . . . . . . . . . . . . . . 122 17 Index . . . . . . . . . . . . . . . . . . . . . . . . . . 123 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Functional Overview . . . . . . . . . . . . . . . . 1 1.2 Implementation Overview . . . . . . . . . . . . . . 2 1.3 Database Concepts . . . . . . . . . . . . . . . . . 2 1.3.1 Homogeneity . . . . . . . . . . . . . . . . . 2 1.3.2 Database Name . . . . . . . . . . . . . . . . 3 1.3.3 Database Template . . . . . . . . . . . . . . 3 1.3.4 Database Files . . . . . . . . . . . . . . . . 3 1.3.5 Database Entries . . . . . . . . . . . . . . . 3 1.3.6 Blocking Factor . . . . . . . . . . . . . . . 3 1.3.7 Database Fields . . . . . . . . . . . . . . . 4 1.3.7.1 Field Name . . . . . . . . . . . . . . . 4 1.3.7.2 Field Size . . . . . . . . . . . . . . . 4 1.3.7.3 Field Type . . . . . . . . . . . . . . . 4 1.3.7.3.1 .ASC. ASCII . . . . . . . . . . . 4 1.3.7.3.2 .ALP. Alphabetic . . . . . . . . . 4 1.3.7.3.3 .NUM. Numeric . . . . . . . . . . 5 1.3.7.3.3.1 Numeric Limit Check . . . . 5 1.3.7.3.4 .DAT. Date . . . . . . . . . . . . 5 1.3.7.4 Protected Fields . . . . . . . . . . . . 5 1.3.7.5 Key Fields . . . . . . . . . . . . . . . 5 1.3.8 Special Fields . . . . . . . . . . . . . . . . 5 1.3.8.1 rn . . . . . . . . . . . . . . . . . . . 5 1.3.8.2 cn . . . . . . . . . . . . . . . . . . . 5 1.3.8.3 st . . . . . . . . . . . . . . . . . . . 5 1.3.8.4 cd . . . . . . . . . . . . . . . . . . . 6 1.3.8.5 ed . . . . . . . . . . . . . . . . . . . 6 1.3.8.6 dd . . . . . . . . . . . . . . . . . . . 6 1.3.8.7 Using Special Fields . . . . . . . . . . 6 1.4 Visual Display Units . . . . . . . . . . . . . . . . 6 1.4.1 Screen Coordinates . . . . . . . . . . . . . . 6 1.4.2 Special Function Keys . . . . . . . . . . . . 7 1.4.3 Screen Layout . . . . . . . . . . . . . . . . 7 1.4.3.1 Pages . . . . . . . . . . . . . . . . . 7 1.4.3.2 Key Page . . . . . . . . . . . . . . . . 7 1.4.3.3 Labels . . . . . . . . . . . . . . . . . 7 1.4.3.4 Field Name . . . . . . . . . . . . . . . 7 1.4.3.5 Field Delimiters . . . . . . . . . . . . 7 1.5 CUSPs . . . . . . . . . . . . . . . . . . . . . . . 7 1.5.1 Customization . . . . . . . . . . . . . . . . 8 1.5.2 Chaining . . . . . . . . . . . . . . . . . . . 8 1.6 Files . . . . . . . . . . . . . . . . . . . . . . . 8 1.6.1 Database . . . . . . . . . . . . . . . . . . . 8 1.6.1.1 Data File . . . . . . . . . . . . . . . 8 1.6.1.2 Key File . . . . . . . . . . . . . . . . 8 1.6.1.3 Parameter File . . . . . . . . . . . . . 9 1.6.2 Template . . . . . . . . . . . . . . . . . . . 9 1.6.3 RPT . . . . . . . . . . . . . . . . . . . . . 9 1.6.4 FRM . . . . . . . . . . . . . . . . . . . . . 9 1.6.4.1 Uparrow Sequence . . . . . . . . . . . . 9 1.6.4.2 Escape Sequence . . . . . . . . . . . . 9 1.6.4.3 Underline Sequence . . . . . . . . . . . 9 1.6.4.4 ^@ Repeat Block Sequence . . . . . . . . 9 1.6.5 SFL . . . . . . . . . . . . . . . . . . . . . 10 1.6.5.1 SFL File Creation . . . . . . . . . . . 10 1.6.5.2 Header Block . . . . . . . . . . . . . . 10 1.6.5.3 Data Blocks . . . . . . . . . . . . . . 10 1.7 Components . . . . . . . . . . . . . . . . . . . . . 10 1.7.1 CUSPs . . . . . . . . . . . . . . . . . . . . 10 1.7.2 Libraries . . . . . . . . . . . . . . . . . . 10 1.7.3 Sources . . . . . . . . . . . . . . . . . . . 10 1.7.4 Example Application . . . . . . . . . . . . . 10 2 Setting up a Database - the Hard Way . . . . . . . . . . . 11 2.1 Environment Definition . . . . . . . . . . . . . . . 11 2.2 EXAMPLE Database Design . . . . . . . . . . . . . . 12 2.3 Template Definition File . . . . . . . . . . . . . . 13 2.3.1 Preamble . . . . . . . . . . . . . . . . . . . 14 2.3.2 Field Declarations . . . . . . . . . . . . . . 14 2.3.3 Key Declarations . . . . . . . . . . . . . . . 14 2.3.3.1 Explicit Key Declarations . . . . . . . 15 2.3.3.2 Implicit Key Declarations . . . . . . . 15 2.3.4 Special Fields . . . . . . . . . . . . . . . . 15 2.4 Template Definition Macros . . . . . . . . . . . . . 16 2.4.1 FORM Macro . . . . . . . . . . . . . . . . . . 17 2.4.1.1 XXXXXX argument . . . . . . . . . . . . 17 2.4.1.2 ENTRIES argument . . . . . . . . . . . . 17 2.4.1.3 BLOCK argument . . . . . . . . . . . . . 17 2.4.1.4 DEVICE Argument . . . . . . . . . . . . 17 2.4.2 FIELD Macro . . . . . . . . . . . . . . . . . 17 2.4.2.1 X argument . . . . . . . . . . . . . . . 17 2.4.2.2 Y argument . . . . . . . . . . . . . . . 18 2.4.2.3 TYPE argument . . . . . . . . . . . . . 18 2.4.2.4 DATA argument . . . . . . . . . . . . . 18 2.4.2.5 XN argument . . . . . . . . . . . . . . 18 2.4.2.6 NAME argument . . . . . . . . . . . . . 18 2.4.2.7 LD argument . . . . . . . . . . . . . . 18 2.4.2.8 RD argument . . . . . . . . . . . . . . 18 2.4.2.9 CHECK argument . . . . . . . . . . . . . 18 2.4.2.10 PROTECT argument . . . . . . . . . . . 18 2.4.2.11 PAGE argument . . . . . . . . . . . . . 19 2.4.2.12 KEY argument . . . . . . . . . . . . . 19 2.4.3 FORMEND Macro . . . . . . . . . . . . . . . . 19 2.4.4 KEY Macro . . . . . . . . . . . . . . . . . . 19 2.4.4.1 X argument . . . . . . . . . . . . . . . 19 2.4.4.2 Y argument . . . . . . . . . . . . . . . 19 2.4.4.3 TYPE argument . . . . . . . . . . . . . 19 2.4.4.4 DATA argument . . . . . . . . . . . . . 20 2.4.4.5 XN argument . . . . . . . . . . . . . . 20 2.4.4.6 NAME argument . . . . . . . . . . . . . 20 2.4.4.7 LD argument . . . . . . . . . . . . . . 20 2.4.4.8 RD argument . . . . . . . . . . . . . . 20 2.4.4.9 CHECK argument . . . . . . . . . . . . . 20 2.4.5 KEYEND Macro . . . . . . . . . . . . . . . . . 20 2.4.6 LABEL Macro . . . . . . . . . . . . . . . . . 20 2.4.6.1 X argument . . . . . . . . . . . . . . . 20 2.4.6.2 Y argument . . . . . . . . . . . . . . . 20 2.4.6.3 LABEL argument . . . . . . . . . . . . . 20 2.4.6.4 PAGE argument . . . . . . . . . . . . . 21 2.4.6.4.1 Key Page Labels . . . . . . . . . 21 2.4.7 SPARE Macro . . . . . . . . . . . . . . . . . 21 2.5 Example Template Definition File . . . . . . . . . . 22 2.6 Creating the TEMPLATE . . . . . . . . . . . . . . . 23 2.7 Initializing the Database . . . . . . . . . . . . . 23 2.8 Hints and Suggestions . . . . . . . . . . . . . . . 24 3 Setting up a Database - The Easy Way . . . . . . . . . . . 25 3.1 Environment Definition . . . . . . . . . . . . . . . 25 3.2 Designing a CARD . . . . . . . . . . . . . . . . . . 26 3.2.1 Specifying Labels . . . . . . . . . . . . . . 26 3.2.2 Specifying Fields . . . . . . . . . . . . . . 26 3.2.2.1 Specifying the Field Type . . . . . . . 26 3.2.2.2 Specifying a Protected Field . . . . . . 27 3.2.2.3 Specifying a Key Field . . . . . . . . . 27 3.2.2.4 Special Fields . . . . . . . . . . . . . 27 3.2.3 Multi-page Database Design . . . . . . . . . . 27 3.3 Creating the TEMPLATE . . . . . . . . . . . . . . . 27 3.4 Initializing the Database . . . . . . . . . . . . . 28 3.5 Example TPL Card . . . . . . . . . . . . . . . . . . 29 4 MENU: CUSP Chain Manager . . . . . . . . . . . . . . . . . 30 4.1 Concepts . . . . . . . . . . . . . . . . . . . . . . 30 4.1.1 Menu . . . . . . . . . . . . . . . . . . . . . 30 4.1.2 Function Keys . . . . . . . . . . . . . . . . 30 4.1.3 CUSP Chains . . . . . . . . . . . . . . . . . 30 4.1.4 Core Common Commands . . . . . . . . . . . . . 30 4.1.5 User Variable . . . . . . . . . . . . . . . . 31 4.1.5.1 Passing User Variable between Menus . . 31 4.2 Invoking MENU . . . . . . . . . . . . . . . . . . . 31 4.2.1 Format File . . . . . . . . . . . . . . . . . 31 4.2.2 CRT type . . . . . . . . . . . . . . . . . . . 31 4.3 Menu Format File . . . . . . . . . . . . . . . . . . 31 4.3.1 Display Definition Section . . . . . . . . . . 32 4.3.2 Function Definition Section . . . . . . . . . 32 4.3.2.1 Action Definition Blocks . . . . . . . . 32 4.3.2.2 Comments . . . . . . . . . . . . . . . . 32 4.3.3 Conditional Blocks . . . . . . . . . . . . . . 32 4.4 MENU Operation . . . . . . . . . . . . . . . . . . . 32 4.4.1 Screen Initialization . . . . . . . . . . . . 33 4.4.2 Slash Function Execution . . . . . . . . . . . 33 4.4.3 Input Wait . . . . . . . . . . . . . . . . . . 33 4.4.4 Digit Key Function Execution . . . . . . . . . 33 4.5 Operators . . . . . . . . . . . . . . . . . . . . . 33 4.5.1 $* Section Delimiter . . . . . . . . . . . . . 33 4.5.2 $=XXX If Terminal of Type XXX . . . . . . . . 33 4.5.3 $#XXX If Terminal NOT of Type XXX . . . . . . 34 4.5.4 $% Conditional Block Terminator . . . . . . . 34 4.5.5 $/ Auto-initiated Function . . . . . . . . . . 34 4.5.6 $0 Action Block for Function Key 0 . . . . . . 34 4.5.7 $1 Action Block for Function Key 1 . . . . . . 34 4.5.8 $2 Action Block for Function Key 2 . . . . . . 34 4.5.9 $3 Action Block for Function Key 3 . . . . . . 34 4.5.10 $4 Action Block for Function Key 4 . . . . . 34 4.5.11 $5 Action Block for Function Key 5 . . . . . 34 4.5.12 $6 Action Block for Function Key 6 . . . . . 34 4.5.13 $7 Action Block for Function Key 7 . . . . . 35 4.5.14 $8 Action Block for Function Key 8 . . . . . 35 4.5.15 $9 Action Block for Function Key 9 . . . . . 35 4.5.16 $! Action Block Terminator . . . . . . . . . 35 4.5.17 $) Output to Terminal . . . . . . . . . . . . 35 4.5.18 $( Input from Terminal . . . . . . . . . . . 35 4.5.19 $D Define User Variable . . . . . . . . . . . 35 4.5.20 $R Restart . . . . . . . . . . . . . . . . . 35 4.5.21 $@ Initiate CUSP Chain . . . . . . . . . . . 35 4.5.21.1 CUSP Chain Initiation . . . . . . . . . 36 4.5.22 $Q Quiet CUSP Chain . . . . . . . . . . . . . 36 4.5.23 $C Include CUSP in Chain . . . . . . . . . . 36 4.5.24 $I Insert Core Common Command . . . . . . . . 36 4.5.25 $~ Substitute System Parameters . . . . . . . 37 4.5.25.1 $~J Operator: Job Number . . . . . . . 37 4.5.25.2 $~P Operator: Primary Line Number . . . 37 4.5.25.3 $~N Operator: User Name . . . . . . . . 37 4.5.25.4 $~V Operator: Flag Virtual Line . . . . 37 4.5.25.5 $~P Operator: Project Number . . . . . 37 4.5.25.6 $~Q Operator: Programmer Number . . . . 37 4.5.25.7 $~( Operator: User Variable . . . . . . 37 4.5.25.8 $~A Operator: Device Assignment . . . . 37 4.6 Hints and Suggestions . . . . . . . . . . . . . . . 37 4.6.1 Core Common Overflow . . . . . . . . . . . . . 37 4.6.2 Very Long CUSP Chains . . . . . . . . . . . . 38 4.6.3 Including VT100 Escape Sequences . . . . . . . 38 4.7 Example Menu . . . . . . . . . . . . . . . . . . . . 39 5 DBSEDT: Database Editor . . . . . . . . . . . . . . . . . 40 5.1 Terminal . . . . . . . . . . . . . . . . . . . . . . 40 5.2 Invoking DBSEDT . . . . . . . . . . . . . . . . . . 40 5.2.1 Using a start-up file . . . . . . . . . . . . 41 5.2.2 CUSP chaining . . . . . . . . . . . . . . . . 41 5.3 Screen Layout . . . . . . . . . . . . . . . . . . . 41 5.3.1 Interaction Area . . . . . . . . . . . . . . . 41 5.3.2 Form Area . . . . . . . . . . . . . . . . . . 41 5.4 Input Modes . . . . . . . . . . . . . . . . . . . . 42 5.4.1 Command Input Mode . . . . . . . . . . . . . . 42 5.4.2 Data Input Mode . . . . . . . . . . . . . . . 42 5.4.2.1 Cursor Movement Keys . . . . . . . . . . 43 5.4.2.1.1 UP . . . . . . . . . . . . . . . . 43 5.4.2.1.2 DOWN . . . . . . . . . . . . . . . 43 5.4.2.1.3 LEFT . . . . . . . . . . . . . . . 43 5.4.2.1.4 RIGHT . . . . . . . . . . . . . . 43 5.4.2.2 Special Function Keys . . . . . . . . . 43 5.4.2.2.1 PF1 . . . . . . . . . . . . . . . 43 5.4.2.2.2 PF2 . . . . . . . . . . . . . . . 43 5.4.2.2.3 PF3 . . . . . . . . . . . . . . . 43 5.4.2.2.4 PF4 . . . . . . . . . . . . . . . 43 5.4.2.3 Control keys . . . . . . . . . . . . . . 43 5.4.2.3.1 TAB . . . . . . . . . . . . . . . 44 5.4.2.3.2 BACKSPACE . . . . . . . . . . . . 44 5.4.2.3.3 DELETE . . . . . . . . . . . . . . 44 5.4.2.3.4 Control-U . . . . . . . . . . . . 44 5.4.2.4 Automatic TABing . . . . . . . . . . . . 44 5.5 Accessing Records . . . . . . . . . . . . . . . . . 44 5.5.1 Explicit Record Access . . . . . . . . . . . . 44 5.5.2 Keyed Record Access . . . . . . . . . . . . . 45 5.6 DBSEDT Commands . . . . . . . . . . . . . . . . . . 45 5.6.1 BYE: Terminate Execution . . . . . . . . . . . 45 5.6.1.1 [RETURN] Implicit Bye Command . . . . . 45 5.6.2 CREATE: Create a New Record . . . . . . . . . 45 5.6.2.1 CREATE N: Create a Copy of Record N . . 45 5.6.3 NEW: Create a New Record . . . . . . . . . . . 46 5.6.3.1 NEW N: Create a Copy of Record N . . . . 46 5.6.4 EDIT: Edit an Existing Record . . . . . . . . 46 5.6.5 INSPECT: Examine Record . . . . . . . . . . . 46 5.6.6 DELETE: Mark Record as Deleted . . . . . . . . 46 5.6.6.1 Recovering DELETED Records . . . . . . . 46 5.6.7 PAGE N: Select Page N . . . . . . . . . . . . 46 5.7 Form Editing Mode Commands . . . . . . . . . . . . . 46 5.7.1 EXIT: Exit Edit Submode . . . . . . . . . . . 47 5.7.1.1 [RETURN] Implicit EXIT Command . . . . . 47 5.7.2 ABORT: Abort Edit Submode . . . . . . . . . . 47 5.7.3 EDIT: Edit Field Data . . . . . . . . . . . . 47 5.7.4 FIRST: Edit First Field . . . . . . . . . . . 47 5.7.5 TOP: Edit Field at Top of Page . . . . . . . . 47 5.7.6 LOCATE: Edit Specified Field . . . . . . . . . 47 5.7.7 PAGE: Select New Page . . . . . . . . . . . . 47 5.7.8 CLEAR: Clear out Record . . . . . . . . . . . 47 5.7.9 REFRESH: Refresh Screen . . . . . . . . . . . 48 6 SELECT: Selective Information Extractor . . . . . . . . . 49 6.1 General Overview . . . . . . . . . . . . . . . . . . 49 6.1.1 FRM File . . . . . . . . . . . . . . . . . . . 49 6.1.1.1 Uparrow Sequence . . . . . . . . . . . . 49 6.1.1.2 Escape Sequence . . . . . . . . . . . . 49 6.1.1.3 Underline Sequence . . . . . . . . . . . 50 6.1.1.4 ^@ Repeat Block Sequence . . . . . . . . 50 6.1.2 SFL File . . . . . . . . . . . . . . . . . . . 50 6.1.2.1 SFL File Creation . . . . . . . . . . . 50 6.1.2.2 Header Block . . . . . . . . . . . . . . 50 6.1.2.3 Data Blocks . . . . . . . . . . . . . . 50 6.1.2.4 Usage . . . . . . . . . . . . . . . . . 50 6.1.2.4.1 Record Selection . . . . . . . . . 51 6.1.2.4.2 Data Extraction . . . . . . . . . 51 6.1.2.4.3 Interfacing to User Programs . . . 51 6.1.3 Record Size . . . . . . . . . . . . . . . . . 51 6.1.4 Cycle . . . . . . . . . . . . . . . . . . . . 51 6.2 Invoking SELECT . . . . . . . . . . . . . . . . . . 51 6.2.1 SELECT Prompts . . . . . . . . . . . . . . . . 51 6.2.1.1 DB Template . . . . . . . . . . . . . . 52 6.2.1.2 Format File . . . . . . . . . . . . . . 52 6.2.1.3 Output File . . . . . . . . . . . . . . 52 6.2.1.4 Record Size . . . . . . . . . . . . . . 52 6.3 SELECT Operation . . . . . . . . . . . . . . . . . . 52 6.3.1 Output Buffer . . . . . . . . . . . . . . . . 52 6.3.2 Output Enable Flag . . . . . . . . . . . . . . 52 6.3.3 Rewinding the Format File . . . . . . . . . . 53 6.4 Arguments . . . . . . . . . . . . . . . . . . . . . 53 6.4.1 Constants . . . . . . . . . . . . . . . . . . 53 6.4.1.1 Numeric Constants . . . . . . . . . . . 53 6.4.1.2 Date Constants . . . . . . . . . . . . . 53 6.4.1.3 Literal Constants . . . . . . . . . . . 53 6.4.2 Variables . . . . . . . . . . . . . . . . . . 53 6.4.2.1 Numeric Variables . . . . . . . . . . . 53 6.4.2.2 Date Variables . . . . . . . . . . . . . 54 6.4.2.2.1 D4: System Date . . . . . . . . . 54 6.4.2.3 Literal Variables . . . . . . . . . . . 54 6.4.2.4 Variable Passing Between CUSPs . . . . . 54 6.4.3 Field Contents . . . . . . . . . . . . . . . . 54 6.4.3.1 Direct Field Access . . . . . . . . . . 54 6.4.3.2 Indirect Field Access . . . . . . . . . 54 6.5 SELECT Operators . . . . . . . . . . . . . . . . . . 54 6.5.1 $$ End of Block . . . . . . . . . . . . . . . 55 6.5.2 $E End of Program . . . . . . . . . . . . . . 55 6.5.3 $* Marking Rewind Point . . . . . . . . . . . 55 6.5.4 $A Append to Existing SFL Contents . . . . . . 55 6.5.5 $R Range Specification . . . . . . . . . . . . 55 6.5.6 $) Output to Terminal . . . . . . . . . . . . 55 6.5.7 $? Input Data from Terminal . . . . . . . . . 55 6.5.8 $( Input Field Name from Terminal . . . . . . 56 6.5.9 $T Do Tests . . . . . . . . . . . . . . . . . 56 6.5.9.1 Output Enable Flag . . . . . . . . . . . 56 6.5.9.1.1 OEF Initialization . . . . . . . . 56 6.5.9.1.2 OEF Setting . . . . . . . . . . . 56 6.5.9.2 $C Clear OEF . . . . . . . . . . . . . . 56 6.5.9.3 Boolean Statements . . . . . . . . . . . 56 6.5.9.3.1 .EQ. Boolean-Operator . . . . . . 57 6.5.9.3.2 .NE. Boolean-Operator . . . . . . 57 6.5.9.3.3 .LT. Boolean-Operator . . . . . . 57 6.5.9.3.4 .GT. Boolean-Operator . . . . . . 57 6.5.9.3.5 .LE. Boolean-Operator . . . . . . 57 6.5.9.3.6 .GE. Boolean-Operator . . . . . . 57 6.5.10 $L Load Data . . . . . . . . . . . . . . . . 57 6.5.10.1 Output Buffer . . . . . . . . . . . . . 57 6.5.10.2 Imbedded Operators . . . . . . . . . . 57 6.5.10.2.1 $< Trim . . . . . . . . . . . . . 57 6.5.10.2.2 $> Right Justify . . . . . . . . 57 6.5.10.2.3 $:DD Position . . . . . . . . . . 58 6.5.10.2.4 $^ Uppercase . . . . . . . . . . 58 6.5.10.2.5 $~ Flush . . . . . . . . . . . . 58 6.5.10.2.6 $S Switch . . . . . . . . . . . . 58 6.5.10.2.7 $# Convert to Ascii . . . . . . . 58 6.5.10.2.8 $- Delete Character . . . . . . . 58 6.6 Example Format Files . . . . . . . . . . . . . . . . 58 7 SORTER: Sorting Utility . . . . . . . . . . . . . . . . . 60 7.1 Using SORTER . . . . . . . . . . . . . . . . . . . . 60 7.2 Optional SORTER Switches . . . . . . . . . . . . . . 60 7.2.1 /R Reverse Order Switch . . . . . . . . . . . 60 7.2.2 /S Statistics Switch . . . . . . . . . . . . . 60 7.2.3 /P Pause Switch . . . . . . . . . . . . . . . 60 7.3 Operational Description . . . . . . . . . . . . . . 61 7.4 Interfacing to SORTER . . . . . . . . . . . . . . . 61 8 INSKEY: Multi-key Database Inspection CUSP . . . . . . . . 62 8.1 Invoking INSKEY . . . . . . . . . . . . . . . . . . 62 8.1.1 Using a start-up file . . . . . . . . . . . . 62 8.1.2 Using CUSP chaining from a menu . . . . . . . 62 8.2 Screen Layout . . . . . . . . . . . . . . . . . . . 63 8.2.1 Interaction Area . . . . . . . . . . . . . . . 63 8.2.2 Form Area . . . . . . . . . . . . . . . . . . 63 8.3 Input Modes . . . . . . . . . . . . . . . . . . . . 63 8.3.1 Command Input Mode . . . . . . . . . . . . . . 63 8.3.2 Keyword Input Mode . . . . . . . . . . . . . . 63 8.3.2.1 Cursor Movement Keys . . . . . . . . . . 64 8.3.2.1.1 UP . . . . . . . . . . . . . . . . 64 8.3.2.1.2 DOWN . . . . . . . . . . . . . . . 64 8.3.2.1.3 LEFT . . . . . . . . . . . . . . . 64 8.3.2.1.4 RIGHT . . . . . . . . . . . . . . 64 8.3.2.2 Special Function Keys . . . . . . . . . 64 8.3.2.2.1 PF1 . . . . . . . . . . . . . . . 64 8.3.2.2.2 PF2 . . . . . . . . . . . . . . . 64 8.3.2.2.3 PF3 . . . . . . . . . . . . . . . 64 8.3.2.2.4 PF4 . . . . . . . . . . . . . . . 64 8.3.2.3 Control keys . . . . . . . . . . . . . . 64 8.3.2.3.1 TAB . . . . . . . . . . . . . . . 64 8.3.2.3.2 BACKSPACE . . . . . . . . . . . . 64 8.3.2.3.3 DELETE . . . . . . . . . . . . . . 64 8.3.2.3.4 Control-U . . . . . . . . . . . . 65 8.4 Using Keywords to Identify Records of Interest . . . 65 8.4.1 Keywords and Keys . . . . . . . . . . . . . . 65 8.4.2 Keyword . . . . . . . . . . . . . . . . . . . 65 8.4.2.1 Spaces . . . . . . . . . . . . . . . . . 65 8.4.2.2 Lower Case . . . . . . . . . . . . . . . 65 8.4.3 Comma Delimiter: Approximate Match . . . . . . 65 8.4.4 Semicolon Delimiter: Exact Match . . . . . . . 65 8.4.5 Multiple Keywords . . . . . . . . . . . . . . 66 8.4.6 Adding Keywords . . . . . . . . . . . . . . . 66 8.5 INSKEY Commands . . . . . . . . . . . . . . . . . . 66 8.5.1 [RETURN] Key . . . . . . . . . . . . . . . . . 66 8.5.2 E Command (Exit) . . . . . . . . . . . . . . . 66 8.5.3 H Command (Help) . . . . . . . . . . . . . . . 66 8.5.4 K Command (Keywords) . . . . . . . . . . . . . 66 8.5.5 A Command (Add-keywords) . . . . . . . . . . . 66 8.5.6 N Command (Next) . . . . . . . . . . . . . . . 67 8.5.7 P Command (Print) . . . . . . . . . . . . . . 67 8.5.8 F Command (Flip-page) . . . . . . . . . . . . 67 8.5.9 R command (Refresh) . . . . . . . . . . . . . 67 8.6 Using INSKEY . . . . . . . . . . . . . . . . . . . . 67 8.7 Setting up INSKEY . . . . . . . . . . . . . . . . . 68 8.7.1 Invoking INSKEY from a Menu . . . . . . . . . 68 8.7.2 Setting up the INDEX File . . . . . . . . . . 68 8.7.2.1 Creating the INDEX File . . . . . . . . 68 8.7.2.2 Loading Keys into the INDEX File . . . . 68 8.7.2.3 Sorting the INDEX File . . . . . . . . . 69 8.7.2.4 Generating the INDEX File from a Menu . 69 8.7.3 Setting up the HELP File . . . . . . . . . . . 69 9 INSPECT: Single-key Online Database Query CUSP . . . . . . 70 9.1 Concepts . . . . . . . . . . . . . . . . . . . . . . 70 9.1.1 Target . . . . . . . . . . . . . . . . . . . . 70 9.1.2 Target Fields . . . . . . . . . . . . . . . . 70 9.1.3 Index File . . . . . . . . . . . . . . . . . . 70 9.2 Using INSPECT . . . . . . . . . . . . . . . . . . . 71 9.3 Application Notes . . . . . . . . . . . . . . . . . 71 9.3.1 Target Fields . . . . . . . . . . . . . . . . 71 9.3.2 Creating the Index File . . . . . . . . . . . 72 9.3.3 Creating the FRM file for SELECT . . . . . . . 72 9.3.4 Setting up the Index File . . . . . . . . . . 72 10 REPORT: Report Writer . . . . . . . . . . . . . . . . . . 73 10.1 Background . . . . . . . . . . . . . . . . . . . . 73 10.1.1 FRM File . . . . . . . . . . . . . . . . . . 73 10.1.1.1 Uparrow Sequence . . . . . . . . . . . 73 10.1.1.2 Escape Sequence . . . . . . . . . . . . 74 10.1.1.3 Underline Sequence . . . . . . . . . . 74 10.1.1.4 ^@ Repeat Block Sequence . . . . . . . 74 10.1.1.5 TPLREP CUSP . . . . . . . . . . . . . . 74 10.1.1.6 SFL File . . . . . . . . . . . . . . . 74 10.1.1.6.1 Header Block . . . . . . . . . . 74 10.1.1.6.2 Data Blocks . . . . . . . . . . . 74 10.1.1.6.3 Usage . . . . . . . . . . . . . . 75 10.1.1.6.4 Entry Size . . . . . . . . . . . 75 10.2 Invoking REPORT . . . . . . . . . . . . . . . . . . 75 10.2.1 Prompt: DB Template . . . . . . . . . . . . . 75 10.2.2 Prompt: Format File . . . . . . . . . . . . . 75 10.2.3 Prompt: Sorter File . . . . . . . . . . . . . 75 10.2.3.1 Omitting Sorter File . . . . . . . . . 75 10.2.4 Prompt: Output File . . . . . . . . . . . . . 76 10.3 REPORT Operational Overview . . . . . . . . . . . . 76 10.3.1 REPORT Cycle . . . . . . . . . . . . . . . . 76 10.3.2 Rewinding the FRM File . . . . . . . . . . . 76 10.3.3 REPORT ($X) Operators . . . . . . . . . . . . 76 10.3.4 REPORT Variables . . . . . . . . . . . . . . 77 10.3.4.1 ! Primary Files . . . . . . . . . . . . 77 10.3.4.2 @ Terminal . . . . . . . . . . . . . . 77 10.3.4.3 A-Z Memory Variables . . . . . . . . . 77 10.3.5 I/O Stream Selection . . . . . . . . . . . . 77 10.3.5.1 Assign RPT File Output Stream . . . . . 77 10.3.5.2 Assign Terminal Output Stream . . . . . 77 10.3.5.3 Assign Memory Variable Output Stream . 77 10.3.5.4 Assign FRM File Input Stream . . . . . 77 10.3.5.5 Assign Terminal Input Stream . . . . . 77 10.3.5.6 Assign Memory Variable Input Stream . . 78 10.3.6 Memory Variable Initialization . . . . . . . 78 10.3.6.1 Memory Variable F Initialization . . . 78 10.3.7 BCD Binary Coded Decimal . . . . . . . . . . 79 10.4 FRM File Structure . . . . . . . . . . . . . . . . 79 10.4.1 Header Section . . . . . . . . . . . . . . . 79 10.4.2 Body Section . . . . . . . . . . . . . . . . 79 10.4.3 Trailer Section . . . . . . . . . . . . . . . 79 10.4.4 All Records Processed Event Handling . . . . 80 10.5 REPORT Operators . . . . . . . . . . . . . . . . . 80 10.5.1 Summary of Operators . . . . . . . . . . . . 80 10.5.1.1 REPORT Main Operators . . . . . . . . . 80 10.5.1.2 REPORT $B (BCD) sub-operators . . . . . 81 10.5.1.3 REPORT $BF (BCD format) sub-operators . 81 10.5.1.4 REPORT $T (TEST) sub-operators . . . . 81 10.5.1.5 REPORT $< (field data) sub-operators . 82 10.5.2 $B BCD Operator . . . . . . . . . . . . . . . 82 10.5.2.1 + BCD addition sub-operator . . . . . . 82 10.5.2.2 - BCD subtraction sub-operator . . . . 82 10.5.2.3 I BCD increment sub-operator . . . . . 82 10.5.2.4 D BCD decrement sub-operator . . . . . 83 10.5.2.5 Z BCD zero sub-operator . . . . . . . . 83 10.5.2.6 F BCD format sub-operator . . . . . . . 83 10.5.3 $C Comment . . . . . . . . . . . . . . . . . 84 10.5.4 $D Dating . . . . . . . . . . . . . . . . . . 84 10.5.5 $E Terminate if all Records Processed . . . . 84 10.5.6 $F Format Operator . . . . . . . . . . . . . 84 10.5.7 $I Switch Input Stream . . . . . . . . . . . 85 10.5.8 $M Select Termination Mode . . . . . . . . . 85 10.5.8.1 M1 Termination Mode . . . . . . . . . . 85 10.5.8.2 M2 Termination Mode . . . . . . . . . . 85 10.5.8.3 M3 Termination Mode . . . . . . . . . . 85 10.5.8.4 M4 Termination Mode . . . . . . . . . . 85 10.5.9 $N Next Record . . . . . . . . . . . . . . . 86 10.5.10 $O Output Stream . . . . . . . . . . . . . . 86 10.5.11 $P Pagination . . . . . . . . . . . . . . . 86 10.5.12 $R Define Ruler . . . . . . . . . . . . . . 86 10.5.13 $T Test Operator . . . . . . . . . . . . . . 86 10.5.13.1 $TE End of Section Marker . . . . . . 87 10.5.13.2 $TR Special End of Section Marker . . 87 10.5.13.3 $T= Skip if Variables Equal . . . . . 87 10.5.13.4 $T= Skip if Variables Equal . . . . . 87 10.5.13.5 $T# Skip if Variables Not Equal . . . 87 10.5.13.6 $T- Skip if Variable Less Than 0 . . . 87 10.5.13.7 $T0 Skip if Variable is 0 . . . . . . 87 10.5.13.8 $T+ Skip if Variable Greater Than 0 . 87 10.5.14 $< Extract Field Data . . . . . . . . . . . 87 10.5.14.1 Field Name . . . . . . . . . . . . . . 88 10.5.14.1.1 Field Name in Variable . . . . . 88 10.5.14.2 Extracted Data: Numeric Fields . . . . 88 10.5.14.3 Extracted Data: Date Fields . . . . . 88 10.5.14.4 Extracted Data: Ascii Fields . . . . . 88 10.5.14.5 Terminators . . . . . . . . . . . . . 88 10.5.15 $; Suppress RPT Output . . . . . . . . . . . 89 10.5.16 $: Absolute Position . . . . . . . . . . . . 89 10.5.17 $) Output to Terminal . . . . . . . . . . . 89 10.5.18 $\ Section Delimiter . . . . . . . . . . . . 89 10.5.19 $= Terminate if Variables Equal . . . . . . 89 10.5.20 $# Terminate if Variables Not Equal . . . . 89 10.5.21 $* Section Delimiter . . . . . . . . . . . . 90 10.6 Example FRM Files . . . . . . . . . . . . . . . . . 90 11 BACKUP: Backup Utilities . . . . . . . . . . . . . . . . 93 11.1 Function . . . . . . . . . . . . . . . . . . . . . 93 11.1.1 BACKUT . . . . . . . . . . . . . . . . . . . 93 11.1.2 BACKUP . . . . . . . . . . . . . . . . . . . 93 11.1.3 BACKUF . . . . . . . . . . . . . . . . . . . 93 11.1.4 BACKUN . . . . . . . . . . . . . . . . . . . 94 11.1.5 CUSP Chaining . . . . . . . . . . . . . . . . 94 11.2 Information Required . . . . . . . . . . . . . . . 94 11.3 Backup File . . . . . . . . . . . . . . . . . . . . 94 11.4 Using BACKUP in an Application . . . . . . . . . . 95 11.5 Example MENU and SELECT Format Files . . . . . . . 97 12 Other CUSPs . . . . . . . . . . . . . . . . . . . . . . 99 12.1 CMDDSP: Dispatch Command to Monitor . . . . . . . . 99 12.2 DBSCPY: Copy Database . . . . . . . . . . . . . . . 99 12.3 DBSMAK: Make Database . . . . . . . . . . . . . . . 100 12.4 DBSSTS: TEMPLATE Disassembler . . . . . . . . . . . 100 12.5 DBSTPL: Template Compiler . . . . . . . . . . . . . 100 12.6 FINDER: Find Matching Records in SFL File . . . . . 100 12.7 PURGER: Purge Database Records . . . . . . . . . . 101 13 DBSFOR: FORTRAN Interface . . . . . . . . . . . . . . . . 102 13.1 Using DBSFOR . . . . . . . . . . . . . . . . . . . 103 13.2 Accessing a database . . . . . . . . . . . . . . . 103 13.3 Overheads . . . . . . . . . . . . . . . . . . . . . 103 13.4 Multiple database access . . . . . . . . . . . . . 103 13.5 Error handling . . . . . . . . . . . . . . . . . . 104 13.6 Channel Purging . . . . . . . . . . . . . . . . . . 104 13.7 DBSFOR Calling Sequences . . . . . . . . . . . . . 104 13.7.1 Function 0: Record Read . . . . . . . . . . . 104 13.7.2 Function 1: Record Write . . . . . . . . . . 104 13.7.3 Function 2: Field Get . . . . . . . . . . . . 105 13.7.4 Function 3: Field Put . . . . . . . . . . . . 105 13.7.5 Function 4: Read Keys . . . . . . . . . . . . 105 13.7.6 Function 5: Write Keys . . . . . . . . . . . 105 13.7.7 Function 6: Key Number . . . . . . . . . . . 106 13.7.8 Function 7: Database Size . . . . . . . . . . 106 13.7.9 Function 8: Database Name . . . . . . . . . . 106 13.7.10 Function 9: Database Select . . . . . . . . 106 13.7.11 Function 10: Database Initialize . . . . . . 106 13.7.12 Function 11: Return Date (Internal Format) . 106 13.7.13 Function 12: To Internal Date Format . . . . 106 13.7.14 Function 13: From Internal Date Format . . . 107 13.7.15 Function 14: Date Comparison . . . . . . . . 107 13.7.16 Function 15: Special Database Initialize . . 107 13.7.17 Function 16: Envoke FORM Editor . . . . . . 107 13.7.18 Function 17: Interface Initialize . . . . . 108 13.7.19 Function 18: Exit . . . . . . . . . . . . . 108 13.7.20 Function 19: Erase Screen . . . . . . . . . 108 13.7.21 Function 20: Input Character . . . . . . . . 108 13.7.22 Function 21: Output Character . . . . . . . 108 13.7.23 Function 22: Input Line . . . . . . . . . . 109 13.7.24 Function 23: Output Line . . . . . . . . . . 109 13.7.25 Function 24: Erase Line . . . . . . . . . . 109 13.7.26 Function 25: Position Cursor . . . . . . . . 109 13.7.27 Function 26: Initialize Core Common . . . . 109 13.7.28 Function 27: Core Common Chain . . . . . . . 109 13.7.29 Function 28: Core Common Input . . . . . . . 109 13.7.30 Function 29: Core Common Check . . . . . . . 109 13.7.31 Function 30: Return Field Attributes . . . . 110 13.7.32 Function 31: Set Field Attributes . . . . . 110 13.7.33 Function 32: Initialize Database . . . . . . 110 13.8 Examples . . . . . . . . . . . . . . . . . . . . . 110 14 XXXFOR: General FORTRAN Support Routines . . . . . . . . 115 14.1 Error handling . . . . . . . . . . . . . . . . . . 115 14.2 CONFOR: Core-Common and Console Support . . . . . . 115 14.3 SRTFOR: SFL File I/O Support . . . . . . . . . . . 116 14.3.1 Rewind SFL File . . . . . . . . . . . . . . . 116 14.3.2 Close SFL File . . . . . . . . . . . . . . . 116 14.3.3 Read SFL File Parameters . . . . . . . . . . 116 14.3.4 Setup SFL File Parameters . . . . . . . . . . 117 14.3.5 Read SFL File Data . . . . . . . . . . . . . 117 14.3.6 Write to SFL File . . . . . . . . . . . . . . 117 14.3.7 Chain to SORTER . . . . . . . . . . . . . . . 117 14.3.8 Chain to Specified Program . . . . . . . . . 117 14.3.9 Peek at SFL Header Block . . . . . . . . . . 117 14.3.10 Poke into SFL Header Block . . . . . . . . . 117 14.3.11 Open SFL File . . . . . . . . . . . . . . . 118 14.3.12 Position at Specified Record . . . . . . . . 118 14.4 FNDFOR: Find Matching Entry in SFL File . . . . . . 118 14.4.1 Opening SFL File . . . . . . . . . . . . . . 118 14.4.2 Rewind File . . . . . . . . . . . . . . . . . 118 14.4.3 Position at Specific Entry . . . . . . . . . 118 14.4.4 Read SFL File Parameters . . . . . . . . . . 118 14.4.5 Locate Matching Entries . . . . . . . . . . . 119 14.4.6 Read SFL Data . . . . . . . . . . . . . . . . 119 14.4.7 Example use of FNDFOR . . . . . . . . . . . . 120 15 Installation Guide . . . . . . . . . . . . . . . . . . . 121 15.1 Running under RSTS/E . . . . . . . . . . . . . . . 121 16 Additional Information . . . . . . . . . . . . . . . . . 122 16.1 RT-11 and RSTS/E differences . . . . . . . . . . . 122 16.2 Supporting a Different Terminal Type . . . . . . . 122 16.3 Module Documentation . . . . . . . . . . . . . . . 122 16.4 Obtaining additional help and information . . . . . 122 17 Index . . . . . . . . . . . . . . . . . . . . . . . . . . 123 Chapter 1 Introduction This chapter is intended as a general introduction to familiarize the reader with DBSMNG terminology and concepts, and to provide a summary of the material presented in the rest of the document. 1.1 Functional Overview The DBSMNG (Database Management) package was designed to allow a homogeneous database to be quickly set up on the smaller DEC computers, and provides the application manager with a number of tools to simplify the integration of such a database into an application subsystem. DBSMNG provides the application manager with the following A simple method of defining the structure of the database. This is done by means of a TEMPLATE which defines the name and attributes of the fields that make up a record. A MENU program that can be used to invoke other database utility programs and pass them commands. This allows the database/application manager to protect the user from the complexities of the computer/operating system being used. A screen oriented EDITOR which allows records to be created, edited and deleted. The editor supports protected and database managed fields, and was designed for use by persons with little or no computer experience. A typical secretary or clerical assistant requires one to three hours of training to be able to effectively use the editor. A REPORT generation subsystem which allows specific records to be selected and used to generate user formatted reports. The selected records can be sorted by any field and the report format can be easily changed. An online QUERY subsystem which allows records to be quickly retrieved for inspection and optionally listed on a printer. This subsystem supports multiple keywords. A BACKUP program that allows selected records to be archived and retrieved. This program can operate in incremental backup mode (ie only records that have been changed are archived), and is very useful when large, frequently modified databases are used. FORTRAN interfaces which allow database records to be accessed and the DBSMNG utilities (eg SORTER) to be invoked from a user application program. The FORTRAN interface allows multiple databases to be accessed simultaneously, and allows record data to be accessed via logical field name, thereby providing data abstraction. DBSMNG: Database Management Package 1 The package is currently in use on a number of different computer systems, including a floppy based PDP11/03, hard disk based PDP11/23s and a PDP11/40, and can be used under the RT-11, TSX-plus and RSTS/E operating systems. 1.2 Implementation Overview The DBSMNG kit contains a number of programs. Each program is designed to do one (and only one) task. Complex functions are implemented by chaining together a number of programs (just as one would do under UNIX). This allows each program to be optimized for the function it is to perform. A special program is provided in the kit to allow the chaining together of programs. This program can also be used to hide the idiosyncrasies of the operating system from the novice user. A very simple file structure is used to store data. While the structure is very robust and allows for easy expansion, it does not allow for rapid location of data. This difficulty is overcome by means of a sorted index file which is easily generated with the tools provided, and can be used with the online query programs for the rapid retrieval of data. DBSMNG is implemented in DEC MACRO-11 assembler. This has been done to ensure minimal memory requirements and to minimize processor loading. The overlay facility of the RT-11 linker is used to further reduce memory requirements. 1.3 Database Concepts A database is typically established to simplify the storage and retrieval of related items of data, and consists of a set of homogeneous records, each of which is divided into a number of fields. Each record is uniquely identified by a 16 bit integer number, termed the RECORD NUMBER, and each field by a unique ASCII name termed the FIELD NAME. In some applications, more than one database may be used, therefore each database has a unique six letter name, termed the Database Name, that unambiguously identifies it. In summary, a database is identified by a unique six letter name, and consists of a set of numbered records, each of which consists of a set of named fields. 1.3.1 Homogeneity - DBSMNG is designed to operate on databases that have records of fixed length and with identical attributes for corresponding fields in each record. Such databases are termed homogeneous (as all records are identically structured). While this tends to be inefficient in terms of disk usage, the resulting increases in speed and security were considered more important. DBSMNG: Database Management Package 2 1.3.2 Database Name - For added security, each database must be assigned a unique six letter name, termed the Database Name, when it is first created. This name is used by the package as an added security check when performing critical operations. The sequence XXXXXX is used in this documentation to represent a database name. 1.3.3 Database Template - The structure of a homogeneous database is completely defined by describing the fields that make up one record. This description is called the Template, and defines the logical name and attributes of the fields in each record. DBSMNG requires that a template be created and made available for it to access a database. Such a template is easily created and consists of a simple definition for each field in a record, declaring its logical name and attributes. Once created it can be used to generate the customized version of the CUSPs (not normally recommended), or to produce a loadable version of the template (termed the TEMPLATE) that can be used with the uncustomized CUSPs. 1.3.4 Database Files - A DBSMNG database consists of three files. These files have identical names but different extensions. These files are created by the DBSMAK utility, and are XXXXXX.DAT ... holds data records XXXXXX.KEY ... holds access keys XXXXXX.PAR ... holds parameters where XXXXXX is the 6 letter database name The data is stored in the DAT file as a series of records. To achieve rapid record access, the KEY file is used to index into the data file. The parameter file consists of one block and is used to store auxiliary information associated with the database. 1.3.5 Database Entries - The size of a database is determined by the number of records that can be simultaneously stored and the blocking factor used (viz the amount of disk storage used per block). The number of records that can be simultaneously stored is called the number of entries in the database, and must be specified when the database template is created. (The number of entries can in fact be changed at a later time.) 1.3.6 Blocking Factor - For various reasons (mostly to simplify access), information is stored on disk in units called Blocks. Each disk block can hold 512 bytes of data. The blocking factor determines the number of records that are to be stored in each disk block, or in the case of large records, the number of disk blocks occupied by each record. The blocking factor is represented as a signed integer. If no blocking factor is explicitly declared for a database, DBSMNG will automatically assign the smallest blocking factor possible. The table below shows the meaning of the various blocking factors available. factor meaning +N each record occupies N blocks +1 each record occupies 1 blocks 0 each record occupies 1 blocks -1 each record occupies 1 blocks -2 two records are stored per block -3 four records are stored per block -4 eight records stored per block DBSMNG: Database Management Package 3 -N 2**(N-1) records stored per block 1.3.7 Database Fields - A database consists of a number of records, and each record consists of a number of fields. A field is an information grouping and has a logical name and attributes. All data grouped into a field must be of the same type, and the information groupings should be small enough to allow the data to be easily manipulated. It is important to use as many fields as possible in a record, as a field is the smallest addressable entity, and it is not possible (without writing your own application program) to access only a portion of the information in a field. An excellent example of the advantage in using many fields to store a item of data is in storing addresses. Assume that one database contains a field called ADDRESS, in which all the address information is stored, while another stores the same information, but in the set of fields called STREET, SUBURB and POSTCODE, and it becomes necessary to produce a report with the records sorted in order of ascending ADDRESS. In reality, this has to be done by sorting records by SUBURB, and within each SUBURB by STREET. This is easy to do with the second database, but impossible with the first. 1.3.7.1 Field Name - Each field must be assigned a unique name, as DBSMNG only allows data within a record to be accessed via its logical name. It is strongly suggested that each field be given a meaningful name that indicates the information it stores. A field name can consists of from 1 to 127 characters. Fields with the following names are reserved rn cn st cd ed dd as they are automatically updated by the DBSMNG CUSPs. See below for more information. 1.3.7.2 Field Size - The size of a field determines the amount of information that can be stored in it. It is important to correctly size a field to ensure that it can hold all desired information, and to divide the record into as many meaningful fields as possible. 1.3.7.3 Field Type - DBSMNG supports four different types of fields, and stores the information within each in a different manner. The four different types are identified by five letter sequences. 1.3.7.3.1 .ASC. ASCII - type fields are used to store a series of characters. The number of characters that can be stored in the field depends on its length. Characters are stored one per byte. 1.3.7.3.2 .ALP. Alphabetic - type fields are used to store series of alphabetic characters. The following ascii characters make up the alphabetic subset .ABCDEFGHIJKLMNOPQRSTUVWXYZ The number of characters that can be stored in the field depends on its length. Characters are stored one per byte. DBSMNG: Database Management Package 4 1.3.7.3.3 .NUM. Numeric - type fields are used to store numbers in the range 0 to 65535. A numeric field requires two bytes. (The least significant bits are stored in the low order byte.) 1.3.7.3.3.1 Numeric Limit Check - Each numeric field may be assigned a check attribute. The check attribute is used to limit the size of the numbers that can be stored in a numeric field. 1.3.7.3.4 .DAT. Date - type fields are used to store dates in the range 1-1-72 to 31-12-99. A date field requires two bytes. (Bits 4-0 represent the day of the month, 8-5 the month of the year, and 15-11 the number of the year relative to 1972.) 1.3.7.4 Protected Fields - any field may be assigned a protection attribute. Assigning a field a protection attribute prevents the changing of the contents of that field by means of the database editor. 1.3.7.5 Key Fields - DBSMNG allows up to 15 fields to be declared as keys. Such fields are are specially handled by DBSMNG to allow then to be used to quickly locate any records that contain specified data in a given key field. 1.3.8 Special Fields - DBSMNG supports six special fields. These special fields are identified by their logical names which consist of two lower case characters. When included in the template, they must be protected (ie assigned a protect attribute of 1) to ensure that their contents can only be modified by DBSMNGDBSMNG. They are normally placed on the bottom line of page 0. The fields and their usage is explained below. 1.3.8.1 rn - The database file is segmented into a number of slots. These are numbered from 0 to N, where N is the number of entries in the database. Each slot is used to hold the data for one record. When a new record is created, DBSMNG allocates the first free slot to hold that record's data. The 'rn' field always holds the number of the slot that has been assigned to the record. This number can be used to explicitly access a record and for integrity checking. The 'rn' field is of type .NUM. . 1.3.8.2 cn - The 'cn' field is of type .NUM.. Each record is assigned a unique 'cn' number when it is created. DBSMNG allocates a 'cn' number of 1 to the first record created, and each new record that is created is allocated the next integer value. The field is particularly useful in transaction type applications, where transaction records have to be tied back to a database record. Because the 'cn' number is uniquely allocated by DBSMNG, using it to tag transactions will guarantee that the transaction can be used to locate its associated record (as long as the record is still in the database). 1.3.8.3 st - The 'st' field is of type .ASC. and is two bytes in size. It is used by to indicate the status of a record. Currently allocated status codes are NW for records in the process of being created (ie new), AC for currently active records, and DE for records that have been deleted and are awaiting purging. (Note that a deleted record must be purged before its data is erased and its slot in the data file released for re-use.) DBSMNG: Database Management Package 5 1.3.8.4 cd - This .DAT. type field is loaded with the system date at the time the record is created. It indicates when the record was created. 1.3.8.5 ed - This .DAT. type field is loaded with the system date each time the record is edited. It indicates when the data in the record was last modified. 1.3.8.6 dd - This .DAT. type field is loaded with the system date at the time the record is marked for deletion. It indicates when deletion of the record was requested. 1.3.8.7 Using Special Fields - It is strongly suggested that all six special fields be included in the user template, and that the "rn", "cn" and "st" fields be defined as key fields. If none of the special fields are explicitly defined in the template, DBSMNG will automatically include all six special fields into the user's database. This requires an additional twelve bytes of storage per record. If this additional room can not be afforded, only those special fields required for the application may be explicitly defined. Explicitly defining any one of the special fields prevents the automatic inclusion of all six into the user's database. ---NOTE--- The "rn" special field must always be included. The "st" special field must be included if the DBSMNG editor is to be used. 1.4 Visual Display Units DBSMNG interacts with the operator by means of a Visual Display Unit (VDU). The package currently supports the following terminals VT100 VT52 ADM32 VC404 TVI912 ISC8001g Support for additional VDU types can be quickly included by writing a driver module and updating a table. 1.4.1 Screen Coordinates - Any point on the VDU screen can be uniquely identified by specifying an X and Y coordinate. The X coordinate represents the column number. The left most column is numbered 0, and the right most 79 (decimal). The Y coordinate represents the line number. The top most line is numbered 0, and the bottom line (on a 24 line screen) 23 (decimal). DBSMNG: Database Management Package 6 1.4.2 Special Function Keys - The VDU drivers provided on the DBSMNG kit support special function keys. When one of the non-standard keys (eg VT100 PF1, PF2 etc) is pressed, the driver modules return special key codes. Special function keys can be simulated on VDUs that lack additional keys by prefixing a normal key with an [ESCAPE]. 1.4.3 Screen Layout - Because DBSMNG is intended primarily for small applications, the template specifies the screen layout to be used to display data to the operator as well as the definition of the database structure. This has been found to be extremely convenient as only one file needs be modified when changing the record structure. 1.4.3.1 Pages - It is best to display all the information for a record on the screen at once. Unfortunately, this is impossible if the record becomes too large. DBSMNG supports multiple pages which allows the data in a large record to be displayed as a series of pages. DBSMNG pages are numbered 0 to 127 (decimal). A unique page number is associated with each field. If no page number is explicitly associated with a field when it is defined, it is implicitly assigned to page 0. Page 0 fields are always displayed. All other fields are only displayed when the page on which they reside is selected. 1.4.3.2 Key Page - As previously explained, some fields may be declared as keys and used to facilitate data retrieval. When this feature is being used, DBSMNGDBSMNG clears the screen and displays a special page (called the key page). This page contains only those fields declared to be key fields. 1.4.3.3 Labels - Labels may be used to improve the layout of the screen. Labels consist of one or more characters and may be assigned to any desired page. Like fields, page 0 labels are always displayed, while labels on any other page are only displayed when that page is selected. 1.4.3.4 Field Name - The logical name of each field is normally displayed on the screen next to the data the field contains. If the logical name of the field commences with an exclamation mark (!) the field name is not displayed on the screen. 1.4.3.5 Field Delimiters - To improve screen clarity, the screen region allocated to display the information contained in a field is normally enclosed in square brackets. The delimiters may be changed or omitted if desired. 1.5 CUSPs The DBSMNG package provides the user with a number of programs that allow the information stored in a database to be manipulated. These programs are termed CUSPs in this documentation, to distinguish them from other system programs. These CUSPs are generated from the sources contained in the package, and can be configured to support multiple databases or generated to support a specific database. DBSMNG: Database Management Package 7 1.5.1 Customization - The DBSMNG CUSPs use a template that defines the structure of the database. The template may be linked with a CUSP when it is built, in which case it can only be used to access that specific database. Such a CUSP is said to have been CUSTOMIZED for that particular database. If the template was not linked in with the CUSP at generation time, it will ask for the name of the template file each time it is invoked, and then use the specified template. This allows the CUSP to be used to access any database, but requires an extra disk access (to load the TEMPLATE). If only one database is being supported, and/or if slow disks are being used (viz LSI11/03 with floppy disks), it is best to customize the CUSPs as this can greatly improve response time. On the other hand, if multiple databases and/or fast disks are available (viz LSI11/23 with hard disks), it is best not to customize the CUSPs, as this reduces the number of CUSP versions on the system, saving disk space and simplifying maintenance. 1.5.2 Chaining - Each CUSP is designed to perform a specific task, thereby making them simple to use and capable of being run in systems with small amounts of memory. The disadvantage in using single function CUSPs is that a number of CUSPs will have to be invoked sequentially to perform each specific function (eg report production requires that the SELECT, SORTER and REPORT cusps be invoked). To facilitate this (and also to protect the inexperienced user against the complexities of the computer/operating system), the application manager may use MENU to set up CUSP chains. Such chains are activated by the user at menu level by a single key, and cause the required CUSPs to be executed in the correct order. MENU can also be used to pass commands to the CUSPs invoked in the chain sequence, thereby minimizing the information that has to be entered by the user. 1.6 Files DBSMNG uses a number of specially structured files. Some of these files are needed to target the CUSPs for a particular application, while others are produced as the result of a user request (eg reports). This section briefly details the different types of files used by the package and explains their function. 1.6.1 Database - DBSMNG creates and maintains three specially formatted files for each database. These files are created by the DBSMAK cusp, and are used to hold the database records, access keys and parameters respectively. 1.6.1.1 Data File - The data file is used to store the actual record data. Each record is allocated a fixed length partition in the file. Records are stored in ascending order of record number. 1.6.1.2 Key File - As previously explained, the actual data is stored in a data file. As the data file contains only data, and does not have any linkage pointers, locating a record via a key value using only the data file would be a lengthy process as every record would have to be accessed. To speed up record access via keys, DBSMNG maintains a simple key file that allows rapid location of records via key values. This file can be regenerated at any time from the contents of the data file. DBSMNG: Database Management Package 8 1.6.1.3 Parameter File - The parameter file is used to store auxiliary data associated with the database, especially that generated and needed by the database editor. 1.6.2 Template - The template file defines the structure of the database records. Thi source file may be created using any of the standard DEC editors, and consists of a one line declaration per record field. Once assembled, it can be used to generate the customized version of the CUSPs, or linked (by itself) to produce a loadable version of the template (called The TEMPLATE) that can be used with the uncustomized CUSPs. 1.6.3 RPT - The RPT files are sequential ASCII stream files created by CUSPs to hold output (eg the output from the REPORT cusp is a RPT file). The ASCII code 0 (ie NULL) is used as a terminator to mark the end-of-data point in the file. This type of file is generally sent to the printer for printing. 1.6.4 FRM - The FRM files are sequential ASCII stream files that are used as input to the CUSPs. These files are created by the application manager and are used to control the operation of the CUSP. They can be thought of as command files. These files are special in that the physical EOF point, NULL and control-Z can all be used to indicate the logical EOF point. As well, certain characters (the Up-arrow, Escape and Under-line) have special significance when included in FRM files. 1.6.4.1 Uparrow Sequence - The uparrow character (^) causes the next character in the FRM file to be converted into a control-character. (eg ^J is equivalent to a line feed.) 1.6.4.2 Escape Sequence - The Escape character ($) causes the next character in the FRM file to be converted into a special function code (eg $A is equivalent to a special function code of 301 octal). 1.6.4.3 Underline Sequence - The underline character (_) inhibits the conversion or interpretation of the next character in the FRM file. (eg _^ is the only way to prevent the uparrow from being interpreted as part of an uparrow sequence.) 1.6.4.4 ^@ Repeat Block Sequence - the sequence of characters ^@ in a FRM file is used to indicate the start of a repeat block. The repeat block is terminated using the same two characters. The format of a repeat block is [^@][X][....][^@] where [....] represents the sequence of characters to be repeated and [X] is a single character whose ASCII value is used as the repeat count. For example, the sequence ^@0ABC^@ is equivalent to writing the characters ABC forty-eight times (since the ASCII code for "0" is forty-eight). DBSMNG: Database Management Package 9 1.6.5 SFL - SFL files are used by the DBSMNG to transfer data from one CUSP or user program to another. An SFL file consists of a 512 byte header followed by a number of equal length records. Data is typically loaded into such a file by means of the SELECT CUSP, optionally sorted using SORTER and then used as input to REPORT, INSPECT, INSKEY or BACKUP. 1.6.5.1 SFL File Creation - SFL files must be pre-allocated by the application manager. This means that they must be explicitly created (ie the space allocated on the disk and the file entered in the directory) prior to attempting to use them. This is done by means of the RT-11 CREATE command. This requirement is to allow them to be positioned on the disk to achieve optimum performance and to prevent disk fragmentation. 1.6.5.2 Header Block - Block 0 of a SFL file is used as a header block. The first word of the header holds the number of bytes per data record. The second word holds the number of data records in the file. The rest of block 0 is available for general data interchange (eg SELECT passes a copy of its variables to REPORT through this area). 1.6.5.3 Data Blocks - The remaining blocks in a SFL file are used to hold data records. Each data record consists of a two byte tag followed by the record data. DBSMNG CUSPs use the tag to hold the "rn" for the database entry from which the following data was obtained. 1.7 Components The DBSMNG package consists of a number of source and binary files that are used in setting up a database application. The most important of these are described below. 1.7.1 CUSPs - The uncustomized version of the DBSMNG CUSPs are provided in the package. 1.7.2 Libraries - Two library files, DBSLIB.SML and DBSLIB.OBJ are a part of the package. The first file contains definitions for the commonly used macros. All DBSMNG source files require this file when being assembled. The second file is a library of object modules that is used when linking DBSMNG program. 1.7.3 Sources - The package contains a number of source files. These files are used to generate the two libraries, and the programs needed for database manipulation. 1.7.4 Example Application - The package contains a number of files that allow a simple personal database of names and phone numbers to be maintained. These files may be used to experiment with the package and as a starting point for developing other applications. DBSMNG: Database Management Package 10 Chapter 2 Setting up a Database - the Hard Way This chapter outlines the procedure to create a DBSMNG database. A simpler procedure is detailed in the next chapter. It is recommended that the information contained herein only be scanned. Less than 1% of DBSMNG applications cannot be setup using the simpler procedure explained in the next chapter. The reader's attention is especially directed to the section containing the guidelines and hints on setting up the database in the hope that this may prevent the repetition of a number of common mistakes. The setup procedure detailed in the rest of this chapter is summarized below. The first step in setting up a database is to decide on the structure of the records. This requires that the size and attributes of each field be determined, and an estimate of the number of records that are going to be used be made. This is termed the template design stage. Once the structure of the database record has been determined, it is necessary to create a template definition file. This is done using an editor to create a source file that contains a one line definition for each record field. This source file is then assembled with the database macro library (DBSLIB.SML) to produce an object version of the template. A special program, TPLMAC is available to simplify the creation of the template definition file. The use of TPLMAC is explained in the next chapter. The application manager must then decide whether to customize the CUSPs for a particular database, or if a set of general purpose CUSPs are to be used instead. Generation of customized CUSPs involves assembling and then rebuilding each of the CUSPs needed for the application, ensuring that the object version of the template is linked in when the program is being built. Note that customized CUSPs should only be used on small floppy based systems as they require more memory, and are harder to maintain. If customized CUSPs are not required, the TEMPLATE file will have to be generated by linking the object version of the template file. The TEMPLATE will be loaded at run time by the uncustomized CUSPs. The actual database files can then be created and initialized by invoking the DBSMNG cusp - the database is then set up and ready for use. 2.1 Environment Definition The procedure outlined in this chapter for the creation of the database assumes that a particular computer configuration is being used. This section describes the environment. DBSMNG: Database Management Package 11 The DBSMNG distribution kit should be copied onto a volume, and that volume assigned the logical name LB:. If RSTS/E is being used, another logical name may be used to prevent conflict with the RSX library area. The logical name DBS: will have to be assigned to a volume upon which the database files are to be created. This volume must have enough free space to hold the database files. As explained in the introduction, each database is identified by a unique six letter name. It is assumed that the sequence XXXXXX represents the name of the database being created. It is assumed that the RT-11 macro assembler (MACRO.SAV) and the RT-11 linker (LINK.SAV) are available, and can be invoked by the standard MACRO and LINK DCL commands respectively. 2.2 EXAMPLE Database Design Prior to creating a template, it is necessary to determine the database structure and the screen layout. This involves deciding what information is to be stored in each record, and how it is to be grouped into fields. Logical names are then assigned to each field, as well as the attributes needed to ensure that each field is properly handled by the CUSPs. When designing a DBSMNG database, it may help to assume that the database is to be implemented using a card system, as DBSMNG was originally designed to replace card-based data retrieval systems, and many of the concepts are the same. The following example is included to clarify the information that must be gathered before the template can be created. The example application demonstrates setting up a personal database to hold information on business acquaintances and friends. The table below details the information that would be held on each card. SURNAME 20 letters Name - surname INITIALS 2 letters Name - initials TITLE 15 letters Name - for greeting COMPANY 15 letters place of work CLASS 2 letters relationship PHONE(W) 10 digits Phone - work PHONE(H) 10 digits Phone - home STREET 20 bytes Address - street SUBURB 20 letters Address - suburb POSTCODE 4 digits Address - postcode COMMENT 120 bytes Comment - on 2 lines DBSMNG: Database Management Package 12 The diagram below shows the approximate data layout to be used for displaying the data on a VDU screen ._____________________________________________. | | | TITLE [.........] SURNAME [.......] | | COMPANY [.........] INITIALS [..] | | CLASS [..] | | | | PHONE(W) [....] STREET [.......] | | PHONE(H) [....] SUBURB [.......] | | POSTCODE [....] | | | | COMMENT [...............................] | | [...............................] | !_____________________________________________! The field information and layout diagram above can now be reduced into a simple table ready for creation of the template. This concise field data table for the example database is presented below. Field Type --Size--- Dx/Dy Nx/Ny Byte Char TITLE .ALP. 15 15 10/01 00/01 COMPANY .ASC. 15 15 10/02 00/02 PHONE(W) .ASC. 10 10 15/05 00/05 PHONE(H) .ASC. 10 10 15/06 00/06 SURNAME .ALP. 20 20 58/01 41/01 INITIALS .ALP. 2 2 76/02 41/02 CLASS .ASC. 2 2 76/03 41/03 STREET .ASC. 20 20 58/05 41/05 SUBURB .ASC. 20 20 58/06 41/06 POSTCODE .NUM. 2 4 74/07 41/07 COMMENT .ASC. 60 60 18/10 00/10 COMMENT .ASC. 60 60 18/11 00/11 As the database structure and screen layouts have now been finalized, the only matters remaining to complete the database design are to decide on the number of records that the database is to hold (ie its size) and the fields that will be used as keys. A database size of 50 records should be adequate. As for which fields should be declared as keys, it is suggested that the TITLE, COMPANY, SURNAME and CLASS fields would make excellent keys. 2.3 Template Definition File Now that the database design is complete, and the information defining the database structure and screen layouts available, it is time to create a Template Definition File. Such a file contains a one line declaration for each record field, and is assembled (using the DBSMNG macro library) to create the required object version of the template. The template definition file may be created with any of the editors available on the system (TECO and KED are suitable). The editor is invoked, and commanded to create a source file called XXXXXX.MAC, where XXXXXX is the unique six letter name that identifies the database. A special program, TPLMAC is available to simplify the creation of the template definition file. The use of TPLMAC is explained in the next chapter. The template definition file consists of a number of sections, and consists of a DBSMNG: Database Management Package 13 Preamble Field declarations Key declarations 2.3.1 Preamble - This section is used to load in the requisite macros from the DBSMNGDBSMNG macro library file, and initialize the various variables and data structures needed by the macros for the assembly of the template. The preamble specifies the database name, the number of entries in the database, the blocking factor to be used and the logical name of the volume upon which the database files are to reside. It consists of the following lines ._______________________________________________. | | | .MCALL FORM | | FORM XXXXXX,ENTRIES=N,BLOCK=BF,DEVICE=DVC | | .radix 10 | !_______________________________________________! The function and usage of the FORM macro is explained in the next section. 2.3.2 Field Declarations - The Field Declaration section is used to define the names and attributes of all the fields that make up a record. This section consists of a series of one line declarations, each commencing with the word FIELD. Each line results in one field being defined. The order in which the fields are declared is important since it determines where the field data appears in the record, and the order in which the fields are activated when accessed for editing. LABEL and SPARE declarations may also be made in the field declaration section. The FORMEND macro marks the end of the field definitions and the start of the key declaration section. A typical field declaration section has the following format ._____________________________________. | | | FIELD .... ; define first field | | FIELD .... ; define next field | | LABEL .... ; define a label | | SPARE .... ; define a spare field| | .... | | FIELD .... ; define last field | | FORMEND ; mark end of section | !_____________________________________! The function and usage of the FIELD, LABEL, SPARE and FORMEND macros is explained in the next section. 2.3.3 Key Declarations - As previously explained, DBSMNG maintains a special KEY file that it uses to speed up the location of records which contain given data in a specified field. The Key Declaration section is used to define which fields may be used to locate a record, and at the same time define the layout of the Key Page that is displayed by DBSEDT (the database editor) to allow the user to enter KEY data that is to be used for record location. The section consists of up to 15 key field declarations, each consisting of a single line beginning with the word KEY. LABEL declarations may also appear in this section. The end of the section is marked with the KEYEND macro. A typical key declaration section has the following format DBSMNG: Database Management Package 14 ._____________________________________. | | | KEY .... ; define first key | | KEY .... ; define next key | | .... | | KEY .... ; define last key | | KEYEND ; mark end of section | !_____________________________________! The function and usage of the KEY, LABEL and KEYEND macros is explained in the next section. 2.3.3.1 Explicit Key Declarations - A template is said to use explicit key declarations when KEY statements appear between the FORMEND and KEYEND declarations. As explained in the next section, DBSMNG supports implicit key declarations which is much easier to use. Explicit key declarations need only be used if the Key Page needs to be specially laid out (eg to place the most frequently used keys first) or if automatic inclusion of all six special fields (viz rn, cn, st, cd, ed, dd) is to be inhibited. Explicit and implicit key declarations cannot be mixed. 2.3.3.2 Implicit Key Declarations - A template is said to use implicit key declarations when no KEY statements appear between the FORMEND and KEYEND declarations. Implicit key declarations may be used when special laying out of the Key Page is not needed and when it is acceptable to include all six special fields (viz rn, cn, st, cd, ed, dd) in the template. If implicit key declarations are to be used, simply append the following ,KEY=YES to the FIELD declaration for each field that is also to be used as a key. When implicit key declarations are used the keys are laid out on the key page one per line (in the order that they are defined). 2.3.4 Special Fields - as explained in the introduction, DBSMNG provides special support for the "rn", "cn", "st", "cd", "ed" and "dd" fields, and, if possible, these fields should always be included in a template. To facilitate this, DBSMNG will automatically include declarations for these fields in a template. The fields will be positioned on line 23 of page 0, and "rn", "cn" and "st" will be defined as keys. In special cases where all six special fields are not wanted, the automatic generation of all six may be inhibited by simply declaring those that are required. The "rn" field must always be defined. The "st" field must be defined if DBSEDT (the DBSMNG editor) is to be used. DBSMNG: Database Management Package 15 2.4 Template Definition Macros This section details the function and usage of the macros used for the definition of the template. These macros are contained in the DBSLIB.SML macro library which must be used when assembling the template. The following background information is relevant to the proper understanding of the usage of the macros. A normal screen consists of 24 lines each of 80 characters. The top line is numbered 0 and the bottom 23. The left-most character position is numbered 0 and the right-most 79. As DBSEDT (the DBSMNG editor) uses the top four lines of the screen, lines 0 to 3 inclusive of the screen cannot be used. As part of the template definition, the user must specify the number of the page and the coordinates associated with each field. The field coordinates are specified as X/Y (column/line) offsets from the top left hand corner of the page. The top left hand corner of the page (corresponding to the X/Y coordinates 0,0) can be positioned at any point on the screen by means to two variables called BASE.X and BASE.Y. The FORM macro initially sets these variables to 5 and 0 respectively. This means that the pages are aligned on the screen to leave the top 5 lines free and that an X/Y coordinate of 0/0 corresponds to character position 0, line number 5 on the screen. This is shown below +------------+-----------------------------------------+ ! char/x 01234567890123456789012345..7890123456789! ! 1 2 6 7! !line Y +-----------------------------------------+ ! 0 ! ! ! 1 ! ! ! 2 ! ! ! 3 ! ! ! 4 ! ! ! 5 0 ! ! ! 6 1 ! ! ! 7 2 ! ! ! 8 3 ! ! ! 9 4 ! ! ! 10 5 ! ! ! .. .. ! ! ! 23 18 ! ! +------------+-----------------------------------------+ While this may sound a little confusing, it does have the advantage that coordinates are specified relative to the start of the page rather than the start of the screen. This can easily be changed by setting BASE.X and BASE.Y to 0,0 in which case the X/Y coordinates would be relative to the start of the screen. If BASE.X and BASE.Y are set to zero, ensure that lines 0 to 3 inclusive are not used. BASE.X and BASE.Y should be modified at the very start of the field declaration section. DBSMNG: Database Management Package 16 2.4.1 FORM Macro - The FORM macro is used in the preamble section to define the database name, the number of records in the database, the blocking factor to be used and the logical name of the volume upon which the database files are to reside. The invocation format is FORM XXXXXX,ENTRIES=N,BLOCK=BF,DEVICE=DVC Only the XXXXXX argument need be explicitly specified. The ENTRIES, BLOCK and DEVICE arguments may optionally be omitted. 2.4.1.1 XXXXXX argument - represents the 6 letter name of the database. This name must be exactly six characters in length, and will be used to identify the database. 2.4.1.2 ENTRIES argument - represents the number of records in the database. This number determines the size of the database files and the maximum number of records that can be held in the database at any time. 2.4.1.3 BLOCK argument - determines how the records are stored on disk. If a blocking factor is not explicitly specified DBSMNG will automatically allocate the smallest possible. A blocking factor need only be specified if more room is to be reserved for each record than is actually required (viz to allow for the additions of extra fields in the future). The table below details the allowed blocking factors. factor meaning +N each record occupies N blocks +1 each record occupies 1 block 0 each record occupies 1 block -1 each record occupies 1 block -2 two records stored per block -3 four records stored per block -4 eight records stored per block -N 2**(N-1) records stored per block Attempting to use too small a blocking factor (ie not allowing enough disk space to hold each record) will cause an assembly-time error. 2.4.1.4 DEVICE Argument - is the logical name of the volume upon which the database files are to reside. If omitted the database files are automatically placed on the volume with a logical name of DBS: . 2.4.2 FIELD Macro - The FIELD macro is used in the field declaration section. It is used to declare the logical name and attributes of the fields that make up each record. The FIELD macro syntax is FIELD X,Y,TYPE,DATA,XN,NAME,LD,RD,CHECK,PROTECT,PAGE,KEY 2.4.2.1 X argument - is the X coordinate at which the contents of the field are to be displayed. X is specified as an integer in the range 0 to 79. DBSMNG: Database Management Package 17 2.4.2.2 Y argument - is the Y coordinate at which the contents of the field are to be displayed. Y is specified as a integer in the range 0 to 18 (or 0 to 23 if BASE.Y is set to zero). 2.4.2.3 TYPE argument - indicates the type of data that is to be stored in the field. TYPE is specified as one of the following any ASCII character .ASC. any alphabetic character .ALP. an integer (0 to 65535) .NUM. a date .DAT. 2.4.2.4 DATA argument - indicates the number of character positions to be used to display the field contents on the screen. DATA is specified as a series of dots enclosed in angle brackets. Each dot corresponds to one character position on the screen. For .ASC. and .ALP. fields the DATA argument also determines the number of bytes in each record needed to store the field contents (one per character). For .NUM. type fields the DATA argument determines the number of digits to be displayed. For .DAT. type fields the DATA argument is effectively ignored. 2.4.2.5 XN argument - is the X coordinate at which the logical name of the field is to be displayed. XN is specified as an integer in the range 0 to 79. 2.4.2.6 NAME argument - is the logical name of the field. Each field must be allocated a unique name, as this is the only means of access the data in the field. If the name starts with an exclamation character ('!'), then the name will not be displayed on the screen. 2.4.2.7 LD argument - is the delimiter displayed on the left side of the field contents. If no left delimiter is specified a opening square bracket (ie [) is used by default. Any character may be used as the delimiter by simply specifying its ASCII code. 2.4.2.8 RD argument - is the delimiter displayed on the right side of the field contents. If no right delimiter is specified a closing square bracket (ie ]) is used by default. Any character may be used as the delimiter by simply specifying its ASCII code. 2.4.2.9 CHECK argument - is only valid for .NUM. type fields, and indicates the maximum value that can be stored in the field. If this argument is omitted, the maximum value that can be stored is 65535. The CHECK argument is specified as an integer in the range 1 to 65536. 2.4.2.10 PROTECT argument - is used to prevent an operator from modifying the contents of a field with DBSEDT. This argument is specified as a 0 or 1. Specifying a 0 allows the field contents to be modified. Specifying a 1 prevents the field contents from being modified. DBSMNG: Database Management Package 18 2.4.2.11 PAGE argument - is used to indicate the number of the page upon which the field is to be displayed. The page is specified as an integer in the range 0 to 127. The default page number is 0. (Page 0 fields are always displayed. Fields on other pages are only displayed when that page is selected.) 2.4.2.12 KEY argument - is used to indicate whether or not the field is also to be used as a key for locating records, and is specified as either YES or NO. The default is NO. Specifying a key argument of YES causes DBSMNGDBSMNG to automatically generate a KEY declaration for the field, called an implicit key declaration. Note that implicit and explicit key declarations cannot both be used. 2.4.3 FORMEND Macro - This macro is used mark the end of the field definition section. It must appear before any explicit key declarations. The macro syntax is FORMEND 2.4.4 KEY Macro - The KEY macro is used to perform explicit key declarations (ie specify which fields are to be used as keys to locate records). Explicit key definitions require more effort and care than do implicit definitions. It is therefore strongly recommended that explicit key declarations are not used. The KEY macro is used in the key declaration section. The key macro syntax is KEY X,Y,TYPE,DATA,XN,NAME,LD,RD,CHECK,PROTECT Each KEY definition must correspond to a FIELD definition. The TYPE, DATA, NAME and CHECK arguments in the corresponding KEY and FIELD definitions must be identical. 2.4.4.1 X argument - is the X coordinate at which the key is to be displayed on the key page. X is specified as an integer in the range 0 to 79. 2.4.4.2 Y argument - is the Y coordinate at which the key is to be displayed on the key page. Y is specified as a integer in the range 0 to 18 (or 0 to 23 if BASE.Y is set to zero). 2.4.4.3 TYPE argument - indicates the type of the key. TYPE is specified as one of the following any ASCII character .ASC. any alphabetic character .ALP. an integer (0 to 65535) .NUM. a date .DAT. and must be identical to that specified in the corresponding FIELD declaration. DBSMNG: Database Management Package 19 2.4.4.4 DATA argument - indicates the number of character positions to be used to display the key on screen. DATA is specified as a series of dots enclosed in angle brackets. Each dot corresponds to one character position on the screen. The DATA argument must be identical to that specified in the corresponding FIELD declaration. 2.4.4.5 XN argument - is the X coordinate at which the logical name of the key is to be displayed on the key page. XN is specified as an integer in the range 0 to 79. 2.4.4.6 NAME argument - is the logical name of the key, and must be identical to that used in the corresponding FIELD declaration. 2.4.4.7 LD argument - is the left delimiter for the key. If no left delimiter is specified a opening square bracket (ie [) is used by default. Any character may be used as the delimiter by simply specifying its ASCII code. 2.4.4.8 RD argument - is the right delimiter for the key. If no right delimiter is specified a closing square bracket (ie ]) is used by default. Any character may be used as the delimiter by simply specifying its ASCII code. 2.4.4.9 CHECK argument - is only valid for .NUM. type keys, and indicates the maximum value that can be specified. If this argument is omitted the maximum value that can be stored is 65535. The CHECK argument is specified as an integer in the range 1 to 65536. 2.4.5 KEYEND Macro - This macro is used mark the end of the key definition section. It must appear at the end of the template. The macro syntax is KEYEND 2.4.6 LABEL Macro - This macro is used to generate labels and may be using in either the field or key definition sections. Labels are simply a series of characters that are displayed on the screen. Labels are typically used to improve the presentation of data on the screen. The macro syntax is LABEL X,Y,LABEL,PAGE 2.4.6.1 X argument - this is the X coordinate at which the label is to be displayed. This is specified as an integer in the range 0 to 79. 2.4.6.2 Y argument - this is the Y coordinate at which the label is to be displayed. This is specified as an integer in the range 0 to 18. 2.4.6.3 LABEL argument - this is the series of characters that are to be displayed on the screen. These characters should be enclosed in angle brackets. DBSMNG: Database Management Package 20 2.4.6.4 PAGE argument - this identifies the page upon which the label is to reside. Page 0 labels are always displayed, while labels on all other pages are only displayed while the page they reside on is selected. PAGE is normally specified as an argument in the range 0 to 127. 2.4.6.4.1 Key Page Labels - it may sometimes be necessary to include labels on the key page. This is accomplished by specifying a PAGE argument of "KEY" in a label definition. 2.4.7 SPARE Macro - The SPARE macro is used in the field declaration section. This is a special purpose macro that allows room to reserved in a record for the later addition of a field. The SPARE macro syntax is SPARE X,Y,TYPE,DATA,XN,NAME,LD,RD,CHECK,PROTECT,PAGE As can be seen, this is identical to the FIELD macro. Simply by changing the word SPARE to FIELD and recompiling the template the new field will be enabled. Another common use of the SPARE macro is when multiple templates are used to examine the data stored in a database. Under such situations a master template is created with all fields defined. This template can then be used to manipulate the database (with DBSEDT). Subsidiary templates can then be created for viewing the data by defining some fields in the master template as SPARE. This allows the contents of "sensitive" fields to be inaccessible with the subsidiary templates. DBSMNG: Database Management Package 21 2.5 Example Template Definition File The template definition file for the personal database used as an example above is as follows .TITLE PHONES ... definition for PHONES database .IDENT /200582/ .enabl lc .radix 10 ; Preamble ; .MCALL FORM ; load macro FORM PHONES,ENTRIES=50,BLOCK=-2. ; initialize ; ; ; Field Declarations ; FIELD 10.,01. .ASC.,<...............> 00., FIELD 10.,02. .ASC.,<...............> 00.,<COMPANY> FIELD 15.,05. .ASC.,<..........> 00.,<PHONE(W)> FIELD 15.,06. .ASC.,<..........> 00.,<PHONE(H)> FIELD 58.,01. .ALP.,<....................> 41.,<SURNAME> FIELD 76.,02. .ALP.,<..> 41.,<INITIAL> FIELD 76.,03. .ASC.,<..> 41.,<CLASS> FIELD 58.,05. .ASC.,<....................> 41.,<STREET> FIELD 58.,06. .ASC.,<....................> 41.,<SUBURB> FIELD 75.,07. .NUM.,<DDDD> 41.,<POSTCODE> FIELD 18.,10. .ASC.,<--- 60 dots ---> 00.,<COMMENT> FIELD 18.,11. .ASC.,<--- 60 dots ---> 00.,<!COMML2> FIELD 04.,18. .NUM.,<DDDD> 00.,<cn>,PROTECT=1 FIELD 13.,18. .NUM.,<DDDD> 10.,<rn>,PROTECT=1 FIELD 22.,18. .ASC.,<..> 19.,<st>,PROTECT=1 FIELD 44.,18. .DAT.,<XX-XX-XX> 41.,<cd>,PROTECT=1 FIELD 57.,18. .DAT.,<XX-XX-XX> 54.,<ed>,PROTECT=1 FIELD 70.,18. .DAT.,<XX-XX-XX> 67.,<dd>,PROTECT=1 FORMEND ; ; Key Declarations ; KEY 15.,01. .ASC.,<...............> 00.,<TITLE> KEY 15.,02. .ASC.,<...............> 00.,<COMPANY> KEY 10.,03. .ALP.,<....................> 00.,<SURNAME> KEY 28.,04. .ASC.,<..> 00.,<CLASS> KEY 26.,06. .NUM.,<DDDD> 00.,<cn> KEY 26.,07. .NUM.,<DDDD> 00.,<rn> KEY 28.,08. .ASC.,<..> 00.,<st> KEYEND DBSMNG: Database Management Package 22 2.6 Creating the TEMPLATE Once the template definition file is created, it is assembled using the DBSLIB.SML macro library file. The DCL command MACRO XXXXXX+LB:DBSLIB.SML/LIB will result in the creation of the file XXXXXX.OBJ, which is the object version of the template. The application manager must now decide upon whether customized or uncustomized CUSPs will be used. Customization of the CUSPs involves linking the object version of the template with each CUSP as it is rebuilt. It is suggested that uncustomized CUSPs be used, in which case all that is needed is to enter the DCL command LINK XXXXXX which results in the file XXXXXX.SAV, which is the TEMPLATE needed by the uncustomized CUSPs. 2.7 Initializing the Database Now that a TEMPLATE is available, the three database files can be created and initialized. This is done using the DBSMAK CUSP. The logical name DBS: will have to be assigned to the volume that is to hold the database files before the program is invoked. If the uncustomized version of the cusps is being used, DBSMAK will request that the name of the TEMPLATE be entered. The following DCL command can be used to invoke DBSMAK RUN LB:DBSMAK The database creation procedure is now complete. The following files should now be available DK:XXXXXX.SAV .... TEMPLATE DBS:XXXXXX.DAT .... data file DBS:XXXXXX.KEY .... key file DBS:XXXXXX.PAR .... parameter file and the application implementation can commence using the above files and the DBSMNGDBSMNG CUSPs as a basis. DBSMNG: Database Management Package 23 2.8 Hints and Suggestions This section contains a number of hints and suggestion that may be of aid when designing a template for a specific application. Always attempt to divide a record into as many fields as possible, as this makes manipulation of the data easier. For example, names of people should always be stored as [SURNAME] and [INITIALS], never as just [NAME], as this makes sorting of records by name difficult (ie A. SMITH would come before B. ADAMS in the second case, which is wrong). The table below gives an indication of how to break up commonly used fields into smaller, more suitable ones [NAME] := [TITLE][INITIALS][SURNAME][NICKNAME] [ADDRESS] := [NUMBER][STREET][SUBURB][CITY][POSTCODE] [PHONE] := [STDCODE][NUMBER][EXTENSION] The blocking factor specified in the FORM declaration determines the amount of disk space reserved per record. Disk space can be allocated in either multiples of a disk block (ie 512 byte blocks), or a number of records (2,4,etc) may be stored in one block. Always chose the smallest possible blocking factor to minimize the amount of disk space wasted. In some cases reducing the size of a field by one or two bytes may be advisable to allow the blocking factor to be reduced in a border-line case. The six DBSMNG supported fields should be included in each record, and positioned on the bottom line of screen page 0. Fields that are to hold numeric data should be declared as .NUM. types, unless the values to be stored are greater than 65000, in which case .ASC. type fields will have to be used to hold the data. If numeric data is being stored in a .ASC. field, always ensure that data is stored right justified, or sorting will not operate correctly on the records. The MACRO.SAV assembler interprets all numbers as octal (ie to base 8), unless they contain a decimal point after the last digit. The default radix may be changed to decimal by placing a .RADIX 10 instruction as the first line of the template file. Also note, that unless a .ENABL LC directive is placed at the start of the file (as in the example template), all input is converted to upper case, which means that the special DBSMNG supported fields will not be included, as their names must be in lower case. When first creating the template, use a large blocking factor and a small number of entries (ie one or two). This speeds up the procedure if a number of attempts have to be made to lay out the screen properly. Once the screen layout is satisfactory, the DBSSTS CUSP may be run to determine the number of bytes in each record, which in turn, can be used to work out the best blocking factor. The template definition file can then be edited to insert the correct blocking factor and number of entries. DBSMNG: Database Management Package 24 Chapter 3 Setting up a Database - The Easy Way This chapter outlines the preferred procedure for setting up a DBSMNG database. It is applicable to 99% of DBSMNG applications and is extremely easy, allowing a database to be set up in a matter of minutes. The procedure consists of the following steps 1 Design a "CARD" to hold the information that is to be stored in the database. The card is designed using a standard editor. A screen editor (eg KED) is best. An estimate of the maximum number of CARDS to be held in the database at any time will have to be made. 2 Create a TEMPLATE for DBSMNG. The TEMPLATE defines the structure of the CARD designed in step 1, and will be used by DBSMNG to determine how the data is to be stored on a CARD. 3 Initialize the database. This results in the allocation of space on the disk for storage of the database information. This chapter is intended for the person installing a DBSMNG application, and assumes some familiarity with RT-11 commands and utilities, including use of one of the system editors (ie KED, EDIT or TECO) and assembling/linking a MACRO program. It is not necessary to be able to write (or understand) a MACRO program, but it is essential to be familiar with the assembly and linking process. (The requisite procedures are adequately explained in the "Introduction to RT-11" manual.) 3.1 Environment Definition The procedure outlined in this chapter for the creation of the database assumes that a particular computer configuration is being used. This section describes the environment. The sequence of characters XXXXXX is used in the rest of this chapter to represent the unique six letter name of the database being created. The logical name LB: is assigned to the volume that holds the DBSMNG distribution kit. The logical name DBS: is assigned to the volume that is to hold the database files. This volume must have enough free space to hold the database files. The RT-11 macro assembler (MACRO.SAV) and linker (LINK.SAV) may be invoked by the DCL commands MACRO and LINK respectively. DBSMNG: Database Management Package 25 3.2 Designing a CARD A DBSMNG database can be thought of as a number of cards "filed" on disk. The first step in setting up a database is to design a card to hold the data. A card consists of fields and labels which are displayed on the screen by the DBSEDT program - the database editor. The data is entered and displayed in the fields (blank areas), while the labels separate the fields and make the card more readable. The card can be designed using any available editor, although a screen editor (such as KED) is most suitable. Create a text file with the name XXXXXX.TPL and in this file lay out a 24-line image of the card as it is to appear on the DBSEDT screen. Spaces and tabs may be used to position the fields and labels in this file. The top 4 lines and the bottom line of the 24-line card image must be left blank for use by DBSMNG. 3.2.1 Specifying Labels - Labels are strings of characters displayed on the DBSEDT screen to make the card more readable. To specify a label and its position on the card, place the required characters in the card image as they are to be displayed on the screen and terminate with a colon (:). The characters [ < and : must not be used in the label. 3.2.2 Specifying Fields - Fields are used to hold and display data stored on a card. Each field has an optional name followed by a data area. The data stored in a field is displayed in the field's data area. The position of the data area is specified by enclosing the desired region of the card image in square brackets [ ]. A field may be given a name by preceding the data area definition with a string of characters. The characters [ < and : must not be used in the name. If the name begins with an exclamation mark (!), it will not be displayed on the DBSEDT screen but will be suppressed. If no name is supplied, an internal name (see below) will be generated. Such names will not be displayed by DBSEDT. 3.2.2.1 Specifying the Field Type - A field may be defined to store ASCII text, alphabetic text, a number or a date. The field type is specified by the characters used between the square brackets in the field data area definition. The following characters may be used Character Field Type . ASCII A ASCII D date L letter (= alphabetic) N number In the following example, FIELD1 holds 4 ASCII characters, FIELD2 is a 2-digit number and FIELD3 is a date FIELD1 [....] FIELD2 [NN] FIELD3 [DD-DD-DD] DBSMNG: Database Management Package 26 3.2.2.2 Specifying a Protected Field - A field may be defined to be Protected for the purpose of preventing an operator from modifying the contents of the field with DBSEDT. To protect a field, use lower case characters instead of upper case to specify the field type in the card image. The fields in the previous example may be protected as follows FIELD1 [aaaa] FIELD2 [nn] FIELD3 [dd-dd-dd] 3.2.2.3 Specifying a Key Field - A field may be defined to be a Key for the purpose of speeding up the search process when it is desired to locate records containing particular data in the field. To specify a field as a key, use angle brackets < > instead of square brackets [ ] to enclose the field data area in the card image. 3.2.2.4 Special Fields - DBSMNG will automatically include 6 special fields on the card for its own use. These fields are named as follows rn cn st cd ed dd and these names should not be used in the card design. The fields are displayed on the bottom line of the DBSEDT screen, which is the reason for this line being left blank in the card design. 3.2.3 Multi-page Database Design - For cases where there is too much information to fit on a single card, DBSMNG allows cards to consist of a number of pages. The page designs should be entered in the card image file separated by form-feed characters (CONTROL-L). Any fields and labels on the first page in a multi-page design will be displayed on all pages by DBSEDT. 3.3 Creating the TEMPLATE Once the card design has been completed, the database TEMPLATE definition can be generated using the TPLMAC program. Refer to the chapter on the TPLMAC program for additional data on the operation and use of the program. The following DCL command is used to envoke TPLMAC RUN LB:TPLMAC The user must supply the name of the database and the maximum number of cards to be allowed. The file XXXXXX.MAC will be created and will contain ASCII text defining the fields and labels in the database. This file can be edited for special purposes (such as including a ":" character in a label) but a thorough understanding of the contents of this file should first be obtained. In most cases, the file need not be touched. The template definition file is then assembled using the DBSLIB.SML macro library file. The DCL command MACRO XXXXXX+LB:DBSLIB.SML/LIB DBSMNG: Database Management Package 27 will result in the creation of the file XXXXXX.OBJ, which is the object version of the template. Next, enter the DCL command LINK XXXXXX to produce the file XXXXXX.SAV, which is the database TEMPLATE. 3.4 Initializing the Database Now that a TEMPLATE is available, the three database files can be created and initialized using the DBSMAK program. The logical name DBS: will have to be assigned to the volume that is to hold the database files before the program is invoked. The following DCL command will invoke DBSMAK RUN LB:DBSMAK and the user will have to supply the database name. The database creation procedure is now complete. The following files should now be available DK:XXXXXX.SAV .... TEMPLATE DBS:XXXXXX.DAT .... data file DBS:XXXXXX.KEY .... key file DBS:XXXXXX.PAR .... parameter file and the database is ready for use. DBSMNG: Database Management Package 28 3.5 Example TPL Card The following example card design demonstrates how the above rules are used. The example is of a personal database to hold information on business acquaintances and friends. It has the following features The TITLE, COMPANY and SURNAME fields are defined to be keys. All fields are ASCII except for the POSTCODE which is numeric. The name of the COMML2 field will not be displayed. ----------------- Delimiter - NOT part of design ------------------------- DBSMNG Example: Personal PHONES Database: TITLE <...............> SURNAME <........................> COMPANY <...............> INITIAL [..] CLASS [..] PHONE(W) [..........] STREET [........................] PHONE(H) [..........] SUBURB [........................] POSTCODE [NNNN] COMMENT [..............................................................] !COMML2 [..............................................................] ----------------- Delimiter - NOT part of design ------------------------- DBSMNG: Database Management Package 29 Chapter 4 MENU: CUSP Chain Manager This chapter describes the operation and use of the DBSMNG MENU CUSP which allows an application manager to set up an application oriented interface. Via MENU a user can invoke one or more CUSPs needed to perform a complex operation by pressing a single key. An example menu is included at the end of the chapter. 4.1 Concepts This section attempts to clarify and define the concepts and terminology used in this chapter. 4.1.1 Menu - A menu consists of a display listing functions that can be performed and the key that must be pressed to initiate each function. 4.1.2 Function Keys - The term Function Key is given to the key on the terminal keyboard that must be pressed to initiate a specific menu function. The MENU CUSP allows any of the digit keys (at the top of the keyboard) to be used as a Function Key. 4.1.3 CUSP Chains - Each DBSMNG CUSP is designed to perform a single operation. Complex functions require that a number of CUSPs be invoked serially, with the output of one used as the input of the next. Such a linking of CUSPs is termed a CUSP Chain. A typical chain to produce a report involves the CUSPs SELECT, SORTER, REPORT and MENU. SELECT is used to select the records to be included in the report, SORTER to sort them in the desired order, REPORT to produce the report and MENU to insure that the other CUSPs are invoked in the correct order and supplied with the information each needs to do its work. 4.1.4 Core Common Commands - Any DBSMNG CUSP may be directly invoked, using the following monitor command RUN LB:??????<RETURN> where ?????? is the name of the CUSP. When invoked in this manner, the CUSP will prompt the user for a number of answers. When invoked as part of a CUSP chain, the answer to the CUSP's questions may be passed from MENU, thereby eliminating the need for the user to type in any responses. Answers supplied to the CUSP from MENU (rather that by the user) are called Core Common Commands (CCCs). DBSMNG: Database Management Package 30 4.1.5 User Variable - MENU supports one user variable that can be used to hold a ascii string. Operators are provided to allow the contents of the variable to be input from the console and then used within a menu. 4.1.5.1 Passing User Variable between Menus - the contents of the user variable may be "passed" to another menu by placing the contents of the variable in core common directly after the name of the new menu. The following code included within an actioning block $@LB:MENU$$ $IREPORT.MNU$$ $I$~($$ would pass the contents of the user variable onto the REPORT menu. 4.2 Invoking MENU MENU may be invoked directly, from a command file, from another MENU, or as the last CUSP in a chain. Typical RSTS/E and TSX-plus applications invoke MENU from the "login" command file. The following command RUN LB:MENU will start up the program, and cause it to prompt the user for the following information. Note that the required responses may be passed as CCCs if MENU is invoked as part of a CUSP chain. 4.2.1 Format File - MENU is targeted for a specific application by means of a Format File. This is a FRM type file, that contains the data to be displayed on the screen, and the CUSP chain information needed by MENU to initiate each user selectable function. The name of the Format File should be entered in response to this question. It is recommended that the MENU FRM file for database XXXXXX be called XXXXXX.MNU. 4.2.2 CRT type - DBSMNG CUSPs are designed to interface with the user via a Visual Display Unit. A number of different terminal types are supported, including the VT100, VT52, TVI etc. Pressing the [RETURN] key in response to this prompt results in MENU listing the types of terminals supported. The type of terminal being used should be entered in response to this prompt. 4.3 Menu Format File Menu Format Files (MFFs) are used to target MENU for specific applications. Such files are created by the application manager using a standard editor (viz KED, EDIT, TECO) and determines the screen format displayed to the user and the action to be performed when a Function Key is pressed. Menu Format Files are FRM type files. The underline (_), dollar ($) and uparrow (^) characters have special significance in a FRM file. DBSMNG: Database Management Package 31 MFFs consist of a display definition section and a function definition section. The display definition section determines what is to be displayed on the screen to inform the user what operations are available and the function definition section defines the actions to be performed when a Function Key is pressed. 4.3.1 Display Definition Section - The display definition section determines what is to be displayed on the screen to inform the user of the operations are available and typically lists the function initiated by each Function Key. The display definition section extends from the first character in the file upto the first $* delimiter. When initiated MENU erases the screen and outputs characters contained in the display definition section starting at the third line of the screen. Uparrow (^) sequences should be used to generate any control codes (other than carriage-return and line-feed) needed to control the screen (ie VT100 bold, double width etc). 4.3.2 Function Definition Section - The function definition section determines the action that is to be taken when a particular Function Key is pressed. The section contains a action definition block for each Function Key that is to be supported. MENU allows action definition blocks to be defined for the digit keys 0 to 9 and as well as the slash (/) key. The function definition section extends from the first $* (that marks the end of the display definition section) upto the second $* delimiter that marks the end of the MFF. 4.3.2.1 Action Definition Blocks - An action definition block must be included in the function definition section for each Function Key that is to be supported. Such a block consists of a $X sequence, where X represents the Function Key that initiates the operations specified in the block. The block is terminated by a $! delimiter. 4.3.2.2 Comments - To facilitate documentation of MFFs, all characters other than those in the display definition section or that are part of a command sequence (see below) are ignored by MENU. MENU. This allows the MFF to be documented. 4.3.3 Conditional Blocks - may be included anywhere in the MFF (ie in either the display or function definition sections or both). A conditional block starts with a $= or $# operator and includes all characters upto and including the $$ operator that marks the end of the block. All characters within the block are ignored if the test condition is not satisfied. 4.4 MENU Operation When first invoked MENU prompts for the name of the MFF and the type of terminal being used. Once this information is entered MENU opens the MFF and executes the following steps. DBSMNG: Database Management Package 32 4.4.1 Screen Initialization - MENU clears the screen and outputs the contents of the display definition section of the MFF which starts with at the first character in the file and extends upto the first $* delimiter. The $* delimiter that terminates the section is not output. 4.4.2 Slash Function Execution - MENU then searches the function definition section for a action definition block for the slash (/) key. If a block has been defined for the slash key MENU executes the command sequences contained within the block. A slash action block is normally used within a conditional block when an application requires a particular type of terminal to log the user off if the incorrect type of terminal is being used. 4.4.3 Input Wait - MENU then waits for the user to press a key. All keys other than [RETURN], [0], [1], [2], [3], [4], [5], [6], [7], [8] or [9] are ignored. If the [RETURN] key is pressed MENU repeats the two previous steps. 4.4.4 Digit Key Function Execution - Once a digit key is pressed MENU searches the function definition section for a action definition block for the key. If a block has been defined for the key MENU executes the command sequences contained within the block. MENU then loops back to the Input Wait step above. 4.5 Operators MENU commands consist of a two letter operation identifier optionally followed by arguments and a terminating delimiter. The first letter of a operation identifier is the dollar ($) character. This section defines the operators supported by MENU. 4.5.1 $* Section Delimiter - The $* operator is used to delimit the display and function definition sections of the Menu Format File. The first $* marks the end of the display definition section and the second the end of the function definition section. 4.5.2 $=XXX If Terminal of Type XXX - The $= operator is used to mark the start of a conditional block. The block is terminated by a $% operator. A three letter terminal type identifier code must directly follow the $= operator. The characters between the $=XXX and the $% are included (ie not ignored) if the terminal being used is of type XXX. The valid terminal identifier codes are V10 VT100 V52 VT52 ISC Intercolor 8001g ADM ADM-3A TVI TVI916 VC4 VC404 DBSMNG: Database Management Package 33 4.5.3 $#XXX If Terminal NOT of Type XXX The $# operator is used to mark the start of a conditional block. The block is terminated by a $% operator. A three letter terminal type identifier code must directly follow the $# operator. The characters between the $#XXX and the $% are included (ie not ignored) if the terminal being used is not of type XXX. 4.5.4 $% Conditional Block Terminator - The $% operator is used to terminate a conditional block. Such a block is started with a $= or $# operator. 4.5.5 $/ Auto-initiated Function - The $/ operator is used to commence a action definition block. The commands contained within the block will be executed by MENU prior to accepting any user input. The block is terminated by a $! operator. 4.5.6 $0 Action Block for Function Key 0 - The $0 operator marks the start of the action definition block that is to be executed when the [0] key is pressed. A $! operator terminates the block. 4.5.7 $1 Action Block for Function Key 1 - The $1 operator marks the start of the action definition block that is to be executed when the [1] key is pressed. A $! operator terminates the block. 4.5.8 $2 Action Block for Function Key 2 - The $2 operator marks the start of the action definition block that is to be executed when the [2] key is pressed. A $! operator terminates the block. 4.5.9 $3 Action Block for Function Key 3 - The $3 operator marks the start of the action definition block that is to be executed when the [3] key is pressed. A $! operator terminates the block. 4.5.10 $4 Action Block for Function Key 4 - The $4 operator marks the start of the action definition block that is to be executed when the [4] key is pressed. A $! operator terminates the block. 4.5.11 $5 Action Block for Function Key 5 - The $5 operator marks the start of the action definition block that is to be executed when the [5] key is pressed. A $! operator terminates the block. 4.5.12 $6 Action Block for Function Key 6 - The $6 operator marks the start of the action definition block that is to be executed when the [6] key is pressed. A $! operator terminates the block. DBSMNG: Database Management Package 34 4.5.13 $7 Action Block for Function Key 7 - The $7 operator marks the start of the action definition block that is to be executed when the [7] key is pressed. A $! operator terminates the block. 4.5.14 $8 Action Block for Function Key 8 - The $8 operator marks the start of the action definition block that is to be executed when the [8] key is pressed. A $! operator terminates the block. 4.5.15 $9 Action Block for Function Key 9 - The $9 operator marks the start of the action definition block that is to be executed when the [9] key is pressed. A $! operator terminates the block. 4.5.16 $! Action Block Terminator - The $! operator marks the end of a action definition block, the start of which is marked by a $/, $0, $1, $2, $3, $4, $5, $6, $7, $8 or $9 key. 4.5.17 $) Output to Terminal - The $) operator can be used within a action definition block to output all characters upto but excluding the $$ terminator to the user's terminal. This operator causes the second line of the screen to be erased and the cursor positioned at the start of the line. 4.5.18 $( Input from Terminal - The $( operator can be used within a action definition block to input a line of text from the terminal. The input text is stored in the user variable. 4.5.19 $D Define User Variable - The $D operator can be used within a action definition block to store a text string in the user variable. The command format is $D...$$ where "..." represents the text that is to be stored in the user variable and $$ is the string terminator. 4.5.20 $R Restart - The $R operator can be used within a action definition block to cause the MENU program to be restarted. An action sequence currently in progress is aborted and the screen is refreshed. 4.5.21 $@ Initiate CUSP Chain - The $@ operator can be used within a action definition block to initiate a CUSP chain. The command format is $@{DDN:}NAME{.EXT}$$ and indicates that control is to be passed to the program DDN:NAME.EXT specified between the $@ and the $$ delimiter. The default device and extension for the file is DK: and SAV respectively. DBSMNG: Database Management Package 35 4.5.21.1 CUSP Chain Initiation - MENU does not actually pass control to the program whose name is specified in the $@ command until the action block terminator (ie $!) is encountered. This is done to allow additional CUSPs to be included in the chain and to allow Core Common Commands to be loaded. 4.5.22 $Q Quiet CUSP Chain - The $Q operator can be used within a action definition block to initiate a CUSP chain. The command format is $Q{DDN:}NAME{.EXT}$$ and indicates that control is to be passed to the program DDN:NAME.EXT specified between the $Q and the $$ delimiter. The default device and extension for the file is DK: and SAV respectively. The difference between the $@ and $Q operator is that the $Q operator causes DBSMNG cusps to suppress their startup dialog. This reduces terminal output to a bare minimum. (Applications should be developed and debugged using the $@ operator. Once debugged the $Q operator can be substituted for the $@ operator.) 4.5.23 $C Include CUSP in Chain - The $C operator is used within a action definition block after a CUSP Chain has been initiated via the $@ operator to include an additional CUSP in the chain. The command format is $C{DDN:}NAME{.EXT}$$ and indicates that the program DDN:NAME.EXT specified between the $@ and the $$ delimiter is to be included in the CUSP chain. The default device and extension for the file is DK: and SAV respectively. ---NOTE--- The $@ or $Q operators should be used in place of the $C operator to initiate all cusps in a cusp chain. The $C operator is supported for compatiability with older applications. 4.5.24 $I Insert Core Common Command - To minimize the burden on the user answers to the questions asked by CUSPs (ie TEMPLATE name) in a chain are normally passed though Core Common. The $I operator is used to load an answer into Core Common. The Command format is $I{string}$$ and results in the loading of {string} into Core Common. Note that the Core Common answers for questions asked by a CUSP must be specified directly after the $@ or $Q command that includes them in the chain. DBSMNG: Database Management Package 36 4.5.25 $~ Substitute System Parameters - The $~ operator is used to insert system information in a menu. The information returned by the $~ operator is relevant under the TSX-Plus operating system. 4.5.25.1 $~J Operator: Job Number - the $~J sequence is replaced by (a two digit) TSX-Plus job number. 4.5.25.2 $~P Operator: Primary Line Number - the $~P sequence is replaced by the two digit TSX-Plus primary line number for the job. 4.5.25.3 $~N Operator: User Name - the $~N sequence is replaced by the twelve character TSX-Plus user name. 4.5.25.4 $~V Operator: Flag Virtual Line - the $~V sequence is replaced by the character V if the user is currently running on a TSX-Plus virtual line. If the user is not on a virtual line the $~V sequence is replaced by a space. 4.5.25.5 $~P Operator: Project Number - the $~P sequence is replaced by the user's three digit TSX-Plus project number. 4.5.25.6 $~Q Operator: Programmer Number - the $~Q sequence is replaced by the user's three digit TSX-Plus programmer number. 4.5.25.7 $~( Operator: User Variable - the $~( sequence is replaced by the text string stored in the user variable. (Text is loaded into the user variable by either the $( or $D operators.) 4.5.25.8 $~A Operator: Device Assignment - the sequence $~Addd: is replaced by the name of the device to which the logical name 'ddd' has been assigned. (The logical name may consists of upto two letters optionally followed by a digit and is terminated by the colon character.) 4.6 Hints and Suggestions This section contains a number of hints and suggestion that have been found useful in the past when setting up menus. 4.6.1 Core Common Overflow - There is a limit to how much data can be passed in Core Common. Under RT-11 and TSX-Plus Core Common can hold approximately 160 bytes of data. Each character in a Core Common Command and in the name of a file included in the chain takes up a byte. In the event of a Core Common overflow try and reduce the names of files in the chain by leaving off the extension etc wherever possible and by reducing the size of Core Common Commands passed via the $I operator. DBSMNG: Database Management Package 37 4.6.2 Very Long CUSP Chains - Very long CUSP chains occur if a single Function Key is used to initiate the production of a number of reports as SELECT, SORT and REPORT must be called once for each report. In such cases Core Common is bound to overflow. This problem is overcome by creating a dummy menu for each report that is to be produced. The display definition section is left empty and the function definition section contains a slash (/) auto-initiated function action block. This block invokes SELECT, SORTER and REPORT to produce a report and then passes control to another dummy menu that produces the next report. 4.6.3 Including VT100 Escape Sequences - Menus tailored for VT100 terminals can be made very impressive by using the VT100s special attributes. Two points should be kept in mind if this is done. The first is that the VT100 escape sequences should be enclosed within a conditional block of the form $=V10{escape sequence}$% This prevents the escape sequence from being sent to a non-VT100 terminal. Secondly it is dangerous to include the <ESCAPE> character in the file the escape character may cause troubles if the file is printed out (eg may change horizontal pitch on a LA120). The suggested way of including an <ESCAPE> in the display definition section of the file is by using the equivalent ^[ construct (ie equivalent to control-[ which is an escape). DBSMNG: Database Management Package 38 4.7 Example Menu The following is a sample Format File that would be used as the menu for a simple database application. It is assumed that the database is called FAULTS, resides along with its TEMPLATE on DBS:. ----------------------------------------------------------------------------- Fault Analysis System Menu 2 Edit Fault Cards 4 Display fault report on terminal 7 Analysis fault data The desired function may be invoked by typing the corresponding digit. $* ; mark end of header $2 ; mark start of sequence $@LB:DBSEDT$$ ; invoke editor $IDBS:FAULTS.SAV$$ ; pass name of TEMPLATE $@LB:MENU$$ ; chain back to ourself $IFAULTS.MNU$$ ; remind ourselves about menu $! ; activate sequence $4 $@LB:SELECT$$ ; want to select records $IDBS:FAULTS.SAV$$ ; pass name of TEMPLATE $IFAULTS.SE4$$ ; tell selection criteria $IFAULTS.SRT$$ ; output selected data to this file $I0$$ ; allowed select to chose size $@LB:SORTER$$ ; invoke sorter $IFAULTS.SRT$$ ; tell file want sorted $@LB:REPORT$$ ; want to produce report $IDBS:FAULTS.SAV$$ ; pass name of TEMPLATE $IFAULTS.RE4$$ ; format of report $IFAULTS.SRT$$ ; sort data file to use $ITT:$$ ; send output to TTY $@LB:MENU$$ ; chain back to ourself $IFAULTS.MNU$$ ; remind ourselves about menu $! ; activate sequence $7 $)Sorry, not yet debugged^G!$$ ; tell not ready $! $*--------------------------------------------------------------------------- DBSMNG: Database Management Package 39 Chapter 5 DBSEDT: Database Editor DBSEDT is a screen oriented editor that allows database records to be created, edited, inspected and deleted. The editor is designed for simplicity of use, and is the main means of entering and maintaining records for most applications. This section details the operation and use of the editor. It is suggested that, if available, the reader use the example database application distributed as part of the DBSMNG package to experiment with the editor and try out each function as it is explained. 5.1 Terminal Being a screen oriented editor DBSEDT requires a visual display type terminal to communicate with the operator. While the following documentation is based on the assumption that a DEC VT100 terminal is being used, DBSEDT can be used with a variety of terminals. Contained in the appendix is a list of supported terminals and a description of how they may be used in place of a VT100. 5.2 Invoking DBSEDT Before DBSEDT can be used, a TEMPLATE must be created and the database files initialized. This is done by the application manager, following the procedure outlined earlier. Before invoking DBSEDT the logical name DBS: must be assigned to the volume upon which the database files reside. Assuming that the uncustomized version of the CUSPs are being used and that these reside on the volume with logical name LB:, the following command entered at keyboard monitor level will start up the editor RUN LB:DBSEDT<CR> Upon activation the editor requests that the user identify the type of terminal being used and enter the name of the file that contains the database TEMPLATE. In response to each of these questions simply enter the appropriate answer terminated by a carriage-return. The editor will then clear the screen and display the command mode prompt. DBSMNG: Database Management Package 40 5.2.1 Using a start-up file - In simple applications the editor may be adequate for performing all the desired operations, in which case a command file can be used to declare the logical assignments, invoke the editor and answer its questions. Under RSTS/E and TSX-plus it is a simple matter to allocate a PPN to an application and order the operating system to execute a start-up command file to automatically invoke DBSEDT when a user logs in. 5.2.2 CUSP chaining - DBSEDT can be invoked as part of a CUSP chain from the DBSMNG MENU CUSP. When invoked from MENU the terminal type and TEMPLATE file name may be optionally passed to DBSEDT through Core Common. In complex applications a number of CUSPs may have to be used to perform all of the desired functions and in such cases it is suggested that the application manager use MENU to set up CUSP chains so that user need only press one key to invoke a desired CUSP (or initiate complex operations involving a number of CUSPs) as this protects the novice user from the idiosyncrasies of the operating system. 5.3 Screen Layout Any point of the terminal screen may be identified by specifying a line and character number. The line number (sometimes called the Y coordinate) can range from 0 to Yb, with 0 representing the top line and Yb the bottom line. The character number (sometimes called the X coordinate) can range from 0 to Xr, with 0 representing the leftmost character position and Xr the rightmost position. DBSEDT sections the screen into two areas termed the INTERACTION and FORM areas. 5.3.1 Interaction Area - Lines 0-3 of the screen are used by DBSEDT for interacting with the user. Lines 0 and 1 are used to display messages that identify the operation being performed and commands available. This provides the novice user with a continuous 'help text' that ensures rapid learning. Line 2 is used for echoing command input and line 3 for displaying error messages. 5.3.2 Form Area - Lines 4-Yb of the screen make up the form area and are used to display the names of the fields that make up a record and the contents of each field. The actual layout used to display the fields is determined by the application manager while creating the TEMPLATE. Imagine that the form area is used to display a card. As we all know a card consists of labels and blanks areas into which data can be written. This is exactly how DBSEDT uses this area. DBSEDT writes the (card) labels and headings in the form area and the user can then fill in the spaces with the appropriate data or order DBSEDT to display the data for a specific database record in the blank areas. DBSMNG: Database Management Package 41 5.4 Input Modes DBSMNG has two modes for accepting user input. The mode used at any time depends upon whether the input is to be interpreted as a command or used as data. At times DBSEDT requires that the user explicitly enter a command and any input will then be interpreted as a command to DBSEDT. At other times DBSEDT may require the user to enter information to be stored within a record or that may be needed to identify a unique record and any input will then be interpreted as data and processed accordingly. 5.4.1 Command Input Mode - DBSEDT indicates that it is in command input mode by displaying the '>' symbol at the start of line 2 and positioning the cursor at character position 1 of the line. The user must enter a command. The <RETURN> key must be pressed when the command is complete to order DBSEDT to execute it. A DBSEDT command consists of a command 'verb' optionally followed by an argument. The argument, if included, is separated from the verb by a <SPACE>. A list of valid command 'verbs' is displayed on line 1 of the screen. If a command verb requires an argument and one is not entered as part of the command DBSEDT will issue an explicit prompt for it. The <DELETE> and <Control-U> keys may be used to edit a command prior to the pressing of the <RETURN> key. The <DELETE> key causes the last letter typed to be deleted and can be used to delete all input if desired. The <Control-U> key causes all input to be deleted. Entering an illegal command causes DBSEDT to display an error message on line 3. 5.4.2 Data Input Mode - DBSEDT indicates that it is in data entry mode by clearing line 2 and displaying the message <CR> --> exit entry mode <TAB> --> next field on line 1 (instead of a list of command verbs). The cursor is positioned on a field data area (ie on a part of the screen that is used to display the data contained within a given field) in the form section of the screen. Typing a character in this mode causes it to appear on the screen in the position occupied by the cursor, after which the cursor is moved one position to the right. As more characters are typed the cursor continues to move to the right until the end of the field area is reached. Typing a character when the cursor is over the last character position of the field area results in the appearance of the character at that position and the movement of the cursor to the start of the next field (see Auto-tab documentation). Entry mode can be exited at any time by typing a <RETURN> which will place the editor into command entry mode in readiness to accept a command. Certain characters are available in data entry mode to allow the contents of a field to be edited and for the cursor to be moved from field to field. It is important to note that it is only possible to position the cursor in an unprotected field data area. It is not possible to position the cursor on any other part of the screen. All keys, other than those listed below, cause the character under the cursor to be replaced and the cursor moved right one position (if not already at rightmost field position). If it is illegal to store the character entered in the field due to a "type" mismatch (eg entering a letter in a numeric field) the character is ignored and the terminal bell sounded as a warning. DBSMNG: Database Management Package 42 5.4.2.1 Cursor Movement Keys - The four cursor movement keys situated on the top right of the main keyboard section are used to move the cursor about within the confines of the field data area in which it currently resides. 5.4.2.1.1 UP - This key results in the cursor being moved to the leftmost position of the field data area. 5.4.2.1.2 DOWN - This key results in the cursor being moved to the rightmost position of the field data area. 5.4.2.1.3 LEFT - This key results in the cursor being moved one place to the left, except when the cursor is already at the leftmost point of the field data area, in which case the key is ignored. 5.4.2.1.4 RIGHT - This key results in the cursor being moved one place to the right, except when the cursor is already at the rightmost point of the field data area, in which case the key is ignored. 5.4.2.2 Special Function Keys - The four special function keys at the top of the auxiliary keypad are used for editing and moving the cursor from one data field to another. 5.4.2.2.1 PF1 - This key causes the character under the cursor to be deleted and all characters to the right of the cursor to be moved left one position. 5.4.2.2.2 PF2 - This key activates insert mode. When the next character is typed all characters under and to the right of the cursor are moved one position to the right, the new character inserted under the cursor and then the cursor is moved to the right (if possible). 5.4.2.2.3 PF3 - This key causes the cursor to be move back to the start of the previous field data area on the screen. If this key is used when the cursor is on the first (unprotected) field on the screen, Data Entry Mode is terminated. 5.4.2.2.4 PF4 - This key causes the cursor to be move to the next field data area on the screen. If this key is used when the cursor is on the last (unprotected) field on the screen, Data Entry Mode is terminated. 5.4.2.3 Control keys - A number of keys (that generate special control codes) can be used for data editing and moving between fields. DBSMNG: Database Management Package 43 5.4.2.3.1 TAB - This key causes the cursor to be move to the next field data area on the screen. If this key is used when the cursor is on the last (unprotected) field on the screen, Data Entry Mode is terminated. 5.4.2.3.2 BACKSPACE - This key causes the cursor to be moved to the start of the previous field data area on the screen. If this key is used when the cursor is on the first (unprotected) field on the screen, Data Entry Mode is terminated. 5.4.2.3.3 DELETE - This key causes the character to the left of the cursor to be deleted and all characters under and to the right of the cursor to be moved left one place. If this key is pressed when the cursor is at the leftmost part of the field it is ignored. 5.4.2.3.4 Control-U - This key causes all data in the field data area (in which the cursor resides) to be erased. The cursor is positioned at the leftmost point of the field data area. 5.4.2.4 Automatic TABing - Typing a character when the cursor is over the last character position of the field area results in the the cursor being moved to the start of the next field data area on the screen. If cursor is already on the last (unprotected) field on the screen, Data Entry Mode is terminated. As this is similar to what happens when the TAB key is hit this feature is termed automatic tabing. 5.5 Accessing Records DBSEDT operates on database records one at a time and thus many of its commands require that the user indicate which record is to be operated on. This can be done in two ways. 5.5.1 Explicit Record Access - Each database record can be identified by a unique (integer) number termed its 'record number'. This number indicates the slot (segment) of the database file that is used to hold the data associated with the record. This special number is automatically stored in each record's "rn" field by DBSMNG. This number can be used to explicitly access any record in the database. A record can be explicitly selected in an operation by entering the command verb followed by the number of the desired record (separate the verb and record number with a <SPACE>). This is the quickest and most efficient method of accessing records. In an equivalent card system the record number would correspond to the position of a card in the system and an explicit access is equivalent to accessing the N'th card. DBSMNG: Database Management Package 44 5.5.2 Keyed Record Access - The other method of accessing records is termed 'keyed' and is used when the record number is not known. When a record number is not specified as part of the command DBSEDT displays a special form termed the "key form" on the screen and positions the cursor at the start of its first field. Data specific to the record to be accessed is entered into this form. The <RETURN> key is used (after the data is entered) to cause DBSEDT to search for any matching records. If no record that matches the target data can be located DBSEDT will display an appropriate error message. If multiple matching records are located DBSEDT displays then one at a time to allow the desired one to be selected. (The operator uses the three DBSMNG commands [ACCEPT], [NEXT] and [ABORT] to select the desired record.) 5.6 DBSEDT Commands At the commencement of each sequence DBSEDT displays the '>' prompt to indicate that is is waiting for a command. When displayed, this prompt indicates that a command verb optionally followed by an argument should be entered. The command is terminated by pressing the <RETURN> key. Commands are available to allow records to be created, edited and deleted. In the case of commands that require the selection of a specific record, either explicit or keyed access may be used to locate the desired record. The available commands are described below. 5.6.1 BYE: Terminate Execution - This command is used to exit the editor. Control is returned to either the monitor or to the next CUSP in the sequence if the editor was invoked as part of a CUSP chain. 5.6.1.1 [RETURN] Implicit Bye Command - Pressing the [RETURN] key without entering a command verb (viz a null command) is equivalent to entering BYE and will cause DBSEDT to exit. 5.6.2 CREATE: Create a New Record - This command is used to create a new record. If space is available in the data file to hold another record DBSEDT will display an empty record on the screen and position the cursor at the start of the new field. The data for the new record may now be entered. Refer to the section on 'Form Editing Mode' for more information. The equivalent command verb NEW can also be used to create a new record. 5.6.2.1 CREATE N: Create a Copy of Record N - A record number may be optionally entered as after the CREATE verb. Doing so causes the data in the new record to be initialized with a copy of the data in the record specified. The command "CREATE 3" causes a new record to be created and the data from record 3 copied into it. DBSMNG: Database Management Package 45 5.6.3 NEW: Create a New Record - This command is used to create a new record. The equivalent command verb CREATE can also be used to create a new record. 5.6.3.1 NEW N: Create a Copy of Record N - A record number may be optionally entered after the NEW verb. Doing so causes the data in the new record to be initialized with a copy of the data in the record specified. 5.6.4 EDIT: Edit an Existing Record - This command is used to edit the data stored in an existing record. The record to be edited can be explicitly or key accessed. DBSEDT will display the selected record on the screen and position the cursor at the start of the first field. The contents of the record may now be altered. Refer to the section on 'Form Editing Mode' for more information. 5.6.5 INSPECT: Examine Record - This command is used to inspect the data in an existing record. The record to be inspected may be explicitly or key accessed. DBSEDT will display the selected record on the screen. 5.6.6 DELETE: Mark Record as Deleted - This command is used to delete a record. The record to be deleted can be explicitly or key accessed. DBSEDT will display the selected record on the screen and request that the operator confirm the command. If the command is confirmed the record is marked as deleted (by setting the "st" field to "DE"). The record is not actually removed from the database until the PURGER program is run. 5.6.6.1 Recovering DELETED Records - The "CREATE N" command can be used to recover the data in a record that has been accidently deleted. 5.6.7 PAGE N: Select Page N - DBSEDT can be used with multi-page TEMPLATEs. The PAGE command is used to select the page that is to be displayed. 5.7 Form Editing Mode Commands The CREATE and EDIT commands place the editor in Form Edit Mode. Upon entry to this mode the cursor is positioned at the start of the first field in the record. Typing any character other than the special ones previously defined will cause the data currently stored in a field to be overwritten. The special keys can be used to move the cursor from field to field and insert and delete characters within each field. Typing the <RETURN> key places DBSEDT in a special command mode. Commands are available in this mode to perform special editing functions. The commands available in this special mode are detailed below. DBSMNG: Database Management Package 46 5.7.1 EXIT: Exit Edit Submode - This command causes the edit submode to be exited. The record, along with any changes made, is written back to disk. 5.7.1.1 [RETURN] Implicit EXIT Command - Pressing the [RETURN] key without entering a command verb (viz a null command) is equivalent to entering EXIT and will cause the edit submode to be exited. 5.7.2 ABORT: Abort Edit Submode - This command causes the edit submode to be exited. The record is not written back to disk. This means that any changes made to the record are discarded. 5.7.3 EDIT: Edit Field Data - This command re-enters data entry mode. The cursor is positioned at the start of the field last edited. 5.7.4 FIRST: Edit First Field - This command re-enters data entry mode, positioning the cursor at the start of the first field in the record. 5.7.5 TOP: Edit Field at Top of Page - This command re-enters data entry mode, positioning the cursor at the start of the first field on the currently selected page. 5.7.6 LOCATE: Edit Specified Field - This command re-enters data entry mode, positioning the cursor at the start of the field whose name is entered as part of the command. The name of the desired field should be separated from the command verb with a <SPACE>. The name may be abbreviated. DBSEDT will locate the first field that matches the abbreviation. If a field name is not entered as part of the command DBSEDT will prompt for one. 5.7.7 PAGE: Select New Page - This command is used to select a desired page of a multi-page record. When all the record data cannot fit on one screen, fields may be assigned to different pages. This command allows the screen to be used to display fields assigned to the specified page. The number of the desired page is typed as part of the command separated from the verb with a space. 5.7.8 CLEAR: Clear out Record - This command results all data being erased from the record. New data may then be entered. DBSMNG: Database Management Package 47 5.7.9 REFRESH: Refresh Screen - This command results in the record being redisplayed. This command is useful if the screen display is accidently corrupted. DBSMNG: Database Management Package 48 Chapter 6 SELECT: Selective Information Extractor This chapter details the operation and use of the SELECT CUSP which is used to extract information from a database and load it into an SFL type file so that it can be used as input to other database CUSPs (eg REPORT, INSKEY, FINDER) or user application programs. SELECT is normally invoked at the start of a CUSP chain to select the information that is to be processed by the chain. A FRM type file is used to determine the data that is to be extracted from the database and how it will be stored in the SFL file. Such a file is in reality a small program that is interpreted by SELECT. While the instruction set is small, it is extremely powerful and allows complex data extraction algorithms to be quickly and efficiently implemented. Instructions are available for interacting with the operator and for testing, moving and converting data. ---NOTE--- Application managers should pay particular attention to the hints and example FRM files at the end of this chapter. 6.1 General Overview This section provides a general overview of the use and operation of SELECT and explains the concepts and terminology used. 6.1.1 FRM File - SELECT is targeted for a specific application by means of a FRM file. The FRM file determines exactly what data is to be extracted and how it will be stored in the SFL file. FRM files are sequential ASCII stream files created by the application manager using an RT-11 editor (eg EDIT, KED or TECO) which determine the operations to be performed. They can be thought of as simple programs interpreted by SELECT. The physical End of File (EOF), a NULL or a control-Z can be used to indicate the logical EOF. Certain characters (viz Uparrow, Escape and Underline) have special significance in FRM files. 6.1.1.1 Uparrow Sequence - The uparrow character (^) causes the next character in the FRM file to be converted into a control character (eg ^J is equivalent to a line feed). 6.1.1.2 Escape Sequence - The Escape character ($) causes the next character in the FRM file to be converted into a special function code (eg $A is equivalent to a special function code of 301 octal). DBSMNG: Database Management Package 49 6.1.1.3 Underline Sequence - The underline character (_) inhibits the conversion or interpretation of the next character in the FRM file (eg _^ is the only way to prevent the uparrow from being interpreted as part of an uparrow sequence). 6.1.1.4 ^@ Repeat Block Sequence - The sequence of characters ^@ in a FRM file is used to indicate the start of a repeat block. The repeat block is terminated using the same two characters. The format of a repeat block is [^@][X][....][^@] where [....] represents the sequence of characters to be repeated and [X] is a single character whose ASCII value is used as the repeat count. For example, the sequence ^@0ABC^@ is equivalent to writing the characters ABC forty-eight times (since the ASCII code for "0" is forty-eight). 6.1.2 SFL File - SFL files are used to transfer data between DBSMNG CUSPs or to a user program. An SFL file consists of a 512-byte header followed by a number of equal length records. Data is typically loaded into such a file by means of the SELECT CUSP, optionally sorted using SORTER and then used as input to REPORT, INSKEY, FINDER or BACKUP. 6.1.2.1 SFL File Creation - SFL files must be pre-allocated by the application manager. This means that they must be explicitly created (ie the space allocated on the disk and the file entered in the directory) prior to attempting to use them. This is done by means of the RT-11 CREATE command. Manual creation allows them to be positioned on the disk to achieve optimum performance and helps prevent disk fragmentation. 6.1.2.2 Header Block - Block 0 of an SFL file is used as a header block. The first word of the header holds the number of bytes per data record. The second word holds the number of data records in the file. The rest of block 0 is available for general data interchange (eg SELECT passes a copy of its variables to REPORT through this area). 6.1.2.3 Data Blocks - The remaining blocks in an SFL file are used to hold data records. Each data record consists of a two-byte tag followed by the record data. DBSMNG CUSPs use the tag to hold the "rn" for the database entry from which the following data was obtained. 6.1.2.4 Usage - An SFL file is used to pass information between DBSMNG CUSPs. It is a vehicle for passing extracted data and a tag to identify the database record from which the data originated. An SFL file can be used in a number of ways. DBSMNG: Database Management Package 50 6.1.2.4.1 Record Selection - The REPORT, PURGER and BACKUP CUSPs use the tag information passed in the SFL file to determine which records in the database are to be processed and the order of processing. The extracted data stored in the SFL file is not used by these CUSPs as once they have the tag information they directly access the database record from which the data was extracted. 6.1.2.4.2 Data Extraction - FINDER allows the data stored in the SFL file to be quickly examined and is used in special applications were rapid data retrieval is needed. A small subset of the data in the database is extracted with SELECT and passed to FINDER via the SFL file. FINDER never access the original database and as such all relevant data must be passed to it in the SFL file. 6.1.2.4.3 Interfacing to User Programs - A SFL file may be used to pass data to a user written application program. SELECT may be used to extract all required data from the database and store it in an SFL file in the format required by the user program. FORTRAN and MACRO interface routines are available to allow the data in the SFL file to be accessed. Passing data to user programs via the SFL file can be advantageous as it eliminates the need to interface directly with the database, thereby significantly reducing the (memory) size and complexity of the program. 6.1.3 Record Size - An SFL file contains a number of equal length entries. The number of bytes in an entry is termed the Record Size. The Record Size may be specified explicitly when SELECT is first invoked or left to SELECT to determine. If the Record Size is not explicitly specified SELECT uses the size of the first record written out to the SFL file as the implicit Record Size. 6.1.4 Cycle - The FRM file specifies the data SELECT is to extract from each database record for output to the SFL file. SELECT effectively interprets the 'program' in the FRM file once for each record in the database. SELECT is said to perform a 'cycle' for each record in the database. 6.2 Invoking SELECT SELECT may be invoked directly via a command file or as part of a CUSP chain. Typical applications invoke SELECT as part of a chain via the MENU program, as this minimizes the information that must be supplied by the user. MENU allows responses to the SELECT questions to be passed as Core Common Commands. 6.2.1 SELECT Prompts - The uncustomized version of SELECT, when invoked from MENU as part of a cusp chain, will prompt the user to supply the following information (as previously noted, answers may be passed via MENU thereby bypassing the user) DBSMNG: Database Management Package 51 6.2.1.1 DB Template - In response to this prompt, the name of the file that holds the TEMPLATE for the database must be entered. 6.2.1.2 Format File - In response to this prompt, the name of the file that specifies the data to be extracted must be entered. 6.2.1.3 Output File - In response to this prompt, the name of the SFL file that is to be used to hold the extracted data must be entered. 6.2.1.4 Record Size - In response to this prompt, the number of bytes per SFL file data entry may be specified. If zero is entered, SELECT will use the size of the first entry output to the SFL file as the record size. 6.3 SELECT Operation The Format File is of type FRM and can be thought of as a small program that SELECT interprets. The algorithm used is as follows Start: Gain access to all files Starting with first database record Rewind FRM file Clear Output Enable Flag Load Record Execute commands in FRM file Repeat for next record Close Files Exit It is important to note that SELECT processes the database records one at a time and terminates execution when all records have been processed. The Output Enable Flag (OEF) and the Output Buffer (OB) are fundamental to understanding the selection cycle executed for each record. 6.3.1 Output Buffer - SELECT allows specified items of data to be loaded into the Output Buffer. While these items are normally the contents of one or more of the fields for the record being processed, SELECT allows the OB to be loaded with data extraneous to the database. The Output Buffer is flushed at the start of each LOAD sequence. During the sequence, data is loaded into the buffer and may optionally be 'edited'. When the desired data has been loaded, the load sequence is terminated and SELECT attempts to output the contents of the buffer to the SFL file. 6.3.2 Output Enable Flag - As explained above, SELECT attempts to output the contents of the Output Buffer to the SFL file at the termination of a LOAD sequence. The Output Enable Flag determines if the contents of the buffer can actually be output to the file. This flag is cleared at the start of each cycle, and will become set only as the result of a successful test. As output can only take place when the flag is set, the Output Enable Flag allows data to be conditionally extracted and output to the SFL file. DBSMNG: Database Management Package 52 6.3.3 Rewinding the Format File - In some cases a Format File may contain initialization instructions that are to be executed only once. In such cases, particularly if the initialization requires user input, rewinding the Format File to its start would result in the unwanted execution of the initialization code each cycle. To overcome this problem a special operator may be included in the format file to mark the rewind point. 6.4 Arguments Many SELECT operators (described in the next section) take arguments. This section describes the types of arguments supported by SELECT. 6.4.1 Constants - SELECT supports Numeric (binary unsigned integers), Date and Literal (ASCII string) constants. 6.4.1.1 Numeric Constants - are unsigned integers in the range 0 to 65000 and consist of a string of digits. 6.4.1.2 Date Constants - are integer triplets that represent dates in Day, Month and Year form. The format for a date constant is !DD-MM-YY where ! indicates that the following constant is of type date, - is a delimiter, and DD, MM and YY are integers representing the day, month and year respectively. YY can only represent the last two digits of the year and can range from 72 to 99. 6.4.1.3 Literal Constants - consist of one or more characters enclosed in either single or double quotes. While the quotes are needed to delimit the string, they are not actually a part of the literal. Single quotes may be included in a literal constant by enclosing the string in double quotes and vice versa. 6.4.2 Variables - SELECT supports different types of variables. The correct 'type' of data must be stored in each variable. 6.4.2.1 Numeric Variables - are used to store numeric constants and are identified by their names. A numeric variable is of the form N? (?=0-9) where N indicates that the argument is a numeric variable and ? indicates which of the 10 numeric variables is being referred to. Numeric variables are initialized to zero. DBSMNG: Database Management Package 53 6.4.2.2 Date Variables - are used to store date constants and are of the form D? (?=0-4) where D indicates that the argument is a date variable and ? indicates which of the 5 date variables is being referred to. Date variables D0, D1, D2 and D3 are initialized to 0-0-72 and D4 to the current system date. 6.4.2.2.1 D4: System Date - The date variable D4 is initialized to the current system date by SELECT. This is useful when selecting records by "today's" date. 6.4.2.3 Literal Variables - Literal variables are used to store literal constants (without their delimiting quotes) and are of the form L? (?=0-3) where L indicates that the argument is a literal variable and ? indicates which of the 4 literal variables is being referred to. Literal variables are initialized to NULLs. 6.4.2.4 Variable Passing Between CUSPs - The contents of variables N0-N9, D0-D4 and L0-L2 are copied into the SFL file header block by SELECT prior to exiting so as to be available to other CUSPs (eg see documentation on initialization of REPORT CUSP variables). 6.4.3 Field Contents - In many operations it is desirable to use as an argument the contents of a field in the record being processed. Fields can be accessed directly or indirectly. 6.4.3.1 Direct Field Access - Using <NAME> or [NAME] as an argument, where NAME is the name of a database field, results in the contents of the field for the record currently being processed being used as an argument. 6.4.3.2 Indirect Field Access - Using @L? as an argument, where L? is one of the literal variables L0 to L3, results in the contents of the database field whose name is in L? to be used as the argument. If L? is null, SELECT uses the NULL string as the argument. 6.5 SELECT Operators SELECT commands consist of operators optionally followed by arguments. An operator is represented in the FRM file as two characters, the first of which must be the 'dollar' character ($). SELECT normally ignores <TAB>, <LF>, <CR> and <SPACE> characters, as well as any characters following a <SEMICOLON> on a line. This allows a complex FRM file to be laid out and commented. The places where these characters are not ignored are obvious (eg within a literal constant). DBSMNG: Database Management Package 54 6.5.1 $$ End of Block - Complex commands (eg TEST and LOAD) are delimited by an operator that indicates the function of the command and a special End of Block operator $$ that marks the end of the complex command. 6.5.2 $E End of Program - The $E operator is used to indicate the end of the SELECT program (in the FRM file). This operator causes SELECT to rewind the file and start processing the next record. 6.5.3 $* Marking Rewind Point - The $* operator marks the FRM file rewind point. Prior to processing the next record SELECT rewinds the FRM file to the point marked by the last $* operator in the file. This command is used to prevent initialization commands from be executed more than once. 6.5.4 $A Append to Existing SFL Contents - In some applications SELECT may have to be used to extract data from a number of databases and store the extracted data in one SFL file. This can be done by using the $A 'append' operator. This operator must appear at the very start of the FRM file and will cause SELECT to append to the data already in the SFL file. Note that the $A operator performs an implicit $* operation. 6.5.5 $R Range Specification - SELECT normally processes each record in a database. This is slow when a small number of records in the middle of a large database have to be selected. The $R command can be used in such cases to cause SELECT to process only records in a given range. The command syntax is "$R N1,N2" where N1 and N2 are two numeric arguments which represent the first and last records in the range that is to be processed. 6.5.6 $) Output to Terminal - The $) operator causes text to be output to the terminal. The text string is terminated with a $$ sequence. This operator is mostly used to prompt the user for input. The command format is $)Output this text to terminal!$$ 6.5.7 $? Input Data from Terminal - The $? operator is used to input data from the terminal and store it in the variable whose name directly follows the operator. The data entered from the terminal must be of the same type (viz Numeric, Date or Literal) as the variable in which it is to be stored. When inputting into a literal variable, all characters up to but not including the [RETURN] typed on the terminal are stored in the specified variable. The command format is $?L0 DBSMNG: Database Management Package 55 6.5.8 $( Input Field Name from Terminal - The $( operator is used to input the name of a database field from the terminal and store in in a literal variable specified directly after the operator. SELECT will automatically issue an error message and retry the input operation if an invalid field name is entered. The command format is $(L0 6.5.9 $T Do Tests - The $T operator marks the start of a test command. The end of the test command is indicated by the $$ (End of Block) operator. This command is used to perform a series of boolean tests (enclosed within the $T and $$ operators). If all the boolean tests succeed (ie are logically TRUE) the Output Enable Flag is set, thereby allowing data (from the record being processed) to be output to the SFL file. The test command is used to determine whether or not data from the record is to be loaded into the SFL file. The command format is $T {$C} {S1} ... {Sn} $$ 6.5.9.1 Output Enable Flag - The LOAD command (see below) is used to determine the data that is to be extracted and loaded into the SFL file. Whether or not the data is actually extracted and loaded for a particular record depends upon the Output Enable Flag. Data is extracted from a record and loaded into the SFL file only if this flag is set. 6.5.9.1.1 OEF Initialization - The OEF is reset to 0 at the start of each select cycle (ie prior to processing the next record) thereby disabling output. 6.5.9.1.2 OEF Setting - The OEF is set when all boolean statements within a test command are logically true and remains set until cleared via a $C operator or the start of a new cycle. 6.5.9.2 $C Clear OEF - In some applications multiple test and load operations have to be performed on the same record. The $C operator may optionally be included within the range of a test command to clear the OEF prior to the performing a series of boolean tests. 6.5.9.3 Boolean Statements - One or more boolean statements can be included within the range of a test command. The OEF will be set if all the boolean statements within the range of the command are logically true. The format of a boolean statement is [A1] [Boolean-operator] [A2] where [A1] and [A2] are two arguments and [Boolean-operator] is one of the boolean relationship operators defined below. Note that the two arguments [A1] and [A2] must be of the same type (ie Numeric, Date or Literal). ---NOTE--- When comparing two literal arguments, lower case letters are treated as upper case letters and the longer literal string is truncated to make it the same size as the shorter. DBSMNG: Database Management Package 56 6.5.9.3.1 .EQ. Boolean-Operator - the boolean statement "A1.EQ.A2" is logically true if and only if argument A1 is equal to argument A2. 6.5.9.3.2 .NE. Boolean-Operator - the boolean statement "A1.NE.A2" is logically true if and only if argument A1 is not equal to argument A2. 6.5.9.3.3 .LT. Boolean-Operator - the boolean statement "A1.LT.A2" is logically true if and only if argument A1 is less than argument A2. 6.5.9.3.4 .GT. Boolean-Operator - the boolean statement "A1.GT.A2" is logically true if and only if argument A1 is greater than argument A2. 6.5.9.3.5 .LE. Boolean-Operator - the boolean statement "A1.LE.A2" is logically true if and only if argument A1 is less than or equal to argument A2. 6.5.9.3.6 .GE. Boolean-Operator - the boolean statement "A1.GE.A2" is logically true if and only if argument A1 is greater than or equal to argument A2. 6.5.10 $L Load Data - The $L operator marks the start of a load data command. A list of arguments and format operators terminated by the End of Block $$ operator follow the $L operator and indicate the data that is to be loaded into the SFL file and how it is to be formatted. The format of the load command is $L {O} {A1} {O} ... {An} {O} $$ where {A1} .. {An} are arguments to be loaded into the SFL file and {O} represents optional operators that can be used to format the output. 6.5.10.1 Output Buffer - The arguments specified in the load command are initially loaded into the Output Buffer and are not actually written out to the SFL file until the $$ terminator is encountered, and then only if the Output Enable Flag is set. This allows simple manipulation of the data in the buffer to be performed. If the Output Buffer is empty or if the Output Enable Flag is not set when the $$ terminator operator is hit, SELECT will not write anything out to the SFL file. The following operators may be used between the $L and $$ operator characters to manipulate the data loaded into the Output Buffer. 6.5.10.2 Imbedded Operators - Special operators may be imbedded within a load command to format and manipulate the data being assembled in the Output Buffer. 6.5.10.2.1 $< Trim - The $< operator causes all trailing spaces to be deleted from the data loaded into the Output Buffer. 6.5.10.2.2 $> Right Justify - The $> operator causes all trailing spaces to be deleted from the data loaded into the Output Buffer and re-inserted at the front of the buffer, thereby right justifying the data. DBSMNG: Database Management Package 57 6.5.10.2.3 $:DD Position - The $: operator will pad the output buffer with SPACEs or delete data bytes previously loaded so that subsequent data is loaded starting at byte DD (where DD represents a two digit decimal number) of the Output Buffer. 6.5.10.2.4 $^ Uppercase - The $^ operator causes all lower case characters in the Output Buffer to be converted to upper case. 6.5.10.2.5 $~ Flush - the $~ operator will flush the Output Buffer if it contains only SPACEs. This operator can be used to stop spaces in empty fields from being output. 6.5.10.2.6 $S Switch - the $S operator results in replacing the first two bytes in the Output Buffer with the 16-bit integer tag associated with each SFL file entry that identifies the number of the record from which the data was extracted. 6.5.10.2.7 $# Convert to Ascii - the $# operator results in the removal of the last two bytes from the Output Buffer and the appending of a 5 character (right justified) decimal string to the remaining data that represents the value of the two bytes that were removed. 6.5.10.2.8 $- Delete Character - the $- operator results in the deletion of all bytes in the Output Buffer that hold the character that immediately follows the $- operator. 6.6 Example Format Files Below are a number of Format Files that perform a variety of useful functions. They also provide a useful starting point for developing others. ; This program will extract the contents of the field named AGE from each ; active database record and output it to the sorter file so that a report ; ordered in ascending AGE can be produced ; $* ; mark start of process loop $T [st].EQ."AC" $$ ; want only active records $L [AGE] $$ ; extract age $E ; end of loop ; This program will extract the contents of the field specified by the user ; for the records within the specified range. If the field contains only ; spaces, we ignore it. Also, as we wish to use the sorter output as ; input to INSPECT, we convert all LC to UC. ; $)Start at record number : $$ $?N1 ; get lower limit $)End at record number : $$ $?N2 ; get higher limit $)Enter field to sort on : $$ $?L1 ; get sort field $* ; mark start of loop $T [st].EQ."AC" ; want only active records [rn].GE.N1 ; record number must be within ... [rn].LE.N2 ; ... limits $$ $L @L1 $~ $^ $$ ; load data $E ; end of loop DBSMNG: Database Management Package 58 ; This program will extract the contents of the field specified by the user ; for the records within the specified range. If the field contains only ; spaces, we ignore it. Also, as we wish to use the sorter output as ; input to INSPECT, we convert all LC to UC. This version uses the $R range ; specification command and is faster than the previous one for large databases. ; $)Start at record number : $$ $?N1 ; get lower limit $)End at record number : $$ $?N2 ; get higher limit $)Enter field to sort on : $$ $?L1 ; get sort field $R N1,N2 ; process records N1 to N2 only $* ; mark start of loop $T [st].EQ."AC" ; want only active records $$ $L @L1 $~ $^ $$ ; load data $E ; end of loop ; This program is used to sort records by the contents of the fields ; ; SUBURB STREET NUMBER ; ; respectively. This means that records will be ordered by SUBURB, within a ; SUBURB by STREET and finally by NUMBER. $* ; start loop $T [st].EQ."AC" $$ ; only want active records $L [SUBURB] [STREET] [NUMBER] ; load $E ; end program ; This program requests the user to input three field names, and concatenates ; the contents of the three fields prior to outputting them to the sort file. ; This program can be used to sort data by up to three user-specified keys. ; Note that the user may simply type in a <CR> to a request for a field ; name if sorting is to be done on only one or two fields. ; $)Enter primary sort field : $$ $?L1 $)Enter secondary sort field : $$ $?L2 $)Enter third sorting field : $$ $?L3 $* ; start of loop $T [st].EQ."AC" $$ ; only want active records $L @L1 @L2 @L3 $^ ; load, convert to UC $E ; end program ; This program extracts data from the personal phone/address database ; and formats so that the SFL file can be used by FINDER. ; $T [st].EQ.'AC' $$ ; only want active records $L [TITLE] $< ' '[SURNAME]$< $^ ; load names (trimmed etc) $:30 ; tab to column 30 ' W:' [PHONE(W)] ; load work phone number ' H:' [PHONE(H)] ; load home phone number $^$~$$ $E DBSMNG: Database Management Package 59 Chapter 7 SORTER: Sorting Utility SORTER is a DBSMNG CUSP that sorts the entries in a SFL into ascending (or descending) order. In typical applications SORTER is invoked after SELECT was extracted data from the database and placed it in the SFL file. SORTER may be invoked as part of a CUSP Chain from MENU. This chapter gives a brief description of the use and operation of SORTER. For most applications SORTER can be treated as a black box as its interaction with the user is minimal. 7.1 Using SORTER SORTER is used to order the entries in a SFL type file. When envoked the program requests that the user enter the name of the SFL file to be SORTED. The CUSP is most commonly invoked as part of a CUSP chain. When called in this mode, the name of the SFL file is generally passed as a Core Common Command. 7.2 Optional SORTER Switches When envoked SORTER prompts for the name of the SFL file to be SORTED. The file name supplied by the user may include one or more of the following switches. 7.2.1 /R Reverse Order Switch - SORTER normally sorts the entries in an SFL file into ascending order. The /R switch causes SORTER to order the entries in the SFL file in descending order. 7.2.2 /S Statistics Switch - The /S switch causes performance statistics to be displayed. The statistics show memory usage, number of I/O operations performed and the time required to perform the sort. 7.2.3 /P Pause Switch - The /P switch causes SORTER to pause at the end of each pass. The user must press the [RETURN] key on the terminal before SORTER commences the next pass. The /P switch can be used when very large SFL files are being sorted to allow a backup copy to be saved at the end of each pass (to protect against a system crash). DBSMNG: Database Management Package 60 7.3 Operational Description The function of SORTER is to sort the entries in a SFL type file into ascending order, and can be used on any file of the following format Block 0 .Word SIZENT ; number of bytes in an entry .Word NUMENT ; number of entries in data file .BLKW 254. ; contents of rest of block irrelevant Block 1 .Blkb SIZENT ; Entry #1 2 ; More .... . ; .... entries . .Blkb SIZENT ; Entry #NUMENT (last entry) The format of all entries are identical, and consists of a two byte header that is used by the database CUSPs to store the number of the database record from which the data was extracted, followed by bytes containing the data extracted. Note that SIZENT is the total number of bytes in an entry, and must be at least three (ie 2 header bytes and at least 1 data byte). The function of SORTER is to rearrange the file such that the first byte in an entry that is not equal to its equivalent byte in the next entry is smaller than it. This means that if the entries hold names, the names will be sorted in ascending alphabetic order. The sort algorithm used is relatively simple. Upon activation, the sorter obtains as much memory as it can from the monitor, and proceeds to divide this memory into queue elements. Each queue element consists of a forward and backward pointer. In a typical environment approximately 500 elements will be available. The program then reads in data from the sort file, and stores it in a queue element. As each queue element is loaded with data, it is linked into an order queue. If the program runs out of queue elements (ie there is not enough memory to hold all the entries at once), the program has to remove an element from one end of the ordered queue and write out its contents before it can process the next entry from the file. In this case another sorting pass will be needed if a subsequent entry is inserted at the end of the queue from which element are being removed. 7.4 Interfacing to SORTER User application programs may make use of SORTER; the SRTFOR module may be used by FORTRAN programs to load data into a SFL type file, invoke SORTER and retrieve the sorted data. MACRO programs may use the SFLIO module to perform equivalent functions. DBSMNG: Database Management Package 61 Chapter 8 INSKEY: Multi-key Database Inspection CUSP The INSKEY programs allows the contents of a database to be viewed on the user's terminal and optionally printed. The user indicates the records that are of interest by means of one or more keywords; INSKEY will locate and make available for viewing those records that best match the specified keys. Because INSKEY uses a specially sorted relational INDEX file it can be used for rapid online retrieval of data from extremely large databases (greater 20000 records) and the database fields used as keys can be changed at any time without having to modify the actual database. INSKEY allows the database manager to incorporate application oriented online help text. 8.1 Invoking INSKEY Before INSKEY can be used, a specially sorted relational INDEX file of keywords must be created. This is done by the application manager, following the procedure outlined later in this chapter using the SELECT and SORTER cusps. Before invoking INSKEY, the logical name DBS: must be assigned to the volume upon which the database files reside. Assuming that the uncustomized version of the CUSPs are being used and that these reside on the volume with logical name LB:, the following command, entered at keyboard monitor level, will start up the program RUN LB:INSKEY<CR> Upon activation, the program requests that the user identify the type of terminal being used, and enter the names of the files that contain the database TEMPLATE, the sorted keys (ie INDEX file) and the application specific help text (ie HELP file). In response to each of these questions simply enter the appropriate answer terminated by a carriage-return. INSKEY will then setup the screen and prompt the user for keywords. 8.1.1 Using a start-up file - In simple applications, the program may be started via a command file which can be used to declare the logical assignments, invoke the program and answer the questions. 8.1.2 Using CUSP chaining from a menu - INSKEY is normally invoked from an application menu as part of a CUSP chain by the DBSMNG MENU program. When invoked from MENU, the terminal type, TEMPLATE, INDEX and HELP file names may optionally be (and normally are) passed directly to INSKEY. This is the preferred method for invoking INSKEY as the user need only press one key to invoke the CUSP and is protected against the idiosyncrasies of the operating system. DBSMNG: Database Management Package 62 8.2 Screen Layout Any point of the terminal screen may be identified by specifying a line and character number. The line number (sometimes called the Y coordinate) can range from 0 to Yb, with 0 representing the top line, and Yb the bottom line. The character number (sometimes called the X coordinate), can range from 0 to Xr, with 0 representing the leftmost character position, and Xr the rightmost position. INSKEY sections the screen into two areas, termed the INTERACTION and FORM areas. 8.2.1 Interaction Area - Lines 0-3 of the screen are used by INSKEY for interacting with the user. Lines 0 and 1 are used to display messages that identify the database, the operation being performed and commands available. This provides the user with a continuous help facility that ensures rapid learning. A 'help' command is also supported to provide the novice user with more detailed operating instructions. Line 2 is used for echoing input and line 3 for displaying error messages. 8.2.2 Form Area - Lines 4-Yb of the screen make up the form area and is used to display the data in the selected records. 8.3 Input Modes INSKEY has two distinct modes for accepting user input; the mode used depends upon whether the input is to be interpreted as a command or used to locate the records of interest. 8.3.1 Command Input Mode - INSKEY indicates that it is in command input mode by displaying the prompt 'Enter Command: ' at the start of line 2 and positioning the cursor directly after the prompt. In response to this prompt a single letter command must be entered. The [RETURN] key must not be pressed after the command letter. INSKEY displays a list of the valid commands on line 1 of the screen. 8.3.2 Keyword Input Mode - INSKEY indicates that it is in keyword input mode by displaying the prompt 'Keywords:' at the start of line 2 and the message 'Enter keywords separated by "," or ";" Press [RETURN] to initiate search' on line 1. Typing a character in this mode causes it to appear on the screen in the position occupied by the cursor, after which the cursor is moved one position to the right. As more characters are typed, the cursor continues to move to the right, until the end of the line is reached. The cursor cannot be moved past the end of the line. Typing additional characters simply causes the last character to be replaced. This mode can be exited at any time by typing a [RETURN], which will cause INSKEY to search for matching records and return to command mode. Special characters are available in this mode to edit the keywords. All other characters than those listed below, cause the character under the cursor to be replaced, and the cursor moved right one position. DBSMNG: Database Management Package 63 8.3.2.1 Cursor Movement Keys - The four cursor movement keys, situated on the top right of the main keyboard section, are used to move the cursor about within the confines of the area in which the keywords are being entered. 8.3.2.1.1 UP - This key results in the cursor being moved to the leftmost position of the area. 8.3.2.1.2 DOWN - This key results in the cursor being moved to the rightmost position of the area. 8.3.2.1.3 LEFT - This key results in the cursor being moved one place to the left, except when the cursor is already at the leftmost point of the area, in which case the key is ignored. 8.3.2.1.4 RIGHT - This key results in the cursor being moved one place to the right, except when the cursor is already at the rightmost point of the area, in which case the key is ignored. 8.3.2.2 Special Function Keys - The four special function keys at the top of the auxiliary keypad can be used for editing the keywords. 8.3.2.2.1 PF1 - This key causes the character under the cursor to be deleted, and all characters to the right of the cursor to be moved left one position. 8.3.2.2.2 PF2 - This key activates insert mode; when the next character is typed all characters under and to the right of the cursor are moved one position to the right, the new character is inserted under the cursor, and then the cursor is moved to the right (if possible). 8.3.2.2.3 PF3 - This key performs the same function as [RETURN]. Pressing [PF3] causes INSKEY to search for matching records and enter command input mode. 8.3.2.2.4 PF4 - This key causes all the keywords previously entered to be erased and the cursor positioned at the start of the area. 8.3.2.3 Control keys - A number of keys (that generate special control codes) can be used for editing the keywords. 8.3.2.3.1 TAB - This key causes all the keywords previously entered to be erased and the cursor positioned at the start of the area. 8.3.2.3.2 BACKSPACE - This key performs the same function as [RETURN] and causes INSKEY to search for matching records and enter command input mode. 8.3.2.3.3 DELETE - This key causes the character to the left of the cursor to be deleted, and all characters under and to the right of the cursor to be moved left one place. If this key is pressed when the cursor is at the leftmost part of the area it is ignored. DBSMNG: Database Management Package 64 8.3.2.3.4 Control-U - This key causes all the keywords previously entered to be erased and the cursor positioned at the start of the area. 8.4 Using Keywords to Identify Records of Interest INSKEY allows a user to inspect information stored in a database. It differs from the other database examination CUSPs in that it is designed for rapid online access. The user indicates via one or more keywords the records that are of interest and INSKEY quickly searches the database to locate records that match the specified keywords. The selected records can then be view one at a time and optionally printed. The keywords are entered separated by either commas or semicolons. 8.4.1 Keywords and Keys - The user enters KEYWORDS which INSKEY uses to determine the records of interest by comparing the specified KEYWORDS against the KEYS setup by the database manager for each record in the INDEX file. This means that INSKEY will select all records with a key of "DEC" if the user specifies the keyword "DEC". 8.4.2 Keyword - INSKEY treats any series of letters other than a comma, semi-colon or an editing character typed while in 'keyword entry mode' as a keyword. The comma and semicolon characters are used as keyword separators and the editing keys to modify the keywords. 8.4.2.1 Spaces - if the user types a <SPACES> as part of a keyword INSKEY displays it on the screen but ignores it when searching for matching records. This means that the keywords "ON LINE" and "ONLINE" are considered identical. 8.4.2.2 Lower Case - keywords can be entered in lower or upper case and are displayed on the screen as typed. INSKEY converts the keywords to upper case prior to performing the search. 8.4.3 Comma Delimiter: Approximate Match - if a keyword is terminated by a comma (,) INSKEY will allow approximate matches for that keyword. This means that INSKEY will select any record that has a key identical to the specified keyword or if the keyword is a valid abbreviation for the key. 8.4.4 Semicolon Delimiter: Exact Match - if a keyword is terminated by a semicolon (;) INSKEY will allow only exact matches for that keyword. This means that INSKEY will select only records that have a key identical to the specified keyword. DBSMNG: Database Management Package 65 8.4.5 Multiple Keywords - If a large database of over 10000 records is being inspected, one keyword can result in the selection of over 1000 records which is too many to inspect individually. Additional keywords have to be specified to reduce the number of selected records to a reasonable number. INSKEY only selects those records that match all the specified keywords. 8.4.6 Adding Keywords - The [RETURN] key is pressed to indicate that the desired keywords have been entered and to initiate the search for matching records and return to command mode. If too many records have been selected the "Add-keyword" command can be used to append additional keywords to those previously entered. 8.5 INSKEY Commands INSKEY displays the "Enter Command:" prompt on line 2 of the screen when it is ready to accept a command from the user. A list of valid commands is displayed on line 1 for the users convenience. In response to the input prompt the user selects a command by pressing a single character key (which is usually the first letter of the command name). The selected command will be executed as soon as the command key is pressed; the [RETURN] key does not have to be pressed after the command key. 8.5.1 [RETURN] Key - pressing the key marked [RETURN] while in command mode causes INSKEY to terminate execution. If INSKEY was invoked from a menu control will be passed back to the menu. 8.5.2 E Command (Exit) - The E key results the termination of the program. If INSKEY was invoked from a menu control will be passed back to the menu. 8.5.3 H Command (Help) - The H key results in the typing of the contents of the HELP file (specified when INSKEY was started) on the user's terminal. If no HELP file was specified the command key is ignored. 8.5.4 K Command (Keywords) - The K key is used to exit from Command Input Mode to Keyword Input Mode. INSKEY will replace the 'Enter Command:' prompt with the 'Keyword:' prompt and position the cursor in readiness to accept a new list of keywords. 8.5.5 A Command (Add-keywords) - The A key is used to exit from Command Input Mode to Keyword Input Mode. INSKEY will replace the 'Enter Command:' prompt with the 'Keyword:' prompt and position the cursor in readiness to accept additional keywords. The A and K commands differ in that the K command erases keywords previously entered while the A command leaves the keywords previously entered. DBSMNG: Database Management Package 66 8.5.6 N Command (Next) - The N key is used to inspect the records that match the specified keywords. Each time the N key is pressed INSKEY displays the next matching record. 8.5.7 P Command (Print) - The P key can only be used on a VT100 terminal and results in the sending of the data currently on the screen to the local printer. 8.5.8 F Command (Flip-page) - The F key can only be used with a multi-page template. Each time the F key is pressed the next page of the template is displayed. 8.5.9 R command (Refresh) - The R key is used to refresh the screen. This command is used to repaint the screen if it becomes corrupted. 8.6 Using INSKEY This section shows how INSKEY can be used by way of an example. Unfortunately, as INSKEY is very flexible and can be specially tailored for each application it is impossible to provide a comprehensive guide in this document for its use. The INSKEY help command should be used to obtain application specific operating instructions. Let us assume that INSKEY is to be used to inspect records contained in a address/telephone database and that the following fields have been setup as keys by the application manager Title Surname Company Profession and that we are trying to find the address of a Mr Johnathon Black. If INSKEY is in command mode the K key is pressed to exit from command mode to keyword input mode. The keywords BLACK;JOH, are entered and the [RETURN] key is then pressed to initiate a search and return to command mode. Note that an exact match of BLACK is requested (semicolon delimiter) as we a sure of the surname but approximate matches are allowed for the second keyword (comma delimiter) as we nor sure if Mr Black's title was entered as JOHN or JOHNATHON. When INSKEY completes it search it displays the number of selected records at the top of the screen and prompts for a command. If the number of selections is small the N (next) key can be used to scroll through them one at a time. If the number of selections is too large additional information must be entered to narrow the field. Assume that BLACK and JOH result in the selection of 39 records and we wish to narrow the field further. Pressing the A (Add-keywords) command will cause INSKEY to exit from command input mode to keyword input mode. The keywords previously typed will be displayed and the cursor positioned after "JOH," in readiness for additional keywords. If we know that Mr Black is a salesman we can modify the keyword list as follows DBSMNG: Database Management Package 67 BLACK;JOH,SALE, and then press [RETURN] to initiate a new search. This procedure of adding additional keywords can be repeated until the number of selected records is reduced to an acceptable level. 8.7 Setting up INSKEY This section is intended for the application manager and shows how to setup INSKEY for a telephone/address database (called PHONES). It is assumed that the contents of the 'Title', 'Surname', 'Company' and 'Profession' fields are to be used as keys. Reference is made to the information in the chapters on SELECT and SORTER which may have to be revised. 8.7.1 Invoking INSKEY from a Menu - INSKEY should normally be invoked from an application menu by including a sequence similar to the one below $7 ; do if key 7 pressed $@LB:INSKEY$$ ; start inskey $IPHONES$$ ; TEMPLATE=PHONES.SAV $IPHONES.INX$$ ; INDEX=PHONES.INX $IPHONES.HLP$$ ; HELP=PHONES.INX $CLB:MENU$$ ; on exit pass control $IPHONES.MNU$$ ; back to menu $! 8.7.2 Setting up the INDEX File - INSKEY uses a special INDEX file to locate records that match the user specified keywords. This file is created and loaded with the keys for each record by the application manager. The SELECT and SORTER cusps are normally used to load the file with keys for each record. As the INDEX file has to be regenerated each time the database is changed a command sequence is normally included on the application menu to regenerate the INDEX file. 8.7.2.1 Creating the INDEX File - The INDEX file is a SFL type file and room for it must be preallocated using the RT-11 CREATE monitor command. The format of the command is CREATE PHONES.INX[20] which would result in the creation of a 20 block (ie 10000 byte) file called PHONES.INX which would be used to hold the keys for the database records. 8.7.2.2 Loading Keys into the INDEX File - Once the INDEX file is created it must be loaded with the keys for each field. This is normally done with the SELECT cusp by loading the SFL file with data from the database fields that are to be used as keys. The following SELECT FRM file would be suitable for the database in our example. DBSMNG: Database Management Package 68 $T$C [st].EQ.'AC' [TITLE].NE.' '$$ $L [TITLE] $^$$ $T$C [st].EQ.'AC' [SURNAME].NE.' '$$ $L [SURNAME] $^$$ $T$C [st].EQ.'AC' [PROFESSION].NE.' '$$ $L [PROFESSION] $^$$ $T$C [st].EQ.'AC' [COMPANY].NE.' '$$ $L [COMPANY] $^$$ $E In effect the above FRM file would cause SELECT to load the contents of the TITLE, SURNAME, PROFESSION and COMPANY field for each active record in the database. Note that the loading of blank fields is not performed and that the keys are converted to upper case. 8.7.2.3 Sorting the INDEX File - Once the keys have been loaded they must be sorted. The SORTER cusp is used to do the sorting. 8.7.2.4 Generating the INDEX File from a Menu - The keys in the INDEX file have to be regenerated each time the database is changed. For this reason the commands needed to regenerate the INDEX file are normally included in the application menu. As it takes time to regenerate the INDEX file, the INDEX file is normally regenerated only at the end of the day. The following commands could be used to regenerate the INDEX file from the application menu $6 ; do when key 6 pressed $@LB:SELECT$$ ; invoke SELECT $IPHONES$$ ; TEMPLATE=PHONES.SAV $IPHONES.SE6$$ ; FRM file name $IPHONES.INX$$ ; INDEX file name $I10$$ ; use 10 letter keys $CLB:SORTER$$ ; Sort $IPHONES.INX$$ ; keys $CLB:MENU$$ ; on exit pass control $IPHONES.MNU$$ ; back to menu $! 8.7.3 Setting up the HELP File - the HELP file is created using a standard text editor and should contain operating instructions specific to the application and should be structured as a twenty line summary followed by detailed operating instructions. DBSMNG: Database Management Package 69 Chapter 9 INSPECT: Single-key Online Database Query CUSP A database is used to maintain important information in a structured form to facilitate access and maintenance. In some applications, it is important to provide for rapid retrieval of records that contain specific information. INSPECT is a DBSMNG CUSP that allows a terminal user to quickly retrieve and optionally print records. The CUSP uses a special SFL type file, termed the Index File, to map a user specified string, called the Target, that identifies the desired record, into a record number that can be used to retrieve the data from the database. This chapter explains the operation and use of INSPECT, and details the procedure that must be followed to allow it to be used. It is recommended that the chapters on SELECT and SORTER be read before attempting to use INSPECT. 9.1 Concepts This section describes the concepts unique to INSPECT. It is assumed that the chapters on SELECT and SORTER have been read, and the concepts introduced therein understood. 9.1.1 Target - The key to the operation of INSPECT is a literal string entered by the user via the keyboard. INSPECT will locate the database record(s) that match the specified target. If no exact matches are found, INSPECT allows the closest matches to be examined. 9.1.2 Target Fields - INSPECT locates matching records by comparing the Target with the contents of various fields in each database record. The fields used for the comparison are termed Target Fields. 9.1.3 Index File - In applications using large databases, INSPECT would take considerable time to check each database record to see if it matches the Target; to speed up the operation an Index File is used. This is a SFL type file, which is loaded with the contents of all Target Fields and then sorted; the SELECT and SORTER CUSPs allow this to be done quickly. The Index File can then be used by INSPECT to locate records that match the Target; as a binary search technique is used, response time is excellent for even large database. DBSMNG: Database Management Package 70 9.2 Using INSPECT INSPECT is normally made available to the user from an application MENU; where the user's only access to the application is via INSPECT, the program can be started up directly from a command file. The CUSP will prompt for the type of CRT being used, and the names of the TEMPLATE and Index Files. The user may enter appropriate responses, or answers can be supplied by MENU when the CUSP is invoked from a menu. MENU then clears the screen and prompts the user to enter a Target. The <RETURN> key is pressed once the Target has been entered to initiate a search of the Index File. After a short time, INSPECT will display the closest match to the Target that it could locate, and then prompt the user the enter a (single key) command. Note that there is no need to press <RETURN> to cause the command to be executed. Commands available are B backup one Index File entry N advance one Index File entry S show record associated with this entry T Prompt for a new Target E exit program P print record currently on screen (VT100 only) R refresh screen The program will also support the following escape sequences generated by the VT100. (For equivalent keys on other terminals refer to appendix) Up backup one Index File entry Down advance one Index File entry Right show record associated with this entry Left Prompt for a new Target PF1 print record currently on screen (VT100 only) PF2 refresh screen Note that the 'print record' command is supported only for VT100 terminals equipped with the optional printer port. 9.3 Application Notes This section is intended for the application manager, and outlines the steps that must be followed to use INSPECT in an application. 9.3.1 Target Fields - The application manager must firstly decide which fields will be used as Target Fields. This is completely application dependant. DBSMNG: Database Management Package 71 9.3.2 Creating the Index File - The next step is to create an Index File. Consideration must be given to how large the file is to be, and if it is to be used for other functions. 9.3.3 Creating the FRM file for SELECT - A FRM type file is needed to tell SELECT what fields are to be loaded into the Index File; the contents of each Target Field should be (individually) loaded into the Index File. Use should be made of the $^ and $~ SELECT operators to convert all characters to upper case and discard any Target Fields that contain only spaces. 9.3.4 Setting up the Index File - SELECT and SORTER may now be used to setup the Index File in readiness for INSPECT. In most applications, a menu item is made available to the database updater (ie the person who uses DBSEDT) to invoke SELECT and SORTER via a CUSP Chain to regenerate the Index File; this allows the updater to regenerate the Index File when significant changes are made without having to bother the application manager. DBSMNG: Database Management Package 72 Chapter 10 REPORT: Report Writer This chapter details the operation and use of the DBSMNG REPORT CUSP that allows reports incorporating information from DBSMNG databases to be quickly and efficiently produced. The layout of a report is determined by a simple FRM type Format File. REPORT is normally invoked as part of a CUSP Chain, after SELECT and SORTER have determined which records (and in what order) are to be used to produce the report. While simple to use, REPORT is more than adequate for producing hard copy output in a majority of applications, including form letters, labels and tabular reports. The CUSP supports interaction with the terminal user automatic pagination and dating BCD string operations conditional paging conditional sections Example FRM files are included at the end of this chapter and show how different types of reports can be produced. 10.1 Background REPORT is designed to be used in a CUSP chain in conjunction with the SELECT and SORTER CUSP. SELECT, SORTER and REPORT exchange information via a SFL file. The application manager uses a FRM type file to indicate to REPORT how the report is to be laid out. This section contains a summary of FRM and SFL type files. 10.1.1 FRM File - REPORT is targeted for a specific application by means of a FRM file. The FRM file determines exactly how the report is to be laid out. It determines the data that is the be extracted from each database record and how it will be laid out on the report page. FRM files are sequential ASCII stream files created by the application manager using an RT-11 editor (eg EDIT, KED or TECO). The physical End of File (EOF), a NULL or a control-Z can be used to indicate the logical EOF. Certain characters (viz Uparrow, Escape and Underline) have special significance in FRM files. 10.1.1.1 Uparrow Sequence - The uparrow character (^) causes the next character in the FRM file to be converted into a control character (eg ^J is equivalent to a line feed). DBSMNG: Database Management Package 73 10.1.1.2 Escape Sequence - The Escape character ($) causes the next character in the FRM file to be converted into a special function code (eg $A is equivalent to a special function code of 301 octal). 10.1.1.3 Underline Sequence - The underline character (_) inhibits the conversion or interpretation of the next character in the FRM file (eg _^ is the only way to prevent the uparrow from being interpreted as part of an uparrow sequence). 10.1.1.4 ^@ Repeat Block Sequence - The sequence of characters ^@ in a FRM file is used to indicate the start of a repeat block. The repeat block is terminated using the same two characters. The format of a repeat block is [^@][X][....][^@] where [....] represents the sequence of characters to be repeated and [X] is a single character whose ASCII value is used as the repeat count. For example, the sequence ^@0ABC^@ is equivalent to writing the characters ABC forty-eight times (since the ASCII code for "0" is forty-eight). Repeat blocks are useful when a report contains "identical" lines. 10.1.1.5 TPLREP CUSP - TPLREP can be used to generate a FRM file for REPORT directly from the card image file used by TPLMAC to generate the template. As explained above, REPORT is targeted for a specific application by means of a FRM file. The FRM file is used by REPORT to determine the data that is to be extracted from the database and how it will be laid out on the page. The TPLREP cusp will generate a FRM file for REPORT that will extract all data from a given record and lay it out (approximately) as it appears on the screen when DBSEDT or INSKEY is used. 10.1.1.6 SFL File - SFL files are used to transfer data between DBSMNG CUSPs. An SFL file consists of a 512-byte header followed by a number of equal length records. Data is typically loaded into such a file by means of the SELECT CUSP, optionally sorted using SORTER and then used as input to REPORT. 10.1.1.6.1 Header Block - Block 0 of an SFL file is used as a header block. The first word of the header holds the number of bytes per data record. The second word holds the number of data records in the file. The rest of block 0 is available for general data interchange (eg SELECT passes a copy of its variables to REPORT through this area). 10.1.1.6.2 Data Blocks - The remaining blocks in an SFL file are used to hold data records. Each data record consists of a two-byte tag followed by the record data. DBSMNG CUSPs use the tag to hold the "rn" for the database entry from which the following data was obtained. DBSMNG: Database Management Package 74 10.1.1.6.3 Usage - An SFL file is used to pass information between DBSMNG CUSPs. It is a vehicle for passing extracted data and a tag to identify the database record from which the data originated. An SFL file can be used in a number of ways. The REPORT CUSP uses the tag information passed in the SFL file to determine which records in the database are to be included in the report and the order in which they are to appear. SELECT is normally used to load into the SFL file the data from database records that is to be used to determine the order in which the records are to appear in the report. SORTER is then used to sort the data. REPORT then uses the tag data associated with each SFL file entry to determine which database records are to be included in the report. 10.1.1.6.4 Entry Size - An SFL file contains a number of equal length entries. The number of bytes in an entry is termed the Entry Size. 10.2 Invoking REPORT REPORT may be invoked directly via a command file or as part of a CUSP chain. Typical applications invoke REPORT as part of a chain via the MENU program as this minimizes the information that must be supplied by the user. MENU allows responses to the REPORT prompts to be passed as Core Common Commands. The uncustomized version of REPORT will prompt the user to supply the following information. 10.2.1 Prompt: DB Template - In response to this prompt, the name of the file that holds the TEMPLATE for the database must be entered. 10.2.2 Prompt: Format File - In response to this prompt, the name of the file that specifies the layout of the report should be entered. 10.2.3 Prompt: Sorter File - In response to this prompt, the name of the SFL file that determines the records to be used to generate the report should be entered. 10.2.3.1 Omitting Sorter File - A SFL file is normally used to control which records are to be included in a report. SELECT and SORTER are used to setup a SFL file with the records selection and ordering information. In some cases (eg when only one record is needed), if the use of a SFL file is not considered warranted, the user may simply press [RETURN] in response to the "Sorter File" prompt. Omitting the SFL file name causes REPORT to prompt the user and obtain the number of the records to be included in the report from the keyboard. DBSMNG: Database Management Package 75 10.2.4 Prompt: Output File - In response to this prompt, the name of the RPT file to which the generated report is to be sent should be entered. This file termed the primary output stream. 10.3 REPORT Operational Overview Three files, of type SFL, FRM and RPT are used by REPORT. The SFL file determines which database records are included in the report. The FRM file determines the layout of the report which is sent to the RPT file. This section describes how these files are used by REPORT and provides an overview of how the program works. 10.3.1 REPORT Cycle - REPORT operates in a cyclic manner that repeats each time the end of the FRM file is reached. A cycle simply involves transferring characters from the input to the output stream, which are normally the FRM and RPT files respectively. The REPORT Cycle is shown below. Start: Gain access to all files Repeat until all records in SFL file processed Rewind FRM file Assign FRM file as input stream Assign RPT file as output stream Repeat until hit EOF on input stream Input byte Execute if command byte Output byte Close Files Exit 10.3.2 Rewinding the FRM File - In some cases a Format File may contain initialization instructions that are to be executed only once. In such cases, particularly if the initialization requires user input, rewinding the Format File to the physical start would result in the unwanted execution of the initialization code each cycle. To overcome this problem a special operator may be included in the format file to mark the desired rewind point. 10.3.3 REPORT ($X) Operators - While most characters are transferred directly from the input to the output streams REPORT handles FRM command characters consisting of a two character sequence starting with a dollar symbol (eg $A, $1) specially. Command characters are used to order REPORT to perform special functions. Command character sequence that have been assigned a function (see below) are termed operators. It is via these operators that database information is extracted and formatted, conditional branching implemented and interaction with the terminal user possible. DBSMNG: Database Management Package 76 10.3.4 REPORT Variables - A number of REPORT operators take arguments. REPORT supports a number of "variables" that can optionally be used as arguments. These variables are identified by single characters. 10.3.4.1 ! Primary Files - The primary input and output files (viz FRM and RPT files) can be used as arguments. The exclamation (!) symbol represents the primary files. 10.3.4.2 @ Terminal - The user's terminal can be used as an argument. The at (@) symbol represents the user's terminal. 10.3.4.3 A-Z Memory Variables - Any one of twenty-six memory variables can be used as arguments. These variables are represented by the upper case characters A to Z. 10.3.5 I/O Stream Selection - REPORT inputs a character from the input stream and copies it to the output stream. At the start of each cycle the input and output streams are assigned to the FRM and RPT files respectively. These assignments may be changed during the actual cycle by means of special operators. Valid assignments are Input Stream ! FRM File @ User's Terminal A-Z Memory Variable Output Stream ! RPT File @ User's Terminal A-Z Memory Variable 10.3.5.1 Assign RPT File Output Stream - Assigning the output stream to the RPT file causes subsequent output to be appended to any that was already been sent to the file. 10.3.5.2 Assign Terminal Output Stream - Assigning the output stream to the user's terminal causes subsequent output to appear on the user's terminal. 10.3.5.3 Assign Memory Variable Output Stream - Assigning the output stream to a memory variable causes subsequent output being stored in the variable. Any data previously stored in the variable is lost. Each memory variable is capable of holding 255 characters. 10.3.5.4 Assign FRM File Input Stream - Assigning the FRM file as the input stream causes the next input character to be taken from the FRM file. The FRM file is not rewound. Characters continue to be obtained from where inputting was previously interrupted. 10.3.5.5 Assign Terminal Input Stream - Assigning the user's terminal as the input stream results in program execution being suspended until the user types a line of input terminated by a [RETURN]. The user's input is stored in a dummy memory variable to which the input stream is assigned and program execution resumes. ---NOTE--- DBSMNG: Database Management Package 77 An input prompt should always be issued using the $) operator before the input stream is assigned to the user's terminal. 10.3.5.6 Assign Memory Variable Input Stream - Assigning a memory variable as the input stream causes further input to come from the variable starting from the first character. If the data in the variable is exhausted the input stream is reassigned to the FRM file. 10.3.6 Memory Variable Initialization - When REPORT is invoked memory variables A, B, C, D and E are initialized to NULLs and variables G-Z with data passed to REPORT from SELECT via the SFL file. If the data passed from select is not required these variables can be used as desired. Variable F is loaded with SFL data by REPORT each time the $N (load next record) directive is executed. Care should be taken when using this variable as it is reset. The table below indicates how each variable is initialized. SELECT memory initial variable value comment A NULL B NULL C NULL D NULL E NULL F SFLENT loaded with SFL file entries G Entsiz size of SFL entries H Nument number of SFL entries I N0 select numeric variable J N1 select numeric variable K N2 select numeric variable L N3 select numeric variable M N4 select numeric variable N N5 select numeric variable O N6 select numeric variable P N7 select numeric variable Q N8 select numeric variable R N9 select numeric variable S D0 select date variable T D1 select date variable U D2 select date variable V D3 select date variable W D4 select date variable X L0 select literal variable Y L1 select literal variable Z L2 select literal variable 10.3.6.1 Memory Variable F Initialization - Memory variable F is initialized each time a $N (load next selected record) directive is issued by loading it with the contents of the SFL entry associated with the record. DBSMNG: Database Management Package 78 10.3.7 BCD Binary Coded Decimal - REPORT allows the user to perform simple arithmetic. Numbers used for arithmetic is represented as a series of digits. Such numbers may be preceded by a plus or minus sign, and can included a leading dollar sign and a decimal point followed by two digits. The decimal point and the two digits following it are termed the fractional part of the number. The "whole" part of a BCD number can contain upto 18 digits. 10.4 FRM File Structure The FRM file is divided into three sections. These sections are termed the header, body and trailer. They are delimited by special operators and are meant to correspond to a report page header, body and trailer. At any instance the report writer can be said to be in one of three phases, depending upon which section of the format file is being processed. Some operators are processed differently in each phase. The action taken upon the occurrence of a significant event (ie no more records to process) is also phase dependant. 10.4.1 Header Section - The header section is processed during phase 1. At the start of phase 1 the FRM file is rewound, the I/O streams reassigned to the FRM and RPT files respectively, the page number increased by one and all output suppression flags cleared. During phase 1 characters are copied from the input to the output stream. Phase 1 is terminated when a $\ operator is encountered in the input stream. 10.4.2 Body Section - The body section is processed during phase 2 and directly follows the header section from which it is delimited by a $\ operator. The body of a report page is produced during phase 2 using data extracted from one or more database records. For example, in a component inventory system the names and quantity in stock of related components are normally output on the same page. Operators can be used in phase 2 to load the next record selected for processing, carry out conditional tests (ie if outputting the page on transistors we must ensure that we start a new page if the next record is for diodes) and detect the "all records processed" condition. The body section is terminated by a $* operator. The $* operator can be omitted if an explicit trailer section is not included; in such cases the $\ operator that marks the end of the FRM file terminates the body section. 10.4.3 Trailer Section - The trailer section is processed during phase 3 and is used to produce the page trailer. In some cases a page trailer is not required and an explicit trailer section can be omitted. The trailer section is terminated by a $delimiter which also marks the logical end of the FRM file. The $\ must always appear at the end of the FRM file. DBSMNG: Database Management Package 79 10.4.4 All Records Processed Event Handling - REPORT always terminates execution in phase 1 (ie when processing the header section) if it detects that all selected records have been processed. During phase 2 and 3 REPORT takes no special action if all records have been processed other than setting a flag that results in the $N command (load next selected record) being treated as a NOP. Special operators can be used in phase 2 to detect that all records have been processed and take appropriate action. 10.5 REPORT Operators While characters are transferred directly from the input to the output streams REPORT treats command characters specially. Command characters consist of a two character sequence starting with a dollar symbol (eg $A, $1) and are used to order REPORT to perform functions. Command character sequence that have been assigned a function (see below) are termed operators. It is via these operators that database information is extracted and formatted, conditional branching implemented and interaction with the terminal user possible. Command character sequences that have not been assigned a function are ignored. 10.5.1 Summary of Operators - The tables below summarize the operators and sub-operators supported by REPORT. Table - 10.5.1.1 REPORT Main Operators $B BCD operation - see BCD sub-operators $C comment $D date $E terminate phase if End-of-data $F format operation - see format sub-operators $I input stream selection $M phase termination mode selection $N next record $O output stream selection $P page number $R RULER definition $T test operation - see test sub-operators $< field data $; suppress report file output $: position cursor $) send output to terminal $\ phase delimiter $= terminate phase if variables equal $# terminate phase if variables not equal $* rewind marker in phase 1 $* trailer marker in phase 2 DBSMNG: Database Management Package 80 Table - 10.5.1.2 REPORT $B (BCD) sub-operators + add variable 1 to variable 2 - subtract variable 1 from variable 2 I increment variable D decrement variable Z zero variable F format variable - see BCD format sub-operators Table - 10.5.1.3 REPORT $BF (BCD format) sub-operators < left justify $ display $ sign (specify as _$) + display + sign if value positive - display - sign if value negative . display decimal point ^X number characters in string (^A=1, ^T=20) $$ format operation terminator Table - 10.5.1.4 REPORT $T (TEST) sub-operators E mark end of conditional section R mark end of conditional section and repeat = commence conditional skip if variables equal # commence conditional skip if variables not equal - commence conditional skip if BCD variable < 0 0 commence conditional skip if BCD variable = 0 + commence conditional skip if BCD variable > 0 Table - $F (format) sub-operators' ${ trim trailing spaces $} trim leading spaces $) trim leading/trailing spaces $( trim leading/trailing spaces $] right justify $[ left justify $| centre text $^ Force upper case $~ NULL blank string $:X set length to X (^A=1, ^T=20) $+X append X spaces (^A=1, ^T=20) $-X delete all occurrences of character X $> leave string as is $< trim trailing spaces (obsolete) > leave string as is (obsolete) < Trim trailing spaces (obsolete) DBSMNG: Database Management Package 81 Table - 10.5.1.5 REPORT $< (field data) sub-operators ${ terminates name, trim trailing spaces $} terminates name, trim leading spaces $) terminates name, trim leading/trailing spaces $( terminates name, trim leading/trailing spaces $] terminates name, right justify $[ terminates name, left justify $| terminates name, centre text $^ terminates name, Force upper case $~ terminates name, NULL blank string $:X terminates name, set length to X (^A=1, ^T=20) $+X terminates name, append X spaces (^A=1, ^T=20) $-X terminates name, delete all occurrences of character X $> terminates name, leave string as is $< terminates name, trim trailing spaces (obsolete) > terminates name, leave string as is (obsolete) < terminates name, Trim trailing spaces (obsolete) 10.5.2 $B BCD Operator - The $B operator is used to perform a BCD operation. The format of the $B command is $B[X]{[A1]...[An]} where $B flags the start of a BCD operation X is the BCD sub-operator (see below) An are optional arguments 10.5.2.1 + BCD addition sub-operator - The + sub-operator is used to perform BCD addition. The command syntax is $B+12 where 1 and 2 represent two variables (ie A, B, C, ... Z). The command results in the sum of variable 1 and variable 2 being stored in variable 2. 10.5.2.2 - BCD subtraction sub-operator - The - sub-operator is used to perform BCD subtraction. The command syntax is $B-12 where 1 and 2 represent two variables (ie A, B, C, ... Z). The command results in variable 1 being subtracted from variable 2. The difference is stored in variable 2. 10.5.2.3 I BCD increment sub-operator - The I sub-operator is used to increment the BCD value of a variable by one. The command syntax is $BI1 where 1 represents a variable (ie A, B, C, ... Z). The command causes the value stored in the specified variable being incremented by one. DBSMNG: Database Management Package 82 10.5.2.4 D BCD decrement sub-operator - The D sub-operator is used to decrement the BCD value of a variable by one. The command syntax is $BD1 where 1 represents a variable (ie A, B, C, ... Z). The command causes the value stored in the specified variable being decremented by one. 10.5.2.5 Z BCD zero sub-operator - The Z sub-operator is used to set to zero the BCD value of a variable The command syntax is $BZ1 where 1 represents a variable (ie A, B, C, ... Z). The command causes the value stored in the specified variable being set to 0. 10.5.2.6 F BCD format sub-operator - The F sub-operator is used to format (prior to displaying) the BCD string stored in a variable. The command syntax is $BF1[<][+][-][$][.][X]$$ where 1 represents a variable (ie A, B, C, ... Z) < is a optional argument, which if present causes the string to be left justified + is a optional argument, which if present enables the display of a leading plus sign - is a optional argument, which if present enables the display of a leading minus sign $ is a optional argument, which if present enables the display of a leading dollar sign . is a optional argument, which if present enables the display of the decimal point and the two fractional digits X is a optional argument, which if present determines the number of character positions that the string is to occupy. If X is omitted the string is reduced to the smallest possible size. $$ terminates the optional arguments and must alwa be included. The X argument, if present, determines the number of characters in the resulting string. X is specified by entering the character whose ascii value is equivalent to the desired length (ie 1 is represented bt ^A, 2 by ^B, 3 by ^C etc). The maximum allowed string length is 24. The table below shows the effect of formatting on the BCD string "-100.01" which is stored in variable B. Command Result $BFB$$ |100| $BFB_$$$ |$100| $BFB.$$ |100.01| $BFB-$$ |-100| $BFB+$$ |100| $BFB^K$$ | 100| $BFB-_$.^K$$ | -$100.01| $BFB-_$.^K<$$ |-$100.01 | DBSMNG: Database Management Package 83 10.5.3 $C Comment - The $C operator causes all input upto and including the next linefeed to be ignored. The $C operator is used to include inline comments in a report format file. 10.5.4 $D Dating - The $D operator is used to output the current system date. The $D operator is replaced by an eight character string that represents the current system date (format DD-MM-YY). 10.5.5 $E Terminate if all Records Processed - The $E operator is used to terminate processing if all records have been processed and is normally used directly after a $N operation. (If a $N directive is issued in the body section and no more records have been selected for processing the directive is ignored and processing of the body section continues. The $E operator can be used after the $N to terminate execution of the body section if no more records are available.) The actual manner in which the body section is terminated depends on the termination mode currently enabled (see $M operator). 10.5.6 $F Format Operator - The $F operator is used to format the contents of a variable. The command syntax is $F[variable][sub-operator] where [variable] is one of the memory variables A to Z and [sub-operator] is one of the operators from the table below. ${ trim trailing spaces $} trim leading spaces $) trim leading and trailing spaces $( trim leading/trailing spaces $] right justify $[ left justify $| centre $^ force upper case $~ NULL blank string $:X set length to X (^A=1, ^T=20) $+X append X spaces (^A=1, ^T=20) $-X delete all occurrences of character X $> leave string as is $< trim trailing spaces (obsolete) > leave string as is (obsolete) < Trim trailing spaces (obsolete) The effect of the various operators on the string " Testing ... " stored in variable B is shown in the table below. Command Result $FB${ | Testing ...| $FB$} |Testing ... | $FB$) |Testing ...| $FB$( |Testing ...| $FB$] | Testing ...| $FB$[ |Testing ... | $FB$| | Testing ... | $FB$^ | TESTING ... | $FB$~ | Testing ... | $FB$:^X | Testing ... | $FB$+^C | Testing ... | DBSMNG: Database Management Package 84 $FB$-. | Testing | $FB$> | Testing ... | $FB$< | Testing ...| $FB> | Testing ... | $FB< | Testing ...| 10.5.7 $I Switch Input Stream - The $I operator is used to switch the input stream to the variable whose identifier directly follows the operator. The valid variable identifiers are @ for terminal, ! for the FRM File and the letters A-Z for the 26 memory variables. 10.5.8 $M Select Termination Mode - The $E, $= and $# operators can be used to conditionally terminate output to the report file. By default, report file output is re-enabled when execution of the body of the FRM file is complete and the header section is restarted. The $M operator can be used to modify how the body section is to be terminated. The command syntax is $MD where D is one of the digits 1, 2, 3 or 4. 10.5.8.1 M1 Termination Mode - Termination mode 1 is the default mode, and results in all output being disabled until the header section is restarted. While output is disabled all $B (BCD) operations and $N commands are ignored. Processing of the record being processed when output was disabled is continued when output is re-enabled. 10.5.8.2 M2 Termination Mode - Termination mode 2 results in all output being disabled until a trailer section (flagged by a $* operator) is encountered or until the header section is restarted. While output is disabled all $B (BCD) operations and $N commands are ignored. Processing of the record being processed when output was disabled is continued when output is re-enabled. Mode 2 is used when a trailer must be included at the bottom of each report page. 10.5.8.3 M3 Termination Mode - Termination mode 3 results in all output being disabled until the header section is restarted. While output is disabled all $B (BCD) operations and $N commands are ignored. The record being processed when output was disabled is discarded and processing of a new record is commenced when output is re-enabled. 10.5.8.4 M4 Termination Mode - Termination mode 4 results in all output being disabled until a trailer section (flagged by a $* operator) is encountered or until the header section is restarted. While output is disabled all $B (BCD) operations and $N commands are ignored. The record being processed when output was disabled is discarded and processing of a new record is commenced when output is re-enabled. Mode 4 is used when a trailer must be included at the bottom of each report page. DBSMNG: Database Management Package 85 10.5.9 $N Next Record - The $N operator is used to load the next database record that has to be processed. This operator should only be used in the body section. If all records have been processed the "end of records event" flag is set and subsequent $N operators are ignored. ---NOTE--- The $N operator uses the SFL file specified when the program was started to determine which record is to be loaded next. If no SFL file was specified when the program was started, the $N operator results in the issuing of a prompt on the users terminal. The user must then entered the record number for the next record to be processed. The program can be terminated by entering a record number of 0. 10.5.10 $O Output Stream - The $O operator is used to switch the output stream to the variable whose identifier directly follows the operator. The valid variable identifiers are @ for terminal, ! for the RPT File and the letters A-Z for the 26 memory variables. 10.5.11 $P Pagination - The $P operator is replaced by a five digit page number. The page number is incremented at the start of each cycle (ie when the FRM file is rewound). 10.5.12 $R Define Ruler - TAB characters can be used in the FRM file to advance the the next tab stop on the report page. Tab stops are initially set at every eighth character position. The $R operator is used to define new tab stops. The command format is $R....T.....T..........T.. The character directly after the $R operator represents column 1 of the report page. The ruler is made up of dots and Ts. The Ts mark the desired tab stops. 10.5.13 $T Test Operator - The $T operator can be used to conditionally include/exclude sections from a report file. The command syntax is $T [sub-operator] [args] [FRM file data] $T[terminator] where [sub-operator] determines the type of test operation to be performed, [args] represents optional arguments, [FRM file data] is the section of the FRM file that is to be conditionally executed and $T[terminator] marks the end of the conditional section. While a conditional skip is in effect, any output sent to the report file is discarded and the $N and all BCD operators are ignored. DBSMNG: Database Management Package 86 10.5.13.1 $TE End of Section Marker - The $TE command is used to mark the end of a conditional section. The effect of the $TE command is to re-enable output that had be disabled by a previous $T=, $T#, $T-, $T0 or $T+ command. 10.5.13.2 $TR Special End of Section Marker - The $TR command is used to mark the end of a conditional section. The effect of the $TR command is to re-enable output that had be disabled by a previous $T=, $T#, $T-, $T0 or $T+ command. If output had been disabled by a $T? command and a FRM file repeat block is being executed (ie ^@X...^@), the $TR command results in the repeat block count being incremented by one. 10.5.13.3 $T= Skip if Variables Equal - The syntax of this command is $T=12.....$TE or $T=12.....$TR, where 1 and 2 represent two memory variables (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the contents of variable 1 and 2 are equal. 10.5.13.4 $T= Skip if Variables Equal - The syntax of this command is $T=12.....$TE or $T=12.....$TR, where 1 and 2 represent two memory variables (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the contents of variable 1 and 2 are equal. 10.5.13.5 $T# Skip if Variables Not Equal - The syntax of this command is $T#12.....$TE or $T#12.....$TR, where 1 and 2 represent two memory variables (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the contents of variable 1 and 2 are not equal. 10.5.13.6 $T- Skip if Variable Less Than 0 - The syntax of this command is $T-1.....$TE or $T-1.....$TR, where 1 represents a memory variable (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the BCD number stored in variable 1 has a value less than 0. 10.5.13.7 $T0 Skip if Variable is 0 - The syntax of this command is $T01.....$TE or $T01.....$TR, where 1 represents a memory variable (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the BCD number stored in variable 1 has a value of 0. 10.5.13.8 $T+ Skip if Variable Greater Than 0 - The syntax of this command is $T+1.....$TE or $T+1.....$TR, where 1 represents a memory variable (A to Z). Report file output is disabled until the $TE or $TR operator is encountered if the BCD number stored in variable 1 has a value greater than 0. 10.5.14 $< Extract Field Data - The $< is used to extract data from the database record currently being processed. The command format is $< {field name} {Terminator} where {field name} is the name of the database field whose contents are to be included in the report and {Terminator} is one of the terminating characters listed below. DBSMNG: Database Management Package 87 10.5.14.1 Field Name - The name of the database field from which the data is to be extracted must directly follow the $< operator. If the name specified is not a valid field name the invalid name is displayed. 10.5.14.1.1 Field Name in Variable - It may be necessary to extract data from the database field whose name is stored in a variable. This can be performed using the following command $<$IV[terminator] where V represents the variable that holds the field name. 10.5.14.2 Extracted Data: Numeric Fields - data extracted from a numeric field is inserted in the report as a five digit number. Leading zeros are converted to spaces. 10.5.14.3 Extracted Data: Date Fields - data extracted from a date field is inserted in the report as three two digit fields separated by the - character representing days, months and years. 10.5.14.4 Extracted Data: Ascii Fields - data extracted from a ascii field is inserted in the report as it appears in the database record. 10.5.14.5 Terminators - The $< command requires a terminator to mark the end of the field name. A variety of terminators are available. These terminators are similar to the $F (format) sub-operators, and allow the data extracted from the database field to be formatted in a variety of ways prior to being included in the report. The table below shows the effects of the available terminators when the $< operator is used to extract data from the field ADD that holds the string ' 1 NEE ST., ' Command Result $<ADD${ | 1 Nee St.,| $<ADD$} |1 Nee St., | $<ADD$) |1 Nee St.,| $<ADD$( |1 Nee St.,| $<ADD$] | 1 Nee St.,| $<ADD$[ |1 Nee St., | $<ADD$| | 1 Nee St., | $<ADD$^ | 1 NEE ST., | $<ADD$~ | 1 Nee St., | $<ADD$:^O | 1 Nee St., | $<ADD$+^C | 1 Nee St., | $<ADD$-. | 1 Nee St, | $<ADD$> | 1 Nee St., | $<ADD$< | 1 Nee St.,| $<ADD> | 1 Nee St., | $<ADD< | 1 Nee St.,| DBSMNG: Database Management Package 88 10.5.15 $; Suppress RPT Output - The $; operator prevents output to the RPT file. RPT output is reenabled when the linefeed that follows a second $; operator is encountered. (The operator toggles an internal flag, that when set, prevents RPT file output. A second $; operator clears the flag and RPT file output is reenabled as soon as a linefeed is encountered.) This operator effects only the RPT file and does not effect output to the terminal or memory variables. This operator should be used to delimit sections of the FRM file not actually used for producing report output. 10.5.16 $: Absolute Position - The $: operator is used to position the cursor at the column represented by the ascii value of the character that follows the operator. The commands $:^A and $:0 position the cursor at columns 1 and 48 respectively. Column 1 represents the left most character position of a report page. An absolute position command is ignored if the specified column has already been passed. 10.5.17 $) Output to Terminal - The $) operator can be used to output a message to the terminal. The command syntax is $)....$$ and results in all characters between the $) and $$ being output on the terminal. 10.5.18 $\ Section Delimiter - The first $\ operator in the FRM file indicates the end of the header section and the start of the body section. The second $\ operator indicates the end of the FRM file. 10.5.19 $= Terminate if Variables Equal - The $= operator is used to conditionally terminate execution of the body of the FRM file. The command syntax is $=12 where 1 and 2 are two memory variables (ie A to Z). Report file output will be disabled (see $M operator above) if the contents of variable 1 and 2 are equal. 10.5.20 $# Terminate if Variables Not Equal - The $# operator is used to conditionally terminate execution of the body of the FRM file. The command syntax is $=12 where 1 and 2 are two memory variables (ie A to Z). Report file output will be disabled (see $M operator above) if the contents of variable 1 and 2 are not equal. DBSMNG: Database Management Package 89 10.5.21 $* Section Delimiter - A $* operator in the header section is used to mark the FRM file rewind point and should be used to prevent once-only initialization instruction from being executed each time the FRM file is rewound. A $* operator in the main section of the FRM file is used to mark the end of the body section and the start of the trailer section. 10.6 Example FRM Files Below are a number of example Format Files that can be used to extract data from a component data base and used to produce reports. It is hoped that these will clarify the meaning of the command characters, and aid the user in designing format files suited to his own application. Note that is assumed that one component is stored per record and that the records are made up of the following fields field contents name Code component identification code Type type of component (ie diode, resistor, switch) Instock number in stock $; This frm file will output the Code, Type and Instock data for each component. Remember that the dollar-semicolon delimiters prevent this section from being included in the report! $; $\$N$E $<CODE> $<TYPE> $<INSTOCK> $\ $; This frm file will output the Code, Type and Instock data. Each report page is properly titled and will contain 50 lines of data. $; ^L Component Inventory Stock Report Page $P ================================ Date $D $\ ^@2$N$E $<CODE> $<TYPE> $<INSTOCK> ^@ $\ DBSMNG: Database Management Package 90 $; This frm file will output the Code, Type and Instock data. Each report page is properly titled and will contain 50 lines of data. Only components of the same type are displayed on a page. $; ^L Component Inventory Stock Report Page $P ================================ Date $D $\ $; $N$E see if have another record $OA$<TYPE>$O! store type of 1st compnt in A $; $<CODE> $<TYPE> $<INSTOCK> ^@1$; setup to repeat 49 times $N$E see if have another record $OB$<TYPE>$O! store type of n'th compnt in B $#AB terminate if type n <> 1st $; $<CODE> $<TYPE> $<INSTOCK> ^@$\ $; This frm file will output the Code, Type and Instock data. Each report page is properly titled and will contain 50 lines of data. Only components of the same type are displayed on a page. A trailer is included at the bottom of each page indicating the type of components on each page. $;$M2 ^L Component Inventory Stock Report --------------------------------------------------------------------------- $\ $; $N$E $OA$<TYPE>$O! $; $<CODE> $<TYPE> $<INSTOCK> ^@1$; $N$E $OB$<TYPE>$O! $#AB $; $<CODE> $<TYPE> $<INSTOCK> ^@ $* --------------------------------------------------------------------------- Page $P Component Type: $IA $\ DBSMNG: Database Management Package 91 $; This report allows the user to enter the name of the person to whom it is to be sent. $O@Enter name of person to whom report is to be sent: $O! $OT$I@$O! $M2; $*^L Component Inventory Stock Report Attention: $IT --------------------------------------------------------------------------- $\ $; $N$E $OA$<TYPE>$O! $; $<CODE> $<TYPE> $<INSTOCK> ^@1$; $N$E $OB$<TYPE>$O! $#AB $; $<CODE> $<TYPE> $<INSTOCK> ^@ $* --------------------------------------------------------------------------- Page $P Component Type: $IA $\ $; Assuming that the following SELECT FRM file was used $)Enter name of sort field: $$ $(L0 $*$T [st].EQ.[AC] $$ $L @L0 $^ $$ $E then this REPORT FRM file can be used to produce a report sorted in the desired order. ^L Component Stock Report sorted in Order of $IX $\ ^@2$N$E $<$IX> $<CODE> $<TYPE> $<INSTOCK> ^@$\ DBSMNG: Database Management Package 92 Chapter 11 BACKUP: Backup Utilities DBSMNG provides four special CUSPs that support incremental archiving of database records, collectively referred to as BACKUP. BACKUP is essential in applications using a database with many, often modified, records. The four CUSPs are cusp Function BACKUT creates the backup file BACKUP copies selected records to the backup BACKUN copies records from the backup file BACKUF creates a special SELECT Format File 11.1 Function The BACKUP utility was divided into four programs to conserve memory usage and to allow the application manager the ability of limiting the user access to potentially dangerous functions. This section describes the function of each of the BACKUP CUSPs. 11.1.1 BACKUT- BACKUT is used exclusively for creating and initializing the file that is to be used to archive the records. The is a SFL type file with additional information stored in the Header section. This CUSP is normally reserved for use of the application manager only. 11.1.2 BACKUP- The BACKUP program can be run whenever desired to copy selected records into the backup file. A SFL type file, as generated by SELECT, is used by BACKUP to determine which records are to be archived. Each time the program is run it appends the new data to that already in the backup file, thereby allowing for any of the archived versions of a record to be recovered. BACKUP outputs a message each time it is run that tells the user how much room is left in the backup file. 11.1.3 BACKUF - A number of criteria can be used to determine which records are to be archived at any time; the most often used is to backup any records created or modified since the last backup date. BACKUF is provided to create a special Format File that will cause SELECT to select all records that have an 'edit date' (viz 'ed' field contents) that indicates that they were modified since the last backup took place. DBSMNG: Database Management Package 93 11.1.4 BACKUN - The BACKUN program is run when database records have been destroyed and it is necessary to restore the database from a backup file. When run, BACKUN simply transfers all record data from the backup file into the database record from which it originally came. 11.1.5 CUSP Chaining - All four programs can be invoked as part of a CUSP Chain, and can be passed commands from MENU. Normally only BACKUP (and BACKUF) would be made available to the user as part of a CUSP Chain activated from a menu. BACKUT and BACKUN should be run only by the application manager. 11.2 Information Required Upon activation the CUSPs will prompt for the following items of information. Note that not all of the CUSPs require all the items listed below; if a particular CUSP is invoked from MENU, some of this data may be passed as Core Common Commands. DB Template This question is only asked if the user's database template was not linked in when the program was linked. In response to this question the user must specify the name of the file that contains the template (in .SAV) format for the user database. Backup File In response to this question, the user must supply the name of the file that is used to hold the backup data. This is a specially formatted file, and is described in detail below. Select File In response to this question, the user must supply the name of the SELECT FORMAT file that determines which records are to be processed; such a file is created by the SELECT cusp, which allows the user to test records for suitability for inclusion in a given operation. Format File In response to this question, the user supplies the name of a file into which a series of commands which will cause SELECT to select all database records that have been created or edited since BACKUP was last run. 11.3 Backup File The key to the operation of the backup package is a specially formatted file, called the BACKUP file. The BACKUT program must be run first to create such a file. BACKUT allows the user to specify the name that is to be given to the file, and the maximum number of records that may be archived in it. The backup file consists of a one block header and a number of data blocks. Backup entries (consisting of a two word header and data from database records) are stored in the data blocks. Note that these backup entries are concatenated one after the other, without any wasted space in order to maximize disk utilization. The format of a backup file is as follows Block Byte Variable data function/usage number offset name type 0 0 Entsiz I*2 number bytes per backup entry 2 Entnum I*2 number backup entries used DBSMNG: Database Management Package 94 40 DBSNME B*6 six letter database name 50 BCKDAT I*2 date of last backup 100 OWNER R*6 must be rad50 string BACKUP 104 Filsiz I*2 number of blocks in file 106 CHECK R*3 must be rad50 string WEN=Write Enable 110 BLK I*2 Block number for start of free space 112 BYT I*2 Offset in BLK to start of free space 1 0 ENT#1 First backup entry . . n m ENT#k last entry written to date BLK BYT free start of free data area The backup entries in turn consist of a two word header and the data from a database record. The first word is the number of the record from which the data was obtained, and the second the date upon which the backup was done. 11.4 Using BACKUP in an Application A typical scenario for the use of the backup package starts when the database manager decides that the data being stored in a database must be protected against hardware malfunction or accidental erasure. This can only be done by using a combination of incremental and complete backup strategies A given intervals (dependant on the amount of new data being entered) a complete backup of the database is performed by copying the PAR, KEY and DAT files for the database onto a backup medium; naturally a number of backup volumes should be used in cyclic order to protect against an undetected corruption working its way into the backup set. As a complete backup, particularly for a large database, can be time consuming, the quicker and easier to use incremental backup system should be used to backup data that is newly entered or changed between the complete backup times. To do this, the application manager must follow the steps outlined below BACKUT must be run to create a backup format file. If possible, this file should be placed on a separate volume to that which holds to database (to protect against a head crash etc), and should be large enough to hold all records that will be created or edited before the next total backup time. The BACKUP program can then be run at any time (say once a day or after busy editing session) to backup all newly created or edited records. Naturally while the manner via which BACKUP is invoked is application specific, it is best to include it as a MENU option if possible. The suggested method is to setup a MENU option that invokes SELECT to select all database records whose edit dates (ie [ed] field) are greater than a given date, and then to invoke BACKUP to backup these selected records. If this is done, incremental backup can then be carried out by users, without bothering the database manager. Note that BACKUP automatically displays how full the backup file is each time it is run, and that a suitable warning message telling the user to inform the database manager is output whenever the backup file becomes over 80% full. DBSMNG: Database Management Package 95 In the event of a database corruption, BACKUN may then be run by the system manager to update the last complete backup file set and thereby restore the database. Naturally, a new incremental backup file should be started whenever a complete backup is performed; the old file may either be archived or deleted. The only point requiring special attention when setting up a incremental backup option in the menu is deciding how records will be selected for backup. Methods available are to allow the user to specify a date, and to backup all records that have an [ed] date later than that, or to use BACKUF to create a select command file that will cause all records that were created or edited on or after the date of the last backup to be selected for backup. Below is provided an example MENU and SELECT command files that may be used to implement incremental backup via the user menu. DBSMNG: Database Management Package 96 11.5 Example MENU and SELECT Format Files ______________________________________________________________________________ Demo Menu for Backup Package 1 Backup all records that have been created or edited since last backup was done 2 Backup all records that have been created or edited today 3 Backup all records that have been created or edited since a user specified date $* $1 $@LB:BACKUF$$ ; chain to BCKUF $IBACKUP.DAT$$ ; name of backup file $ISELECT.CMD$$ ; name for SELECT command file $CLB:SELECT$$ ; chain to SELECT $IDBSTMP$$ ; name of database template $ISELECT.CMD$$ ; name of selection command file $ISELECT.DAT$$ ; file in which to store selections $I0$$ ; no maximum record length $CLB:BACKUP$$ ; invoke backup $IDBSTMP$$ ; name of database template $IBACKUP.DAT$$ ; name of backup file $ISELECT.DAT$$ ; file of selected records $CLB:MENU$$ ; pass control back to menu $IMENU.FRM$$ ; name of menu format file $! $2 $@LB:SELECT$$ ; chain to SELECT $IDBSTMP$$ ; name of database template $ISELECT.002$$ ; name of selection command file $ISELECT.DAT$$ ; file in which to store selections $I0$$ ; no maximum record length $CLB:BACKUP$$ ; invoke backup $IDBSTMP$$ ; name of database template $IBACKUP.DAT$$ ; name of backup file $ISELECT.DAT$$ ; file of selected records $CLB:MENU$$ ; pass control back to menu $IMENU.FRM$$ ; name of menu format file $! $3 $@LB:SELECT$$ ; chain to SELECT $IDBSTMP$$ ; name of database template $ISELECT.003$$ ; name of selection command file $ISELECT.DAT$$ ; file in which to store selections $I0$$ ; no maximum record length $CLB:BACKUP$$ ; invoke backup $IDBSTMP$$ ; name of database template $IBACKUP.DAT$$ ; name of backup file $ISELECT.DAT$$ ; file of selected records $CLB:MENU$$ ; pass control back to menu DBSMNG: Database Management Package 97 $IMENU.FRM$$ ; name of menu format file $! $* ------------------------------------------------------------------------------ The files SELECT.002 and SELECT.003 are as follows ; File SELECT.002 that causes all records with an edit date equivalent to the ; present system date to be selected $* $T [ed].EQ.D4$$ $L [ed] $$ $E ; file SELECT.003 that causes all records with an edit date equal to or ; greater than that specified by the user to be selected ; $)Enter date : $$ $?D1 $* $T [ed].GE.D1$$ $L [ed] $$ $E DBSMNG: Database Management Package 98 CHAPTER 12 Other CUSPs This chapter describes the simpler DBSMNG CUSPs. These CUSPs are included in the distribution kit primarily for the benefit of the application manager. 12.1 CMDDSP: Dispatch Command to Monitor The CMDDSP CUSP allows a command to be passed to the RT-11, RSTS/E or TSX-plus keyboard monitor. The CUSP can only be invoked via MENU and expects Core Common to hold the command that is to be passed. The CUSP is normally used to log a user off or to start up a control file. The following example menu shows how CMDDSP can be used. Student Training System Master Menu *********************************** 1 Terminate Session 2 Initialize Work Area $* $1 $@LB:CMDDSP$$ $IBYE$$ $! $2 $@LB:CMDDSP$$ $I@INITLZ.COM$$ $! $* 12.2 DBSCPY: Copy Database The DBSCPY CUSP is used to copy the contents of one database into another. DBSCPY copies the contents of a record in the old database into its equivalent record in the new database. DBSCPY is useful when the order of the fields that make up a record has to be changed, when the 'type' or 'size' of a field has to be changed, when new fields have to be added or when the size of a database has to be increased. The suggested procedure is summarized below. 1) Protect the TEMPLATE and database files for the database that is to be copied to minimize the probability of accidently losing data. 2) Create a TEMPLATE for the new database and invoke DBSMAK to create the database files. DBSMNG: Database Management Package 99 3) Invoke DBSCPY to copy the old database into the new database. DBSCPY will request that the names of the TEMPLATE files for the old and new databases be entered. DBSCPY will abort the copy operation if the new database already contains data. 4) Use DBSEDT to inspect a number of records in the new database. If the records look ok the operation has been successful and the old database files can be archived. 12.3 DBSMAK: Make Database The DBSMAK CUSP is used to create and initialize the three (DAT, KEY and PAR) database files. When invoked the program requests that the name of the TEMPLATE file for the database be entered. The program will then proceed to create the files DBS:XXXXXX.PAR, DBS:XXXXXX.KEY and DBS:XXXXXX.DAT. 12.4 DBSSTS: TEMPLATE Disassembler The DBSSTS CUSP disassembles a TEMPLATE. When invoked it requests that the user enter the name of the TEMPLATE and an output file. The program will then produce an output file that identifies the fields and their attributes. 12.5 DBSTPL: Template Compiler The DBSTPL CUSP is used to create a Template file (ie a .MAC file that contains a 1 line declaration per record field) from a card image. Refer to chapter 3 (Setting up a Database - The Easy Way) for further details. 12.6 FINDER: Find Matching Records in SFL File The FINDER CUSP is used to print all entries in a SFL file that match a specified target. SELECT is normally used to extract data out of a database and load it into a SFL file. This SFL file is then sorted using SORTER. FINDER can then be used to type out on the user's terminal any entries in the SFL file that match a specified target. The program is used when extremely rapid retrieval of a small fraction of the data stored the database records is needed. DBSMNG: Database Management Package 100 12.7 PURGER: Purge Database Records The PURGER CUSP is used to delete/erase records from a database. Once a record has been purged the database slot allocated to that record is freed and can then be used to hold a new record. SELECT must first be run to select those records that have to be erased. The SFL file produced with SELECT is then used by PURGER to determine which records are to actually be erased. The following SELECT FRM file $T [st].EQ.'DE'$$ $L [rn] $$ $E would select all records marked for deletion and load their record number into a SFL file. PURGER would then be run to actually erase the records. DBSMNG: Database Management Package 101 Chapter 13 DBSFOR: FORTRAN Interface The DBSFOR routine allows the FORTRAN programmer to interface with a DBSMNG database. The FORTRAN statement CALL DBSFOR (ICODE,ARG1,....,ARGn) is used to invoke DBSFOR. The first argument (ICODE) is an opcode that determines the operation to be performed. The following opcodes are supported code function 0 read database record 1 write database record 2 get field contents 3 put field contents 4 read record keys 5 write record keys 6 map field name to key number 7 get number of entries in database 8 get database name 9 select database 10 initialize database 11 return date in internal format 12 convert date to internal format 13 convert date from internal format 14 compare two (internal format) dates 15 initialize database on specified volume 16 envoke form editor 17 initialize interface 18 program exit 19 erase screen 20 input character 21 output character 22 input line 23 output line 24 erase line 25 position cursor 26 initialize core common 27 setup core common chain 28 setup core common input 29 check core common 30 return field attributes 31 set field attributes 32 initialize database, reuse memory This chapter provides a summary of the functions performed by DBSFOR for the FORTRAN programmer. DBSMNG: Database Management Package 102 13.1 Using DBSFOR A FORTRAN program must be linked with the DBSLIB.OBJ library (contained on the distribution kit) to use the DBSFOR routine. The following DCL command LINK MYPROG,LB:DBSLIB or LINK MYPROG,TEMPLT,LB:DBSLIB can be used to accomplish this. The first method of linking should be used when the database template is to be loaded at run time. The second method binds the database template (TEMPLT.OBJ in this case) into the application program at link time. A warning message stating that the global FORM$$ is undefined will be issued by the linker if the first method of linking is used. This warning message should be ignored. 13.2 Accessing a database The DBSFOR module allows the FORTRAN programmer to access DBSMNG databases. Like all other DBSMNG modules, DBSFOR uses a TEMPLATE to gain access to a database. The user may link in the object version of the Template when the application program is built, or use DBSMNG to load the TEMPLATE at run time. While either method may be used, it is suggested that the first option (linking in the Template) be only used in small systems where the extra disk access needed to load the Template is unacceptable. While the rest of this text is addressed to applications that load the TEMPLATE at run time, all facilities described below are available even when the Template is linked in. 13.3 Overheads When using DBSFOR, the programmer must make allowance for the three I/O channels and memory required by the DBSMNG routines and to hold the database TEMPLATEs. The I/O channels are obtained from the FORTRAN OTS, and are used to access the DAT, KEY and PAR database files. To conserve I/O channels the same channels are multiplexed for all databases. 13.4 Multiple database access DBSFOR allows an application program to access a number of databases simultaneously. To do this, the TEMPLATE for each database must be loaded at run time and assigned to an INDEX (in the range 0 to 7). The INDEX number is used to select which database is to be accessed. DBSFOR allows the user to dynamically select any of the loaded databases. Once a database is selected, all DBSFOR operations function on that database until another database is selected. DBSMNG: Database Management Package 103 13.5 Error handling DBSFOR will abort program execution upon detection of an error. An error message will be displayed on the terminal identifying the cause of the error and the FORTRAN statement that had called the routine. 13.6 Channel Purging For historical reasons all open channels will be purged (reset) when a DBSFOR(17) function is performed the first time. To avoid possible difficulties a DBSFOR(17) call should be the first executable statement in the FORTRAN mainline program. 13.7 DBSFOR Calling Sequences The DBSFOR routine allows the FORTRAN programmer to interface with a DBSMNG database. The FORTRAN statement CALL DBSFOR (ICODE,ARG1,....,ARGn) is used to invoke the DBSFOR routine. The first argument (ICODE) is an opcode that determines the operation to be performed. Additional arguments are included if required. This section details the various functions that can be performed. 13.7.1 Function 0: Record Read - the command CALL DBSFOR (0,I) results in the the I'th record of the currently selected database being loaded into memory. Note The first database record is numbered 1. The number for last record in the database can be obtained via the DBSFOR(7) command. 13.7.2 Function 1: Record Write - the command CALL DBSFOR (1,I) results in the record currently in memory being written out to the currently selected database as record I. Note Any changes made to a record do not become permanent until the record is written out to disk. DBSMNG: Database Management Package 104 13.7.3 Function 2: Field Get - the command CALL DBSFOR (2,STR,BUF,ISIZE,ITYPE) results in contents of the field whose name is in STR being returned in BUF. ISIZE and ITYPE are the number of data bytes returned and the field type respectively. Note * This command operates only on the currently selected database and returns the contents of the field for the currently loaded record. * Valid field types (returned in ITYPE) are 1 and 2 for ascii fields and 3 and 4 for two byte integer integer and date fields respectively. * The field name (STR) is passed as a .asciz string (eg 'Address'). * When extracting data from a ascii type field (or when the field type is unknown) BUF is best declared as a logical*1 array and should be adequately dimensioned to ensure that it can accomodate the returned data. 13.7.4 Function 3: Field Put - the command CALL DBSFOR (3,STR,BUF,ISIZE,ITYPE) results in contents of the field STR being updated with the contents of BUF. ISIZE and ITYPE are the field size and type attributes that must be correctly specified. Note * This command operates only on the currently selected database an effects only the memory copy of the record. Modifications do not become permanent until the records is written back to disk. * The ISIZE and ITYPE arguments are included in the command to protect against accidental modification of the wrong field. 13.7.5 Function 4: Read Keys - the command CALL DBSFOR (4,I,IKEYS) results in the 16 (integer) keys associated with record I of the currently selected database being returned in the array IKEYS. Note * DBSMNG maintains 16 integer keys for each recoard. IKEYS should be declared as a sixteen element integer array. * A zero first IKEYS element is used by DBSMNG to indicate that a record is free. 13.7.6 Function 5: Write Keys - the command CALL DBSFOR (5,IKEYS) results in the 16 (integer) keys associated with record I of the currently selected database being changed to IKEYS. Note * DBSMNG automatically regenerates all but the first record keys (from the record data) whenever a record is written to disk. * An application can mark a record as free or in-use by setting IKEYS(1) to zero or non-zero respectively. * The Key Write function should be used with extreme care. DBSMNG: Database Management Package 105 13.7.7 Function 6: Key Number - the command CALL DBSFOR (6,STR,IKEY) returns in IKEY the number of the key (in range 1 to 16) that is assigned to field STR. 13.7.8 Function 7: Database Size - the command CALL DBSFOR (7,I) returns the number of slots/records (ie the ENTRIES) in the currently selected database in variable I. 13.7.9 Function 8: Database Name - the command CALL DBSFOR (8,NAME) returns the six letter name of the currently selected database in the variable NAME. Note NAME should be declared as a LOGICAL*1 array of six elements. 13.7.10 Function 9: Database Select - the command CALL DBSFOR (9,I) selects database I. All subsequent operations will be performed upon the specified database until another is selected. Note A database must be initialized (via a DBSFOR 10, 15 or 32 command) before it can be selected. 13.7.11 Function 10: Database Initialize - the command CALL DBSFOR (10,I,STR) loads, initializes and selects the database whose TEMPLATE is in the file STR. The database is assigned a (user specified) index of I. The index must be in the range 0 to 7 and is henceforth used to select the database. If NAME is not specified, the Template linked in when the program was built is initialized and selected. All databases must be initialized before they can be used. Note * The name of the database TEMPLATE is passed as a .ASCIZ string in STR. The device and extension parts of the TEMPLATE name are optional. The default device and extension are DK and SAV respectively. * The use of this command requires that the database DAT, KEY and PAR files reside on volume DBS. If this is not the case a DBSFOR (15) or (32) command should be used. 13.7.12 Function 11: Return Date (Internal Format) - the command CALL DBSFOR (11,IDATE) returns the current system date in DBSMNG format. 13.7.13 Function 12: To Internal Date Format - the command CALL DBSFOR (12,IDATE,ID,IM,IY) returns in IDATE the date in DBSMNG format equivalent to the day, month and year (to base 1900) counts passed in ID, IM and IY. DBSMNG: Database Management Package 106 13.7.14 Function 13: From Internal Date Format - the command CALL DBSFOR (13,IDATE,ID,IM,IY) converts the DBSMNG format date (IDATE) to the equivalent day, month and year (to base 1900) counts returned in ID, IM and IY. 13.7.15 Function 14: Date Comparison - the command CALL DBSFOR (14,IDATE1,IDATE2,IFLAG) compares the two DBSMNG format dates IDATE1 and IDATE2. IFLAG on return will be -1, 0 or +1 to indicate that IDATE1 is less than, equal or greater than IDATE2 respectively. 13.7.16 Function 15: Special Database Initialize - the command CALL DBSFOR (15,I,STR,IDEV) loads, initializes and selects the database whose TEMPLATE is in the file STR. The database is assigned index I. If STR is not specified, the Template linked in when the program was built is initialized and selected. IDEV is the RAD50 name of the device on which the database key, parameter and data files reside. If all databases reside on one device the logical name DBS should be assigned to that device and the DBSFOR(10) used to initialize the databases for access. If the databases reside on different devices the DBSFOR(15) function can be used to allow the device name to be specified when the databases are initialized. Note The device specification supplied in IDEV does not effect the TEMPLATE name passed in STR. If the TEMPLATE resides on an area other than DK the device name should be included as part of the file name passed in STR. 13.7.17 Function 16: Envoke FORM Editor - the command CALL DBSFOR (16,IPAGE,IFLG,CMD,MES) envokes the DBSMNG form/card editor to edit the data in the currently loaded record (for the currently selected database). (The DBSMNG form/card editor is called FEDMNG and is used by DBSEDT to edit a record.) The IPAGE argument determines the page that is to be edited. This parameter should be 0 for single page templates. The IFLG parameter controls the operation of FEDMNG. This parameter consists of four 1 bit flags, represented by the octal values 10, 4, 2 and 1. (Octal numbers are designated in FORTRAN by a preceding double quote - eg "10.) The flag value can have more that one bit set at a time. The flag values are explained below. The CMD parameter is a .ASCIZ string that is passed to FEDMNG as a command line iff IFLG bit 3 (ie value "10) is set. The MES parameter is a .ASCIZ string that if not NULL is displayed on the topmost line of the screen as a prompt to the user. If a NULL string is specified for MES (eg '') the last status message is displayed. The IFLG bit meanings are Bit Meaning 1 0 old data in record is displayed as default 1 zero record before entering data 2 0 FEDMNG starts in command mode 1 FEDMNG starts in entry/edit mode 4 0 allow command mode 1 disable command mode 10 0 ignore CMD argument 1 pass CMD to FEDMNG as a command DBSMNG: Database Management Package 107 Note A DBSFOR(17) function must have been performed to setup the console I/O subsystem before attempting to use DBSFOR(16). 13.7.18 Function 17: Interface Initialize - the command CALL DBSFOR (17,IMODE) initializes the DBSMNG FORTRAN interface. A DBSFOR(17) function should be performed before attempting to use any other DBSFOR function. The first time a DBSFOR(17) function is executed the console interface subsystem is initialized, operating system specific initialization is performed, all channels are purged and terminal input mode IMODE is selected. On subsequent DBSFOR(17) calls only the terminal input mode is effected. The IMODE argument selects how terminal input is performed. If IMODE is set to 1 single character input mode is enabled. This means that the monitor will pass characters to the application program as soon as they are typed. Character echo will not be performed by the monitor. If IMODE is set to 2 line input mode is enabled. This means that the monitor will echo each character as it is typed and store it in a buffer. The characters are not passed to the application program until a [RETURN] is pressed. If all terminal I/O is to be performed through the DBSFOR functions set IMODE to 1. If some terminal I/O is to be performed through FORTRAN read/write commands set IMODE to 2. (All DBSFOR terminal I/O is performed in single character mode. If IMODE is set to 2 DBSFOR enables single character input mode temporarily each time a DBSFOR terminal input function is performed.) Note A DBSFOR(17,IMODE) command should be the first executable statement of a FORTRAN program. For historical reasons the first DBSFOR(17,IMODE) call resets all I/O channels. 13.7.19 Function 18: Exit - The command CALL DBSFOR (18) will terminate program execution. If a CUSP chain is currently active control will pass to the next program in the chain. 13.7.20 Function 19: Erase Screen - The command CALL DBSFOR (19) will clear the user's screen. 13.7.21 Function 20: Input Character - The command CALL DBSFOR (20,BYTE) inputs a character and returns it in BYTE. The input character is not echoed. Note BYTE should be declared a LOGICAL*1 variable. 13.7.22 Function 21: Output Character - The command CALL DBSFOR (21,BYTE) outputs character in BYTE to the console. (Byte values above "201 can be used to perform terminal specific functions. Refer to the terminal driver description for the function performed by the special codes.) DBSMNG: Database Management Package 108 13.7.23 Function 22: Input Line - The command CALL DBSFOR (22,BUF) causes a line of input to be returned in .ASCIZ format in BUF. Input is returned from Core Common if available. If none is available in Core Common input is obtained from the terminal. 13.7.24 Function 23: Output Line - The command CALL DBSFOR (23,STR) causes the .asciz string in STR to be output to the terminal. 13.7.25 Function 24: Erase Line - The command CALL DBSFOR (24,IX,IY) erases line IY and leaves the cursor positioned on column IX of line IY. Note The topmost line is numbered 0. The leftmost column is numbered 0. 13.7.26 Function 25: Position Cursor - The command CALL DBSFOR (25,IX,IY) is used to position the cursor on column IX of line IY. Note The top left of the screen is equivalent to column 0 of line 0. 13.7.27 Function 26: Initialize Core Common - Core Common is used by DBSMNG to pass information between Cusps. The command CALL DBSFOR (26) clears out and resets the Core Common variables. This command should be used before attempting to load new data into Core Common. 13.7.28 Function 27: Core Common Chain - The command CALL DBSFOR (27,STR) stores a command in core common to chain to program whose name is passed in STR (in .asciz format). 13.7.29 Function 28: Core Common Input - The command CALL DBSFOR (28,STR) stores a command in core common to return the .asciz string passed in STR to a cusp that tries and read data from core common. 13.7.30 Function 29: Core Common Check - The command CALL DBSFOR (29,BYTE) is used to determine if core common holds any data. The next byte that will be read from core common is returned in BYTE. This command is useful for seeing if core common is empty. A -1 is returned in BYTE if core common is empty. DBSMNG: Database Management Package 109 13.7.31 Function 30: Return Field Attributes - The command CALL DBSFOR (30,NAME,IPAGE,IPROT,IX,IY,IXN,IYN) returns in variables IPAGE,IPROT,IX,IY,IXN,IYN the page, protection, field X/Y and name X/Y coordinates for field NAME. A field is protected if the MSB of IPROT (ie "100000) is set. 13.7.32 Function 31: Set Field Attributes - The command CALL DBSFOR (31,NAME,IPAGE,IPROT,IX,IY,IXN,IYN) sets the page, protection, field X/Y and name X/Y coordinates for field NAME to IPAGE, IPROT, IX/IY, IXN/IYN. The field will be protected if the MSB of IPROT (ie "100000) is set. Use IPROT=0 to unprotect and IPROT=-1 to protect. Specifying IXN=-1 and IYN-1 will cause the field name to be suppressed. 13.7.33 Function 32: Initialize Database - The command CALL DBSFOR (32,I,STR,IDEV,MEMORY,IWORDS) is used to initialize the database whose TEMPLATE is contained in the file STR. The database is assigned index I. The logical name of the volume upon which the database files (ie DAT, KEY and PAR) reside must be passed in rad50 format in IDEV. The user must allocate an INTEGER array (MEMORY) of sufficient size to hold the TEMPLATE. The MEMORY and IWORDS arguments are the array into which the TEMPLATE will be loaded and the size of the array (in words). This form of the database initialize command should only be used when a number of databases are to be accessed and the TEMPLATES will not all fit into memory at the same time. In such a situation the user should allocate an integer*2 array large enough to hold the largest TEMPLATE. This version of the database initialize can then be used to load the TEMPLATES one at a time into memory. Note * When using the above command to initialize databases there is no need to allocate each database a different index number. All databases loaded with this command can be allocated the same index number. * Initializing a new database with the above command results in a database previously initialized with the same command being purged. 13.8 Examples The examples on the following pages demonstrate the use of the more useful DBSFOR commands. DBSMNG: Database Management Package 110 PROGRAM MENU C C This program shows how the DBSFOR core common interface functions C to implement a simple FORTRAN menu program. C LOGICAL*1 ICHAR C CALL DBSFOR (17,2) ! initialize DBSFOR CALL DBSFOR (26) ! initialize core common CALL DBSFOR (19) ! erase screen C C WRITE (7,10) ! prompt 10 FORMAT(1X, 1 /1X,'Options' 1 /1X,' 1 Edit database' 1 /1X,' 2 Inspect database' 1 /1X,' 3 Exit' 1 /1X 1 /1X,'Press digit key corresponding to desired option',$) 20 CALL DBSFOR (20,ICHAR) ! input response IF (ICHAR.EQ.'1') GOTO 1000 ! decode IF (ICHAR.EQ.'2') GOTO 2000 IF (ICHAR.EQ.'3') GOTO 3000 GOTO 20 ! ignore inv char C C envoke editor C 1000 CALL DBSFOR (27,'LB:DBSEDT') ! run DBSEDT CALL DBSFOR (28,'LB:PHONES') ! template name CALL DBSFOR (27,'DK:MENU') ! on exit run self 1010 CALL DBSFOR (19) ! erase screen CALL DBSFOR (18) ! envoke chain C C envoke inspect C 2000 CALL DBSFOR (27,'LB:SELECT') ! run select CALL DBSFOR (28,'LB:PHONES') ! template name CALL DBSFOR (28,'LB:PHONES.SE2') ! select file CALL DBSFOR (28,'DBS:PHONES.INX') ! index file CALL DBSFOR (28,'42') ! record size CALL DBSFOR (27,'LB:SORTER') ! run sorter CALL DBSFOR (28,'DBS:PHONES.INX') ! index file CALL DBSFOR (27,'LB:INSPECT') ! run inspect CALL DBSFOR (28,'LB:PHONES') ! template name CALL DBSFOR (28,'DBS:PHONES.INX') ! index file CALL DBSFOR (27,'DK:MENU') ! on exit run self GOTO 1010 ! envoke chain C C exit C 3000 CALL DBSFOR (18) ! bye END DBSMNG: Database Management Package 111 PROGRAM UPDATE C C This program shows how DBSFOR can be used to allow an operator to C quickly update a specific field in a database. The operator enters C the number of the record to be updated and the new value that is C to be stored in the record. The program then load the record, updates C the field with the user specified value and writes out the record. C C C initialize C CALL DBSFOR (17,2) ! initialize DBSFOR CALL DBSFOR (10,1,'COMPNT') ! open database CALL DBSFOR (7,NUMREC) ! NUMREC = number records C C C get record number and new value C 100 write (7,110) ! prompt 110 format (1x,'Enter record number, new value: ',$) read (5,120) irn,inp ! read 120 format (2i10) if (irn.le.0) call dbsfor (18) ! record=0 --> done if (irn.gt.numrec) goto 100 ! ensure record valid C C C update record C CALL DBSFOR (0,irn) ! load record CALL DBSFOR (2,'st',ISTS,ISIZE,ITYPE) ! get [st] IF (ISTS.NE.'AC') GOTO 100 ! ensure active CALL DBSFOR (2,'Present',ipv,ips,ipt) ! get number present CALL DBSFOR (3,'Present',inp,ips,ipt) ! setup new number present CALL DBSFOR (1,irn) ! write goto 100 ! loop end DBSMNG: Database Management Package 112 PROGRAM COPY12 C ******* ****** C C This program copies data from one database to another. Database INVOIC is C on DY0 and database MASTER is on RK1. The MASTER database contains the C names and addresses for each client. Database INVOIC contains invoices for C each client. To minimize the amount of data entry for each invoice a client C INDEX code is entered on each invoice rather than the client name and C address. When this program is run it uses the INDEX code in each invoice C to locate the client name/address in the MASTER database and then copies C the name and address into the invoice. C LOGICAL*1 BUF(70) ! work buffer DATA IDEV1 /3RDY0/ ! database 1 volume DATA IDEV2 /3RRK1/ ! database 2 volume C CALL DBSFOR (15,1,'INVOIC',IDEV1) ! open database 1 CALL DBSFOR (7,NUM1) ! NUM1=number records CALL DBSFOR (15,2,'MASTER',IDEV2) ! open database 2 CALL DBSFOR (7,NUM2) ! NUM2=number records CALL DBSFOR (9,1) ! select database 1 C DO 1000 I1=1,NUM1 ! for all invoices CALL DBSFOR (0,I1) ! load record CALL DBSFOR (2,'st',ISTS,ISIZE,ITYPE) ! get status IF (ISTS.NE.'AC') goto 1000 ! ignore if inactive CALL DBSFOR (2,'INDEX',INDEX,ISIZE,ITYPE)! get index into MASTER IF (INDEX.LT.1) CALL ERROR () ! trap invalid IF (INDEX.GT.NUM2.) CALL ERROR () ! index number C CALL DBSFOR (9,2) ! select MASTER CALL DBSFOR (0,INDEX) ! load master record CALL DBSFOR (2,'Name',BUF,ISIZE,ITYPE) ! get name CALL DBSFOR (9,1) ! select INVOICE CALL DBSFOR (3,'Name',BUF,ISIZE,ITYPE) ! copy over name CALL DBSFOR (9,2) ! select MASTER CALL DBSFOR (2,'Addrss',BUF,ISIZE,ITYPE)! get address CALL DBSFOR (9,1) ! select INVOICE CALL DBSFOR (3,'Addrss',BUF,ISIZE,ITYPE)! copy over address CALL DBSFOR (1,I1) ! write out updated invoice C 1000 CONTINUE ! loop STOP END DBSMNG: Database Management Package 113 PROGRAM LISTER C ******* ****** C C This program allows the users to open any database and print the contents C of any ascii field on the terminal. The program demonstrates the use C a memory array to hold templates. The use of a memory array allows the C memory used to hold a TEMPLATE to be reclaimed when the TEMPLATE is nolonger C needed. C INTEGER*2 MEM(4000) ! memory array LOGICAL*1 BUF(70) ! work buffer DATA IDEV1 /3RDBS/ ! database 1 volume C 1000 CALL DBSFOR (17,2) ! mode 2 CALL DBSFOR (23,'Enter TEMPLATE name') ! prompt CALL DBSFOR (22,BUF) ! input name CALL DBSFOR (32,1,BUF,IDEV1,MEM,4000) ! open database, load into MEM CALL DBSFOR (23,'Enter FIELD name') ! prompt CALL DBSFOR (22,BUF) ! input name CALL LIST (BUF) ! process GOTO 1000 ! loop END SUBROUTINE LIST (FIELD) C ********** **** C LOGICAL*1 BUF(80) C CALL DBSFOR (7,NUM1) ! NUM1=number records DO 1000 I1=1,NUM1 ! for all records CALL DBSFOR (0,I1) ! load record CALL DBSFOR (2,'st',ISTS,ISIZE,ITYPE) ! get status IF (ISTS.NE.'AC') goto 1000 ! ignore if inactive CALL DBSFOR (2,FIELD,BUF,ISIZE,ITYPE) ! get data IF (ITYPE.LT.1) STOP 'Invalid field type'!invalid type IF (ITYPE.EQ.3) STOP 'Numeric field' ! don't handle numeric fields IF (ITYPE.EQ.4) STOP 'DATE field' ! don't handle date fields IF (ITYPE.GT.4) STOP 'Invalid field type'!invalid type WRITE (7,700) I1,(BUF(J),J=1,ISIZE) ! display 700 FORMAT (1X,I6,1X,100A1) 1000 CONTINUE ! loop RETURN END DBSMNG: Database Management Package 114 Chapter 14 XXXFOR: General FORTRAN Support Routines The DBSMNG object library DBSLIB.OBJ included on the distribution kit contains a number of general purpose FORTRAN callable routines that facilitate the inclusion of user written programs in a DBSMNG application. These routines are invoked with the FORTRAN instruction CALL XXXFOR (ICODE,ARG1,....,ARGn) where XXXFOR is the name of the routine. The first argument (ICODE) is an opcode that determines the operation to be performed. This chapter provides a summary of the functions performed by these routines for the FORTRAN programmer. 14.1 Error handling These routines will abort program execution upon detection of an error. An error message will be displayed on the terminal identifying the cause of the error and the FORTRAN statement that had called the routine. 14.2 CONFOR: Core-Common and Console Support The CONFOR allows a FORTRAN program to be included in a CUSP Chain. CONFOR allows Core Common Commands to be accepted by the program. The CONFOR calling sequences are summarized below. sequence function CONFOR (0) ignored CONFOR (1) terminates program execution CONFOR (2) illegal CONFOR (3,CHAR) outputs character CHAR to user TTY CONFOR (4,BUF) returns .asciz string from CC or TTY CONFOR (5,BUF) outputs .ASCIZ string in BUF to TTY CONFOR (6,X,Y) illegal CONFOR (7,X,Y) illegal CONFOR (8,X,Y) illegal CONFOR (9,C,N) outputs character C N times CONFOR (10,C1,..CN) output series of bytes C1 to CN DBSMNG: Database Management Package 115 14.3 SRTFOR: SFL File I/O Support The SRTFOR routine allows the FORTRAN programmer to access and manipulate DBSMNGDBSMNG SFL files. The SFL files consists of a one block header followed by a number of fixed length records. Such files are can be loaded with data extracted from a DBSMNG database using the SELECT CUSP, optionally sorted using SORTER or used as input to the REPORT, BACKUP or INSPECT CUSPs. Use of this module uses up one RT-11 channel and may require the allocation of more channels. The calling convention for the routine is CALL SRTFOR (ICODE,ARG1,....,ARGn) where ICODE is the opcode that determines the operation to perform and ARG? are optional arguments. The following calls are supported sequence function SRTFOR (0) rewind/reset the input stream SRTFOR (1) closes out file SRTFOR (2,I1,I2,I3,I4) return SFL file parameters SRTFOR (3,I1,I2,I3,I4) setup SFL file parameters SRTFOR (4,BUF,NUM) read NUM bytes into BUF SRTFOR (5,BUF,NUM) write NUM bytes from BUF SRTFOR (6) chain to LB:SORTER.SAV SRTFOR (7,FILNME) chain to program SRTFOR (8,INDEX,BUF) peek at header block byte SRTFOR (9,INDEX,VAL) poke into header block byte STRFOR (10,NAME) open SFL file SRTFOR (11,NUM) 14.3.1 Rewind SFL File - The FORTRAN instruction CALL SRTFOR (0) is used to reset the SFL file I/O subsystem. The contents of the output buffer are output and then both the input and output stream rewound to the first entry in the file. 14.3.2 Close SFL File - The FORTRAN instruction CALL SRTFOR (1) is used to close the SFL file. The contents of the output buffer are written out and the file is then closed. (Another SFL file may then be opened.) 14.3.3 Read SFL File Parameters - The FORTRAN instruction CALL SRTFOR (2,ISIZE,NUMENT,ISTS,ICHN) is used to read the number of bytes per entry and the number of entries in the file in ISIZE and NUMENT respectively. The ISTS and ICHN parameters are set to 0 and are supported only for compatibility with the earlier version of SRTFOR. DBSMNG: Database Management Package 116 14.3.4 Setup SFL File Parameters - The FORTRAN instruction CALL SRTFOR (3,ISIZE,NUMENT,ISTS,NME) is used to to setup up the header block to indicate that the file consists of NUMENT entries each of ISIZE bytes. The ISTS and NME arguments are ignored and are only supported for compatibility with the earlier version of SRTFOR. 14.3.5 Read SFL File Data - The FORTRAN instruction CALL SRTFOR (4,BUF,NUM) is used to load the logical*1 buffer BUF with the next NUM bytes in the input stream. 14.3.6 Write to SFL File - The FORTRAN instruction CALL SRTFOR (5,BUF,NUM) is used to write NUM bytes from the logical*1 array BUF to the output stream. 14.3.7 Chain to SORTER - The FORTRAN instruction CALL SRTFOR (6) is used to chain to LB:SORTER.SAV. This operation results in the SFL file being closed out and a chain to the program SORTER.SAV on device LB: being performed. 14.3.8 Chain to Specified Program - The FORTRAN instruction CALL SRTFOR (7,FILNME) is used to chain to another program. This operation results in the SFL file being closed out and a chain to the program whose RAD50 file spec is in FILNME being performed. 14.3.9 Peek at SFL Header Block - The FORTRAN instruction CALL SRTFOR (8,INDEX,BUF) is used to peek at header block. This operation results in the contents of byte INDEX (first byte is numbered 0) being returned in BUF. 14.3.10 Poke into SFL Header Block - The FORTRAN instruction CALL SRTFOR (9,INDEX,VAL) is used to poke into the SFL file header block. This operation results in the value VAL being stored in the byte INDEX (first byte is numbered 0) of the header block. DBSMNG: Database Management Package 117 14.3.11 Open SFL File - The FORTRAN instruction CALL STRFOR (10,NAME) is used to open a SFL file. This operation results in the SFL file whose .ASCIZ name is passed in NAME being opened. 14.3.12 Position at Specified Record - The FORTRAN instruction CALL SRTFOR (11,NUM) is used to position the input stream. This operation results in the SFL input stream being position so that the next read operation will return the data sorted in entry NUM. The first entry is number 1. 14.4 FNDFOR: Find Matching Entry in SFL File The FNDFOR routine allows the FORTRAN programmer to access data stored in a DBSMNGDBSMNG SFL files. Such files are can be loaded with data extracted from a DBSMNGDBSMNG database using the SELECT CUSP. The FNDFOR routine can then be used to rapidly retrieve an entries that match a specified target. The SFL file must be sorted with SORTER before being used by FNDFOR as FNDFOR performs a binary search to locate matching records. 14.4.1 Opening SFL File - The FORTRAN instruction CALL FNDFIN () or CALL FNDFIN (STRING) can be used to open the SFL file DK:SORTER.DAT or STRING (where STRING is a .ASCIZ string) respectively. 14.4.2 Rewind File - The FORTRAN instruction CALL FNDFOR (0) is used to reset the SFL file. The next read operation will return the data stored in entry 1. 14.4.3 Position at Specific Entry - The FORTRAN instruction CALL FNDFOR (1,I) is used to position the file so that the next read operation will return the data stored in the I'th record. 14.4.4 Read SFL File Parameters - The FORTRAN instruction CALL FNDFOR (2,ISIZE,NUMENT,ISTS,ICHN) is used to read the number of bytes per entry and the number of entries in the file in ISIZE and NUMENT respectively. The ISTS and ICHN parameters are set to 0 and are supported only for compatibility with the earlier version of SRTFOR. DBSMNG: Database Management Package 118 14.4.5 Locate Matching Entries - The FORTRAN instruction CALL FNDFOR (3,TARGET,IREC,MATCHS) is used search for entries that match the string TARGET. If MATCH is non-zero on return, IREC points to first exact match and MATCHS holds number of exact matches. If no matches are found IREC will point to closest smaller entry. 14.4.6 Read SFL Data - The FORTRAN instruction CALL FNDFOR (4,BUF,NUM) is used to read the next NUM bytes from the SFL file into array BUF. DBSMNG: Database Management Package 119 14.4.7 Example use of FNDFOR - a simple example FORTRAN program showing the intended usage of FNDFOR follows. PROGRAM SEARCH C ======= ====== C LOGICAL*1 BUF(70),TARGET(70),ANS,IRECB(2) EQUIVALENCE (IREC,IRECB) C CALL FNDFIN ('ZB5:SORTER.DAT') ! initialize FND CALL FNDFOR (2,IFNSZ,IFNNM,I,I) ! get entry size C 1000 WRITE (7,1100) ! prompt 1100 FORMAT (1X,'?',$) READ (7,1200) TARGET ! get target 1200 FORMAT (80A1) CALL FNDFOR (3,TARGET,IFNRC,IFLAG) ! attempt to find IF (IFLAG.EQ.1) GOTO 4000 ! found -> skip WRITE (7,1300) IFLAG 1300 FORMAT (I10/) C 2000 CALL FNDFOR (1,IFNRC) ! position at best CALL FNDFOR (4,BUF,IFNSZ) ! read string BUF(IFNSZ+1) = "200 ! terminate string CALL PRINT (BUF(3)) ! print string WRITE (7,2100) ! Prompt 2100 FORMAT (1X,'?',$) READ (5,2200) ANS ! get answer 2200 FORMAT (A1) IF (ANS.EQ.' ') GOTO 1000 ! new target IF (ANS.EQ.'M') GOTO 4000 ! have match IF (ANS.EQ.'N') IFNRC = IFNRC+1 ! next record IF (ANS.EQ.'P') IFNRC = IFNRC-1 ! previous record GOTO 2000 ! loop C 4000 CALL FNDFOR (1,IFNRC) ! position at best CALL FNDFOR (4,BUF,IFNSZ) ! read string IRECB(1)= BUF(1) ! setup lob IRECB(2)= BUF(2) ! setup hob WRITE (7,4100) IREC,(BUF(J),J=3,IFNSZ) 4100 FORMAT (1X,'Match @',I4,2X,70A1) STOP END DBSMNG: Database Management Package 120 Chapter 15 Installation Guide DBSMNG is distributed on a double density floppy diskette (or the equivalent). The diskette contains the major cusps and the DBSLIB.SML macro library. These files should be copied onto the LB: volume. The RT-11 programs MACRO.SAV, LINK.SAV and an editor (KED.SAV) are needed to setup a DBSMNG database application. 15.1 Running under RSTS/E Assuming that the DBSMNG CUSPs reside in PPN [1,7] and have been assigned a protection of <60>, the following command file can be used to start up a DBSMNG application. SWITCH RT11 SIZE 28 PPN [1,7] LIB [1,7] R MENU APPLIC.MNU VT100 DBSMNG: Database Management Package 121 Chapter 16 Additional Information Due to timing difficulties (namely in there only being 24 hours in each day), the complete DBSMNG documentation can not be included in this version of the manual. This chapter contains a number of notes that may be of aid until the complete documentation becomes available (if it every does). 16.1 RT-11 and RSTS/E differences Due to a failure of the RSTS/E RT-11 emulator to properly emulate the .SERR, .DSTATUS and .CHAIN EMTs the automatic sizing of memory normally performed by the CUSPs cannot be performed under RSTS/E. 16.2 Supporting a Different Terminal Type Adding support for other types of terminals requires writing a special driver module for the terminal (use VT1IO.MAC as a basis) and updating a table in the CONIO.MAC module to enable the new terminal type. Alternatively it is possible to modify one of the other terminal drives (viz TVIIO.MAC) that is not likely to be used. 16.3 Module Documentation The information contained in this manual has been extracted from the front of the various source modules. Due to a lack to time it has not been possible to extract all relevant data. It is strongly suggested that the appropriate source module documentation be referred to if additional information is needed. 16.4 Obtaining additional help and information If in need of help, I may be contacted as follows S.E.Q.E.B, Phone Work: 07-2235174 62 Ann St., Brisbane, Qld, 4001. Attention: Ray Di Marco DBSMNG: Database Management Package 122 17 INDEX ! ! Primary Files 77 $! $! Action Block Terminator 35 $# $# Convert to Ascii 58 $# $# If Terminal NOT of Type XXX 34 $# $# Terminate if Variables Not Equal 89 $$ $$ End of Block 55 $% $% Conditional Block Terminator 34 $( $( Input Field Name from Terminal 56 $( $( Input from Terminal 35 $) $) Output to Terminal 35 55 89 $* $* Marking Rewind Point 55 $* $* Section Delimiter 33 90 $- $- Delete Character 58 $/ $/ Auto initiated Function 34 $: $: Absolute Position 58 89 $; $; Suppress RPT Output 89 $< $< Extract Field Data 87 $< $< Trim 57 $< REPORT $< (field data) sub operators 82 $= $= If Terminal of Type XXX 33 $= $= Terminate if Variables Equal 89 $> $> Right Justify 57 $? $? Input Data from Terminal 55 $@ $@ Initiate CUSP Chain 35 $\ $\ Section Delimiter 89 $^ $^ Uppercase 58 $a $A Append to Existing SFL Contents 55 $b $B BCD Operator 82 $b REPORT $B (BCD) suboperators 81 $bf REPORT $BF (BCD format) sub operators 81 $c $C Clear OEF 56 $c $C Comment 84 $c $C Include CUSP in Chain 36 $d $D Dating 84 $d $D Define User Variable 35 $e $E End of Program 55 $e $E Terminate if all Records Processed 84 $f $F Format Operator 84 $i $I Insert Core Common Command 36 $i $I Switch Input Stream 85 $K $0 Action Block for Function Key K 34 35 $l $L Load Data 57 $m $M Select Termination Mode 85 $n $N Next Record 86 $o $O Output Stream 86 $p $P Pagination 86 $q $Q Quiet CUSP Chain 36 $r $R Define Ruler 86 $r $R Range Specification 55 $r $R Restart 35 $s $S Switch 58 $t $T Do Tests 56 DBSMNG:INDEX 123 $t $T Test Operator 86 $t REPORT $T (TEST) sub operators 81 $t# $T# Skip if Variables Not Equal 87 $t+ $T+ Skip if Variable Greater Than 0 87 $t- $T- Skip if Variable Less Than 0 87 $t0 $T0 Skip if Variable is 0 87 $t= $T= Skip if Variables Equal 87 $te $TE End of Section Marker 87 $tr $TR Special End of Section Marker 87 $x REPORT ($X) Operators 76 $~ $~ Flush 58 $~ $~ Substitute System Parameters 37 $~( $~( Operator: User Variable 37 $~a $~A Operator: Device Assignment 37 $~j $~J Operator: Job Number 37 $~n $~N Operator: User Name 37 $~p $~P Operator: Project Number 37 $~q $~Q Operator: Programmer Number 37 $~v $~V Operator: Flag Virtual Line 37 + + BCD addition sub operator 82 - - BCD subtraction sub operator 82 .alp. .ALP. Alphabetic 4 .asc. .ASC. ASCII 4 .dat. .DAT. Date 5 .eq. .EQ. Boolean Operator 57 .ge. .GE. Boolean Operator 57 .gt. .GT. Boolean Operator 57 .le. .LE. Boolean Operator 57 .lt. .LT. Boolean Operator 57 .ne. .NE. Boolean Operator 57 /p /P Pause Switch 60 /r /R Reverse Order Switch 60 /s /S Statistics Switch 60 @ @ Terminal 77 ^@ ^@ Repeat Block Sequence 9 50 74 a-z A-Z Memory Variables 77 abort ABORT: Abort Edit Submode 47 access Accessing a database 103 access Accessing Records 44 access Direct Field Access 54 access Explicit Record Access 44 access Indirect Field Access 54 access Keyed Record Access 45 access Multiple database access 103 action Action Definition Blocks 32 34 35 add Add keywords command 66 addition + BCD addition sub operator 82 alphabetic .ALP. Alphabetic 4 append Append to Existing SFL Contents 55 application Application Notes 71 application Example Application 10 application Using BACKUP in an Application 95 approximate Comma Delimiter: Approximate Match 65 area Form Area 41 63 area Interaction Area 41 63 argument Arguments 53 argument BLOCK argument 17 argument CHECK argument 18 20 argument DATA argument 18 20 argument DEVICE Argument 17 DBSMNG:INDEX 124 argument ENTRIES argument 17 argument KEY argument 19 argument LABEL argument 20 argument LD argument 18 20 argument NAME argument 18 20 argument PAGE argument 19 21 argument PROTECT argument 18 argument RD argument 18 20 argument TYPE argument 18 19 argument X argument 17 19 20 argument XN argument 18 20 argument XXXXXX argument 17 argument Y argument 18 19 20 ascii $# Convert to Ascii 58 ascii .ASC. ASCII 4 ascii Extracted Data: Ascii Fields 88 assign Assign Format File Input Stream 77 assign Assign Memory Variable Input Stream 78 assign Assign Memory Variable Output Stream 77 assign Assign RPT File Output Stream 77 assign Assign Terminal Input Stream 77 assign Assign Terminal Output Stream 77 assignment $~A Operator: Device Assignment 37 attribute Function 30: Return Field Attributes 110 attribute Function 31: Set Field Attributes 110 auto $/ Auto initiated Function 34 automatic Automatic tabing 44 background Background 73 backspace BACKSPACE 44 64 backuf BACKUF 93 backun BACKUN 94 backup BACKUP 93 backup Backup File 94 backup Backup Utilities 93 backup Using BACKUP in an Application 95 backut BACKUT 93 bcd $B BCD Operator 82 bcd + BCD addition sub operator 82 bcd - BCD subtraction sub operator 82 bcd BCD Binary Coded Decimal 79 bcd D BCD decrement sub operator 83 bcd F BCD format sub operator 83 bcd I BCD increment sub operator 82 bcd REPORT $B (BCD) suboperators 81 bcd REPORT $BF (BCD format) sub operators 81 bcd Z BCD zero sub operator 83 binary BCD Binary Coded Decimal 79 block $$ End of Block 55 block ^@ Repeat Block Sequence 9 50 74 block Action Definition Blocks 32 34 35 block BLOCK argument 17 block Conditional Blocks 32 block Data Blocks 10 50 74 block Header Block 10 50 74 block Peek at SFL Header Block 117 block Poke into SFL Header Block 117 blocking Blocking Factor 3 body Body Section 79 boolean Boolean Operators 56 57 boolean Boolean Statements 56 DBSMNG:INDEX 125 buffer Output Buffer 52 57 bye [RETURN] Implicit Bye Command 45 bye BYE: Terminate Execution 45 calling DBSFOR Calling Sequences 104 card Designing a CARD 26 card Example TPL Card 29 case Lower Case 65 cd cd 6 chain $@ Initiate CUSP Chain 35 chain $C Include CUSP in Chain 36 chain $Q Quiet CUSP Chain 36 chain Chain to SORTER 117 chain Chain to Specified Program 117 chain CUSP Chain Initiation 36 chain CUSP Chains 8 30 41 94 chain Function 27: Core Common Chain 109 chain MENU: CUSP Chain Manager 30 chain Using CUSP chaining from a menu 62 chain Very Long CUSP Chains 38 channel Channel Purging 104 character $- Delete Character 58 character Function 20: Input Character 108 character Function 21: Output Character 108 check CHECK argument 18 20 check Function 29: Core Common Check 109 check Numeric Limit Check 5 clear $C Clear OEF 56 clear CLEAR: Clear out Record 47 close Close SFL File 116 cmddsp CMDDSP: Dispatch Command to Monitor 99 cn cn 5 comma Comma Delimiter: Approximate Match 65 command $I Insert Core Common Command 36 command [RETURN] Implicit Exit 45 47 command Add keywords Command 66 command CMDDSP: Dispatch Command to Monitor 99 command Command Input Mode 42 63 command Core Common Commands 30 command DBSEDT Commands 45 command Exit Command 66 command Flip page Command 67 command Form Editing Mode Commands 46 command Help Command 66 command INSKEY Commands 66 command Keywords Command 66 command Next Command 67 command Print Command 67 command Refresh Command 67 comment Comments 32 84 common CONFOR: Core Common and Console Support 115 common Core Common Commands 30 common Core Common Overflow 37 common Function 26: Initialize Core Common 109 common Function 27: Core Common Chain 109 common Function 28: Core Common Input 109 common Function 29: Core Common Check 109 common Insert Core Common Command 36 compare Function 14: Date Comparison 107 compiler DBSTPL: Template Compiler 100 component Components 10 DBSMNG:INDEX 126 concept Concepts 2 30 70 conditional $% Conditional Block Terminator 34 conditional Conditional Blocks 32 confor CONFOR: Core Common and Console Support 115 constant Constants 53 constant Date Constants 53 constant Literal Constants 53 constant Numeric Constants 53 content Append to Existing SFL Contents 55 content Field Contents 54 control Control keys 43 64 control-u Control-U 44 65 convert Convert to Ascii 58 coordinate Screen Coordinates 6 copy DBSCPY: Copy Database 99 core $I Insert Core Common Command 36 core CONFOR: Core Common and Console Support 115 core Core Common Commands 30 core Core Common Overflow 37 core Function 26: Initialize Core Common 109 core Function 27: Core Common Chain 109 core Function 28: Core Common Input 109 core Function 29: Core Common Check 109 create Create a New Record 46 create CREATE: Create a New Record 45 create Creating the Format file for SELECT 72 create Creating the INDEX File 68 72 create Creating the TEMPLATE 23 27 create SFL File Creation 10 50 crt CRT type 31 cursor Cursor Movement Keys 43 64 cursor Function 25: Position Cursor 109 cusp $@ Initiate CUSP Chain 35 cusp $C Include CUSP in Chain 36 cusp $Q Quiet CUSP Chain 36 cusp CUSP Chain Initiation 36 cusp CUSP chaining 30 41 94 cusp CUSPs 7 10 cusp INSKEY: Multi key Database Inspection CUSP 62 cusp INSPECT: Online Database Query CUSP 70 cusp MENU: CUSP Chain Manager 30 cusp Other CUSPs 99 cusp TPLREP CUSP 74 cusp Using CUSP chaining from a menu 62 cusp Variable Passing Between CUSPs 54 cusp Very Long CUSP Chains 38 customization Customization 8 cycle Cycle 51 cycle REPORT Cycle 76 d D BCD decrement sub operator 83 d4 D4: System Date 54 data $< Extract Field Data 87 data $? Input Data from Terminal 55 data $L Load Data 57 data DATA argument 18 20 data Data Blocks 10 50 74 data Data Extraction 51 data Data File 8 data Data Input Mode 42 data Edit Field Data 47 DBSMNG:INDEX 127 data Extracted Data: Ascii Fields 88 data Extracted Data: Date Fields 88 data Extracted Data: Numeric Fields 88 data Read SFL File Data 117 data REPORT $< (field data) sub operators 82 database Accessing a database 103 database Database 8 database Database Concepts 2 database Database Entries 3 database Database Fields 4 database Database Files 3 database Database Name 3 database Database Template 3 database DBSCPY: Copy Database 99 database DBSEDT: Database Editor 40 database DBSMAK: Make Database 100 database EXAMPLE Database Design 12 database Function 10: Database Initialize 106 database Function 15: Special Database Initialize 107 database Function 32: Initialize Database 110 database Function 7: Database Size 106 database Function 8: Database Name 106 database Function 9: Database Select 106 database Initializing the Database 23 28 database INSKEY: Multi key Database Inspection CUSP 62 database INSPECT: Online Database Query CUSP 70 database Multi page Database Design 27 database Multiple database access 103 database PURGER: Purge Database Records 101 database Setting up a Database 11 25 date $D Dating 84 date .DAT. Date 5 date D4: System Date 54 date Date Constants 53 date Date Variables 54 date Extracted Data: Date Fields 88 date Function 11: Return Date (Internal Format) 106 date Function 12: To Internal Date Format 106 date Function 13: From Internal Date Format 107 date Function 14: Date Comparison 107 dbscpy DBSCPY: Copy Database 99 dbsedt DBSEDT Commands 45 dbsedt DBSEDT: Database Editor 40 dbsedt Invoking DBSEDT 40 dbsfor DBSFOR Calling Sequences 104 dbsfor DBSFOR: FORTRAN Interface 102 dbsfor Using DBSFOR 103 dbsmak DBSMAK: Make Database 100 dbssts DBSSTS: TEMPLATE Disassembler 100 dbstpl DBSTPL: Template Compiler 100 decimal BCD Binary Coded Decimal 79 declaration Explicit Key Declarations 15 declaration Field Declarations 14 declaration Implicit Key Declarations 15 declaration Key Declarations 14 decrement D BCD decrement sub operator 83 define Action Definition Blocks 32 define Define Ruler 86 define Define User Variable 35 define Display Definition Section 32 DBSMNG:INDEX 128 define Environment Definition 11 25 define Example Template Definition File 22 define Function Definition Section 32 define Template Definition File 13 define Template Definition Macros 16 delete $- Delete Character 58 delete DELETE 44 64 delete DELETE: Mark Record as Deleted 46 delete Recovering DELETED Records 46 delimiter $* Section Delimiter 33 90 delimiter $\ Section Delimiter 89 delimiter Comma Delimiter: Approximate Match 65 delimiter Field Delimiters 7 delimiter Semicolon Delimiter: Exact Match 65 description Operational Description 61 design Designing a CARD 26 design EXAMPLE Database Design 12 design Multi page Database Design 27 device $~A Operator: Device Assignment 37 device DEVICE Argument 17 digit Digit Key Function Execution 33 direct Direct Field Access 54 disassembler DBSSTS: TEMPLATE Disassembler 100 dispatch CMDDSP: Dispatch Command to Monitor 99 display Display Definition Section 32 display Visual Display Units 6 down DOWN 43 64 edit DBSEDT: Database Editor 40 edit Form Editing Mode Commands 46 47 edit Function 16: Envoke FORM Editor 107 enable Output Enable Flag 52 56 end $$ End of Block 55 end $E End of Program 55 end $TE End of Section Marker 87 end $TR Special End of Section Marker 87 enter [ENTER] Implicit Exit 45 47 66 entry Database Entries 3 entry ENTRIES argument 17 entry Entry Size 75 entry FNDFOR: Find Matching Entry 118 environment Environment Definition 11 25 envoke Function 16: Envoke FORM Editor 107 equal $# Terminate if Variables Not Equal 89 equal $= Terminate if Variables Equal 89 equal $T# Skip if Variables Not Equal 87 equal $T= Skip if Variables Equal 87 erase Function 19: Erase Screen 108 erase Function 24: Erase Line 109 error Error handling 104 115 escape Escape Sequence 9 49 74 escape Including VT100 Escape Sequences 38 event All Records Processed Event Handling 80 exact Semicolon Delimiter: Exact Match 65 examine INSPECT: Examine Record 46 example Example Application 10 example EXAMPLE Database Design 12 example Example Format Files 58 example Example Format Files 90 example Example Menu 39 example Example MENU and SELECT Format Files 97 DBSMNG:INDEX 129 example Example Template Definition File 22 example Example TPL Card 29 example Examples 110 execution Digit Key Function Execution 33 execution Slash Function Execution 33 exit [RETURN] Implicit EXIT Command 47 exit E Command: Exit 66 exit EXIT: Exit Edit Submode 47 exit Function 18: Exit 108 explicit Explicit Key Declarations 15 explicit Explicit Record Access 44 extract $< Extract Field Data 87 extract Data Extraction 51 extract Extracted Data: Ascii Fields 88 extract Extracted Data: Date Fields 88 extract Extracted Data: Numeric Fields 88 extract SELECT: Selective Information Extractor 49 factor Blocking Factor 3 field $( Input Field Name from Terminal 56 field $< Extract Field Data 87 field Database Fields 4 field Direct Field Access 54 field EDIT: Edit Field Data 47 field Extracted Data: Ascii Fields 88 field Extracted Data: Date Fields 88 field Extracted Data: Numeric Fields 88 field Field Contents 54 field Field Declarations 14 field Field Delimiters 7 field FIELD Macro 17 field Field Name 4 7 88 field Field Name in Variable 88 field Field Size 4 field Field Type 4 field Function 2: Field Get 105 field Function 30: Return Field Attributes 110 field Function 31: Set Field Attributes 110 field Function 3: Field Put 105 field Indirect Field Access 54 field Key Fields 5 field LOCATE: Edit Specified Field 47 field Protected Fields 5 field REPORT $< (field data) sub operators 82 field Special Fields 5 6 15 27 field Specifying a Key Field 27 field Specifying a Protected Field 27 field Specifying Fields 26 field Specifying the Field Type 26 field Target Fields 70 71 file ! Primary Files 77 file Assign Format File Input Stream 77 file Assign RPT File Output Stream 77 file Backup File 94 file Close SFL File 116 file Creating the INDEX File 68 72 file Data File 8 file Database Files 3 file Example Format Files 58 90 97 file Example Template Definition File 22 file Files 8 DBSMNG:INDEX 130 file FINDER: Find Matching Records in SFL File 100 file FNDFOR: Find Matching Entry in SFL File 118 file Format File 31 49 52 73 79 file Generating the INDEX File from a Menu 69 file Index File 70 file Key File 8 file Loading Keys into the INDEX File 68 file Menu Format File 31 file Omitting Sorter File 75 file Open SFL File 118 file Output File 52 file Parameter File 9 file Read SFL File Data 117 file Read SFL File Parameters 116 file Rewind File 53 76 116 118 file SELECT Format File 72 file Setting up the HELP File 69 file Setting up the INDEX File 68 72 file Setup SFL File Parameters 117 file SFL File 10 50 74 file SFL File 74 file Sorting the INDEX File 69 file SRTFOR: SFL File I/O Support 116 file Template Definition File 13 file Using a start up file 41 62 file Write to SFL File 117 find FINDER: Find Matching Records in SFL File 100 find FNDFOR: Find Matching Entry in SFL File 118 finder FINDER: Find Matching Records in SFL File 100 first FIRST: Edit First Field 47 flag $~V Operator: Flag Virtual Line 37 flag Output Enable Flag 52 56 flip F Command: Flip page 67 flush $~ Flush 58 fndfor FNDFOR: Find Matching Entry in SFL File 118 form Form Area 41 63 form Form Editing Mode Commands 46 form FORM Macro 17 form Function 16: Envoke FORM Editor 107 format $F Format Operator 84 format Assign Format File 77 format Example Format Files 58 90 97 format F BCD format sub operator 83 format Format File 9 31 49 52 73 format Format File Structure 79 format Function 11: Return Date (Internal Format) 106 format Function 12: To Internal Date Format 106 format Function 13: From Internal Date Format 107 format Menu Format File 31 format REPORT $BF (BCD format) sub operators 81 format Rewinding the Format File 53 76 format SELECT Format File 72 formend FORMEND Macro 19 fortran DBSFOR: FORTRAN Interface 102 fortran XXXFOR: General FORTRAN Support Routines 115 function $/ Auto initiated Function 34 function $K Action Block for Function Key K 34 35 function Digit Key Function Execution 33 function Function 93 function Function 0: Record Read 104 DBSMNG:INDEX 131 function Function 1: Record Write 104 function Function 2: Field Get 105 function Function 3: Field Put 105 function Function 4: Read Keys 105 function Function 5: Write Keys 105 function Function 6: Key Number 106 function Function 7: Database Size 106 function Function 8: Database Name 106 function Function 9: Database Select 106 function Function 10: Database Initialize 106 function Function 11: Return Date (Internal Format) 106 function Function 12: To Internal Date Format 106 function Function 13: From Internal Date Format 107 function Function 14: Date Comparison 107 function Function 15: Special Database Initialize 107 function Function 16: Envoke FORM Editor 107 function Function 17: Interface Initialize 108 function Function 18: Exit 108 function Function 19: Erase Screen 108 function Function 20: Input Character 108 function Function 21: Output Character 108 function Function 22: Input Line 109 function Function 23: Output Line 109 function Function 24: Erase Line 109 function Function 25: Position Cursor 109 function Function 26: Initialize Core Common 109 function Function 27: Core Common Chain 109 function Function 28: Core Common Input 109 function Function 29: Core Common Check 109 function Function 30: Return Field Attributes 110 function Function 31: Set Field Attributes 110 function Function 32: Initialize Database 110 function Function Definition Section 32 function Function Keys 30 function Slash Function Execution 33 function Special Function Keys 7 43 64 generating Generating the INDEX File from a Menu 69 header Header Block 10 50 74 header Header Section 79 header Peek at SFL Header Block 117 header Poke into SFL Header Block 117 help Setting up the HELP File 69 hint Hints and Suggestions 24 37 homogeneity Homogeneity 2 i/o I/O Stream Selection 77 i/o SRTFOR: SFL File I/O Support 116 identify Using Keywords to Identify Records 65 imbedded Imbedded Operators 57 implementation Implementation Overview 2 implicit Implicit Key Declarations 15 include $C Include CUSP in Chain 36 include Including VT100 Escape Sequences 38 increment I BCD increment sub operator 82 index Creating the INDEX File 68 72 index Generating the INDEX File from a Menu 69 index Index File 70 index Loading Keys into the INDEX File 68 index Setting up the INDEX File 68 72 index Sorting the INDEX File 69 indirect Indirect Field Access 54 DBSMNG:INDEX 132 initialize Function 10: Database Initialize 106 initialize Function 15: Special Database Initialize 107 initialize Function 17: Interface Initialize 108 initialize Function 26: Initialize Core Common 109 initialize Function 32: Initialize Database 110 initialize Initializing the Database 23 28 initialize Memory Variable F Initialization 78 initialize Memory Variable Initialization 78 initialize OEF Initialization 56 initialize Screen Initialization 33 initiate $/ Auto initiated Function 34 initiate $@ Initiate CUSP Chain 35 initiate CUSP Chain Initiation 36 input $( Input Field Name from Terminal 56 input $( Input from Terminal 35 input $? Input Data from Terminal 55 input $I Switch Input Stream 85 input Assign Format File Input Stream 77 input Assign Memory Variable Input Stream 78 input Assign Terminal Input Stream 77 input Command Input Mode 42 63 input Data Input Mode 42 input Function 20: Input Character 108 input Function 22: Input Line 109 input Function 28: Core Common Input 109 input Input Modes 42 63 input Input Wait 33 input Keyword Input Mode 63 insert $I Insert Core Common Command 36 inskey INSKEY Commands 66 inskey INSKEY: Multi key Database Inspection CUSP 62 inskey Invoking INSKEY 62 inskey Invoking INSKEY from a Menu 68 inskey Setting up INSKEY 68 inskey Using INSKEY 67 inspect INSKEY: Multi key Database Inspection CUSP 62 inspect INSPECT: Examine Record 46 inspect INSPECT: Online Database Query CUSP 70 inspect Using INSPECT 71 interface DBSFOR: FORTRAN Interface 102 interface Function 17: Interface Initialize 108 interface Interfacing to SORTER 61 interface Interfacing to User Programs 51 internal Function 11: Return Date (Internal Format) 106 internal Function 12: To Internal Date Format 106 internal Function 13: From Internal Date Format 107 invoking Invoking DBSEDT 40 invoking Invoking INSKEY 62 invoking Invoking INSKEY from a Menu 68 invoking Invoking MENU 31 invoking Invoking REPORT 75 invoking Invoking SELECT 51 job $~J Operator: Job Number 37 justify $> Right Justify 57 key [RETURN] Key 66 key Action Block for Function Key 33 34 35 key Control keys 43 64 key Cursor Movement Keys 43 64 key Explicit Key Declarations 15 key Function 4: Read Keys 105 DBSMNG:INDEX 133 key Function 5: Write Keys 105 key Function 6: Key Number 106 key Function Keys 30 key Implicit Key Declarations 15 key INSKEY: Multi key Database Inspection CUSP 62 key INSPECT: Online Database Query CUSP 70 key KEY argument 19 key Key Declarations 14 key Key Fields 5 key Key File 8 key KEY Macro 19 key Key Page 7 key Key Page Labels 21 key Keyed Record Access 45 key Keywords and Keys 65 key Loading Keys into the INDEX File 68 key Special Function Keys 7 43 64 key Specifying a Key Field 27 keyend KEYEND Macro 20 keyword A Command: Add keywords 66 keyword Adding Keywords 66 keyword K Command: Keywords 66 keyword Keyword 65 keyword Keyword Input Mode 63 keyword Keywords and Keys 65 keyword Multiple Keywords 66 keyword Using Keywords to Identify Records 65 label Key Page Labels 21 label LABEL argument 20 label LABEL Macro 20 label Labels 7 label Specifying Labels 26 layou Screen Layout 7 41 63 ld LD argument 18 20 libraries Libraries 10 limit Numeric Limit Check 5 line $~V Operator: Flag Virtual Line 37 line Function 22: Input Line 109 line Function 23: Output Line 109 line Function 24: Erase Line 109 literal Literal Constants 53 literal Literal Variables 54 load $L Load Data 57 load Loading Keys into the INDEX File 68 locate LOCATE: Edit Specified Field 47 long Very Long CUSP Chains 38 lower Lower Case 65 m1 M1 Termination Mode 85 m2 M2 Termination Mode 85 m3 M3 Termination Mode 85 m4 M4 Termination Mode 85 macro FIELD Macro 17 macro FORM Macro 17 macro FORMEND Macro 19 macro KEY Macro 19 macro KEYEND Macro 20 macro LABEL Macro 20 macro SPARE Macro 21 macro Template Definition Macros 16 main REPORT Main Operators 80 DBSMNG:INDEX 134 make DBSMAK: Make Database 100 manager MENU: CUSP Chain Manager 30 mark $* Marking Rewind Point 55 mark $TE End of Section Marker 87 mark $TR Special End of Section Marker 87 mark DELETE: Mark Record as Deleted 46 match Comma Delimiter: Approximate Match 65 match FINDER: Find Matching Records in SFL File 100 match FNDFOR: Find Matching Entry in SFL File 118 match Semicolon Delimiter: Exact Match 65 memory A-Z Memory Variables 77 memory Assign Memory Variable Input Stream 78 memory Assign Memory Variable Output Stream 77 memory Memory Variable F Initialization 78 memory Memory Variable Initialization 78 menu Example Menu 39 97 menu Generating the INDEX File from a Menu 69 menu Invoking INSKEY from a Menu 68 menu Invoking MENU 31 menu Menu 30 menu Menu Format File 31 menu MENU Operation 32 menu MENU: CUSP Chain Manager 30 menu Passing User Variable between Menus 31 menu Using CUSP chaining from a menu 62 mode $M Select Termination Mode 85 mode Command Input Mode 42 63 mode Data Input Mode 42 mode Form Editing Mode Commands 46 mode Input Modes 42 63 mode Keyword Input Mode 63 mode Termination Modes 85 monitor CMDDSP: Dispatch Command to Monitor 99 move Cursor Movement Keys 43 64 multi INSKEY: Multi key Database Inspection CUSP 62 multi Multi page Database Design 27 multiple Multiple database access 103 multiple Multiple Keywords 66 name $( Input Field Name from Terminal 56 name $~N Operator: User Name 37 name Database Name 3 name Field Name 4 7 88 name Field Name in Variable 88 name Function 8: Database Name 106 name NAME argument 18 20 new CREATE: Create a New Record 45 new NEW: Create a New Record 46 new NEW: Create a New Record 46 new PAGE: Select New Page 47 next $N Next Record 86 next N Command: Next 67 note Application Notes 71 number $~J Operator: Job Number 37 number $~P Operator: Project Number 37 number $~Q Operator: Programmer Number 37 number Function 6: Key Number 106 Numeric .NUM. Numeric 5 numeric Extracted Data: Numeric Fields 88 numeric Numeric Constants 53 numeric Numeric Limit Check 5 DBSMNG:INDEX 135 numeric Numeric Variables 53 oef $C Clear OEF 56 oef OEF Initialization 56 oef OEF Setting 56 omit Omitting Sorter File 75 online INSPECT: Online Database Query CUSP 70 open Open SFL File 118 operation MENU Operation 32 operation Operational Description 61 operation REPORT Operational Overview 76 operation SELECT Operation 52 operator $B BCD Operator 82 operator $F Format Operator 84 operator $T Test Operator 86 operator $~( Operator: User Variable 37 operator $~A Operator: Device Assignment 37 operator $~J Operator: Job Number 37 operator $~N Operator: User Name 37 operator $~P Operator: Project Number 37 operator $~Q Operator: Programmer Number 37 operator $~V Operator: Flag Virtual Line 37 operator + BCD addition sub operator 82 operator - BCD subtraction sub operator 82 operator .EQ. Boolean Operator 57 operator .GE. Boolean Operator 57 operator .GT. Boolean Operator 57 operator .LE. Boolean Operator 57 operator .LT. Boolean Operator 57 operator .NE. Boolean Operator 57 operator D BCD decrement sub operator 83 operator F BCD format sub operator 83 operator I BCD increment sub operator 82 operator Imbedded Operators 57 operator Operators 33 operator REPORT $< (field data) sub operators 82 operator REPORT $BF (BCD format) sub operators 81 operator REPORT $T (TEST) sub operators 81 operator REPORT ($X) Operators 76 operator REPORT Main Operators 80 operator REPORT Operators 80 operator SELECT Operators 54 operator Summary of Operators 80 operator Z BCD zero sub operator 83 optional Optional SORTER Switches 60 order /R Reverse Order Switch 60 output $) Output to Terminal 35 55 89 output $; Suppress RPT Output 89 output $O Output Stream 86 output Assign Memory Variable Output Stream 77 output Assign RPT File Output Stream 77 output Assign Terminal Output Stream 77 output Function 21: Output Character 108 output Function 23: Output Line 109 output Output Buffer 52 57 output Output Enable Flag 52 56 output Output File 52 overflow Core Common Overflow 37 overhead Overheads 103 overview Functional Overview 1 overview General Overview 49 DBSMNG:INDEX 136 overview Implementation Overview 2 overview REPORT Operational Overview 76 page F Command: Flip page 67 page Key Page 7 page Key Page Labels 21 page Multi page Database Design 27 page PAGE argument 19 21 page PAGE: Select Page N 46 47 page TOP: Edit Field at Top of Page 47 pages Pages 7 pagination $P Pagination 86 parameter $~ Substitute System Parameters 37 parameter Parameter File 9 parameter Read SFL File Parameters 116 parameter Setup SFL File Parameters 117 passing Passing User Variable between Menus 31 passing Variable Passing Between CUSPs 54 pause /P Pause Switch 60 peek Peek at SFL Header Block 117 poke Poke into SFL Header Block 117 position $: Position 58 89 position Function 25: Position Cursor 109 position Position at Specific Entry 118 position Position at Specified Record 118 preamble Preamble 14 primary ! Primary Files 77 print Print Command 67 process $E Terminate if all Records Processed 84 process All Records Processed Event Handling 80 program $E End of Program 55 program Chain to Specified Program 117 program Interfacing to User Programs 51 programmer $~Q Operator: Programmer Number 37 project $~P Operator: Project Number 37 protect PROTECT argument 18 protect Protected Fields 5 protect Specifying a Protected Field 27 purge Channel Purging 104 purge PURGER: Purge Database Records 101 put Function 3: Field Put 105 query INSPECT: Online Database Query CUSP 70 quiet $Q Quiet CUSP Chain 36 range $R Range Specification 55 rd RD argument 18 20 read Function 0: Record Read 104 read Function 4: Read Keys 105 read Read SFL File Data 117 read Read SFL File Parameters 116 record $E Terminate if all Records Processed 84 record $N Next Record 86 record Accessing Records 44 record CLEAR: Clear out Record 47 record CREATE: Create a New Record 45 record DELETE: Mark Record as Deleted 46 record EDIT: Edit an Existing Record 46 record Explicit Record Access 44 record FINDER: Find Matching Records in SFL File 100 record Function 0: Record Read 104 record Function 1: Record Write 104 record INSPECT: Examine Record 46 DBSMNG:INDEX 137 record Keyed Record Access 45 record NEW: Create a New Record 46 record Position at Specified Record 118 record PURGER: Purge Database Records 101 record Record Selection 51 record Record Size 51 52 record Recovering DELETED Records 46 record Using Keywords to Identify Records 65 recover Recovering DELETED Records 46 refresh REFRESH: Refresh Screen 48 repeat ^@ Repeat Block Sequence 9 50 74 report Invoking REPORT 75 report REPORT $< (field data) sub operators 82 report REPORT $B (BCD) suboperators 81 report REPORT $BF (BCD format) sub operators 81 report REPORT $T (TEST) sub operators 81 report REPORT ($X) Operators 76 report REPORT Cycle 76 report REPORT Main Operators 80 report REPORT Operational Overview 76 report REPORT Operators 80 report REPORT Variables 77 report REPORT: Report Writer 73 restart $R Restart 35 return [RETURN] Implicit Exit 45 47 66 return Function 11: Return Date (Internal Format) 106 return Function 30: Return Field Attributes 110 reverse /R Reverse Order Switch 60 rewind $* Marking Rewind Point 55 rewind Rewind File 118 rewind Rewind SFL File 116 rewind Rewinding the Format File 53 76 right $> Right Justify 57 rn rn 5 rpt $; Suppress RPT Output 89 rpt Assign RPT File Output Stream 77 rpt RPT 9 ruler $R Define Ruler 86 screen Function 19: Erase Screen 108 screen REFRESH: Refresh Screen 48 screen Screen Coordinates 6 screen Screen Initialization 33 screen Screen Layout 7 41 63 section $* Section Delimiter 33 90 section $\ Section Delimiter 89 section $TE End of Section Marker 87 section $TR Special End of Section Marker 87 section Body Section 79 section Display Definition Section 32 section Function Definition Section 32 section Header Section 79 section Trailer Section 79 select $M Select Termination Mode 85 select Creating the Format file for SELECT 72 select Example MENU and SELECT Format Files 97 select Function 9: Database Select 106 select I/O Stream Selection 77 select Invoking SELECT 51 select PAGE: Select Page N 46 47 select Record Selection 51 DBSMNG:INDEX 138 select SELECT Operation 52 54 select SELECT: Selective Information Extractor 49 semicolon Semicolon Delimiter: Exact Match 65 sequence ^@ Repeat Block Sequence 9 50 74 sequence DBSFOR Calling Sequences 104 sequence Escape Sequence 9 49 74 sequence Including VT100 Escape Sequences 38 sequence Underline Sequence 9 50 74 sequence Uparrow Sequence 9 49 73 set Function 31: Set Field Attributes 110 set Setting up a Database 11 25 set Setting up INSKEY 68 set Setting up the HELP File 69 set Setting up the INDEX File 68 72 set Setup SFL File Parameters 117 sfl $A Append to Existing SFL Contents 55 sfl Close SFL File 116 sfl FINDER: Find Matching Records in SFL File 100 sfl FNDFOR: Find Matching Entry in SFL File 118 sfl Open SFL File 118 sfl Peek at SFL Header Block 117 sfl Poke into SFL Header Block 117 sfl Read SFL File Data 117 sfl Read SFL File Parameters 116 sfl Rewind SFL File 116 sfl Setup SFL File Parameters 117 sfl SFL File 10 50 74 50 sfl SRTFOR: SFL File I/O Support 116 sfl Write to SFL File 117 single INSPECT: Online Database Query CUSP 70 size Entry Size 75 size Field Size 4 size Function 7: Database Size 106 size Record Size 51 52 skip $T# Skip if Variables Not Equal 87 skip $T+ Skip if Variable Greater Than 0 87 skip $T- Skip if Variable Less Than 0 87 skip $T0 Skip if Variable is 0 87 skip $T= Skip if Variables Equal 87 skip $T= Skip if Variables Equal 87 slash Slash Function Execution 33 sorter Chain to SORTER 117 sorter Interfacing to SORTER 61 sorter Omitting Sorter File 75 sorter Optional SORTER Switches 60 sorter SORTER: Sorting Utility 60 sorter Using SORTER 60 sorting Sorting the INDEX File 69 source Sources 10 spare SPARE Macro 21 special $TR Special End of Section Marker 87 special Function 15: Special Database Initialize 107 special Special Fields 5 6 15 27 special Special Function Keys 7 43 64 specify $R Range Specification 55 specify Specifying a Key Field 27 specify Specifying a Protected Field 27 specify Specifying Fields 26 specify Specifying Labels 26 specify Specifying the Field Type 26 DBSMNG:INDEX 139 srtfor SRTFOR: SFL File I/O Support 116 st st 5 startup Using a startup file 62 statement Boolean Statements 56 statistic /S Statistics Switch 60 stream $I Switch Input Stream 85 stream $O Output Stream 86 stream Assign Format File Input Stream 77 stream Assign Memory Variable Input Stream 78 stream Assign Memory Variable Output Stream 77 stream Assign RPT File Output Stream 77 stream Assign Terminal Input Stream 77 stream Assign Terminal Output Stream 77 stream I/O Stream Selection 77 structure Format File Structure 79 suboperator REPORT $B (BCD) suboperators 81 substitute $~ Substitute System Parameters 37 subtraction - BCD subtraction sub operator 82 suggestion Hints and Suggestions 24 summary Summary of Operators 80 support CONFOR: Core Common and Console Support 115 support SRTFOR: SFL File I/O Support 116 support XXXFOR: General FORTRAN Support Routines 115 suppress $; Suppress RPT Output 89 switch $I Switch Input Stream 85 switch $S Switch 58 switch /P Pause Switch 60 switch /R Reverse Order Switch 60 switch /S Statistics Switch 60 switch Optional SORTER Switches 60 system $~ Substitute System Parameters 37 system D4: System Date 54 tabing tabing 44 64 target Target 70 target Target Fields 70 71 template Creating the TEMPLATE 23 27 template Database Template 3 52 75 template DBSSTS: TEMPLATE Disassembler 100 template DBSTPL: Template Compiler 100 template Example Template Definition File 22 template Template 9 template Template Definition File 13 template Template Definition Macros 16 terminal $# If Terminal NOT of Type XXX 34 terminal $( Input Field Name from Terminal 56 terminal $( Input from Terminal 35 terminal $) Output to Terminal 35 55 89 terminal $= If Terminal of Type XXX 33 terminal $? Input Data from Terminal 55 terminal @ Terminal 77 terminal Assign Terminal Input Stream 77 terminal Assign Terminal Output Stream 77 terminal Terminal 40 terminate $# Terminate if Variables Not Equal 89 terminate $= Terminate if Variables Equal 89 terminate $E Terminate if all Records Processed 84 terminate $M Select Termination Mode 85 terminate BYE: Terminate Execution 45 terminate Termination Mode 85 terminator $! Action Block Terminator 35 DBSMNG:INDEX 140 terminator $% Conditional Block Terminator 34 terminator Terminators 88 test $T Do Tests 56 test $T Test Operator 86 test REPORT $T (TEST) sub operators 81 top TOP: Edit Field at Top of Page 47 tpl Example TPL Card 29 tplrep TPLREP CUSP 74 trailer Trailer Section 79 trim $< Trim 57 type CRT type 31 type Field Type 4 type Specifying the Field Type 26 type TYPE argument 18 19 underline Underline Sequence 9 50 74 uparrow Uparrow Sequence 9 49 73 uppercase $^ Uppercase 58 usage Usage 50 75 user $D Define User Variable 35 user $~( Operator: User Variable 37 user $~N Operator: User Name 37 user Interfacing to User Programs 51 user Passing User Variable between Menus 31 user User Variable 31 using Using a start up file 41 62 using Using BACKUP in an Application 95 using Using CUSP chaining from a menu 62 using Using DBSFOR 103 using Using INSKEY 67 using Using INSPECT 71 using Using Keywords to Identify Records 65 using Using SORTER 60 using Using Special Fields 6 utility BACKUP: Backup Utilities 93 utility SORTER: Sorting Utility 60 variable $# Terminate if Variables Not Equal 89 variable $= Terminate if Variables Equal 89 variable $D Define User Variable 35 variable $T# Skip if Variables Not Equal 87 variable $T+ Skip if Variable Greater Than 0 87 variable $T- Skip if Variable Less Than 0 87 variable $T0 Skip if Variable is 0 87 variable $T= Skip if Variables Equal 87 variable $T= Skip if Variables Equal 87 variable $~( Operator: User Variable 37 variable A-Z Memory Variables 77 variable Assign Memory Variable Input Stream 78 variable Assign Memory Variable Output Stream 77 variable Date Variables 54 variable Field Name in Variable 88 variable Literal Variables 54 variable Memory Variable F Initialization 78 variable Memory Variable Initialization 78 variable Numeric Variables 53 variable Passing User Variable between Menus 31 variable REPORT Variables 77 variable User Variable 31 variable Variable Passing Between CUSPs 54 variable Variables 53 virtual $~V Operator: Flag Virtual Line 37 DBSMNG:INDEX 141 vt100 Including VT100 Escape Sequences 38 wait Input Wait 33 write Function 1: Record Write 104 write Function 5: Write Keys 105 write Write to SFL File 117 writer REPORT: Report Writer 73 zero Z BCD zero sub operator 83 DBSMNG:INDEX 142 ���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������