import initSqlJs, { type Database, type SqlJsStatic } from 'sql.js'; let sqlPromise: Promise<SqlJsStatic> | null = null; function getSql(): Promise<SqlJsStatic> { if (!sqlPromise) { sqlPromise = initSqlJs({ locateFile: () => '/sql-wasm.wasm', }); } return sqlPromise;} export async function openSqliteFile(buffer: ArrayBuffer): Promise<Database> { const SQL = await getSql(); return new SQL.Database(new Uint8Array(buffer));} export function getTableNames(db: Database): string[] { const result = db.exec( "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name" ); if (!result.length) return []; return result[0].values.map((row) => String(row[0]));} export function getTableColumns(db: Database, table: string): string[] { const result = db.exec(`PRAGMA table_info("${table}")`); if (!result.length) return []; return result[0].values.map((row) => String(row[1]));} export function queryTable( db: Database, table: string): Record<string, unknown>[] { const result = db.exec(`SELECT * FROM "${table}"`); if (!result.length) return []; const columns = result[0].columns; return result[0].values.map((row) => { const obj: Record<string, unknown> = {}; columns.forEach((col, i) => { obj[col] = row[i]; }); return obj; });} export async function exportDbWithRatings( originalBuffer: ArrayBuffer, table: string, ratings: Map<string, number>, // id -> rating idColumn: string): Promise<Uint8Array> { const SQL = await getSql(); const db = new SQL.Database(new Uint8Array(originalBuffer)); // Add paired_rating column if it doesn't exist const cols = getTableColumns(db, table); if (!cols.includes('paired_rating')) { db.run(`ALTER TABLE "${table}" ADD COLUMN paired_rating REAL`); } // Update each row with its rating const stmt = db.prepare(`UPDATE "${table}" SET paired_rating = ? WHERE "${idColumn}" = ?`); for (const [id, rating] of ratings) { stmt.run([rating, id]); } stmt.free(); const data = db.export(); db.close(); return data;}
import initSqlJs, { type Database, type SqlJsStatic } from 'sql.js';
let sqlPromise: Promise<SqlJsStatic> | null = null;
function getSql(): Promise<SqlJsStatic> {
if (!sqlPromise) {
sqlPromise = initSqlJs({
locateFile: () => '/sql-wasm.wasm',
});
}
return sqlPromise;
}
export async function openSqliteFile(buffer: ArrayBuffer): Promise<Database> {
const SQL = await getSql();
return new SQL.Database(new Uint8Array(buffer));
}
export function getTableNames(db: Database): string[] {
const result = db.exec(
"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
);
if (!result.length) return [];
return result[0].values.map((row) => String(row[0]));
}
export function getTableColumns(db: Database, table: string): string[] {
const result = db.exec(`PRAGMA table_info("${table}")`);
if (!result.length) return [];
return result[0].values.map((row) => String(row[1]));
}
export function queryTable(
db: Database,
table: string
): Record<string, unknown>[] {
const result = db.exec(`SELECT * FROM "${table}"`);
if (!result.length) return [];
const columns = result[0].columns;
return result[0].values.map((row) => {
const obj: Record<string, unknown> = {};
columns.forEach((col, i) => {
obj[col] = row[i];
});
return obj;
});
}
export async function exportDbWithRatings(
originalBuffer: ArrayBuffer,
table: string,
ratings: Map<string, number>, // id -> rating
idColumn: string
): Promise<Uint8Array> {
const SQL = await getSql();
const db = new SQL.Database(new Uint8Array(originalBuffer));
// Add paired_rating column if it doesn't exist
const cols = getTableColumns(db, table);
if (!cols.includes('paired_rating')) {
db.run(`ALTER TABLE "${table}" ADD COLUMN paired_rating REAL`);
}
// Update each row with its rating
const stmt = db.prepare(`UPDATE "${table}" SET paired_rating = ? WHERE "${idColumn}" = ?`);
for (const [id, rating] of ratings) {
stmt.run([rating, id]);
}
stmt.free();
const data = db.export();
db.close();
return data;
}