Local Data Persistence

This section documents the local persistence layer used by the TEG Gateway for buffering, archiving, and recovery.

SQLite Abstraction

SQLite database utilities for the Edge Gateway.

This module provides a lightweight SQLite wrapper used throughout the Edge Gateway for local persistence. It is designed to be robust against transient failures, support concurrent access via locking, and automatically recover from corrupted or unavailable database files.

Typical use cases include buffering telemetry, logs, and controller messages when network connectivity is unavailable.

Design goals

• Safe concurrent access using a write lock.

• Automatic retry and recovery on database errors.

• Minimal abstraction over the standard sqlite3 module.

Notes

• Write-ahead logging (WAL) is enabled to improve concurrency.

• Databases are reset automatically if they become unusable.

class modules.sqlite.SqliteConnection(path, nr_retries=3, dont_retry=False)

Bases: object

Thread-safe SQLite connection wrapper with automatic recovery.

This class wraps a single SQLite connection and provides helper methods for executing queries, checking table state, and recovering from database errors by resetting the database file if necessary.

The connection is configured for concurrent access using WAL mode and a write lock to serialize write operations.

check()

Return the list of tables present in the database.

Return type:

None

close()

Close the underlying SQLite connection.

Return type:

None

do_table_values_exist(table)

Check whether a table exists and contains at least one row.

does_table_exist(table)

Check whether a table exists in the database.

Parameters:

table – Table name.

Return type:

bool

Returns:

True if the table exists, otherwise False.

execute(query, params=())

Execute an SQL query with optional parameters.

Parameters:

• query – SQL query string.

• params – Optional query parameters.

Return type:

Any

Returns:

Query result as returned by fetchall(), or None if the database is unavailable.

is_table_empty(table)

Check whether a table contains any rows.

Parameters:

table – Table name.

Return type:

bool

Returns:

True if the table exists and contains no rows.

reset_db_conn(error_msg, nr_retries=3, query='unknown')

Reset the SQLite database after an error.

Deletes the database file and reinitializes the connection.

Parameters:

• error_msg – Original exception or error message.

• nr_retries – Remaining retry count.

• query – Query that triggered the error.

Return type:

None

class modules.sqlite.SqliteTables(*values)

Bases: Enum

Enumeration of SQLite table names used by the Edge Gateway.

CONTROLLER_MESSAGES = 'messages'

HEALTH_CHECK = 'health_check'

PENDING_MQTT_MESSAGES = 'pending_mqtt_messages'

Database Schemas

Controller Archive

Database schema: controller archive table.

This module defines the SQL statements used to create and index the SQLite table that stores archived controller messages.

The table is intended for local backups. Messages can later be republished or discarded via RPC commands (see the user guide page on Remote Procedure Calls).

Schema

Table name:

controller_archive

Columns:

• id (INTEGER PRIMARY KEY AUTOINCREMENT): Surrogate key.

• timestamp_ms (INTEGER): Unix timestamp in milliseconds.

• message (TEXT): Serialized controller message payload.

Index

An index on timestamp_ms is created to accelerate time-range queries.

Notes

• The SQL strings are provided as constants to be executed by the gateway’s SQLite initialization/migration logic.

• The statements are idempotent via IF NOT EXISTS.

Controller Messages

Database schema: controller messages table.

This module defines the SQL statement used to create the SQLite table that stores controller messages before they are processed or forwarded by the Edge Gateway.

The table acts as a lightweight message buffer and is typically used for short- lived persistence of controller-originated messages.

Schema

Table name:

Value of sqlite.SqliteTables.CONTROLLER_MESSAGES

Columns:

• id (INTEGER PRIMARY KEY AUTOINCREMENT): Surrogate key.

• type (TEXT): Message type or category.

• message (TEXT): Serialized message payload.

Notes

• The SQL statement is provided as a constant and executed by the gateway’s database initialization logic.

• Table creation is idempotent via IF NOT EXISTS.

Pending Messages

Database schema: pending MQTT messages table.

This module defines the SQL statement used to create the SQLite table that stores pending MQTT messages managed by the Edge Gateway.

The table is used as a temporary persistence layer for outbound MQTT messages that could not yet be delivered, for example due to connectivity issues. Messages are removed once they are successfully published.

Schema

Table name:

Value of sqlite.SqliteTables.PENDING_MQTT_MESSAGES

Columns:

• id (INTEGER PRIMARY KEY AUTOINCREMENT): Surrogate key.

• type (TEXT): Message type or category.

• message (TEXT): Serialized MQTT message payload.

Notes

• The SQL statement is executed during gateway database initialization.

• Table creation is idempotent via IF NOT EXISTS.

• This table is distinct from the controller archive and intended for short-lived buffering only.