Ionic pre-populated database with Antair Cordova SQLitePlugin [help request]
____ INTRO
Hello everyone, first of all three clarifications:
- My English is not very good, so I apologize in advance for my mistakes,
- I'm a beginner, so forgive me for the inaccuracies,
- I have previously searched and tried the solutions I found on the internet, but still I cannot seem to resolve the issue of implementing a pre-populated database.
____ GOAL
I want to develop an iOS and Android app with a pre-populated database .
For example, a database contains 15,000 records , each of which consists of three key-value pairs (id, firstname, and lastname).
___ WHAT DO I DO
Steps:
ionic start myapp blank
cd myapp
ionic platform add ios
ionic platform add android
Then I created a sqlite database for testing purposes named mydb.sqlite, consisting of one table containing two ids, firstname, lastname.
I decided to use the following plugin: https://github.com/Antair/Cordova-SQLitePlugin This is because it can be installed using the cordova tool.
ionic plugin add https://github.com/Antair/Cordova-SQLitePlugin
(Warning: I think the instructions on the website show the wrong link - "plug-in cordova add https://github.com/brodysoft/Cordova-SQLitePlugin " - this refers to a different plugin).
Then, following the instructions on the plugin website, I copied the database to myapp / www / db / so that it can now be found in myapp / www / db / mydb.sqlite
I changed index.html, including the SQLite plugin, right after the standard app.js script:
<!-- your app js -->
<script src="js/app.js"></script>
<script src="SQLitePlugin.js"></script>
I also write some lines of code in my index.html file to show the button:
<ion-content ng-controller="MyCtrl">
<button class="button" ng-click="all()">All</button>
</ion-content>
Finally, I changed. /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
___ PROBLEM
I don't know how to go to the controller section to query all the records (just an example query) and show the results in the console.log.
I think the following code must be completed somehow:
angular.module('starter', ['ionic' /* What goes here? */ ])
And also the section of code inside the controller must be completed:
$scope.all = function(){
var query = "SELECT * FROM people";
// I don't know how to proceed
}; // $scope.all
___ FINAL THANKS
Thanks in advance for the help you give me.
source to share
So this guy code helped a lot to encapsulate my DAL. I highly recommend that you use his code quite literally.
https://gist.github.com/jgoux/10738978
You will see that it 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 it down a bit. The query function takes a query string (query parameter) and a list of possible bindings? in a query like "SELECT * FROM A_TABLE WHERE ID =?". Since the code is a service, the value self points to the service itself for all future calls. The function will execute a transaction against db, but return a promise that is only fulfilled after db returns.
Its 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 entire rows into an array. The result parameter for fetchAll is the result variable passed in to fulfill the promise to the request function.
If you copy and paste its code into your service file, you now have a Bonafide DB service. You can wrap this service in 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 as it has some "threading" code to make sure the same promise is doubled, it only works once with the DB. The general takeaway is to show that DB.query returns a promise. Then, following the query method, it uses the database service to fetch the data and add it to local memory space. This is all coordinated by self.findLocations returning the d.promise variable.
Your reputation will be you. The controller could have your DAL service like my LocationService injected into it by AngularJS. If you are using AngularJS UI it can be resolved and passed to a list.
Finally, the only problem I ran into with the guy's code is that the db has to come from this code.
var dbMaker = ($window.sqlitePlugin || $window);
The reason for this is that the plugin does not work in Apache Ripple. Since the plugin does a wonderful job of mirroring the browser's web interface, this simple little change will allow Ripple to run your Ionic Apps while still allowing you to run your SQLite on a real device.
Hope this helps.
source to share