87 lines
2.2 KiB
JavaScript
87 lines
2.2 KiB
JavaScript
import sqlite3 from 'sqlite3';
|
|
import { dirname, join } from 'path';
|
|
import { fileURLToPath } from 'url';
|
|
import fs from 'fs';
|
|
|
|
const __dirname = dirname(fileURLToPath(import.meta.url));
|
|
const dbPath = join(__dirname, 'database.sqlite');
|
|
|
|
// Ensure db directory exists
|
|
if (!fs.existsSync(__dirname)) {
|
|
fs.mkdirSync(__dirname, { recursive: true });
|
|
}
|
|
|
|
const db = new sqlite3.Database(dbPath);
|
|
|
|
// Helper to run database queries with Promises
|
|
export const query = {
|
|
run(sql, params = []) {
|
|
return new Promise((resolve, reject) => {
|
|
db.run(sql, params, function (err) {
|
|
if (err) reject(err);
|
|
else resolve({ lastID: this.lastID, changes: this.changes });
|
|
});
|
|
});
|
|
},
|
|
get(sql, params = []) {
|
|
return new Promise((resolve, reject) => {
|
|
db.get(sql, params, (err, row) => {
|
|
if (err) reject(err);
|
|
else resolve(row);
|
|
});
|
|
});
|
|
},
|
|
all(sql, params = []) {
|
|
return new Promise((resolve, reject) => {
|
|
db.all(sql, params, (err, rows) => {
|
|
if (err) reject(err);
|
|
else resolve(rows);
|
|
});
|
|
});
|
|
}
|
|
};
|
|
|
|
// Initialize schema
|
|
export async function initDb() {
|
|
// Enable WAL mode for high concurrency
|
|
await query.run('PRAGMA journal_mode = WAL;');
|
|
|
|
// Users table
|
|
await query.run(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
purchased_slots INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Projects table
|
|
await query.run(`
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
parts TEXT NOT NULL,
|
|
saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(user_id, name)
|
|
)
|
|
`);
|
|
|
|
// Purchases table
|
|
await query.run(`
|
|
CREATE TABLE IF NOT EXISTS purchases (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL,
|
|
amount INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
console.log('Database initialized successfully at:', dbPath);
|
|
}
|