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

Proposed by Raoul Snyman on 2011-08-25
Status: Superseded
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) Needs Fixing on 2011-08-25
Tim Bentley 2011-08-25 Approve on 2011-08-25
Review via email: mp+72916@code.launchpad.net

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

This proposal has been superseded by a proposal from 2011-08-25.

Commit message

Implemented a seamless database upgrade system.

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 :

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) wrote :

Works for me.

review: Approve
Andreas Preikschat (googol) wrote :

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 updated on 2011-08-25
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

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== modified file 'openlp/core/lib/db.py'
2--- openlp/core/lib/db.py 2011-08-16 19:53:52 +0000
3+++ openlp/core/lib/db.py 2011-08-25 20:15:14 +0000
4@@ -31,11 +31,13 @@
5 import os
6
7 from PyQt4 import QtCore
8-from sqlalchemy import create_engine, MetaData
9-from sqlalchemy.exc import InvalidRequestError
10-from sqlalchemy.orm import scoped_session, sessionmaker
11+from sqlalchemy import Table, MetaData, Column, types, create_engine
12+from sqlalchemy.exc import SQLAlchemyError, InvalidRequestError, DBAPIError
13+from sqlalchemy.orm import scoped_session, sessionmaker, mapper
14 from sqlalchemy.pool import NullPool
15
16+from openlp.core.lib import translate
17+from openlp.core.lib.ui import critical_error_message_box
18 from openlp.core.utils import AppLocation, delete_file
19
20 log = logging.getLogger(__name__)
21@@ -59,6 +61,48 @@
22 autocommit=auto_commit, bind=engine))
23 return session, metadata
24
25+
26+def upgrade_db(url, upgrade):
27+ """
28+ Upgrade a database.
29+
30+ ``url``
31+ The url of the database to upgrade.
32+
33+ ``upgrade``
34+ The python module that contains the upgrade instructions.
35+ """
36+ session, metadata = init_db(url)
37+ tables = upgrade.upgrade_setup(metadata)
38+ metadata_table = Table(u'metadata', metadata,
39+ Column(u'key', types.Unicode(64), primary_key=True),
40+ Column(u'value', types.UnicodeText(), default=None)
41+ )
42+ metadata_table.create(checkfirst=True)
43+ mapper(Metadata, metadata_table)
44+ version_meta = session.query(Metadata).get(u'version')
45+ if version_meta is None:
46+ version_meta = Metadata.populate(key=u'version', value=u'0')
47+ version = 0
48+ else:
49+ version = int(version_meta.value)
50+ if version > upgrade.__version__:
51+ return version, upgrade.__version__
52+ version += 1
53+ while hasattr(upgrade, u'upgrade_%d' % version):
54+ log.debug(u'Running upgrade_%d', version)
55+ try:
56+ getattr(upgrade, u'upgrade_%d' % version)(session, metadata, tables)
57+ version_meta.value = unicode(version)
58+ except SQLAlchemyError, DBAPIError:
59+ log.exception(u'Could not run database upgrade script "upgrade_%s"'\
60+ ', upgrade process has been halted.', version)
61+ break
62+ version += 1
63+ session.add(version_meta)
64+ session.commit()
65+ return int(version_meta.value), upgrade.__version__
66+
67 def delete_database(plugin_name, db_file_name=None):
68 """
69 Remove a database file from the system.
70@@ -79,6 +123,7 @@
71 AppLocation.get_section_data_path(plugin_name), plugin_name)
72 return delete_file(db_file_path)
73
74+
75 class BaseModel(object):
76 """
77 BaseModel provides a base object with a set of generic functions
78@@ -94,11 +139,19 @@
79 return instance
80
81
82+class Metadata(BaseModel):
83+ """
84+ Provides a class for the metadata table.
85+ """
86+ pass
87+
88+
89 class Manager(object):
90 """
91 Provide generic object persistence management
92 """
93- def __init__(self, plugin_name, init_schema, db_file_name=None):
94+ def __init__(self, plugin_name, init_schema, db_file_name=None,
95+ upgrade_mod=None):
96 """
97 Runs the initialisation process that includes creating the connection
98 to the database and the tables if they don't exist.
99@@ -109,6 +162,9 @@
100 ``init_schema``
101 The init_schema function for this database
102
103+ ``upgrade_schema``
104+ The upgrade_schema function for this database
105+
106 ``db_file_name``
107 The file name to use for this database. Defaults to None resulting
108 in the plugin_name being used.
109@@ -134,7 +190,27 @@
110 unicode(settings.value(u'db hostname').toString()),
111 unicode(settings.value(u'db database').toString()))
112 settings.endGroup()
113- self.session = init_schema(self.db_url)
114+ if upgrade_mod:
115+ db_ver, up_ver = upgrade_db(self.db_url, upgrade_mod)
116+ if db_ver > up_ver:
117+ critical_error_message_box(
118+ translate('OpenLP.Manager', 'Database Error'),
119+ unicode(translate('OpenLP.Manager', 'The database being '
120+ 'loaded was created in a more recent version of '
121+ 'OpenLP. The database is version %d, while OpenLP '
122+ 'expects version %d. The database will not be loaded.'
123+ '\n\nDatabase: %s')) % \
124+ (db_ver, up_ver, self.db_url)
125+ )
126+ return
127+ try:
128+ self.session = init_schema(self.db_url)
129+ except:
130+ critical_error_message_box(
131+ translate('OpenLP.Manager', 'Database Error'),
132+ unicode(translate('OpenLP.Manager', 'OpenLP cannot load your '
133+ 'database.\n\nDatabase: %s')) % self.db_url
134+ )
135
136 def save_object(self, object_instance, commit=True):
137 """
138
139=== modified file 'openlp/plugins/songs/lib/db.py'
140--- openlp/plugins/songs/lib/db.py 2011-07-07 18:03:12 +0000
141+++ openlp/plugins/songs/lib/db.py 2011-08-25 20:15:14 +0000
142@@ -70,7 +70,6 @@
143 """
144 pass
145
146-
147 def init_schema(url):
148 """
149 Setup the songs database connection and initialise the database schema.
150@@ -111,10 +110,6 @@
151 * file_name
152 * type
153
154- **media_files_songs Table**
155- * media_file_id
156- * song_id
157-
158 **song_books Table**
159 The *song_books* table holds a list of books that a congregation gets
160 their songs from, or old hymnals now no longer used. This table has the
161@@ -162,7 +157,7 @@
162
163 # Definition of the "authors" table
164 authors_table = Table(u'authors', metadata,
165- Column(u'id', types.Integer, primary_key=True),
166+ Column(u'id', types.Integer(), primary_key=True),
167 Column(u'first_name', types.Unicode(128)),
168 Column(u'last_name', types.Unicode(128)),
169 Column(u'display_name', types.Unicode(255), index=True, nullable=False)
170@@ -170,22 +165,25 @@
171
172 # Definition of the "media_files" table
173 media_files_table = Table(u'media_files', metadata,
174- Column(u'id', types.Integer, primary_key=True),
175+ Column(u'id', types.Integer(), primary_key=True),
176+ Column(u'song_id', types.Integer(), ForeignKey(u'songs.id'),
177+ default=None),
178 Column(u'file_name', types.Unicode(255), nullable=False),
179- Column(u'type', types.Unicode(64), nullable=False, default=u'audio')
180+ Column(u'type', types.Unicode(64), nullable=False, default=u'audio'),
181+ Column(u'weight', types.Integer(), default=0)
182 )
183
184 # Definition of the "song_books" table
185 song_books_table = Table(u'song_books', metadata,
186- Column(u'id', types.Integer, primary_key=True),
187+ Column(u'id', types.Integer(), primary_key=True),
188 Column(u'name', types.Unicode(128), nullable=False),
189 Column(u'publisher', types.Unicode(128))
190 )
191
192 # Definition of the "songs" table
193 songs_table = Table(u'songs', metadata,
194- Column(u'id', types.Integer, primary_key=True),
195- Column(u'song_book_id', types.Integer,
196+ Column(u'id', types.Integer(), primary_key=True),
197+ Column(u'song_book_id', types.Integer(),
198 ForeignKey(u'song_books.id'), default=None),
199 Column(u'title', types.Unicode(255), nullable=False),
200 Column(u'alternate_title', types.Unicode(255)),
201@@ -202,31 +200,23 @@
202
203 # Definition of the "topics" table
204 topics_table = Table(u'topics', metadata,
205- Column(u'id', types.Integer, primary_key=True),
206+ Column(u'id', types.Integer(), primary_key=True),
207 Column(u'name', types.Unicode(128), index=True, nullable=False)
208 )
209
210 # Definition of the "authors_songs" table
211 authors_songs_table = Table(u'authors_songs', metadata,
212- Column(u'author_id', types.Integer,
213+ Column(u'author_id', types.Integer(),
214 ForeignKey(u'authors.id'), primary_key=True),
215- Column(u'song_id', types.Integer,
216- ForeignKey(u'songs.id'), primary_key=True)
217- )
218-
219- # Definition of the "media_files_songs" table
220- media_files_songs_table = Table(u'media_files_songs', metadata,
221- Column(u'media_file_id', types.Integer,
222- ForeignKey(u'media_files.id'), primary_key=True),
223- Column(u'song_id', types.Integer,
224+ Column(u'song_id', types.Integer(),
225 ForeignKey(u'songs.id'), primary_key=True)
226 )
227
228 # Definition of the "songs_topics" table
229 songs_topics_table = Table(u'songs_topics', metadata,
230- Column(u'song_id', types.Integer,
231+ Column(u'song_id', types.Integer(),
232 ForeignKey(u'songs.id'), primary_key=True),
233- Column(u'topic_id', types.Integer,
234+ Column(u'topic_id', types.Integer(),
235 ForeignKey(u'topics.id'), primary_key=True)
236 )
237
238@@ -238,8 +228,7 @@
239 'authors': relation(Author, backref='songs',
240 secondary=authors_songs_table, lazy=False),
241 'book': relation(Book, backref='songs'),
242- 'media_files': relation(MediaFile, backref='songs',
243- secondary=media_files_songs_table),
244+ 'media_files': relation(MediaFile, backref='songs'),
245 'topics': relation(Topic, backref='songs',
246 secondary=songs_topics_table)
247 })
248
249=== added file 'openlp/plugins/songs/lib/upgrade.py'
250--- openlp/plugins/songs/lib/upgrade.py 1970-01-01 00:00:00 +0000
251+++ openlp/plugins/songs/lib/upgrade.py 2011-08-25 20:15:14 +0000
252@@ -0,0 +1,77 @@
253+# -*- coding: utf-8 -*-
254+# vim: autoindent shiftwidth=4 expandtab textwidth=80 tabstop=4 softtabstop=4
255+
256+###############################################################################
257+# OpenLP - Open Source Lyrics Projection #
258+# --------------------------------------------------------------------------- #
259+# Copyright (c) 2008-2011 Raoul Snyman #
260+# Portions copyright (c) 2008-2011 Tim Bentley, Gerald Britton, Jonathan #
261+# Corwin, Michael Gorven, Scott Guerrieri, Matthias Hub, Meinert Jordan, #
262+# Armin Köhler, Joshua Miller, Stevan Pettit, Andreas Preikschat, Mattias #
263+# Põldaru, Christian Richter, Philip Ridout, Simon Scudder, Jeffrey Smith, #
264+# Maikel Stuivenberg, Martin Thompson, Jon Tibble, Frode Woldsund #
265+# --------------------------------------------------------------------------- #
266+# This program is free software; you can redistribute it and/or modify it #
267+# under the terms of the GNU General Public License as published by the Free #
268+# Software Foundation; version 2 of the License. #
269+# #
270+# This program is distributed in the hope that it will be useful, but WITHOUT #
271+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or #
272+# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for #
273+# more details. #
274+# #
275+# You should have received a copy of the GNU General Public License along #
276+# with this program; if not, write to the Free Software Foundation, Inc., 59 #
277+# Temple Place, Suite 330, Boston, MA 02111-1307 USA #
278+###############################################################################
279+"""
280+The :mod:`upgrade` module provides a way for the database and schema that is the backend for
281+the Songs plugin
282+"""
283+
284+from sqlalchemy import Column, ForeignKey, Table, types
285+from migrate import changeset
286+from migrate.changeset.constraint import ForeignKeyConstraint
287+
288+__version__ = 1
289+
290+def upgrade_setup(metadata):
291+ """
292+ Set up the latest revision all tables, with reflection, needed for the
293+ upgrade process. If you want to drop a table, you need to remove it from
294+ here, and add it to your upgrade function.
295+ """
296+ tables = {
297+ u'authors': Table(u'authors', metadata, autoload=True),
298+ u'media_files': Table(u'media_files', metadata, autoload=True),
299+ u'song_books': Table(u'song_books', metadata, autoload=True),
300+ u'songs': Table(u'songs', metadata, autoload=True),
301+ u'topics': Table(u'topics', metadata, autoload=True),
302+ u'authors_songs': Table(u'authors_songs', metadata, autoload=True),
303+ u'songs_topics': Table(u'songs_topics', metadata, autoload=True)
304+ }
305+ return tables
306+
307+
308+def upgrade_1(session, metadata, tables):
309+ """
310+ Version 1 upgrade.
311+
312+ This upgrade removes the many-to-many relationship between songs and
313+ media_files and replaces it with a one-to-many, which is far more
314+ representative of the real relationship between the two entities.
315+
316+ In order to facilitate this one-to-many relationship, a song_id column is
317+ added to the media_files table, and a weight column so that the media
318+ files can be ordered.
319+ """
320+ Table(u'media_files_songs', metadata, autoload=True).drop(checkfirst=True)
321+ Column(u'song_id', types.Integer(), default=None)\
322+ .create(table=tables[u'media_files'], populate_default=True)
323+ Column(u'weight', types.Integer(), default=0)\
324+ .create(table=tables[u'media_files'], populate_default=True)
325+ if metadata.bind.url.get_dialect().name != 'sqlite':
326+ # SQLite doesn't support ALTER TABLE ADD CONSTRAINT
327+ ForeignKeyConstraint([u'song_id'], [u'songs.id'],
328+ table=tables[u'media_files']).create()
329+
330
331=== modified file 'openlp/plugins/songs/songsplugin.py'
332--- openlp/plugins/songs/songsplugin.py 2011-07-23 21:29:24 +0000
333+++ openlp/plugins/songs/songsplugin.py 2011-08-25 20:15:14 +0000
334@@ -36,7 +36,8 @@
335 from openlp.core.lib.db import Manager
336 from openlp.core.lib.ui import UiStrings, base_action, icon_action
337 from openlp.core.utils.actions import ActionList
338-from openlp.plugins.songs.lib import clean_song, SongMediaItem, SongsTab
339+from openlp.plugins.songs.lib import clean_song, upgrade, SongMediaItem, \
340+ SongsTab
341 from openlp.plugins.songs.lib.db import init_schema, Song
342 from openlp.plugins.songs.lib.importer import SongFormat
343 from openlp.plugins.songs.lib.olpimport import OpenLPSongImport
344@@ -58,8 +59,8 @@
345 Create and set up the Songs plugin.
346 """
347 Plugin.__init__(self, u'songs', plugin_helpers, SongMediaItem, SongsTab)
348+ self.manager = Manager(u'songs', init_schema, upgrade_mod=upgrade)
349 self.weight = -10
350- self.manager = Manager(u'songs', init_schema)
351 self.icon_path = u':/plugins/plugin_songs.png'
352 self.icon = build_icon(self.icon_path)
353
354
355=== modified file 'resources/debian/debian/control'
356--- resources/debian/debian/control 2011-03-09 06:55:41 +0000
357+++ resources/debian/debian/control 2011-08-25 20:15:14 +0000
358@@ -11,7 +11,7 @@
359 Architecture: all
360 Depends: ${shlibs:Depends}, ${misc:Depends}, ${python:Depends}, python-qt4,
361 python-qt4-phonon, python-sqlalchemy, python-chardet, python-beautifulsoup,
362- python-lxml, python-sqlite, python-enchant
363+ python-lxml, python-sqlite, python-enchant, python-migrate
364 Conflicts: python-openlp
365 Description: Church lyrics projection application
366 OpenLP is free church presentation software, or lyrics projection software,
367
368=== modified file 'scripts/check_dependencies.py'
369--- scripts/check_dependencies.py 2011-07-15 17:38:09 +0000
370+++ scripts/check_dependencies.py 2011-08-25 20:15:14 +0000
371@@ -46,14 +46,14 @@
372 'sqlalchemy': '0.5',
373 # pyenchant 1.6 required on Windows
374 'enchant': '1.6' if is_win else '1.3'
375- }
376+}
377
378 # pywin32
379 WIN32_MODULES = [
380 'win32com',
381 'win32ui',
382 'pywintypes',
383- ]
384+]
385
386 MODULES = [
387 'PyQt4',
388@@ -72,7 +72,8 @@
389 'enchant',
390 'BeautifulSoup',
391 'mako',
392- ]
393+ 'migrate',
394+]
395
396
397 OPTIONAL_MODULES = [