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.
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.
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.
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.
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:
- rename the old table,
- make a new table with the old name and with the correct column definitions,
- copy the data over, and
- 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