SQLStorage to SQLite db on ionic


#1

Ok so I used to have a SQLStorage working on beta11, but now that’s gone and I’m implementing it all on SQLite, but I’m not sure how to do most of it.

I have several models each for a group of tables which I used to create like so:

constructor(public platform: Platform){
    this.synced=false;
    this.platform.ready().then(() => {
        this.fooSQL = new Storage(SqlStorage);
    });
}

And then I’d call this method for initialization:

initializeSQL(){
    this.platform.ready().then(() => {
this.fooSQL.query('CREATE TABLE IF NOT EXISTS foo_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...)').then((data) => {
            if(this.SQLisEmpty()){
                this.synced = false; //has no synced content
            }
            else{
                this.synced = true;//has synced content
            }
        }, (error) => {
            console.log("ERROR -> " + JSON.stringify(error.err));
        });
    });
}

And then I could use methods like this add:

addItem(data){
    return this.platform.ready().then(() => {
        return this.fooSQL.query('INSERT INTO foo_table ( data ) VALUES ( ? )',data);
    });
}

But how do I do all of this with SQLite? I saw that it requires opening and closing the database and instead of using queries, you use executeSql, but I’m not sure how, exactly.


#2

I made a simple wrapper around sqliteplugin that uses WebSQL in browsers and sqlite-storage on devices.

do ionic plugin add cordova-sqlite-storage

You have to put it in providers section in app.module.ts and in app.component.ts in platform.ready you call init method for the databaseprovider.
app.component.ts:

import { DatabaseProvider } from '../providers/database-provider';

constructor(platform: Platform, public db: DatabaseProvider) {
	platform.ready()
		.then(() => this.db.init());
	}
}

database-provider.ts:

import { Injectable } from '@angular/core';

declare var window: any;

@Injectable()
export class DatabaseProvider {

	public db: any;
	public dbname: string = 'YourDBName.db';

	constructor() { }

	/**
	 * Init - init database etc. PS! Have to wait for Platform.ready
	 */
	init(): Promise<any> {
		return new Promise(resolve => {
			if (typeof window.sqlitePlugin !== 'undefined') {
				this.db = window.sqlitePlugin.openDatabase({ name: this.dbname, location: 'default' });
				// console.log("--> running on device: ", this.db);
				resolve();
			} else {
				this.db = window.openDatabase(this.dbname, '1.0', 'Test DB', -1);
				// console.log("--> running in browser: ", this.db);
				resolve();
			};
		});
	}

	/**
	 * query - executes sql
	 */
	query(q: string, params?: any): Promise<any> {
		return new Promise((resolve, reject) => {
			params = params || [];
			this.db.transaction((tx) => {
				tx.executeSql(q, params, (tx, res) => {
					resolve(res);
				}, (tx, err) => {
					reject(err);
				});
			});
		});
	}
}

you can then use:

this.db.query("CREATE TABLE IF NOT EXISTS tableName (id INTEGER, name TEXT)")
.then(res=> {
  console.log("Result: ", res);
})
.catch(err=> {
  console.log("Error: ", err);
});

#3

Thanks, I was getting insane since Rc0 with this.


#4

Okay, so I’m now using that provider and on each model that uses it I have:

constructor(public platform: Platform, public mainDB : DatabaseProvider){}

and the same initializeSQL() function I posted earlier, except using this.mainDB.query, but it’s not running the queries.

I added DatabaseProvider to the providers array on app.module.ts ( along with every model I’m using ) and on app.component.ts’s constructor I do:

platform.ready().then(() => {
    db.init().then(() => {
        model1.initializeSQL();
        model2.initializeSQL();
        ...
    }
}

I can see there’s a database being created, but none of my table-creating functions are running there.


#5

There were a missing resolve call in the init function. I updated original post with the corrections.


#6

@sveinla - I was having issues getting SQLite working using ionic-native import as per the docs, using your plugin and example implementation appears to be working… thanks!


#7

@sveinla Can I buy you a drink, chocolate, anything??? I was on the brink of a nervous breakdown on how to get it all working properly across multiple pages and your solution saved my day (after around 10 hours of fiddling, reading, not understanding why, trying, failing, more failing, even more failing and so on!)

THANK YOU!!!