iPhone SQLite File management

Question

I'm developing an application and I'm using SQLite as my database storage. I want to add and update the sqlite database throughout my application duration, so i copied it to the documents directory so i can do all kinds of stuff with my database except only selecting from it.

But since I'm developing "time", my database changes frequently (the database architecture is made by another developer) through the process

On the iPhone i'm checking

1. if the database exists in the documents directory
2. (if not) copy it to it.
3. use that database

but when i want to update my database ( i made in a separate sqlite manager ) it fails through the process of copying the newer version because i'm only checking if the database exists.

Does anyone of you guys experienced the same kind of problem ? and what did you do to pass this kind of problem ..

my only idea was to create a settings table and hold a row that check's for a version number of the database..

Is there also a way to edit the sql file from the documents directory in any sqlite management tool ?

or are there better solutions ?

Answer 1

Did you try to delete application on iPhone, and after that to deploy application again?

Answer 2

What i did in my app: - First i do the same thing. Check whether DB is in Documents or not. - Then i check the previously installed version: small text file in documents tells me this - If there is a need of an update, i merge the databases: the one in /Documents with the another one from app bundle. - In my case i need to merge. In yours may be you can just copy over, if user doesn't change it.

Answer 3

What I do is create a series of sql scripts that are capable of upgrading from a previous version 1.sql, 2.sql, 3.sql, etc. When i open my database i query the version from a settings table and compare it with the newest .sql file i've found in my application bundle. If there is a newer version i begin running the .sql scripts until i have encountered the latest version. This has worked out pretty well for me on a couple of projects now.

Answer 4

What I do is similar to what you did, except I added a small second table to the database. This database just contains one record - a database version number. So you start with version 1 in this database table. Then, when you change the structure, you update this version number to 2. In your startup code, check to see if the database exists. If so, check the version number of the database. If the version number of the database matches what you expect, just continue. If it's less than what you expect, call some code to upgrade the database to the current version and then continue.