print-empty2 ( crypto-targets.json 제외 )
#!/usr/bin/env node
'use strict';
const fs = require('fs');
const path = require('path');
const oracledb = require('oracledb');
function parseArgs(argv) {
const args = {
config: path.resolve(__dirname, '..', 'db.ini'),
targets: path.resolve(__dirname, 'crypto-targets.json'),
output: '',
owners: process.env.SCAN_OWNERS || '',
includeSystemOwners: process.env.SCAN_INCLUDE_SYSTEM_OWNERS === 'true'
};
for (let i = 0; i < argv.length; i += 1) {
const arg = argv[i];
const readValue = () => {
if (i + 1 >= argv.length) {
throw new Error(`${arg} option requires a value.`);
}
i += 1;
return argv[i];
};
if (arg === '--config') args.config = path.resolve(readValue());
else if (arg === '--targets') args.targets = path.resolve(readValue());
else if (arg === '--output') args.output = path.resolve(readValue());
else if (arg === '--owners') args.owners = readValue();
else if (arg === '--include-system-owners') args.includeSystemOwners = true;
else if (arg === '--help' || arg === '-h') {
printHelp();
process.exit(0);
} else {
throw new Error(`Unknown option: ${arg}`);
}
}
return args;
}
function printHelp() {
console.log(`Usage:
node print-empty.js [options]
Prints columns in crypto-targets.json object format when:
1. the table has zero rows, or
2. the table has rows but the column has no non-null values.
Options:
--config DB ini path. Default: ../db.ini
--targets crypto-targets.json path. Default: ./crypto-targets.json
--output write JSON to file instead of stdout
--owners owner list. Default: all accessible non-system schemas
--include-system-owners include system schemas
`);
}
function readIni(filePath) {
if (!fs.existsSync(filePath)) {
return {};
}
return fs.readFileSync(filePath, 'utf8')
.split(/\r?\n/)
.map((line) => line.trim())
.filter((line) => line && !line.startsWith('#') && !line.startsWith(';'))
.reduce((acc, line) => {
const index = line.indexOf('=');
if (index > -1) {
acc[line.slice(0, index).trim()] = line.slice(index + 1).trim();
}
return acc;
}, {});
}
function buildDbConfig(args) {
const ini = readIni(args.config);
const user = process.env.DB_USER || process.env.ORACLE_USER || ini.id;
const password = process.env.DB_PASSWORD || process.env.ORACLE_PASSWORD || ini.pass;
const host = process.env.DB_HOST || ini.ip || '127.0.0.1';
const port = process.env.DB_PORT || ini.port || '1521';
const sid = process.env.DB_SID || ini.sid;
const serviceName = process.env.DB_SERVICE_NAME || ini.serviceName || ini.service_name;
const connectString = process.env.DB_CONNECT_STRING
|| ini.connectString
|| ini.connect_string
|| `${host}:${port}/${serviceName || sid}`;
if (!user || !password || !connectString) {
throw new Error('DB connection information is missing. Check db.ini or DB_* environment variables.');
}
return { user, password, connectString };
}
function splitCsv(value) {
return String(value || '')
.split(',')
.map((item) => item.trim().toUpperCase())
.filter(Boolean);
}
function addInListPredicate(columnName, values, bindPrefix, binds) {
const placeholders = values.map((value, index) => {
const key = `${bindPrefix}${index}`;
binds[key] = value;
return `:${key}`;
});
return `${columnName} IN (${placeholders.join(', ')})`;
}
function normalizeKey(tableName, columnName) {
return `${String(tableName || '').toUpperCase()}|${String(columnName || '').toUpperCase()}`;
}
function loadExistingTargets(filePath) {
if (!fs.existsSync(filePath)) {
throw new Error(`crypto-targets.json not found: ${filePath}`);
}
const parsed = JSON.parse(fs.readFileSync(filePath, 'utf8'));
if (!Array.isArray(parsed)) {
throw new Error('crypto-targets.json must be a JSON array.');
}
const keys = new Set();
for (const item of parsed) {
if (item && item.tableName && item.plainColumn) {
keys.add(normalizeKey(item.tableName, item.plainColumn));
}
}
return keys;
}
function quoteIdentifier(identifier) {
return `"${String(identifier).replace(/"/g, '""')}"`;
}
function toCryptoTarget(row) {
return {
tableName: row.TABLE_NAME,
plainColumn: row.COLUMN_NAME,
cryptoType: '',
encColumn: '',
hashColumn: '',
javaProperty: '',
encryptTypeHandler: '',
hashTypeHandler: ''
};
}
async function getTables(connection, args) {
const owners = splitCsv(args.owners);
const binds = {};
const predicates = [
'DROPPED = \'NO\'',
'TEMPORARY = \'N\'',
'NESTED = \'NO\'',
'TABLE_NAME NOT LIKE \'BIN$%\''
];
if (owners.length > 0) {
predicates.push(addInListPredicate('OWNER', owners, 'owner', binds));
}
if (!args.includeSystemOwners) {
predicates.push(`OWNER NOT IN (
'SYS', 'SYSTEM', 'XDB', 'MDSYS', 'CTXSYS', 'ORDSYS', 'OLAPSYS',
'WMSYS', 'OUTLN', 'DBSNMP', 'APPQOSSYS', 'AUDSYS'
)`);
}
const sql = `
SELECT OWNER, TABLE_NAME
FROM ALL_TABLES
WHERE ${predicates.join('\n AND ')}
ORDER BY OWNER, TABLE_NAME
`;
const result = await connection.execute(sql, binds, { outFormat: oracledb.OUT_FORMAT_OBJECT });
return result.rows;
}
async function getColumns(connection, owner, tableName) {
const result = await connection.execute(
`
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = :owner
AND TABLE_NAME = :tableName
ORDER BY COLUMN_ID
`,
{ owner, tableName },
{ outFormat: oracledb.OUT_FORMAT_OBJECT }
);
return result.rows;
}
async function getCount(connection, sql) {
const result = await connection.execute(sql, [], { outFormat: oracledb.OUT_FORMAT_OBJECT });
return result.rows[0].CNT;
}
async function getEmptyColumns(connection, args, existingTargets) {
const tables = await getTables(connection, args);
const emptyColumns = [];
const errors = [];
let scannedColumns = 0;
for (const [tableIndex, table] of tables.entries()) {
const tableRef = `${quoteIdentifier(table.OWNER)}.${quoteIdentifier(table.TABLE_NAME)}`;
process.stderr.write(`[${tableIndex + 1}/${tables.length}] ${table.OWNER}.${table.TABLE_NAME}\r`);
let tableRows;
let columns;
try {
tableRows = await getCount(connection, `SELECT COUNT(*) AS CNT FROM ${tableRef}`);
columns = await getColumns(connection, table.OWNER, table.TABLE_NAME);
} catch (error) {
errors.push({
owner: table.OWNER,
tableName: table.TABLE_NAME,
columnName: '',
error: error.message
});
continue;
}
scannedColumns += columns.length;
for (const column of columns) {
if (tableRows === 0) {
if (!existingTargets.has(normalizeKey(table.TABLE_NAME, column.COLUMN_NAME))) {
emptyColumns.push(toCryptoTarget({
TABLE_NAME: table.TABLE_NAME,
COLUMN_NAME: column.COLUMN_NAME
}));
}
continue;
}
let columnRows;
try {
columnRows = await getCount(
connection,
`SELECT COUNT(*) AS CNT FROM ${tableRef} WHERE ${quoteIdentifier(column.COLUMN_NAME)} IS NOT NULL`
);
} catch (error) {
errors.push({
owner: table.OWNER,
tableName: table.TABLE_NAME,
columnName: column.COLUMN_NAME,
error: error.message
});
continue;
}
if (columnRows === 0) {
if (!existingTargets.has(normalizeKey(table.TABLE_NAME, column.COLUMN_NAME))) {
emptyColumns.push(toCryptoTarget({
TABLE_NAME: table.TABLE_NAME,
COLUMN_NAME: column.COLUMN_NAME
}));
}
}
}
}
process.stderr.write(' '.repeat(120) + '\r');
console.error(`Tables scanned: ${tables.length}`);
console.error(`Columns scanned: ${scannedColumns}`);
console.error(`Existing targets: ${existingTargets.size}`);
console.error(`Empty columns: ${emptyColumns.length}`);
console.error(`Skipped errors: ${errors.length}`);
for (const error of errors.slice(0, 20)) {
const target = error.columnName
? `${error.owner}.${error.tableName}.${error.columnName}`
: `${error.owner}.${error.tableName}`;
console.error(`Skipped ${target}: ${error.error}`);
}
if (errors.length > 20) {
console.error(`Skipped error list truncated: ${errors.length - 20} more`);
}
return emptyColumns;
}
async function main() {
const args = parseArgs(process.argv.slice(2));
const existingTargets = loadExistingTargets(args.targets);
const dbConfig = buildDbConfig(args);
oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
const connection = await oracledb.getConnection(dbConfig);
try {
const columns = await getEmptyColumns(connection, args, existingTargets);
const output = `${JSON.stringify(columns, null, 2)}\n`;
if (args.output) {
fs.writeFileSync(args.output, output, 'utf8');
console.error(`Output written: ${args.output}`);
} else {
process.stdout.write(output);
}
} finally {
await connection.close();
}
}
main().catch((error) => {
console.error(error.message);
process.exitCode = 1;
});
댓글
댓글 쓰기