how to you alter a sqlite db with coolstorage on wp7?

Question

I'm writing an app for wp7 using coolstorage and sqlite as the database.

The reason I chose sqlite was because it appears to have a good ability to do updates to the schema.

However, in my testing I can run an 'alter table...' command but if I add a column and then try to read that column I get an error that the column doesn't exist.

But if I close the app and run it again, the column is there.

I thought about trying to close my connection, but I don't know how to do that using coolstorage. Is there a way? Would that work?

I'm new to both Coolstorage and SQLite so any help is appreciated.

How can I alter a sqlite schema without closing and re-oppening the app?

[Update: 15 Dec 2011] We avoided this problem by making sure we ran our schema updates before querying the affected tables, which is probably the right thing to do anyway.

If anyone comes up with a way to do the schema change after running a query against a table, I would still be interested in knowing.

Answer 1

Sqlite has the ability to add columns to the end of existing tables in-place, however any queries which have already been prepared will not show the changes. In my experience, if you prepare the query again, it works. Alternatively, you could close and re-open the database. Note that other schema changes are not directly supported, and will require you to do the following:

1. Rename the original table
2. Create a new table with the desired changes.
3. Copy the data from the old table to the new one, as applicable.
4. Delete the original table.