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.
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: 2
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: null
# database pool size
#pool_size: 2 (default = workers)
# item OIDs / OID masks
oids:
- "#"
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 |