====== 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? ====

[[https://www.ittia.com/products/ittia-db-sql/evaluation|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:

  * User’s Guide: The essential guide to ITTIA DB SQL usage and software development.
  * C API Reference: Reference manual for the ITTIA DB C API.
  * C%%++%% API Reference: Reference manual for the ITTIA DB C%%++%% API.

===== Installing and Using ITTIA DB SQL =====
==== Prerequisites and Requirements ====
Host
  * OSELAS toolchain [[https://public.pengutronix.de/software/ptxdist/appnotes/AppNote_BuildingToolchain.pdf|Appnote building OSELAS Toolchain]] or [[https://www.ptxdist.org/doc/environment.html#install-the-binary-oselas-toolchain|Install the binary OSELAS Toolchain]]
Target
  * Target system with operating RootFS
  * User with password (needed for ssh remote access)
  * Network connection to development host

==== 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:\\
<code bash>
tar xzf -C / ittiasql-*-linux*.tar.gz
</code>
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:

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

For this tutorial:

<code bash>
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
</code>

==== Starting ITTIA DB SQL and creating your first database ====
On the target:
  - Run the ITTIA SQL Utility with the command ''ittiasql''.
  - Create a new database file with the ''.create'' command. The database file is created in the current working directory by default.
  - Create a table, insert a few rows, and using select to list all rows in the table. For example:<code mysql>
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;
</code>
  - 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.
  - 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:
<code bash>
make -C examples
</code>
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:
<code mysql>
select * from tables
</code>

==== 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%%:

<code mysql>
.open idb+tcp://localhost/example.db
</code>

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:
<code cpp>
#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;
}
</code>

Create **GNUmakefile** in the same folder:
<code bash>
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 $<
</code>
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:
  *[[https://www.ittia.com/products/ittia-db-sql/examples/file-storage|File Storage Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/sql-statement|Statement Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/replication|Replication Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/security|Security Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/error-handling|Error Handling Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/data-modeling|Data Modeling Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/memory-storage|Memory Storage Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/shared-access|Shared Access Examples]]
  *[[https://www.ittia.com/products/ittia-db-sql/examples/phone-book|Phone Book]]

==== 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:
<code bash>
export LD_LIBRARY_PATH=/path/to/lib
</code>
During evaluation on TQ platforms, you have access to to full support from ITTIA.
  *[[http://www.ittia.com/services/technical-support|Contact ITTIA Technical Support]]
==== Other Platforms ====
  *[[https://www.ittia.com/guide/linux|Linux]]
  *[[https://www.ittia.com/platforms/vxworks|VxWorks]]
  *[[https://www.ittia.com/platforms/qnx|QNX]]
  *[[https://www.ittia.com/guide/windows|Windows]]
==== Related Links ====
  *[[https://www.ittia.com/services/training|ITTIA DB SQL Training]]
  *[[https://www.ittia.com/online-database-consultation|Request a consulation]]