SQLite3 Database
The SQLite3 database primarily saves profiles and data related to sequences (shots) with high fidelity. It also saves a few bits of persistent data, such as the scale-period estimates. It is not intended to be a generic settings store.
SQLite3 allows concurrent, multi-user access with WAL mode. Additional SQLite3 databases can be opened by other apps, allowing joins and other operations between them.
Note
When opening the database from another app or the command line, only use the pyde1 user.
Although other users have read access, failing to use the pyde1 user may result in files being created that the pyde1 user does not have write access to. This can cause “read-only” errors, even though the pyde1 user has write access to the main database file.
Backing Up
For any database, file-system backups may not result in a self-consistent snapshot. Using the database’s backup utility is highly recommended.
One approach is shown in this script
#!/bin/sh
filename=$(date +'/home/pyde1/db_backup/pyde1.%Y-%m-%d_%H%M.sqlite3')
sqlite3 /var/lib/pyde1/pyde1.sqlite3 ".backup $filename"
xz $filename
This can be run periodically by pyde1 by editing the crontab with
sudo -u pyde1 crontab -e
to add a line like
00 03 * * * /home/pyde1/bin/pyde1-backup.sh
(Every day at 0300, local time)
Schema
The schema version is available as PRAGMA user_version
. The schema itself
is distributed at pyDE1/database/schema
.
Times are real values, such as would be returned by Python time.time()
Sequences
Most of the database is dedicated to capturing the notifications that are
generated immediately before and during a FlowSequencer
sequence.
There is a rolling buffer that captures the most recent notifications that then gets written to the database shortly after the start of a sequence.
Each sequence has a “master record” that is created at the start of the sequence. Some fields are updated, such as times, are updated as the sequence progresses. As this is done asynchronously, this record may not be complete the instant the sequence ends.
CREATE TABLE sequence (
id TEXT NOT NULL PRIMARY KEY,
active_state TEXT,
start_sequence REAL,
start_flow REAL,
end_flow REAL,
end_sequence REAL,
profile_id TEXT NOT NULL REFERENCES profile(id),
-- https://www.sqlite.org/quirks.html#no_separate_boolean_datatype
profile_assumed INTEGER, -- will match TRUE and FALSE keywords
resource_version TEXT,
resource_de1_id TEXT,
resource_de1_read_once TEXT,
resource_de1_calibration_flow_multiplier TEXT,
resource_de1_control_mode TEXT,
resource_de1_control_tank_water_threshold TEXT,
resource_de1_setting_before_flow TEXT,
resource_de1_setting_steam TEXT,
resource_de1_setting_target_group_temp TEXT,
resource_scale_id TEXT
);
Virtually all of the MQTT notifications are captured and associated with the
sequence.id
to allow for recreation of the data during the shot.
As an example
CREATE TABLE shot_sample_with_volume_update (
sequence_id TEXT NOT NULL REFERENCES sequence(id),
version TEXT,
sender TEXT,
arrival_time REAL,
create_time REAL,
event_time REAL,
--
de1_time REAL,
--
sample_time INTEGER,
group_pressure REAL,
group_flow REAL,
mix_temp REAL,
head_temp REAL,
set_mix_temp REAL,
set_head_temp REAL,
set_group_pressure REAL,
set_group_flow REAL,
frame_number INTEGER,
steam_temp REAL,
--
volume_preinfuse REAL,
volume_pour REAL,
volume_total REAL,
volume_by_frames TEXT -- Python list, default formatting
);
Profiles
Profiles, uploaded through the HTTP API, get stored in the database, along
with their metadata. They are referenced by a unique ID over the uploaded
content, as well as indexed by a fingerprint
of the frames that would be
delivered to the DE1. The same fingerprint
is the same for the DE1,
but would be from different source data or have different metadata.
CREATE TABLE profile (
id TEXT NOT NULL PRIMARY KEY,
source BLOB NOT NULL,
source_format TEXT NOT NULL,
fingerprint TEXT NOT NULL,
date_added REAL,
title TEXT,
author TEXT,
notes TEXT,
beverage_type TEXT
);
persist_hkv
This is a small table used internally to persist time-varying data across restarts of pyDE1 or connection and disconnection of devices. It should be considered opaque and not part of the supported API.