My goal: to have a mobile app that downloads its initial database from the server instead of pulling the data down through the normal API one at a time. This was for speed reasons, and there are probably other implementations and methods to try. Basically, if the app has never synced, pull down the database.
A bunch of posts on the subject (not exactly what I needed, but helpful): Using a local database with Titanium Update database and table content DB install from remote sqlite file The official database documentation Some helpful SQLite documentation for generating the dump from your database A nice way to programmatically modify the table that you dumped with some examples
The project happened to be using joli.js for persistence. My code eventually looked something like this:
function checkForBlankDb(callback) {
if (models.somemodel.count() === 0) { // never pulled from server
Ti.API.debug("Going to try to download the database");
activityIndicator.message = 'Downloading database...';
activityIndicator.show();
var xhr = Ti.Network.createHTTPClient();
xhr.onload = function() {
var f = Ti.Filesystem.getFile('file:///data/data/myprojectpackage/databases/mydb');
Ti.API.debug("f.nativePath: " + f.nativePath);
// key if you have opened the db, I needed to extend Joli to do this
joli.connection.close();
// remove the file, not entirely sure this is necessary
if (f.exists()) {
Ti.API.debug("deleting the file at nativepath");
f.deleteFile();
}
Ti.API.debug("going to write data");
f.write(this.responseData); // actually write the data we got
Ti.API.debug("wrote data");
Ti.API.debug("reconnecting to database");
joli.connection = new joli.Connection('mydb');
Ti.API.debug("should have successfully synced.");
callback();
};
xhr.onerror = function() {
alert("Could not connect to server to download.");
// TODO: better error handling
}
xhr.open('GET', serverpath + '/downloads/database');
authenticate(xhr);
xhr.send();
} else {
callback();
}
}
The long and short of it is that if you do something like opening a database mydb, it stores it at the file path listed up in the code sample (file:///blah). This is only for Android, the path for iOS is different. There are ways to generate that path more dynamically, but this works for now. If you want to see exactly what you are creating, use adb -e shell to check out the file system. The callback stuff makes the asynchronous call basically synchronous, because I wanted to do some things immediately after that required the database being loaded. Most of the time, the initial check would pass, and we would not download the database again (just look for diffs.) Hope this helps!