slaj/_exec_sql.js

const { Client } = require('pg');
const fs = require('fs');

const SQL_FILE = process.argv[2];
if (!SQL_FILE) {
    console.error('Usage: node _exec_sql.js <sql_file>');
    process.exit(1);
}

const sql = fs.readFileSync(SQL_FILE, 'utf8');

async function main() {
    const client = new Client({
        host: '39.98.38.2',
        port: 5866,
        database: 'highgo',
        user: 'highgo',
        password: 'Gw@141516',
        connectionTimeoutMillis: 30000,
    });

    try {
        await client.connect();
        console.log('Connected to database.');

        // Start transaction
        await client.query('BEGIN');
        console.log('Transaction started.');

        // Split SQL into individual statements
        // Skip comment lines and empty lines
        const statements = [];
        let current = '';
        const lines = sql.split('\n');

        for (const line of lines) {
            const trimmed = line.trim();

            // Skip pure comment lines and empty lines
            if (trimmed === '' || trimmed.startsWith('--')) {
                if (current.trim()) current += '\n';
                continue;
            }

            current += line + '\n';

            // Check if statement is complete (ends with ;)
            if (trimmed.endsWith(';')) {
                const stmt = current.trim();
                if (stmt) statements.push(stmt);
                current = '';
            }
        }

        // Don't forget last statement
        if (current.trim()) statements.push(current.trim());

        console.log(`Total statements to execute: ${statements.length}`);

        let ok = 0;
        let fail = 0;

        for (let i = 0; i < statements.length; i++) {
            const stmt = statements[i];
            // Get first line for preview
            const preview = stmt.split('\n')[0].substring(0, 80);
            try {
                await client.query(stmt);
                ok++;
                if ((i + 1) % 50 === 0) {
                    console.log(`  [${i+1}/${statements.length}] ${ok} ok, ${fail} fail`);
                }
            } catch (err) {
                fail++;
                console.error(`\n  FAIL [#${i+1}]: ${preview}`);
                console.error(`  Error: ${err.message}`);
                // Ask whether to continue or rollback
                console.error(`  Rolling back entire transaction due to error.`);
                await client.query('ROLLBACK');
                await client.end();
                console.log(`\n=== ROLLED BACK ===`);
                console.log(`Executed: ${ok} ok, failed at #${i+1}`);
                process.exit(1);
            }
        }

        // Commit transaction
        await client.query('COMMIT');
        console.log(`\n=== COMMITTED ===`);
        console.log(`Total: ${ok} statements executed successfully, ${fail} failed`);

        await client.end();
    } catch (err) {
        console.error('Connection error:', err.message);
        try { await client.query('ROLLBACK'); } catch(e) {}
        await client.end();
        process.exit(1);
    }
}

main();