|
|
|
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.
|
|
|
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");
|
|
|
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");
|
|
|
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:
|
|
|
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(), ctdbSetFieldPrecision(), ctdbSetFieldScale(), and ctdbSetFieldNullFlag().
Most changes relating to fields will trigger ctdbAlterTable() to perform a full table rebuild.
|
|
|
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.
|
|
|
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:
Action |
Value |
Explanation |
|---|---|---|
CTDB_ALTER_NORMAL |
0 |
Check for table changes before altering the table and perform only the changes required. |
CTDB_ALTER_INDEX |
1 |
Force rebuild of all indexes, regardless of table changes. |
CTDB_ALTER_FULL |
3 |
Force full table rebuild, regardless of table changes. |
CTDB_ALTER_PURGEDUP |
4096 |
Purge duplicate records |
CTDB_ALTER_TRUNCATE |
8192 |
Quickly remove all records |
|
|
|
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:
FairCom DB API |
FairCom DB API .NET |
|
CTDATE_MDCY |
MDCY_DATE |
Date is mm/dd/ccyy |
CTDATE_MDY |
MDY_DATE |
Date is mm/dd/yy |
CTDATE_DMCY |
DMCY_DATE |
Date is dd/mm/ccyy |
CTDATE_DMY |
DMY_DATE |
Date is dd/mm/yy |
CTDATE_CYMD |
CYMD_DATE |
Date is ccyymmdd |
CTDATE_YMD |
YMD_DATE |
Date is yymmdd |
Time Types can be one of the following string time formats:
FairCom DB API |
FairCom DB API .NET |
|
CTTIME_HMSP |
HMSP_TIME |
Time is hh:mm:ss am|pm |
CTTIME_HMP |
HMP_TIME |
Time is hh:mm am|pm |
CTTIME_HMS |
HMS_TIME |
Time is hh:mm:ss (24 hour) |
CTTIME_HM |
HM_TIME |
Time is hh:mm (24 hour) |
CTTIME_MIL |
MIL_TIME |
Time is hhmm (military) |
CTTIME_HHMST |
|
Time is hh:mm:ss.ttt (24 hour) |
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");
|
|
|
To add one or more indexes to an existing table, perform the following steps:
/* 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");
|
|
|
To delete one or more indexes from a table, perform the following steps:
/* 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");
|
|
|
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");
|
|
|
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");
