bug: cannot create tables with standard sql statements

[tobiasmuecksch]

Describe the bug
I'm trying to create tables with standard sql statements as described in the documentation.
But sqlite responds with {changes: {changes: 0}} and the table is not created. There is no exception thrown.

When I try to read or write to the table, an exception is thrown stating, that the table does not exist.

SelectSQL: queryAll: no such table: test

To Reproduce

   // example: database creation with standard SQLite statements 
    const ret = await sqlite.checkConnectionsConsistency();
    const isConn = (await sqlite.isConnection("db_tab3")).result;
    let db: SQLiteDBConnection
    if (ret.result && isConn) {
      db = await sqlite.retrieveConnection("db_tab3");
    } else {
      db = await sqlite.createConnection("db_tab3", false, "no-encryption", 1);
    }
    await db.open();
    const query = `
    CREATE TABLE IF NOT EXISTS test (
      id INTEGER PRIMARY KEY NOT NULL,
      name TEXT NOT NULL
    );
    `
    // res is `{changes: {changes: 0}}`
    const res = await db.execute(query);
    if(res.changes && res.changes.changes && res.changes.changes < 0) {
      throw new Error(`Error: execute failed`);
    }

    const statement = 'SELECT * FROM test;';
    const values = [];
    // The following line throws exception: "SelectSQL: queryAll: no such table: test"
    const result = await db.query(statement, values);
    console.log('select result', result.values);

    await sqlite.closeConnection("db_tab3");

Expected behavior
The table should be created.

Additional context
Tested in browser (Safari 15.4)

[Sampath-Lokuge]

This is how I have created a table and all are working fine for me: i.e. Ionic/Angular Native app

createExpensesTable(): Promise<capSQLiteChanges> {
    const query = `
          CREATE TABLE IF NOT EXISTS Expenses (
            ExpenseId TEXT PRIMARY KEY NOT NULL,
            Name TEXT NULL,
            Amount REAL NULL,
            Date INTEGER NULL,
            VatId TEXT NULL,
            ExpenseTypeId TEXT NULL,
            Image BLOB NULL,
            ExpenseTemplateId TEXT NULL,
            CreatedDate INTEGER NOT NULL,
            last_modified INTEGER NOT NULL,
            UserId TEXT NOT NULL,
            FOREIGN KEY(VatId) REFERENCES Vats(VatId),
            FOREIGN KEY(ExpenseTypeId) REFERENCES ExpenseTypes(ExpenseTypeId),
            FOREIGN KEY(ExpenseTemplateId) REFERENCES ExpenseTemplates(ExpenseTemplateId)
          );`;

    return this.sqliteDbConnection.execute(query);
  }

this.sqliteDbConnection -

This is my SQLiteDBConnection.

[jepiqueau]

@tobiasmuecksch Are you using the Web part of the plugin?

[tobiasmuecksch]

@jepiqueau Yes. With jeep-sqlite and so on.

[jepiqueau]

@tobiasmuecksch Can you share your project on github. As your query statement contains only the table schema it is correct that the returned res is {changes: {changes: 0}} if its failed it would have been {changes: {changes: -1}}.
if you do the following after executing the query

            const res = await db.getTableList();
            console.log(`&&&& getTableList res ${JSON.stringify(res)}`)

it should return

[log] - &&&& getTableList res {"values":["test"]}

[tobiasmuecksch]

@jepiqueau I'll create a demo specifically for this problem in the next hours.

[jepiqueau]

@tobiasmuecksch it should come from your setup. i test it this morning with Safari Version 15.3 (17612.4.9.1.8) and as foreseen i do not have any issue. Which framework are you using?

[jepiqueau]

@tobiasmuecksch i just saw that you are using Safari 15.4, i did not have it install yet

[tobiasmuecksch]

I'm working with Ionic & Angular. This is the repo I'm working in (https://github.com/tobiasmuecksch/capacitor-sqlite-angular). You should know it, since you already helped me with getting sqlite to work in the browser.

It does not contain the code for the reproduction yet. I'll let you know, when I have finished.

[tobiasmuecksch]

I've just tested it in Chrome 100 and the error is there too.

[jepiqueau]

@tobiasmuecksch that is what i was just thinking suggesting. So it is not due to Safari 15.4

[tobiasmuecksch]

@jepiqueau While creating the demo code, I found the problem:

I haven't closed the connection after creating the table. Instead I've reused the same connection to run a select query.

Do I have to close the connection after every query? Are there any rules on when I have to close db connections?

[jepiqueau]

@tobiasmuecksch No you should not but for web you have a saveToStore method which save the in-memory database to store that you can use at your discretion

            if(platform === 'web') {
                // save the db to store
                await sqlite.saveToStore('db_tab3');
            }

When you use the db.close() or the sqlite.closeConnection("db_tab3") this is done automatically.

my test code is:

            const db: : SQLiteDBConnection = await openConnection('db_tab3', false,
            'no-encryption', 1,true);
            setLog((log) => log.concat("> Open connection 'db_tab3' successful\n"));
            // create tables in db1
            const query = `
                CREATE TABLE IF NOT EXISTS test (
                id INTEGER PRIMARY KEY NOT NULL,
                name TEXT NOT NULL
                );
            `        
            const ret: any = await db.execute(query);
            console.log(`&&&& createSchema 'db_tab3' ret ${JSON.stringify(ret)}`)
            if (ret.changes.changes < 0) {
            const msg = "Error: Execute createSchema 'db_tab3' failed";
            setErrMsg((errMsg) => errMsg.concat(msg));
            return;
            }
            setLog((log) => log.concat("> Create DB Schema 'db_tab3' successful\n"));
            cont res: any = await db.getTableList();
            console.log(`&&&& getTableList res ${JSON.stringify(res)}`)
            const statement = 'SELECT * FROM test;';
            const qValues: any[] = [];
            // The following line throws exception: "SelectSQL: queryAll: no such table: test"
            const result = await db.query(statement, qValues);
            console.log('select result', result.values);
        
            await sqlite.closeConnection("db_tab3");                                                            

with

const openConnection = async (dbName: string, encrypted: boolean,
                                     mode: string, version: number,
                                     isDelete: boolean): Promise<SQLiteDBConnection> => {
    let db: SQLiteDBConnection;
    try {
        const retCC = (await sqlite.checkConnectionsConsistency()).result;
        let isConn = (await sqlite.isConnection(dbName)).result;
        if(retCC && isConn) {
            db = await sqlite.retrieveConnection(dbName);
        } else {
            db = await sqlite
                    .createConnection(dbName, encrypted, mode, version);
        }
        if (isDelete) {
            await deleteDatabase(db);
        }
        await db.open();
        return db;
    } catch (err) {
        return Promise.reject(err);
    }
}
const deleteDatabase = async (db: SQLiteDBConnection): Promise<void> => {
    try {
        const ret = (await db.isExists()).result;
        if(ret) {
            const dbName = db.getConnectionDBName();
            await db.delete();
            return Promise.resolve();
        } else {
            return Promise.resolve();
        }
    } catch(err) {
        return Promise.reject(err);
    }
}

Hope this clarify

[jepiqueau]

@tobiasmuecksch in my code above you can replace

await sqlite.closeConnection("db_tab3");

by

if(platform === 'web') {
   // save the db to store
   await sqlite.saveToStore('db_tab3');
}

and continue working with the open connection

[tobiasmuecksch]

@jepiqueau Do I have to call the .saveToStore() method after ever query which creates or changes data?

[jepiqueau]

@tobiasmuecksch It is not mandatory, you can do it at your discretion when the database got quite a number of changes and that if there is a crash of the browser or the app these changes would have already been stored in localforage store

[tobiasmuecksch]

Allright. Thank you so much, for your awesome support!

Just one more question. If I would never call this method, does the plugin automatically save the data to the store from time to time?

[jepiqueau]

@tobiasmuecksch No, it is only call automatically when you close the db or the connection

[tobiasmuecksch]

Good to know! Thank you very much.