73 lines
2.8 KiB
JavaScript
73 lines
2.8 KiB
JavaScript
import mysql from 'mysql2/promise';
|
|
import dotenv from 'dotenv';
|
|
|
|
dotenv.config();
|
|
|
|
const pool = mysql.createPool({
|
|
host: process.env.DB_HOST,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASS,
|
|
database: process.env.DB_NAME,
|
|
port: parseInt(process.env.DB_PORT || '3306'),
|
|
});
|
|
|
|
async function migrate() {
|
|
const conn = await pool.getConnection();
|
|
try {
|
|
console.log('1. Creating asset_remote_v4 table...');
|
|
await conn.query(`
|
|
CREATE TABLE IF NOT EXISTS asset_remote_v4 (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
asset_id VARCHAR(50) NOT NULL,
|
|
net_type VARCHAR(20) NOT NULL, /* 'IP' or 'REMOTE' */
|
|
net_name VARCHAR(100), /* e.g., '기본망', 'AnyDesk' */
|
|
net_value1 VARCHAR(100), /* IP or ID */
|
|
net_value2 VARCHAR(100), /* MAC or PW */
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
deactivated_at DATETIME NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (asset_id) REFERENCES asset_core(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
`);
|
|
|
|
console.log('2. Migrating data from asset_remote...');
|
|
const [oldRows] = await conn.query('SELECT * FROM asset_remote WHERE is_active = 1');
|
|
|
|
let ipCount = 0;
|
|
let remoteCount = 0;
|
|
|
|
for (const row of oldRows) {
|
|
// Migrating IP/MAC
|
|
if (row.ip_address || row.mac_address) {
|
|
await conn.query(
|
|
'INSERT INTO asset_remote_v4 (asset_id, net_type, net_name, net_value1, net_value2, created_at) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[row.asset_id, 'IP', '기본망', row.ip_address, row.mac_address, row.created_at]
|
|
);
|
|
ipCount++;
|
|
}
|
|
// Migrating Remote
|
|
if (row.remote_tool || row.remote_id || row.remote_pw) {
|
|
await conn.query(
|
|
'INSERT INTO asset_remote_v4 (asset_id, net_type, net_name, net_value1, net_value2, created_at) VALUES (?, ?, ?, ?, ?, ?)',
|
|
[row.asset_id, 'REMOTE', row.remote_tool, row.remote_id, row.remote_pw, row.created_at]
|
|
);
|
|
remoteCount++;
|
|
}
|
|
}
|
|
|
|
console.log(`Migrated ${ipCount} IP records and ${remoteCount} Remote records.`);
|
|
|
|
console.log('3. Renaming tables...');
|
|
await conn.query('DROP TABLE IF EXISTS asset_remote_legacy');
|
|
await conn.query('RENAME TABLE asset_remote TO asset_remote_legacy, asset_remote_v4 TO asset_remote;');
|
|
|
|
console.log('✅ Migration V4 (Remote) Complete.');
|
|
} catch (e) {
|
|
console.error('Migration failed:', e);
|
|
} finally {
|
|
conn.release();
|
|
pool.end();
|
|
}
|
|
}
|
|
|
|
migrate(); |