Preparing a SQLite db to be loaded remotely for mobile development

My mobile titanium app is loading data from a remote url, in the form of a xml file, that I have generated. The idea is that the app is downloading that xml and processes it into a SQLlite db. But I was thinking, I could also replace the server side generated xml with a ready sqlite file with all the data in it. That way, I don't need to do any processing client side, which saves some of the user's time.

Is this a good idea? If yes, how would I "fake" generate a sqlite file? There are no headers that I need t开发者_JAVA技巧o pass on, so that's not the issue, but what I noticed when I opened an .sqlite file with coda, that there are weird characters meaning the encoding must be different.

Thanks!

There is no mechanism in any browsers I am aware of which would allow you to transfer a binary image of a sqlite database (even assuming byte ordering and character coding were not an issue).

Certainly, it may improve performance to maintain / transfer the data in a format closer to a set of CREATE and INSERT statements than atomic XML.

You can create a SQLite database in many programs - I use MesaSQLLite. It is just a binary file. Here is an example of downloading and installing on iOS. I have not tested it on Android.

var win = Ti.UI.createWindow({
    backgroundColor: '#eee'
});

var button1 = Ti.UI.createButton({
    width: '60%',
    height: 30,
    top: 5,
    title: '1) Get Database'
});

var button2 = Ti.UI.createButton({
    width: '60%',
    height: 30,
    top: 40,
    title: '2) Install Database'
});

var button3 = Ti.UI.createButton({
    width: '60%',
    height: 30,
    top: 75,
    title: '3) Query Database'
});

var msg = Ti.UI.createLabel({
    top: 110,
    width: '80%',
    height: 120,
    text: 'click the buttons in order',
    font: {
        fontFamily: 'Courier',
        fontSize: 12
    },
    textAlign: 'center'
});

win.add(button1);
win.add(button2);
win.add(button3);
win.add(msg);

function localFile() {
    var localDbFile = 'testdb.db';
    return Ti.Filesystem.getFile(Titanium.Filesystem.applicationDataDirectory, localDbFile);
}

button1.addEventListener('click',
function() {
    msg.text = "Getting Database from Server";
    var xhr = Ti.Network.createHTTPClient();
    xhr.open("GET", "http://www.titaniumdevelopment.com.au/testdb.db");
    xhr.onload = function() {
        if (xhr.status == 200) {
            msg.text = "saving database";
            var file = localFile();
            file.write(this.responseData);
            msg.text = "database saved as " + file.nativePath;
        } else {
            msg.text = "Unable to get database. Response code was " + xhr.status;
        }
    };
    xhr.onerror = function() {
        msg.text = "Error fetching database";
    };
    xhr.send();
});

button2.addEventListener('click',
function() {
    var file = localFile();
    if (file.exists()) {
        msg.text = "installing database";
        Ti.Database.install(file.nativePath, 'test');
        msg.text = "database installed";
    } else {
        msg.text = "unable to find database";
    }
});

button3.addEventListener('click',
function() {
    try {
        var db = Titanium.Database.open('test');
        var rows = db.execute('SELECT * FROM TIPS');
        msg.text = 'ROW COUNT = ' + rows.getRowCount();
        rows.close();
        db.close();
    } catch(e){
        msg.text = "unable to open database";
    }
});

win.open();

Note: I'm not sure the App Store would approve this as they are not too keen on fetching data remotely.