FairCom DB API for C

Adding or deleting indexes

Indexes and index segments are key-based search tools that make record seeking faster and more efficient. An index is a mapping table that contains keys describing certain records and pointers to those records. Each record (row) of an index table contains a key that is made up of one or more fields from the associated data table, and contains a record number / row ID, which points at the corresponding record (row) in the associated data table.

To build a key, you need to specify where to find, and how to treat, each data field that comprises the key. For example if you had a "customer" table that contained (among other things) a "Customer Number" and a "Last Name" field / column, an index could be built whose key values consisted of the Customer Number followed by the customer’s Last Name. Example keys would be "1000Johnson", "1001Smith", "1002Aadams", etc.

In this example, our index would contain two segments, one which specifies that the first part of each key comes from the "Customer Number" column of the associated table, and the other which specifies that the last part of each key comes from the "Last Name" column of the associated table.

Since the power of an index comes from its key, it is important that an index key be designed with care. The index keys are always stored in the index in order, so the aforementioned segments would sort the data table by customer number and then by last name. If the two segments had been added to the index in the other order, the index would sort the table by last name and then customer number, since the keys would look like this: "Aadams1002", "Johnson1000", "Smith1001", etc.

Each index needs at least one segment, or there would be no way to generate that index’s keys. The FairCom DB API programmer only needs to specify the index segments. After that, the index is automatically maintained – adding and removing records from the associated data table automatically adds and removes rows from the index. That way, the index always has the same number of rows as the associated table. Changing / updating a row in the table automatically updates the associated row in the index, if needed.

Indexes are added to the table definition in the order they are declared. The FairCom DB API API also includes a set of functions that will allow an index to be deleted from the table index definition.

ctdbAddIndex() will add a new index at the end of the table index definition. For each index added to the table, one or more index segments should also be added to the index to define which field combination form a particular index. ctdbAddSegment(), ctdbAddSegmentByName(), and ctdbAddSegmentByNbr() accomplish the task of adding segments to an index.

/* allocate a new table handle */

CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* add two fields to the table record definition */

ctdbAddField(hTable, "Field0", CT_INTEGER, 4);

ctdbAddField(hTable, "Field1", CT_CHAR, 30);

/* add index 0 - the first index */

ctdbAddIndex(hTable, "MyIndex1", CTINDEX_FIXED, YES, NO);

/* add index 0 segments */

ctdbAddSegmentByName(hTable, 0, "Field0", CTSEG_SCHSEG);

/* add index 1 - the second index */

ctdbAddIndex(hTable, "MyIndex2", CTINDEX_FIXED, NO, NO);

/* add index 1 segments */

ctdbAddSegmentByName(hTable, 1, "Field1", CTSEG_SCHSEG);

ctdbAddSegmentByName(hTable, 1, "Field0", CTSEG_SCHSEG);

/* create the table */

ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

ctdbAddIndex() takes a table handle, index name, index type, and two Boolean flags indicating if the index accepts duplicate keys and if the index should process null keys. The valid index types are listed in Index Key Types later in this guide.

Example

The add and insert segment functions require a segment mode as the last parameter. Refer to Segment Modes, which describes the valid segment modes.

When you create a new index you can set the index up for keys that are always unique, or you can allow duplicate keys.

An index can be deleted from the table index definition by calling ctdbDelIndex().