Crashes when querying all records from a database containing images (blobs)

[Sempervivum]

Describe the bug
My database file is 90MB large and contains 400 records, many of then containing an image blob. Running a query through all records makes the app crash, window disappears from screen. When running the same code on a smaller database (say 20 records, 4 images) there are no issues.

To Reproduce
Running this code makes the app crash:

            conn = new exports.SQLiteConnection(exports.CapacitorSQLite);
            console.log(conn);
            conn.createConnection(
                "Substances", false, "no-encryption", 3, false
            ).then(db => {
                console.log(db);
                db.open("Substances", false, 'no-encryption', 3, true).then(() => {
                    const sql = 'select * from `Substanzen - Substanzen`';
                    db.query(sql).then(
                        result => {
                            console.log(result);
                            document.querySelector('section.substances').innerHTML = '';
                            result.values.forEach(row => {
                                console.log(row.Name + ' - ' + row.Art);
                                // HTML aus Template lesen und die Platzhalter ersetzen
                                let html = document.getElementById('substance-tpl').innerHTML
                                    .replace(/{{name}}/g, row.Name)
                                    .replace(/{{art}}/g, row.Art)
                                    .replace(/{{beschreibung}}/g, row.Beschreibung)
                                if (row.Bild) {
                                    console.log((typeof new Blob([new Uint8Array(row.Bild).buffer])))
                                    html = html.replace(/{{img}}/g, URL.createObjectURL(new Blob([new Uint8Array(row.Bild).buffer])));
                                } else {
                                    html = html.replace(/{{img}}/g, '""');
                                }
                                // HTML in section mit den Substanzen am Ende eintragen
                                document.querySelector('section.substances')
                                    .insertAdjacentHTML('beforeend', html)
                            });
                            db.close("Substances");
                            return 'database read successfully';
                        });
                });
            });

Expected behavior
Query should run and complete without issues

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Smartphone (please complete the following information):

• Device: Samsung A50
• OS: Android 11
• Browser n. a.
• Version [e.g. 22]

Additional context
When running the query without images, i. e.
"select Name, Art, Beschreibung from Substanzen - Substanzen"
it's running without issues. I was able to create a running workaround by selecting the texts in a first step and then querying the images one by one:

            conn = new exports.SQLiteConnection(exports.CapacitorSQLite);
            console.log(conn);
            conn.createConnection(
                "Substances", false, "no-encryption", 3, false
            ).then(db => {
                console.log(db);
                db.open("Substances", false, 'no-encryption', 3, true).then(() => {
                    const sql = "select Name, Art, Beschreibung from `Substanzen - Substanzen`";
                    db.query(sql).then(result => {
                        console.log(result);
                        document.querySelector('section.substances').innerHTML = '';
                        result.values.forEach(row => {
                            console.log(row.Name + ' - ' + row.Art);
                            const sqlImg = "select Bild from `Substanzen - Substanzen` where Name='" + row.Name + "'";
                            db.query(sqlImg).then(result => {
                                const img = result.values[0].Bild;
                                if (img) {
                                    // HTML aus Template lesen und die Platzhalter ersetzen
                                    let html = document.getElementById('substance-tpl').innerHTML
                                        .replace(/{{name}}/g, row.Name)
                                        .replace(/{{art}}/g, row.Art)
                                        .replace(/{{beschreibung}}/g, row.Beschreibung)
                                    if (img) {
                                        console.log((typeof new Blob([new Uint8Array(img).buffer])))
                                        html = html.replace(/{{img}}/g,
                                            URL.createObjectURL(new Blob([new Uint8Array(img).buffer])));
                                    } else {
                                        html = html.replace(/{{img}}/g, '""');
                                    }
                                    // HTML in section mit den Substanzen am Ende eintragen
                                    document.querySelector('section.substances')
                                        .insertAdjacentHTML('beforeend', html)
                                }
                            });
                        });
                        // db.close("Substances");
                        return 'database read successfully';
                    });
                });
            });

[jepiqueau]

@Sempervivum have you tried to increase the page-size and cache-size with the PRAGMA

[Sempervivum]

Thanks for this hint. Unfortunately I'm new to Capacitor and the universe of Android and not familiar with these terms. I'm gonna do some research and give it a try.

[jepiqueau]

@Sempervivum you do it with an SQL statement that you execute
string stmt = "PRAGMA cache_size = 10000"
db.execute(stmt)
If 10000 is not enough increase it till is works

[Sempervivum]

Many thanks for this explanation. I see, this is related to SQLite, not Android. I'll give it a try ASAP.

[Sempervivum]

Many thanks!
I added it to my code but unfortunately the app keeps craching. I suspect the size of my mobile's memory is not sufficient.

            db.open("Substances", false, 'no-encryption', 3, true).then(() => {
                const stmt = "PRAGMA cache_size = 100000; PRAGMA page_size = 32768";
                db.execute(stmt).then((result) => {
                    console.log(result);
                    const sql = "select rowid, Name, Art, Beschreibung, Bild from `Substanzen - Substanzen`";
                    db.query(sql).then(result => {

I increased the values step by step.
The result of the execute statement says "changes: 0" which seems to be OK to me as no rows are affected.

[jepiqueau]

@Sempervivum sorry but there is nothing i can do more. If you select half of the rows did it work.

[Sempervivum]

Hi @jepiqueau
thanks for your support!
Don't worry, fortunately I got the workaround I mentioned in my initial posting. However this is fairly slow, therefore I referenced the images by the rowid:

            db.open("Substances", false, 'no-encryption', 3, true).then(() => {
                const sql = "select rowid, Name, Art, Beschreibung from `Substanzen - Substanzen`";
                db.query(sql).then(result => {
                    console.log(result);
                    ...
                    result.values.forEach(row => {
                            ...
                            const sqlImg = "select Bild from `Substanzen - Substanzen` where rowid='" + row.rowid + "'";
                            db.query(sqlImg).then(result => {

Unfortunately this didn't improve speed significantly.
I ended up in caching the images, i. e. storing them in the cache folder and reading them from there when the same database is read again. This improved speed a lot. I think we can go with this.
I do not understand why reading from the file system is that faster than reading from the database. Does Android cache these images in memory?
Best regards, Ulrich

[Antarian]

Remagine what you are doing. It's like every image is reencoded to SQLite blob instead of putting it in plain to the same filesystem.

For Images you should use Capacitor Filestorage, good example is basic tutorial on Ionic website. And if needed, like with PWA (using LocalStorage), sync them to server. This will also allow to save images to external storage which mobile users will prefer.

SQLite is by design database primarily for text data, it is not as robust as MySQL, Postgres or similar relational DBs. Not that I would suggest using any RDB for images or files. You can hardly optimize it if you running it on the same filesystem as your code.

[Sempervivum]

Hi @Antarian
Thanks for this info. Keeping the images in the database together with the texts was requested by the user: He prepares the database by use of DB Browser for SQLite. this makes handling easy: He can do this on his desktop computer and then transfer the file to the mobile. Additionally he requested that data should be available offline, therefore we didn't keep the database on a server, which would be an approach more common.

[Antarian]

Offline is not a problem with Capacitor FileStorage. But to make bridge between FileStorage and DB Browser would be a "fun" task.

Thing which can help here for displaying data back in html is pagination or so called loading data on scroll.
But that would require to separate html part out from the script you posted. If it is not already in your real code. Like document.getElementById should not be in the loop at all. There should be only one same ID per html site.

One function will only load data from DB (part of it based on pagination).
Another will process them in the loop and put them to predefined html template.
Third one will put it all to document/html.

If that would be still slow and you need some quicker pagination for SQLite here is something which should still work:
https://gist.github.com/ssokolow/262503
First limit in that query is how many lines to skip (50) second is how many to query (10).

I hope this will help at least a little.

[Antarian]

OK, sorry, you have only one ID used for loading template. I would load it before loop as htmlTpl and then just copy it to var html = htmlTpl. Which should work as JS has no object pointers. But that's up to preference.
Edit: Just tried it in my test code and it is assigned as reference/pointer. Will not work this way.

[Sempervivum]

Hi @Antarian
thanks for your support. I tested pagination and it works without crashes. I used the query you referenced above. However not significantly faster than my approach, reading only texts first and then reading the images one by one.

BTW: I tried some debugging and noticed in the java sources that there is something like a cursor behind the scenes: Getting one row by one which would make it possible to avoid putting complete result of the query into a huge array. I suspect that this might be another option to avoid crashes. Is that correct? However I can't find a cursor in the docs of the SQLite plugin for capacitor.

[jepiqueau]

@Sempervivum For me the best will be to use a select with a LIMIT to two times the number of images that you can see simultaneously in your application page and when you slide up or down in your page manage to make a new SELECT containing the next or previous images. Your solution is also valid.
I will close the issue