A database may contain multiple tables, and a table may belong to multiple databases. Tables created using the FairCom DB API interface are kept as files with the extension .dat. Indexes are stored in separate files with the extension .idx. The general process to create and use a FairCom DB API table and its associated indexes is as follows:

• Create a table object
• 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:

• Create a table object
• 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 a table
• Make changes to:
  • Fields and/or
  • Indexes and/or
  • Segments
• Call the alter table method CTTable::Alter().

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

In general, the table creation is done once in the application, the table is opened, and the data is added in a separate routine. During table creation, besides the table layer itself, three FairCom DB API layers will be directly involved: the fields, indexes and segments. These layers 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 layers 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.

Creating a Table object

A valid table object is required before most table operations can take place. You need to pass a valid CTDatabase object to the CTTable constructor.

// create a CTTable object
CTTable ATable(ADatabase);
try
{
// open table "MyTable"
ATable.Open("MyTable", CTOPEN_NORMAL);
}
catch (CTException &err)
{
printf("Table open failed with error %d\n", err.GetErrorCode);
}

If you create a dynamic CTTable object with the new operator, you are required to destroy the object with the delete operator.

// create a dynamic CTTable object
CTTable* pTable = new CTTable(ADatabase);

if (!pTable)
{
   printf("CTTable creation failed\n");
}  
... other operations ..
// destroy the CTTable object
delete pTable;

If you destroy an active table object, all record objects associated with the table will be reset and the table closed before the object is destroyed.

Creating a table object without database support

It is possible to create or open a table without database support by passing a session object when creating the table object. Start your session with the CTSESSION_CTREE mode.

// create the session and table objects
CTSession ASession(CTSESSION_CTREE);
CTTable ATable(ASession);

// logon to session
ASession.Logon("FAIRCOMS", "ADMIN", "ADMIN");
// without database support, it is necessary to
// specify the path were the table is located
ATable.SetPath("C:\\MyDocuments");
// open the table
try
{
   ATable.Open("MyTable", CTOPEN_NORMAL);
}
catch (CTException &err)
{
   printf("Table open failed with error %d\n, err.GetErrorCode());
}

Please note from the code above the need to specify the path where the table is located. If no path is specified, FairCom DB API will try to open the table from the current directory. The same principle applies when creating a table without database support:

// create the session and table objects
CTSession ASession(CTSESSION_CTREE);
CTTable ATable(ASession);

// logon to session
ASession.Logon("FAIRCOMS", "ADMIN", "ADMIN");
// add fields to table
ATable.AddField("Field1", CT_INT2, 2);
ATable.AddField("Field2", CT_FSTRING, 30);
// without database support, it is necessary to
// specify the path were the table is located
ATable.SetPath("C:\\MyDocuments");
// open the table
try
{
   ATable.Create("MyTable", CTCREATE_NORMAL);
}
catch (CTException &err)
{
   printf("Create open failed with error %d\n, err.GetErrorCode());
}

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. 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 record definition and fields can also be deleted from the record definition.

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

CTTable::AddField() method will add a new field to the end of the record definition.

// create a new table object
CTTable ATable(ADatabase);
// add two fields to the table record definition
ATAble.AddField("Field1", CT_INTEGER, 4);
ATable.AddField("Field2", CT_CHAR, 30);
// create the table
ATable.Create("MyTable", CTCREATE_NORMAL);

CTTable::InsField() inserts a new field before a given field that has already been defined. CTTable::InsField() overloaded methods allow you to specify a field index or a field name to identify the position on the record definition that the new field will be inserted. The first field is number 0, the second field is number 1 and so on.

// create a new table object
CTTable ATable(ADatabase);
// add two fields to the table record definition
ATable.AddField( "Field1", CT_INTEGER, 4);
ATable.AddField( "Field2", CT_CHAR, 30);
ATable.InsField("Field2", "Field3", CT_BOOL, 1);
// create the table
ATable.Create("MyTable", CTCREATE_NORMAL);

CTTable::DelField() deletes a field from the record definition. CTTable::DelField() overloaded methods allow the user to specify a field index or a field name to identify the field to delete. The first field is number 0, the second field is number 1, and so on.

// create a new table object
CTTable ATable(ADatabase);
// add two fields to the table record definition
ATable.AddField( "Field1", CT_INTEGER, 4);
ATable.AddField( "Field2", CT_CHAR, 30);
// delete field2 from record definition
ATable.DelField("Field2");
// create the table
ATable.Create("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. 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 c-tree Plus data type in parentheses:

CT_PSTRING    (CT_PSTRING)

CT_VARBINARY  (CT_2STRING)

CT_LVB    (CT_4STRING)

CT_VARCHAR    (CT_STRING)

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

There are three special fields that 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 fields ($ROWID$)

These fields are transparently and automatically handled by the FairCom DB API API and can’t be handled directly by the field handling functions of the FairCom DB API 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 create table modes that allow the developer creating FairCom DB API tables without any one, 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 NUL 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, but should use the appropriate API functions:

• Use CTRecord::IsNullField() or CTField::GetNullFlag() to verify the if a field contains null data or not.
• Use CTField::SetNullFlag() to set the null flag for a particular field.
• Other functions that clear the null flag are CTRecord::ClearField() and CTRecord::Clear().

$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 CTRecord::GetRowid(), or locate a record given its rowid using CTRecord::FindRowid(). To find out if a table has support for rowid, use CTTable::HasRowid().

$ROWID$ is used by FairCom DB SQL as a unique record identifier. For ISAM files or FairCom DB API tables created with CTCREATE_NOROWID 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. Thus the RECBYT index cannot be used as a unique record identifier.

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

The default the table layout is presented below. Note that 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. An index segment describes the table field from which the keys are created.

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.

CTTable::AddIndex() 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 define which field combination form a particular index. CTTable::AddSegment() overloaded methods will accomplish the task of adding segments to an index.

// create a new table object
CTTable ATable(ADatabase);

// add two fields to the table record definition
ATable.AddField("Field1", CT_INTEGER, 4);
ATable.AddField("Field2", CT_CHAR, 30);

// add index 0 - the first index
ATable.AddIndex("MyIndex1", CTINDEX_FIXED, YES, NO);

// add index 0 segments
ATable.AddSegment("MyIndex1", "Field1", CTSEG_SCHSEG);

// add index 1 - the second index
ATable.AddIndex("MyIndex2", CTINDEX_FIXED, NO, NO);

// add index 1 segments
ATable.AddSegment("MyIndex2", "Field2", CTSEG_SCHSEG);
ATable.AddSegment("MyIndex2", "Field1", CTSEG_SCHSEG);

// create the table
try
{
   ATable.Create("MyTable", CTCREATE_NORMAL);
}
catch (CTException &err)
{
   printf("Create table failed with error %d\n", err.GetErrorCode());
}

The CTTable::AddIndex() method takes an 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:

Note: FairCom DB API .NET Index Key Types are defined in the KEY_TYPE enum.

Deferred Indexing (V11 and later)

The FairCom DB API add, delete, and update operations perform key insert and deletes on every index associated with a data file:

• a record add inserts a key value for each associated index.
• a record delete deletes a key value for each associated index.
• a record update deletes the old key and inserts the new key for each associated index (for those keys whose values changed).

However, each additional index operation can impose measurable performance loss, especially when numerous indexes are involved. If these immediate key operations could be held off for a period of time, applications can gain substantial performance in many circumstances. In V11 (and later), FairCom addressed these challenges with deferred maintenance modes. By delaying select index operations, applications can quickly create and/or update files with large numbers of indexes very quickly.

Background Loading (V11.7 and later)

ctdbCheckIndexBackgroundLoad() function can be used to monitor the status of the index loading on tables.

See Also:

NAV (FairCom DB API) API Variable Length Keys with Compression

The add and insert segment functions require a segment mode to be passed as the last parameter. Please refer to "Segment Modes" describing the valid segment modes.

An index can be deleted from the table index definition by calling one of CTTable::DelIndex() overloaded methods.

// create a new table object
CTTable ATable(ADatabase);

// add two fields to the table record definition
ATable.AddField("Field1", CT_INTEGER, 4);
ATable.AddField("Field2", CT_CHAR, 30);

// add index 0 - the first index
ATable.AddIndex("MyIndex1", CTINDEX_FIXED, YES, NO);

// add index 0 segments
ATable.AddSegment("MyIndex1", "Field1", CTSEG_SCHSEG);

// add index 1 - the second index
ATable.AddIndex("MyIndex2", CTINDEX_FIXED, NO, NO);

// add index 1 segments
ATable.AddSegment("MyIndex2", "Field2", CTSEG_SCHSEG);
ATable.AddSegment("MyIndex2", "Field1", CTSEG_SCHSEG);

// delete index 0
ATable.DelIndex("MyIndex1");

// create the table
try
{
   ATable.Create("MyTable", CTCREATE_NORMAL);
}
catch (CTException &err)
{
   printf("Create table failed with error %d\n", err.GetErrorCode());
}

Segment Modes

By default, the preferred segment modes (see Index 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.

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

The other segment modes are kept for compatibility with existing FairCom DB applications. Advanced FairCom DB API functions like ctdbAlterTable() may not work properly if the segment mode is not one of the preferred segment modes.

You may specify these segment modes with ctdbAddSegmentEx(), which expects an absolute record offset where the segment is to start (instead of a field indicator), the length in bytes of the segment, and the segment mode.

FairCom DB API .NET Segment Modes are defined in the SET_MODE enum.

See also Index segment modes.

ROWID index

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

The ROWID index, formed by a field and an associated 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:

• CTTable::HasRowid() - to determine if the table has ROWID support;
• CTRecord::GetRowid() - to retrieve the ROWID for a given record;
• CTRecord::FindRowid() - to locate and retrieve a record, given the ROWID.

By default, all FairCom DB API created tables have support for the ROWID index. If, for any reason, a table should be created without support for this index, the create mode CTCREATE_NOROWID should be added in CTTable:Create() method.

RECBYT index

RECBYT indexes were introduced in the c-tree Plus 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 an index 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 a 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 index are transparent to the user except for additional 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 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.

Changing default properties

When a table is created, 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 table property values must be changed after the creating the table object, but before calling CTTable::Create(). The following table properties may be changed:

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

Please 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 CTTable::Create() method. CTTable::Create() method take as parameters the table name and the create mode.

// allocate a new table handle
CTTable ATable(ADatabase);

// add two fields to the table record definition
ATable.AddField("Field1", CT_INTEGER, 4);
ATable.AddField("Field2", CT_CHAR, 30);

// add index 0 - the first index
ATable.AddIndex("MyIndex1", CTINDEX_FIXED, YES, NO);

// add index 0 segments
ATable.AddSegment("MyIndex1", "Field1", CTSEG_SCHSEG);

// add index 1 - the second index
ATable.AddIndex("MyIndex2", CTINDEX_FIXED, NO, NO);

// add index 1 segments
ATable.AddSegment("MyIndex2", "Field2", CTSEG_SCHSEG);
ATable.AddSegment("MyIndex2", "Field1", CTSEG_SCHSEG);

// create the table
try
{
   ATable.Create("MyTable", CTCREATE_NORMAL);
}
catch (CTException &err)
{
   printf("Create table failed with error %d\n", err.GetErrorCode());
}

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

The table below lists the table create modes that are to be used with the ctdbCreateTable() function, and are returned by the ctdbGetTableCreateMode() function.

• If you wish to use transaction processing (the ctdbBegin(), ctdbCommit(), and ctdbAbort() functions) to ensure atomicity, 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 use CTCREATE_HUGEFILE.
• Note that some of the modes listed below can be OR’ed together.

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 a 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 open with CTOPEN_EXCLUSIVE mode, otherwise the table open operation will fail. After the transaction is committed the table can be open 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:

// create a new table object
CTTable ATable(ADatabase);

// begin a transaction
ATable.Begin();
try
{
  // add a field
  ATable.AddField("Field1", CT_INTEGER, 4);

  // add another field
  ATable.AddField("Field1", CT_CHAR, 30);

  // create the table
  ATable.Create("MyTable", CTCREATE_NORMAL);

  // commit the transaction
  ATable.Commit();
}
catch (CTException &err)
{
  // abort the transaction
  ATable.Abort();
  printf("Create table failed with error %d\n", err.GetErrorCode());
}

Note: It is important to note that if you open a table that was created inside the current transaction, i.e. the transaction has not yet been committed or aborted, you must open the table in exclusive mode by specifying the CTOPEN_EXCLUSIVE mode to CTTable::Open().

Opening a table

A table must be opened before any data operations within it can take place. Use the CTTable::Open() method to open a table.

// open a table
CTTable ATable(ADatabase);
try
{
   ATable.Open("MyTable", CTOPEN_NORMAL);
}
catch (CTException &err)
{
   printf("Open table failed with error %d\n", err.GetErrorCode());
}

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".

The CTTable::Open() method take as parameters a table name and the table open mode.

Note: FairCom DB API .NET users can find the open modes listed in the OPEN_MODE enum.

Opening a table with password

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

// opening a table with password
CTTable ATable(ADatabase);

// set the table password
ATable.SetPassword("MyPassword");

// open the table
try
{
   ATable.Open("MyTable", CTOPEN_NORMAL);
}
catch (CTException &err)
{
   printf("Open table failed with error %d\n", err.GetErrorCode());
}

Closing a table

After a successful open table, and if the table object is no longer needed, the table should be closed to allow all FairCom DB API, c-tree Plus 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
try
{
   ATable.Close();
}
catch (CTException &err)
{
   printf("Close table failed with error %s\n", err.GetErrorCode());
}

Altering a table

The FairCom DB API alter table function was designed and implemented to allow the modification of table, field and index properties after a table was created, and possibly already populated with data.

The usual steps to perform an alter table are:

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

Add, insert, delete, or edit fields

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

To add, insert or delete a field, call CTTable::AddField(), CTTable::InsField(), CTTable::DelField(), CTField::SetName(), CTField::SetType(), CTField::SetLength(), CTField::SetPrecision(), CTField::SetScale(), and CTField::SetNullFlag().

Most changes relating to fields will trigger the CTTable::Alter() to perform a full table build.

Add, edit or delete indexes

By calling one of the following index management methods, the table definition is marked as modified. For the changes to be reflected on the data and index files, you must call CTTable::Alter() method.

To add or delete an index from a table, call CTTable::AddIndex() and CTTable::DelIndex(). To edit index properties call CTIndex::SetEmptyChar(), CTIndex::SetDuplicateFlag(), and CTIndex::SetNullFlag().

To add, insert or delete index segments from an index, call one of the overloaded methods CTTable::AddSegment(), CTTable::InsSegment(), or CTTable::DelSegment().

Most changes relating to indexes will trigger the CTTable::Alter() method to perform only an index rebuild. If only one index if affected, CTTable::Alter() will only rebuild the affected index. If changes affect more than one index, CTTable::Alter() 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

CTTable::Alter() 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 in 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
CTTable ATable(ADatabase);

// open the table
ATable.Open("MyTable", CTOPEN_NORMAL);

// add one field to the table
ATable.AddField("Wages", CT_CURRENCY, 8);

// alter the table
try
{
   ATable.Alter(CTDB_ALTER_NORMAL);
}
catch (CTException &err)
{
   printf("Alter table failed with error %d\n", err.GetErrorCode());
}

CTTable::Alter() method take as parameter an alter table action parameter:

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 CTField::SetFieldDefaultValue() method.

Example:

// set the default value of country field */
try
{
     CTField hField = hTable.GetField("country"));
     hField.SetFieldDefaultValue("USA");
}
catch (CTException &err)
{
     printf("SetFieldDefaultValue failed\n");
}

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

Example:

// check if default field value is 'USA'
try
{
	CTString value;
	CTField hField = hTable.GetFielld("country");

	hField = ctdbGetField(hTable, 5);
	if (hField.GetFieldDefaultValue(value) > 0)
	{
		if (value == "USA")
			printf("Default value is USA\n");
		else
			printf("Default value is not USA\n");
	}
	else
	 	printf("No default value set\n");
}
catch (CTException &err)
{
	printf("GetFieldDefaultValue failed\n");
}

You can check if a default value is set by calling the IsFieldDefaultValueSet() method.

Example:

// check if default field value is set
CTField hField = hTable.GetField("country");
if (hField.IsFieldDefaultValueSet())
    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 ClearFieldDefaultValue() method 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
try
{
	CTField hField = hTable.GetField("country");
	hField.ClearFieldDefaultValue();
}
catch (CTException &err)
{
	printf("ClearFieldDefaultValue failed\n");
}

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

Example:

// clear all default field values
try
{
	hTable.ClearAllFieldDefaultValue();
}
catch (CTException &err)
{
	printf("ClearAllFieldDefaultValue failed\n");
}

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

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
try
{
	CTField hField = hTable.GetField("country");
	hField.SetFieldDefaultDateTimeType();
}
catch (CTException &err)
{
	printf("SetFieldDefaultDateTimeType failed\n");
}

The default date type value can be retrieved by calling the GetFieldDefaultDateType() method.

Example:

// check the default date type
try
{
	CTField hField = hTable.GetField("country");
	if (hField.GetFieldDefaultDateType() == CTDATE_MDCY)
		printf("Field default date type is OK\n");
}
catch (CTException &err)
{
	printf("GetFieldDefaultDateType() failed\n");
}

The default time type value can be retrieved by calling the GetFieldDefaultTimeType() method.

Example:

// check the default date type
try
{
	CTField hField = hTable.GetField("country");
	if (hField.GetFieldDefaultTimeType() == CTTIME_HMS)
		printf("Field default time type is OK\n");
}
catch (CTException &err)
{
	printf("GetFieldDefaultTimeType() failed\n");
}

Adding an index to a table

If you need to add one or more indexes to an existing table, perform the following steps:

1. Add the index by calling CTTable::AddIndex(). Repeat this step for each new index.
2. Add, insert, or delete index segments by calling the overloaded methods CTTable::AddSegment(), CTTable::InsertSegment(), or CTTable::DelSegment(). Repeat this step for each segment of the index.
3. Call CTTable::Alter() to add the new index

// add new index to table
CTTable ATable(ADatabase);

// open the table
ATable.Open("MyTable", CTOPEN_ NORMAL);

// add the new index
ATable.AddIndex("MyNewIndex", CTINDEX_FIXED, YES, NO);

// add new index segments
ATable.AddSegment("MyNewIndex", "Field1", CTSEG_SCHSEG);

// alter the table to commit index changes to disk
try
{
   ATable.Alter(CTDB_ALTER_NORMAL);
}
catch (CTExplorer &err)
{
   printf("Alter table failed with error %d\n", err.GetErrorCode());
}

Deleting an index from a table

If you need to delete one or mode indexes from a table, perform the following steps:

1. Delete the index by calling CTTable::DelIndex(). There is no need to delete the index segments. Repeat this step for each index you want to delete.
2. Call CTTable::Alter() to delete the index from the table.

// delete the first index
CTTable ATable(ADatabase);

// open the table
ATable.Open("MyTable", CTOPEN_NORMAL);

// delete the first index - MyIndex0
ATable.DelIndex("MyIndex0");

// alter the table to commit index changes to disk
try
{
   ATable.Alter(CTDB_ALTER_NORMAL);
}
catch (CTExplorer &err)
{
   printf("Alter table failed with error %d\n", err.GetErrorCode());
}

Forcing an index rebuild

There may be situations where you may need to build the indexes of a table. You can use the CTDB_ALTER_INDEX action parameter of CTTable::Alter() method to force the rebuild of all indexes of a table. when CTDB_ALTER_INDEX is specified, CTTable::Alter() will rebuild all indexes of a table regardless of any changes done to the table specification.

// rebuild all indexes
CTTable ATable(ADatabase);

// open the table
ATable.Open("MyTable", CTOPEN_NORMAL);

// rebuild all indexes
// alter the table to commit index changes to disk
try
{
   ATable.Alter(CTDB_ALTER_INDEX);
}
catch (CTExplorer &err)
{
   printf("Alter table failed with error %d\n", err.GetErrorCode());
}

Forcing a table rebuild

There may be situations where you may need to force a full table rebuild. Please remember that 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
CTTable ATable(ADatabase);

// open the table
ATable.Open("MyTable", CTOPEN_NORMAL);

// rebuild the table
try
{
   ATable.Alter(CTDB_ALTER_FULL);
}
catch (CTExplorer &err)
{
   printf("Alter table failed with error %d\n", err.GetErrorCode());
}

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.

CTTable::Attach() attaches a c-tree Plus 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.

AttachXtd() attaches a c-tree Plus 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.

Detach() 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:

/* DODA */
static DATOBJ doda[] =
{
	{"f1", (pTEXT)0, CT_INT4, 4},
	{"f2", (pTEXT)4, CT_FSTRING, 10}
};

/* IFIL */
static ISEG iseg = {0, 4, 12};
static IIDX iidx = {4, 0, 0, 0, 0, 1, &iseg, "i311x1", NULL, NULL, NULL};
static IFIL ifil = {"test310", -1, 14, 0, 0, 1, 0, 0, &iidx, "f1", "f2", 0};

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 */
ctdbAttachTableXtd(hTable, datno, doda, &ifil);

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

CTDBRET ctdbDetachTable(CTHANDLE Handle);
void CTTable::Detach();

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 as it can’t be changed once the table is created. Use CTTable::GetName() to retrieve the table name.

Table Path

The table path property is by default set to NULL. If this property is not changed prior to a CTTable::Create() call, the table is created in the same directory the database is located. Change the table path property with CTTable::SetPath(). CTTable::GetPath() retrieves the current table path.

Table file extension

The table data file extension is by default set to ".dat". Change the default data file extension with CTTable::SetDataExtension(). CTTable::GetDataExtension() retrieves the current data file extension for the table.

Index file extension

The table index file extension is by default set to ".idx". Change the default index file extension with CTTable::SetIndexExtension(). CTTable::GetIndexExtension() 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 the need to change it, use CTTable::SetDataDefaultExtentSize(). To retrieve the index default extent size, use CTTable::GetDataDefaultExtentSize().

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 bytes by default. If there is the need to change it, use CTTable::SetIndexDefaultExtentSize(). To retrieve the index default extent size, use CTTable::GetIndexDefaultExtentSize().

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 before creating the table. To set the password use CTTable::SetPassword(), and CTTable::GetPassword() 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. To set a group ID, use CTTable::SetGroupid() and CTTable::GetGroupid() to retrieve it.

Table permission mask

The permission mask is set at table 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 CTTable::SetPermission().

To retrieve the permission mask, use CTTable::GetPermission().

The valid permission mask values are: 

Number of fields

The "number of fields" property indicates the total number of fields defined for a table. The value takes in consideration only user defined fields and does not include any hidden fields that exist for the table. Use CTTable::GetFieldCount() method 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 in consideration only user defined indexes and does not include the RECBYT or ROWID indexes. Use CTTable::GetIndexCount() method to retrieve the number of indexes associated with a table.

Field padding

By default the FairCom DB API API pad fixed length string fields CT_CHARS (CT_FSTRING), CT_FPSTRING, CT_F2STRING and CT_F4STRING with Nulls (‘\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 c-tree Plus ISAM and low level APIs using a fixed string padding strategy that is different from the FairCom DB API API default.

Use CTTable::SetPadChar() to set the pad and field delimiter character. CTTable::GetPadChar() retrieves the current pad and field delimiter character.

// set the table pad and delimiter characters to spaces
try
{
   ATable.SetPadChar(' ', ' ');
}
catch (CTException &err)
{
   printf("Set pad character failed with error %d\n", err.GetErrorCode());
}

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 NUL: pad character is ‘ ’ and field delimiter is ‘\0’

Update create mode

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

// create a new table object
CTTable ATable(ADatabase);

// open the table exclusive
ATable.Open("MyTable", CTOPEN_EXCLUSIVE);

// update the table create mode
try
{
   // retrieve the current create mode
   CTCREATE_MODE curmode = ATable.GetCreateMode();

   // add transaction processing
   curmode |= CTCREATE_TRNLOG;

   // update the table create mode
   ATable.UpdateCreateMode(CTCREATE_TRNLOG);
}
catch (CTException &err)
{
   printf("Update create mode failed with error %d\n", err.GetErrorCode());
}

CTTable::UpdateCreateMode() 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 CTTable::UpdateCreateMode() represents the new table create mode. It must be perfectly formed, as it will replace the current table create mode. Use the function 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