server/prisma/convert-sqlite-to-postgres.js

const fs = require('fs');
const path = require('path');

/**
 * 将 SQLite 迁移文件转换为 PostgreSQL 语法
 */

// SQLite 到 PostgreSQL 的转换映射
const sqliteToPostgresConversions = [
  // 主键和自动递增
  {
    pattern: /INTEGER\s+NOT\s+NULL\s+PRIMARY\s+KEY\s+AUTOINCREMENT/g,
    replacement: 'SERIAL PRIMARY KEY'
  },
  {
    pattern: /\s+AUTOINCREMENT/g,
    replacement: ''
  },
  // 数据类型
  {
    pattern: /\bDATETIME\b/g,
    replacement: 'TIMESTAMP'
  },
  // 布尔值默认值 - 修复正则表达式，更精确的匹配
  {
    pattern: /DEFAULT\s+(?:'?(false|true)'?)/gi,
    replacement: "DEFAULT $1"
  },
  // 数字默认值
  {
    pattern: /DEFAULT\s+'?0'?/g,
    replacement: "DEFAULT 0"
  },
  {
    pattern: /DEFAULT\s+'?1'?/g,
    replacement: "DEFAULT 1"
  }
];

/**
 * 转换单个 SQL 文件
 */
function convertSqlFile(filePath) {
  try {
    let sqlContent = fs.readFileSync(filePath, 'utf8');

    console.log(`转换文件: ${path.basename(filePath)}`);

    // 应用所有转换规则
    sqliteToPostgresConversions.forEach(({ pattern, replacement }) => {
      sqlContent = sqlContent.replace(pattern, replacement);
    });

    // 写回文件
    fs.writeFileSync(filePath, sqlContent);

    console.log(`✓ ${path.basename(filePath)} 转换完成`);
    return true;
  } catch (error) {
    console.error(`✗ ${path.basename(filePath)} 转换失败:`, error.message);
    return false;
  }
}

/**
 * 递归处理目录中的所有迁移文件
 */
function convertMigrationsDirectory(migrationsDir) {
  const items = fs.readdirSync(migrationsDir);

  let successCount = 0;
  let failCount = 0;

  items.forEach(item => {
    const itemPath = path.join(migrationsDir, item);
    const stat = fs.statSync(itemPath);

    if (stat.isDirectory() && item !== 'migration_lock.toml') {
      // 递归处理子目录
      const result = convertMigrationsDirectory(itemPath);
      successCount += result.successCount;
      failCount += result.failCount;
    } else if (item === 'migration.sql') {
      // 处理迁移文件
      if (convertSqlFile(itemPath)) {
        successCount++;
      } else {
        failCount++;
      }
    }
  });

  return { successCount, failCount };
}

/**
 * 主函数
 */
function main() {
  const migrationsDir = path.join(__dirname, 'migrations');

  console.log('开始转换 SQLite 迁移文件到 PostgreSQL 语法...\n');

  const result = convertMigrationsDirectory(migrationsDir);

  console.log(`\n转换完成!`);
  console.log(`成功: ${result.successCount} 个文件`);
  console.log(`失败: ${result.failCount} 个文件`);
}

// 运行转换
if (require.main === module) {
  main();
}

module.exports = { convertSqlFile, convertMigrationsDirectory };