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.

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.

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

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

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.

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).

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

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.

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.

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.

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

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.