Rebuilding Indexes from R/BASIC (TB#73) (Functions/Subroutines/Programs)

Rebuilding Indexes from R/BASIC

Although there are system subroutines for many indexing functions (for instance, flushing or searching an index), there is no easily-callable system subroutine for completely rebuilding an index. This technical bulletin describes the techniques required to rebuild an index from within an R/BASIC program and provides the code for an external function that accomplishes this task.

Figure 1 contains the source code for an index rebuild subroutine. The subroutine is coded to accept to arguments and to return an error code via the system function STATUS( ). Figure 2 illustrates a method used to call the code in Figure 1.

How to Trigger an Index Rebuild

Advanced Revelation can build or rebuild an entire index that is what happens when you create a new index. This process is embedded in the routines that flush index transaction records from the !INDEXING file (central index transaction file) into the individual indexes. To rebuild an index you must communicate with these routines.

You cannot, however, call these routines directly. Instead, you must create a special index rebuild "token" a special version of the normal index transaction record and save it in the !INDEXING file. When the index flush routines encounter this special transaction, they then rebuild the index.

Note: In versions of Advanced Revelation 2.1 and higher the !INDEXING file is no longer used. In these versions the index token should be saved in the !datafile.

Rebuilding an index from within an R/BASIC program consists of these steps:

_ Build an index rebuild token.

_ Save this token in the !INDEXING file, or to the !datafile if you are running 2.1 or higher.

_ Call an index flush routine to set off the actual rebuild process.

Building the Token

The index rebuild token is a seven-field dynamic array that has this layout:

Field Contents

1 file specification

2 the literal number 1

3 the index name (field name) to rebuild

4 the literal ALL.IDS

5 (null)

6 (null)

7 (null)

Note: In version 2.1 and higher, the first two fields of the token are dropped.

File specification The file specification has three parts in the format:

file_name*account_name*volume label

Given the file name, you can extract the account name and volume label from the FILES and VOLUMES file. The account name for a given file is kept in the third field of the entry in the FILES file for that file. The volume name for the file is in the first field of the same record, which in turn allows you to extract the volume label from the first field of the corresponding record in the VOLUMES file. See the code in Figure 1 for a means of extracting this information automatically.

The index name (field name) must be the name of the field as indexed. For example, if you want to rebuild the Cross Reference index for the field COMPANY_NAME, you must specify the field name COMPANY_NAME.XREF, because this is the name of the field as it appears in the index.

The literal number 1

This is merely an indicator telling the indexing system that there in only one transaction for this file in this particular token (normal index transactions can have higher numbers than this).

The literal ALL.IDS

This is the trigger that indicates that a rebuild is required.

Null fields

Finally, three field marks (@FM) must follow the fourth field in the index rebuild token. Without these fields the index rebuild will not be properly triggered.

Writing the Token to the Transaction File

Once you have created the index rebuild token, you must save it in the !INDEXING file as the last transaction of the "0" (zero) record. Make sure before you do so that there is a field mark (@FM) separating the rebuild token from any previous transactions, or the rebuild process will fail.

Note: In versions of Advanced Revelation 2.1 and higher the !INDEXING file is no longer used. In these versions the index token should be saved in the !datafile.

Because you may be working in a network environment, the "0" record is locked. In the example code, the LOCK statement is in a loop with a two-second timeout; the program assumes that any locks against record "0" in !INDEXING are transient and will be cleared expediently. Depending on how much traffic there is on your network, this may not be true; code accordingly.

Flushing the Index

Once the index rebuild token is in the !INDEXING file, any process that flushes the index will set off the rebuild. You can force an index flush by calling the system subroutine INDEX.FLUSH, or you can use the Update option off the Tools-Files-Indexing menu.

It is important to recognize that backgound indexing will not deal with the token. Because rebuilding an index can be a lengthy process, and is a process that cannot be interrupted, it is not well suited to background processing.

Figure 1

SUBROUTINE INDEX.REBUILD.SUB(FILE_NAME,INDEX)

*

* desc: indirectly causes an index to be rebuilt by putting the

* appropriate information into the !INDEXING file for the

* indexing system to pick up. The program is divided into

* 3 parts:

*

* 1) determine what file and field to re-index

* 2) build a "rebuild all" token to put into !INDEXING

* 3) place rebuild token at the end of the 0 record in

* !INDEXING (or, in 2.1, in !DATAFILE)

*

*—————————————————————————

* error return codes in STATUS()

*

* 0 = everything ok

* 1 = !INDEXING not available (Not needed in 2.1)

* 2 = "filename" is not available

* 3 = "index" is not a field or is not an indexed field

*—————————————————————————

DECLARE SUBROUTINE DELAY

DECLARE FUNCTION GETCONFIGURE

$INSERT INCLUDE CONFIGURE.RECORD.EQUS

* init

EQU TRUE$ TO 1

EQU FALSE$ TO 0

EQU NULL$ TO ""

* error return codes for STATUS()

EQU OK$ TO 0

EQU NO_BANG_INDEXING$ TO 1 ;* Not needed in 2.1

EQU BAD_FILE$ TO 2

EQU BAD_INDEX$ TO 3

STATUS() = OK$ ;* assume ok

*——————-

TWO_ONE = FALSE$

CONFIG = NULL$

IF GETCONFIGURE(CONFIG) THEN

VERSION = CONFIG<SYSTEM.LEVEL$>

IF VERSION >= 2.1 THEN

TWO_ONE = TRUE$

END

END

IF TWO_ONE THEN

* 2.1 code

OPEN "!":FILE_NAME TO BANG_INDEXING_FILE ELSE

STATUS() = BAD_FILE$

RETURN

END

* end 2.1 code

END ELSE

* Pre 2.1 code

OPEN '!INDEXING' TO BANG_INDEXING_FILE ELSE

STATUS() = NO_BANG_INDEXING$

RETURN

END

* end pre 2.1 code

END

OPEN FILE_NAME TO T$FILE ELSE

STATUS() = BAD_FILE$

RETURN

END

BTREE_FLAG = XLATE("DICT.":FILE_NAME, INDEX, 6, "X")

IF BTREE_FLAG ELSE

STATUS() = BAD_INDEX$

RETURN

END

*——————-

* build token

* build index name/volume string out of information in FILES and VOLUMES

VOLUME_NAME = XLATE("FILES", FILE_NAME, 1, "X")

ACCOUNT_NAME = XLATE("FILES", FILE_NAME, 3, "X")

VOLUME_LABEL = XLATE("VOLUMES", VOLUME_NAME, 1, "X")

FILE_SPEC = FILE_NAME:"*":ACCOUNT_NAME:"*":VOLUME_LABEL

TOKEN = TOKEN<1> = FILE_SPEC TOKEN<2> = 1 ; * number of 'transactions' to accomplish for this file TOKEN<3> = INDEX ; * field name TOKEN<4> = "ALL.IDS" ; * actual rebuild/clear token TOKEN<5> = "" ; * trailing @FMs are important TOKEN<6> = "" TOKEN<7> = "" IF TWO_ONE THEN * If 2.1 or higher, remove the first two fields of the token. TOKEN = DELETE(TOKEN, 1, 0, 0) TOKEN = DELETE(TOKEN, 1, 0, 0) END *——————- * place token as last transaction in 0 record in !INDEXING LOCKED_FLAG = FALSE$ LOOP LOCK BANG_INDEXING_FILE, "0" THEN LOCKED_FLAG = TRUE$ UNTIL LOCKED_FLAG * try again after 2 seconds DELAY(2) REPEAT * place rebuild token at the end of the 0 record READ ZERO_REC FROM BANG_INDEXING_FILE, "0" THEN IF ZERO_REC[-1,1] NE @FM THEN ZERO_REC := @FM END ELSE ZERO_REC = '0' : @FM END ZERO_REC := TOKEN WRITE ZERO_REC ON BANG_INDEXING_FILE, "0" UNLOCK BANG_INDEXING_FILE, "0" RETURN Figure 2 FILE_NAME = 'SAMPLE_CUSTOMERS' INDEX = 'COMPANY_NAME' CALL INDEX.REBUILD.SUB(FILE_NAME,INDEX) IF STATUS() THEN TEXT = "Index rebuild subroutine failed with error %1%" CALL MSG(TEXT,,'',STATUS())

END ELSE

CALL INDEX.FLUSH(FILE_NAME,INDEX)

END

STOP

Most of this information appeared in an altered version by the same author in the November 1990 issue of REVealing, the UK Revelation User Group newsletter. Used with permission.