list_index

Performs index operations on a list.

Available in:

Apps (win) Apps (char) Reportwriter RPC Standalone PL
X X X X X

Syntax

expr list_index(list,option[[,index][,expr0[,...exprn]]])
list           list
int            option
int            index
expr           expr0,...exprn

Description

Manages indexes on lists and positions the current record based on a predicate. Both btree and hash indexes are supported. Column references are always zero-based.
list specifies the list to use.

option specifies what to do. It can be one of the following (defined in trim.h):
  • idx_cre_btree - Create a btree index.

    expr0[,...exprn] are the columns to be used. The number of unique key items is returned. If this number is the same as the number of rows in the list then the index is unique. 

          list_index(LL,idx_cre_btree | idx_delete,0,1));
    Deletes and creates a btree index on column 1.

  • idx_cre_hash - Create a hash index.

    expr0[,...exprn] are the columns to be used. The item count for the hash bucket with the most chained items. This will be the worst case number of comparisons needed. 

          list_index(LL,idx_cre_hash | idx_delete,0,1,0));
    Deletes and creates a hash index on column 1 and 0.

  • idx_delete - Delete a index.

    This option may be OR'd (|) with any of the 'create' options. No error is issued if the index does not exist.

  • idx_uniquecnt - Return number of unique key items in index.

    For a hash index this value is always 0.

  • idx_maxchain - Return item count for hash bucket with most chained items.

    For a btree index this value is always 0.

  • idx_indexcnt - Return number of indexes in the list.

  • idx_indexcols - Return the index columns.

    The index columns are returned as a string with blank separated integers.

  • idx_pos_XX - Position the current item.

    expr0[...exprn] are the data values to be used. They must match the index columns (both in number and individual datatypes). No conversions are done during comparisons. For hash indexes only the equal operator is allowed (idx_pos_eq). 0 is return if the positioning failed. On success 1 is returned and the current item is set (based on the operator) as follows:

    • idx_pos_eq - The actual item if it is unique. For non-unique indexes it is positioned on the first item for btrees. For non- unique hash indexes it is positioned on the first item in the bucket chain.

    • idx_pos_lt - The closest item that is smaller.

    • idx_pos_leq - The closest item that is smaller if the item itself is not found. If the item is found then it is positioned at the last one (if non-unique).

    • idx_pos_gt - The closest item that is greater.

    • idx_pos_geq - The closest item that is greater if the item itself is not found. If found it is positioned as for 'idx_pos_eq'.
index specifies the index to use.

Notes

When creating an index the regular sequence will always be set to the sequence of the particular index type created: sorted for btrees and and hash function order for hash indexes.

When positioning within an index this order becomes the order used for all list functions such as list_seek(), etc.

The indexes are lost when the list is written into a regular file using list_copy() and list_file(). The indexes are preserved for list_copy() and list_file() into shared memory however this will use more memory when more than one index exists.

Hash indexes work best for unique integer keys.

Lists with indexes are read-only. Once the last index has been dropped the list becomes modifiable again.

Examples

Create an index on two columns and search for a value: 
ll = list_open("select id,name,dept,job,years,salary,comm from staff",100000);
list_index(ll,idx_cre_btree,0,0,1);
list_index(ll,idx_pos_eq,0,10,"SANDERS");