SQL databases state history
Allows to store item states history in SQL databases. Supported:
SQLite (serverless)
Microsoft SQL Server (alpha)
Dataframe filling requires tsdb extension (ts_extension configuration parameter) installed and chosen. The following extensions are supported:
Timescale (PostgreSQL)
The service provides unified database EAPI.
For a dedicated TimescaleDB connector, see TimescaleDB databases state history.
Note
Time convention used in EVA ICS time-frames to fill data:
S for seconds (e.g. 5S for 5 seconds)
T for minutes
H for hours
D for days
W for weeks
A to get automatic number of records (e.g. 5A for 5 exactly records)
Time convention used in EVA ICS to specify start/end of a timeframe:
If there are dedicated parameters for start/end, they are filled separately
If there is a single parameter only, it is filled either as START:END or as START only (END is automatically set to the current time)
where values must be:
UNIX timestamps only (if a parameter is strictly specified as a number)
Date in human-readable format (RFC3339 recommended)
Using the same notation as for filling. E.g. setting start=30T sets time-frame start to 30 minutes before now.
START value is always mandatory. If END value is required to be set to the current time, it can be omitted.
Setup
Use the template EVA_DIR/share/svc-tpl/svc-tpl-db-sql.yml:
# Event-to-SQL database service
command: svc/eva-db-sql
workers: 1
bus:
path: var/bus.ipc
config:
# database URI (supported: sqlite, mysql, postgres)
db: sqlite:///tmp/eva_history.db
# db: mysql://USER:PASSWORD@HOST/DB
# db: postgres://USER:PASSWORD@HOST/DB
# TS extension (supported: timescale), required for fills
#ts_extension: timescale
# event buffer time-to-live, seconds
buf_ttl_sec: null
# periodically submit all item states
interval: null
# do not submit remote disconnected items (useful for zfrepl or similar)
skip_disconnected: false
# ignore real-time events
ignore_events: false
# false - perform smart cleaning (slower) or simple
simple_cleaning: false
# keep records (seconds)
keep: 604800
# event queue size
queue_size: 8192
# panic on critical errors in X seconds after happened (0 - panic immediately)
panic_in: 0
# database pool size
#pool_size: 2 (default = workers)
# item OIDs / OID masks
oids:
- "#"
oids_exclude: []
user: nobody
Create the service using eva-shell:
eva svc create eva.db.s1 /opt/eva4/share/svc-tpl/svc-tpl-db-sql.yml
or using the bus CLI client:
cd /opt/eva4
cat DEPLOY.yml | ./bin/yml2mp | \
./sbin/bus ./var/bus.ipc rpc call eva.core svc.deploy -
(see eva.core::svc.deploy for more info)
EAPI methods
See EAPI commons for the common information about the bus, types, errors and RPC calls.
state_history
Description |
Gets item state history |
Parameters |
required |
Returns |
State history payload |
Name |
Type |
Description |
Required |
i |
String |
Item OID |
yes |
t_start |
f64 |
Beginning timestamp (default: last 24 hours) |
no |
t_end |
f64 |
Ending timestamp (default: now) |
no |
fill |
String |
Fill (nS/T/H/D/W e.g. 10T for 10-minute, requires ts_extension) |
no |
precision |
u32 |
Round values to digits after commma |
no |
limit |
u32 |
Limit records to |
no |
xopts |
Map<String, String> |
Extra, not used for SQL databases |
no |
compact |
bool |
Pack data in arrays according to type |
no |
Return payload example:
[
{
"status": 1,
"t": 1652059860.0424938,
"value": 15
},
{
"status": 1,
"t": 1652059865.045223,
"value": 15
},
{
"status": 1,
"t": 1652059870.0452943,
"value": 15
},
{
"status": 1,
"t": 1652059875.0443518,
"value": 15
}
]
state_log
Description |
Gets item state log |
Parameters |
required |
Returns |
State log payload (includes OIDs, as other svcs may support get-by-mask) |
Name |
Type |
Description |
Required |
i |
String |
Item OID, supports ending masks (e.g. sensor:group/#) |
yes |
t_start |
f64 |
Beginning timestamp (default: last 24 hours) |
no |
t_end |
f64 |
Ending timestamp (default: now) |
no |
limit |
u32 |
Limit records to |
no |
xopts |
Map<String, String> |
Extra: offset=N for query offset |
no |
Return payload example:
[
{
"oid": "sensor:tests/temp",
"status": 1,
"t": 1652060175.0443184,
"value": 15
},
{
"oid": "sensor:tests/temp",
"status": 1,
"t": 1652060180.046056,
"value": 15
},
{
"oid": "sensor:tests/temp",
"status": 1,
"t": 1652060185.0454304,
"value": 15
}
]
state_push
Description |
push item states into db, (payload: single item state or list) |
Parameters |
none |
Returns |
nothing |