Database upgrade in a new version of a game

Solar2D General Questions/Discussion
#1

Hi,
If I add a db table or columns to an update of my game that are already in the stores, Does that count as a new database file or the same?

At startup i check if there is a db file in system.DocumentsDirectory if it is not i create it by getting the template file i have in system.ResourceDirectory.

So if i change the template in system.ResourceDirectory for the update, will it still keep all progress made in the game, and just updating the database with new tables and columns? [import]uid: 126729 topic_id: 29923 reply_id: 329923[/import]

#2

With the way you are doing it will wipe out their data. You are much better off handling all of the db creation/alterations directly through the sqlite api. [import]uid: 147305 topic_id: 29923 reply_id: 119963[/import]

#3

So you mean that my code will receive it as no file exists, even though there will be a data.sqlite in system.DocumentsDirectory, because of the changes in the db structure?

My code looks like this:
[lua]local path = system.pathForFile(“data.sqlite”, system.DocumentsDirectory);
local file = io.open(path, “r”);

local function dbInit()
if(file==nil) then
–DATABASE NOT PRESENT
local pathSource = system.pathForFile(“data.sqlite”, system.ResourceDirectory);
local fileSource = io.open(pathSource, “r”);
local contentSource = fileSource:read("*a");

local pathDestination = system.pathForFile(“data.sqlite”, system.DocumentsDirectory);
local fileDestination = io.open(pathDestination, “w”);
fileDestination:write(contentSource);

io.close(fileDestination);
else
–DATABASE PRESENT
end
end
dbInit()[/lua]

I already have it this way in an app deployed to the stores. What can i do to minimize the damage? [import]uid: 126729 topic_id: 29923 reply_id: 120040[/import]

#4

Erik, the way you’re doing it in that code it will not recreate the db with the new table unless you actually delete the db from the documents directory first.

What you should do instead of only check if the file exists is check what the db structure of the resource-directory table is as well, iterate through all tables, and if you recognize a table in the resource-db that doesn’t exist in the documents-db then create it directly through the sqlite api. That way you wont lose any data in the process [import]uid: 14018 topic_id: 29923 reply_id: 120044[/import]

#5

Thanks for the help, it did wonders :slight_smile: [import]uid: 126729 topic_id: 29923 reply_id: 120049[/import]

#6

With the way you are doing it will wipe out their data. You are much better off handling all of the db creation/alterations directly through the sqlite api. [import]uid: 147305 topic_id: 29923 reply_id: 119963[/import]

#7

So you mean that my code will receive it as no file exists, even though there will be a data.sqlite in system.DocumentsDirectory, because of the changes in the db structure?

My code looks like this:
[lua]local path = system.pathForFile(“data.sqlite”, system.DocumentsDirectory);
local file = io.open(path, “r”);

local function dbInit()
if(file==nil) then
–DATABASE NOT PRESENT
local pathSource = system.pathForFile(“data.sqlite”, system.ResourceDirectory);
local fileSource = io.open(pathSource, “r”);
local contentSource = fileSource:read("*a");

local pathDestination = system.pathForFile(“data.sqlite”, system.DocumentsDirectory);
local fileDestination = io.open(pathDestination, “w”);
fileDestination:write(contentSource);

io.close(fileDestination);
else
–DATABASE PRESENT
end
end
dbInit()[/lua]

I already have it this way in an app deployed to the stores. What can i do to minimize the damage? [import]uid: 126729 topic_id: 29923 reply_id: 120040[/import]

#8

Erik, the way you’re doing it in that code it will not recreate the db with the new table unless you actually delete the db from the documents directory first.

What you should do instead of only check if the file exists is check what the db structure of the resource-directory table is as well, iterate through all tables, and if you recognize a table in the resource-db that doesn’t exist in the documents-db then create it directly through the sqlite api. That way you wont lose any data in the process [import]uid: 14018 topic_id: 29923 reply_id: 120044[/import]

#9

Thanks for the help, it did wonders :slight_smile: [import]uid: 126729 topic_id: 29923 reply_id: 120049[/import]

#10

Erik,

I had a similar issue for an app I am currently working on.

I anticipate several versions of the app as time goes on, and so needed a watertight way of handling database upgrades as users install later versions of the app, even handling cases where users have skipped an earlier version.

The way I handled it was to have a table called “dbversion”, with a single column “currentversion”, which holds the current version no. (e.g. 1) of the database on the device.

When the app launches and initializes SQLite, the first thing it does is compares the value of this “currentversion” column against the version of the database that the app requires (e.g. 3).

If an upgrade is required, the required changes are made for each version in turn until reaching the latest version. I.e. from version 1 to 2, then version 2 to 3, etc.

It seems to work really well for my app.

Hope it helps. :slight_smile: [import]uid: 60457 topic_id: 29923 reply_id: 121926[/import]

#11

Erik,

I had a similar issue for an app I am currently working on.

I anticipate several versions of the app as time goes on, and so needed a watertight way of handling database upgrades as users install later versions of the app, even handling cases where users have skipped an earlier version.

The way I handled it was to have a table called “dbversion”, with a single column “currentversion”, which holds the current version no. (e.g. 1) of the database on the device.

When the app launches and initializes SQLite, the first thing it does is compares the value of this “currentversion” column against the version of the database that the app requires (e.g. 3).

If an upgrade is required, the required changes are made for each version in turn until reaching the latest version. I.e. from version 1 to 2, then version 2 to 3, etc.

It seems to work really well for my app.

Hope it helps. :slight_smile: [import]uid: 60457 topic_id: 29923 reply_id: 121926[/import]