diff options
Diffstat (limited to 'src/mailman/database')
| -rw-r--r-- | src/mailman/database/schema/mm_20120407000000.py | 95 | ||||
| -rw-r--r-- | src/mailman/database/schema/sqlite_20120407000000_01.sql | 40 | ||||
| -rw-r--r-- | src/mailman/database/tests/test_migrations.py | 31 |
3 files changed, 136 insertions, 30 deletions
diff --git a/src/mailman/database/schema/mm_20120407000000.py b/src/mailman/database/schema/mm_20120407000000.py index 068a05834..f351d60ca 100644 --- a/src/mailman/database/schema/mm_20120407000000.py +++ b/src/mailman/database/schema/mm_20120407000000.py @@ -31,6 +31,12 @@ All column changes are in the `mailinglist` table. - generic_nonmember_action - nntp_host +* Added: + - list_id + +* Changes: + member.mailing_list holds the list_id not the fqdn_listname + See https://bugs.launchpad.net/mailman/+bug/971013 for details. """ @@ -77,29 +83,61 @@ def upgrade_sqlite(database, store, version, module_path): # rename the temporary table to its place. database.load_schema( store, version, 'sqlite_{0}_01.sql'.format(version), module_path) - results = store.execute( - 'SELECT id, include_list_post_header, ' - 'news_prefix_subject_too, news_moderation, ' - 'archive, archive_private FROM mailinglist;') + results = store.execute(""" + SELECT id, include_list_post_header, + news_prefix_subject_too, news_moderation, + archive, archive_private, list_name, mail_host + FROM mailinglist; + """) for value in results: (id, list_post, news_prefix, news_moderation, - archive, archive_private) = value + archive, archive_private, + list_name, mail_host) = value # Figure out what the new archive_policy column value should be. - store.execute( - 'UPDATE ml_backup SET ' - ' allow_list_posts = {0}, ' - ' newsgroup_moderation = {1}, ' - ' nntp_prefix_subject_too = {2}, ' - ' archive_policy = {3} ' - 'WHERE id = {4};'.format( + list_id = '{0}.{1}'.format(list_name, mail_host) + fqdn_listname = '{0}@{1}'.format(list_name, mail_host) + store.execute(""" + UPDATE ml_backup SET + allow_list_posts = {0}, + newsgroup_moderation = {1}, + nntp_prefix_subject_too = {2}, + archive_policy = {3}, + list_id = '{4}' + WHERE id = {5}; + """.format( list_post, news_moderation, news_prefix, archive_policy(archive, archive_private), + list_id, id)) + # Also update the member.mailing_list column to hold the list_id + # instead of the fqdn_listname. + store.execute(""" + UPDATE member SET + mailing_list = '{0}' + WHERE mailing_list = '{1}'; + """.format(list_id, fqdn_listname)) + # Pivot the backup table to the real thing. store.execute('DROP TABLE mailinglist;') store.execute('ALTER TABLE ml_backup RENAME TO mailinglist;') + # Now add some indexes that were previously missing. + store.execute( + 'CREATE INDEX ix_mailinglist_list_id ON mailinglist (list_id);') + store.execute( + 'CREATE INDEX ix_mailinglist_fqdn_listname ' + 'ON mailinglist (list_name, mail_host);') + # Now, do the member table. + results = store.execute('SELECT id, mailing_list FROM member;') + for id, mailing_list in results: + store.execute(""" + UPDATE mem_backup SET list_id = '{0}' + WHERE id = {1}; + """.format(list_id, id)) + # Pivot the backup table to the real thing. + store.execute('DROP TABLE member;') + store.execute('ALTER TABLE mem_backup RENAME TO member;') @@ -108,17 +146,20 @@ def upgrade_postgres(database, store, version, module_path): results = store.execute( 'SELECT id, archive, archive_private FROM mailinglist;') # Do the simple renames first. - store.execute( - 'ALTER TABLE mailinglist ' - ' RENAME COLUMN news_prefix_subject_too TO nntp_prefix_subject_too;') - store.execute( - 'ALTER TABLE mailinglist ' - ' RENAME COLUMN news_moderation TO newsgroup_moderation;') - store.execute( - 'ALTER TABLE mailinglist ' - ' RENAME COLUMN include_list_post_header TO allow_list_posts;') + store.execute(""" + ALTER TABLE mailinglist + RENAME COLUMN news_prefix_subject_too TO nntp_prefix_subject_too; + """) + store.execute(""" + ALTER TABLE mailinglist + RENAME COLUMN news_moderation TO newsgroup_moderation; + """) + store.execute(""" + ALTER TABLE mailinglist + RENAME COLUMN include_list_post_header TO allow_list_posts; + """) # Do the easy column drops next. - for column in ('archive_volume_frequency', + for column in ('archive_volume_frequency', 'generic_nonmember_action', 'nntp_host'): store.execute( @@ -130,11 +171,11 @@ def upgrade_postgres(database, store, version, module_path): # archive_policy from the old values. for value in results: id, archive, archive_private = value - store.execute('UPDATE mailinglist SET ' - ' archive_policy = {0} ' - 'WHERE id = {1};'.format( - archive_policy(archive, archive_private), - id)) + store.execute(""" + UPDATE mailinglist SET + archive_policy = {0} + WHERE id = {1}; + """.format(archive_policy(archive, archive_private), id)) # Now drop the old columns. for column in ('archive', 'archive_private'): store.execute( diff --git a/src/mailman/database/schema/sqlite_20120407000000_01.sql b/src/mailman/database/schema/sqlite_20120407000000_01.sql index a4eb0adce..b93e214c4 100644 --- a/src/mailman/database/schema/sqlite_20120407000000_01.sql +++ b/src/mailman/database/schema/sqlite_20120407000000_01.sql @@ -6,7 +6,7 @@ -- For SQLite3 migration strategy, see -- http://sqlite.org/faq.html#q11 --- This is the base mailinglist table but with these changes: +-- REMOVALS from the mailinglist table. -- REM archive -- REM archive_private -- REM archive_volume_frequency @@ -15,15 +15,25 @@ -- REM news_prefix_subject_too -- REM nntp_host -- --- THESE COLUMNS ARE ADDED BY THE PYTHON MIGRATION LAYER: +-- ADDS to the mailing list table. -- ADD allow_list_posts -- ADD archive_policy +-- ADD list_id -- ADD newsgroup_moderation -- ADD nntp_prefix_subject_too -- LP: #971013 -- LP: #967238 +-- REMOVALS from the member table. +-- REM mailing_list + +-- ADDS to the member table. +-- ADD list_id + +-- LP: #1024509 + + CREATE TABLE ml_backup( id INTEGER NOT NULL, -- List identity @@ -132,7 +142,6 @@ CREATE TABLE ml_backup( PRIMARY KEY (id) ); - INSERT INTO ml_backup SELECT id, -- List identity @@ -240,7 +249,32 @@ INSERT INTO ml_backup SELECT welcome_message_uri FROM mailinglist; +CREATE TABLE mem_backup( + id INTEGER NOT NULL, + _member_id TEXT, + role INTEGER, + moderation_action INTEGER, + address_id INTEGER, + preferences_id INTEGER, + user_id INTEGER, + PRIMARY KEY (id) + ); + +INSERT INTO mem_backup SELECT + id, + _member_id, + role, + moderation_action, + address_id, + preferences_id, + user_id + FROM member; + + -- Add the new columns. They'll get inserted at the Python layer. ALTER TABLE ml_backup ADD COLUMN archive_policy INTEGER; +ALTER TABLE ml_backup ADD COLUMN list_id TEXT; ALTER TABLE ml_backup ADD COLUMN nntp_prefix_subject_too INTEGER; ALTER TABLE ml_backup ADD COLUMN newsgroup_moderation INTEGER; + +ALTER TABLE mem_backup ADD COLUMN list_id TEXT; diff --git a/src/mailman/database/tests/test_migrations.py b/src/mailman/database/tests/test_migrations.py index c69a8c545..8df50f40e 100644 --- a/src/mailman/database/tests/test_migrations.py +++ b/src/mailman/database/tests/test_migrations.py @@ -39,6 +39,7 @@ from mailman.interfaces.archiver import ArchivePolicy from mailman.interfaces.listmanager import IListManager from mailman.interfaces.mailinglist import IAcceptableAliasSet from mailman.interfaces.nntp import NewsgroupModeration +from mailman.interfaces.subscriptions import ISubscriptionService from mailman.testing.helpers import temporary_db from mailman.testing.layers import ConfigLayer @@ -54,10 +55,14 @@ class MigrationTestBase(unittest.TestCase): * news_prefix_subject_too -> nntp_prefix_subject_too * include_list_post_header -> allow_list_posts * ADD archive_policy + * ADD list_id * REMOVE archive * REMOVE archive_private * REMOVE archive_volume_frequency * REMOVE nntp_host + + table member: + * mailing_list -> list_id """ layer = ConfigLayer @@ -83,11 +88,15 @@ class TestMigration20120407Schema(MigrationTestBase): # Verify that the database has not yet been migrated. for missing in ('allow_list_posts', 'archive_policy', + 'list_id', 'nntp_prefix_subject_too'): self.assertRaises(DatabaseError, self._database.store.execute, 'select {0} from mailinglist;'.format(missing)) self._database.store.rollback() + self.assertRaises(DatabaseError, + self._database.store.execute, + 'select list_id from member;') for present in ('archive', 'archive_private', 'archive_volume_frequency', @@ -100,6 +109,8 @@ class TestMigration20120407Schema(MigrationTestBase): # that we can perform? self._database.store.execute( 'select {0} from mailinglist;'.format(present)) + # Again, this should not produce an exception. + self._database.store.execute('select mailing_list from member;') def test_post_upgrade_columns_migration(self): # Test that after the migration, the old table columns are missing @@ -111,11 +122,13 @@ class TestMigration20120407Schema(MigrationTestBase): # Verify that the database has been migrated. for present in ('allow_list_posts', 'archive_policy', + 'list_id', 'nntp_prefix_subject_too'): # This should not produce an exception. Is there some better test # that we can perform? self._database.store.execute( 'select {0} from mailinglist;'.format(present)) + self._database.store.execute('select list_id from member;') for missing in ('archive', 'archive_private', 'archive_volume_frequency', @@ -128,6 +141,9 @@ class TestMigration20120407Schema(MigrationTestBase): self._database.store.execute, 'select {0} from mailinglist;'.format(missing)) self._database.store.rollback() + self.assertRaises(DatabaseError, + self._database.store.execute, + 'select mailing_list from member;') @@ -263,6 +279,21 @@ class TestMigration20120407MigratedData(MigrationTestBase): mlist = getUtility(IListManager).get('test@example.com') self.assertEqual(mlist.archive_policy, ArchivePolicy.public) + def test_list_id(self): + # Test that the mailinglist table gets a list_id column. + self._upgrade() + with temporary_db(self._database): + mlist = getUtility(IListManager).get('test@example.com') + self.assertEqual(mlist.list_id, 'test.example.com') + + def test_list_id_member(self): + # Test that the member table's mailing_list column becomes list_id. + self._upgrade() + with temporary_db(self._database): + service = getUtility(ISubscriptionService) + members = list(service.find_members(list_id='test.example.com')) + self.assertEqual(len(members), 4) + def test_news_moderation_none(self): # Test that news_moderation becomes newsgroup_moderation. self._database.store.execute( |