SQLITE - Ensure data survives an App Update

Getting Started Newbie Questions
#1

Hi All, 

Another newbie question. I have created some levels for my game and stored them in a sqlite database. I would like to ensure that these survive an app update, as I store the level status (locked, unlocked) and highscores in the database.

local dbPath = system.pathForFile("levelScores.db3", system.DocumentsDirectory) local db = sqlite3.open( dbPath ) local tablesetup = [[CREATE TABLE IF NOT EXISTS worlds (id, image, status, highscore, name); INSERT INTO worlds VALUES (1, 'hardscape\_c.jpg', 'unlocked', '0', "Unknown I"); INSERT INTO worlds VALUES (2, 'fissure\_c.jpg', 'locked', '0', "Nova II");]] db:exec( tablesetup ) --Create it now. db:close() --Then close the database

Will the database be wiped off if the user updates the app?

#2

My understanding is that it will not be wiped after an update as long as you do not include the empty SQLite db in your applications build directory. If you do that then each time your app is installed it will come with an empty copy of your db which may overwrite what you have…  

You might want to consider adding a table and field where you keep track of your app version and check this at each start. This way after an update you can catch the fact that you may need to update some tables or add new fields etc and do what is needed via SQL statements. 

#3

A regular version update should not touch files in your system.DocumentsDirectory.  However if they delete the app and re-install it, it will be deleted when they delete the app.

Rob

#4

Thanks ksan and Rob.

@Ksan, I shall consider implementing a unique identifier, as it would help during upgrades.

#5

Hi all,

I have added a field for tracking the version in the database. Now I would like to upgrade the app and modify certain tables…How can I ensure that

a) Certain tables are untouched

b) Update certain columns in Table B… (one of the columns will certainly be the “version”)

Can someone point out the right way to use the Update table code in Lua/Corona?

#6

My understanding is that it will not be wiped after an update as long as you do not include the empty SQLite db in your applications build directory. If you do that then each time your app is installed it will come with an empty copy of your db which may overwrite what you have…  

You might want to consider adding a table and field where you keep track of your app version and check this at each start. This way after an update you can catch the fact that you may need to update some tables or add new fields etc and do what is needed via SQL statements. 

#7

A regular version update should not touch files in your system.DocumentsDirectory.  However if they delete the app and re-install it, it will be deleted when they delete the app.

Rob

#8

Thanks ksan and Rob.

@Ksan, I shall consider implementing a unique identifier, as it would help during upgrades.

#9

Just a friendly nudge to this query… 

#10

Hi,

this should work:

local statement = "UPDATE [tablename] SET [columname] = 'versionnumber'" db:exec(statement)

Best regards,

Tomas

#11

Hi all,

I have added a field for tracking the version in the database. Now I would like to upgrade the app and modify certain tables…How can I ensure that

a) Certain tables are untouched

b) Update certain columns in Table B… (one of the columns will certainly be the “version”)

Can someone point out the right way to use the Update table code in Lua/Corona?

#12

Just a friendly nudge to this query… 

#13

Hi,

this should work:

local statement = "UPDATE [tablename] SET [columname] = 'versionnumber'" db:exec(statement)

Best regards,

Tomas