web.ts (9367B)
1 import { asset_cache_fetch_bytes, handle_err, resolve_wasm_path, type IdbClientConfig, type ResolveError } from "@radroots/utils"; 2 import { del as idb_del, get as idb_get, set as idb_set, type UseStore } from "idb-keyval"; 3 import type { BindParams, Database, SqlJsStatic, SqlValue, Statement } from "sql.js"; 4 import init_sql_js from "sql.js/dist/sql-wasm.js"; 5 import { backup_b64_to_bytes, backup_bytes_to_b64 } from "../backup/codec.js"; 6 import type { BackupSqlPayload } from "../backup/types.js"; 7 import type { LegacyKeyConfig } from "../crypto/types.js"; 8 import { IDB_CONFIG_CIPHER_SQL } from "../idb/config.js"; 9 import { WebEncryptedStore } from "../idb/encrypted_store.js"; 10 import { idb_value_as_bytes } from "../idb/value.js"; 11 import { is_error } from "../utils/resolve.js"; 12 import { cl_sql_error } from "./error.js"; 13 import { DEFAULT_SQL_WASM_PATH } from "./constants.js"; 14 import type { IClientSqlEncryptedStore, IWebSqlEngine, SqlJsExecOutcome, SqlJsParams, SqlJsResultRow, WebSqlEngineConfig } from "./types.js"; 15 16 const DEFAULT_SQL_CIPHER_CONFIG: IdbClientConfig = IDB_CONFIG_CIPHER_SQL; 17 const resolve_or_throw = <T>(value: ResolveError<T>): T => { 18 if (is_error(value)) throw new Error(value.err); 19 return value; 20 }; 21 22 interface IWebSqlEngineEncryptedStore extends IClientSqlEncryptedStore { 23 get_store_id(): string; 24 } 25 26 class WebSqlEngineEncryptedStore implements IWebSqlEngineEncryptedStore { 27 private readonly store_key: string; 28 private readonly store_id: string; 29 private readonly encrypted_store: WebEncryptedStore; 30 31 constructor(config: WebSqlEngineConfig) { 32 this.store_key = config.store_key; 33 this.store_id = `sql:${this.store_key}`; 34 const legacy_idb_config = config.cipher_config === null 35 ? null 36 : config.cipher_config ?? DEFAULT_SQL_CIPHER_CONFIG; 37 const legacy_key: LegacyKeyConfig | null = legacy_idb_config 38 ? { 39 idb_config: legacy_idb_config, 40 key_name: `radroots.sql.${this.store_key}.aes-gcm.key`, 41 iv_length: 12, 42 algorithm: "AES-GCM" 43 } 44 : null; 45 this.encrypted_store = new WebEncryptedStore({ 46 idb_config: config.idb_config, 47 store_id: this.store_id, 48 idb_error: cl_sql_error.idb_undefined, 49 legacy_key, 50 iv_length: 12 51 }); 52 } 53 54 public get_store_id(): string { 55 return this.store_id; 56 } 57 58 private async get_store(): Promise<UseStore> { 59 const store = await this.encrypted_store.get_store(); 60 return resolve_or_throw(store); 61 } 62 63 async load(): Promise<Uint8Array | null> { 64 if (typeof indexedDB === "undefined") return null; 65 const store = await this.get_store(); 66 const data = await idb_get(this.store_key, store); 67 const bytes = idb_value_as_bytes(data); 68 if (!bytes) return null; 69 const outcome = resolve_or_throw(await this.encrypted_store.decrypt_record(bytes)); 70 if (outcome.reencrypted) await idb_set(this.store_key, outcome.reencrypted, store); 71 return outcome.plaintext; 72 } 73 74 async save(bytes: Uint8Array): Promise<void> { 75 if (typeof indexedDB === "undefined") return; 76 const enc = resolve_or_throw(await this.encrypted_store.encrypt_bytes(bytes)); 77 const store = await this.get_store(); 78 await idb_set(this.store_key, enc, store); 79 } 80 81 async remove(): Promise<void> { 82 if (typeof indexedDB === "undefined") return; 83 const store = await this.get_store(); 84 await idb_del(this.store_key, store); 85 } 86 } 87 88 export class WebSqlEngine implements IWebSqlEngine { 89 private save_timer: number | undefined; 90 private db: Database; 91 private readonly store_id: string; 92 93 private constructor( 94 private readonly sqljs: SqlJsStatic, 95 db: Database, 96 private readonly store: WebSqlEngineEncryptedStore 97 ) { 98 this.db = db; 99 this.store_id = store.get_store_id(); 100 } 101 102 static async create(config: WebSqlEngineConfig): Promise<WebSqlEngine> { 103 const wasm_url = resolve_wasm_path( 104 config.sql_wasm_path, 105 "sql-wasm.wasm", 106 DEFAULT_SQL_WASM_PATH 107 ); 108 const locate_wasm = (wasm_file: string): string => { 109 const resolved = resolve_wasm_path( 110 config.sql_wasm_path, 111 wasm_file, 112 DEFAULT_SQL_WASM_PATH 113 ); 114 console.log(`[sql] wasm_resolve`, { 115 wasm_file, 116 resolved, 117 sql_wasm_path: config.sql_wasm_path, 118 store_key: config.store_key, 119 idb_database: config.idb_config.database, 120 idb_store: config.idb_config.store 121 }); 122 return resolved; 123 }; 124 console.log(`[sql] init`, { 125 sql_wasm_path: config.sql_wasm_path, 126 store_key: config.store_key, 127 idb_database: config.idb_config.database, 128 idb_store: config.idb_config.store 129 }); 130 let wasm_bytes: Uint8Array | null = null; 131 try { 132 wasm_bytes = await asset_cache_fetch_bytes(wasm_url, { request_init: { cache: "force-cache" } }); 133 } catch { } 134 const sql = await init_sql_js({ 135 locateFile: locate_wasm, 136 wasmBinary: wasm_bytes ?? undefined 137 }); 138 const store = new WebSqlEngineEncryptedStore(config); 139 const existing = await store.load(); 140 const db = existing ? new sql.Database(existing) : new sql.Database(); 141 return new WebSqlEngine(sql, db, store); 142 } 143 144 async close(): Promise<void> { 145 if (this.save_timer) { 146 self.clearTimeout(this.save_timer); 147 this.save_timer = undefined; 148 const bytes = this.db.export(); 149 await this.store.save(bytes); 150 } 151 this.db.close(); 152 } 153 154 async purge_storage(): Promise<void> { 155 await this.store.remove(); 156 } 157 158 public get_store_id(): string { 159 return this.store_id; 160 } 161 162 private schedule_persist(): void { 163 if (this.save_timer) return; 164 this.save_timer = self.setTimeout(async () => { 165 const bytes = this.db.export(); 166 await this.store.save(bytes); 167 this.save_timer = undefined; 168 }, 200); 169 } 170 171 public exec(sql: string, params: SqlJsParams): SqlJsExecOutcome { 172 const st = this.prepare(sql); 173 this.bind(st, params); 174 const result = this.consume_exec(st); 175 st.free(); 176 this.schedule_persist(); 177 return result; 178 } 179 180 public query(sql: string, params: SqlJsParams): SqlJsResultRow[] { 181 const st = this.prepare(sql); 182 this.bind(st, params); 183 const rows = this.collect_rows(st); 184 st.free(); 185 return rows; 186 } 187 188 public export_bytes(): Uint8Array { 189 return this.db.export(); 190 } 191 192 public async import_bytes(bytes: Uint8Array): Promise<void> { 193 this.db.close(); 194 this.db = new this.sqljs.Database(bytes); 195 await this.store.save(bytes); 196 } 197 198 public async export_backup(): Promise<ResolveError<BackupSqlPayload>> { 199 try { 200 const bytes = this.export_bytes(); 201 return { bytes_b64: backup_bytes_to_b64(bytes) }; 202 } catch (e) { 203 return handle_err(e); 204 } 205 } 206 207 public async import_backup(payload: BackupSqlPayload): Promise<ResolveError<void>> { 208 try { 209 const bytes = backup_b64_to_bytes(payload.bytes_b64); 210 await this.import_bytes(bytes); 211 return; 212 } catch (e) { 213 return handle_err(e); 214 } 215 } 216 217 private prepare(sql: string): Statement { 218 return this.db.prepare(sql); 219 } 220 221 private bind(st: Statement, params: SqlJsParams): void { 222 let bind_params: BindParams; 223 if (Array.isArray(params)) bind_params = [...params]; 224 else bind_params = { ...(params as Readonly<Record<string, SqlValue>>) }; 225 st.bind(bind_params); 226 } 227 228 private consume_exec(st: Statement): SqlJsExecOutcome { 229 const changes_before = this.db.getRowsModified(); 230 let last_id = 0; 231 232 while (st.step()) { 233 const col_names = st.getColumnNames(); 234 const idx = col_names.indexOf("last_insert_rowid()"); 235 if (idx >= 0) { 236 const v = st.get()[idx]; 237 if (typeof v === "number") last_id = v; 238 } 239 } 240 241 const changes = this.db.getRowsModified() - changes_before; 242 243 if (!last_id) { 244 const res = this.db.exec("select last_insert_rowid() as id"); 245 if (res[0]?.values?.[0]?.[0]) { 246 const v = res[0].values[0][0]; 247 if (typeof v === "number") { 248 last_id = v; 249 } 250 } 251 } 252 253 return { changes, last_insert_id: last_id }; 254 } 255 256 private collect_rows(st: Statement): SqlJsResultRow[] { 257 const out: SqlJsResultRow[] = []; 258 const names = st.getColumnNames(); 259 260 while (st.step()) { 261 const row = st.get(); 262 const obj: SqlJsResultRow = {}; 263 for (let i = 0; i < names.length; i++) obj[names[i]] = row[i]; 264 out.push(obj); 265 } 266 267 return out; 268 } 269 }