40 lines
1.2 KiB
JavaScript
40 lines
1.2 KiB
JavaScript
|
// Migration: Add jira_tickets table
|
||
|
|
const sqlite3 = require('sqlite3').verbose();
|
||
|
|
const path = require('path');
|
||
|
|
|
||
|
|
const dbPath = path.join(__dirname, 'cve_database.db');
|
||
|
|
const db = new sqlite3.Database(dbPath);
|
||
|
|
|
||
|
|
console.log('Starting JIRA tickets migration...');
|
||
|
|
|
||
|
|
db.serialize(() => {
|
||
|
|
// Create jira_tickets table
|
||
|
|
db.run(`
|
||
|
|
CREATE TABLE IF NOT EXISTS jira_tickets (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
cve_id TEXT NOT NULL,
|
||
|
|
vendor TEXT NOT NULL,
|
||
|
|
ticket_key TEXT NOT NULL,
|
||
|
|
url TEXT,
|
||
|
|
summary TEXT,
|
||
|
|
status TEXT DEFAULT 'Open' CHECK(status IN ('Open', 'In Progress', 'Closed')),
|
||
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
FOREIGN KEY (cve_id, vendor) REFERENCES cves(cve_id, vendor) ON DELETE CASCADE
|
||
|
|
)
|
||
|
|
`, (err) => {
|
||
|
|
if (err) console.error('Error creating table:', err);
|
||
|
|
else console.log('✓ jira_tickets table created');
|
||
|
|
});
|
||
|
|
|
||
|
|
// Create indexes
|
||
|
|
db.run('CREATE INDEX IF NOT EXISTS idx_jira_tickets_cve ON jira_tickets(cve_id, vendor)');
|
||
|
|
db.run('CREATE INDEX IF NOT EXISTS idx_jira_tickets_status ON jira_tickets(status)');
|
||
|
|
|
||
|
|
console.log('✓ Indexes created');
|
||
|
|
});
|
||
|
|
|
||
|
|
db.close(() => {
|
||
|
|
console.log('Migration complete!');
|
||
|
|
});
|