Click here to Skip to main content
15,071,518 members
Articles / Database Development
Tip/Trick
Posted 16 Aug 2011

Tagged as

Stats

18.9K views
6 bookmarked

Updating an SQLite database

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
21 Aug 2011CPOL2 min read
Updating an SQLite database to a new version and copying the contents of the old one into the updated 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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mike Hankey
Retired
United States United States
Currently enjoying retirement and working on projects without pressure, deadlines or any kind of management.

Comments and Discussions

 
GeneralGood checklist. Pin
Dr.Walt Fair, PE16-Aug-11 16:23
professionalDr.Walt Fair, PE16-Aug-11 16:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.