Data Acquisition

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

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

Currently PostgreSQL and SQLite backends are supported.

Creating the schema

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

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 TABLE %1.event_int
(
        id serial PRIMARY KEY,
        tag_id integer REFERENCES %1.tag(id),
        value integer NOT NULL,
        time timestamptz NOT NULL
);
 
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 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 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 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
);
 

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, thereofre 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 serial PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value BOOL NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE TABLE [%1.event_int]
(
        id serial PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value INTEGER NOT NULL,
        time INTEGER NOT NULL
);
 
CREATE TABLE [%1.event_real]
(
        id serial PRIMARY KEY,
        tag_id INTEGER REFERENCES [%1.tag](id),
        value double precision NOT NULL,
        time INTEGER NOT NULL
);
 
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 INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);
 
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 INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);
 
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 INTEGER NOT NULL,
        close_time INTEGER NOT NULL,
        count INTEGER NOT NULL
);

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 TABLE IF EXISTS [%1.event_int];
DROP TABLE IF EXISTS [%1.event_real];
DROP TABLE IF EXISTS [%1.history_bool];
DROP TABLE IF EXISTS [%1.history_int];
DROP TABLE IF EXISTS [%1.history_real];