271 lines
8.3 KiB
JavaScript
271 lines
8.3 KiB
JavaScript
import sqlite3 from 'sqlite3';
|
|
import { fileURLToPath } from 'url';
|
|
import { dirname, join } from 'path';
|
|
import dotenv from 'dotenv';
|
|
|
|
dotenv.config();
|
|
|
|
const __filename = fileURLToPath(import.meta.url);
|
|
const __dirname = dirname(__filename);
|
|
|
|
const dbPath = process.env.DATABASE_PATH || join(__dirname, '..', 'database.sqlite');
|
|
|
|
const db = new sqlite3.Database(dbPath, (err) => {
|
|
if (err) {
|
|
console.error('Error opening database:', err.message);
|
|
} else {
|
|
console.log('Connected to SQLite database');
|
|
initDatabase();
|
|
}
|
|
});
|
|
|
|
function initDatabase() {
|
|
db.serialize(() => {
|
|
// Users table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
email TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
theme TEXT DEFAULT 'light',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
`);
|
|
|
|
// Collections table (with nested support)
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
parent_id INTEGER,
|
|
user_id INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (parent_id) REFERENCES collections(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Tags table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS tags (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT UNIQUE NOT NULL,
|
|
color TEXT DEFAULT '#6c757d'
|
|
)
|
|
`);
|
|
|
|
// Models table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS models (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
file_path TEXT NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
file_size INTEGER,
|
|
file_type TEXT,
|
|
preview_image TEXT,
|
|
creator TEXT,
|
|
source_url TEXT,
|
|
notes TEXT,
|
|
is_supported BOOLEAN DEFAULT 0,
|
|
license TEXT DEFAULT 'Unknown',
|
|
user_id INTEGER,
|
|
collection_id INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (collection_id) REFERENCES collections(id)
|
|
)
|
|
`);
|
|
|
|
// Model tags junction table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS model_tags (
|
|
model_id INTEGER,
|
|
tag_id INTEGER,
|
|
PRIMARY KEY (model_id, tag_id),
|
|
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Model files table (for multi-file models)
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS model_files (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
model_id INTEGER,
|
|
file_path TEXT NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
file_size INTEGER,
|
|
file_type TEXT,
|
|
is_primary BOOLEAN DEFAULT 0,
|
|
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Print queue table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS print_queue (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
model_id INTEGER,
|
|
user_id INTEGER,
|
|
priority INTEGER DEFAULT 0,
|
|
status TEXT DEFAULT 'pending',
|
|
notes TEXT,
|
|
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at DATETIME,
|
|
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
)
|
|
`);
|
|
|
|
// Printer settings table
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS printer_settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
printer_type TEXT,
|
|
printer_name TEXT,
|
|
access_token TEXT,
|
|
serial_number TEXT,
|
|
model_name TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
)
|
|
`);
|
|
|
|
// Site credentials table (for storing encrypted login credentials)
|
|
db.run(`
|
|
CREATE TABLE IF NOT EXISTS site_credentials (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
site_name TEXT NOT NULL,
|
|
username TEXT,
|
|
password TEXT,
|
|
api_key TEXT,
|
|
access_token TEXT,
|
|
is_connected BOOLEAN DEFAULT 1,
|
|
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
UNIQUE(user_id, site_name)
|
|
)
|
|
`);
|
|
|
|
// Migration: Add is_primary column to model_files if it doesn't exist
|
|
db.all("PRAGMA table_info(model_files)", [], (err, columns) => {
|
|
if (err) {
|
|
console.error('Error checking model_files schema:', err);
|
|
return;
|
|
}
|
|
|
|
const hasIsPrimary = columns.some(col => col.name === 'is_primary');
|
|
if (!hasIsPrimary) {
|
|
db.run('ALTER TABLE model_files ADD COLUMN is_primary BOOLEAN DEFAULT 0', (err) => {
|
|
if (err) {
|
|
console.error('Error adding is_primary column:', err);
|
|
} else {
|
|
console.log('Added is_primary column to model_files table');
|
|
}
|
|
});
|
|
}
|
|
});
|
|
|
|
// Migration: Add parent_id column to collections if it doesn't exist
|
|
db.all("PRAGMA table_info(collections)", [], (err, columns) => {
|
|
if (err) {
|
|
console.error('Error checking collections schema:', err);
|
|
return;
|
|
}
|
|
|
|
const hasParentId = columns.some(col => col.name === 'parent_id');
|
|
if (!hasParentId) {
|
|
db.run('ALTER TABLE collections ADD COLUMN parent_id INTEGER REFERENCES collections(id) ON DELETE CASCADE', (err) => {
|
|
if (err) {
|
|
console.error('Error adding parent_id column:', err);
|
|
} else {
|
|
console.log('Added parent_id column to collections table');
|
|
}
|
|
});
|
|
}
|
|
});
|
|
|
|
// Migration: Add license column to models if it doesn't exist
|
|
db.all("PRAGMA table_info(models)", [], (err, columns) => {
|
|
if (err) {
|
|
console.error('Error checking models schema:', err);
|
|
return;
|
|
}
|
|
|
|
const hasLicense = columns.some(col => col.name === 'license');
|
|
if (!hasLicense) {
|
|
db.run('ALTER TABLE models ADD COLUMN license TEXT DEFAULT \'Unknown\'', (err) => {
|
|
if (err) {
|
|
console.error('Error adding license column:', err);
|
|
} else {
|
|
console.log('Added license column to models table');
|
|
}
|
|
});
|
|
}
|
|
});
|
|
|
|
// Migration: Add theme column to users if it doesn't exist
|
|
db.all("PRAGMA table_info(users)", [], (err, columns) => {
|
|
if (err) {
|
|
console.error('Error checking users schema:', err);
|
|
return;
|
|
}
|
|
|
|
const hasTheme = columns.some(col => col.name === 'theme');
|
|
if (!hasTheme) {
|
|
db.run('ALTER TABLE users ADD COLUMN theme TEXT DEFAULT \'light\'', (err) => {
|
|
if (err) {
|
|
console.error('Error adding theme column:', err);
|
|
} else {
|
|
console.log('Added theme column to users table');
|
|
}
|
|
});
|
|
}
|
|
});
|
|
|
|
// Migration: Add print specifications columns to print_queue if they don't exist
|
|
db.all("PRAGMA table_info(print_queue)", [], (err, columns) => {
|
|
if (err) {
|
|
console.error('Error checking print_queue schema:', err);
|
|
return;
|
|
}
|
|
|
|
const newColumns = {
|
|
quantity: 'INTEGER DEFAULT 1',
|
|
filament_type: 'TEXT',
|
|
color: 'TEXT',
|
|
print_temp: 'INTEGER',
|
|
bed_temp: 'INTEGER',
|
|
print_speed: 'TEXT DEFAULT \'normal\'',
|
|
support_structure: 'TEXT DEFAULT \'none\'',
|
|
infill_density: 'INTEGER DEFAULT 20',
|
|
layer_height: 'REAL DEFAULT 0.2',
|
|
special_instructions: 'TEXT'
|
|
};
|
|
|
|
Object.entries(newColumns).forEach(([colName, colDef]) => {
|
|
const hasColumn = columns.some(col => col.name === colName);
|
|
if (!hasColumn) {
|
|
db.run(`ALTER TABLE print_queue ADD COLUMN ${colName} ${colDef}`, (err) => {
|
|
if (err) {
|
|
console.error(`Error adding ${colName} column:`, err);
|
|
} else {
|
|
console.log(`Added ${colName} column to print_queue table`);
|
|
}
|
|
});
|
|
}
|
|
});
|
|
});
|
|
|
|
console.log('Database tables initialized');
|
|
});
|
|
}
|
|
|
|
export default db;
|