backend/utils/database.js - master - varsl/HTML-CSS

backend/utils/database.js
275 строк8 KB
varsl
upload files
21 дек 2025, 23:55
21 дек 2025, 23:55861d17d
const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');
const { dbPath } = require('../config');

// Ensure database directory exists
const dbDir = path.dirname(dbPath);
if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, { recursive: true });
}

// Create database connection
const db = new sqlite3.Database(path.resolve(dbPath), (err) => {
    if (err) {
        console.error('❌ Error opening database:', err);
    } else {
        console.log('✅ Connected to SQLite database');

        // Enable foreign keys
        db.run('PRAGMA foreign_keys = ON', (err) => {
            if (err) {
                console.error('❌ Error enabling foreign keys:', err);
            } else {
                console.log('✅ Foreign keys enabled');
            }
        });

        // Enable WAL mode for better concurrency
        db.run('PRAGMA journal_mode = WAL', (err) => {
            if (err) {
                console.error('❌ Error enabling WAL mode:', err);
            } else {
                console.log('✅ WAL mode enabled');
            }
        });

        // Set busy timeout
        db.run('PRAGMA busy_timeout = 5000', (err) => {
            if (err) {
                console.error('❌ Error setting busy timeout:', err);
            }
        });
    }
});

// Helper function to run SQL with parameters
const run = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.run(sql, params, function(err) {
            if (err) {
                console.error('❌ Database run error:', err);
                console.error('SQL:', sql);
                console.error('Params:', params);
                reject(err);
            } else {
                resolve({
                    id: this.lastID,
                    changes: this.changes
                });
            }
        });
    });
};

// Helper function to get single row
const get = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.get(sql, params, (err, row) => {
            if (err) {
                console.error('❌ Database get error:', err);
                console.error('SQL:', sql);
                console.error('Params:', params);
                reject(err);
            } else {
                resolve(row);
            }
        });
    });
};

// Helper function to get all rows
const all = (sql, params = []) => {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('❌ Database all error:', err);
                console.error('SQL:', sql);
                console.error('Params:', params);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
};

// Helper function to execute multiple statements
const exec = (sql) => {
    return new Promise((resolve, reject) => {
        db.exec(sql, (err) => {
            if (err) {
                console.error('❌ Database exec error:', err);
                console.error('SQL:', sql);
                reject(err);
            } else {
                resolve();
            }
        });
    });
};

// Helper function to begin transaction
const beginTransaction = () => {
    return run('BEGIN TRANSACTION');
};

// Helper function to commit transaction
const commit = () => {
    return run('COMMIT');
};

// Helper function to rollback transaction
const rollback = () => {
    return run('ROLLBACK');
};

// Helper function to check if table exists
const tableExists = async (tableName) => {
    try {
        const result = await get(
            "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
            [tableName]
        );
        return !!result;
    } catch (error) {
        console.error('❌ Error checking if table exists:', error);
        return false;
    }
};

// Helper function to get table schema
const getTableSchema = async (tableName) => {
    try {
        return await all(`PRAGMA table_info(${tableName})`);
    } catch (error) {
        console.error('❌ Error getting table schema:', error);
        return [];
    }
};

// Helper function to backup database
const backup = (backupPath) => {
    return new Promise((resolve, reject) => {
        const backupDb = new sqlite3.Database(backupPath);

        db.backup(backupDb, {
            progress: (_, remaining, total) => {
                const percent = Math.round((total - remaining) / total * 100);
                if (percent % 10 === 0) {
                    console.log(`Backup progress: ${percent}%`);
                }
            }
        }, (err) => {
            backupDb.close();
            if (err) {
                console.error('❌ Backup error:', err);
                reject(err);
            } else {
                console.log('✅ Backup completed successfully');
                resolve();
            }
        });
    });
};

// Helper function to close database
const close = () => {
    return new Promise((resolve, reject) => {
        db.close((err) => {
            if (err) {
                console.error('❌ Error closing database:', err);
                reject(err);
            } else {
                console.log('✅ Database connection closed');
                resolve();
            }
        });
    });
};

// Initialize database schema
const initializeDatabase = async () => {
    try {
        console.log('🔄 Initializing database...');

        // Read schema file
        const schemaPath = path.join(__dirname, '..', 'database', 'schema.sql');
        if (!fs.existsSync(schemaPath)) {
            throw new Error(`Schema file not found: ${schemaPath}`);
        }

        const schema = fs.readFileSync(schemaPath, 'utf8');

        // Execute schema
        await exec(schema);
        console.log('✅ Database schema created');

        // Read init data file
        const initPath = path.join(__dirname, '..', 'database', 'init.sql');
        if (fs.existsSync(initPath)) {
            const initData = fs.readFileSync(initPath, 'utf8');
            await exec(initData);
            console.log('✅ Initial data inserted');
        }

        console.log('✅ Database initialized successfully');
        return true;
    } catch (error) {
        console.error('❌ Database initialization error:', error);
        throw error;
    }
};

// Health check
const healthCheck = async () => {
    try {
        await get('SELECT 1 as health');
        return { healthy: true, message: 'Database is connected' };
    } catch (error) {
        return { healthy: false, message: `Database error: ${error.message}` };
    }
};

// Get database statistics
const getStats = async () => {
    try {
        const tables = await all(
            "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"
        );

        const stats = {};
        for (const table of tables) {
            const countResult = await get(`SELECT COUNT(*) as count FROM ${table.name}`);
            stats[table.name] = countResult.count;
        }

        return {
            success: true,
            tables: tables.map(t => t.name),
            rowCounts: stats,
            totalRows: Object.values(stats).reduce((sum, count) => sum + count, 0)
        };
    } catch (error) {
        console.error('❌ Error getting database stats:', error);
        return { success: false, error: error.message };
    }
};

module.exports = {
    db,
    run,
    get,
    all,
    exec,
    beginTransaction,
    commit,
    rollback,
    tableExists,
    getTableSchema,
    backup,
    close,
    initializeDatabase,
    healthCheck,
    getStats
};