replace old SQLite3 database with new but keep parts of content from old db

[nlc]

As the topic says, is there any good pattern for that? As it is now I have a SQLite3 database and want to use couple of columns from the old db in the next version that is a new and bigger db.

For example. old database has a table with animals and a list of properties like "I have seen this animal" stores a value for it. When I replace the old database with a new one that has more information about the animals I want the users profile for the saves to be transfered to the new one. Or something similar...

If someone followed, any solutions or suitable patterns are welcome. As it is now I have thought of different options but none that feels just "right"

Thanks

[iisword]

would like to know this too...

[Fuzzy Logic]

Not so sure what's the right way of doing this, but I can tell you how I'm doing it.
For starters, it would be easier for the future to keep a field with the version of the database, because you never know how many times you'll want to change the structure and your update needs to handle what ever version the user upgrades from.

I'm guessing you probably have some sort of a method that checks if the db is in the user directory and if not, it creates a template db (possibly by copying an empty db from the bundle), and then initialize the objects that provide the interface to the db. What I do is, after checking if the db is present, I check for missing columns (and for later versions of my app a db version field), and make the needed changes to the db based and the version of the db I'm updating from.

Here's a small part of my code for example (ugly... I know... it's one of those quick hacks that I never get to rewrite). Notice that I use a general query and check for number of columns in the db, and then I use ALTER TABLE sql command to add another column scrollspeed of type real to table song.

- (void)checkDatabaseAndCorrectIfNeeded{
int success;
	//check for version 1.1 missing features
	sqlite3_stmt *checkColumns;
	const char *sqlCheckColumns = "SELECT * FROM song";
	if (sqlite3_prepare_v2(database, sqlCheckColumns, -1, &checkColumns, NULL) == SQLITE_OK){
		if (sqlite3_column_count(checkColumns) <= 6){
			//Update version 1.1 changes
			sqlite3_stmt *addScrollSpeedColumn;
			const char *sqlAddScrollSpeedColumn = "ALTER TABLE song ADD scrollspeed REAL";
			if (sqlite3_prepare_v2(database, sqlAddScrollSpeedColumn, -1, &addScrollSpeedColumn, NULL) == SQLITE_OK) {
				// Execute the query.
				success = sqlite3_step(addScrollSpeedColumn);
				// Handle errors.
				if (success != SQLITE_DONE) {
					NSAssert1(0, @"Error: failed to alter the database with message '%s'.", sqlite3_errmsg(database));
				}
				sqlite3_finalize(addScrollSpeedColumn);
			}
....

(Obviously it's not all of it...)

I hope that helps a bit... there's probably a better (and cleaner) way to do that...