Your database remains intact. But if you want to upgrade your database, make sure you do so in such a way that you can upgrade users who have older versions than just your previous one. Not everyone will update to each new version you release.
I use "pragma user_version" to determine the current database schema version. Then I update the database, and then increment the user_version (pragma user_version = [your new version]
.
Unfortunately with SQLite, you can't delete columns. Who ever came up with that brain dead idea to not allow deleting of columns should be shot. So that makes fixing up past model mistakes complicated because you either have to just leave the column there and add a new column, or migrate your data to a new table that doesn't have the old column. Ugh.
Anyway, just thought I'd get that off my shoulders. :-)
I use FMDB for my SQLite database access, so this code may not work with your setup, but it might point you in the right direction on how to implement it for your needs:
Code:
+ (void)executeSqlFileAtPath:(NSString *)path forDatabase:(FMDatabase *)database {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
if ([fileManager fileExistsAtPath:path]) {
NSString *sql = [[NSString alloc] initWithContentsOfFile:path encoding:NSUTF8StringEncoding error:&error];
// loop through lines in string;
if (sql != nil) {
NSArray *sqlStatements = [sql componentsSeparatedByString:@";\n"];
for (NSString *sqlStatement in sqlStatements) {
BOOL success = [database executeUpdate:[sqlStatement stringByAppendingString:@";"]];
}
}
[sql release];
}
}
// migration support so we can upgrade the database when new features come out.
+ (BOOL)migrateDatabase:(FMDatabase *)database {
BOOL success = YES;
// get current database version of schema
int currentVersion = 0;
int databaseVersion = [database intForQuery:@"pragma user_version;"];
if (databaseVersion) {
currentVersion = databaseVersion;
}
NSString *migrationFilePath = nil;
// get latest database version number from Info.plist distributed with app
int latestVersion = [[[[NSBundle mainBundle] infoDictionary] objectForKey:@"TFDatabaseVersion"] intValue];
// if current version is older than latest version, then perform migration
// read all migration sql files from current version + 1 to latest version
// execute sql from each file in order.
if (currentVersion < latestVersion) {
[[NSNotificationCenter defaultCenter] postNotificationName:kMigratingDatabaseNotification object:NULL];
}
for (int i = currentVersion + 1; i <= latestVersion; i++) {
migrationFilePath = [NSString stringWithFormat:@"%@/migration.%i.sql", [[NSBundle mainBundle] resourcePath], i];
// load the sql file into memory and execute each sql block
[DBUtils executeSqlFileAtPath:migrationFilePath forDatabase:database];
}
if (currentVersion < latestVersion) {
[database executeUpdate:[NSString stringWithFormat:@"pragma user_version = %i;", latestVersion]];
[[NSNotificationCenter defaultCenter] postNotificationName:kMigratingDatabaseCompleteNotification object:NULL];
}
return success;
}
Oh ya, I store all my database migrations in files called migration.0.sql, migration.1.sql, ... migration.n.sql. I load them in one by one and execute the sql contained within them. Works really well.
Hope this helps a bit.
What happens to a user's database when you submit a new version.
Linear Mode
