ITTIA DB
User's Guide

Version 2.5 build 2067


Table of Contents

1. Introduction
1.1. Overview
1.2. Supported platforms
1.3. About the documentation
2. Database Concepts
2.1. Introduction to databases
2.2. Mobile and embedded considerations
3. Using ITTIA DB
3.1. Selecting an API
3.2. C++ API Example
3.3. C++ API Example Using SQL
3.4. Linking ITTIA DB into an application
3.4.1. Microsoft Visual C++ 6
3.4.2. Microsoft Visual C++ 2003/2005
3.4.3. Microsoft eMbedded Visual C++ 4.0
3.4.4. GNU gcc
3.5. Included example programs
3.5.1. Microsoft Visual C++
3.5.2. Microsoft eMbedded Visual C++
3.5.3. GNU gcc
3.6. Multi-threading
3.7. Database files
4. SQL
4.1. Introduction
4.1.1. Creating Tables and Inserting Values
4.1.2. Projection and Selection
4.1.3. Joining Related Tables
4.2. SQL Reference
4.2.1. Column Types
4.2.2. SELECT Queries
4.2.3. Data Manipulation
4.2.4. Transactions
4.2.5. Schema Definition
4.3. C++ API
4.4. C API
5. The C++ API
5.1. Getting started
5.1.1. Creating and Opening a Database
5.1.2. Error Handling
5.1.3. Run-time Configuration
5.2. Database Design
5.2.1. Overview
5.2.2. Column Types
5.2.3. Defining the Database Schema
5.3. Database Access
5.3.1. Transactions
5.3.2. Table Cursors
5.3.3. Inserting Data
5.3.4. Traversing a Table
5.3.5. Searching for a Row
5.3.6. Reading Data
5.3.7. Updating Data
5.3.8. Deleting the Row
5.3.9. Reading and Writing BLOBs
6. The C API
6.1. Getting Started
6.1.1. Initializing the Library
6.1.2. Creating and Opening a Database
6.1.3. Error Handling
6.1.4. Data Structures
6.2. Database Design
6.2.1. Column Types
6.2.2. Tables, Fields and Indexes
6.2.3. Sequence Generators
6.3. Database Access
6.3.1. Transactions
6.3.2. Cursors
6.3.3. Rows
6.3.4. Inserting Data
6.3.5. Traversing a Cursor
6.3.6. Updating Data
6.3.7. Deleting the Row
6.3.8. Reading and Writing BLOBs
7. Shared Database Access
7.1. Overview
7.. Client/server shared access
7.3. Multi-threaded shared access
7.4. Isolation levels
7.4.1. Serializable
7.4.2. Repeatable read
7.4.3. Read committed
7.4.4. Read uncommitted
7.5. Selecting an isolation level
7.6. Data change notification
8. Advanced Topics
8.1. Introduction
8.1.1. Recommended Reading
8.2. ACID Properties
8.3. Logging
8.4. Locking
8.4.1. Types of Locks
8.4.2. Lockable Objects
8.4.3. Index Locking
8.4.4. Manual Locking
I. Utilities
dbserver - Light-weight data server for multi-process shared access.
dbserver GUI - Graphical interface to run the database server (Windows CE only).
ittiasql - SQL interactive query prompt
Index

List of Figures

6.1. Managed Field Binding
6.2. Absolute Field Binding
6.3. Relative Field Bindings

List of Tables

1.1. Supported platforms
4.1. SQL Reserved Keywords
4.2. SQL Column Types
4.3. SQL Comparison Operators
4.4. SQL Aggregate Functions
4.5. SQL Built-in Functions
5.1. C++ Column Types
6.1. C Column Types
7.1. Isolation Level Implementations
7.2. Phenomena prevented by Serializable Isolation
8.1. ITTIA DB Database Characteristics Quick Reference
8.2. ACID Properties
13. Utility program files by platform

List of Examples

3.1. C++ Hello World Program
3.2. SQL Hello World Program
4.1. CREATE TABLE
4.2. INSERT INTO
4.3. SELECT
4.4. SELECT ... JOIN

Chapter 1. Introduction

1.1. Overview

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:

Fast

Today's mobile device users demand instant data access. ITTIA DB was developed to help meet this requirement.

Flexible

ITTIA DB is designed with extensibility in mind. Add and remove database components to suite your application’s exact requirements and achieve minimal footprint. The kernel is a tightly integrated set of components with clearly defined interfaces, which makes it easy to develop a customized version of any component using the included source code.

Efficient and Lightweight

Optimized for restricted environments, ITTIA DB is able to utilize scarce resources most effectively. Battery life, form-factor and cost requirements greatly restrict the resources available to mobile and embedded developers. ITTIA DB is built for minimum memory and processor time consumption.

Reliable

In mobile devices, the power can fail at any time. Thus, the database is fully ACID-compliant, which ensures data integrity through the use of transactions and recovery, and data consistency by means of isolation.

Features include:

  • Cross-platform compatibility

  • Shared database access

  • Small footprint

  • Support for multithreading

  • Modular design: add new features with ease

  • Configurable: flexibility at compile-time and run-time

  • Embedded directly into applications for improved performance

  • Portable database file format

  • Support for solid state storage such as CompactFlash

  • Data encryption

  • Dynamic schema alteration

  • Variable-sized records

  • Multiple isolation levels

1.2. Supported platforms

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.

Operating SystemArchitecturesDevelopment Tools
Windows NT
Windows 2000
Windows XP
x86
Visual C++ 6.0
Visual C++ .NET
gcc (MingW)
Windows CE
Windows Mobile
x86
ARM
Visual C++ eMbedded 4.0
Linux
x86
ARM
gcc
MontaVista Linux
Mobilinux
x86
ARM
DevRocket
gcc
VxWorks
x86
Wind River Workbench

Table 1.1. Supported platforms

1.3. About the documentation

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:

  • HTML

  • Windows Help

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

Chapter 2. Database Concepts

2.1. Introduction to databases

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]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:

  1. Multi-threaded: In this architecture single process accesses the data in the database using multiple threads and

  2. Client server architecture: This model supports multiple clients accessing the database- remotely or locally.

    [Note]Note

    ITTIA DB supports all levels of concurrency mentioned above.

2.2. Mobile and embedded considerations

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.

Chapter 3. Using ITTIA DB

3.1. Selecting an API

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.

3.2. C++ API Example

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

Example 3.1. C++ Hello World Program

// 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;
}

3.3. C++ API Example Using SQL

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

Example 3.2. SQL Hello World Program

// 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;
}

3.4. Linking ITTIA DB into an application

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.

Single threaded stand-alone

Used in single process environment. There are two versions debug and release.

Multi-threaded stand-alone

Used in a multi-threaded environment where multiple threads, within a single process, are running against the database. This configuration also has a debug as well as a release version.

Client/server inter-process communications (IPC)

In this version the database version of ITTIA DB must be running as back-end database. Clients connect to the running ITTIA DB server. In this scenario the clients are linked with the client library and we run the server version of ITTIA DB called dbserver at the back-end. From application program perspective there is no change The only difference is that to open a database a specailly formated URI in the form [xdbtp://server[:port]/]database is passed to create or open database functions.

[Note]Note

For more details on the multi-threaded and client/server configurations, see Shared Database Access.

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]Note

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

[Note]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]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.2. Microsoft Visual C++ 2003/2005

Add ITTIA DB to a Visual C++ Project:

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

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

    Project -> Properties

  3. Select All Configurations from the Configuration list.

  4. Select the property page:

    Configuration Properties -> C/C++ -> General

  5. Add $DBDIR\win32\include to the Additional Include Directories property.

  6. Select the property page:

    Configuration Properties -> Linker -> General

  7. Add $DBDIR\win32\lib to the Additional Library Directories property.

  8. Select the property page:

    Configuration Properties -> Linker -> Input

  9. Add ittiasql.lib to the Additional Dependencies property.

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

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

  10. Click OK to close the project settings dialog.

  11. 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:

      File -> Add -> Existing Project

      Answer Yes when asked whether you should convert and open this project.

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

      Project -> Project Dependencies

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

    3. Open the settings for your project again and select the property page:

      Configuration Properties -> C++ -> Code Generation

    4. Select Release from the Configuration list. Set the Runtime Library property to Multi-threaded DLL (/MD).

      [Note]Note

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

    5. Select Debug from the Configuration list. Set the Runtime Library property to Multi-threaded Debug DLL (/MDd).

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

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

  14. Build and run your application.

3.4.3. Microsoft eMbedded Visual C++ 4.0

Add ITTIA DB to an eMbedded Visual C++ Project:

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

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

    Project -> Settings

  3. Select All Configurations from the Settings For: list.

  4. Select the property page:

    Project Settings -> C/C++ -> Category: Preprocessor

  5. Add $DBDIR\win32\include to the Additional Include Directories property.

  6. Select the property page:

    Project Settings -> Linker -> Category: Input

  7. Add $DBDIR\win32\lib to the Additional Library Directories 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.vcp to the workspace, using the following menu item:

      Project -> Insert Project into Workspace

      Answer Yes when asked whether you should convert and open this project.

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

      Project -> Dependencies

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

  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]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]Tip

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

3.5. Included example programs

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:

  • Create and open a new database

  • Define a database schema

  • Insert, update, and delete records

  • Search, scan, and join database tables

  • Close a database

3.5.1. Microsoft Visual C++

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

  1. Open the workspace $DBDIR\example\phonebook.dsw

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

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

3.5.3. GNU gcc

To build and run the example program with gcc:

  1. Change to the directory $DBDIR/example

  2. Run make

  3. Run ./phonebook to start the C++ API example program.

  4. Run ./phbook to start the C API example program.

3.6. Multi-threading

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

3.7. Database files

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.

Chapter 4. SQL

4.1. Introduction

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]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]Tip

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

4.1.2. Projection and Selection

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 4.3. SELECT

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

4.1.3. Joining Related Tables

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 4.4. SELECT ... JOIN

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

4.2. SQL Reference

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:

symbol ::=
KEYWORD
replaceable-symbol
replaceable-keyword
identifier
[optional]
choice 1 | choice 2 | choice 3
{ choice 1 | choice 2 | choice 3 }
repeatable,...
{repeatable},...

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.

symbol

The grammar starts with the name of a symbol and is followed by a description of the syntax for that symbol.

KEYWORD

An SQL keyword is shown in all caps. In practice, SQL keywords are case insensitive and can be used with any capitalization.

replaceable-symbol

A replaceable symbol is replaced with syntax defined for that symbol elsewhere in the documentation.

replaceable-keyword

A replaceable keyword is replaced with an SQL keyword listed in a table, such as Table 4.2, “SQL Column Types”.

identifier

An identifier is used to identify a table, column, or other schema object. Identifiers must start with a letter and can contain letters, numbers, and the underscore character (_). Identifiers are limited to 32 characters.

optional

Optional syntax is enclosed in square brackets.

choice

Multiple choices are separated by the pipe (|) symbol. Only one choice should be used.

repeatable

In SQL, many items are repeatable, in which case items are separated by commas. If only one item is given, no comma is used.

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

EncodingCode Unit
UTF-81 byte
UTF-162 bytes
UTF-324 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]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]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”.

NameArgument TypeReturn Value
minAny typeThe lowest value
maxAny typeThe highest value
sumNumberSum of values
avgNumberAverage of values
countAny 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.

NameArgument TypesReturns
abs(n)NumberAbsolute value of n
sign(n)NumberSign of n: -1 for negative, 0 for zero, +1 for positive
mod(n, m)NumberRemainder of n divided by m
least(a, ...)Any typeLowest value listed. Result is NULL if any value is NULL.
greatest(a, ...)Any typeGreatest value listed. Result is NULL if any value is NULL.
trim(str)Stringstr with leading and trailing spaces removed
ltrim(str)Stringstr with leading spaces removed
rtrim(str)Stringstr with trailing spaces removed
substr(str, pos, len)String, integer, integerSubstring of str of length len, starting from pos, where the first character is position 1.
substr(str, pos)String, integerSubstring of str, starting from pos, where the first character is position 1, to the end of the string.
contcat(str, ...)StringConcatenation of listed strings
coalesce(a, ...)Any typeFirst 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]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]Tip

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

4.2.5. Schema Definition

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

4.2.5.1. CREATE TABLE

CREATE TABLE
table-name (
{ column-name column-type },...
)

Create an empty table with listed columns. Each column is assigned a name and a type from Table 4.2, “SQL Column Types”.

The following statement creates a contact table with columns for a unique identifier, a name encoded in Unicode, a ring tone identifier, a picture file name, and picture data.

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

4.2.5.2. CREATE INDEX

CREATE [UNIQUE] INDEX
index-name ON table-name ( column-name, ... )

Create an index on one or more columns in a table. Indexed columns can be searched faster than non-indexed columns, especially when there are a large number of rows in the table. Multiple columns can be included in the same index to improve performance on queries that search multiple columns at once. Queries that only search on one column can only utilize the first field in each index, so more than one index is usually needed to support a variety of queries.

The following statement creates an index on contact names:

create index by_name on contact (name)

An index can also be set as UNIQUE, which prevents duplicate values for the indexed columns. The contact identifier uses a unique index to ensure that contact identifiers are not reused:

create unique index by_id on contact (id)

4.2.5.3. CREATE SEQUENCE

CREATE SEQUENCE
sequence-name [START WITH number]

A sequence generator provides unique numbers to use as identifiers in the database or in the application. A sequence generator can be assigned a non-negative starting value when created.

create sequence contact_id start with 1

4.2.5.4. ALTER TABLE

ALTER TABLE
table-name
ADD COLUMN column-name column-type
| DROP COLUMN column-name

Columns can be added and removed from a table using the ALTER TABLE statement. A column can only be dropped if it is not used by any index and is not the only column in the table.

4.2.5.5. DROP TABLE

DROP TABLE
table-name

DROP TABLE removes a table and all of its contents from the database, including indexes. This action cannot be undone.

4.2.5.6. DROP INDEX

DROP INDEX
index-name ON table-name

DROP INDEX removes an index from a table. Because the index does not contain any irreplaceable data, it can be recreated at a later time.

4.2.5.7. DROP SEQUENCE

DROP SEQUENCE
sequence-name

DROP SEQUENCE removes a sequence generator from the database.

4.3. C++ API

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()
}

4.4. C API

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.

Chapter 5. The C++ API

5.1. Getting started

5.1.1. Creating and Opening a Database

To use the ITTIA DB 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]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 done using the database, call close().

db.close();

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

5.1.2. Error Handling

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

5.1.3. Run-time Configuration

Various features of ITTIA DB 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 Database::initialize():

memory_mode

Select a memory optimization strategy: LibraryConfig::TIGHT, LibraryConfig::COMPACT, or LibraryConfig::LARGE.

transactions

Approximate number of concurrent transactions expected.

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

open_flags

Select read-only access or disable logging to the journal.

buffer_count

Sets the number of pages to allocate for caching database pages. The total size of the buffers will be buffer_count * page_size.

page_size

Sets the page size for creating a new database. The page size of an existing database cannot be changed by setting this value.

checkpoint_interval

Specifies how many operations to perform between checkpoints in the journal.

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

5.2. Database Design

5.2.1. Overview

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

5.2.2. 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 retreived 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”.

Column Type

FieldDescSet Function

C++ Class

sint8

add_sint(name, 1)

IntegerField

sint16

add_sint(name, 2)

IntegerField

sint32

add_sint(name, 4)

IntegerField

sint64

add_sint(name)

IntegerField

uint8

add_uint(name, 1)

IntegerField

uint16

add_uint(name, 2)

IntegerField

uint32

add_uint(name, 4)

IntegerField

uint64

add_uint(name)

IntegerField

float64

add_float(name)

FloatField

ansistr

add_string(name, length)

StringField

utf16str

add_wstring(name, length)

WStringField

currency

add_currency(name)

CurrencyField

date

add_date(name)

DateTimeField

time

add_time(name)

DateTimeField

datetime

add_date(name, DB_DATETIME)

DateTimeField

timestamp

add_date(name, DB_TIMESTAMP)

DateTimeField

blob

add_blob(name)

BlobField

Table 5.1. C++ Column Types

5.2.3. Defining the Database Schema

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]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");

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

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

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

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]Note

Calls to create_table(),