Command INDEX | Xbase++ | Alaska Software

Creates an index file.

INDEX ON <IndexKey> [TAG <cTagName>] ;
      TO <cIndexFile> ;
 [FOR    <lForCondition>] ;
 [WHILE  <lWhileCondition>] ;
 [NEXT   <nCount>] ;
 [RECORD <xRecordID>] ;
 [EVAL   <bBlock> [EVERY <nStep>] ] ;
 [REST] ;
 [ALL] ;
 [UNIQUE] ;
 [ASCENDING | DESCENDING] ;
 [ADDITIVE] ;
 [CANDIDATE] ;
 [SUBINDEX] ;
 [CUSTOM]
Or for index files with multiple indexes:
INDEX ON <IndexKey> TAG <cTagName> ;
 [TO     <cIndexFile>] ;
 [FOR    <lForCondition>] ;
 [WHILE  <lWhileCondition>] ;
 [NEXT   <nCount>] ;
 [RECORD <xRecordID>] ;
 [EVAL   <lExpression> [EVERY <nStep>] ] ;
 [REST] ;
 [ALL] ;
 [UNIQUE] ;
 [ASCENDING | DESCENDING] ;
 [ADDITIVE] ;
 [CANDIDATE] ;
 [SUBINDEX] ;
 [CUSTOM]

ON <IndexKey>

<IndexKey> is an expression (index key) specifying the value copied into the index file for each record in the current work area. The value of the index expression can have any type supported by the database engine.

The optional argument <cTagName> specifies the name of the index to create. It can be specified as either a literal or a character expression in parentheses. Using the name to specify the index means that knowing the ordinal position of the index in the list of open indexes is not required later (this is comparable to the alias name of a work area). The length of <cTagName> is limited by the DatabaseEngine which manages the index. characters.

<cIndexFile> determines the name of the index file containing the index created. The file name can be specified as a literal or as a character expression in parentheses. If the file name is specified without a file extension, the default file extension for index files is used. This depends on the database engine (DBE) managing the index files in the work area.

<lForCondition> is an optional logical expression defining a condition. The FOR condition is stored in the index file and is considered when the index file is rebuilt using REINDEX. Index values are added to the index only for records where <lForCondition>returns the value .T. (true).

<lWhileCondition> is an optional logical expression defining a condition. records are copied into the index, starting with the current record, as long as <lWhileCondition> returns the value .T. (true). As soon as the expression results in the value .F. (false), the index operation terminates. The WHILE condition is not stored in the index file, is only considered during execution of the command INDEX ON, and is not available during future reorganization of the index using REINDEX.

While conditions are supported by the DatabaseEngines NTXDBE and CDXDBE.

<nCount> optionally specifies the number of records copied into the index, starting with the current record.

<xRecordID> is an optional record ID (for DBF files, it is the record number). If it is specified, only the specified record is copied into the index.

<lExpression> is an optional logical expression evaluated after each <nStep> number of records. The EVAL expression is not stored in the index file and is considered only during the execution of the command INDEX ON. If the index is later reorganized using REINDEX, the EVAL expression is not available. The EVAL expression is generally used only to inform the user of the indexing progress. The procedure is terminated as soon as the EVAL expression provides the value .F. (false).

This option is ignored by the DatabaseEngines NTXDBEand CDXDBE.

<nStep>

<nStep> is a positive integer specifying after how many data records the EVAL expression is executed. The default value is 1.

This option is ignored by the DatabaseEngines NTXDBEand CDXDBE.

REST

The option REST determines whether only the records from the current to the last record are copied into the index. If a condition is indicated, the option ALL is the default value.

ALL

The option ALL specifies that all records of the current work area are considered for storage in the index. This is the default setting. If a condition is indicated, this condition is tested for all records.

UNIQUE

The option UNIQUE specifies whether several records having the same index key are each stored in the index file. When the option is specified, a unique index is created where each key value appears only once. If the option is missing, the current setting of Set(_SET_UNIQUE) is used.

ASCENDING

The option ASCENDING creates the index in ascending order. This is the default setting.

DESCENDING

The option DESCENDING creates the index in descending order. The function Descend() is not needed if this option is used. The DESCENDING attribute is stored in the index file.

ADDITIVE

When the option ADDITIVE is used, all open index files remain open in the work area. Otherwise they are closed before the new index is created.

CANDIDATE

The option CANDIDATE creates an candidate index. A candidate index is an index that never permits duplicate values on the specified fields/expression. The name "Candidate" refers to the status of the index; since candidate indexes prohibit duplicate values, they qualify as "candidates" to be selected as the primary index on a table. You can create multiple candidate indexes for a table.

SUBINDEX

With the option SUBINDEX an an sub-index is created. Sub-indexes use the current filter/scope and/or order to determine the keys to be inserted into the index. 'regular' indexes add all records from the associated table to the index if no FOR condition was specified. Sub-indexes are designed to ease query processing and to calculate and/or re-order sub-sets in a fast and efficient manner.

CUSTOM

The option CUSTOM creates an custom index. Custom indexes are empty, the DatabaseEngine does not automatically maintain index keys. Instead the programmer has to add and/or remove keys to/from an custom index using OrdKeyAdd() and OrdKeyRemove()

The index command INDEX ON creates an index in the current work area. The command is similar to DbCreateIndex(), but allows several indexes to be created per index file. However, this capacity depends on the ability of the active database engine (DBE) to support index files containing several indexes. Also, the options FOR, WHILE, EVAL, EVERY and DESCENDING require an appropriate DBE to be available (refer to the "Specifications of DatabaseEngines"chapters in the Xbase++ documentation).

The command INDEX ON closes all open index files in the current work area and recreates the index file <cIndexFile>. The index file is opened exclusively and the index is constructed in it. If the file cannot be exclusively opened (such as in a network environment), a runtime error is generated. Otherwise, after completion of the indexing, the index becomes the controlling index in the current work area and the record pointer is positioned on the first logical record.

When a DBE is used which supports several indexes per index file, <cIndexFile> does not need to be specified if a controlling index is already available in the work area. In this case, the index <cTagName> is added to the index file containing the controlling index.

An index makes possible the logical sorting of records based on the index key <IndexKey>. The value of the index key is contained in the index file <cIndexFile> and directly refers to the corresponding record. The records in the work area then appear in the same order as the index key values appear in the index file. The physical order of the records remains unchanged. This permits very rapid access when searching for specific records.

INDEX ON creates an index with character values sorted according to their lexical order, numeric values sorted by size, date values sorted chronologically and logical values sorted with .F. (false) considered smaller than .T. (true). An index cannot be created based on memo fields.

An index expression (index key) can contain several fields from a file. This allows the data to be logically sorted on several fields (columns). The values of fields having differing data types must be converted to a single data type. Always designing the index keys to return character values is recommended.

To create an index in strict alphabetical order, character values must be converted to upper case using the function Upper(). Otherwise, the sorting occurs based on the sorting weight of the characters selected with SET COLLATION.

An index expression must always return a value of constant length. Indexing character values using the function RTrim() leads to a defective index, since the resulting values have varying lengths if they have blank spaces at the end.

When date fields are combined with other fields in an index, date values should always be converted to character values using the function DtoS() and never with DtoC(). Otherwise, chronological order is not preserved.

Only field variables (database fields), and not memory variables, should be used in an index expression. Memory variables exist only at runtime and if they are referenced in an index expression, it will probably lead to a defective index.

Records marked for deletion or filtered out continue to appear in the index file.

Creating an INDEX


// The example shows various uses of the command 
// INDEX ON. 

PROCEDURE Main 

   USE Customer NEW EXCLUSIVE 

** Simple index 
   INDEX ON CustNo TO CustA UNIQUE 

** Alphabetical index with index name 
   INDEX ON Upper(LName+FName) TAG CustName TO CustB 

** Create subindex from current index 
   SEEK "MILLER" 
   INDEX ON Zip TO CustC ; 
       WHILE Trim(Upper(LName))=="MILLER" 

** Descending index 
   INDEX ON Zip TO CustD DESCENDING 

   USE Invoice NEW EXCLUSIVE 

** Combined index with date and character value 
   INDEX ON DtoS(InvDate)+PartNo TO InvA 

** Index for subset of records 
   INDEX ON DtoS(InvDate)+PartNo TO InvB ; 
        FOR InvDate >= CtoD("01/01/93") 

RETURN

Creating an custom INDEX

// In this sample a custom index is created. The index file 
// name is composed using the current date to remind the 
// date of snapshot creation. The index itself has all 
// customers ordered by their current sales total. Because 
// the index is a custom index future changes to the 
// TOTALSALES field don't affect the order. Therefore the 
// order can safely opened, even if the table gets updated. 
// 
PROCEDURE Main 

USE Customer EXCLUSIVE 
GOTO 2 
FIELD->TOTALSALES := 120 
DbCommit() 

GOTO 4 
FIELD->TOTALSALES := 10 
DbCommit() 

GOTO 6 
FIELD->TOTALSALES := 500 
DbCommit() 

INDEX ON TOTALSALES FOR TOTALSALES>0 ; 
      TO ("SALES_SNAPSHOT."+DTOS(Date())+".NTX" CUSTOM 

// the index includes record: 4,2,6 

GOTO 2 
FIELD->TOTALSALES := 5 
DbCommit() 

GOTO 5 
FIELD->TOTALSALES := 400 
DbCommit() 

// the index still includes record: 4,2,6 

CLOSE ALL 
RETURN

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Command INDEX Foundation