SQL databases state history

Contents

SQL databases state history
- Setup
- EAPI methods

Allows to store item states history in SQL databases. Supported:

SQLite (serverless)
MySQL
PostgreSQL
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

Parameters
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)

Parameters
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