294 lines
10 KiB
TypeScript
294 lines
10 KiB
TypeScript
/**
|
||
* 工单 ID 迁移脚本
|
||
* 将 tickets.id 从自增整数改为工单号（14位纯数字），删除 ticket_no 列
|
||
*
|
||
* 用法：npx tsx scripts/migrate-ticket-id.ts
|
||
*/
|
||
|
||
import Database from 'better-sqlite3'
|
||
import path from 'path'
|
||
|
||
const DB_PATH = process.env.DATABASE_PATH || path.join(__dirname, '..', 'data', 'issue.db')
|
||
|
||
console.log(`数据库路径: ${DB_PATH}`)
|
||
const db = new Database(DB_PATH)
|
||
|
||
// 开启 WAL 模式并强制 checkpoint
|
||
db.pragma('journal_mode = WAL')
|
||
db.pragma('wal_checkpoint(TRUNCATE)')
|
||
console.log('WAL checkpoint 完成')
|
||
|
||
// ============================================================
|
||
// 1. 预检查
|
||
// ============================================================
|
||
|
||
const ticketCount = (db.prepare('SELECT COUNT(*) as c FROM tickets').get() as any).c
|
||
const stepCount = (db.prepare('SELECT COUNT(*) as c FROM ticket_steps').get() as any).c
|
||
console.log(`当前 tickets: ${ticketCount}, ticket_steps: ${stepCount}`)
|
||
|
||
if (ticketCount === 0) {
|
||
console.log('tickets 表为空，无需迁移，直接退出')
|
||
process.exit(0)
|
||
}
|
||
|
||
// 检查是否有 ticket_no 列
|
||
const tableInfo = db.prepare('PRAGMA table_info(tickets)').all() as any[]
|
||
const hasTicketNo = tableInfo.some((col: any) => col.name === 'ticket_no')
|
||
if (!hasTicketNo) {
|
||
console.log('tickets 表已无 ticket_no 列，可能已迁移过，退出')
|
||
process.exit(0)
|
||
}
|
||
|
||
// 检查 id 列是否已是 INTEGER PRIMARY KEY 无自增
|
||
const idCol = tableInfo.find((col: any) => col.name === 'id')
|
||
if (idCol && !idCol.pk) {
|
||
console.error('id 列不是主键，异常状态，退出')
|
||
process.exit(1)
|
||
}
|
||
|
||
// 检查孤立 steps
|
||
const orphanSteps = (db.prepare(
|
||
'SELECT COUNT(*) as c FROM ticket_steps WHERE ticket_id NOT IN (SELECT id FROM tickets)'
|
||
).get() as any).c
|
||
if (orphanSteps > 0) {
|
||
console.error(`存在 ${orphanSteps} 条孤立 ticket_steps 记录，请先清理`)
|
||
process.exit(1)
|
||
}
|
||
|
||
// ============================================================
|
||
// 2. 构建 id 映射
|
||
// ============================================================
|
||
|
||
console.log('构建旧 id → 新 id 映射...')
|
||
const tickets = db.prepare('SELECT id, ticket_no FROM tickets').all() as any[]
|
||
|
||
// 验证 ticket_no 有有效数字
|
||
for (const t of tickets) {
|
||
if (!t.ticket_no || t.ticket_no.replace(/\D/g, '') === '') {
|
||
console.error(`工单 id=${t.id} 的 ticket_no 无有效数字: "${t.ticket_no}"`)
|
||
process.exit(1)
|
||
}
|
||
}
|
||
|
||
// 转换函数：清除所有非数字字符，取后14位，不足则右补0
|
||
function toNewId(ticketNo: string): number {
|
||
const digits = ticketNo.replace(/\D/g, '')
|
||
if (digits.length >= 14) {
|
||
return parseInt(digits.slice(-14)) // 取最后14位，防止超长
|
||
}
|
||
return parseInt(digits.padEnd(14, '0'))
|
||
}
|
||
|
||
// 构建映射并检查重复
|
||
const idMap = new Map<number, number>()
|
||
const reverseMap = new Map<number, number>() // new_id → old_id，用于检测重复
|
||
|
||
for (const t of tickets) {
|
||
const newId = toNewId(t.ticket_no)
|
||
if (reverseMap.has(newId)) {
|
||
const conflictOldId = reverseMap.get(newId)!
|
||
console.error(`工单号冲突: ${newId}`)
|
||
console.error(` 旧工单 id=${t.id}, ticket_no=${t.ticket_no}`)
|
||
console.error(` 旧工单 id=${conflictOldId}, ticket_no=${tickets.find(x => x.id === conflictOldId)?.ticket_no}`)
|
||
process.exit(1)
|
||
}
|
||
reverseMap.set(newId, t.id)
|
||
idMap.set(t.id, newId)
|
||
}
|
||
|
||
// 打印转换示例
|
||
let sampleCount = 0
|
||
for (const t of tickets) {
|
||
const newId = idMap.get(t.id)!
|
||
if (String(newId) !== t.ticket_no) {
|
||
console.log(` 转换: ${t.ticket_no} → ${newId}`)
|
||
sampleCount++
|
||
if (sampleCount >= 5) break
|
||
}
|
||
}
|
||
|
||
console.log(`映射构建完成，共 ${idMap.size} 条`)
|
||
|
||
// ============================================================
|
||
// 3. 执行迁移
|
||
// ============================================================
|
||
|
||
console.log('开始迁移（事务模式）...')
|
||
|
||
try {
|
||
db.pragma('foreign_keys = OFF')
|
||
|
||
const migrate = db.transaction(() => {
|
||
// a. 备份旧表
|
||
console.log(' 备份旧表...')
|
||
db.exec('ALTER TABLE tickets RENAME TO tickets_backup')
|
||
db.exec('ALTER TABLE ticket_steps RENAME TO ticket_steps_backup')
|
||
|
||
// b. 创建新 tickets 表（去掉 ticket_no 列，id 无 AUTOINCREMENT）
|
||
console.log(' 创建新 tickets 表...')
|
||
db.exec(`
|
||
CREATE TABLE tickets (
|
||
id INTEGER PRIMARY KEY,
|
||
device_ip TEXT,
|
||
device_sn TEXT,
|
||
device_name TEXT,
|
||
content TEXT,
|
||
assign_time TEXT,
|
||
close_time TEXT,
|
||
duration_minutes INTEGER,
|
||
availability REAL,
|
||
process_summary TEXT,
|
||
conclusion TEXT,
|
||
fault_category TEXT,
|
||
fault_subcategory TEXT,
|
||
parts_replaced TEXT,
|
||
current_status TEXT NOT NULL DEFAULT 'open',
|
||
counted_in_sla INTEGER NOT NULL DEFAULT 1,
|
||
responsibility TEXT,
|
||
created_by INTEGER REFERENCES users(id),
|
||
updated_by INTEGER REFERENCES users(id),
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
||
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
)
|
||
`)
|
||
|
||
// c. 创建新 ticket_steps 表
|
||
console.log(' 创建新 ticket_steps 表...')
|
||
db.exec(`
|
||
CREATE TABLE ticket_steps (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
ticket_id INTEGER NOT NULL REFERENCES tickets(id) ON DELETE CASCADE,
|
||
step_order INTEGER NOT NULL,
|
||
time_node TEXT,
|
||
handler TEXT,
|
||
description TEXT,
|
||
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
||
)
|
||
`)
|
||
|
||
// d. 迁移 tickets 数据
|
||
console.log(' 迁移 tickets 数据...')
|
||
const cols = [
|
||
'device_ip', 'device_sn', 'device_name', 'content', 'assign_time',
|
||
'close_time', 'duration_minutes', 'availability', 'process_summary',
|
||
'conclusion', 'fault_category', 'fault_subcategory', 'parts_replaced',
|
||
'current_status', 'counted_in_sla', 'responsibility',
|
||
'created_by', 'updated_by', 'created_at', 'updated_at'
|
||
]
|
||
|
||
for (const t of tickets) {
|
||
const newId = idMap.get(t.id)!
|
||
const old = db.prepare(
|
||
`SELECT ${cols.join(', ')} FROM tickets_backup WHERE id = ?`
|
||
).get(t.id) as any
|
||
|
||
const values = cols.map(c => old[c])
|
||
const placeholders = cols.map(() => '?').join(', ')
|
||
db.prepare(
|
||
`INSERT INTO tickets (id, ${cols.join(', ')}) VALUES (?, ${placeholders})`
|
||
).run(newId, ...values)
|
||
}
|
||
|
||
// e. 迁移 ticket_steps 数据
|
||
console.log(' 迁移 ticket_steps 数据...')
|
||
const stepsBackup = db.prepare('SELECT * FROM ticket_steps_backup').all() as any[]
|
||
for (const s of stepsBackup) {
|
||
const newTicketId = idMap.get(s.ticket_id)
|
||
if (!newTicketId) {
|
||
console.error(` ticket_steps id=${s.id}: ticket_id=${s.ticket_id} 在映射中找不到，跳过`)
|
||
continue
|
||
}
|
||
db.prepare(`
|
||
INSERT INTO ticket_steps (id, ticket_id, step_order, time_node, handler, description, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
`).run(s.id, newTicketId, s.step_order, s.time_node, s.handler, s.description, s.created_at)
|
||
}
|
||
|
||
// f. 重建索引（使用新名称避免与备份表索引冲突）
|
||
console.log(' 重建索引...')
|
||
db.exec('CREATE INDEX IF NOT EXISTS idx_ts_ticket_id ON ticket_steps(ticket_id)')
|
||
db.exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_ts_unique ON ticket_steps(ticket_id, step_order)')
|
||
})
|
||
|
||
migrate()
|
||
|
||
// ============================================================
|
||
// 4. 验证
|
||
// ============================================================
|
||
|
||
console.log('验证数据完整性...')
|
||
const newTicketCount = (db.prepare('SELECT COUNT(*) as c FROM tickets').get() as any).c
|
||
const backupTicketCount = (db.prepare('SELECT COUNT(*) as c FROM tickets_backup').get() as any).c
|
||
const newStepCount = (db.prepare('SELECT COUNT(*) as c FROM ticket_steps').get() as any).c
|
||
const backupStepCount = (db.prepare('SELECT COUNT(*) as c FROM ticket_steps_backup').get() as any).c
|
||
|
||
console.log(` tickets: ${newTicketCount} (备份: ${backupTicketCount})`)
|
||
console.log(` ticket_steps: ${newStepCount} (备份: ${backupStepCount})`)
|
||
|
||
if (newTicketCount !== backupTicketCount) {
|
||
throw new Error(`tickets 计数不一致: ${newTicketCount} vs ${backupTicketCount}`)
|
||
}
|
||
if (newStepCount !== backupStepCount) {
|
||
throw new Error(`ticket_steps 计数不一致: ${newStepCount} vs ${backupStepCount}`)
|
||
}
|
||
|
||
// 外键完整性
|
||
const orphan = (db.prepare(
|
||
'SELECT COUNT(*) as c FROM ticket_steps WHERE ticket_id NOT IN (SELECT id FROM tickets)'
|
||
).get() as any).c
|
||
if (orphan > 0) {
|
||
throw new Error(`外键断裂: ${orphan} 条`)
|
||
}
|
||
console.log(' 外键完整性: OK')
|
||
|
||
// id 唯一性
|
||
const dupCheck = db.prepare(
|
||
'SELECT id, COUNT(*) as c FROM tickets GROUP BY id HAVING c > 1'
|
||
).all() as any[]
|
||
if (dupCheck.length > 0) {
|
||
throw new Error(`重复 id: ${JSON.stringify(dupCheck)}`)
|
||
}
|
||
console.log(' id 唯一性: OK')
|
||
|
||
db.pragma('foreign_keys = ON')
|
||
|
||
console.log('')
|
||
console.log('✅ 迁移成功！')
|
||
console.log('')
|
||
console.log('确认无误后手动清理备份表:')
|
||
console.log(' DROP TABLE tickets_backup;')
|
||
console.log(' DROP TABLE ticket_steps_backup;')
|
||
|
||
} catch (e) {
|
||
console.error('')
|
||
console.error('❌ 迁移失败，尝试回滚...')
|
||
console.error(e)
|
||
|
||
// 回滚：检查备份表是否存在，恢复之
|
||
try {
|
||
const hasBackup = db.prepare(
|
||
"SELECT COUNT(*) as c FROM sqlite_master WHERE type='table' AND name='tickets_backup'"
|
||
).get() as any
|
||
|
||
if (hasBackup.c > 0) {
|
||
db.exec('DROP TABLE IF EXISTS tickets')
|
||
db.exec('ALTER TABLE tickets_backup RENAME TO tickets')
|
||
}
|
||
const hasStepsBackup = db.prepare(
|
||
"SELECT COUNT(*) as c FROM sqlite_master WHERE type='table' AND name='ticket_steps_backup'"
|
||
).get() as any
|
||
if (hasStepsBackup.c > 0) {
|
||
db.exec('DROP TABLE IF EXISTS ticket_steps')
|
||
db.exec('ALTER TABLE ticket_steps_backup RENAME TO ticket_steps')
|
||
}
|
||
db.pragma('foreign_keys = ON')
|
||
console.log('已回滚到迁移前状态')
|
||
} catch (rollbackErr) {
|
||
console.error('自动回滚失败，请从备份文件手动恢复数据库！', rollbackErr)
|
||
console.error('运行: cp data/issue.db.backup-* data/issue.db')
|
||
}
|
||
process.exit(1)
|
||
} finally {
|
||
db.close()
|
||
}
|