jBASE Indexes

The CREATE-INDEX command builds a secondary index on an attribute or combination of attributes in the specified file. The command syntax is:

create-index -Options filename indexname index-definition

Syntax Elements

jsh-> key-select ORDERS WITH PLACE-DATE > 10638

jsh->key-select ORDERS WITH PLACE-DATE > 14-feb-1997

CREATE-INDEX FILENAME INDEXNAME BY 4.3

You can use the CREATE-INDEX command to create a new index definition. By default, the index is rebuilt to include any existing records.

Examples

• Create an index based on attribute 1, concatenated with attribute 2.

  jsh--> create-index filename indexname by 1 : 2

• Create an index on the attribute given in the dictionary definition NAME and a second attribute number 3, but in descending order.

  jsh--> create-index filename indexname by NAME by-dl 3

• Create an index on attribute 4, which is normally an internal date. You want to be able to specify dates in external format when doing selections against it. Additionally, if the field is a null string, then don't store any index information.

  % CREATE-INDEX -lD -n ORDERS BY-AR 4

• Create an index using three attributes.

  sh-> create-index FILENAME INDEXNAME BY 1 BY 2 : "*" : 3

  In the above definition, the index key is built out of three attributes. If these attributes have differing numbers of multi-values, then it becomes difficult to create a logically consistent index key. Therefore, the above example will fail to create the index definition.

• If an index is generated on a record with an attribute as follows:

  PIPE

  001 123]456]789

  Then by default three index values based on "123", "456" and "789" will be created. With the -m or (M) option on create-index, you will build a single index value based on "123]456]789".

Notes

In addition to the above create-index syntax, a compatible form of the create-index command can also be used.

CREATE-INDEX FILENAME ITEM

This creates an index called ITEM and the index definition is based on the dictionary item ITEM. jBASE supports this by converting the syntax to the jBASE syntax and notifying the user of the equivalent converted command (unless the (S) option is used). During this conversion, the dictionary definition is used as follows:

• Attribute 2 specifies what attribute number to extract.

• Attribute 7 specifies any lookup code that is what to convert any matching string using.

• Attribute 8 specifies any conversions to apply when building the index data.

• Attribute 9 specifies if it is a left or right (numeric) sort.

jBASE allows indexes created in this manner to be used with some jQL commands like SELECT or SORT. An index, which is not created via a dictionary item, must query the index with key-select or query-index.

If a complex definition exists in attribute 8, then the conversion will fail and the user will have to use the jBASE syntax.

This example shows a DICT item in jBASE. If you run the create-index command against it, it will be converted to the jBASE syntax and get executed.

INDEX1
001 A
002 3
003 Description
004
005
006
007 D2
008 MCU
009 R
010 10

For example,

CREATE-INDEX -a filename INDEX1
Command converted to "CREATE-INDEX -lD2 filename BY-AR OCONV(3,"MCU")"
Index definition "INDEX1" created successfully

This command deletes one or more index definitions that are associated with a file. All the space taken by the index is released to the file overflow space (but not necessarily the operating system file free space). The command syntax is:

delete-index -Options filename { {indexname {indexname ...}} | *}

Syntax Elements

Examples

• Delete all the index definitions for the file PRODUCTS.

  jsh--> DELETE-INDEX -a PRODUCTS

  jsh--> DELETE-INDEX PRODUCTS *

• Delete the index name "value" in the file PRODUCTS.

  % delete-index PRODUCTS value

This command allows you to select or count a list of record keys. Note that file updates that cause a change to an index will wait for a query-index to complete.

The first structure of the query-index command allows you to select all record keys sorted by the index definition. For example, to select all customers sorted by their last name:

jsh-> query-index CUSTOMERS lastname

Command Syntax

query-index -Options filename index_name
query-index -Options filename {IF/WITH} iname {Op} "startpos"
query-index -Options filename {IF/WITH} iname {Op} "startpos" {AND} {Op} "endpos"

Syntax Elements

Op can be:

Options can be:

The second structure of the query-index command allows you to specify a single conditional parameter. You can make this query less than, greater than, etc. to the parameter. If you don't specify LT, GT, etc. then it defaults to equals.

Examples

• Select all customers whose name begins with "KOOP".

  jsh-> QUERY-INDEX CUSTOMERS IF lastname "KOOP"

NOTE: In this case, the double quotes will be ignored. The IF token is a throwaway token, and is used simply for clarity. WITH can also be used to the same effect.

• Select all customers whose date of birth is before 25 July 1956.

  jsh-> QUERY-INDEX CUSTOMERS WITH dob LT 25-JUL-1956

The third structure of the query-index command allows you to specify a range of values. This means the operators must be either GT or GE followed by LT or LE. If the operators are not specified, then the command defaults to GE and LE.

Example

Count all the customers whose last order was placed between 19-DEC-1996 and 23-DEC-1996 inclusively.

jsh--> query-index -c CUSTOMERS IF order.date >= "19-DEC-1996" and <= "23-DEC-1996"

Option -c counts number of record keys instead of producing a select list and option -m changes the pattern-matching algorithm.

The command performs its test using a simple string or numeric test. If you want to make the test more similar to a jQL command (for example), then you would use the -mJQL option. For example, if you want to select all customers whose name ends in PATEL, then you can run the following command:

jsh-> query-index -mJQL CUSTOMERS IF lastname EQ "[PATEL"

Similarly, you can use the -mREGEXP option to use a pattern-matching algorithm called "Regular Expressions". This helps you to search complicated patterns. For example, the following command selects all products whose description begins with the letter A and is followed by any number of characters before the sequence PIPE is found:

jsh-> query-index -mREGEXP PRODUCTS IF description EQ "^A.*PIPE"

Option -i can be used to restrict the number of indexes used to create the list of record keys. This can be useful to restrict a search to a smaller subset.

Option -r is similar to -i except it restrict the number of record keys.

This command displays the screen details of all the current definitions. It generates a format similar to jQL. The command syntax is:

list-index -Options filename {Same as jQL options}

Syntax Elements

Examples

Display all the index definitions for the file CUSTOMERS and send the output to the printer.

jsh--> LIST-INDEX -v CUSTOMERS (P

Notes

Machine-readable format is broken down as follows:

This command rebuilds the index definitions. You can use it in the following circumstances:

• Following the corruption of the index file.

• Following a file restore using jrestore.

• Following the creation of an index using create-index -a.

The command syntax is:

rebuild-index -Options filename { {indexname {indexname ...}} | *}

Syntax Elements

This command verifies the integrity of an index definition for internal corruption. It doesn't actually verify that the index data correctly cross references the data file records. The command syntax is:

verify-index -Options filename { {indexname {indexname ...}} | *}

Syntax Elements

jBASE supports a limited mechanism whereby jQL commands such as SORT or SELECT can automatically use any valid secondary index to reduce the search time. This does not involve creating a specific dictionary item. If for any reason the index cannot be found, or is not up to date (for example, awaiting a rebuild-index command), then the jQL command will ignore the secondary index and retrieve the information in the usual manner.

At present, only a single search pattern can be used in a jQL command. As an example, a file will have an index built onto attribute 23, the customer last name like this:

jsh->create-index CUSTOMERS lastname BY 23

Let us assume there exists a dictionary definition called LASTNAME that looks like this:

LASTNAME
001 A
002 23
003 Customer Lastname
004
005
006
007
008
009 T
010 20

Now assume that you try to select all customers in that file whose last name equals "COOPER]". The jQL statement would look like this:

jsh-> SELECT CUSTOMERS IF LASTNAME EQ "COOPER]"

In this example, the index definition is "out of sync", awaiting a rebuild-index command to be performed. Therefore, the SELECT would achieve the result by looking through the entire file. Now let us run the rebuild-index command as:

jsh-> rebuild-index CUSTOMERS lastname

If you now re-execute the SELECT command, then instead of scanning through the entire CUSTOMERS file, it will look through the index definition "lastname" instead and will therefore execute considerably quicker.

It is possible to call a jBC subroutine from an index definition. The subroutine should have five parameters as follows:

For example, consider the following index creation:

jsh-> CREATE-INDEX FILENAME INDEXNAME BY 1 : CALL(2,"INDEX-DEF")

When an update occurs, the index key is calculated by taking attribute 1 and concatenating it with the output from a call to a subroutine called INDEX-DEF. The source code for this may look something like this:

INDEX-DEF
001 SUBROUTINE INDEX-DEF(result , file , record , key , field )
002 IF NUM(field) THEN result = "*1" ELSE result = "*0"
003 result := record<3>
004 RETURN

In the above example, the result is created in the first parameter, the result variable. This is calculated by taking the string "*1" or "*0" and concatenating it with attribute 3 from the record being updated. The choice of "*1" or "*0" depends upon whether the extracted attribute, passed in the fifth parameter as variable field is numeric or not. The index definition was CALL(2,"INDEX-DEF"), so this extracted attribute will be attribute 2.

Any normal jBC code will execute in these subroutines but you should be aware of the following pitfalls:

• The code should always create exactly the same result given the same record. This means you should avoid using functionality that creates a variable value, such as the RND() function, the TIME() or DATE() functions, the users port number and so on. If this occurs, then there will be no way jBASE can delete a changed index value and so the index file will continually grow with invalid data even if the number of records remain constant.

• These subroutines will be called implicitly from other running jBC code, which to its knowledge has merely executed a DELETE or WRITE statement. You should therefore avoid any code that changes the nature of the environment such as using the default select list, turning echo on or off, turning the break key on or off. There are ways around many of these, for example, you can turn the echo on and off so long as your code remembers in all cases to restore it to its original status. Similarly, you can do a SELECT so long as it is to a local variable rather than the default select list.

• Depending upon your application, these subroutines may be accessed by users other than the account in which the files exist. Therefore, all persons who have access to OPEN and update the file must also have access to CALL your subroutine. This can be done in a number of ways.

  • All users who want to update the file may have the JBCOBJECTLIST environment variable set to include the library where these subroutines were cataloged. For example, if the subroutines have been cataloged from account greg, then you can set up the JBCOBJECTLIST environment variable as follows so that you look in the users current lib directory and failing that in the lib directory for greg (this from the Korn shell).

    export JBCOBJECTLIST=$HOME/lib:~greg/lib

  • You can CATALOG into a directory that is common to all users. Such as the lib directory where jBASE is installed. In this example, you do not need to set up JBCOBJECTLIST. However, remember to re-catalog all these subroutines when a new version of jBASE is loaded. You change the output directory for CATALOG with the JBCDEV_LIB environment variable. For example, from the UNIX Korn shell you would do this:

    export JBCDEV_LIB=$JBCRELEASEDIR/lib
    CATALOG BP INDEX-DEF
    

Regular expressions are the name given to a set of pattern matching characters. The term derived from UNIX environment. The regular expressions can be used to great effect using the query-index command to decide what records to select. You can obtain a full description of regular expressions on UNIX systems by entering the command below:

% man 8 regexp

For Windows systems, only a limited subset of regular expressions are available. The table below describes the characters inside a regular expression.

Examples

On either a UNIX or a Windows/NT system, you can use key-select to find a product description that has the text SLIPPER at the start of the description. This can be done using the jQL format. The two methods are therefore:

jsh-> key-select -mJQL PRODUCTS IF description EQ "SLIPPER]"
jsh-> key-select -mREGEXP PRODUCTS if description EQ "^SLIPPER"

As a more complicate regular expression, the following example looks for a product that begins with the string BIG, has the words RED somewhere in the text, and then must end with the word ENGINE:

jsh-> query-index -mREGEXP PRODUCTS "^BIG.*RED.*ENGINE$"

The UNIX implementation uses the operating system supplied version of regular expressions and these are far more powerful than the jBASE supplied version of regular expressions on Windows systems. As already mentioned, use 5 regular expressions to find more details.

The following example looks for a product description that begins with the words PIPE, any number of spaces, then one or more numeric characters follow (including optionally a decimal point), any number of spaces, and finally the characters mm, which are case insensitive:

jsh->query-index -mREGEXP PRODUCTS EQ "^PIPE *[0-9\.][0-9\.]*[mM][mM]$"

The above command can find any of the following:

PIPE 5 mm
PIPE15MM
PIPE 33.3 mm

There are three ways of backing up your database.

ACCOUNT-SAVE

This command is used when you want to transfer your data to a non-jBASE system. The indexing information will be lost.

tar and cpio

These UNIX supplied backup utilities are used to backup only jBASE files. These utilities should be used with caution and only against databases where there is no update activity. The tar and cpio programs does not follow the locks on a file that jBASE normally uses.

jbackup and jrestore

These are the jBASE supplied backup and restore programs and are the preferred way of backing up your data. These programs follow all locks and will keep the tapes free from any soft format errors (occur if using tar and cpio on an active database).

By default, when you use jbackup to save a database, any indexing information will be saved as just the index definition, not the actual index data. Conversely, during a restore using jrestore, the index definition will be restored, but not the index data, whether it exists on tape or not.

So the full scenario, by default:

1. Backup your database:

   cd $HOME
   find . -print | jbackup -v -f/dev/rmt/0
   

2. Restore your database:

   cd $HOME
   rm -rf *
   jrestore -v -f /dev/rmt/0
   

3. Rebuild indexes.

4. The stage 2 restores the database and the index definitions, but not the index data. Now to rebuild the index data for all the files you have just restored:

   rebuild-index -r $HOME

   If you have restored files into sub-directories, you could use the following UNIX command to rebuild the indexes for all files in all sub-directories:

   cd $HOME
   find . -print | xargs rebuild-index
   

When you backup a database with jbackup, you can use the -c option and this will backup the actual index data as well as the data files. The index data is dumped as a normal UNIX binary file. Therefore, during the restore phase, the index data will be restored exactly as is.

When you restore a database using jrestore, by default it will restore the index information, but will not rebuild the index data. This is quite time-consuming and so by default jrestore works in the quickest mode. The index will need to be re-built at a later stage using rebuild-index. In the meantime, any attempts to use query-index or key-select will fail, and the jQL programs such as COUNT and SELECT will not use the index in order to satisfy the request. During the restore a warning message will be shown against each file, that needs re-building. Once the rebuild-index has completed, the index will become available again to the jQL programs and query-index.

You can optionally use the -N option to jrestore and this will result in the indexes being built as the data is restored. This will slow down the restore time, which means the index is immediately available for use without the need to re-build the indexes with rebuild-index.