290 lines
10 KiB
JavaScript
Executable File
290 lines
10 KiB
JavaScript
Executable File
#!/usr/bin/env node
|
||
// Migration script: v1.0.0 -> v1.1.0
|
||
// Adds: users, sessions tables, multi-vendor support, vendor column in documents
|
||
// Run: node migrate-to-1.1.js
|
||
|
||
const sqlite3 = require('sqlite3').verbose();
|
||
const bcrypt = require('bcryptjs');
|
||
const fs = require('fs');
|
||
const path = require('path');
|
||
|
||
const DB_FILE = './cve_database.db';
|
||
const BACKUP_FILE = `./cve_database_backup_${Date.now()}.db`;
|
||
|
||
async function migrate() {
|
||
console.log('╔════════════════════════════════════════════════════════╗');
|
||
console.log('║ CVE Database Migration: v1.0.0 → v1.1.0 ║');
|
||
console.log('╚════════════════════════════════════════════════════════╝\n');
|
||
|
||
// Check if database exists
|
||
if (!fs.existsSync(DB_FILE)) {
|
||
console.log('❌ Database not found. Run setup.js for fresh install.');
|
||
process.exit(1);
|
||
}
|
||
|
||
// Backup database
|
||
console.log('📦 Creating backup...');
|
||
fs.copyFileSync(DB_FILE, BACKUP_FILE);
|
||
console.log(` ✓ Backup saved to: ${BACKUP_FILE}\n`);
|
||
|
||
const db = new sqlite3.Database(DB_FILE);
|
||
|
||
try {
|
||
// Run migrations in sequence
|
||
await addUsersTable(db);
|
||
await addSessionsTable(db);
|
||
await addVendorToDocuments(db);
|
||
await updateCvesConstraint(db);
|
||
await createDefaultAdmin(db);
|
||
await updateView(db);
|
||
|
||
console.log('\n╔════════════════════════════════════════════════════════╗');
|
||
console.log('║ MIGRATION COMPLETE! ║');
|
||
console.log('╚════════════════════════════════════════════════════════╝');
|
||
console.log('\n📋 Summary:');
|
||
console.log(' ✓ Users table added');
|
||
console.log(' ✓ Sessions table added');
|
||
console.log(' ✓ Vendor column added to documents');
|
||
console.log(' ✓ Multi-vendor constraint applied to cves');
|
||
console.log(' ✓ Default admin user created (admin/admin123)');
|
||
console.log(`\n💾 Backup saved: ${BACKUP_FILE}`);
|
||
console.log('\n🚀 Restart your server to apply changes.\n');
|
||
|
||
} catch (error) {
|
||
console.error('\n❌ Migration failed:', error.message);
|
||
console.log(`\n🔄 To restore from backup: cp ${BACKUP_FILE} ${DB_FILE}`);
|
||
process.exit(1);
|
||
} finally {
|
||
db.close();
|
||
}
|
||
}
|
||
|
||
function run(db, sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.run(sql, params, function(err) {
|
||
if (err) reject(err);
|
||
else resolve(this);
|
||
});
|
||
});
|
||
}
|
||
|
||
function get(db, sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.get(sql, params, (err, row) => {
|
||
if (err) reject(err);
|
||
else resolve(row);
|
||
});
|
||
});
|
||
}
|
||
|
||
function all(db, sql, params = []) {
|
||
return new Promise((resolve, reject) => {
|
||
db.all(sql, params, (err, rows) => {
|
||
if (err) reject(err);
|
||
else resolve(rows);
|
||
});
|
||
});
|
||
}
|
||
|
||
async function addUsersTable(db) {
|
||
console.log('1️⃣ Adding users table...');
|
||
|
||
const exists = await get(db,
|
||
"SELECT name FROM sqlite_master WHERE type='table' AND name='users'"
|
||
);
|
||
|
||
if (exists) {
|
||
console.log(' ⏭️ Users table already exists, skipping');
|
||
return;
|
||
}
|
||
|
||
await run(db, `
|
||
CREATE TABLE users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
role VARCHAR(20) NOT NULL DEFAULT 'viewer',
|
||
is_active BOOLEAN DEFAULT 1,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
last_login TIMESTAMP,
|
||
CHECK (role IN ('admin', 'editor', 'viewer'))
|
||
)
|
||
`);
|
||
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)');
|
||
console.log(' ✓ Users table created');
|
||
}
|
||
|
||
async function addSessionsTable(db) {
|
||
console.log('2️⃣ Adding sessions table...');
|
||
|
||
const exists = await get(db,
|
||
"SELECT name FROM sqlite_master WHERE type='table' AND name='sessions'"
|
||
);
|
||
|
||
if (exists) {
|
||
console.log(' ⏭️ Sessions table already exists, skipping');
|
||
return;
|
||
}
|
||
|
||
await run(db, `
|
||
CREATE TABLE sessions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id VARCHAR(255) UNIQUE NOT NULL,
|
||
user_id INTEGER NOT NULL,
|
||
expires_at TIMESTAMP NOT NULL,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
)
|
||
`);
|
||
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_session_id ON sessions(session_id)');
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id)');
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at)');
|
||
console.log(' ✓ Sessions table created');
|
||
}
|
||
|
||
async function addVendorToDocuments(db) {
|
||
console.log('3️⃣ Adding vendor column to documents...');
|
||
|
||
// Check if vendor column exists
|
||
const columns = await all(db, "PRAGMA table_info(documents)");
|
||
const hasVendor = columns.some(col => col.name === 'vendor');
|
||
|
||
if (hasVendor) {
|
||
console.log(' ⏭️ Vendor column already exists, skipping');
|
||
return;
|
||
}
|
||
|
||
// Add vendor column
|
||
await run(db, "ALTER TABLE documents ADD COLUMN vendor VARCHAR(100)");
|
||
|
||
// Populate vendor from the cves table based on cve_id
|
||
await run(db, `
|
||
UPDATE documents
|
||
SET vendor = (
|
||
SELECT c.vendor
|
||
FROM cves c
|
||
WHERE c.cve_id = documents.cve_id
|
||
LIMIT 1
|
||
)
|
||
WHERE vendor IS NULL
|
||
`);
|
||
|
||
// Set default for any remaining nulls
|
||
await run(db, "UPDATE documents SET vendor = 'Unknown' WHERE vendor IS NULL");
|
||
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_doc_vendor ON documents(vendor)');
|
||
console.log(' ✓ Vendor column added and populated');
|
||
}
|
||
|
||
async function updateCvesConstraint(db) {
|
||
console.log('4️⃣ Updating CVEs table for multi-vendor support...');
|
||
|
||
// Check current schema
|
||
const tableInfo = await get(db,
|
||
"SELECT sql FROM sqlite_master WHERE type='table' AND name='cves'"
|
||
);
|
||
|
||
if (tableInfo.sql.includes('UNIQUE(cve_id, vendor)')) {
|
||
console.log(' ⏭️ Multi-vendor constraint already exists, skipping');
|
||
return;
|
||
}
|
||
|
||
// SQLite doesn't support ALTER CONSTRAINT, so we need to rebuild the table
|
||
console.log(' 📋 Rebuilding table with new constraint...');
|
||
|
||
// Create new table with correct schema
|
||
await run(db, `
|
||
CREATE TABLE cves_new (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
cve_id VARCHAR(20) NOT NULL,
|
||
vendor VARCHAR(100) NOT NULL,
|
||
severity VARCHAR(20) NOT NULL,
|
||
description TEXT,
|
||
published_date DATE,
|
||
status VARCHAR(50) DEFAULT 'Open',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(cve_id, vendor)
|
||
)
|
||
`);
|
||
|
||
// Copy data
|
||
await run(db, `
|
||
INSERT INTO cves_new (id, cve_id, vendor, severity, description, published_date, status, created_at, updated_at)
|
||
SELECT id, cve_id, vendor, severity, description, published_date, status, created_at, updated_at
|
||
FROM cves
|
||
`);
|
||
|
||
// Drop old table
|
||
await run(db, 'DROP TABLE cves');
|
||
|
||
// Rename new table
|
||
await run(db, 'ALTER TABLE cves_new RENAME TO cves');
|
||
|
||
// Recreate indexes
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_cve_id ON cves(cve_id)');
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_vendor ON cves(vendor)');
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_severity ON cves(severity)');
|
||
await run(db, 'CREATE INDEX IF NOT EXISTS idx_status ON cves(status)');
|
||
|
||
console.log(' ✓ Multi-vendor constraint applied');
|
||
}
|
||
|
||
async function createDefaultAdmin(db) {
|
||
console.log('5️⃣ Creating default admin user...');
|
||
|
||
const exists = await get(db, "SELECT id FROM users WHERE username = 'admin'");
|
||
|
||
if (exists) {
|
||
console.log(' ⏭️ Admin user already exists, skipping');
|
||
return;
|
||
}
|
||
|
||
const passwordHash = await bcrypt.hash('admin123', 10);
|
||
|
||
await run(db, `
|
||
INSERT INTO users (username, email, password_hash, role, is_active)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`, ['admin', 'admin@localhost', passwordHash, 'admin', 1]);
|
||
|
||
console.log(' ✓ Admin user created (admin/admin123)');
|
||
}
|
||
|
||
async function updateView(db) {
|
||
console.log('6️⃣ Updating document status view...');
|
||
|
||
// Drop old view if exists
|
||
await run(db, 'DROP VIEW IF EXISTS cve_document_status');
|
||
|
||
// Create updated view with multi-vendor support
|
||
await run(db, `
|
||
CREATE VIEW cve_document_status AS
|
||
SELECT
|
||
c.id as record_id,
|
||
c.cve_id,
|
||
c.vendor,
|
||
c.severity,
|
||
c.status,
|
||
COUNT(DISTINCT d.id) as total_documents,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) as advisory_count,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'email' THEN d.id END) as email_count,
|
||
COUNT(DISTINCT CASE WHEN d.type = 'screenshot' THEN d.id END) as screenshot_count,
|
||
CASE
|
||
WHEN COUNT(DISTINCT CASE WHEN d.type = 'advisory' THEN d.id END) > 0
|
||
THEN 'Complete'
|
||
ELSE 'Missing Required Docs'
|
||
END as compliance_status
|
||
FROM cves c
|
||
LEFT JOIN documents d ON c.cve_id = d.cve_id AND c.vendor = d.vendor
|
||
GROUP BY c.id, c.cve_id, c.vendor, c.severity, c.status
|
||
`);
|
||
|
||
console.log(' ✓ View updated');
|
||
}
|
||
|
||
// Run migration
|
||
migrate();
|