Using BUILDINDEX_SUB (Functions/Subroutines/Programs)

BUILDINDEX_SUB

BUILDINDEX_SUB( indexType, indexName, indexInfo, clearFlag,

indexHandle, sortFileName)

Using BUILDINDEX_SUB

Builds an index in a single table. The index to be built can be a Btree index, a Cross Reference indexes, or a Relational index.

+ The subroutine BUILDINDEX_SUB cannot build Quickdex or Rightdex indexes. To build those, use the system subroutine ???

dataTable

The name of the table with for which you want to rebuild indexes. The table must be attached.

dataHandle

The file handle for dataTable.

F If you are rebuilding an index for a symbolic column, you must also open the dictionary of the table to @DICT before calling BUILDINDEX_SUB.

indexHandle

The file handle for the index table (the !file) associated with dataTable. For example, if you are rebuilding indexes in the table SAMPLE_CUSTOMERS, you must also open !SAMPLE_CUSTOMERS and pass the file handle of the index in this parameter. You can use the system subroutine INDEX.OPEN to open the index table.

fieldInfo

An array (@FM-delimited) of indexes (columns) to rebuild. Each index to be rebuilt is one field in this array, with details about each index as values (@VM-delimited) within each field. The layout of each field is as follows:

Most of this information can be read directly from the dictionary entry of the column for which the index is being rebuilt. (See the second example program for details on where to find this information.)

One exception is the information for justification and sort direction. For Relational indexes, use this table to determine what values to pass:

flagAll

Set to true (1) if all indexes in the table are being rebuilt. This allows faster processing.

Warning! If not all indexes are being rebuilt or if you are not sure if all indexes are being rebuilt do not set this flag. You will lose index information.

Values returned

The return status of BUILDINDEX_SUB is indicated by the system variable @file.error. If the process completes successfully, the variable is null. Multiple errors are delimited with record marks (@RM). The error number appears in field 1 of each @file.error "record", and additional information about the error appears in field 2. Possible errors are:

Notes

Status line display

The BUILDINDEX_SUB subroutine displays information in the status line first as it scans the data in the table, and then as it rebuilds the index. For tables with more than 100 rows, the status line is updated only every 100 rows. There is no option to suppress this display.

Locking and multi-user access to indexes

While you are rebuilding indexes, no other user can be updating the index (meaning that users cannot change any indexed columns throughout the table). To ensure that no other users are updating the index, the control entry *INDEXING and the 0 (zero) entry in the index (!table) are locked. During the rebuild itself, these index entries are locked: name*INDEXING, name*, and name*ROOT.

BUILDINDEX_SUB loops until all of these entries are locked. Therefore, if another user has any of these entries in use, the rebuild process halts until the entry is available. By the same token, once the rebuild process is underway, any attempt to update the index will simply wait until the control entries are available again.

It is therefore recommended that you do not attempt to rebuild indexes during periods of heavy use, because your rebuild process could be delayed, and because while it is running, no user can be updating the indexes.

Quitting during the rebuild process

The user is able to abort the rebuild process by pressing [Esc] while a rebuild is underway. However, this leaves the indexes in an unpredictable condition; the indexes cannot be used until the are rebuilt.. You should always test to see if the process has been interrupted, and if so, to warn the user that the indexes should be rebuilt before they are used.

Correct use of BUILDINDEX_SUB

Figure 1. A program that illustrates all three types of indexes.

declare subroutine buildindex_sub, fsmsg

declare function index.open

tablename = "PEOPLE_10000"

open tablename to datahandle else

fsmsg()

stop

end

(continued)

open "DICT", tablename to @dict else

fsmsg()

stop

end

if index.open("!":tablename, indexhandle) else

fsmsg()

stop

end

fieldinfo = ""

fieldinfo<1, 1> = "AGE"

fieldinfo<1, 2> = "Btree"

fieldinfo<1, 3> = ""

fieldinfo<1, 4> = "AGE"

fieldinfo<1, 5> = 1 ;* Btree

fieldinfo<1, 6> = 4 ;* Column position

fieldinfo<1, 7> = 1 ;* Convert to Uppercase

fieldinfo<1, 8> = "RL"

fieldinfo<1, 9> = "AA"

fieldinfo<1, 10> = 0 ;* single value

fieldinfo<2, 1> = "ADDRESS"

fieldinfo<2, 2> = "CrossRef"

fieldinfo<2, 3> = ""

fieldinfo<2, 4> = "ADDRESS_XREF"

fieldinfo<2, 5> = 1 ;* Btree

fieldinfo<2, 6> = 5

fieldinfo<2, 7> = 1 ;* Convert to Uppercase

fieldinfo<2, 8> = "LL"

fieldinfo<2, 9> = "AA"

fieldinfo<2, 10> = 0 ;* single value

fieldinfo<3, 1> = "CITY"

fieldinfo<3, 2> = "Relational"

fieldinfo<3, 3> = "CITIES*PEOPLE_IN_CITY*BOT" ;* Related info

fieldinfo<3, 4> = "CITY"

fieldinfo<3, 5> = 2 ;* Relational

fieldinfo<3, 6> = 6

fieldinfo<3, 7> = 1 ;* Convert to Uppercase

fieldinfo<3, 8> = "LL"

fieldinfo<3, 9> = "AA" ;* Sort for "BOT" index

fieldinfo<3, 10> = 0 ;* single value

buildindex_sub(tablename, datahandle, indexhandle, fieldinfo, 0)

if @file.error then

if @file.error[1, @RM] eq "421" then

text = "The process has been cancelled. You MUST rebuild"

text←1> = "the indexes before you can use them!"

msg( text )

end else

fsmsg()

end

end

Figure 2. An example of a program that builds the index information array from information in the dictionary entry for the indexed column.

declare subroutine msg, fsmsg

msg('Rebuild index in what table?','RC', tableName, "" )

if tableName else stop

open tableName to tableHandle else

fsmsg() ; stop

end

/* open index */

bang_tableName = "!":tableName

open bang_tableName to bangTableHandle else

msg(tableName: " is not an indexed table!" ) ; stop

end

call msg('Rebuild index for what column?','RC', indexName, ) if indexName else stop call msg("what index type? [B/C/R]", "RC", indexType, "" ) if indexType else stop if index( "BCR", indexType, 1 ) else msg("not a valid index type!" ) ; stop end open "DICT." : tableName to @dict else call fsmsg() ; stop end /* read entry from dictionary, then assemble necessary information for indexInfo array */ read dictEntry from @dict, indexName else call fsmsg() ; stop end indexInfo = "" relationalInfo = "" begin case case indexType = "B" if dictEntry<6> else msg("%1% is not a Btree index!", , , indexName ) ; stop end indexTypeName = "Btree" indexTypeCode = 1 case indexType = "C" if dictEntry<22> else msg("%1% is not a Cross reference index!", , , indexName ) ; stop end indexTypeName = "Crossreference" indexTypeCode = 1 (continued) case indexType = "R" if dictEntry<23> else msg("%1% is not a Relational index", , '', indexName ) ; stop

end

indexTypeName = "Relational"

indexTypeCode = 2

relational_info = dictEntry<23>

end case

/* fill in the rest of the index_info array using data from the

dictionary entry */

indexInfo< 1 > = indexName /* name of column &*/

indexInfo< 2 > = indexTypeName /* text of index type */

indexInfo< 3 > = relationalInfo /* null for Btree/Xref indexes */

if indexType = "C" then

indexName := "_XREF"

end

indexInfo< 4 > = indexName /* name of column */

indexInfo< 5 > = indexTypeCode /* 1=Btree/Xref, 2=Relational */

if dictEntry<1> = "F" then

indexInfo< 6 > = dictEntry< 2 > /* column position */

end else

indexInfo< 6 > = indexName /* column name */

end

indexInfo< 7 > = Not( dictEntry< 26 > ) /* case-sensitive flag */

gosub GetJustification

indexInfo< 8 > = just : sortOrder

gosub getSortDirection

indexInfo < 9 > = sortDirection

if dictEntry< 4 > = "M" then /* multivalue flag */

indexInfo< 10 > = 1

end else

indexInfo< 10 > = ""

end

convert @fm to @vm in indexInfo

call buildindex_sub( tableName, tableHandle, bangTableHandle, indexInfo, 0 )

status = status()

fe = @file.error

debug

if @file.error then

if @file.error[1,@rm] eq 421 then

msg("Process interrupted! Please rebuild indexes before using!")

end else

fsmsg()

end

end

stop

(continued)

/* ——————————————————————-*/

GetJustification:

sortOrder = "L" /* default except for relational */

if indexType = "R" then

sortOrder = field( relationalInfo, "*", 3 )

if sortOrder[2,1] = "R" then

sortOrder = "R"

end else

sortOrder = "L"

end

end

just = dictEntry< 9 >

if just ne "R" then just = "L" /* make "T" and "C" into "L" */

return

/* ——————————————————————-*/

GetSortDirection:

sortDirection = "AA" /* default except for relational */

if indexType = "R" then

sortOrder = field( relationalInfo, "*", 3 )

if sortOrder = "TOP" or sortOrder[1,1] = "D" then

sortOrder = "D"

end else

sortOrder = "A"

end

sortDirection = "A" : sortOrder

end

return