I am using sqlite for my iphone and I anticipate the database schema might change over time. What are the gotchas, naming conventions and things to watch out for to do a successful migration each time?
For example, I have thought of appending a version to the database name (e.g. Database_v1).
I maintain an application that periodically needs to update a sqlite database and migrate old databases to the new schema and here's what I do:
For tracking the database version, I use the built in user-version variable that sqlite provides (sqlite does nothing with this variable, you are free to use it however you please). It starts at 0, and you can get/set this variable with the following sqlite statements:
> PRAGMA user_version;
> PRAGMA user_version = 1;
When the app starts, I check the current user-version, apply any changes that are needed to bring the schema up to date, and then update the user-version. I wrap the updates in a transaction so that if anything goes wrong, the changes aren't committed.
For making schema changes, sqlite supports "ALTER TABLE" syntax for certain operations (renaming the table or adding a column). This is an easy way to update existing tables in-place. See the documentation here: http://www.sqlite.org/lang_altertable.html. For deleting columns or other changes that aren't supported by the "ALTER TABLE" syntax, I create a new table, migrate date into it, drop the old table, and rename the new table to the original name.
The best solution IMO is to build a SQLite upgrade framework. I had the same problem (in the C# world) and I built my own such framework. You can read about it here. It works perfectly and makes my (previously nightmarish) upgrades work with minimal effort on my side.
Although the library is implemented in C#, the ideas presented there should work fine in your case also.