Open
Description
Plugin version:
6.0.2
Platform(s):
Web
Current behavior:
I think the bug occurs when I call the .open() function on a SQLiteDBConnection.
Expected behavior:
No error.
Steps to reproduce:
- Install necessary dependencies.
- Create a db connection
- Open the connection
Related code:
Here is a class I use for managing the database.
import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection, DBSQLiteValues } from "@capacitor-community/sqlite";
import { SqlStatements } from "../lib/sqlStatements";
import { Utils } from "../lib/utils";
import { AppContent } from "../lib/types";
import {
TABLE_BPJ_JOURNAL,
TABLE_CPD,
TABLE_EVENTS,
TABLE_HOME_SLIDES,
TABLE_MEMBER_BENEFITS,
TABLE_MEMBER_DISCOUNTS,
TABLE_MISC,
TABLE_NEWS,
TABLE_PI_JOURNAL,
TABLE_POLLS,
} from "../lib/constants";
import { AppContentSchema } from "../lib/typeSchemas";
import { PreferencesService } from "./preferencesService";
const DATABASE_NAME = "cop_db";
const DATABASE_READ_ONLY = false;
const DATABASE_LATEST_SCHEMA_VERSION = 2;
class LocalDatabaseService {
#sqliteConnection = new SQLiteConnection(CapacitorSQLite);
async initDatabase(savedDatabaseSchemaVersion: number) {
try {
// Perform initial table creation IF the tables don't already exist
const dbConnection = await this.open();
await dbConnection.execute(SqlStatements.setupTables);
await this.updateDatabaseSchema(dbConnection, savedDatabaseSchemaVersion);
} catch (error) {
throw Utils.extractErrorMessage(error);
} finally {
await this.close();
}
}
// update database schema if required
async updateDatabaseSchema(dbConnection: SQLiteDBConnection, savedDatabaseSchemaVersion: number): Promise<void> {
if (savedDatabaseSchemaVersion < DATABASE_LATEST_SCHEMA_VERSION) {
await dbConnection.execute("ALTER TABLE news_articles ADD COLUMN appExclusive INTEGER;");
// add upgrade expressions here...
await PreferencesService.saveDatabaseSchemaVersion(DATABASE_LATEST_SCHEMA_VERSION);
}
}
async open(): Promise<SQLiteDBConnection> {
try {
let dbConnection: SQLiteDBConnection;
const connectionConsistency = (await this.#sqliteConnection.checkConnectionsConsistency()).result; // have no idea what "connection consistency" is but the library's example souce code had it so here we are
const isConnected = (await this.#sqliteConnection.isConnection(DATABASE_NAME, DATABASE_READ_ONLY)).result;
// Check if already connected just in case it wasn't closed properly
if (connectionConsistency && isConnected) {
console.log("retrieving existing db connection...");
dbConnection = await this.#sqliteConnection.retrieveConnection(DATABASE_NAME, DATABASE_READ_ONLY);
} else {
console.log("creating new db connection...");
dbConnection = await this.#sqliteConnection.createConnection(
DATABASE_NAME,
false,
"no-encryption",
1,
DATABASE_READ_ONLY
);
}
await dbConnection.open();
return dbConnection;
} catch (error) {
throw Utils.extractErrorMessage(error);
}
}
async close(): Promise<void> {
try {
const isConnected = (await this.#sqliteConnection.isConnection(DATABASE_NAME, DATABASE_READ_ONLY)).result;
if (isConnected) {
console.log("closing db connection...");
await this.#sqliteConnection.closeConnection(DATABASE_NAME, DATABASE_READ_ONLY);
}
} catch (error) {
throw Utils.extractErrorMessage(error);
}
}
async updateAllTables(appContent: AppContent): Promise<void> {
try {
const dbConnection = await this.open();
await this.updateTable(dbConnection, TABLE_EVENTS, appContent.events);
await this.updateTable(dbConnection, TABLE_CPD, appContent.cpdVideos);
await this.updateTable(dbConnection, TABLE_HOME_SLIDES, appContent.homeCarouselSlides);
await this.updateTable(dbConnection, TABLE_NEWS, appContent.newsArticles);
await this.updateTable(dbConnection, TABLE_MEMBER_BENEFITS, appContent.memberBenefits);
await this.updateTable(dbConnection, TABLE_MEMBER_DISCOUNTS, appContent.memberDiscounts);
await this.updateTable(dbConnection, TABLE_BPJ_JOURNAL, appContent.paramedicInsightEntries);
await this.updateTable(dbConnection, TABLE_PI_JOURNAL, appContent.bpjEntries);
await this.updateTable(dbConnection, TABLE_MISC, appContent.miscellaneousItems);
await this.updateTable(dbConnection, TABLE_POLLS, appContent.polls);
} catch (error) {
throw Utils.extractErrorMessage(error);
} finally {
await this.close();
}
}
async dropTables(): Promise<void> {
try {
const dbConnection = await this.open();
await dbConnection.query(`DROP TABLE ${TABLE_EVENTS};`);
await dbConnection.query(`DROP TABLE ${TABLE_CPD};`);
await dbConnection.query(`DROP TABLE ${TABLE_HOME_SLIDES};`);
await dbConnection.query(`DROP TABLE ${TABLE_NEWS};`);
await dbConnection.query(`DROP TABLE ${TABLE_MEMBER_BENEFITS};`);
await dbConnection.query(`DROP TABLE ${TABLE_MEMBER_DISCOUNTS};`);
await dbConnection.query(`DROP TABLE ${TABLE_BPJ_JOURNAL};`);
await dbConnection.query(`DROP TABLE ${TABLE_PI_JOURNAL};`);
await dbConnection.query(`DROP TABLE ${TABLE_MISC};`);
await dbConnection.query(`DROP TABLE ${TABLE_POLLS};`);
} catch (error) {
console.log("Failed to drop tables", error);
} finally {
await this.close();
}
}
async updateTable(dbConnection: SQLiteDBConnection, tableName: string, items: any[]): Promise<void> {
try {
for (const item of items) {
const keys = Object.keys(item);
const values = Object.values(item).map((it) => {
if (it === "" || it === undefined || it === null) {
// This value should only be set for non-boolean types (e.g. if a description field is not set in Airtable),
// because all booleans are given values in the mapServerDataToCustomTypes() function.
// In the local SQLite database, boolean types use INTEGER, and everything else uses TEXT, so this should be fine
return "";
}
if (typeof it === "object") {
return JSON.stringify(it);
}
return it;
});
// Construct the INSERT statement dynamically based on the keys and values
const insertQuery = `INSERT OR REPLACE INTO ${tableName} (${keys.join(", ")}) VALUES (${keys
.map(() => "?")
.join(", ")});`;
await dbConnection.query(insertQuery, values);
}
// await Utils.delay(2000);
// throw "custom error from updateDatabase";
} catch (error) {
throw Utils.extractErrorMessage(error);
}
}
async query(sql: string, values: string[]): Promise<DBSQLiteValues> {
try {
const dbConnection = await this.open();
return await dbConnection.query(sql, values);
} catch (error) {
throw Utils.extractErrorMessage(error);
} finally {
await this.close();
}
}
async updateBookmarkStatusForVideo(newValue: boolean, recordId: string): Promise<void> {
try {
const dbConnection = await this.open();
const statement = `UPDATE ${TABLE_CPD} SET isBookmarked = ? WHERE id = ?;`;
await dbConnection.query(statement, [newValue, recordId]);
} catch (error) {
throw Utils.extractErrorMessage(error);
} finally {
await this.close();
}
}
/**
* Retrieves all App Content records from the local database, checks that results are not empty, and that they are mapped to the correct types.
* @returns
*/
async getAppContent(): Promise<AppContent> {
try {
const dbConnection = await this.open();
const events = (await dbConnection.query(`SELECT * FROM ${TABLE_EVENTS};`, [])).values ?? [];
const cpdVideos = (await dbConnection.query(`SELECT * FROM ${TABLE_CPD};`, [])).values ?? [];
const homeCarouselSlides = (await dbConnection.query(`SELECT * FROM ${TABLE_HOME_SLIDES};`, [])).values ?? [];
const newsArticles = (await dbConnection.query(`SELECT * FROM ${TABLE_NEWS};`, [])).values ?? [];
const memberBenefits = (await dbConnection.query(`SELECT * FROM ${TABLE_MEMBER_BENEFITS};`, [])).values ?? [];
const memberDiscounts = (await dbConnection.query(`SELECT * FROM ${TABLE_MEMBER_DISCOUNTS};`, [])).values ?? [];
const paramedicInsightEntries = (await dbConnection.query(`SELECT * FROM ${TABLE_PI_JOURNAL};`, [])).values ?? [];
const bpjEntries = (await dbConnection.query(`SELECT * FROM ${TABLE_BPJ_JOURNAL};`, [])).values ?? [];
const miscellaneousItems = (await dbConnection.query(`SELECT * FROM ${TABLE_MISC};`, [])).values ?? [];
const polls = (await dbConnection.query(`SELECT * FROM ${TABLE_POLLS};`, [])).values ?? [];
// Parse data, converting JSON values and boolean values to the correct types
events.forEach((e) => {
e.isRegistered = e.isRegistered === 1;
});
cpdVideos.forEach((vid) => {
vid.presenters = JSON.parse(vid.presenters);
vid.smallScreenshot = JSON.parse(vid.smallScreenshot);
vid.largeScreenshot = JSON.parse(vid.largeScreenshot);
vid.isBookmarked = vid.isRegistered === 1;
vid.isFeatured = vid.isRegistered === 1;
vid.isSample = vid.isRegistered === 1;
vid.seen = vid.seen === 1;
});
homeCarouselSlides.forEach((slide) => {
slide.active = slide.active === 1;
slide.guestOnly = slide.guestOnly === 1;
});
polls.forEach((poll) => {
poll.question = JSON.parse(poll.question);
poll.responses = JSON.parse(poll.responses);
poll.active = poll.active === 1;
});
const appContent = {
events,
cpdVideos,
homeCarouselSlides,
newsArticles,
memberBenefits,
memberDiscounts,
paramedicInsightEntries,
bpjEntries,
miscellaneousItems,
polls,
};
// Validate data against schema. Will throw an error if validation fails
AppContentSchema.parse(appContent);
return appContent;
} catch (error) {
throw Utils.extractErrorMessage(error);
} finally {
await this.close();
}
}
}
// Export the only instance of LocalDatabaseService, acting as a 'singleton' for the app.
export default new LocalDatabaseService();Other information:
This package was working fine on web until I recently updated Capacitor to v6.
Capacitor doctor:
💊 Capacitor Doctor 💊
Latest Dependencies:
@capacitor/cli: 6.2.0
@capacitor/core: 6.2.0
@capacitor/android: 6.2.0
@capacitor/ios: 6.2.0
Installed Dependencies:
@capacitor/cli: 6.2.0
@capacitor/android: 6.2.0
@capacitor/core: 6.2.0
@capacitor/ios: 6.2.0
[success] iOS looking great! 👌
[success] Android looking great! 👌