Working with Tables

A database may contain multiple tables, and a table may belong to multiple databases. A table can contain zero or more records (rows). Each record is made up of one or more fields. A record is essentially an entry row in a table.

A table can have zero or more indexes associated with it, which enhance the retrieval of records from that table and allow the table to be sorted in various ways. Indexes typically have one or more segments, which describe the index key structure and how the index works.

Tables created using the FairCom DB API interface are kept as files with the extension .dat.

Indices are stored in separate files with the extension .idx.

Indexes are stored in separate files with the extension .idx.

Tables, records, fields, and indexes are all manipulated programmatically with handles. The general process to create and use a new FairCom DB API table and its associated indexes is as follows:

• Allocate table handle
• Add or insert fields
• Add indexes
• Add index segments
• Create the table
• Open the table
• Operate on records

Before you can work with an existing table, the table must be opened as follows:

• Allocate table handle
• Open the table
• Operate on records

With FairCom DB API, it is possible to modify an existing table. The general process to modify a table after it has been created is as follows:

• Open the table
• Make changes to:
  • Fields and/or
  • Indexes and/or
  • Segments
• Call the alter table function: ctdbAlterTable().

More details on this process are described in Altering a table.

In general, the table creation is done once in the application, in a Create() function. Then the table is opened and the data is added (records inserted into the table) in a separate routine.

During table creation, besides the table layer itself, three FairCom DB API APIs will be directly involved: the fields, indexes and segments. These APIs are only directly involved when the table is being created, or in the event the table structure is modified. When records are being added to or searched in the table, these APIs are not relevant, but the record layer is crucial.

With this in mind, this section on Tables discusses these layers in the appropriate order:

• Table creation using fields, indexes, and segments.
• Table parameters and properties.
• Opening and manipulating tables.

Allocating a Table Handle

A table handle is required before most table operations can take place. A table handle is allocated with ctdbAllocTable() and freed with ctdbFreeTable().

When allocating a table handle you need to provide a handle to a database so the new table handle can be properly associated with that database. The database handle must have been previously allocated by calling ctdbAllocDatabase(), but need not be connected.

CTHANDLE hSession = ctdbAllocSession(CTSESSION_CTDB);
CTHANDLE hDatabase = ctdbAllocDatabase(hSession);
CTHANDLE hTable = ctdbAllocTable(hDatabase);
if (!hSession || !hDatabase || !hTable) {
   printf("Session, database or table handle allocation failed\n");
   exit(1);
}

When the table handle is no longer needed it should be released by calling ctdbFreeTable(). A table handle must be allocated and released by ctdbAllocTable() and ctdbFreeTable().

Do not use the C runtime library functions malloc() and free() on table handles (or any FairCom DB handles).

If you release an active table's handle (the handle of a table that is currently open), ctdbFreeTable() automatically closes the table and resets all of the record buffers in the record handles that have been associated with the table.

Allocating a Table Handle without Database Support

It is possible to create or open a table without database support by providing the session handle when allocating the table handle. Here is an example without error checking:

CTHANDLE hSession;
CTHANDLE hTable;

/* Allocate a new session handle */
hSession = ctdbAllocSession(CTSESSION_CTREE);

/* Logon to session */
ctdbLogon(hSession, "FAIRCOMS", "ADMIN", "ADMIN");

/* Allocate a new table handle, note the use of a session handle, not a database handle */
hTable = ctdbAllocTable(hSession);

/* Override the default path where FairCom DB API will look for the table files. */
ctdbSetTablePath(hTable, "c:\\MyDocuments");

/* Open the table */
ctdbOpenTable(hTable, "mytable", CTOPEN_NORMAL);

Note that FairCom DB API will try to open the table files from the server directory (client/server applications) or from the execution directory (stand-alone applications). If you wish to override this default location, call ctdbSetTablePath() to specify your own directory before you call ctdbOpenTable(). The same "default directory" principle applies when creating a table without database support:

CTHANDLE hSession;
CTHANDLE hTable;

/* Allocate a new session handle without session dictionary support*/
hSession = ctdbAllocSession(CTSESSION_CTREE);

/* Logon to session */
ctdbLogon(hSession, "FAIRCOMS", "ADMIN", "ADMIN");

/* Allocate a new table handle, note the use of a session handle, not a database handle */
hTable = ctdbAllocTable(hSession);

/* add fields to table */
ctdbAddField(hTable, "Field0", CT_INT2, 2);
ctdbAddField(hTable, "Field1", CT_FSTRING, 30);

/* Override the default path where FairCom DB API will write the table files. */
ctdbSetTablePath(hTable, "c:\\MyDocuments");

/* Create the table in the directory specified above */
ctdbCreateTable(hTable, "mytable", CTCREATE_NORMAL);

Creating a New Table

Creating a new table may be one of the most crucial operations performed by a database developer or administrator. The FairCom DB API API offers a powerful, yet easy-to-use, mechanism for creating tables.

The create table process does not leave the table open after the table is created. The user must explicitly open the table to be able to add data records and query any of the table properties.

The create table process involves the following steps:

• Allocate a new table handle
• Add, insert, or delete fields
• Optionally, add, insert, or delete indexes
• Change any of the default table properties
• Create the table

Adding, inserting or deleting fields

Fields are what hold the actual record data for each row in a table. Whereas a record could be considered a "row" in a table, a field could be considered a "column" in a table, or a "cell" in a record. The fields are defined at the time of the table creation.

Fields are added to the record definition in the order they are declared. In other words, the first field added is field number zero, the second added is field number one, etc. The FairCom DB API API also includes a set of functions that will allow a field to be inserted at a certain place in the records definition and fields can also be deleted from the record's definition.

Each field will have a field type (e.g., CT_INTEGER and CT_CHAR in the example below). For more information, see Field Types.

ctdbAddField() adds a new field to the end of the record definition.

/* 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);
/* create the table */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

ctdbInsField() and ctdbInsFieldByName() each insert a new field before a previously-defined field. Use ctdbInsField() to specify the field index by its position in the record definition. The first field is number 0, the second field is number 1 and so on. Use ctdbInsFieldByName() to specify the field index by its name.

/* allocate a new table handle */
CTHANDLE hTable = ctdbAllocTable(hDatabase);
/* add two fields to the table record definition */
ctdbAddField(hTable, "FirstField", CT_INTEGER, 4);  /* This is field #0 */
ctdbAddField(hTable, "SecondField", CT_CHAR, 30);   /* This is field #1 (for now!) */
/* insert a field into the location previously occupied by field "SecondField" */
ctdbInsFieldByName(hTable, "SecondField", "ThirdField", CT_BOOL, 1);
/* create the table */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

The fields in the table are now as follows:

• Field #0="FirstField"
• Field #1="ThirdField"
• Field #2="SecondField"

ctdbDelField() and ctdbDelFieldByName() delete a field from the record definition. Use ctdbDelField() to delete a field specifying the field number. The first field is number 0, the second field is number 1, and so on. Use ctdbDelFieldByName() to delete a field specifying the field name. Note that any fields with higher field numbers than the one you deleted will have one subtracted from their field numbers to fill the gap left by the field you deleted.

/* 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);
ctdbDelFieldByName(hTable, "Field1");
/* create the table with just one field ("Field0") */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

Fixed or variable-length records

The FairCom DB API API automatically, and transparently, handles the details of fixed and variable-length records. A table is set to variable-length if it has at least one variable-length field. A fixed-length field always consumes the same amount of disk space in a table file, regardless of what is actually stored in that field. A variable-length field, on the other hand, consumes an amount of disk space that varies with the field's contents.

Variable-length fields require more housekeeping than fixed-length fields. Fortunately, FairCom DB API does this for you. FairCom DB API scans the user field definitions until it encounters the first variable-length field. If a table contains no variable-length fields, the record is set to fixed-length.

FairCom DB API also automatically calculates the size of the fixed portion of a record by adding the size of the fixed-length fields, taking into consideration the field alignment in the record buffer, until the first variable-length field is encountered. The variable-length fields are listed below, with the matching FairCom DB ISAM data type in parentheses:

CT_PSTRING (CT_PSTRING)
CT_VARBINARY (CT_2STRING)
CT_LVB (CT_4STRING)
CT_VARCHAR or CT_LVC (CT_STRING)

Any type of field can be placed anywhere in the record buffer and also be used as an index segment. FairCom DB API makes full use of this feature by providing the user with an advanced dynamic record buffer management.

Hidden fields

Three special fields are automatically included by FairCom DB API in the table record definition. FairCom DB SQL makes extensive use of the null flag and ROWID fields:

• The delete flag ($DELFLD$)
• The null flag ($NULFLD$)
• The ROWID field ($ROWID$)

These fields are transparently and automatically handled by the FairCom DB API and can't be handled directly by the field-handling functions of the FairCom DB API. There are specific functions that will, in some cases, retrieve data and status information kept by the hidden fields. These fields are also optional and FairCom DB API will operate correctly with tables that do not possess them. There are also table creation modes allowing developers to create FairCom DB API tables without any, or all, of the hidden fields.

The delete flag field is for internal deletion purposes and should not be modified. $DELFLD$ is a CT_ARRAY field of four bytes. The only purpose of this field is to keep a place at the beginning of the record to hold a c-tree delete flag byte when the record is deleted. Four bytes are used instead of just one byte due to alignment requirements. This is an internal FairCom DB API requirement, and should not be modified or touched by the user.

$NULFLD$ is a CT_ARRAY field with the size based on the number of user defined fields for the table. For each user-defined field, one bit is reserved in $NULFLD$. The $NULFLD$ field keeps the NULL flag information for each user-defined field. If a user field is null, the corresponding bit in $NULFLD$ will be set. When data is written to the field, the corresponding bit is cleared. The user should never modify or verify this field directly; always use the appropriate API functions:

• ctdbIsNullField() returns a field's NULL flag. This is for a single field in a single record/row.
• ctdbClearField() and ctdbClearRecord() set the NULL flag of a single field / entire record, respectively.
• To clear the NULL flag of a single field or entire record, write to that field / record.
• Do not use ctdbGetFieldNullFlag() and ctdbSetFieldNullFlag() to interact with the NULL flag discussed here. These two functions are for a column's SQL "NOT NULL" property in the SQL layer.

$ROWID$ is a CT_INT8 (64-bit integer) field holding the auto-increment value generated by c-tree every time a new record is added to the table. This field is a read-only field that acts as a unique record identifier. Retrieve the ROWID using ctdbGetRowid(), or retrieve the record handle given its ROWID using ctdbFindRowid(). To know if a table has support for ROWID , use ctdbHasRowid().

$ROWID$ is used by FairCom DB SQL as a unique record identifier. For ISAM files or FairCom DB API tables created with CTCREATE_NOWROWID flag, the $ROWID$ field will not exist. In this case the RECBYT offset will be used instead.

Note: Record offsets may change for a given variable-length record when a record is updated to a larger record size than the original record. Therefore, the RECBYT index cannot be used as a unique record identifier.

By default, FairCom DB API creates these three hidden fields. Tables created with the FairCom DB ISAM and Low-level APIs will not include these fields by default. FairCom DB API does not require the fields to operate, but they allow more advanced capabilities. When creating a new table, disable the inclusion of the hidden fields using the create modes CTCREATE_NONULFLD, CTCREATE_NODELFLD, and CTCREATE_NOROWID.

The default table layout is presented below. Note: The first field added by the user is always field 0.

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().

/* 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);
/* delete index 1 */
ctdbDelIndex(hTable, 1);
/* create the table */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

Index segment modes

Each index segment has a "mode", which controls how that segment’s part of the index key is generated from the specified field / column of the associated table.

The segment modes are listed in Segment modes.

Preferred Segment Modes

Some of the segment modes are "preferred" because they maintain maximum compatibility and functionality.

These segment modes are based on the absolute field number in the data table (also known as schema fields). They use the entire field, rather than just part of a field. The preferred segment modes are:

• CTSEG_SCHSEG
• CTSEG_USCHSEG
• CTSEG_VSCHSEG
• CTSEG_UVSCHSEG
• CTSEG_SCHSRL

Using the preferred segment modes makes FairCom DB API-based tables fully compatible with ISAM/Low-Level applications and/or FairCom DB SQL applications.

Sort Order

By default, the preferred segment modes sort in ascending order. This can be changed to descending sort order by OR’ing CTSEG_DESCENDING with the segment mode. If a custom sort order is desired, OR the segment mode with CTSEG_ALTSEG instead.

SCHSEG and Its Variants

When key segment mode SCHSEG or any of its variants (USCHSEG, VSCHSEG, or UVSCHSEG) is used, the interpretation of the data is based on the underlying type found in the schema.

The use of VSCHSEG instead of SCHSEG is rather subtle:

If a fixed-length field has a field delimiter or a length count, specifying VSCHSEG indicates that if the actual data is shorter than the segment length, pad the contents out to the full segment length. The default padding is an ASCII space character, but the ctdbSetPadChar() function can be used to change the padding and field delimiter characters for fixed-length string fields.

If SCHSEG is used instead, then delimiters or length counts will not be used to determine how to pad the key segment. Whatever is in the fixed-length field will be used; even if it causes "garbage" to be used beyond the actual contents. Of course, if a fixed-length field is carefully pre-filled or padded, then the use of SCHSEG will not cause any problem.

You may OR in the mode CTSEG_DESCENDING to the segment mode to specify the descending sort order for a segment. You can also OR in the segment mode CTSEG_ALTSEG to specify an alternate collating sequence for the segment. Both of these modifications can be used at the same time, and apply to the individual segment, and not to the entire key.

Key segment mode SCHSEG is used when the segment links to an auto-increment field / column in the data table.

ROWID index

Two indexes are created by default during the table creation: the ROWID and RECBYT indexes.

The ROWID index holds the auto-increment value generated automatically by c-tree every time a new record is added to the table.

When a table is created with ROWID support, a ROWID index is automatically created for the table. The operation of the ROWID index is transparent to the FairCom DB API user. FairCom DB API will automatically update the index entries. The ROWID index will not appear in the list of indexes for a table. The user cannot alter or delete the components of the ROWID index.

Functions that deal with the ROWID index are:

• ctdbHasRowid() - to determine if the table has ROWID support;
• ctdbGetRowid() - to retrieve the ROWID for a given record;
• ctdbFindRowid() - to retrieve the record, given the ROWID.

By default, all FairCom DB API tables are created with support for the ROWID index. To create a table without ROWID support, the CTCREATE_NOROWID should be OR-ed into the create mode parameter to the ctdbCreateTable() function.

RECBYT index

RECBYT indexes were introduced in the FairCom DB ISAM API to provide improved space management for variable-length records in a table and to permit backward physical traversal of data files that contain resources and variable length records. A RECBYT index is based on the byte offset (recbyt) of the record being indexed.

RECBYT indexes are optional and the user can disable their creation by specifying the CTCREATE_NORECBYT when the table is created.

When a table is created with RECBYT index support, a RECBYT index is automatically created for the table. The operations on the RECBYT indexes are transparent to the user except for an added performance overhead. FairCom DB API will automatically update the index entries. The RECBYT index will not appear in the list of indexes for a table. The user cannot alter or delete the components of the RECBYT index. The use of RECBYT index has no impact on the fields in the record buffer of a table. There is no field associated with this index.

Note: The RECBYT index enables backward physical traversal of variable-length records. By default, the FairCom DB API API creates this index on every table. Because it is an extra index, it slows inserts, updates, and deletes. Unless you need to walk variable-length records backwards, disable the creation of the index by adding NORECBYT to the table create mode.

FairCom DB API API Compression

New index node types provide options for reducing index size. [01/2024]The new NAV (FairCom DB API) index modes are as follows:

• Variable-Length Keys with Compression: CTINDEX_VARIABLE index mode provides variable length keys with padding compression to squeeze more capacity into each page block. Recommended for indexes over variable length data.
• RLE Compression: CTINDEX_VARIABLE_RLE index mode uses variable length keys with a simple RLE key compression of bytes 0x0, 0x20, 0x30 resulting in shorter index entries and smaller index files. Recommended for indexes over variable length data with multiple segments, binary data, or large numeric data types (8 byte integers).

Example

The following example demonstrates how to use one of these modes:

pIndex = ctdbAddIndex(hTableCustMast, "cm_custnumb_idx", CTINDEX_VARIABLE_RLE, NO, NO);

For more information, see ctdbAddIndex in the FairCom DB API documentation.

Changing default properties

When a table handle is allocated by ctdbAllocTable(), the table properties are set to default values. Developers using the FairCom DB API API may need to change the default value of the table properties to suit the design requirements of their applications.

The following table properties may be changed after allocating the table handle, but before creating the table with ctdbCreateTable():

• Path
• Data file extension
• Index file extension
• Password
• Group ID
• Permission Mask
• Default Data Extent Size
• Default Index Extent Size
• Field padding

Refer to Table Properties for more information on the table properties.

Creating the table

After all fields and indexes have been defined and the table properties set, it is time to create the table by calling ctdbCreateTable(). ctdbCreateTable() takes the table handle used to define fields and indexes, and to set the table properties, the table name and the create mode.

/* 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);

The table create modes are a combination of the following valid modes. You can specify more than one create mode by OR-ing the following constants together.

For a list of modes for creating a table, see Table Create Modes.

Note that if you wish to use transaction processing (the ctdbBegin(), ctdbCommit(), and ctdbAbort() functions) to ensure atomicity, then it is important to create the table using the CTCREATE_PREIMG or the CTCREATE_TRNLOG mode. If you think your table might get big (larger than 2 gigabytes), be sure to OR in CTCREATE_HUGEFILE.

Creating a table under transaction control

You can add an extra level of data integrity when creating a new table by placing the code to create the table inside a transaction. If the transaction is aborted, the table entry in the database dictionary file is removed, and the table data and index files are deleted from disk.

When a table is created inside a transaction, and until the transaction is committed or aborted, the newly-created table must be opened with CTOPEN_EXCLUSIVE mode, otherwise the table open operation will fail. After the transaction is committed, the table can be opened in non-exclusive mode.

The code fragment below creates a new table under transaction control. Again no error checking code is included in the example:

/* allocate a new table handle */
hTable = ctdbAllocTable(hDatabase);

/* begin a transaction */
ctdbBegin(hTable);

/* add a field
ctdbAddField(hTable, "Field0", CT_INTEGER, 4);

/* add another field */
ctdbAddField(hTable, "Field1", CT_CHAR, 30);

/* create the table */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

/* commit the transaction */
ctdbCommit(hTable);

Note: If you open a table that was created inside the current transaction (that is, the transaction has not yet been committed or aborted), you must open the table in exclusive mode by specifying the CTOPEN_EXCLUSIVE mode to ctdbOpenTable().

Opening a table

A table must be opened before any data operations within it can take place. Use ctdbOpenTable() to open a table.

/* open a table */
if (ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL) != CTDBRET_OK)
   printf("Open table failed\n");

After opening the table, usual operations like add, update, delete, and search for records can be done. Record operations are described in detail in Working with Records.

ctdbOpenTable() takes as parameters a newly allocated table handle, the table name, and the table open mode.

For a list of modes for opening a table, see Table Open Modes.

Opening a table with password

If a table was created with a password, every time that table is opened, you need to specify the correct password for the open table operation to succeed. After the table handle is allocated, but before the table is opened, the table password property must be set.

/* opening a table with password */
CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* set the table password */
ctdbSetTablePassword(hTable, "MyPassword");

/* open the table */
if (ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL) != CTDBRET_OK)
   printf("Open table failed\n");

Closing a table

When the handle to an open table is no longer needed, the table should be closed, to allow all FairCom DB API, FairCom DB and operating systems buffers to be flushed to disk. It is very good programming practice to always close every open table before the process or thread is terminated.

/* close the table */
if (ctdbCloseTable(hTable) != CTDBRET_OK)
   printf("Close table failed\n");

Altering a Table

The FairCom DB API alter table function allows the modification of table, field and index properties after a table has been created, and possibly already populated with data.

The usual steps to perform an alter table operation are:

• Add, insert, delete or edit fields
• Add, edit or delete indexes
• Alter the table by calling ctdbAlterTable()

Add, insert, delete, or edit fields

By calling one of the following edit management functions, the table definition is marked as modified. For the changes to be reflected on the data and index files, you must then call ctdbAlterTable().

To add a field to the end of a table call ctdbAddField().

To insert a field into the middle of a table, call ctdbInsField() / ctdbInsFieldByName(), specifying the insert location by field number / name.

To delete an existing field from a table, call ctdbDelField() / ctdbDelFieldByName(), specifying the field to delete by field number / name.

To edit the field properties, call ctdbSetFieldName(), ctdbSetFieldLength(), ctdbSetFieldType(), b, ctdbSetFieldScale(), and ctdbSetFieldNullFlag().

Most changes relating to fields will trigger ctdbAlterTable() to perform a full table rebuild.

Add, edit or delete indexes

By calling one of the following index management functions, the table definition is marked as modified and for the changes to be reflected on the data and index files, you must then call ctdbAlterTable().

To add or delete an index from a table, call ctdbAddIndex() or ctdbDelIndex().

To edit index properties, call ctdbSetIndexKeyType(), ctdbSetIndexEmptyChar(), ctdbSetIndexDuplicateFlag(), ctdbSetIndexNullFlag(), and ctdbSetIndexTemporaryFlag().

To add, insert or delete index segments from an index call ctdbAddSegment(), ctdbInsSegment(), ctdbDelSegment(), or any of its variations.

Most changes relating to indexes will cause ctdbAlterTable() to perform only an index rebuild. If only one index is affected, ctdbAlterTable() only rebuilds the affected index. If changes affect more than one index, ctdbAlterTable() may rebuild all indexes.

After a successful alter table, all records associated with the altered table will automatically re-initialize to reflect any new table field and index definitions.

Alter the table

ctdbAlterTable() scans the table, field, index, and segment structures to decide which changes need to be made and how to do it. At the very least, it may only update the table DODA if the only change done was, for example, in field types that are compatible with each other: changing from types CT_INT4 and CT_INT4U. Then, if the only changes occurred in a single index: a single index was added or deleted or the index properties changed, only that index is rebuilt. If more than one index changed, or more than one index was added or deleted, then it may be necessary to rebuild all indexes of the table. If fields were added, deleted, inserted, or the changes in the field property were not compatible with each other, then Alter needs to perform a full rebuild of the table.

A table is rebuilt by creating a temporary table with the correct current properties taking into consideration all changes. All records are read from the original table and written into the temporary table. Once all data records have been moved, the original table is deleted and the temporary table is renamed with the name of the original table.

/* add one field to the table and rebuild it */
CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* open the table */
ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL);

/* add one field to the table */
ctdbAddField(hTable, "Wages", CT_CURRENCY, 8);

/* alter the table */
if (ctdbAlterTable(hTable, CTDB_ALTER_NORMAL) != CTDBRET_OK)
   printf("Alter table failed\n");

ctdbAlterTable() takes as parameters an active table handle and an alter table action:

Default Values

FairCom DB API’s alter table function can be used to alter the schema of an existing table by adding new fields or modifying existing fields of the specified table. During an alter table operation, when a new field is added to the table, or when an existing field type is changed, an optional default field value can be specified for these fields.

The default value of a field is used during an alter table operation when a full table rebuild is performed. During a full alter table rebuild, and after the old record buffer data is moved to the new record buffer, the new record buffer is scanned and, if a NULL field is found and that NULL field has a default value, the default value is copied to the field buffer. Typically the default field value is applied for new fields added to the table and to existing fields that have their types changed and the field value is NULL.

The field default value is kept as a string representation of the data. It is recommended that numeric data should be converted to string using one of the rich set of FairCom DB API data conversion functions. Binary data can also be used by passing the pointer to data and the appropriate length.

The default value is set by calling the ctdbSetFieldDefaultValue() function.

Example

/* set the default value for country - field #5 */
hField = ctdbGetField(hTable, 5);
if (hField)
     if (ctdbSetFieldDefaultValue(hField, "USA", 3) != CTDBRET_OK)
          printf("ctdbSetFieldDefaultValue failed\n");

Use ctdbGetDefaultfieldValue() to retrieve the current field default value.

Example

/* check if default field value is 'USA' */
hField = ctdbGetField(hTable, 5);
if (hField)
{
    VRLEN len;
    pTEXT value = ctdbGetFieldDefaultValue(hField, &len);

    if (value)
    {
        if (strcmp(value, "USA") == 0)
            printf("Default value is 'USA'\n");
        else
            printf("Default value is not 'USA'\n");
    }
    else
        printf("No default value set\n");
}

You can check if a default value is set by calling the ctdbIsFieldDefaultValueSet() function.

Example

/* check if default field value is set */
hField = ctdbGetField(hTable, 5);
if (ctdbIsFieldDefaultValueSet(hField))
    printf("Default field value is set\n");
else
   printf("No default field value\n");

Once set, a default field value will remain in place until the table handle is closed. The ctdbClearFieldDefaultValue() function clears the default value associated with a field. The default date and time types are also reset to their default values of CTDATE_MDCY and CTTIME_HMS respectively.

Example

/* clear the default field value */
hField = ctdbGetField(hTable, 5);
if (hField)
    if (ctdbClearField(hField) != CTDBRET_OK)
        printf("ctdbClearField failed\n");

You can clear the default values for all fields in a table by calling the ctdbClearAllFieldDefaultValue() function.

Example

/* clear all default field values *.
if (ctdbClearAllFieldDefaultValue(hTable) != CTDBRET_OK)
    printf("ctdbClearAllFieldDefaultValue failed\n");

The default date and time types used for conversions to and from strings can be changed by calling the ctdbSetFieldDefaultDateTimeType() function.

When setting the default field values with date, time or timestamp data, the data must be first converted to string. By default the date type is CTDATE_MDCY while the default time type is CTTIME_HMS.

The possible date formats for string conversion are:

Time Types can be one of the following string time formats:

Example

/* set the field default date and time types */
hField = ctdbGetField(hTable, 5);
if (hField)
    if (ctdbSetFieldDefaultDateTimeType(hField, CTDATE_DMY, CTIME_HMP))
        printf("ctdbSetFieldDefaultDateTimeType failed\n");

The default date type value can be retrieved by calling the ctdbGetFieldDefaultDateType() function.

Example

/* check the default date type */
hField = ctdbGetField(hTable, 5);
if (ctdbGetFieldDefaultDateType(hField) != CTDATE_MDCY)
	printf("Default date type is not OK\n");

The default time type value can be retrieved by calling the ctdbGetFieldDefaultTimeType() function.

Example

/* check the default time type */
hField = ctdbGetField(hTable, 5);
if (ctdbGetFieldDefaultTimeType(hField) != CTDBRET_OK)
	printf("Default time type is not OK\n");

Adding an index to a table

To add one or more indexes to an existing table, perform the following steps:

• Add the index with ctdbAddIndex(). Repeat this step for each new index.
• Add, insert, or delete index segments with ctdbAddSegment(), ctdbInsSegment(), ctdbDelSegment(), or any of their variations. Repeat this step for each segment of the index.
• Call ctdbAlterTable() to add the new index

/* add new index to table */
CTHANDLE hTable = ctdbAllocTable(hTable);
CTHANDLE hIndex;
CTHANDLE hField;

/* open the table */
ctdbOpenTable(hTable "MyTable", CTOPEN_ NORMAL);

/* add the new index */
hIndex = ctdbAddIndex(hTable, "MyNewIndex", CTINDEX_FIXED, YES, NO);

/* get the field handle to be used as the index segment */
hField = ctdbGetFieldByName(hTable, "Field0");

/* add new index segments */
ctdbAddSegment(hIndex, hField, CTSEG_SCHSEG);

/* alter the table to commit index changes to disk */
if (ctdbAlterTable(hTable, CTDB_ALTER_NORMAL) != CTDBRET_OK)
   printf("Add index failed\n");

Deleting an index from a table

To delete one or more indexes from a table, perform the following steps:

• Delete the index with ctdbDelIndex(). There is no need to delete the index segments. Repeat this step for each index you want to delete.
• Call ctdbAlterTable() to delete the index from the table.

/* delete the first index */
CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* open the table */
ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL);

/* delete the first index - index 0 */
ctdbDelIndex(hTable, 0);

/* alter the table */
if (ctdbAlterTable(hTable, CTDB_ALTER_NORMAL) != CTDBRET_OK)
   printf("Delete index failed\n");

Forcing an index rebuild

There may be situations where you may need to build the indexes of a table. Use the CTDB_ALTER_INDEX action parameter of ctdbAlterTable() to force the rebuild of all indexes of a table. When CTDB_ALTER_INDEX is specified, ctdbAlterTable() rebuilds all indexes of a table regardless of any changes to the table specification.

/* rebuild all indexes */
CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* open the table */
ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL);

/* rebuild all indexes */
if (ctdbAlterTable(hTable, CTDB_ALTER_INDEX) != CTDBRET_OK)
   printf("Index rebuild failed\n");

Forcing a table rebuild

There may be situations where you may need to force a full table rebuild. In a full table rebuild a temporary table is created based on the properties of the original table, then all records are read from the original table and written into the temporary table. All indexes are also rebuilt. Once all data records have been moved, the original table is deleted and the temporary table is renamed with the name of the original table.

/* rebuild a table */
CTHANDLE hTable = ctdbAllocTable(hDatabase);

/* open the table */
ctdbOpenTable(hTable, "MyTable", CTOPEN_NORMAL);

/* rebuild the table */
if (ctdbAlterTable(hTable, CTDB_ALTER_FULL) != CTDBRET_OK)
   printf("Table rebuild failed\n");

Attach and Detach Open Tables

A FairCom DB API table handle or object can be attached and detached to an already open data file. Applications may need to open a table using c-tree ISAM and low level functions and then attach the table to a FairCom DB API table handle to take advantage of full FairCom DB API functionality.

ctdbAttachTable() attaches a FairCom DB ISAM datno object to a FairCom DB API table handle. This function is useful if you have opened a data or index file using one of c- tree’s ISAM open functions and need to attach it to a table handle to use some of the advanced FairCom DB API features such as alter table or the record handler. ctdbAttachTable() returns CTDBRET_OK on success.

ctdbAttachTableXtd() attaches a FairCom DB ISAM datno object to a FairCom DB API table handle. This function is useful if you have opened a data and index file using one of c-tree’s ISAM open functions and need to attach it to a table handle to use some of the advanced FairCom DB API features such as alter table or the record handler. This extended version allows the user to specify the DODA and IFIL for the table, enabling tables without DODA and/or IFIL to be attached to FairCom DB API.

ctdbDetachTable() detaches a FairCom DB API table handle from a c-tree data and index files. The table is not closed but the FairCom DB API table handle resources are released and the handle re-initialized.

Example

CTHANDLE hSession = ctdbAllocSession(CTSESSION_CTREE);
CTHANDLE hTable = ctdbAllocTable(hSession);
CTHANDLE hRecord = ctdbAllocRecord(hTable);
NINT datno, count = 0;

/* logon to c-tree */
ctdbLogon(hSession, SERVER, USER, PASSWD);

/* open the file using c-tree ISAM */
datno = (NINT)OPNRFILX((COUNT) -1, "test309.dat", (COUNT)0, NULL);

/* attach to table */
ctdbAttachTable(hTable, datno);

/* read the records */
if (ctdbFirstRecord(hRecord) == CTDBRET_OK)
do
{
	count++;
}
while (ctdbNextRecord(hRecord) == CTDBRET_OK);

/* cleanup */
ctdbDetachtable(hTable);
ctdbFreeRecord(hRecord);
ctdbFreeTable(hTable);
ctdbLogout(hSession);
ctdbFreeSession(hSession);

Table Properties

FairCom DB API tables have a number of properties that can be set when the table is created or with "set" and "get" functions.

Table Name

The table name is a read-only property that can't be changed once the table is created. Use ctdbGetTableName() to retrieve the table name.

Table Path

The table path property is set to NULL by default. If this property is not changed prior to calling ctdbCreateTable(), the table is created in the same directory as the database. Call ctdbSetTablePath() to change the table path property. ctdbGetTablePath() retrieves the current table path.

Table File Extension

The table data file extension defaults to “.dat”. The default data file extension can be changed with ctdbSetTableExtension(). ctdbGetTableExtension() retrieves the current data file extension for the table.

Index File Extension

The table index file extension defaults to “.idx”. The default index file extension can be changed with ctdbSetIndexExtension(). ctdbGetIndexExtension() retrieves the current index file extension for the table.

Data File Extent Size

The data file extent is the default size by which the data file is extended when necessary. This value is 0 bytes by default. If there is a need to change it, use ctdbSetTableDefaultDataExtentSize(). To retrieve the data default extent size, use ctdbGetTableDefaultDataExtentSize().

Index File Extent Size

The index file extent is the default size by which the index file is extended when necessary. This value is 0 byte by default. If there is a need to change it, use ctdbSetTableDefaultIndexExtentSize(). To retrieve the index default extent size, use ctdbGetTableDefaultIndexExtentSize().

Table Password

By default tables do not have a password (i.e.. the password property is set to NULL). If a table is to be created with a password, change this property with a call to ctdbSetTablePassword() before creating the table with a call to ctdbCreateTable(). Use ctdbSetTablePassword() to set the password and ctdbGetTablePassword() to retrieve it.

Table Group ID

The group ID can be used to manage table permissions for multiple users. By default, no group ID settings are specified for FairCom DB API tables (i.e., the default group ID value is NULL). If a table is to be created with a group ID, set the value of this property before creating the table with a call to ctdbCreateTable(). To set a group ID, use ctdbSetTableGroupid() and ctdbGetTableGroupid() to retrieve it.

Table Permission Mask

The permission mask is set at file creation and specifies a permission mask that determines the kind of access that users may acquire on subsequent opens. For more information, see Table Permission Mask.

Number of Fields

The “number of fields” property indicates the total number of fields defined for a table. The value takes into consideration only user defined fields and does not include any hidden fields that exist for the table. Use ctdbGetTableFieldCount() to retrieve the number of fields associated with a table.

Number of Indexes

The “number of indexes” property indicates the total number of indexes defined for a table. The value takes into consideration only user defined indexes and does not include the RECBYT or ROWID indexes. Use ctdbGetTableIndexCount() to retrieve the number of indexes associated with a table.

Field Padding

The field padding property sets the table pad and field delimiter characters FairCom DB API uses to pad fixed-length string fields. For more information, see Field Padding.

Update Create Mode

The update table create mode property changes the table create mode after the table has been created. For more information, see Update Create Mode.

Update Create Mode

Use the update table create mode property to change the table create mode after the table has been created. Use ctdbUpdateCreateMode() to change the table create mode. You can only update the create mode if the table was opened in exclusive mode.

/* update the table create mode */
if (ctdbUpdateCreateMode(hTable, CTCREATE_TRNLOG) != CTDBRET_OK)
   printf("Update create mode failed\n');

ctdbUpdateCreateMode() changes critical file mode attributes such as the level of transaction control. No check is made to determine if the mode change will damage data. No check is made if the new mode is valid. Use this function with caution as data may be lost. For instance, changing a data file from transaction processing to no transaction processing makes automatic recovery unavailable.

The mode parameter passed to ctdbUpdateCreateMode() represents the new table create mode. It must be perfectly formed, as it will replace the current table create mode. Use ctdbGetTableCreateMode() to retrieve the current create mode and apply the changes on a fully qualified create mode. Update only the following create table modes:

• CTCREATE_PREIMG
• CTCREATE_TRNLOG
• CTCREATE_WRITETHRU
• CTCREATE_CHECKLOCK
• CTCREATE_CHECKREAD
• CTCREATE_HUGEFILE

Table Permission Mask

The permission mask is set at file creation and specifies a permission mask that determines the kind of access that users may acquire on subsequent opens. The mask is comprised of three components: owner permissions, group permissions and world permissions. With this structure, you are able to allow different users different levels of access to the file. The default permission mask is set to (OPF_ALL | GPF_READ | GPF_WRITE | WPF_READ | WPF_WRITE). To set the table permissions, use ctdbSetTablePermission(). To retrieve the values, use ctdbGetTablePermission().

Valid permission mask values are:

Field Padding

By default the FairCom DB API API pads fixed-length string fields CT_CHARS (CT_FSTRING), CT_FPSTRING, CT_F2STRING, and CT_F4STRING with NULL ('\0') bytes. The field padding property sets the table pad and field delimiter characters to allow proper target key formation. This property allows the FairCom DB API API to operate on files created using the FairCom DB ISAM and Low-Level APIs using a fixed string padding strategy that is different from the FairCom DB API API default.

Use ctdbSetPadChar() to set the pad and field delimiter characters and ctdbGetPadChar() to retrieve the current pad and field delimiter characters.

/* set the table pad and delimiter characters to spaces */
if (ctdbSetPadChar(hTable, ' ', ' ') != CTDBRET_OK)
   printf('Set pad characters failed\n");

The most common strategies for padding fixed string fields are:

• Pad with NULLs: pad character is '\0' and field delimiter is '\0'
• Pad with spaces: pad character is ' ' and field delimiter is ' '
• Pad with spaces and terminate with NULL: pad character is ' ' and field delimiter is ‘\0’