Ionic Prepopulated Database with Antair Cordova SQ

Question

____ INTRO

Hello everyone, first of all, three clarifications:

1. My english is not good, so I beg your pardon in advance for my mistakes,
2. I'm a newbie so forgive me for inaccuracies,
3. I have previously searched and tried the solutions I found on the internet but still I can not solve the problem of embedding a prepopulated database.

____ THE GOAL

I want to develop an app for iOS and Android with a prepopulated database.

Just for example, the database consists of 15.000 records each one made of three key-value pair (id, firstname and lastname).

___ WHAT I DID

Steps:

ionic start myapp blank
cd myapp
ionic platform add ios
ionic platform add android

Then I created an sqlite database for testing purpose, named mydb.sqlite, made of one table people containing two id, firstname, lastname records.

I decided to use the following plugin: https://github.com/Antair/Cordova-SQLitePlugin That's because it can be installed with cordova tool.

ionic plugin add https://github.com/Antair/Cordova-SQLitePlugin

(Alert: I think that the instructions on the website show an incorrect reference - "cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin" - which refers to another plugin).

Then, following the instructions on the plugin website, I copied the database to myapp/www/db/ so that it can now be found at myapp/www/db/mydb.sqlite

I modified the index.html including the SQLite plugin just after the default app.js script:

<!-- your app's js -->
<script src="js/app.js"></script>
<script src="SQLitePlugin.js"></script>

I also write some lines of code in index.html file to show a button:

<ion-content ng-controller="MyCtrl">
    <button class="button" ng-click="all()">All</button>
</ion-content>

Finally I had modified ./js/app.js:

// Ionic Starter App

var db = null;

angular.module('starter', ['ionic' /* What goes here? */ ])

.run(function($ionicPlatform) {
  $ionicPlatform.ready(function() {
    // some predefined code has been omitted

    window.sqlitePlugin.importPrepopulatedDatabase({file: "mydb.sqlite", "importIfExists": true});
    db = window.sqlitePlugin.openDatabase({name: "mydb.sqlite"});

  }); // $ionicPlatform.ready
}) // .run

.controller('MyCtrl', function($scope){

    $scope.all = function(){
            var query = "SELECT * FROM people";
            // I don't know how to proceed

    }; // $scope.all

}); // .controller

___ THE PROBLEM

I don't know how to proceed in the controller section to query all the records (just an example of query) and show the results in the console.log.

I think that the following code must be completed in some way:

angular.module('starter', ['ionic' /* What goes here? */ ]) 

And also the code inside controller section must be completed:

       $scope.all = function(){
                var query = "SELECT * FROM people";
                // I don't know how to proceed

        }; // $scope.all

___ FINAL THANKS

Thank you in advance for the help you will give to me.

Answer 1

So this guy's code has helped a lot to encapsulate my DAL. I highly recommend that you use he's code pretty much verbatim.

https://gist.github.com/jgoux/10738978

You'll see he has the following method:

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

    self.db.transaction(function(transaction) {
        transaction.executeSql(query, bindings, function(transaction, result) {
            deferred.resolve(result);
        }, function(transaction, error) {
            deferred.reject(error);
        });
    });

    return deferred.promise;
};

Let's break this down a bit. The query function takes a query string (the query param) and a list of possible bindings for ? in a query like "SELECT * FROM A_TABLE WHERE ID = ?". Because he's code is a service, the self value points to the service itself for all future invocations. The function will execute a transaction against the db, but it returns a promise that is only fulfilled once the db comes back.

His service provides a second helper function: fetchAll.

self.fetchAll = function(result) {
    var output = [];

    for (var i = 0; i < result.rows.length; i++) {
        output.push(result.rows.item(i));
    }

    return output;
};

fetchAll will read the rows in their entirety into an array. The result param for fetchAll is the result variable passed in the query function's promise fulfillment.

If you copy and paste his code into your service file, you now have a bonafide DB service. You can wrap that service up in a DAL. Here's an example from my project.

.service('LocationService', function ($q, DB, Util) {
    'use strict';
    var self = this;
    self.locations = [];
    self.loadLocked = false;
    self.pending = [];

    self.findLocations = function () {
        var d = $q.defer();
        if (self.locations.length > 0) {
            d.resolve(self.locations);
        }
        else if (self.locations.length === 0 && !self.loadLocked) {
            self.loadLocked = true;
            DB.query("SELECT * FROM locations WHERE kind = 'active'")
                   .then(function (resultSet) {
                       var locations = DB.fetchAll(resultSet);
                       self.locations.
                           push.apply(self.locations, locations);
                       self.loadLocked = false;
                       d.resolve(self.locations);
                       self.pending.forEach(function (d) {
                           d.resolve(self.locations);
                       });
                   }, Util.handleError);
            } else {
                self.pending.push(d);
            }

            return d.promise;
        };
})

This example is a bit noisy since it has some "threading" code to make sure if the same promise is fired twice it only runs against the DB once. The general poin is to show that the DB.query returns a promise. The "then" following the query method uses the DB service to fetchAll the data and add it into my local memory space. All of this is coordinated by the self.findLocations returning the variable d.promise.

Yours would behalf similarly. The controller could have your DAL service, like my LocationService, injected into it by AngularJS. If you're using the AngularJS UI, you can have it resolve the data and pass it into the list.

Finally, the only issue I have with the guy's code is that the db should come from this code.

var dbMaker = ($window.sqlitePlugin || $window);

The reason for this is that the plugin does not work within Apache Ripple. Since the plugin does a fine job mirroring the Web SQL interface of the browser, this simple little change will enable Ripple to run your Ionic Apps while still allowing you to work your SQLite in a real device.

I hope this helps.