Skip to content
rachel/mdump-viewer
File

Blob: app/workers/sqlWorker.js

javascript373 lines
1import initSqlJs from "sql.js";
2import sqlWasmUrl from "sql.js/dist/sql-wasm.wasm?url";
3import {
4  formatBytes,
5  normalizeDump,
6  quoteIdentifier,
7  quoteSqlString,
8  readSqlTextFromFile,
9  rewriteStatementForSqlite,
10  splitSqlStatements,
11  yieldToUi,
12} from "../../lib/sqlDumpUtils";
13 
14let SQL = null;
15let db = null;
16let importInProgress = false;
17let importCancelRequested = false;
18 
19self.onmessage = async (event) => {
20  const message = event.data || {};
21  const { type, requestId } = message;
22 
23  if (type === "cancelImport") {
24    if (importInProgress) {
25      importCancelRequested = true;
26      postEvent("import-cancel-requested", {});
27    }
28    return;
29  }
30 
31  if (!requestId) {
32    return;
33  }
34 
35  try {
36    let data;
37 
38    switch (type) {
39      case "init":
40        data = await initEngine();
41        break;
42      case "reset":
43        data = resetDatabase();
44        break;
45      case "getTables":
46        data = getTables();
47        break;
48      case "getTablePage":
49        data = getTablePage(message);
50        break;
51      case "runQuery":
52        data = runQuery(message.sql);
53        break;
54      case "importDump":
55        data = await importDump(message.file);
56        break;
57      default:
58        throw new Error(`Unknown worker command: ${type}`);
59    }
60 
61    postResponse(requestId, true, data);
62  } catch (error) {
63    postResponse(requestId, false, {
64      message: error?.message || "Worker command failed.",
65      stack: error?.stack || "",
66    });
67  }
68};
69 
70async function initEngine() {
71  if (!SQL) {
72    SQL = await initSqlJs({
73      locateFile: () => sqlWasmUrl,
74    });
75  }
76 
77  if (db) {
78    try {
79      db.close();
80    } catch {
81      // no-op
82    }
83  }
84 
85  db = new SQL.Database();
86  return { ready: true };
87}
88 
89function resetDatabase() {
90  ensureDbReady();
91 
92  try {
93    db.close();
94  } catch {
95    // no-op
96  }
97 
98  db = new SQL.Database();
99  return { ok: true };
100}
101 
102function getTables() {
103  ensureDbReady();
104 
105  const result = db.exec(
106    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
107  );
108 
109  return {
110    tables: result[0]?.values?.map((row) => String(row[0])) || [],
111  };
112}
113 
114function getTablePage({ tableName, page, pageSize }) {
115  ensureDbReady();
116 
117  if (!tableName) {
118    return {
119      tableName: "",
120      page: 0,
121      pageSize: 50,
122      totalRows: 0,
123      columns: [],
124      rows: [],
125      canPrevPage: false,
126      canNextPage: false,
127    };
128  }
129 
130  const safePageSize = Math.max(1, Number(pageSize) || 50);
131  const requestedPage = Math.max(0, Number(page) || 0);
132  const escapedName = quoteIdentifier(tableName);
133 
134  const countRes = db.exec(`SELECT COUNT(*) AS n FROM ${escapedName}`);
135  const totalRows = Number(countRes[0]?.values?.[0]?.[0] || 0);
136 
137  const maxPage = totalRows > 0 ? Math.floor((totalRows - 1) / safePageSize) : 0;
138  const safePage = Math.min(requestedPage, maxPage);
139  const offset = safePage * safePageSize;
140 
141  const dataRes = db.exec(`SELECT * FROM ${escapedName} LIMIT ${safePageSize} OFFSET ${offset}`);
142 
143  let columns = [];
144  let rows = [];
145  if (dataRes.length > 0) {
146    columns = dataRes[0].columns;
147    rows = dataRes[0].values;
148  } else {
149    const infoRes = db.exec(`PRAGMA table_info(${quoteSqlString(tableName)})`);
150    columns = infoRes[0]?.values?.map((row) => String(row[1])) || [];
151  }
152 
153  return {
154    tableName,
155    page: safePage,
156    pageSize: safePageSize,
157    totalRows,
158    columns,
159    rows,
160    canPrevPage: safePage > 0,
161    canNextPage: offset + rows.length < totalRows,
162  };
163}
164 
165function runQuery(sql) {
166  ensureDbReady();
167 
168  const statement = String(sql || "").trim();
169  if (!statement) {
170    throw new Error("Enter a query first.");
171  }
172 
173  const resultSets = db.exec(statement);
174  if (!resultSets.length) {
175    return {
176      columns: [],
177      rows: [],
178      meta: "Query executed successfully. No result set returned.",
179    };
180  }
181 
182  const first = resultSets[0];
183  return {
184    columns: first.columns,
185    rows: first.values,
186    meta: `Returned ${first.values.length.toLocaleString()} rows in first result set (${resultSets.length} set${
187      resultSets.length === 1 ? "" : "s"
188    }).`,
189  };
190}
191 
192async function importDump(file) {
193  ensureDbReady();
194 
195  if (!file || typeof file.arrayBuffer !== "function") {
196    throw new Error("No dump file was provided.");
197  }
198 
199  if (importInProgress) {
200    throw new Error("An import is already in progress.");
201  }
202 
203  importInProgress = true;
204  importCancelRequested = false;
205 
206  const appendLog = (line) => {
207    postEvent("log", { line });
208  };
209 
210  appendLog(`[file] ${file.name} (${formatBytes(file.size)})`);
211 
212  let transactionOpen = false;
213 
214  try {
215    const sqlText = await readSqlTextFromFile(file, appendLog);
216    appendLog(`[decode] extracted ${sqlText.length.toLocaleString()} characters`);
217 
218    if (importCancelRequested) {
219      appendLog("[cancelled] import cancelled before parsing");
220      return {
221        cancelled: true,
222        applied: 0,
223        skipped: 0,
224      };
225    }
226 
227    const normalized = normalizeDump(sqlText);
228    const rawStatements = splitSqlStatements(normalized);
229 
230    const sourceTargets = Array.from(
231      new Set(rawStatements.map(extractSourceTarget).filter(Boolean))
232    );
233    if (sourceTargets.length > 0) {
234      appendLog(
235        `[warn] this dump references ${sourceTargets.length.toLocaleString()} SOURCE file(s) that cannot be auto-loaded by the browser.`
236      );
237      const sampleTargets = sourceTargets.slice(0, 8).join(", ");
238      appendLog(`[hint] import referenced file(s) manually after schema load: ${sampleTargets}`);
239      if (sourceTargets.length > 8) {
240        appendLog(`[hint] ...and ${sourceTargets.length - 8} more SOURCE file(s).`);
241      }
242    }
243 
244    const statements = rawStatements
245      .flatMap(rewriteStatementForSqlite)
246      .filter(Boolean);
247 
248    if (!statements.length) {
249      throw new Error("No executable SQL statements found in the dump.");
250    }
251 
252    postEvent("import-started", {
253      totalStatements: statements.length,
254    });
255 
256    const start = performance.now();
257    let applied = 0;
258    let skipped = 0;
259 
260    db.run("BEGIN");
261    transactionOpen = true;
262 
263    for (let i = 0; i < statements.length; i += 1) {
264      if (importCancelRequested) {
265        db.run("ROLLBACK");
266        transactionOpen = false;
267        appendLog("[cancelled] import cancelled by user");
268        postEvent("import-cancelled", {
269          processed: i,
270          totalStatements: statements.length,
271          applied,
272          skipped,
273        });
274 
275        return {
276          cancelled: true,
277          applied,
278          skipped,
279        };
280      }
281 
282      const stmt = statements[i];
283 
284      try {
285        db.run(stmt);
286        applied += 1;
287      } catch (error) {
288        skipped += 1;
289        if (skipped <= 25) {
290          appendLog(`[skip ${i + 1}] ${error.message}`);
291          appendLog(`  ${stmt.slice(0, 180).replace(/\s+/g, " ")}`);
292        }
293      }
294 
295      const processed = i + 1;
296      if (processed % 200 === 0 || processed === statements.length) {
297        postEvent("import-progress", {
298          processed,
299          totalStatements: statements.length,
300          applied,
301          skipped,
302        });
303        await yieldToUi();
304      }
305    }
306 
307    db.run("COMMIT");
308    transactionOpen = false;
309 
310    const elapsedMs = Math.round(performance.now() - start);
311    appendLog(`[done] applied=${applied} skipped=${skipped} time=${elapsedMs}ms`);
312 
313    postEvent("import-finished", {
314      applied,
315      skipped,
316      elapsedMs,
317    });
318 
319    return {
320      cancelled: false,
321      applied,
322      skipped,
323      elapsedMs,
324    };
325  } catch (error) {
326    if (transactionOpen) {
327      try {
328        db.run("ROLLBACK");
329      } catch {
330        // no-op
331      }
332    }
333 
334    appendLog(`[error] ${error.stack || error.message}`);
335    throw error;
336  } finally {
337    importInProgress = false;
338    importCancelRequested = false;
339  }
340}
341 
342function ensureDbReady() {
343  if (!SQL || !db) {
344    throw new Error("Database engine is not ready yet.");
345  }
346}
347 
348function postResponse(requestId, ok, payload) {
349  self.postMessage({
350    type: "response",
351    requestId,
352    ok,
353    ...(ok ? { data: payload } : { error: payload }),
354  });
355}
356 
357function postEvent(event, data) {
358  self.postMessage({
359    type: "event",
360    event,
361    data,
362  });
363}
364 
365function extractSourceTarget(statement) {
366  const match = String(statement || "").trim().match(/^SOURCE\s+(.+?)\s*;?$/i);
367  if (!match) {
368    return null;
369  }
370 
371  return match[1].replace(/^['"`]|['"`]$/g, "").trim() || null;
372}