Data Acquisition

Development status

This extension provides fundamental components for data acquisition - a pillar of SCADA system.

This extension internally relies on CuteHMI.SharedDatabase.1 extension and it is heavily recommended to use it with database connection created with CuteHMI.SharedDatabase.1.

Currently PostgreSQL and SQLite backends are supported.

Creating the schema

In order to use this extension, apart from making CuteHMI.SharedDatabase.1 operational, a schema has to be created. This can be done from C++ or QML, but creation and drop scripts are also listed below.

Console tool

Schema can be created with cutehmi.console.0 tool. To do so launch the tool.

cutehmi.console.0 CuteHMI.DataAcquisition.1

TL;DR

To determine whether it will work at all, type the following in the console.

# service.start()
CuteHMI.2: [NOTIFICATION] Service 'Database Service' has started.
# schema.create()
CuteHMI.2: [NOTIFICATION] Successfully created 'console' schema.

This should create default SQLite database, appropriate for testing.

To drop the schema type:

# schema.drop()
CuteHMI.2: [NOTIFICATION] Dropped 'console' schema.

Custom setup

For customized setup, first you need to pick a database type.

# db.type = "QPSQL"

Valid types are QPSQL for PostgreSQL and QSQLITE for SQLite.

Supply database name.

# db.name = "meinDatabase"

Note that SQLite will create a file with database given name, but for PostgreSQL you must create database beforehand!

Then for PostgreSQL typically follows host, port, user and password, but SQLite driver ignores most of these fields.

# db.host = "localhost"
# db.port = 5432
# db.user = "postgres"
# db.password = "postgres"

Start the service.

# service.start()
CuteHMI.2: [NOTIFICATION] Service 'Database Service' has started.

If you get errors, fix database setup, ensure that database is runnig, check permissions, firewall, default gateway, etc. Start with cables...

Now you may choose shcema name.

# schema.name = "meinSchema"

Schema user can be also selected. Database defaults are used if left empty.

# schema.user = "Adolf"

To complete the process call create() slot.

# schema.create()
CuteHMI.2: [NOTIFICATION] Successfully created 'meinSchema' schema.

Schema can be dropped in a similar way by calling drop() slot.

# schema.drop()
CuteHMI.2: [NOTIFICATION] Dropped 'meinSchema' schema.

PostgreSQL

Following script is used for PostgreSQL to create schema. All occurrences of %1 shall be replaced with given schema name.

CREATE SCHEMA %1;
 
CREATE TABLE %1.tag
(
        id serial PRIMARY KEY,
        name varchar(255) NOT NULL UNIQUE
);
 
CREATE INDEX index_tag_name ON %1.tag USING hash (name);
 
CREATE TABLE %1.recency_bool
(
        tag_id integer REFERENCES %1.tag(id) PRIMARY KEY,
        value bool NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE TABLE %1.recency_int
(
        tag_id integer REFERENCES %1.tag(id) PRIMARY KEY,
        value integer NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE TABLE %1.recency_real
(
        tag_id integer REFERENCES %1.tag(id) PRIMARY KEY,
        value double precision NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE TABLE %1.event_bool
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        value bool NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE INDEX index_event_bool_time ON %1.event_bool (time);
 
CREATE TABLE %1.event_int
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        value integer NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE INDEX index_event_int_time ON %1.event_int (time);
 
CREATE TABLE %1.event_real
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        value double precision NOT NULL,
        time timestamptz NOT NULL
);
 
CREATE INDEX index_event_real_time ON %1.event_real (time);
 
CREATE TABLE %1.history_bool
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        open bool NOT NULL,
        close bool NOT NULL,
        min bool NOT NULL,
        max bool NOT NULL,
        open_time timestamptz NOT NULL,
        close_time timestamptz NOT NULL,
        count integer NOT NULL
);
 
CREATE INDEX index_history_bool_close_time ON %1.history_bool (close_time);
 
CREATE TABLE %1.history_int
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        open integer NOT NULL,
        close integer NOT NULL,
        min integer NOT NULL,
        max integer NOT NULL,
        open_time timestamptz NOT NULL,
        close_time timestamptz NOT NULL,
        count integer NOT NULL
);
 
CREATE INDEX index_history_int_close_time ON %1.history_int (close_time);
 
CREATE TABLE %1.history_real
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        open double precision NOT NULL,
        close double precision NOT NULL,
        min double precision NOT NULL,
        max double precision NOT NULL,
        open_time timestamptz NOT NULL,
        close_time timestamptz NOT NULL,
        count integer NOT NULL
);
 
CREATE INDEX index_history_real_close_time ON %1.history_real (close_time);

To drop the schema use the following.

DROP SCHEMA %1 CASCADE;

SQLite

Following script is used for SQLite to create schema. SQLite treats database name as a schema, therefore schema name is incorporated into table names. This requires table names to be wrapped in square brackets. All occurrences of %1 shall be replaced with given schema name.

CREATE TABLE [%1.tag]
(
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        name VARCHAR(255) NOT NULL UNIQUE
);
 
CREATE INDEX [%1.index_tag_name] ON [%1.tag] (name);
 
CREATE TABLE [%1.recency_bool]
(
        tag_id INTEGER REFERENCES [%1.tag](id) PRIMARY KEY,
        value BOOL NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE TABLE [%1.recency_int]
(
        tag_id INTEGER REFERENCES [%1.tag](id) PRIMARY KEY,
        value INTEGER NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE TABLE [%1.recency_real]
(
        tag_id INTEGER REFERENCES [%1.tag](id) PRIMARY KEY,
        value double precision NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE TABLE [%1.event_bool]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value BOOL NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_event_bool_time] ON [%1.event_bool] (time);
 
CREATE TABLE [%1.event_int]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value INTEGER NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_event_int_time] ON [%1.event_int] (time);
 
CREATE TABLE [%1.event_real]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value double precision NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_event_real_time] ON [%1.event_real] (time);
 
CREATE TABLE [%1.history_bool]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        open BOOL NOT NULL,
        close BOOL NOT NULL,
        min BOOL NOT NULL,
        max BOOL NOT NULL,
        open_time INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_history_bool_close_time] ON [%1.history_bool] (close_time);
 
CREATE TABLE [%1.history_int]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        open INTEGER NOT NULL,
        close INTEGER NOT NULL,
        min INTEGER NOT NULL,
        max INTEGER NOT NULL,
        open_time INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_history_int_close_time] ON [%1.history_int] (close_time);
 
CREATE TABLE [%1.history_real]
(
        id INTEGER PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        open double precision NOT NULL,
        close double precision NOT NULL,
        min double precision NOT NULL,
        max double precision NOT NULL,
        open_time INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);
 
CREATE INDEX [%1.index_history_real_close_time] ON [%1.history_real] (close_time);

To drop the schema use the following.

DROP TABLE IF EXISTS [%1.tag];
DROP INDEX IF EXISTS [%1.index_tag_name];
DROP TABLE IF EXISTS [%1.recency_bool];
DROP TABLE IF EXISTS [%1.recency_int];
DROP TABLE IF EXISTS [%1.recency_real];
DROP TABLE IF EXISTS [%1.event_bool];
DROP INDEX IF EXISTS [%1.index_event_bool_time];
DROP TABLE IF EXISTS [%1.event_int];
DROP INDEX IF EXISTS [%1.index_event_int_time];
DROP TABLE IF EXISTS [%1.event_real];
DROP INDEX IF EXISTS [%1.index_event_real_time];
DROP TABLE IF EXISTS [%1.history_bool];
DROP INDEX IF EXISTS [%1.index_history_bool_close_time];
DROP TABLE IF EXISTS [%1.history_int];
DROP INDEX IF EXISTS [%1.index_history_int_close_time];
DROP TABLE IF EXISTS [%1.history_real];
DROP INDEX IF EXISTS [%1.index_history_real_close_time];