Skip to main content
Version: 5.0

ITTIA DB SQL for Device Data Management

ITTIA DB SQL​

ITTIA DB SQL develops, manufactures, licenses, and supports data management software products and provides related services. ITTIA DB SQL brings robust data management facilities to bear on the tough data management problems now faced by embedded developers, in a package that is lightweight, cross-platform, fast, and easy to use. With ITTIA DB SQL, intelligent devices and embedded systems have access to full data management capabilities even when disconnected.

ITTIA DB SQL for Device Data Management​

ITTIA DB SQL for Device Data Management includes an interactive SQL utility named ittiasql and demos such as ITTIA Device Database Microgrid demo that stores sensor data stored on the device in flash SQL database and enables the data visualization online.

Supported Modules​

The following Computer on Modules are supported:

  • Colibri iMX6
  • Colibri iMX6ULL

Supported Displays​

While you can use a wide variety of displays and monitors, additional configuration may be required for a specific setup.

A section in the end of this guide provide instructions about display configuration.

Intended Use​

This partner demo image is meant for the evaluation of the technology. It is not suitable for development or production.

How to Get Started​

This section provides instructions for you to quickly get started with ITTIA DB SQL for Device Data Management.

Load the Toradex Easy Installer on the target module and select ITTIA DB SQL for Device Data Management from the list of available images. Please follow the steps described in ITTIA .

ITTIA Toradex Easy Installer

Test ITTIA Device Database Microgrid demo​

When the installation ends, power-cycle the board and wait until you see a desktop environment. You can verify the stored data into your web browser, using the board's IP, e.g. if your board's IP is 10.22.1.42 one can navigate into the Microgrid demo by typing 10.22.1.42 in the web browser.

ITTIA Device Database Microgrid

The dashboard shown is updated with real-time data and complete history of sensor records.

You can create queries to access and analyze data on the device. To do so, run the ittiasql utility using the following command line


root@colibri-imx6:~# /usr/bin/ittiasql
ittiasql version 7.3.8
Copyright (C) 2005-2018 ITTIA. All rights reserved.

SQL statements end with a semi-colon (;). Interactive commands start with
a period (.) and do not end with a semi-colon.

Type '.help' for a list of interactive commands. Type '.exit' to quit.
$

Open one of the database files located at /home/root, e.g.

$ .open microgrid-demo.ittiadb
1.731 seconds
microgrid-demo.ittiadb$

or even

$ .open idb+tcp://localhost/microgrid-demo.ittiadb
0.872 seconds
idb+tcp://localhost/microgrid-demo.ittiadb$

Look at the tables in the given database

idb+tcp://localhost/microgrid-demo.ittiadb$ .list

+--------+----------------------------------------------------------------+----------+
|table_id|table_name |table_type|
+--------+----------------------------------------------------------------+----------+
|23 |measurement |kheap |
|26 |sensor |kheap |
|30 |event |kheap |
|32 |data_originator |kheap |
|34 |sensor_fault |kheap |
|36 |fault_type |kheap |
|2 |SolarReadingProfile |memory |
|3 |SolarReadingProfile_solarReadingList_solarReading |memory |
|4 |GenerationReadingProfile |memory |
|5 |GenerationReadingProfile_generationReadingList_generationReading|memory |
|6 |ResourceReadingProfile |memory |
|7 |ResourceReadingProfile_resourceReadingList_resourceReading |memory |
|8 |ESSReadingProfile |memory |
|9 |ESSReadingProfile_essReadingList_essReading |memory |
+--------+----------------------------------------------------------------+----------+

0.327 seconds

Execute queries in the tables


idb+tcp://localhost/microgrid-demo.ittiadb$ select * from sensor;

+-------------+---------+---------------------+-------------+-------------+
|originator_id|sensor_id|sensor_name |min_tolerance|max_tolerance|
+-------------+---------+---------------------+-------------+-------------+
|1 |0 |DEMO.MGRID.RESOURCE.1|NULL |10000000 |
|1 |1 |DEMO.MGRID.RESOURCE.2|NULL |10000000 |
|1 |2 |DEMO.MGRID.RESOURCE.3|NULL |10000000 |
|1 |3 |DEMO.MGRID.RESOURCE.4|NULL |10000000 |
|1 |4 |DEMO.MGRID.RESOURCE.5|NULL |10000000 |
|1 |5 |DEMO.MGRID.RESOURCE.6|NULL |10000000 |
|1 |6 |DEMO.MGRID.SOLAR.1 |NULL |10000000 |
|1 |7 |DEMO.MGRID.BATTERY.1 |NULL |10000000 |
|1 |8 |DEMO.MGRID.WIND.1 |NULL |10000000 |
+-------------+---------+---------------------+-------------+-------------+

9 rows in 2 passes, 0.211 seconds, transaction active

Create your own database with ITTIA DB SQL utility​

In your target module, run the utility ittiasql. To create a new database file, use the command .create according to the instructions below. The database file is created in the current working directory by default.

$ .create database-name [storage=<string>] [password=<string>]
[page_size=<bytes>] [buffer_count=<integer>]
[mem_size=<bytes>] [mem_page_size=<bytes>]
[cipher_type=<string>] [cipher_data=<string>]
Creates a database. If successful, the current open database is closed.

The arguments are described

ArgumentsExplanation
database-namea local filename or remote database URL:
- [path/]example.db
- idb+tcp://server[:port]/[path/]example.db
- idb+shm://[share-name]/[path/]example.db
storage"file" (default) or "memory".
passworddatabase authorization password
page_sizefile storage page size (default 4KiB)
buffer_countnumber of page buffers to allocate
mem_sizenumber of bytes to allocate for memory tables
mem_page_sizememory storage page size (default 2KiB)
cipher_type"none" (default), "AES128" or "AES256"
cipher_datafile storage encryption key

As an example,

$ .create test.db
test.db$

Create a table using

test.db$ create table hello_world (x integer, y varchar(20));
0.567 seconds

To insert rows, use the following

test.db$ insert into hello_world values (10, 'Hello');
1 row affected, 0.023 seconds, transaction active

Execute queries in the tables from your database

test.db$ select * from hello_world;

+--+-----+
|x |y |
+--+-----+
|10|Hello|
+--+-----+

1 row in 2 passes, 0.311 seconds, transaction active

And save the changes with commit

test.db$ commit;
0.636 seconds

Check your database statistics with stats

test.db$ .stats

=== Page cache buffers currently in use ===
Buffer Page Pin Rank Locked Fixed Type
0 0 30 5 no no root
1 1 1 1 no no alloc
2 23 6 5 no no B+tree leaf
3 2 16 5 no no B+tree leaf
4 3 4 3 no no B+tree leaf
5 4 18 5 no no B+tree leaf
6 5 20 5 no no B+tree leaf
7 6 4 3 no no B+tree leaf
8 9 5 4 no no B+tree leaf
9 11 1 0 no no B+tree leaf
10 13 1 0 no no B+tree leaf
11 15 1 0 no no B+tree leaf
12 18 1 0 no no B+tree leaf
13 21 1 0 no no B+tree leaf
14 16 1 1 no no B+tree leaf
15 7 6 5 no no B+tree leaf
16 10 7 5 no no B+tree leaf
17 19 3 5 no no B+tree leaf
18 14 3 5 no no B+tree leaf
19 20 1 1 no no B+tree leaf
Page cache size: 4194304 bytes = 1024 buffers * 4096 bytes/buffer

=== Page cache utilization ===
Pin requests: 212 (1st time: 110, 2nd time: 82)
Cache hits: 192 (90.57%), incl. 0 on 2nd attempt
Cache misses: 20 (9.43%), +0 on 1st attempt only
Pages stolen after first hit: 0
Frames scanned: 20 (with 0 restarts)

=== Disk utilization ===
Pages loaded from file: 20 (0 discarded without change)
Pages flushed to file: 0
(0 background, 0 forced, 0 in lazy write)

=== File storage metrics ===
Storage size: 98304 bytes = 24 pages * 4096 bytes/page
Used space: 98304 bytes in 24 pages
Free space: 0 bytes (0%)

0.408 seconds

For further information, please visit ITTIA DB SQL for Device Data Management.

Display Configuration​

Displays and Monitors used in Embedded Systems are available in a myriad of configuration possibilities - resistive, capacitive or without touch, single or multi touch, different resolution, density of pixels, pin-out and clock frequency are some examples.

To make things easy for you, Toradex provides specific instructions on how to use its display offerings as well as comprehensive information about how to interface your custom display or monitor to Toradex modules. Please refer to the table below:

DisplayDisplay ResolutionTouch Screen SupportTouch Screen Calibration
Resistive Touch Display 7" ParallelFirst Steps with Parallel Resistive Touch Display - ResolutionSupported out-of-the-boxTouch Screen Calibration (Linux)
Capacitive Touch Display 7" ParallelFirst Steps with Capacitive Touch Display 7 Inch Parallel - ResolutionFirst Steps with Capacitive Touch Display 7 Inch Parallel - Touch ScreenTouch Screen Calibration (Linux)
VGA monitorDisplay Output, Resolution and TimingsUSB HID touch screen works out-of-the-boxTouch Screen Calibration (Linux)
DVI/HDMI monitorDisplay Output, Resolution and TimingsUSB HID touch screen works out-of-the-boxTouch Screen Calibration (Linux)
• For additional comprehensive information
• If you use another display/monitor
Display Output, Resolution and Timings• Compile the specific kernel driver: Build Linux kernel
• Enable it in the device-tree: Device Tree Customization
Touch Screen Calibration (Linux)

Downloads​

Download offline installers and older releases of the Partner Demo Image in this section.

Colibri iMX6​

Colibri iMX6ULL​

ITTIA DB SQL for Device Data Management License Agreement​

ITTIA DB SQL for Device Data Management is licensed under a commercial license. Contact ITTIA DB SQL for further information and get your evaluation kit.