Table Indices

The tables managed by dataMineLib allow for the creation and management of one or more indices on a table. Each index is stored in the object repository along with the table on which it is defined. One indexObject record exists for each index defined on the table. Table indices are not allowed on meta tables or member tables. The table below summarizes the cursor functions allowing creation, management and query against an index defined on a table.

createIndex	Create an index on the cursor.
deleteIndex	Delete an existing index on the cursor.
disableIndicies	Disable all index processing.
enableIndices	Enable index processing and reindex any existing indices.
getRecordsByKey	Get a vector of records by key in a specified index.
getRecordRowsByKey	Get a vector of record rows by key in a specified index.
indexView	Make the current view reflect the order and content of a specified index. Provide an optional key value to filter the current view as well.
getBestIndex	Inspect the cursor's indices and return the name of the best index to use to search on a list of specified columns.

In addition, a few dataMineLib level functions are available that "wrapper" functionality available at the cursor level. A summary of these are shown in the following table.

createIndex	Create an index on a table. Opens a disk cursor, creates the index and then closes the disk cursor.
deleteIndex	Delete an index on a table. Opens a disk cursor, deletes the index and then closes the disk cursor.
open	The disk and static cursors open by default with indices enabled. The memory cursor opens by default with indices disabled. New arguments noIndicies: and withIndices:. noIndicies will open the cursor as if there were no indices defined on the table. noIndices is the default for the memory cursor. withIndices will open the memory cursor with indicies and is the default for disk and static cursors. New optional argument loadByIndex: and indexName and an optional keyVal argument to specify the cursor be opened and read in index order, filtered to include only those keys matching the optional keyVal argument if it was passed.

Note: The dataMineLib.open function has an option called disableIndicies: to allow you to open a cursor with its indices disabled. The indicies for a table are shared among the disk and static cursors opened on the same table.

Use the createIndex function to add a new index to a table. While indices may be created on disk or memory cursors, they are saved on cursor.close only for disk cursors. However, any indices you create on a memory cursor will be saved if you issue the cursor.save command on the memory cursor. createIndex is available for disk and memory cursors only.

Syntax: (cursor.createIndex indexName keySpec unique: )

indexName	A string or symbol containing the name of the index.
keySpec	A structure or string. If a string is provided then it must be a Lisp expression that will extract the appropriate key from the table columns as required. If a structure is provided it should contain a column list with sort specification for each column. ex: #{columnName: A: | D: ...). NOTE: Currently only the A: (ascending sort) option is allowed when defining the column list. You must specify A: for future compatiblity with the future addition of the D: (descending sort) option.
unique:	An optional symbol argument specifying that the index is to be a unique key index.
Returns	True

Use the deleteIndex function to delete an existing index from the table. deleteIndex is available for disk and memory cursors only.
Syntax: (cursor.deleteIndex indexName)

indexName	A string or symbol containing the name of an existing index.
Returns	True

Use the disableIndices function to disable index processing on the cursor. This is useful if you want to do a lot of table updates without the incremental overhead of index management. Use the enableIndices to turn index processing back on. disableIndices is available for disk and memory cursors only. When you disableIndicies on a disk cursor the indicies for the table are disabled for all static cursors opened on that table.
Syntax: (cursor.deleteIndex)

Returns

True

Use the enableIndices function to enable index processing on the cursor after a disableIndices. enableIndices will force a reindexing of all indices defined on the cursor. enableIndices is available for disk and memory cursors only. when you enableIndices ona a disk cursor the indicies for the table are enabled on all satic cursors opend on that table.
Syntax: (cursor.deleteIndex)

Returns

True

Inspect the cursor's indices and return the name of the best index to use to search on a list of specified columns.
Syntax: (cursor.getBestIndex colSpec)

colSpec	A vector containing one or more column names. #(column: ...)
Returns	Name of best index to use or false if no appropriate index was found.

Use the getRecordsByKey function to find and return a vector of records in the table by key. getRecordsByKey works against the entire table and will return records not in the current view. Remeber that the current view may have been filtered and not reflect the entire content of the table.
Syntax: (cursor.getRecordsByKey indexName keyValues )

indexName	A string or symbol containing the name of an existing index.
keyValues	For single column indicies the keyValues is a single value. For multiple column indicies (coposite keys) the keyValues argument is a vector containing the key components you wish to search on. If less components are passed than exist in the composite key then the search is t reated as a partial search.
Returns	A object vector containing the first record found or false if no record was found.

Use the getRecordRowsByKey function to find and return vector of record rows in the table by key. getRecordRowsByKey works against the entire table and will return a record rows for records not in the current view. Remeber that the current view may have been filtered and not reflect the entire content of the table.
Syntax: (cursor.getRecordRowsByKey indexName keyValues )

indexName	A string or symbol containing the name of an existing index.
keyValues	For single column indicies the keyValues is a single value. For multiple column indicies (coposite keys) the keyValues argument is a vector containing the key components you wish to search on. If less components are passed than exist in the composite key then the search is t reated as a partial search.
Returns	A number containing the first record's row found or false if no record was found.

Use the getRecordBySJKey function to find and return a record in a meta table by key . The getRecordByKey function will return the first record matching the supplied key value from the table. getRecordBySJKey works against the entire table and will return a record even if it is not in the current view. Remeber that the current view may have been filtered and not reflect the entire content of the table.
Syntax: (cursor.getRecordBySJKey tableKey rowKey )

tableKey
rowKey
Returns	A object vector containing the first record found or false if no record was found.

Use the getRecordRowBySJKey function to find and return a record's row in a meta table by key. The getRecordBySJKey function will return the row of the first record matching the supplied key value from the table. getRecordByRowSJKey works against the entire table and will return a record's row even if it is not in the current view. Remeber that the current view may have been filtered and not reflect the entire content of the table.
Syntax: (cursor.getRecordRowBySJKey tableKey rowKey)

tableKey
rowKey
Returns	A number containing the first record's row found or false if no record was found.

Use the indexView function to filter the current view so that it contains only records from the table that match the specified key value.
Syntax: (cursor.indexView indexName keyValues )

indexName	A string or symbol containing the name of an existing index.
keyValues	For single column indicies the keyValues is a single value. For multiple column indicies (coposite keys) the keyValues argument is a vector containing the key components you wish to search on. If less components are passed than exist in the composite key then the search is t reated as a partial search.
Returns	Number of records found.

Use the indexView function to filter the current view so that it contains only records from the meta table that match the specified key value.
Syntax: (cursor.indexViewbySJTableKey tableKey )

tableKey
Returns	Number of records found.

Use the indexView function to filter the current view so that it contains only records from the meta table that match the specified key value.
Syntax: (cursor.indexViewbySJRowKey rowKey )

rowKey
Returns	Number of records found.