Sqlite database maintenance

Games
#1

I started playing around with Sqlite. Very easy to use and quite powerful. Easy to create a DB, create tables, rows, and query. Lita is very nice, too.

How would you go about modifying a DB, though, on a subsequent release? This is tricky since most times you deal with a database, it’s centrally located and you have access to it with a DB admin tool (like Lita), but in this case, every customer that has your app has a copy of the database.

Let’s say you decide to add a column to a table. The DB already exists on the customer’s device, so you can’t simply create it again. Plus, you have the issue of existing data that you probably don’t want to blow away.

I suppose Sqlite supports ALTER TABLE, but you would only want to run that once, not every time the app fires up, since it would fail after the column is already there.

Maybe you just accept that failure and swallow the error. Anyone dealing with this? [import]uid: 58455 topic_id: 26109 reply_id: 326109[/import]

#2

Plan carefully.

The approach I use is a checkDB() routine that checks for a database ‘version’ number and runs the queries that will update the database incrementally.

Say I ship the app with dbVer == 1 and issue an update that changes the db in some way. I have a query of tables such that

[lua]query[1] = [[SQL to update from 1 to 2]]
query[2] = [[SQL to update from 2 to 3]]
–additional entries for incremental updates
query[#query+1] = [[SQL to update dbVer = 2]][/lua]

Every entry is an atomic update to the database so you can loop like

[lua]for i=currentDBVersion,#query do
db:exec(query[i])
end[/lua]

and get the updates applied incrementally and nondestructively, and only once instead of at every launch.

Still, “plan carefully” is the best advice. Avoid errors if you can with conditional SQL statements (e.g., “INSERT OR IGNORE”), or if you must “accept the failure” make sure it’s not a catastrophic one.

Not every db change can be made cleanly. You can’t use ALTER TABLE to rename a column, for example. Such a dilemma may get you into a corner from which you cannot gracefully exit.
[import]uid: 44647 topic_id: 26109 reply_id: 105726[/import]

#3

It’s a good question and one way to deal with it is to pre add some columns for future use. Not the cleanest and best looking way to deal with it :wink:

Joakim [import]uid: 81188 topic_id: 26109 reply_id: 105727[/import]

#4

Thanks…I like that approach. It can even be a problem when I have my clients start testing on their devices. In fact, much more likely to see schema changes here than in production. [import]uid: 58455 topic_id: 26109 reply_id: 105754[/import]

#5

I previously had issues with an update as well because I had to add columns. I could never get everything to work without issues so I just added a new table with the additional columns. I also made sure to do what Joakim suggested, and I added additional columns for future use just in case. [import]uid: 31262 topic_id: 26109 reply_id: 105756[/import]

#6

Adding columns at random is inefficient and doesn’t necessarily help.

If you want to know (in code) which columns your tables have execute this query:

pragma table_info(table_name)

and it will return a dataset containing info on all of the columns in specified table.

To then add columns to a table just use:

alter table (table_)name) add column (column_definition_goes_here).

The workflow for updating user DBs then becomes:

* Execute the pragma command
* Check the resulting dataset for the presence of the expected column
* If the column doesn’t exist, execute the alter command

Easy, clean and sensible…

[import]uid: 45444 topic_id: 26109 reply_id: 111474[/import]