数据库迁移（升级）记录

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, 'hazop.db');
const db = new sqlite3.Database(dbPath);


db.serialize(() => {
  const createNodesTableSQL = `
    CREATE TABLE IF NOT EXISTS hazop_nodes (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      zysb TEXT,
      description TEXT,
      node_sequence TEXT,
      design_intent TEXT,
      medium_hazard_params TEXT,
      pid TEXT,
      data_table TEXT,
      merge_cells TEXT,
      text_styles TEXT,
      sort_order INTEGER DEFAULT 0,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now')),
      FOREIGN KEY (project_id) REFERENCES hazop_projects(id) ON DELETE CASCADE
    )
  `;
  
  db.run(createNodesTableSQL, (err) => {
  });

  db.run('CREATE INDEX IF NOT EXISTS idx_hazop_nodes_project_id ON hazop_nodes(project_id)', (err) => {
    if (err) {
      console.error('创建索引失败:', err);

  });

  db.run('CREATE INDEX IF NOT EXISTS idx_hazop_nodes_name ON hazop_nodes(project_id, name)', (err) => {
    if (err) {
      console.error('创建索引失败:', err);
    } else {
    }
  });

  db.all('SELECT id, node_table FROM hazop_projects WHERE node_table IS NOT NULL', [], (err, projects) => {

    console.log(` ${projects.length} 个项目需要迁移`);
    
    let migratedCount = 0;
    let totalNodes = 0;

    projects.forEach((project, index) => {
      const projectId = project.id;
      const nodeTable = project.node_table;

      db.get(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, [nodeTable], (err, tableExists) => {

        db.all(`SELECT * FROM ${nodeTable}`, [], (err, nodes) => {
          if (err) {
            console.error(`  项目 ${projectId}: 读取节点失败:`, err);
            migratedCount++;
            if (migratedCount === projects.length) {
              finishMigration();
            }
            return;
          }

          const insertSQL = `
            INSERT INTO hazop_nodes (
              project_id, name, zysb, description, node_sequence, 
              design_intent, medium_hazard_params, pid, data_table, 
              merge_cells, text_styles, sort_order, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          `;

          let insertedNodes = 0;
          nodes.forEach(node => {
            db.run(insertSQL, [
              projectId,
              node.name,
              node.zysb,
              node.description,
              node.node_sequence,
              node.design_intent,
              node.medium_hazard_params,
              node.pid,
              node.data_table,
              node.merge_cells,
              node.text_styles,
              node.sort_order || 0,
              node.created_at || null,
              node.updated_at || null
            ], (err) => {
              insertedNodes++;
              if (insertedNodes === nodes.length) {
                totalNodes += nodes.length;
                
                migratedCount++;
                if (migratedCount === projects.length) {
                  finishMigration();
                }
              }
            });
          });
        });
      });
    });

    function finishMigration() {
      db.close();
    }
  });
});

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, 'hazop.db');
const db = new sqlite3.Database(dbPath);


db.serialize(() => {
  const createTablesSQL = `
    CREATE TABLE IF NOT EXISTS hazop_tables (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      project_id INTEGER NOT NULL,
      node_id INTEGER NOT NULL,
      row_order INTEGER,
      
      number TEXT,
      parameter TEXT,
      deviation TEXT,
      cause TEXT,
      category TEXT,
      consequence_description TEXT,
      
      risk_L INTEGER,
      risk_S INTEGER,
      risk_R TEXT,
      
      existing_measures TEXT,
      
      residual_risk1_AL INTEGER,
      residual_risk1_AS INTEGER,
      residual_risk1_AR TEXT,
      
      recommendation TEXT,
      recommendation_reason TEXT,
      recommendation_category TEXT,
      
      residual_risk2_AL INTEGER,
      residual_risk2_AS INTEGER,
      residual_risk2_AR TEXT,
      
      responsible_party TEXT,
      
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now')),
      
      FOREIGN KEY (project_id) REFERENCES hazop_projects(id) ON DELETE CASCADE,
      FOREIGN KEY (node_id) REFERENCES hazop_nodes(id) ON DELETE CASCADE
    )
  `;
  
  db.run(createTablesSQL, (err) => {
  });

  db.run('CREATE INDEX IF NOT EXISTS idx_hazop_tables_project_node ON hazop_tables(project_id, node_id)', (err) => {
    if (err) {
      console.error('创建索引失败:', err);
    } else {
    }
  });

  db.run('CREATE INDEX IF NOT EXISTS idx_hazop_tables_row_order ON hazop_tables(project_id, node_id, row_order)', (err) => {
    if (err) {
      console.error('创建索引失败:', err);
  }
  });

  db.all('SELECT id, project_id, data_table FROM hazop_nodes WHERE data_table IS NOT NULL AND data_table != ""', [], (err, nodes) => {

    console.log(` ${nodes.length} 个节点需要迁移分析数据`);
    
    let migratedCount = 0;
    let totalRows = 0;

    nodes.forEach((node) => {
      const nodeId = node.id;
      const projectId = node.project_id;
      const dataTable = node.data_table;

      db.get(`SELECT name FROM sqlite_master WHERE type='table' AND name=?`, [dataTable], (err, tableExists) => {

        db.all(`SELECT * FROM ${dataTable} ORDER BY row_order ASC`, [], (err, rows) => {
          if (err) {
            console.error(`  节点 ${nodeId}: 读取分析数据失败:`, err);
            migratedCount++;
            if (migratedCount === nodes.length) {
              finishMigration();
            }
            return;
          }

          const insertSQL = `
            INSERT INTO hazop_tables (
              project_id, node_id, row_order,
              number, parameter, deviation, cause, category, consequence_description,
              risk_L, risk_S, risk_R,
              existing_measures,
              residual_risk1_AL, residual_risk1_AS, residual_risk1_AR,
              recommendation, recommendation_reason, recommendation_category,
              residual_risk2_AL, residual_risk2_AS, residual_risk2_AR,
              responsible_party,
              created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          `;

          let insertedRows = 0;
          rows.forEach(row => {
            db.run(insertSQL, [
              projectId,
              nodeId,
              row.row_order,
              row.number,
              row.parameter,
              row.deviation,
              row.cause,
              row.category,
              row.consequence_description,
              row.risk_L,
              row.risk_S,
              row.risk_R,
              row.existing_measures,
              row.residual_risk1_AL,
              row.residual_risk1_AS,
              row.residual_risk1_AR,
              row.recommendation,
              row.recommendation_reason,
              row.recommendation_category,
              row.residual_risk2_AL,
              row.residual_risk2_AS,
              row.residual_risk2_AR,
              row.responsible_party,
              row.created_at || null,
              row.updated_at || null
            ], (err) => {
              if (err) {
                console.error(`    行 ${row.id} 迁移失败:`, err);
              }
              
              insertedRows++;
            });
          });
        });
      });
    });

    function finishMigration() {
    
      db.close();
    }
  });
});