Applications developed using ITTIA DB Compact™ can be upgraded to ITTIA DB-SQL Standard™ or ITTIA DB-SQL Plus™. Applications developed for ITTIA DB-SQL Standard™ can also be upgraded to ITTIA DB-SQL Plus™.

To upgrade an existing ITTIA DB application to ITTIA DB-SQL Standard™, modify the build configuration to link with one of the single-user libraries listed in Section 3.3, “Linking ITTIA DB SQL™ into an Application”. Automatic recovery will be automatically enabled without modifying the application code. The application will also be able to use SQL queries if the SQL library is selected.

To upgrade an existing ITTIA DB application to ITTIA DB-SQL Plus™, modify the build configuration to link with one of the default, IPC client, or local storage libraries listed in Section 3.3, “Linking ITTIA DB SQL™ into an Application”. Row-level locking will be automatically enabled without modifying the application code. The application will also be able to share an open database between threads, or connect to dbserver to share databases between multiple applications running on one or more devices.

	Note
	The following instructions assume that ITTIA DB SQL™ has been installed to the directory $DBDIR. In the instructions that follow, you must replace $DBDIR with the directory in which ITTIA DB SQL™ is installed.

The following steps show how to add ITTIA DB SQL™ to a Visual C++ Project.

The following steps show how to add ITTIA DB SQL™ to a Visual C++ Project.

The following steps show how to add ITTIA DB SQL™ to an eMbedded Visual C++ Project:

On the command line:

If only the C API is needed, the C++ API library can be omitted from the above command by removing the -ldbcppapi option. While gcc can be used to compile source code files, g++ must be used to link the application.

To run the application, Linux must be able to find the ITTIA DB shared library. When the application is deployed, either install the library to /usr/lib or add the directory in which the shared library is installed to /etc/ld.so.conf, then run ldconfig. During development, the environment variable LD_LIBRARY_PATH can be set to a colon-separated list of directories where libraries should be search for.

	Tip
	The GNUMakefile included with the example program can also be used as a template for automating the build process.

To build and run the example program in Visual C++ 6:

To build and run the example program in Visual Studio 2005:

To build and run the example program in Microsoft eMbedded Visual C++:

	Note
	The required dll files for these examples must be copied to the device or emulator. If eMbedded Visual Studio does not copy these dlls to the target environment, you must copy them manually using the Remote File Viewer tool.

To build and run the example program with gcc:

The ittiasql utility is an easy way to learn about ITTIA DB SQL™. The following session creates a database file and initializes the schema.

$ .create example.db
example.db$ create table contact (
         -$   id uint64, name nvarchar(50), ring_id uint64,
         -$   picture_name varchar(50), picture blob
         -$ );
example.db$ create unique index by_id on contact (id);
example.db$ create index by_name on contact (name);
example.db$ create table phone_number (
         -$   contact_id uint64, number ansistr(20),
         -$   type uint64, speed_dial sint64
         -$ );
example.db$ create index by_contact_id on phone_number (contact_id);
example.db$ create sequence contact_id start with 1;

After tables are created, sample data can be inserted as part of a transaction.

example.db$ start transaction;
example.db$ insert into contact (id, name)
         -$ values (next value for contact_id, 'Bob');
example.db$ insert into phone_number (contact_id, number, type, speed_dial)
         -$ values (current value for contact_id, '555-5555', 0, 5);
example.db$ insert into phone_number (contact_id, number, type, speed_dial)
         -$ values (current value for contact_id, '555-6666', 1, null);
example.db$ commit;

	Tip
	The schema cannot be modified while a transaction is pending. Commit or rollback before creating additional tables.

Data is read from a table using the SELECT statement. SELECT can be used to read the entire contents of a table, or limit the results to only columns and rows of interest.

Projection limits the number of columns that are read, and is accomplished by giving SELECT a list of columns to include in the result. The list of columns can also contain expressions.

Selection limits the number of rows that are read, and is accomplished by including a WHERE clause with criteria that each row must satisfy. Rows that do not satisfy the criteria are not included in the results.

example.db$ select * from contact;

+--+----+-------+------------+-------+
|ID|NAME|RING_ID|PICTURE_NAME|PICTURE|
+--+----+-------+------------+-------+
|1 |Bob |NULL   |NULL        |NULL   |
+--+----+-------+------------+-------+

example.db$ select id, name from contact;

+--+----+
|ID|NAME|
+--+----+
|1 |Bob |
+--+----+

example.db$ select * from phone_number where type = 0;

+----------+--------+----+----------+
|CONTACT_ID|NUMBER  |TYPE|SPEED_DIAL|
+----------+--------+----+----------+
|1         |555-5555|0   |5         |
+----------+--------+----+----------+

Tables are related when they each have columns that contain the same data. Related tables can be joined together by matching the related columns to create rows that contain data from both tables.

example.db$ select name, number, type, speed_dial
         -$ from contact
         -$ join phone_number on id = contact_id;

+----+--------+----+----------+
|NAME|NUMBER  |TYPE|SPEED_DIAL|
+----+--------+----+----------+
|Bob |555-5555|0   |5         |
+----+--------+----+----------+

Joins are described in detail in Section 4.2.2.1, “FROM Clause”.

Literal values in an SQL statement can be replaced by parameters. This feature allows an application to separate variable data from the logic of the query, improving both performance and security.

ITTIA DB SQL™ reads parameter values directly from application memory without parsing or unnecessary conversion. This avoids the overhead of converting numeric values to strings and then back when the query is executed. Frequently-used statements are automatically cached by the database and will execute more quickly on subsequent runs. This performance benefit is only available for identical statements executed multiple times, but parameter values for that statement can be changed.

When string literals are inserted directly into an SQL statement, the application is susceptible to SQL injection attacks. A carefully designed string can contain SQL code that modifies the meaning of the SQL statement, allowing the attacker to execute arbitrary SQL commands. Fortunately, a fully parameterized query is completely impervious to this kind of attack.

	Important
	Use of parameters is highly recommended in all application queries. Never construct an SQL statement string that contains values supplied by the user or from a source outside the program's control.

To use parameters, replace literal values with the ? placeholder, as shown in Example 4.5, “Positional SQL Query Parameters”. In the ittiasql utility, values for each parameter are then input one at a time. In application source code, parameter values can be set through the API before the query is executed.

Use the ? placeholder to order parameters by position.

example.db$ insert into contact (id, name) values (?, ?);
param[0] (integer): 2
param[1] (string): Charley

example.db$ insert into phone_number (contact_id, number, type) values (?, ?, ?);
param[0] (integer): 2
param[1] (string): 555-4444
param[2] (integer): 0

example.db$ select * from contact where name = ?;
param[0] (string): Charley

+--+-------+-------+------------+-------+
|ID|NAME   |RING_ID|PICTURE_NAME|PICTURE|
+--+-------+-------+------------+-------+
|2 |Charley|NULL   |NULL        |NULL   |
+--+-------+-------+------------+-------+

Parameters are identified by the order they appear in the SQL statement, starting from zero. The first ? placeholder to appear in the statement corresponds to parameter 0, the second ? placeholder corresponds to parameter 1, and so on.

The ? placeholder can only appear in a WHERE clause or a VALUES list. The data type of the parameter is inferred from the expression it is used in.

An alternative placeholder syntax is also available in ITTIA DB SQL™ that provides greater parameter control. The ? placeholder can be replaced with the $<type>n placeholder. Replace type with the name of the column data type to use for the parameter and n with the parameter number. See Example 4.6, “Numbered SQL Query Parameters” for an example using this syntax.

With numbered parameters, it is possible to use the same parameter multiple places in the query. And because they are explicitly typed, numbered parameters can also appear in the SELECT list where the data type of the parameter cannot be inferred from context.

example.db$ select * from contact where name = $<nvarchar>0;
param[0] (string): Charley

+--+-------+-------+------------+-------+
|ID|NAME   |RING_ID|PICTURE_NAME|PICTURE|
+--+-------+-------+------------+-------+
|2 |Charley|NULL   |NULL        |NULL   |
+--+-------+-------+------------+-------+

ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.

Data types are listed in Table 4.2, “SQL Column Types”. Each data type has one or more SQL column type names, which can be used interchangeably. The column type name is used when a table is created or altered. See Section 4.2.8, “Schema Definition” for more information.

Tip

For each column type, the C API provides a platform-independent C variable type that best matches the size and format used in the database. For best performance, the corresponding C data type should be used, but some column types can be bound to other C data types as well. See Section 6.2.1, “Column Types” for more information. Field classes in the C++ API can be cast to a variety of native types. See Section 5.2.2, “Column Types” for more information.

4.2.1.4. NULL Values

ITTIA DB SQL™ has a special value called NULL. When a value is NULL, it indicates that the value is unknown or has not been set. Every column type supports NULL. If a value is not specified for a column when a row is inserted, the default value is NULL.

Comparison operators handle NULL as a special case. When any value is compared with NULL, the result is false. To determine whether a value is NULL, use the expression "value IS NULL" or "value IS NOT NULL".

	Tip
	In C and C++, NULL is a pointer type equal to 0. In SQL, 0 and NULL are different values.

SELECT is used to read data from the database. In its most basic form, SELECT will read the entire contents of a table. The following statement returns the entire contents of the contact table.

select *
  from contact

The results can be restricted to only certain columns by naming them in the field list.

select id, name
  from contact

Columns can be renamed when selected with the AS keyword.

select id as contact_id, name as contact_name
  from contact

	Tip
	The AS keyword is optional and can be omitted, but is recommended for clarity.

The results of a query may contain multiple rows. To eliminate duplicates, use the DISTINCT keyword. The following query identifies all contacts with at least one phone number:

select distinct contact_id from phone_number

The CAST keyword is used to convert a term to a different data type.

select 'The ID is: ' || cast (id as varchar)
  from contact

The CASE keyword provides conditional logic. There are two forms of CASE. One checks a series of search conditions, similar to an if statement in C. The other form compares two terms and uses the first match. The following example shows both forms:

select number,
    case
      when speed_dial >= 0 and speed_dial <= 9 then speed_dial
      else null
    end as speed_dial,
    case type
      when 1 then 'Mobile'
      when 2 then 'Work'
      when 3 then 'Fax'
      when 4 then 'Pager'
      else number
    end as type
  from phone_number

The sequence expressions NEXT VALUE FOR and CURRENT VALUE FOR are described in nextval(str) and currval(str).

select name, number
  from contact
  join phone_number on id = contact_id

select contact.name, phone_number.number
  from contact
  join phone_number on contact.id = phone_number.contact_id

select c.name, p.number
  from contact as c
  join phone_number as p on c.id = p.contact_id

select p1.number as number, p2.number as alternative_number
  from phone_number as p1
  join phone_number as p2 on p1.contact_id = p2.contact_id and p1.number <> p2.number

select *
  from contact
  where name = 'Bob'

select contact_id, count(number)
  from phone_number
  group my contact_id

select name, contact_id, count(number) as number_count
  from phone_number
  join contact on contact_id = id
  group by name, contact_id

select *
  from contact
  order by name

select *
  from phone_number
  order by contact_id asc, type desc

select * from a
union
select * from b
union
select * from c
except
select * from d
union
select * from e

select *
  from (
    select * from a
    intersect
    select * from b
  )
union
select *
  from c

In ITTIA DB SQL™, SQL statements can use the natural numbers virtual table, $nat(count), to select from a list of integers. The resulting table contains a single column named "n" listing the first count natural numbers.

$ select * from $nat(10);

+-+
|N|
+-+
|0|
|1|
|2|
|3|
|4|
|5|
|6|
|7|
|8|
|9|
+-+

$ select n + 1, 'Number ' || cast(n + 1 as varchar) from $nat(4);

+--+--------+
|$0|$1      |
+--+--------+
|1 |Number 1|
|2 |Number 2|
|3 |Number 3|
|4 |Number 4|
+--+--------+

$ insert into some_table (n) select * from $nat(1000);

Several built-in functions are available to perform common mathematical and string operations. Available functions are listed in Table 4.6, “SQL Built-in Functions”. Functions can be used in the SELECT field list, JOIN ON condition, WHERE clause, GROUP BY clause, ORDER BY clause, and INSERT INTO ... VALUES list.

Three kinds of modifications to database rows are available: insert new rows, update existing rows, and delete rows. Any modification of the database will start a transaction, which must be committed before changes will become persistent. For more information, see Section 4.2.6, “Transactions”.

insert into phone_number (contact_id, number, type, speed_dial)
  values (1, '555-7777', 0, null)

insert into phone_number (contact_id, number, type)
  values (1, '555-7777', 0)

insert into phone_number (contact_id, number, type, speed_dial)
  select 2, number, type, speed_dial
    from phone_number
    where contact_id = 1

update contact
  set name = 'Bobby', ring_id = 8
  where name = 'Bob'

4.2.5.3. DELETE

DELETE FROM
table-name [AS alias]
[WHERE search-condition]

DELETE FROM removes zero, one or more rows from a single table. By default, all rows in the table are deleted.

If a WHERE clause is provided, only rows matching the search criteria will be deleted. For more information, see Section 4.2.2.2, “WHERE Clause”.

	Tip
	Before deleting rows, run a similar SELECT statement to test the WHERE condition.

Database updates through SQL will automatically start a transaction so that related changes are grouped together into a single atomic operation. Changes will not become permanent or available to other users of the database until the transaction is committed.

A transaction can be started manually with the START TRANSACTION statement. When the database is shared, this can also be used to select an isolation level, as described in Section 7.4, “Isolation Levels”. If no isolation level is selected, the default isolation level set in the API will be used.

The schema cannot be modified while a transaction is active.

The COMMIT statement finishes a transaction.

A completion mode can be used to control when the changes are written to disk or other storage media immediately. FORCED COMPLETION ensures that all changes are persistent before the statement can return. GROUP COMPLETION and LAZY COMPLETION will return immediately and delay writing until later. GROUP COMPLETION waits for several transactions to be committed before writing changes to disk. LAZY COMPLETION will only write to disk when more memory is needed or when a disk flush is explicitly requested through the API. Changes are always written when the database is closed.

If no completion mode is given, the default completion mode selected in the API is used.

To cancel changes made during an active transaction, use the ROLLBACK statement instead of COMMIT. The transaction is aborted and the database is restored to its former state.

	Tip
	The C and C++ APIs also provide functions to control transactions without using SQL.

Savepoints are used within a transaction to support partial rollback without reverting the entire transaction. Savepoints can be nested, allowing them to be used with nested function calls.

Locks obtained during the savepoint are not released until the end of the enclosing transaction.

Set a savepoint. The savepoint can be assigned a optional name. UNIQUE ensures that no savepoint exists with the same name. OVERRIDE replaces existing savepoints with the same name. NESTED allows multiple savepoints to be created with the same name.

A transaction is started if not already active. Savepoints are automatically released when the transaction is committed or rolled back.

Release a savepoint. Use CURRENT to release the most recently created savepoint.

When a savepoint is released, it can no longer be used to roll back to beginning of the savepoint. However, this does not garauntee that any work will be saved. If the enclosing transaction or an enclosing savepoint is rolled back, work performed in this savepoint will be lost.

Any savepoints that were subsequently set are automatically released.

When a savepoint is rolled back, the database is returned to the state it was in when the savepoint was set. Any savepoints that were subsequently set are automatically rolled back as well.

Schema definition statements alter the layout of the database. These statements cannot be used when a transaction is active and cannot be rolled back.

create table contact (
  id uint64 not null,
  name nvarchar(50),
  ring_id uint64,
  picture_name varchar(50),
  picture blob,
  constraint by_id primary key (id)
)

create index by_name on contact (name)

create unique index by_id on contact (id)

create sequence contact_id start with 1

alter table contact
  add column age integer;

alter table contact
  add unique (age);

alter table contact
  drop unique (age);

alter table contact
  drop column age;

alter table phone_number
  drop constraint contact_ref;

alter table phone_number
  add constraint contact_ref foreign key (contact_id)
  references contact(id)
  on delete cascade
  on update set null;

To use the ITTIA DB SQL™ C++ API, include the header ittia/db++.h.

#include <ittia/db++.h>

The API is encapsulated in the db namespace.

A database is managed by a Database object. To create a database for the first time, use the object's create() function.

db::Database db;
db::StorageMode mode;
int rc;

rc = db.create("phone_book.db", mode);

if (DB_FAILED(rc)) {
    cerr << "Error creating database: " << rc << endl;
}

	Caution
	If the file already exists, create() will overwrite it.

To open an existing database, use the open() function.

rc = db.open("phone_book.db", mode);

if (DB_FAILED(rc)) {
    cerr << "Error opening database: " << rc << endl;
}

When finished with the database, call close().

db.close();

The database will also close automatically when the Database object is destroyed.

The following example creates a memory storage named memory.db.

db::Database db;
db::StorageMode mode;
int rc;

mode.file_mode = db::DB_MEMORY_STORAGE;
mode.memory_storage_size = 4 * 1024 * 1024;

rc = db.create("memory.db", mode);

if (DB_FAILED(rc)) {
    cerr << "Error creating database: " << rc << endl;
}

When a memory storage is created, the application must specify the size of the memory storage. This is the number of bytes that will be allocated for tables and indexes. The size of the database cannot exceed this amount.

An application can create multiple storages in memory if each storage has a different name. Other threads and clients can connect to an existing memory storage using open().

rc = db.open("memory.db", mode);

The memory_storage_size parameter is ignored when opening an existing memory storage. If another thread or client has already created the memory storage, the existing size will be used. Otherwise, open() will return a NULL handle and the error DB_ENOENT will be set.

When finished with the database, call close() to close the connection. When the last connection is closed, the storage is destroyed.

db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);

To connect to a database containing tables both in memory and on disk, set the memory_storage_size parameter before creating or opening a file storage. The following example creates a new file storage with support for memory tables.

db::Database db;
db::StorageMode mode;
int rc;

// Optional: file storage is the default.
mode.file_mode = db::DB_FILE_STORAGE;
mode.memory_storage_size = 4 * 1024 * 1024;

rc = db.create("file.db", mode);

if (DB_FAILED(rc)) {
    cerr << "Error creating database: " << rc << endl;
}

The memory storage size must be specified when opening an existing file storage. If necessary, an empty memory storage will be allocated.

When all connections to a hybrid database are closed, memory tables will not be dropped, but all memory tables will be empty the next time the database is opened.

Most C++ API functions return an error code. To determine whether an operation was successful, check the return code with DB_SUCCESS() or DB_FAILED().

if (DB_SUCCESS(rc)) {
    cout << "Operation successful" << endl;
}

if (DB_FAILED(rc)) {
    cout << "Operation failed with error code: " << rc << endl;
}

	Tip
	Error codes are listed in the Error Handling section of the API Reference. The same error codes are used in both the C and C++ APIs.

Various features of ITTIA DB SQL™ can be configured dynamically at run-time. Changing these configurations in relation to available hardware and data access patterns can provide optimized performance.

The following options can be set by passing a LibraryConfig object to the Database::initialize() function:

The following options can be set in the StorageMode object before opening or creating a database:

See the API Reference manual for more details on these and other settings.

Database design, like any software design, requires careful attention. When developing a database schema, a designer must consider how the data is going to be accessed to provide efficient access for the most common operations. Techniques for designing an optimal schema are beyond the scope of this document but ITTIA engineers will be happy to assist you.

An ITTIA DB SQL™ database is a collection of tables which stores application data as rows of data values. A table is defined by its list of columns, which specifies the kind of data that is to be stored. Indexes can be defined to speed up searches for a specific row or to retrieve a range of values in sorted order.

A table can have many indexes defined, and an index can be created on more than one column. Relationships between tables can be expressed by defining a unique index on one table and including the same data values in another table.

ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.

Columns are created by calling a member function of the FieldDescSet class, as described in Section 5.2.3, “Defining the Database Schema”. Data stored in a column is accessed by binding a field class to a cursor, as described in Section 5.3, “Database Access”.

The database schema is defined programmatically by creating tables. Tables can be created at any time, and can be modified even after data has been entered into them.

	Note
	Schema updates cannot be performed inside a transaction. See Transactions.

5.2.3.1. Tables, Fields and Indexes

To create a table, first describe the table's fields and indexes using FieldDescSet and IndexDescSet objects. Then pass these objects to Database::create_table().

db::Database db;
db::FieldDescSet fields;
db::IndexDescSet indexes;
int rc;

// ...
// Create the database
// ...

fields.add_uint("id");
fields.add_string("name");
fields.add_uint("ring_id", sizeof(db_uint), true);

indexes.add_index("id-index", db::DB_PRIMARY)
       .add_field("id");

indexes.add_index("name-index", db::DB_MULTISET)
       .add_field("name");

rc = db.create_table("person", fields, indexes);

To create a memory table in a file storage, add a fourth parameter to create_table(): db::DB_MEMORY_TABLE.

ITTIA DB™ supports three types of indexes:

db::DB_MULTISET

The same values can occur in the index multiple times. A multiset index will never prevent a row from being inserted.

db::DB_UNIQUE

The index fields for each row must be unique. The same values cannot occur in more than one row. However, NULL can occur in more than one row because NULL is not a value.

db::DB_PRIMARY

The primary key index is identical to a unique index, except that all fields must use the DB_NOT_NULL flag. Each table can have at most one primary key index.

To modify the schema of an existing table, first use describe_table() to obtain a FieldDescSet and IndexDescSet for the current schema. Modify these objects and then call update_table() to update the schema. Fields are identified by name.

To remove a table from the database, including all data stored in the table, call drop_table().

rc = db.drop_table("person");

	Note
	Calls to create_table(), update_table(), and drop_table() are atomic.

db::FieldDescSet fields;
db::IndexDescSet indexes;
db::ForeignKeyDescSet foreign_keys;

// Foreign key into the "contact" table
fields.add_uint("contact_id");
// The telephone number, stored as a string
fields.add_string("number", 20);
// The type of device
fields.add_uint("type");
fields.add_sint("speed_dial", sizeof(db_sint), true);

indexes.add_index("by_contact_id", db::DB_MULTISET)
       .add_field("contact_id");

foreign_keys.add_foreign_key("contact_ref", "contact", DB_FK_MATCH_SIMPLE,
                             DB_FK_ACTION_RESTRICT, DB_FK_ACTION_RESTRICT)
       .add_field("contact_id", "id");

rc = db.create_table("phone_number", fields, indexes, foreign_keys);

5.2.3.3. Sequence Generators

Sequence generators are used to generate unique, increasing integer values. A sequence generator is created with an initial starting value and will then retrieve successive numbers in the sequence. Each generated number is always greater than all previous numbers produced by the sequence generator. This provides a convenient source for unique identifiers.

	Note
	Sequences may contain gaps, and so should not be used in situations where numbers cannot be skipped, such as matching with sequence numbers pre-printed on paper forms.

To create a sequence, call create_sequence() giving a name for the sequence and its initial value.

rc = db.create_sequence("person-id", 1);

If a sequences is no longer required, it can be dropped from the database.

rc = db.drop_sequence("person-id");

All data access operations must occur inside a transaction. A transaction is a logical group of operations that must all succeed or fail together. Changes made to the database do not become permanent until the transaction is committed. A transaction can also be aborted, which will undo any changes made since the transaction was started.

To start a transaction, call the tx_begin() function.

rc = db.tx_begin();

To commit the changes made during the transaction, call tx_commit().

rc = db.tx_commit();

	Note
	When the commit function returns successfully, any modifications made within the transaction are guaranteed to be completely written to the storage media.

To cancel the changes made during the transaction, call tx_abort().

rc = db.tx_abort();

A table cursor provides direct access to a single table and its indexes. The cursor can be used to read, find, insert, update, or delete one row at a time.

An application can iterate through the table cursor's row set by seeking forward and backward. By default, the row set contains all rows in the table, in no particular order. The row set can also be filtered and/or sorted.

To open a cursor, create a Table object and call the open() function.

db::Table person;
person.open(db, "person");

When you are done with the cursor, call close() to free resources. The cursor will close itself automatically when it is destroyed, but it is best to release the cursor as soon as it is no longer needed.

person.close();

5.3.2.1. Inserting Data

Before inserting data, it is often useful to obtain a unique value from a sequence generator.

db::Sequence id_sequence;
db_uint id = 0;

id_sequence.open(db, "person-id");
id_sequence.get_next_value(id);

To insert data, first put the table in insert mode by calling insert(), then assign values to each field using the cursor's [] operator. These values are stored in a temporary buffer. Finally, call post() to actually insert the new row. post() copies the buffer into the table but does not commit the transaction.

person.insert();
person["id"] = id;
person["name"] = db::String("Bob");
person["ring_id"] = 7;
rc = person.post();

If, after calling insert() and possibly assigning values to the fields, you do not want to insert the row into the database, call the cancel() function instead of post() to clear the buffer.

	Note
	Data inserted into the database will not be persistent until the current transaction is committed. See Transactions.

person.set_sort_order("name-index");

for (person.seek_first(); !person.is_eof(); person.seek_next()) {
    // Perform operation on current row
}

5.3.2.3. Searching a Table Cursor

To filter the row set, call begin_filter(), assign values for each field that should be compared, then call apply_filter().

contact.begin_filter(db::DB_SEEK_EQUAL);
person["name"] = db::String("Bob");
rc = contact.apply_filters();

After the filter is applied, the row set is restricted to contain only rows that satisfy the filter conditions. Each field is compared separately and all conditions must be satisfied. The cursor is positioned on the first matching row.

By default, the filter is applied by scanning the table until a matching row is found. To improve performance, set an index with set_sort_order() before the filter is applied. Select an index that begins with the filtered fields.

person.set_sort_order("name-index");

To restore the row set to all rows in the table, call remove_filters().

	Caution
	The set_sort_order() function also removes all filters. Always select the index before adding filters or sorting a table cursor.

The begin_seek() and apply_seek() functions can also be used to search a table using an index. Unlike filters, the seek functions do not restrict the row set. Instead, the cursor is positioned on the first matching row.

To perform an index seek, set the index with set_sort_order(), Then call begin_seek() to enter seek mode, assign search criteria to each field used by the index, and call apply_seek().

person.begin_seek(db::DB_SEEK_EQUAL);
person["name"] = db::String("Bob");
rc = person.apply_seek();

The fields used as search criteria must be fields of the sort index. Furthermore, if the index contains multiple fields, the search criteria must be some or all of the first fields in the index. For example, if an index has three fields, the search criteria can be either the first field in the index, the first two fields, or all three fields. Each field is compared from left to right in index order until a non-equal comparison is reached. These functions cannot be used if an index has not been set.

person.set_sort_order("name-index");

db::IndexFieldSet sort_fields;
sort_fields.add("name");
contact.sort(sort_fields);

db_uint id = person["id"].as_int();

db::String name = person["name"].as_string();
db_uint ring_id = person["ring_id"].as_int();

cout << "Id: " << (long) id << endl;
cout << "Name: " << name.c_str() << endl;
cout << "Ring tone id: " << (int) ring_id << endl;

5.3.2.6. Updating Data

In addition to accessing data, you can also update the current row. Call edit() to enter edit mode, assign new values to each field that should be changed, and call post() to update the row.

person.edit();
person["name"] = db::String("Sue");
person["ring_id"] = 3;
rc = person.post();

	Note
	As with inserts, modifications to the database will not be persistent until the current transaction is committed. See Transactions.

5.3.2.7. Deleting the Row

To delete the current row, call remove().

rc = person.remove();

	Note
	The row will not be permanently removed from the database until the current transaction is committed. See Transactions.

db::Table t;

... // Open t and position on an existing row. */

FILE *picture_file;
picture_file = fopen("read.png", "rb");

const db_len_t data_size = 1024;
char data[data_size];

int picture_field = t.find_field("picture");
int num_chunks = bytes_read = 0;

while((bytes_read = fread(data, 1, data_size, picture_file)) > 0)
{
    t.write_blob (picture_field, data_size * num_chunks, data, bytes_read);
    num_chunks++;
}

fclose(picture_file);

db::Table t;

... // Open t and position on an existing row. */

FILE *picture_file;
picture_file = fopen("write.png", "wb");

const db_len_t data_size = 1024;
char data[data_size];

int offset, bytes_read;
int picture_field = t.find_field("picture");
db_len_t blob_size = t.get_blob_size(picture_field);


for(offset = 0; offset < blob_size; offset += data_size)
{
    bytes_read = contact.read_blob(picture_field, offset, data, data_size);
    fwrite(data, bytes_read, 1, picture_file);
}

fclose(picture_file);

Structured Query Language, or SQL, is a standard database interface that uses query strings to access and modify data. For the complete description of the SQL dialect used by ITTIA DB SQL™, see Chapter 4, SQL.

To execute an SQL statement from the ITTIA DB SQL™ C++ API, create an instance of the db::Query class. Then use the db::Query object to perform the following steps:

Steps 2 and 4 can be omitted depending on the nature of the SQL statement. For example, INSERT statements do not have a result set and thus do not use step 4. Schema definition statements, such as CREATE TABLE, can skip step 2 because they do not accept parameters.

	Important
	Use parameters whenever possible. Parameters are faster and more secure than embedding data directly in an SQL statement, for example using sprintf.

To execute a simple query with no parameters, call db::Query::exec_direct(), which combines the prepare and execute steps.

db::Query q;
int rc = q.exec_direct(db, "create table some_table (column_a integer, column_b integer)");

Alternately, these steps can be performed separately with the prepare() and execute() functions.

db::Query q;
int rc;
rc = q.prepare(db, "select column_a, column_b from some_table");
rc = q.execute();

If the query is a SELECT statement, a result set will be available after the query is executed. The result set is a read-only collection of rows with the same structure as a table. The result set is only sorted if the SQL statement contains an ORDER BY clause.

The db::Query class implements the db::Cursor interface, which is used to iterate over rows in a query result set or table. To iterate over the result set, use seek_first() to position on the first row, seek_next() to advance to the next row, and check is_eof() to determine when all rows have been processed. An SQL query can only be traversed forward and cannot be reset to the first row after seek_next() has been called.

Each row in the result set contains one or more fields that correspond to the columns and expressions in the SELECT list. Fields are identified by the order that they appear in the SELECT list, starting from zero.

To access the value of a field, first locate the db::Field class listed in Table 5.1, “C++ Column Types” that corresponds to the column's data type. After the query has been executed, construct an instance of that class using the db::Query object and the position of the field in the select list. For each row, cast the db::Field to an appropriate C++ data type to access the value. Example 5.1, “SQL Select Example in C++” illustrates this process.

db::Database db = ...;
db::Query q;
char  *cmd;

cmd = "select id, name "
      "  from contact "
      "  order by name ";

if  (DB_SUCCESS(q.exec_direct(db, cmd))) {
    db::IntegerField id  (q, 0);
    db::WStringField name(q, 1);

    for (q.seek_first(); !q.is_eof(); q.seek_next()) {
        std::wcout << (long) id << '\t';
        std::wcout << WString(name).c_str() << std::endl;
    }
}

If a query uses parameters, as described in Section 4.1.4, “SQL Query Parameters”, values can be assigned to each parameter after the query has been prepared but before it is executed. Use the param() function to assign a value to each parameter. The param() function returns a db::Field that can be assigned a value directly using the assignment operator, or set to NULL with the set_null() function. Each parameter is NULL until a value is assigned.

To execute the query again with different parameter values, first call unexecute() to return the query to a prepared state, then modify the parameters as before. Example 5.1, “SQL Select Example in C++” shows how to use parameters to execute a single query multiple times. Each parameter's previous value will carry over to the next execution unless it is explicitly assigned a new value or set to NULL.

db::Database db = ...;
const wchar_t *name = L"My Name";
db_uint ring_id = 6;

db::Query q;

q.prepare(db,
    "insert into contact (id, name, ring_id) "
    "  values (next value for contact_id, ?, ?) ");
q.param(0) = name;
q.param(1) = ring_id;
q.execute();
q.unexecute();

q.param(0) = L"Your Name";
q.param(1) = 3;
q.execute();
q.unexecute();

Before calling any other API functions, the library must be initialized. To initialize the library, call db_init_ex() with the current API version:

db_init_ex(DB_API_VER, NULL);

When the API is no longer needed, call db_done_ex() to free all resources used by the library:

db_done_ex(NULL);

Additional configuration for the ITTIA DB library can be set using the second argument to db_init_ex(). Options include lock manager configuration and access to the built-in memory allocator, which restricts the runtime memory footprint to preallocated segments.

To use ITTIA DB SQL™, include the header ittia/db.h.

#include <ittia/db.h>

A database is managed by a db_t handle. To create a database for the first time, use the db_create_file_storage() function.

db_t hdb = db_create_file_storage("file.db", NULL);

if (hdb == NULL)
     fprintf(stderr, "Error creating database: %d\n", get_db_error());

	Caution
	If the file already exists, db_create_file_storage() will overwrite it.

To open an existing database, use the db_open_file_storage() function. If the file does not exist, db_open_memory_storage() will return a NULL handle and the error DB_ENOENT will be set. If the file exists but cannot be opened for another reason, a different error code will be set.

db_t hdb = db_open_file_storage("file.db", NULL);

if (hdb == NULL)
     fprintf(stderr, "Error opening database: %d\n", get_db_error());

When finished with the database, call db_shutdown() to close the connection.

db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);

The following example creates a memory storage named memory.db using db_create_memory_storage().

db_memory_storage_config_t config;
db_memory_storage_config_init(&config);

config.memory_storage_size = 4 * 1024 * 1024;
hdb = db_create_memory_storage("memory.db", &config);

db_memory_storage_config_destroy(&config);

When a memory storage is created, the application must specify the size of the memory storage. This is the number of bytes that will be allocated for tables and indexes. The size of the database cannot exceed this amount.

An application can create multiple storages in memory if each storage has a different name. Other threads and clients can connect to an existing memory storage using db_open_memory_storage().

hdb = db_open_memory_storage("memory.db", NULL);

The memory_storage_size parameter is ignored when opening an existing memory storage. If another thread or client has already created the memory storage, the existing size will be used. Otherwise, db_open_memory_storage() will return a NULL handle and the error DB_ENOENT will be set.

When finished with the database, call db_shutdown() to close the connection. When the last connection is closed, the storage is destroyed.

db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);

To connect to a database containing tables both in memory and on disk, set the memory_storage_size parameter before creating or opening a file storage. The following example creates a new file storage, and also opens an existing file storage, both with support for memory tables.

db_file_storage_config_t config;
db_file_storage_config_init(&config);

config.memory_storage_size = 4 * 1024 * 1024;
hdb = db_create_file_storage("file.db", &config);
hdb = db_open_file_storage("existing_file.db", &config);

db_file_storage_config_destroy(&config);

The memory storage size must be specified when opening an existing file storage. If necessary, an empty memory storage will be allocated.

When all connections to a hybrid database are closed, memory tables will not be dropped, but all memory tables will be empty the next time the database is opened.

Many errors can occur during a call to a C API function. Whether or not an error has occurred is indicated by the return value of the function. For functions that return a handle type, such as db_t, db_cursor_t or db_row_t, a value of NULL is used to indicate an error. For functions that return a value, such as a number or flags, a special value is reserved for errors, often -1 (DB_WTIME_FAIL, DB_LEN_FAIL). Any function that would not otherwise return a value returns a db_result_t, which can be either DB_OK (1) or DB_FAIL (0).

When an error occurs, an error code is set that indicates the kind of error. The error code can be retrieved by calling get_db_error(). Negative values indicate an error. Positive values indicate a warning.

When ITTIA DB SQL™ is configured for multi-user access, get_db_error() is thread-safe. It will only report errors in API functions that have been called from the same thread.

	Note
	After handling an error, call clear_db_error() to clear the current error code. The current error code is not cleared when an API function completes successfully.

	Tip
	Error codes are listed in the Error Handling section of the API Reference. The same error codes are used in both the C and C++ APIs.

Several data structures in the C API can be instantiated statically at compile-time. These structures are db_oid_t, db_indexdef_t and db_tabledef_t. To initialize these data structures, use the DB_ALLOC_INITIALIZER() macro as the value for the db_alloc member. In the case of db_oid_t, use DB_OID_INITIALIZER() to initialize a new variable.

ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.

To select the type of a column, set the field_type of a db_fielddef_t structure to the schema identifier listed in Table 6.1, “C Column Types” when a table is created as described in Section 6.2.2, “Tables, Fields and Indexes”. To access data stored in a column, use the data type identifier to bind the column to a variable of the listed C data type. See Section 6.3.3, “Rows” for more information about binding a column in a row to a variable.

Tip

Column types in the same category can be bound to the C data type of any other column type in the same category. For example, a column of type DB_COLTYPE_SINT8 can be bound to a variable of type int32_t with the DB_VARTYPE_SINT32 identifier. Or a column of type DB_COLTYPE_UTF8STR can be bound to a variable of type db_utf16_t with the DB_VARTYPE_UTF16STR identifier. Data in the variable will be automatically converted to the database format. Data can be lost when converting to a smaller numeric format. Data is not lost when converting between Unicode string types, provided that the maximum string length is not exceeded.

To create a table, first describe the table's fields as arrays of db_fielddef_t. Describe indexes as arrays of db_indexfield_t and db_index_def_t. Then call db_create_table() and db_create_index() to create the table and its indexes.

#define MAX_CONTACT_NAME  50
#define MAX_FILE_NAME     50

static db_fielddef_t contact_fields[] =
{
#define CONTACT_ID              0   
#define CONTACT_NAME            1
#define CONTACT_RING_ID         2
#define CONTACT_PICTURE_NAME    3
#define CONTACT_PICTURE         4
    { CONTACT_ID,           "id",           DB_COLTYPE_UINT64,   0,                0, DB_NOT_NULL, 0 },
    { CONTACT_NAME,         "name",         DB_COLTYPE_WCHARSTR, MAX_CONTACT_NAME, 0, DB_NOT_NULL, 0 },
    { CONTACT_RING_ID,      "ring_id",      DB_COLTYPE_UINT64,   0,                0, DB_NULLABLE, 0 },
    { CONTACT_PICTURE_NAME, "picture_name", DB_COLTYPE_ANSISTR,  MAX_FILE_NAME,    0, DB_NULLABLE, 0 },
    { CONTACT_PICTURE,      "picture",      DB_COLTYPE_BLOB,     0,                0, DB_NULLABLE, 0 }
};

Next define the table as db_tabledef_t using the field definitions created above.

#define CONTACT_TABLE "contact"
db_tabledef_t contact_table = 
{
    DB_ALLOC_INITIALIZER(),
    DB_TABLETYPE_DEFAULT,
    CONTACT_TABLE,
    5, /* Number of fields in the table. */
    contact_fields
};

In most cases, use the table type DB_TABLETYPE_DEFAULT. To create a memory table in a file storage, use DB_TABLETYPE_MEMORY instead.

Finally, create the table by calling db_create_table().

db_result_t rc;

rc = db_create_table(hdb, contact_table->table_name, &contact_table, 0);

if (rc == DB_FAIL)
     fprintf(stderr, "Unable to create table\n");

ITTIA DB™ supports three types of indexes:

First, describe which fields will be include in the index with an array of db_indexfield_t.

static db_indexfield_t contact_by_id_fields[] = 
{
    { CONTACT_ID, 0 }
};

Define the attributes of the index as db_indexdef_t.

#define CONTACT_BY_ID "by_id"
static db_indexdef_t contact_index =
{
    DB_ALLOC_INITIALIZER(),
    DB_INDEXTYPE_DEFAULT,
    CONTACT_BY_ID,
    DB_PRIMARY_INDEX,
    1,  /* Number of fields in the index. */
    contact_by_id_fields
};

Create the indexes by calling db_create_index() for each index.

rc = db_create_index(hdb, contact_table->table_name, contact_index->index_name, &contact_index);

if (rc == DB_FAIL)
     fprintf(stderr, "Unable to create index\n");

A foreign key defines a relationship between two tables. Foreign keys are used to enforce the existance of related rows by referencing columns in another table.

db_foreign_key_def_t phone_number_foreign_keys[] = {
    "CONTACT_REF",
    /* Reference the contact table. */
    CONTACT_TABLE,
    /* Match type only applies to compound keys (on more than one column.) */
    DB_FK_MATCH_SIMPLE,
    /* Protect referenced rows from update or delete. */
    DB_FK_ACTION_RESTRICT, /* update_rule */
    DB_FK_ACTION_RESTRICT, /* delete_rule */
    /* Checks cannot be deferred until commit. */
    DB_FK_NOT_DEFERRABLE,
    /* Checks occur immediately by default. */
    DB_FK_CHECK_IMMEDIATE,
    /* Reference the ID field in the contact table. */
    1,
    {
        { PHONE_CONTACT_ID, CONTACT_ID },    
    }
};

If the foreign key uses more than one field, at least one of which can be null, then select an appropriate match option. If not, both match options have equivalent behavior.

When a row is updated or deleted in the referenced table, the database will act according to the update_rule or delete_rule, respectively, in the foreign key.

Foreign keys are always checked immediately whenever a row is inserted into the referencing table, deleted in the referenced table, or updated in either table. Always use DB_FK_NOT_DEFERRABLE and DB_FK_CHECK_IMMEDIATE. Other options are not currently supported.

Call db_create_foreign_key() to add each foreign key to the referencing table.

rc = db_create_foreign_key(hdb, "phone_number", &phone_number_foreign_keys);

if (rc == DB_FAIL)
     fprintf(stderr, "Unable to create foreign key\n");

Sequence generators are used to generate unique, increasing integer values. A sequence generator is created with an initial starting value and will then retrieve successive numbers in the sequence. Each generated number is always greater than all previous numbers produced by the sequence generator. This provides a convenient source for unique identifiers.

	Note
	Sequences may contain gaps, and so should not be used in situations where numbers cannot be skipped, such as matching with sequence numbers pre-printed on paper forms.

To create a sequence define a db_seqdef_t with a name and initial value then call the db_create_sequence() function.

#define CONTACT_ID_SEQUENCE    "contact_id"
db_seqdef_t contact_id_sequence = {
    CONTACT_ID_SEQUENCE,
    {{ 1, 0}}
};

rc = db_create_sequence(hdb, contact_id_sequence->name, contact_id_sequence);

if (rc == DB_FAIL)
       fprintf(stderr, "Unable to create sequence %s\n", contact_id_sequence->seq_name);

All data access operations must occur inside a transaction. A transaction is a logical group of operations that must all succeed or fail together. Changes made to the database do not become permanent until the transaction is committed. A transaction can also be aborted, which will undo any changes made since the transaction was started.

To start a transaction, call the db_begin_tx() function.

db_begin_tx(hdb, DB_DEFAULT_ISOLATION | DB_LOCK_SHARED);

	Note
	For Isolation mode and Lock mode parameters see Isolation Levels and Locking.

To commit the changes made during the transaction, call db_commit_tx(). The completion parameter determines how quickly the changes made in this transaction will be flushed from the database cache to permanent storage.

db_commit_tx(hdb, DB_FORCED_COMPLETION);

	Note
	When the commit function returns successfully, any modifications made within the transaction are guaranteed to be completely written to the storage media.

To cancel the changes made during the transaction, call db_abort_tx().

db_abort_tx(hdb, DB_FORCED_COMPLETION);

A cursor is an object that provides access to rows and fields in the database. A cursor often has a row set and maintains a position in the row set.

There are two types of cursors: table cursors and SQL query cursors. A table cursor corresponds to a specific table and has a row set containing the table's rows and fields. An SQL query cursor may have a row set, depending on the type of query, and can accept a row of parameters when executed.

A cursor is created by calling a function that returns a cursor handle, of type db_cursor_t. The cursor is associated with the database connection that was used to create it. Regardless of how the cursor was created, it is closed by calling db_close_cursor(), or when the database connection is closed.

db_t db = /* ... */;
db_cursor_t cursor = db_open_table_cursor(hdb, "table_name", NULL);

/* ... */

db_close_cursor(cursor);

Multiple cursors can be used simultaneously with the same table or tables. An open cursor does not prevent access to any row, including the cursor's current row. However, the schema for any tables associated with the cursor cannot be changed while the cursor is open.

	Tip
	Transactions can be started, committed, and aborted while a cursor is open. Between transactions, the cursor's current row may be deleted or updated by another connection to the database. During a serializable or repeatable read transaction, only cursors created with the same database connection can modify or remove the cursor's current row.

Data is stored and retrieved through rows. A row is a set of temporary buffers for storing the contents of database fields. Each field in a row can be stored in one of three locations: a managed internal buffer, a local variable referenced by memory address, or a member of a data structure. A row variable is independent of any specific row in the database and can be re-used for many insert, fetch and update operations.

A row is created by calling either db_alloc_row() or db_alloc_cursor_row(). A row can only be used to access fields that it is aware of and will not set or retrieve values for any fields that it is not. A row variable can be created that is initially bound to no database fields:

db_row_t contact_row;
contact_row = db_alloc_row(NULL, 2);

This row should then be dynamically bound to two fields by calling db_bind_field().

The data in a row can be read and written by calling db_get_field_*() and db_set_field_*() functions. For safe access, use db_get_field_data() and db_set_field_data() to copy values to or from each field in the row. The size of the field can be determined by calling db_get_field_size().

	Tip
	To obtain a direct pointer to the buffer that is used by the row, call db_get_field_buffer(). When writing to this pointer, be careful not to overrun the size of the buffer.

When a row is no longer needed, it must be freed:

db_free_row(contact_row);

6.3.3.1. Managed Fields

Figure 6.1. Managed Field Binding

The first method, a managed buffer, is most suitable when the exact data type of a field is not known at compile time. It allows the value to be extracted as a void pointer and cast to the appropriate type at run time. To create a row with managed buffers for all fields in a cursor, call db_alloc_cursor_row():

db_row_t contact_row;
contact_row = db_alloc_cursor_row( contact_cursor );

6.3.3.2. Absolute Bound Fields

Figure 6.2. Absolute Field Binding

When the data type of a field is known at compile time, it can be bound directly to a local variable by providing the memory address and size of the variable in bytes. When data is retrieved from the database, it will be stored in the bound variables. When data is stored to the database, it will be read from these same variables. To create a row that has fields bound to memory addresses, call db_alloc_row() with an array of binding definitions:

uint32_t id;
db_ucs2_t name[50];

const db_bind_t row_def[] =
{
    {
        CONTACT_ID,                   /* field_no   */
        DB_VARTYPE_UINT32,            /* data_type  */
        DB_BIND_ADDRESS(&id),         /* data_ptr   */
        sizeof(id),                   /* data_size  */
        DB_BIND_ADDRESS(NULL),        /* data_ind   */
        DB_BIND_ABSOLUTE,             /* data_flags */
    },
    {
        CONTACT_NAME,                 /* field_no   */
        DB_VARTYPE_UTF16STR,          /* data_type  */
        DB_BIND_ADDRESS(name),        /* data_ptr   */
        sizeof(name),                 /* data_size  */
        DB_BIND_ADDRESS(NULL),        /* data_ind   */
        DB_BIND_ABSOLUTE,             /* data_flags */
    }
};

contact_row = db_alloc_row(row_def, 2);

Instead of using internal buffers, the row will use the variables or memory addresses that have been provided for these fields. These fields can still be accessed using db_get_field_*() and db_set_field_*() functions if necessary, but it is generally more convenient to use the local variables to which they are bound directly.

	Tip
	The data_ind member of db_bind_t is used to bind a field length indicator for the given field. The field length indicator provides additional information about the value stored in the database, such as string length and NULL state. In an absolute binding, set data_ind to the address of a db_len_t variable to access the field length indicator.

Fields can be bound dynamically using db_bind_field(). In the following example, db_bind_field() is used to bind the variable id to two different row objects.

db_row_t simple_row, contact_row;
uint32_t id = 0;
db_len_t id_ind = DB_FIELD_NULL;

static db_bind_t bind_def =
{
    CONTACT_ID,                   /* field_no   */
    DB_VARTYPE_UINT32,            /* data_type  */
    DB_BIND_ADDRESS(&id),         /* data_ptr   */
    sizeof(id),                   /* data_size  */
    DB_BIND_ADDRESS(&id_ind),     /* data_ind   */
    DB_BIND_ABSOLUTE,             /* data_flags */
};

simple_row = db_alloc_row(NULL, 1);
db_bind_field(simple_row, &bind_def);

contact_row = db_alloc_cursor_row( contact_cursor );
db_bind_field(contact_row, &bind_def);

	Tip
	db_bind_field() will override any previous bindings for that field. In this example, managed bindings are first created for all cursor fields. The binding that was created for CONTACT_ID is then replaced by an absolute binding to the id variable. In this way, a row can contain a mixture of different bind methods.

Binding to the same variable in two different rows is an efficient way to copy fields between tables. Data can be fetched from one table using the first binding and then stored directly to another table using the second binding without making any additional copies.

6.3.3.3. Relative Bound Fields

Figure 6.3. Relative Field Bindings

A row can also be bound to a data structure to provide convenient access using a C struct.

A relative binding is defined in much the same way as for address bindings, but instead of using an absolute address, an offset into the data structure is used. The DB_BIND_OFFSET() and DB_BIND_SIZE() macros can be used to determine the offset and size of a struct member.

#define MAX_PHONE_NUMBER 20
typedef struct phone
{
    contactid_t contact_id;
    db_ansi_t   number[MAX_PHONE_NUMBER + 1];
    uint32_t    type;
    int32_t     speed_dial;

} phone_t;

static const db_bind_t phone_number_binds[] =
{
    { PHONE_CONTACT_ID,                       /* field_no  */
        DB_VARTYPE_UINT32,                    /* data_type */
        DB_BIND_OFFSET(phone_t, contact_id),  /* data_ptr  */
        DB_BIND_SIZE(phone_t, contact_id),    /* data_size */
        -1,                                   /* data_ind  */
        DB_BIND_RELATIVE},
    { PHONE_NUMBER,
        DB_VARTYPE_ANSISTR,
        DB_BIND_OFFSET(phone_t, number),
        DB_BIND_SIZE(phone_t, number),
        -1,
        DB_BIND_RELATIVE},
    { PHONE_TYPE,
        DB_VARTYPE_UINT32,
        DB_BIND_OFFSET(phone_t, type),
        DB_BIND_SIZE(phone_t, type),
        -1,
        DB_BIND_RELATIVE},
    { PHONE_SPEED_DIAL,
        DB_VARTYPE_SINT32,
        DB_BIND_OFFSET(phone_t, speed_dial),
        DB_BIND_SIZE(phone_t, speed_dial),
        -1,
        DB_BIND_RELATIVE},
};

phone_t phone;
db_row_t phone_row;

phone_row = db_alloc_row(phone_number_binds, 4);
db_fetch(phone_cursor, phone_row, &phone);

The functions db_fetch(), db_insert(), and db_update() each accept a uobject parameter that is only required for relative bound fields. This parameter is the address of a data structure instance that corresponds to the relative bindings.

	Note
	If relative binding is not used in the row, the parameter of type db_object_t should always be NULL.

	Tip
	Relative bindings can also be used with an untyped memory buffer instead of a C data structure by setting data_ptr to the byte offset of each field.

To traverse a cursor with a row set, use db_seek_first(), db_seek_next(), and db_eof() to position on each row of data.

	Tip
	To check whether a cursor has a row set, call db_is_browsable(). A cursor is browsable if and only if it has a row set.

To read the current row's fields, use the db_fetch() function and a row object. Data is copied from the database into the row object. Type conversion and locking, if applicable, occur during the fetch operation only.

db_cursor_t cursor = /* ... */;
db_row_t row = /* ... */;

for (db_seek_first(cursor); !db_eof(cursor); db_seek_next(cursor))
{
    db_fetch(cursor, row, NULL);
    /* ... */
}

A table cursor is used to position on a specific row in a table and to search using an index. An index is selected when opening the cursor to determine which fields to sort by when traversing the table and which fields are available for searching. If the index is NULL, the table is not sorted and rows are ordered for most efficient access.

db_cursor_t contact_cursor;
db_table_cursor_t p;

db_table_cursor_init(&p);
p.index = CONTACT_BY_ID;
p.flags = DB_CAN_MODIFY | DB_LOCK_EXCLUSIVE;
contact_cursor = db_open_table_cursor(hdb, CONTACT_TABLE, &p);
db_table_cursor_destroy(&p);

Alternately, the cursor options can be created with a static definition:

db_cursor_t contact_cursor;
static const db_table_cursor_t contact_cursor_def =
{
    CONTACT_BY_NAME, /* index */
    DB_SCAN_FORWARD | DB_LOCK_SHARED, /* flags */
    0 /* sort_buffer_size */
};

contact_cursor = db_open_table_cursor(hdb, CONTACT_TABLE, &contact_cursor_def );

Most cursors should use at least on of the following flags:

If the table cursor definition is NULL, the cursor is created with all of the above capabilities and no index.

A cursor should be closed when it is no longer needed to free resources:

db_close_cursor(contact_cursor);

6.3.5.1. Inserting Data

To insert data using a table cursor, assign values to a row, then call db_insert():

uint32_t id = 4;
db_ucs2_t name[50] = "Bob";

const db_bind_t row_def[] =
{
    {
        CONTACT_ID,                   /* field_no   */
        DB_VARTYPE_UINT32,            /* data_type  */
        DB_BIND_ADDRESS(&id),         /* data_ptr   */
        sizeof(id),                   /* data_size  */
        DB_BIND_ADDRESS(NULL),        /* data_ind   */
        DB_BIND_ABSOLUTE,             /* data_flags */
    },
    {
        CONTACT_NAME,                 /* field_no   */
        DB_VARTYPE_UTF16STR,          /* data_type  */
        DB_BIND_ADDRESS(name),        /* data_ptr   */
        sizeof(name),                 /* data_size  */
        DB_BIND_ADDRESS(NULL),        /* data_ind   */
        DB_BIND_ABSOLUTE,             /* data_flags */
    }
};

contact_row = db_alloc_row(row_def, 2);

db_insert(contact_cursor, contact_row, NULL);

db_free_row(r);

	Note
	Data inserted into the database will not be persistent until the current transaction is committed. See Transactions.

After the row is inserted, the cursor's position is undefined. To position the cursor on the new row after it is inserted, use the flag DB_INSERT_SEEK_NEW:

db_insert(contact_cursor, contact_row, DB_INSERT_SEEK_NEW);

db_cursor_t cursor = /* ... */;
db_row_t search_row = /* ... */;

db_add_filter(cursor, DB_SEEK_EQUAL, search_row, NULL, NULL, 0);
if (db_seek_first(cursor) == DB_SUCCESS) {
    /* The cursor is positioned on a row in the database that
     * is equal to search_row for all fields in search_row. */
}

6.3.5.3. Sorting a Table Cursor

To sort a table cursor using any of the table's fields, use the db_sort() function.

static const db_indexfield_t sort_by_name[] = {
    { CONTACT_NAME, 0 }
};

db_sort(contact_cursor, sort_by_name, DB_ARRAY_DIM(sort_by_name));

The db_sort function may fetch all rows in the table cursor's current row set to perform the sort. If the cursor's index already sorts the table by the requested fields, db_sort will return immediately.

	Tip
	If applying filters and sorting a table cursor, call db_sort() between db_add_filter() and db_seek_first() for best performance.

When the table cursor is created, the sort_buffer_size attribute determines how much memory can be allocated for sorting. If the buffer is too small, a partial sort will be performed and an error is returned.

6.3.5.4. Updating a Table Cursor

In addition to accessing data, you can also update the current row. Update the values bound to the row and call db_update().

db_update(contact_cursor, contact_row, NULL);

	Note
	As with inserts, modifications to the database will not be persistent until the current transaction is committed. See Transactions.

6.3.5.5. Deleting a Table Row

To delete the current row from a table cursor, call db_delete().

db_delete(contact_cursor);

	Note
	The row will not be permanently removed from the database until the current transaction is committed. See Transactions.

#include <ittia/db.h>
#include "db_helper.h"

db_cursor_t t;

... /* Open t and position on an existing row. */

db_blob_t blob;

/* Bind "blob" to field number 3. */
const db_bind_t row_def[] =
{
    DB_BIND_VAR(3, DB_VARTYPE_BLOB, blob),
};

/* Allocate a row containing the BLOB field binding. */
db_row_t r = db_alloc_row(row_def, DIM(row_def));

/* Clear BLOB settings (optional). */
memset(&blob, 0, sizeof(blob));

FILE *picture_file = fopen("read.png", "rb");

/* Allocate buffer */
#define DATA_SIZE 1024
char buffer[DATA_SIZE];

/* Prepare BLOB variables */
int bytes_read = 0;
picture.chunk_data = (void*)buffer;

/* Store picture into BLOB field */
while((bytes_read = fread(buffer, 1, DATA_SIZE, picture_file)) > 0)
{
    blob.offset = DATA_SIZE * num_chunks;
    blob.chunk_size = bytes_read;
    db_update(t, r, NULL);
}
fclose(picture_file);

FILE *picture_file = fopen("write.png", "wb")

/* Allocate buffer */
#define DATA_SIZE 1024
char buffer[DATA_SIZE];

/* fetch file_name and blob.blob_size */
db_fetch(contact_cursor, contact_row, NULL);

/* Prepare BLOB variables */
blob.chunk_data = buffer;
blob.chunk_size = DATA_SIZE;

/* Export file from BLOB to disk */
for(blob.offset = 0; blob.offset < blob.blob_size; blob.offset += DATA_SIZE)
{
    db_fetch(t, r, NULL);
    fwrite(blob.chunk_data, blob.actual_size, 1, picture_file);
}

fclose(picture_file);

Structured Query Language, or SQL, is a standard database interface that uses query strings to access and modify data. For the complete description of the SQL dialect used by ITTIA DB SQL™, see Chapter 4, SQL.

To execute an SQL statement from the ITTIA DB SQL™ C API, create a query cursor by calling db_prepare_sql_cursor(). The cursor returned by this function can be used to execute the query and read the result set. An application should follow these steps to process an SQL statement:

Steps 2 and 4 can be omitted depending on the nature of the SQL statement. For example, INSERT statements do not have a result set and thus do not use step 4. Schema definition statements, such as CREATE TABLE, can skip step 2 because they do not accept parameters.

	Important
	Use parameters whenever possible. Parameters are faster and more secure than embedding data directly in an SQL statement, for example using sprintf.

The function db_prepare_sql_cursor() parses an SQL statement and returns an SQL cursor. The last parameter is reserved for future use. Always use the value 0 for this parameter.

db_cursor_t sql_cursor;
sql_cursor = db_prepare_sql_cursor(hdb, "select * from some_table", 0);

After the cursor is prepared, the statement can be executed by a call to db_execute(). Executing a statement may modify the database or open a result set that can then be browsed through the cursor.

db_execute(sql_cursor, NULL, NULL);

If the query is a SELECT statement, a result set will be available after the query is executed. The result set is a read-only collection of rows with the same structure as a table. The result set is only sorted if the SQL statement contains an ORDER BY clause.

To iterate over the result set, use db_seek_first() to position on the first row, db_seek_next() to advance to the next row, and check db_is_eof() to determine when all rows have been processed. An SQL query can only be traversed forward and cannot be reset to the first row after db_seek_next() has been called.

Each row in the result set contains one or more fields that correspond to the columns and expressions in the SELECT list. Fields are identified by the order that they appear in the SELECT list, starting from zero.

To access field values in the result set, use db_fetch() to copy data into a C API row, as described in Section 6.3.3, “Rows”.

Because result set is read-only, db_insert(), db_update(), and db_delete() cannot be used on an SQL cursor.

Example 6.4, “Executing a SELECT statement” shows how to execute a SELECT statement and iterate over the results using a managed row.

db_cursor_t     sql_cursor;
int             nfield;
db_fielddef_t   field_def;
db_row_t        row;
int             field_count;

sql_cursor = db_prepare_sql_cursor(hdb, "select * from some_table", 0);

row = db_alloc_cursor_row(sql_cursor);
field_count = db_get_field_count(sql_cursor);

db_execute(sql_cursor);
db_seek_first(sql_cursor);

while ( !db_eof(sql_cursor) ) {
    //------------------------------
    // RECORD LEVEL PROCESSING
    //------------------------------
    db_fetch( sql_cursor, row, NULL );

    for ( nfield = 0; nfield < field_count; nfield++ ) {
        //--------------------------
        // FIELD LEVEL PROCESSING
        //--------------------------
        db_get_field ( sql_cursor, nfield, &field_def );
        if  ( db_is_null(row, nfield) ) {
            //----------------------
            // NULL DATA CHECK
            //----------------------
            continue;
        }
        db_get_field_data(row, nfield, field_def.field_type, &var, sizeof(var));
        .
        .
        .
    }
    db_seek_next(sql_cursor);
}

db_free_row(row);

db_close_cursor(sql_cursor);

Some SQL statements accept one or more parameters. For these queries, a parameter row must be passed as the second parameter to db_execute(). If any fields in the row use relative bindings, an object address must also be passed as the third parameter, otherwise the third parameter can be NULL. The fieldno for each bound field must be set to the number of the parameter. See Section 4.1.4, “SQL Query Parameters” for more information.

An SQL query can be executed multiple times by repeatedly calling db_execute(). The values of the parameter row can be modified each time before the query is executed. If the query has a result set, the result set must be closed by calling db_unexecute() before the cursor can be executed again.

	Tip
	A prepared SQL cursor can be left open and reused later to avoid the overhead of opening the cursor. If the cursor is not closed immediately after use, it is a good practice to call db_unexecute() to free any resources held by the query.

The serializable isolation level provides full isolation. The database behaves as though all transactions occur sequentially, one after another.

According to SQL standard, the serializable isolation level guarantees absence of the following phenomena:

The repeatable read isolation level ensures that all data accessed by a transaction is not modified by other transactions until after the transaction is committed. This means that reads are repeatable: every time you read a row, it contains the same data. If you write data to a row, or create a new row within a transaction, it will contain the same data until that transaction writes something different there.

Unlike serializable isolation, repeatable read isolation does not prevent phantom reads. A phantom read occurs when a transaction reads a row that was inserted by a later transaction. This can cause the results of a query to change over the life of a transaction. For example, a transaction at the repeatable read isolation level may read all people in a table with age greater than 18. If later the same transaction attempts to read all people of age greater than 21, it may process new entries that were not present in the first query. If this is okay, then using repeatable read isolation is sufficient.

Allows non-repeatable reads. A non-repeatable read happens when a transaction reads changes to data that it has already read. One transaction sees the committed changes made by a later transaction.

Allows dirty reads. A dirty read occurs when a transaction reads uncommitted changes made by a later transaction. The data read from the database may be inconsistent, representing only part of the changes made by the later transaction.

The database provides no isolation at the read uncommitted isolation level.

The replication mode, set independently for each table, determines in which direction changes can be sent. Supported modes are: IN, OUT, and INOUT.

To configure one-way master/slave replication for a table, use OUT mode in the master database and IN mode in the slave database, regardless of which database will establish the connection. Changes will only be replicated if allowed by the replication modes used in both databases. Conflicts may occur under certain configurations, and they will be detected and resolved according to the rules configured in each table.

When replication is enabled, changes to disk tables — row insert, update, or delete — are stored in the log file as replication events. However, replication events are only stored for tables that have OUT or INOUT replication enabled.

Replication does not require that all databases have exactly the same table structure. During an exchange, only the subset of columns that is common to both the source and recipient is used. Data types are automatically converted when necessary. If type conversion is not possible, the source transaction is not applied.

Special columns can be added to replicated tables to help track the origin of each row and identify conflicts. These columns do not necessarily exist in the source database. Instead, values are set using information such as the peer address of the source database.

The peer address column can be used to form a unique key when a table consolidates rows from multiple sources. If the source databases use overlapping primary keys, the peer address column should be added to the primary key in the recipient table.

When the peer address column is used, each row is exchanged only with the peer identified by the column. In this way, a table can be partitioned between peers.

Whenever a transaction that changes a database is committed, all synchronous replication peers configured in that database are updated immediately. The commit operation will not return until all synchronous peers acknowledge receipt of the changes or an error occurs.

A distributed transaction is used protect the integrity of the database and its peers. If an error occurs when updating any peer, the commit operation will fail with an error and all peers will be rolled back. An error will occur if the database is unable to connect to a peer, the connection is interrupted, or a constraint in a peer is violated as a result of the changes.

If a power failure or crash occurs during the commit operation, the application may need to resolve the transaction state of some or all of the databases involved in the distributed transaction.

	Warning
	Parts of the database that were accessed during the transaction will remain locked until the transaction state is resolved. An application that uses synchronous replication should check for pending transactions whenever it opens the first connection to an existing database.

Peers configured in ad hoc mode use asynchronous replication.

For disk tables, transactions are accumulated in the replication log. The log file is periodically rotated to a new file, and old log files are automatically deleted when no longer needed. Log files are retained until all peers have received all replication events contained therein.

The log-based replication approach used by ITTIA DB™ has several advantages over alternative methods:

For memory tables, transactions are accumulated in the memory journal until they have been exchanged with all replication peers.

Replication can be applied to a variety of system architectures. For example, replication can be used to share data between a fleet of devices and a main database on a back-end system. Several different types of replication can be used in this scenario, each applied to a different set of tables:

ITTIA DB™ uses peer-to-peer replication, which can be configured to support these and other replication scenarios.

Figure 8.1. Replication Use Cases

ITTIA DB™ can detect and resolve two types of conflicts: operation conflicts and data conflicts. Both types of conflicts are identified by searching for key values in the recipient database.

Each database that will participate in replication must be assigned a unique replication address. This number is used by replication peers to identify the database.

db_t hdb = db_open_file_storage("example.db", NULL);

/* Set a unique replication address for each database file. */
db_rep_config_t rep_config = { 0 };
rep_config.rep_address = 1;
db_rep_set_config( hdb, &rep_config );

After the replication address is set for a database, log file rotation is enabled. Replication exchanges must be performed periodically to purge old log files.

Changes are replicated by opening peer-to-peer connections to other databases. Each database has a list of peers that it can connect to stored in the database file.

Peers only need to be added to the database that will initiate the peer-to-peer connection, regardless of which direction changes will be sent.

/* Setup ad hoc replication with a peer database. */
db_rep_peerdef_t peer;
db_rep_peerdef_init(&peer);

/* Connect to the peer database using shared memory. */
peer.rep_type = DB_REPTYPE_ADHOC;
peer.peer_address = 2;
strcpy(peer.peer_uri, "idb+shm:///some_peer.db");

/* Create the replication peer. */
db_rep_create_peer(hdb, "some_peer", &peer);

db_rep_peerdef_destroy(&peer);

The replication peer definition is stored in the database file and can be accessed by name. The peer name must be unique within this database only.

The replication address, peer_address, assigned to the peer should be unique across all databases that might participate in the same replication network.

The peer_uri is the location of the peer database in URI format, either a local database file name or a shared database URL. For information on connecting to a remote database, see Section 7.2, “Client-server Shared Access”.

Replication must be enabled for each table that will send or receive changes. The db_rep_table_query function gets the current replication settings for a table, which is disabled by default, and the db_rep_table_set function updates the settings.

Every database that participates in replication must be configured in this way. However, the replication settings will usually vary from one database to another. When changes are exchanged between two databases, only tables and fields with identical names will be shared. Other tables and fields are ignored.

/* Initialize rep_info to default settings. */
db_rep_table_info_t rep_info;
db_rep_table_query(hdb, "example_table", &rep_info);

/* Enable bidirectional replication for this table. */
rep_info.rep_mode = DB_REP_MODE_INOUT;
/* Do not accept conflicting inserts from other peers. */
rep_info.insert_resolve = DB_REP_RESOLVE_REFUSE;
/* Accept all conflicting updates, but only if the row
 * already exists in this database. */
rep_info.update_resolve = DB_REP_RESOLVE_ACCEPT |
                          DB_REP_RESOLVE_EXCL;
/* Accept conflicting deletes only from peers with greater priority. */
rep_info.delete_resolve = DB_REP_RESOLVE_PRIORITY_MIN + 5;

/* Configure special replication columns. */
strcpy(rep_info.peer_column, "peer");
strcpy(rep_info.state_column, "state");
strcpy(rep_info.rid_column, "rid");
strcpy(rep_info.stamp_column, "stamp");

/* Set replication index. */
strcpy(rep_info.rep_index, "some_unique_index");

/* Apply replication settings to a table. */
db_rep_table_set(hdb, "example_table", &rep_info);

The peer_column, state_column, and rid_column fields must either be blank or identify existing columns of type DB_VARTYPE_SINT32 (SQL keyword INTEGER). The stamp_column field must identify a column of type DB_VARTYPE_SINT64 (SQL keyword BIGINT). When a row is received from a peer database, these columns are populated with information about the source of the data.

After replication is configured, changes can be exchanged with an ad hoc peer database by calling db_rep_exchange. Changes are both sent to and received from the peer database.

db_rep_exchange(hdb, "some_peer", 0);

After exchanging rows with a peer, state columns can be checked to determine whether further conflict resolution is necessary before exchanging with other peers.

When row-level locking is used, concurrent connections to both databases are not interrupted. Transactions are isolated in the usual way. However, changes made to either database by other connections during the exchange may not be replicated until a subsequent exchange.

To send the contents of a table to a table of the same name in a replication peer, use db_rep_snapshot with the DB_REP_MODE_OUT flag.

db_rep_snapshot(hdb, "some_table", "some_peer", DB_REP_MODE_OUT);

To receive the contents of a table from a replication peer, use db_rep_snapshot with the DB_REP_MODE_IN flag.

db_rep_snapshot(hdb, "some_table", "some_peer", DB_REP_MODE_IN);

Table snapshots can be created even if the table is not configured for replication. However, special columns will be used if configured.

The type of replication assigned to the peer is ignored. Snapshot replication can even be used with a disabled peer.

If the destination table is not empty, only rows that differ between the two tables are actually copied.

Readers who are interested in a good introduction to database implementation techniques and related issues may enjoy the following books:

There are two general types of locks used on database objects:

There are number of objects being locked during database operations. They are:

Imagine we have an index which contains the sequence of values [1,2,3,4,5]. A transaction deletes the record with key [3], and the index is modified during the operation to exclude the deleted key (i.e. the index modification is not delayed till the commit). At the same time if another transaction scans the same index it would not know if a key was deleted from the index or not, thus causing the phantom of record absence in case the first transaction aborts. It can try to insert a record with the same key as deleted, or update another record to have the same key, which would lead to collision if the first transaction rollbacks. To prevent a collision from occurring, we should lock the modified range, since it contains an uncertain state that is unknown to the other transaction.

To avoid this situation the database locks the next key, i.e. the lock is delegated to the next key in index order, even if the next record has not actually been modified. In such a case the concurrently scanning transaction would see the locked row and would wait for the first transaction to resolve the state by either committing or aborting.

The next key locking technique is actually a range lock, where one lock represents the whole locked range – in our example, after deleting the key [3], we lock the whole range or keys from [2] to [4].

Now we can describe how various operations are performed to cope with the problem:

During insert the database finds an appropriate place to insert and then requests a lock on the next record, assuming that there is a possibility of a key being deleted. As soon as the lock is granted the insert operation is performed, and the next row lock is released.

Key update is done in a way that is similar to the insert operation; it first checks the new key position just like the insert operation does. The only difference is that since an update operation deletes the old key value, it leaves the row that is next to the old key value locked.

Fetch is done as usual, requesting a single lock per record fetched.

Next key locking might cause ‘mysterious’ locks – since there is no difference between a row lock and a range lock this might cause unexpected operation waiting sometimes. In our example, if the concurrent transaction tries to update the row with key [4], which is used as a range lock, it would wait until the first transaction completes.^[1]

The database performs automatic locking on every operation. Sometimes this locking is not enough, so the database allows locking tables and rows manually. Since these locks do not reflect the transaction state, but serve application synchronization, the manual locks are considered to be advisory and are not enforced to obey the rules of 2PL. This means that the application can request and release advisory locks at any time. Moreover the application can request locks which have a life-time that is longer than transaction time, making it possible to guarantee inter-transaction consistency.

For more information on manual locking, see the section on Locking in the C API Reference and the section on Object Ids and Locking in the C++ API Reference.