How to overwrite a SQLite DB in iOS

Question

i have created an App in where i am using a SQLlite Database...I copy that Database, if needed, in the NSCaches Directory at the first start of the app with the following method:

- (void) copyDatabaseIfNeeded {

//Using NSFileManager we can perform many file system operations.
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSString *dbPath = [self getDBPath];
BOOL success = [fileManager fileExistsAtPath:dbPath];

if(!success) {


    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"datenbankSpeed"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

    if (!success)
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}
}

- (NSString *) getDBPath {

//Search for standard documents using NSSearchPathForDirectoriesInDomains
//First Param = Searching the documents directory
//Second Param = Searching the Users directory and not the System
//Expand any tildes and identify home directories.


NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory , NSUserDomainMask, YES);
NSString *documentsDir = [paths lastObject];
return [documentsDir stringByAppendingPathComponent:@"datenbankSpeed"];
}

My problem is, if i change sth in the Database- file and create a new App File for my Customers, they install the new App over the old App but the old Database is still in use, which will result in a crash!

maroux · Accepted Answer · 2013-04-29T17:53:39.143

0

I'm assuming the problem is only when you change your db schema. Create a version table in the sqlite database and add a column schema_version and populate it with a value from your code. Now, whenever you change your sql schema, update schema_version number in your code. In copyDatabaseIfNeeded, check if you have existing db file, open it and read schema_version. If this version is the same as your current version, then you're fine. Else, you need to migrate the schema. You'll probably also want to migrate the data into the new schema as well.

EDIT: To clarify - in copyDatabaseIfNeeded, do the following:

int version = ... // read schema_version from db
if (version != kCurrentSchemaVersion)
{
    // convert the schema into new version preserving data if required. 
    // this can be a multi-step process. Eg. if user directly upgrades to schema_version 3
    // after schema_version 1, first you'll convert from 1->2, then 2->3. 
}

You might also want to take a look at PRAGMA user_version as mentioned by @Martin in the comments.

edited Apr 29 '13 at 17:53

answered Apr 29 '13 at 17:17

maroux

3,764
3
23
32

1

I think you can use `PRAGMA user_version` for that purpose. – Martin R Apr 29 '13 at 17:22
Thanks for the info. You should add an answer explaining how to use it. – maroux Apr 29 '13 at 17:24
Yes, that sounds great, but how to do it exactly in my copyDatabaseIfNeeded?! Can you give an example?! – davidOhara Apr 29 '13 at 17:29
But what can i do with the databases, which do not have the new Column?! Is there any way to force the app to use the database? – davidOhara Apr 29 '13 at 19:29
Create a new column with a default value. – maroux Apr 29 '13 at 19:34
Yes, but i have customers who already get my Update from the app, and the app crashes because the old database file is still in use, you know what i mean?! – davidOhara Apr 29 '13 at 19:47
1

Yes, you need to release another update. In this update, ensure your db has version table. After launch, check if old db file exists, and if so, [check if it has the new column](http://stackoverflow.com/a/2520983/440060). If not, convert it into new schema format by executing relevant commands like `ALTER TABLE ADD COLUMN` etc. Hope this clarifies. – maroux Apr 29 '13 at 20:58

How to overwrite a SQLite DB in iOS

1 Answers1

Linked