Updating an SQLite database

I have been developing a VS2010 extension for my own use and while using it, I kept adding features and changed the database schema quite frequently. Since I hadn't planned on it growing like it did, I updated the database using the old band aid method until it finally got out of hand and I had to go back and do a little bit better design on the way I was doing my updates.

I should have used a table to store the current DB version number to begin with, but like I said I hadn't intended on releasing it. Basically I check the current assembly Version number and if the assembly version is greater than the DB version, I do my updates then update the record in the DBVersion table. In this case, I have decided that there are so many changes to the schema that I am going to create a new database with the new schema then copy all the data from the old database if one exists. Meaning that if one exists they are upgrading to the new version of the extension if not then it's a new install and I don't have to worry about doing the update. I make one assumption in my logic that being that there are no fields in any of the old tables that aren't in the new one. i.e. I have added fields but not deleted any in the new database schema.

Having said that, here are the steps needed and some of the code I used to accomplish this:

1. Create the new database, tables and fields using CREATE TABLE[^]
   
2. Attach the old database using ATTACH[^]
   
3. Get a list of all user tables in the old database using "select * from old.sqlite_master where name not like 'sql%'".
   

   The structure returned has the following fields;
        type - which in this case will always be 'table'
        name - Name you gave the table
        tbl_name - same as name
        rootpage - page where this data begins
        sql - the CREATE TABLE sql used to create the table
   

   
   
4. Check each table name against a list of table names I want updated and if on list proceed.
   
5. Retrieve the old tables schema information using 'PRAGMA old.table_info(name_of_the_table)' where old is the value that you used in the ATTACH statement.
   

   The structure returned is;
       cid - field id number
       name - field name
       type - INTEGER, NVARCHAR, etc.
       notnull - either 99 or 0 indicating if present or not
       dflt_value - the default value if any
       pk - 0 or 1 1 if is a primary key and 0 if not.
   

   
   
6. Build an INSERT [^] statement using the name field returned by previous step.
   An example INSERT would be:
   

   INSERT INTO new_table_name (field1, field2, ...) SELECT field1, field2, ... FROM old_table_name

   
   
7. goto step 7
   

This worked in my situation and thought I would share it.
Happy trails!