Overview

A database is a collection of tables and a session may contain several different databases. To use a database, you must first add that database to a session. (This is typically done only once, because sessions are persistent; see Working with Sessions in this manual.)

FairCom DB uses handles to manipulate databases and their tables. Before you can manipulate a database (that has been added to the session), you must first allocate a database handle and connect it to a database. You then use that handle to refer to the database when making API calls. Database handles can be reused (connected to one database, and then, later, connected to a different database), so allocating a handle and connecting it to a database are separate operations:

• The "allocate" call configures the database handle and associates it with a session.
• The "connect" call connects the database handle to one of the databases in that session.

Later, the database handle can be reused, by disconnecting it from its database and then connecting it to a different database in that session. Generally, a database handle is required before any table or data operations may take place. The exception to this rule is sessions allocated in CTSESSION_CTREE mode. These sessions do not have access to the database component, so they do not use database handles. (See Working with Sessions without Dictionary Support in this manual.)

The following are typical operations performed on a database handle:

• Allocate a database handle and associate it with a session by calling ctdbAllocDatabase().
• Connect that handle to a database in that session by calling ctdbConnect().
• Use the database handle to perform table, index, field and record operations with that database.
• When done with the database, disconnect it from the database handle by calling ctdbDisconnect().
• Release the database handle by calling ctdbFreeDatabase().

Allocating a Database Handle

Except for sessions allocated in CTSESSION_CTREE mode, a database handle is required before most database operations can take place. The FairCom DB API functions that work with databases expect a database handle as one of their parameters. A database handle is allocated with ctdbAllocDatabase() and freed with ctdbFreeDatabase().

When allocating a database handle, you need to provide a properly-allocated session handle because the call to ctdbAllocDatabase() associates the newly-allocated database handle with the specified session. The session does not need to be logged in, only allocated, before the database handle can be allocated and associated with that session.

CTHANDLE hSession = ctdbAllocSession(CTSESSION_CTDB);
CTHANDLE hDatabase = ctdbAllocDatabase(hSession);

if (!hSession || !hDatabase) {
   printf("Session or database handle allocation failed\n");
   exit(1);
}

When the database handle is no longer needed, release it with ctdbFreeDatabase(). A database handle must be allocated and released by ctdbAllocDatabase() and ctdbFreeDatabase().

If you free/release an active database handle (one that is currently connected to a database), ctdbFreeDatabase() automatically closes all tables associated with the database and disconnects the database from the database handle before releasing any of the resources associated with the database handle.

Connecting to a Database

Now that a database handle has been allocated and associated with a session (by calling ctdbAllocDatabase()), the database handle can be connected to one of the databases in that session by calling ctdbConnect(), which connects the database handle to the database's files on disk. After that, the database is considered "active", and the database handle can be used to perform operations on that database.

Note that creating a new database is the exception to this rule; database creation does not require a database handle.

CTHANDLE pMyDatabase;           
pMyDatabase = ctdbAllocDatabase(pMySession);
ctdbConnect(pMyDatabase, "MyFirstDatabase");

The first line above declares the database handle. The allocation of the handle (second line) links the handle to the session. Use ctdbConnect() (third line) to connect the database handle to the database using the database handle and the name of the database.

If the database doesn't exist, or exists but was dropped from the session, or was never added to the session, this call returns INOT_ERR (101). The best approach to avoid errors is to attempt to connect to the database, and if it doesn't exist, create it. The database path is not required to connect, since this information is stored inside the Session Dictionary. (The database path was provided to the session when the database was added to the session; see ctdbAddDatabase().)

When finished working with a database, disconnect it from your database handle using ctdbDisconnect() or disconnect all databases in a session from their handles with ctdbDisconnectAll(). When the database handle is no longer needed, free the allocated memory using ctdbFreeDatabase(). These steps are shown below, along with logging out from the session and freeing the session handle.

ctdbDisconnect(pMyDatabase);
ctdbLogout(pMySession);
ctdbFreeDatabase(pMyDatabase);
ctdbFreeSession(pMySession);

Database Properties

The default database properties are suitable for most FairCom DB API applications. Advanced developers may need to tune FairCom DB API to meet application requirements.

Database Name

Once the database handle is connected to a database, the database name can be retrieved by calling ctdbGetDatabaseName(). ctdbGetDatabaseName() returns NULL if the database handle is not connected to a database or the database handle is invalid.

/* display the database name */
printf("Database: %s\n", ctdbGetDatabaseName(hDatabase));

Table Count

ctdbGetTableCount() retrieves the number of tables associated with a database, or returns -1 if the database handle is not connected to a database or if the database handle is invalid.

/* display the number of tables in database */
printf("Number of tables: %d\n", ctdbGetTableCount(hDatabase));

Database Path

The database path, by default, is the server directory for client/server applications, or the execution directory for non-server applications. This is where the database dictionary file (which maintains information about the tables associated with the database) is stored on disk. To set a different database path, when the database is being created, just insert the appropriate path as the third parameter in ctdbCreateDatabase(). (See Creating a New Database.) With this information stored in the Session dictionary, the user doesn't need to know where the database dictionary file is located on disk, since it will be automatically retrieved given the database name (ctdbConnect()).

Once the database is successfully connected to a session (via a database handle), the database path can be obtained by calling ctdbGetDatabasePath().

/* display database properties */
void DisplayDatabaseProperties(CTHANDLE hDatabase)
{
   printf("Database: %s\n", ctdbGetDatabaseName(hDatabase));
   printf(   "Path: %s\n", ctdbGetDatabasePath(hDatabase));
   printf("Number of tables: %d\n", ctdbGetTableCount(hDatabase));
}

Managing Tables

Once the user performs a successful database connect, the database handle can be used to operate on the tables that are part of that database.

Every time a new table is created, or an existing table is added to a database, some of the table properties such as table name, path, data file extension, index file extension, etc, are placed in an entry of the database dictionary file (a per-database file that FairCom DB uses to keep track of each database).

Every time a user activates a table by opening it with ctdbOpenTable(), the handle of that table is placed in a list of active (connected) tables within the database handle. When a table is deactivated, or closed by calling ctdbCloseTable(), the table handle is removed from the list of active tables in the database handle. A user may query the active table list by locating the first active table, the next active table, or a specific active table.

Creating a New Table

Some specific steps must be taken when creating a new table using the FairCom DB API API:

• Allocate a new table handle and associate it with a database by calling ctdbAllocTable()
• Add fields to the table by calling ctdbAddField()
• Optionally add indexes and index segments to the table by calling ctdbAddIndex() to add indexes and ctdbAddSegment() to add index segments
• Optionally, change any of the default table properties
• Create the table by calling ctdbCreateTable()

The code fragment below creates a new table, with two fields and no indexes. Please note that error checking was omitted:

/* allocate a new table handle */
hTable = ctdbAllocTable(hDatabase);
/* add a field
ctdbAddField(hTable, "Field0", CT_INTEGER, 4);
/* add another field */
ctdbAddField(hTable, "Field1", CT_CHAR, 30);
/* create the table */
ctdbCreateTable(hTable, "MyTable", CTCREATE_NORMAL);

Working with Tables describes the process of creating a table in detail.

Creating a New 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 opened with CTOPEN_EXCLUSIVE mode, otherwise the table open operation will fail. After the transaction is committed the table can be opened in non-exclusive mode.

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

/* allocate a new table handle and associate it with a database */
hTable = ctdbAllocTable(hDatabase);

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

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

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

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

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

Adding an Existing Table

An existing table may be added or imported to a database by calling ctdbAddTable(). ctdbAddTable() takes a database handle, the table name, and the path where the database is located.

/* add MyTable to the current database */
if (ctdbAddTable(hDatabase, "MyTable", "") != CTDBRET_OK)
    printf("Add table failed\n");

Adding an Existing Table under Transaction Control

An extra level of data integrity may be achieved when you add an existing table to a database under transaction control. When the transaction is committed the database dictionary data for the table is committed to disk. If the transaction is aborted, the dictionary data for the table is automatically removed from the database dictionary.

The code fragment below shows how to add an existing table under transaction control. No error checking is included in the sample code:

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

/* add MyTable to the current database */
if (ctdbAddTable(hDatabase, "MyTable", "") != CTDBRET_OK) {
   printf("Add table failed\n");
   ctdbAbort(hDatabase);
} else {
   printf("Add table\n");
   ctdbCommit(hDatabase);
}

Dropping a Table

When you drop a table from a database, the table information is removed from the database dictionary, but the table data and index files are left untouched. The drop table operation can be reversed with an add table operation. Drop a table from a database by calling ctdbDropTable().

/* drop MyTable from current database */
if (ctdbDropTable(hDatabase, "MyTable") != CTDBRET_OK)
   printf("Drop table failed\n");

Dropping a Table under Transaction Control

An extra level of data integrity may be achieved when you drop a table from a database under transaction control. When the transaction is committed the changes to the database dictionary data for the table is committed to disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary.

The code fragment below shows how to drop an existing table under transaction control. No error checking is included in the sample code:

/* start a transaction */
ctdbBegin(hDatabase);
/* drop MyTable from current database */
if (ctdbDropTable(hDatabase, "MyTable") != CTDBRET_OK) {
   printf("Drop table failed\n");
   ctdbAbort(hDatabase);
} else {
   printf("Drop table\n");
   ctdbCommit(hDatabase);
}

Deleting a Table

When you delete a table from a database, the table information is removed from the database dictionary and the table data and index files are deleted from disk. The delete table operation can be reversed only when used under transaction control (by using the roll back functionality – see the ctrdmp utility). Without transaction control, a delete table operation will permanently delete the data and index files and the table data will be lost. Delete a table from a database by calling ctdbDeleteTable().

/* delete MyTable from current database */
if (ctdbDeleteTable(hDatabase, "MyTable", NULL) != CTDBRET_OK)
   printf("Delete table failed\n");

ctdbDeleteTable() takes as parameters a database handle, the table name, and the table password. Set the password parameter to NULL if a table was created without a password. Note that active/open tables must be closed before they can be deleted.

Note: ctdbDeleteTable() is for use within a CTDB session. For a CTREE session, you must use ctdbRemoveTable(). See also Allocating a Session Handle.

Deleting a Table under Transaction Control

An extra level of data integrity may be achieved when you delete a table from a database under transaction control. When the transaction is committed the changes to the database dictionary data for the table is committed to disk and the table and index files are permanently deleted from disk. If the transaction is aborted, the dictionary data for the table is automatically restored to the database dictionary and the original data and index files are restored to their original state.

The code fragment below shows how to delete an existing table under transaction control. No error checking is included in the sample code:

/* start a transaction */
ctdbBegin(hDatabase);

/* delete MyTable from current database */
if (ctdbDeleteTable(hDatabase, "MyTable", NULL) != CTDBRET_OK) {
   printf("Delete table failed\n");
   ctdbAbort(hDatabase);
} else {
   printf("Delete table\n");
   ctdbCommit(hDatabase);
}

First Table

ctdbFirstTable() retrieves the name and path of the first table in a database dictionary. If the database has no tables, ctdbFirstTable() returns INOT_ERR (101). See the example in Next Table.

Next Table

ctdbNextTable() retrieves the name and path of the next table in a database or returns INOT_ERR (101) when the last table in the current database dictionary has already been retrieved.

/* Display all tables in a database */
CTDBRET DisplayTables(CTHANDLE hDatabase)
{
   CTDBRET Retval;
   TEXT Name[MAX_NAME];
   TEXT Path[MAX_PATH];

   if ((Retval = ctdbFirstTable(hDatabase,Name,sizeof(Name),
                 Path,sizeof(Path)) == CTDBRET_OK)
   {
      do {
         printf("Table: %s Database: %s\n", Name, Path);
         Retval = ctdbNextTable(hDatabase, Name, sizeof(Name),
                  Path, sizeof(Path);
         } while (Retval == CTDBRET_OK);
   }
   if (Retval == INOT_ERR) {
      Retval = CTDBRET_OK;
   }
   return Retval;
}

Find Table

ctdbFindTable() retrieves the path of a specific table in a database dictionary given the table name. If the table is not in the dictionary, ctdbFindTable() returns INOT_ERR (101).

/* return YES if table exist or NO if table does not exit */
CTBOOL TableExist(CTHANDLE hDatabase, pTEXT tblName)
{
   TEXT tblPath[MAX_NAME];

   return(ctdbFindTable(hDatabase,TblName,tblPath,sizeof(tblPath))
          == CTDBRET_OK) ? YES : NO;
}

First Active Table

ctdbGetFirstActiveTable() retrieves the table handle of the first active/open table in a database dictionary. If the database dictionary contains no active/open tables, ctdbGetFirstActiveTable() returns NULL.

Next Active Table

ctdbGetNextActiveTable() retrieves the table handle of the next active/open table in a database dictionary. After the handle of the last active/open table in that database dictionary has been retrieved, ctdbGetNextActiveTable() returns NULL.

/* Display all active tables */
void DisplayActiveTables(CTHANDLE hDatabase)
{
   VRLEN hScan;
   CTHANDLE hTable;

   if ((hTable = ctdbGetFirstActiveTable(hDatabase, &hScan)) != NULL) {
      do {
         printf("Table: %s Path: %s\n",ctdbGetTableName(hTable),ctdbGetTablePath(hTable));
         hTable = ctdbGetNextActiveTable(hDatabase, &hScan);
         } while (hTable != NULL);
   }
}

Find Active Table

ctdbFindActiveTable() retrieves the handle of a specific active/open table in the database dictionary. If that table is not active/connected, or it is not in the database dictionary, ctdbFindActiveTable() returns NULL.

/* Check if table is active */
CTBOOL IsTableActive(CTHANDLE hDatabase, pTEXT tblName)
{
   return (ctdbFindActiveTable(hDatabase, tblName) != NULL) ? YES : NO;
}

The function above is shown for example purposes only. ctdbIsActiveTable() provides a more efficient way to check if a table is active.

Table UID (Unique IDentifier)

When a table is created or added to a database, an automatic and unique identifier (UID) is associated with the table. A table UID is unique within the database associated with the table.

A table UID is an unsigned long value that can be used as an alternative method to operate on tables, once the table is created or added to a database.

Find Table by UID

ctdbFindTableByUID() retrieves the name and path of a table in a database dictionary given the table UID. ctdbFindTableByUID() requires a database or a table handle. The following example shows how to implement a table open function using the table UID instead of the table name.

/* open table using UID */
CTDBRET OpenByUID(CTHANDLE hTable, ULONG uid, CTOPEN_MODE OpenMode)
{
   TEXT tblName[MAX_NAME];
   TEXT tblPath[MAX_PATH];
   CTDBRET Retval;

   Retval = ctdbFindTableByUID(hTable, uid, tblName, sizeof(dbName),
            tblPath, sizeof(tblPath));
   if (Retval == CTDBRET_OK)
      Retval = ctdbOpenTable(hTable, tblName, OpenMode);
   return Retval;
}

The code fragment above is provided as example only. ctdbOpenTableByUID() will open a table using the table UID.

Find Active Table by UID

ctdbFindActiveTableByUID() retrieves the handle of an active/open table in a database dictionary given the table's UID. The following example shows how to check if a table is active using its UID:

/* check if a table is active, by UID */
CTBOOL IsActiveTableByUID(CTHANDLE hTable, ULONG uid)
{
   return (ctdbFindActiveTableByUID(hTable, uid) != NULL) ? YES : NO;
}

Database Dictionary

A database must have a database dictionary; this file is named with the database name and an extension of .fdd. It is created with ctdbCreateDatabase(), and must exist for the user to connect to the database. Each database dictionary maintains information about the tables associated with the database. When a database is connected to the session, it is considered "active".

Database Dictionary Layout

This information is included for general information on the database dictionary structure. FairCom DB API has appropriate functions to retrieve any needed information from the database dictionary, such as the table UID or path.

A database dictionary file has several different record types:

• Table records (the field Type set to 2) holds information about a data file (table).
• Index records (the field Type set to 3) holds information about an index file.
• A special type four record which maintained the COUNTER field has been been deprecated as of FairCom DB V9.0.