Factory wrapper for SQLite plugin

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 ?

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.

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;
});```
1 Like

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:

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

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:

5 Likes

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.

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:

1 Like

Hi Julien,

Thank you very much !!

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()
});
}

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

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

Cheers!! Got some good stuff going on there

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

SQLite Plugin on Gist

2 Likes

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

I don’t understand your question

If datas are in a Sqlite database datas are available even after a restart.

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

This is a working example

@ronyrun: does the DB gets stored somewhere??

1 Like

the DB is stored within the the browser.
In ionic case it’s created as a SQLITE file in the root area.