27 lines
2.6 KiB
JavaScript
27 lines
2.6 KiB
JavaScript
|
import fs from 'node:fs';
|
||
|
|
import {spawnSync} from 'node:child_process';
|
||
|
|
const dir=new URL('./',import.meta.url), results=[];
|
||
|
|
function sql(db,query){const r=spawnSync('D:/baiyue-runtime/mysql/server/bin/mysql.exe',['--host=127.0.0.1','--user=root','--database='+db,'-N','-B','--default-character-set=utf8mb4'],{input:query,encoding:'utf8',env:process.env,maxBuffer:20e6});if(r.status)throw new Error(r.stderr);return r.stdout.trim();}
|
||
|
|
function parts(s){let q='',d=0,start=0;const out=[];for(let i=0;i<s.length;i++){const c=s[i];if(q){if(c==='\\'){i++;continue;}if(c===q){if(s[i+1]===q)i++;else q='';}continue;}if("'\"`".includes(c)){q=c;continue;}if(c==='(')d++;else if(c===')')d--;else if(c===','&&!d){out.push(s.slice(start,i).trim());start=i+1;}}out.push(s.slice(start).trim());return out;}
|
||
|
|
for(const [db,file] of [['baiyue_firstleg','头程数据库统一增量-MySQL8.sql'],['baiyue_lastleg','海外区域统一增量-MySQL8.sql']]){
|
||
|
|
let active='';const tables=[];
|
||
|
|
for(const m of fs.readFileSync('deploy/sql/'+file,'utf8').matchAll(/USE (\w+);|CREATE TABLE\s+(?:IF NOT EXISTS\s+)?(\w+)\s*\(([\s\S]*?)\n\) ENGINE=[^;]+;/g)){if(m[1])active=m[1];else if(active===db)tables.push({name:m[2],defs:parts(m[3])});}
|
||
|
|
const columns=new Set(sql(db,'SELECT CONCAT(table_name,".",column_name) FROM information_schema.columns WHERE table_schema=DATABASE();').split(/\r?\n/));
|
||
|
|
const indexes=new Set(sql(db,'SELECT DISTINCT CONCAT(table_name,".",index_name) FROM information_schema.statistics WHERE table_schema=DATABASE();').split(/\r?\n/));
|
||
|
|
const report={db,baselineTables:tables.length,missingColumns:[],missingIndexes:[],appliedIndexes:[],blockedIndexes:[]};
|
||
|
|
for(const t of tables)for(const d of t.defs){
|
||
|
|
const col=d.match(/^`?(\w+)`?\s+(?:bigint|int|tinyint|smallint|mediumint|varchar|char|text|longtext|mediumtext|datetime|timestamp|date|time|decimal|double|float|json|boolean|binary|varbinary|blob|enum|bit)\b/i);
|
||
|
|
if(col&&!columns.has(t.name+'.'+col[1]))report.missingColumns.push(t.name+'.'+col[1]);
|
||
|
|
const key=d.match(/^(UNIQUE\s+)?KEY\s+`?(\w+)`?\s*\(([\s\S]*)\)$/i);
|
||
|
|
if(!key||indexes.has(t.name+'.'+key[2]))continue;
|
||
|
|
report.missingIndexes.push(t.name+'.'+key[2]);
|
||
|
|
if(process.argv.includes('--apply')){
|
||
|
|
try {sql(db,'SET SESSION lock_wait_timeout=15; ALTER TABLE `'+t.name+'` ADD '+d+';');report.appliedIndexes.push(t.name+'.'+key[2]);}
|
||
|
|
catch(e){report.blockedIndexes.push({index:t.name+'.'+key[2],reason:e.message});}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
results.push(report);
|
||
|
|
}
|
||
|
|
fs.writeFileSync(new URL(process.argv.includes('--apply')?'index-repair.json':'schema-verification.json',dir),JSON.stringify(results,null,2));
|
||
|
|
console.log(JSON.stringify(results,null,2));
|