Pick Database - A "Basic" Explanation

Pick:

• Pick Database is since 1965 and produced by Don Nelson and Richard Pick

• Raining Data Corporation: merging Pick Systems® and Omnis Technology Corp.

Simple Model of Pick:

• TCL:  Terminal Control Language 
• PROC: Consist of TCL Commands,  like shell programming in Unix

• Editor / ED :  Raining Data  editor to create, maintain and delete virtually any element within the database.
• Database Manager:  Responsible for the management of all aspects of the files and interfaces directly to the operating system. It determines the file type (hashing method), space allocation, maintains file integrity via various utilities.

• INFO/ACCESS: Query Language : LIST , SORT , COUNT etc.

• LIST CUSTOMERS : lists items in a file. Item-Ids.
• LIST CUSTOMERS LAST-NAME STREET CITY STATE :  LAST-NAME, STREET, CITY and STATE are Attributes.
• SORT CUSTOMERS BY LAST-NAME

• Basic: BP: Procedure Language 
• MDS : Master Dictionary of the System.
• MD: Master Dictionary for each Account and Each file to define structure of the Account and File.
• File: Each File has Dictionary and Data Part to define structure of Attributes and Contain Data part.

• BASIC programs (source code) are created using the Editor processor.

ED{IT} file.name itemID

An example of creating and filing a BASIC program named COUNT:

• BASIC programs are compiled using the BASIC command from TCL.

BASIC file.name {item.list} {(options)}

 The BASIC  compiler stores the object code for a BASIC program in the dictionary of the same file as the source code, using the same item ID.

• Compiled BASIC programs can be run by issuing the RUN command at the TCL level.

RUN file.name itemID {(options)}

• Compiled BASIC programs can be cataloged into the current master dictionary with the CATALOG command. This allows the cataloged program name to be used as a command from the TCL level.

CATALOG file.name {item.list} {(option)}

• MultiValue Database (MVDB or MVDBMS) Structure: 

• Variable length files, items, and attributes
• Multi-value (and sub values) within attributes
•  Unlimited file and item size (up to the size of  the disk)
• Disk space is divided up into frames. 
• A frame is the basic I/O unit: 500 to 4,000 bytes depending on the implementation on OS.
• Hierarchical organizational structure with a SYSTEM file at the top. MDS file: defines all the ACCOUNTS on the database.
• Each ACCOUNT consists of 1 Master Dictionary, the MD.  The MD, like any other file, consists of items. These items contain all the verbs, file definitions, pointers to files in other accounts. This allows one to customize each account to be able to execute only those verbs and access/update only those files that are defined in its MD. Accounts are set up by the user and can be activity-specific (e.g. a SALES account, used by several people), or user-specific (e.g. MARY, used only by Mary), or in whatever other way suits one's needs. Accounts are collections of logically related files.
• A file definition basically specifies which frame number the file starts in (called the base frame id, or FID) and how many "groups" are assigned to it as primary file space, (called the "modulo"). On creation of the file, <modulo> number of contiguous frames are allocated, starting at the base.
• The system applies a hashing algorithm to each record key (item-id) as it writes it to that file to decide into which group to put it. When a frame in a group fills up with items, a new frame is automatically attached to it from anoverflow table which keeps track of unused frames. Thus a group always consists of at least one frame, but as time goes by and more items are added into the group, it can come to consist of many frames. Files that have many overflow frames become slow to access, but the modulo can be reassigned to provide for more groups, thus reducing the number of overflow frames. item-id "hashes" to a file.
• There is only one file type: Variable Length.
• Each file definition in the MD is actually a definition for the dictionary of the data file in question.
• With an attribute definition item it's possible to "translate", or retrieve, data from other files: Ref. Field concept: thus making the MVDB a relational database.
• Appropriate functions are provided as part of the system to convert a date from practically any external format.
• MVDBMS has been Y2K compliant.
• Numeric values should always be stored as integers, conversion functions are provided.
• Only one file structure available. It is known as a random access file structure because records physically reside in the file in random order.
• Pick System treats each item as a string of characters, there is no concept of "type" of fields.
• In the Pick System, all files and items are accessible. From the data files up to the system files, everything is available to the user/programmer, who can greatly enhance the functionality of the system.
• PICK/BASIC LANGUAGE:
• Allows multiple statements to be put on one physical line (attribute) provided that each statement is separated by a semicolon (;).
• Literal constants are any strings enclosed in single or double quotes, or between backslashes ( \ ). maximum item size of 32K.
• Variable: In PICK/BASIC, no concept of data type exists.
• There are two types of data used in Pick/BASIC: numeric and string.
• A variable can be a simple variable, an array element, a dynamic array element, or a substring.
• two types of arrays: dynamic arrays and dimensioned arrays.
• Dimensioned arrays: defined with the dim statement, specifies the name of the array and the number of elements in the array. and limited to two dimensions (rows and columns).
• A dynamic array is a string containing attribute marks, value marks, and/or subvalue marks which are used as field delimiters. All elements within dynamic arrays are separated by one of these delimiters. Dynamic arrays do not have a fixed size nor are they dimensioned. Subscripts in dynamic arrays are enclosed in angle brackets (<>). A dynamic array may be an element in a dimensioned array.
• Format String: justification, precision, scaling, and credit indication. The entire format string is enclosed in quotation marks. A format string literal can immediately follow the string it is to format.
• substring = string[m,n]. S is the string "ABCDEFG", then the current value of S[3,2] is the substring "CD".
• string[m,n] = substring. S above ("ABCDEFG") is to have characters 3 to 5 inclusive replaced by the string "123" the assignment S[3,3]="123". Result is "AB123FG".
• Statement Syntax:
• REM, or an asterisk
• (*), or an exclamation point (!) at the beginning of a program statement.
• REM THESE BASIC STATEMENTS
• ! DO NOT AFFECT
• * PROGRAM EXECUTION
• Spaces appearing in a program line (which are not part of a data item) are ignored.
• PROMPT ":" /* Default prompt character is a question mark.
• equ True to 1
• PRINT "PLEASE ENTER YOUR NAME " : /* Display on Terminal.
• PRINT (FORMAT) MASKING : an L for left-justified or R for right- justified, D for date justification,*  Fills output with asterisks, #  Fills output with blanks, %  Fills output with zeros.
• CRT @(0,23):@(-4):Function         Description
  @(-1)            Clears the screen.
  @(-3)            Clears from the current cursor position to the end of the screen.
  @(-4)            Clears from the current cursor position to the end of the current line.
  @(x,y)           Positions the cursor at column (horizontal axis) "x" on row (vertical axis) "y".
• : : CONCATENATION : FIRST.NAME : " " : LAST.NAME
• "MC":Mask Character:
• "MCT" conversion converts the first alphabetic character in each word of a string to its uppercase form
• The MCU code converts all of the alphabetic characters to uppercase.
•  The MCL code converts all of the alphabetic characters to lowercase.
•  The MCN code retrieves all the numeric characters from the string.
•  The MC/N code retrieves all the nonnumeric characters.
•  The MCA code retrieves all the alphabetic characters (upper- or lowercase) from the string
• The MC/A code retrieves all the non-alphabetic characters.
• MR conversion is used to convert numeric amounts to their internal equivalents.
• Internal
  format                  Conversion       Result
  123 Main Street            MCU           123 MAIN STREET
  123 MAIN STREET            MCL           123 main street
  123 MAIN STREET            MCN           123
  123 MAIN STREET            MCA           MAINSTREET
  123 MAIN STREET            MCT           123 Main Street
  SEAN O'BRIEN               MCT           Sean O'Brien
  MEAGAN MCDONALD            MCT           Meagan Mcdonald
• INPUT NAME /* Input from Key board and stored in NAME.
• IF NAME = "" OR NAME = "QUIT" THEN STOP /* IF Statement.
• SUBROUTINE MAILDEL(FILEDIR,FILENAME)
• RETURN
• Compiler Directives:$ is considered to be a compiler directive.$CHAIN $COMP $DEFINE $IFDEF $IFNDEF $INCLUDE $INSERT $OPTIONS UNDEFINE $TRUE $T $FALSE $F
•  $INCLUDE PGM.FDEF  ABORT.LOG.FDEF.
• $INSERT SPELLER.COMMON
• OPEN 'LETTER-HEADERS' TO F.LETTER.HEADERS ELSE STOP
• READ REC FROM FILE, I /* I is Item Id
• READU : With Lock
• WRITE REC2 TO FILE, "MGR.":I /* "MGR.":I is key
• WRITEU : With Lock
• openseq "DATA-CR","GLEEMSTRI.M" to CurrentMasterFile
• readseq MasterRec from CurrentMasterFile
• READV D3DIR FROM F.LETTER.HEADERS,"D3DIR",1 ELSE STOP 202,'D3DIR' :  reads variable from file specified by Item-Id and Attribute number.
• READU SERVER.REC FROM SERVER.FILE, SERVER.KEY LOCKED
• remove Suffix from Suffixs setting MoreSuffix
• delete VocFile, "QFILE".
• SELECT BCC /* selects all items from a file, The data selected is placed in an internal select-list, to be accessed by a subsequent READNEXT statement.
• clearselect
• readnext
• remove
• execute x
• CALL @HUSHIT(TRUE$)
• CHAIN 'LOGOUT'
• deffun Maxs(a, b)
• date()
• time()
• TIMEDATE() :  produces output in the form: 10:17:36 12 DEC 1997
• loop
• UNTIL
• repeat
• for
• next
• while
• BREAK OFF                   ; * DISABLE BREAK KEY. Disabling the break key prevents the operator from interrupting the program
   BREAK ON                                    ; * ENABLE BREAK KEY
• begin case
• case Idx eq DictRec<2>
• case 1
• end CASE
• continue
• LOCATE(CFILENAME,TEST;POS) /*CFILENAME is searched in TEST and Position is returned in POS if found or not.
• LOCATE(CFILENAME,TEST;POS) THEN
      CRT "WAITING ON BCC.FLG FILE TO BE DELETED"
       RQM 4
      END ELSE
           ACK = 1
  END
• RQM 4 /* suspends execution of a program for a specified number of seconds.
• exit
• EXECUTE "!rm -f ":FILEPATH CAPTURING TEST RETURNING ERRMSG /*Executes Unix command  rm and returns output in CAPTURING and Error code in TEST.EXECUTE command-expr [CAPTURING cvar] [RETURNING rvar] {optional.clauses
• DATA '/tmp/splitter.wrk' /*  stores the specified data for use by subsequent input requests.
• end
• stop
• GOTO 10 /Not a Line No but Numeric Label
• GOSUB 1000
• page
• printer on 
• printer off 
• printer close
• COMMON /DEVSYS/ VOC.FILE /* Common Sorage Allocation 
• ICONV(WORD,'S') /* Internal presentation conversion
• oconv(DATE(),"D2/") /* Output presentation conversion for output device
• convert "/" to "" in BatchDate
• STR(' ',3*(I-1)) /* Build a string of particular Char. of specified Len
• index(Field,@SM,1))
• NUM(RESPONSE)  /* String to Number conversion.
• SQRT(RESPONSE) /*Function 
• ALPHA(RESPONSE) /*Function
• LEN(RESPONSE) /* Function
• MOD(LENGTH,256)
• CHAR(((128 + LENGTH) - LOW))
• ABS(RESPONSE) /*Function
• COUNT(WORD.STRING," ") : determine the number of occurrences of a character, or a string of characters, within another string of characters.
• DCOUNT ("XXXXXXXXXX","XXX") :  behaves exactly like the COUNT function plus 1.
• TRIM(WORD.STRING) : all leading and trailing blanks are removed, and any occurrences of two or more spaces within the string are replaced by a single blank.
• RND(10) + 1 ; * GENERATE THE RANDOM NUMBER.
• REM(NUMERATOR,DENOMINATOR)/*returns the remainder of a numeric expression
• END /* Block of Statements by IF .. THEN ... END.
• EQUATE ATTRIBUTE.MARK TO CHAR(254) /*also used to assign constants
• SLEEP NAPTIME ; /*put a process "to sleep" for a certain period of time
• The CHAR function converts a decimal integer into its ASCII equivalent.
• The SEQ function is exactly the opposite of the CHAR function. produces the decimal equivalent of any ASCII character:
• The STR function is used to generate or print a string of characters of a predetermined length
• SPACE(15) /* creates spaces of 15 Characters.
• the MATCHES relational operator:checks data against a pattern, "N" for numeric, "A" for alphabetic and "X" for wildcards (any character).
• SOCIAL.SECURITY.NUMBER MATCHES "3N'-'2N'-'4N"
• TEST.DATE MATCHES "2N'-'2N'-'2N"
• ALPHA.STRING MATCHES "0A"
• "2N'-'2N'-'2N"            12-01-97: OK       12/01/97 : Not OK
  "1A2N1A3N"                A22T003: OK       A2T03 : Not OK
• ED BP EX.001 /* Editor invoked and Basic Program name EX.001 created.
•  .I<cr> /* Editor Command , Insert Lines.
• . FI /* Finish Insert , Editor Command.
• >BASIC BP EX.001<cr> /* Compile Program 
• >CATALOG BP EX.001<cr> /* Catalog the compiled Program.
• >EX.001<cr> /* Execute the Program.
•  

• Terminal Control Language (TCL):

• system-level command language with system-defined or user-defined statements that can be executed individually or sequentially.
• System-defined statements are called TCL verbs or commands.
• User-defined statements are: macros, menus, PROC's, and cataloged Pick/BASIC programs.
• The first word of a TCL statement must be either a system verb, macro, menu, PROC or cataloged Pick/BASIC program.
• TCL prompt ":" (colon) (or ">".
• TCL command stack facilities are provided
• .L : List most recent statements.
• .X: Executes last statement.
• TCL Command editing:
• CTRL+H: Back space.
• CTRL+X: Delete the command
• CTRL+W: Delete Word
• The "tcl-stack" file stores every TCL command.
• Access commands. Access is a system-level information retrieval language that allows users to query data bases without writing complex programs.
• an-ad hoc data query language.
• Complex data calculations and output formatting.
• The "ss" (spread sheet) connective allows printing out Access reports in spread-sheet format.
• Use of b-tree indexes has increased the speed and performance of Access.
• Spooler commands. These commands control how information is output to the printer.
• Proc: Consist of TCL Commands : JCL and Shell Programming
• PROC processor:
• PROCLIB is a file that contains Procs supplied with the system.
• LISTPROCS is a system Proc that lists all the Procs in file PROCLIB in dictionary format.
• SYSPROG account has additional Procs and programs stored in the SYSPROG-PL (SPL is a synonym for it)  file.
• Proc (initial capitalization) refers to a procedure
• Proc is to move data between input and output buffers, using arguments passed from the command line, user-prompted input, and stored data and commands to build a TCL statement.
• TCL or INFO/ACCESS statements called Procs.
• Primary input buffer
• Secondary input buffer
• Primary output buffer
• At any given time, one input buffer and one output buffer are active
• PROC Buffer. A buffer contains parameters which are delimited by spaces. A buffer pointer points to the current parameter.
• Secondary output buffer (also known as the STACK)
• TCL Processor
• PQ to identify it to the system as a Proc
• H command places the literal string in the output buffer
• A command copies user input to the output buffer.
• P command sends the contents of the output buffer to the TCL processor.
• O command displays a string of text on the user’s terminal screen.
• IP command reads the information entered by the user into the input buffer. It prompts for the input with a colon (:).
• GO 10 transfers control to the command line that is labelled 10
• X command terminates the Proc and returns control to the TCL processor
• IF command provides for conditional branching.
• C must be the first character on a comment line
• ()([DICT] filename proc-name): create Proc
• IT Command: Tape Input
• IH Command: replaces the current parameter in the input buffer with text
• +/- Commands: + (add) command adds an integer to the current parameter in the input buffer and the – (subtract) command subtracts an integer from this parameter.
• RI Command: Reset Input
• F Command Moves the active input buffer pointer forward to the next parameter.
• B Command Moves the active input buffer pointer backward one parameter.
• S Command The S (set) command moves the active input buffer pointer to a specific parameter.
• LIST CUSTOMERS WITH LAST-NAME = “JOHNSON” LAST-NAME FIRST-NAME STREET CITY STATE : Access Statement: List Customers with Last name as JOHNSON with other fields.
• SORT-ITEM MD WITH D/CODE = "PQ": INFO/ACCESS command: sorts and displays all items in the Master Dictionary whose first line is PQ.
• EDIT/ED Command : Editor of Raining Data
• ED [IT] [DICT] filename item-list [(options)]
• Option: D allows the file definition item to be edited.
• EDIT PROCLIB CUST-SORT /*Edit Proc item CUST-SORT from file PROCLIB
• EDIT BP CUST-ORDER /*Insert Mode creates the new item CUST-ORDER in file BP.
• ED DICT CLIENTS LNAME
• .I : Insert Mode
• .L: List next line.
• .F: Flip buffer
• .FI: Save to media
• .FS: Save and Exit
• .E: Exit with out save
• .R{n} Replace lines
• .R/X/Y: Replace X with Y.
• .G n : Goto line n.
• .DE4: Deletes 4 Lines from current line.
• .ME [n] / [item-ID] / [m] /*n lines are copied from m line.
• .ME [n] ([DICT] filename [item-ID]) [m]
• .X{F} Undo last replace, delete, insert
• create-file filename dict-modulo data-modulo
• Modulo is number of frames required for Items.
• Frames has to be prime numbers.
• create-file address 3 17 /* File address, 3 is Dictionary Modulo and 17 is Data modulo.
• Create the Attribute-Defining Items:
• ud addresses f.name /* Command File and Attribute. Requires Type, Width and Name. f.name is Attribute.
• U inventory ‘022-47-6391’ /* Enter data in File.
•  Update Processor, a full screen editor
• [u|up|update] file.reference item.id attribute.list {( options)}
• Ctrl xe: Exit Item without filing.
• Ctrl xf:  File the item, then exit it.
• create-index addresses a2:1/* Index on Attribute 2.
• clear-file
•  rename-file
• copy
• delete-file
• steal-file
• u addresses /* Data Entry in addresses File.
• D/Codes:
• A :Attribute-defining item
• C :Connective
• CC: Compiled FlashBASIC programs
• D :File-defining item (D-pointer)
• DC: Compiled FlashBASIC programs (obsolete)
• M :Macro
• ME:Menu
• N :Macro (nonstop)
• P :UP prestore item
• PQ: Proc
• Q: Q-pointer
• S :Substitute attribute-defining item
• V :Verb
• X :Suppress attribute-defining item

• D³ NT of Raining Data :  Database of the NT and Windows 2000 operating system 

• It's a full-featured, high-performance, multi-user/multi-value database that eases the pain of developing next-generation OLAP and OLTP applications for Internet, Intranet and n-tier client/server architectures.
• Easy adoption because all code from legacy Pick, licensed Pick variants and Pick-derivative systems are forward compatible.