commit 20ad7162b5043e24c0f62d822cf7e491daf9157b
parent 6ddb8f06db93f142d7b3528bc6a130e1256a5f17
Author: triesap <137732411+triesap@users.noreply.github.com>
Date: Mon, 23 Sep 2024 20:55:33 +0000
client: update sqlite classes
Diffstat:
5 files changed, 472 insertions(+), 536 deletions(-)
diff --git a/client/src/capacitor/index.ts b/client/src/capacitor/index.ts
@@ -17,7 +17,7 @@ import { CapacitorClientNetwork } from "./network";
import { CapacitorClientPreferences } from "./preferences";
import { CapacitorClientSettings } from "./settings";
import { CapacitorClientShare } from "./share";
-import { CapacitorClientSQLite } from "./sql";
+import { CapacitorClientSQLite, type ICapacitorClientSQLiteUpgrade } from "./sqlite";
import { CapacitorClientWifi } from "./wifi";
import { CapacitorClientWindow } from "./window";
@@ -39,8 +39,14 @@ export class ClientCapacitor implements IClient {
private _window: IClientWindow = new CapacitorClientWindow();
private _ble: IClientBluetoothLe = new CapacitorClientBluetoothLe();
private _camera: IClientCamera = new CapacitorClientCamera();
- private _db: CapacitorClientSQLite = new CapacitorClientSQLite();
private _settings: CapacitorClientSettings = new CapacitorClientSettings();
+ private _db: CapacitorClientSQLite;
+
+ constructor(opts: {
+ sqlite_upgrade: ICapacitorClientSQLiteUpgrade[];
+ }) {
+ this._db = new CapacitorClientSQLite(opts.sqlite_upgrade);
+ }
public get nostr() {
return this._nostr;
diff --git a/client/src/capacitor/sql.ts b/client/src/capacitor/sql.ts
@@ -1,532 +0,0 @@
-import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection, type DBSQLiteValues, type capSQLiteChanges, type capSQLiteUpgradeOptions, type capSQLiteVersionUpgrade } from '@radroots/capacitor-sqlite';
-import { Capacitor } from '@capacitor/core';
-import { time_created_on, uuidv4, err_msg } from '@radroots/utils';
-import { type IModelsQueryParam, type IModelsQueryBindValue, type IModelsQueryBindValueTuple, type IModelsQueryBindValueOpt, models_initial_upgrade, parse_location_gcs_form_field_types, location_gcs_sort, type ILocationGcsGetList, type ILocationGcsGet, type ILocationGcsUpdate, type ILocationGcsQueryBindValues, type ILocationGcsQueryBindValuesKey, type ILocationGcsQueryBindValuesTuple, parse_location_gcs, parse_location_gcss, type LocationGcs, type LocationGcsFields, type LocationGcsFormFields, LocationGcsSchema, parse_trade_product_form_field_types, trade_product_sort, type ITradeProductGetList, type ITradeProductGet, type ITradeProductUpdate, type ITradeProductQueryBindValues, type ITradeProductQueryBindValuesKey, type ITradeProductQueryBindValuesTuple, parse_trade_product, parse_trade_products, type TradeProduct, type TradeProductFields, type TradeProductFormFields, TradeProductSchema} from "@radroots/models";
-
-const models_upgrades = [
- {
- toVersion: 1,
- statements: [
- ...models_initial_upgrade
- ]
- }
-];
-
-export type IISQLiteServiceDatabaseLog = { key: string, bind_values: IModelsQueryBindValueOpt[], query: string, e: any };
-export type IISQLiteServiceOpenDatabase = {
- platform: string;
- database: string;
- upgrade: capSQLiteVersionUpgrade[];
- version: number;
-};
-
-export type IISQLiteServiceMessage =
- | "*-location-gcs-geohash-unique"
- | "*-validate"
- | "*-result"
- | "*-fields"
- | "*-open"
- | "*-connect"
- | "*-db"
- | "*-exe-result"
- | "*-exe"
- | "*-sel-result"
- | "*-sel"
- | "*";
-
-class SQLiteVersionService {
- version_map: Map<string, number> = new Map();
-
- set_version(db_name: string, version: number) {
- this.version_map.set(db_name, version);
- };
- get_version(db_name: string): number | undefined {
- const version = this.version_map.get(db_name);
- return version;
- };
-};
-const sqlite_version_svc = new SQLiteVersionService();
-
-export class SQLiteService {
- private _platform = Capacitor.getPlatform();
- private _plugin = CapacitorSQLite;
- private _conn = new SQLiteConnection(CapacitorSQLite);
- private _db_version_dict: Map<string, number> = new Map();
- private _logs: IISQLiteServiceDatabaseLog[] = [];
-
- public get logs() {
- return this._logs;
- }
-
- public get platform() {
- return this._platform;
- }
- public async init_web_store(): Promise<void> {
- try {
- await this._conn.initWebStore();
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.init_web_store: ${error}`);
- }
- }
- public async add_upgrade(options: capSQLiteUpgradeOptions): Promise<void> {
- try {
- await this._plugin.addUpgradeStatement(options);
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.add_upgrade: ${error}`);
- }
- }
- public async open_db(db_name: string, loadToVersion: number, read_only: boolean, encryption_passphrase?: string): Promise<SQLiteDBConnection> {
- this._db_version_dict.set(db_name, loadToVersion);
- const mode = encryption_passphrase ? "secret" : "no-encryption";
- try {
- let db: SQLiteDBConnection;
- const retCC = (await this._conn.checkConnectionsConsistency()).result;
- const isConn = (await this._conn.isConnection(db_name, read_only)).result;
- if (retCC && isConn) {
- db = await this._conn.retrieveConnection(db_name, read_only);
- } else {
- db = await this._conn
- .createConnection(db_name, !!encryption_passphrase, mode, loadToVersion, read_only);
- };
- await db.open();
- const res = (await db.isDBOpen()).result!;
- if (!res) {
- throw new Error('SQLiteService.open_db: database not opened')
- }
- return db;
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.open_db: ${error}`);
- }
- }
- public async close_db(db_name: string, read_only: boolean): Promise<void> {
- try {
- const isConn = (await this._conn.isConnection(db_name, read_only)).result;
- if (isConn) {
- await this._conn.closeConnection(db_name, read_only);
- }
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.close_db: ${error}`);
- }
- }
- public async save_to_store(db_name: string): Promise<void> {
- try {
- await this._conn.saveToStore(db_name);
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.save_to_store: ${error}`);
- }
- }
- public async save_to_disk(db_name: string): Promise<void> {
- try {
- await this._conn.saveToLocalDisk(db_name);
- return;
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.save_to_disk: ${error}`);
- }
- }
- public async is_connected(db_name: string, read_only: boolean): Promise<boolean> {
- try {
- const is_connected = (await this._conn.isConnection(db_name, read_only)).result;
- if (is_connected !== undefined) {
- return is_connected;
- } else {
- throw new Error(`SQLiteService.is_connected undefined`);
- }
-
- } catch (e) {
- const { error } = err_msg(e);
- throw new Error(`SQLiteService.is_connected: ${error}`);
- }
- }
-}
-const sqlite_svc = new SQLiteService();
-
-export class CapacitorClientSQLite {
- private _platform = sqlite_svc.platform;
- private _db_name: string | null = null;
- private _db: SQLiteDBConnection | null = null;
- private _upgrade = models_upgrades;
- private _version = models_upgrades[models_upgrades.length - 1].toVersion;
-
- public get logs() {
- return sqlite_svc.logs;
- }
-
- //
- // private
- private append_logs(log_key: IISQLiteServiceMessage, bind_values: any, query: string, e: any): IISQLiteServiceMessage {
- sqlite_svc.logs.push({
- key: "database-" + log_key,
- bind_values,
- query,
- e,
- })
- return log_key;
- }
-
- private filter_bind_value_fields(fields: IModelsQueryBindValueTuple[]): IModelsQueryBindValueTuple[] {
- return fields.filter(([_, v]) => !!v);
- }
-
- private async execute(query: string, bv_o?: IModelsQueryBindValueOpt): Promise<capSQLiteChanges | IISQLiteServiceMessage> {
- try {
- if (!this._db) return "*-db";
- const result = await this._db.run(query, bv_o ? bv_o : undefined);
- if (sqlite_svc.platform === "web" && this._db_name) await sqlite_svc.save_to_store(this._db_name);
- if (result) return result;
- return this.append_logs("*-exe-result", bv_o, query, result);
- } catch (e) {
- const { error } = err_msg(e, "execute");
- if (String(e).includes("UNIQUE constraint failed: location_gcs.geohash")) return "*-location-gcs-geohash-unique";
- return this.append_logs("*-exe", bv_o, query, error);
- };
- };
-
- private async select(query: string, bv_o?: IModelsQueryBindValueOpt): Promise<DBSQLiteValues | IISQLiteServiceMessage> {
- try {
- if (!this._db) return "*-db";
- const result = await this._db.query(query, bv_o ? bv_o : undefined);
- if (result) return result;
- return this.append_logs("*-sel-result", bv_o, query, result);
- } catch (e) {
- const { error } = err_msg(e, "select");
- return this.append_logs("*-sel", bv_o, query, error);
- };
- };
-
- private async open(opts: IISQLiteServiceOpenDatabase): Promise<undefined | IISQLiteServiceMessage> {
- try {
- if (this._platform === "web") await sqlite_svc.init_web_store();
- await sqlite_svc.add_upgrade({
- database: opts.database,
- upgrade: opts.upgrade
- });
- const db = await sqlite_svc.open_db(opts.database, opts.version, false);
- sqlite_version_svc.set_version(opts.database, opts.version);
- if (!db) return "*-db";
- if (opts.platform === "web") await sqlite_svc.save_to_store(opts.database);
- this._db = db;
- this._db_name = opts.database;
- return this.append_logs("*-open", [], "database opened", [new Date().toISOString()]);
- } catch (e) {
- const { error } = err_msg(e, "open");
- return this.append_logs("*-open", [], "catch", error);
- };
- }
-
- public async connect(database: string): Promise<true | IISQLiteServiceMessage> {
- try {
- this._db_name = database;
- await this.open({
- platform: this._platform,
- database,
- upgrade: this._upgrade,
- version: this._version,
- }).then(async () => {
- if (this._platform === "web") await sqlite_svc.save_to_store(database);
- });
- return true;
- } catch (e) {
- const { error } = err_msg(e, "connect");
- return this.append_logs("*-connect", [], "catch", error);
- };
- }
-
- private location_gcs_add_validate(opts: LocationGcsFormFields): LocationGcsFields | string[] {
- const opts_filtered = Object.entries(opts).reduce((acc: Record<string, (string | number)>, [key, value]) => {
- if (!!value) {
- switch (parse_location_gcs_form_field_types(key)) {
- case "string":
- acc[key] = value;
- break;
- case "number":
- acc[key] = Number(value);
- break;
- }
- };
- return acc;
- }, {});
- const location_gcs_v = LocationGcsSchema.safeParse(opts_filtered);
- if (!location_gcs_v.success) return location_gcs_v.error.issues.map(i => i.message);
- else return {
- ...location_gcs_v.data,
- };
- };
-
- public async location_gcs_add(opts: LocationGcsFormFields): Promise<{ id: string; } | string[] | IISQLiteServiceMessage> {
- const optsv = this.location_gcs_add_validate(opts);
- if (Array.isArray(optsv)) return optsv;
- const fields = Object.entries(optsv);
- if (!fields.length) return "*-fields";
- const id = uuidv4();
- const bind_values_tup: IModelsQueryBindValueTuple[] = [
- ["id", id],
- ["created_at", time_created_on()]
- ];
- for (const field of this.filter_bind_value_fields(fields)) bind_values_tup.push(field);
- const bind_values = bind_values_tup.map(([_, v]) => v);
- const query = `INSERT INTO location_gcs (${bind_values_tup.map(([k]) => k).join(", ")}) VALUES (${bind_values_tup.map((_, num) => `$${1 + num}`).join(", ")});`;
- try {
- const result = await this.execute(query, bind_values);
- if (typeof result !== "string" && typeof result.changes?.changes === "number" && result.changes.changes > 0) return { id };
- else if (typeof result === "string") return result;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", bind_values, query, ["location_gcs_add", e]);
- };
- };
-
- private location_gcs_query_bind_values = (opts: ILocationGcsQueryBindValues): ILocationGcsQueryBindValuesTuple => {
- if ("id" in opts) return ["id", opts.id];
- else return ["geohash", opts.geohash];
- };
-
- private location_gcs_get_query_list = (opts: ILocationGcsGetList): IModelsQueryParam => {
- const sort = location_gcs_sort[opts.sort || "newest"];
- let query = "";
- let bind_values = null;
- if (opts.list[0] === "all") {
- query = `SELECT * FROM location_gcs ORDER BY ${sort};`;
- }
- if (!query) throw new Error("Error: Missing query (location_gcs_get_query_list)")
- return {
- query,
- bind_values
- };
- };
-
- private location_gcs_get_parse_opts = (opts: ILocationGcsGet): IModelsQueryParam => {
- if ("list" in opts) return this.location_gcs_get_query_list(opts);
- else {
- const bv_tup = this.location_gcs_query_bind_values(opts);
- return {
- query: `SELECT * FROM location_gcs WHERE ${bv_tup[0]} = $1;`,
- bind_values: [bv_tup[1]]
- };
- };
- };
-
- public async location_gcs_get(opts: ILocationGcsGet): Promise<LocationGcs[] | IISQLiteServiceMessage> {
- const { query, bind_values } = this.location_gcs_get_parse_opts(opts);
- try {
- const response = await this.select(query, bind_values);
- if (typeof response === "string") return response;
- else {
- const result = parse_location_gcss(response);
- if (result) return result;
- }
- return "*-result";
- } catch (e) {
- return this.append_logs("*", opts, query, ["location_gcs_get", e]);
- };
- };
-
- public async location_gcs_delete(opts: ILocationGcsQueryBindValues): Promise<true | IISQLiteServiceMessage> {
- const bv_tup = this.location_gcs_query_bind_values(opts);
- const bind_values = [bv_tup[1]];
- const query = `DELETE FROM location_gcs WHERE ${bv_tup[0]} = $1;`;
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") return response;
- else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", [], query, ["location_gcs_delete", e]);
- };
- };
-
- public async location_gcs_update(opts: ILocationGcsUpdate): Promise<true | string[] | IISQLiteServiceMessage> {
- const optsv = this.location_gcs_add_validate(opts.fields);
- if (Array.isArray(optsv)) return optsv;
- const fields = this.filter_bind_value_fields(Object.entries(optsv));
- if (!fields.length) return "*-fields";
- const bv_tup = this.location_gcs_query_bind_values(opts.on);
- const bind_values = [bv_tup[1], ...fields.map(([_, v]) => v)];
- const query = `UPDATE location_gcs SET ${fields.map(([k], num) => `${k} = $${1 + num}`).join(", ")} WHERE ${bv_tup[0]} = $1;`;
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") return response;
- else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", [], query, ["location_gcs_update", e]);
- };
- };
-
- private trade_product_add_validate(opts: TradeProductFormFields): TradeProductFields | string[] {
- const opts_filtered = Object.entries(opts).reduce((acc: Record<string, (string | number)>, [key, value]) => {
- if (!!value) {
- switch (parse_trade_product_form_field_types(key)) {
- case "string":
- acc[key] = value;
- break;
- case "number":
- acc[key] = Number(value);
- break;
- }
- };
- return acc;
- }, {});
- const trade_product_v = TradeProductSchema.safeParse(opts_filtered);
- if (!trade_product_v.success) return trade_product_v.error.issues.map(i => i.message);
- else return {
- ...trade_product_v.data,
- };
- };
-
- public async trade_product_add(opts: TradeProductFormFields): Promise<{ id: string; } | string[] | IISQLiteServiceMessage> {
- const optsv = this.trade_product_add_validate(opts);
- if (Array.isArray(optsv)) return optsv;
- const fields = Object.entries(optsv);
- if (!fields.length) return "*-fields";
- const id = uuidv4();
- const bind_values_tup: IModelsQueryBindValueTuple[] = [
- ["id", id],
- ["created_at", time_created_on()]
- ];
- for (const field of this.filter_bind_value_fields(fields)) bind_values_tup.push(field);
- const bind_values = bind_values_tup.map(([_, v]) => v);
- const query = `INSERT INTO trade_product (${bind_values_tup.map(([k]) => k).join(", ")}) VALUES (${bind_values_tup.map((_, num) => `$${1 + num}`).join(", ")});`;
- try {
- const result = await this.execute(query, bind_values);
- if (typeof result !== "string" && typeof result.changes?.changes === "number" && result.changes.changes > 0) return { id };
- else if (typeof result === "string") return result;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", bind_values, query, ["trade_product_add", e]);
- };
- };
-
- private trade_product_query_bind_values = (opts: ITradeProductQueryBindValues): ITradeProductQueryBindValuesTuple => {
- if ("id" in opts) return ["id", opts.id];
- else return ["url", opts.url];
- };
-
- private trade_product_get_query_list = (opts: ITradeProductGetList): IModelsQueryParam => {
- const sort = trade_product_sort[opts.sort || "newest"];
- let query = "";
- let bind_values = null;
- if (opts.list[0] === "all") {
- query = `SELECT * FROM trade_product ORDER BY ${sort};`;
- }
- if (!query) throw new Error("Error: Missing query (trade_product_get_query_list)")
- return {
- query,
- bind_values
- };
- };
-
- private trade_product_get_parse_opts = (opts: ITradeProductGet): IModelsQueryParam => {
- if ("list" in opts) return this.trade_product_get_query_list(opts);
- else {
- const bv_tup = this.trade_product_query_bind_values(opts);
- return {
- query: `SELECT * FROM trade_product WHERE ${bv_tup[0]} = $1;`,
- bind_values: [bv_tup[1]]
- };
- };
- };
-
- public async trade_product_get(opts: ITradeProductGet): Promise<TradeProduct[] | IISQLiteServiceMessage> {
- const { query, bind_values } = this.trade_product_get_parse_opts(opts);
- try {
- const response = await this.select(query, bind_values);
- if (typeof response === "string") return response;
- else {
- const result = parse_trade_products(response);
- if (result) return result;
- }
- return "*-result";
- } catch (e) {
- return this.append_logs("*", opts, query, ["trade_product_get", e]);
- };
- };
-
- public async trade_product_delete(opts: ITradeProductQueryBindValues): Promise<true | IISQLiteServiceMessage> {
- const bv_tup = this.trade_product_query_bind_values(opts);
- const bind_values = [bv_tup[1]];
- const query = `DELETE FROM trade_product WHERE ${bv_tup[0]} = $1;`;
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") return response;
- else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", [], query, ["trade_product_delete", e]);
- };
- };
-
- public async trade_product_update(opts: ITradeProductUpdate): Promise<true | string[] | IISQLiteServiceMessage> {
- const optsv = this.trade_product_add_validate(opts.fields);
- if (Array.isArray(optsv)) return optsv;
- const fields = this.filter_bind_value_fields(Object.entries(optsv));
- if (!fields.length) return "*-fields";
- const bv_tup = this.trade_product_query_bind_values(opts.on);
- const bind_values = [bv_tup[1], ...fields.map(([_, v]) => v)];
- const query = `UPDATE trade_product SET ${fields.map(([k], num) => `${k} = $${1 + num}`).join(", ")} WHERE ${bv_tup[0]} = $1;`;
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") return response;
- else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
- return "*-result";
- } catch (e) {
- return this.append_logs("*", [], query, ["trade_product_update", e]);
- };
- };
-
- public async get_trade_product_location(): Promise<any[] | IISQLiteServiceMessage> {
- const bind_values = undefined;
- const query = "SELECT * FROM trade_product_location;";
- try {
- const response = await this.select(query, bind_values);
- if (typeof response === "string") {
- return response;
- } else if (response && Array.isArray(response.values)) {
- return response.values;
- }
- return "*-result";
- } catch (e) {
- const { error } = err_msg(e, "connect");
- return this.append_logs("*", bind_values, query, ["get_trade_product_location", error]);
- };
- };
-
- public async set_trade_product_location(opts: { trade_product_id: string; location_gcs_id: string }): Promise<true | IISQLiteServiceMessage> {
- const bind_values = [opts.trade_product_id, opts.location_gcs_id];
- const query = "INSERT INTO trade_product_location (tb_tploc_0, tb_tploc_1) VALUES ($1, $2);";
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") {
- return response;
- } else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) {
- return true;
- }
- return "*-result";
- } catch (e) {
- const { error } = err_msg(e, "connect");
- return this.append_logs("*", bind_values, query, ["set_trade_product_location", error]);
- };
- };
-
- public async unset_trade_product_location(opts: { trade_product_id: string; location_gcs_id: string; }): Promise<true | IISQLiteServiceMessage> {
- const bind_values = [opts.trade_product_id, opts.location_gcs_id];
- const query = "DELETE FROM trade_product_location WHERE tb_tploc_0 = $1 AND tb_tploc_1 = $2;";
- try {
- const response = await this.execute(query, bind_values);
- if (typeof response === "string") {
- return response;
- } else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) {
- return true;
- }
- return "*-result";
- } catch (e) {
- const { error } = err_msg(e, "connect");
- return this.append_logs("*", bind_values, query, ["unset_trade_product_location", error]);
- };
- };
-};
-\ No newline at end of file
diff --git a/client/src/capacitor/sqlite.ts b/client/src/capacitor/sqlite.ts
@@ -0,0 +1,340 @@
+import { type capSQLiteChanges, type DBSQLiteValues, SQLiteDBConnection } from "@radroots/capacitor-sqlite";
+import { type IModelsQueryValue, type IModelsQueryParam, type IModelsQueryBindValue, type IModelsQueryBindValueTuple, type IModelsQueryBindValueOpt, parse_location_gcs_form_fields, location_gcs_sort, type ILocationGcsGetList, type ILocationGcsGet, type ILocationGcsUpdate, type ILocationGcsQueryBindValues, type ILocationGcsQueryBindValuesKey, type ILocationGcsQueryBindValuesTuple, parse_location_gcs, parse_location_gcs_list, type LocationGcs, type LocationGcsFields, type LocationGcsFormFields, LocationGcsSchema, parse_trade_product_form_fields, trade_product_sort, type ITradeProductGetList, type ITradeProductGet, type ITradeProductUpdate, type ITradeProductQueryBindValues, type ITradeProductQueryBindValuesKey, type ITradeProductQueryBindValuesTuple, parse_trade_product, parse_trade_product_list, type TradeProduct, type TradeProductFields, type TradeProductFormFields, TradeProductSchema} from "@radroots/models";
+import { err_msg, time_created_on, uuidv4 } from "@radroots/utils";
+import { sqlite_svc, sqlite_version_svc, type IISQLiteServiceOpenDatabase } from "./sqlite_lib";
+
+export type ICapacitorClientSQLiteUpgrade = { toVersion: number; statements: string[]; };
+export type ICapacitorClientSQLiteMessage =
+ | "*-location-gcs-geohash-unique"
+ | "*-validate"
+ | "*-result"
+ | "*-fields"
+ | "*-open"
+ | "*-connect"
+ | "*-db"
+ | "*-exe-result"
+ | "*-exe"
+ | "*-sel-result"
+ | "*-sel"
+ | "*";
+
+export class CapacitorClientSQLite {
+ private _platform = sqlite_svc.platform;
+ private _db_name: string | null = null;
+ private _db: SQLiteDBConnection | null = null;
+ private _upgrade: ICapacitorClientSQLiteUpgrade[];
+ private _version: number;
+
+ constructor(upgrade: ICapacitorClientSQLiteUpgrade[]) {
+ this._upgrade = upgrade;
+ this._version = upgrade[upgrade.length - 1].toVersion;
+ }
+
+ private append_logs(log_key: ICapacitorClientSQLiteMessage, bind_values: any, query: string, e: any): ICapacitorClientSQLiteMessage {
+ sqlite_svc.logs.push({
+ key: "database-" + log_key,
+ bind_values,
+ query,
+ e,
+ })
+ return log_key;
+ }
+
+ private filter_bind_value_fields(fields: IModelsQueryBindValueTuple[]): IModelsQueryBindValueTuple[] {
+ return fields.filter(([_, v]) => !!v);
+ }
+
+ private async execute(query: string, bv_o?: IModelsQueryBindValueOpt): Promise<capSQLiteChanges | ICapacitorClientSQLiteMessage> {
+ try {
+ if (!this._db) return "*-db";
+ const result = await this._db.run(query, bv_o ? bv_o : undefined);
+ if (sqlite_svc.platform === "web" && this._db_name) await sqlite_svc.save_to_store(this._db_name);
+ if (result) return result;
+ return this.append_logs("*-exe-result", bv_o, query, result);
+ } catch (e) {
+ const { error } = err_msg(e, "execute");
+ if (String(e).includes("UNIQUE constraint failed: location_gcs.geohash")) return "*-location-gcs-geohash-unique";
+ return this.append_logs("*-exe", bv_o, query, error);
+ };
+ }
+
+ private async select(query: string, bv_o?: IModelsQueryBindValueOpt): Promise<DBSQLiteValues | ICapacitorClientSQLiteMessage> {
+ try {
+ if (!this._db) return "*-db";
+ const result = await this._db.query(query, bv_o ? bv_o : undefined);
+ if (result) return result;
+ return this.append_logs("*-sel-result", bv_o, query, result);
+ } catch (e) {
+ const { error } = err_msg(e, "select");
+ return this.append_logs("*-sel", bv_o, query, error);
+ };
+ }
+
+ private async open(opts: IISQLiteServiceOpenDatabase): Promise<undefined | ICapacitorClientSQLiteMessage> {
+ try {
+ if (this._platform === "web") await sqlite_svc.init_web_store();
+ await sqlite_svc.add_upgrade({
+ database: opts.database,
+ upgrade: opts.upgrade
+ });
+ const db = await sqlite_svc.open_db(opts.database, opts.version, false);
+ sqlite_version_svc.set_version(opts.database, opts.version);
+ if (!db) return "*-db";
+ if (opts.platform === "web") await sqlite_svc.save_to_store(opts.database);
+ this._db = db;
+ this._db_name = opts.database;
+ return this.append_logs("*-open", [], "database opened", [new Date().toISOString()]);
+ } catch (e) {
+ const { error } = err_msg(e, "open");
+ return this.append_logs("*-open", [], "catch", error);
+ };
+ }
+
+ public get logs() {
+ return sqlite_svc.logs;
+ }
+
+ public async connect(database: string): Promise<true | ICapacitorClientSQLiteMessage> {
+ try {
+ this._db_name = database;
+ await this.open({
+ platform: this._platform,
+ database,
+ upgrade: this._upgrade,
+ version: this._version,
+ }).then(async () => {
+ if (this._platform === "web") await sqlite_svc.save_to_store(database);
+ });
+ return true;
+ } catch (e) {
+ const { error } = err_msg(e, "connect");
+ return this.append_logs("*-connect", [], "catch", error);
+ };
+ }
+
+ private location_gcs_add_validate(fields: LocationGcsFormFields): LocationGcsFields | string[] {
+ const fields_record = Object.entries(fields).filter(([_, v]) => !!v).reduce((acc: Record<string, IModelsQueryValue>, i) => {
+ const [key, val] = parse_location_gcs_form_fields(i);
+ acc[key] = val;
+ return acc;
+ }, {});
+ const fields_schema = LocationGcsSchema.safeParse(fields_record);
+ if (!fields_schema.success) return fields_schema.error.issues.map(i => i.message);
+ else return {
+ ...fields_schema.data,
+ };
+ }
+
+ public async location_gcs_add(opts: LocationGcsFormFields): Promise<{ id: string; } | string[] | ICapacitorClientSQLiteMessage> {
+ const opts_v = this.location_gcs_add_validate(opts);
+ if (Array.isArray(opts_v)) return opts_v;
+ const fields = Object.entries(opts_v);
+ if (!fields.length) return "*-fields";
+ const id = uuidv4();
+ const bind_values_tup: IModelsQueryBindValueTuple[] = [
+ ["id", id],
+ ["created_at", time_created_on()]
+ ];
+ for (const field of this.filter_bind_value_fields(fields)) bind_values_tup.push(field);
+ const bind_values = bind_values_tup.map(([_, v]) => v);
+ const query = `INSERT INTO location_gcs (${bind_values_tup.map(([k]) => k).join(", ")}) VALUES (${bind_values_tup.map((_, num) => `$${1 + num}`).join(", ")});`;
+ try {
+ const result = await this.execute(query, bind_values);
+ if (typeof result !== "string" && typeof result.changes?.changes === "number" && result.changes.changes > 0) return { id, };
+ else if (typeof result === "string") return result;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", bind_values, query, ["location_gcs_add", e]);
+ };
+ }
+
+ private location_gcs_query_bind_values = (opts: ILocationGcsQueryBindValues): ILocationGcsQueryBindValuesTuple => {
+ if ("id" in opts) return ["id", opts.id];
+ else return ["geohash", opts.geohash];
+ }
+
+ private location_gcs_get_query_list = (opts: ILocationGcsGetList): IModelsQueryParam => {
+ const sort = location_gcs_sort[opts.sort || "newest"];
+ let query = "";
+ let bind_values = null;
+ if (opts.list[0] === "all") {
+ query = `SELECT * FROM location_gcs ORDER BY ${sort};`;
+ }
+ if (!query) throw new Error("Error: Missing query (location_gcs_get_query_list)")
+ return {
+ query,
+ bind_values
+ };
+ }
+
+ private location_gcs_get_parse_opts = (opts: ILocationGcsGet): IModelsQueryParam => {
+ if ("list" in opts) return this.location_gcs_get_query_list(opts);
+ else {
+ const bv_tup = this.location_gcs_query_bind_values(opts);
+ return {
+ query: `SELECT * FROM location_gcs WHERE ${bv_tup[0]} = $1;`,
+ bind_values: [bv_tup[1]]
+ };
+ };
+ }
+
+ public async location_gcs_get(opts: ILocationGcsGet): Promise<LocationGcs[] | ICapacitorClientSQLiteMessage> {
+ const { query, bind_values } = this.location_gcs_get_parse_opts(opts);
+ try {
+ const response = await this.select(query, bind_values);
+ if (typeof response === "string") return response;
+ else {
+ const result = parse_location_gcs_list(response);
+ if (result) return result;
+ }
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", opts, query, ["location_gcs_get", e]);
+ };
+ }
+
+ public async location_gcs_delete(opts: ILocationGcsQueryBindValues): Promise<true | ICapacitorClientSQLiteMessage> {
+ const bv_tup = this.location_gcs_query_bind_values(opts);
+ const bind_values = [bv_tup[1]];
+ const query = `DELETE FROM location_gcs WHERE ${bv_tup[0]} = $1;`;
+ try {
+ const response = await this.execute(query, bind_values);
+ if (typeof response === "string") return response;
+ else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", [], query, ["location_gcs_delete", e]);
+ };
+ }
+
+ public async location_gcs_update(opts: ILocationGcsUpdate): Promise<true | string[] | ICapacitorClientSQLiteMessage> {
+ const opts_v = this.location_gcs_add_validate(opts.fields);
+ if (Array.isArray(opts_v)) return opts_v;
+ const fields = this.filter_bind_value_fields(Object.entries(opts_v));
+ if (!fields.length) return "*-fields";
+ const bv_tup = this.location_gcs_query_bind_values(opts.on);
+ const bind_values = [bv_tup[1], ...fields.map(([_, v]) => v)];
+ const query = `UPDATE location_gcs SET ${fields.map(([k], num) => `${k} = $${1 + num}`).join(", ")} WHERE ${bv_tup[0]} = $1;`;
+ try {
+ const response = await this.execute(query, bind_values);
+ if (typeof response === "string") return response;
+ else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", [], query, ["location_gcs_update", e]);
+ };
+ }
+
+ private trade_product_add_validate(fields: TradeProductFormFields): TradeProductFields | string[] {
+ const fields_record = Object.entries(fields).filter(([_, v]) => !!v).reduce((acc: Record<string, IModelsQueryValue>, i) => {
+ const [key, val] = parse_trade_product_form_fields(i);
+ acc[key] = val;
+ return acc;
+ }, {});
+ const fields_schema = TradeProductSchema.safeParse(fields_record);
+ if (!fields_schema.success) return fields_schema.error.issues.map(i => i.message);
+ else return {
+ ...fields_schema.data,
+ };
+ }
+
+ public async trade_product_add(opts: TradeProductFormFields): Promise<{ id: string; } | string[] | ICapacitorClientSQLiteMessage> {
+ const opts_v = this.trade_product_add_validate(opts);
+ if (Array.isArray(opts_v)) return opts_v;
+ const fields = Object.entries(opts_v);
+ if (!fields.length) return "*-fields";
+ const id = uuidv4();
+ const bind_values_tup: IModelsQueryBindValueTuple[] = [
+ ["id", id],
+ ["created_at", time_created_on()]
+ ];
+ for (const field of this.filter_bind_value_fields(fields)) bind_values_tup.push(field);
+ const bind_values = bind_values_tup.map(([_, v]) => v);
+ const query = `INSERT INTO trade_product (${bind_values_tup.map(([k]) => k).join(", ")}) VALUES (${bind_values_tup.map((_, num) => `$${1 + num}`).join(", ")});`;
+ try {
+ const result = await this.execute(query, bind_values);
+ if (typeof result !== "string" && typeof result.changes?.changes === "number" && result.changes.changes > 0) return { id, };
+ else if (typeof result === "string") return result;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", bind_values, query, ["trade_product_add", e]);
+ };
+ }
+
+ private trade_product_query_bind_values = (opts: ITradeProductQueryBindValues): ITradeProductQueryBindValuesTuple => {
+ if ("id" in opts) return ["id", opts.id];
+ else return ["url", opts.url];
+ }
+
+ private trade_product_get_query_list = (opts: ITradeProductGetList): IModelsQueryParam => {
+ const sort = trade_product_sort[opts.sort || "newest"];
+ let query = "";
+ let bind_values = null;
+ if (opts.list[0] === "all") {
+ query = `SELECT * FROM trade_product ORDER BY ${sort};`;
+ }
+ if (!query) throw new Error("Error: Missing query (trade_product_get_query_list)")
+ return {
+ query,
+ bind_values
+ };
+ }
+
+ private trade_product_get_parse_opts = (opts: ITradeProductGet): IModelsQueryParam => {
+ if ("list" in opts) return this.trade_product_get_query_list(opts);
+ else {
+ const bv_tup = this.trade_product_query_bind_values(opts);
+ return {
+ query: `SELECT * FROM trade_product WHERE ${bv_tup[0]} = $1;`,
+ bind_values: [bv_tup[1]]
+ };
+ };
+ }
+
+ public async trade_product_get(opts: ITradeProductGet): Promise<TradeProduct[] | ICapacitorClientSQLiteMessage> {
+ const { query, bind_values } = this.trade_product_get_parse_opts(opts);
+ try {
+ const response = await this.select(query, bind_values);
+ if (typeof response === "string") return response;
+ else {
+ const result = parse_trade_product_list(response);
+ if (result) return result;
+ }
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", opts, query, ["trade_product_get", e]);
+ };
+ }
+
+ public async trade_product_delete(opts: ITradeProductQueryBindValues): Promise<true | ICapacitorClientSQLiteMessage> {
+ const bv_tup = this.trade_product_query_bind_values(opts);
+ const bind_values = [bv_tup[1]];
+ const query = `DELETE FROM trade_product WHERE ${bv_tup[0]} = $1;`;
+ try {
+ const response = await this.execute(query, bind_values);
+ if (typeof response === "string") return response;
+ else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", [], query, ["trade_product_delete", e]);
+ };
+ }
+
+ public async trade_product_update(opts: ITradeProductUpdate): Promise<true | string[] | ICapacitorClientSQLiteMessage> {
+ const opts_v = this.trade_product_add_validate(opts.fields);
+ if (Array.isArray(opts_v)) return opts_v;
+ const fields = this.filter_bind_value_fields(Object.entries(opts_v));
+ if (!fields.length) return "*-fields";
+ const bv_tup = this.trade_product_query_bind_values(opts.on);
+ const bind_values = [bv_tup[1], ...fields.map(([_, v]) => v)];
+ const query = `UPDATE trade_product SET ${fields.map(([k], num) => `${k} = $${1 + num}`).join(", ")} WHERE ${bv_tup[0]} = $1;`;
+ try {
+ const response = await this.execute(query, bind_values);
+ if (typeof response === "string") return response;
+ else if (typeof response.changes?.changes === "number" && response.changes.changes > 0) return true;
+ return "*-result";
+ } catch (e) {
+ return this.append_logs("*", [], query, ["trade_product_update", e]);
+ };
+ }
+};
+\ No newline at end of file
diff --git a/client/src/capacitor/sqlite_lib.ts b/client/src/capacitor/sqlite_lib.ts
@@ -0,0 +1,121 @@
+import { Capacitor } from "@capacitor/core";
+import { CapacitorSQLite, type capSQLiteUpgradeOptions, type capSQLiteVersionUpgrade, SQLiteConnection, SQLiteDBConnection } from "@radroots/capacitor-sqlite";
+import type { IModelsQueryBindValueOpt } from "@radroots/models";
+import { err_msg } from "@radroots/utils";
+
+export type ISQLiteServiceDatabaseLog = { key: string, bind_values: IModelsQueryBindValueOpt[], query: string, e: any };
+export type IISQLiteServiceOpenDatabase = {
+ platform: string;
+ database: string;
+ upgrade: capSQLiteVersionUpgrade[];
+ version: number;
+};
+
+export class CapacitorClientSQLiteVersionService {
+ version_map: Map<string, number> = new Map();
+
+ set_version(db_name: string, version: number) {
+ this.version_map.set(db_name, version);
+ }
+
+ get_version(db_name: string): number | undefined {
+ const version = this.version_map.get(db_name);
+ return version;
+ }
+};
+
+export class CapacitorClientSQLiteService {
+ private _platform = Capacitor.getPlatform();
+ private _plugin = CapacitorSQLite;
+ private _conn = new SQLiteConnection(CapacitorSQLite);
+ private _db_version_dict: Map<string, number> = new Map();
+ private _logs: ISQLiteServiceDatabaseLog[] = [];
+
+ public get logs() {
+ return this._logs;
+ }
+
+ public get platform() {
+ return this._platform;
+ }
+
+ public async init_web_store(): Promise<void> {
+ try {
+ await this._conn.initWebStore();
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService init_web_store: ${error}`);
+ }
+ }
+
+ public async add_upgrade(options: capSQLiteUpgradeOptions): Promise<void> {
+ try {
+ await this._plugin.addUpgradeStatement(options);
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService add_upgrade: ${error}`);
+ }
+ }
+
+ public async open_db(db_name: string, loadToVersion: number, read_only: boolean, encryption_passphrase?: string): Promise<SQLiteDBConnection> {
+ this._db_version_dict.set(db_name, loadToVersion);
+ const mode = encryption_passphrase ? "secret" : "no-encryption";
+ try {
+ let db: SQLiteDBConnection;
+ const ret_cc = (await this._conn.checkConnectionsConsistency()).result;
+ const is_conn = (await this._conn.isConnection(db_name, read_only)).result;
+ if (ret_cc && is_conn) db = await this._conn.retrieveConnection(db_name, read_only);
+ else db = await this._conn
+ .createConnection(db_name, !!encryption_passphrase, mode, loadToVersion, read_only);
+ await db.open();
+ const res = (await db.isDBOpen()).result!;
+ if (!res) throw new Error('Error: CapacitorClientSQLiteService open_db: database not opened')
+ return db;
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService open_db: ${error}`);
+ }
+ }
+
+ public async close_db(db_name: string, read_only: boolean): Promise<void> {
+ try {
+ const is_conn = (await this._conn.isConnection(db_name, read_only)).result;
+ if (is_conn) await this._conn.closeConnection(db_name, read_only);
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService close_db: ${error}`);
+ }
+ }
+
+ public async save_to_store(db_name: string): Promise<void> {
+ try {
+ await this._conn.saveToStore(db_name);
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService save_to_store: ${error}`);
+ }
+ }
+
+ public async save_to_disk(db_name: string): Promise<void> {
+ try {
+ await this._conn.saveToLocalDisk(db_name);
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService save_to_disk: ${error}`);
+ }
+ }
+
+ public async is_conn(db_name: string, read_only: boolean): Promise<boolean> {
+ try {
+ const is_conn = (await this._conn.isConnection(db_name, read_only)).result;
+ if (is_conn !== undefined) return is_conn;
+ throw new Error(`Error: CapacitorClientSQLiteService is_conn undefined`);
+ } catch (e) {
+ const { error } = err_msg(e);
+ throw new Error(`Error: CapacitorClientSQLiteService is_conn: ${error}`);
+ }
+ }
+};
+
+export const sqlite_version_svc = new CapacitorClientSQLiteVersionService();
+export const sqlite_svc = new CapacitorClientSQLiteService();
+\ No newline at end of file
diff --git a/client/src/types.ts b/client/src/types.ts
@@ -4,7 +4,7 @@ import { type ScanResult } from '@radroots/capacitor-bluetooth-le';
import { IOSSettings, type AndroidSettings } from '@radroots/capacitor-native-settings';
import { type ConnectToWifiResult, type GetCurrentWifiResult, type PermissionStatus, type ScanWifiResult } from '@radroots/capacitor-wifi';
import { type ErrorResponse } from '@radroots/utils';
-import { CapacitorClientSQLite } from './capacitor/sql';
+import { CapacitorClientSQLite } from './capacitor/sqlite';
export type IClient = {
nostr: IClientNostr;
