| 1 | |
| 2 | import Database from "better-sqlite3"; |
| 3 | |
| 4 | export const DB_PATH = |
| 5 | process.env.DOCKET_DB_PATH || "/opt/docket/data/docket.db"; |
| 6 | |
| 7 | let _db: Database.Database | null = null; |
| 8 | |
| 9 | |
| 10 | function db(): Database.Database { |
| 11 | if (!_db) { |
| 12 | _db = new Database(DB_PATH, { |
| 13 | readonly: false, |
| 14 | fileMustExist: false, |
| 15 | }); |
| 16 | try { |
| 17 | _db.pragma("journal_mode = WAL"); |
| 18 | _db.pragma("busy_timeout = 4000"); |
| 19 | } catch { |
| 20 | } |
| 21 | } |
| 22 | return _db; |
| 23 | } |
| 24 | |
| 25 | |
| 26 | export function safeAll<T>( |
| 27 | sql: string, |
| 28 | params: Record<string, unknown> = {}, |
| 29 | map?: (row: Record<string, unknown>) => T |
| 30 | ): T[] { |
| 31 | try { |
| 32 | const stmt = db().prepare(sql); |
| 33 | const rows = stmt.all(params) as Record<string, unknown>[]; |
| 34 | return map ? rows.map(map) : (rows as T[]); |
| 35 | } catch { |
| 36 | return []; |
| 37 | } |
| 38 | } |
| 39 | |
| 40 | export function safeGet<T>( |
| 41 | sql: string, |
| 42 | params: Record<string, unknown> = {} |
| 43 | ): T | null { |
| 44 | try { |
| 45 | const stmt = db().prepare(sql); |
| 46 | return (stmt.get(params) as T) ?? null; |
| 47 | } catch { |
| 48 | return null; |
| 49 | } |
| 50 | } |
| 51 | |
| 52 | |
| 53 | export function safeCount(table: string): number { |
| 54 | if (!/^[a-z_]+$/.test(table)) return 0; |
| 55 | try { |
| 56 | const row = db() |
| 57 | .prepare(`SELECT COUNT(*) AS c FROM "${table}"`) |
| 58 | .get() as { c: number } | undefined; |
| 59 | return row?.c ?? 0; |
| 60 | } catch { |
| 61 | return 0; |
| 62 | } |
| 63 | } |
| 64 | |
| 65 | |
| 66 | export const MAX_LIMIT = 200; |
| 67 | export const DEFAULT_LIMIT = 50; |
| 68 | |
| 69 | export type Paged = { limit: number; offset: number }; |
| 70 | |
| 71 | export function parsePaging(sp: URLSearchParams): Paged { |
| 72 | const clamp = (n: number, hi: number) => |
| 73 | Number.isFinite(n) && n > 0 ? Math.min(Math.floor(n), hi) : DEFAULT_LIMIT; |
| 74 | const limit = clamp(parseInt(sp.get("limit") ?? "", 10), MAX_LIMIT); |
| 75 | const offsetRaw = parseInt(sp.get("offset") ?? "", 10); |
| 76 | const offset = |
| 77 | Number.isFinite(offsetRaw) && offsetRaw > 0 ? Math.floor(offsetRaw) : 0; |
| 78 | return { limit, offset }; |
| 79 | } |
| 80 | |
| 81 | export function paramArray(sp: URLSearchParams, key: string): string[] { |
| 82 | return sp.getAll(key).map((v) => v.toLowerCase()); |
| 83 | } |
| 84 | |
| 85 | |
| 86 | export type ItemList<T> = { items: T[]; total: number; limit: number; offset: number }; |
| 87 | |
| 88 | export function emptyList(p: Paged): ItemList<never> { |
| 89 | return { items: [], total: 0, limit: p.limit, offset: p.offset }; |
| 90 | } |