Database

--------
 * The `ban` table now uses list-ids to cross-reference the mailing list,
   since these cannot change even if the mailing list is moved or renamed.

Interfaces
----------
 * The `IBanManager` is no longer a global utility.  Instead, you adapt an
   `IMailingList` to an `IBanManager` to manage the bans for a specific
   mailing list.  To manage the global bans, adapt ``None``.

6 files changed, 232 insertions, 81 deletions

diff --git a/src/mailman/database/docs/migration.rst b/src/mailman/database/docs/migration.rst
index 6216f9bbc..2988579d3 100644
--- a/src/mailman/database/docs/migration.rst
+++ b/src/mailman/database/docs/migration.rst
@@ -30,12 +30,13 @@ already applied.
     >>> from mailman.model.version import Version
     >>> results = config.db.store.find(Version, component='schema')
     >>> results.count()
-    2
+    3
     >>> versions = sorted(result.version for result in results)
     >>> for version in versions:
     ...     print version
     00000000000000
     20120407000000
+    20121015000000
 
 
 Migrations
@@ -96,6 +97,7 @@ This will load the new migration, since it hasn't been loaded before.
     ...     print result
     00000000000000
     20120407000000
+    20121015000000
     20129999000000
     >>> test = config.db.store.find(Version, component='test').one()
     >>> print test.version
@@ -126,6 +128,7 @@ The first time we load this new migration, we'll get the 801 marker.
     ...     print result
     00000000000000
     20120407000000
+    20121015000000
     20129999000000
     20129999000001
     >>> test = config.db.store.find(Version, component='test')
@@ -142,6 +145,7 @@ We do not get an 802 marker because the migration has already been loaded.
     ...     print result
     00000000000000
     20120407000000
+    20121015000000
     20129999000000
     20129999000001
     >>> test = config.db.store.find(Version, component='test')
@@ -176,6 +180,7 @@ You'll notice that the ...04 version is not present.
     ...     print result
     00000000000000
     20120407000000
+    20121015000000
     20129999000000
     20129999000001
     20129999000002
diff --git a/src/mailman/database/schema/mm_20120407000000.py b/src/mailman/database/schema/mm_20120407000000.py
index d6e647c1a..3910e9438 100644
--- a/src/mailman/database/schema/mm_20120407000000.py
+++ b/src/mailman/database/schema/mm_20120407000000.py
@@ -149,7 +149,7 @@ def upgrade_sqlite(database, store, version, module_path):
 def upgrade_postgres(database, store, version, module_path):
     # Get the old values from the mailinglist table.
     results = store.execute("""
-        SELECT id, archive, archive_private, list_name, mail_host 
+        SELECT id, archive, archive_private, list_name, mail_host
         FROM mailinglist;
         """)
     # Do the simple renames first.
diff --git a/src/mailman/database/schema/mm_20121015000000.py b/src/mailman/database/schema/mm_20121015000000.py
new file mode 100644
index 000000000..51e0602e7
--- /dev/null
+++ b/src/mailman/database/schema/mm_20121015000000.py
@@ -0,0 +1,88 @@
+# Copyright (C) 2012 by the Free Software Foundation, Inc.
+#
+# This file is part of GNU Mailman.
+#
+# GNU Mailman is free software: you can redistribute it and/or modify it under
+# the terms of the GNU General Public License as published by the Free
+# Software Foundation, either version 3 of the License, or (at your option)
+# any later version.
+#
+# GNU Mailman is distributed in the hope that it will be useful, but WITHOUT
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
+# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
+# more details.
+#
+# You should have received a copy of the GNU General Public License along with
+# GNU Mailman.  If not, see <http://www.gnu.org/licenses/>.
+
+"""3.0b2 -> 3.0b3 schema migrations.
+
+* bans.mailing_list -> bans.list_id
+"""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'upgrade',
+    ]
+
+
+VERSION = '20121015000000'
+
+
+
+def upgrade(database, store, version, module_path):
+    if database.TAG == 'sqlite':
+        upgrade_sqlite(database, store, version, module_path)
+    else:
+        upgrade_postgres(database, store, version, module_path)
+
+
+
+def _make_listid(fqdn_listname):
+    list_name, at, mail_host = fqdn_listname.partition('@')
+    if at == '':
+        # If there is no @ sign in the value, assume it already contains the
+        # list-id.
+        return fqdn_listname
+    return '{0}.{1}'.format(list_name, mail_host)
+
+
+
+def upgrade_sqlite(database, store, version, module_path):
+    database.load_schema(
+        store, version, 'sqlite_{0}_01.sql'.format(version), module_path)
+    results = store.execute("""
+        SELECT id, mailing_list
+        FROM ban;
+        """)
+    for id, mailing_list in results:
+        # Skip global bans since there's nothing to update.
+        if mailing_list is None:
+            continue
+        store.execute("""
+            UPDATE ban_backup SET list_id = '{0}'
+            WHERE id = {1};
+            """.format(_make_listid(mailing_list), id))
+    # Pivot the backup table to the real thing.
+    store.execute('DROP TABLE ban;')
+    store.execute('ALTER TABLE ban_backup RENAME TO ban;')
+
+
+
+def upgrade_postgres(database, store, version, module_path):
+    # Get the old values from the ban table.
+    results = store.execute('SELECT id, mailing_list FROM ban;')
+    store.execute('ALTER TABLE ban ADD COLUMN list_id TEXT;')
+    for id, mailing_list in results:
+        # Skip global bans since there's nothing to update.
+        if mailing_list is None:
+            continue
+        store.execute("""
+            UPDATE ban SET list_id = '{0}'
+            WHERE id = {1};
+            """.format(_make_listid(mailing_list), id))
+    store.execute('ALTER TABLE ban DROP COLUMN mailing_list;')
+    # Record the migration in the version table.
+    database.load_schema(store, version, None, module_path)
diff --git a/src/mailman/database/schema/sqlite_20120407000000_01.sql b/src/mailman/database/schema/sqlite_20120407000000_01.sql
index b93e214c4..53bab70dd 100644
--- a/src/mailman/database/schema/sqlite_20120407000000_01.sql
+++ b/src/mailman/database/schema/sqlite_20120407000000_01.sql
@@ -269,7 +269,7 @@ INSERT INTO mem_backup SELECT
     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;
diff --git a/src/mailman/database/schema/sqlite_20121015000000_01.sql b/src/mailman/database/schema/sqlite_20121015000000_01.sql
new file mode 100644
index 000000000..c0df75111
--- /dev/null
+++ b/src/mailman/database/schema/sqlite_20121015000000_01.sql
@@ -0,0 +1,22 @@
+-- THIS FILE CONTAINS THE SQLITE3 SCHEMA MIGRATION FROM
+-- 3.0b2 TO 3.0b3
+--
+-- AFTER 3.0b3 IS RELEASED YOU MAY NOT EDIT THIS FILE.
+
+-- REMOVALS from the ban table.
+-- REM mailing_list
+
+-- ADDS to the ban table.
+-- ADD list_id
+
+CREATE TABLE ban_backup (
+    id INTEGER NOT NULL,
+    email TEXT,
+    PRIMARY KEY (id)
+    );
+
+INSERT INTO ban_backup SELECT
+    id, email
+    FROM ban;
+
+ALTER TABLE ban_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 f50ce35d3..4401b030f 100644
--- a/src/mailman/database/tests/test_migrations.py
+++ b/src/mailman/database/tests/test_migrations.py
@@ -29,6 +29,7 @@ __all__ = [
 
 import unittest
 
+from operator import attrgetter
 from pkg_resources import resource_string
 from storm.exceptions import DatabaseError
 from zope.component import getUtility
@@ -40,30 +41,14 @@ 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.model.bans import Ban
 from mailman.testing.helpers import temporary_db
 from mailman.testing.layers import ConfigLayer
 
 
 
 class MigrationTestBase(unittest.TestCase):
-    """Test the dated migration (LP: #971013)
-
-    Circa: 3.0b1 -> 3.0b2
-
-    table mailinglist:
-    * news_moderation -> newsgroup_moderation
-    * 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
-    """
+    """Test database migrations."""
 
     layer = ConfigLayer
 
@@ -73,6 +58,30 @@ class MigrationTestBase(unittest.TestCase):
     def tearDown(self):
         self._database._cleanup()
 
+    def _missing_present(self, table, migrations, missing, present):
+        """The appropriate migrations leave columns missing and present.
+
+        :param table: The table to test columns from.
+        :param migrations: Sequence of migrations to load.
+        :param missing: Set of columns which should be missing after the
+            migrations are loaded.
+        :param present: Set of columns which should be present after the
+            migrations are loaded.
+        """
+        for migration in migrations:
+            self._database.load_migrations(migration)
+            self._database.store.commit()
+        for column in missing:
+            self.assertRaises(DatabaseError,
+                              self._database.store.execute,
+                              'select {0} from {1};'.format(column, table))
+            self._database.store.rollback()
+        for column in present:
+            # This should not produce an exception.  Is there some better test
+            # that we can perform?
+            self._database.store.execute(
+                'select {0} from {1};'.format(column, table))
+
 
 
 class TestMigration20120407Schema(MigrationTestBase):
@@ -81,70 +90,52 @@ class TestMigration20120407Schema(MigrationTestBase):
     def test_pre_upgrade_columns_migration(self):
         # Test that before the migration, the old table columns are present
         # and the new database columns are not.
-        #
-        # Load all the migrations to just before the one we're testing.
-        self._database.load_migrations('20120406999999')
-        self._database.store.commit()
-        # 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;')
-        self._database.store.rollback()
-        for present in ('archive',
-                        'archive_private',
-                        'archive_volume_frequency',
-                        'generic_nonmember_action',
-                        'include_list_post_header',
-                        'news_moderation',
-                        'news_prefix_subject_too',
-                        'nntp_host'):
-            # 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))
-        # Again, this should not produce an exception.
-        self._database.store.execute('select mailing_list from member;')
+        self._missing_present('mailinglist',
+                              ['20120406999999'],
+                              # New columns are missing.
+                              ('allow_list_posts',
+                               'archive_policy',
+                               'list_id',
+                               'nntp_prefix_subject_too'),
+                              # Old columns are present.
+                              ('archive',
+                              'archive_private',
+                              'archive_volume_frequency',
+                              'generic_nonmember_action',
+                              'include_list_post_header',
+                              'news_moderation',
+                              'news_prefix_subject_too',
+                              'nntp_host'))
+        self._missing_present('member',
+                              ['20120406999999'],
+                              ('list_id',),
+                              ('mailing_list',))
 
     def test_post_upgrade_columns_migration(self):
         # Test that after the migration, the old table columns are missing
         # and the new database columns are present.
-        #
-        # Load all the migrations up to and including the one we're testing.
-        self._database.load_migrations('20120406999999')
-        self._database.load_migrations('20120407000000')
-        # 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',
-                        'generic_nonmember_action',
-                        'include_list_post_header',
-                        'news_moderation',
-                        'news_prefix_subject_too',
-                        'nntp_host'):
-            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 mailing_list from member;')
+        self._missing_present('mailinglist',
+                              ['20120406999999',
+                               '20120407000000'],
+                              # The old columns are missing.
+                              ('archive',
+                               'archive_private',
+                               'archive_volume_frequency',
+                               'generic_nonmember_action',
+                               'include_list_post_header',
+                               'news_moderation',
+                               'news_prefix_subject_too',
+                               'nntp_host'),
+                              # The new columns are present.
+                              ('allow_list_posts',
+                              'archive_policy',
+                              'list_id',
+                              'nntp_prefix_subject_too'))
+        self._missing_present('member',
+                              ['20120406999999',
+                               '20120407000000'],
+                              ('mailing_list',),
+                              ('list_id',))
 
 
 
@@ -367,3 +358,48 @@ class TestMigration20120407MigratedData(MigrationTestBase):
         with temporary_db(self._database):
             mlist = getUtility(IListManager).get('test@example.com')
             self.assertTrue(mlist.allow_list_posts)
+
+
+
+class TestMigration20121015Schema(MigrationTestBase):
+    """Test column migrations."""
+
+    def test_pre_upgrade_column_migrations(self):
+        self._missing_present('ban',
+                              ['20121014999999'],
+                              ('list_id',),
+                              ('mailing_list',))
+
+    def test_post_upgrade_column_migrations(self):
+        self._missing_present('ban',
+                              ['20121014999999',
+                               '20121015000000'],
+                              ('mailing_list',),
+                              ('list_id',))
+
+
+class TestMigration20121015MigratedData(MigrationTestBase):
+    """Test non-migrated data."""
+
+    def test_migration_bans(self):
+        # Load all the migrations to just before the one we're testing.
+        self._database.load_migrations('20121014999999')
+        # Insert a list-specific ban.
+        self._database.store.execute("""
+            INSERT INTO ban VALUES (
+                1, 'anne@example.com', 'test@example.com');
+            """)
+        # Insert a global ban.
+        self._database.store.execute("""
+            INSERT INTO ban VALUES (
+                2, 'bart@example.com', NULL);
+            """)
+        # Update to the current migration we're testing.
+        self._database.load_migrations('20121015000000')
+        # Now both the local and global bans should still be present.
+        bans = sorted(self._database.store.find(Ban),
+                      key=attrgetter('email'))
+        self.assertEqual(bans[0].email, 'anne@example.com')
+        self.assertEqual(bans[0].list_id, 'test.example.com')
+        self.assertEqual(bans[1].email, 'bart@example.com')
+        self.assertEqual(bans[1].list_id, None)