diff --git a/web/database/queries/db-queries.js b/web/database/queries/db-queries.js deleted file mode 100644 --- a/web/database/queries/db-queries.js +++ /dev/null @@ -1,74 +0,0 @@ -// @flow - -import type { SqliteDatabase } from 'sql.js'; - -import { migrations } from '../utils/migrations.js'; - -function getSQLiteDBVersion(db: SqliteDatabase): number { - const versionData = db.exec('PRAGMA user_version;'); - if (!versionData.length || !versionData[0].values.length) { - throw new Error('Error while retrieving database version: empty result'); - } - const [dbVersion] = versionData[0].values[0]; - if (typeof dbVersion !== 'number') { - throw new Error( - 'Error while retrieving database version: invalid type returned', - ); - } - return dbVersion; -} - -function setSQLiteDBVersion(db: SqliteDatabase, version: number) { - db.exec(`PRAGMA user_version=${version};`); -} - -function setupSQLiteDB(db: SqliteDatabase) { - db.exec(` - CREATE TABLE IF NOT EXISTS drafts ( - key TEXT UNIQUE PRIMARY KEY NOT NULL, - text TEXT NOT NULL - ); - - CREATE TABLE IF NOT EXISTS metadata ( - name TEXT UNIQUE PRIMARY KEY NOT NULL, - data TEXT NOT NULL - ); - - CREATE TABLE IF NOT EXISTS persist_storage ( - key TEXT UNIQUE PRIMARY KEY NOT NULL, - item TEXT NOT NULL - ); - - CREATE TABLE IF NOT EXISTS reports ( - id TEXT UNIQUE PRIMARY KEY NOT NULL, - report TEXT NOT NULL - ); - `); - const migrationKeys = migrations.size ? migrations.keys() : [0]; - const newDatabaseVersion = Math.max(...migrationKeys); - setSQLiteDBVersion(db, newDatabaseVersion); -} - -function migrate(sqliteDb: SqliteDatabase): boolean { - const dbVersion = getSQLiteDBVersion(sqliteDb); - console.info(`Db version: ${dbVersion}`); - - for (const [idx, migration] of migrations.entries()) { - if (idx <= dbVersion) { - continue; - } - - try { - migration(sqliteDb); - console.log(`migration ${idx} succeeded.`); - setSQLiteDBVersion(sqliteDb, idx); - } catch (e) { - console.error(`migration ${idx} failed.`); - console.error(e); - return false; - } - } - return true; -} - -export { getSQLiteDBVersion, setupSQLiteDB, setSQLiteDBVersion, migrate }; diff --git a/web/database/queries/draft-queries.js b/web/database/queries/draft-queries.js deleted file mode 100644 --- a/web/database/queries/draft-queries.js +++ /dev/null @@ -1,51 +0,0 @@ -// @flow - -import { SqliteDatabase } from 'sql.js'; - -import type { ClientDBDraftInfo } from 'lib/types/draft-types.js'; - -import { parseMultiStatementSQLiteResult } from '../utils/db-utils.js'; - -function removeAllDrafts(db: SqliteDatabase) { - db.exec(`DELETE FROM drafts`); -} - -function updateDraft(db: SqliteDatabase, key: string, text: string) { - const query = ` - INSERT OR REPLACE INTO drafts (key, text) - VALUES ($key, $text) - `; - const params = { - $key: key, - $text: text, - }; - - db.exec(query, params); -} - -function moveDraft(db: SqliteDatabase, oldKey: string, newKey: string) { - const query = ` - UPDATE OR REPLACE drafts - SET key = $newKey, text = (SELECT text FROM drafts WHERE key = $oldKey) - WHERE key = $oldKey - `; - const params = { - $newKey: newKey, - $oldKey: oldKey, - }; - - db.exec(query, params); -} - -function getAllDrafts(db: SqliteDatabase): ClientDBDraftInfo[] { - const rawDBResult = db.exec(`SELECT * FROM drafts`); - const dbResult = - parseMultiStatementSQLiteResult(rawDBResult); - if (dbResult.length === 0) { - return []; - } - - return dbResult[0]; -} - -export { removeAllDrafts, updateDraft, moveDraft, getAllDrafts }; diff --git a/web/database/queries/metadata-queries.js b/web/database/queries/metadata-queries.js deleted file mode 100644 --- a/web/database/queries/metadata-queries.js +++ /dev/null @@ -1,56 +0,0 @@ -// @flow - -import { SqliteDatabase } from 'sql.js'; - -import { parseMultiStatementSQLiteResult } from '../utils/db-utils.js'; - -type Metadata = { - name: string, - data: string, -}; - -function setMetadata(db: SqliteDatabase, entryName: string, data: string) { - const query = ` - INSERT OR REPLACE INTO metadata (name, data) - VALUES ($entryName, $data) - `; - const params = { - $entryName: entryName, - $data: data, - }; - - db.exec(query, params); -} - -function getMetadata(db: SqliteDatabase, entryName: string): ?string { - const query = ` - SELECT * - FROM metadata - WHERE name = $entryName - `; - const params = { - $entryName: entryName, - }; - - const rawResult = db.exec(query, params); - const result = parseMultiStatementSQLiteResult(rawResult); - if (result.length === 0 || result[0].length === 0) { - return undefined; - } - const [entry] = result[0]; - return entry.data; -} - -function clearMetadata(db: SqliteDatabase, entryName: string) { - const query = ` - DELETE FROM metadata - WHERE name = $entryName - `; - const params = { - $entryName: entryName, - }; - - db.exec(query, params); -} - -export { setMetadata, getMetadata, clearMetadata }; diff --git a/web/database/queries/report-queries.js b/web/database/queries/report-queries.js deleted file mode 100644 --- a/web/database/queries/report-queries.js +++ /dev/null @@ -1,72 +0,0 @@ -// @flow - -import { SqliteDatabase } from 'sql.js'; - -import { parseMultiStatementSQLiteResult } from '../utils/db-utils.js'; - -type Report = { - id: string, - report: string, -}; - -function createReportTable(db: SqliteDatabase) { - const query = ` - CREATE TABLE IF NOT EXISTS reports ( - id TEXT UNIQUE PRIMARY KEY NOT NULL, - report TEXT NOT NULL - ); - `; - - db.exec(query); -} - -function updateReport(db: SqliteDatabase, id: string, report: string) { - const query = ` - INSERT OR REPLACE INTO reports (id, report) - VALUES ($id, $report) - `; - const params = { - $id: id, - $report: report, - }; - - db.exec(query, params); -} - -function getAllReports(db: SqliteDatabase): Report[] { - const query = ` - SELECT * - FROM reports - `; - - const rawResult = db.exec(query); - const result = parseMultiStatementSQLiteResult(rawResult); - if (result.length === 0 || result[0].length === 0) { - return []; - } - return result[0]; -} - -function removeReports(db: SqliteDatabase, ids: $ReadOnlyArray) { - const query = ` - DELETE FROM reports - WHERE id IN ($ids) - `; - const params = { - $ids: ids.join(', '), - }; - - db.exec(query, params); -} - -function removeAllReports(db: SqliteDatabase) { - db.exec(`DELETE FROM reports`); -} - -export { - createReportTable, - updateReport, - getAllReports, - removeReports, - removeAllReports, -}; diff --git a/web/database/queries/storage-engine-queries.js b/web/database/queries/storage-engine-queries.js deleted file mode 100644 --- a/web/database/queries/storage-engine-queries.js +++ /dev/null @@ -1,60 +0,0 @@ -// @flow - -import { SqliteDatabase } from 'sql.js'; - -import { parseMultiStatementSQLiteResult } from '../utils/db-utils.js'; - -type Entry = { - key: string, - item: string, -}; - -function setPersistStorageItem(db: SqliteDatabase, key: string, item: string) { - const query = ` - INSERT OR REPLACE INTO persist_storage (key, item) - VALUES ($key, $item) - `; - const params = { - $key: key, - $item: item, - }; - - db.exec(query, params); -} - -function getPersistStorageItem(db: SqliteDatabase, key: string): string { - const query = ` - SELECT * - FROM persist_storage - WHERE key = $key - `; - const params = { - $key: key, - }; - - const rawResult = db.exec(query, params); - const result = parseMultiStatementSQLiteResult(rawResult); - if (result.length === 0 || result[0].length === 0) { - return ''; - } - const [entry] = result[0]; - return entry.item; -} - -function removePersistStorageItem(db: SqliteDatabase, key: string) { - const query = ` - DELETE FROM persist_storage - WHERE key = $key - `; - const params = { - $key: key, - }; - - db.exec(query, params); -} - -export { - setPersistStorageItem, - getPersistStorageItem, - removePersistStorageItem, -}; diff --git a/web/database/utils/db-utils.js b/web/database/utils/db-utils.js --- a/web/database/utils/db-utils.js +++ b/web/database/utils/db-utils.js @@ -1,7 +1,6 @@ // @flow import { detect as detectBrowser } from 'detect-browser'; -import type { QueryExecResult } from 'sql.js'; import { isStaff } from 'lib/shared/staff-utils.js'; import { isDev } from 'lib/utils/dev-utils.js'; @@ -21,28 +20,6 @@ dbModule.FS.unlink(path); } -function parseSQLiteQueryResult(result: QueryExecResult): T[] { - const { columns, values } = result; - return values.map(rowResult => { - const row: any = Object.fromEntries( - columns.map((key, index) => [key, rowResult[index]]), - ); - return row; - }); -} - -// NOTE: sql.js has behavior that when there are multiple statements in query -// e.g. "statement1; statement2; statement3;" -// and statement2 will not return anything, the result will be: -// [result1, result3], not [result1, undefined, result3] -function parseMultiStatementSQLiteResult( - rawResult: $ReadOnlyArray, -): T[][] { - return rawResult.map((queryResult: QueryExecResult) => - parseSQLiteQueryResult(queryResult), - ); -} - function importDatabaseContent( content: Uint8Array, dbModule: EmscriptenModule, @@ -80,7 +57,6 @@ browser && browser.name === 'safari' && browser.os === 'Mac OS'; export { - parseMultiStatementSQLiteResult, isSQLiteSupported, isDesktopSafari, importDatabaseContent, diff --git a/web/database/utils/db-utils.test.js b/web/database/utils/db-utils.test.js deleted file mode 100644 --- a/web/database/utils/db-utils.test.js +++ /dev/null @@ -1,118 +0,0 @@ -// @flow - -import initSqlJs from 'sql.js'; - -import { parseMultiStatementSQLiteResult } from './db-utils.js'; - -describe('SQLite database result parsing', () => { - let database; - - const tableA = [ - { - key: 'key_a', - val: 42, - }, - ]; - - const tableB = [ - { - idx: 1, - text: 'test1', - }, - { - idx: 2, - text: null, - }, - ]; - - const initQuery = ` - CREATE TABLE table_a ( - key TEXT UNIQUE PRIMARY KEY NOT NULL, - val INTEGER - ); - CREATE TABLE table_b ( - idx INTEGER PRIMARY KEY AUTOINCREMENT, - text TEXT - ); - INSERT INTO table_a VALUES ($keyA, $valA); - INSERT INTO table_b(text) VALUES ($textB); - INSERT INTO table_b(text) VALUES (NULL); -`; - - const initValues = { - $keyA: tableA[0].key, - $valA: tableA[0].val, - $textB: tableB[0].text, - }; - - beforeEach(async () => { - const SQL = await initSqlJs(); - database = new SQL.Database(); - - database.exec(initQuery, initValues); - }); - - it('should return result for one query', () => { - const rawResult = database.exec(`SELECT * FROM table_b`); - - const result = parseMultiStatementSQLiteResult(rawResult); - expect(result.length).toBe(1); - - const [data] = result; - expect(data).toEqual(expect.arrayContaining(tableB)); - expect(data.length).toEqual(tableB.length); - }); - - it('should return result for more than one query', () => { - const rawResult = database.exec(` - SELECT * FROM table_a; - SELECT * FROM table_b; - `); - - const result = parseMultiStatementSQLiteResult(rawResult); - expect(result.length).toBe(2); - - const [dataA, dataB] = result; - expect(dataA).toEqual(expect.arrayContaining(tableA)); - expect(dataA.length).toEqual(tableA.length); - expect(dataB).toEqual(expect.arrayContaining(tableB)); - expect(dataB.length).toEqual(tableB.length); - }); - - it('should return an empty array for 0 results', async () => { - const rawResult = database.exec(` - SELECT * FROM table_b - WHERE 1 = 0 - `); - - const result = parseMultiStatementSQLiteResult(rawResult); - expect(result).toStrictEqual([]); - }); - - it('should return exact row for specific query', async () => { - const rawResult = database.exec(` - SELECT * FROM table_b - WHERE idx = 1 - `); - - const result = parseMultiStatementSQLiteResult(rawResult); - expect(result.length).toBe(1); - - const [data] = result; - expect(data.length).toBe(1); - expect(data[0]).toStrictEqual(tableB[0]); - }); - - it('should return aggregated value', async () => { - const rawResult = database.exec(` - SELECT AVG(val) as average FROM table_a - `); - - const result = parseMultiStatementSQLiteResult(rawResult); - expect(result.length).toBe(1); - - const [data] = result; - expect(data.length).toBe(1); - expect(data[0]).toStrictEqual({ average: 42 }); - }); -}); diff --git a/web/database/utils/migrations.js b/web/database/utils/migrations.js deleted file mode 100644 --- a/web/database/utils/migrations.js +++ /dev/null @@ -1,17 +0,0 @@ -// @flow - -import type { SqliteDatabase } from 'sql.js'; - -import { createReportTable } from '../queries/report-queries.js'; - -const migrations: $ReadOnlyMap void> = - new Map([ - [ - 1, - (sqliteDb: SqliteDatabase) => { - createReportTable(sqliteDb); - }, - ], - ]); - -export { migrations };