openlp-core team mailing list archive
-
openlp-core team
-
Mailing list archive
-
Message #11521
[Merge] lp:~raoul-snyman/openlp/db-upgrades into lp:openlp
Raoul Snyman has proposed merging lp:~raoul-snyman/openlp/db-upgrades into lp:openlp.
Requested reviews:
Andreas Preikschat (googol)
Tim Bentley (trb143)
For more details, see:
https://code.launchpad.net/~raoul-snyman/openlp/db-upgrades/+merge/72953
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!
--
https://code.launchpad.net/~raoul-snyman/openlp/db-upgrades/+merge/72953
Your team OpenLP Core is subscribed to branch lp:openlp.
=== 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:16:24 +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:16:24 +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:16:24 +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:16:24 +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:16:24 +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:16:24 +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 = [
Follow ups
