1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
import Database from "better-sqlite3";
import path from "path";
let db: Database.Database | null = null;
export function getDb(): Database.Database {
if (!db) {
const dbPath = path.resolve("whois.db");
db = new Database(dbPath);
db.pragma("journal_mode = WAL");
db.pragma("foreign_keys = ON");
initSchema(db);
}
return db;
}
function initSchema(db: Database.Database) {
db.exec(`
CREATE TABLE IF NOT EXISTS bands (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
name_kana TEXT,
formed_at TEXT,
area TEXT,
genre TEXT,
url TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS members (
id TEXT PRIMARY KEY,
band_id TEXT NOT NULL REFERENCES bands(id),
name TEXT NOT NULL,
name_kana TEXT,
role TEXT,
joined_at TEXT,
left_at TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_members_band_id ON members(band_id);
`);
}
|
