Copyright © 2005-2010 ITTIA
Table of Contents
List of Figures
List of Tables
List of Examples
SELECT statementTable of Contents
ITTIA DB SQL™ 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 SQL™ is:
Today's mobile device users demand instant data access. ITTIA DB SQL™ was developed to help meet this requirement.
ITTIA DB SQL™ 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.
Optimized for restricted environments, ITTIA DB SQL™ 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 SQL™ is built for minimum memory and processor time consumption.
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
ITTIA DB SQL™ is a cross-platform database management system, designed to work in a variety of environments. In addition to the platforms listed below, ITTIA DB SQL™ can be easily ported to run on other operating systems and architectures.
| Operating System | Architectures | Development Tools | |||||||
|---|---|---|---|---|---|---|---|---|---|
|
|
| |||||||
|
|
| |||||||
|
|
| |||||||
|
|
|
Table 1.1. Supported platforms
ITTIA DB SQL™ is a family of products for high-performance relational data storage on embedded systems and devices. Each edition of ITTIA DB is designed to meet the requirements of a certain class of devices with the best performance and minimum footprint. The same API and file format is used by all ITTIA DB products.
For devices that only need table-based storage with high-performance indexing, this software library gives devices direct access to ITTIA DB™ database files.
For devices storing mission-critical data, ITTIA DB-SQL Standard™ incorporates ACID-compliant crash recovery with optional run-time SQL queries.
Share database files between threads, processes, and devices using ITTIA's lightweight data server, designed to support a high level of concurrency even in low-memory environments.
Table 1.2, “Features Available in Each Edition of ITTIA DB SQL™” explains each of the features in Compact, Standard, and Plus. API functions that are not supported by a particular edition of ITTIA DB SQL™ will produce the error DB_ENOTIMPL when called.
| Feature | Compact | Standard | Plus |
|---|---|---|---|
| C API | Yes | Yes | Yes |
| C++ API | Yes | Yes | Yes |
| Transaction rollback | Yes | Yes | Yes |
| Disk tables and file storage | Yes | Yes | Yes |
| Memory tables and storage | Yes | Yes | Yes |
| Strongly-typed tables | Yes | Yes | Yes |
| B+ tree indexes | Yes | Yes | Yes |
| T-tree indexes | Yes | Yes | Yes |
| Platform-independent file format | Yes | Yes | Yes |
| SQL | No | Yes | Yes |
| ODBC | No | No | Yes |
| Java API | No | No | Yes |
| C# API | No | No | Yes |
| Client/server shared access | No | No | Yes |
| Multi-threaded shared access | No[a] | No[a] | Yes |
| Row-level locking and isolation | No | No | Yes |
| Recovery logging | Optional[b] | Yes | Yes |
| Dynamic schema alteration | Yes[b] | Yes | Yes |
| Encryption callbacks | Optional[b] | Yes | Yes |
| Change notification | Optional[b] | Yes | Yes |
| Sequences | Optional[b] | Yes | Yes |
| BLOB data type | Optional[b] | Yes | Yes |
| Unicode data type | Yes[b] | Yes | Yes |
| Automatic type conversion | Optional[b] | Yes | Yes |
[a] While ITTIA DB SQL™ API calls are not thread-safe without multi-threaded shared access, it is compiled against the multithreaded C runtime when available. [b] This feature can be enabled or disabled in the source code version of ITTIA DB SQL™. Optional features are not included in the binary compact library distributed by ITTIA. | |||
Table 1.2. Features Available in Each Edition of ITTIA DB SQL™
Also see Table 3.1, “ITTIA DB SQL™ Kernel Libraries for Windows” and Table 3.2, “ITTIA DB SQL™ Kernel Libraries for gcc” for a list of libraries in ITTIA DB SQL™ software development kits.
Applications developed using ITTIA DB Compact™ can be upgraded to ITTIA DB-SQL Standard™ or ITTIA DB-SQL Plus™. Applications developed for ITTIA DB-SQL Standard™ can also be upgraded to ITTIA DB-SQL Plus™.
To upgrade an existing ITTIA DB application to ITTIA DB-SQL Standard™, modify the build configuration to link with one of the single-user libraries listed in Section 3.3, “Linking ITTIA DB SQL™ into an Application”. Automatic recovery will be automatically enabled without modifying the application code. The application will also be able to use SQL queries if the SQL library is selected.
To upgrade an existing ITTIA DB application to ITTIA DB-SQL Plus™, modify the build configuration to link with one of the default, IPC client, or local storage libraries listed in Section 3.3, “Linking ITTIA DB SQL™ into an Application”. Row-level locking will be automatically enabled without modifying the application code. The application will also be able to share an open database between threads, or connect to dbserver to share databases between multiple applications running on one or more devices.
Documentation for ITTIA DB SQL™ consists of several documents:
User's Guide
C API Reference Manual
C++ API Reference Manual
All products in the ITTIA DB family – Compact, Standard, and Plus – are covered by the documentation. To find out whether a feature described in the documentation is available in a particular edition of ITTIA DB, see Table 1.2, “Features Available in Each Edition of ITTIA DB SQL™”.
The User's Guide provides an introduction to software development with ITTIA DB SQL™. Example code from the phonebook example application is used to demonstrate many key concepts and tasks.
Consult the API Reference Manuals for detailed information about specific ITTIA DB SQL™ API functions.
Table of Contents
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.
Where data is stored controls performance and durability.
A file storage database is saved to disk continuously. Data is organized into large pages to take advantage of block device performance characteristics. The algorithms used to access data in a file storage database offer consistent overall performance, even as the size of the database grows to exceed the size of main memory.
A memory storage database is stored primarily in memory. Direct pointers are used internally so that individual operations always complete in a predictable amount of time. For this reason, the size of a memory storage database is limited by the size of main memory.
A hybrid database uses both file and memory storage to store both disk and memory tables in the same database. In this way, applications can balance requirements for durability and performance by creating some tables as memory tables. A hybrid database is created by setting the memory storage size when opening a file storage database.
![]() | Warning |
|---|---|
Avoid creating a memory storage that is larger than main memory. Virtual memory page faults will occur frequently in a large memory storage database. Instead, use a file storage and store some or all data in disk tables. The paging algorithms used for disk tables are specifically designed to minimize paging for table data. |
The storage model can be changed easily because it does not affect the way that tables are used. The only differences are in:
How the application connects to the database.
Whether tables are created on disk or in memory. For file storage, disk tables are created by default. For memory storage, memory tables are created by default.
Performance characteristics.
What data is lost when the database is closed or an unexpected failure occurs.
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.
Table of Contents
An ITTIA DB SQL™ 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 ITTIA DB SQL™ configurations 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. SQL queries can be executed from either C or C++ API.
The following example program demonstrates how to create an empty database, insert data, and perform an indexed search with direct table access:
Example 3.1. C++ Hello World Program with Table Cursors
/**************************************************************************/
/* */
/* Copyright (c) 2005-2011 by ITTIA L.L.C. All rights reserved. */
/* */
/* This software is copyrighted by and is the sole property of ITTIA */
/* L.L.C. All rights, title, ownership, or other interests in the */
/* software remain the property of ITTIA L.L.C. This software may only */
/* be used in accordance with the corresponding license agreement. Any */
/* unauthorized use, duplication, transmission, distribution, or */
/* disclosure of this software is expressly forbidden. */
/* */
/* This Copyright notice may not be removed or modified without prior */
/* written consent of ITTIA L.L.C. */
/* */
/* ITTIA L.L.C. reserves the right to modify this software without */
/* notice. */
/* */
/* info@ittia.com */
/* http://www.ittia.com */
/* */
/* */
/**************************************************************************/
// 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 row." << std::endl;
}
// Save changes to the database file.
db.tx_commit();
// Clean up.
helloWorld.close();
db.close();
return 0;
}
The following example shows how the previous example can be written using SQL.
Example 3.2. C++ Hello World Program with SQL
/**************************************************************************/
/* */
/* Copyright (c) 2005-2011 by ITTIA L.L.C. All rights reserved. */
/* */
/* This software is copyrighted by and is the sole property of ITTIA */
/* L.L.C. All rights, title, ownership, or other interests in the */
/* software remain the property of ITTIA L.L.C. This software may only */
/* be used in accordance with the corresponding license agreement. Any */
/* unauthorized use, duplication, transmission, distribution, or */
/* disclosure of this software is expressly forbidden. */
/* */
/* This Copyright notice may not be removed or modified without prior */
/* written consent of ITTIA L.L.C. */
/* */
/* ITTIA L.L.C. reserves the right to modify this software without */
/* notice. */
/* */
/* info@ittia.com */
/* http://www.ittia.com */
/* */
/* */
/**************************************************************************/
// 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 to execute SQL statements.
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, "message");
for (rc = q.seek_first();
DB_SUCCESS(rc) && !q.is_eof();
rc = q.seek_next())
{
cout << String(message).c_str() << endl;
}
// Save changes to the database file.
q.exec_direct(db, "commit");
// Clean up.
q.close();
db.close();
return 0;
}
The ITTIA DB SQL™ kernel is a software library that can be directly embedded in an application, or accessed through a separate server processes. The database kernel provides a transactional C API that the application can use directly, or through ITTIA DB's C++ API bindings.
To minimize an application's code footprint and optimize performance, ITTIA DB SQL™ includes several alternative configurations of the ITTIA DB kernel that can be substituted for the default kernel library. These configurations omit specific features without changing the API interface. Available libraries are listed in Table 3.1, “ITTIA DB SQL™ Kernel Libraries for Windows” and Table 3.2, “ITTIA DB SQL™ Kernel Libraries for gcc”.
| Configuration | Export Library | Run-time DLL |
|---|---|---|
| Default |
ittiasql.lib
|
ittiasql.dll
|
| Storage-level locking |
ittiasql_slock.lib
|
ittiasql_slock.dll
|
| IPC client only |
ittiasql_client.lib
|
ittiasql_client.dll
|
| Local storage only, with SQL |
ittiasql_local.lib
|
ittiasql_local.dll
|
| Local storage only, without SQL |
ittiadb_local.lib
|
ittiadb_local.dll
|
| Local storage only, with storage-level locking |
ittiasql_slock_local.lib
|
ittiasql_slock_local.dll
|
| Single-user local storage, with SQL |
ittiasql_su_local.lib
|
ittiasql_su_local.dll
|
| Single-user local storage, without SQL |
ittiadb_su_local.lib
|
ittiadb_su_local.dll
|
| Compact kernel |
ittiadb_compact_su_local.lib
|
ittiadb_compact_su_local.dll
|
Table 3.1. ITTIA DB SQL™ Kernel Libraries for Windows
| Configuration | Argument |
|---|---|
| Default |
-littiasql
|
| Storage-level locking |
-littiasql_slock
|
| IPC client only |
-littiasql_client
|
| Local storage only, with SQL |
-littiasql_local
|
| Local storage only, without SQL |
-littiadb_local
|
| Local storage only, with storage-level locking |
-littiasql_slock_local
|
| Single-user local storage, with SQL |
-littiasql_su_local
|
| Single-user local storage, without SQL |
-littiadb_su_local
|
| Compact kernel |
-littiadb_compact_su_local
|
Table 3.2. ITTIA DB SQL™ Kernel Libraries for gcc
![]() | Note |
|---|---|
ITTIA DB-SQL Plus™ and the evaluation packaging include all available configurations. ITTIA DB-SQL Standard™ contains the single-user local storage configurations only. ITTIA DB Compact™ contains only the Compact kernel configuration. |
The Storage-level locking configuration provides low-overhead multi-user capability by locking the entire storage whenever the database is modified. The default library locks individual rows..
The IPC client only configuration is a lightweight inter-processes communications (IPC) client library that can only connect to remote databases using dbserver. When this configuration is used, databases filenames must be of the form idb-tcp:// or server[:port]/databaseidb-shm://, where share/databaseserver is the name or IP address of the server, port is the TCP/IP port number, share is the name of a shared memory area, and database is the name of a database file on the server.
Local storage only configurations can only open local database files and cannot connect to remote servers. However, multiple threads in a single process can share access to a local database file with automatic row level locking provided by the database kernel.
![]() | Tip |
|---|---|
For more details on client/server and local multi-threaded shared access, see Chapter 7, Shared Database Access. |
Single-user local storage configurations support only one database connection at a time.
Some configurations do not include support for SQL. Preparing an SQL statement using one of these libraries will produce an error.
To get started with ITTIA DB SQL™, 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.
![]() | Note |
|---|---|
The following instructions assume that ITTIA DB SQL™ has been installed to the directory |
The following steps show how to add ITTIA DB SQL™ to a Visual C++ Project.
Open Visual Studio and create a new Win32 Console Application project.
Open the settings for your project from the menu: → .
Select All Configurations from the Settings For list.
In the Project Settings dialog, select the tab and from the Category list, select Preprocessor.
Add the folder $DBDIR\win32\include to the Additional include directories property.
In the Project Settings dialog, select the tab and from the Category list, select Input.
Add $DBDIR\win32\lib to the Additional library path property.
Add ittiasql.lib to the Object/library modules property.
Click to close the project settings dialog.
For any project that uses the C++ API, follow these additional steps:
Add the project $DBDIR\win32\dbcppapi\dbcppapi.dsp to the workspace, using the menu item: →
Include the dbcppapi project as a dependency. Select your project, then go to the menu item: → .
Check dbcppapi in the list that is presented and click .
In the Project Settings dialog, select the tab and from the Category list, select Code Generation.
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. |
Select Win32 Debug from the Settings For list. Set the Use run-time library property to Debug Multi-threaded DLL.
Add application source code files to the project. You can use the C++ example code to get started.
Copy ittiasql.dll from $DBDIR\win32\lib to the project directory.
Build and run your application.
The following steps show how to add ITTIA DB SQL™ to a Visual C++ Project.
Open Visual Studio and create a new Win32 Console Application project.
Open the settings for your project from the menu: → .
Select All Configurations from the Configuration list.
Select the property page: → → .
Add $DBDIR\win32\include to the Additional Include Directories property.
Select the property page: → → .
Add $DBDIR\win32\lib to the Additional Library Directories property.
Select the property page: → → .
Add ittiasql.lib to the Additional Dependencies property.
Click to close the project settings dialog.
For any project that uses the C++ API, follow these additional steps:
Add the project $DBDIR\win32\dbcppapi\dbcppapi.dsp to the workspace, using the menu item: → → .
Answer when asked whether you should convert and open this project.
Include the dbcppapi project as a dependency for your project. Select your project, then go to the menu item: → .
Check dbcppapi in the list that is presented and click .
Open the settings for your project again and select the property page: → → .
Select Release from the Configuration list. Set the Runtime Library property to Multi-threaded DLL (/MD).
![]() | 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. |
Select Debug from the Configuration list. Set the Runtime Library property to Multi-threaded Debug DLL (/MDd).
Add application source code files to the project. You can use the C++ example code to get started.
Copy ittiasql.dll from $DBDIR\win32\lib to the project directory.
Build and run your application.
The following steps show how to add ITTIA DB SQL™ to an eMbedded Visual C++ Project:
Open eMbedded Visual Studio and create a new Win32 Application project.
Open the settings for your project from the menu: → .
Select All Configurations from the Settings For: list.
Select the property page: → → .
Add $DBDIR\win32\include to the Additional Include Directories property.
Select the property page: → → .
Add $DBDIR\win32\lib to the Additional Library Directories property.
Add ittiasql.lib to the Object/library modules: property.
Click to close the project settings dialog.
For any project that uses the C++ API, follow these additional steps:
Add the project $DBDIR\win32\dbcppapi\dbcppapi.vcp to the workspace, using the menu item: → .
Answer when asked whether you should convert and open this project.
Include the dbcppapi project as a dependency for your project. Select your project, then go to the menu item: → .
Check dbcppapi in the list that is presented and click .
Add application source code files to the project. You can use the C++ example code to get started.
Copy ittiasql.dll from $DBDIR\win32\lib to the project directory.
Build and run your application.
On the command line:
When compiling source files, add ITTIA DB SQL™'s include directory to the include path:
g++ -I$DBDIR/include main.cpp -o main.oInclude the ITTIA DB SQL™ library when linking object files together into an executable program:
g++ -L$DBDIR -ldbcppapi -littiasql main.o -o programIf only the C API is needed, the C++ API library can be omitted from the above command by removing the -ldbcppapi option. While gcc can be used to compile source code files, g++ must be used to link the application.
When compiling source files, add ITTIA DB SQL™'s include directory to the include path:
gcc -I$DBDIR/include main.c -o main.oInclude the ITTIA DB SQL™ library when linking object files together into an executable program:
g++ -L$DBDIR -littiadb main.o -o programTo run the application, Linux must be able to find the ITTIA DB shared library. When the application is deployed, either install the library to /usr/lib or add the directory in which the shared library is installed to /etc/ld.so.conf, then run ldconfig. During development, the environment variable LD_LIBRARY_PATH can be set to a colon-separated list of directories where libraries should be search for.
![]() | Tip |
|---|---|
The |
ITTIA DB SQL™ 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 SQL™. 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 named 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
To build and run the example program in Visual C++ 6:
Open the workspace $DBDIR\examples\examples.dsw. This workspace can also be accessed from the Start Menu icon for Example project (VC++ 6).
Select an example with: → .
Run the example with: → .
To build and run the example program in Visual Studio 2005:
Open the workspace $DBDIR\examples\examples.dsw. This workspace can also be accessed from the Start Menu icon for Example project (VS2005).
Click on an example project in Solution Explorer, then select → from the menu.
Run the example with: → .
To build and run the example program in Microsoft eMbedded Visual C++:
Open the workspace $DBDIR\examples\examples.vcw.
Select Active WCE Configuration on the Workspace Configuration Tool-Bar.
Select Active Target Configuration on the Workspace Configuration Tool-Bar.
Select Default Device on the Workspace Configuration Tool-Bar.
Run the example with: → .
![]() | Note |
|---|---|
The required dll files for these examples must be copied to the device or emulator. If eMbedded Visual Studio does not copy these dlls to the target environment, you must copy them manually using the Remote File Viewer tool. |
To build and run the example program with gcc:
Change to the directory $DBDIR/example
Run make
Run ./phonebook to start the C++ API example program.
Run ./phbook to start the C API example program.
ITTIA DB SQL™ 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 SQL™. The single user version is not thread safe and requires that all database operations occur in a single thread. |
ITTIA DB SQL™ 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.
Table of Contents
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 development tool because it allows a developer to quickly inspect the contents of a database without writing any additional program code.
ITTIA DB SQL™ 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 SQL™. See Section 3.3, “Linking ITTIA DB SQL™ 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. |
The ittiasql utility is an easy way to learn about ITTIA DB SQL™. The following session creates a database file and initializes the schema.
Example 4.1. CREATE TABLE
$.create example.dbexample.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. |
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 | +----------+--------+----+----------+
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 | +----+--------+----+----------+
Joins are described in detail in Section 4.2.2.1, “FROM Clause”.
Literal values in an SQL statement can be replaced by parameters. This feature allows an application to separate variable data from the logic of the query, improving both performance and security.
ITTIA DB SQL™ reads parameter values directly from application memory without parsing or unnecessary conversion. This avoids the overhead of converting numeric values to strings and then back when the query is executed. Frequently-used statements are automatically cached by the database and will execute more quickly on subsequent runs. This performance benefit is only available for identical statements executed multiple times, but parameter values for that statement can be changed.
When string literals are inserted directly into an SQL statement, the application is susceptible to SQL injection attacks. A carefully designed string can contain SQL code that modifies the meaning of the SQL statement, allowing the attacker to execute arbitrary SQL commands. Fortunately, a fully parameterized query is completely impervious to this kind of attack.
![]() | Important |
|---|---|
Use of parameters is highly recommended in all application queries. Never construct an SQL statement string that contains values supplied by the user or from a source outside the program's control. |
To use parameters, replace literal values with the ? placeholder, as shown in Example 4.5, “Positional SQL Query Parameters”. In the ittiasql utility, values for each parameter are then input one at a time. In application source code, parameter values can be set through the API before the query is executed.
Use the ? placeholder to order parameters by position.
Example 4.5. Positional SQL Query Parameters
example.db$ insert into contact (id, name) values (?, ?); param[0] (integer): 2 param[1] (string): Charley example.db$ insert into phone_number (contact_id, number, type) values (?, ?, ?); param[0] (integer): 2 param[1] (string): 555-4444 param[2] (integer): 0 example.db$ select * from contact where name = ?; param[0] (string): Charley +--+-------+-------+------------+-------+ |ID|NAME |RING_ID|PICTURE_NAME|PICTURE| +--+-------+-------+------------+-------+ |2 |Charley|NULL |NULL |NULL | +--+-------+-------+------------+-------+
Parameters are identified by the order they appear in the SQL statement, starting from zero. The first ? placeholder to appear in the statement corresponds to parameter 0, the second ? placeholder corresponds to parameter 1, and so on.
The ? placeholder can only appear in a WHERE clause or a VALUES list. The data type of the parameter is inferred from the expression it is used in.
An alternative placeholder syntax is also available in ITTIA DB SQL™ that provides greater parameter control. The ? placeholder can be replaced with the $<type>n placeholder. Replace type with the name of the column data type to use for the parameter and n with the parameter number. See Example 4.6, “Numbered SQL Query Parameters” for an example using this syntax.
With numbered parameters, it is possible to use the same parameter multiple places in the query. And because they are explicitly typed, numbered parameters can also appear in the SELECT list where the data type of the parameter cannot be inferred from context.
Example 4.6. Numbered SQL Query Parameters
example.db$ select * from contact where name = $<nvarchar>0; param[0] (string): Charley +--+-------+-------+------------+-------+ |ID|NAME |RING_ID|PICTURE_NAME|PICTURE| +--+-------+-------+------------+-------+ |2 |Charley|NULL |NULL |NULL | +--+-------+-------+------------+-------+
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.
The grammar starts with the name of a symbol and is followed by a description of the syntax for that symbol.
An SQL keyword is shown in all caps. In practice, SQL keywords are case insensitive and can be used with any capitalization.
A replaceable symbol is replaced with syntax defined for that symbol elsewhere in the documentation.
A replaceable keyword is replaced with an SQL keyword listed in a table, such as Table 4.2, “SQL Column Types”.
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 syntax is enclosed in square brackets.
Multiple choices are separated by the pipe (|) symbol. Only one choice should be used.
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.
| add | all | alter |
| and | ansistr | as |
| asc | avg | between |
| bigint | blob | by |
| case | cast | coalesce |
| column | commit | committed |
| completion | concat | count |
| counted | create | cross |
| currency | current | current_date |
| current_datetime | current_time | current_timestamp |
| date | datetime | delete |
| desc | distinct | drop |
| else | end | except |
| float | float32 | float64 |
| for | forced | from |
| full | group | in |
| index | inner | insert |
| int | int16 | int32 |
| int64 | int8 | integer |
| intersect | into | is |
| isolation | join | key |
| lazy | left | level |
| localdatetime | localtime | localtimestamp |
| max | memory | min |
| modify | natural | nested |
| next | not | null |
| nullif | nvarchar | on |
| only | or | order |
| outer | override | primary |
| read | release | rename |
| repeatable | right | rollback |
| savepoint | select | sequence |
| serializable | set | sint16 |
| sint32 | sint64 | sint8 |
| smallint | start | sum |
| table | then | time |
| timestamp | tinyint | to |
| transaction | uint16 | uint32 |
| uint64 | uint8 | uncommitted |
| union | unique | unsigned |
| update | using | utf16str |
| utf32str | utf8str | value |
| values | varchar | when |
| where | with | work |
| write |
Table 4.1. SQL Reserved Keywords
ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.
Data types are listed in Table 4.2, “SQL Column Types”. Each data type has one or more SQL column type names, which can be used interchangeably. The column type name is used when a table is created or altered. See Section 4.2.8, “Schema Definition” for more information.
| 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 |
| currency | CurrencyField | db_sint32_t | 4 bytes |
varchar(n), ansistr(n) | StringField | db_ansi_t, char * | 1/2 page size |
utf8str(n) | WStringField | db_utf8_t, char * | 1/2 page size |
nvarchar(n), utf16str(n) | WStringField | db_utf16_t | 1/2 page size |
utf32str(n) | WStringField | db_utf32_t | 1/2 page size |
| 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. |
Integer types are signed or unsigned, indicated by a "s" or "u" prefix to the type name. Numeric types also have a suffix that indicates the number of bits allocated to the data type. Type names are listed in Table 4.2, “SQL Column Types”.
Integer types can be mixed in expressions and are always converted to the largest type.
ITTIA DB SQL™ provides two floating-point types: float32, and float64. The float type is an alias for float64.
| Floating-Point Type | Maximum Value |
|---|---|
| float32 | 3.4028234 x 1038 |
| float64 | 1.7976931348623157 x 10308 |
ITTIA DB SQL™ also includes a 32-bit currency data type that has a fixed precision of 2 decimal digits. When a currency value is accessed through the C or C++ API, the value is stored in a sint32_t variable that must be divided by 100 to obtain the correct value stored in the database.
ITTIA DB SQL™ 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 SQL™ ̵ 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.
To store a string in UTF-8 encoding, use the utf8str type. Similarly, use utf16str for UTF-16 encoding and utf32str for UTF-32 encoding. The type nvarchar is an alias for utf16str.
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 SQL™ also supports the ANSI character format with the ansistr type, which is also aliased as varchar. ITTIA DB assumes that ANSI character strings are encoded using the current locale, which is a platform-dependent setting. The most commonly encoding is ISO 8859-1.
String literals are formed by enclosing text in quotes and are of type varchar.
ITTIA DB SQL™ uses a common string format for all date and time types.
Date and time literals are formed by following the name of the data type with a string literal in a specific format. The literal formats accepted by ITTIA DB SQL™ are listed in Table 4.3, “Date and Time Literal Formats”.
| SQL Column Type | Literal Format | Literal Examples | |||
|---|---|---|---|---|---|
| date | date 'YYYY-mm-dd' |
| |||
| time | time 'HH:MM:SS' |
| |||
| datetime | datetime 'YYYY-mm-dd HH:MM:SS' | datetime '2008-05-23 10:12:05' | |||
| timestamp | timestamp 'YYYY-mm-dd HH:MM:SS' | timestamp '2008-05-23 10:12:05' | |||
| timestamp | timestamp 'YYYY-mm-dd HH:MM:SS.ffffff' | timestamp '2008-05-23 10:12:05.123456' |
Table 4.3. Date and Time Literal Formats
ITTIA DB SQL™ has a special value called NULL. When a value is NULL, it indicates that the value is unknown or has not been set. Every column type supports NULL. If a value is not specified for a column when a row is inserted, the default value is NULL.
Comparison operators handle NULL as a special case. When any value is compared with NULL, the result is false. To determine whether a value is NULL, use the expression "value IS NULL" or "value IS NOT NULL".
![]() | Tip |
|---|---|
In C and C++, NULL is a pointer type equal to 0. In SQL, 0 and NULL are different values. |
SELECT
[ ALL | DISTINCT ] field,...
[FROM table-expression]
[WHERE search-condition]
[GROUP BY field,... ]
[ORDER BY order-by]
[{ UNION | EXCEPT | INTERSECT } [ DISTINCT | ALL ] 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
| (term)
| function( arguments,... )
| { NEXT | CURRENT } VALUE FOR sequence-name
| CAST (term AS column-type)
| CASE {WHEN search-condition THEN term}... [ELSE term] END
| CASE term {WHEN term 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
The CAST keyword is used to convert a term to a different data type.
select 'The ID is: ' || cast (id as varchar) from contact
The CASE keyword provides conditional logic. There are two forms of CASE. One checks a series of search conditions, similar to an if statement in C. The other form compares two terms and uses the first match. The following example shows both forms:
select number,
case
when speed_dial >= 0 and speed_dial <= 9 then speed_dial
else null
end as speed_dial,
case type
when 1 then 'Mobile'
when 2 then 'Work'
when 3 then 'Fax'
when 4 then 'Pager'
else number
end as type
from phone_number
The sequence expressions NEXT VALUE FOR and CURRENT VALUE FOR are described in nextval(str) and currval(str).
FROM
table-expression
table-expression ::=
table-reference
| table-expression [join-type] JOIN table-reference [join-condition]
| table-expression NATURAL [join-type] JOIN table-reference [join-condition]
| table-expression,...
table-reference ::=
table-name [[AS] correlation-name]
join-type ::=
CROSS | INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER
join-condition ::=
ON search-condition
| USING (column-list)
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
The FROM clause is optional. When the FROM clause is omitted, the select list must contain only literal values, which will be returned as a single row.
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.4. SQL Comparison Operators
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.5, “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.5. 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.
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
select-statement { UNION | EXCEPT | INTERSECT } [ DISTINCT | ALL ] 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. Two rows match only when all fields are identical.
An SQL statement can use multiple set operations to merge results from more than two SQL queries. UNION has the highest precidence. EXCEPT and INTERSECT have equal precidence and are evaluated from left to right.
select * from a union select * from b union select * from c except select * from d union select * from e
The result of this example is grouped as follows: (a union b) except (d union e).
Subqueries can be used to control group set operations.
select *
from (
select * from a
intersect
select * from b
)
union
select *
from cThe result of this example is grouped as follows: (a intersect b) union c.
Set operations can also include the DISTINCT or ALL keyword to control how duplicate values are handled in the result. By default, set operations are DISTINCT, meaning that each row in the result will be unique. The ALL keyword permits duplicate rows to appear in the result.
In ITTIA DB SQL™, SQL statements can use the natural numbers virtual table, $nat(count), to select from a list of integers. The resulting table contains a single column named "n" listing the first count natural numbers.
$select * from $nat(10);+-+ |N| +-+ |0| |1| |2| |3| |4| |5| |6| |7| |8| |9| +-+ $select n + 1, 'Number ' || cast(n + 1 as varchar) from $nat(4);+--+--------+ |$0|$1 | +--+--------+ |1 |Number 1| |2 |Number 2| |3 |Number 3| |4 |Number 4| +--+--------+ $insert into some_table (n) select * from $nat(1000);
Several built-in functions are available to perform common mathematical and string operations. Available functions are listed in Table 4.6, “SQL Built-in Functions”. Functions can be used in the SELECT field list, JOIN ON condition, WHERE clause, GROUP BY clause, ORDER BY clause, and INSERT INTO ... VALUES list.
| 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(n, ...) | Number | Lowest value listed. Result is NULL if any value is NULL. |
greatest(n, ...) | Number | 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. |
nextval(str) | String | Current value for the sequence identified by str. |
currval(str) | String | Next value for the sequence identified by str. |
Table 4.6. SQL Built-in Functions
abs(n)Absolute value of n.
$select abs(1);1 $select abs(-10);-10 $select abs(-3.1415);-3.1415
sign(n)Sign of n: -1 for negative, 0 for zero, +1 for positive. The result is the same type as the argument.
$select sign(8);1 $select sign(0);0 $select sign(-16.9);-1.0
mod(n, m)Remainder of n divided by m.
$select mod(23, 10);3 $select mod(23, -10);3 $select mod(-23, 10);3 $select mod(17, 8);3
least(n, ...)Lowest value listed. Result is NULL if any value is NULL.
Each value can be a different numeric type. The return value will be the most general type listed.
$select least(60, 22, 19, 37);19 $select least(5, 10, null, -1);NULL $select least(-5.1, -10);-10.0
greatest(n, ...)Greatest value listed. Result is NULL if any value is NULL.
Each value can be a different numeric type. The return value will be the most general type listed.
$select greatest(60, 22, 19, 37);60 $select greatest(5, 10, null, -1);NULL $select greatest(-5.1, -10);-5.1
trim(str)str with leading and trailing spaces removed.
$ select trim(' Hello World ');
'Hello World'ltrim(str)str with leading spaces removed.
$ select ltrim(' Hello World ');
'Hello World 'rtrim(str)str with trailing spaces removed.
$ select rtrim(' Hello World ');
' Hello World'substr(str, pos, len)Substring of str of length len, starting from pos, where the first character is position 1.
If pos is less than 1, it is treated as though it were 1.
$select substr('Hello World', 1, 5);'Hello' $select substr('Hello World', 3, 5);'llo W'
substr(str, pos)Substring of str, starting from pos, where the first character is position 1, to the end of the string.
If pos is less than 1, it is treated as though it were 1.
$select substr('Hello World', 7);'World' $select substr('Hello World', 100);''
contcat(str, ...)Concatenate listed strings.
This function is equivalent to the string concatenation operator "||".
$select concat('Hello', ' ', 'World');'Hello World' $select 'Hello' || ' ' || 'World';'Hello World'
coalesce(a, ...)Return the first value that is not NULL from a list of values.
All values must have the same data type.
$select coalesce(null, 1, 2);1 $select coalesce(3, null, 4);3
The coalesce function is a convenient shorthand that could otherwise be expressing using a CASE statement. Example 4.7, “coalesce vs. case” shows two equivalent queries, the first using coalesce and the second using a CASE statement.
Example 4.7. coalesce vs. case
select coalesce(a, b, c) from some_table
select
case
when a is not null then a
when b is not null then b
when c is not null then c
else null
end
from some_tablenextval(str)Obtain the next value from the sequence generator identified by str. For the given sequence generator, the value returned is guaranteed to be unique and greater than any value previously returned.
![]() | Tip |
|---|---|
|
$create sequence myid start with 1;$select nextval('myid');2 $select next value for myid;3 $insert into some_table (n) values (next value for myid);
currval(str)Obtain the current value from the sequence generator identified by str.
![]() | Tip |
|---|---|
|
$create sequence myid start with 1;$select currval('myid');1 $select current value for myid;1 $select next value for myid;2 $select current value for myid;2 $insert into some_table (n) values (current value for myid);
Three kinds of modifications to database rows are available: insert new rows, update existing rows, and delete rows. Any modification of the database will start a transaction, which must be committed before changes will become persistent. For more information, see Section 4.2.6, “Transactions”.
INSERT INTO
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 = 1UPDATE 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'
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. |
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.
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. |
Savepoints are used within a transaction to support partial rollback without reverting the entire transaction. Savepoints can be nested, allowing them to be used with nested function calls.
Locks obtained during the savepoint are not released until the end of the enclosing transaction.
SAVEPOINT
[savepoint-name [ UNIQUE | OVERRIDE | NESTED ]]
Set a savepoint. The savepoint can be assigned a optional name. UNIQUE ensures that no savepoint exists with the same name. OVERRIDE replaces existing savepoints with the same name. NESTED allows multiple savepoints to be created with the same name.
A transaction is started if not already active. Savepoints are automatically released when the transaction is committed or rolled back.
RELEASE SAVEPOINT
[ savepoint-name | CURRENT ]
Release a savepoint. Use CURRENT to release the most recently created savepoint.
When a savepoint is released, it can no longer be used to roll back to beginning of the savepoint. However, this does not garauntee that any work will be saved. If the enclosing transaction or an enclosing savepoint is rolled back, work performed in this savepoint will be lost.
Any savepoints that were subsequently set are automatically released.
ROLLBACK TO SAVEPOINT
[ savepoint-name | CURRENT ]
ROLLBACK WORK TO SAVEPOINT
[ savepoint-name | CURRENT ]
When a savepoint is rolled back, the database is returned to the state it was in when the savepoint was set. Any savepoints that were subsequently set are automatically rolled back as well.
Schema definition statements alter the layout of the database. These statements cannot be used when a transaction is active and cannot be rolled back.
CREATE [MEMORY] TABLE
table-name (
{ column-name column-type [ NOT NULL | NULL ] [ PRIMARY KEY | UNIQUE ] [reference]}, ...
[[CONSTRAINT pk-name] PRIMARY KEY ( column-name, ... ),]
[[CONSTRAINT fk-name] FOREIGN KEY ( column-name, ... ) reference], ...
[[CONSTRAINT uc-name] UNIQUE ( column-name, ... )], ...
)
reference ::=
REFERENCES ref-table [( ref-column, ... )]
[ MATCH FULL | MATCH SIMPLE ]
[ ON UPDATE RESTRICT | ON UPDATE CASCADE | ON UPDATE SET NULL | ON UPDATE SET DEFAULT ]
[ ON DELETE RESTRICT | ON DELETE CASCADE | ON DELETE SET NULL | ON DELETE SET DEFAULT ]
Create an empty table with listed columns. Each column is assigned a name and a type from Table 4.2, “SQL Column Types”.
If the MEMORY keyword is used, or the database is in a memory storage, the table is stored in memory. If the MEMORY keyword is not used, and the database is in a file storage, the table is stored on disk. For more information about memory tables, see Section 2.2, “File and Memory Storage”.
To require that a column contain no null values, use the NOT NULL option. The default option, NULL, allows null values in the column.
A key consists of one or more columns that must together be unique among all rows of the table. Each table can have one primary key, and any number of unique key constraints. Columns in the primary key cannot be NULL, though the NOT NULL option is implied. Columns in a unique key can be NULL.
An index is created for each primary key and each unique key with the same name as the constraint. If a name is not specified for the constraint, it is assigned a random name.
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 not null, name nvarchar(50), ring_id uint64, picture_name varchar(50), picture blob, constraint by_id primary key (id) )
Foreign keys can be created either by referencing another table in a column definition or by declaring a foreign key constraint. If a list of referenced columns is not provided for a foreign key, the other table's primary key is used by default.
If the foreign key uses more than one field, at least one of which can be null, then select an appropriate match option. MATCH SIMPLE is the default.
Some fields in the foreign key may be null, in which case no row is referenced. A row is only referenced when no foreign key fields are null.
All fields in the foreign key must be null if any are null. A row is only referenced when no foreign key fields are null.
When a row is updated or deleted in the referenced table, the database will act according to the action set in the foreign key for that operation.
The action cannot be performed on a referenced row.
When the action is performed on key fields in a referenced row, the same action is performed on referencing rows.
When the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to null.
When the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to their default values.
Foreign keys are always checked immediately whenever a row is inserted into the referencing table, deleted in the referenced table, or updated in either table. However, the foreign key has no effect when other columns are updated in either table.
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)
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
ALTER TABLE
table-name
ADD COLUMN column-name column-type [ NOT NULL | NULL ] [ PRIMARY KEY | UNIQUE | INDEX ]
| DROP COLUMN column-name
| ADD [CONSTRAINT pk-name] PRIMARY KEY ( column-name,... )
| DROP PRIMARY KEY
| ADD [CONSTRAINT fk-name] FOREIGN KEY ( column-name, ... ) reference
| ADD [CONSTRAINT uc-name] UNIQUE ( column-name,... )
| DROP UNIQUE ( column-name,... )
| DROP CONSTRAINT constraint-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.
The ALTER TABLE statement can also be used to add and remove constraints, including primary keys, foreign keys, and unique constraints.
The syntax for a foreign key reference is defined in Section 4.2.8.1, “CREATE TABLE”. If a list of referenced columns is not provided for a foreign key, the other table's primary key is used by default.
alter table contact add column age integer; alter table contact add unique (age); alter table contact drop unique (age); alter table contact drop column age; alter table phone_number drop constraint contact_ref; alter table phone_number add constraint contact_ref foreign key (contact_id) references contact(id) on delete cascade on update set null;
DROP TABLE
table-name
DROP TABLE removes a table and all of its contents from the database, including indexes. This action cannot be undone.
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.
DROP SEQUENCE
sequence-name
DROP SEQUENCE removes a sequence generator from the database.
Table of Contents
This section describes how to connect to a database with the ITTIA DB C++ API. For a simple example of common operations using the C++ API, see Section 3.2, “C++ API Examples”.
To use the ITTIA DB SQL™ C++ API, include the header ittia/db++.h.
#include <ittia/db++.h>
The API is encapsulated in the db namespace.
A database is managed by a Database object. To create a database for the first time, use the object's create() function.
db::Database db;
db::StorageMode mode;
int rc;
rc = db.create("phone_book.db", mode);
if (DB_FAILED(rc)) {
cerr << "Error creating database: " << rc << endl;
}![]() | Caution |
|---|---|
If the file already exists, |
To open an existing database, use the open() function.
rc = db.open("phone_book.db", mode);
if (DB_FAILED(rc)) {
cerr << "Error opening database: " << rc << endl;
}When finished with the database, call close().
db.close();
The database will also close automatically when the Database object is destroyed.
The following example creates a memory storage named memory.db.
db::Database db;
db::StorageMode mode;
int rc;
mode.file_mode = db::DB_MEMORY_STORAGE;
mode.memory_storage_size = 4 * 1024 * 1024;
rc = db.create("memory.db", mode);
if (DB_FAILED(rc)) {
cerr << "Error creating database: " << rc << endl;
}When a memory storage is created, the application must specify the size of the memory storage. This is the number of bytes that will be allocated for tables and indexes. The size of the database cannot exceed this amount.
An application can create multiple storages in memory if each storage has a different name. Other threads and clients can connect to an existing memory storage using open().
rc = db.open("memory.db", mode);The memory_storage_size parameter is ignored when opening an existing memory storage. If another thread or client has already created the memory storage, the existing size will be used. Otherwise, open() will return a NULL handle and the error DB_ENOENT will be set.
When finished with the database, call close() to close the connection. When the last connection is closed, the storage is destroyed.
db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);
To connect to a database containing tables both in memory and on disk, set the memory_storage_size parameter before creating or opening a file storage. The following example creates a new file storage with support for memory tables.
db::Database db;
db::StorageMode mode;
int rc;
// Optional: file storage is the default.
mode.file_mode = db::DB_FILE_STORAGE;
mode.memory_storage_size = 4 * 1024 * 1024;
rc = db.create("file.db", mode);
if (DB_FAILED(rc)) {
cerr << "Error creating database: " << rc << endl;
}The memory storage size must be specified when opening an existing file storage. If necessary, an empty memory storage will be allocated.
When all connections to a hybrid database are closed, memory tables will not be dropped, but all memory tables will be empty the next time the database is opened.
Most C++ API functions return an error code. To determine whether an operation was successful, check the return code with DB_SUCCESS() or DB_FAILED().
if (DB_SUCCESS(rc)) {
cout << "Operation successful" << endl;
}
if (DB_FAILED(rc)) {
cout << "Operation failed with error code: " << rc << endl;
}![]() | Tip |
|---|---|
Error codes are listed in the Error Handling section of the API Reference. The same error codes are used in both the C and C++ APIs. |
Various features of ITTIA DB SQL™ can be configured dynamically at run-time. Changing these configurations in relation to available hardware and data access patterns can provide optimized performance.
The following options can be set by passing a LibraryConfig object to the Database::initialize() function:
memory_modeSelect a memory optimization strategy:
LibraryConfig::TIGHT
LibraryConfig::COMPACT
LibraryConfig::LARGE
transactionsApproximate number of concurrent transactions expected.
The following options can be set in the StorageMode object before opening or creating a database:
open_flagsSelect read-only access or disable logging to the journal.
buffer_countSets the number of pages to allocate for caching database pages. The total size of the buffers will be buffer_count * page_size.
page_sizeSets the page size for creating a new database. The page size of an existing database cannot be changed by setting this value.
checkpoint_intervalSpecifies how many operations to perform between checkpoints in the journal.
See the API Reference manual for more details on these and other settings.
Database design, like any software design, requires careful attention. When developing a database schema, a designer must consider how the data is going to be accessed to provide efficient access for the most common operations. Techniques for designing an optimal schema are beyond the scope of this document but ITTIA engineers will be happy to assist you.
An ITTIA DB SQL™ database is a collection of tables which stores application data as rows of data values. A table is defined by its list of columns, which specifies the kind of data that is to be stored. Indexes can be defined to speed up searches for a specific row or to retrieve a range of values in sorted order.
A table can have many indexes defined, and an index can be created on more than one column. Relationships between tables can be expressed by defining a unique index on one table and including the same data values in another table.
ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.
Columns are created by calling a member function of the FieldDescSet class, as described in Section 5.2.3, “Defining the Database Schema”. Data stored in a column is accessed by binding a field class to a cursor, as described in Section 5.3, “Database Access”.
Column Type |
| C++ |
|---|---|---|
sint8 |
|
|
sint16 |
|
|
sint32 |
|
|
sint64 |
|
|
uint8 |
|
|
uint16 |
|
|
uint32 |
|
|
uint64 |
|
|
float64 |
|
|
currency |
|
|
ansistr |
|
|
utf16str |
|
|
date |
|
|
time |
|
|
datetime |
|
|
timestamp |
|
|
blob |
|
|
Table 5.1. C++ Column Types
The database schema is defined programmatically by creating tables. Tables can be created at any time, and can be modified even after data has been entered into them.
![]() | Note |
|---|---|
Schema updates cannot be performed inside a transaction. See Transactions. |
To create a table, first describe the table's fields and indexes using FieldDescSet and IndexDescSet objects. Then pass these objects to Database::create_table().
db::Database db;
db::FieldDescSet fields;
db::IndexDescSet indexes;
int rc;
// ...
// Create the database
// ...
fields.add_uint("id");
fields.add_string("name");
fields.add_uint("ring_id", sizeof(db_uint), true);
indexes.add_index("id-index", db::DB_PRIMARY)
.add_field("id");
indexes.add_index("name-index", db::DB_MULTISET)
.add_field("name");
rc = db.create_table("person", fields, indexes);To create a memory table in a file storage, add a fourth parameter to create_table(): db::DB_MEMORY_TABLE.
ITTIA DB™ supports three types of indexes:
db::DB_MULTISETThe same values can occur in the index multiple times. A multiset index will never prevent a row from being inserted.
db::DB_UNIQUEThe index fields for each row must be unique. The same values cannot occur in more than one row. However, NULL can occur in more than one row because NULL is not a value.
db::DB_PRIMARYThe primary key index is identical to a unique index, except that all fields must use the DB_NOT_NULL flag. Each table can have at most one primary key index.
To modify the schema of an existing table, first use describe_table() to obtain a FieldDescSet and IndexDescSet for the current schema. Modify these objects and then call update_table() to update the schema. Fields are identified by name.
To remove a table from the database, including all data stored in the table, call drop_table().
rc = db.drop_table("person");![]() | Note |
|---|---|
Calls to |
A foreign key defines a relationship between two tables. Foreign keys are used to enforce the existance of related rows by referencing columns in another table.
db::FieldDescSet fields;
db::IndexDescSet indexes;
db::ForeignKeyDescSet foreign_keys;
// Foreign key into the "contact" table
fields.add_uint("contact_id");
// The telephone number, stored as a string
fields.add_string("number", 20);
// The type of device
fields.add_uint("type");
fields.add_sint("speed_dial", sizeof(db_sint), true);
indexes.add_index("by_contact_id", db::DB_MULTISET)
.add_field("contact_id");
foreign_keys.add_foreign_key("contact_ref", "contact", DB_FK_MATCH_SIMPLE,
DB_FK_ACTION_RESTRICT, DB_FK_ACTION_RESTRICT)
.add_field("contact_id", "id");
rc = db.create_table("phone_number", fields, indexes, foreign_keys);If the foreign key uses more than one field, at least one of which can be null, then select an appropriate match option. If not, both match options have equivalent behavior.
DB_FK_MATCH_SIMPLESome fields in the foreign key may be null, in which case no row is referenced. A row is only referenced when no foreign key fields are null.
DB_FK_MATCH_FULLAll fields in the foreign key must be null if any are null. A row is only referenced when no foreign key fields are null.
When a row is updated or deleted in the referenced table, the database will act according to the update_rule or delete_rule, respectively, in the foreign key.
DB_FK_ACTION_RESTRICTThe action cannot be performed on a referenced row.
DB_FK_ACTION_CASCADEWhen the action is performed on key fields in a referenced row, the same action is performed on referencing rows.
DB_FK_ACTION_SETNULLWhen the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to null.
DB_FK_ACTION_SETDEFAULTWhen the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to their default values.
Foreign keys are always checked immediately whenever a row is inserted into the referencing table, deleted in the referenced table, or updated in either table.
Sequence generators are used to generate unique, increasing integer values. A sequence generator is created with an initial starting value and will then retrieve successive numbers in the sequence. Each generated number is always greater than all previous numbers produced by the sequence generator. This provides a convenient source for unique identifiers.
![]() | Note |
|---|---|
Sequences may contain gaps, and so should not be used in situations where numbers cannot be skipped, such as matching with sequence numbers pre-printed on paper forms. |
To create a sequence, call create_sequence() giving a name for the sequence and its initial value.
rc = db.create_sequence("person-id", 1);If a sequences is no longer required, it can be dropped from the database.
rc = db.drop_sequence("person-id");All data access operations must occur inside a transaction. A transaction is a logical group of operations that must all succeed or fail together. Changes made to the database do not become permanent until the transaction is committed. A transaction can also be aborted, which will undo any changes made since the transaction was started.
To start a transaction, call the tx_begin() function.
rc = db.tx_begin();
To commit the changes made during the transaction, call tx_commit().
rc = db.tx_commit();
![]() | Note |
|---|---|
When the commit function returns successfully, any modifications made within the transaction are guaranteed to be completely written to the storage media. |
To cancel the changes made during the transaction, call tx_abort().
rc = db.tx_abort();
A table cursor provides direct access to a single table and its indexes. The cursor can be used to read, find, insert, update, or delete one row at a time.
An application can iterate through the table cursor's row set by seeking forward and backward. By default, the row set contains all rows in the table, in no particular order. The row set can also be filtered and/or sorted.
To open a cursor, create a Table object and call the open() function.
db::Table person; person.open(db, "person");
When you are done with the cursor, call close() to free resources. The cursor will close itself automatically when it is destroyed, but it is best to release the cursor as soon as it is no longer needed.
person.close();
Before inserting data, it is often useful to obtain a unique value from a sequence generator.
db::Sequence id_sequence; db_uint id = 0; id_sequence.open(db, "person-id"); id_sequence.get_next_value(id);
To insert data, first put the table in insert mode by calling insert(), then assign values to each field using the cursor's [] operator. These values are stored in a temporary buffer. Finally, call post() to actually insert the new row. post() copies the buffer into the table but does not commit the transaction.
person.insert();
person["id"] = id;
person["name"] = db::String("Bob");
person["ring_id"] = 7;
rc = person.post();If, after calling insert() and possibly assigning values to the fields, you do not want to insert the row into the database, call the cancel() function instead of post() to clear the buffer.
![]() | Note |
|---|---|
Data inserted into the database will not be persistent until the current transaction is committed. See Transactions. |
To traverse a table, first select an index to define the order over which the rows will be iterated.
person.set_sort_order("name-index");Then use seek_first(), seek_next(), and is_eof() to position on each row of data.
for (person.seek_first(); !person.is_eof(); person.seek_next()) {
// Perform operation on current row
}If order is not important, pass NULL to set_sort_order to traverse rows in table-scan order, which is arbitrary but fast.
To filter the row set, call begin_filter(), assign values for each field that should be compared, then call apply_filter().
contact.begin_filter(db::DB_SEEK_EQUAL);
person["name"] = db::String("Bob");
rc = contact.apply_filters();After the filter is applied, the row set is restricted to contain only rows that satisfy the filter conditions. Each field is compared separately and all conditions must be satisfied. The cursor is positioned on the first matching row.
By default, the filter is applied by scanning the table until a matching row is found. To improve performance, set an index with set_sort_order() before the filter is applied. Select an index that begins with the filtered fields.
person.set_sort_order("name-index");To restore the row set to all rows in the table, call remove_filters().
![]() | Caution |
|---|---|
The |
The begin_seek() and apply_seek() functions can also be used to search a table using an index. Unlike filters, the seek functions do not restrict the row set. Instead, the cursor is positioned on the first matching row.
To perform an index seek, set the index with set_sort_order(), Then call begin_seek() to enter seek mode, assign search criteria to each field used by the index, and call apply_seek().
person.begin_seek(db::DB_SEEK_EQUAL);
person["name"] = db::String("Bob");
rc = person.apply_seek();The fields used as search criteria must be fields of the sort index. Furthermore, if the index contains multiple fields, the search criteria must be some or all of the first fields in the index. For example, if an index has three fields, the search criteria can be either the first field in the index, the first two fields, or all three fields. Each field is compared from left to right in index order until a non-equal comparison is reached. These functions cannot be used if an index has not been set.
A table cursor can be sorted by a list of fields in the table. The fastest method of sorting is to select an index on the fields to be sorted by. The set_sort_order() function sets the current index and immediately sorts the cursor's row set.
person.set_sort_order("name-index");However, this method requires an existing index for the desired sort fields. Only one index can be selected at a time, so it is not effective if another index was used to filter the row set.
The sort() function dynamically sorts the table cursor's row set using any list of fields in the table. It uses an IndexFieldSet to sort the row set as though there were an index on those fields.
db::IndexFieldSet sort_fields;
sort_fields.add("name");
contact.sort(sort_fields);If sort is combined with filtering, apply all filters before calling the sort function. If the row set is large, the sort function may take some time and memory to perform the sort operation.
Once the cursor has been positioned on a row, the row's fields can be read using the cursor. For each field that is needed, use the operator to get the field and call the appropriate []Field::as_*() function for the field's type.
db_uint id = person["id"].as_int(); db::String name = person["name"].as_string(); db_uint ring_id = person["ring_id"].as_int(); cout << "Id: " << (long) id << endl; cout << "Name: " << name.c_str() << endl; cout << "Ring tone id: " << (int) ring_id << endl;
In addition to accessing data, you can also update the current row. Call edit() to enter edit mode, assign new values to each field that should be changed, and call post() to update the row.
person.edit();
person["name"] = db::String("Sue");
person["ring_id"] = 3;
rc = person.post();![]() | Note |
|---|---|
As with inserts, modifications to the database will not be persistent until the current transaction is committed. See Transactions. |
To delete the current row, call remove().
rc = person.remove();
![]() | Note |
|---|---|
The row will not be permanently removed from the database until the current transaction is committed. See Transactions. |
A BLOB (Binary Large OBject) is a special type that can store data of arbitrarily large size. To do this, data in a BLOB field is accessed in small divisions called chunks. These chunks can be written to and read from using loops to access the entire BLOB or only a portion of the BLOB.
BLOB data is read and written directly from a db:Table object using the read_blob() and write_blob() functions. The table cursor must be positioned on an existing row and any pending updates to other columns must be posted with the post() function before the BLOB is accessed.
Here is an example of inserting a row with a BLOB field with data that is loaded from a file:
db::Table t;
... // Open t and position on an existing row. */
FILE *picture_file;
picture_file = fopen("read.png", "rb");
const db_len_t data_size = 1024;
char data[data_size];
int picture_field = t.find_field("picture");
int num_chunks = bytes_read = 0;
while((bytes_read = fread(data, 1, data_size, picture_file)) > 0)
{
t.write_blob (picture_field, data_size * num_chunks, data, bytes_read);
num_chunks++;
}
fclose(picture_file);Here is an example of reading a BLOB from the database back into a file:
db::Table t;
... // Open t and position on an existing row. */
FILE *picture_file;
picture_file = fopen("write.png", "wb");
const db_len_t data_size = 1024;
char data[data_size];
int offset, bytes_read;
int picture_field = t.find_field("picture");
db_len_t blob_size = t.get_blob_size(picture_field);
for(offset = 0; offset < blob_size; offset += data_size)
{
bytes_read = contact.read_blob(picture_field, offset, data, data_size);
fwrite(data, bytes_read, 1, picture_file);
}
fclose(picture_file);Structured Query Language, or SQL, is a standard database interface that uses query strings to access and modify data. For the complete description of the SQL dialect used by ITTIA DB SQL™, see Chapter 4, SQL.
To execute an SQL statement from the ITTIA DB SQL™ C++ API, create an instance of the db::Query class. Then use the db::Query object to perform the following steps:
Prepare an SQL statement.
Set query parameters (optional).
Execute the query.
Iterate through rows in the result set (optional).
Unexecute the query and repeat from step 2 (optional).
Steps 2 and 4 can be omitted depending on the nature of the SQL statement. For example, INSERT statements do not have a result set and thus do not use step 4. Schema definition statements, such as CREATE TABLE, can skip step 2 because they do not accept parameters.
![]() | Important |
|---|---|
Use parameters whenever possible. Parameters are faster and more secure than embedding data directly in an SQL statement, for example using |
To execute a simple query with no parameters, call db::Query::exec_direct(), which combines the prepare and execute steps.
db::Query q; int rc = q.exec_direct(db, "create table some_table (column_a integer, column_b integer)");
Alternately, these steps can be performed separately with the prepare() and execute() functions.
db::Query q; int rc; rc = q.prepare(db, "select column_a, column_b from some_table"); rc = q.execute();
If the query is a SELECT statement, a result set will be available after the query is executed. The result set is a read-only collection of rows with the same structure as a table. The result set is only sorted if the SQL statement contains an ORDER BY clause.
The db::Query class implements the db::Cursor interface, which is used to iterate over rows in a query result set or table. To iterate over the result set, use seek_first() to position on the first row, seek_next() to advance to the next row, and check is_eof() to determine when all rows have been processed. An SQL query can only be traversed forward and cannot be reset to the first row after seek_next() has been called.
Each row in the result set contains one or more fields that correspond to the columns and expressions in the SELECT list. Fields are identified by the order that they appear in the SELECT list, starting from zero.
To access the value of a field, first locate the db::Field class listed in Table 5.1, “C++ Column Types” that corresponds to the column's data type. After the query has been executed, construct an instance of that class using the db::Query object and the position of the field in the select list. For each row, cast the db::Field to an appropriate C++ data type to access the value. Example 5.1, “SQL Select Example in C++” illustrates this process.
Example 5.1. SQL Select Example in C++
db::Database db = ...;
db::Query q;
char *cmd;
cmd = "select id, name "
" from contact "
" order by name ";
if (DB_SUCCESS(q.exec_direct(db, cmd))) {
db::IntegerField id (q, 0);
db::WStringField name(q, 1);
for (q.seek_first(); !q.is_eof(); q.seek_next()) {
std::wcout << (long) id << '\t';
std::wcout << WString(name).c_str() << std::endl;
}
}If a query uses parameters, as described in Section 4.1.4, “SQL Query Parameters”, values can be assigned to each parameter after the query has been prepared but before it is executed. Use the param() function to assign a value to each parameter. The param() function returns a db::Field that can be assigned a value directly using the assignment operator, or set to NULL with the set_null() function. Each parameter is NULL until a value is assigned.
To execute the query again with different parameter values, first call unexecute() to return the query to a prepared state, then modify the parameters as before. Example 5.1, “SQL Select Example in C++” shows how to use parameters to execute a single query multiple times. Each parameter's previous value will carry over to the next execution unless it is explicitly assigned a new value or set to NULL.
Example 5.2. SQL Insert Example in C++
db::Database db = ...;
const wchar_t *name = L"My Name";
db_uint ring_id = 6;
db::Query q;
q.prepare(db,
"insert into contact (id, name, ring_id) "
" values (next value for contact_id, ?, ?) ");
q.param(0) = name;
q.param(1) = ring_id;
q.execute();
q.unexecute();
q.param(0) = L"Your Name";
q.param(1) = 3;
q.execute();
q.unexecute();Table of Contents
Before calling any other API functions, the library must be initialized. To initialize the library, call db_init_ex() with the current API version:
db_init_ex(DB_API_VER, NULL);
When the API is no longer needed, call db_done_ex() to free all resources used by the library:
db_done_ex(NULL);
Additional configuration for the ITTIA DB library can be set using the second argument to db_init_ex(). Options include lock manager configuration and access to the built-in memory allocator, which restricts the runtime memory footprint to preallocated segments.
To use ITTIA DB SQL™, include the header ittia/db.h.
#include <ittia/db.h>
A database is managed by a db_t handle. To create a database for the first time, use the db_create_file_storage() function.
db_t hdb = db_create_file_storage("file.db", NULL);
if (hdb == NULL)
fprintf(stderr, "Error creating database: %d\n", get_db_error());![]() | Caution |
|---|---|
If the file already exists, |
To open an existing database, use the db_open_file_storage() function. If the file does not exist, db_open_memory_storage() will return a NULL handle and the error DB_ENOENT will be set. If the file exists but cannot be opened for another reason, a different error code will be set.
db_t hdb = db_open_file_storage("file.db", NULL);
if (hdb == NULL)
fprintf(stderr, "Error opening database: %d\n", get_db_error());When finished with the database, call db_shutdown() to close the connection.
db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);
The following example creates a memory storage named memory.db using db_create_memory_storage().
db_memory_storage_config_t config;
db_memory_storage_config_init(&config);
config.memory_storage_size = 4 * 1024 * 1024;
hdb = db_create_memory_storage("memory.db", &config);
db_memory_storage_config_destroy(&config);When a memory storage is created, the application must specify the size of the memory storage. This is the number of bytes that will be allocated for tables and indexes. The size of the database cannot exceed this amount.
An application can create multiple storages in memory if each storage has a different name. Other threads and clients can connect to an existing memory storage using db_open_memory_storage().
hdb = db_open_memory_storage("memory.db", NULL);The memory_storage_size parameter is ignored when opening an existing memory storage. If another thread or client has already created the memory storage, the existing size will be used. Otherwise, db_open_memory_storage() will return a NULL handle and the error DB_ENOENT will be set.
When finished with the database, call db_shutdown() to close the connection. When the last connection is closed, the storage is destroyed.
db_shutdown(hdb, DB_SOFT_SHUTDOWN, NULL);
To connect to a database containing tables both in memory and on disk, set the memory_storage_size parameter before creating or opening a file storage. The following example creates a new file storage, and also opens an existing file storage, both with support for memory tables.
db_file_storage_config_t config;
db_file_storage_config_init(&config);
config.memory_storage_size = 4 * 1024 * 1024;
hdb = db_create_file_storage("file.db", &config);
hdb = db_open_file_storage("existing_file.db", &config);
db_file_storage_config_destroy(&config);The memory storage size must be specified when opening an existing file storage. If necessary, an empty memory storage will be allocated.
When all connections to a hybrid database are closed, memory tables will not be dropped, but all memory tables will be empty the next time the database is opened.
Many errors can occur during a call to a C API function. Whether or not an error has occurred is indicated by the return value of the function. For functions that return a handle type, such as db_t, db_cursor_t or db_row_t, a value of NULL is used to indicate an error. For functions that return a value, such as a number or flags, a special value is reserved for errors, often -1 (DB_WTIME_FAIL, DB_LEN_FAIL). Any function that would not otherwise return a value returns a db_result_t, which can be either DB_OK (1) or DB_FAIL (0).
When an error occurs, an error code is set that indicates the kind of error. The error code can be retrieved by calling get_db_error(). Negative values indicate an error. Positive values indicate a warning.
When ITTIA DB SQL™ is configured for multi-user access, get_db_error() is thread-safe. It will only report errors in API functions that have been called from the same thread.
![]() | Note |
|---|---|
After handling an error, call |
![]() | Tip |
|---|---|
Error codes are listed in the Error Handling section of the API Reference. The same error codes are used in both the C and C++ APIs. |
Several data structures in the C API can be instantiated statically at compile-time. These structures are db_oid_t, db_indexdef_t and db_tabledef_t. To initialize these data structures, use the DB_ALLOC_INITIALIZER() macro as the value for the db_alloc member. In the case of db_oid_t, use DB_OID_INITIALIZER() to initialize a new variable.
For a detailed discussion of database design issues, see Section 5.2, “Database Design”.
![]() | Note |
|---|---|
Schema updates cannot be performed inside a transaction. See Section 5.3.1, “Transactions” for more information. |
ITTIA DB SQL™ can store data in a wide range of types. Every column has a data type, which limits the kind of data that can be stored in that column. This ensures that data is always in the expected format when retrieved from the database and imposes a hard limit on the amount of storage needed for each row.
To select the type of a column, set the field_type of a db_fielddef_t structure to the schema identifier listed in Table 6.1, “C Column Types” when a table is created as described in Section 6.2.2, “Tables, Fields and Indexes”. To access data stored in a column, use the data type identifier to bind the column to a variable of the listed C data type. See Section 6.3.3, “Rows” for more information about binding a column in a row to a variable.
![]() | Tip |
|---|---|
Column types in the same category can be bound to the C data type of any other column type in the same category. For example, a column of type Data can be lost when converting to a smaller numeric format. Data is not lost when converting between Unicode string types, provided that the maximum string length is not exceeded. |
Category | Schema Identifier | C Data Type | C Data Type Identifier |
|---|---|---|---|
Integer | DB_COLTYPE_SINT8 | int8_t | DB_VARTYPE_SINT8 |
DB_COLTYPE_SINT16 | int16_t | DB_VARTYPE_SINT16 | |
DB_COLTYPE_SINT32 | int32_t | DB_VARTYPE_SINT32 | |
DB_COLTYPE_SINT64 | int64_t | DB_VARTYPE_SINT64 | |
DB_COLTYPE_UINT8 | uint8_t | DB_VARTYPE_UINT8 | |
DB_COLTYPE_UINT16 | uint16_t | DB_VARTYPE_UINT16 | |
DB_COLTYPE_UINT32 | uint32_t | DB_VARTYPE_UINT32 | |
DB_COLTYPE_UINT64 | uint64_t | DB_VARTYPE_UINT64 | |
Float | DB_COLTYPE_FLOAT32 | db_float32_t | DB_VARTYPE_FLOAT32 |
DB_COLTYPE_FLOAT64 | db_float64_t | DB_VARTYPE_FLOAT64 | |
Currency | DB_COLTYPE_CURRENCY | db_sint32_t | DB_VARTYPE_SINT32 |
ANSI String | DB_COLTYPE_ANSISTR | db_ansi_t, char* | DB_VARTYPE_ANSISTR |
Unicode String | DB_COLTYPE_UTF8STR | db_utf8_t, char* | DB_VARTYPE_UTF8STR |
DB_COLTYPE_UTF16STR | db_utf16_t | DB_VARTYPE_UTF16STR | |
DB_COLTYPE_UTF32STR | db_utf32_t | DB_VARTYPE_UTF32STR | |
Date and Time | DB_COLTYPE_DATE | db_date_t | DB_VARTYPE_DATE |
DB_COLTYPE_TIME | db_time_t | DB_VARTYPE_TIME | |
DB_COLTYPE_DATETIME | db_datetime_t | DB_VARTYPE_DATETIME | |
DB_COLTYPE_TIMESTAMP | db_timestamp_t | DB_VARTYPE_TIMESTAMP | |
BLOB | DB_COLTYPE_BLOB | db_blob_t | DB_VARTYPE_BLOB |
Table 6.1. C Column Types
To create a table, first describe the table's fields as arrays of db_fielddef_t. Describe indexes as arrays of db_indexfield_t and db_index_def_t. Then call db_create_table() and db_create_index() to create the table and its indexes.
#define MAX_CONTACT_NAME 50
#define MAX_FILE_NAME 50
static db_fielddef_t contact_fields[] =
{
#define CONTACT_ID 0
#define CONTACT_NAME 1
#define CONTACT_RING_ID 2
#define CONTACT_PICTURE_NAME 3
#define CONTACT_PICTURE 4
{ CONTACT_ID, "id", DB_COLTYPE_UINT64, 0, 0, DB_NOT_NULL, 0 },
{ CONTACT_NAME, "name", DB_COLTYPE_WCHARSTR, MAX_CONTACT_NAME, 0, DB_NOT_NULL, 0 },
{ CONTACT_RING_ID, "ring_id", DB_COLTYPE_UINT64, 0, 0, DB_NULLABLE, 0 },
{ CONTACT_PICTURE_NAME, "picture_name", DB_COLTYPE_ANSISTR, MAX_FILE_NAME, 0, DB_NULLABLE, 0 },
{ CONTACT_PICTURE, "picture", DB_COLTYPE_BLOB, 0, 0, DB_NULLABLE, 0 }
};Next define the table as db_tabledef_t using the field definitions created above.
#define CONTACT_TABLE "contact"
db_tabledef_t contact_table =
{
DB_ALLOC_INITIALIZER(),
DB_TABLETYPE_DEFAULT,
CONTACT_TABLE,
5, /* Number of fields in the table. */
contact_fields
};In most cases, use the table type DB_TABLETYPE_DEFAULT. To create a memory table in a file storage, use DB_TABLETYPE_MEMORY instead.
Finally, create the table by calling db_create_table().
db_result_t rc;
rc = db_create_table(hdb, contact_table->table_name, &contact_table, 0);
if (rc == DB_FAIL)
fprintf(stderr, "Unable to create table\n");ITTIA DB™ supports three types of indexes:
DB_MULTISET_INDEXThe same values can occur in the index multiple times. A multiset index will never prevent a row from being inserted.
DB_UNIQUE_INDEXThe index fields for each row must be unique. The same values cannot occur in more than one row. However, NULL can occur in more than one row because NULL is not a value.
DB_PRIMARY_INDEXThe primary key index is identical to a unique index, except that all fields must use the DB_NOT_NULL flag. Each table can have at most one primary key index.
First, describe which fields will be include in the index with an array of db_indexfield_t.
static db_indexfield_t contact_by_id_fields[] =
{
{ CONTACT_ID, 0 }
};Define the attributes of the index as db_indexdef_t.
#define CONTACT_BY_ID "by_id"
static db_indexdef_t contact_index =
{
DB_ALLOC_INITIALIZER(),
DB_INDEXTYPE_DEFAULT,
CONTACT_BY_ID,
DB_PRIMARY_INDEX,
1, /* Number of fields in the index. */
contact_by_id_fields
};Create the indexes by calling db_create_index() for each index.
rc = db_create_index(hdb, contact_table->table_name, contact_index->index_name, &contact_index);
if (rc == DB_FAIL)
fprintf(stderr, "Unable to create index\n");A foreign key defines a relationship between two tables. Foreign keys are used to enforce the existance of related rows by referencing columns in another table.
db_foreign_key_def_t phone_number_foreign_keys[] = {
"CONTACT_REF",
/* Reference the contact table. */
CONTACT_TABLE,
/* Match type only applies to compound keys (on more than one column.) */
DB_FK_MATCH_SIMPLE,
/* Protect referenced rows from update or delete. */
DB_FK_ACTION_RESTRICT, /* update_rule */
DB_FK_ACTION_RESTRICT, /* delete_rule */
/* Checks cannot be deferred until commit. */
DB_FK_NOT_DEFERRABLE,
/* Checks occur immediately by default. */
DB_FK_CHECK_IMMEDIATE,
/* Reference the ID field in the contact table. */
1,
{
{ PHONE_CONTACT_ID, CONTACT_ID },
}
};If the foreign key uses more than one field, at least one of which can be null, then select an appropriate match option. If not, both match options have equivalent behavior.
DB_FK_MATCH_SIMPLESome fields in the foreign key may be null, in which case no row is referenced. A row is only referenced when no foreign key fields are null.
DB_FK_MATCH_FULLAll fields in the foreign key must be null if any are null. A row is only referenced when no foreign key fields are null.
When a row is updated or deleted in the referenced table, the database will act according to the update_rule or delete_rule, respectively, in the foreign key.
DB_FK_ACTION_RESTRICTThe action cannot be performed on a referenced row.
DB_FK_ACTION_CASCADEWhen the action is performed on key fields in a referenced row, the same action is performed on referencing rows.
DB_FK_ACTION_SETNULLWhen the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to null.
DB_FK_ACTION_SETDEFAULT When the action is performed on key fields in a referenced row, all foreign key fields in referencing rows are set to their default values.
Foreign keys are always checked immediately whenever a row is inserted into the referencing table, deleted in the referenced table, or updated in either table. Always use DB_FK_NOT_DEFERRABLE and DB_FK_CHECK_IMMEDIATE. Other options are not currently supported.
Call db_create_foreign_key() to add each foreign key to the referencing table.
rc = db_create_foreign_key(hdb, "phone_number", &phone_number_foreign_keys);
if (rc == DB_FAIL)
fprintf(stderr, "Unable to create foreign key\n");Sequence generators are used to generate unique, increasing integer values. A sequence generator is created with an initial starting value and will then retrieve successive numbers in the sequence. Each generated number is always greater than all previous numbers produced by the sequence generator. This provides a convenient source for unique identifiers.
![]() | Note |
|---|---|
Sequences may contain gaps, and so should not be used in situations where numbers cannot be skipped, such as matching with sequence numbers pre-printed on paper forms. |
To create a sequence define a db_seqdef_t with a name and initial value then call the db_create_sequence() function.
#define CONTACT_ID_SEQUENCE "contact_id"
db_seqdef_t contact_id_sequence = {
CONTACT_ID_SEQUENCE,
{{ 1, 0}}
};
rc = db_create_sequence(hdb, contact_id_sequence->name, contact_id_sequence);
if (rc == DB_FAIL)
fprintf(stderr, "Unable to create sequence %s\n", contact_id_sequence->seq_name);An application accesses tables in a database through transactions, cursors, and rows.
A group of database operations performed sequentially on a single connection. Transactions are associated with the database handle.
An object that provides access to rows and fields in the database. A cursor often has a row set and maintains a position in the row set.
An object that maps fields in the database to variables, data structures, and/or buffers in the application.
All data access operations must occur inside a transaction. A transaction is a logical group of operations that must all succeed or fail together. Changes made to the database do not become permanent until the transaction is committed. A transaction can also be aborted, which will undo any changes made since the transaction was started.
To start a transaction, call the db_begin_tx() function.
db_begin_tx(hdb, DB_DEFAULT_ISOLATION | DB_LOCK_SHARED);
![]() | Note |
|---|---|
For Isolation mode and Lock mode parameters see Isolation Levels and Locking. |
To commit the changes made during the transaction, call db_commit_tx(). The completion parameter determines how quickly the changes made in this transaction will be flushed from the database cache to permanent storage.
db_commit_tx(hdb, DB_FORCED_COMPLETION);
![]() | Note |
|---|---|
When the commit function returns successfully, any modifications made within the transaction are guaranteed to be completely written to the storage media. |
To cancel the changes made during the transaction, call db_abort_tx().
db_abort_tx(hdb, DB_FORCED_COMPLETION);
A cursor is an object that provides access to rows and fields in the database. A cursor often has a row set and maintains a position in the row set.
There are two types of cursors: table cursors and SQL query cursors. A table cursor corresponds to a specific table and has a row set containing the table's rows and fields. An SQL query cursor may have a row set, depending on the type of query, and can accept a row of parameters when executed.
A cursor is created by calling a function that returns a cursor handle, of type db_cursor_t. The cursor is associated with the database connection that was used to create it. Regardless of how the cursor was created, it is closed by calling db_close_cursor(), or when the database connection is closed.
db_t db = /* ... */; db_cursor_t cursor = db_open_table_cursor(hdb, "table_name", NULL); /* ... */ db_close_cursor(cursor);
Multiple cursors can be used simultaneously with the same table or tables. An open cursor does not prevent access to any row, including the cursor's current row. However, the schema for any tables associated with the cursor cannot be changed while the cursor is open.
![]() | Tip |
|---|---|
Transactions can be started, committed, and aborted while a cursor is open. Between transactions, the cursor's current row may be deleted or updated by another connection to the database. During a serializable or repeatable read transaction, only cursors created with the same database connection can modify or remove the cursor's current row. |
Data is stored and retrieved through rows. A row is a set of temporary buffers for storing the contents of database fields. Each field in a row can be stored in one of three locations: a managed internal buffer, a local variable referenced by memory address, or a member of a data structure. A row variable is independent of any specific row in the database and can be re-used for many insert, fetch and update operations.
A row is created by calling either db_alloc_row() or db_alloc_cursor_row(). A row can only be used to access fields that it is aware of and will not set or retrieve values for any fields that it is not. A row variable can be created that is initially bound to no database fields:
db_row_t contact_row; contact_row = db_alloc_row(NULL, 2);
This row should then be dynamically bound to two fields by calling db_bind_field().
The data in a row can be read and written by calling db_get_field_*() and db_set_field_*() functions. For safe access, use db_get_field_data() and db_set_field_data() to copy values to or from each field in the row. The size of the field can be determined by calling db_get_field_size().
![]() | Tip |
|---|---|
To obtain a direct pointer to the buffer that is used by the row, call db_get_field_buffer(). When writing to this pointer, be careful not to overrun the size of the buffer. |
When a row is no longer needed, it must be freed:
db_free_row(contact_row);
The first method, a managed buffer, is most suitable when the exact data type of a field is not known at compile time. It allows the value to be extracted as a void pointer and cast to the appropriate type at run time. To create a row with managed buffers for all fields in a cursor, call db_alloc_cursor_row():
db_row_t contact_row; contact_row = db_alloc_cursor_row( contact_cursor );
When the data type of a field is known at compile time, it can be bound directly to a local variable by providing the memory address and size of the variable in bytes. When data is retrieved from the database, it will be stored in the bound variables. When data is stored to the database, it will be read from these same variables. To create a row that has fields bound to memory addresses, call db_alloc_row() with an array of binding definitions:
uint32_t id;
db_ucs2_t name[50];
const db_bind_t row_def[] =
{
{
CONTACT_ID, /* field_no */
DB_VARTYPE_UINT32, /* data_type */
DB_BIND_ADDRESS(&id), /* data_ptr */
sizeof(id), /* data_size */
DB_BIND_ADDRESS(NULL), /* data_ind */
DB_BIND_ABSOLUTE, /* data_flags */
},
{
CONTACT_NAME, /* field_no */
DB_VARTYPE_UTF16STR, /* data_type */
DB_BIND_ADDRESS(name), /* data_ptr */
sizeof(name), /* data_size */
DB_BIND_ADDRESS(NULL), /* data_ind */
DB_BIND_ABSOLUTE, /* data_flags */
}
};
contact_row = db_alloc_row(row_def, 2);Instead of using internal buffers, the row will use the variables or memory addresses that have been provided for these fields. These fields can still be accessed using db_get_field_*() and db_set_field_*() functions if necessary, but it is generally more convenient to use the local variables to which they are bound directly.
![]() | Tip |
|---|---|
The |
Fields can be bound dynamically using db_bind_field(). In the following example, db_bind_field() is used to bind the variable id to two different row objects.
db_row_t simple_row, contact_row;
uint32_t id = 0;
db_len_t id_ind = DB_FIELD_NULL;
static db_bind_t bind_def =
{
CONTACT_ID, /* field_no */
DB_VARTYPE_UINT32, /* data_type */
DB_BIND_ADDRESS(&id), /* data_ptr */
sizeof(id), /* data_size */
DB_BIND_ADDRESS(&id_ind), /* data_ind */
DB_BIND_ABSOLUTE, /* data_flags */
};
simple_row = db_alloc_row(NULL, 1);
db_bind_field(simple_row, &bind_def);
contact_row = db_alloc_cursor_row( contact_cursor );
db_bind_field(contact_row, &bind_def);![]() | Tip |
|---|---|
|
Binding to the same variable in two different rows is an efficient way to copy fields between tables. Data can be fetched from one table using the first binding and then stored directly to another table using the second binding without making any additional copies.
A row can also be bound to a data structure to provide convenient access using a C struct.
A relative binding is defined in much the same way as for address bindings, but instead of using an absolute address, an offset into the data structure is used. The DB_BIND_OFFSET() and DB_BIND_SIZE() macros can be used to determine the offset and size of a struct member.
#define MAX_PHONE_NUMBER 20
typedef struct phone
{
contactid_t contact_id;
db_ansi_t number[MAX_PHONE_NUMBER + 1];
uint32_t type;
int32_t speed_dial;
} phone_t;
static const db_bind_t phone_number_binds[] =
{
{ PHONE_CONTACT_ID, /* field_no */
DB_VARTYPE_UINT32, /* data_type */
DB_BIND_OFFSET(phone_t, contact_id), /* data_ptr */
DB_BIND_SIZE(phone_t, contact_id), /* data_size */
-1, /* data_ind */
DB_BIND_RELATIVE},
{ PHONE_NUMBER,
DB_VARTYPE_ANSISTR,
DB_BIND_OFFSET(phone_t, number),
DB_BIND_SIZE(phone_t, number),
-1,
DB_BIND_RELATIVE},
{ PHONE_TYPE,
DB_VARTYPE_UINT32,
DB_BIND_OFFSET(phone_t, type),
DB_BIND_SIZE(phone_t, type),
-1,
DB_BIND_RELATIVE},
{ PHONE_SPEED_DIAL,
DB_VARTYPE_SINT32,
DB_BIND_OFFSET(phone_t, speed_dial),
DB_BIND_SIZE(phone_t, speed_dial),
-1,
DB_BIND_RELATIVE},
};
phone_t phone;
db_row_t phone_row;
phone_row = db_alloc_row(phone_number_binds, 4);
db_fetch(phone_cursor, phone_row, &phone);The functions db_fetch(), db_insert(), and db_update() each accept a uobject parameter that is only required for relative bound fields. This parameter is the address of a data structure instance that corresponds to the relative bindings.
![]() | Note |
|---|---|
If relative binding is not used in the row, the parameter of type db_object_t should always be |
![]() | Tip |
|---|---|
Relative bindings can also be used with an untyped memory buffer instead of a C data structure by setting data_ptr to the byte offset of each field. |
To traverse a cursor with a row set, use db_seek_first(), db_seek_next(), and db_eof() to position on each row of data.
![]() | Tip |
|---|---|
To check whether a cursor has a row set, call |
To read the current row's fields, use the db_fetch() function and a row object. Data is copied from the database into the row object. Type conversion and locking, if applicable, occur during the fetch operation only.
db_cursor_t cursor = /* ... */;
db_row_t row = /* ... */;
for (db_seek_first(cursor); !db_eof(cursor); db_seek_next(cursor))
{
db_fetch(cursor, row, NULL);
/* ... */
}A table cursor is used to position on a specific row in a table and to search using an index. An index is selected when opening the cursor to determine which fields to sort by when traversing the table and which fields are available for searching. If the index is NULL, the table is not sorted and rows are ordered for most efficient access.
db_cursor_t contact_cursor; db_table_cursor_t p; db_table_cursor_init(&p); p.index = CONTACT_BY_ID; p.flags = DB_CAN_MODIFY | DB_LOCK_EXCLUSIVE; contact_cursor = db_open_table_cursor(hdb, CONTACT_TABLE, &p); db_table_cursor_destroy(&p);
Alternately, the cursor options can be created with a static definition:
db_cursor_t contact_cursor;
static const db_table_cursor_t contact_cursor_def =
{
CONTACT_BY_NAME, /* index */
DB_SCAN_FORWARD | DB_LOCK_SHARED, /* flags */
0 /* sort_buffer_size */
};
contact_cursor = db_open_table_cursor(hdb, CONTACT_TABLE, &contact_cursor_def );Most cursors should use at least on of the following flags:
DB_CAN_MODIFY
DB_SCAN_FORWARD
DB_SCAN_BACKWARD
If the table cursor definition is NULL, the cursor is created with all of the above capabilities and no index.
A cursor should be closed when it is no longer needed to free resources:
db_close_cursor(contact_cursor);
To insert data using a table cursor, assign values to a row, then call db_insert():
uint32_t id = 4;
db_ucs2_t name[50] = "Bob";
const db_bind_t row_def[] =
{
{
CONTACT_ID, /* field_no */
DB_VARTYPE_UINT32, /* data_type */
DB_BIND_ADDRESS(&id), /* data_ptr */
sizeof(id), /* data_size */
DB_BIND_ADDRESS(NULL), /* data_ind */
DB_BIND_ABSOLUTE, /* data_flags */
},
{
CONTACT_NAME, /* field_no */
DB_VARTYPE_UTF16STR, /* data_type */
DB_BIND_ADDRESS(name), /* data_ptr */
sizeof(name), /* data_size */
DB_BIND_ADDRESS(NULL), /* data_ind */
DB_BIND_ABSOLUTE, /* data_flags */
}
};
contact_row = db_alloc_row(row_def, 2);
db_insert(contact_cursor, contact_row, NULL);
db_free_row(r);![]() | Note |
|---|---|
Data inserted into the database will not be persistent until the current transaction is committed. See Transactions. |
After the row is inserted, the cursor's position is undefined. To position the cursor on the new row after it is inserted, use the flag DB_INSERT_SEEK_NEW:
db_insert(contact_cursor, contact_row, DB_INSERT_SEEK_NEW);
Several methods can be used to search for a row with a table cursor:
Apply one or more filters.
Scan for a matching row.
Seek directly to a row using the cursor's index.
Apply a range match to the cursor's index.
Filters are applied to a table cursor by calling db_add_filter() one or more times, followed by db_seek_first() to locate the first matching row. Each row in the table is compared with the row provided to db_add_filter() according to the specified criteria.
db_cursor_t cursor = /* ... */;
db_row_t search_row = /* ... */;
db_add_filter(cursor, DB_SEEK_EQUAL, search_row, NULL, NULL, 0);
if (db_seek_first(cursor) == DB_SUCCESS) {
/* The cursor is positioned on a row in the database that
* is equal to search_row for all fields in search_row. */
}If the table is large, the db_seek_first() function and subsequent calls to db_seek_next() may take a long time to complete. If the table cursor was created with an index, the indexed fields will be used to optimize the filter. Index fields are used from left to right in the order that they appear in the index.
To manage the performance cost of searching a table, use the functions db_seek_next(), db_seek(), and db_set_range() in conjunction with db_fetch() to locate the desired row or rows. These four functions operate on one row at a time and will always complete in a short, predictable length of time.
To sort a table cursor using any of the table's fields, use the db_sort() function.
static const db_indexfield_t sort_by_name[] = {
{ CONTACT_NAME, 0 }
};
db_sort(contact_cursor, sort_by_name, DB_ARRAY_DIM(sort_by_name));The db_sort function may fetch all rows in the table cursor's current row set to perform the sort. If the cursor's index already sorts the table by the requested fields, db_sort will return immediately.
![]() | Tip |
|---|---|
If applying filters and sorting a table cursor, call |
When the table cursor is created, the sort_buffer_size attribute determines how much memory can be allocated for sorting. If the buffer is too small, a partial sort will be performed and an error is returned.
In addition to accessing data, you can also update the current row. Update the values bound to the row and call db_update().
db_update(contact_cursor, contact_row, NULL);
![]() | Note |
|---|---|
As with inserts, modifications to the database will not be persistent until the current transaction is committed. See Transactions. |
To delete the current row from a table cursor, call db_delete().
db_delete(contact_cursor);
![]() | Note |
|---|---|
The row will not be permanently removed from the database until the current transaction is committed. See Transactions. |
A BLOB (Binary Large OBject) is a special type that can store data of arbitrarily large size. To do this, data in a BLOB field is accessed in small divisions called chunks. These chunks can be written to and read from using loops to access the entire BLOB or only a portion of the BLOB.
In the C API, a BLOB field is managed by binding to a variable of type db_blob_t. This structure is used to control what portion of the BLOB will be used in an update or fetch. When a BLOB field is fetched, blob_size is set to the total size of the BLOB field. If chunk_data is not NULL, it is filled with chunk_size bytes starting from position offset in the field. In case there is less data in the BLOB than offset + chunk_size, the actual number of bytes read is stored in actual_size.
Example 6.1, “Prepare to access BLOB field” shows how to bind a column in a table to a BLOB field. The resulting row is passed to db_update() for each chunk of data to be written into the database, as demonstrated in Example 6.2, “Write data from a file to a BLOB field”. Or the row can be passed to db_fetch() for each chunk of data to be read from the BLOB column, as in Example 6.3, “Read data from a BLOB field to a file”.
Example 6.1. Prepare to access BLOB field
#include <ittia/db.h>
#include "db_helper.h"
db_cursor_t t;
... /* Open t and position on an existing row. */
db_blob_t blob;
/* Bind "blob" to field number 3. */
const db_bind_t row_def[] =
{
DB_BIND_VAR(3, DB_VARTYPE_BLOB, blob),
};
/* Allocate a row containing the BLOB field binding. */
db_row_t r = db_alloc_row(row_def, DIM(row_def));
/* Clear BLOB settings (optional). */
memset(&blob, 0, sizeof(blob));Example 6.2. Write data from a file to a BLOB field
FILE *picture_file = fopen("read.png", "rb");
/* Allocate buffer */
#define DATA_SIZE 1024
char buffer[DATA_SIZE];
/* Prepare BLOB variables */
int bytes_read = 0;
picture.chunk_data = (void*)buffer;
/* Store picture into BLOB field */
while((bytes_read = fread(buffer, 1, DATA_SIZE, picture_file)) > 0)
{
blob.offset = DATA_SIZE * num_chunks;
blob.chunk_size = bytes_read;
db_update(t, r, NULL);
}
fclose(picture_file);Example 6.3. Read data from a BLOB field to a file
FILE *picture_file = fopen("write.png", "wb")
/* Allocate buffer */
#define DATA_SIZE 1024
char buffer[DATA_SIZE];
/* fetch file_name and blob.blob_size */
db_fetch(contact_cursor, contact_row, NULL);
/* Prepare BLOB variables */
blob.chunk_data = buffer;
blob.chunk_size = DATA_SIZE;
/* Export file from BLOB to disk */
for(blob.offset = 0; blob.offset < blob.blob_size; blob.offset += DATA_SIZE)
{
db_fetch(t, r, NULL);
fwrite(blob.chunk_data, blob.actual_size, 1, picture_file);
}
fclose(picture_file);Structured Query Language, or SQL, is a standard database interface that uses query strings to access and modify data. For the complete description of the SQL dialect used by ITTIA DB SQL™, see Chapter 4, SQL.
To execute an SQL statement from the ITTIA DB SQL™ C API, create a query cursor by calling db_prepare_sql_cursor(). The cursor returned by this function can be used to execute the query and read the result set. An application should follow these steps to process an SQL statement:
Prepare an SQL statement.
Set query parameters (optional).
Execute the query.
Iterate through rows in the result set (optional).
Unexecute the query and repeat from step 2 (optional).
Steps 2 and 4 can be omitted depending on the nature of the SQL statement. For example, INSERT statements do not have a result set and thus do not use step 4. Schema definition statements, such as CREATE TABLE, can skip step 2 because they do not accept parameters.
![]() | Important |
|---|---|
Use parameters whenever possible. Parameters are faster and more secure than embedding data directly in an SQL statement, for example using |
The function db_prepare_sql_cursor() parses an SQL statement and returns an SQL cursor. The last parameter is reserved for future use. Always use the value 0 for this parameter.
db_cursor_t sql_cursor; sql_cursor = db_prepare_sql_cursor(hdb, "select * from some_table", 0);
After the cursor is prepared, the statement can be executed by a call to db_execute(). Executing a statement may modify the database or open a result set that can then be browsed through the cursor.
db_execute(sql_cursor, NULL, NULL);
If the query is a SELECT statement, a result set will be available after the query is executed. The result set is a read-only collection of rows with the same structure as a table. The result set is only sorted if the SQL statement contains an ORDER BY clause.
To iterate over the result set, use db_seek_first() to position on the first row, db_seek_next() to advance to the next row, and check db_is_eof() to determine when all rows have been processed. An SQL query can only be traversed forward and cannot be reset to the first row after db_seek_next() has been called.
Each row in the result set contains one or more fields that correspond to the columns and expressions in the SELECT list. Fields are identified by the order that they appear in the SELECT list, starting from zero.
To access field values in the result set, use db_fetch() to copy data into a C API row, as described in Section 6.3.3, “Rows”.
Because result set is read-only, db_insert(), db_update(), and db_delete() cannot be used on an SQL cursor.
Example 6.4, “Executing a SELECT statement” shows how to execute a SELECT statement and iterate over the results using a managed row.
Example 6.4. Executing a SELECT statement
db_cursor_t sql_cursor;
int nfield;
db_fielddef_t field_def;
db_row_t row;
int field_count;
sql_cursor = db_prepare_sql_cursor(hdb, "select * from some_table", 0);
row = db_alloc_cursor_row(sql_cursor);
field_count = db_get_field_count(sql_cursor);
db_execute(sql_cursor);
db_seek_first(sql_cursor);
while ( !db_eof(sql_cursor) ) {
//------------------------------
// RECORD LEVEL PROCESSING
//------------------------------
db_fetch( sql_cursor, row, NULL );
for ( nfield = 0; nfield < field_count; nfield++ ) {
//--------------------------
// FIELD LEVEL PROCESSING
//--------------------------
db_get_field ( sql_cursor, nfield, &field_def );
if ( db_is_null(row, nfield) ) {
//----------------------
// NULL DATA CHECK
//----------------------
continue;
}
db_get_field_data(row, nfield, field_def.field_type, &var, sizeof(var));
.
.
.
}
db_seek_next(sql_cursor);
}
db_free_row(row);
db_close_cursor(sql_cursor);Some SQL statements accept one or more parameters. For these queries, a parameter row must be passed as the second parameter to db_execute(). If any fields in the row use relative bindings, an object address must also be passed as the third parameter, otherwise the third parameter can be NULL. The fieldno for each bound field must be set to the number of the parameter. See Section 4.1.4, “SQL Query Parameters” for more information.
An SQL query can be executed multiple times by repeatedly calling db_execute(). The values of the parameter row can be modified each time before the query is executed. If the query has a result set, the result set must be closed by calling db_unexecute() before the cursor can be executed again.
![]() | Tip |
|---|---|
A prepared SQL cursor can be left open and reused later to avoid the overhead of opening the cursor. If the cursor is not closed immediately after use, it is a good practice to call |
Table of Contents
Highly available systems are designed to operate continuously by leveraging redundant components. For such systems, ITTIA DB™ can create and maintain redundant copies, so that data is always available through media failure, intermittent network connections, and similar unpredictable errors. Many different hardware configurations can be used to create redundancy, so the database must be flexible enough to handle a wide range of scenarios.
ITTIA DB™ supports replication and online backup, which can each be used to achieve redundancy. Online backup creates a complete copy of a database file, and replication automatically propagates changes from one database to other database files. In most cases, data is copied in the background with little to no impact on system performance. Data can either be copied to another storage medium, such as a removable flash card, or, in the case of replication, transmitted to other devices over a network.
High availability features work together with transaction logging to protect data from corruption and keep the database consistent. Only complete, committed transactions are saved when copies are made.
Online backup is used to create a backup database file and an associated log file. Both files are required to restore the backup.
To perform an online backup, call db_backup with a destination file name. If the destination file exists, it will be overwritten.
db_backup returns only when the backup is complete. Other connections to the same database are not interrupted, and can read and write changes safely during the backup. If possible db_backup should be used in a background thread.
![]() | Tip |
|---|---|
When connected to an ITTIA DB server, the backup file location is relative to the server process. |
db_t hdb = db_open_file_storage("example.db", NULL);
db_backup(hdb, "backup.db", 0, 0);
db_shutdown(hdb, 0, NULL);To restore the backup database file, open it normally.
db_t hdb2 = db_open_file_storage("backup.db", NULL);
db_shutdown(hdb2, 0, NULL);When the backup database is opened, the log file is applied and truncated automatically, ensuring that the database is in a consistent state.
Only disk tables are copied. A memory table must be copied to a disk table prior to the backup to include its contents in the backup file. Memory storage does not support this backup method.
Replication is used to distribute data between ITTIA DB database files. Changes to one database can be shared with other databases both automatically and on demand. Other databases can be opened directly from the same process, perhaps on an alternate storage media, or remotely through ITTIA DB's built-in client/server technology. Replication settings stored in each database file control both the direction of replication for each table and the list of other database with which to replicate.
To enable replication, assign each database a replication address. Any database that participates in replication is known as a replication peer. The replication address is a 32-bit integer value that must be unique across all peers in the same replication network.
![]() | Note |
|---|---|
Replication peers are not servers or devices, but individual database files. A replication network can consist of two database files that are only accessed by one process on a single device. A replication network can also consist of database files on many different devices that share changes over a TCP/IP network. |
Replication is accomplished through peer-to-peer connections: any database can connect to one or more replication peers to both send and receive database changes. Each database contains a list of peers to which it can connect, but there is no restriction on which peers a database can receive a connection from.
Two types of replication can be configured: synchronous replication and ad hoc exchange. The type of replication determines when replication occurs, and is set independently for each peer. Synchronous replication occurs whenever a transaction is committed, while ad hoc exchange occurs only when the application requests an exchange with the peer.
A third type of replication, table snapshots, is not configured and can be used with any type of peer. A table snapshot is used to initialize a table in one database to the same state as a similar table in another database. The initial state for each table can come from either database in a peer-to-peer connection.
All configuration for replication is stored in the database files themselves. Replication settings can be configured with the ITTIA DB C API and utilities. When a database file is backed up, replication settings will be preserved in the backup copy. However, if a backup copy will be used as a replication peer along with the original, it must be assigned a new replication address.
The replication mode, set independently for each table, determines in which direction changes can be sent. Supported modes are: IN, OUT, and INOUT.
To configure one-way master/slave replication for a table, use OUT mode in the master database and IN mode in the slave database, regardless of which database will establish the connection. Changes will only be replicated if allowed by the replication modes used in both databases. Conflicts may occur under certain configurations, and they will be detected and resolved according to the rules configured in each table.
When replication is enabled, changes to disk tables — row insert, update, or delete — are stored in the log file as replication events. However, replication events are only stored for tables that have OUT or INOUT replication enabled.
Replication does not require that all databases have exactly the same table structure. During an exchange, only the subset of columns that is common to both the source and recipient is used. Data types are automatically converted when necessary. If type conversion is not possible, the source transaction is not applied.
Special columns can be added to replicated tables to help track the origin of each row and identify conflicts. These columns do not necessarily exist in the source database. Instead, values are set using information such as the peer address of the source database.
The peer address column can be used to form a unique key when a table consolidates rows from multiple sources. If the source databases use overlapping primary keys, the peer address column should be added to the primary key in the recipient table.
When the peer address column is used, each row is exchanged only with the peer identified by the column. In this way, a table can be partitioned between peers.
Whenever a transaction that changes a database is committed, all synchronous replication peers configured in that database are updated immediately. The commit operation will not return until all synchronous peers acknowledge receipt of the changes or an error occurs.
A distributed transaction is used protect the integrity of the database and its peers. If an error occurs when updating any peer, the commit operation will fail with an error and all peers will be rolled back. An error will occur if the database is unable to connect to a peer, the connection is interrupted, or a constraint in a peer is violated as a result of the changes.
If a power failure or crash occurs during the commit operation, the application may need to resolve the transaction state of some or all of the databases involved in the distributed transaction.
![]() | Warning |
|---|---|
Parts of the database that were accessed during the transaction will remain locked until the transaction state is resolved. An application that uses synchronous replication should check for pending transactions whenever it opens the first connection to an existing database. |
Peers configured in ad hoc mode use asynchronous replication.
For disk tables, transactions are accumulated in the replication log. The log file is periodically rotated to a new file, and old log files are automatically deleted when no longer needed. Log files are retained until all peers have received all replication events contained therein.
The log-based replication approach used by ITTIA DB™ has several advantages over alternative methods:
Transaction boundaries are preserved in the replication log. If a replication event cannot be applied to a recipient database because it violates a constraint, no other events are applied from the same transaction. This prevents inconsistency in the recipient database.
The order of changes is preserved. Replication events are applied to the recipient database in the same order that they occurred in the source database.
Performance is consistent, regardless of the number of peer databases. The same replication log is shared between all peers.
For memory tables, transactions are accumulated in the memory journal until they have been exchanged with all replication peers.
Replication can be applied to a variety of system architectures. For example, replication can be used to share data between a fleet of devices and a main database on a back-end system. Several different types of replication can be used in this scenario, each applied to a different set of tables:
Tables in the main database are replicated to all other peers. Only the main database can modify these tables. Broadcast replication can be used to share a global dictionary between all devices.
Tables in the main database are partitioned so that each row belongs to exactly one device. Each row can be modified either in the main database or on one device, limiting the possibility of conflicts. Unicast replication can be used to share device-specific configuration values.
Entire tables are replicated directly between devices. Priority-based conflict resolution can be useful when more than two devices share the same table. Bidirectional replication can be used to share the active state between devices that need to cooperate on the same problem.
Maintain multiple identical database files, either on separate devices or on different storage media on a single device. No data will be lost if one storage media or device fails. Also, a copy on a remote device can be used to improve access time for read operations on that device.
ITTIA DB™ uses peer-to-peer replication, which can be configured to support these and other replication scenarios.
ITTIA DB™ can detect and resolve two types of conflicts: operation conflicts and data conflicts. Both types of conflicts are identified by searching for key values in the recipient database.
The source and recipient databases for a replication event may not always contain the same rows. If the source row cannot be found in the recipient when it is expected, or a row already exists in the recipient that had not previously existed in the source, a key conflict occurs.
Key conflicts are resolved according to Table 8.1, “Default Key Conflict Resolution Policy”. The action taken depends on the database operation that is being replicated and whether or not a matching row exists in the recipient. To override this behavior for a specific operation, use the DB_REP_RESOLVE_EXCL flag when configuring replication.
| Operation in source | Row exists in recipient | Row is missing in recipient |
|---|---|---|
| INSERT | UPDATE the row | INSERT the new row |
| UPDATE | UPDATE the row | INSERT the new row |
| DELETE | DELETE the row | Skip the operation |
Table 8.1. Default Key Conflict Resolution Policy
A data conflict occurs when the same row has been in both the source and recipient databases. Data conflicts can be detected in one of two ways:
Compare the current row in the recipient database with the original row from the source database. When any column that they have in common is different, a conflict is recognized. This method ensures that operation is only performed on a row with the same initial state.
Compare the stamp column in the source and recipient databases. The stamp column combines the address of the peer that last modified the row with a random value that changes each time the row is modified. A conflict is detected whenever the row is updated independently in separate databases. Stamp comparison is more strict than row comparison because the row data is ignored.
Row comparison is used by default. To enable stamp comparison, configure a stamp column as described in Section 8.2.8, “Table Configuration”.
When a conflict is detected, a conflict resolution rule is applied. A different conflict resolution rule can be configured for each operation on each table. Three policies are available:
Always accept changes. The current row in the recipient is overwritten by the source row.
Always refuse conflicting changes. The current row in the recipient remains intact.
Compare the source and recipient priorities. Conflicting changes are only accepted if the source priority is strictly greater than the recipient priority. Otherwise, conflicting changes are refused.
Each database that will participate in replication must be assigned a unique replication address. This number is used by replication peers to identify the database.
db_t hdb = db_open_file_storage("example.db", NULL);
/* Set a unique replication address for each database file. */
db_rep_config_t rep_config = { 0 };
rep_config.rep_address = 1;
db_rep_set_config( hdb, &rep_config );After the replication address is set for a database, log file rotation is enabled. Replication exchanges must be performed periodically to purge old log files.
Changes are replicated by opening peer-to-peer connections to other databases. Each database has a list of peers that it can connect to stored in the database file.
Peers only need to be added to the database that will initiate the peer-to-peer connection, regardless of which direction changes will be sent.
/* Setup ad hoc replication with a peer database. */ db_rep_peerdef_t peer; db_rep_peerdef_init(&peer); /* Connect to the peer database using shared memory. */ peer.rep_type = DB_REPTYPE_ADHOC; peer.peer_address = 2; strcpy(peer.peer_uri, "idb+shm:///some_peer.db"); /* Create the replication peer. */ db_rep_create_peer(hdb, "some_peer", &peer); db_rep_peerdef_destroy(&peer);
The replication peer definition is stored in the database file and can be accessed by name. The peer name must be unique within this database only.
The replication address, peer_address, assigned to the peer should be unique across all databases that might participate in the same replication network.
The peer_uri is the location of the peer database in URI format, either a local database file name or a shared database URL. For information on connecting to a remote database, see Section 7.2, “Client-server Shared Access”.
Replication must be enabled for each table that will send or receive changes. The db_rep_table_query function gets the current replication settings for a table, which is disabled by default, and the db_rep_table_set function updates the settings.
Every database that participates in replication must be configured in this way. However, the replication settings will usually vary from one database to another. When changes are exchanged between two databases, only tables and fields with identical names will be shared. Other tables and fields are ignored.
/* Initialize rep_info to default settings. */
db_rep_table_info_t rep_info;
db_rep_table_query(hdb, "example_table", &rep_info);
/* Enable bidirectional replication for this table. */
rep_info.rep_mode = DB_REP_MODE_INOUT;
/* Do not accept conflicting inserts from other peers. */
rep_info.insert_resolve = DB_REP_RESOLVE_REFUSE;
/* Accept all conflicting updates, but only if the row
* already exists in this database. */
rep_info.update_resolve = DB_REP_RESOLVE_ACCEPT |
DB_REP_RESOLVE_EXCL;
/* Accept conflicting deletes only from peers with greater priority. */
rep_info.delete_resolve = DB_REP_RESOLVE_PRIORITY_MIN + 5;
/* Configure special replication columns. */
strcpy(rep_info.peer_column, "peer");
strcpy(rep_info.state_column, "state");
strcpy(rep_info.rid_column, "rid");
strcpy(rep_info.stamp_column, "stamp");
/* Set replication index. */
strcpy(rep_info.rep_index, "some_unique_index");
/* Apply replication settings to a table. */
db_rep_table_set(hdb, "example_table", &rep_info);The peer_column, state_column, and rid_column fields must either be blank or identify existing columns of type DB_VARTYPE_SINT32 (SQL keyword INTEGER). The stamp_column field must identify a column of type DB_VARTYPE_SINT64 (SQL keyword BIGINT). When a row is received from a peer database, these columns are populated with information about the source of the data.
The insert_resolve, update_resolve, and delete_resolve fields control what kind of changes will be accepted from peer databases. Each member contains both a priority setting and a set of flags.
The priority bits are accessed with DB_REP_RESOLVE_PRIORITY_MASK and can be set to one of:
DB_REP_RESOLVE_ACCEPTAlways accept conflicting changes.
DB_REP_RESOLVE_REFUSEAlways refuse conflicting changes.
A number between DB_REP_RESOLVE_PRIORITY_MIN and DB_REP_RESOLVE_PRIORITY_MAX, inclusive.
The following flags can also be set for each of the resolve members:
DB_REP_RESOLVE_EXCLPerform this operation exclusively. Otherwise, insert will update when the row already exists and update will insert when the row doesn't exist.
DB_REP_RESOLVE_DISABLEDisable operation.
The peer column, identified by peer_column, contains the replication address of a peer database. When a row is accepted from a peer database, that peer's replication address is assigned to the peer column.
When rows are sent to a peer, the peer only receives rows that are assigned to it using the peer column, or have the value DB_REP_ADDRESS_ALL. If a peer column is not configured, is DB_REP_ADDRESS_NONE or is null for a particular row, then all peers will receive the changes.
The peer column can be modified by the application. When a row's peer address is modified, the row is deleted from the table in the original peer database, if the peer address was not null, and inserted into the table in the new peer database, unless the peer address is set to null.
The state column, identified by state_column, stores the current replication state of each row. This column is provided for informational purposes only. Modifying this column has no effect on replication behavior.
The state column is automatically set to one of the following values whenever a row is modified, received, or sent:
DB_REP_STATE_LOCALLast change was local, but will not be delivered to replication peers.
DB_REP_STATE_DELIVERYLast change was local and will be delivered to replication peers.
DB_REP_STATE_REMOTELast change was remote.
DB_REP_STATE_DELIVEREDChanges were delivered to a peer.
DB_REP_STATE_REFUSEDChanges were refused by a peer due to conflicts.
DB_REP_STATE_IGNOREDChanges were ignored by a peer.
When a row is inserted or updated, the state column is automatically set to DB_REP_STATE_LOCAL or DB_REP_STATE_DELIVERY. When a row is deleted, the state column is not used.
After the change is sent to a replication peer, the state is updated to reflect how the recipient handles the change, as follows:
If the change is accepted by the recipient, the state is set to DB_REP_STATE_DELIVERED.
If the change is refused by the recipient due to a conflict, the state is set to DB_REP_STATE_REFUSED.
If the operation is ignored because the recipient uses DB_REP_RESOLVE_DISABLE, the state is set to DB_REP_STATE_IGNORED.
The recipient sets its state column to DB_REP_STATE_REMOTE whenever a change is accepted. If the recipient refuses or ignores a change, its state column does not change.
The state column can be modified by the application, but the value is ignored and may be overwritten when replication occurs.
The row ID column, identified by rid_column, specifies the row in the peer database from which the data was obtained. This column is provided for informational purposes only. Modifying this column has no effect on replication behavior.
Together, the peer address and row ID uniquely identify a row across all replication peers. However, row ID values may be reused when rows are deleted in the peer.
Do not modify the row ID column directly.
The stamp column, identified by stamp_column, provides an alternative method of detecting conflicts. The stamp column is updated whenever a row is modified, so that changes can be detected without comparing the entire row.
Do not modify the stamp column directly.
The replication index, identified by rep_index, is used to identify corresponding rows in peer databases.
The rep_index must either be blank or identify an existing index. If blank, the table's primary key is used to identify rows.
After replication is configured, changes can be exchanged with an ad hoc peer database by calling db_rep_exchange. Changes are both sent to and received from the peer database.
db_rep_exchange(hdb, "some_peer", 0);
After exchanging rows with a peer, state columns can be checked to determine whether further conflict resolution is necessary before exchanging with other peers.
When row-level locking is used, concurrent connections to both databases are not interrupted. Transactions are isolated in the usual way. However, changes made to either database by other connections during the exchange may not be replicated until a subsequent exchange.
To send the contents of a table to a table of the same name in a replication peer, use db_rep_snapshot with the DB_REP_MODE_OUT flag.
db_rep_snapshot(hdb, "some_table", "some_peer", DB_REP_MODE_OUT);
To receive the contents of a table from a replication peer, use db_rep_snapshot with the DB_REP_MODE_IN flag.
db_rep_snapshot(hdb, "some_table", "some_peer", DB_REP_MODE_IN);
Table snapshots can be created even if the table is not configured for replication. However, special columns will be used if configured.
The type of replication assigned to the peer is ignored. Snapshot replication can even be used with a disabled peer.
If the destination table is not empty, only rows that differ between the two tables are actually copied.
Mirroring is a method of maintaining one or more identical copies of a database file at different locations. Mirroring can be achieved with ITTIA DB through replication:
Create one or more backup copies of the main database.
Assign each database file a unique replication address and set the address through the C API or a with a utility.
In the main database file, enable DB_REP_MODE_OUT replication for all tables. Do not configure any special columns.
In each mirror database file, enable DB_REP_MODE_IN replication for all tables. Do not configure any special columns.
Add peers to the main database for each mirror database.
When connected to the main database, periodically call db_rep_exchange for each peer.
In an alternative mirroring configuration, each mirror database can add the main database as a peer. In this case, db_rep_exchange should be called while connected to each mirror.
Table of Contents
| Feature | Implementation |
|---|---|
| Concurrency control | Pessimistic locking |
| Locking method | Strict 2-phase locking |
| Locking mode | Multiple granularity locks |
| Locking granularity | Record |
| Isolation levels | SERIALIZABLE, REPEATABLE READ, READ COMMITTED |
| SERIALIZABLE isolation level | ARIES/IM next key locking |
| Lock owner | Database connection |
| Deadlock detection | Waits-for-graph depth search |
| Deadlock resolution | The thread detected deadlock state refuses the operation. |
| Logging method | UNDO/REDO write-ahead logging (WAL) |
| Indexing methods | B+ tree |
| Buffer scheduling | Clock algorithm |
Table 9.1. ITTIA DB SQL™ Database Characteristics Quick Reference
In this section, we explain in further detail some of the technologies employed by the ITTIA DB SQL™ kernel. Understanding these topics will help developers to optimize and diagnose database-driven software. The reader is assumed to have some prior knowledge of general database concepts such as B+Tree indexes and locking.
ITTIA DB SQL™ fully obeys Atomicity, Consistency, Isolation, and Durability (ACID) properties. These properties are defined as follows:
| Atomicity | The changes to a database performed within a transaction are either all applied when committed or none are applied when aborted.[a] |
| Consistency | Consistency refers to the legality of the database state at the time the transaction starts and commits. There are consistency constraints which the database must obey, if not the transaction cannot be performed. |
| Isolation | Isolation refers to the ability to perform an operation in a transaction independent of concurrently run transactions. Each transaction should behave as if it is the only one modifying the database. Each time a conflict between transactions is about to occur, this conflict is detected and one transaction is delayed.[b] |
| Durability | Durability refers to the guarantee that as soon as a commit operation reports success the changes, made by that transaction, will persist and will not be undone.[c] |
[a] Sequence number generators are an exception to atomicity. A sequence generator will never return the same value twice, regardless of whether the transaction commits or aborts. [b] This property can be relaxed using multiple isolation levels to increase concurrent performance. [c] This property can be relaxed using lazy transaction commits, which have no durability guarantee. | |
Table 9.2. ACID Properties
Atomicity and Durability properties are maintained through logging. Consistency and Isolation properties are maintained through locking.
Logging is a way of tracking changes in database content in order to be able to rollback these changes on system crash or transaction abort. ITTIA DB SQL™ uses UNDO/REDO WAL logging method, which is also known as ARIES. The basis of operation of ARIES is that the physical changes (i.e. changes made at the database page level) are logged during forward operation, containing all the necessary information to repeat the same action again (redo) or rollback those changes (undo).
As soon as an operation completes a journal/log record is made. This can be done in two ways through a completion journal record or a completion log record (CLR). A CLR describes how to undo the operation logically, rather than physically so these records can be undone only. In the other words, while an operation performs changes to the database object, physical object consistency is maintained so that the operation can be undone physically, leaving the object in the exact state as it was before the operation started. On the contrary, operation completion records carry logical operations, thus when the operation is undone, the object can be in a different physical state.
This kind of logging makes fine-grained locking possible, allowing concurrency control on entities with no regard to database pages.
All operation completion records within a transaction are chained together, making it possible to selectively undo operations, enforcing the Atomicity property. Due to chaining, the undo operation is very efficient and requires almost the same amount of time as the original forward operation. This assessment assumes that most transactions commit rather than abort.
To ensure correctness the journal is flushed to the permanent storage before corresponding data changes are written to the storage. The journal is flushed to the storage at transaction commit time as well to ensure Durability property. The latter requirement greatly contributes to the commit time.
The database uses Strict Two Phase Locking (Strict 2PL) to ensure consistency and isolation. 2PL locking follows these rules:
If a transaction wants to read or write an object it must acquire a shared or exclusive lock on that object.
The transaction is not allowed to acquire any new locks after a lock has been released. The effect of this is transactions only release exclusive locks at commit time.
These two rules divide transactions into two phases. The first is the lock acquisition phase and the second is lock releasing phase. Locks are acquired automatically, during the transaction progress, or can be requested manually to avoid deadlock situations.
There are two general types of locks used on database objects:
Shared locks allow read only access on a resource. Multiple shared locks can be held on one database object.
Exclusive locks allow both read and write access to a resource. Only one exclusive lock can be held on a single database object.
There are number of objects being locked during database operations. They are:
This kind of lock is acquired at transaction start. By default transactions run concurrently, acquiring shared locks, but sometimes an operation needs to guarantee that it is performing a transaction alone.
This kind of lock is acquired automatically in shared mode as soon as the first cursor on a table is opened and released when the last cursor is deleted. When a transaction wants to modify a table's schema – add or drop fields or indexes, the exclusive lock is acquired.
This lock is acquired in the appropriate mode when a cursor observes a table row. When the lock is released depends on the isolation level, and will be described in more detail in Section 7.4, “Isolation Levels”.
This lock is acquired in the appropriate mode when performing an operation that involves indexes. The exact handling is described in Section 9.4.3, “Index Locking”.
Imagine we have an index which contains the sequence of values [1,2,3,4,5]. A transaction deletes the record with key [3], and the index is modified during the operation to exclude the deleted key (i.e. the index modification is not delayed till the commit). At the same time if another transaction scans the same index it would not know if a key was deleted from the index or not, thus causing the phantom of record absence in case the first transaction aborts. It can try to insert a record with the same key as deleted, or update another record to have the same key, which would lead to collision if the first transaction rollbacks. To prevent a collision from occurring, we should lock the modified range, since it contains an uncertain state that is unknown to the other transaction.
To avoid this situation the database locks the next key, i.e. the lock is delegated to the next key in index order, even if the next record has not actually been modified. In such a case the concurrently scanning transaction would see the locked row and would wait for the first transaction to resolve the state by either committing or aborting.
The next key locking technique is actually a range lock, where one lock represents the whole locked range – in our example, after deleting the key [3], we lock the whole range or keys from [2] to [4].
Now we can describe how various operations are performed to cope with the problem:
During insert the database finds an appropriate place to insert and then requests a lock on the next record, assuming that there is a possibility of a key being deleted. As soon as the lock is granted the insert operation is performed, and the next row lock is released.
Key update is done in a way that is similar to the insert operation; it first checks the new key position just like the insert operation does. The only difference is that since an update operation deletes the old key value, it leaves the row that is next to the old key value locked.
Fetch is done as usual, requesting a single lock per record fetched.
Next key locking might cause ‘mysterious’ locks – since there is no difference between a row lock and a range lock this might cause unexpected operation waiting sometimes. In our example, if the concurrent transaction tries to update the row with key [4], which is used as a range lock, it would wait until the first transaction completes.[1]
The database performs automatic locking on every operation. Sometimes this locking is not enough, so the database allows locking tables and rows manually. Since these locks do not reflect the transaction state, but serve application synchronization, the manual locks are considered to be advisory and are not enforced to obey the rules of 2PL. This means that the application can request and release advisory locks at any time. Moreover the application can request locks which have a life-time that is longer than transaction time, making it possible to guarantee inter-transaction consistency.
For more information on manual locking, see the section on Locking in the C API Reference and the section on Object Ids and Locking in the C++ API Reference.
[1] In practice these collisions are very rare. So rare that almost no database server using exactly this technique will ever mention this in the documentation.
| Platform | dbserver | dbserver GUI |
|---|---|---|
| Windows | bin\dbserver.exe | N/A |
| Windows CE | bin\ | bin\ |
| Linux | bin/dbserver | N/A |
Table 16. Utility program files by platform
Table of Contents
dbserver — Light-weight data server for multi-process shared access.
dbserver [ --start | -d ] [-t threadnum] [--tcp] [-ba address] [-bp port] [--shared-memory] [--shm-name name] [--shm-memory-size size] [--stop]
dbserver --stop | --install | --uninstall
dbserver provides shared database access to network clients. This allows processes running on one or more devices and computers to share access to the same database files.
An application will automatically connect to dbserver if it is linked with the inter-process communications (IPC) client library. For more details, see Section 7.2, “Client-server Shared Access”.
![]() | Tip |
|---|---|
dbserver is a light-weight program and is designed to be run on devices. Devices that are often disconnected from the network can use dbserver to store data locally and provide remote access to data stored on the device. |
Database files are created in the current working directory from which dbserver is run. Clients will have access to all database files in the current directory and all subdirectories. On Windows CE, the full path to the database file must always be given.
By default, dbserver only accepts connections from clients running on the same device. To accept connections from other devices or computers on the network, use the --bind-addr option to specify which network adapter should be allowed to connect.
On Windows CE, the dbserver command is named dbservercmd.exe.
On Linux, dbserver writes messages to the system log.
Server Options:
Run in the background as a service. The first argument must be --start and all subsequent arguments are handled as usual. The service must be installed with --install prior to using --start. The service can also be started using Services in the Control Panel, but --start should be omitted from the Start parameters when the service is started in this way. This option is not available on all platforms.
Run in the background as a daemon. This option detaches dbserver from the console. Database paths are relative to the root directory rather than the current directory. When the --daemon option is utilized by the root user, dbserver runs with the privileges of the user "daemon". This option is not available on all platforms.
threadnumMaximum number of simultaneous connections.
TCP Options:
Listen for TCP connections on the idb+tcp:// protocol. If no other connection type is selected, TCP is enabled by default.
addressBind to a network adapter to allow remote access. The default value is 127.0.0.1, which only allows access from the same device. Use the value 0.0.0.0 to accept connections from all network adapters.
portListen for a connection on the given TCP port. The default port number is 16534.
Shared Memory Options:
Listen for shared memory connections on the idb+shm:// protocol.
Name used to identify the shared server. The name can be blank, but it must be unique to run multiple instances of the shared memory server on the same machine.
Size of the memory buffer used for shared memory communications.
Help Options:
Displays complete command line option list.
Displays brief usage message.
Service Options:
Stop an ITTIA DB
Install dbserver as a service. After the service is installed, the dbserver executable must not be moved. By default, the service must be started manually. Only one copy of dbsever can be installed as a service at a time.
Remove the dbserver service.
dbserver GUI — Graphical interface to run the database server (Windows CE only).
On Windows CE, dbserver.exe is a graphical user interface to run and manage the data server.
![]() | Important |
|---|---|
dbservercmd.exe must be located in the same directory as dbserver.exe. |
ittiasql — SQL interactive query prompt
ittiasql [--create [--force]] [ --batch | --interactive ] [--execute command] [database]
ittiasql is an interactive command-driven program that allows you to use common SQL syntax to create, query, modify, and delete ITTIA DB SQL™ databases and tables. This utility is extremely useful for testing SQL commands and syntax before compiling them into your programs.
ittiasql take the following command line parameters:
Create a new database if it does not already exist.
When used with --create, force database creation even if the file already exists.
Execute SQL commands in batch mode. No prompt is displayed and query results are output in TSV format. Batch mode is used by default when stdin is a non-interactive stream, such as a file.
Execute SQL commands in interactive mode. A prompt is displayed and query results are output with formatting. Interactive mode is used by default when stdin is an interactive stream, such as a terminal window or command prompt.
commandExecute a single SQL command and exit.
databaseThe name of a database to open. A database can also be opened interactively with the .open command.
The following commands are available within an ittiasql session:
Session Commands
Displays a list of interactive commands.
Closes any open database and exits from ittiasql.
Database Commands
databaseOpens an existing database file.
Arguments: database - Name of database file to open.
databaseCreates a new database file.
Arguments: database - Name of the database to be created. If there is a database currently opened with the same name, the user is prompted to overwrite the file.
Closes the current open database.
Table Commands
Displays a list of all tables in the current open database.
Arguments: none.
table_nameDisplays a table's schema.
Arguments: table_name - Name of the table to describe.
Welcome to ittiasql. All SQL Commands end with a semi-colon (;). Non-sql userinputs all start with a period (.). No ending semi-colon (;) is required for non-sql userinputs. Type '.help' for help. ittia_sql$.open phone_book.dbphone_book.db$.list+--------+------------+ |TABLE_ID|TABLE_NAME | +--------+------------+ |13 |CONTACT | |16 |PHONE_NUMBER| |22 |TEST_TYPES | +--------+------------+ phone_book.db$select A.name, B.number from contact A, phone_number B-$where A.id = B.contact_id-$order by A.name;+-----+------------+ |NAME |NUMBER | +-----+------------+ |Bob |206-555-1000| |Fred |206-555-1308| |Fred |206-555-2335| |Fred |206-555-5361| |Susan|206-555-3890| +-----+------------+ phone_book.db$.closeittia_sql$.exit
| Name | Code | Description |
|---|---|---|
| DB_EBADF | -2 | Invalid file handle |
| DB_EEXIST | -3 | File exists |
| DB_ENOENT | -4 | File doesn't exist |
| DB_ENOSPACE | -5 | No space available |
| DB_EIO | -6 | I/O error |
| DB_EACCESS | -7 | Invalid access mode |
| DB_EEXTEND | -8 | File operation is out of file size |
| DB_EDEADLOCK | -9 | Unable to lock - deadlock condition detected |
| DB_ELOCKED | -10 | Unable to acquire lock |
Table A.3. OS Errors
| Name | Code | Description |
|---|---|---|
| DB_EINVAL | -11 | Invalid argument |
| DB_ERANGE | -12 | Argument is out of range |
| DB_ESTATE | -13 | Object state is not compatible with the method called or arguments |
| DB_ENOTIMPL | -14 | The requested functionality is not implemented in the current library package |
| DB_ENOMEM | -15 | [HY001] No memory available for allocation |
| DB_EOSERROR | -16 | General operating system error |
| DB_ENOTAPPLICABLE | -17 | Not applicable |
| DB_ENOTHREAD | -18 | No such thread |
| DB_EINTERNAL | -19 | Internal error detected |
| DB_EINVPATH | -20 | Invalid path encoding |
| DB_EINVFS | -21 | Invalid file system requested |
| DB_EDBGFAILURE | -22 | Simulated failure for debugging |
| DB_ENLSSPACE | -23 | Not enough space for NLS conversion |
| DB_EINVCHAR | -24 | Invalid character for NLS conversion |
| DB_EINVENC | -25 | Invalid encoding for NLS conversion |
Table A.4. Argument Errors
| Name | Code | Description |
|---|---|---|
| DB_ESOCKETSTARTUP | -50 | Socket startup error |
| DB_ESOCKETOPEN | -51 | Socket open error |
| DB_ESOCKET | -52 | General socket error |
| DB_ESOCKETHOSTNAME | -53 | Host name error |
| DB_ESOCKETCONNECTION | -54 | Connection error |
| DB_ESOCKETRECV | -55 | Socket data receive error |
| DB_ESOCKETSEND | -56 | Socket data send error |
| DB_ESOCKETNOTCONN | -57 | Socket is not connected |
Table A.5. Socket Errors
| Name | Code | Description |
|---|---|---|
| DB_EDUPLICATE | -100 | Duplicate value not allowed |
| DB_ENOTFOUND | -101 | Item not found |
| DB_ENOMOREDATA | -102 | No more data |
| DB_EKEYOUTOFRANGE | -103 | Key out of range |
| DB_EEMPTYRANGE | -104 | An attempt was made to set an empty range |
| DB_EBTREESLOTTOOLARGE | -105 | An attempt was made to insert into a B+tree a key and data that is too large |
| DB_ENEEDREVALIDATE | -106 | B+tree iterator needs revalidation |
| DB_ENORECORD | -107 | No record found |
| DB_ENAME | -109 | Invalid name |
| DB_EROWRANGE | -110 | Too many records in a table |
| DB_ESHUTDOWN | -111 | Shutdown is in progress |
| DB_ECONNLOCK | -112 | Connection is used by another thread |
Table A.6. General Errors
| Name | Code | Description |
|---|---|---|
| DB_ETABLE | -120 | General table error |
| DB_ETABLETYPE | -121 | Unknown table type |
| DB_ENOTABLE | -122 | No such table |
| DB_ENOMEMSTORAGE | -123 | Memory storage is not open, memory_storage_size is 0 |
| DB_ENOCONSTRAINT | -125 | No such constraint defined |
| DB_ETABLEEXISTS | -126 | Table already exists |
Table A.7. Table Errors
| Name | Code | Description |
|---|---|---|
| DB_EINDEX | -130 | General index error |
| DB_EPRIMARYDUP | -131 | Multiple primary keys defined |
| DB_EFKREFERENCE | -132 | Referenced row not found |
| DB_EFKRESTRICTED | -133 | Operation is restricted due to references |
| DB_EFKMATCHFULL | -134 | Operation is restricted due to incomplete full row match |
| DB_EFKEYDEF | -135 | Invalid foreign key definition |
| DB_EFKLIMIT | -136 | Too many cascade operations |
Table A.8. Index Errors
| Name | Code | Description |
|---|---|---|
| DB_EFIELD | -140 | General field error |
| DB_EDUPLICATEFIELDS | -141 | Cannot create duplicate fields |
| DB_EFIELDSIZE | -142 | Invalid field size |
| DB_EBINDSIZE | -143 | Invalid bind size |
| DB_EPARAM | -144 | Invalid parameter |
| DB_EINDVALUE | -145 | Length indicator value exceeds buffer size or is an invalid negative number |
| DB_ENULLFIELD | -146 | Insert or update would violate a NOT NULL constraint, or primary key must be NOT NULL |
| DB_EFIELDTYPE | -147 | Invalid field type |
Table A.9. Field Errors
| Name | Code | Description |
|---|---|---|
| DB_ESEQ | -150 | General sequence error |
| DB_ESEQ_OVERFLOW | -151 | Sequence overflow: no unique values remain |
| DB_ESEQ_NOCURVAL | -152 | Sequence does not yet have a current value, get the next value first |
Table A.10. Sequence Errors
| Name | Code | Description |
|---|---|---|
| DB_EINVALROWTYPE | -160 | Invalid row type |
| DB_EINVALKEYTYPE | -161 | Invalid key type |
| DB_EKEYTOOSHORT | -162 | Key is too short |
| DB_ERECORDSIZE | -163 | Record size is too large for chosen page size |
Table A.11. Row Errors
| Name | Code | Description |
|---|---|---|
| DB_ECURSOR | -169 | Invalid cursor for the given operation |
| DB_EDATA | -170 | Invalid field data value |
| DB_ECONVERT | -171 | Incompatible types: conversion is not possible |
Table A.12. Data Errors
| Name | Code | Description |
|---|---|---|
| DB_ESTORAGE | -174 | Storage data is invalid |
| DB_ECRC | -175 | CRC verification failed: database integrity has been compromised |
| DB_ERECOVERY | -176 | Storage needs crash recovery |
| DB_EUNDOSPACE | -177 | Overflow in undo log space, unable to write entries required for rollback |
| DB_EJOURNALTYPE | -178 | Invalid journal type |
| DB_EJOURNAL | -179 | Invalid journal |
| DB_EJOURNALCRC | -180 | Journal CRC validation failed |
| DB_EPREPARED | -181 | Prepared transactions support is required to complete operation |
Table A.15. Kernel Errors
| Name | Code | Description |
|---|---|---|
| DB_ENOTX | -190 | No transaction is active |
| DB_ETXSTATE | -191 | The operation cannot be performed in the current transaction state |
| DB_ETXACTIVE | -192 | Transaction is already running |
| DB_ESAVEPOINTEXIST | -193 | Savepoint already exists |
| DB_ENOSAVEPOINT | -194 | Specified savepoint does not exist |
| DB_ETXKIND | -195 | Operation cannot be performed for such transaction type (f.e local commit cannot be done on prepared transaction) |
| DB_EDTMXID | -196 | Invalid DTM xid provided |
Table A.16. Transaction Errors
| Name | Code | Description |
|---|---|---|
| DB_ENOLOCK | -200 | There are no available lock cells |
| DB_EMAXLOCKERS | -201 | There are too many lockers |
| DB_EINVLOCK | -202 | Unlock request for a lock that has not been obtained |
| DB_EWAITINTR | -203 | Lock waiting interrupted |
| DB_ENOLOCKOBJ | -204 | There are no available lock object cells |
Table A.17. Shared Access Errors
| Name | Code | Description |
|---|---|---|
| DB_EUSER | -210 | User generated error |
| DB_EAPIVER | -220 | API not initialized or feature not available in this version of the API |
| DB_EEVALUATION | -250 | Operation not permitted in evaluation version |
Table A.18. Other Errors
| Name | Code | Description |
|---|---|---|
| DB_EIPCPROTO | -300 | No compatible protocol negotiated |
| DB_EIPCINVAL | -301 | Invalid parameter in IPC message |
| DB_EIPCINVALMESSAGETAG | -302 | Invalid parameter in IPC message |
| DB_EIPCINVALPROCTAG | -303 | Invalid procedure tag in IPC CALL message |
| DB_EIPCSTORAGEALREADYOPENED | -304 | Storage already opened |
| DB_EIPCSTORAGENOTOPENED | -305 | Storage not opened |
| DB_EIPCINVALHANDLE | -306 | Invalid object's handle |
| DB_EIPCNULLPARAM | -307 | Complex parameter is NULL |
| DB_EIPCTOOFEWPARAMS | -308 | Too few parameters in message |
| DB_EIPCINVALMESSAGESIZE | -309 | Invalid message size |
| DB_EIPCINVALMESSAGEFORMAT | -310 | Invalid message format |
| DB_EIPCINVALURL | -311 | Invalid URL format |
| DB_EIPCINVALURLSCHEMA | -312 | Invalid URL schema |
| DB_EIPCENVSTARTUP | -313 | IPC environment startup error |
| DB_EIPCACCESS | -314 | Disabled operations |
| DB_EIPCMAXCONNECTIONS | -315 | Maximum connections is reached |
| DB_EIPCALREADYSTARTED | -316 | Server is already running |
| DB_EIPCNOLISTENER | -317 | No listeners |
| DB_EIPCDISCONNECT | -318 | Connection has been dropped |
Table A.19. IPC Client/Server Errors
| Name | Code | Description |
|---|---|---|
| DB_ESQLCODESIZETOOLARGE | -400 | Generated code exceeds maximum code size |
| DB_ESQLTABLENOTEXISTS | -401 | Table does not exist |
| DB_ESQLINVALIDCOLUMNNAME | -402 | Invalid column name |
| DB_ESQLCOLUMNAMBIGUOUSLYDEFINED | -403 | Column ambiguously defined |
| DB_ESQLINCOMPATIBLETYPES | -404 | Incompatible types |
| DB_ESQLINVALIDFUNCTIONNAME | -405 | Invalid function name |
| DB_ESQLINVALIDNUMBEROFARGUMENTS | -406 | Invalid number of arguments |
| DB_ESQLININVALIDARGUMENTTYPE | -407 | Invalid argument type |
| DB_ESQLINVALIDNUMBER | -408 | Invalid number |
| DB_ESQLINVALIDQUALIFIER | -409 | Invalid qualifier |
| DB_ESQLINCOMPATIBLEROWTYPES | -410 | Incompatible row types |
| DB_ESQLPARSEERROR | -411 | Error parsing SQL syntax |
| DB_ESQLNOTAGROUPBYEXPRESSION | -412 | Selected field is not a group-by expression |
| DB_ESQLFEATURENOTALLOWED | -413 | Feature not allowed |
| DB_ESQLDIVIDEBYZERO | -414 | Divide by zero |
| DB_ESQLTYPECVTNOTIMPLEMENTED | -415 | Cannot convert to incompatible type |
| DB_ESQLOPERATIONNOTIMPLEMENTED | -416 | Operation not implemented |
| DB_ESQLINVALIDRELATIONALOPERATOR | -417 | Invalid relation |
| DB_ESQLROWSIZETOOLARGE | -418 | Row size too large |
| DB_ESQLNOTSORTABLETYPE | -419 | Ordered by non-sortable type |
| DB_ESQLNOENOUGHVALUES | -420 | Not enough values specified |
| DB_ESQLTOOMANYVALUES | -421 | Too many values specified |
| DB_ESQLBOOLEXPRESSIONINSELECTLIST | -422 | Boolean expression in select list |
| DB_ESQLENV | -423 | SQL environment error |
| DB_ESQLINCONSISTENTPARAMTYPE | -424 | Inconsistent parameter type |
| DB_ESQLCOULDNOTDEDUCEPARAMTYPE | -425 | Could not deduce parameter type from context |
| DB_ESQLINVALIDPARAMINDEX | -426 | Missing parameter value |
| DB_ESQLPARAMCONVERT | -427 | Could not convert parameter to required type |
| DB_ESQLMIXEDPARAMSTYLE | -428 | Mixed parameter style (? and $) not supported |
| DB_ESQLINVALIDCOLUMNINDEX | -429 | Invalid column number |
| DB_ESQLINVALIDSORTCOLUMN | -430 | Invalid column number in order by clause |
| DB_ESQLINVALIDEXPRTYPE | -431 | Invalid expression type, boolean may be expected |
| DB_ESQLBINARYLITERAL | -432 | Invalid BINARY type literal |
Table A.20. SQL Statement Errors
| Name | Code | Description |
|---|---|---|
| DB_EREP_PEERPATH | -500 | Path to peer is unknown |
| DB_EREP_PEERADDRESS | -501 | Invalid or unset peer address |
| DB_EREP_ADDRESS | -502 | Invalid or unset replication address |
| DB_EREP_INVADDRESS | -503 | Peer address mismatch |
| DB_EREP_LOOPBACK | -504 | Attempt to replicate with a database having the same address |
| DB_EREP_NOPEER | -505 | Unknown replication peer |
| DB_EREP_CONFLICT | -506 | Conflict when propogating to replication peer |
| DB_EREP_NOCONN | -507 | Unable to open connection to replication peer |
| DB_EREP_BUFSIZE | -508 | Buffer underflow during replication |
Table A.21. Replication Errors