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:
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 .
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.
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
| Arguments | Explanation |
|---|---|
| database-name | a 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". |
| password | database authorization password |
| page_size | file storage page size (default 4KiB) |
| buffer_count | number of page buffers to allocate |
| mem_size | number of bytes to allocate for memory tables |
| mem_page_size | memory storage page size (default 2KiB) |
| cipher_type | "none" (default), "AES128" or "AES256" |
| cipher_data | file 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:
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.