Database

--------
 * The ORM layer, previously implemented with Storm, has been replaced by
   SQLAlchemy, thanks to the fantastic work by Abhilash Raj and Aurélien
   Bompard.  Alembic is now used for all database schema migrations.
 * The new logger `mailman.database` logs any errors at the database layer.

API
---
 * Several changes to the internal API:
   - `IListManager.mailing_lists` is guaranteed to be sorted in List-ID order.
   - `IDomains.mailing_lists` is guaranteed to be sorted in List-ID order.
   - Iteration over domains via the `IDomainManager` is guaranteed to be sorted
     by `IDomain.mail_host` order.
   - `ITemporaryDatabase` interface and all implementations are removed.

6 files changed, 160 insertions, 772 deletions

diff --git a/src/mailman/database/tests/data/__init__.py b/src/mailman/database/tests/data/__init__.py
deleted file mode 100644
index e69de29bb..000000000
--- a/src/mailman/database/tests/data/__init__.py
+++ /dev/null
diff --git a/src/mailman/database/tests/data/mailman_01.db b/src/mailman/database/tests/data/mailman_01.db
deleted file mode 100644
index 1ff8d8343..000000000
--- a/src/mailman/database/tests/data/mailman_01.db
+++ /dev/null
diff --git a/src/mailman/database/tests/data/migration_postgres_1.sql b/src/mailman/database/tests/data/migration_postgres_1.sql
deleted file mode 100644
index b82ecf6e4..000000000
--- a/src/mailman/database/tests/data/migration_postgres_1.sql
+++ /dev/null
@@ -1,133 +0,0 @@
-INSERT INTO "acceptablealias" VALUES(1,'foo@example.com',1);
-INSERT INTO "acceptablealias" VALUES(2,'bar@example.com',1);
-
-INSERT INTO "address" VALUES(
-    1,'anne@example.com',NULL,'Anne Person',
-    '2012-04-19 00:52:24.826432','2012-04-19 00:49:42.373769',1,2);
-INSERT INTO "address" VALUES(
-    2,'bart@example.com',NULL,'Bart Person',
-    '2012-04-19 00:53:25.878800','2012-04-19 00:49:52.882050',2,4);
-
-INSERT INTO "domain" VALUES(
-    1,'example.com','http://example.com',NULL,'postmaster@example.com');
-
-INSERT INTO "mailinglist" VALUES(
-    -- id,list_name,mail_host,include_list_post_header,include_rfc2369_headers
-    1,'test','example.com',True,True,
-    -- created_at,admin_member_chunksize,next_request_id,next_digest_number
-    '2012-04-19 00:46:13.173844',30,1,1,
-    -- digest_last_sent_at,volume,last_post_at,accept_these_nonmembers
-    NULL,1,NULL,E'\\x80025D71012E',
-    -- acceptable_aliases_id,admin_immed_notify,admin_notify_mchanges
-    NULL,True,False,
-    -- administrivia,advertised,anonymous_list,archive,archive_private
-    True,True,False,True,False,
-    -- archive_volume_frequency
-    1,
-    --autorespond_owner,autoresponse_owner_text
-    0,'',
-    -- autorespond_postings,autoresponse_postings_text
-    0,'',
-    -- autorespond_requests,authoresponse_requests_text
-    0,'',
-    -- autoresponse_grace_period
-    '90 days, 0:00:00',
-    -- forward_unrecognized_bounces_to,process_bounces
-    1,True,
-    -- bounce_info_stale_after,bounce_matching_headers
-    '7 days, 0:00:00','
-# Lines that *start* with a ''#'' are comments.
-to: friend@public.com
-message-id: relay.comanche.denmark.eu
-from: list@listme.com
-from: .*@uplinkpro.com
-',
-    -- bounce_notify_owner_on_disable,bounce_notify_owner_on_removal
-    True,True,
-    -- bounce_score_threshold,bounce_you_are_disabled_warnings
-    5,3,
-    -- bounce_you_are_disabled_warnings_interval
-    '7 days, 0:00:00',
-    -- filter_action,filter_content,collapse_alternatives
-    2,False,True,
-    -- convert_html_to_plaintext,default_member_action,default_nonmember_action
-    False,4,0,
-    -- description
-    '',
-    -- digest_footer_uri
-    'mailman:///$listname/$language/footer-generic.txt',
-    -- digest_header_uri
-    NULL,
-    -- digest_is_default,digest_send_periodic,digest_size_threshold
-    False,True,30.0,
-    -- digest_volume_frequency,digestable,discard_these_nonmembers
-    1,True,E'\\x80025D71012E',
-    -- emergency,encode_ascii_prefixes,first_strip_reply_to
-    False,False,False,
-    -- footer_uri
-    'mailman:///$listname/$language/footer-generic.txt',
-    -- forward_auto_discards,gateway_to_mail,gateway_to_news
-    True,False,FAlse,
-    -- generic_nonmember_action,goodby_message_uri
-    1,'',
-    -- header_matches,header_uri,hold_these_nonmembers,info,linked_newsgroup
-    E'\\x80025D71012E',NULL,E'\\x80025D71012E','','',
-    -- max_days_to_hold,max_message_size,max_num_recipients
-    0,40,10,
-    -- member_moderation_notice,mime_is_default_digest,moderator_password
-    '',False,NULL,
-    -- new_member_options,news_moderation,news_prefix_subject_too
-    256,0,True,
-    -- nntp_host,nondigestable,nonmember_rejection_notice,obscure_addresses
-    '',True,'',True,
-    -- owner_chain,owner_pipeline,personalize,post_id
-    'default-owner-chain','default-owner-pipeline',0,1,
-    -- posting_chain,posting_pipeline,preferred_language,private_roster
-    'default-posting-chain','default-posting-pipeline','en',True,
-    -- display_name,reject_these_nonmembers
-    'Test',E'\\x80025D71012E',
-    -- reply_goes_to_list,reply_to_address
-    0,'',
-    -- require_explicit_destination,respond_to_post_requests
-    True,True,
-    -- scrub_nondigest,send_goodbye_message,send_reminders,send_welcome_message
-    False,True,True,True,
-    -- subject_prefix,subscribe_auto_approval
-    '[Test] ',E'\\x80025D71012E',
-    -- subscribe_policy,topics,topics_bodylines_limit,topics_enabled
-    1,E'\\x80025D71012E',5,False,
-    -- unsubscribe_policy,welcome_message_uri
-    0,'mailman:///welcome.txt');
-
-INSERT INTO "member" VALUES(
-    1,'d1243f4d-e604-4f6b-af52-98d0a7bce0f1',1,'test@example.com',4,NULL,5,1);
-INSERT INTO "member" VALUES(
-    2,'dccc3851-fdfb-4afa-90cf-bdcbf80ad0fd',2,'test@example.com',3,NULL,6,1);
-INSERT INTO "member" VALUES(
-    3,'479be431-45f2-473d-bc3c-7eac614030ac',3,'test@example.com',3,NULL,7,2);
-INSERT INTO "member" VALUES(
-    4,'e2dc604c-d93a-4b91-b5a8-749e3caade36',1,'test@example.com',4,NULL,8,2);
-
-INSERT INTO "preferences" VALUES(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-
-INSERT INTO "user" VALUES(
-    1,'Anne Person',NULL,'0adf3caa-6f26-46f8-a11d-5256c8148592',
-    '2012-04-19 00:49:42.370493',1,1);
-INSERT INTO "user" VALUES(
-    2,'Bart Person',NULL,'63f5d1a2-e533-4055-afe4-475dec3b1163',
-    '2012-04-19 00:49:52.868746',2,3);
-
-INSERT INTO "uid" VALUES(1,'8bf9a615-f23e-4980-b7d1-90ac0203c66f');
-INSERT INTO "uid" VALUES(2,'0adf3caa-6f26-46f8-a11d-5256c8148592');
-INSERT INTO "uid" VALUES(3,'63f5d1a2-e533-4055-afe4-475dec3b1163');
-INSERT INTO "uid" VALUES(4,'d1243f4d-e604-4f6b-af52-98d0a7bce0f1');
-INSERT INTO "uid" VALUES(5,'dccc3851-fdfb-4afa-90cf-bdcbf80ad0fd');
-INSERT INTO "uid" VALUES(6,'479be431-45f2-473d-bc3c-7eac614030ac');
-INSERT INTO "uid" VALUES(7,'e2dc604c-d93a-4b91-b5a8-749e3caade36');
diff --git a/src/mailman/database/tests/data/migration_sqlite_1.sql b/src/mailman/database/tests/data/migration_sqlite_1.sql
deleted file mode 100644
index a5ac96dfa..000000000
--- a/src/mailman/database/tests/data/migration_sqlite_1.sql
+++ /dev/null
@@ -1,133 +0,0 @@
-INSERT INTO "acceptablealias" VALUES(1,'foo@example.com',1);
-INSERT INTO "acceptablealias" VALUES(2,'bar@example.com',1);
-
-INSERT INTO "address" VALUES(
-    1,'anne@example.com',NULL,'Anne Person',
-    '2012-04-19 00:52:24.826432','2012-04-19 00:49:42.373769',1,2);
-INSERT INTO "address" VALUES(
-    2,'bart@example.com',NULL,'Bart Person',
-    '2012-04-19 00:53:25.878800','2012-04-19 00:49:52.882050',2,4);
-
-INSERT INTO "domain" VALUES(
-    1,'example.com','http://example.com',NULL,'postmaster@example.com');
-
-INSERT INTO "mailinglist" VALUES(
-    -- id,list_name,mail_host,include_list_post_header,include_rfc2369_headers
-    1,'test','example.com',1,1,
-    -- created_at,admin_member_chunksize,next_request_id,next_digest_number
-    '2012-04-19 00:46:13.173844',30,1,1,
-    -- digest_last_sent_at,volume,last_post_at,accept_these_nonmembers
-    NULL,1,NULL,X'80025D71012E',
-    -- acceptable_aliases_id,admin_immed_notify,admin_notify_mchanges
-    NULL,1,0,
-    -- administrivia,advertised,anonymous_list,archive,archive_private
-    1,1,0,1,0,
-    -- archive_volume_frequency
-    1,
-    --autorespond_owner,autoresponse_owner_text
-    0,'',
-    -- autorespond_postings,autoresponse_postings_text
-    0,'',
-    -- autorespond_requests,authoresponse_requests_text
-    0,'',
-    -- autoresponse_grace_period
-    '90 days, 0:00:00',
-    -- forward_unrecognized_bounces_to,process_bounces
-    1,1,
-    -- bounce_info_stale_after,bounce_matching_headers
-    '7 days, 0:00:00','
-# Lines that *start* with a ''#'' are comments.
-to: friend@public.com
-message-id: relay.comanche.denmark.eu
-from: list@listme.com
-from: .*@uplinkpro.com
-',
-    -- bounce_notify_owner_on_disable,bounce_notify_owner_on_removal
-    1,1,
-    -- bounce_score_threshold,bounce_you_are_disabled_warnings
-    5,3,
-    -- bounce_you_are_disabled_warnings_interval
-    '7 days, 0:00:00',
-    -- filter_action,filter_content,collapse_alternatives
-    2,0,1,
-    -- convert_html_to_plaintext,default_member_action,default_nonmember_action
-    0,4,0,
-    -- description
-    '',
-    -- digest_footer_uri
-    'mailman:///$listname/$language/footer-generic.txt',
-    -- digest_header_uri
-    NULL,
-    -- digest_is_default,digest_send_periodic,digest_size_threshold
-    0,1,30.0,
-    -- digest_volume_frequency,digestable,discard_these_nonmembers
-    1,1,X'80025D71012E',
-    -- emergency,encode_ascii_prefixes,first_strip_reply_to
-    0,0,0,
-    -- footer_uri
-    'mailman:///$listname/$language/footer-generic.txt',
-    -- forward_auto_discards,gateway_to_mail,gateway_to_news
-    1,0,0,
-    -- generic_nonmember_action,goodby_message_uri
-    1,'',
-    -- header_matches,header_uri,hold_these_nonmembers,info,linked_newsgroup
-    X'80025D71012E',NULL,X'80025D71012E','','',
-    -- max_days_to_hold,max_message_size,max_num_recipients
-    0,40,10,
-    -- member_moderation_notice,mime_is_default_digest,moderator_password
-    '',0,NULL,
-    -- new_member_options,news_moderation,news_prefix_subject_too
-    256,0,1,
-    -- nntp_host,nondigestable,nonmember_rejection_notice,obscure_addresses
-    '',1,'',1,
-    -- owner_chain,owner_pipeline,personalize,post_id
-    'default-owner-chain','default-owner-pipeline',0,1,
-    -- posting_chain,posting_pipeline,preferred_language,private_roster
-    'default-posting-chain','default-posting-pipeline','en',1,
-    -- display_name,reject_these_nonmembers
-    'Test',X'80025D71012E',
-    -- reply_goes_to_list,reply_to_address
-    0,'',
-    -- require_explicit_destination,respond_to_post_requests
-    1,1,
-    -- scrub_nondigest,send_goodbye_message,send_reminders,send_welcome_message
-    0,1,1,1,
-    -- subject_prefix,subscribe_auto_approval
-    '[Test] ',X'80025D71012E',
-    -- subscribe_policy,topics,topics_bodylines_limit,topics_enabled
-    1,X'80025D71012E',5,0,
-    -- unsubscribe_policy,welcome_message_uri
-    0,'mailman:///welcome.txt');
-
-INSERT INTO "member" VALUES(
-    1,'d1243f4d-e604-4f6b-af52-98d0a7bce0f1',1,'test@example.com',4,NULL,5,1);
-INSERT INTO "member" VALUES(
-    2,'dccc3851-fdfb-4afa-90cf-bdcbf80ad0fd',2,'test@example.com',3,NULL,6,1);
-INSERT INTO "member" VALUES(
-    3,'479be431-45f2-473d-bc3c-7eac614030ac',3,'test@example.com',3,NULL,7,2);
-INSERT INTO "member" VALUES(
-    4,'e2dc604c-d93a-4b91-b5a8-749e3caade36',1,'test@example.com',4,NULL,8,2);
-
-INSERT INTO "preferences" VALUES(1,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(3,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(4,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(5,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(6,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(7,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-INSERT INTO "preferences" VALUES(8,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
-
-INSERT INTO "user" VALUES(
-    1,'Anne Person',NULL,'0adf3caa-6f26-46f8-a11d-5256c8148592',
-    '2012-04-19 00:49:42.370493',1,1);
-INSERT INTO "user" VALUES(
-    2,'Bart Person',NULL,'63f5d1a2-e533-4055-afe4-475dec3b1163',
-    '2012-04-19 00:49:52.868746',2,3);
-
-INSERT INTO "uid" VALUES(1,'8bf9a615-f23e-4980-b7d1-90ac0203c66f');
-INSERT INTO "uid" VALUES(2,'0adf3caa-6f26-46f8-a11d-5256c8148592');
-INSERT INTO "uid" VALUES(3,'63f5d1a2-e533-4055-afe4-475dec3b1163');
-INSERT INTO "uid" VALUES(4,'d1243f4d-e604-4f6b-af52-98d0a7bce0f1');
-INSERT INTO "uid" VALUES(5,'dccc3851-fdfb-4afa-90cf-bdcbf80ad0fd');
-INSERT INTO "uid" VALUES(6,'479be431-45f2-473d-bc3c-7eac614030ac');
-INSERT INTO "uid" VALUES(7,'e2dc604c-d93a-4b91-b5a8-749e3caade36');
diff --git a/src/mailman/database/tests/test_factory.py b/src/mailman/database/tests/test_factory.py
new file mode 100644
index 000000000..d7c4d8503
--- /dev/null
+++ b/src/mailman/database/tests/test_factory.py
@@ -0,0 +1,160 @@
+# Copyright (C) 2013-2014 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/>.
+
+"""Test database schema migrations"""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'TestSchemaManager',
+    ]
+
+
+import unittest
+import alembic.command
+
+from mock import patch
+from sqlalchemy import MetaData, Table, Column, Integer, Unicode
+from sqlalchemy.exc import ProgrammingError, OperationalError
+from sqlalchemy.schema import Index
+
+from mailman.config import config
+from mailman.database.alembic import alembic_cfg
+from mailman.database.factory import LAST_STORM_SCHEMA_VERSION, SchemaManager
+from mailman.database.model import Model
+from mailman.interfaces.database import DatabaseError
+from mailman.testing.layers import ConfigLayer
+
+
+
+class TestSchemaManager(unittest.TestCase):
+
+    layer = ConfigLayer
+
+    def setUp(self):
+        # Drop the existing database.
+        Model.metadata.drop_all(config.db.engine)
+        md = MetaData()
+        md.reflect(bind=config.db.engine)
+        for tablename in ('alembic_version', 'version'):
+            if tablename in md.tables:
+                md.tables[tablename].drop(config.db.engine)
+        self.schema_mgr = SchemaManager(config.db)
+
+    def tearDown(self):
+        self._drop_storm_database()
+        # Restore a virgin database.
+        Model.metadata.create_all(config.db.engine)
+
+    def _table_exists(self, tablename):
+        md = MetaData()
+        md.reflect(bind=config.db.engine)
+        return tablename in md.tables
+
+    def _create_storm_database(self, revision):
+        version_table = Table(
+            'version', Model.metadata,
+            Column('id', Integer, primary_key=True),
+            Column('component', Unicode),
+            Column('version', Unicode),
+            )
+        version_table.create(config.db.engine)
+        config.db.store.execute(version_table.insert().values(
+            component='schema', version=revision))
+        config.db.commit()
+        # Other Storm specific changes, those SQL statements hopefully work on
+        # all DB engines...
+        config.db.engine.execute(
+            'ALTER TABLE mailinglist ADD COLUMN acceptable_aliases_id INT')
+        Index('ix_user__user_id').drop(bind=config.db.engine)
+        # Don't pollute our main metadata object, create a new one.
+        md = MetaData()
+        user_table = Model.metadata.tables['user'].tometadata(md)
+        Index('ix_user_user_id', user_table.c._user_id).create(
+            bind=config.db.engine)
+        config.db.commit()
+
+    def _drop_storm_database(self):
+        """Remove the leftovers from a Storm DB.
+
+        A drop_all() must be issued afterwards.
+        """
+        if 'version' in Model.metadata.tables:
+            version = Model.metadata.tables['version']
+            version.drop(config.db.engine, checkfirst=True)
+            Model.metadata.remove(version)
+        try:
+            Index('ix_user_user_id').drop(bind=config.db.engine)
+        except (ProgrammingError, OperationalError):
+            # Nonexistent.  PostgreSQL raises a ProgrammingError, while SQLite
+            # raises an OperationalError.
+            pass
+        config.db.commit()
+
+    def test_current_database(self):
+        # The database is already at the latest version.
+        alembic.command.stamp(alembic_cfg, 'head')
+        with patch('alembic.command') as alembic_command:
+            self.schema_mgr.setup_database()
+            self.assertFalse(alembic_command.stamp.called)
+            self.assertFalse(alembic_command.upgrade.called)
+
+    @patch('alembic.command')
+    def test_initial(self, alembic_command):
+        # No existing database.
+        self.assertFalse(self._table_exists('mailinglist'))
+        self.assertFalse(self._table_exists('alembic_version'))
+        self.schema_mgr.setup_database()
+        self.assertFalse(alembic_command.upgrade.called)
+        self.assertTrue(self._table_exists('mailinglist'))
+        self.assertTrue(self._table_exists('alembic_version'))
+
+    @patch('alembic.command.stamp')
+    def test_storm(self, alembic_command_stamp):
+        # Existing Storm database.
+        Model.metadata.create_all(config.db.engine)
+        self._create_storm_database(LAST_STORM_SCHEMA_VERSION)
+        self.schema_mgr.setup_database()
+        self.assertFalse(alembic_command_stamp.called)
+        self.assertTrue(
+            self._table_exists('mailinglist')
+            and self._table_exists('alembic_version')
+            and not self._table_exists('version'))
+
+    @patch('alembic.command')
+    def test_old_storm(self, alembic_command):
+        # Existing Storm database in an old version.
+        Model.metadata.create_all(config.db.engine)
+        self._create_storm_database('001')
+        self.assertRaises(DatabaseError, self.schema_mgr.setup_database)
+        self.assertFalse(alembic_command.stamp.called)
+        self.assertFalse(alembic_command.upgrade.called)
+
+    def test_old_db(self):
+        # The database is in an old revision, must upgrade.
+        alembic.command.stamp(alembic_cfg, 'head')
+        md = MetaData()
+        md.reflect(bind=config.db.engine)
+        config.db.store.execute(md.tables['alembic_version'].delete())
+        config.db.store.execute(md.tables['alembic_version'].insert().values(
+            version_num='dummyrevision'))
+        config.db.commit()
+        with patch('alembic.command') as alembic_command:
+            self.schema_mgr.setup_database()
+            self.assertFalse(alembic_command.stamp.called)
+            self.assertTrue(alembic_command.upgrade.called)
diff --git a/src/mailman/database/tests/test_migrations.py b/src/mailman/database/tests/test_migrations.py
deleted file mode 100644
index 9619b80a4..000000000
--- a/src/mailman/database/tests/test_migrations.py
+++ /dev/null
@@ -1,506 +0,0 @@
-# Copyright (C) 2012-2014 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/>.
-
-"""Test schema migrations."""
-
-from __future__ import absolute_import, print_function, unicode_literals
-
-__metaclass__ = type
-__all__ = [
-    'TestMigration20120407MigratedData',
-    'TestMigration20120407Schema',
-    'TestMigration20120407UnchangedData',
-    'TestMigration20121015MigratedData',
-    'TestMigration20121015Schema',
-    'TestMigration20130406MigratedData',
-    'TestMigration20130406Schema',
-    ]
-
-
-import unittest
-
-from datetime import datetime
-from operator import attrgetter
-from pkg_resources import resource_string
-from sqlite3 import OperationalError
-from storm.exceptions import DatabaseError
-from zope.component import getUtility
-
-from mailman.interfaces.database import IDatabaseFactory
-from mailman.interfaces.domain import IDomainManager
-from mailman.interfaces.archiver import ArchivePolicy
-from mailman.interfaces.bounce import BounceContext
-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.model.bounce import BounceEvent
-from mailman.testing.helpers import temporary_db
-from mailman.testing.layers import ConfigLayer
-
-
-
-class MigrationTestBase(unittest.TestCase):
-    """Test database migrations."""
-
-    layer = ConfigLayer
-
-    def setUp(self):
-        self._database = getUtility(IDatabaseFactory, 'temporary').create()
-
-    def tearDown(self):
-        self._database._cleanup()
-
-    def _table_missing_present(self, migrations, missing, present):
-        """The appropriate migrations leave some tables missing and present.
-
-        :param migrations: Sequence of migrations to load.
-        :param missing: Tables which should be missing.
-        :param present: Tables which should be present.
-        """
-        for migration in migrations:
-            self._database.load_migrations(migration)
-            self._database.store.commit()
-        for table in missing:
-            self.assertRaises(OperationalError,
-                              self._database.store.execute,
-                              'select * from {};'.format(table))
-        for table in present:
-            self._database.store.execute('select * from {};'.format(table))
-
-    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):
-    """Test column migrations."""
-
-    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.
-        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.
-        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',))
-
-
-
-class TestMigration20120407UnchangedData(MigrationTestBase):
-    """Test non-migrated data."""
-
-    def setUp(self):
-        MigrationTestBase.setUp(self)
-        # Load all the migrations to just before the one we're testing.
-        self._database.load_migrations('20120406999999')
-        # Load the previous schema's sample data.
-        sample_data = resource_string(
-            'mailman.database.tests.data',
-            'migration_{0}_1.sql'.format(self._database.TAG))
-        self._database.load_sql(self._database.store, sample_data)
-        # XXX 2012-12-28: We have to load the last migration defined in the
-        # system, otherwise the ORM model will not match the SQL table
-        # definitions and we'll get OperationalErrors from SQLite.
-        self._database.load_migrations('20121015000000')
-
-    def test_migration_domains(self):
-        # Test that the domains table, which isn't touched, doesn't change.
-        with temporary_db(self._database):
-            # Check that the domains survived the migration.  This table
-            # was not touched so it should be fine.
-            domains = list(getUtility(IDomainManager))
-            self.assertEqual(len(domains), 1)
-            self.assertEqual(domains[0].mail_host, 'example.com')
-
-    def test_migration_mailing_lists(self):
-        # Test that the mailing lists survive migration.
-        with temporary_db(self._database):
-            # There should be exactly one mailing list defined.
-            mlists = list(getUtility(IListManager).mailing_lists)
-            self.assertEqual(len(mlists), 1)
-            self.assertEqual(mlists[0].fqdn_listname, 'test@example.com')
-
-    def test_migration_acceptable_aliases(self):
-        # Test that the mailing list's acceptable aliases survive migration.
-        # This proves that foreign key references are migrated properly.
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            aliases_set = IAcceptableAliasSet(mlist)
-            self.assertEqual(set(aliases_set.aliases),
-                             set(['foo@example.com', 'bar@example.com']))
-
-    def test_migration_members(self):
-        # Test that the members of a mailing list all survive migration.
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            # Test that all the members we expect are still there.  Start with
-            # the two list delivery members.
-            addresses = set(address.email
-                            for address in mlist.members.addresses)
-            self.assertEqual(addresses,
-                             set(['anne@example.com', 'bart@example.com']))
-            # There is one owner.
-            owners = set(address.email for address in mlist.owners.addresses)
-            self.assertEqual(len(owners), 1)
-            self.assertEqual(owners.pop(), 'anne@example.com')
-            # There is one moderator.
-            moderators = set(address.email
-                             for address in mlist.moderators.addresses)
-            self.assertEqual(len(moderators), 1)
-            self.assertEqual(moderators.pop(), 'bart@example.com')
-
-
-
-class TestMigration20120407MigratedData(MigrationTestBase):
-    """Test affected migration data."""
-
-    def setUp(self):
-        MigrationTestBase.setUp(self)
-        # Load all the migrations to just before the one we're testing.
-        self._database.load_migrations('20120406999999')
-        # Load the previous schema's sample data.
-        sample_data = resource_string(
-            'mailman.database.tests.data',
-            'migration_{0}_1.sql'.format(self._database.TAG))
-        self._database.load_sql(self._database.store, sample_data)
-
-    def _upgrade(self):
-        # XXX 2012-12-28: We have to load the last migration defined in the
-        # system, otherwise the ORM model will not match the SQL table
-        # definitions and we'll get OperationalErrors from SQLite.
-        self._database.load_migrations('20121015000000')
-
-    def test_migration_archive_policy_never_0(self):
-        # Test that the new archive_policy value is updated correctly.  In the
-        # case of old column archive=0, the archive_private column is
-        # ignored.  This test sets it to 0 to ensure it's ignored.
-        self._database.store.execute(
-            'UPDATE mailinglist SET archive = {0}, archive_private = {0} '
-            'WHERE id = 1;'.format(self._database.FALSE))
-        # Complete the migration
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.archive_policy, ArchivePolicy.never)
-
-    def test_migration_archive_policy_never_1(self):
-        # Test that the new archive_policy value is updated correctly.  In the
-        # case of old column archive=0, the archive_private column is
-        # ignored.  This test sets it to 1 to ensure it's ignored.
-        self._database.store.execute(
-            'UPDATE mailinglist SET archive = {0}, archive_private = {1} '
-            'WHERE id = 1;'.format(self._database.FALSE,
-                                   self._database.TRUE))
-        # Complete the migration
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.archive_policy, ArchivePolicy.never)
-
-    def test_archive_policy_private(self):
-        # Test that the new archive_policy value is updated correctly for
-        # private archives.
-        self._database.store.execute(
-            'UPDATE mailinglist SET archive = {0}, archive_private = {0} '
-            'WHERE id = 1;'.format(self._database.TRUE))
-        # Complete the migration
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.archive_policy, ArchivePolicy.private)
-
-    def test_archive_policy_public(self):
-        # Test that the new archive_policy value is updated correctly for
-        # public archives.
-        self._database.store.execute(
-            'UPDATE mailinglist SET archive = {1}, archive_private = {0} '
-            'WHERE id = 1;'.format(self._database.FALSE,
-                                   self._database.TRUE))
-        # Complete the migration
-        self._upgrade()
-        with temporary_db(self._database):
-            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(
-            'UPDATE mailinglist SET news_moderation = 0 '
-            'WHERE id = 1;')
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.newsgroup_moderation,
-                             NewsgroupModeration.none)
-
-    def test_news_moderation_open_moderated(self):
-        # Test that news_moderation becomes newsgroup_moderation.
-        self._database.store.execute(
-            'UPDATE mailinglist SET news_moderation = 1 '
-            'WHERE id = 1;')
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.newsgroup_moderation,
-                             NewsgroupModeration.open_moderated)
-
-    def test_news_moderation_moderated(self):
-        # Test that news_moderation becomes newsgroup_moderation.
-        self._database.store.execute(
-            'UPDATE mailinglist SET news_moderation = 2 '
-            'WHERE id = 1;')
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertEqual(mlist.newsgroup_moderation,
-                             NewsgroupModeration.moderated)
-
-    def test_nntp_prefix_subject_too_false(self):
-        # Test that news_prefix_subject_too becomes nntp_prefix_subject_too.
-        self._database.store.execute(
-            'UPDATE mailinglist SET news_prefix_subject_too = {0} '
-            'WHERE id = 1;'.format(self._database.FALSE))
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertFalse(mlist.nntp_prefix_subject_too)
-
-    def test_nntp_prefix_subject_too_true(self):
-        # Test that news_prefix_subject_too becomes nntp_prefix_subject_too.
-        self._database.store.execute(
-            'UPDATE mailinglist SET news_prefix_subject_too = {0} '
-            'WHERE id = 1;'.format(self._database.TRUE))
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertTrue(mlist.nntp_prefix_subject_too)
-
-    def test_allow_list_posts_false(self):
-        # Test that include_list_post_header -> allow_list_posts.
-        self._database.store.execute(
-            'UPDATE mailinglist SET include_list_post_header = {0} '
-            'WHERE id = 1;'.format(self._database.FALSE))
-        self._upgrade()
-        with temporary_db(self._database):
-            mlist = getUtility(IListManager).get('test@example.com')
-            self.assertFalse(mlist.allow_list_posts)
-
-    def test_allow_list_posts_true(self):
-        # Test that include_list_post_header -> allow_list_posts.
-        self._database.store.execute(
-            'UPDATE mailinglist SET include_list_post_header = {0} '
-            'WHERE id = 1;'.format(self._database.TRUE))
-        self._upgrade()
-        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',))
-        self._missing_present('mailinglist',
-                              ['20121014999999'],
-                              (),
-                              ('new_member_options', 'send_reminders',
-                               'subscribe_policy', 'unsubscribe_policy',
-                               'subscribe_auto_approval', 'private_roster',
-                               'admin_member_chunksize'),
-                              )
-
-    def test_post_upgrade_column_migrations(self):
-        self._missing_present('ban',
-                              ['20121014999999',
-                               '20121015000000'],
-                              ('mailing_list',),
-                              ('list_id',))
-        self._missing_present('mailinglist',
-                              ['20121014999999',
-                               '20121015000000'],
-                              ('new_member_options', 'send_reminders',
-                               'subscribe_policy', 'unsubscribe_policy',
-                               'subscribe_auto_approval', 'private_roster',
-                               'admin_member_chunksize'),
-                              ())
-
-
-
-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)
-
-
-
-class TestMigration20130406Schema(MigrationTestBase):
-    """Test column migrations."""
-
-    def test_pre_upgrade_column_migrations(self):
-        self._missing_present('bounceevent',
-                              ['20130405999999'],
-                              ('list_id',),
-                              ('list_name',))
-
-    def test_post_upgrade_column_migrations(self):
-        self._missing_present('bounceevent',
-                              ['20130405999999',
-                               '20130406000000'],
-                              ('list_name',),
-                              ('list_id',))
-
-    def test_pre_listarchiver_table(self):
-        self._table_missing_present(['20130405999999'], ('listarchiver',), ())
-
-    def test_post_listarchiver_table(self):
-        self._table_missing_present(['20130405999999',
-                                     '20130406000000'],
-                                     (),
-                                     ('listarchiver',))
-
-
-
-class TestMigration20130406MigratedData(MigrationTestBase):
-    """Test migrated data."""
-
-    def test_migration_bounceevent(self):
-        # Load all migrations to just before the one we're testing.
-        self._database.load_migrations('20130405999999')
-        # Insert a bounce event.
-        self._database.store.execute("""
-            INSERT INTO bounceevent VALUES (
-                1, 'test@example.com', 'anne@example.com',
-                '2013-04-06 21:12:00', '<abc@example.com>',
-                1, 0);
-            """)
-        # Update to the current migration we're testing
-        self._database.load_migrations('20130406000000')
-        # The bounce event should exist, but with a list-id instead of a fqdn
-        # list name.
-        events = list(self._database.store.find(BounceEvent))
-        self.assertEqual(len(events), 1)
-        self.assertEqual(events[0].list_id, 'test.example.com')
-        self.assertEqual(events[0].email, 'anne@example.com')
-        self.assertEqual(events[0].timestamp, datetime(2013, 4, 6, 21, 12))
-        self.assertEqual(events[0].message_id, '<abc@example.com>')
-        self.assertEqual(events[0].context, BounceContext.normal)
-        self.assertFalse(events[0].processed)