Pre-populated Databases Question

[coommark]

This is just super awesome! Okay just one VERY IMPORTANT question:

Prior to now, to use pre-populated dbs, we had to use cordova-plugin-dbcopy together with the Cordova-sqlite-storage (core), THE VERY IMPORTANT question is: does it mean with cordova-sqlite-ext, I can just place prepopulated.db in www, plug my Android device, run ionic run android, unplug it and continue testing away?

(BTW I saw the https://github.com/jdnichollsc/Ionic-Starter-Template, it is nice but a little not-like-default-ionic. Also, that starter assumes that only one db is to be used in the project, and that db is known at design time. In reality, some apps (like a book apps) require that additional dbs be downloaded after the app is installed. Plus, we are months into our development so no option for starter templates.)

UPDATE I guess I am wrong in saying I used two databases with Ionic. Actually I merely substituted one for another and it worked for whatever reason. Now I try to substitute it back and the first one is the one still loading, even though I have changed it! Weird!

[brodycj]

Prior to now, to use pre-populated dbs, we had to use cordova-plugin-dbcopy together with the Cordova-sqlite-storage

I would leave it as-is if it is working.

(core), THE VERY IMPORTANT question is: does it mean with cordova-sqlite-ext, I can just place prepopulated.db in www, plug my Android device, run ionic run android, unplug it and continue testing away?

As documented in README.md, you have to use the createFromLocation option when you open the database.

FYI I do not personally favor the idea of using a pre-populated database. I added the feature due to popular demand/pressure.

UPDATE I guess I am wrong in saying I used two databases with Ionic. Actually I merely substituted one for another and it worked for whatever reason. Now I try to substitute it back and the first one is the one still loading, even though I have changed it! Weird!

This is really weird. As I said in #14 I really cannot comment on this without a reproduction sample.

[coommark]

Hi @brodybits, thanks for your response. Actually being new to mobile development (ionic), it took me a few days to figure things out, so I am posting this for others who may find it useful. When packaging an app, if we build it with the pre-populated db (in the www folder) and use dbcopy or createFromLocation, the problem is our app still remains unnecessarily large on disk, and on Android you can't even delete the db from the apk. It just sits there, and gives our app a bad name for being too large. So the approach I thought best was deploy the app without db (5mb), then download the db (23mb) on first run or whatever.

So in Ionic, I just added these plugins: cordova plugin add org.apache.cordova.file-transfer

cordova plugin add cordova-plugin-file

Then I created my controller like this:

.controller('DownloadsCtrl', function($scope, $timeout, $cordovaFileTransfer) {  
  $scope.downloadFile = function () { 
      var url = "http://192.168.43.44:3000/download";      
      var targetPath = cordova.file.applicationStorageDirectory + '/databases/' + "largebook.db"; 
      $cordovaFileTransfer.download(url, targetPath, {}, true).then(function (result) {
          //console.log('Success');
      }, function (error) {
          //console.log('Error');
      }, function (progress) {
          // PROGRESS HANDLING GOES HERE
      });    
  };  
});

NOTE: For iOS the targetPath will be different. Check the documentations where iOS stores dbs. And my view is like this:

<ion-view view-title="Downloads">
  <ion-content>
    <h1>Downloads</h1>    
    <button class="button" ng-click="downloadFile()"> Download </button>
  </ion-content>
</ion-view>

And that is all! Voila! Now instead of my app being 28mb, it is just 5mb and users can download the db direct to the proper location without packaging the app with that additional 23mb db that I cannot delete. Awesome right? Okay and the good part? I never have to worry about createFromLocation, dbcopy and all that! And in my service or controller, I simply open the db like this:

$ionicPlatform.ready(function() {       
        db = window.sqlitePlugin.openDatabase({name: "largebook.db"});
    });

Isn't this just sexy? Now my boss has sworn to promote me by end of week (Just kidding, but...)

PS: Okay anyone wondering about the http://192.168.43.44:3000/download as my url? Yeah I thought so. Alright what I did was to create a portable hotspot on my Android phone, connect my laptop to it, run ipconfig, copy out the wireless ip address (ie 192.168.43.44 and add the port for my Node Express app to the ip which is :3000). That way I created a very basic Node Express app with one download controller like this:

var path = require('path');
module.exports.download = function(req, res, next){    
  res.download(__dirname+ '/' + 'largebooksecret.db', 'largebook.db', function(err) {
    if (err) { 
        console.log(err); 
    }
    else { 
        console.log('file downloaded'); 
    }
  });   
};

So you get it right? Now instead of hosting my db on the internet during development and all the hassles, I simply have a local server serving it to my installed Ionic/Cordova app. And that, my friends, is how in exactly 2 and half days, I solved my SQLite/Ionic problem! (Yeah I know, I am not the worst idiot on Github cos at least I solved it after 2 days of reading up on how the pieces tie together, so don't laugh at me!)

[brodycj]

Thanks @coommark for sharing, this should definitely be documented. I will document this somewhere when I get a chance.

[s-pic]

Hello @brodybits,

I am trying to achieve the same thing like @coommark for Android, but I can't get it to work. The Cordova-Plugin-File-Transfer Plugins download method – using a targetPath as stated above – stores the file under file:///data/user/0/{packageName})/databases/{filename]

The window.sqlitePlugin.openDatabase command however does not seem to open this db, instead it seems to create a new db.

Where does the file has to be downloaded to open the db with the param location: 'default'? What is the default location actually? The readme does not state it. Thanks in advance.

Here is the code, using the latest cordova cli and plugins cordova-sqlite-ext 0.10.0 and cordova-plugin-file-transfer 1.5.1.

`document.addEventListener("deviceready", onDeviceReady, false); function onDeviceReady() {

    var fileTransfer = new FileTransfer();
    var targetFilename = CONF.name + '.mbtiles'
    var targetPath = cordova.file.applicationStorageDirectory + '/databases/' + targetFilename;
    var url = encodeURI(CONF.url);

    fileTransfer.download(url, targetPath, function (entry) {
            console.log("download complete: " + entry.toURL());
            db = window.sqlitePlugin.openDatabase({location: 'default', 
                name: targetFilename, 
                androidDatabaseImplementation: 2}); // since reading blobs
            // do stuff
        },
        function (error) {
            /error handler
        }
    );
}`

[brodycj]

@SaPicOne I will document this when I get a chance. I think correct answer was already given for Android. Please double-check your full path name.

[s-pic]

The path @coommark stated is correct. I solved my problem.