IaC / SwiftFlow

Artifact ab51cc4a625fe30867601d969c43fcb93a6a04325d6121c91b15378537cb60d9:

• File backend/src/database.gleam — part of check-in [dfa747c1ad] at 2026-10-23 06:08:28 on branch trunk — Web 管理控制台

  - 用 Vite 和 React 做完整的 Web 端：登录、按权限显示菜单、切换当前作业仓库 - 覆盖来肯同步、仓库、库位规划、商品、销售订单、采购入库与批次码、采购订单、库存、用户授权和审计日志 - 开发时由 Vite 把 /api 代理到本机后端 - SQLite 写入和页面读取冲突时自动重试，避免请求进程因数据库忙碌直接崩溃 (user: pluto size: 6308)

import config.{type DbDriver, Postgres, Sqlite}
import exception
import gleam/dynamic/decode
import gleam/erlang/process
import gleam/int
import gleam/list
import gleam/result
import gleam/string
import pog
import simplifile
import sqlight

pub type DbError {
  DbError(message: String)
}

pub type Database {
  SqliteFile(path: String)
  PostgresPool(conn: pog.Connection)
}

pub type Conn {
  SqliteConn(sqlight.Connection)
  PostgresConn(pog.Connection)
}

pub type Param {
  IntParam(Int)
  FloatParam(Float)
  TextParam(String)
}

pub fn start(cfg: config.Config) -> Result(Database, DbError) {
  case cfg.db_driver {
    Sqlite -> {
      ensure_parent(cfg.database_path)
      use conn <- result.try(open_sqlite(cfg.database_path))
      let _ = sqlight.close(conn)
      Ok(SqliteFile(cfg.database_path))
    }
    Postgres ->
      case cfg.database_url {
        "" -> Error(DbError("DATABASE_URL 未配置"))
        url -> {
          let name = process.new_name("swiftflow_postgres")
          case pog.url_config(name, url) {
            Error(_) -> Error(DbError("DATABASE_URL 无法解析"))
            Ok(pg_config) ->
              case pog.start(pg_config) {
                Ok(started) -> Ok(PostgresPool(started.data))
                Error(reason) ->
                  Error(DbError(
                    "PostgreSQL 连接池启动失败: " <> string.inspect(reason),
                  ))
              }
          }
        }
      }
  }
}

pub fn transaction(
  db: Database,
  callback: fn(Conn) -> Result(a, DbError),
) -> Result(a, DbError) {
  case db {
    SqliteFile(path) -> sqlite_with_retry(path, callback, 6)
    PostgresPool(pool) -> postgres_transaction(pool, callback)
  }
}

/// esqlite 在 SQLITE_BUSY 时返回原子 '$busy'，查询循环没有这个分支，进程会直接崩溃。
/// 轮询写入和页面读取撞上时重试，而不是把整个请求打成 500。
fn sqlite_with_retry(
  path: String,
  callback: fn(Conn) -> Result(a, DbError),
  left: Int,
) -> Result(a, DbError) {
  case exception.rescue(fn() { sqlite_transaction(path, callback) }) {
    Ok(result) -> result
    Error(err) -> {
      let detail = string.inspect(err)
      case left > 0 && string.contains(string.lowercase(detail), "busy") {
        True -> {
          process.sleep(80)
          sqlite_with_retry(path, callback, left - 1)
        }
        False -> Error(DbError("数据库操作失败: " <> detail))
      }
    }
  }
}

pub fn query_on(
  conn: Conn,
  sql: String,
  params: List(Param),
  decoder: decode.Decoder(row),
) -> Result(List(row), DbError) {
  case conn {
    SqliteConn(connection) ->
      case
        sqlight.query(
          sql,
          on: connection,
          with: list.map(params, to_sqlight),
          expecting: decoder,
        )
      {
        Ok(rows) -> Ok(rows)
        Error(err) -> Error(DbError(sqlight_message(err)))
      }
    PostgresConn(connection) -> {
      let query =
        list.fold(params, pog.query(postgres_placeholders(sql)), fn(query, param) {
          pog.parameter(query, to_pog(param))
        })
        |> pog.returning(decoder)
      case pog.execute(query, connection) {
        Ok(returned) -> Ok(returned.rows)
        Error(err) -> Error(DbError(string.inspect(err)))
      }
    }
  }
}

pub fn exec_on(
  conn: Conn,
  sql: String,
  params: List(Param),
) -> Result(Nil, DbError) {
  use _ <- result.try(query_on(conn, sql, params, decode.success(Nil)))
  Ok(Nil)
}

pub fn postgres_placeholders(sql: String) -> String {
  adapt(sql, Postgres)
}

pub fn adapt(sql: String, driver: DbDriver) -> String {
  case driver {
    Sqlite -> sql
    Postgres -> {
      string.split(sql, "?")
      |> list.index_map(fn(part, index) {
        case index {
          0 -> part
          n -> "$" <> int.to_string(n) <> part
        }
      })
      |> string.join("")
    }
  }
}

fn sqlite_transaction(
  path: String,
  callback: fn(Conn) -> Result(a, DbError),
) -> Result(a, DbError) {
  use connection <- result.try(open_sqlite(path))
  use <- exception.defer(fn() {
    let _ = sqlight.close(connection)
    Nil
  })
  let conn = SqliteConn(connection)
  let result = case exec_on(conn, "BEGIN", []) {
    Error(err) -> Error(err)
    Ok(_) ->
      case callback(conn) {
        Ok(value) ->
          case exec_on(conn, "COMMIT", []) {
            Ok(_) -> Ok(value)
            Error(err) -> {
              let _ = exec_on(conn, "ROLLBACK", [])
              Error(err)
            }
          }
        Error(err) -> {
          let _ = exec_on(conn, "ROLLBACK", [])
          Error(err)
        }
      }
  }
  result
}

fn postgres_transaction(
  pool: pog.Connection,
  callback: fn(Conn) -> Result(a, DbError),
) -> Result(a, DbError) {
  case pog.transaction(pool, fn(connection) { callback(PostgresConn(connection)) }) {
    Ok(value) -> Ok(value)
    Error(pog.TransactionQueryError(err)) -> Error(DbError(string.inspect(err)))
    Error(pog.TransactionRolledBack(err)) -> Error(err)
  }
}

fn open_sqlite(path: String) -> Result(sqlight.Connection, DbError) {
  case sqlight.open(path) {
    Error(err) -> Error(DbError(sqlight_message(err)))
    Ok(connection) -> {
      let _ = sqlight.exec("PRAGMA foreign_keys = ON", on: connection)
      let _ = sqlight.exec("PRAGMA busy_timeout = 5000", on: connection)
      let _ = sqlight.exec("PRAGMA journal_mode = WAL", on: connection)
      Ok(connection)
    }
  }
}

fn ensure_parent(path: String) -> Nil {
  case string.split(path, "/") {
    [] | [_] -> Nil
    parts -> {
      let dir =
        parts
        |> list.take(list.length(parts) - 1)
        |> string.join("/")
      case dir {
        "" -> Nil
        _ -> {
          let _ = simplifile.create_directory_all(dir)
          Nil
        }
      }
    }
  }
}

fn to_sqlight(param: Param) -> sqlight.Value {
  case param {
    IntParam(value) -> sqlight.int(value)
    FloatParam(value) -> sqlight.float(value)
    TextParam(value) -> sqlight.text(value)
  }
}

fn to_pog(param: Param) -> pog.Value {
  case param {
    IntParam(value) -> pog.int(value)
    FloatParam(value) -> pog.float(value)
    TextParam(value) -> pog.text(value)
  }
}

fn sqlight_message(error: sqlight.Error) -> String {
  case error {
    sqlight.SqlightError(_, message, _) -> message
  }
}