Click here to Skip to main content
12,820,521 members (29,692 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

12.8K views
5 bookmarked
Posted 16 Aug 2011

Updating an SQLite database

, 21 Aug 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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
I'm old and I have nothing more to say!

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralGood checklist. Pin
Walt Fair, Jr.16-Aug-11 17:23
subeditorWalt Fair, Jr.16-Aug-11 17: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 21 Aug 2011
Article Copyright 2011 by Mike Hankey
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid