28
28
from sqlalchemy import Table, Column, ForeignKey, types
29
from sqlalchemy.sql.expression import func, false, null, text
29
from sqlalchemy.sql.expression import func, false, null, text, and_, select
31
31
from openlp.core.lib.db import get_upgrade_op
32
32
from openlp.core.utils.db import drop_columns
34
34
log = logging.getLogger(__name__)
38
38
# TODO: When removing an upgrade path the ftw-data needs updating to the minimum supported version
105
105
# Since SQLite doesn't support changing the primary key of a table, we need to recreate the table
106
106
# and copy the old values
107
107
op = get_upgrade_op(session)
108
songs_table = Table('songs', metadata)
109
if 'author_type' not in [col.name for col in songs_table.c.values()]:
110
op.create_table('authors_songs_tmp',
111
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
112
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
113
Column('author_type', types.Unicode(255), primary_key=True,
114
nullable=False, server_default=text('""')))
108
authors_songs = Table('authors_songs', metadata, autoload=True)
109
if 'author_type' not in [col.name for col in authors_songs.c.values()]:
110
authors_songs_tmp = op.create_table(
112
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
113
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
114
Column('author_type', types.Unicode(255), primary_key=True,
115
nullable=False, server_default=text('""'))
115
117
op.execute('INSERT INTO authors_songs_tmp SELECT author_id, song_id, "" FROM authors_songs')
116
118
op.drop_table('authors_songs')
117
119
op.rename_table('authors_songs_tmp', 'authors_songs')
126
128
This upgrade adds support for multiple songbooks
128
130
op = get_upgrade_op(session)
129
songs_table = Table('songs', metadata)
130
if 'song_book_id' in [col.name for col in songs_table.c.values()]:
131
songs_table = Table('songs', metadata, autoload=True)
132
if 'song_book_id' not in [col.name for col in songs_table.c.values()]:
131
133
log.warning('Skipping upgrade_5 step of upgrading the song db')
134
136
# Create the mapping table (songs <-> songbooks)
135
op.create_table('songs_songbooks',
136
Column('songbook_id', types.Integer(), ForeignKey('song_books.id'), primary_key=True),
137
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
138
Column('entry', types.Unicode(255), primary_key=True, nullable=False))
137
songs_songbooks_table = op.create_table(
139
Column('songbook_id', types.Integer(), ForeignKey('song_books.id'), primary_key=True),
140
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
141
Column('entry', types.Unicode(255), primary_key=True, nullable=False)
140
144
# Migrate old data
141
145
op.execute('INSERT INTO songs_songbooks SELECT song_book_id, id, song_number FROM songs\
142
WHERE song_book_id IS NOT NULL AND song_number IS NOT NULL')
146
WHERE song_book_id IS NOT NULL AND song_book_id <> 0 AND song_number IS NOT NULL')
144
148
# Drop old columns
145
149
if metadata.bind.url.get_dialect().name == 'sqlite':
148
152
op.drop_constraint('songs_ibfk_1', 'songs', 'foreignkey')
149
153
op.drop_column('songs', 'song_book_id')
150
154
op.drop_column('songs', 'song_number')
157
def upgrade_6(session, metadata):
161
This is to fix an issue we had with songbooks with an id of "0" being imported in the previous upgrade.
163
op = get_upgrade_op(session)
164
songs_songbooks = Table('songs_songbooks', metadata, autoload=True)
165
del_query = songs_songbooks.delete().where(songs_songbooks.c.songbook_id == 0)
166
op.execute(del_query)
added
removed
openlp/plugins/songs/lib/upgrade.py
