Table of Contents

ITTIA DB SQL Getting Started Guide


ITTIA DB SQL

ITTIA DB SQL is a relational database management software library for embedded systems and intelligent Internet-of-Things devices.
Device applications embed ITTIA DB SQL to store, manage, analyze, connect, and distribute data.

How do I access the Software?

Request an evaluation kit for your TQ target board.

Package Contents

SDK Folder Structure
The ITTIA DB SQL SDK contains the following folders:

Folder Description
include Header files for ITTIA DB SQL
lib ITTIA DB SQL libraries
bin Utilities and applications
exampels Example C and C++ applications
share/doc/ittiadb Product documentation

ITTIA DB SQL documentation includes:


Installing and Using ITTIA DB SQL

Prerequisites and Requirements

Host

Target


Unpacking and installing the software

To install the ITTIA DB SQL development kit on the host, unpack the archive as root with the following command:

tar xzf -C / ittiasql-*-linux*.tar.gz

The ITTIA DB SQL SDK will be installed to /opt/ittiadb-<version>/<arch>.
Next, transfer the bin and lib folders from the SDK to the target’s /usr folder.
Finally, add the toolchain to the host PATH.

Generic: 

 
PATH=PATH:/path/to/your/OSELAS.Toolchain<version>/<cross compiler name>/gcc-<version>-glibc-<version>-binutils-<version>-kernel-<version>-sanitized/bin

For this tutorial: 

PATH=$PATH:/opt/OSELAS.Toolchain-2013.12.2/arm-v7a-linux-gnueabihf/gcc-4.8.3-glibc-2.18-binutils-2.24-kernel-3.12-sanitized/bin


Starting ITTIA DB SQL and creating your first database

On the target: 1. Run the ITTIA SQL Utility with the command ittiasql.
2. Create a new database file with the .create command. The database file is created in the current working directory by default.
3. Create a table, insert a few rows, and using select to list all rows in the table. For example:

create table hello_world (x integer, y varchar(20));
insert into hello_world
  values (1, 'Hello World');
insert into hello_world
  select n, 'Hello ' || cast(n as varchar) from $nat(3);
select * from hello_world;

4. Save all changes to the database with the commit; SQL statement. If you do not commit your changes, they will be lost when you close the database.
5. Use the .exit command to close the database when you are finished.

Running the examples

On the host, in the ITTIA DB SQL SDK folder, run:

make -C examples

This will build database sample programs. Copy one of the example programs, such as examples/c/sql/sql_select_query_c, to a writeable directory on the target, then run it from that directory. Most examples will create a database file, perform some updates, then prompt for a SQL statement. Enter a blank line to quit the example, or enter a statement, such as:

select * from tables


Connecting to ITTIA DB SQL for the first time

Start the ITTIA DB Server by running dbserver –no-restrict on the target. Use the –help option to learn more about this command.
To connect the database on server from the ittiasql utility, open the example database that you created in the previous section using the .open command and the <idb+tcp://> protocol: 

.open idb+tcp://localhost/example.db


Use the ITTIA DB Server to share a database file between different applications and processes. In each application, prefix the file name with idb+tcp://localhost/ to connect with dbserver.

Creating a Hello World Application


On the host, create hello_sql.c in a new folder:

#include <storage/data/environment.h>
#include <storage/ittiadb/connection.h>
#include <storage/ittiadb/query.h>
#include <storage/ittiadb/transaction.h>
#include <storage/data/single_field.h>
#include <storage/types/character_varying.h>
 
#include <iostream>
 
using ::storage::data::Environment;
using ::storage::ittiadb::Connection;
using ::storage::ittiadb::Query;
using ::storage::ittiadb::Transaction;
using ::storage::data::SingleField;
using ::storage::types::CharacterVarying;
 
int main(int argc, char* argv[])
{
    Environment::init();
 
    // Create a local database file
    Connection database("hello_sql.ittiadb");
    database.open(Connection::CreateAlways);
 
    // Initialize database schema
    Query(database, 
        "create table hello_world ("
        "  id integer,"
        "  message varchar(20)"
        ")"
        ).execute();
 
    // Insert a record
    Query(database,
        "insert into hello_world values (0, 'hello world')"
        ).execute();
 
    // Read the value of a single database field
    SingleField message;
    Query(database,
        "select message from hello_world where id = 0"
        ).execute(message);
 
    // Save changes and release locks
    Transaction(database).commit();
 
    // Output field value
    std::cout << *message.to< CharacterVarying<20> >() << std::endl;
 
    return 0;
}


Create GNUmakefile in the same folder:

ARCH = arm-v7a-linux-gnueabihf
CXX = $(ARCH)-g++

ITTIA_DB_VERSION = 1.0.0
ITTIA_DB_HOME = /opt/ittiadb-$(ITTIA_DB_VERSION)/$(ARCH)

hello_sql: hello_sql.o
     $(CXX) -o $@ $(LDFLAGS) $< -L$(ITTIA_DB_HOME)/lib -littiasql -lstorage -pthread

hello_sql.o: hello_sql.cpp
     $(CXX) -c -o $@ $(CPPFLAGS) $(CXXFLAGS) -pthread -I$(ITTIA_DB_HOME)/include $<

Run make to compile the program hello_sql. Transfer this program to the target and run from a writeable directory.

Tutorial

Examples

The ITTIA DB SQL SDK includes examples in the following categories:


Troubleshooting

If the ITTIA DB SQL shared library is not installed to /usr/lib on the target, you may get a missing library error. This error can be remedied by setting the LD_LIBRARY_PATH environment variable on the target: 

export LD_LIBRARY_PATH=/path/to/lib

During evaluation on TQ platforms, you have access to to full support from ITTIA.

Other Platforms