SQLite Question - before I release the app

[howcr]

Hi,

I am using SQLite (SQLite without Core Data) in an app for the first time and I am ready to submit the app for approval but I have a question about handling database upgrades:

Question:

1. I am going to make the assumption that I will have to either add another column or another table to the SQLite database at some point in the future. Is there a way to properly manage future upgrades to the db by creating some sort of db versioning in my app from the very beginning?

For example if I currently have a table with 3 columns (id, FirstName, LastName) and I find out I need to add a column called JobTitle after the app is Live, how can I ensure that adding this new column will not crash the app when users upgrade?

I am trying to avoid any problems that may 'break' the database in a future release because I didn't properly prepare for handling upgrades from the beginning.

Any advice on this topic would be appreciated.

Regards,
Howcr

[timle8n1]

Originally Posted by howcr

I am going to make the assumption that I will have to either add another column or another table to the SQLite database at some point in the future. Is there a way to properly manage future upgrades to the db by creating some sort of db versioning in my app from the very beginning?

Sure, I'm going to assume you have a method to check if the DB exists in your applications documents directory (so you can write to it) and if not creates it.

So what I did was add a table to this database, call it whatever you like. Add a column that represents the version of the database (an int - 0,1,2,3). Add a row that represents the db version for the application.

Each time you change the db in a release of your application increment the expected number.

If the existing number does not match the expected number, perform SQL to bring the DB up to spec. Remember the user may skip releases, so if 0 was the first version you might have someone that does not upgrade until version 3 - so version 3 needs to know how to go from 0, 1, or 2 to 3.

[howcr]

Originally Posted by timle8n1

Sure, I'm going to assume you have a method to check if the DB exists in your applications documents directory (so you can write to it) and if not creates it.

So what I did was add a table to this database, call it whatever you like. Add a column that represents the version of the database (an int - 0,1,2,3). Add a row that represents the db version for the application.

Each time you change the db in a release of your application increment the expected number.

If the existing number does not match the expected number, perform SQL to bring the DB up to spec. Remember the user may skip releases, so if 0 was the first version you might have someone that does not upgrade until version 3 - so version 3 needs to know how to go from 0, 1, or 2 to 3.

Hi,

Thanks, it makes sense. Yes, I check if the DB exists, else I create it.

So when you have to, down the road, add a column to table 'x' (or create a new table) are you simply modifying your database.sqlite file and including that modified file in the app bundle OR are you doing something like this:

// current db version should be 3, the users app is still on version 2 so let's perform upgrade:
CREATE TABLE newtable.....
UPDATE TABLE oldtable1....

Regards,
Howcr

[timle8n1]

Originally Posted by howcr

So when you have to, down the road, add a column to table 'x' (or create a new table) are you simply modifying your database.sqlite file and including that modified file in the app bundle OR are you doing something like this:

// current db version should be 3, the users app is still on version 2 so let's perform upgrade:
CREATE TABLE newtable.....
UPDATE TABLE oldtable1....

Regards,
Howcr

Well that depends if you want to preserve the data created by the user then SQL statements to modify the tables are in order. If you don't need to preserve the data then shipping a new database would be the way to go.

[howcr]

Originally Posted by timle8n1

Well that depends if you want to preserve the data created by the user then SQL statements to modify the tables are in order. If you don't need to preserve the data then shipping a new database would be the way to go.

Great. Thanks for your help!

[Bertrand21]

Originally Posted by howcr

Great. Thanks for your help!

I added 8 columns to my database and no one had crashes. All I dud was check if the columns already exist. If they do not then I do an ALTER statement to add my new columns.

[howcr]

Hi, how did you check if the columns already exist?

[Bertrand21]

Originally Posted by howcr

Hi, how did you check if the columns already exist?

Check if new columns are there

-(BOOL)doesDatabaseContainNewColumns{
	NSLog(@"Checking Settings Data");
	sqlite3 *database;
	sqlite3_stmt *compiled_statement;
	if(sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK) {
		NSString *formatedSql = [NSString stringWithFormat:@"SELECT newColumn1,newColumn2,newColumn3,newColumn4,newColumn5,newColumn6,newColumn7 FROM MyTableName LIMIT 1"];
		const char *sql = [formatedSql UTF8String];
		
		if (sqlite3_prepare_v2(database, sql, -1, &compiled_statement, NULL) != SQLITE_OK) {
			return NO;
		}
		else{
			return YES;
		}
		
	}
	
}

If they are not there create them...

-(NSInteger)addNewColumnsToDatabase{
	sqlite3 *database;
	NSMutableArray *columns = [[NSMutableArray array] retain];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn1 integer DEFAULT '1';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn2 integer DEFAULT '1';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn3 double DEFAULT '10';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn4 double DEFAULT '10';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn5 integer DEFAULT '1';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn6 integer DEFAULT '1';"];
	[columns addObject:@"ALTER TABLE MyTableName ADD COLUMN newColumn7 double DEFAULT '1.0';"];
	
	sqlite3_stmt *compiled_statement;
	for (NSString *statement in columns) {
		if(sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK) {
			NSString *formatedSql = statement;
			const char *sql = [formatedSql UTF8String];
			
			if (sqlite3_prepare_v2(database, sql, -1, &compiled_statement, NULL) != SQLITE_OK) {
			
			}
			sqlite3_step(compiled_statement);
			
			sqlite3_reset(compiled_statement);
		}
	}
	
	[columns release];
    return 1;
}

[howcr]

Great, thanks for the snippet. I appreciate it.