60 lines
1.4 KiB
JavaScript
Executable File
60 lines
1.4 KiB
JavaScript
Executable File
const Database = require("better-sqlite3")
|
|
|
|
const DB = Database(process.env.DB || "./db.sqlite3")
|
|
|
|
const migrations = [
|
|
`
|
|
CREATE TABLE thing_log (
|
|
id INTEGER PRIMARY KEY,
|
|
timestamp INTEGER NOT NULL,
|
|
type TEXT NOT NULL,
|
|
thing TEXT NOT NULL
|
|
);
|
|
`,
|
|
`
|
|
CREATE TABLE inventory (
|
|
thing TEXT NOT NULL UNIQUE,
|
|
obtained_at INTEGER NOT NULL,
|
|
quantity REAL NOT NULL
|
|
);
|
|
`
|
|
]
|
|
|
|
const executeMigration = DB.transaction((i) => {
|
|
const migration = migrations[i]
|
|
DB.exec(migration)
|
|
DB.pragma(`user_version = ${i + 1}`)
|
|
console.log(`Migrated to schema ${i + 1}`)
|
|
})
|
|
|
|
const schemaVersion = DB.pragma("user_version", { simple: true })
|
|
if (schemaVersion < migrations.length) {
|
|
console.log(`Migrating DB - schema ${schemaVersion} used, schema ${migrations.length} available`)
|
|
for (let i = schemaVersion; i < migrations.length; i++) {
|
|
executeMigration(i)
|
|
}
|
|
}
|
|
|
|
DB.pragma("foreign_keys = 1")
|
|
|
|
const preparedStatements = new Map()
|
|
|
|
const SQL = (strings, ...params) => {
|
|
const sql = strings.join("?")
|
|
let stmt
|
|
const cachedValue = preparedStatements.get(sql)
|
|
if (!cachedValue) {
|
|
stmt = DB.prepare(sql)
|
|
preparedStatements.set(sql, stmt)
|
|
} else {
|
|
stmt = cachedValue
|
|
}
|
|
return {
|
|
get: () => stmt.get.apply(stmt, params),
|
|
run: () => stmt.run.apply(stmt, params),
|
|
all: () => stmt.all.apply(stmt, params),
|
|
statement: stmt
|
|
}
|
|
}
|
|
|
|
module.exports = { DB, SQL } |