b49d703e3c
All checks were successful
Gitea Actions Demo / Explore-Gitea-Actions (push) Successful in 1m22s
103 lines
2.9 KiB
TypeScript
103 lines
2.9 KiB
TypeScript
import Database from 'better-sqlite3';
|
|
import fs from 'node:fs';
|
|
import path from 'node:path';
|
|
|
|
const dataDir = path.resolve(process.cwd(), '.data');
|
|
fs.mkdirSync(dataDir, { recursive: true });
|
|
|
|
const dbPath = path.join(dataDir, 'scriptflow.db');
|
|
export const db = new Database(dbPath);
|
|
|
|
db.pragma('journal_mode = WAL');
|
|
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
password_salt TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS drafts (
|
|
user_id INTEGER PRIMARY KEY,
|
|
payload TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
`);
|
|
|
|
export type UserRow = {
|
|
id: number;
|
|
username: string;
|
|
password_hash: string;
|
|
password_salt: string;
|
|
};
|
|
|
|
export function findUserByUsername(username: string) {
|
|
return db.prepare('SELECT * FROM users WHERE username = ?').get(username) as UserRow | undefined;
|
|
}
|
|
|
|
export function findUserById(id: number) {
|
|
return db.prepare('SELECT * FROM users WHERE id = ?').get(id) as UserRow | undefined;
|
|
}
|
|
|
|
export function createUser(username: string, passwordHash: string, passwordSalt: string) {
|
|
const result = db
|
|
.prepare('INSERT INTO users (username, password_hash, password_salt) VALUES (?, ?, ?)')
|
|
.run(username, passwordHash, passwordSalt);
|
|
return findUserById(Number(result.lastInsertRowid));
|
|
}
|
|
|
|
export function createSession(token: string, userId: number) {
|
|
db.prepare('INSERT INTO sessions (token, user_id) VALUES (?, ?)').run(token, userId);
|
|
}
|
|
|
|
export function findUserBySessionToken(token: string) {
|
|
return db
|
|
.prepare(`
|
|
SELECT users.*
|
|
FROM sessions
|
|
INNER JOIN users ON users.id = sessions.user_id
|
|
WHERE sessions.token = ?
|
|
`)
|
|
.get(token) as UserRow | undefined;
|
|
}
|
|
|
|
export function deleteSession(token: string) {
|
|
db.prepare('DELETE FROM sessions WHERE token = ?').run(token);
|
|
}
|
|
|
|
export function getDraftForUser(userId: number) {
|
|
const row = db.prepare('SELECT payload, updated_at FROM drafts WHERE user_id = ?').get(userId) as { payload: string; updated_at: string } | undefined;
|
|
if (!row) return null;
|
|
return {
|
|
payload: JSON.parse(row.payload),
|
|
updatedAt: row.updated_at,
|
|
};
|
|
}
|
|
|
|
export function saveDraftForUser(userId: number, payload: unknown) {
|
|
const serialized = JSON.stringify(payload);
|
|
const updatedAt = new Date().toISOString();
|
|
db
|
|
.prepare(`
|
|
INSERT INTO drafts (user_id, payload, updated_at)
|
|
VALUES (?, ?, ?)
|
|
ON CONFLICT(user_id) DO UPDATE SET payload = excluded.payload, updated_at = excluded.updated_at
|
|
`)
|
|
.run(userId, serialized, updatedAt);
|
|
|
|
return {
|
|
payload,
|
|
updatedAt,
|
|
};
|
|
}
|