Infrastructure as a Service / Nemoptera

Artifact 67e0b98510a9188f032d0504481fb3bc08f86ec9777bc82371176b5a600c7458:

• File src/db/nemoptera_db.lfe — part of check-in [14d905c889] at 2026-11-08 15:25:21 on branch trunk — refactor(core): 重命名项目为 Nemoptera (user: pluto size: 3702)

(defmodule nemoptera_db
  (export all))

(defun conn-key () 'nemoptera_db_conn)

(defun open ()
  (open (nemoptera_config:db-path)))

(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)
     (esqlite3:exec conn "PRAGMA journal_mode=WAL;")
     (esqlite3:exec conn "PRAGMA foreign_keys=ON;")
     (esqlite3:exec conn "PRAGMA busy_timeout=5000;")
     (ensure-schema conn))
    (other other)))

(defun conn ()
  (persistent_term:get (conn-key)))

(defun q (sql)
  (q sql '()))

(defun q (sql params)
  (catch (esqlite3:q (conn) sql params)))

(defun as-tuple (row)
  (if (is_list row) (list_to_tuple row) row))

(defun exec (sql)
  (catch (esqlite3:exec (conn) sql)))

(defun insert-id ()
  (esqlite3:last_insert_rowid (conn)))

(defun ensure-schema (conn)
  (esqlite3:exec conn "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);")
  (apply-migrations conn (schema-version conn) (migrations))
  `#(ok ,conn))

(defun schema-version (conn)
  (case (esqlite3:q conn "SELECT value FROM meta WHERE key='schema_version';")
    ('() 0)
    (`((,value)) (nemoptera_text:to-int value 0))
    (_ 0)))

(defun apply-migrations (conn current pending)
  (case pending
    ('() 'ok)
    (`(,(tuple version statements) . ,rest)
     (if (> version current)
         (run-migration conn version statements)
         'ok)
     (apply-migrations conn version rest))))

(defun run-migration (conn version statements)
  (lists:foreach (lambda (ddl) (esqlite3:exec conn ddl)) statements)
  (esqlite3:q conn "INSERT OR REPLACE INTO meta(key,value) VALUES('schema_version',?);"
             (list (integer_to_list version))))

(defun migrations ()
  (list (tuple 1 (v1))))

(defun v1 ()
  (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),
      description TEXT NOT NULL DEFAULT '',
      source TEXT NOT NULL,
      remote_url TEXT,
      sync_interval_minutes INTEGER NOT NULL DEFAULT 0,
      setup_username TEXT NOT NULL,
      setup_password_enc TEXT NOT NULL,
      git_enabled INTEGER NOT NULL DEFAULT 0,
      git_url TEXT,
      git_username TEXT,
      git_token_enc TEXT,
      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);"
   "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 NOT NULL 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 NOT NULL DEFAULT '');"))