slaj/_db_compare.js

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

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');

        // Step 1: Get all table names quickly
        const tableRes = await client.query(`
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = 'slaj' AND table_type = 'BASE TABLE'
            ORDER BY table_name
        `);

        const dbTableNames = tableRes.rows.map(r => r.table_name);
        console.log(`Total tables in slaj schema: ${dbTableNames.length}`);

        // Save all table names first
        fs.writeFileSync('d:/Workspaces/slaj/_slaj_table_names.json', JSON.stringify(dbTableNames, null, 2));
        console.log('Saved table names to _slaj_table_names.json');

        // Step 2: Load ROHS table names
        const rohsRaw = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_rohs_tables.json', 'utf8'));
        const rohsNames = Object.keys(rohsRaw);

        // Find overlap - case insensitive comparison
        // ROHS tables are uppercase, DB tables could be mixed case
        const dbUpperMap = {};
        dbTableNames.forEach(t => { dbUpperMap[t.toUpperCase()] = t; });

        const overlap = [];
        const rohsOnly = [];
        const dbOnly = [];

        for (const rt of rohsNames) {
            if (dbUpperMap[rt.toUpperCase()]) {
                overlap.push({rohs: rt, db: dbUpperMap[rt.toUpperCase()]});
            } else {
                rohsOnly.push(rt);
            }
        }

        console.log(`\nOverlap tables: ${overlap.length}`);
        console.log(`ROHS-only tables: ${rohsOnly.length}`);
        console.log(`DB-only tables: ${dbTableNames.length - overlap.length}`);

        // Step 3: For overlapping tables, fetch DB column details
        const overlapDbNames = overlap.map(o => o.db);
        console.log(`\nFetching columns for ${overlapDbNames.length} overlapping tables...`);

        const dbOverlapCols = {};
        for (let i = 0; i < overlapDbNames.length; i++) {
            const tname = overlapDbNames[i];
            const colRes = await client.query(`
                SELECT column_name, data_type,
                       COALESCE(character_maximum_length, numeric_precision) AS max_length,
                       is_nullable, column_default,
                       udt_name
                FROM information_schema.columns
                WHERE table_schema = 'slaj' AND table_name = $1
                ORDER BY ordinal_position
            `, [tname]);

            dbOverlapCols[tname] = colRes.rows.map(c => ({
                name: c.column_name.toUpperCase(),
                type: c.udt_name ? c.udt_name.toUpperCase() : c.data_type.toUpperCase(),
                nullable: c.is_nullable === 'YES',
                default: c.column_default,
            }));
            if ((i+1) % 20 === 0) console.log(`  Fetched ${i+1}/${overlapDbNames.length}`);
        }

        // Save overlap columns
        fs.writeFileSync('d:/Workspaces/slaj/_db_overlap_cols.json', JSON.stringify(dbOverlapCols, null, 2));
        console.log(`Saved overlap columns to _db_overlap_cols.json`);

        // Step 4: Do detailed comparison
        let matchCount = 0;
        let mismatchCount = 0;
        const mismatches = [];

        for (const o of overlap) {
            const rohsCols = rohsRaw[o.rohs] || [];
            const dbCols = dbOverlapCols[o.db] || [];

            const rohsColNames = rohsCols.map(c => c.name);
            const dbColNames = dbCols.map(c => c.name);

            const rohsOnlyCols = rohsColNames.filter(n => !dbColNames.includes(n));
            const dbOnlyCols = dbColNames.filter(n => !rohsColNames.includes(n));
            const commonCols = rohsColNames.filter(n => dbColNames.includes(n));

            // Compare types of common columns
            const typeDiffs = [];
            for (const cn of commonCols) {
                const rohsType = (rohsCols.find(c => c.name === cn) || {}).type || '';
                const dbType = (dbCols.find(c => c.name === cn) || {}).type || '';
                if (rohsType !== dbType) {
                    typeDiffs.push({col: cn, rohs: rohsType, db: dbType});
                }
            }

            const isMatch = rohsOnlyCols.length === 0 && dbOnlyCols.length === 0 && typeDiffs.length === 0;
            if (isMatch) {
                matchCount++;
            } else {
                mismatchCount++;
                mismatches.push({
                    rohs: o.rohs,
                    db: o.db,
                    rohsOnlyCols,
                    dbOnlyCols,
                    typeDiffs,
                    commonCount: commonCols.length,
                });
            }
        }

        // Generate report
        const report = {
            summary: {
                rohsTotalTables: rohsNames.length,
                dbTotalTables: dbTableNames.length,
                overlapCount: overlap.length,
                rohsOnlyCount: rohsOnly.length,
                dbOnlyCount: dbTableNames.length - overlap.length,
                fullMatchCount: matchCount,
                mismatchCount: mismatchCount,
            },
            mismatches,
            rohsOnly,
            overlapAll: overlap.map(o => ({rohs: o.rohs, db: o.db})),
        };

        fs.writeFileSync('d:/Workspaces/slaj/_comparison_report.json', JSON.stringify(report, null, 2));
        console.log(`\n=== COMPARISON SUMMARY ===`);
        console.log(`ROHS tables: ${rohsNames.length}`);
        console.log(`安监 DB tables: ${dbTableNames.length}`);
        console.log(`重叠表: ${overlap.length}`);
        console.log(`ROHS独有表: ${rohsOnly.length}`);
        console.log(`安监独有表: ${dbTableNames.length - overlap.length}`);
        console.log(`完全匹配: ${matchCount}`);
        console.log(`字段有差异: ${mismatchCount}`);

        console.log(`\n=== 字段有差异的表 ===`);
        for (const m of mismatches) {
            console.log(`\n  ROHS: ${m.rohs}  <->  DB: ${m.db}`);
            if (m.rohsOnlyCols.length) console.log(`    ROHS独有字段: ${m.rohsOnlyCols.join(', ')}`);
            if (m.dbOnlyCols.length) console.log(`    DB独有字段: ${m.dbOnlyCols.join(', ')}`);
            for (const td of m.typeDiffs) {
                console.log(`    类型差异 [${td.col}]: ROHS=${td.rohs}  DB=${td.db}`);
            }
        }

        console.log(`\n=== ROHS独有表 (安监中不存在) ===`);
        rohsOnly.forEach(t => {
            console.log(`  ${t} (${(rohsRaw[t]||[]).length} cols)`);
        });

        await client.end();
        console.log('\nDone! Full report saved to _comparison_report.json');
    } catch (err) {
        console.error('Error:', err.message);
        console.error(err.stack);
        process.exit(1);
    }
}

main();