Titanium Community Questions & Answer Archive

We felt that 6+ years of knowledge should not die so this is the Titanium Community Questions & Answer Archive

Push incremental database updates to app - Best Practices

Hello,

I am doing some proof-of-concept work on an app that searches a local SQLite database.

My requirements:

  1. Application database must be updatable independently of Application updates (Database updates are more frequent that Application updates)
  2. Database updates are small, might occur on a weekly basis, and could be fetched as plain SQL via REST and applied by the app to the local db
  3. There will be no user data in the db, it is read-only for the user

Proposed process:

  • Fetch update as sql string containing a dozen or so records via REST
  • Backup installed database (not the one in Resources)
  • Apply sql to database
  • If all goes well, delete the backup
  • If not, replace the currently installed db with the backup and offer to download the entire database file in its latest version

Questions:

  1. Do I have direct access to the installed database file via Ti.Filesystem?
  2. If so, what is the path to it?
  3. Is there a better way of doing this altogether?

Thanks

— asked March 18th 2011 by Henning Glatter-Gotz
  • android
  • iphone
  • mobile
  • rest
  • sqlite
2 Comments

  • Depends. I am not sure about the android filesystem. But why do you need to replace the database altogheter? Why not just add info to the current database?

    if There will be no user data in the db, it is read-only for the user I assume that every data will be received from the REST, right?

    — commented March 18th 2011 by Daniel Ander

  • I actually am proposing what you suggest - Look at the 3rd bullet in Proposed process.
    Replacing the whole database would only happen if the update fails for some reason. This would simply be a fallback in case something goes wrong. Maybe this is overkill, but that is why I am also asking if there is a better solution for this.

    — commented March 18th 2011 by Henning Glatter-Gotz

3 Answers

  • Accepted Answer

    Ti.Filesystem.applicationSupportDirectory + '/database/databasename.sql'

    — answered March 18th 2011 by Sj Singh
    permalink
    1 Comment

    • Thanks Sj! I would also be interested in your opinion on #3.

      — commented March 18th 2011 by Henning Glatter-Gotz

  • Here is my code that I used, and was able to update 2 apps that have been published for a few weeks. I needed to edit 2 of my tables, and this worked flawlessly, preserving data that was already there.

    var upgrade=Titanium.App.Properties.getString("upgrade");
var file = Titanium.Filesystem.getFile(Titanium.Filesystem.applicationSupportDirectory+ '/database/plotme.sqlite.sql');


if(file.exists()) {
     //Ti.API.info('file exists');

                     if (upgrade<"1.1")
                    {

                        var db = Titanium.Database.install('plotme.sqlite','plotme.sqlite');
                        var sql="ALTER TABLE locations ADD username varchar(244)";
                        //Ti.API.info(sql);
                        db.execute(sql);
                        var sql="ALTER TABLE locations ADD myhash varchar(244)";
                        //Ti.API.info(sql);
                        db.execute(sql);
                        Titanium.App.Properties.setString("upgrade","1.1");
                        //Ti.API.info('database upgrade');

                    }

            } //end file exist check
     else
     {
         //Ti.API.info('file does not exist');
         var db = Titanium.Database.install('plotme.sqlite','plotme.sqlite');
         Titanium.App.Properties.setString("upgrade","1.1");

      }

    Here's how it works

    • first checks the "upgrade" status in the properties, to see what version I'm on, and to skip if needed
    • it checks to see if the database is already there (because if its not an upgrade, but a fresh install, it doesnt need to do the table changes. If it doesnt exist, non of this code runs
    • if it does exist, if the version is not 1.1 (the version I'm upgrading to), then run the code
    • the .install runs, but since its already there, it doesnt matter
    • runs my ALTER statements
    • sets my "upgrade" flag

    Thats it. If the file didnt exist, it would install the database that already has the changes, and sets the "upgrade" flag anyways. Now when I upgrade again, I can just add more code if needed, set the "upgrade" to whatever version, and I'm done.

    One problem I did have was checking if the file exists. My database is called plotme.sqlite, but I still had to append the .sql to the end of that.

    — answered March 18th 2011 by Josh Lewis
    permalink
    1 Comment

    • Josh, thanks for sharing your solution! I would be curious to know how you deal with exception handling in this particular case. It seems that db.execute() and Ti.Database.install() could throw an exception (looking at the objective-c source).

      — commented March 21st 2011 by Henning Glatter-Gotz

  • Now granted, mine doesnt go out and fetch, but the principle is about the same, setting flags to check the status, installing the new db, etc.

    — answered March 18th 2011 by Josh Lewis
    permalink
    0 Comments
The ownership of individual contributions to this community generated content is retained by the authors of their contributions.
All trademarks remain the property of the respective owner.