Click here to Skip to main content
Click here to Skip to main content

Upgrade framework for SQLite databases

By , 1 Feb 2009
Rate this:
Please Sign up or sign in to vote.

upgrade.png

Introduction

If you are like me, you absolutely hate to write the database code needed to upgrade existing customer database installations to the newest database schema.

After looking around in the Internet, I came to the conclusion that there are no free and good solutions (actually, I did not find any commercial ones either) that I can use to support these upgrades. So I had to write my own Smile | :)

Before delving any deeper into the architecture of the solution, I have to mention that the upgrade framework I'm going to show you does not include support for triggers, views, or any fancy constraints (like foreign key constraints that are not really supported in SQLite anyway). So, if you really want to use it to your advantage, I'd suggest you either drop them from your schema, or extend the framework to support them.

The framework does support simple tables and indexes pretty well, which I think will be enough to make this framework useful to most developers.

So, without further ado, let’s start with some definitions.

Definitions

  • Database Schema - The meta-data that describes the structure of a given database without considering the actual data it contains. For example, a database schema describes the fields of a table or the columns of an index.
  • Database Upgrade Module - A module that knows how to upgrade a database that has an X database schema and convert it to a database that has a Y database schema.
  • Upgrade Path - A list of modules that, when applied successively, will upgrade a database from the source schema to the target schema. For example, if we have an upgrade module that knows to upgrade from schema X to schema Y, and another upgrade module that knows how to upgrade from schema Y to schema Z, then we can apply them successively in order to upgrade from schema X to schema Z.
  • Optimal Upgrade Path - All the upgrade modules in the system form a kind of an upgrade graph where every node is an upgrade module, and the arcs connect one upgrade module to another if the destination schema of one upgrade module is the source schema of another upgrade module. In order to shorten the upgrade time, we always want to find the shortest path in the upgrade graph. In my framework, I use a simple BFS algorithm for this purpose.

The following picture illustrates the definitions above:

upgrade1.png

In this picture, we see that originally, the database went through three separate schema upgrades (T, R, and W) before changing to the final Z schema. At this point, the developers wanted to make the transition from schema X to schema Z faster for existing clients, and so they chose to write a separate upgrade module that knows how to upgrade from schema X to schema Z directly.

The upgrade path from X to Z using the x->z upgrade module (marked with orange color) is the optimal upgrade path because it involves the minimal number of upgrade modules.

Framework Architecture

The upgrade framework supports two vital operations in order to make it useful:

  1. Loading all upgrade modules into memory and creating the upgrade graph in memory. In this phase, every upgrade module is queried for its ‘FROM’ schema and its ‘TO’ schema. Every schema is assigned a separate identifier, and a graph is constructed with these identifiers as its nodes.
  2. Doing the DB upgrade. This is done by executing the following actions:
    1. Loading and parsing the database schema of the database file we want to upgrade.
    2. Find the optimal upgrade path (the list of upgrade modules) that is needed in order to upgrade from the database schema of the database file to the schema that was specified as the destination schema.
    3. Clone the original database file into a temporary file (this will prevent changes to the original database in case the upgrade fails).
    4. Run every upgrade module in the upgrade path. For every upgrade module, verify that the database schema was indeed changed to the ‘TO’ schema defined in the upgrade module after it finished running.
    5. Once all upgrade modules finish running, vacuum the database in order to defragment it.
    6. Rename the temporary database file to have the name of the original database file and delete the original database file.

The following diagrams illustrate the key players in the framework architecture:

upgrade2.png

The basic building block of the upgrade process is the IDbUpgrader interface which represents a single upgrade module that knows how to upgrade a database file that has a ‘From’ schema to a database file that has a ‘To’ schema. Every upgrade module also has a Name property that is used during the process for progress notifications.

/// <summary>
/// This interface should be implemented by all upgrade modules. It provides the
/// information and actions that are required by the upgrade manager module in order
/// to perform DB upgrades.
/// </summary>
public interface IDbUpgrader
{
    #region Events
    /// <summary>
    /// Fired whenever some progress is made in the upgrade module (0-100)
    /// </summary>
    event DbUpgradeProgressEventHandler DbUpgradeProgress;
    #endregion

    #region Properties
    /// <summary>
    /// The DB schema from which the upgrade module performs the upgrade.
    /// </summary>
    DbSchema FromSchema
    {
        get;
    }

    /// <summary>
    /// The DB schema to which the upgrade module performs the upgrade.
    /// </summary>
    DbSchema ToSchema
    {
        get;
    }

    /// <summary>
    /// The name of the upgrade module module (used to display progress information).
    /// </summary>
    string Name
    {
        get;
    }
    #endregion

    #region Methods
    /// <summary>
    /// Upgrade the specified DB file to my ToSchema schema.
    /// </summary>
    /// <param name="dbPath">The DB file to upgrade</param>
    void Upgrade(string dbPath);

    /// <summary>
    /// Cancels the upgrade
    /// </summary>
    void Cancel();
    #endregion
}

Every upgrade module supports the Upgrade method and a Cancel method that are called from the upgrade manager module. It also supports progress notifications via the DbUpgradeProgress event.

All automatic upgrade code is located in the BasicDbUpgrader class that implements the IDbUpgrader class and provides various hooks that can be used by derived classes in order to override some (or all) of the decisions made by the basic upgrade module code.

The default behavior of the basic database upgrade module class is to allow all actions that are needed in order to transform the database file schema to its ‘To’ schema. In order to support fine-tuning where needed, the basic upgrade module provides the following hooks:

  • AllowIndexAddition – returning false will instruct the upgrade module not to add the index whose name is specified in the call. This allows the concrete class to add the index later when its DoSpecificUpgrades method is called.
  • AllowIndexDeletion – returning false will instruct the upgrade module not to delete the index whose name is specified in the call. This allows the concrete class to delete the index later when its DoSpecificUpgrades method is called.
  • AllowTableAutoUpgrade – returning false will instruct the upgrade module not to do automatic upgrade to a table. This allows the concrete class to handle the upgrade issue later when its DoSpecificUpgrades method is called. The basic upgrade module will try to choose the least costly way to upgrade a table – ALTER TABLE if possible, or a complete re-write of the existing table otherwise.
  • AllowTableCreation – returning false will instruct the upgrade module not to create the table whose name is specified in the call. Note that a new table is created without any data. If you need to add rows to such tables, you can use the DoSpecificUpgrades hook. Note that when a table is created, all of its indexes are created as well so you won’t get separate AllowIndexAddition hook calls.
  • AllowTableDeletion – returning false will instruct the upgrade module not to delete the table whose name is specified in the call. Note that when a table is deleted, all of its indexes are deleted as well so you won’t get separate AllowIndexDeletion hook calls.
  • AllowTableReplacement – The upgrade module deems that a table should be replaced if all of its original columns were replaced by other columns. Replacing a table is the same as dropping it and than creating it again (according to the new schema). All previous information in the table is deleted.
  • DoSpecificUpgrades – After all automatic upgrade operations are finished, the basic upgrade module will call this method in order to provide the concrete upgrade module class with a hook for doing more elaborate upgrade operations if necessary. For example, sometimes, we don’t want the upgrade module to delete a table because we want to use its data in order to populate some other table. In such a case, we’ll prevent the deletion of that table by returning false in the AllowTableDeletion hook, and we’ll put code in the DoSpecificUpgrades method that will copy the contents of the table and in the end delete it.

Aside from supporting these hooks, the basic upgrade module also provides some helper methods for concrete upgrade module classes that derive from it:

  • CreateIndex – Requests the creation of the index specified in the arguments of the method.
  • CreateTable – Requests the creation of the table specified in the arguments of the method.
  • DropIndex – Requests the deletion of the index specified in the arguments of the method.
  • DropTable – Requests the deletion of the table specified in the arguments of the method
  • ReplaceIndex – Requests replacing an existing index by the specified index.

When parsing SQL schema files or the embedded database schema (using the SQLITE_MASTER table), the upgrade framework uses the facilities of the four classes shown below besides the basic upgrade module class:

  • The DbSchema class – encapsulates the structure of the database. Currently, it does not support views or triggers, only tables and indexes.
  • The DbTable class – encapsulates the structure of a database table. It contains the list of columns, the list of primary keys, and the name of the table.
  • The DbColumn class – encapsulates the structure of a single database table column. It contains the name of the column, its type and size descriptors, and the various constraints of the column. As explained earlier, not all constraints are supported, so if you want to work with the upgrade framework, you’ll need to remove those constraints that are incompatible (CHECK constraints are not supported, REFERENCE constraints are not supported, ON CONFLICT clauses are not supported as well).

upgrade3.png

The upgrade manager class is the central class that is responsible for coordinating all upgrade operations. It supports the following methods:

  • SetUpgraders – Here, you provide the list of upgrade modules that you want to support. The method will construct an upgrade graph internally. This graph will be used to construct an upgrade path later.
  • UpgradeDB – Here, you provide the path to the DB file you want to upgrade and the target schema to which you want to upgrade. Note that the target schema must be supported by one of your upgrade modules (otherwise, the upgrade manager will not be able to construct an upgrade path).
  • Cancel – Called in case the user wants to cancel the upgrade process in the middle. It will call the Cancel method of all relevant upgrade modules and stop the upgrade process.

During the upgrade process, the upgrade manager class will issue progress events via the DbUpgradeProgress event. These can be handled by your GUI for displaying the progress of the upgrade to the user.

Using the code

In order to make it easier to use the code, I've attached a sample application that includes the upgrade engine library. The attached solution has the following structure:

  • DB - contains the latest SQL schema definition file for the database.
  • Libs - contains the SQLite .NET provider DLL.
  • DbUpgraders - a class library project that contains sample database upgrade classes.
  • SchemaExtractor - a utility application to extract the SQL schema definition of SQLite databases. The SQL schema files are then used as embedded resources in the upgrade utility (see the code in the DbUpgraders library).
  • TestApp - a skeletal upgrade utility application that can be used, with a few modifications on your part, in order to perform the necessary database upgrades.
  • UpgradeEngine - the database upgrade framework (a class library).
  • DbVersions - a folder that contains a historical version of the database for each supported version. This folder is very useful when debugging database upgrades.

Final Notes

I've tried to explain the internal structure and use of the SQLite database upgrade framework as much as I can, but I'm not perfect. If you think my explanations are lacking, please drop me an email with your questions, and I'll do my best to answer them as soon as I can.

History

  • 02 February 2009 - Version 1.0.

License

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

About the Author

liron.levi
Software Developer Arineta Cardio Imaging
Israel Israel
My name is Liron Levi and I'm developing software for fun & profit for 15 years already. I'm now working for Arineta Cardio Imaging as a software developer (the company develops a CT machine).
 
I can be contacted directly at liron.levi@outlook.com or via linkedin at http://www.linkedin.com/pub/liron-levy/1/578/ab5

Comments and Discussions

 
GeneralGreat job Pinmemberloyal ginger6-Oct-09 6:01 
GeneralRe: Great job Pinmemberliron.levi6-Oct-09 21:50 
QuestionGreat code(a bug ? ) PinmemberMember 383862228-Feb-09 9:56 
AnswerRe: Great code(a bug ? ) Pinmemberliron.levi28-Feb-09 20:12 
AnswerRe: Great code(a bug ? ) PinmemberMember 38386221-Mar-09 5:25 
GeneralRe: Great code(a bug ? ) Pinmemberliron.levi1-Mar-09 7:11 
GeneralSlightly puzzled... Pinmemberwtwhite2-Feb-09 16:22 
I like the idea of using a BFS to discover the fastest upgrade path between two DB schemas X and Y. But I'm slightly confused as to the level of detail apparently provided by the interface that your DB update class implements -- can't everything you want to do be accomplished using only the DoSpecificUpgrades() method? Why do you need the other methods? What is the advantage in explicitly modeling particular types of updates such as creation of new indices or tables?
 
I can see three advantages of not trying to explicitly model particular types of updates:
 
(1) Any DB update that can be accomplished using SQL statements, be they DDL (e.g. CREATE TABLE ...) or DML (e.g. UPDATE foo SET bar='baz') or any combination of the two, can be represented. So for example, triggers and stored procedures can be added or removed without difficulty.
(2) If you are disciplined enough that you make all schema changes to your private development version of the DB using SQL DDL commands (rather than e.g. via a GUI interface) and record them to a text file, you can simply use those text files as the update scripts.
(3) Simpler code in the update manager.
 
If there are factors I've overlooked that obviate the above advantages I'd love to hear about them.
GeneralRe: Slightly puzzled... [modified] Pinmemberliron.levi2-Feb-09 20:08 
GeneralRe: Slightly puzzled... Pinmemberwtwhite3-Feb-09 18:18 
GeneralRe: Slightly puzzled... Pinmemberliron.levi3-Feb-09 19:41 
GeneralAh! Pinmemberwtwhite4-Feb-09 11:59 
GeneralRe: Ah! Pinmemberliron.levi4-Feb-09 20:26 
GeneralRe: Slightly puzzled... [modified] Pinmemberliron.levi2-Feb-09 20:20 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 1 Feb 2009
Article Copyright 2009 by liron.levi
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid