1
# Copyright (C) 2012-2014 by the Free Software Foundation, Inc.
3
# This file is part of GNU Mailman.
5
# GNU Mailman is free software: you can redistribute it and/or modify it under
6
# the terms of the GNU General Public License as published by the Free
7
# Software Foundation, either version 3 of the License, or (at your option)
10
# GNU Mailman is distributed in the hope that it will be useful, but WITHOUT
11
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
12
# FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
15
# You should have received a copy of the GNU General Public License along with
16
# GNU Mailman. If not, see <http://www.gnu.org/licenses/>.
18
"""3.0b1 -> 3.0b2 schema migrations.
20
All column changes are in the `mailinglist` table.
23
- news_prefix_subject_too -> nntp_prefix_subject_too
24
- news_moderation -> newsgroup_moderation
27
- archive, archive_private -> archive_policy
30
- archive_volume_frequency
31
- generic_nonmember_action
38
member.mailing_list holds the list_id not the fqdn_listname
40
See https://bugs.launchpad.net/mailman/+bug/971013 for details.
43
from __future__ import absolute_import, print_function, unicode_literals
51
from mailman.database.schema.helpers import pivot
52
from mailman.interfaces.archiver import ArchivePolicy
55
VERSION = '20120407000000'
59
def upgrade(database, store, version, module_path):
60
if database.TAG == 'sqlite':
61
upgrade_sqlite(database, store, version, module_path)
63
upgrade_postgres(database, store, version, module_path)
67
def archive_policy(archive, archive_private):
68
"""Convert archive and archive_private to archive_policy."""
70
return ArchivePolicy.never.value
71
elif archive_private == 1:
72
return ArchivePolicy.private.value
74
return ArchivePolicy.public.value
78
def upgrade_sqlite(database, store, version, module_path):
79
# Load the first part of the migration. This creates a temporary table to
80
# hold the new mailinglist table columns. The problem is that some of the
81
# changes must be performed in Python, so after the first part is loaded,
82
# we do the Python changes, drop the old mailing list table, and then
83
# rename the temporary table to its place.
85
store, version, 'sqlite_{0}_01.sql'.format(version), module_path)
86
results = store.execute("""
87
SELECT id, include_list_post_header,
88
news_prefix_subject_too, news_moderation,
89
archive, archive_private, list_name, mail_host
94
news_prefix, news_moderation,
95
archive, archive_private,
96
list_name, mail_host) = value
97
# Figure out what the new archive_policy column value should be.
98
list_id = '{0}.{1}'.format(list_name, mail_host)
99
fqdn_listname = '{0}@{1}'.format(list_name, mail_host)
101
UPDATE mailinglist_backup SET
102
allow_list_posts = {0},
103
newsgroup_moderation = {1},
104
nntp_prefix_subject_too = {2},
105
archive_policy = {3},
112
archive_policy(archive, archive_private),
115
# Also update the member.mailing_list column to hold the list_id
116
# instead of the fqdn_listname.
120
WHERE mailing_list = '{1}';
121
""".format(list_id, fqdn_listname))
122
# Pivot the backup table to the real thing.
123
pivot(store, 'mailinglist')
124
# Now add some indexes that were previously missing.
126
'CREATE INDEX ix_mailinglist_list_id ON mailinglist (list_id);')
128
'CREATE INDEX ix_mailinglist_fqdn_listname '
129
'ON mailinglist (list_name, mail_host);')
130
# Now, do the member table.
131
results = store.execute('SELECT id, mailing_list FROM member;')
132
for id, mailing_list in results:
133
list_name, at, mail_host = mailing_list.partition('@')
135
list_id = mailing_list
137
list_id = '{0}.{1}'.format(list_name, mail_host)
139
UPDATE member_backup SET list_id = '{0}'
141
""".format(list_id, id))
142
# Pivot the backup table to the real thing.
143
pivot(store, 'member')
147
def upgrade_postgres(database, store, version, module_path):
148
# Get the old values from the mailinglist table.
149
results = store.execute("""
150
SELECT id, archive, archive_private, list_name, mail_host
153
# Do the simple renames first.
155
ALTER TABLE mailinglist
156
RENAME COLUMN news_prefix_subject_too TO nntp_prefix_subject_too;
159
ALTER TABLE mailinglist
160
RENAME COLUMN news_moderation TO newsgroup_moderation;
163
ALTER TABLE mailinglist
164
RENAME COLUMN include_list_post_header TO allow_list_posts;
166
# Do the easy column drops next.
167
for column in ('archive_volume_frequency',
168
'generic_nonmember_action',
171
'ALTER TABLE mailinglist DROP COLUMN {0};'.format(column))
172
# Now do the trickier collapsing of values. Add the new columns.
173
store.execute('ALTER TABLE mailinglist ADD COLUMN archive_policy INTEGER;')
174
store.execute('ALTER TABLE mailinglist ADD COLUMN list_id TEXT;')
175
# Query the database for the old values of archive and archive_private in
176
# each column. Then loop through all the results and update the new
177
# archive_policy from the old values.
178
for value in results:
179
id, archive, archive_private, list_name, mail_host = value
180
list_id = '{0}.{1}'.format(list_name, mail_host)
182
UPDATE mailinglist SET
183
archive_policy = {0},
186
""".format(archive_policy(archive, archive_private), list_id, id))
187
# Now drop the old columns.
188
for column in ('archive', 'archive_private'):
190
'ALTER TABLE mailinglist DROP COLUMN {0};'.format(column))
191
# Now add some indexes that were previously missing.
193
'CREATE INDEX ix_mailinglist_list_id ON mailinglist (list_id);')
195
'CREATE INDEX ix_mailinglist_fqdn_listname '
196
'ON mailinglist (list_name, mail_host);')
197
# Now, do the member table.
198
results = store.execute('SELECT id, mailing_list FROM member;')
199
store.execute('ALTER TABLE member ADD COLUMN list_id TEXT;')
200
for id, mailing_list in results:
201
list_name, at, mail_host = mailing_list.partition('@')
203
list_id = mailing_list
205
list_id = '{0}.{1}'.format(list_name, mail_host)
207
UPDATE member SET list_id = '{0}'
209
""".format(list_id, id))
210
store.execute('ALTER TABLE member DROP COLUMN mailing_list;')
211
# Record the migration in the version table.
212
database.load_schema(store, version, None, module_path)