99 lines
2.9 KiB
JavaScript
99 lines
2.9 KiB
JavaScript
// OPFS 기반 WebAssembly SQLite (sql.js) 초기화 및 동기화
|
|
// 가벼운 전체 파일 교체 방식. 향후 Supabase 연동 시 증분 동기화로 확장 가능.
|
|
|
|
const DB = (() => {
|
|
let SQL, db, ready;
|
|
const OPFS_FILE = 'faults.db';
|
|
const STORE_KEY = 'db-version';
|
|
|
|
async function ensureSqlJs() {
|
|
if (SQL) return SQL;
|
|
// sql.js CDN
|
|
const module = await window.initSqlJs({ locateFile: f => `https://sql.js.org/dist/${f}` });
|
|
SQL = module;
|
|
return SQL;
|
|
}
|
|
|
|
async function readFromOpfs() {
|
|
if (!('storage' in navigator) || !('getDirectory' in navigator.storage)) return null;
|
|
try {
|
|
const root = await navigator.storage.getDirectory();
|
|
const handle = await root.getFileHandle(OPFS_FILE, { create: false }).catch(() => null);
|
|
if (!handle) return null;
|
|
const file = await handle.getFile();
|
|
return new Uint8Array(await file.arrayBuffer());
|
|
} catch { return null; }
|
|
}
|
|
|
|
async function writeToOpfs(bytes) {
|
|
if (!('storage' in navigator) || !('getDirectory' in navigator.storage)) return;
|
|
const root = await navigator.storage.getDirectory();
|
|
const handle = await root.getFileHandle(OPFS_FILE, { create: true });
|
|
const w = await handle.createWritable();
|
|
await w.write(bytes);
|
|
await w.close();
|
|
}
|
|
|
|
async function init() {
|
|
if (ready) return ready;
|
|
ready = (async () => {
|
|
await ensureSqlJs();
|
|
// 1) OPFS에서 기존 DB 로드
|
|
const bytes = await readFromOpfs();
|
|
if (bytes) {
|
|
db = new SQL.Database(bytes);
|
|
} else {
|
|
// 없으면 서버에서 부트스트랩 DB 다운로드
|
|
const buf = await fetch('/api/db').then(r => r.arrayBuffer());
|
|
db = new SQL.Database(new Uint8Array(buf));
|
|
await persist();
|
|
}
|
|
return true;
|
|
})();
|
|
return ready;
|
|
}
|
|
|
|
async function persist() {
|
|
const data = db.export();
|
|
await writeToOpfs(data);
|
|
}
|
|
|
|
async function syncIfNeeded() {
|
|
// 서버 버전 확인 후 다르면 교체
|
|
try {
|
|
const remoteMeta = await fetch('/api/meta').then(r => r.json());
|
|
const localMeta = getLocalMeta();
|
|
if (!localMeta || Number(remoteMeta.version) > Number(localMeta.version)) {
|
|
const buf = await fetch('/api/db').then(r => r.arrayBuffer());
|
|
db = new SQL.Database(new Uint8Array(buf));
|
|
await persist();
|
|
setLocalMeta(remoteMeta);
|
|
return true;
|
|
}
|
|
} catch {}
|
|
return false;
|
|
}
|
|
|
|
function getLocalMeta() {
|
|
try { return JSON.parse(localStorage.getItem(STORE_KEY)); } catch { return null; }
|
|
}
|
|
function setLocalMeta(meta) {
|
|
localStorage.setItem(STORE_KEY, JSON.stringify(meta));
|
|
}
|
|
|
|
function query(sql, params = []) {
|
|
const stmt = db.prepare(sql);
|
|
stmt.bind(params);
|
|
const rows = [];
|
|
while (stmt.step()) rows.push(stmt.getAsObject());
|
|
stmt.free();
|
|
return rows;
|
|
}
|
|
|
|
return { init, syncIfNeeded, query, persist };
|
|
})();
|
|
|
|
window.DB = DB;
|
|
|
|
|