jBASE General Index Information

These pages give some general definitions about the jBASE Secondary Indexes.

The following definitions are described in this section

External Representation of data "Lookup Code" (Return to Top)

In jBASE there is a concept of internal and external representation of data. These differences usually materialise in fields where dates and times are stored, but are not necessarily limited to dates and times.

An "external" representation of a date is that which a user might understand, such as "15 JULY 1956" or "8/22/87". The data in the file would normally be stored in "internal" representation, and in the case of dates this is the number of days since 31 DEC 1967.

When you create an index definition you might, for example, want to create it on a date field that is stored in the internal representation of a date. The following example creates an index on attribute 7, and it does so as a right justified (i.e. numeric) sort so that the numbers in attribute 7 are sorted numerically (i.e. sorted in ascending dates)

jsh --> CREATE-INDEX ORDERS ORDER.DATE BY-AR 7

If we assume attribute 7 is the order date stored as a numeric value (i.e. count of days since 31 DEC 1967) then the following command would select all orders received on and after 1st January 1997.

jsh --> key-select ORDERS ORDER.DATE GE 10594

where 10594 is the number of days since 31 DEC 1967.

Obviously this isn't a particularly friendly sort of command ! The answer is to use the "look-up" code definition when an index is first created. When this is defined it means that any subsequent queries against the index from key-select or query-index will have that input string converted from an external representation to an internal representation. The following is a modification of the above example to show the index being created with the -lD option (which causes input data to be passed through the "D" conversion) and the modified, easier to use, version of key-select

jsh --> CREATE-INDEX -lD ORDERS ORDER.DATE BY-AR 7
jsh --> key-select ORDERS ORDER.DATE GE 1-JAN-1997

The look-up code only applies to the key-select or query-index command. Statements inside jBC code such as SELECTINDEX or SELECT .... ATKEY will continue to use the internal representation and no lookup code will be applied. The following is an example of jBC code to do the equivalent of the above key-select statement.

filename = "ORDERS"

OPENINDEX filename ,"ORDER.DATE" TO indexvar ELSE STOP 201,filename

SELECT indexvar TO list ATKEY "10594"

LOOP WHILE READNEXT KEY index.value,record.key FROM list DO

  PRINT "Next key past 1st Jan 1997 is ":OCONV(index.value,"D")

  PRINT "  from record key ":record.key

REPEAT

Multi-value support (Return to Top)

When an index is created, by default jBASE assumes it is a multi-value index. Consider the following creation of an index.

jsh --> create-index PRODUCTS BY DESCRIPTION

Let us further assume DESCRIPTION is a DICT record that defines attribute 1. Finally let us assume just a single record exists like this :

jsh --> COPY PRODUCTS * (T
Jim
001 SLIPPERS]PIPE]BOOKS

This shows there is one record called "Jim" who has 3 products assigned to him, SLIPPERS, PIPE and BOOKS. As we have created an index on attribute 1, there will be 3 index keys created, one for each value.

This default usage of multi-values breaks down when multiple attributes are defined in the index definition. Consider the following index definition.

jsh --> create-index PRODUCTS BY 1 BY 2

In the above example we concatenate attribute 1 with attribute 2 to create the index definition. If we assume both attributes are multi-valued, then what does the above actually mean? Does it mean we take the 3 values in attribute 1, add the 3 values in attribute 2, this creates 6 values and so we create a total of 6 index keys ? Or does it mean we take value 1 from both attribute 1 and 2 and concatenate them to create a single value ? If so, what happens when they have differing number of values in each attribute ?

As you can see, once we use 2 or more attributes the multi-value approach becomes difficult to solve in a logical and consistent manner. Therefore we will flag an error and the above example of create-index will fail.

If you want to do a concatenation or some other complex definition, you can only do this in non-multi-value mode. This is performed using the -m or (M) option to create-index. With this option we will not do a multi-value extract but instead treat the entire attribute as a single entity as though it had no multi-value marks. Therefore the above create-index example could be replaced by :

jsh --> create-index PRODUCTS BY 1 BY 2 (M)

If you really want to have a go at multi-value support , then you will need to call a subroutine to perform it, and do the coding yourself in jBC code. In the following example we call a subroutine called CONCAT. This subroutine will be called with attribute 5 (in this example) passed. However the subroutine will also have the entire record passed to it in another parameter, and in your SUBROUTINE code you can do whatever you like. The resulting output can have as many values as you like, created however you like.

jsh --> create-index PRODUCTS BY CALL(5,"CONCAT")

jQL usage of secondary indexes. (Return to Top)

Once an index has been created with create-index , and assuming the index data is in sync, then you can start to use the index data with special commands such as key-select or with jBC statements such as SELECTINDEX.

Another way the index data can be used is from jQL commands such as COUNT or SELECT. This occurs automatically and will dramatically speed up the operation of the jQL statements.

For example, let us assume you create an index called DESCR on a file called PRODUCTS like this

jsh --> create-index PRODUCTS DESCR

This usage of create-index uses an existing DICT definition called DESCR and create and index name of DESCR. If you now do a SELECT against the file like this

jsh --> SELECT PRODUCTS IF DESCR EQ "PIPE]"

Then in the above example the SELECT command will automatically use the index instead of the usual mechanism of searching through the entire file. You could have similarly used the following command instead :

jsh --> key-select PRODUCTS DESCR PIPE]

Note the index could equally have been created with a command like the following, it is just we showed the alternative syntax for create-index as this used an existing DICT definition to create the index (the command syntax is easier but less powerful)

jsh --> create-index PRODUCTS DESCR BY 55

There are some limitations to this usage of the index. If the jQL statement is too complex or the index data is not in sync, the jQL command will still work but will use the normal mechanism of searching the file rather than using any index data for improved performance.

Limited compound selection criteria is supported -- for example the following first example will still use the index data whereas the second example will not even if an index exists for both BIRTHDAY and SURNAME.

jsh --> SELECT PRODUCTS IF BIRTHDAY GE "23-JUL-56" AND BIRTHDAY LE "28-JUL-56"

jsh --> SELECT PRODUCTS IF BIRTHDAY GE "23-JUL-56" AND SURNAME EQ "COOPER]"

If you want to be absolutely sure the index data is used to speed up your query, then you should change your application to use the key-select command.

In-Sync definition (Return to Top)

The indexing of a file in jBASE comprises three parts

1. The actual file data
2. The index definition
3. The index data

When an index is created using the create-index command, you specify a file name ( (1) above) and the create-index command will create both the index definition (2) and the index data (3). When this is done the index data is said to be "in sync" with the file data. In other words, the index data matches the actual file data.

There may be occasions when this is not true and the index is not "in sync". This could occur following a restore from an archive using jrestore or maybe the -a option to create-index was used.

When a index is "in sync" it means commands such as key-select can be used against the index. It also means that jQL commands such as SELECT and COUNT may be able to use the index to speed up the command.

If the index is not "in sync" then the commands key-select and query-index will fail with an error message. The jQL commands such as SELECT and COUNT will simply avoid using the index and will use the regular mechanism of searching the file data. The jBC statements such as SELECTINDEX will simply fail to give any keys and the OPENINDEX statement will take the ELSE clause.

If a file is not "in sync" then you can use the rebuild-index command to rebuild the index data from the file data and once rebuild-index is run the status will change to "in sync"

Regular Expressions.(Return to Top)

Regular expressions are the name given to a set of pattern matching characters. The term is derived from Unix environment. The regular expressions can be used to great effect using the query-index command (or key-select command) to decide what records to select.

A full description of regular expressions can be obtained on Unix systems by entering the command :

% man 5 regexp

For Windows/NT systems only a limited subset of regular expressions are available. The following characters inside a regular expression have special meaning :

For example, on either a Unix or Windows/NT system you could use key-select to find a product description that has the text SLIPPER at the start of the description. This can be done using a jQL style syntax you might be familiar with using or by using regular expressions. The two methods are therefore :

jsh-> key-select 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 words 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/NT systems. As already mentioned , use man 5 regexp 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 would find any of the following:

• PIPE 5 mm
• PIPE15MM
• PIPE 33.3 mm

Universal Co-ordinated Time (Return to Top)

A standard time and date format exists on many operating systems including Unix and Windows/NT. This is called Universal Co-ordinated Time and is the number of seconds since 00:00:00 January 1st 1970. Confusingly it appears to have been given the acronym UTC (instead of UCT as you might expect !).

Within a jBASE application the normal convention adopted by jBASE , for the purposes of legacy code compatibility, is that times are stored as seconds past 00:00:00 and dates stored as the number of days since 31st December 1967.

The way to convert from UTC to jBASE time and dates and vice-versa can be demonstrated by the following code segment

*
* Convert a UTC value to a displayable time and date
*
UTC = 866558733
internal.date = INT(UTC/86400)+732
internal.time = MOD(UTC,86400)
CRT "Date = ":OCONV(internal.date,"D")
CRT "Time = ":OCONV(internal.time,"MTS")
*
* Convert internal time and date to UTC
*
UTC2 = (DATE()-732)*86400 + TIME()
CRT "UTC2 = ":UTC2

One important aspect to remember is that the UTC is often in the base time of the operating system without any time zone applied. For example on Unix systems you set the time and date of your system in UTC but then individual accounts may have different time zones applied. Thus if you create an index at what appears to be a time of say 10:40:29 then this could actually be a time of 11:40:29 but with a time zone of minus one hour applied.

http://807199.827977/r5/knowledgebase/manuals/3.0/SecondaryIndexes/html/general.htm last modified on 06/19/09 05:29 AM