ITTIA DB
User's Guide

ITTIA DB is a database management system for environments with extremely limited memory and processor resources. A solid database kernel provides reliable data management at optimal speeds, protecting data from power failure and crashes.

ITTIA DB is:

Features include:

ITTIA DB is a cross-platform database management system, designed to work in a variety of environments. In addition to the platforms listed below, ITTIA DB can be easily ported to run on other operating systems and architectures.

This User's Guide provides an introduction to software development with ITTIA DB. Example code from the phonebook example application is used to demonstrate many key concepts and tasks.

Documentation is available in a variety of formats, including:

Also consult the API Reference Manual for detailed information about using the ITTIA DB API.

A database management system provides the most convenient means to store data in a reliable, organized format. This removes the burden of managing data from the application code and provides several key advantages that an application would otherwise not have access to.

Reliable storage is the primary motivation for using a database. Operations on a database are grouped into transactions, which will either succeed (commit) or fail (abort) as a single unit. Because transactions are persistent, data will not become lost or corrupted even after a power failure. After a crash, data is automatically recovered up to the last committed transaction.

	Warning
	A database cannot protect against a corrupted file system or damaged media hardware. Regular backups are the only way to provide full protection against data loss.

A database can also provide powerful tools to help organize and access data. The structure of a database is specified in the database schema, which is used as the basis for navigating the data. Data is stored in tables, which contain a list of typed columns. Keys are defined on certain columns of the table to quickly locate rows based on the data stored in the key column. This fast data access through indexed key fields is another primary advantage of using a database managment system.

Because data is stored in an organized format, it is possible to perform generic queries on the data. This allows existing software tools to be used with the data stored in the database, regardless of what application it was created with. Data can also be stored in a platform-independent format so that database files can be moved between different operating systems and processor architectures.

To prevent data inconsistency, it is important to manage how multiple users modify the database at the same time. A database management system provides concurrency support to ensure that transactions commited by multiple users always appear to complete sequentially. This provides shared access to the database in a safe, efficient way.

In general databases support multiple levels of concurrency. Concurrency refers to the ability of the database to handle multiple simultaneous access to the data stored in the database. The most simple case is that the database supports no concurrency at all. This case is more common in embedded environment where a single process accesses the database sequentially. The databases that support concurrency can be divided into the following categories:

On mobile devices and embedded systems, resource restrictions provide an additional challenge to developers. Execution speed, storage space, and memory are often limited. In embedded environments, known bounds on the size of resources can allow those resources to be used more effectively.

Resources on such devices can also exhibit different behavior than similar devices on a desktop or server system. For example, compact flash memory is a popular form of persistent storage on mobile and embedded devices. Unlike disk-based storage, flash storage has no seek time but slower throughput, and if data is written to the same location too many times, it will wear out.

A database management system, or any embedded application, can take advantage of this knowledge to operate more efficiently.

An ITTIA DB database can be accessed through one of two APIs. The C API provides the lowest level interface to the database and thus the greatest flexibility. The C++ API is easy to use and helpful for introducing the concepts of database access. The C++ API is provided as source code that can be linked with the C API interface to the library.

Some versions of ITTIA DB also support SQL, a dynamic query language that allows complex queries to be planned and executed at run-time using a simple string-based syntax. The C and C++ APIs both contain support for SQL.

The following example program demonstrates how to create an empty database, insert data, and perform an indexed search.

// A Hello World console program for ITTIA DB

#include <ittia/db++.h>
#include <iostream>

int main(int argc, char** argv[])
{
    // Create an empty database.
    db::Database db;
    db::StorageMode mode;
    db.create("hello_world.db", mode);

    // Create a new table with two fields and one index.
    db::FieldDescSet fields;
    db::IndexDescSet indexes;
    fields.add_uint("id");
    fields.add_string("message", 50);
    indexes.add_index("by_id", db::DB_UNIQUE).add_field("id");
    db.create_table("hello_world", fields, indexes);

    // Open a table cursor.
    db::Table helloWorld;
    helloWorld.open(db, "hello_world");

    // Start a transaction before adding or accessing data.
    db.tx_begin();

    // Insert a row using the table cursor.
    helloWorld.insert();
    helloWorld["id"] = 0;
    helloWorld["message"] = "Hello World";
    helloWorld.post();

    // Search for the row that was inserted.
    helloWorld.set_sort_order("by_id");
    helloWorld.begin_seek(db::DB_SEEK_EQUAL);
    helloWorld["id"] = 0;
    if (DB_SUCCESS(helloWorld.apply_seek())) {
        db::String message = helloWorld["message"].as_string();
        std::cout << message.c_str() << std::endl;
    } else {
        std::cerr << "Could not find contact." << std::endl;
    }

    // Commit the transaction to make changes persistent.
    db.tx_commit();

    // Close the table cursor and database.
    helloWorld.close();
    db.close();
 
    return 0;
}

The following example shows how the previous example can be written using SQL.

// A Hello World console program for ITTIA DB

#include <ittia/db++.h>
#include <iostream>

using namespace db;
using namespace std;

int main(int argc, char** argv[])
{
    //-----------------------------------------------------------------------
    // Create a Database handle.
    //-----------------------------------------------------------------------
    Database db;
    StorageMode mode;

    //-----------------------------------------------------------------------
    // Create a Query object.
    //-----------------------------------------------------------------------
    Query q;

    //-----------------------------------------------------------------------
    // Create a new database file using the default mode.
    //-----------------------------------------------------------------------
    db.create("hello_world.db", mode);
    int rc;

    //-----------------------------------------------------------------------
    // Create hello_world table using the Query object.
    //-----------------------------------------------------------------------
    if(!DB_SUCCESS(q.exec_direct(db,
        "create table hello_world (id integer, message varchar(20))")))
    {
        cout << "Unable to create table\n";
        return -1;
    }

    //-----------------------------------------------------------------------
    // Start a transaction prior to modifying the hello_world table.
    //-----------------------------------------------------------------------
    if(!DB_SUCCESS(q.exec_direct(db,
        "START TRANSACTION")))
    {
        cout << "Unable to insert values into table\n";
        return -1;
    }

    //-----------------------------------------------------------------------
    // Insert 'hello world' message into the table.
    //-----------------------------------------------------------------------
    if(!DB_SUCCESS(q.exec_direct(db,
        "insert into hello_world values(0, 'hello world')")))
    {
        cout << "Unable to insert values into table\n";
        return -1;
    }

    //-----------------------------------------------------------------------
    // Select the message that was just inserted.
    //-----------------------------------------------------------------------
    if(!DB_SUCCESS(q.exec_direct(db,
        "select message from hello_world where id = 0")))
    {
        cout << "Unable to select from table\n";
        return -1;
    }

    //-----------------------------------------------------------------------
    // Print query result.
    //-----------------------------------------------------------------------
    StringField message(q, 0);
    for (rc = q.seek_first(); DB_SUCCESS(rc) && !q.is_eof(); rc = q.seek_next())
        cout << String(message).c_str() << endl;

    q.exec_direct(db, "COMMIT");

    //-----------------------------------------------------------------------
    // Clean up.
    //-----------------------------------------------------------------------
    q.close();
    db.close();

    return 0;
}

The ITTIA DB kernel is a library which is embedded directly into each application that uses it. This provides direct access to the database files from within the application, without the need to administer a separate database server.

There are three library configurations that an application can be linked with. These configurations correspond to different versions of ITTIA DB that are available.

To get started with ITTIA DB, first obtain the package appropriate for your operating system and architecture and extract all files. For more instruction on how to install ITTIA DB please refer to the README file for you platform.

3.4.1. Microsoft Visual C++ 6

	Note
	The following instructions assume that ITTIA DB has been installed to the directory $DBDIR.

	Note
	$DBDIR mentioned above is not an environment variable. In the instructions that follow, you must replace it with the directory in which ITTIA DB is installed.

The following explains how to add ITTIA DB to a Visual C++ Project. As mentioned in Linking ITTIA DB into an application the database comes in different flavors. Single and multi-threaded versions as well as client/server. In the following the main steps focus on linking with single and multi-threaded version and linking with the client library for client server is covered as sub-steps.

1. Open Visual Studio and create a new Win32 Console Application project.

2. Open the settings for your project from the menu:

   Project -> Settings

3. Select All Configurations from the Settings For list.

4. In the Project Settings dialog, select the C/C++ tab and from the Category list, select Preprocessor.

5. Add the folder $DBDIR\win32\include to the Additional include directories property.

6. In the Project Settings dialog, select the Link tab and from the Category list, select Input.

7. Add $DBDIR\win32\lib to the Additional library path property.

8. Add ittiasql.lib to the Object/library modules property.

   • For ITTIA DB-Standard, use ittiadb.lib instead.

   • For client/server IPC configuration, use ittiaipc.lib instead.

9. Click OK to close the project settings dialog.

10. For any project that uses the C++ API, follow these additional steps:

    1. Add the project $DBDIR\win32\dbcppapi\dbcppapi.dsp to the workspace, using the following menu item:

       Project -> Insert Project into Workspace

    2. Include the dbcppapi project as a dependency. Select your project, then go to the menu item:

       Project -> Dependencies

       Check dbcppapi in the list that is presented and click OK.

    3. In the Project Settings dialog, select the C/C++ tab and from the Category list, select Code Generation.

    4. Select Win32 Release from the Settings For list. Set the Use run-time library property to Multi-threaded DLL.

       	Note
       	This step is necessary because all projects must be compiled using the same runtime library. Alternatively, you can change the Use run-time library setting for the dbcppapi project to match the desired setting for your project.

    5. Select Win32 Debug from the Settings For list. Set the Use run-time library property to Debug Multi-threaded DLL.

11. Add application source code files to the project. You can use the C++ example code to get started.

12. Copy ittiasql.dll from $DBDIR\win32\lib to the project directory.

    • For ITTIA DB-Standard, copy ittiadb.dll to the project directory.

    • For client/server IPC configuration, copy the file ittiaipc.dll to the project directory.

13. Build and run your application.

3.4.4. GNU gcc

On the command line:

1. When compiling source files, add ITTIA DB's include directory to the include path:

   g++ -I$DBDIR/include main.cpp -o main.o

2. Include the ITTIA DB library when linking object files together into an executable program:

   g++ -L$DBDIR -ldbcppapi -littiadb main.o -o program

   	Note
   	For client/server IPC configuration, use the link option -ldbipcclient instead of -littiadb.

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++ should still be used for linking.

1. When compiling source files, add ITTIA DB's include directory to the include path:

   gcc -I$DBDIR/include main.c -o main.o

2. Include the ITTIA DB library when linking object files together into an executable program:

   g++ -L$DBDIR -littiadb main.o -o program

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

ITTIA DB includes an example phonebook program to demonstrate some of its capabilities. The example program uses a simple console-based interface so as not to distract from the basic principles of using ITTIA DB. The phonebook example contains two C++ classes: PhoneBook, which handles database operations, and PhoneBookConsoleApp, which acts as a simple controller for the application.

A similar example program based on the C API name phbook is also included. This example shares the same schema as the C++ example program and can be used to access the same database files.

Both example programs demonstrate how to:

3.5.2. Microsoft eMbedded Visual C++

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

1. Open the workspace $DBDIR\examples\examples.vcw

2. Select Active WCE Configuration on the Workspace Configuration Tool-Bar.

3. Select Active Target Configuration on the Workspace Configuration Tool-Bar.

4. Select Default Device on the Workspace Configuration Tool-Bar.

5. Select Execute phonebook.exe from the Build menu.

	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.

ITTIA DB allows multiple threads within the same application process to access the database concurrently. See Multi-threaded shared access for more information on accessing the database from multiple threads.

	Warning
	Multithreading is only supported in the shared access version of ITTIA DB. The single user version is not thread safe and requires that all database operations occur in a single thread.

ITTIA DB stores all data in a single file, including the system catalog, data, and keys. The transaction journal is stored in a separate log file that should not be deleted. A database file can be created at any time, either when an application is run for the first time, or when the application is packaged. The application can even treat database files as a custom document format, allowing the user to create as many databases as needed.

Structured Query Language, or SQL, is a standard database interface that uses query strings to access and modify data. A query string can be embedded in software code to simplify complex tasks or entered by a trusted user to perform advanced reporting. SQL is a powerful a development tool because it allows a developer to quickly inspect the contents of a database without writing any additional program code.

ITTIA DB includes the ittiasql utility to execute SQL statements through a command-line interface. Source code for this utility is included with the other examples. See ittiasql for usage information.

SQL is not available in all versions of ITTIA DB. See Linking ITTIA DB into an application for information on which libraries include support for SQL.

	Tip
	While SQL has many advantages, because SQL statements are parsed and executed at run-time, they require some additional overhead to use. All SQL operations can be performed directly with ITTIA DB's C and C++ APIs for best performance. Many operations, such as inserting data from variables and transaction control, require less code to perform directly than with SQL.

4.1.1. Creating Tables and Inserting Values

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

Example 4.1. CREATE TABLE

ittiasql version 2.5
Copyright (C) 2005-2007 ITTIA. All rights reserved.

SQL statements end with a semi-colon (;). Interactive commands start with
a period (.) and do not end with a semi-colon.

Type '.help' for a list of interactive commands. Type '.exit' to quit.
$ .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 4.2. INSERT INTO

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.

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

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

The basic unit of work in SQL is the statement. A statement can span multiple lines and any extra whitespace between keywords and symbols is ignored. In the interactive ittiasql utility, statements are terminated with a semi-colon. When an SQL statement is executed through the API, a semi-colon should not be used.

The following conventions are used in this document to show SQL syntax:

In this document, SQL syntax is documented in a format similar to the following:

Each of these lines demonstrates a convention used to explain the grammar of SQL. Square brackets ([]), curly brackets ({}), single pipes (|) and ellipsis dots (...) are part of the notation and are not used in SQL syntax.

Table 4.1, “SQL Reserved Keywords” lists all keywords that are reserved for the SQL language. Do not use these names for table, column or other identifiers.

4.2.1. Column Types

ITTIA DB 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.5, “Schema Definition” for more information.

SQL Column Type	C++ Class	C Data Type	Maximum Storage Size
tinyint, sint8	IntegerField	int8_t	1 byte
smallint, sint16	IntegerField	int16_t	2 bytes
integer, int, sint32	IntegerField	int32_t	4 bytes
bigint, sint64	IntegerField	int64_t	8 bytes
uint8	IntegerField	uint8_t	1 byte
uint16	IntegerField	uint16_t	2 bytes
uint32	IntegerField	uint32_t	4 bytes
uint64	IntegerField	uint64_t	8 bytes
float32	FloatField	db_float32_t	4 bytes
float, float64	FloatField	db_float64_t	8 bytes
varchar(n), ansistr(n)	StringField	db_ansi_t, char *	page size
utf8str(n)	WStringField	db_utf8_t, char *	page size
nvarchar(n), utf16str(n)	WStringField	db_utf16_t	page size
utf32str(n)	WStringField	db_utf32_t	page size
currency	CurrencyField	db_float64_t	8 bytes
date	DateTimeField	db_date_t	6 bytes
time	DateTimeField	db_time_t	6 bytes
datetime	DateTimeField	db_datetime_t	12 bytes
timestamp	DateTimeField	db_timestamp_t	16 bytes
blob	BlobField	db_blob_t	database size

Table 4.2. SQL Column Types

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.

ITTIA DB supports four character formats: ANSI, UTF-8, UTF-16, and UTF-32. When a character column is created, the maximum size must be provided as parameter n, though when data is stored, only as much space as is needed will be used.

Unicode is the best character format, and should be used whenever possible. The Unicode encodings supported by ITTIA DB ̵ UTF-8, UTF-16, and UTF-32 ̵ are each able to store the entire range of Unicode characters. ITTIA DB will automatically convert between Unicode encodings when a database column is bound to a variable, so always select the encoding that provides the most compact representation for the column type. UTF-8 is best for Latin text and many European languages, while UTF-16 is best for Asian languages.

For Unicode encodings, the maximum size is given in code units. Each Unicode character requires one or more code units when encoded in UTF-8 or UTF-16.

Encoding	Code Unit
UTF-8	1 byte
UTF-16	2 bytes
UTF-32	4 bytes

As an alternative to Unicode, ITTIA DB also supports the ANSI character format. ITTIA DB assumes that ANSI character strings are encoded using the current locale, which is usually a platform-dependent encoding such as ISO 8859-1.

4.2.1.1. NULL Values

ITTIA DB 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.

4.2.2. SELECT Queries

SELECT
[ ALL | DISTINCT ] field,...
FROM table-expression
[WHERE search-condition]
[GROUP BY field,... ]
[ORDER BY order-by]
[{ UNION | EXCEPT | INTERSECT } select-statement ]

field ::=
[table-name.]column-name [[AS] alias-name]
| [table-name.]*
| term [[AS] alias-name]

order-by ::=
{ field [ ASC | DESC ]},...

column-list ::=
column-name,...

term ::=
term { + | - | * | / | % | || } term
| { + | - } term
| (expression)
| function( arguments,... )
| { NEXT | CURRENT } VALUE FOR sequence-name
| CAST (term AS column-type)
| CASE {WHEN expression THEN term}... [ELSE term] END
| literal-value
| [table-name.]column-name

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

4.2.2.1. FROM Clause

FROM
table-expression

table-expression ::=
table-reference
| table-expression [join-type] JOIN table-reference [join-condition]
| table-expression,...

table-reference ::=
table-name [[AS] correlation-name]

join-condition ::=
ON search-condition

The FROM clause specifies which tables to include in the query. Related tables can be joined together by matching the related columns to create rows that contain data from both tables. The following statement selects all related rows from the contact and phone_number tables:

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

These two tables are related by the id column in contact and the contact_id column in phone_number. The search-condition following the ON keyword uses the same syntax as the WHERE clause discussed in Section 4.2.2.2, “WHERE Clause”. The join clause can be repeated to add more tables to the query.

Column names can be prefixed with a table name for clarity and to avoid ambiguity. The following query is equivalent to the previous query:

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

Tables can be given an alternate correlation-name to identify columns in the query, as in the following example:

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

The correlation name can also be used to join multiple instances of the same table. The following query lists alternate phone numbers for each phone number in the database by joining the phone_number table with itself:

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

4.2.2.2. WHERE Clause

WHERE
search-condition

search-condition ::=
expression

expression ::=
expression { = | <> | > | < | >= | <= } expression
| expression OR expression
| expression AND expression
| NOT expression
| expression IS [NOT] NULL
| IN ( term,... )
| term BETWEEN term AND term
| term

The WHERE clause is optional, and if given will limit the rows in the result set to those satisfied by the search-condition. The search-condition can be a simple comparison or a complex expression.

The following query searches for a contact named Bob. Since the name column is indexed, this search will always be fast.

select *
  from contact
  where name = 'Bob'

=	Equal to
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to

Table 4.3. SQL Comparison Operators

4.2.2.3. GROUP BY Clause

GROUP BY
term,...

The GROUP BY clause is used to summarize the results of a query by grouping multiple rows into a single row in the result set. Grouping occurs after tables have been joined and filtered by the FROM and WHERE clauses.

The following statement groups phone numbers by contact_id, summarizing the number of phone numbers recorded for each contact.

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

This query can be extended to include the contact name by joining with the contact table and adding the name column to the grouping list.

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

When grouping is used, the SELECT field list can only contain terms that are listed in the GROUP BY clause, unless enclosed in an aggregate function.

Aggregate functions return a single value from an expression that is evaluated over multiple rows. Aggregate functions are listed in Table 4.4, “SQL Aggregate Functions”.

Name	Argument Type	Return Value
min	Any type	The lowest value
max	Any type	The highest value
sum	Number	Sum of values
avg	Number	Average of values
count	Any type or *	Number of rows

Table 4.4. SQL Aggregate Functions

Aggregate functions can also be used to group all rows into a single row in the result set when the GROUP BY clause is omitted. The SELECT field list can only contain aggregate and literal terms when any aggregate function is used without a GROUP BY clause.

4.2.2.4. ORDER BY Clause

ORDER BY
{ field [ ASC | DESC ]},...

By default, rows are returned in an arbitrary order. Use the ORDER BY clause to sort the results.

select *
  from contact
  order by name

If multiple columns are named, results are sorted by the first column and then by the following columns only when values in the first column are equal. By default, columns are sorted in ascending order, but the DESC modifier can be applied to any column to change its direction.

select *
  from phone_number
  order by contact_id asc, type desc

4.2.2.5. UNION, EXCEPT, and INTERSECT

select-statement { UNION | EXCEPT | INTERSECT } select-statement

SELECT statements can be combined through the set operations UNION, EXCEPT, and INTERSECT. Combined SELECT statements must all have the same types of fields, listed in the same order.

The UNION operation returns all rows from both statements. The EXCEPT operation returns only those rows from the first query that are also present in the second. The INTERSECT operation returns only rows that are present in the results of both statements.

4.2.2.6. Built-in functions

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

Name	Argument Types	Returns
abs(n)	Number	Absolute value of n
sign(n)	Number	Sign of n: -1 for negative, 0 for zero, +1 for positive
mod(n, m)	Number	Remainder of n divided by m
least(a, ...)	Any type	Lowest value listed. Result is NULL if any value is NULL.
greatest(a, ...)	Any type	Greatest value listed. Result is NULL if any value is NULL.
trim(str)	String	str with leading and trailing spaces removed
ltrim(str)	String	str with leading spaces removed
rtrim(str)	String	str with trailing spaces removed
substr(str, pos, len)	String, integer, integer	Substring of str of length len, starting from pos, where the first character is position 1.
substr(str, pos)	String, integer	Substring of str, starting from pos, where the first character is position 1, to the end of the string.
contcat(str, ...)	String	Concatenation of listed strings
coalesce(a, ...)	Any type	First value listed that is not NULL

Table 4.5. SQL Built-in Functions

4.2.3. Data Manipulation

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.4, “Transactions”.

4.2.3.1. INSERT

INSERT INTO
table-name [( column-name, ... )]
VALUES value, ...
| select-statement

INSERT INTO will insert zero, one or more rows into a single table. By default, values must be given for all columns in the order they were created.

Optionally, a list of column names can be used. Values should be given in the same order as the columns in the list. Columns not listed will be assigned the value NULL by default.

To insert literal values for a single row, use the VALUES keyword followed by a list of values. The following query adds a new phone number to the first contact:

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

Since speed_dial is left NULL, it can be omitted from column list:

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

To insert one or more rows, use a SELECT statement instead to obtain rows from another table. The following query copies all phone numbers from contact 1 to contact 2:

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

4.2.3.2. UPDATE

UPDATE table-name [AS alias]
SET { column-name = value,... }
[WHERE search-condition]

UPDATE modifies the value of zero, one or more rows in a single table. Columns and their new values are given in a list following the SET keyword, and by default all rows are modified. Values can be literal or expressions containing other columns from the table.

To limit changes to one or more rows, supply a search condition in the form of a WHERE clause with the same syntax as when used with SELECT. For more information, see Section 4.2.2.2, “WHERE Clause”.

The following statement updates a contact's name and changes their ring tone:

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

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

4.2.4. Transactions

START TRANSACTION
[[ISOLATION LEVEL] READ COMMITTED | REPEATABLE READ | SERIALIZABLE ]

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.

If a transaction is not active, each SELECT statement will run in its own transaction and a transaction will not be active after the statement is completed. However, when the results of a select statement will be used to update other tables, a single transaction should be started manually to ensure data consistency.

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.

COMMIT
[ TRANSACTION | WORK
[ LAZY COMPLETION | GROUP COMPLETION | FORCED COMPLETION ]]

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.

ROLLBACK
[ TRANSACTION | WORK
[ LAZY COMPLETION | GROUP COMPLETION | FORCED COMPLETION ]]

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.

create table contact (
  id uint64, name nvarchar(50), ring_id uint64,
  picture_name varchar(50), picture blob
)

create index by_name on contact (name)

create unique index by_id on contact (id)

create sequence contact_id start with 1

The ITTIA DB SQL C++ API is comprised of three new functions which, when used in conjunction with the base query functions, allow the database to be accessed and manipulated through the use of standardized SQL statements.

As with all database access, the database must first be opened before the ITTIA DB SQL C API can be accessed. A successful call to open(), or create(), will return a valid database handle for use with the SQL C++ API.

The first SQL C++ API function called must be prepare(). This function parses the SQL statement and prepares the internal structures for SQL execution:

Query q;
int   rc;
rc = q.prepare(db, sql_text);

Once the query is prepared, a call to execute() must be performed to process the SQL command:

int   rc;
rc = q.execute();

These two calls have been packaged into a single exec_direct() call which will prepare and execute the SQL command.

int   rc;
rc = q.exec_direct(db, sql_text);

Once the SQL query returns from a navigational expression, the ITTIA DB C++ API cursor functions can be used to process the data returned from the query. The following code illustrates how this is accomplished:

Query           q;
IntegerField    id;
WStringField    w_string;
StringField     a_string;

if  ( DB_SUCCESS( q.exec_direct(db, sql_command) ) ) {

    //--------------------------------------------
    // Bind the query fields to local variables
    //--------------------------------------------
    id.attach( q, FIELD_ID );
    w_string.attach( q, FIELD_W_STRING );
    a_string.attach( q, FIELD_A_STRING );

    //--------------------------------------------
    // Traverse the returned rows of data
    //--------------------------------------------
    for (q.seek_first(); !q.is_eof(); q.seek_next()) {

        cout  <<  "Id:   " << (long) id << endl;
        wcout << L"WStr: " << WString( w_string ).c_str() << endl;
        cout  <<  "AStr: " << String( a_string ).c_str() << endl;

    }
    q.close()
}

The ITTIA DB SQL C API is comprised of three new functions which, when used in conjunction with the base cursor functions, allow the database to be accessed and manipulated through the use of standardized SQL statements.

As with all database access, the database must first be opened before the ITTIA DB SQL C API can be accessed. A successful call to db_open(), or db_create(), will return a valid database handle for use with the SQL C API.

The first SQL C API function called must be db_prepare_sql_cursor(). This function translates the SQL statement and prepares the internal structures for SQL execution:

db_cursor_t sql_cursor;
sql_cursor = db_prepare_sql_cursor(hdb, sql_text, 0);

Once the cursor is prepared, a call to db_execute() must be performed to process the SQL command:

db_execute(sql_cursor);

If the SQL query returns from a navigational expression (SELECT) the SQL state will be placed in navigational mode and must be reset to prepared mode to perform any other DML expressions (INSERT / UPDATE / DELETE / MERGE). A call to db_unexecute(), or another call to db_prepare_sql_cursor(), will set the cursor back to the prepared state.

db_unexecute(sql_cursor);

Once the SQL query returns from a navigational expression, the ITTIA DB C API cursor functions can be used to process the data returned from the query. The following code illustrates how this is accomplished:

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, sql_text, 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);
}

The cursor returned by db_prepare_sql_cursor is accessed through the same functions used to access a table cursor. See Section 6.3.3, “Rows” for methods to fetch query results directly into local variables and data structures. SQL cursors are read-only.