WebAssembly 与 OPFS 离线存储架构¶

// worker/sqlite-storage.worker.ts
import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

interface QueryMessage {
  type: 'EXECUTE' | 'QUERY';
  sql: string;
  params?: any[];
}

let db: any = null;

async function initSqlite() {
  const sqlite3 = await sqlite3InitModule({
    print: console.log,
    printErr: console.error,
  });

  if ('opfs' in sqlite3) {
    // 实例化 OPFS 高性能持久化数据库
    db = new sqlite3.oo1.OpfsDb('/offline_app.sqlite3');
    console.log('✅ SQLite OPFS Database successfully mounted at /offline_app.sqlite3');

    // 初始化核心业务表结构
    db.exec(`
      CREATE TABLE IF NOT EXISTS notes (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        content TEXT,
        sync_status INTEGER DEFAULT 0, -- 0: 待同步, 1: 已同步
        updated_at INTEGER NOT NULL
      );
      CREATE INDEX IF NOT EXISTS idx_notes_sync ON notes(sync_status);
    `);
  }
}

self.onmessage = async (event: MessageEvent<QueryMessage>) => {
  if (!db) await initSqlite();

  const { type, sql, params } = event.data;

  try {
    if (type === 'QUERY') {
      const results: any[] = [];
      db.exec({
        sql,
        bind: params,
        rowMode: 'object',
        callback: (row: any) => results.push(row),
      });
      self.postMessage({ success: true, data: results });
    } else {
      db.exec({ sql, bind: params });
      self.postMessage({ success: true, rowsModified: db.changes() });
    }
  } catch (err: any) {
    self.postMessage({ success: false, error: err.message });
  }
};

// services/sync-engine.ts
export class OfflineSyncEngine {
  private isSyncing = false;

  async triggerSync() {
    if (!navigator.onLine || this.isSyncing) return;
    this.isSyncing = true;

    try {
      // 1. 查询本地所有未同步的变更记录
      const dirtyRecords = await this.queryLocal(
        'SELECT * FROM notes WHERE sync_status = 0 ORDER BY updated_at ASC'
      );

      if (dirtyRecords.length > 0) {
        // 2. 批量推送到云端
        const response = await fetch('/api/sync/batch-push', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ changes: dirtyRecords }),
        });

        if (response.ok) {
          const { appliedIds } = await response.json();
          // 3. 标记本地状态为已同步
          await this.executeLocal(
            `UPDATE notes SET sync_status = 1 WHERE id IN (${appliedIds.map(() => '?').join(',')})`,
            appliedIds
          );
        }
      }
    } finally {
      this.isSyncing = false;
    }
  }

  private queryLocal(sql: string, params: any[] = []): Promise<any[]> {
    // 封装向 SQLite Web Worker 发起的请求
    return new Promise((resolve) => { /* ... */ });
  }
  private executeLocal(sql: string, params: any[] = []): Promise<void> {
    return new Promise((resolve) => { /* ... */ });
  }
}