====== 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>
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> 
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>
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:
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:\\
<code>
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>
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:\\
<code>
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>
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>
.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 c>
#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>
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>
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]]