====== Using BUILDINDEX_SUB (Functions/Subroutines/Programs) ====== ====== ====== ==== Created at 14 NOV 1996 02:19PM ==== **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: |**Value**|**Contents** | | |The name of the column (index). For Cross Reference indexes this is the name of the column without the "_XREF" or ".XREF" suffix. | | |The literal text //Btree//, //Cross Reference//, or //Related To// to describe the type of index being rebuilt. This text is used in error messages if necessary. | | |Control information for Relational indexes the full description of the relation in this format:\\ \\ \\ \\ //targetTable*targetColumn*sortOrder//\\ \\ // //\\ \\ This information is stored in field 23 of the dictionary entry for the source column for the relational index.| | |The full name of the column. For Btree and Relational indexes, this is the same as the information in value 1. For Cross Reference indexes this is the name of the column //with// the "_XREF" or ".XREF" suffix. | | |A code indicating the index type: 1 for Btree and Cross Reference, 2 for Relational. | | |The column position (field number) of the index. For indexes on symbolic columns, use the full column name. | | |A case-sensitivity flag determining whether the data should be converted to uppercase. 1 (true) means data should be converted (a case-insensitive index); 0 (false) indicates data should not be converted (a case-sensitive index). | | |The justification of the column. For Btree and Cross Reference indexes this is the justification of the column as defined in the dictionary (L or R only) followed by the literal "L". For Relational indexes, see the table below. | | |The sort direction. For Btree and Cross Reference indexes this is always "AA". For Relational indexes, see the table below. | | |A multivalue flag. Pass true (1) if the column is multivalued, false otherwise.\\ \\ | 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: |**Index Sort Type**|**Justification (Dict just + value in this column)**|**Sort **\\ \\ **Direction**| |AL |L |AA | |AR |R |AA | |DL |L |AD | |DR |R |AD | |TOP |L |AD | |BOT |L |AA\\ \\ ** ** | **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: |**Error**|**Meaning** |**Add'l Info.** | |421 |User canceled process. | | |259 |Related table is not available. |<2,1> table name\\ \\ <2,2> rel. table name | |B286 |The column name is not a valid index name. |<2> name of column | |B702 |The index must be rebuilt (if the process is canceled by the user).|<2> name of index | |S195 |Out of disk space. Unable to rebuild index. |<2,1> type of index\\ \\ <2,2> name\\ \\ ** **| **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