root/branch/merger/sqlitedb.py @ 4406

import os
import shutil
try:
    from sqlite3 import dbapi2 as sqlite
except ImportError:
    from pysqlite2 import dbapi2 as sqlite

def _dict_factory(cursor, row):
    res = {}
    for indx, col in enumerate(cursor.description):
        res[col[0]] = row[indx]

    return res

class _DictCursor(sqlite.Cursor):
    def __init__(self, *args, **kwargs):
        sqlite.Cursor.__init__(self, *args, **kwargs)
        self.row_factory = _dict_factory


def merge(new_dbpath, old_dbpath, dry_run=False):
    """Merge a newer database structure with an older (but very similar)
    database."""
    new = sqlite.connect(new_dbpath)
    new_cursor = new.cursor(_DictCursor)

    if not os.path.isfile(old_dbpath):
        raise Exception("The older db %r is not a file." % old_dbpath)

    old = sqlite.connect(old_dbpath)
    old_cursor = old.cursor(_DictCursor)

    new_tables = new_cursor.execute(
            "SELECT name FROM sqlite_master "
            "WHERE type='table'").fetchall()
    for table in new_tables:
        name = table['name']
        # Verify that this table is in the older db, or not
        old_table = old_cursor.execute(
                "SELECT name FROM sqlite_master "
                "WHERE type='table' AND name=?", (name, )).fetchone()

        if old_table is None:
            # The older db does not have this table, create it there as well
            # the related triggers.
            print "Adding the table '%s' and related triggers." % name
            if dry_run:
                continue

            new_table_sql = new_cursor.execute(
                    "SELECT sql FROM sqlite_master "
                    "WHERE type='table' and NAME=?", (name, )).fetchone()
            old_cursor.execute(new_table_sql['sql'])
            new_table_triggers = new_cursor.execute(
                    "SELECT sql FROM sqlite_master "
                    "WHERE type='trigger' and NAME=?", (name, )).fetchall()
            for result in new_table_triggers:
                old_cursor.execute(result['sql'])

        else:
            # The old db already have this table but maybe columns or triggers
            # for it changed.
            res = new_cursor.execute("pragma table_info(%s)" % name)
            new_table_info = res.fetchall()
            new_by_col = {}
            for col in new_table_info:
                colname = col.pop('name')
                new_by_col[colname] = col

            # sqlite says pragmas may disappear at any time, so, supposing
            # the table name does exist in the database and table_info is
            # an empty result then "pragma table_info" disappeared!
            if not new_table_info:
                # Maybe do something special here (like a warning?).
                return

            res = old_cursor.execute("pragma table_info(%s)" % name)
            old_table_info = res.fetchall()
            old_by_col = {}
            for col in old_table_info:
                colname = col.pop('name')
                old_by_col[colname] = col

            for cname, cinfo in old_by_col.iteritems():
                if cname not in new_by_col:
                    # sqlite can't handle deletion of columns, this merge
                    # can't be done.
                    raise Exception("The table '%s' in the old database "
                            "contains a column named '%s' which no longer "
                            "exists in the new table, sqlite can't handle "
                            "this." % (name, cname))
                elif cinfo != new_by_col[cname]:
                    # sqlite also can't handle changes in column type and
                    # others
                    raise Exception("The table '%s' in the old database "
                            "differs in the column named '%s'. (%r != %r)" % (
                                name, cname, cinfo, new_by_col[cname]))

                del new_by_col[cname]

            # Attempt to create new columns
            # XXX not checking if they reference other columns yet.
            for cname, cinfo in new_by_col.iteritems():
                if cinfo['pk']:
                    # sqlite doesn't allow creating new columns as primary key
                    raise Exception("The table '%s' in the new database "
                            "has a new column named '%s' which is a primary "
                            "key, but sqlite can't handle this." % (
                                name, cname))

                query = "ALTER TABLE %s ADD COLUMN %s %s" % (
                        name, cname, cinfo['type'])
                if cinfo['notnull']:
                    query += " NOT NULL"
                if cinfo['dflt_value']:
                    query += " DEFAULT %s" % cinfo['dflt_value']
                old_cursor.execute(query)

            # XXX check for triggers here


if __name__ == "__main__":
    import sys
    merge(*sys.argv[1:3])