diff --git a/web/database/queries/report-queries.js b/web/database/queries/report-queries.js new file mode 100644 --- /dev/null +++ b/web/database/queries/report-queries.js @@ -0,0 +1,72 @@ +// @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/report-queries.test.js b/web/database/queries/report-queries.test.js new file mode 100644 --- /dev/null +++ b/web/database/queries/report-queries.test.js @@ -0,0 +1,86 @@ +// @flow + +import initSqlJs from 'sql.js'; + +import { + createReportTable, + getAllReports, + removeAllReports, + removeReports, + updateReport, +} from './report-queries.js'; +import { parseMultiStatementSQLiteResult } from '../utils/db-utils.js'; + +describe('Report Store queries', () => { + let db; + + beforeAll(async () => { + const SQL = await initSqlJs(); + db = new SQL.Database(); + createReportTable(db); + }); + + beforeEach(() => { + db.exec(` + INSERT INTO reports VALUES ("1", "{report_content_1"); + INSERT INTO reports VALUES ("2", "{report_content_2}"); + `); + }); + + afterEach(() => { + db.exec(`DELETE FROM reports`); + }); + + it('should create table', () => { + const res = db.exec(`PRAGMA table_info(reports);`); + const [parsedRes] = parseMultiStatementSQLiteResult(res); + + expect(parsedRes).toHaveLength(2); + expect(parsedRes).toEqual( + expect.arrayContaining([ + expect.objectContaining({ + name: 'id', + type: 'TEXT', + }), + expect.objectContaining({ + name: 'report', + type: 'TEXT', + }), + ]), + ); + }); + + it('should return all reports', () => { + const reports = getAllReports(db); + expect(reports).toHaveLength(2); + }); + + it('should remove all reports', () => { + removeAllReports(db); + const reports = getAllReports(db); + expect(reports).toHaveLength(0); + }); + + it('should insert not existing report', () => { + const id = '3'; + const report = '{some_content}'; + updateReport(db, id, report); + + const reports = getAllReports(db); + expect(reports).toHaveLength(3); + expect(reports).toContainEqual({ id, report }); + }); + + it('should reports with given id', () => { + const id = '2'; + removeReports(db, [id]); + + const reports = getAllReports(db); + expect(reports).toHaveLength(1); + expect(reports).not.toContain( + expect.objectContaining({ + id, + }), + ); + }); +});