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

Proposed by Raoul Snyman
Status: Merged
Approved by: Andreas Preikschat
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) Approve
Tim Bentley Approve
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
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:16:24 +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:16:24 +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:16:24 +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:16:24 +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:16:24 +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:16:24 +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 = [