;; SQLite3 平台元数据库访问层（设计文档 §5）
;; 连接保存在 persistent_term；本应用单进程内串行访问，无需连接池。
(defmodule lamina_db
(export all))
(defun conn-key () 'lamina_db_conn)
(defun to-int (v)
(if (is_binary v) (binary_to_integer v) (list_to_integer v)))
;;---- 打开与迁移 ----
(defun open (db-path)
(filelib:ensure_dir (filename:join (filename:dirname db-path) "x"))
(case (esqlite3:open db-path)
(`#(ok ,conn)
(persistent_term:put (conn-key) conn)
(exec "PRAGMA journal_mode=WAL;")
(ensure-schema conn))
(other other)))
(defun close ()
(case (persistent_term:get (conn-key) 'undefined)
('undefined 'ok)
(conn
(progn (esqlite3:close conn)
(persistent_term:erase (conn-key))
'ok))))
;;---- 查询 ----
(defun conn ()
(persistent_term:get (conn-key)))
(defun q (sql) (esqlite3:q (conn) sql))
(defun q (sql params) (esqlite3:q (conn) sql params))
(defun exec (sql) (esqlite3:exec (conn) sql))
(defun last-rowid () (esqlite3:last_insert_rowid (conn)))
(defun changes () (esqlite3:changes (conn)))
;;---- schema 迁移 ----
(defun ensure-schema (conn)
(esqlite3:exec conn "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);")
(let ((rows (esqlite3:q conn "SELECT value FROM meta WHERE key='schema_version'")))
(let ((current (case rows
('() 0)
(`((,v)) (to-int v))
(_ 0))))
(run-migrations conn current))))
(defun run-migrations (conn current)
(let ((target (fold-migrations conn current (migrations))))
`#(ok ,target)))
(defun fold-migrations (conn current pending)
(case pending
('() current)
(`((,version . ,statements) . ,rest)
(if (=< version current)
(fold-migrations conn current rest)
(let ((final (apply-migration conn version statements)))
(fold-migrations conn final rest))))))
(defun apply-migration (conn version statements)
(lists:foreach
(lambda (ddl) (esqlite3:exec conn ddl))
statements)
(esqlite3:exec
conn
(lists:flatten
(io_lib:format
"INSERT OR REPLACE INTO meta(key,value) VALUES('schema_version','~p');"
(list version))))
version)
;; 迁移清单：每个元素 (版本号 . DDL语句列表)。只追加，不修改已发布版本。
(defun migrations ()
(list
(cons 1 (v1-ddl))
(cons 2 (v2-ddl))))
(defun v2-ddl ()
(list "ALTER TABLE repositories ADD COLUMN repo_status TEXT NOT NULL DEFAULT 'up';"))
(defun v1-ddl ()
(list
"CREATE TABLE IF NOT EXISTS admin_user (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL);"
"CREATE TABLE IF NOT EXISTS site_settings (
key TEXT PRIMARY KEY, value TEXT NOT NULL);"
"CREATE TABLE IF NOT EXISTS organizations (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
description TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL);"
"CREATE TABLE IF NOT EXISTS repositories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
organization_id INTEGER REFERENCES organizations(id),
display_name TEXT NOT NULL DEFAULT '',
description TEXT NOT NULL DEFAULT '',
source TEXT NOT NULL CHECK(source IN ('local','remote')),
remote_url TEXT,
sync_interval_minutes INTEGER,
setup_username TEXT NOT NULL,
setup_password_enc BLOB NOT NULL,
git_enabled INTEGER NOT NULL DEFAULT 0,
git_url TEXT,
git_username TEXT,
git_token_enc BLOB,
git_auto_push INTEGER NOT NULL DEFAULT 0,
fossil_path TEXT NOT NULL UNIQUE,
last_timeline_at TEXT,
last_synced_at TEXT,
last_sync_status TEXT,
last_pushed_at TEXT,
last_push_status TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
UNIQUE(name, organization_id));"
"CREATE INDEX IF NOT EXISTS idx_repos_org ON repositories(organization_id);"
"CREATE TABLE IF NOT EXISTS sync_log (
id INTEGER PRIMARY KEY,
repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
started_at TEXT NOT NULL,
finished_at TEXT,
status TEXT NOT NULL,
message TEXT DEFAULT '');"
"CREATE TABLE IF NOT EXISTS push_log (
id INTEGER PRIMARY KEY,
repository_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
started_at TEXT NOT NULL,
finished_at TEXT,
status TEXT NOT NULL,
message TEXT DEFAULT '');"
"CREATE INDEX IF NOT EXISTS idx_sync_repo ON sync_log(repository_id, started_at DESC);"
"CREATE INDEX IF NOT EXISTS idx_push_repo ON push_log(repository_id, started_at DESC);"))