304 lines
11 KiB
TypeScript
304 lines
11 KiB
TypeScript
/**
|
|
* 导入服务器资产数据
|
|
* 用法: npx tsx scripts/import-servers.ts [excel文件路径]
|
|
* 默认路径: templates-docs/服务器信息-issue.xlsx
|
|
*/
|
|
|
|
import Database from 'better-sqlite3'
|
|
import path from 'path'
|
|
import fs from 'fs'
|
|
|
|
// 动态导入 xlsx
|
|
const XLSX = require('xlsx')
|
|
|
|
const dbPath = process.env.DATABASE_PATH || './data/assets.db'
|
|
const dbDir = path.dirname(dbPath)
|
|
if (!fs.existsSync(dbDir)) fs.mkdirSync(dbDir, { recursive: true })
|
|
|
|
const db = new Database(dbPath)
|
|
db.pragma('journal_mode = WAL')
|
|
db.pragma('foreign_keys = ON')
|
|
|
|
// 初始化数据库
|
|
function initDb() {
|
|
db.exec(`
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
display_name TEXT NOT NULL,
|
|
email TEXT,
|
|
role TEXT NOT NULL DEFAULT 'viewer',
|
|
is_active INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL,
|
|
permissions TEXT NOT NULL DEFAULT '[]',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
id TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
key_hash TEXT NOT NULL,
|
|
permissions TEXT NOT NULL DEFAULT '["assets:read"]',
|
|
last_used_at TEXT,
|
|
expires_at TEXT,
|
|
is_active INTEGER NOT NULL DEFAULT 1,
|
|
created_by INTEGER REFERENCES users(id),
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS assets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
serial_number TEXT UNIQUE,
|
|
device_type TEXT NOT NULL,
|
|
device_purpose TEXT,
|
|
room TEXT,
|
|
rack_position TEXT,
|
|
node_name TEXT,
|
|
business_ip TEXT,
|
|
hdm_ip TEXT,
|
|
manufacturer TEXT,
|
|
device_model TEXT,
|
|
status TEXT NOT NULL DEFAULT '在用',
|
|
warranty_date TEXT,
|
|
cpu_model TEXT, cpu_generation TEXT, cpu_cores INTEGER,
|
|
cpu_count INTEGER, cpu_threads INTEGER, cpu_spec TEXT,
|
|
memory_model TEXT, memory_frequency TEXT, memory_unit_capacity TEXT,
|
|
memory_count INTEGER, memory_total TEXT,
|
|
gpu_model TEXT, gpu_power TEXT, gpu_count INTEGER,
|
|
nic1_model TEXT, nic1_type TEXT, nic1_speed TEXT, nic1_count INTEGER,
|
|
nic2_model TEXT, nic2_type TEXT, nic2_speed TEXT, nic2_count INTEGER,
|
|
nic3_model TEXT, nic3_type TEXT, nic3_speed TEXT, nic3_count INTEGER,
|
|
sys_disk_model TEXT, sys_disk_spec TEXT, sys_disk_capacity TEXT,
|
|
sys_disk_type TEXT, sys_disk_protocol TEXT, sys_disk_speed TEXT, sys_disk_count INTEGER,
|
|
data_disk1_model TEXT, data_disk1_spec TEXT, data_disk1_capacity TEXT,
|
|
data_disk1_type TEXT, data_disk1_protocol TEXT, data_disk1_speed TEXT, data_disk1_count INTEGER,
|
|
data_disk2_model TEXT, data_disk2_spec TEXT, data_disk2_capacity TEXT,
|
|
data_disk2_type TEXT, data_disk2_protocol TEXT, data_disk2_speed TEXT, data_disk2_count INTEGER,
|
|
data_disk_total_space TEXT,
|
|
raid_model TEXT, raid_spec TEXT, raid_count INTEGER,
|
|
psu1_model TEXT, psu1_power TEXT, psu1_count INTEGER,
|
|
psu2_model TEXT, psu2_power TEXT, psu2_count INTEGER,
|
|
psu_total_power TEXT,
|
|
board_model TEXT, board_count INTEGER,
|
|
raw_data TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE TABLE IF NOT EXISTS audit_logs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER REFERENCES users(id),
|
|
api_key_id INTEGER REFERENCES api_keys(id),
|
|
action TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id INTEGER,
|
|
details TEXT,
|
|
ip_address TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_node_name ON assets(node_name);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_business_ip ON assets(business_ip);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_device_type ON assets(device_type);
|
|
CREATE INDEX IF NOT EXISTS idx_assets_status ON assets(status);
|
|
`)
|
|
|
|
// 创建默认 admin
|
|
const bcrypt = require('bcryptjs')
|
|
const existingAdmin = db.prepare('SELECT id FROM users WHERE username = ?').get('admin')
|
|
if (!existingAdmin) {
|
|
const passwordHash = bcrypt.hashSync('admin123', 10)
|
|
db.prepare('INSERT INTO users (username, password_hash, display_name, role) VALUES (?, ?, ?, ?)')
|
|
.run('admin', passwordHash, '管理员', 'admin')
|
|
}
|
|
|
|
const defaultRoles = [
|
|
{ name: 'admin', display_name: '管理员', permissions: '["*"]' },
|
|
{ name: 'editor', display_name: '编辑者', permissions: '["assets:read","assets:write","assets:delete"]' },
|
|
{ name: 'viewer', display_name: '查看者', permissions: '["assets:read"]' },
|
|
]
|
|
for (const role of defaultRoles) {
|
|
const existing = db.prepare('SELECT id FROM roles WHERE name = ?').get(role.name)
|
|
if (!existing) {
|
|
db.prepare('INSERT INTO roles (name, display_name, permissions) VALUES (?, ?, ?)')
|
|
.run(role.name, role.display_name, role.permissions)
|
|
}
|
|
}
|
|
}
|
|
|
|
// Excel 列名到数据库字段的映射
|
|
const COL_MAP: Record<string, string> = {
|
|
'序号': '_skip',
|
|
'机房': 'room',
|
|
'机架位置': 'rack_position',
|
|
'节点名称': 'node_name',
|
|
'设备型号': 'device_model',
|
|
'序列号': 'serial_number',
|
|
'状态': 'status',
|
|
'HDM地址\n掩码255.255.255.128\n网关29.237.252.126/25': 'hdm_ip',
|
|
'业务管理IP\n掩码255.255.255.128\n网关29.237.253.126/25': 'business_ip',
|
|
'维保截止日期': 'warranty_date',
|
|
'CPU型号': 'cpu_model',
|
|
'CPU代数': 'cpu_generation',
|
|
'CPU核心数': 'cpu_cores',
|
|
'CPU数量': 'cpu_count',
|
|
'CPU总线程数': 'cpu_threads',
|
|
'CPU规格': 'cpu_spec',
|
|
'内存型号': 'memory_model',
|
|
'最大频率': 'memory_frequency',
|
|
'单条内存容量': 'memory_unit_capacity',
|
|
'数量': 'memory_count',
|
|
'内存总量': 'memory_total',
|
|
'系统盘型号': 'sys_disk_model',
|
|
'系统盘规格': 'sys_disk_spec',
|
|
'介质类型': 'sys_disk_type',
|
|
'协议': 'sys_disk_protocol',
|
|
'最大速率': 'sys_disk_speed',
|
|
'数量.1': 'sys_disk_count',
|
|
'数据盘型号': 'data_disk1_model',
|
|
'数据盘规格': 'data_disk1_spec',
|
|
'介质类型.1': 'data_disk1_type',
|
|
'协议.1': 'data_disk1_protocol',
|
|
'最大速率.1': 'data_disk1_speed',
|
|
'数量.2': 'data_disk1_count',
|
|
'数据盘总空间': 'data_disk_total_space',
|
|
'GPU卡型号': 'gpu_model',
|
|
'功率': 'gpu_power',
|
|
'数量.3': 'gpu_count',
|
|
'RAID卡型号': 'raid_model',
|
|
'芯片型号': 'raid_spec',
|
|
'数量.4': 'raid_count',
|
|
'主板型号': 'board_model',
|
|
'网卡1': 'nic1_model',
|
|
'数量.5': 'nic1_count',
|
|
'芯片型号.1': 'nic1_type',
|
|
'端口类型': 'nic1_speed',
|
|
'端口数量': '_skip',
|
|
'端口总数': '_skip',
|
|
'网卡2': 'nic2_model',
|
|
'数量.6': 'nic2_count',
|
|
'芯片型号.2': 'nic2_type',
|
|
'端口类型.1': 'nic2_speed',
|
|
'端口数量.1': '_skip',
|
|
'端口总数.1': '_skip',
|
|
'网卡3': 'nic3_model',
|
|
'数量.7': 'nic3_count',
|
|
'芯片型号.3': 'nic3_type',
|
|
'端口类型.2': 'nic3_speed',
|
|
'端口数量.2': '_skip',
|
|
'端口总数.2': '_skip',
|
|
'电源型号1': 'psu1_model',
|
|
'额定功率': 'psu1_power',
|
|
'数量.8': 'psu1_count',
|
|
'电源型号2': 'psu2_model',
|
|
'额定功率.1': 'psu2_power',
|
|
'数量.9': 'psu2_count',
|
|
'电源总功率': 'psu_total_power',
|
|
}
|
|
|
|
function cleanValue(val: any): string | number | null {
|
|
if (val === null || val === undefined || val === '' || (typeof val === 'string' && val.trim() === '')) return null
|
|
if (typeof val === 'number') return val
|
|
return String(val).trim()
|
|
}
|
|
|
|
function importSheet(ws: any, deviceType: string, devicePurpose: string) {
|
|
const data = XLSX.utils.sheet_to_json(ws, { header: 1, defval: '' })
|
|
if (data.length < 2) {
|
|
console.log(` Sheet has no data rows`)
|
|
return 0
|
|
}
|
|
|
|
const headers = data[0] as string[]
|
|
const rows = data.slice(1)
|
|
|
|
// Map column index to db field
|
|
const fieldMap: { idx: number; field: string }[] = []
|
|
for (let i = 0; i < headers.length; i++) {
|
|
const header = headers[i]
|
|
const field = COL_MAP[header]
|
|
if (field && field !== '_skip') {
|
|
fieldMap.push({ idx: i, field })
|
|
}
|
|
}
|
|
|
|
// 按位置兜底：如果关键字段尚未映射（列名匹配失败时），按固定列索引补上
|
|
const hasField = (f: string) => fieldMap.some(m => m.field === f)
|
|
if (!hasField('business_ip') && headers.length > 8) {
|
|
fieldMap.push({ idx: 8, field: 'business_ip' })
|
|
console.log(' 警告: business_ip 未通过列名匹配，按列索引 8 兜底')
|
|
}
|
|
if (!hasField('hdm_ip') && headers.length > 7) {
|
|
fieldMap.push({ idx: 7, field: 'hdm_ip' })
|
|
console.log(' 警告: hdm_ip 未通过列名匹配，按列索引 7 兜底')
|
|
}
|
|
|
|
const insertFields = ['device_type', 'device_purpose', ...fieldMap.map(f => f.field)]
|
|
const placeholders = insertFields.map(() => '?').join(',')
|
|
const stmt = db.prepare(`
|
|
INSERT OR REPLACE INTO assets (${insertFields.join(',')}, raw_data)
|
|
VALUES (${placeholders}, ?)
|
|
`)
|
|
|
|
let count = 0
|
|
const importTime = new Date().toISOString()
|
|
|
|
const transaction = db.transaction(() => {
|
|
for (const row of rows) {
|
|
const r = row as any[]
|
|
if (!r || r.length === 0 || !r[0]) continue
|
|
|
|
const values: any[] = [deviceType, devicePurpose]
|
|
for (const fm of fieldMap) {
|
|
values.push(cleanValue(r[fm.idx]))
|
|
}
|
|
values.push(JSON.stringify({ imported_at: importTime, raw_row: r }))
|
|
|
|
stmt.run(...values)
|
|
count++
|
|
}
|
|
})
|
|
|
|
transaction()
|
|
return count
|
|
}
|
|
|
|
async function main() {
|
|
const excelPath = process.argv[2] || path.join(__dirname, '..', 'templates-docs', '服务器信息-issue.xlsx')
|
|
|
|
if (!fs.existsSync(excelPath)) {
|
|
console.error(`Excel file not found: ${excelPath}`)
|
|
process.exit(1)
|
|
}
|
|
|
|
console.log('Initializing database...')
|
|
initDb()
|
|
|
|
console.log(`Reading Excel: ${excelPath}`)
|
|
const wb = XLSX.readFile(excelPath)
|
|
|
|
console.log('Importing GPU servers...')
|
|
const gpuSheet = wb.Sheets['GPU服务器']
|
|
const gpuCount = importSheet(gpuSheet, 'GPU服务器', 'GPU计算')
|
|
console.log(` Imported ${gpuCount} GPU servers`)
|
|
|
|
console.log('Importing storage servers...')
|
|
const storSheet = wb.Sheets['存储服务器']
|
|
const storCount = importSheet(storSheet, '存储服务器', '存储')
|
|
console.log(` Imported ${storCount} storage servers`)
|
|
|
|
console.log(`\nTotal: ${gpuCount + storCount} servers imported`)
|
|
db.close()
|
|
}
|
|
|
|
main().catch(console.error)
|