Titanium Community Questions & Answer Archive

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

Database On App Upgrade

Hi All,

I am progressing in the development of my first Titanium mobile application (iphone only ATM) and have come across an issue with Database planning.

My application is a heavy CRUD application. The DB has a number of tables that are prepopulated as well as number of tables that are populated by the user at first run and significantly over time.

The DB is installed using Ti.Database.Install. I have placed all the DB functions into a share DB library JS file that is included in the sections that require DB access.

It has occurred to me that in the future I will wish to release updates to my application that will include new and modified prepopulated database tables.

In this instance does anyone have any idea how I would handle the database during the upgrade?

I don't want to endanger the users data.

As this is my first App I am not sure what is possible during upgrade. Does the API support running specific code just on upgrade?

For example is it possible to publish an upgrade that performs the database changes dynamically by executing SQL statments perhaps reading from flatfiles? I would hate to have to check for this each time the app starts.



— asked August 2nd 2010 by Nick The Geek
  • database
  • iphone
  • mobile
  • upgrade

6 Answers

  • I'm not sure what the best way to approach this issue, but what I'm considering is including a file within the resource folder which signals an update has been completed.

    So the app checks for the file if it exists run the database changes. Once complete delete the file.

    — answered August 2nd 2010 by John Welch
  • I had the same idea but I wanted to avoid testing on start each time. It sounds like this will be unavoidable.

    I might create a table that tracks database version info and the query that when the database install call is run. If the db version doesn't match the current version run the SQL from a specific file.

    — answered August 3rd 2010 by Nick The Geek
  • bump

    — answered August 22nd 2010 by Sindre Sorhus
  • Hi
    From my experience, the mysqllite DB that's embedded in your app is not upgraded when the user is having an update done through the AppStore. If you want to upgrade this DB you need to give it a new name like in this link
    From there if you want to keep the use data AND upgrade DB the best approach to me would be to install a news database, import userdata from the old one and erase the old DB.
    I haven't coded that for the moment, but I need this kind of trickery for future project I'm workin on.
    I'm also thinking of having 2 DB : a user generated one and an embeded one.

    — answered August 23rd 2010 by michel perrin
    • I went with 2 DBs. The problem is when you want to compare or check them against each other, it's possible, but it's a lot more job than if they where one DB.

      — commented August 23rd 2010 by Sindre Sorhus
    • you should use ONE DB and add to your table names a version number:

      table: user_v1 and user_v2

      — commented September 27th 2010 by Carl Jahn
  • -subscribing-

    — answered September 26th 2010 by Jicks Steen
  • I'd like to suggest (to those who are running into similar issue) to verify schema changes.

    Based on a similar discussion about "Updating new version to app store with different sqlite db structure"

    (1) Run snippet

    [INFO] 0 - cid
    [INFO] title - name
    [INFO] 1 - cid
    [INFO] tip - name
    [INFO] 2 - cid
    [INFO] newfield - name
    [INFO]  i: 0
    [INFO]  pragma user_version = 0

    (2) Alter your DB (MesaSqlite can be used for this purposes)

    (3) Upgrade application version field (you can just change from v1.0 to v1.1 at tiapp.xml)

    (4) Run snippet again and check console output:

    [INFO] 0 - cid
    [INFO] title - name
    [INFO] 1 - cid
    [INFO] tip - name
    [INFO] 2 - cid
    [INFO] tipCopyl - name
    [INFO]  i: 0
    [INFO]  pragma user_version = 1

    Pragma version detects schema changes then SQLite allows very limited changes to be done to existing tables. In general overall recommendations are:

    1. rename the old table,
    2. make a new table with the old name and with the correct column definitions,
    3. copy the data over, and
    4. drop the old table.

    Sample code consist of a couple of files that are few lines of generic SQL scripts and you may want to get it to work by using this DB. I expect this gives further ideas to get over the issue. Just my two cents here…

    Disclaimer: Tested on TiSDK 3.1.2.GA with iOS 6

    — answered September 4th 2013 by Eduardo Gomez
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.