procedure AddIndex(const IName, IExpression: string; Options: TIndexOptions );
Description
Use AddIndex to create new index for the table associated with a dataset. The table must exist and dataset should be active before calling AddIndex. New index is automatically opened and added to IndexFiles and AllIndexFiles list.
Important note: When creating indexes for Mediator tables, Table must be opened as Exclusive.
Specify the name of new index as IName parameter. Depending on the table type (TableType property) the following happens:
For ttDBFNTX tables - new NTX index file with the name IName is created. It contains single order with the same name.
For ttDBFCDX tables - new order is added to the CDX file with the same name as TableName and CDX extension. If the CDX file does not exist, it is created.
For ttMEDNTX tables - same as for ttDBFNTX but no physical file is created. Instead, Mediator server creates index in SQL database and updates its repository storing approprite information describing the index.
For ttMEDCDX tables - same as for ttDBFCDX but no physical file is created. Instead, Mediator server creates index in SQL database and updates its repository storing approprite information describing the order.
IExpression contains either xHarbour (xBase) expression to be used for indexing or a list of fields that are to be indexed. If Options parameter contains ixExpression option then IExpression should contain an xBase expression to be used for indexing. Otherwise, it is assumed that it contains a semicolon-separated list of fields to be indexed. xHarbour expression may contain field names, xHarbour operators, calls to xHarbour/Clipper functions and calls to user defined functions attached as DLL file. See User defined xHarbour functions and additional DLLs for more information about user-defined xHarbour functions.
Options parameter is a set of attributes for the new index. The following attributes can be specified in a set:
ixExpression - as described - designates IExpression contains xHarbour (xBase) expression
ixUnique - no duplicate index values are allowed. Only first record with a given index key value is indexed.
ixDescending - an index with the descending sort order will be created
ixCaseInsensitive - records will be sorted case insensitively
As currently implemented, all indexes are implemented in xHarbour and Mediator as expression indexes. If you specify ixExpression attribute, the given expression (IExpression) is taken "as is" without modifications and used for index creation. Otherwise, if field list is specified in IExpression, this list is automatically translated into xHarbour character expression capable of indexing all specified fields. This expression is constructed as a sum of the following elements:
ˇSTR(fieldname,width,prec) for NUMBER fields
ˇfieldname for CHARACTER fields
ˇDTOS(fieldname) for DATE fields
ˇIIF(fieldname,".T.",".F.") for LOGICAL fields
Single field indexes are implemented as direct indexes without using the above convertion rules.
If ixCaseInsensitive is specified in Options, the resulting expression value is uppered using xHarbour's UPPER() function. This is also true for user expressions (ixExpression).
The above rules are recognized by may TMedTable functions which accept field lists as their argument, such as Lookup, Locate and range setting functions. As a general rule, it is preferable to either create all indexes as "field list" indexes and use typical dataset methods for setting ranges and searching or create indexes as expression indexes and use xHarbour low-level functions such as HbDbSeek and HbOrdScope for searching and scoping.
Note: When using scopes (ScopeTop, ScopeBottom, Scoped) programmer is responsible to prepare scopping values in such a way that they match the expression used for index generation.
Example:
Lets assume the table dbtest.dbf exists in Mediator, is opened as ttMEDCDX and has the following structure:
FN NUMBER 10
FNF NUMBER 12,2
FC CHARACTER 40
FD DATE
FL LOGICAL
The call
tab.AddIndex('myind1','fc;fd;fnf',[ixCaseInsensitive])
will create the order myind1 in dbtest.cdx order bag using the following automatically constructed xHarbour expression: UPPER(FC+DTOS(FD)+STR(FNF,12,2))
The call
tab.AddIndex('myind2','fn',[])
will create the order myind2 in dbtest.cdx order bag using the following xHarbour expression: FN
The call tab.AddIndex('myind2','SUBSTR(FC,10)+DTOC(FD)',[ixExpression,ixCaseInsensitive]) will
create the order myind2 in dbtest.cdx order bag using the following (uppered) xHarbour expression: UPPER(SUBSTR(FC,10)+DTOC(FD))