phonegap sqlite tutorial in ionic 1

Here’s my tutorial on making your sqlite work in your ionic application.

1.run “cordova plugin add io.litehelpers.cordova.sqlite”

2.create a file called “yourdb.db” in your www folder.

3.Run “ionic run android —device” to open your app even if its not complete.

4.Open chrome to “chrome://inspect/#devices” and open the developer tool.

5.In order to add the tables you need to sqlite, you must do it on the device. You can’t do it from an editor on your computer from my experience.

6.Type following to your developer tool.

var db = window.sqlitePlugin.openDatabase({name: "yourdb.db", createFromLocation: 1, location: 2, androidLockWorkaround: 1});
db.executeSql('CREATE TABLE IF NOT EXISTS profile (id integer primary key, email text, passwords text, details text)',[], function(res){console.log(res);});
db.executeSql('PRAGMA table_info(profile);',[], function(res){console.log(res);});

The first line opens the database. Make sure you have all those options there to enable it to work on both android and ios.

Next create the tables you need.

To see the columns you have run the third line.

Now everything is setup, I’ll show you how to code from ionic.

I have given a non trival example sql module you can use.

I show you how to insert, delete, query, update your database.

app.js

angular.module('home', [])
.run(['$ionicPlatform', '$rootScope',
 function($ionicPlatform, $rootScope) {

  $ionicPlatform.ready(function() {
    // Hide the accessory bar by default (remove this to show the accessory bar above the keyboard
    // for form inputs)
    if(window.cordova && window.cordova.plugins.Keyboard) {
      cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
    }
    if(window.StatusBar) {
      StatusBar.styleDefault();
    }
  });

  document.addEventListener("deviceready", function(){
    $rootScope.db = window.sqlitePlugin.openDatabase({name: "yourdb.db", createFromLocation: 1, location: 2, androidLockWorkaround: 1});
  }, false);
}]);

sqlService.js

angular.module('sqlService', [])
.service('sqlModule', ['$rootScope', '$q',  sqlModule])

function sqlModule($r, $q){
 var self = this;
 this.insertProfile = function(email, password, detailObj, cb){
    var query = "Insert INTO chat (email, password, detailObj) VALUES (?,?,?);";
    $r.db.transaction(function(tx) {
        tx.executeSql(query, [email, password, JSON.stringify(detailObj)], function(tx, res) {
          console.log("insertId: " + res.insertId);
          return cb(null, true);
        }, function(e) {
          console.log("ERROR: " + e.message);
          return cb(e, false);
        });
      });
 };

 this.queryProfile = function(email, cb){
    $r.db.executeSql('SELECT * FROM profile WHERE email LIKE "%' + email + '";',[], function(res){
        var result = [];
        var length = res.rows.length;
        for (var i = 0; i < length; i++){
            var holder = res.rows.item(i);
            var jsonObj = JSON.parse(holder.details);
            result.push({
                email: holder.email,
                password: holder.password,
                detail: jsonObj
            });
        }
        return cb(null, result);
    });
 };

 this.queryAllProfile = function(cb){
    $r.db.executeSql('SELECT * FROM profile;',[], function(res){
        var result = [];
        var length = res.rows.length;
        for (var i = 0; i < length; i++){
            var holder = res.rows.item(i);
            var jsonObj = JSON.parse(holder.details);
            result.push({
                email: holder.email,
                password: holder.password,
                detail: jsonObj
            });
        }
        return cb(null, result);
    });
 };

 this.updateProfile = function(email, password, details, cb){
    var query = "UPDATE profile SET password = ?, details = ? WHERE email = ?;";
    $r.db.transaction(function(tx) {
        tx.executeSql(query, [password, JSON.stringify(details), email], function(tx, res) {
          console.log("update: " + res);
          return cb(null, true);
        }, function(e) {
          console.log("ERROR: " + e.message);
          return cb(e, false);
        });
      });
 };

 this.deleteAllProfile = function(){
    $r.db.executeSql('DELETE FROM profile;',[], function(res){
        console.log(res);
    });
 };

}