SQL databases state history

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

Dataframe filling requires tsdb extension (ts_extension configuration parameter) installed and chosen. The following extensions are supported:

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