H Y P E R D E X HyperShell Database Extension Version 1 U S E R S G U I D E Copyright (c) Text Technology 1992 All rights reserved HyperShell and HyperDex are trade marks of Text Technology dBase is a Trademark of Borland International Clipper is a Trademark of Nantucket Corporation This document cannot be reproduced in full or in part, except with the express permission of Text Technology or an appointed representative. HyperDex Users Guide Issue 1.1 March 1992 Text Technology 66 Kennedy Avenue Macclesfield Cheshire SK10 3DE HyperDex Users Guide Contents CONTENTS 1. Introduction 6 1.1 Who the system is for 6 1.2 Easy database applications 7 1.3 dBase III and IV compatibility 7 1.4 The database extensions to HyperShell 8 1.5 Variants of HyperDex 8 1.6 Developer distribution 9 1.7 Warning and Limitation to liability 9 2. Using HyperDex 10 2.1 Introduction to databases 10 2.1.1 File 10 2.1.2 Database 10 2.1.3 Record 10 2.1.4 Field 10 2.1.5 Record number 10 2.1.6 Current Record 11 2.1.7 Index 11 2.1.8 Key field 11 2.1.9 Lock 11 2.1.10 Example database 11 2.2 Creating a new database 12 2.2.1 Name 12 2.2.2 Type 12 2.2.3 Length 13 2.2.4 Decimal Places 13 2.2.5 Structure specification 13 2.3 Accessing an existing database 15 2.4 Adding records 15 2.5 Accessing existing records 16 2.5.1 TOP of database 16 2.5.2 Next record 16 2.5.3 Other positioning formats 16 2.6 Changing record contents 17 2.7 Writing records randomly 18 2.8 Deleting records 18 2.9 Indexes and keyed access 19 2.9.1 Creating an Index 19 2.9.2 Index creation filter 20 2.9.3 Using an existing Index 20 2.9.4 Indexed access 21 2.10 Finishing with a database 21 2.11 Selection filters 22 2.11.1 Layered filters 22 2.12 Relational Join 23 2.13 Locking 23 2.14 Database Administration 24 2.14.1 Cloning a database 24 2.14.2 Sorting and selecting 24 2.14.3 Compacting the database 25 2.14.4 Optimising the performance 25 2.15 Controlling HyperDex 26 2.16 Memo fields 27 - 3 - Contents Hyperdex Users Guide 2.16.1 Creating and editing memo fields 28 2.16.2 Using memo field data 28 2.16.3 Memo as popup note 29 2.16.4 Memo as named variable 29 2.17 Design considerations 30 2.17.1 Overview of database structure 30 2.17.2 Disk space usage 30 2.17.3 Hybrid applications 31 2.17.3.1 Structure 32 2.17.3.2 Production and maintenance of information 32 2.17.3.3 Access of hybrid data 32 3. Reference 34 3.1 Command line switches 34 3.1.1 Error conditions 34 3.2 Database actions 34 3.3 Database function codes 49 3.4 Initialisation 56 4. Codes and limitations 57 4.1 Configuration strings 57 4.2 Data types 58 4.3 Options 58 4.4 Limitations 59 4.4.1 Restrictions 59 4.4.2 Limits 59 5. Browse Program 60 5.1 Learning Browse 60 5.2 Manual Conventions 60 5.3 Getting Started 60 5.4 Command Line 60 5.5 BROWSE REFERENCE 61 5.5.1 Entry Areas 61 5.5.2 Group entry 62 5.5.3 Menu Selections 62 5.5.4 Scrolling 63 5.5.5 Modify Menu 64 5.5.6 Position Menu 65 5.5.7 Find Menu 66 5.5.8 Edit/Browse Switch 68 5.5.9 Exiting Browse 68 5.6 APPENDICES 69 5.6.1 Appendix A - Compatibility 69 5.6.2 Appendix B - Multiuser Requirements 69 5.6.3 Appendix C - Error Messages 69 1 120 Opening File 69 2 Illegal Date 69 3 Record Not Found 69 5.6.4 Appendix D - Summary of Commands 70 6. Expressions 71 6.1 Constants 71 6.2 Operators 71 6.2.1 Numeric operators 72 6.2.2 Character operators 72 6.2.3 Relational operators 72 6.2.4 Logical Operators 73 6.3 Functions 73 - 4 - HyperDex Users Guide Contents 7. Error Codes 76 7.1 General Disk Access Errors 76 7.2 Database Specific Errors 76 7.3 Index File Specific Errors 76 7.4 Multi-User Errors 76 7.5 Expression Evaluation Errors 76 7.6 Memo File Errors 77 7.7 Extended Routine Errors 77 7.8 Memory Error 77 7.9 Internal Error 77 7.10 Sorting Errors 77 7.11 HyperShell Error codes 77 - 5 - HyperDex Users Guide Introduction HYPERDEX - HYPERSHELL DATABASE EXTENSION 1. Introduction The HyperDex program is an extension to the HyperShell system which handles dBase compatible databases. It provides an extension to the HyperShell scripting language which offers a wide set of commands for the creation, access and maintenance of databases. This guide assumes that you are familiar with using HyperShell and that you have read the main HyperShell documentation. It is also useful to have some familiarity with dBase or a compatible database system, although it will be sufficient to have some knowledge of datafile structures. It is recommended that you read through chapters two and five, and scan through the rest to become familiar with the capabilities. Then, when using the system to create your own application, the reference chapters may be used. The supplied hyperfiles are not comprehensive examples of the capabilities of HyperDex, and indeed only utilise the more simple facilities. 1.1 Who the system is for The HyperDex system is intended for both personal users and applications developers. A readonly runtime program is provided for distribution with applications written by developers. The main purpose of the program is as an information management system, handling the storage and access of information of various kinds, ranging from free format text to structured numeric and date information. The versatile information presentation capabilities of HyperShell make it ideal for a range of applications. It is not the purpose of this program to provide high throughput data processing capabilities, such as are provided with the dBase compatible compilers. It can, however, be used for light to medium throughput data processing applications such as club membership systems and home accounts. A general purpose controlling hyperfile is provided so that the system can be used for simple database creation and access without special coding, but for more complex applications a user written hyperfile is required. The general purpose hyperfile is self documenting, and is not described within this document. - 6 - HyperDex Users Guide Introduction HyperDex offers a hybrid database environment adding a powerful dBase data, index and memo file compatible relational database system to the extensive free-text hypertext capabilities of HyperShell. The two types of information can be utilised together to provide hypertext or key field indexed access to structured and / or textual data. The database memo field data can also be used for information of textual content. 1.2 Easy database applications The hypertext text organisation capabilities can be combined with the structured data and indexing capabilities to readily provide user friendly information systems and catalogues. The support for memo fields allows the use of these for free text in support of the structured data, for data-related menus and for data related processing in the form of scripts. The flexible hypertext front end allows applications to be developed which incorporate popup/pulldown menus and built-in help. The form-fill capabilities can be used for data entry as well as 'query by example' lookups. Record Selection Expressions may be supplied to filter the data. Multiple databases can be 'joined' on an expression, providing full relational database capabilities. Applications such as catalogues and price lists can be distributed using the 'read-only' version of the database. This allows the use of index files for rapid access to data. 1.3 dBase III and IV compatibility HyperDex supports dBase compatible data and index files (.DBF and .NDX), and memo files (.DBT). The dBase-III and dBase-IV compatibility supports databases produced using dBase or compatible systems. The database (.DBF), index (.NDX) and memo (.DBT) file formats may be created, read and updated using HyperDex. The Clipper index file format (.NTX) is not yet supported (this may be provided later). The system does not support dBase program files (.PRG) or other types of supporting file. The memo file format used is the later type, as produced by DBase IV, which re-uses file space when the memo file is modified. Earlier formats, such as dBase-III and PC-File, can be supported by performing a memo file conversion operation (action >y). This should not prevent the data being accessed by dBase-III after conversion. The database access offers networking support through locking control. This is provided automatically, and actions are provided for direct control. - 7 - Introduction Hyperdex Users Guide 1.4 The database extensions to HyperShell The database extensions are implemented as a set of additional actions and a set of additional action modifiers. These can be used in the same fashion as other HyperShell actions and action modifiers. The database actions all start with the '>' character, followed by a single letter action code. These actions provide the commands to give high level database access. The action modifiers start with the '<' character, followed by a single letter function code. These provide access to information and database control. In some cases the actions and modifiers map onto dBase equivalents, so some familiarity with dBase can help you get started more quickly with HyperDex. The other HyperShell functionality is retained in full, although the size of the program prevents it being used with large hyperfiles. It is intended that applications developed using the HyperDex system will utilise small to medium sized hyperfiles and small to large sized databases. 1.5 Variants of HyperDex The main HyperDex program, HDX.EXE, is a superset of the integrated development environment. This means that the changes made to the hyperfile structure are saved at exit time. The database information is 'live', however, and updates will have immediate effect.A full editing version is provided to allow the interactive development of applications. This version also provides full update access to the database data. The database version, HDB.EXE, is intended for distribution by developers, providing update access for the database, but being restricted to read-only access of the controlling hyperfile. The read-only version, HDR.EXE, is restricted to those actions and functions which do not change the content of the database or the hyperfile, and is thus ideal for developers of applications having information for browsing only. - 8 - HyperDex Users Guide Introduction +---------------------------------------+ | Program | Hyperfile | Database | |-------------+-----------+-------------| | HDX.EXE | update | update | | HDB.EXE | readonly | update | | HDR.EXE | readonly | readonly | +---------------------------------------+ 1.6 Developer distribution The HDB.EXE and HDR.EXE variants may be distributed by developers, provided the application is not sold as a general purpose database. The HDX.EXE variant, the control hyperfile and any other hyperfile supplied as part of the HyperDex system MUST NOT be distributed. The HyperShell runtime components, as listed in the HyperShell documentation, may also be distributed. 1.7 Warning and Limitation to liability No liability is accepted for any data lost through use of the HyperDex system, by either a HyperShell licence holder or their customers. It is recommended that regular backups of database data are taken, just as they are for hyperfiles. Also the 'flush buffers' action ( >o ) should be used on a regular basis during a database update session in case of a software or power failure. It is suggested that developers also state a limitation to liability for applications developed using the system. At the time of writing, the system has not been tested on a network. It is not expected that there will be any problems with this, but no guarantee is given for network operation. - 9 - HyperDex Users Guide Using HyperDex USING HYPERDEX 2. Using HyperDex This chapter gives an introduction to the use of HyperDex. It explains the basic concepts of a database and gives instructions on how to design and produce your own databases. For additional assistance with the terminology and design aspects of databases, you are referred to the vast range of published material in support of dBase and other databases. 2.1 Introduction to databases For those of you not conversant with database terminology, the following is a brief overview of what the various terms mean. 2.1.1 File A file is information which is saved on a computer disk. Files have names which identify them. These names are up to eight characters long and are optionally followed by a period and a file name extension which is up to three characters long. MAILING.DBF is an example of a file name with the extension ".DBF". 2.1.2 Database A database is a file or group of files containing all information about a particular subject, usually as a collection of records. For example a bank might keep a customer database. The traditional meaning is of a collection of files used for a particular application, but more recently it has been used to describe each file making up the set of data for an application. 2.1.3 Record A set of related data fields. For example the name and address details of a bank customer. 2.1.4 Field Discrete unit of information specific to a record. eg. the name field of a name and address record. 2.1.5 Record number A serial number allocated to each record written to the database. The record number remains associated with a particular item of information until the database is packed to remove deleted information. - 10 - HyperDex Users Guide Using HyperDex 2.1.6 Current Record The current record is the database record currently being processed, if indeed a record has been selected for processing. Commands exist to move through the list of records and to locate a record on a key field using an index. 2.1.7 Index It may be necessary to examine a database in different orders at different times. A mailing list database might need to be sorted by last name sometimes and by address address at others. It would take a long time to re-sort a large database every time a new order was required. Consequently, index files are created to permanently contain sorted information. An index file contains one sort order for one database. Because information is already sorted, the database displays and locates records quickly using index files. An index is used to provide a fast means of accessing records, and for accessing records sequentially on a different sequence to the records in the file. An index is usually held as a separate file for each key field. 2.1.8 Key field One of the fields in a record (which is usually a field by which the record can be identified, and is usually unique to that record) used as the value given to access the record using an index. 2.1.9 Lock A means of stopping more than one person from accessing a data record or a database at the same time. This is used on network systems to allow a number of users to access and update the same data. 2.1.10 Example database In the following example, the whole set of data forms the database. The columns (eg. Age) are the fields, and the rows of data (eg. A Jones details) are the records. The database file also holds information such as the names of the fields and their lengths and types. - 11 - Using Hyperdex Hyperdex Users Guide Database NAMES +---------------------------------------------------+ | NAME |INTL|AGE| ADDRESS | |--------+----+---+---------------------------------| | Jones | A | 34| 14, The Larches, Bromley | | Smith | K | 38| 17, Long lane, Lilliput | | Taylor | S | 27| The Old Barn, Barnston | | Brown | J | 29| 542, Main Street, Broomfield | | Davies | T | 24| 542, Main Street, Broomfield | | Morris | S | 37| 25, Willow Close, Warrington | | Thomas | B | 24| 8, The Crescent, Ilkley | +---------------------------------------------------+ 2.2 Creating a new database New databases can be created from scratch, or an existing database can be 'cloned' to produce a new one with the same structure. Before creating the database, it is necessary to analyse the data to determine how it should be organised. Many simple applications can be modelled using a single database with simple field types. Other applications may need a number of database files to allow more complex structures to be supported. Such analysis is covered in detail in books on databases, and it is not therefore intended that it will be covered here. A small database may not need to be indexed, so will just consist of a database file, along with the hyperfile to control the application. To create the database, it is necessary to identify the fields which make up the database, along with information about them. For each field, the following information is required:- 2.2.1 Name This is the name by which the field is known (1 to 12 characters, which can be uppercase letters, numbers or the underscore character). Example names are:- ADDRESS_1 ACCOUNT_NUM ENGINE_SIZE 2.2.2 Type This indicates what type of data is to be stored in the field. The possible values are:- C - Character Character string (eg. Name or address) N - Integer Number with no decimal places (eg. an account number) N - Decimal Number with decimal places (eg. a bank balance) - 12 - HyperDex Users Guide Using HyperDex F - Fltg point Floating point number (eg. the speed of light) D - Date Date (eg. 10/10/91) L - Logical True/False or Yes/No field (eg. OVERDRAWN) M - Memo Large, possibly multiple line, variable amount of text. NOTE: The floating point data type should not be used if compatibility with dBase III (and compatibles) is required. 2.2.3 Length This indicates the length of the field, including any decimal point and decimal places. Dates are 8 characters long. To choose the correct length for a field, it is necessary to think of the longest or largest field that might fit in that field, and then to add a small amount for contingency. For instance, the longest name you can think of is 15 characters, so you might allocate 20 characters to hold the field. The dBase file uses a fixed length record format, so you cannot go over the specified length. Also this means that whatever the length of the data, a fixed amount is always used to store it. This means that you should consider the lengths carefully if you are worried about storage space. Large text fields (over 80 characters) might be better stored in 'memo' fields, which are described later. These are variable length, and so do not waste as much space on disk. They are more difficult to handle, however. 2.2.4 Decimal Places This indicates the number of decimal places in a numeric field. For money, this would be 2, for the pence/cents part of the field. 2.2.5 Structure specification The supplied hyperfiles include a simple database creation capability. You may wish to utilise this for creating an example database. It is capable of handling fairly large database specifications. The following information shows the details of how database creation is achieved, and you should read this if you wish to undertake a less simple project, or you wish to tackle this at a lower level. For the database to be created in the required form the details of the fields are assigned to named variables. This is generally done using the HyperShell ZV action. The fields required are as follows:- - 13 - Using Hyperdex Hyperdex Users Guide +--------------------------------------------------+ | Variable | Used for | |------------+-------------------------------------| | fndb | Name of database (as used in >U) | | fields | Number of fields (eg. 4) | | fn1 | Name of field 1 (eg. NAME ) | | ft1 | Type of field 1 (eg. C) | | fw1 | Width of field 1 (eg. 8) | | fd1 | Decimal places in field 1 (eg. 0) | | | | | fn2,3 etc | Names of fields 2, 3 etc. | | ft2,3 etc | Types of fields 2, 3 etc. | | fw2,3 etc | Widths of fields 2, 3 etc. | | fd2,3 etc | Decimal places in 2, 3 etc. | +--------------------------------------------------+ It is a good idea to produce a script file (.HSF) containing the database definitions. This can then be edited and rerun whenever you wish to create a database with a similar structure. For the simple example database above, the actions necessary to define the database are as follows:- ZVfndb=NAMES ZVfields=4 ZVfn1=NAME ZVft1=C ZVfw1=8 ZVfd1=0 ZVfn2=INTL ZVft2=C ZVfw2=3 ZVfd2=0 ZVfn3=AGE ZVft3=N ZVfw3=3 ZVfd3=0 ZVfn4=ADDRESS ZVft4=C ZVfw4=33 ZVfd4=0 Once the database specification is set up in the variables, the following action will create the database. >CNAMES,1 The '1' parameter indicates that the database should not be created if it exists already. Once the database is created (or when an old database is opened), the current database has a 'reference' number associated with it which can be used in certain other operations. The reference number of the current database can be obtained using the function 'zf Action >z clears all named variables starting with the string supplied. In this case all variables starting with 'f' are cleared. This action needs to be used with care. 2.3 Accessing an existing database Of course you may wish to access an existing database created using dBase or a compatible system. In dBase III and IV a database is made available with the USE command. The HyperDex action to do the same is:- >Udatabase This opens the named database ready for use. 2.4 Adding records Having created a database, you can then start to add the data records. Before the data can be entered in a record, a record buffer needs to be set up and its place in the database determined. This is usually done by appending a blank record which can have the fields changed to the required values. This is done with the action:- >A A data record is a set of fields. Fields in HyperDex are a special form of HyperShell named variable. The variable name is the same as the field name. So for the example database, the variables would be called NAME, INTL, AGE and ADDRESS. To assign a value to a field, the action '>V' is used. So, to set up the A Jones record, the following actions would be executed:- >VNAME=Jones >VINTL=A >VAGE=34 >VADDRESS=14, The Larches, Bromley It is not necessary to explicitly write the record away, as this will be performed automatically when a new record is obtained or the database is closed. - 15 - Using Hyperdex Hyperdex Users Guide To duplicate a record with just a few changes, the current record buffer can be appended ready for filling, rather than a blank record, and the key fields changed as required. The action to do this is:- >a 2.5 Accessing existing records There are a number of ways of accessing the records in a database, but most of them result in a selected record becoming 'current'. The fields for the current record are then available as named variables, and the contents may be changed by modifying the values (see below). 2.5.1 TOP of database The simplest way of processing a database is to start at the beginning and step through the records. The action to go to a record is '>G'. To position at the 'top' of the database, the action is:- >GT After issuing this action, the first record in the database is selected as the current record, and the field variables will contain the value of the fields for that record. So, in the example database, NAME would be set to Jones. The variable can be used in the same way as other HyperShell named variables, and the variable token $(NAME) is replaced by the string 'Jones' in this case. 2.5.2 Next record To step to the next record, the action:- >G+1 can be given. This steps one record at a time through the database, until the end. At the end of the database, the EOF (end of file) status is set, and the HyperShell condition code 'z' is set. The following action can be used as an abbreviated form of the above, as it is a common action:- >G 2.5.3 Other positioning formats The general form of the 'go to record' action is as follows:- >Gxxxx Where xxxx represents the record position to move to. - 16 - HyperDex Users Guide Using HyperDex >GT Top (first) record >GB Bottom (last) record >Gnn nnth record >G+1 next record >G+nn nnth next record >G-1 previous record >G-nn nnth previous record 2.6 Changing record contents Once the required record is current, we can modify the data by simply assigning new values to the fields. As the fields are held in named variables, this is done using a special form of the variable assignment. >VINTL=F This changes the initials field to 'F'. The new variable value is used to change the corresponding field in the database record after the assignment. If we step to the next record or go to another part of the database, the updated record will be written to the database. The full specification of the >V action is:- >Vname={value|action modifier} This shows that the 'value' field can be supplied by an action modifier, instead of a literal value. For example, we can prompt for the new initials using the action:- >VINTL=?New initials Or obtain the value from a menu such as in this example:- >VACCTYPE=@MAccount type We can avoid putting incorrect information into fields by validating the data before we do the assignment. This is performed using the following function:- O action. The default values are listed in the section on HyperDex configuration. ZiVBALANCE=$b - 17 - Using Hyperdex Hyperdex Users Guide We can also use an alternative assignment which will do basic data content checking. This takes the form:- >vNAME=value With this, the value to be assigned is checked against the pattern for the type of the field. So, for a numeric field, pattern N is used. If the pattern match fails, the action modifier will fail, and the assignment will not be performed. eg. >vBALANCE=$b NOTE: If the ZV action is used to change the fields value, the new values will not be placed in the records unless a >W action is performed. 2.7 Writing records randomly On occasions, you may wish to explicitly write the current record. You might also wish to write the current record into another record position. This is achieved using the action:- >Wrecno Where recno is the record number to which the current buffer should be written. If it is omitted, it is written as the current record. When you add records to a database, you usually append them at the end, as this is the only place available to write them. You can also insert a new record into a database, moving the remaining records down one position. This requires a lot of disk reading and writing, so should normally be avoided. The action to do this is:- >wrecno Where recno is the record number to insert before, or the current record if this is omitted. 2.8 Deleting records To remove records which we do not require, we use the delete action. This very simply marks the specified (or current) record as deleted:- >Drecno If recno is supplied, it indicates the record number to delete. If it is omitted, the current record is deleted. We can recall a deleted record (assuming the database has not been re-organised or packed), by use of the recall action:- >Rrecno - 18 - HyperDex Users Guide Using HyperDex Again, if recno is supplied, it specifies the record number to recall, otherwise it recalls the current record. To delete a whole range of records, the 'zap' action can be used:- >Zstart,end This specifies the start and end record number of the range of records to be deleted. 2.9 Indexes and keyed access When a database grows beyond a certain size, accessing records becomes very slow when you have to read through the data to get to the record you are after. If one field of the data contains a value that you can supply to identify that record or group of records, you can build an 'index' which uses that field as a 'key'. With an index, you can go straight to a record by supplying its 'key'. 2.9.1 Creating an Index To create an index file, the following format of action is used:- >Xindexfile,expression Where indexfile is the name of the index file (without extension), and expression is an expression involving fields which is used as the index key. In its normal, simplest, case it is just a field name. So, for instance, in our example database, the NAME field could be identified as a key, and an index built. The action to do this is:- >XNAMEIND,NAME This creates an index called NAMEIND, which is keyed using the NAME field. Once this has been done, the index is current. This means that when you use the 'go to' action, the database will act as though it is sorted in the sequence of the key field. If we had been adding records randomly to our NAMES database, the records will now appear in alphabetic order of NAME. See the section on expressions for details on how more complex expressions can be specified. The index, like the database, has a reference number associated with it. This can be obtained using the function 'xref Where ref is the reference number of the index file. The current index is rebuilt if 'ref' is not supplied. 2.9.2 Index creation filter Another thing we can do with indexes in more advanced applications is to use a selection expression (or 'filter') when we are creating the index, so that only a subset of the database records have index entries. This allows us to then step through the selected data very quickly. The selection expression is specified using the '>Y' action as follows:- >Yexpression Where expression is an expression involving the database fields. See the section on expressions for more details. The filter must be cleared using the action '>Y' if we wish to subsequently create a full index. 2.9.3 Using an existing Index Of course, we don't want to have to build a new index each time we use the program. To associate an existing index with the database, we use the action:- >Iindexname Where indexname is the name of the index file. In our example case, this would be simply >INAMIND If we have a reference number available for an index we used earlier in the same session, we can use the index reference number instead of the name:- >Iref A variant of the use database action can specify an index to be associated with the database. This uses the following variant of the command:- >Udatabase,indexname where indexname is the name of the index file. - 20 - HyperDex Users Guide Using HyperDex 2.9.4 Indexed access Having set up the index, we can make use of it to rapidly access a record. This is done using a database function rather than an action, so that we can immediately test the result of the search. The function to do this is the 'seek' function:- u It is not normally necessary to do this as it is performed automatically when you end the program, but you may wish to do it explicitly for other reasons, for instance to close the database and open another one. - 21 - Using Hyperdex Hyperdex Users Guide To close, or stop using, the current index, so that the normal record sequence is used, the action is again the lowercase form of that used to open it:- >i 2.11 Selection filters When we step through the database, we will normally be given every record for processing. In many cases we don't want to process all records, so we test each record as it is read, and ignore it if it is not required. There is a better way of handling this, using what are known as 'filters', which are record selection expressions which restrict the data we are given when we step through the data. The filter expression involves fields in the record, and will, if true, pass the record through for processing. If the expression is false, the record is skipped. The filter action takes the form:- >Fexpression The expression takes the form of a database expression (see section on expressions below). Further selection criteria can be added by subsequent filter actions. Thus the set of records can be narrowed down step by step. If the last filter expression causes too much of a restriction, it can be dropped using the action:- >F The entire set of filters can be dropped using the action:- >f 2.11.1 Layered filters Database query languages generally provide the concept of a 'selection' which is a collection of records resulting from a selection operation which can be refined using further selection expressions. This functionality is provided in HyperDex by applying successive filters to the data. As the filters are 'layers', they can be backed out (one at a time or altogether) if the selection proves to be too selective, and further filters applied. Once the required selection is present, it can be processed using the sequential retrieval mechanism. Apply main filter + refine with further selection criteria + refine further - 22 - HyperDex Users Guide Using HyperDex - drop last filter if too selective + try new refinement = process results 2.12 Relational Join Relational databases require the capability of 'joining' two or more databases on a common field, so that repeated information can be modelled, or so that information can be combined for viewing or processing. The action to perform a join operation is:- >Jexpr,dbref,idxref Where expr is the join expression, dbref is the reference number of the database to join to and idxref is the index for the join operation on that database. For example, if we have a personnel database with a department number in an employees record, and a department database with information about the department, we might join these with the action:- >JDEPT_NUM,$d,DEPNO This joins the current (employee) database with the DEPART database (which has been previously opened, and has a database identifier whose value has been loaded into variable 'd') using the DEPNO index, which is the main index for the DEPART database on the department number key. Note that an index is used to load the secondary record. If an index is not specified, the record number on the secondary file has to be provided by the expression. The joined data can be refreshed using the '>J' action with no parameters:- >J The current join information can be dropped using the action:- >j 2.13 Locking When the database is being used on a network, it is necessary to prevent more than one person changing the same information at the same time. This is done by 'locking' the information. The lock can be applied at the record level or at the database level. HyperDex generally takes care of locking for you, locking records until a new record is selected. - 23 - Using Hyperdex Hyperdex Users Guide There are occasions, however, when you may need direct control over record and database locks. The lock action is as follows:- >Lrecno,wait This locks the record specified by recno. The wait parameter is used to indicate whether the program should wait for the record if it is already locked, otherwise it returns the 'locked' status. Any record previously locked by the current session is freed for others to access. The whole database can be locked by setting recno to -1. The count bytes of the database can be locked using a recno of 0. The locked record is unlocked when another record is locked. It can be explicitly unlocked using the action:- >lrecno Where recno settings are as for '>L'. 2.14 Database Administration A number of actions are provided to allow you to handle database files at a higher level. These actions generally work on a single database. 2.14.1 Cloning a database Suppose you wish to produce a database which is very similar to an existing database. This may be done to simply create a similar database to hold information for a different company, or to add additional fields to an existing database. The structure of the current database can be extracted into the structure specification variables, using the following action:- >E The information can then be modified and a new database created. If extra fields are required, it is difficult to restructure the variables. The recommended solution to this problem is to save the structure as a simple datafile, and to modify the file and read it back in. The supplied control hyperfile provides this capability. 2.14.2 Sorting and selecting To extract a subset of the records of a database into another database, the select action can be performed. This takes the following format:- - 24 - HyperDex Users Guide Using HyperDex >snewname,start,safety Where newname is the new name for the database, start is the first record number to select from the current database, and safety is a flag which, if set to 1, prevents the output file from overwriting an existing database. This selection takes into account any selection filter set up using the '>F' action. To extract a subset of a database, sorted in a different order, the sort action may be performed. This takes the form:- >Snewname,expr,safety Where newname is the new name for the database, expr is a selection expression to select a subset of the database, and safety is a flag which, if set to 1, prevents the output file from overwriting an existing database. This takes into account any selection filter set up using the '>F' action. NOTE: Do not use the select or sort actions on databases which have memo fields associated with them. 2.14.3 Compacting the database After a number of records have been deleted, you will need to clean out the deleted data so that the database file only contains current data. The following action performs this compaction. >P* NOTE: this is an disk intensive operation, and will take a while to perform on a large database. Also it is important that the database is not in use on a network when you perform this operation. The '*' character is required to help prevent this being accidentally performed. 2.14.4 Optimising the performance Performance of large databases, or multiple databases used in complex ways, might be improved by modifying the buffering parameters. The default processing uses a fairly simple algorithm which cannot be optimal for all circumstances. If you experience performance problems, the following information may help you improve performance. You can explicitly allocate a memory block to be used for record buffering when accessing the database. The default is 63k. Using more memory may help speed up the database processing. The action to allocate buffer memory is:- - 25 - Using Hyperdex Hyperdex Users Guide >Bsize Where size is the amount of memory to allocate for buffering. You can also optimise the use of memory buffers for operations on the database. The buffering parameters can be tuned to reflect the kind of operations you wish to perform on the database. You should remember that disk operations are done on a per-buffer basis (ie. if you change a record, and cause it to be written, the entire buffer in which it resides will be written). When reading or writing sequentially, one large buffer should be used (a high buffering factor). This will reduce the number of disk accesses, and speed throughput. When doing random access throughout the database, the buffer factor should be small. A large buffer will be wasted in this case, as most of the data in it will be redundant. The buffering parameters are set using the action:- >btotrex,maxbufs,rexperbuf Sets buffering parameters:- totrex - total number of records to buffer maxbufs - maximum number of buffers rexperbuf - buffering factor - records per buffer totrex indicates the total number of records to buffer. A value of 0 will stop buffering for the database. maxbufs indicates the maximum number of buffers to allocate for the database. rexperbuf indicates the buffering factor. If totrex divided by rexperbuf is greater than maxbuf, then only maxbuf buffers are allocated. A maximum of 64k bytes is permitted for each buffer. The buffer memory can be freed later by an action of the form:- >b0,0,0 Also see the reference section on action >H for details on initialisation configuration. 2.15 Controlling HyperDex Various aspects of HyperDex operation can be controlled to a certain extent, in a similar manner to HyperShell. Strings used in the extensions can be configured using the action:- >Kxstring Where x is a digit representing the configuration item, and string is the value to assign to it. - 26 - HyperDex Users Guide Using HyperDex Also the configuration strings A to Z may be assigned and used as pattern match strings for input data validation. See section 4.1 for details of the configuration strings. There are some run control options which may be set to influence the operation of HyperDex. These are set using the action:- >OXnnnn Where X is a single letter HyperDex option code and nnnn is a numeric value which is assigned to it. For boolean options, a value of 1 indicates ON and a value of 0 indicates OFF. A - set to 1 (true) to cause approximate SOUNDEX matching. B - decimal code for character used to indicate line breaks in memo fields. M - (75) decimal value indicating width to wrap memo field lines at. R - boolean indicating readonly operation S - boolean indicating safety checks for file creation T - boolean - trim leading and trailing spaces off fields U - boolean indicating that index key should be unique W - (6) set to the word width for the SOUNDEX function. The current value of an option can be obtained using the function:- ymemofile.ext Memo fields are held on the database in a 10 byte field, which is the memo field identification within the memo file. It is possible to have two records pointing at the same memo field by copying the memo field variable value. Quite what effect this has on dBase is not known, but it should probably be avoided if compatibility with other systems is required. - 27 - Using Hyperdex Hyperdex Users Guide 2.16.1 Creating and editing memo fields A memo field can be added to a record using the action:- >mfieldname This enters edit mode (or an external editor in HDB) to allow data to be typed in to the memo field. An existing field can be edited in a similar fashion using the action:- >Mfieldname Memo fields will wrap at 75 columns when displayed or edited. This can be changed using database option letter M. eg. to set a wrap width of 40:- >OM40 Memo lines can be forced to break at a certain point by setting a memo line break character. This is done using option letter B (memo break). eg. to mark line breaks with a '|' character,:- >OB124 The break character itself is not used in the text. Memo fields can also contain binary data, such as graphic images. A named variable can be stored in a memo field, without line wrap interpretation, using the action:- >nfield,variable This stores the variable in the memo field of the current record. 2.16.2 Using memo field data The following function can be used to obtain memo field information:- price + Layered filters. + Date, numeric and soundex searches eg. Sounds like Jean + Browsing versatility + Sequentially on one of a number of keys + Using selection filters + Forward,backward,etc. + Can do data lists for 'contents list' equivalents + Other considerations + Can compress text part to bring size down + Can protect text part - 33 - HyperDex Users Guide Reference DATABASE EXTENSION REFERENCE 3. Reference 3.1 Command line switches -ox Where x is a HyperDex option code letter (in lowercase form). This sets the specified option to 1. For example:- -ot will cause option T to be set to 1 (trim spaces from field values when loading variables). 3.1.1 Error conditions The condition code 'z' is set for EOF conditions. It is also set if an attempt is made to step backwards, or to record 0. A special set of error return values are provided, as follows:- 70 - read only - write operation attempted 71 - Out of memory 72 - No memo field present 73 - Error writing memo field 74 - Field fails validation (>v action). Error 25 is returned if the action code is not valid. 3.2 Database actions Database actions start with the > character, followed by a single letter action code. Actions can have up to 3 parameters, separated by commas. The READONLY option can be set to filter allowable actions the list of available action letters is in configuration string 6 (See action >K). Many actions have the lowercase variant as a reverse operation (ie. >U and >u, >J and >j, >L and >l). Other actions are reversed using the same action without a parameter. Some actions return a negative status code, which is indicated in the action description. - 34 - HyperDex Users Guide Reference It is essential to flush the data ( action >o ) or to do a database close operation ( action >u ) to update the database information. This is done by default for all open databases when HyperShell quits. If the program terminates abnormally, or the computer 'hangs', the database contents are not guaranteed. The commands indicated in square brackets (eg. [ APPEND ]) are dBase commands which are the same as, or similar to, the described action. These are provided for those of you familiar with dBase and equivalent languages. Note: The commands are not specified in this form. >A Append blank record >A Appends a blank record (with empty fields) ready for filling. This is the usual method of adding records to the database. The procedure is to append a blank record, and then modify the fields as required, by assigning values to the field variables (see >V). The record should then be written using the >W action, without a parameter. [ APPEND BLANK ] Status returns are:- 0 success -1 error >a Append copy of current >a Appends the current record buffer, after writing any changes, and allows fields to be changed. This is used where a record is to be duplicated for the most part. This must be used with care if a memo field is present, as the memo reference will be duplicated, and if the memo is subsequently modified, results will be unpredictable. [ APPEND ] Status returns are:- 0 success -1 error -2 file locked -3 duplicate key found for a unique key index file - 35 - Reference Hyperdex Users Guide >B Allocate buffer >Bsize Allocates memory block of size 'size' for database buffering operations. The default value used is 63k. Allocating a larger block will speed up some database operations. If not enough memory is available, the status is set. In this case, a smaller amount can be tried. This should be used if you notice poor performance, and you have plenty of memory available. Status:- 0 success -1 not enough memory available >b Set buffering parameters >btotrex,maxbufs,rexperbuf Sets buffering parameters:- totrex - total number of records to buffer maxbufs - maximum number of buffers rexperbuf - buffering factor - records per buffer This optimises the use of memory buffers for operations on the database. The buffering parameters can be tuned to reflect the kind of operations you wish to perform on the database. It should be remembered that disk operations are done on a per-buffer basis (ie. if you change a record, and cause it to be written, the entire buffer in which it resides will be written. When reading or writing sequentially, one large buffer should be used (a high buffering factor). This will reduce the number of disk accesses, and speed throughput. When doing random access throughout the database, the buffer factor should be small. A large buffer will be wasted in this case, as most of the data in it will be redundant. totrex indicates the total number of records to buffer. A value of 0 will stop buffering for the database. maxbufs indicates the maximum number of buffers to allocate for the database. rexperbuf indicates the buffering factor. If totrex divided by rexperbuf is greater than maxbuf, then only maxbuf buffers are allocated. A maximum of 64k bytes is permitted for each buffer. The buffer memory can be freed later by an action of the form:- - 36 - HyperDex Users Guide Reference >b0,0,0 Status:- 0 success -1 not enough memory available See Also: Initialisation parameters [ >H, >h ] >C Create database >Cname{,safety} Creates a database with 'name' If safety = 1, does not overwrite database if it exists The structure of the database must be specified in variables as follows. fn1,fn2,fn3,fn4... field names ft1,ft2,ft3,ft4... field types (see D Delete record >Drecno Deletes record number recno, or current record if no recno supplied. [ DELETE {recno} ] Status:- 0 success 1 record does not exist -1 error See also: Recall deleted record ( >R ) - 37 - Reference Hyperdex Users Guide >E Extract structure >E Extracts the structure of the current database into named variables as follows:- fn1,fn2,fn3,fn4... field names fv1,fv2,fv3,fv4... field names as "$(name)" to expand as value ft1,ft2,ft3,ft4... field types (see F Filter records >Fexpr If expr supplied, it is used to set a filter which restricts the retrieval of records to those matching the expression. If a filter has already been specified, it is added to the list of filters, all then being in effect. This latter is used for refinement of selections. If expr is not supplied, the last filter added is removed from the list. [ LIMIT or SCOPE ] >f Reset filter This resets the filters added using >F, removing all filtering. >G Go to a record >Grec This positions at (goes to) a record in the database. For a joined database, this retrieves the corresponding records in the associated databases. - 38 - HyperDex Users Guide Reference The argument 'rec' can be one of the following:- T Top - first record in database B bottom - last record in database nnn record number nnn +nnn forward nnn records from current (+1 moves to next record) -nnn backward nnn records (-1 moves to previous record) If no argument is supplied, it moves to the next record (equal to +1) The fields in the record are loaded into named variables. These have the same name as the field on the database eg. for the field called NAME, the named variable NAME is assigned. Therefore $(NAME) expands to the value of the name field. [ GOTO number ] [ SKIP +n/-n ] Status:- 0 success 3 end or beginning of file >H,>h Set initialisation parameters >Hdatabases,indexes,indexblocks >hbufferarea,evalbuffer These actions set the initialisation parameters for the database subsystem. The parameters are as follows:- databases - number of database files A memory use optimisation figure only - it is not a limit From 2 upwards. Default 10. indexes - number of index files A memory use optimisation figure only - it is not a limit From 2 upwards. Default 10. indexblocks - number of blocks assigned to index. Avoid using too many if memory is short. From 10 to 100 (50 for Clipper). Default 12. bufferarea - general buffer area for database use. Can assign a very large area if memory is available. From 32000 upwards. Default 64512. evalbuffer - expression evaluation buffer area. The default is adequate for most expressions. From 1000 upwards. Default 3000. - 39 - Reference Hyperdex Users Guide These actions are usually used in the configuration file, but may also be used at other times. If they are specified in the configuration file, they take effect when the subsystem is first initialised. If they are specified subsequent to startup, the subsystem is cleared and re-initialised. Only the >H action will cause the re-initialisation in these circumstances, and the >h action should be specified first if these parameters are to be changed. Status:- 0 success -1 if memory not available See Also: Buffering parameters ( >B, >b ) >I Select index >Iindex Selects an index for use with the current database. The index determines the sequence of the records retrieved, and provides a fast means of accessing records using an index key. If index is numeric, this is assumed to be the reference of a previously opened index, else it is assumed to be the name of a new index to open. [ INDEX indexname ] Status:- 0 success 4 if file not found >i Unselect current index >i Unselect the current index. This causes the normal sequence of the database to be used. [ CLOSE INDEX ] >J Join databases >Jexpr,dbref,idxref or >J - 40 - HyperDex Users Guide Reference This joins another database (dbref) and optional index (idxref) to the current database, on the join expression 'expr', which should involve fields (or record numbers) from both databases. For cases where there is no matching record, a blank record from the secondary file is used. If no argument is given, the join is 'refreshed', i.e. after the prime file is read, the refresh is done to bring in the joined data fields. [ JOIN ] Status: 0 Success -1 Error >j Remove joins >j Removes any joins in existence. >K Configure string >Kxstring Sets the configuration string 'x' to 'string' x can be on one of the following ranges:- 0-9 - general configuration string A-Z - validation pattern a-z - special configuration string See section 4 for a list of the configuration strings. >L Lock record / database >Lrecno,wait Locks record number recno. If wait = 1, waits for record if it is already locked, else returns lock status. If recno = -1, locks whole database. If recno = 0, locks count bytes of database (see ???). This action enables selective record locks or whole database locks to be performed. Locking a record causes a previously locked record to be unlocked. - 41 - Reference Hyperdex Users Guide This is for use when the database is shared on a network. See also: unlock ( >l ), test for lock function ( l unlock record / database >lrecno Unlocks record number recno. If recno = -1, unlocks whole database. If recno = 0, unlocks count bytes of database (see ???). This action removes record locks or whole database locks. This is for use when the database is shared on a network. See also: lock ( >L ), test for lock function ( M Edit memo >Mmemofield or >mmemofield Enters the edit mode on the named memo field of the current record. The lowercase form is for when the field does not yet exist, and creates the named memo field, entering edit mode to allow it to be entered. If using HDX.EXE, the edit mode is used to edit the memo field. If using HDB.EXE, an external editor is used to edit the memo field. This defaults to the note editor (NOTE.EXE) supplied with HyperShell. You can configure it to use your own editor by setting configuration string 5. This should contain the name of your editor, and must not contain spaces. The maximum size of a memo field is 20000 characters. Status: 0 Success See also: display memo as note, get memo data ( n Store memo >nmemofield,variable Stores the named variable in the named memo field of the current record. >O Set option >OXnnn Sets option X to nnn X can be an upper case option letter, from A to Z. A (default 0) Approximation switch for SOUNDEX and SOUNDEQ functions. When set to other than 1, the soundex comparison is done on an approximate basis, rather than a straight soundex basis. It will also cause a different value to be returned by the SOUNDEX function, which represents the approximate value. The values returned under the two settings are incompatible. B (default none) Break character - is the ASCII decimal value of the character used to denote a line break position in a memo field entered through another database program which does not support normal line breaks. M (default 75) Memo width - is the width at which lines in memo fields wrap. The line is wrapped at the start of a word. R (default 0) Readonly - tells HyperDex to act in a readonly fashion. This prevents any updates from taking place on the database. This MUST be set BEFORE the database is opened using the >U action. S (default 0) Safety - tells HyperDex to check before creating databases and indexes to see if the file already exists, and not to overwrite it if so. T (default 0) Trim spaces - Fields are normally returned with leading or trailing spaces. This option causes the leading and trailing spaces to be trimmed when the field is loaded into the named variable. The field is padded with spaces appropriately when it is written. U (default 0) Unique - tells HyperDex that the key of the index is unique, and cannot be duplicated. This option is used to specify that the index key should be unique, and has to be set to 1 before using the >X action if unique keys are required. The unique key information is maintained for that index afterwards, and the option does not require setting otherwise. It is set to 0 by default, indicating that keys need not be unique. - 43 - Reference Hyperdex Users Guide W (default 6) Word length - indicates to what length the soundex conversion should be specified. This can be smaller than 6, but it is not recommended to be greater than 6, as it will overflow the return value if too large, and will cause the comparison to be too selective. See also: get option value [ o Flush output buffers >oref Flushes the output buffers of database reference ref. If ref is omitted, the current database is flushed. If ref is set to -1, all open databases are flushed. This action only has meaning for when buffering has been enabled using the >b action. >P Pack database >P* Packs the current database. The '*' parameter must be supplied for this to work (as a safety measure). When records are deleted from a database, the deleted records remain until they are either recalled or cleared out in a pack operation. The pack operation removes all deleted records. As the record numbers will change during a pack operation, anything which identifies records by record number must be reset to the new number after pack. [ PACK ] See also: Delete record, Recall record [ >D, >R ] Status:- 0 success -1 error -2 database is locked >R Recall record >Rrecno Recalls the deleted record identified by recno (the current record is assumed if recno is not supplied). - 44 - HyperDex Users Guide Reference [ RECALL ] Status:- 0 success 1 record does not exist -1 error >S Sort database >Snewname,expr,safety Sorts the current database on the expression 'expr' into the named database 'newname'. If the safety parameter is set to 1, the operation will fail if the database exists already. [ SORT ] >s Select subset >snewname,start,safety Selects records starting from 'start' into a new database named 'newname'. If the safety parameter is set to 1, the operation will fail if the database exists already. >U Use database >Uname,index Opens database 'name' ready for use, and optionally opens index file 'index' for use with it. If name is numeric, it is assumed to be the reference number of a previously opened database. If the index parameter is numeric, it is assumed to be the reference number of an already opened index. Named variable 'fndb' is set to the database name. [ USE name or USE name,index] [ SELECT ref ] Status:- 0 Success 4 if file cannot be found - 45 - Reference Hyperdex Users Guide >u Close use of database >u Closes the current database, flushing buffers. >V Assign field value >Vvariable=value Assigns the given 'value' to named 'variable', and updates the current record if it is a field name. The 'value' can be supplied from an action modifier. eg. To prompt for the value:- >Vname=?name >v Assign variable with validation >vvariable=value This works as for the >V action, but the value is tested against the validation pattern for the field type before assignment, and the action aborts if the validation fails. For instance, if a non- numeric value is provided for a numeric database field, the assignment does not take place and an error code is set. >W Write buffer >Wrec Writes buffer, replacing record number 'rec' Current record is updated first. All current index files are maintained. Note that it is not necessary to explicitly call this action to simply update a record, as repositioning to another record will cause any changed field variables to be written beforehand. [ WRITE ] >w Insert new record >wrec - 46 - HyperDex Users Guide Reference Inserts a new record as number 'rec'. The current record is updated first. Status:- 0 Success -1 Error -2 Database locked by another user >X Build index >Xname,expression Build index named name, based on field expression. Option U is used to determine 'unique' parameter ???? Option S is used to determine whether the operation should fail if the index file already exists. [ INDEX ON ... ] >x Rebuild index >xref Re-indexes index reference ref, or current index if ref not specified. [ REINDEX ] >Y Index creation filter >Yexpression Specifies a filter to be applied when creating an index file, which will restrict the records contained in that index. This is a more optimal way of handling a subset of records if an index is used as well as a filter. If no argument is given, any current index creation filter is removed. >y Convert memo file format >ymemoname - 47 - Reference Hyperdex Users Guide Converts a memo file to dBase IV format, from dBase III format, still allowing it to be used with dBase III. >Z Zap range of records >Zstart,end Deletes a range of records in the current database, starting at record start and ending at end. [ ZAP ] Status:- 0 Success -1 Error -2 Database locked >z delete set of named variables >zstring Deletes all named variables which start with string. eg. to delete all variables starting 'memo1':- >zmemo1 - 48 - HyperDex Users Guide Reference 3.3 Database function codes The database function codes act as other action modifiers, returning a string depending on their function. The database function codes start with the '<' character. Because of this, this character is reserved in the second or third positions of an action string to its intended purpose. Database functions may be used in variable expansion tokens as for the &, = and % modifiers. So for example the current database reference number is expanded from $(K7ABCDEFGH >K8GH/EF/CD will cause the function call:- <19910823 to return:- 23/08/91 K and section 4) This function returns the action from the selected record (usually record number, so that record can be selected for detail display). [ BROWSE ] F, is returned. - 51 - Reference Hyperdex Users Guide KX, and returns a positive value if true, else it returns a value of zero. K, as follows:- >KdDD/YY/MM If the initialisation parameters have been set with >h and >H in the configuration file, they will be used for the database memory initialisation, otherwise the default values are used - see action >H. - 56 - HyperDex Users Guide Codes and limits CODES AND LIMITATIONS 4. Codes and limitations This section contains tables of codes, settings and options used in HyperDex. A section showing the limits of the system is also provided. 4.1 Configuration strings # Default setting Usage 0 - Reserved - 1 %d search data '%s' Mid Title for OR 7 Encode - from string 8 Encode - to string 9 - Reserved - A-Z - validation patterns. These may be redefined, and spare items may be used for other validation patterns. The items with ++ against them are not directly data-type related. - 57 - Codes and limits Hyperdex Users Guide A [A-Za-z]# ++ Alphabetic B C <*> Character - any old data D <[0-9][0-9][/][0-9][0-9][/][0-9][0-9]> Date E F <[-+Ee.0-9]+> Floating point G H I <[-+0-9]+> ++ Integer J K L <[TFtfYNyn]> Logical field M <[0-9]+> memo address N <[-+.0-9]+> Numeric and fixed point O P <[0-9]+> ++ Positive integer Q R S T U [A-Z]+ ++ Uppercase - field name V W X <[0-9A-Za-z_]#> ++ Alphanumeric Y Z Special configuration strings d - date format 4.2 Data types C - Character Character string N - Integer Number with no decimal places N - Decimal Number with decimal places F - Floating Floating point number D - Date Date L - Logical True/False or Yes/No field M - Memo Variable amount of text. 4.3 Options A (default 0) Approximation switch for SOUNDEX and SOUNDEQ B (default none) Break character M (default 75) Memo width R (default 0) Readonly S (default 0) Safety T (default 0) Trim leading/trailing spaces from fields U (default 0) Unique W (default 6) Soundex Word length - 58 - HyperDex Users Guide Codes and limits 4.4 Limitations 4.4.1 Restrictions The use of action modifier code '<' means that this character is also restricted in where it can and cannot be used. This therefore has the same restrictions as the other action modifiers (eg. @ & % = ! ?). The normal HyperShell limits for the lengths of action strings apply in HyperDex. This means that certain existing database information which exceeds these limits cannot be handled for certain purposes. The use of named variables to hold the field data means that the same field name cannot be used in more than one concurrent database (eg. when databases are joined or switched). This may conflict with existing dBase applications. 4.4.2 Limits Database files - operating system imposed Index files - operating system imposed The database and index file limits will vary depending on the FILES parameter in the CONFIG.SYS file, and the number of other files open in the session, such as hyperfiles and data files. Fields in record - 1,022 Records in database - 2,000,000,000 The number of fields in a record has an influence on performance, and memory limitations will also cause impose a limit on the practical number of fields possible. It is also worth remembering that the record size is fixed, so large numbers of fields are very inefficient. - 59 - HyperDex Users Guide Browse Program BROWSE PROGRAM 5. Browse Program 5.1 Learning Browse Browse allows the user to easily scan through a database to modify and add records. The user can search for given values and reposition to any record in the database. The switch between the browse and edit screens is a single keystroke. The ordering of the database records can be altered to reflect any index file that has been specified. All levels of users, from the novice to the veteran, will find Browse easy to learn and use. First time users should read the initial section, on Learning Browse, to understand the basic terms and operations of Browse. The second section, on The Browse Editor, explains how to use the browse and edit screens for entering and looking at data. 5.2 Manual Conventions Some keys or key combinations are represented as one or two words with angle brackets around them. Examples: Key Description Represents the key. Represents holding down the key and pressing the key. Represents holding a key and pressing the key. 5.3 Getting Started Browse can be started with a database file name along with zero or more index file names as parameters. The files used by BROWSE are dBASE compatible. For example, BROWSE will edit a dBASE database with a dBASE index file. 5.4 Command Line BROWSE Database_File [Index_File] [Index_File] ... Examples:- - 60 - HyperDex Users Guide Browse Program 1. BROWSE MAILING.DBF 2. BROWSE MAILING.DBF NAMES.NDX ADDRESS.NDX If no indexes are specified, the ordering of the records is the ordering in which they were entered (record number ordering). With one or more indexes specified, the master index becomes the last index specified after the database file. Note that the extensions do not need to be given. Browse assumes that the first argument is the database (.dbf) file, and the other arguments (if given) are the index (.ndx) files. The master index can be changed from within browse. 5.5 BROWSE REFERENCE Browse may be used for browsing and editing a database, and to select index files for sorting and searching. The screen consists of a menu along the top of the screen, with the majority of the window going towards the displaying of the database, according to the sorted index. Once the database is displayed, records can be added, modified or deleted. Records can also be located quickly by using index files or by scrolling. 5.5.1 Entry Areas When information is typed in Browse, it is typed into an Entry Area. For example, when modifying a record in the database, there will be one Entry Area for each piece of information to be entered. Entry Areas are not always associated with a database record. For example, an Entry Area appears after the Find command is initiated. There are several special command keys which facilitate the entering of information into an Entry Area. Flips between Insert Mode and Replace Mode. In Replace Mode, the character typed replaces the character under the cursor; in Insert Mode, the character typed is inserted before the character under the cursor. When in Insert Mode, the cursor is slightly larger. Deletes the character under the cursor. Deletes the character to the left of the cursor. Moves the cursor to the first character position in the Entry Area. Moves the cursor to the end of the entered information. - 61 - Browse Program Hyperdex Users Guide Moves the cursor one position to the left. Moves the cursor one position to the right. Deletes all the Entry Area characters under and to the right of the cursor. 5.5.2 Group entry The following commands are used when entering information into a group of Entry Areas: , or Move to the next Entry Area. If there is no next Entry Area, then the group of Entry Areas are entered. Move to the previous Entry Area. Move to the first Entry Area. Move to the last Entry Area. , or Enter the group of Entry Areas. This is like pressing when on the last Entry Area Abort the changes made in the Entry Areas. The exact effect of aborting will be determined by the particular group of Entry Areas. Move to the previous Entry Area. Move to the next Entry Area. 5.5.3 Menu Selections Menus are used throughout Browse to choose from a list of available choices. When is pressed, the highlighted choice is selected. Two types of menus exist in Browse. The first menu is present when Browse is initiated. For this menu, the following keys may be used to change the highlighted choice:- - 62 - HyperDex Users Guide Browse Program The highlighted choice moves one to the left. The highlighted choice moves one to the right. All other menus use the following keys to change the highlighted choice: The highlighed choice moves up one. The highlighed choice moves down one. The top option becomes the highlighted choice. The bottom option becomes the highlighted choice. Aborts the menu. A - Z Pressing a letter or number highlights the next available option in the menu beginning with the character pressed. 5.5.4 Scrolling Scrolling allows the user to browse through the database records. All scrolling is done relative to the current record. Page Down (Press ) The Page Down command scrolls down one window of records. If the window contains one record, the next record will be displayed. If the window contains twenty records, the top record of the new window will contain the record after the last record of the old window screen. If all the records fit within the window, the Page Down command will cause the cursor to position itself at the last record. Page Up (Press ) The Page Up command scrolls up one window of records; it has the opposite effect of the Page Down command. - 63 - Browse Program Hyperdex Users Guide Down Arrow (Press ) The Down Arrow command scrolls forward one record. Up Arrow (Press ) The Up Arrow command scrolls backwards one record. Top (Press T) The Top command scrolls up to the first record. Bottom (Press B) The Bottom command scrolls down to the last record. The selected index file affects scrolling as it determines the "first", "next", "previous", and "last" record. Help (Press H) The Help option is one of the menu choices given in the main menu. When Help is selected, a window pops up which gives command keys which can be pressed. Each command key corresponds to an option which could be selected from a menu. Refer to Appendix F for a list of the command keys. 5.5.5 Modify Menu Modify Record (Press M) The Modify Record command is used to modify the current record. Press until the active entry area corresponds to the information to be modified. Then complete the modification press or . If is pressed while modifying a record, the modifications of the entire record are aborted. - 64 - HyperDex Users Guide Browse Program Pressing another command key using the key also completes the modification. In addition, it will initiate the command corresponding to the character entered. Delete Record (Press D) The Delete Record command flags the current record for deletion. An asterisk is displayed by the record to indicate that it is marked for deletion. Note that the records are not actually removed and that a serparate program or utility must be used to actually remove the records. Undelete Record (Press U) The Undelete Record command removes the record deletion mark. This reverses the effect of the Delete Record command. Add Blank (Press A) The Add Blank command takes a blank record and appends it to the end of the database file. After executing the command, the entry areas are highlighted. Press to move through the entry areas, and when you press the last or , the record is added and displayed in its proper position in the sorted index. If is pressed while adding a record, the Add Blank command aborts and no record is added. Hint : Pressing when adding a record adds the record and starts adding a new record. This is useful when adding several records in a row. Add Copy (Press C) The Add Copy command is the same as the Add Blank command except the Add Copy command starts with a copy of the current record. 5.5.6 Position Menu This is used for record positioning. - 65 - Browse Program Hyperdex Users Guide Record (Press R) The Record command locates a record by its record number. Records are numbered in the order they were added. For example, if there are 5 records in the database, the last record added is record number 5. After initiating the Record command, enter the number of the record to be located. If the record number entered exists, the current record will become the entered record number. The total number of database records will be displayed at the bottom of the screen. If is pressed rather than entering a record number, the current record is not changed. If the number entered is negative, the current record will not change. If real numbers are entered, the Record number will only look at the whole part of the number, not the fraction (ie. will truncate the remainder). Record numbers that are out of range will not cause the current record to change. Top (Press T) After initiating the Top command, the current record will become the top record, according to the sort of the index file. Bottom (Press B) After initiating the Bottom command, the current record will become the bottom record, according to the sort of the index file. 5.5.7 Find Menu To locate a record quickly, the Find command uses an index file. If the database has several index files, the Select Record Ordering Index command determines which index file is used. The Select Record Ordering Index command also determines the order in which records are displayed. Find (Press F) The Find command locates a record using an index file. After initiating the Find command, a Find box appears in the middle of the screen. The index being used is given on the first line. A prompt requesting the find information appears on the next line. - 66 - HyperDex Users Guide Browse Program The information to be entered corresponds to the index file. For example, an index files might order a database according to the name of an individual. In this case, you would enter the name of the person to search for. It is not necessary to enter the complete search information or the completely correct search information. Browse will find the closest match it can. After the Find command is complted, the found record becomes the current record. Example: Entered Find Information -- John Names in the database -- Jackson Kerns Matheson The record with "Kerns" becomes the current record. This is the name after 'John'. Select Record Ordering Index (Press S) The Select Record Ordering Index command accesses a menu containing the index files of the database and the option "Record Number Ordering". Example Menu Contents: 1. Record Number Ordering 2. NAME.NDX In this case, the database only has the index file "NAME.NDX". The menu option corresponding to the current ordering option is initially selected. For example, if "NAME.NDX" was last selected, the next time the menu appears, it will be highlighted. If "Record Number Ordering" is selected, the database will be displayed in the order the records were intially entered. In addition, the first index file on the Select Record Ordering Index menu will be used by the Find commands. If an index file is selected, the database will be displayed in the order determined by the index file. In addition, that index file will be used by the Find command. - 67 - Browse Program Hyperdex Users Guide 5.5.8 Edit/Browse Switch The Edit/Browse option allows the user to switch between edit mode and browse mode. In the browse mode, the user can look at many records at one time and move freely among them. Note that if all of the record does not fit in the window, only a subset will be displayed. To see more of the records, you must select the Edit option. Under the Edit option, only one record is displayed at a time. The same commands are available with the edit option as with the browse option. 5.5.9 Exiting Browse The Exit command (Press X) is used to exit Browse. Changes to the contents of the database and index files were made immediately after each record was modified or added. The records marked for deletion will remain marked and continue to exist in the database until the database file is packed. Consequently, the Exit command has no effect on the changes made to the database and index files. - 68 - HyperDex Users Guide Browse Program 5.6 APPENDICES 5.6.1 Appendix A - Compatibility Browse is a self contained database program. However, its database and index files are compatible with dBASE III, dBASE III PLUS and dBASE IV. Some dBASE databases contain "memo fields". Browse ignores "memo fields". 5.6.2 Appendix B - Multiuser Requirements Any network which supports MS-DOS 3.0 (or higher) file sharing conventions. Examples: 1. Novell Advanced Net Ware. 2. IBM PC Network Program. Browse will detect the presence of the network and automatically use its multiuser capabilities. This means two workstations, both using Browse, can use the same database and index files at once. 5.6.3 Appendix C - Error Messages 1 120 Opening File This problem is usually caused by specifying a file which does not exist. If an improper .dbf file is specified, Browse is terminated, and control is returned to DOS. If improper index files are specified, the rightmost .ndx file which is correctly specified is used as the master index. If all the index files are incorrectly specified, record number ordering will be used. 2 Illegal Date An illegal date error occurs when a date is entered that does not have the proper month specification. The user must correctly change the month to a proper one, or press to abort the entry. 3 Record Not Found An error message is given when the find information was not found. This occurs only if there are no records after the find information. - 69 - Browse Program Hyperdex Users Guide 5.6.4 Appendix D - Summary of Commands The commands can also be initiated by pressing the command key along with the key. +--------------------------------------------------+ |Command Name | Key | Description | |----------------+-------+-------------------------| |Add Blank | A | Add a blank record | |Bottom | B | Move to the bottom | | | | database record | |Add Copy | C | Add a record by copying | | | | the current record. | |Delete Record | D | Mark the current record | | | | for deletion | |Edit | E | Switch to the edit | | | | screen if possible | |Find | F | Find a record | |Help | H | Display the help screen | |Modify Record | M | Modify current record | |Record | R | Enter record to move to | |Top | T | Move to the top database| | | | record | |Undelete Record | U | Remove the deletion mark| | | | from the current record | |Browse | Z | Switch to the browse | | | | screen if possible | |Exit | X | Exit | |Page Up || Move up one screen of | | | | records | |Page Down || Move down one screen of | | | | records | |Up | | Move to the next record | |Down | | Move to the prev record | +--------------------------------------------------+ - 70 - HyperDex Users Guide Expressions EXPRESSION EVALUATION 6. Expressions Expressions are primarily used to define the keys and selections applied to the file. Expressions can also be used to process the data fields of the current record. In HyperDex, the expression evaluation can involve variable tokens as well as data fields, so the expression evaluation can be used for general numeric and string processing operations independent of the database data. When used for selection and index purposes, the expression return value is of a specific type, which must correspond to that expected. The return type of the expression is derived from the context of evaluation. If an expression involves a field name, it is of the same type as that data field. Field names, constants and functions can be used within an expression. any part of the expression string may be supplied by a variable token, so a variable token could, for instance, supply a 'constant' value or a data field name. 6.1 Constants Constants may be numeric, logical or character types. Numeric constants can be an integer or decimal number. Eg. 2.3, -5 Logical constants can be .TRUE. or .FALSE. and can be abbreviated to .T. or .F.. Character constants must be quoted using single or double quotes. Eg. "Aardvaark", 'KETTLE' If a character constant is to contain a quote character, use the other kind to bracket the constant. Eg. "It's", 'Press "Y" to confirm' 6.2 Operators The types of operator reflect the three data types: numeric, logical, and string. Each requires operands of the same type, either constants and data fields, and returns a value of that type. Operators have a precedence which determines the order in which they are evaluated. The precedence is shown in the following sections as a number, with a higher number indicating an earlier evaluation. Thus an expression such as 2+3*4 is evaluated such that the multiplication is performed before the addition. - 71 - Expressions Hyperdex Users Guide 6.2.1 Numeric operators +---------------------------------+ | Addition | + | [5] | | Subtraction | - | [5] | | Multiplication | * | [6] | | Division | / | [6] | | Exponentiation | ** or ^ | [7] | +---------------------------------+ 6.2.2 Character operators +----------------------------+ | Concatenation | + | [5] | | Concatenation | - | [5] | +----------------------------+ The first variant is a straight character string concatenation. The second variant is different in that any spaces at the end of the first operand are moved to the end of the resulting value. Eg. "Fred "+"Jones" - returns "Fred Jones" "Fred "-"Jones" - returns "FredJones " 6.2.3 Relational operators A relational operator returns a logical result. Its operands must be of the same type, which can be Numeric, Character or Date format. There are six relational operators that compare Numeric, Character or Date operands: +-------------------------------------+ | Equal | = | [4] | | Less Than | < | [4] | | Less Than Or Equal To | <= | [4] | | Greater Than | > | [4] | | Greater Than Or Equal To | >= | [4] | | Not Equal | <> | [4] | | Not Equal | # | [4] | +-------------------------------------+ The following compare Character type operands only:- +-----------------------------+ | Contained in | $ | [4] | Note 1 | Contained in | ? | [4] | | Pattern match | ~ | [4] | Note 2 +-----------------------------+ Note 1: For compatibility, do not use the '$' operator, use the '?' operator instead. - 72 - HyperDex Users Guide Expressions Note 2: The '~' operator uses the HyperShell pattern match. The Second operand is taken as the pattern. 6.2.4 Logical Operators +------------------------------------+ | Both operands true | .AND. | [2] | | Either operand true | .OR. | [1] | | Invert truth | .NOT. | [3] | +------------------------------------+ The .AND. and .OR. operators take two logical values and return a logical value. The .NOT. operator takes a logical value and returns the opposite logical value 6.3 Functions A function can be used as an expression, or in place of an operand in an expression. Every function has a type: umeric, ate, haracter, or ogical, depending on the values it returns. A function can have parameters, enclosed in parentheses, and separated by commas. Functions take precedence over the other operators. CTOD(char-type) Takes a date string in the "DD/MM/YY" or "MM/DD/YY" format and returns a date type. DATE() Returns the current date DAY(date-type) Returns the day part of a supplied date DEL() Returns "*" if current record is marked for deletion, else returns " ". DELETED() Returns .TRUE. if the current record is marked for deletion DTOC(date-type) Converts a date type value to a character type in the format "MM/DD/YY" or "DD/MM/YY". DTOS(date-type) Converts a date value to a character value in the format "CCYYMMDD". - 73 - Expressions Hyperdex Users Guide IIF(log-type,true-result,false-result) If log-type .TRUE. returns true-result, else returns .FALSE. result. eg. IIF(married,'Married','Single') Returns Married if logical 'married' is true. LOWER(char-type) Returns the lowercase form of the operand. MONTH(date-type) Returns the month part of a date RECCOUNT() Returns the number of records in the currently selected database. RECNO() Returns the current record number SOUNDEX(char-type) Returns a value which represents the soundex value of the supplied string. This can be stored in the database and used for rapid soundex searches. SOUNDEQ(char-type,char-type) Returns .TRUE. if the supplied strings match each other using soundex comparison STOD(char-type) Converts a character value in the "CCYYMMDD" format to a date type. STR(num,len,dec) Converts a numeric type into a character type formatted to length len and dec places of decimals. If insufficient length is provided, the result is formatted as "***" eg. STR(5.7, 4, 2) returns "5.70" SUBSTR(string,start,len) Returns the substring of 'string' starting at 'start' for 'len' characters. eg. SUBSTR("abcdefg",3,2) returns "cd" TIME() Returns the current time as a string TRIM(char-type) Returns operand trimmed of trailing space UPPER(char-type) Returns the uppercase form of the operand - 74 - HyperDex Users Guide Expressions VAL(char-type) Converts a character string to a number YEAR(date-type) Returns the year part of a date - 75 - HyperDex Users Guide Error Codes ERROR CODES 7. Error Codes These error codes relate to database operations. They are returned by the 'v action). - 77 - HyperDex Users Guide Example Hyperfile EXAMPLE HYPERFILE Shown here is CARDEM.HYP, the example hyperfile provided with HyperDex. The set of files making up the CAR demo example is:- CARDEM.HYP, CARS.DBF, CARTYPE.NDX, INSGRP.DBF, INSGRP.NDX .REMARK EXAMPLE DATABASE HYPERFILE - TO SHOW HYPERDEX FUNCTIONALITY .HEADER .SCRIPT SETUP -- open insurance group database >UINSGRP -- get database reference number VrIINSGRP -- get index reference number ViUCARS,CARTYPE -- Join databases on insurance group >JGROUP,r,i -- go to top record >GT -- Set up Frame Transition Script KG0SFrame refresh . .SCRIPT Frame Refresh -- Frame refresh script Vz ZiK3MODEL -- set up detail line as other main fields >K4$$(MAKE) $$(CAPACITY)cc $$(MPG) m.p.g. œ$$(PRICE) -- Do list without search string >G<$t y . .SCRIPT Delete Vc?Confirm delete record(y or n) -- if not 'y', quit vcy;anq -- Delete record >D y;pRecord deleted . .SCRIPT Seek -- Seek for record and display -- go to 'Ranswer' depending on seek result JRG+0 VmNear match JDISPLAY -- 2 is near miss - current record LR2 >G+0 VmNear match JDISPLAY -- 3 is end of file LR3 VmEnd of file reached JDISPLAY -- -1 is error condition LR-1 VmError condition <$( LDISPLAY -- refresh and display message y;p$m . .SCRIPT Append - 79 - Example Hyperfile Hyperdex Users Guide -- Append new record - add blank >A y;pEnter Data for New record;ZUz . .SCRIPT Select -- set up Selection filter and clear input variable >F$x;Vx -- goto TOP and refresh >GT;y . .NOTE Selections [1] $(VMAKE=%s;I~Kz~E MODEL:~C14~I$(MODEL)~W20~P>VMODEL=%s;I~E CAPACITY:~C14~I$(CAPACITY)~W5~P>VCAPACITY=%s;I~E MPG:~C14~I$(MPG)~W3~P>VMPG=%s;I~E PRICE:~C14~I$(PRICE)~W6~P>VPRICE=%s;I~E COMMENTS:~C14~I$(COMMENTS)~W20~P>VCOMMENTS=%s;I~E Insurance GROUP: ~I$(GROUP)~W2~P>VGROUP=%s~E Third Party: œ$(TPFT) Comprehensive: œ$(COMP) Move to [t] ~ATop~P>GT;y~Kt~E [n] ~ANext~P>G+1;y~Kn~E [p] ~APrevious~P>G-1;y~Kp~E [b] ~ABottom~P>GB;y~Kb~E Indexed Search [s] Make Model: ~I$l~W40~PVl%s;SSeek~Ks~E Selection filter [f] Expression: ~I$x~W40~PVx%s;SSelect~Kf~E Examples:- PRICE < 10000 .AND. MPG > 35 MAKE = "Ford" Filter [v] ~AView expressions~PNSelections~Kv~E [d] ~ADrop Last~P>F;>GT;y~Kd~E [r] ~AReset All~P>f;>GT;y~Kr~E Database list [l] ~AList~PSData list(H)~Kl~E [c] ~AContinue~PSData list(h)~Kc~E Data Entry [a] ~AAdd new car~PSAppend~Ka~E [m] ~AModify~Ppselect field and modify~Km~E [k] ~ADelete record~PSdelete~Kk~E [u] ~AUndelete~P>R~Ku~E Exit to DOS [q] ~AQuit~PQ~Kq~E . - 80 -