Перейти к содержанию
Материалы раздела

Модуль SQLite

Модуль lsqlite3 предоставляет SQLite 3.53.4 на плате ODNFC-LAN-PRO. В профиле ODNFC-LAN модуль выключен из-за недостаточного запаса flash под прошивку.

local sqlite3 = require("lsqlite3")
-- То же самое доступно как ROM-глобал sqlite3.

local db, code, message = sqlite3.open("/public/example.db")
assert(db, string.format("SQLite %d: %s", code, message))

assert(db:exec([[
    CREATE TABLE IF NOT EXISTS sensor(
        id INTEGER PRIMARY KEY,
        value REAL NOT NULL,
        created_at TEXT NOT NULL
    )
]]) == sqlite3.OK)

local stmt = assert(db:prepare(
    "INSERT INTO sensor(value, created_at) VALUES(?1, ?2)"))
assert(stmt:bind_values(23.5, "2026-08-22T12:00:00Z") == sqlite3.OK)
assert(stmt:step() == sqlite3.DONE)
assert(stmt:finalize() == sqlite3.OK)

for row in db:nrows("SELECT id,value,created_at FROM sensor ORDER BY id") do
    print(row.id, row.value, row.created_at)
end

assert(db:close() == sqlite3.OK)

Файлы базы данных

SQLite работает только с обычными незашифрованными файлами в корневой LittleFS, например /public/example.db. Псевдобаза :memory:, URI вида file:..., файлы из /romfs и /ramfs, shared cache и пользовательские VFS не поддерживаются.

Готовую .db можно загрузить без преобразования в разделе «Файлы» веб-среды. Файл передаётся бинарным PUT и после загрузки доступен как /public/<имя>.db. Перед заменой или удалением обязательно закройте все statement, backup и соединения с этой базой. Если файл открыт через SQLite, сервер вернёт HTTP 409 и не станет менять его.

Обмен работает в обе стороны. Базу можно создать, заполнить и проверить на ПК, а затем загрузить на устройство. Файл, созданный или обновлённый устройством, можно скачать на ПК и открыть обычными инструментами SQLite — например, для аудита, отчёта или резервного копирования. Перед выгрузкой завершите транзакции, закройте все statement, backup и соединения. Способы передачи описаны в разделе «Передача файлов».

Подготавливая базу на компьютере, завершите WAL и переключите её на rollback journal до закрытия процесса SQLite:

PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA journal_mode=DELETE;
PRAGMA integrity_check;

Загружайте только основной .db. Файлы -wal и -shm не загружаются и не поддерживаются. Если у базы остался незакрытый WAL или используются внешние виртуальные таблицы/расширения либо нестандартное шифрование, сначала создайте на ПК обычную копию через .backup или VACUUM INTO, затем снова проверьте PRAGMA integrity_check.

Открытие и выполнение SQL

sqlite3.open(path [, flags])

Открывает файл и возвращает объект database. При ошибке возвращает nil, code, message. По умолчанию используются OPEN_READWRITE | OPEN_CREATE. Разрешены только OPEN_READONLY, OPEN_READWRITE, OPEN_CREATE, OPEN_FULLMUTEX и OPEN_PRIVATECACHE.

Для каждого соединения принудительно действуют:

  • journal_mode=DELETE;
  • synchronous=FULL;
  • temp_store=FILE;
  • mmap_size=0.

Попытки включить WAL, MEMORY или OFF для журнала, установить synchronous=OFF либо temp_store=MEMORY завершаются ошибкой.

database:exec(sql [, callback [, userdata]])

Выполняет одну или несколько SQL-команд и возвращает код SQLite. Для результирующих строк можно передать callback в стиле LuaSQLite3. Метод execute является алиасом exec.

database:prepare(sql)

Создаёт prepared statement. Основные методы statement:

  • bind(index, value), bind_values(...), bind_names(table);
  • bind_blob(index, string) для бинарной строки;
  • bind_int64(index, decimal_string) для точного 64-битного INTEGER;
  • step(), reset(), finalize();
  • get_value(index), get_values(), get_named_values() и методы получения имён/типов столбцов;
  • rows(), urows(), nrows().

Индексы параметров начинаются с 1, индексы столбцов в get_value() — с 0. Всегда вызывайте finalize() после окончания работы; GC является страховкой, а не основным способом закрытия ресурсов.

database:rows(sql), database:urows(sql), database:nrows(sql)

Возвращают итераторы по строкам: таблица значений, распакованные значения или таблица с именами столбцов соответственно.

Точный INTEGER 64 bit

В Lua RTOS lua_Integer и lua_Number имеют 32 бита. Поэтому SQLite INTEGER в диапазоне Lua возвращается числом, а значение за его пределами — десятичной строкой без потери точности. Для точной записи используйте только строку:

local stmt = assert(db:prepare("INSERT INTO counters(id) VALUES(?1)"))
assert(stmt:bind_int64(1, "9223372036854775807") == sqlite3.OK)
assert(stmt:step() == sqlite3.DONE)
stmt:finalize()

for value in db:urows("SELECT id FROM counters") do
    assert(value == "9223372036854775807")
end

bind_int64 принимает строго десятичное значение от -9223372036854775808 до 9223372036854775807.

Транзакции, конкуренция и журнал

Транзакции задаются обычным SQL. Для записи рекомендуется явно ограничивать объём операции:

assert(db:exec("BEGIN IMMEDIATE") == sqlite3.OK)
local rc = db:exec("UPDATE sensor SET value=value+1")
if rc == sqlite3.OK then
    assert(db:exec("COMMIT") == sqlite3.OK)
else
    db:exec("ROLLBACK")
end

VFS поддерживает блокировки SHARED, RESERVED, PENDING и EXCLUSIVE между соединениями внутри прошивки. При конфликте возвращается sqlite3.BUSY; время ожидания задаётся database:busy_timeout(milliseconds) или callback busy_handler.

Rollback journal <база>-journal создаётся рядом с базой и обычно удаляется после COMMIT/ROLLBACK. synchronous=FULL синхронизирует журнал до записи основного файла, поэтому после сброса питания SQLite восстанавливает старое или полностью зафиксированное состояние. Это не отменяет резервные копии и не защищает от физического износа/повреждения flash.

На LittleFS оставляйте свободное место как минимум под базу, rollback journal размером до изменяемой части базы и временные файлы запроса. Для больших транзакций безопасный ориентир — более двух размеров базы плюс запас файловой системы. При недостатке места операция вернёт sqlite3.FULL.

Callbacks, hooks и backup

Сохранён совместимый API LuaSQLite3:

  • create_function, create_aggregate, create_collation;
  • trace, progress_handler, busy_handler, busy_timeout;
  • update_hook, commit_hook, rollback_hook;
  • sqlite3.backup_init(target, target_name, source, source_name) и методы backup step, remaining, pagecount, finish.

Backup удерживает оба соединения. Пока он не завершён через finish() или GC, database:close() возвращает sqlite3.BUSY. Statements и callbacks также нужно завершать/снимать до закрытия базы.

local source = assert(sqlite3.open("/public/main.db"))
local target = assert(sqlite3.open("/public/main-backup.db"))
local backup = assert(sqlite3.backup_init(target, "main", source, "main"))
assert(backup:step(-1) == sqlite3.DONE)
assert(backup:finish() == sqlite3.OK)
assert(target:close() == sqlite3.OK)
assert(source:close() == sqlite3.OK)

Исключённые возможности

Недоступны open_memory, open_ptr/get_ptr, load_extension, изменение temp_directory, URI, memory flags, shared cache, mmap, WAL, deserialize и loadable extensions. Модуль, его константы и метатаблицы database/statement/ context/backup доступны из ROM и не изменяются из Lua.

Диагностика

print(sqlite3.version(), sqlite3.lversion())
local db = assert(sqlite3.open("/public/example.db"))
for value in db:urows("PRAGMA journal_mode") do print("journal", value) end
for value in db:urows("PRAGMA synchronous") do print("sync", value) end
for value in db:urows("PRAGMA integrity_check") do print("integrity", value) end
print(db:db_filename("main"))
db:close()

В командной оболочке команда ls /public/* показывает основной файл и наличие -journal внутри незавершённой транзакции. Перед выпуском прошивки повторите сценарий на оборудовании после прогрева библиотек, дождитесь отложенной очистки FreeRTOS и сравните как свободную heap, так и heap_caps_get_largest_free_block(); успешная сборка сама по себе не доказывает отсутствие утечек.