Skip to content
rachel/mdump-viewer
File

Blob: lib/sqlDumpUtils.js

javascript583 lines
1export async function readSqlTextFromFile(file, appendLog) {
2  const rawBytes = new Uint8Array(await file.arrayBuffer());
3  const inputBytes = isLikelyGzip(file, rawBytes) ? await gunzipBytes(rawBytes, appendLog) : rawBytes;
4  return new TextDecoder("utf-8").decode(inputBytes);
5}
6 
7function isLikelyGzip(file, bytes) {
8  const byName = /\.gz$/i.test(file.name);
9  const byMagic = bytes.length > 2 && bytes[0] === 0x1f && bytes[1] === 0x8b;
10  return byName || byMagic;
11}
12 
13async function gunzipBytes(bytes, appendLog) {
14  appendLog("[gzip] compressed file detected");
15 
16  if (typeof DecompressionStream === "undefined") {
17    throw new Error("This app requires DecompressionStream support for .sql.gz files.");
18  }
19 
20  try {
21    const ds = new DecompressionStream("gzip");
22    const stream = new Blob([bytes]).stream().pipeThrough(ds);
23    const ab = await new Response(stream).arrayBuffer();
24    appendLog("[gzip] decompressed via DecompressionStream");
25    return new Uint8Array(ab);
26  } catch (error) {
27    throw new Error(`Failed to decompress .gz file: ${error.message}`);
28  }
29}
30 
31export function normalizeDump(sql) {
32  let text = sql;
33 
34  if (text.charCodeAt(0) === 0xfeff) {
35    text = text.slice(1);
36  }
37 
38  text = text.replace(/\r\n?/g, "\n");
39 
40  text = text.replace(/\/\*![0-9]{5}\s*([\s\S]*?)\*\//g, "$1");
41  text = text.replace(/\/\*(?!\!)[\s\S]*?\*\//g, " ");
42 
43  text = text.replace(/^\s*--.*$/gm, "");
44  text = text.replace(/^\s*#.*$/gm, "");
45  text = text.replace(/^\s*LOCK TABLES\b.*$/gim, "");
46  text = text.replace(/^\s*UNLOCK TABLES\b.*$/gim, "");
47  text = text.replace(/^\s*DELIMITER\b.*$/gim, "");
48  text = text.replace(/^\s*SET\b.*$/gim, "");
49  text = text.replace(/^\s*START TRANSACTION\b.*$/gim, "");
50  text = text.replace(/^\s*COMMIT\b.*$/gim, "");
51 
52  return text;
53}
54 
55export function splitSqlStatements(sql) {
56  const statements = [];
57  let current = "";
58  let inSingle = false;
59  let inDouble = false;
60  let inBacktick = false;
61 
62  for (let i = 0; i < sql.length; i += 1) {
63    const ch = sql[i];
64 
65    if ((inSingle || inDouble) && ch === "\\") {
66      current += ch;
67      i += 1;
68      if (i < sql.length) {
69        current += sql[i];
70      }
71      continue;
72    }
73 
74    if (!inDouble && !inBacktick && ch === "'") {
75      inSingle = !inSingle;
76      current += ch;
77      continue;
78    }
79 
80    if (!inSingle && !inBacktick && ch === '"') {
81      inDouble = !inDouble;
82      current += ch;
83      continue;
84    }
85 
86    if (!inSingle && !inDouble && ch === "`") {
87      inBacktick = !inBacktick;
88      current += ch;
89      continue;
90    }
91 
92    if (!inSingle && !inDouble && !inBacktick && ch === ";") {
93      const trimmed = current.trim();
94      if (trimmed) {
95        statements.push(trimmed);
96      }
97      current = "";
98      continue;
99    }
100 
101    current += ch;
102  }
103 
104  const last = current.trim();
105  if (last) {
106    statements.push(last);
107  }
108 
109  return statements;
110}
111 
112export function rewriteStatementForSqlite(statement) {
113  let s = statement.trim();
114 
115  if (!s) {
116    return [];
117  }
118 
119  if (/^(CREATE|DROP)\s+DATABASE\b/i.test(s)) return [];
120  if (/^USE\b/i.test(s)) return [];
121  if (/^ALTER\s+TABLE\b/i.test(s)) return [];
122  if (/^(LOCK|UNLOCK)\s+TABLES\b/i.test(s)) return [];
123  if (/^DELIMITER\b/i.test(s)) return [];
124  if (/^SOURCE\b/i.test(s)) return [];
125  if (/^FLUSH\b/i.test(s)) return [];
126  if (/^SELECT\b/i.test(s) && /@@/.test(s)) return [];
127 
128  s = s.replace(/`/g, '"');
129  s = s.replace(/\bUNSIGNED\b/gi, "");
130  s = s.replace(/\bZEROFILL\b/gi, "");
131  s = s.replace(/\bAUTO_INCREMENT\s*=\s*\d+\b/gi, "");
132  s = s.replace(/\bAUTO_INCREMENT\b/gi, "");
133  s = s.replace(/\bCHARACTER\s+SET\s+\w+/gi, "");
134  s = s.replace(/\bCOLLATE\s+\w+/gi, "");
135  s = s.replace(/\bON\s+UPDATE\s+CURRENT_TIMESTAMP(?:\(\))?/gi, "");
136  s = s.replace(/\bCURRENT_TIMESTAMP\(\)/gi, "CURRENT_TIMESTAMP");
137 
138  if (/^DROP\s+TABLE\b/i.test(s)) {
139    return rewriteDropTable(s);
140  }
141 
142  if (/^CREATE\s+TABLE\b/i.test(s)) {
143    return rewriteCreateTable(s);
144  }
145 
146  if (/^CREATE\s+OR\s+REPLACE\s+VIEW\b/i.test(s)) {
147    return rewriteCreateOrReplaceView(s);
148  }
149 
150  s = s.replace(/^INSERT\s+IGNORE\s+INTO\b/i, "INSERT OR IGNORE INTO");
151  s = rewriteMySqlStringLiterals(s);
152 
153  return [s.trim()];
154}
155 
156function rewriteDropTable(sql) {
157  const dropMatch = sql.match(/^DROP\s+TABLE\s+(IF\s+EXISTS\s+)?([\s\S]+)$/i);
158  if (!dropMatch) {
159    return [sql.trim()];
160  }
161 
162  const hasIfExists = Boolean(dropMatch[1]);
163  const tableList = dropMatch[2].trim();
164  const tableTokens = splitTopLevelCsv(tableList).map((token) => token.trim()).filter(Boolean);
165 
166  if (tableTokens.length <= 1) {
167    return [sql.trim()];
168  }
169 
170  return tableTokens.map((token) => {
171    const normalizedName = normalizeIdentifierToken(token);
172    return `DROP TABLE ${hasIfExists ? "IF EXISTS " : ""}${normalizedName}`;
173  });
174}
175 
176function rewriteCreateOrReplaceView(sql) {
177  const createView = sql.replace(/^CREATE\s+OR\s+REPLACE\s+VIEW\b/i, "CREATE VIEW").trim();
178  const viewMatch = createView.match(/^CREATE\s+VIEW\s+("[^"]+"|[^\s]+)\s+AS\b/i);
179 
180  if (!viewMatch) {
181    return [rewriteMySqlStringLiterals(createView)];
182  }
183 
184  const viewName = normalizeIdentifierToken(viewMatch[1]);
185  const normalizedCreateView = createView.replace(
186    /^CREATE\s+VIEW\s+("[^"]+"|[^\s]+)\s+AS\b/i,
187    `CREATE VIEW ${viewName} AS`
188  );
189 
190  return [`DROP VIEW IF EXISTS ${viewName}`, rewriteMySqlStringLiterals(normalizedCreateView)];
191}
192 
193function rewriteCreateTable(sql) {
194  const tableMatch = sql.match(/^CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?("[^"]+"|[^\s(]+)/i);
195  const openParen = sql.indexOf("(");
196  const closeParen = sql.lastIndexOf(")");
197 
198  if (!tableMatch || openParen === -1 || closeParen <= openParen) {
199    return [rewriteMySqlStringLiterals(sql)];
200  }
201 
202  const tableToken = normalizeIdentifierToken(tableMatch[1]);
203  const tableNameRaw = stripIdentifierQuotes(tableToken);
204  const body = sql.slice(openParen + 1, closeParen);
205  const parts = splitTopLevelCsv(body);
206  const columns = [];
207  const tableConstraints = [];
208  const indexStatements = [];
209 
210  for (const rawPart of parts) {
211    const part = rawPart.trim();
212    if (!part) continue;
213 
214    const partWithoutConstraintName = part.replace(
215      /^CONSTRAINT\s+(?:"[^"]+"|`[^`]+`|[^\s]+)\s+/i,
216      ""
217    );
218 
219    if (/^(KEY|INDEX|FULLTEXT\s+KEY|SPATIAL\s+KEY|UNIQUE\s+KEY|UNIQUE\s+INDEX)\b/i.test(partWithoutConstraintName)) {
220      const indexSql = buildIndexStatement(partWithoutConstraintName, tableToken, tableNameRaw);
221      if (indexSql) {
222        indexStatements.push(indexSql);
223      }
224      continue;
225    }
226 
227    const tableConstraint = normalizeTableConstraint(partWithoutConstraintName);
228    if (tableConstraint) {
229      tableConstraints.push(tableConstraint);
230      continue;
231    }
232 
233    if (/^CONSTRAINT\b/i.test(part)) {
234      continue;
235    }
236 
237    const columnSql = normalizeColumnDefinition(part);
238    if (columnSql) {
239      columns.push(columnSql);
240    }
241  }
242 
243  if (!columns.length) {
244    return [rewriteMySqlStringLiterals(sql.slice(0, closeParen + 1).replace(/,\s*\)/g, ")"))];
245  }
246 
247  const createBody = [...columns, ...tableConstraints].join(",\n  ");
248  const createStmt = rewriteMySqlStringLiterals(`CREATE TABLE ${tableToken} (\n  ${createBody}\n)`);
249  return [createStmt, ...indexStatements];
250}
251 
252function normalizeColumnDefinition(part) {
253  const match = part.match(/^("[^"]+"|`[^`]+`|[^\s]+)\s+([\s\S]+)$/);
254  if (!match) {
255    return null;
256  }
257 
258  const colName = normalizeIdentifierToken(match[1]);
259  let definition = match[2];
260  const hasAutoIncrement = /\bAUTO_INCREMENT\b/i.test(definition);
261 
262  definition = definition.replace(/\bUNSIGNED\b/gi, "");
263  definition = definition.replace(/\bZEROFILL\b/gi, "");
264  definition = definition.replace(/\bAUTO_INCREMENT\b/gi, "");
265  definition = definition.replace(/\bCHARACTER\s+SET\s+[^\s,]+/gi, "");
266  definition = definition.replace(/\bCOLLATE\s+[^\s,]+/gi, "");
267  definition = definition.replace(/\bCOMMENT\s+'(?:\\.|[^'])*'/gi, "");
268  definition = definition.replace(/\bON\s+UPDATE\s+CURRENT_TIMESTAMP(?:\(\))?/gi, "");
269 
270  definition = normalizeDataType(definition);
271  definition = definition.replace(/\s+/g, " ").trim();
272 
273  if (!definition) {
274    definition = "TEXT";
275  }
276 
277  if (hasAutoIncrement) {
278    if (/\bPRIMARY\s+KEY\b/i.test(definition)) {
279      return `${colName} INTEGER PRIMARY KEY`;
280    }
281    if (!/^INTEGER\b/i.test(definition)) {
282      definition = `INTEGER ${definition}`;
283    }
284  }
285 
286  return `${colName} ${definition}`.replace(/\s+/g, " ").trim();
287}
288 
289function normalizeDataType(definition) {
290  let out = definition;
291  out = out.replace(/\bTINYINT\s*\(\s*1\s*\)(?=\s|,|\)|$)/gi, "INTEGER");
292  out = out.replace(/\b(?:TINYINT|SMALLINT|MEDIUMINT|INT|INTEGER|BIGINT)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "INTEGER");
293  out = out.replace(/\b(?:DOUBLE\s+PRECISION|DOUBLE|FLOAT|DECIMAL|NUMERIC)(?:\s*\(\s*\d+\s*(?:,\s*\d+\s*)?\))?(?=\s|,|\)|$)/gi, "REAL");
294  out = out.replace(/\b(?:VARCHAR|CHAR|TEXT|TINYTEXT|MEDIUMTEXT|LONGTEXT|JSON)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "TEXT");
295  out = out.replace(/\b(?:ENUM|SET)\s*\([^)]*\)/gi, "TEXT");
296  out = out.replace(/\b(?:DATE|TIME|DATETIME|TIMESTAMP|YEAR)\b/gi, "TEXT");
297  out = out.replace(/\b(?:BLOB|TINYBLOB|MEDIUMBLOB|LONGBLOB|BINARY|VARBINARY)(?:\s*\(\s*\d+\s*\))?(?=\s|,|\)|$)/gi, "BLOB");
298  out = out.replace(/\bBOOL(?:EAN)?\b/gi, "INTEGER");
299  out = out.replace(/\bCURRENT_TIMESTAMP\(\)/gi, "CURRENT_TIMESTAMP");
300  return out;
301}
302 
303function normalizeTableConstraint(part) {
304  const normalized = part.replace(/`/g, '"').trim();
305 
306  const primaryMatch = normalized.match(/^PRIMARY\s+KEY\s*\(([^)]+)\)/i);
307  if (primaryMatch) {
308    const cols = normalizeIndexColumns(primaryMatch[1]);
309    return cols.length ? `PRIMARY KEY (${cols.join(", ")})` : null;
310  }
311 
312  const uniqueMatch = normalized.match(/^UNIQUE(?:\s+KEY|\s+INDEX)?(?:\s+(?:"[^"]+"|[^\s(]+))?\s*\(([^)]+)\)/i);
313  if (uniqueMatch) {
314    const cols = normalizeIndexColumns(uniqueMatch[1]);
315    return cols.length ? `UNIQUE (${cols.join(", ")})` : null;
316  }
317 
318  const foreignMatch = normalized.match(
319    /^FOREIGN\s+KEY\s*\(([^)]+)\)\s+REFERENCES\s+("[^"]+"|[^\s(]+)\s*\(([^)]+)\)\s*([\s\S]*)$/i
320  );
321  if (foreignMatch) {
322    const cols = normalizeIndexColumns(foreignMatch[1]);
323    const refTable = normalizeIdentifierToken(foreignMatch[2]);
324    const refCols = normalizeIndexColumns(foreignMatch[3]);
325    const actions = normalizeForeignKeyActions(foreignMatch[4]);
326    if (cols.length && refCols.length && refTable) {
327      return `FOREIGN KEY (${cols.join(", ")}) REFERENCES ${refTable} (${refCols.join(", ")})${actions}`;
328    }
329    return null;
330  }
331 
332  return null;
333}
334 
335function normalizeForeignKeyActions(actionsSpec) {
336  if (!actionsSpec) {
337    return "";
338  }
339 
340  const clauses = [];
341  const actionRegex = /\bON\s+(DELETE|UPDATE)\s+(RESTRICT|CASCADE|SET\s+NULL|SET\s+DEFAULT|NO\s+ACTION)\b/gi;
342  let match;
343 
344  while ((match = actionRegex.exec(actionsSpec)) !== null) {
345    const eventType = match[1].toUpperCase();
346    const actionType = match[2].replace(/\s+/g, " ").toUpperCase();
347    clauses.push(`ON ${eventType} ${actionType}`);
348  }
349 
350  return clauses.length ? ` ${clauses.join(" ")}` : "";
351}
352 
353function buildIndexStatement(part, tableToken, tableNameRaw) {
354  const normalized = part.replace(/`/g, '"').trim();
355  const match = normalized.match(/^(UNIQUE\s+)?(?:FULLTEXT\s+|SPATIAL\s+)?(?:KEY|INDEX)\s*(?:"([^"]+)"|([^\s(]+))?\s*\(([^)]+)\)/i);
356  if (!match) {
357    return null;
358  }
359 
360  const unique = Boolean(match[1]);
361  const explicitName = match[2] || match[3] || "";
362  const cols = normalizeIndexColumns(match[4]);
363  if (!cols.length) {
364    return null;
365  }
366 
367  const generatedName = `${tableNameRaw}_${cols.map(stripIdentifierQuotes).join("_")}${unique ? "_uidx" : "_idx"}`;
368  const indexName = quoteIdentifier(explicitName || generatedName);
369  return `CREATE ${unique ? "UNIQUE " : ""}INDEX IF NOT EXISTS ${indexName} ON ${tableToken} (${cols.join(", ")})`;
370}
371 
372function normalizeIndexColumns(columnsSpec) {
373  return splitTopLevelCsv(columnsSpec)
374    .map((column) => column.trim())
375    .map((column) => column.replace(/\(\s*\d+\s*\)$/, ""))
376    .map((column) => column.replace(/\s+(ASC|DESC)$/i, ""))
377    .map((column) => normalizeIdentifierToken(column))
378    .filter(Boolean);
379}
380 
381function splitTopLevelCsv(input) {
382  const parts = [];
383  let current = "";
384  let depth = 0;
385  let inSingle = false;
386  let inDouble = false;
387  let inBacktick = false;
388 
389  for (let i = 0; i < input.length; i += 1) {
390    const ch = input[i];
391 
392    if ((inSingle || inDouble) && ch === "\\") {
393      current += ch;
394      i += 1;
395      if (i < input.length) {
396        current += input[i];
397      }
398      continue;
399    }
400 
401    if (!inDouble && !inBacktick && ch === "'") {
402      inSingle = !inSingle;
403      current += ch;
404      continue;
405    }
406 
407    if (!inSingle && !inBacktick && ch === '"') {
408      inDouble = !inDouble;
409      current += ch;
410      continue;
411    }
412 
413    if (!inSingle && !inDouble && ch === "`") {
414      inBacktick = !inBacktick;
415      current += ch;
416      continue;
417    }
418 
419    if (!inSingle && !inDouble && !inBacktick) {
420      if (ch === "(") {
421        depth += 1;
422      } else if (ch === ")") {
423        depth = Math.max(0, depth - 1);
424      } else if (ch === "," && depth === 0) {
425        if (current.trim()) {
426          parts.push(current.trim());
427        }
428        current = "";
429        continue;
430      }
431    }
432 
433    current += ch;
434  }
435 
436  if (current.trim()) {
437    parts.push(current.trim());
438  }
439 
440  return parts;
441}
442 
443function normalizeIdentifierToken(token) {
444  const trimmed = String(token || "").trim();
445  if (!trimmed) {
446    return "";
447  }
448 
449  if (trimmed === "*") {
450    return trimmed;
451  }
452 
453  if (trimmed.includes("(")) {
454    return trimmed;
455  }
456 
457  if (trimmed.includes(".")) {
458    return trimmed
459      .split(".")
460      .map((part) => quoteIdentifier(stripIdentifierQuotes(part)))
461      .join(".");
462  }
463 
464  return quoteIdentifier(stripIdentifierQuotes(trimmed));
465}
466 
467function stripIdentifierQuotes(value) {
468  const trimmed = String(value || "").trim();
469  if (!trimmed) {
470    return "";
471  }
472 
473  if (
474    (trimmed.startsWith('"') && trimmed.endsWith('"')) ||
475    (trimmed.startsWith("`") && trimmed.endsWith("`")) ||
476    (trimmed.startsWith("[") && trimmed.endsWith("]"))
477  ) {
478    return trimmed.slice(1, -1).replace(/""/g, '"');
479  }
480 
481  return trimmed;
482}
483 
484function rewriteMySqlStringLiterals(sql) {
485  let out = "";
486  let inSingle = false;
487 
488  for (let i = 0; i < sql.length; i += 1) {
489    const ch = sql[i];
490 
491    if (!inSingle) {
492      out += ch;
493      if (ch === "'") {
494        inSingle = true;
495      }
496      continue;
497    }
498 
499    if (ch === "\\") {
500      const next = sql[i + 1];
501      if (next === undefined) {
502        out += ch;
503        continue;
504      }
505 
506      if (next === "'") {
507        out += "''";
508        i += 1;
509        continue;
510      }
511 
512      if (next === "\\") {
513        out += "\\";
514        i += 1;
515        continue;
516      }
517 
518      if (next === "n") {
519        out += "\n";
520        i += 1;
521        continue;
522      }
523 
524      if (next === "r") {
525        out += "\r";
526        i += 1;
527        continue;
528      }
529 
530      if (next === "t") {
531        out += "\t";
532        i += 1;
533        continue;
534      }
535 
536      out += next;
537      i += 1;
538      continue;
539    }
540 
541    if (ch === "'") {
542      if (sql[i + 1] === "'") {
543        out += "''";
544        i += 1;
545        continue;
546      }
547 
548      out += ch;
549      inSingle = false;
550      continue;
551    }
552 
553    out += ch;
554  }
555 
556  return out;
557}
558 
559export function formatCell(value) {
560  if (value === null || value === undefined) return "NULL";
561  if (value instanceof Uint8Array) return `[BLOB ${value.length} bytes]`;
562  if (typeof value === "object") return JSON.stringify(value);
563  return String(value);
564}
565 
566export function quoteIdentifier(identifier) {
567  return `"${String(identifier).replace(/"/g, '""')}"`;
568}
569
570export function quoteSqlString(value) {
571  return `'${String(value).replace(/'/g, "''")}'`;
572}
573 
574export function formatBytes(bytes) {
575  if (bytes < 1024) return `${bytes} B`;
576  if (bytes < 1024 * 1024) return `${(bytes / 1024).toFixed(1)} KB`;
577  return `${(bytes / (1024 * 1024)).toFixed(1)} MB`;
578}
579 
580export function yieldToUi() {
581  return new Promise((resolve) => setTimeout(resolve, 0));
582}