SQLite database already open: data.db


#1

I am using Ionic2 with SQLite.

I get the following warning. Please can anyone advise how I should be handling the open of the database:

database already open: data.db

As you can see below, on initialization in app.ts I open the database and create the tables. Then each time I want to use it in service.ts, I open it again, hence the error.

How can I check if the database is already open, or how should I handle this?

Thanks

app.ts

  private createDatabase(): void {
    this.utilityService.setLocalStrorage('this.messages.observe', 'false');
    let db: SQLite = new SQLite();
    db.openDatabase({
      name: "data.db",
      location: "default"
    }).then(() => {
      db.executeSql("CREATE TABLE IF NOT EXISTS chats (_id TEXT PRIMARY KEY, memberIds TEXT, title TEXT, subTitle TEXT, picture TEXT, lastMessageId TEXT, lastMessageCreatedAt DATE)", {}).then((chatData) => {
        db.executeSql("CREATE TABLE IF NOT EXISTS messages (_id TEXT PRIMARY KEY, chatId TEXT, senderId TEXT, ownership TEXT, content TEXT, createdAt DATE, changeDate BOOLEAN, readByReceiver BOOLEAN)", {}).then((messageData) => {
        }, (error) => {
          console.error("Unable to execute messages sql", error);
        });

      }, (error) => {
        console.error("Unable to execute chats sql", error);
      });
    }, (error) => {
      console.error("Unable to open database", error);
    });
  }
}

service.ts

public openDatabase(): Promise<any> {
    let promise: Promise<any> = new Promise<any>(resolve => {
        this.database = new SQLite();
        this.database.openDatabase({ name: "data.db", location: "default" }).then(() => {
            let promiseChats: Promise<Array<Chat>> = this.refreshChats();
            return promiseChats.then((chatsData: Array<Chat>) => {
                let promiseMessages: Promise<Array<Message>> = this.refreshMessages();
                return promiseMessages.then((messagesData: Array<Message>) => {
                    resolve(messagesData);
                });
            });
        }, (error) => {
            console.log("OPEN ERROR: ", error);
        });
    });
    return promise;
}

How to check if Ionic 2 SQLite Native database is already open
#2

Don’t open it twice. :wink: Why not move the schema creation to service.ts if that’s the only service that uses that database?


#3

Yeah just like what @mirkonasato said, move your database to a service and inject it in the main module. Then you have two options, either wrap the functionality of SQLite with your own custom methods, or expose the database object through a property that you can always call.

@Injectable()
export class DatabaseProvider {
  db: SQLite;

  constructor(platform: Platform){
    platform.ready().then(() => {
      this.db = new SQLite();
      this.db.openDatabse(.....);
    });
  }
}

@Component()
export class MyComponent {

  constructor(private dbService: DatabaseProvider) {
    // NOTE: make sure that `deviceready` has fired and that the database is initialized 
    dbService.db.transaction().then((tx) => { .... });
  }

}

#4

I try the following, i.e. wrapping it in a transaction as you suggest, but it cases it to just freeze.

I have the following code, that executes a transaction. However, it freezes.

dataService.ts

public openDatabase(): Promise<Array<Message>> {
    let promise: Promise<Array<Message>> = new Promise<Array<Message>>(resolve => {
        if (this.database && this.database != null) {
            this.database.transaction((tx) => {
                Promise.all([this.refreshChats(tx), this.refreshMessages(tx)]).then(() => { resolve(this.messages) });
            });
        } else {
            this.database = new SQLite();
            this.database.openDatabase({
                name: "data.db",
                location: "default"
            }).then(() => {
                this.database.transaction((tx) => {
                    Promise.all([this.refreshChats(tx), this.refreshMessages(tx)]).then(() => { resolve(this.messages) });
                });
            }, (error) => {
                console.log("OPEN ERROR: ", error);
            });
        }
    });
    return promise;
}

public refreshChats(db: any): Promise<any> {
    return db.executeSql("SELECT * FROM chats", [])
        .then((chatData) => {
            let promises: Array<any> = [];
            this.chats = [];
            if (chatData.rows.length > 0) {
                for (var i = 0; i < chatData.rows.length; i++) {
                    promises.push(this.populateChat(db, chatData.rows.item(i)));
                }
            }
            Promise.all(promises).then(() => {
                return this.chats;
            });
        })
        .catch(error => {
            console.log("ERROR REFRESHING CHATS: " + JSON.stringify(error));
            console.log(error);
        });
}

When I remove the transaction wrapper:

...
        if (this.database && this.database != null) {
                Promise.all([this.refreshChats(this.database), this.refreshMessages(this.database)]).then(() => { resolve(this.messages) });
        } else {
...

It no longer freezes, but I get the following error:

ERROR REFRESHING CHATS: {}

TypeError {stack: (...), message: "Cannot read property 'executeSql' of undefined"}

If I open the database again, it seems to work fine, apart from giving this warning:

database already open: data.db

Please can anyone advise how I should be handling the opening of the database and executing transactions?

Thanks


#5

You need to be careful mixing transactions and promises. A transaction is committed when its callback completes, but if you do async stuff in the callback weird things may happen, like the transaction being left open so no further db operations can be performed.

Unless you really need to execute two or more sql statements atomically (i.e. you need to ensure that they all complete successfully, or else they will all be rolled back) it’s a lot easier to call executeSql directly on the database object.


#6

Thanks Mirkonasato. I would actually prefer to do the transaction directly on the database, because it is just a single transaction. The reason I tried this, is because when I do it directly on the database, I get the following:

TypeError {stack: (...), message: "Cannot read property 'executeSql' of undefined"}


#8

I put together a small project to make it easier to work with the sqlite plugin here: mirkonasato/ionix-sqlite.


#9

Thank you. I will have a look


#10

Hi mirkonasato,

I am trying to implement ionix-sqlite, I run npm install --save ionix-sqlite, and implement the code, which builds with no errors. However, when I try start up the app, I get the following error:

Uncaught SyntaxError: <unknown message reserved_word> app.bundle.js:120054
deviceready has not fired after 5 seconds. cordova.js:1185
Channel not fired: onDOMContentLoaded 

compiled code ( app.bundle.js):

120052    /* 729 */
120053    /***/ function(module, exports) {
120054    	export { SqlDatabase } from './SqlDatabase';
120055    /***/ },

Do you have any ideas what I have done wrong? Is it complaining about the reserved word “export”?

Here is my implementation:

app.ts

  initializeApp() {
    this.platform.ready().then(() => {
      // Okay, so the platform is ready and our plugins are available.
      // Here you can do any higher level native things you might need.
      StatusBar.styleDefault();

      if (window.cordova) {
        this.chatsStorageService.createDatabase();
      }
    });
  }

chatsStorageService.ts

import { Injectable } from "@angular/core";
import { SQLite } from 'ionic-native';
import { Chat, Message } from 'api/models';
import { Messages } from 'api/collections';
import { SqlDatabase } from 'ionix-sqlite';
//import { Item } from './item.model';

@Injectable()
export class ChatsStorageService {

    private database = null;
    private name: string = 'data.db.thewhozoo';
    public chats: Array<Chat> = new Array<Chat>();
    public messages: Array<Message> = new Array<Message>();


    private dbPromise: Promise<SqlDatabase>;

    constructor() {
    }

public createDatabase(): void {
    const createChatsTable: string = "CREATE TABLE IF NOT EXISTS chats (_id TEXT PRIMARY KEY, memberIds TEXT, title TEXT, subTitle TEXT, picture TEXT, lastMessageId TEXT, lastMessageCreatedAt DATE)";
    const createMessagesTable: string = "CREATE TABLE IF NOT EXISTS messages (_id TEXT PRIMARY KEY, chatId TEXT, senderId TEXT, ownership TEXT, content TEXT, createdAt DATE, changeDate BOOLEAN, readByReceiver BOOLEAN)";
    this.dbPromise = SqlDatabase.open(this.databaseName, [createChatsTable]);
    this.dbPromise.then((data) => {
        return this.dbPromise
            .then((db) => db.execute(createMessagesTable))
            .then((resultSet) => {
                console.log('database ' + this.databaseName + ' created');
            });
    });
}

    public refreshChats(): Promise<any> {
        const statement: string = "SELECT * FROM chats";
        return this.dbPromise
            .then((db) => db.execute(statement))
            .then((resultSet) => {
                this.chats = [];
                if (resultSet.rows.length > 0) {
                    for (var i = 0; i < resultSet.rows.length; i++) {
                        this.populateChat(resultSet.rows.item(i));
                    }
                }
            });
    }

    private populateChat(chatItem: any): Promise<any> {
        const statement: string = "SELECT * FROM messages where _id = ?";
        const params: string[] = [chatItem.lastMessageId];
        let messages: Array<Message> = new Array<Message>();
        return this.dbPromise
            .then((db) => db.execute(statement, params))
            .then((messageData) => {
                this.chats = [];
                if (messageData.rows.length > 0) {
                    for (var j = 0; j < messageData.rows.length; j++) {
                        if (messageData.rows.item(j)) {
                            let messageValue: Message = { _id: messageData.rows.item(j)._id, chatId: messageData.rows.item(j).chatId, senderId: messageData.rows.item(j).senderId, ownership: messageData.rows.item(j).ownership, content: messageData.rows.item(j).content, createdAt: messageData.rows.item(j).createdAt, changeDate: messageData.rows.item(j).changeDate, readByReceiver: messageData.rows.item(j).readByReceiver };
                            messages.push(messageValue);
                        }
                    }
                }
                let chatValue: Chat = {
                    _id: chatItem._id, memberIds: chatItem.memberIds, title: chatItem.title, subTitle: chatItem.subTitle,
                    picture: chatItem.picture, lastMessage: messages[0], lastMessageCreatedAt: chatItem.lastMessageCreatedAt
                };
                if (this.chats.indexOf(chatValue) === -1) {
                    this.chats.push(chatValue);
                }
            });
    }

    public refreshMessages(): Promise<any> {
        const statement: string = "SELECT * FROM messages";
        return this.dbPromise
            .then((db) => db.execute(statement))
            .then((resultSet) => {
                this.messages = [];
                if (resultSet.rows.length > 0) {
                    for (var i = 0; i < resultSet.rows.length; i++) {
                        if (resultSet.rows.item(i)) {
                            this.messages.push({ _id: resultSet.rows.item(i)._id, chatId: resultSet.rows.item(i).chatId, senderId: resultSet.rows.item(i).senderId, ownership: resultSet.rows.item(i).ownership, content: resultSet.rows.item(i).content, createdAt: resultSet.rows.item(i).createdAt, changeDate: resultSet.rows.item(i).changeDate, readByReceiver: resultSet.rows.item(i).readByReceiver });
                        }
                    }
                }
            });
    }

    public addChat(chat: Chat): Promise<any> {
        if (window.cordova) {
            if (this.chats.indexOf(chat) < 0) {
                const statement: string = "INSERT INTO chats (_id, memberIds, title, subTitle, picture, lastMessageId, lastMessageCreatedAt) VALUES (?, ?, ?, ?, ?, ?, ?)";
                const params: any[] = [chat._id, chat.memberIds, chat.title, chat.subTitle, chat.picture, chat.lastMessage._id, chat.lastMessageCreatedAt];
                this.chats.push(chat);
                return this.dbPromise
                    .then((db) => db.execute(statement, params))
                    .then((resultSet) => {
                        console.log('added chat: ', resultSet);
                    });
            }
        }
    }

    public updateChat(chat: Chat): Promise<any> {
        if (window.cordova) {
            let idx: number = this.contains(this.chats, chat);
            if (idx >= 0) {
                this.chats[idx] = chat;
                const statement: string = "UPDATE chats SET memberIds = ?, title = ?, subTitle = ?, picture = ?, lastMessageId = ?, lastMessageCreatedAt = ? WHERE _id = ?"
                const params: any[] = [chat.memberIds, chat.title, chat.subTitle, chat.picture, chat.lastMessage._id, chat.lastMessageCreatedAt, chat._id];
                return this.dbPromise
                    .then((db) => db.execute(statement, params))
                    .then((resultSet) => {
                        console.log('updated chat: ', resultSet);
                    });
            }
        }
    }

    public updateMessage(message: Message): Promise<any> {
        if (window.cordova) {
            let idx: number = this.contains(this.messages, message);
            if (idx >= 0) {
                this.messages[idx] = message;
                const statement: string = "UPDATE messages SET readByReceiver = ? WHERE _id = ?";
                const params: any[] = [message.readByReceiver, message._id];
                return this.dbPromise
                    .then((db) => db.execute(statement, params))
                    .then((resultSet) => {
                        console.log('updated message: ', resultSet);
                    });
            }
        }
    }

    private contains(arr: Array<any>, obje: any): number {
        for (let i: number = 0; i < arr.length; i++) {
            if (arr[i]._id === obje._id) {
                return i;
            }
        }
        return -1;
    }

    public removeChat(chat: Chat): Promise<any> {
        if (window.cordova) {
            let idx: number = this.chats.indexOf(chat);
            if (idx >= 0) {
                const selectStatement: string = "SELECT * FROM chats where _id = ?";
                const selectParams: string[] = [chat._id];
                const deleteStatement: string = "DELETE FROM chats where _id = ?";
                const deleteParams: string[] = [chat._id];
                for (let i: number = 0; i < this.messages.length; i++) {
                    if (this.messages[i].chatId === chat._id) {
                        this.removeMessage(this.messages[i]);
                    }
                }
                this.chats.splice(idx, 1);
                return this.dbPromise
                    .then((db) => db.execute(selectStatement, selectParams))
                    .then((resultSet) => {
                        if (resultSet && resultSet.rows.length > 0) {
                            return this.dbPromise
                                .then((db) => db.execute(deleteStatement, deleteParams))
                                .then((resultSet) => {
                                    console.log('chat deleted: ', resultSet);
                                });
                        }
                    });
            }
        }
    }

    public addMessage(chat: Chat, message: Message): Promise<Message> {
        let promise: Promise<Message> = new Promise<Message>(resolve => {
            if (window.cordova) {
                const statement: string = "SELECT * FROM chats where _id = ?";
                const params: string[] = [chat._id];
                return this.dbPromise
                    .then((db) => db.execute(statement, params))
                    .then((resultSet) => {
                        if (!resultSet || resultSet.rows.length === 0) {
                            this.addChat(chat);
                        }
                        if (this.messages.indexOf(message) < 0) {
                            this.addTheMessage(message);
                        }
                    });
            }
        });
        return promise;
    }

    private addTheMessage(message: Message): Promise<any> {
        const selectStatement: string = "SELECT * FROM messages where _id = ?";
        const selectParams: string[] = [message._id];
        const insertStatement: string = "INSERT INTO messages (_id, chatId, senderId, ownership, content, createdAt, changeDate, readByReceiver) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        const insterParams: any[] = [message._id, message.chatId, message.senderId, message.ownership, message.content, message.createdAt, message.changeDate, message.readByReceiver];
        return this.dbPromise
            .then((db) => db.execute(selectStatement, selectParams))
            .then((resultSet) => {
                if (!resultSet || resultSet.rows.length === 0) {
                    return this.dbPromise
                        .then((db) => db.execute(insertStatement, insterParams))
                        .then((resultSet) => {
                            if (!resultSet || resultSet.rows.length === 0) {
                                let length: number = this.messages.push(message);
                                console.log('message inserted: ' + length, message);
                            }
                        });
                }
            });
    }

    public removeMessage(message: Message): Promise<any> {
        if (window.cordova) {
            let idx: number = this.messages.indexOf(message);
            if (idx >= 0) {
                const selectStatement: string = "SELECT * FROM messages where _id = ?";
                const selectParams: string[] = [message._id];
                const deleteStatement: string = "DELETE FROM messages where _id = ?";
                const deleteParams: string[] = [message._id];
                this.messages.splice(idx, 1);
                return this.dbPromise
                    .then((db) => db.execute(selectStatement, selectParams))
                    .then((resultSet) => {
                        if (resultSet && resultSet.rows.length > 0) {
                            return this.dbPromise
                                .then((db) => db.execute(deleteStatement, deleteParams))
                                .then((resultSet) => {
                                    console.log('message deleted: ', message);
                                });
                        }
                    });
            }
        }
    }

    public findLastMessageForChat(chat: Chat): Promise<Message> {
        let promise: Promise<Message> = new Promise<Message>(resolve => {
            const statement: string = "SELECT * FROM messages where chatId = ? order by createdAt desc";
            const params: string[] = [chat._id];
            return this.dbPromise
                .then((db) => db.execute(statement, params))
                .then((data) => {
                    let message: Message = null;
                    if (data.rows.length > 0) {
                        for (var i = 0; i < data.rows.length; i++) {
                            if (data.rows.item(i)) {
                                message = ({ _id: data.rows.item(i)._id, chatId: data.rows.item(i).chatId, senderId: data.rows.item(i).senderId, ownership: data.rows.item(i).ownership, content: data.rows.item(i).content, createdAt: data.rows.item(i).createdAt, changeDate: data.rows.item(i).changeDate, readByReceiver: data.rows.item(i).readByReceiver });
                                break;
                            }
                        }
                    }
                    resolve(message);
                });
        });
        return promise;
    }

}

Note: I also commented out the following import:

//import { Item } from './item.model';

because it cases the following build error:

ERROR in ./app/pages/chats/chatsStorageService.ts
(6,22): error TS2307: Cannot find module './item.model'.

#11

It’s probably because I only published es2015 modules. It should work fine with Rollup or Webpack2 (tested with a new Ionic 2 project) but not with other build systems. Do you know what you’re using in your project? What do you get if you run

npm list @ionic/app-scripts

#12

I am using a webpack from a WhatsApp Clone Tutorial I followed.

E:\Development\IDE\ionic-apps\theWhoZoo>npm list @ionic/app-scripts
theWhoZoo@ E:\Development\IDE\ionic-apps\theWhoZoo
└── (empty)

npm ERR! code 1

Is this my problem?


#13

It just means you created your project with an older version of the Ionic CLI I guess.

I should probably publish the code in the CommonJS format as well to make it usable with other systems. I need to look into it…


#14

Okay, thanks.

Do you mind if I copy your code in SqlDatabase.ts into my own implementation for now until you’ve updated your ionix-sqlite?


#15

Not a problem, it’s open source. Thanks for testing it.


#16

When I copy your code into my workspace, I am getting the following build error now.

ERROR in ./app/pages/chats/SqlDatabase.ts
(25,7): error TS2322: Type 'Promise<SqlResultSet>' is not assignable to type 'Promise<SqlDatabase>'.
  Type 'SqlResultSet' is not assignable to type 'SqlDatabase'.
    Property '_db' is missing in type 'SqlResultSet'.
23    // execute all the other statements (if any) sequentially
24    for (let sql of initStatements) {
25      dbPromise = dbPromise.then(() => _db.execute(sql));
26    }

#17

solved:

23    // execute all the other statements (if any) sequentially
24    for (let sql of initStatements) {
25     dbPromise.then(() => _db.execute(sql));
26    }

#18

That’s not the same thing, because you’re no longer chaining the promises one after another. What typescript version are you using?


#19
E:\Development\IDE\ionic-apps\theWhoZoo>tsc -v
Version 1.8.10

#20

I am getting a run time error now, maybe, it’s because I removed the dbPromise = from:

dbPromise = dbPromise.then(() => _db.execute(sql));

[ionix-sqlite] using Cordova sqlitePlugin app.bundle.js:120109
execute: CREATE TABLE IF NOT EXISTS chats (_id TEXT PRIMARY KEY, memberIds TEXT, title TEXT, subTitle TEXT, picture TEXT, lastMessageId TEXT, lastMessageCreatedAt DATE) app.bundle.js:120087
new transaction is waiting for open operation SQLitePlugin.js:106
database data.db.thewhozoo created 
SqlDatabase
 app.bundle.js:119809
OPEN database: data.db.thewhozoo - OK SQLitePlugin.js:179
DB opened: data.db.thewhozoo SQLitePlugin.js:80
execute: resolve:  
Object
 app.bundle.js:120090
EXCEPTION: Error: Uncaught (in promise): TypeError: Cannot call method 'indexOf' of undefined app.bundle.js:44906
EXCEPTION: Error: Uncaught (in promise): TypeError: Cannot call method 'indexOf' of undefined app.bundle.js:44897
STACKTRACE: app.bundle.js:44897
Error: Uncaught (in promise): TypeError: Cannot call method 'indexOf' of undefined
    at resolvePromise (file:///android_asset/www/build/js/zone.js:538:32)
    at file:///android_asset/www/build/js/zone.js:574:18
    at ZoneDelegate.invokeTask (file:///android_asset/www/build/js/zone.js:356:38)
    at Object.NgZoneImpl.inner.inner.fork.onInvokeTask (file:///android_asset/www/build/js/app.bundle.js:13297:42)
    at ZoneDelegate.invokeTask (file:///android_asset/www/build/js/zone.js:355:43)
    at Object.NgZoneImpl.inner.inner.fork.onInvokeTask (file:///android_asset/www/build/js/app.bundle.js:13297:42)
    at ZoneDelegate.invokeTask (file:///android_asset/www/build/js/zone.js:355:43)
    at Zone.runTask (file:///android_asset/www/build/js/zone.js:256:48)
    at drainMicroTaskQueue (file:///android_asset/www/build/js/zone.js:474:36)
    at XMLHttpRequest.ZoneTask.invoke (file:///android_asset/www/build/js/zone.js:426:22) app.bundle.js:44897
Unhandled Promise rejection: Cannot call method 'indexOf' of undefined ; Zone: angular ; Task: Promise.then ; Value: TypeError {stack: (...), message: "Cannot call method 'indexOf' of undefined"} zone.js:461
Error {stack: "Error: Uncaught (in promise): TypeError: Cannot ca…ile:///android_asset/www/build/js/zone.js:426:22)", rejection: TypeError, promise: ZoneAwarePromise, zone: Zone, task: ZoneTask…}

#21

Yes it won’t work properly if you don’t wait for one operation to complete before executing the next one. I’m using typescript 2.0.3. You can try upgrading

npm install --save-dev typescript@latest