INDEXES

SUBROUTINES MAIN PAGE

INDEX.OPEN
INDEX.REBUILD.SUB

Removing Indexing Manually

Sometimes you need to remove indexing from a file manually. This is usually happens when you clear or delete the indexing file (!filename) explicitly, which also deletes important control information. When you then try to remove indexing from the file, the system returns an error message.

In order to remove indexing from a file manually, there are three steps to be performed.
_ Update the volume directory (formerly known as the media map).

_ Delete the indexing file.

_ Update the individual dictionary records. Each of these steps will be covered in turn. Updating the Volume Directory The volume directory maintains information about each file in a volume. Part of that information is a list of Modifying Filing Systems (MFSs) associated with the file. When a file has indexing on it, an MFS called SI.MFS is placed on the file. This MFS must be removed. To remove the MFS, follow these steps.

_ Log to the account SYSPROG. Volume directories can only be edited from SYSPROG.

_ Attach the volume directory for the volume containing the file. The volume directory is an Advanced Revelation file called REVMEDIA. For example, if the file is on a volume called SALES_DATA, the TCL command would be:
ATTACH SALES_DATA REVMEDIA

_ Bring the proper volume directory record up in the editor. Each file on the volume has a record in the volume directory that is identified by a two-part key. The first part of the key is the filename, and the second part is the name of the account that the file is in. If the indexed file is the CUSTOMERS file in the account SALES, the TCL command would be:

EDIT REVMEDIA CUSTOMERS*SALES

_ Edit line two of the volume directory entry to remove SI.MFS. If line two has just SI.MFS on it, you can delete it by pressing [Ctrl-X]. If there are other MFSs on the file, use the [Delete] key to delete SI.MFS and the trailing value mark, if any.

_ Press [F9] to save the change, then press [Esc] to exit the editor.

Deleting the Indexing File

If the indexing file (!filename, not !INDEXING) still exists, it must be deleted. To delete the file, follow these steps:

_ Log to the account to which the file belongs. Continuing with the example above, you need to log to the SALES account. From level one of TCL you can enter this command: LOGTO SALES

_ Attach the volume that holds the file:
ATTACH SALES_DATA

_ Delete the indexing file:
DELETEFILE !CUSTOMERS

Updating the Dictionary

The indexing system maintains a variety of information in the dictionary record of an indexed field. Depending on the type on indexing, different information is maintained. You will have to edit the dictionary record for each field in the file that was indexed: Warning! Do not use [Ctrl-D] (delete a line) or press [Enter] while editing a dictionary record. This will alter the dictionary record, making it unusable. To edit a dictionary record, enter from TCL:

EDIT DICT.FILENAME FIELDNAME

Btree Indexes

Field 6 of each dictionary record tells the system whether or not this field has a Btree index. If there is a 1 in this field, the field has had a Btree index installed. Change the 1 to a 0 and save the record.

Cross Reference Indexes

When a Cross Reference index is placed on a field, a symbolic field is created to maintain the index. This field can be identified by the .XREF extension. Because this field is no longer required, it should be deleted. If a cross reference index is placed on the field again, the symbolic field will be recreated. In addition, a field with a Cross Reference index has the name of the cross reference symbolic in field 22 of the dictionary record. Position the cursor on line 22 of the dictionary record, press [Ctrl-X], and save the record.

Relational Indexes

Relational indexes use several fields in the dictionary record to store their control information. In the record that serves as the target of the relational index, change the 1 on line 25, which tells the system that this field's data is protected, to a 0. Line 24 contains the name of the relational index, which should be cleared with [Ctrl-X]. To completely remove the Relational index, the dictionary of the source file must also be edited. In that dictionary, the source field will have the name of the related field on line 23. Remove the name with[Ctrl-X].

Final Steps In Advanced Revelation 2.0 and higher

There are two other records in the dictionary that should be deleted: %PROTECT.SPEC% and %FIELDS%. Delete these fields after editing the indexed fields. Reattach the file and the system will recreate these records, reflecting the changes made to the dictionary. At this point the file has had all indexing removed. Indexes can be re-established as necessary.

Rebuilding Indexes from R/BASIC TechBull 73


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

Rebuilding Relational Indexes Using R/BASIC

Technical Bulletin #73 documents a method of rebuilding indexes from within an R/BASIC program. This method consists of creating a special "rebuild all" index transaction and placing it in the index transaction file either !INDEXING, or in Advanced Revelation 2.1 and higher, the !file itself.

The documentation in Technical Bulletin #73 covers only Btree indexes, and by extension, Cross Reference indexes. Relational indexes were not addressed. However, only minor changes are required to accommodate Relational indexes. Those changes are described below. Code for a modified version of the code in Technical Bulletin #73 appears in Figure 1.

Broadly speaking, there are three changes to the code:

1) Add an argument by which the type of the index to be rebuilt (Btree or Relational) can be passed.
2) Expand the error checking for the index so that it looks for Relational as well as Btree indexing.
3) Put different information into the "rebuild all" index transaction depending on the type of the index to be rebuilt.

Specifying Index Type

If you wish to use a single subroutine to handle all types of index rebuilds, you must pass into the routine the index type (Btree or Relational). You can do this by passing an extra argument to the subroutine for the type, which will allow you to determine where to do special processing to accommodate relational indexing.

This change affects the calling syntax for this program. The new syntax is:

CALL INDEX.REBUILD.SUB(FILE,INDEX,TYPE)

where type can be "B" (Btree) or "R" (Relational).

Checking for Indexing

When you rebuild a Btree index, you can check to see if the field is indexed by looking for a flag in field 6 of the dictionary record. To check for relational indexing, check field 23 -- if there is something there, the field is relationally indexed.

Field 23 is not a simple true/false flag. Instead, it is a specification for the target file, target field, and sort order of the index in this format:

target_file*target_field*sort

The "sort" specification is of the form BOT, AR, etc., as entered when the index was established. This entire specification is used when building the index token (see below).

Because you may need to look in different places in the dictionary record (depending on the type of index you are rebuilding) the program extracts the entire dictionary record rather than just field 6 before doing its error checking.

Index Token

Field 3 of the "rebuild all" index token for a Btree index is simply the index name. For a relational index, however, this field should contain instead the entire index specification as found in field 23 of the dictionary record (see "Index Flags" above).

In the program, the third field of the token is made dependent on the index type. The value for the third field is assigned as part of the error checking for valid index types (see "Checking for Indexing", above).

List of Specific Changes

The changes discussed above are all found in Figure 1. Other changes have been made as well, some of which do not directly address relational indexing. All changes are marked in bold in Figure 1. This is a summary of the changes since Technical Bulletin 73:

1) Accepts an argument type (B or R) to indicate the type of index to be rebuilt.
2) Incorporates an additional error value for STATUS( ) to indicate a bad index type (not B or R). Provides separate error codes for non-existent and non-indexed fields.
3) Checks that arguments have been passed with a value (aborts if not). This guards against the possibility that the routine is called with an uninitialized variable as one of the arguments.
4) Does error checking against the index type passed into the routine.
5) Uses more sophisticated checking to make sure that the nominated field exists and that it is indexed according to the type requested.
6) Skips the creation of the file_spec parameter for the rebuild token unless the token is being built for a Btree rebuild in versions previous to 2.1.
7) Builds the third field of the "rebuild all" token according to the type of the index being rebuilt.

Figure 1

SUBROUTINE INDEX.REBUILD.SUB(FILE_NAME,INDEX,TYPE)

* desc: modified version of code in TB73. Handles relational
* indexes in addition to btree indexes.
*---------------------------------------------------------------------------
* index types are:
*
* B = Btree (includes Xref by specifying the .XREF suffix)
* R = Relational
*
* error return codes in STATUS()
*
* 0 = everything ok
* 1 = !INDEXING or !file_name (2.1+) not available
* 2 = "file_name" is not available
* 3 = "index" is not a field name in "file_name"
* 4 = "type" is not a valid indexing type (B or R)
* 5 = "index" is not an indexed field
*
*-------------------
* init
EQU TRUE$ TO 1
EQU FALSE$ TO 0

* error return codes for STATUS()
EQU OK$ TO 0
EQU NO_BANG_INDEXING$ TO 1
EQU BAD_FILE$ TO 2
EQU BAD_INDEX$ TO 3
EQU BAD_TYPE$ TO 4
EQU NOT_INDEXED$ TO 5

DECLARE SUBROUTINE DELAY
DECLARE FUNCTION GETCONFIGURE, UNASSIGNED

IF UNASSIGNED(FILE_NAME) THEN
STATUS() = BAD_FILE$
RETURN
END
IF UNASSIGNED(INDEX) THEN
STATUS() = BAD_INDEX$
RETURN
END
IF UNASSIGNED(TYPE) THEN
STATUS() = BAD_TYPE$
RETURN
END

STATUS() = OK$ ; * assume ok
*-------------------
OPEN FILE_NAME TO T$FILE ELSE
STATUS() = BAD_FILE$
RETURN
END
* check type
TYPE = TYPE[1,1]
CONVERT @LOWER.CASE TO @UPPER.CASE IN TYPE
IF INDEX('BR',TYPE,1) ELSE
STATUS() = BAD_TYPE$
RETURN
END

* check for valid field name and index type
DICT_REC = XLATE("DICT.":FILE_NAME, INDEX, '', "X")
BEGIN CASE
CASE DICT_REC = ''
* no such field
STATUS() = BAD_INDEX$
CASE TYPE = 'B'
BTREE_FLAG = DICT_REC<6>
IF BTREE_FLAG THEN
* for Btree indexes, field name is used in token
INDEX_TOKEN = INDEX
END ELSE
STATUS() = NOT_INDEXED$
END
CASE TYPE = 'R'
RELIX_FLAG = DICT_REC<23>
IF RELIX_FLAG THEN
* for Relational indexes, full index spec in put into token
INDEX_TOKEN = RELIX_FLAG
END ELSE
STATUS() = NOT_INDEXED$
END
CASE 1
* undefined error
STATUS() = BAD_TYPE$
END CASE
IF STATUS() THEN RETURN

*-------------------
* get version number
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
BANG_FILE_NAME = '!':FILE_NAME
END ELSE
BANG_FILE_NAME = '!INDEXING'
END

OPEN BANG_FILE_NAME TO BANG_INDEXING_FILE ELSE
STATUS() = NO_BANG_INDEXING$
RETURN
END
*-------------------
* build token
FILE_SPEC = ''
IF TYPE EQ 'B' AND VERSION < "2.1" THEN
* 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
END

TOKEN = ''
TOKEN<1> = FILE_SPEC
TOKEN<2> = 1 ; * no of 'transactions' to accomplish for this file
TOKEN<3> = INDEX_TOKEN ; * field name -or- relational specification
TOKEN<4> = "%%ALL.IDS%%" ; * actual rebuild/clear token
TOKEN<5> = "" ; * trailing fms are important
TOKEN<6> = ""
TOKEN<7> = ""

* if on 2.1+, the 1st 2 fields of the token are no longer used
IF TWO_ONE THEN
TOKEN = DELETE( TOKEN, 1, 0, 0)
TOKEN = DELETE( TOKEN, 1, 0, 0)
END

*-------------------
* place token as last transaction in 0 record in !INDEXING or !file_name
LOCKED_FLAG = FALSE$
LOOP
LOCK BANG_INDEXING_FILE, "0" THEN LOCKED_FLAG = TRUE$
UNTIL LOCKED_FLAG
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

INDEX.OPEN

2.X

Description
INDEX.OPEN is an external function which 
attempts to open the index file specified and return a file 
variable for the index.  The index file must exist in an 
attached volume.
Arguments
index.file
The name of the index (! prefix) file to 
open, or a file specification consisting of a source 
datafile name, associated account name, and media name.  The 
media name is stored in the first field of a specified 
record in the<$IVOLUMES> VOLUMES file.  The account name is 
stored in the third field of a specified record in the 
<$IFILES>FILES file.  These names must be delimited by * 
(asterisk).
filevar
A variable to be associated with the file
Values returned
If the index file was opened successfully, 
INDEX.OPEN returns true and passes the file variable 
assigned to file in filevar, otherwise INDEX.OPEN returns 
false.
Examples

*Initialization for each example
DECLARE FUNCTION INDEX.OPEN
DECLARE SUBROUTINE MSG

*Open the specified index (! prefix) file
STATUS = INDEX.OPEN('!CUST',FILEVAR)
MSG('STATUS = %1%
FILEVAR = 
%2%','','',STATUS:@FM:FILEVAR)

(Examples cont.)

*Get the media name and open the index file 
associated with the *datafile specification
FILE = 'CUST'
ACCOUNT = 'SYSPROG'
OPEN 'VOLUMES' TO VOL.FILE THEN
READ VOL.REC FROM VOL.FILE,VOLUME ELSE 
STOP
MEDIA = VOL.REC<<1>>
FILE.SPEC = FILE:'*':ACCOUNT:'*':MEDIA
STATUS =  INDEX.OPEN(FILE.SPEC,FILEVAR)
MSG('STATUS = %1%
FILEVAR = 
%2%','','',STATUS:@FM:FILEVAR)
END