xHarbour Reference Documentation > Command Reference

INDEX

Creates an index and/or index file.

Syntax

   INDEX ON <indexExpr> ;
       [TAG <cIndexName>] ;
        [TO <cIndexFile>] ;
       [FOR <lForCondition>] ;
     [WHILE <lWhileCondition>] ;
       [ALL] ;
      [NEXT <nNumber>] ;
    [RECORD <nRecNo>] ;
      [REST] ;
      [EVAL <bBlock>] ;
     [EVERY <nInterval>] ;
    [UNIQUE] ;
 [ASCENDING|DESCENDING] ;
[USECURRENT] ;
  [ADDITIVE] ;
    [CUSTOM] ;
[NOOPTIMIZE] ;
 [TEMPORARY] ;
 [USEFILTER] ;
 [EXCLUSIVE]

Note: the TAG and TO clauses are optional, but 
it is necessary to specify at least one of them.

Arguments

ON <indexExpr>

This is an expression which is evaluated for the records in the current work area. The value of <indexExpr> determines the logical order of records when the index is the controlling index. The data type of the index may be Character, Date, Numeric or Logical. The maximum length of an index expression and its value is determined by the replaceable database driver used to create the index.

Important:  When the index expression yields values of data type Character, the index value must be of constant length. The functions Trim() or Ltrim() cannot be used in an index expression since they result in a corrupt index.

TAG <cIndexName>

This is the symbolic name of the index to create in an index file. It can be specified as a literal name or a character expression enclosed in parentheses.

TO <cIndexFile>

<cIndexFile> is the name of the file that stores the new index. The file name can be specified as a literal name or a character expression enclosed in parentheses. When the file extension is omitted, it is determined by the database driver that creates the file.

Although both TAG and TO clauses are optional, at least one of them must be used.

FOR <lForCondition>

This is an optional logical expression which is evaluated for all records in the current work area. Those records where <lForCondition> yields .T. (true) are included in the index. The FOR condition is stored in the index file and is maintained by the database driver when records are updated. That is, if a record is changed so that it does not match the FOR condition, it is removed from the index.

The FOR expression cannot exceed 250 characters in length. RDDs that do not support a FOR condition when creating indexes generate a runtime error when this option is used.

WHILE <lWhileCondition>

This is a logical expression indicating to continue index creation while the condition is true. The INDEX command stops evaluating records as soon as <lWhileCondition> yields .F. (false).

Unlike the FOR expression, which is stored in the index file and exists throughout the lifetime of an index, the WHILE condition is only evaluated during index creation. It is discarded when the index is complete.

RDDs that do not support a FOR condition when creating indexes generate a runtime error when this option is used.

ALL

The option specifies that <indexExpr> should be evaluated with all records in the current work area. It is the default scope option if the INDEX command.

NEXT <nNumber>

This option restricts the number of records to evaluate during index creation to <nNumber>.

RECORD <nRecNo>

This option adds only the record with the record number <nRecNo> to the index.

REST

The REST scope instructs the database driver to evaluate <indexExpr> for the records beginning with the current record down to the end of file.

EVAL <bBlock>

This parameter is a code block that must return a logical value. The indexing operation continues as long as Eval(<bBlock>) returns .T. (true). The operation is stopped when Eval(<bBlock>) returns .F. (false).

The code block is evaluated for each record unless the EVERY option is specified. It is recommened to use EVERY in conjunction with EVAL.

EVERY <nInterval>

This option is a numeric value specifying the number of records after which the EVAL block is evaluated. This can improve the indexing operation especially for large databases. EVERY is ignored when no EVAL block is supplied.

UNIQUE

The option suppresses inclusion of records that yield duplicate index values. When an index value exists already in an index, a second record resulting in the same index value is not added to the index.

ASCENDING|DESCENDING

These options are mutually exclusive. They specify if the index is created in ascending or descending order. If not specified, ASCENDING is the default.

When the ASCENDING or DESCENDING option is not supported by the database driver, a runtime error is generated.

USECURRENT

The option instructs the database driver to use the current logical order of records for navigating the database during index creation. The logical order is determined by the controlling index and the SET SCOPE restriction.

When the USECURRENT clause is omitted, the records in the current work area are evaluated in physical order.

ADDITIVE

The option makes sure that index files remain open during index creation. If not specified, all files but <cIndexFile> are closed prior to indexing.

CUSTOM

This option creates an empty index that is custom built. Index entries must be added or deleted explicitely using functions OrdKeyAdd() and OrdKeyDel(). RDDs that support custom indexes do not add or delete keys automatically.

NOOPTIMIZE

This specifies a non-optimized FOR condition. If not specified, it will be optimized if the RDD supports it.

TEMPORARY

If this option is specified, a temporary index is created which is automatically destroyed when the index is closed. The temporary index may be created in memory only or in a temporary file. This lies in the responsibility of the RDD used for index creation.

USEFILTER

This option instructs the RDD to recognize a filter condition active in the current work area that is set with SET FILTER or SET DELETED. In this case, filtered records are not included in the index.

EXCLUSIVE

With this option, the index file is created in EXCLUSIVE file access mode. By default, the index file is created in SHARED mode.

Description

The INDEX command is used to created indexes and/or index files. An index is stored in an index file which is maintained separately from a database. Indexes provide for logical ordering of records in a work area by specifying the expression <indexExpr>. An index file is created by the INDEX command and the index expression is evaluated with the records in the current work area. The resulting index value is stored in sorted order in an index file.

With RDDs that support multiple indexes per index file, such as DBFCDX, indexes are added to <cIndexFile> when this file is open in the current work area.

The commands SET INDEX and SET ORDER are used to open an existing index file and select an index as the controlling one. When an index is selected as the controlling index, database navigation with SKIP occurs in logical index order and not in the physical order of records as they are stored in the database.

Open index files can be re-organized using the REINDEX command. Note, however, that only the FOR condition and the ASCENDING/DESCENDING option are maintained with REINDEX. All other options available with the INDEX command are not used with REINDEX.

Info

See also:	CLOSE, DbOrderInfo(), Descend(), DtoS(), IndexKey(), IndexOrd(), OrdCreate(), OrdKeyNo(), REINDEX, SEEK, SET INDEX, SET ORDER, SORT, USE
Category:	Database commands , Index commands
Source:	rdd\dbcmd.c
LIB:	xhb.lib
DLL:	xhbdll.dll

Example

// The example creates three indexes in one index file and
// browses the customer database in the index order LName

   REQUEST DBFCDX

   PROCEDURE Main
       RddSetDefault( "DBFCDX" )
       USE Customer

       INDEX ON Upper(FirstName) TAG FName TO Cust01
       INDEX ON Upper(LastName)  TAG LName TO Cust01
       INDEX ON Upper(City)      TAG City  TO Cust01

       SET ORDER TO TAG LName
       Browse()

       USE
   RETURN