* The policy for archiving has now been collapsed into a single enum, called

   ArchivePolicy.  This describes the three states of never archive, archive
   privately, and archive_publicly. (LP: #967238)

Database
--------
 * Schema migrations (LP: #971013)
   - include_list_post_header -> allow_list_posts
   - news_prefix_subject_too  -> nntp_prefix_subject_too
   - news_moderation          -> newsgroup_moderation
   - archive and archive_private have been collapsed into archive_policy.
   - nntp_host has been removed.
 * The PostgreSQL port of the schema accidentally added a moderation_callback
   column to the mailinglist table.  Since this is unused in Mailman, it was
   simply commented out of the base schema for PostgreSQL.

17 files changed, 1268 insertions, 103 deletions

diff --git a/src/mailman/database/base.py b/src/mailman/database/base.py
index 1595007f1..41d9374f9 100644
--- a/src/mailman/database/base.py
+++ b/src/mailman/database/base.py
@@ -142,17 +142,21 @@ class StormBaseDatabase:
         database.DEBUG = (as_boolean(config.database.debug)
                           if debug is None else debug)
         self.store = store
-        self.load_migrations()
         store.commit()
 
-    def load_migrations(self):
-        """Load all not-yet loaded migrations."""
+    def load_migrations(self, until=None):
+        """Load schema migrations.
+
+        :param until: Load only the migrations up to the specified timestamp.
+            With default value of None, load all migrations.
+        :type until: string
+        """
         migrations_path = config.database.migrations_path
         if '.' in migrations_path:
             parent, dot, child = migrations_path.rpartition('.')
         else:
             parent = migrations_path
-            child =''
+            child = ''
         # If the database does not yet exist, load the base schema.
         filenames = sorted(resource_listdir(parent, child))
         # Find out which schema migrations have already been loaded.
@@ -168,17 +172,40 @@ class StormBaseDatabase:
             parts = module_fn.split('_')
             if len(parts) < 2:
                 continue
-            version = parts[1]
+            version = parts[1].strip()
+            if len(version) == 0:
+                # Not a schema migration file.
+                continue
             if version in versions:
-                # This one is already loaded.
+                log.debug('already migrated to %s', version)
                 continue
+            if until is not None and version > until:
+                # We're done.
+                break
             module_path = migrations_path + '.' + module_fn
             __import__(module_path)
             upgrade = getattr(sys.modules[module_path], 'upgrade', None)
             if upgrade is None:
                 continue
+            log.debug('migrating db to %s: %s', version, module_path)
             upgrade(self, self.store, version, module_path)
 
+    def load_sql(self, store, sql):
+        """Load the given SQL into the store.
+
+        :param store: The Storm store to load the schema into.
+        :type store: storm.locals.Store`
+        :param sql: The possibly multi-line SQL to load.
+        :type sql: string
+        """
+        # Discard all blank and comment lines.
+        lines = (line for line in sql.splitlines()
+                 if line.strip() != '' and line.strip()[:2] != '--')
+        sql = NL.join(lines)
+        for statement in sql.split(';'):
+            if statement.strip() != '':
+                store.execute(statement + ';')
+
     def load_schema(self, store, version, filename, module_path):
         """Load the schema from a file.
 
@@ -199,22 +226,10 @@ class StormBaseDatabase:
         """
         if filename is not None:
             contents = resource_string('mailman.database.schema', filename)
-            # Discard all blank and comment lines.
-            lines = (line for line in contents.splitlines()
-                     if line.strip() != '' and line.strip()[:2] != '--')
-            sql = NL.join(lines)
-            for statement in sql.split(';'):
-                if statement.strip() != '':
-                    store.execute(statement + ';')
+            self.load_sql(store, contents)
         # Add a marker that indicates the migration version being applied.
         store.add(Version(component='schema', version=version))
-        # Add a marker so that the module name can be found later.  This is
-        # used by the test suite to reset the database between tests.
-        store.add(Version(component=version, version=module_path))
 
-    def _reset(self):
-        """See `IDatabase`."""
-        from mailman.database.model import ModelMeta
-        self.store.rollback()
-        ModelMeta._reset(self.store)
-        self.store.commit()
+    @staticmethod
+    def _make_temporary():
+        raise NotImplementedError
diff --git a/src/mailman/database/docs/migration.rst b/src/mailman/database/docs/migration.rst
index 9897b1ef2..a4d25d648 100644
--- a/src/mailman/database/docs/migration.rst
+++ b/src/mailman/database/docs/migration.rst
@@ -24,17 +24,18 @@ Migrations are applied automatically when Mailman starts up, but can also be
 applied at any time by calling in the API directly.  Once applied, a
 migration's version string is registered so it will not be applied again.
 
-We see that the base migration is already applied.
+We see that the base migration, as well as subsequent standard migrations, are
+already applied.
 
     >>> from mailman.model.version import Version
     >>> results = config.db.store.find(Version, component='schema')
     >>> results.count()
-    1
-    >>> base = results.one()
-    >>> print base.component
-    schema
-    >>> print base.version
+    2
+    >>> versions = sorted(result.version for result in results)
+    >>> for version in versions:
+    ...     print version
     00000000000000
+    20120407000000
 
 
 Migrations
@@ -55,6 +56,14 @@ specified in the configuration file.
     ... migrations_path: migrations
     ... """)
 
+.. Clean this up at the end of the doctest.
+    >>> def cleanup():
+    ...     import shutil
+    ...     from mailman.config import config
+    ...     config.pop('migrations')
+    ...     shutil.rmtree(tempdir)
+    >>> cleanups.append(cleanup)
+
 Here is an example migrations module.  The key part of this interface is the
 ``upgrade()`` method, which takes four arguments:
 
@@ -69,7 +78,7 @@ This migration module just adds a marker to the `version` table.
 
     >>> with open(os.path.join(path, '__init__.py'), 'w') as fp:
     ...     pass
-    >>> with open(os.path.join(path, 'mm_20120211000000.py'), 'w') as fp:
+    >>> with open(os.path.join(path, 'mm_20129999000000.py'), 'w') as fp:
     ...     print >> fp, """
     ... from __future__ import unicode_literals
     ... from mailman.model.version import Version
@@ -86,14 +95,15 @@ This will load the new migration, since it hasn't been loaded before.
     >>> for result in sorted(result.version for result in results):
     ...     print result
     00000000000000
-    20120211000000
+    20120407000000
+    20129999000000
     >>> test = config.db.store.find(Version, component='test').one()
     >>> print test.version
-    20120211000000
+    20129999000000
 
 Migrations will only be loaded once.
 
-    >>> with open(os.path.join(path, 'mm_20120211000001.py'), 'w') as fp:
+    >>> with open(os.path.join(path, 'mm_20129999000001.py'), 'w') as fp:
     ...     print >> fp, """
     ... from __future__ import unicode_literals
     ... from mailman.model.version import Version
@@ -115,13 +125,14 @@ The first time we load this new migration, we'll get the 801 marker.
     >>> for result in sorted(result.version for result in results):
     ...     print result
     00000000000000
-    20120211000000
-    20120211000001
+    20120407000000
+    20129999000000
+    20129999000001
     >>> test = config.db.store.find(Version, component='test')
     >>> for marker in sorted(marker.version for marker in test):
     ...     print marker
     00000000000801
-    20120211000000
+    20129999000000
 
 We do not get an 802 marker because the migration has already been loaded.
 
@@ -130,17 +141,42 @@ We do not get an 802 marker because the migration has already been loaded.
     >>> for result in sorted(result.version for result in results):
     ...     print result
     00000000000000
-    20120211000000
-    20120211000001
+    20120407000000
+    20129999000000
+    20129999000001
     >>> test = config.db.store.find(Version, component='test')
     >>> for marker in sorted(marker.version for marker in test):
     ...     print marker
     00000000000801
-    20120211000000
+    20129999000000
+
+
+Partial upgrades
+================
+
+It's possible (mostly for testing purposes) to only do a partial upgrade, by
+providing a timestamp to `load_migrations()`.  To demonstrate this, we add two
+additional migrations, intended to be applied in sequential order.
+
+    >>> from shutil import copyfile
+    >>> from mailman.testing.helpers import chdir
+    >>> with chdir(path):
+    ...     copyfile('mm_20129999000000.py', 'mm_20129999000002.py')
+    ...     copyfile('mm_20129999000000.py', 'mm_20129999000003.py')
+    ...     copyfile('mm_20129999000000.py', 'mm_20129999000004.py')
+
+Now, only migrate to the ...03 timestamp.
 
-.. Clean up the temporary directory::
+    >>> config.db.load_migrations('20129999000003')
 
-    >>> config.pop('migrations')
-    >>> sys.path.remove(tempdir)
-    >>> import shutil
-    >>> shutil.rmtree(tempdir)
+You'll notice that the ...04 version is not present.
+
+    >>> results = config.db.store.find(Version, component='schema')
+    >>> for result in sorted(result.version for result in results):
+    ...     print result
+    00000000000000
+    20120407000000
+    20129999000000
+    20129999000001
+    20129999000002
+    20129999000003
diff --git a/src/mailman/database/factory.py b/src/mailman/database/factory.py
new file mode 100644
index 000000000..127c4aaeb
--- /dev/null
+++ b/src/mailman/database/factory.py
@@ -0,0 +1,101 @@
+# 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/>.
+
+"""Database factory."""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'DatabaseFactory',
+    'DatabaseTemporaryFactory',
+    'DatabaseTestingFactory',
+    ]
+
+
+import types
+
+from zope.component import getAdapter
+from zope.interface import implementer
+from zope.interface.verify import verifyObject
+
+from mailman.config import config
+from mailman.interfaces.database import (
+    IDatabase, IDatabaseFactory, ITemporaryDatabase)
+from mailman.utilities.modules import call_name
+
+
+
+@implementer(IDatabaseFactory)
+class DatabaseFactory:
+    """Create a new database."""
+
+    @staticmethod
+    def create():
+        """See `IDatabaseFactory`."""
+        database_class = config.database['class']
+        database = call_name(database_class)
+        verifyObject(IDatabase, database)
+        database.initialize()
+        database.load_migrations()
+        database.commit()
+        return database
+
+
+
+def _reset(self):
+    """See `IDatabase`."""
+    from mailman.database.model import ModelMeta
+    self.store.rollback()
+    self._pre_reset(self.store)
+    ModelMeta._reset(self.store)
+    self._post_reset(self.store)
+    self.store.commit()
+
+
+@implementer(IDatabaseFactory)
+class DatabaseTestingFactory:
+    """Create a new database for testing."""
+
+    @staticmethod
+    def create():
+        """See `IDatabaseFactory`."""
+        database_class = config.database['class']
+        database = call_name(database_class)
+        verifyObject(IDatabase, database)
+        database.initialize()
+        database.load_migrations()
+        database.commit()
+        # Make _reset() a bound method of the database instance.
+        database._reset = types.MethodType(_reset, database)
+        return database
+
+
+
+@implementer(IDatabaseFactory)
+class DatabaseTemporaryFactory:
+    """Create a temporary database for some of the migration tests."""
+
+    @staticmethod
+    def create():
+        """See `IDatabaseFactory`."""
+        database_class_name = config.database['class']
+        database = call_name(database_class_name)
+        verifyObject(IDatabase, database)
+        adapted_database = getAdapter(
+            database, ITemporaryDatabase, database.TAG)
+        return adapted_database
diff --git a/src/mailman/database/model.py b/src/mailman/database/model.py
index c45517c9b..58d5942a4 100644
--- a/src/mailman/database/model.py
+++ b/src/mailman/database/model.py
@@ -17,7 +17,7 @@
 
 """Base class for all database classes."""
 
-from __future__ import absolute_import, unicode_literals
+from __future__ import absolute_import, print_function, unicode_literals
 
 __metaclass__ = type
 __all__ = [
@@ -25,8 +25,6 @@ __all__ = [
     ]
 
 
-import sys
-
 from operator import attrgetter
 
 from storm.properties import PropertyPublisherMeta
@@ -44,46 +42,24 @@ class ModelMeta(PropertyPublisherMeta):
         self.__storm_table__ = name.lower()
         super(ModelMeta, self).__init__(name, bases, dict)
         # Register the model class so that it can be more easily cleared.
-        # This is required by the test framework.
-        if name == 'Model':
-            return
-        ModelMeta._class_registry.add(self)
+        # This is required by the test framework so that the corresponding
+        # table can be reset between tests.
+        #
+        # The PRESERVE flag indicates whether the table should be reset or
+        # not.  We have to handle the actual Model base class explicitly
+        # because it does not correspond to a table in the database.
+        if not getattr(self, 'PRESERVE', False) and name != 'Model':
+            ModelMeta._class_registry.add(self)
 
     @staticmethod
     def _reset(store):
         from mailman.config import config
-        from mailman.model.version import Version
         config.db._pre_reset(store)
-        # Give each schema migration a chance to do its pre-reset.  See below
-        # for calling its post reset too.
-        versions = sorted(version.version for version in
-                          store.find(Version, component='schema'))
-        migrations = {}
-        for version in versions:
-            # We have to give the migrations module that loaded this version a
-            # chance to do both pre- and post-reset operations.  The following
-            # find the actual the module path for the migration.  See
-            # StormBaseDatabase.load_schema().
-            migration = store.find(Version, component=version).one()
-            if migration is None:
-                continue
-            migrations[version] = module_path = migration.version
-            module = sys.modules[module_path]
-            pre_reset = getattr(module, 'pre_reset', None)
-            if pre_reset is not None:
-                pre_reset(store)
         # Make sure this is deterministic, by sorting on the storm table name.
         classes = sorted(ModelMeta._class_registry,
                          key=attrgetter('__storm_table__'))
         for model_class in classes:
             store.find(model_class).remove()
-        # Now give each migration a chance to do post-reset operations.
-        for version in versions:
-            module = sys.modules[migrations[version]]
-            post_reset = getattr(module, 'post_reset', None)
-            if post_reset is not None:
-                post_reset(store)
-        config.db._post_reset(store)
 
 
 
diff --git a/src/mailman/database/postgresql.py b/src/mailman/database/postgresql.py
index 988f7a1af..49188148f 100644
--- a/src/mailman/database/postgresql.py
+++ b/src/mailman/database/postgresql.py
@@ -17,17 +17,23 @@
 
 """PostgreSQL database support."""
 
-from __future__ import absolute_import, unicode_literals
+from __future__ import absolute_import, print_function, unicode_literals
 
 __metaclass__ = type
 __all__ = [
     'PostgreSQLDatabase',
+    'make_temporary',
     ]
 
 
+import types
+
+from functools import partial
 from operator import attrgetter
+from urlparse import urlsplit, urlunsplit
 
 from mailman.database.base import StormBaseDatabase
+from mailman.testing.helpers import configuration
 
 
 
@@ -40,8 +46,8 @@ class PostgreSQLDatabase(StormBaseDatabase):
         """See `BaseDatabase`."""
         table_query = ('SELECT table_name FROM information_schema.tables '
                        "WHERE table_schema = 'public'")
-        table_names = set(item[0] for item in
-                          store.execute(table_query))
+        results = store.execute(table_query)
+        table_names = set(item[0] for item in results)
         return 'version' in table_names
 
     def _post_reset(self, store):
@@ -63,3 +69,37 @@ class PostgreSQLDatabase(StormBaseDatabase):
                               max("id") IS NOT null)
                        FROM "{0}";
                 """.format(model_class.__storm_table__))
+
+
+
+# Test suite adapter for ITemporaryDatabase.
+
+def _cleanup(self, store, tempdb_name):
+    from mailman.config import config
+    store.rollback()
+    store.close()
+    # From the original database connection, drop the now unused database.
+    config.db.store.execute('DROP DATABASE {0}'.format(tempdb_name))
+
+
+def make_temporary(database):
+    """Adapts by monkey patching an existing PostgreSQL IDatabase."""
+    from mailman.config import config
+    parts = urlsplit(config.database.url)
+    assert parts.scheme == 'postgres'
+    new_parts = list(parts)
+    new_parts[2] = '/mmtest'
+    url = urlunsplit(new_parts)
+    # Use the existing database connection to create a new testing
+    # database.
+    config.db.store.execute('ABORT;')
+    config.db.store.execute('CREATE DATABASE mmtest;')
+    with configuration('database', url=url):
+        database.initialize()
+    database._cleanup = types.MethodType(
+        partial(_cleanup, store=database.store, tempdb_name='mmtest'),
+        database)
+    # bool column values in PostgreSQL.
+    database.FALSE = 'False'
+    database.TRUE = 'True'
+    return database
diff --git a/src/mailman/database/schema/mm_00000000000000_base.py b/src/mailman/database/schema/mm_00000000000000_base.py
index d703088d6..0dcd28edd 100644
--- a/src/mailman/database/schema/mm_00000000000000_base.py
+++ b/src/mailman/database/schema/mm_00000000000000_base.py
@@ -22,34 +22,14 @@ from __future__ import absolute_import, print_function, unicode_literals
 __metaclass__ = type
 __all__ = [
     'upgrade',
-    'post_reset',
-    'pre_reset',
     ]
 
 
-_migration_path = None
 VERSION = '00000000000000'
+_helper = None
 
 
 
 def upgrade(database, store, version, module_path):
     filename = '{0}.sql'.format(database.TAG)
     database.load_schema(store, version, filename, module_path)
-
-
-def pre_reset(store):
-    global _migration_path
-    # Save the entry in the Version table for the test suite reset.  This will
-    # be restored below.
-    from mailman.model.version import Version
-    result = store.find(Version, component=VERSION).one()
-    # Yes, we abuse this field.
-    _migration_path = result.version
-
-
-def post_reset(store):
-    from mailman.model.version import Version
-    # We need to preserve the Version table entry for this migration, since
-    # its existence defines the fact that the tables have been loaded.
-    store.add(Version(component='schema', version=VERSION))
-    store.add(Version(component=VERSION, version=_migration_path))
diff --git a/src/mailman/database/schema/mm_20120407000000.py b/src/mailman/database/schema/mm_20120407000000.py
new file mode 100644
index 000000000..df192b2ae
--- /dev/null
+++ b/src/mailman/database/schema/mm_20120407000000.py
@@ -0,0 +1,136 @@
+# 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.0b1 -> 3.0b2 schema migrations.
+
+All column changes are in the `mailinglist` table.
+
+* Renames:
+ - news_prefix_subject_too -> nntp_prefix_subject_too
+ - news_moderation         -> newsgroup_moderation
+
+* Collapsing:
+ - archive, archive_private -> archive_policy
+
+* Remove:
+ - nntp_host
+
+See https://bugs.launchpad.net/mailman/+bug/971013 for details.
+"""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'upgrade',
+    ]
+
+
+from mailman.interfaces.archiver import ArchivePolicy
+
+
+VERSION = '20120407000000'
+_helper = None
+
+
+
+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 archive_policy(archive, archive_private):
+    """Convert archive and archive_private to archive_policy."""
+    if archive == 0:
+        return int(ArchivePolicy.never)
+    elif archive_private == 1:
+        return int(ArchivePolicy.private)
+    else:
+        return int(ArchivePolicy.public)
+
+
+
+def upgrade_sqlite(database, store, version, module_path):
+    # Load the first part of the migration.  This creates a temporary table to
+    # hold the new mailinglist table columns.  The problem is that some of the
+    # changes must be performed in Python, so after the first part is loaded,
+    # we do the Python changes, drop the old mailing list table, and then
+    # 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;')
+    for value in results:
+        (id, list_post,
+         news_prefix, news_moderation,
+         archive, archive_private) = 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_post,
+                news_moderation,
+                news_prefix,
+                archive_policy(archive, archive_private),
+                id))
+    store.execute('DROP TABLE mailinglist;')
+    store.execute('ALTER TABLE ml_backup RENAME TO mailinglist;')
+
+
+
+def upgrade_postgres(database, store, version, module_path):
+    # Get the old values from the mailinglist table.
+    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;')
+    # Do the column drop next.
+    store.execute('ALTER TABLE mailinglist DROP COLUMN nntp_host;')
+    # Now do the trickier collapsing of values.  Add the new columns.
+    store.execute('ALTER TABLE mailinglist ADD COLUMN archive_policy INTEGER;')
+    # Query the database for the old values of archive and archive_private in
+    # each column.  Then loop through all the results and update the new
+    # 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))
+    # Now drop the old columns.
+    store.execute('ALTER TABLE mailinglist DROP COLUMN archive;')
+    store.execute('ALTER TABLE mailinglist DROP COLUMN archive_private;')
+    # Record the migration in the version table.
+    database.load_schema(store, version, None, module_path)
diff --git a/src/mailman/database/schema/postgres.sql b/src/mailman/database/schema/postgres.sql
index 2e9ba249f..0e97a4332 100644
--- a/src/mailman/database/schema/postgres.sql
+++ b/src/mailman/database/schema/postgres.sql
@@ -110,7 +110,8 @@ CREATE TABLE mailinglist (
     topics_enabled BOOLEAN,
     unsubscribe_policy INTEGER,
     welcome_message_uri TEXT,
-    moderation_callback TEXT,
+    -- This was accidentally added by the PostgreSQL porter.
+    -- moderation_callback TEXT,
     PRIMARY KEY (id)
     );
 
diff --git a/src/mailman/database/schema/sqlite.sql b/src/mailman/database/schema/sqlite.sql
index e6211bf53..e2b2d3814 100644
--- a/src/mailman/database/schema/sqlite.sql
+++ b/src/mailman/database/schema/sqlite.sql
@@ -1,3 +1,6 @@
+-- THIS FILE HAS BEEN FROZEN AS OF 3.0b1
+-- SEE THE SCHEMA MIGRATIONS FOR DIFFERENCES.
+
 PRAGMA foreign_keys = ON;
 
 CREATE TABLE _request (
diff --git a/src/mailman/database/schema/sqlite_20120407000000_01.sql b/src/mailman/database/schema/sqlite_20120407000000_01.sql
new file mode 100644
index 000000000..e5d3a39ff
--- /dev/null
+++ b/src/mailman/database/schema/sqlite_20120407000000_01.sql
@@ -0,0 +1,248 @@
+-- THIS FILE CONTAINS THE SQLITE3 SCHEMA MIGRATION FROM
+-- 3.0b1 TO 3.0b2
+--
+-- AFTER 3.0b2 IS RELEASED YOU MAY NOT EDIT THIS FILE.
+
+-- For SQLite3 migration strategy, see
+-- http://sqlite.org/faq.html#q11
+
+-- This is the base mailinglist table but with these changes:
+-- REM archive
+-- REM archive_private
+-- REM archive_volume_frequency
+-- REM include_list_post_header
+-- REM news_moderation
+-- REM news_prefix_subject_too
+-- REM nntp_host
+--
+-- THESE COLUMNS ARE ADDED BY THE PYTHON MIGRATION LAYER:
+-- ADD allow_list_posts
+-- ADD archive_policy
+-- ADD newsgroup_moderation
+-- ADD nntp_prefix_subject_too
+
+-- LP: #971013
+-- LP: #967238
+
+CREATE TABLE ml_backup(
+    id INTEGER NOT NULL,
+    -- List identity
+    list_name TEXT,
+    mail_host TEXT,
+    allow_list_posts BOOLEAN,
+    include_rfc2369_headers BOOLEAN,
+    -- Attributes not directly modifiable via the web u/i
+    created_at TIMESTAMP,
+    admin_member_chunksize INTEGER,
+    next_request_id INTEGER,
+    next_digest_number INTEGER,
+    digest_last_sent_at TIMESTAMP,
+    volume INTEGER,
+    last_post_at TIMESTAMP,
+    accept_these_nonmembers BLOB,
+    acceptable_aliases_id INTEGER,
+    admin_immed_notify BOOLEAN,
+    admin_notify_mchanges BOOLEAN,
+    administrivia BOOLEAN,
+    advertised BOOLEAN,
+    anonymous_list BOOLEAN,
+    -- Automatic responses.
+    autorespond_owner INTEGER,
+    autoresponse_owner_text TEXT,
+    autorespond_postings INTEGER,
+    autoresponse_postings_text TEXT,
+    autorespond_requests INTEGER,
+    autoresponse_request_text TEXT,
+    autoresponse_grace_period TEXT,
+    -- Bounces.
+    forward_unrecognized_bounces_to INTEGER,
+    process_bounces BOOLEAN,
+    bounce_info_stale_after TEXT,
+    bounce_matching_headers TEXT,
+    bounce_notify_owner_on_disable BOOLEAN,
+    bounce_notify_owner_on_removal BOOLEAN,
+    bounce_score_threshold INTEGER,
+    bounce_you_are_disabled_warnings INTEGER,
+    bounce_you_are_disabled_warnings_interval TEXT,
+    -- Content filtering.
+    filter_action INTEGER,
+    filter_content BOOLEAN,
+    collapse_alternatives BOOLEAN,
+    convert_html_to_plaintext BOOLEAN,
+    default_member_action INTEGER,
+    default_nonmember_action INTEGER,
+    description TEXT,
+    digest_footer_uri TEXT,
+    digest_header_uri TEXT,
+    digest_is_default BOOLEAN,
+    digest_send_periodic BOOLEAN,
+    digest_size_threshold FLOAT,
+    digest_volume_frequency INTEGER,
+    digestable BOOLEAN,
+    discard_these_nonmembers BLOB,
+    emergency BOOLEAN,
+    encode_ascii_prefixes BOOLEAN,
+    first_strip_reply_to BOOLEAN,
+    footer_uri TEXT,
+    forward_auto_discards BOOLEAN,
+    gateway_to_mail BOOLEAN,
+    gateway_to_news BOOLEAN,
+    generic_nonmember_action INTEGER,
+    goodbye_message_uri TEXT,
+    header_matches BLOB,
+    header_uri TEXT,
+    hold_these_nonmembers BLOB,
+    info TEXT,
+    linked_newsgroup TEXT,
+    max_days_to_hold INTEGER,
+    max_message_size INTEGER,
+    max_num_recipients INTEGER,
+    member_moderation_notice TEXT,
+    mime_is_default_digest BOOLEAN,
+    moderator_password TEXT,
+    new_member_options INTEGER,
+    nondigestable BOOLEAN,
+    nonmember_rejection_notice TEXT,
+    obscure_addresses BOOLEAN,
+    owner_chain TEXT,
+    owner_pipeline TEXT,
+    personalize INTEGER,
+    post_id INTEGER,
+    posting_chain TEXT,
+    posting_pipeline TEXT,
+    preferred_language TEXT,
+    private_roster BOOLEAN,
+    display_name TEXT,
+    reject_these_nonmembers BLOB,
+    reply_goes_to_list INTEGER,
+    reply_to_address TEXT,
+    require_explicit_destination BOOLEAN,
+    respond_to_post_requests BOOLEAN,
+    scrub_nondigest BOOLEAN,
+    send_goodbye_message BOOLEAN,
+    send_reminders BOOLEAN,
+    send_welcome_message BOOLEAN,
+    subject_prefix TEXT,
+    subscribe_auto_approval BLOB,
+    subscribe_policy INTEGER,
+    topics BLOB,
+    topics_bodylines_limit INTEGER,
+    topics_enabled BOOLEAN,
+    unsubscribe_policy INTEGER,
+    welcome_message_uri TEXT,
+    PRIMARY KEY (id)
+    );
+
+
+INSERT INTO ml_backup SELECT
+    id,
+    -- List identity
+    list_name,
+    mail_host,
+    include_list_post_header,
+    include_rfc2369_headers,
+    -- Attributes not directly modifiable via the web u/i
+    created_at,
+    admin_member_chunksize,
+    next_request_id,
+    next_digest_number,
+    digest_last_sent_at,
+    volume,
+    last_post_at,
+    accept_these_nonmembers,
+    acceptable_aliases_id,
+    admin_immed_notify,
+    admin_notify_mchanges,
+    administrivia,
+    advertised,
+    anonymous_list,
+    -- Automatic responses.
+    autorespond_owner,
+    autoresponse_owner_text,
+    autorespond_postings,
+    autoresponse_postings_text,
+    autorespond_requests,
+    autoresponse_request_text,
+    autoresponse_grace_period,
+    -- Bounces.
+    forward_unrecognized_bounces_to,
+    process_bounces,
+    bounce_info_stale_after,
+    bounce_matching_headers,
+    bounce_notify_owner_on_disable,
+    bounce_notify_owner_on_removal,
+    bounce_score_threshold,
+    bounce_you_are_disabled_warnings,
+    bounce_you_are_disabled_warnings_interval,
+    -- Content filtering.
+    filter_action,
+    filter_content,
+    collapse_alternatives,
+    convert_html_to_plaintext,
+    default_member_action,
+    default_nonmember_action,
+    description,
+    digest_footer_uri,
+    digest_header_uri,
+    digest_is_default,
+    digest_send_periodic,
+    digest_size_threshold,
+    digest_volume_frequency,
+    digestable,
+    discard_these_nonmembers,
+    emergency,
+    encode_ascii_prefixes,
+    first_strip_reply_to,
+    footer_uri,
+    forward_auto_discards,
+    gateway_to_mail,
+    gateway_to_news,
+    generic_nonmember_action,
+    goodbye_message_uri,
+    header_matches,
+    header_uri,
+    hold_these_nonmembers,
+    info,
+    linked_newsgroup,
+    max_days_to_hold,
+    max_message_size,
+    max_num_recipients,
+    member_moderation_notice,
+    mime_is_default_digest,
+    moderator_password,
+    new_member_options,
+    nondigestable,
+    nonmember_rejection_notice,
+    obscure_addresses,
+    owner_chain,
+    owner_pipeline,
+    personalize,
+    post_id,
+    posting_chain,
+    posting_pipeline,
+    preferred_language,
+    private_roster,
+    display_name,
+    reject_these_nonmembers,
+    reply_goes_to_list,
+    reply_to_address,
+    require_explicit_destination,
+    respond_to_post_requests,
+    scrub_nondigest,
+    send_goodbye_message,
+    send_reminders,
+    send_welcome_message,
+    subject_prefix,
+    subscribe_auto_approval,
+    subscribe_policy,
+    topics,
+    topics_bodylines_limit,
+    topics_enabled,
+    unsubscribe_policy,
+    welcome_message_uri
+    FROM mailinglist;
+
+-- 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 nntp_prefix_subject_too INTEGER;
+ALTER TABLE ml_backup ADD COLUMN newsgroup_moderation INTEGER;
diff --git a/src/mailman/database/sqlite.py b/src/mailman/database/sqlite.py
index 2677d0d71..8415aa1ee 100644
--- a/src/mailman/database/sqlite.py
+++ b/src/mailman/database/sqlite.py
@@ -17,19 +17,25 @@
 
 """SQLite database support."""
 
-from __future__ import absolute_import, unicode_literals
+from __future__ import absolute_import, print_function, unicode_literals
 
 __metaclass__ = type
 __all__ = [
     'SQLiteDatabase',
+    'make_temporary',
     ]
 
 
 import os
+import types
+import shutil
+import tempfile
 
+from functools import partial
 from urlparse import urlparse
 
 from mailman.database.base import StormBaseDatabase
+from mailman.testing.helpers import configuration
 
 
 
@@ -41,7 +47,7 @@ class SQLiteDatabase(StormBaseDatabase):
     def _database_exists(self, store):
         """See `BaseDatabase`."""
         table_query = 'select tbl_name from sqlite_master;'
-        table_names = set(item[0] for item in 
+        table_names = set(item[0] for item in
                           store.execute(table_query))
         return 'version' in table_names
 
@@ -54,3 +60,25 @@ class SQLiteDatabase(StormBaseDatabase):
         # Ignore errors
         if fd > 0:
             os.close(fd)
+
+
+
+# Test suite adapter for ITemporaryDatabase.
+
+def _cleanup(self, tempdir):
+    shutil.rmtree(tempdir)
+
+
+def make_temporary(database):
+    """Adapts by monkey patching an existing SQLite IDatabase."""
+    tempdir = tempfile.mkdtemp()
+    url = 'sqlite:///' + os.path.join(tempdir, 'mailman.db')
+    with configuration('database', url=url):
+        database.initialize()
+    database._cleanup = types.MethodType(
+        partial(_cleanup, tempdir=tempdir),
+        database)
+    # bool column values in SQLite must be integers.
+    database.FALSE = 0
+    database.TRUE = 1
+    return database
diff --git a/src/mailman/database/tests/__init__.py b/src/mailman/database/tests/__init__.py
new file mode 100644
index 000000000..e69de29bb
--- /dev/null
+++ b/src/mailman/database/tests/__init__.py
diff --git a/src/mailman/database/tests/data/__init__.py b/src/mailman/database/tests/data/__init__.py
new file mode 100644
index 000000000..e69de29bb
--- /dev/null
+++ b/src/mailman/database/tests/data/__init__.py
diff --git a/src/mailman/database/tests/data/mailman_01.db b/src/mailman/database/tests/data/mailman_01.db
new file mode 100644
index 000000000..1ff8d8343
--- /dev/null
+++ b/src/mailman/database/tests/data/mailman_01.db
diff --git a/src/mailman/database/tests/data/migration_postgres_1.sql b/src/mailman/database/tests/data/migration_postgres_1.sql
new file mode 100644
index 000000000..b82ecf6e4
--- /dev/null
+++ b/src/mailman/database/tests/data/migration_postgres_1.sql
@@ -0,0 +1,133 @@
+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
new file mode 100644
index 000000000..a5ac96dfa
--- /dev/null
+++ b/src/mailman/database/tests/data/migration_sqlite_1.sql
@@ -0,0 +1,133 @@
+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_migrations.py b/src/mailman/database/tests/test_migrations.py
new file mode 100644
index 000000000..6ad648623
--- /dev/null
+++ b/src/mailman/database/tests/test_migrations.py
@@ -0,0 +1,335 @@
+# 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/>.
+
+"""Test schema migrations."""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'TestMigration20120407MigratedData',
+    'TestMigration20120407Schema',
+    'TestMigration20120407UnchangedData',
+    ]
+
+
+import unittest
+
+from pkg_resources import resource_string
+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.listmanager import IListManager
+from mailman.interfaces.mailinglist import IAcceptableAliasSet
+from mailman.interfaces.nntp import NewsgroupModeration
+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
+    * REMOVE archive
+    * REMOVE archive_private
+    * REMOVE archive_volume_frequency
+    * REMOVE nntp_host
+    """
+
+    layer = ConfigLayer
+
+    def setUp(self):
+        self._database = getUtility(IDatabaseFactory, 'temporary').create()
+
+    def tearDown(self):
+        self._database._cleanup()
+
+
+
+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.
+        #
+        # 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',
+                        'nntp_prefix_subject_too'):
+            self.assertRaises(DatabaseError,
+                              self._database.store.execute,
+                              'select {0} from mailinglist;'.format(missing))
+            self._database.store.rollback()
+        for present in ('archive',
+                        'archive_private',
+                        'archive_volume_frequency',
+                        '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))
+
+    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',
+                        '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))
+        for missing in ('archive',
+                        'archive_private',
+                        'archive_volume_frequency',
+                        '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()
+
+
+
+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)
+        # Update to the current migration we're testing.
+        self._database.load_migrations('20120407000000')
+
+    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):
+        # Update to the current migration we're testing.
+        self._database.load_migrations('20120407000000')
+
+    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_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)