89 lines
2.7 KiB
JavaScript
89 lines
2.7 KiB
JavaScript
|
const { query, pool } = require('./config/database');
|
||
|
|
|
||
|
|
async function analyzeDatabase() {
|
||
|
|
console.log('🔍 Analyzing Database Schema...\n');
|
||
|
|
|
||
|
|
try {
|
||
|
|
// Get all tables
|
||
|
|
console.log('📋 Tables in database:');
|
||
|
|
const tables = await query(`
|
||
|
|
SELECT tablename
|
||
|
|
FROM pg_tables
|
||
|
|
WHERE schemaname = 'public'
|
||
|
|
ORDER BY tablename
|
||
|
|
`);
|
||
|
|
tables.rows.forEach(row => console.log(` • ${row.tablename}`));
|
||
|
|
|
||
|
|
// Analyze each important table
|
||
|
|
const importantTables = [
|
||
|
|
'products', 'product_images', 'blogposts', 'pages',
|
||
|
|
'portfolioprojects', 'adminusers', 'customers', 'orders'
|
||
|
|
];
|
||
|
|
|
||
|
|
for (const table of importantTables) {
|
||
|
|
const exists = tables.rows.find(r => r.tablename === table);
|
||
|
|
if (!exists) {
|
||
|
|
console.log(`\n⚠️ Missing table: ${table}`);
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
|
||
|
|
console.log(`\n📊 Table: ${table}`);
|
||
|
|
const columns = await query(`
|
||
|
|
SELECT column_name, data_type, is_nullable, column_default
|
||
|
|
FROM information_schema.columns
|
||
|
|
WHERE table_name = $1
|
||
|
|
ORDER BY ordinal_position
|
||
|
|
`, [table]);
|
||
|
|
|
||
|
|
columns.rows.forEach(col => {
|
||
|
|
console.log(` ${col.column_name} | ${col.data_type} | ${col.is_nullable === 'YES' ? 'NULL' : 'NOT NULL'}`);
|
||
|
|
});
|
||
|
|
|
||
|
|
// Check foreign keys
|
||
|
|
const fkeys = await query(`
|
||
|
|
SELECT
|
||
|
|
tc.constraint_name,
|
||
|
|
kcu.column_name,
|
||
|
|
ccu.table_name AS foreign_table_name,
|
||
|
|
ccu.column_name AS foreign_column_name
|
||
|
|
FROM information_schema.table_constraints AS tc
|
||
|
|
JOIN information_schema.key_column_usage AS kcu
|
||
|
|
ON tc.constraint_name = kcu.constraint_name
|
||
|
|
JOIN information_schema.constraint_column_usage AS ccu
|
||
|
|
ON ccu.constraint_name = tc.constraint_name
|
||
|
|
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = $1
|
||
|
|
`, [table]);
|
||
|
|
|
||
|
|
if (fkeys.rows.length > 0) {
|
||
|
|
console.log(' Foreign Keys:');
|
||
|
|
fkeys.rows.forEach(fk => {
|
||
|
|
console.log(` ${fk.column_name} -> ${fk.foreign_table_name}(${fk.foreign_column_name})`);
|
||
|
|
});
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Check indexes
|
||
|
|
console.log('\n📇 Indexes:');
|
||
|
|
const indexes = await query(`
|
||
|
|
SELECT
|
||
|
|
tablename,
|
||
|
|
indexname,
|
||
|
|
indexdef
|
||
|
|
FROM pg_indexes
|
||
|
|
WHERE schemaname = 'public' AND tablename IN ('products', 'product_images', 'blogposts', 'portfolioprojects', 'pages')
|
||
|
|
ORDER BY tablename, indexname
|
||
|
|
`);
|
||
|
|
|
||
|
|
indexes.rows.forEach(idx => {
|
||
|
|
console.log(` ${idx.tablename}.${idx.indexname}`);
|
||
|
|
});
|
||
|
|
|
||
|
|
process.exit(0);
|
||
|
|
} catch (error) {
|
||
|
|
console.error('❌ Error:', error.message);
|
||
|
|
process.exit(1);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
analyzeDatabase();
|