| import Database from 'better-sqlite3'; |
| import path from 'path'; |
| import fs from 'fs'; |
| import bcrypt from 'bcryptjs'; |
|
|
| const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '../data/gallery.db'); |
|
|
| const dbDir = path.dirname(dbPath); |
| if (!fs.existsSync(dbDir)) { |
| fs.mkdirSync(dbDir, { recursive: true }); |
| } |
|
|
| export const db = new Database(dbPath); |
| db.pragma('journal_mode = WAL'); |
|
|
| export function initDatabase() { |
| db.exec(` |
| CREATE TABLE IF NOT EXISTS categories ( |
| cid INTEGER PRIMARY KEY AUTOINCREMENT, |
| owner_id INTEGER DEFAULT 0, |
| name TEXT NOT NULL, |
| description TEXT DEFAULT '', |
| pos INTEGER DEFAULT 0, |
| parent INTEGER DEFAULT 0, |
| thumb INTEGER DEFAULT 0 |
| ); |
| |
| CREATE TABLE IF NOT EXISTS albums ( |
| aid INTEGER PRIMARY KEY AUTOINCREMENT, |
| title TEXT NOT NULL, |
| description TEXT DEFAULT '', |
| visibility INTEGER DEFAULT 0, |
| uploads TEXT DEFAULT 'NO', |
| comments TEXT DEFAULT 'YES', |
| votes TEXT DEFAULT 'YES', |
| pos INTEGER DEFAULT 0, |
| category INTEGER DEFAULT 0, |
| owner INTEGER DEFAULT 1, |
| thumb INTEGER DEFAULT 0, |
| keyword TEXT DEFAULT '', |
| alb_hits INTEGER DEFAULT 0 |
| ); |
| |
| CREATE TABLE IF NOT EXISTS pictures ( |
| pid INTEGER PRIMARY KEY AUTOINCREMENT, |
| aid INTEGER NOT NULL, |
| filepath TEXT NOT NULL, |
| filename TEXT NOT NULL, |
| filesize INTEGER DEFAULT 0, |
| pwidth INTEGER DEFAULT 0, |
| pheight INTEGER DEFAULT 0, |
| hits INTEGER DEFAULT 0, |
| mtime DATETIME DEFAULT CURRENT_TIMESTAMP, |
| ctime INTEGER DEFAULT 0, |
| owner_id INTEGER DEFAULT 1, |
| pic_rating INTEGER DEFAULT 0, |
| votes INTEGER DEFAULT 0, |
| title TEXT DEFAULT '', |
| caption TEXT DEFAULT '', |
| keywords TEXT DEFAULT '', |
| approved TEXT DEFAULT 'YES', |
| position INTEGER DEFAULT 0 |
| ); |
| |
| CREATE TABLE IF NOT EXISTS comments ( |
| msg_id INTEGER PRIMARY KEY AUTOINCREMENT, |
| pid INTEGER NOT NULL, |
| msg_author TEXT NOT NULL, |
| msg_body TEXT NOT NULL, |
| msg_date DATETIME DEFAULT CURRENT_TIMESTAMP, |
| author_id INTEGER DEFAULT 0 |
| ); |
| |
| CREATE TABLE IF NOT EXISTS usergroups ( |
| group_id INTEGER PRIMARY KEY AUTOINCREMENT, |
| group_name TEXT NOT NULL, |
| has_admin_access INTEGER DEFAULT 0, |
| can_rate_pictures INTEGER DEFAULT 1, |
| can_send_ecards INTEGER DEFAULT 1, |
| can_post_comments INTEGER DEFAULT 1, |
| can_upload_pictures INTEGER DEFAULT 1, |
| can_create_albums INTEGER DEFAULT 1 |
| ); |
| |
| CREATE TABLE IF NOT EXISTS users ( |
| user_id INTEGER PRIMARY KEY AUTOINCREMENT, |
| user_group INTEGER DEFAULT 2, |
| user_active TEXT DEFAULT 'YES', |
| user_name TEXT NOT NULL UNIQUE, |
| user_password TEXT NOT NULL, |
| user_email TEXT NOT NULL, |
| user_regdate DATETIME DEFAULT CURRENT_TIMESTAMP |
| ); |
| |
| CREATE TABLE IF NOT EXISTS config ( |
| name TEXT PRIMARY KEY, |
| value TEXT NOT NULL |
| ); |
| `); |
|
|
| const groupCount = (db.prepare('SELECT COUNT(*) as count FROM usergroups').get() as any).count; |
| if (groupCount === 0) { |
| db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(1, 'Administrators', 1); |
| db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(2, 'Registered', 0); |
| db.prepare('INSERT INTO usergroups (group_id, group_name, has_admin_access) VALUES (?, ?, ?)').run(3, 'Guests', 0); |
| } |
|
|
| const userCount = (db.prepare('SELECT COUNT(*) as count FROM users').get() as any).count; |
| if (userCount === 0) { |
| const adminPasswordHash = bcrypt.hashSync('admin', 10); |
| db.prepare('INSERT INTO users (user_id, user_group, user_name, user_password, user_email) VALUES (?, ?, ?, ?, ?)').run( |
| 1, |
| 1, |
| 'admin', |
| adminPasswordHash, |
| 'admin@example.com' |
| ); |
| } |
|
|
| const configCount = (db.prepare('SELECT COUNT(*) as count FROM config').get() as any).count; |
| if (configCount === 0) { |
| const defaultConfig = [ |
| ['gallery_name', 'Coppermine Photo Gallery'], |
| ['gallery_description', 'Your online photo album'], |
| ['theme', 'classic'], |
| ['thumb_pfx', 'thumb_'], |
| ['normal_pfx', 'normal_'], |
| ['max_upl_width', '2048'], |
| ['max_upl_height', '2048'], |
| ['max_upl_size', '10485760'], |
| ['thumb_width', '200'], |
| ['albums_per_page', '12'], |
| ['pics_per_page', '12'] |
| ]; |
| const stmt = db.prepare('INSERT INTO config (name, value) VALUES (?, ?)'); |
| for (const [name, val] of defaultConfig) { |
| stmt.run(name, val); |
| } |
| } |
|
|
| const catCount = (db.prepare('SELECT COUNT(*) as count FROM categories').get() as any).count; |
| if (catCount === 0) { |
| db.prepare('INSERT INTO categories (cid, name, description) VALUES (?, ?, ?)').run(1, 'General Photography', 'Default category for general photos'); |
| } |
|
|
| const albCount = (db.prepare('SELECT COUNT(*) as count FROM albums').get() as any).count; |
| if (albCount === 0) { |
| db.prepare('INSERT INTO albums (aid, title, description, category, uploads) VALUES (?, ?, ?, ?, ?)').run(1, 'Sample Album', 'Welcome to your photo gallery!', 1, 'YES'); |
| } |
| } |
|
|
| export function getConfig(): Record<string, string> { |
| const rows = db.prepare('SELECT name, value FROM config').all() as { name: string; value: string }[]; |
| const config: Record<string, string> = {}; |
| for (const row of rows) { |
| config[row.name] = row.value; |
| } |
| return config; |
| } |
|
|
