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

Proposed by Raoul Snyman on 2011-08-25
Status: Merged
Approved by: Andreas Preikschat on 2011-08-26
Approved revision: 1725
Merged at revision: 1721
Proposed branch: lp:~raoul-snyman/openlp/db-upgrades
Merge into: lp:openlp
Diff against target: 397 lines (+181/-37)
6 files modified
openlp/core/lib/db.py (+81/-5)
openlp/plugins/songs/lib/db.py (+15/-26)
openlp/plugins/songs/lib/upgrade.py (+77/-0)
openlp/plugins/songs/songsplugin.py (+3/-2)
resources/debian/debian/control (+1/-1)
scripts/check_dependencies.py (+4/-3)
To merge this branch: bzr merge lp:~raoul-snyman/openlp/db-upgrades
Reviewer Review Type Date Requested Status
Andreas Preikschat (community) 2011-08-25 Approve on 2011-08-26
Tim Bentley 2011-08-25 Approve on 2011-08-25
Review via email: mp+72953@code.launchpad.net

This proposal supersedes a proposal from 2011-08-25.

Commit message

- Implemented a seamless database upgrade system.
- Added the first upgrade as a demonstration.
- Added database version check so that newer version databases are not loaded.

Description of the change

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!

To post a comment you must log in.
Raoul Snyman (raoul-snyman) wrote : Posted in a previous version of this proposal

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) : Posted in a previous version of this proposal
review: Approve
Tim Bentley (trb143) wrote : Posted in a previous version of this proposal

Works for me.

review: Approve
Andreas Preikschat (googol) wrote : Posted in a previous version of this proposal

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
Tim Bentley (trb143) :
review: Approve
review: Approve

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== 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 @@
31import os31import os
3232
33from PyQt4 import QtCore33from PyQt4 import QtCore
34from sqlalchemy import create_engine, MetaData34from sqlalchemy import Table, MetaData, Column, types, create_engine
35from sqlalchemy.exc import InvalidRequestError35from sqlalchemy.exc import SQLAlchemyError, InvalidRequestError, DBAPIError
36from sqlalchemy.orm import scoped_session, sessionmaker36from sqlalchemy.orm import scoped_session, sessionmaker, mapper
37from sqlalchemy.pool import NullPool37from sqlalchemy.pool import NullPool
3838
39from openlp.core.lib import translate
40from openlp.core.lib.ui import critical_error_message_box
39from openlp.core.utils import AppLocation, delete_file41from openlp.core.utils import AppLocation, delete_file
4042
41log = logging.getLogger(__name__)43log = logging.getLogger(__name__)
@@ -59,6 +61,48 @@
59 autocommit=auto_commit, bind=engine))61 autocommit=auto_commit, bind=engine))
60 return session, metadata62 return session, metadata
6163
64
65def upgrade_db(url, upgrade):
66 """
67 Upgrade a database.
68
69 ``url``
70 The url of the database to upgrade.
71
72 ``upgrade``
73 The python module that contains the upgrade instructions.
74 """
75 session, metadata = init_db(url)
76 tables = upgrade.upgrade_setup(metadata)
77 metadata_table = Table(u'metadata', metadata,
78 Column(u'key', types.Unicode(64), primary_key=True),
79 Column(u'value', types.UnicodeText(), default=None)
80 )
81 metadata_table.create(checkfirst=True)
82 mapper(Metadata, metadata_table)
83 version_meta = session.query(Metadata).get(u'version')
84 if version_meta is None:
85 version_meta = Metadata.populate(key=u'version', value=u'0')
86 version = 0
87 else:
88 version = int(version_meta.value)
89 if version > upgrade.__version__:
90 return version, upgrade.__version__
91 version += 1
92 while hasattr(upgrade, u'upgrade_%d' % version):
93 log.debug(u'Running upgrade_%d', version)
94 try:
95 getattr(upgrade, u'upgrade_%d' % version)(session, metadata, tables)
96 version_meta.value = unicode(version)
97 except SQLAlchemyError, DBAPIError:
98 log.exception(u'Could not run database upgrade script "upgrade_%s"'\
99 ', upgrade process has been halted.', version)
100 break
101 version += 1
102 session.add(version_meta)
103 session.commit()
104 return int(version_meta.value), upgrade.__version__
105
62def delete_database(plugin_name, db_file_name=None):106def delete_database(plugin_name, db_file_name=None):
63 """107 """
64 Remove a database file from the system.108 Remove a database file from the system.
@@ -79,6 +123,7 @@
79 AppLocation.get_section_data_path(plugin_name), plugin_name)123 AppLocation.get_section_data_path(plugin_name), plugin_name)
80 return delete_file(db_file_path)124 return delete_file(db_file_path)
81125
126
82class BaseModel(object):127class BaseModel(object):
83 """128 """
84 BaseModel provides a base object with a set of generic functions129 BaseModel provides a base object with a set of generic functions
@@ -94,11 +139,19 @@
94 return instance139 return instance
95140
96141
142class Metadata(BaseModel):
143 """
144 Provides a class for the metadata table.
145 """
146 pass
147
148
97class Manager(object):149class Manager(object):
98 """150 """
99 Provide generic object persistence management151 Provide generic object persistence management
100 """152 """
101 def __init__(self, plugin_name, init_schema, db_file_name=None):153 def __init__(self, plugin_name, init_schema, db_file_name=None,
154 upgrade_mod=None):
102 """155 """
103 Runs the initialisation process that includes creating the connection156 Runs the initialisation process that includes creating the connection
104 to the database and the tables if they don't exist.157 to the database and the tables if they don't exist.
@@ -109,6 +162,9 @@
109 ``init_schema``162 ``init_schema``
110 The init_schema function for this database163 The init_schema function for this database
111164
165 ``upgrade_schema``
166 The upgrade_schema function for this database
167
112 ``db_file_name``168 ``db_file_name``
113 The file name to use for this database. Defaults to None resulting169 The file name to use for this database. Defaults to None resulting
114 in the plugin_name being used.170 in the plugin_name being used.
@@ -134,7 +190,27 @@
134 unicode(settings.value(u'db hostname').toString()),190 unicode(settings.value(u'db hostname').toString()),
135 unicode(settings.value(u'db database').toString()))191 unicode(settings.value(u'db database').toString()))
136 settings.endGroup()192 settings.endGroup()
137 self.session = init_schema(self.db_url)193 if upgrade_mod:
194 db_ver, up_ver = upgrade_db(self.db_url, upgrade_mod)
195 if db_ver > up_ver:
196 critical_error_message_box(
197 translate('OpenLP.Manager', 'Database Error'),
198 unicode(translate('OpenLP.Manager', 'The database being '
199 'loaded was created in a more recent version of '
200 'OpenLP. The database is version %d, while OpenLP '
201 'expects version %d. The database will not be loaded.'
202 '\n\nDatabase: %s')) % \
203 (db_ver, up_ver, self.db_url)
204 )
205 return
206 try:
207 self.session = init_schema(self.db_url)
208 except:
209 critical_error_message_box(
210 translate('OpenLP.Manager', 'Database Error'),
211 unicode(translate('OpenLP.Manager', 'OpenLP cannot load your '
212 'database.\n\nDatabase: %s')) % self.db_url
213 )
138214
139 def save_object(self, object_instance, commit=True):215 def save_object(self, object_instance, commit=True):
140 """216 """
141217
=== 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 @@
70 """70 """
71 pass71 pass
7272
73
74def init_schema(url):73def init_schema(url):
75 """74 """
76 Setup the songs database connection and initialise the database schema.75 Setup the songs database connection and initialise the database schema.
@@ -111,10 +110,6 @@
111 * file_name110 * file_name
112 * type111 * type
113112
114 **media_files_songs Table**
115 * media_file_id
116 * song_id
117
118 **song_books Table**113 **song_books Table**
119 The *song_books* table holds a list of books that a congregation gets114 The *song_books* table holds a list of books that a congregation gets
120 their songs from, or old hymnals now no longer used. This table has the115 their songs from, or old hymnals now no longer used. This table has the
@@ -162,7 +157,7 @@
162157
163 # Definition of the "authors" table158 # Definition of the "authors" table
164 authors_table = Table(u'authors', metadata,159 authors_table = Table(u'authors', metadata,
165 Column(u'id', types.Integer, primary_key=True),160 Column(u'id', types.Integer(), primary_key=True),
166 Column(u'first_name', types.Unicode(128)),161 Column(u'first_name', types.Unicode(128)),
167 Column(u'last_name', types.Unicode(128)),162 Column(u'last_name', types.Unicode(128)),
168 Column(u'display_name', types.Unicode(255), index=True, nullable=False)163 Column(u'display_name', types.Unicode(255), index=True, nullable=False)
@@ -170,22 +165,25 @@
170165
171 # Definition of the "media_files" table166 # Definition of the "media_files" table
172 media_files_table = Table(u'media_files', metadata,167 media_files_table = Table(u'media_files', metadata,
173 Column(u'id', types.Integer, primary_key=True),168 Column(u'id', types.Integer(), primary_key=True),
169 Column(u'song_id', types.Integer(), ForeignKey(u'songs.id'),
170 default=None),
174 Column(u'file_name', types.Unicode(255), nullable=False),171 Column(u'file_name', types.Unicode(255), nullable=False),
175 Column(u'type', types.Unicode(64), nullable=False, default=u'audio')172 Column(u'type', types.Unicode(64), nullable=False, default=u'audio'),
173 Column(u'weight', types.Integer(), default=0)
176 )174 )
177175
178 # Definition of the "song_books" table176 # Definition of the "song_books" table
179 song_books_table = Table(u'song_books', metadata,177 song_books_table = Table(u'song_books', metadata,
180 Column(u'id', types.Integer, primary_key=True),178 Column(u'id', types.Integer(), primary_key=True),
181 Column(u'name', types.Unicode(128), nullable=False),179 Column(u'name', types.Unicode(128), nullable=False),
182 Column(u'publisher', types.Unicode(128))180 Column(u'publisher', types.Unicode(128))
183 )181 )
184182
185 # Definition of the "songs" table183 # Definition of the "songs" table
186 songs_table = Table(u'songs', metadata,184 songs_table = Table(u'songs', metadata,
187 Column(u'id', types.Integer, primary_key=True),185 Column(u'id', types.Integer(), primary_key=True),
188 Column(u'song_book_id', types.Integer,186 Column(u'song_book_id', types.Integer(),
189 ForeignKey(u'song_books.id'), default=None),187 ForeignKey(u'song_books.id'), default=None),
190 Column(u'title', types.Unicode(255), nullable=False),188 Column(u'title', types.Unicode(255), nullable=False),
191 Column(u'alternate_title', types.Unicode(255)),189 Column(u'alternate_title', types.Unicode(255)),
@@ -202,31 +200,23 @@
202200
203 # Definition of the "topics" table201 # Definition of the "topics" table
204 topics_table = Table(u'topics', metadata,202 topics_table = Table(u'topics', metadata,
205 Column(u'id', types.Integer, primary_key=True),203 Column(u'id', types.Integer(), primary_key=True),
206 Column(u'name', types.Unicode(128), index=True, nullable=False)204 Column(u'name', types.Unicode(128), index=True, nullable=False)
207 )205 )
208206
209 # Definition of the "authors_songs" table207 # Definition of the "authors_songs" table
210 authors_songs_table = Table(u'authors_songs', metadata,208 authors_songs_table = Table(u'authors_songs', metadata,
211 Column(u'author_id', types.Integer,209 Column(u'author_id', types.Integer(),
212 ForeignKey(u'authors.id'), primary_key=True),210 ForeignKey(u'authors.id'), primary_key=True),
213 Column(u'song_id', types.Integer,211 Column(u'song_id', types.Integer(),
214 ForeignKey(u'songs.id'), primary_key=True)
215 )
216
217 # Definition of the "media_files_songs" table
218 media_files_songs_table = Table(u'media_files_songs', metadata,
219 Column(u'media_file_id', types.Integer,
220 ForeignKey(u'media_files.id'), primary_key=True),
221 Column(u'song_id', types.Integer,
222 ForeignKey(u'songs.id'), primary_key=True)212 ForeignKey(u'songs.id'), primary_key=True)
223 )213 )
224214
225 # Definition of the "songs_topics" table215 # Definition of the "songs_topics" table
226 songs_topics_table = Table(u'songs_topics', metadata,216 songs_topics_table = Table(u'songs_topics', metadata,
227 Column(u'song_id', types.Integer,217 Column(u'song_id', types.Integer(),
228 ForeignKey(u'songs.id'), primary_key=True),218 ForeignKey(u'songs.id'), primary_key=True),
229 Column(u'topic_id', types.Integer,219 Column(u'topic_id', types.Integer(),
230 ForeignKey(u'topics.id'), primary_key=True)220 ForeignKey(u'topics.id'), primary_key=True)
231 )221 )
232222
@@ -238,8 +228,7 @@
238 'authors': relation(Author, backref='songs',228 'authors': relation(Author, backref='songs',
239 secondary=authors_songs_table, lazy=False),229 secondary=authors_songs_table, lazy=False),
240 'book': relation(Book, backref='songs'),230 'book': relation(Book, backref='songs'),
241 'media_files': relation(MediaFile, backref='songs',231 'media_files': relation(MediaFile, backref='songs'),
242 secondary=media_files_songs_table),
243 'topics': relation(Topic, backref='songs',232 'topics': relation(Topic, backref='songs',
244 secondary=songs_topics_table)233 secondary=songs_topics_table)
245 })234 })
246235
=== 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 @@
1# -*- coding: utf-8 -*-
2# vim: autoindent shiftwidth=4 expandtab textwidth=80 tabstop=4 softtabstop=4
3
4###############################################################################
5# OpenLP - Open Source Lyrics Projection #
6# --------------------------------------------------------------------------- #
7# Copyright (c) 2008-2011 Raoul Snyman #
8# Portions copyright (c) 2008-2011 Tim Bentley, Gerald Britton, Jonathan #
9# Corwin, Michael Gorven, Scott Guerrieri, Matthias Hub, Meinert Jordan, #
10# Armin Köhler, Joshua Miller, Stevan Pettit, Andreas Preikschat, Mattias #
11# Põldaru, Christian Richter, Philip Ridout, Simon Scudder, Jeffrey Smith, #
12# Maikel Stuivenberg, Martin Thompson, Jon Tibble, Frode Woldsund #
13# --------------------------------------------------------------------------- #
14# This program is free software; you can redistribute it and/or modify it #
15# under the terms of the GNU General Public License as published by the Free #
16# Software Foundation; version 2 of the License. #
17# #
18# This program is distributed in the hope that it will be useful, but WITHOUT #
19# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or #
20# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for #
21# more details. #
22# #
23# You should have received a copy of the GNU General Public License along #
24# with this program; if not, write to the Free Software Foundation, Inc., 59 #
25# Temple Place, Suite 330, Boston, MA 02111-1307 USA #
26###############################################################################
27"""
28The :mod:`upgrade` module provides a way for the database and schema that is the backend for
29the Songs plugin
30"""
31
32from sqlalchemy import Column, ForeignKey, Table, types
33from migrate import changeset
34from migrate.changeset.constraint import ForeignKeyConstraint
35
36__version__ = 1
37
38def upgrade_setup(metadata):
39 """
40 Set up the latest revision all tables, with reflection, needed for the
41 upgrade process. If you want to drop a table, you need to remove it from
42 here, and add it to your upgrade function.
43 """
44 tables = {
45 u'authors': Table(u'authors', metadata, autoload=True),
46 u'media_files': Table(u'media_files', metadata, autoload=True),
47 u'song_books': Table(u'song_books', metadata, autoload=True),
48 u'songs': Table(u'songs', metadata, autoload=True),
49 u'topics': Table(u'topics', metadata, autoload=True),
50 u'authors_songs': Table(u'authors_songs', metadata, autoload=True),
51 u'songs_topics': Table(u'songs_topics', metadata, autoload=True)
52 }
53 return tables
54
55
56def upgrade_1(session, metadata, tables):
57 """
58 Version 1 upgrade.
59
60 This upgrade removes the many-to-many relationship between songs and
61 media_files and replaces it with a one-to-many, which is far more
62 representative of the real relationship between the two entities.
63
64 In order to facilitate this one-to-many relationship, a song_id column is
65 added to the media_files table, and a weight column so that the media
66 files can be ordered.
67 """
68 Table(u'media_files_songs', metadata, autoload=True).drop(checkfirst=True)
69 Column(u'song_id', types.Integer(), default=None)\
70 .create(table=tables[u'media_files'], populate_default=True)
71 Column(u'weight', types.Integer(), default=0)\
72 .create(table=tables[u'media_files'], populate_default=True)
73 if metadata.bind.url.get_dialect().name != 'sqlite':
74 # SQLite doesn't support ALTER TABLE ADD CONSTRAINT
75 ForeignKeyConstraint([u'song_id'], [u'songs.id'],
76 table=tables[u'media_files']).create()
77
078
=== 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 @@
36from openlp.core.lib.db import Manager36from openlp.core.lib.db import Manager
37from openlp.core.lib.ui import UiStrings, base_action, icon_action37from openlp.core.lib.ui import UiStrings, base_action, icon_action
38from openlp.core.utils.actions import ActionList38from openlp.core.utils.actions import ActionList
39from openlp.plugins.songs.lib import clean_song, SongMediaItem, SongsTab39from openlp.plugins.songs.lib import clean_song, upgrade, SongMediaItem, \
40 SongsTab
40from openlp.plugins.songs.lib.db import init_schema, Song41from openlp.plugins.songs.lib.db import init_schema, Song
41from openlp.plugins.songs.lib.importer import SongFormat42from openlp.plugins.songs.lib.importer import SongFormat
42from openlp.plugins.songs.lib.olpimport import OpenLPSongImport43from openlp.plugins.songs.lib.olpimport import OpenLPSongImport
@@ -58,8 +59,8 @@
58 Create and set up the Songs plugin.59 Create and set up the Songs plugin.
59 """60 """
60 Plugin.__init__(self, u'songs', plugin_helpers, SongMediaItem, SongsTab)61 Plugin.__init__(self, u'songs', plugin_helpers, SongMediaItem, SongsTab)
62 self.manager = Manager(u'songs', init_schema, upgrade_mod=upgrade)
61 self.weight = -1063 self.weight = -10
62 self.manager = Manager(u'songs', init_schema)
63 self.icon_path = u':/plugins/plugin_songs.png'64 self.icon_path = u':/plugins/plugin_songs.png'
64 self.icon = build_icon(self.icon_path)65 self.icon = build_icon(self.icon_path)
6566
6667
=== 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 @@
11Architecture: all11Architecture: all
12Depends: ${shlibs:Depends}, ${misc:Depends}, ${python:Depends}, python-qt4,12Depends: ${shlibs:Depends}, ${misc:Depends}, ${python:Depends}, python-qt4,
13 python-qt4-phonon, python-sqlalchemy, python-chardet, python-beautifulsoup,13 python-qt4-phonon, python-sqlalchemy, python-chardet, python-beautifulsoup,
14 python-lxml, python-sqlite, python-enchant14 python-lxml, python-sqlite, python-enchant, python-migrate
15Conflicts: python-openlp15Conflicts: python-openlp
16Description: Church lyrics projection application16Description: Church lyrics projection application
17 OpenLP is free church presentation software, or lyrics projection software,17 OpenLP is free church presentation software, or lyrics projection software,
1818
=== 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 @@
46 'sqlalchemy': '0.5',46 'sqlalchemy': '0.5',
47 # pyenchant 1.6 required on Windows47 # pyenchant 1.6 required on Windows
48 'enchant': '1.6' if is_win else '1.3'48 'enchant': '1.6' if is_win else '1.3'
49 }49}
5050
51# pywin3251# pywin32
52WIN32_MODULES = [52WIN32_MODULES = [
53 'win32com',53 'win32com',
54 'win32ui',54 'win32ui',
55 'pywintypes',55 'pywintypes',
56 ]56]
5757
58MODULES = [58MODULES = [
59 'PyQt4',59 'PyQt4',
@@ -72,7 +72,8 @@
72 'enchant',72 'enchant',
73 'BeautifulSoup',73 'BeautifulSoup',
74 'mako',74 'mako',
75 ]75 'migrate',
76]
7677
7778
78OPTIONAL_MODULES = [79OPTIONAL_MODULES = [