A database may contain multiple tables, and a table may belong to multiple databases. Tables created using the FairCom DB API .NET 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 .NET 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 .NET, 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 .NET 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 = new CTTable(ADatabase);
try
{
   // open table "MyTable"
   ATable.Open("MyTable", OPEN_MODE.NORMAL_OPEN);
}
catch (CTException err)
{
   Console.Write("Table open failed with error {0}\n", err.GetErrorCode());
}

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.

// create the session and table objects
CTSession ASession = new CTSession(SESSION_TYPE.CTDB_SESSION);
CTTable ATable = new CTTable(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", OPEN_MODE.NORMAL_OPEN);
}
catch (CTException err)
{
   Console.Write("Table open failed with error {0}\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 .NET 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 = new CTSession(SESSION_TYPE.CTDB_SESSION);
CTTable ATable = new CTTable(ASession);

// logon to session
ASession.Logon("FAIRCOMS", "ADMIN", "ADMIN");
// add fields to table
ATable.AddField("Field1", FIELD_TYPE.INT2, 2);
ATable.AddField("Field2", FIELD_TYPE.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", CREATE_MODE.NORMAL_CREATE);
}
catch (CTException err)
{
   Console.Write("Create open failed with error {0}\n, err.GetErrorCode());
}

Note: Delphi users should use the equivalent method CreateSession() instead of Create().

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 .NET 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 .NET 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 field will have a file type (e.g., CT_INTEGER and CT_CHAR in the example below). For more information, see Field Types.

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

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

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

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

Note: Delphi users should use the equivalent method CreateSession() instead of Create().

CTTable.InsertField() inserts a new field before a given field that has already been defined. The CTTable.InsertField() 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 = new CTTable(ADatabase);

// add two fields to the table record definition
ATable.AddField( "Field1", FIELD_TYPE.INTEGER, 4);
ATable.AddField( "Field2", FIELD_TYPE.CHARS, 30);
ATable.InsertField("Field2", "Field3", FIELD_TYPE.BOOL, 1);

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

CTTable.DelField() deletes a field from the record definition. The 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 = new CTTable(ADatabase);

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

// delete field2 from record definition
ATable.DelField("Field2");
 
// create the table
ATable.Create("MyTable", CREATE_MODE.NORMAL_CREATE);

Field Types

FairCom DB API .NET supports all original c-tree Plus field types and includes redefinition for new field types. For compatibility reasons, the original c-tree Plus field types can be used, but FairCom suggests using the new FairCom DB API .NET field types.

The "FairCom DB API .NET field types" do not represent new field types. They are just new names for the existing c-tree Plus field types. The new naming convention is used in the c-treeSQL product line, and offers a better description of the fields.

FairCom DB API .NET field types are defined in the enum type FIELD_TYPE, which must be used every time a field type is needed.

For more information, see Field type equivalencies across APIs.

Fixed or Variable Length Records

The FairCom DB API .NET 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 .NET 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 .NET 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:

FIELD_TYPE.VPSTRING    (CT_PSTRING)

FIELD_TYPE.VARBINARY   (CT_2STRING)

FIELD_TYPE.LVB         (CT_4STRING)

FIELD_TYPE.VARCHAR     (CT_STRING)

FIELD_TYPE.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 .NET 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 .NET in the table record definition:

• the delete flag ($DELFLD$)
• the null flag ($NULFLD$)
• the ROWID fields ($ROWID$)

These fields are transparently and automatically handled by the FairCom DB API .NET API and can't be handled directly by the field handling functions of the FairCom DB API .NET 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 .NET will operate correctly with tables that do not possess them. There are also create table modes that allow the developer create FairCom DB API .NET tables without any one, or all, of the hidden fields.

Note: The FairCom DB SQL Server makes extensive use of the NULL flag and ROWID fields.

The delete flag field is for internal deletion purposes and should not be modified. $DELFLD$ is a CT_ARRAY field of 4 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 .NET 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 c-treeSQL as a unique record identifier. For ISAM files or FairCom DB API .NET tables created with CREATE_MODE.NOROWID_CREATE 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 .NET 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 .NET 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 CREATE_MODE.NONULFLD_CREATE, CREATE_MODE.NODELFLD_CREATE and CREATE_MODE.NOROWID_CREATE.

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 .NET 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 forms a particular index. CTTable.AddSegment() overloaded methods will accomplish the task of adding segments to an index.

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

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

// add index 0 - the first index
ATable.AddIndex("MyIndex1", KEY_TYPE.FIXED_INDEX, true, false);

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

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

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

// create the table
try
{
   ATable.Create("MyTable", CREATE_MODE.NORMAL_CREATE);
}
catch (CTException err)
{
   Console.Write("Create table failed with error {0}\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 defined in the KEY_TYPE enum:

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 = new CTTable(ADatabase);

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

// add index 0 - the first index
ATable.AddIndex("MyIndex1", KEY_TYPE.FIXED_INDEX, true, false);

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

// add index 1 - the second index
ATable.AddIndex("MyIndex2", KEY_TYPE.FIXED_INDEX, false, false);

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

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

// create the table
try
{
   ATable.Create("MyTable", CREATE_MODE.NORMAL_CREATE);
}
catch (CTException err)
{
   Console.Write("Create table failed with error {0}\n", err.GetErrorCode());
}

Segment Modes

The segment modes based on absolute field number, also known as schema fields, are the preferred modes for segment definition. The preferred segment modes are defined in the SEG_MODE enum:

• SCHSEG_SEG
• USCHSEG_SEG
• VSCHSEG_SEG
• UVSCHSEG_SEG
• SRLSEG_SEG

You may OR in the mode SEG_MODE.DESCENDING_SEG to the segment mode to specify the descending sort order for a segment. You can also OR in the segment mode SEG_MODE.ALTSEG_SEG to specify an alternate collating sequence for the segment.

The use of the preferred segment modes makes the FairCom DB API .NET table 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 .NET functions like CTTable.Alter() may not work properly if the segment mode is not one of the preferred 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 .NET user. FairCom DB API .NET 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 tables created with FairCom DB API .NET have support for the ROWID index. If, for any reason, a table should be created without support for this index, the create mode CREATE_MODE.NOROWID_CREATE should be added in CTTable:Create() method.

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 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 CREATE_MODE.NORECBYT_CREATE 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, although this index can add a performance overhead. FairCom DB API .NET 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 the 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.

Changing Default Properties

After all fields and indexes have been defined and the table properties set, it is time to create the table by calling the CTTable.Create() method. This method takes as parameters the table name and the create mode.

Note: Delphi users should use the equivalent method CreateSession() instead of Create().

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

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

// add index 0 - the first index
ATable.AddIndex("MyIndex1", KEY_TYPE.FIXED_INDEX, true, false);

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

// add index 1 - the second index
ATable.AddIndex("MyIndex2", KEY_TYPE.FIXED_INDEX, false, false);

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

// create the table
try
{
   ATable.Create("MyTable", CREATE_MODE.NORMAL_CREATE); 
}
catch (CTException err)
{
   Console.Write("Create table failed with error {0}\n", err.GetErrorCode());
}

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 defined in the CREATE_MODE enum:

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

Note: Delphi users should use the equivalent method CreateSession() instead of Create().

// create a new table object
CTTable ATable = new CTTable(ADatabase);
// add two fields to the table record definition
ATable.AddField("Field1", FIELD_TYPE.INTEGER, 4);
ATable.AddField("Field2", FIELD_TYPE.CHARS, 30);
// add index 0 - the first index
ATable.AddIndex("MyIndex1", KEY_TYPE.FIXED_INDEX, true, false);
// add index 0 segments
ATable.AddSegment("MyIndex1", "Field1", SEG_MODE.SCHSEG_SEG);
// add index 1 - the second index
ATable.AddIndex("MyIndex2", KEY_TYPE.FIXED_INDEX, false, false);
// add index 1 segments 
ATable.AddSegment("MyIndex2", "Field2", SEG_MODE.SCHSEG_SEG);
ATable.AddSegment("MyIndex2", "Field1", SEG_MODE.SCHSEG_SEG);
// create the table
try
{
   ATable.Create("MyTable", CREATE_MODE.NORMAL_CREATE);  
}
catch (CTException err)
{
   Console.Write("Create table failed with error {0}\n", err.GetErrorCode());
}

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 defined in the CREATE_MODE enum:

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 OPEN_MODE.EXCLUSIVE_OPEN 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 = new CTTable(ADatabase);

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

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

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

   // commit the transaction
   ATable.Commit();
}
catch (CTException err)
{
   // abort the transaction
   ATable.Abort();
   Console.Write("Create table failed with error {0}\n", err.GetErrorCode());
}

 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 OPEN_MODE.EXCLUSIVE_OPEN 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 = new CTTable(ADatabase);
try
{
   ATable.Open("MyTable", OPEN_MODE.NORMAL_OPEN);
}
catch (CTException err)
{
   Console.Write("Open table failed with error {0}\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 takes as parameters a table name and the table open mode defined in the OPEN_MODE enum.

Table Open Modes

Opening a Table with a Password

If a table was created with a password, every time that table is opened, 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 = new CTTable(ADatabase);

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

// open the table
try
{
   ATable.Open("MyTable", OPEN_MODE.NORMAL_OPEN);
}
catch (CTException err)
{
   Console.Write("Open table failed with error {0}\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 .NET, 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)
{
   Console.Write("Close table failed with error {0}\n", err.GetErrorCode());
}

Altering a Table

The FairCom DB API .NET 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 the 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 in the data and index files, you must call CTTable.Alter().

To add, insert or delete a field, call CTTable.AddField(), CTTable.InsertField(), 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 rebuild.

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 in 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

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.AddSeg, 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 = new CTTable(ADatabase);

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

// add the new index
ATable.AddIndex("MyNewIndex", KEY_TYPE.FIXED_INDEX, true, false);

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

// alter the table to commit index changes to disk
try
{
   ATable.Alter(ALTER_TABLE.NORMAL);
}
catch (CTException err)
{
   Console.Write("Alter table failed with error {0}\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 = new CTTable(ADatabase);

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

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

// alter the table to commit index changes to disk
try
{
   ATable.Alter(ALTER_TABLE.NORMAL);
}
catch (CTException err)
{
   Console.Write("Alter table failed with error {0}\n", err.GetErrorCode());
}

Forcing an Index Rebuild

There may be situations where you need to rebuild the indexes of a table. You can use the ALTER_TABLE.INDEX action parameter of the CTTable.Alter() method to force the rebuild of all indexes of a table. When ALTER_TABLE.INDEX is specified, CTTable.Alter() will rebuild all indexes of a table regardless of any changes made to the table specification.

// rebuild all indexes
CTTable ATable = new CTTable(ADatabase);

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

// rebuild all indexes
// alter the table to commit index changes to disk
try
{
   ATable.Alter(ALTER_TABLE.INDEX);
}
catch (CTException err)
{
   Console.Write("Alter table failed with error {0}\n", err.GetErrorCode());
}

Forcing a Table Rebuild

There may be situations where you 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.

CTTable ATable = new CTTable(ADatabase);

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

// rebuild all indexes
// alter the table to commit index changes to disk
try
{
   ATable.Alter(ALTER_TABLE.FULL);
}
catch (CTException err)
{
   Console.Write("Alter table failed with error {0}\n", err.GetErrorCode());
}

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 a 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 a need to change it, use CTTable.SetIndexDefaultExtentSize(). To retrieve the default index extent size, use CTTable.GetIndexDefaultExtentSize().

Table Password

By default tables do not have a password (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 .NET tables (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 (O_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, defined in the TABLE_PERMS enum, 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 the 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 the CTTable.GetIndexCount() method to retrieve the number of indexes associated with a table.

Field Padding

By default the FairCom DB API .NET API pad fixed length string fields FIELD_TYPE.CHARS (FIELD_TYPE.FSTRING), FIELD_TYPE.FPSTRING, FIELD_TYPE.F2STRING and FIELD_TYPE.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 .NET 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 .NET 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)
{
   Console.Write("Set pad character failed with error {0}\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 CTTable.UpdateCreateMode() 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 = new CTTable(ADatabase);

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

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

   // add transaction processing
   curmode |= CREATE_MODE.TRNLOG_CREATE;

   // update the table create mode
   ATable.UpdateCreateMode(curmode);
}
catch (CTException err)
{
   Console.Write("Update create mode failed with error {0}\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 CTTable.GetCreateMode() to retrieve the current create mode and apply the changes on a fully qualified create mode. Update only the following create table modes:

• CREATE_MODE.PREIMG_CREATE
• CREATE_ MODE.TRNLOG_CREATE
• CREATE_ MODE.WRITETHRU_CREATE
• CREATE_ MODE.CHECKLOCK_CREATE
• CREATE_ MODE.CHECKREAD_CREATE
• CREATE_ MODE.HUGEFILE_CREATE