Factory wrapper for SQLite plugin


#1

Hi,
I’m trying to set up the SQLite plugin (https://github.com/brodysoft/Cordova-SQLitePlugin) for persisting my application data.
I have to use the Platform object to wait until the device is ready. How would you create this service ? How can I return an instance of sqlitePlugin from the Platform ready function ? I’m not sure about how to do it.

.factory('DB', function(Platform) {
    var db;
    Platform.ready(function() {
       db = window.sqlitePlugin.openDatabase({name: "DB"});
    });
    return db;
})

Simple example of sqllite plugin with with ngCordova
Database persistence
Ionic and $cordovaSQLite - how to ensure factory ready?
Problem with global variable for db sqlite
Any example of using Sqlite in ionic apps? or is i possible to use a pre-populated Sqlite DB in ionic apps?
#2

I would suggest not having the service return a reference to sqlitePlugin directly. Rather, use the service as a wrapper exposing some public methods to initialize and query the database. Then in the main app’s .run, function, init the database service from your $ionicPlatform.ready callback.
That way, you aren’t initializing the database too early, and since you’ve encapsulated all database interaction within the service, you can swap it out in the future with minimal impact to the rest of the app.


#3

Thanks for the answer !
If I declare the database connexion in the main run() function, how can I access it globally ?
For example in my service, I’ll have to do something like :

db.transaction(function(transaction) { ... });

Where does “db” come from ?


#4

Inside an init method of your service. The main run function calls the init method on the service once the platform is ready. You won’t access the database directly from anywhere outside the service itself. The service is a wrapper for the sqlitePlugin methods. For instance, your service would expose a query method that runs the transaction on the database. Everywhere else in the app, you’ll inject the service and run the query method. That way if you changed plugins or database back-ends, you could just change the service itself, and the rest of your app will work with it.


#5

Excuse typos or syntax errors, this example code was written in the post editor, not tested.
Your run method might look like this:

.run(function (SqliteService) {
  $ionicPlatform.ready(function () {
    SqliteService.init().then(function(){
      //now your database service is ready
      SqliteService.query();
    })
  })      
})
```
Your service might look something like this:
```
.factory('SqliteService',function($q){
  var self = this;
  self.db = null;
  self.init =function(){
  var def = $q.defer();
  try{	  
    if(window.sqlitePlugin){
      self.db = window.sqlitePlugin.openDatabase({name: "whatever.db"},function(){
        def.resolve(true);
      });
  }else{
    def.reject();
  }
}catch(e){
  def.reject(e);
}
  return def.promise;
 }
};
  self.query = function(){
  //do stuff to the database. use promises
}	
return self;
});```

#6

Thanks for the advices !
I’m stuck with my DB factory, I don’t understand how to chain promises to be able to do something like this :

// Random controller
.controller('UserIndexCtrl', function($scope, User) {
    $scope.users= [];
    User.all().then(function(users) {
        $scope.users = users;
    });
})

// User service
.factory('User', function(DB) {
    var self = this;
    self.all = function() {
        return DB.query('SELECT * FROM users');
    };
    return self;
})

// DB service
.factory('DB', function($q, DB_CONFIG) {
    var self = this;
    self.db = null;

    self.init = function() {
        // DB init from config.js
        self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1);
        angular.forEach(DB_CONFIG.tables, function(table) {
            var columns = [];
            angular.forEach(table.columns, function(column) {
                columns.push(column.name + ' ' + column.type);
            });
            var query = 'CREATE TABLE IF NOT EXISTS ' + table.name + ' (' + columns.join(',') + ')';
            console.log('Création de la table ' + table.name);
            self.db.query(query);
        });
    };

    self.query = function(query, bindings) {
        bindings = typeof bindings !== 'undefined' ? bindings : [];
        var deferred = $q.defer();
        self.db.transaction(function(transaction) {
            return transaction.executeSql(query, bindings);
        });
        return deferred.promise;
    };

    return self;
})

So basically I want to use promise in my DB.query function, and then use this function in my Resources services (User as an example here).
And I want my Resources services to return promises too to map my $scope variables with the result of the query function.
Any idea ? :slight_smile:


#7

At first glance, I see that your query function is indeed returning a promise, but that promise is never resolved. You also are not actually processing the data response from the executeSql call. You might want that block to look something like this:

      self.query = function(query, bindings) {
        bindings = typeof bindings !== 'undefined' ? bindings : [];
        var deferred = $q.defer();
        self.db.transaction(function(transaction) {
            transaction.executeSql(query, bindings,function(trans,resp){
                deferred.resolve(resp);               
            });
        });
        return deferred.promise;
    };

You should also be prepared to reject the promise if your query goes awry, and handle that rejection further upstream. Read up on angular promises for more info:
http://docs.angularjs.org/api/ng/service/$q


#8

Haha I forgot to resolve it, indeed !
Thank you for all the help provided, here is my working service if anyone want to use it https://gist.github.com/jgoux/10738978 :smiley:


The best caching for ionic framework
#9

Hi Julien,

Thank you. I’m learning as well SQlite plugin.

You defined your tables like this :
tables: [
{
name: ‘documents’,
columns: [
{name: ‘id’, type: ‘integer primary key’},
{name: ‘title’, type: ‘text’},
{name: ‘keywords’, type: ‘text’},
{name: ‘version’, type: ‘integer’},
{name: ‘release_date’, type: ‘text’},
{name: ‘filename’, type: ‘text’},
{name: ‘context’, type: ‘text’}
]

Did you use a particular tool to create your database model ?

I’d like to create a database model with relation between tables :slight_smile:

R.


#10

Hello ronyrun,
I didn’t use any particular tool to create this schema.
Note that I changed how I declare my schema in a more simple form :

    tables: {
        documents: {
            id: 'integer primary key',
            title: 'text'
        }
    }

For a more robust solution and to handle relationships, maybe you can give persistencejs or lawnchair a try.
Good luck. :slight_smile:


#11

Hi Julien,

Thank you very much !!


#12

Thanks, it works perfectly.
Just to add for anyone new to this topic: for iOS the DB.init() must be called after the platformReady. Something in the line:
.controller(‘MyController’, function($scope, $ionicPlatform, $state,DBFactoryObj) {
$ionicPlatform.ready(function() {
// Platform stuff here.
DBFactoryObj.initDB();// the factory method will call the database initialization code, DB.init()
});
}


#13

Anyone got an example insert based on this wrapper? @ronyrun @jgx @mbuster


#14

here : http://dl.free.fr/rm8mcENls


#15

Cheers!! Got some good stuff going on there


#16

Anyone want to help me write a SQLite Plugin for ngCordova?

SQLite Plugin on Gist


#17

how did you manage to persist your data after the app is restarted?


#18

I don’t understand your question…
If datas are in a Sqlite database datas are available even after a restart.


#19

@ronyrun: Can you please help with an working example?


#20

This is a working example