Table Indices

 

Indices Overview

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.

createIndex

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

deleteIndex

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

disableIndices

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

enableIndices

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

getBestIndex

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.

getRecordByKey

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.

getRecordRowByKey

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.

getRecordBySJKey

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.

getRecordRowBySJKey

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.

indexView

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.

indexViewBySJTableKey

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.

indexViewBySJRowKey

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.