Convert IndexedDB to SQLite

[chrisvire]

There are many features that have already been developed that are being shared between the SAB and DAB PWA. This is to do some work for the SAB app that is not shared with DAB, but needs to be done soon without anyone to work on it.

For storing annotations, the PWA used IndexedDB. It turns out that IndexedDB is not a stable place to store data long-term.

https://gist.github.com/pesterhazy/4de96193af89a6dd5ce682ce2adff49a

We need to convert the mechanism for storing data from IndexedDB to SQLite.

[chrisvire]

Here are some lnks:

SQLite Wasm wrapped in TypeScript

These articles have instructions for downloading the SQLite Wasm and integrating it into a SvelteKit application. I think it would be better to import SQLite Wasm from an NPM module.

• Creating an Offline-Ready SvelteKit Application Using SQLite (Part 1): Setting up the Database
• Creating an Offline-Ready SvelteKit Application Using SQLite (Part 2): Messaging and Client Tables
• Saving Data on the Client - Offline App with SvelteKit + SQLite (Part 3)
• Querying Offline Data - Offline App with SvelteKit + SQLite (Part 4)

Source for articles

It looks we need to use Origin Private File System or OPFS for short. This enables writing to the SQLite database.

You should take into account that there will be multiple apps published at the same origin and that we need to segment the data based on the package name in the configuration.

It looks like these writes should happen in a Web Worker so we will need to learn how to use those as well. The Scripture PWA currently uses a Web Worker for search.

[chrisvire]

Here is the code from the Android app (Java) to create the annotations table:

    private static final String ANNOTATIONS_TABLE = "annotations";


    private String getSqlForCreateAnnotationsTable() {
        return "CREATE TABLE " + ANNOTATIONS_TABLE + " (" +
               "    id TEXT PRIMARY KEY, " + // unique ID
               "    date DATETIME, " +       // date modified
               "    page TEXT, " +           // page reference (book collection, book, chapter)
               "    type TEXT, " +           // annotation type
               "    details TEXT, " +        // details as JSON string
               "    deleted INT DEFAULT 0" + // deleted
               ");";
    }

[chrisvire]

I have started the work of including SQLite WASM into the dependencies:

https://github.com/sillsdev/appbuilder-pwa/tree/feature/sqlite-wasm-dependency

[chrisvire]

Create a empty database based on schema from the IndexedDB code.