Cordova/Ionic pre populated SQLite database issue

Question

So I have a Ionic app I want to deploy with pre populated SQLite database(tried populating it manually, but it's way to slow). So I decided to use the Cordova-sqlite-storage plugin(https://github.com/litehelpers/Cordova-sqlite-storage). I have a dump file witch opens just fine with no errors, but when I try to fetch anything I get no results. Anyways, here's my code:

In app.js:

app.run(function($ionicPlatform) {
    $ionicPlatform.ready(function() {
        db = window.sqlitePlugin.openDatabase({name: "mydb", createFromLocation: 1});
    });
})

In controllers.js

.controller("HomeController", function($scope,Test){
    $scope.test = function(){
        Test.getAlchohol().then(function(res){
            console.log(res.length);
        });
    };
})

My db fetching service:

.factory('Test', function($cordovaSQLite){
    var arr = [];
    return{
        getAlchohol:function(){
            var query = "SELECT * FROM Alchohol";
            return $cordovaSQLite.execute(db, query, []).then(function(res){
                arr = res.rows;
                return arr;
            }, function(err){
                console.error(err);
            });
        }
    }
})

And my dump file goes something like this:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "Alchohol" (
  "id" int(11) NOT NULL ,
  "intoxication" int(11) NOT NULL,
  "text_id_1" int(11) NOT NULL,
  "text_id_2" int(11) NOT NULL,
  "text_id_3" int(11) NOT NULL,
  PRIMARY KEY ("id")
);
INSERT INTO "Alchohol" VALUES (1,0,20,21,22);
INSERT INTO "Alchohol" VALUES (2,1,18,19,22);
INSERT INTO "Alchohol" VALUES (3,2,23,24,25);
INSERT INTO "Alchohol" VALUES (4,3,26,24,25);
INSERT INTO "Alchohol" VALUES (5,4,27,28,25);
INSERT INTO "Alchohol" VALUES (6,5,29,30,25);
INSERT INTO "Alchohol" VALUES (7,6,29,31,32);
INSERT INTO "Alchohol" VALUES (8,7,33,31,34);
INSERT INTO "Alchohol" VALUES (9,8,35,31,36);
INSERT INTO "Alchohol" VALUES (10,9,33,31,37);
END TRANSACTION;

I've tried every possible solution I found online and nothing worked for me. Any help will be greatly appreciated..

Answer 1

Pretty sure that @Joerg is right and you need to use a SQLite DB file to pre-populate the DB with the Cordova-sqlite-storage plugin, not a SQL dump.

You could use this plugin to import the SQL dump.

Answer 2

Please see my answer here: Open prepopulated SQLite dtabase in Cordova (for android) and Visual Studio

Basically, the plugin you use (https://github.com/litehelpers/Cordova-sqlite-storage) doesn't work with pre-populated databases.

However, this one works: https://www.npmjs.com/package/cordova-plugin-sqlite

Answer 3

Basically instead of using cordova-plugin-sqlite (deprecated) or cordova-sqlite-storage, just use cordova-sqlite-ext for this.

Check my complete answer for the same problem here: https://stackoverflow.com/a/36478499/976948

Answer 4

Do some changes.
1. Remove database connection from app.run(). Put that code in factory service

    dbConnection :function(){
                if (window.cordova) {
                dbconn = $cordovaSQLite.openDB({ name: "my.db" , location: 'default'}); 
          $cordovaSQLite.execute(dbconn, "CREATE TABLE IF NOT EXISTS Contacts(Id INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL UNIQUE, Email TEXT NOT NULL UNIQUE, Phone INTEGER NOT NULL UNIQUE,Website   TEXT,Address TEXT,Notes TEXT,Softdelete INTEGER default 0)");
            }
           else{
           dbconn = window.openDatabase("my.db", '1', 'my', 1024 * 1024 * 100);
            $cordovaSQLite.execute(dbconn, "CREATE TABLE IF NOT EXISTS Contacts(Id INTEGER PRIMARY KEY NOT NULL, Name TEXT NOT NULL UNIQUE,Email TEXT NOT NULL UNIQUE, Phone INTEGER NOT NULL UNIQUE,Website TEXT,Address TEXT,Notes TEXT,Softdelete INTEGER default 0)");
                                    }
           return dbconn;
        }

2. Call your getAlchohol() function on device ready.

   document.addEventListener("deviceready", onDeviceReady, false);
   function onDeviceReady() {
       Test.getAlchohol().then(function(res){
               console.log(res.length);
           });
   }