Merge lp:~raoul-snyman/openlp/db-upgrades into lp:openlp

[Raoul Snyman]

Implemented a seamless database upgrade system.

Database upgrade system.

1. Create a module to use for upgrading.
2. from migrate import changeset
3. Define some default tables, which use reflection, in an "upgrade_setup()" function.
4. Write your upgrade functions named "upgrade_X()" where X is the version number.
5. Write your upgrade statements using the extended methods from Migrate.
6. Make sure the DB manager object knows about the upgrade module.

Done!

[Raoul Snyman (raoul-snyman)]

To test this:

1. Install SQLAlchemy Migrate (eg: apt-get install python-migrate)
2. Backup your songs database
3. Run OpenLP
4. Look for a "weight" column on the "media_files" table

[Tim Bentley (trb143)]

review: Approve

[Tim Bentley (trb143)]

Works for me.

review: Approve

[Andreas Preikschat (googol-deactivatedaccount)]

You need to add the dependency to the scripts/check_dependencies.py. And do not forget the mail to the mailing list about the new dependency.

Please add docstrings to upgrade_1. It would be good if we document the "database history" and when we changed things.

review: Needs Fixing

[lp:~raoul-snyman/openlp/db-upgrades]

1724. By Raoul Snyman on 2011-08-25

Fixed a bug where the version number saved to the database was incorrect. Added the ability to throw an error message and not load the database if the db is too new or incorrect.

1725. By Raoul Snyman on 2011-08-25

A few more tweaks as requested by Andreas.

Unmerged revisions

=== modified file 'openlp/core/lib/db.py'
--- openlp/core/lib/db.py	2011-08-16 19:53:52 +0000
+++ openlp/core/lib/db.py	2011-08-25 20:15:14 +0000
@@ -31,11 +31,13 @@
 import os
 
 from PyQt4 import QtCore
-from sqlalchemy import create_engine, MetaData
-from sqlalchemy.exc import InvalidRequestError
-from sqlalchemy.orm import scoped_session, sessionmaker
+from sqlalchemy import Table, MetaData, Column, types, create_engine
+from sqlalchemy.exc import SQLAlchemyError, InvalidRequestError, DBAPIError
+from sqlalchemy.orm import scoped_session, sessionmaker, mapper
 from sqlalchemy.pool import NullPool
 
+from openlp.core.lib import translate
+from openlp.core.lib.ui import critical_error_message_box
 from openlp.core.utils import AppLocation, delete_file
 
 log = logging.getLogger(__name__)
@@ -59,6 +61,48 @@
         autocommit=auto_commit, bind=engine))
     return session, metadata
 
+
+def upgrade_db(url, upgrade):
+    """
+    Upgrade a database.
+
+    ``url``
+        The url of the database to upgrade.
+
+    ``upgrade``
+        The python module that contains the upgrade instructions.
+    """
+    session, metadata = init_db(url)
+    tables = upgrade.upgrade_setup(metadata)
+    metadata_table = Table(u'metadata', metadata,
+        Column(u'key', types.Unicode(64), primary_key=True),
+        Column(u'value', types.UnicodeText(), default=None)
+    )
+    metadata_table.create(checkfirst=True)
+    mapper(Metadata, metadata_table)
+    version_meta = session.query(Metadata).get(u'version')
+    if version_meta is None:
+        version_meta = Metadata.populate(key=u'version', value=u'0')
+        version = 0
+    else:
+        version = int(version_meta.value)
+    if version > upgrade.__version__:
+        return version, upgrade.__version__
+    version += 1
+    while hasattr(upgrade, u'upgrade_%d' % version):
+        log.debug(u'Running upgrade_%d', version)
+        try:
+            getattr(upgrade, u'upgrade_%d' % version)(session, metadata, tables)
+            version_meta.value = unicode(version)
+        except SQLAlchemyError, DBAPIError:
+            log.exception(u'Could not run database upgrade script "upgrade_%s"'\
+                ', upgrade process has been halted.', version)
+            break
+        version += 1
+    session.add(version_meta)
+    session.commit()
+    return int(version_meta.value), upgrade.__version__
+
 def delete_database(plugin_name, db_file_name=None):
     """
     Remove a database file from the system.
@@ -79,6 +123,7 @@
             AppLocation.get_section_data_path(plugin_name), plugin_name)
     return delete_file(db_file_path)
 
+
 class BaseModel(object):
     """
     BaseModel provides a base object with a set of generic functions
@@ -94,11 +139,19 @@
         return instance
 
 
+class Metadata(BaseModel):
+    """
+    Provides a class for the metadata table.
+    """
+    pass
+
+
 class Manager(object):
     """
     Provide generic object persistence management
     """
-    def __init__(self, plugin_name, init_schema, db_file_name=None):
+    def __init__(self, plugin_name, init_schema, db_file_name=None,
+                 upgrade_mod=None):
         """
         Runs the initialisation process that includes creating the connection
         to the database and the tables if they don't exist.
@@ -109,6 +162,9 @@
         ``init_schema``
             The init_schema function for this database
 
+        ``upgrade_schema``
+            The upgrade_schema function for this database
+
         ``db_file_name``
             The file name to use for this database. Defaults to None resulting
             in the plugin_name being used.
@@ -134,7 +190,27 @@
                 unicode(settings.value(u'db hostname').toString()),
                 unicode(settings.value(u'db database').toString()))
         settings.endGroup()
-        self.session = init_schema(self.db_url)
+        if upgrade_mod:
+            db_ver, up_ver = upgrade_db(self.db_url, upgrade_mod)
+            if db_ver > up_ver:
+                critical_error_message_box(
+                    translate('OpenLP.Manager', 'Database Error'),
+                    unicode(translate('OpenLP.Manager', 'The database being '
+                        'loaded was created in a more recent version of '
+                        'OpenLP. The database is version %d, while OpenLP '
+                        'expects version %d. The database will not be loaded.'
+                        '\n\nDatabase: %s')) % \
+                        (db_ver, up_ver, self.db_url)
+                )
+                return
+        try:
+            self.session = init_schema(self.db_url)
+        except:
+            critical_error_message_box(
+                translate('OpenLP.Manager', 'Database Error'),
+                unicode(translate('OpenLP.Manager', 'OpenLP cannot load your '
+                    'database.\n\nDatabase: %s')) % self.db_url
+            )
 
     def save_object(self, object_instance, commit=True):
         """

=== modified file 'openlp/plugins/songs/lib/db.py'
--- openlp/plugins/songs/lib/db.py	2011-07-07 18:03:12 +0000
+++ openlp/plugins/songs/lib/db.py	2011-08-25 20:15:14 +0000
@@ -70,7 +70,6 @@
     """
     pass
 
-
 def init_schema(url):
     """
     Setup the songs database connection and initialise the database schema.
@@ -111,10 +110,6 @@
         * file_name
         * type
 
-    **media_files_songs Table**
-        * media_file_id
-        * song_id
-
     **song_books Table**
         The *song_books* table holds a list of books that a congregation gets
         their songs from, or old hymnals now no longer used. This table has the
@@ -162,7 +157,7 @@
 
     # Definition of the "authors" table
     authors_table = Table(u'authors', metadata,
-        Column(u'id', types.Integer, primary_key=True),
+        Column(u'id', types.Integer(), primary_key=True),
         Column(u'first_name', types.Unicode(128)),
         Column(u'last_name', types.Unicode(128)),
         Column(u'display_name', types.Unicode(255), index=True, nullable=False)
@@ -170,22 +165,25 @@
 
     # Definition of the "media_files" table
     media_files_table = Table(u'media_files', metadata,
-        Column(u'id', types.Integer, primary_key=True),
+        Column(u'id', types.Integer(), primary_key=True),
+        Column(u'song_id', types.Integer(), ForeignKey(u'songs.id'),
+            default=None),
         Column(u'file_name', types.Unicode(255), nullable=False),
-        Column(u'type', types.Unicode(64), nullable=False, default=u'audio')
+        Column(u'type', types.Unicode(64), nullable=False, default=u'audio'),
+        Column(u'weight', types.Integer(), default=0)
     )
 
     # Definition of the "song_books" table
     song_books_table = Table(u'song_books', metadata,
-        Column(u'id', types.Integer, primary_key=True),
+        Column(u'id', types.Integer(), primary_key=True),
         Column(u'name', types.Unicode(128), nullable=False),
         Column(u'publisher', types.Unicode(128))
     )
 
     # Definition of the "songs" table
     songs_table = Table(u'songs', metadata,
-        Column(u'id', types.Integer, primary_key=True),
-        Column(u'song_book_id', types.Integer,
+        Column(u'id', types.Integer(), primary_key=True),
+        Column(u'song_book_id', types.Integer(),
             ForeignKey(u'song_books.id'), default=None),
         Column(u'title', types.Unicode(255), nullable=False),
         Column(u'alternate_title', types.Unicode(255)),
@@ -202,31 +200,23 @@
 
     # Definition of the "topics" table
     topics_table = Table(u'topics', metadata,
-        Column(u'id', types.Integer, primary_key=True),
+        Column(u'id', types.Integer(), primary_key=True),
         Column(u'name', types.Unicode(128), index=True, nullable=False)
     )
 
     # Definition of the "authors_songs" table
     authors_songs_table = Table(u'authors_songs', metadata,
-        Column(u'author_id', types.Integer,
+        Column(u'author_id', types.Integer(),
             ForeignKey(u'authors.id'), primary_key=True),
-        Column(u'song_id', types.Integer,
-            ForeignKey(u'songs.id'), primary_key=True)
-    )
-
-    # Definition of the "media_files_songs" table
-    media_files_songs_table = Table(u'media_files_songs', metadata,
-        Column(u'media_file_id', types.Integer,
-            ForeignKey(u'media_files.id'), primary_key=True),
-        Column(u'song_id', types.Integer,
+        Column(u'song_id', types.Integer(),
             ForeignKey(u'songs.id'), primary_key=True)
     )
 
     # Definition of the "songs_topics" table
     songs_topics_table = Table(u'songs_topics', metadata,
-        Column(u'song_id', types.Integer,
+        Column(u'song_id', types.Integer(),
             ForeignKey(u'songs.id'), primary_key=True),
-        Column(u'topic_id', types.Integer,
+        Column(u'topic_id', types.Integer(),
             ForeignKey(u'topics.id'), primary_key=True)
     )
 
@@ -238,8 +228,7 @@
             'authors': relation(Author, backref='songs',
                 secondary=authors_songs_table, lazy=False),
             'book': relation(Book, backref='songs'),
-            'media_files': relation(MediaFile, backref='songs',
-                secondary=media_files_songs_table),
+            'media_files': relation(MediaFile, backref='songs'),
             'topics': relation(Topic, backref='songs',
                 secondary=songs_topics_table)
         })

=== added file 'openlp/plugins/songs/lib/upgrade.py'
--- openlp/plugins/songs/lib/upgrade.py	1970-01-01 00:00:00 +0000
+++ openlp/plugins/songs/lib/upgrade.py	2011-08-25 20:15:14 +0000
@@ -0,0 +1,77 @@
+# -*- coding: utf-8 -*-
+# vim: autoindent shiftwidth=4 expandtab textwidth=80 tabstop=4 softtabstop=4
+
+###############################################################################
+# OpenLP - Open Source Lyrics Projection                                      #
+# --------------------------------------------------------------------------- #
+# Copyright (c) 2008-2011 Raoul Snyman                                        #
+# Portions copyright (c) 2008-2011 Tim Bentley, Gerald Britton, Jonathan      #
+# Corwin, Michael Gorven, Scott Guerrieri, Matthias Hub, Meinert Jordan,      #
+# Armin Köhler, Joshua Miller, Stevan Pettit, Andreas Preikschat, Mattias     #
+# Põldaru, Christian Richter, Philip Ridout, Simon Scudder, Jeffrey Smith,    #
+# Maikel Stuivenberg, Martin Thompson, Jon Tibble, Frode Woldsund             #
+# --------------------------------------------------------------------------- #
+# This program is free software; you can redistribute it and/or modify it     #
+# under the terms of the GNU General Public License as published by the Free  #
+# Software Foundation; version 2 of the License.                              #
+#                                                                             #
+# This program is distributed in the hope that it will be useful, but WITHOUT #
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or       #
+# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for    #
+# more details.                                                               #
+#                                                                             #
+# You should have received a copy of the GNU General Public License along     #
+# with this program; if not, write to the Free Software Foundation, Inc., 59  #
+# Temple Place, Suite 330, Boston, MA 02111-1307 USA                          #
+###############################################################################
+"""
+The :mod:`upgrade` module provides a way for the database and schema that is the backend for
+the Songs plugin
+"""
+
+from sqlalchemy import Column, ForeignKey, Table, types
+from migrate import changeset
+from migrate.changeset.constraint import ForeignKeyConstraint
+
+__version__ = 1
+
+def upgrade_setup(metadata):
+    """
+    Set up the latest revision all tables, with reflection, needed for the
+    upgrade process. If you want to drop a table, you need to remove it from
+    here, and add it to your upgrade function.
+    """
+    tables = {
+        u'authors': Table(u'authors', metadata, autoload=True),
+        u'media_files': Table(u'media_files', metadata, autoload=True),
+        u'song_books': Table(u'song_books', metadata, autoload=True),
+        u'songs': Table(u'songs', metadata, autoload=True),
+        u'topics': Table(u'topics', metadata, autoload=True),
+        u'authors_songs': Table(u'authors_songs', metadata, autoload=True),
+        u'songs_topics': Table(u'songs_topics', metadata, autoload=True)
+    }
+    return tables
+
+
+def upgrade_1(session, metadata, tables):
+    """
+    Version 1 upgrade.
+
+    This upgrade removes the many-to-many relationship between songs and 
+    media_files and replaces it with a one-to-many, which is far more
+    representative of the real relationship between the two entities.
+
+    In order to facilitate this one-to-many relationship, a song_id column is
+    added to the media_files table, and a weight column so that the media
+    files can be ordered.
+    """
+    Table(u'media_files_songs', metadata, autoload=True).drop(checkfirst=True)
+    Column(u'song_id', types.Integer(), default=None)\
+        .create(table=tables[u'media_files'], populate_default=True)
+    Column(u'weight', types.Integer(), default=0)\
+        .create(table=tables[u'media_files'], populate_default=True)
+    if metadata.bind.url.get_dialect().name != 'sqlite':
+        # SQLite doesn't support ALTER TABLE ADD CONSTRAINT
+        ForeignKeyConstraint([u'song_id'], [u'songs.id'],
+            table=tables[u'media_files']).create()
+

=== modified file 'openlp/plugins/songs/songsplugin.py'
--- openlp/plugins/songs/songsplugin.py	2011-07-23 21:29:24 +0000
+++ openlp/plugins/songs/songsplugin.py	2011-08-25 20:15:14 +0000
@@ -36,7 +36,8 @@
 from openlp.core.lib.db import Manager
 from openlp.core.lib.ui import UiStrings, base_action, icon_action
 from openlp.core.utils.actions import ActionList
-from openlp.plugins.songs.lib import clean_song, SongMediaItem, SongsTab
+from openlp.plugins.songs.lib import clean_song, upgrade, SongMediaItem, \
+    SongsTab
 from openlp.plugins.songs.lib.db import init_schema, Song
 from openlp.plugins.songs.lib.importer import SongFormat
 from openlp.plugins.songs.lib.olpimport import OpenLPSongImport
@@ -58,8 +59,8 @@
         Create and set up the Songs plugin.
         """
         Plugin.__init__(self, u'songs', plugin_helpers, SongMediaItem, SongsTab)
+        self.manager = Manager(u'songs', init_schema, upgrade_mod=upgrade)
         self.weight = -10
-        self.manager = Manager(u'songs', init_schema)
         self.icon_path = u':/plugins/plugin_songs.png'
         self.icon = build_icon(self.icon_path)
 

=== modified file 'resources/debian/debian/control'
--- resources/debian/debian/control	2011-03-09 06:55:41 +0000
+++ resources/debian/debian/control	2011-08-25 20:15:14 +0000
@@ -11,7 +11,7 @@
 Architecture: all
 Depends: ${shlibs:Depends}, ${misc:Depends}, ${python:Depends}, python-qt4,
  python-qt4-phonon, python-sqlalchemy, python-chardet, python-beautifulsoup,
- python-lxml, python-sqlite, python-enchant
+ python-lxml, python-sqlite, python-enchant, python-migrate
 Conflicts: python-openlp
 Description: Church lyrics projection application
  OpenLP is free church presentation software, or lyrics projection software,

=== modified file 'scripts/check_dependencies.py'
--- scripts/check_dependencies.py	2011-07-15 17:38:09 +0000
+++ scripts/check_dependencies.py	2011-08-25 20:15:14 +0000
@@ -46,14 +46,14 @@
     'sqlalchemy': '0.5',
     # pyenchant 1.6 required on Windows
     'enchant': '1.6' if is_win else '1.3'
-    }
+}
 
 # pywin32
 WIN32_MODULES = [
     'win32com',
     'win32ui',
     'pywintypes',
-    ]
+]
 
 MODULES = [
     'PyQt4',
@@ -72,7 +72,8 @@
     'enchant',
     'BeautifulSoup',
     'mako',
-    ]
+    'migrate',
+]
 
 
 OPTIONAL_MODULES = [