Search by Tags

Partner Demo Image - ITTIA DB SQL for Device Data Management

 
Subscribe for this article updates

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 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.

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

    Installing ITTIA DB SQL for Device Data Management using the Toradex Easy Installer (click to enlarge)

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

    ITTIA Device Database Microgrid demo (click to enlarge)

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

You can create queries to access and analyse 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:

ITTIA DB SQL for Device Data Management License Agreement

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