* 检查 SQL 文件的幂等性
(filePath)
| 73 | * 检查 SQL 文件的幂等性 |
| 74 | */ |
| 75 | function validateMigrationFile(filePath) { |
| 76 | const fileName = path.basename(filePath); |
| 77 | const content = fs.readFileSync(filePath, "utf-8"); |
| 78 | const lines = content.split("\n"); |
| 79 | |
| 80 | const issues = []; |
| 81 | |
| 82 | // 检查 CREATE TABLE 语句 |
| 83 | const createTableRegex = /CREATE\s+TABLE\s+"[^"]+"/gi; |
| 84 | const createTableIfNotExistsRegex = /CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+"[^"]+"/gi; |
| 85 | |
| 86 | const createTables = content.match(createTableRegex) || []; |
| 87 | const createTablesIfNotExists = content.match(createTableIfNotExistsRegex) || []; |
| 88 | |
| 89 | const missingIfNotExistsTables = createTables.length - createTablesIfNotExists.length; |
| 90 | |
| 91 | if (missingIfNotExistsTables > 0) { |
| 92 | createTables.forEach((match) => { |
| 93 | if (!/IF\s+NOT\s+EXISTS/i.test(match)) { |
| 94 | const lineNumber = lines.findIndex((line) => line.includes(match.split('"')[1])) + 1; |
| 95 | issues.push({ |
| 96 | type: "CREATE TABLE", |
| 97 | line: lineNumber, |
| 98 | statement: match, |
| 99 | suggestion: match.replace(/CREATE\s+TABLE\s+/i, "CREATE TABLE IF NOT EXISTS "), |
| 100 | }); |
| 101 | } |
| 102 | }); |
| 103 | } |
| 104 | |
| 105 | // 检查 CREATE INDEX 语句 |
| 106 | const createIndexRegex = /CREATE\s+(?:UNIQUE\s+)?INDEX\s+"[^"]+"/gi; |
| 107 | const createIndexIfNotExistsRegex = |
| 108 | /CREATE\s+(?:UNIQUE\s+)?INDEX\s+IF\s+NOT\s+EXISTS\s+"[^"]+"/gi; |
| 109 | |
| 110 | const createIndexes = content.match(createIndexRegex) || []; |
| 111 | const createIndexesIfNotExists = content.match(createIndexIfNotExistsRegex) || []; |
| 112 | |
| 113 | const missingIfNotExistsIndexes = createIndexes.length - createIndexesIfNotExists.length; |
| 114 | |
| 115 | if (missingIfNotExistsIndexes > 0) { |
| 116 | createIndexes.forEach((match) => { |
| 117 | if (!/IF\s+NOT\s+EXISTS/i.test(match)) { |
| 118 | const lineNumber = lines.findIndex((line) => line.includes(match)) + 1; |
| 119 | issues.push({ |
| 120 | type: "CREATE INDEX", |
| 121 | line: lineNumber, |
| 122 | statement: match, |
| 123 | suggestion: match.replace( |
| 124 | /CREATE\s+(UNIQUE\s+)?INDEX\s+/i, |
| 125 | "CREATE $1INDEX IF NOT EXISTS " |
| 126 | ), |
| 127 | }); |
| 128 | } |
| 129 | }); |
| 130 | } |
| 131 | |
| 132 | return { fileName, issues }; |