Migrate bounceevent.list_name -> bounceevent.list_id

* Rename StormBaseDatabase._create() -> .initialize()
* Refactor database initialization.
* make_listid() helper.
* Add a pivot() helper for schema migrations.

7 files changed, 175 insertions, 48 deletions

diff --git a/src/mailman/database/schema/helpers.py b/src/mailman/database/schema/helpers.py
new file mode 100644
index 000000000..c8638a12a
--- /dev/null
+++ b/src/mailman/database/schema/helpers.py
@@ -0,0 +1,43 @@
+# Copyright (C) 2013 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/>.
+
+"""Schema migration helpers."""
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'make_listid',
+    ]
+
+
+
+def make_listid(fqdn_listname):
+    """Turn a FQDN list name into a List-ID."""
+    list_name, at, mail_host = fqdn_listname.partition('@')
+    if at == '':
+        # If there is no @ sign in the value, assume it already contains the
+        # list-id.
+        return fqdn_listname
+    return '{0}.{1}'.format(list_name, mail_host)
+
+
+
+def pivot(store, table_name):
+    """Pivot a backup table into the real table name."""
+    store.execute('DROP TABLE {}'.format(table_name))
+    store.execute('ALTER TABLE {0}_backup RENAME TO {0}'.format(table_name))
diff --git a/src/mailman/database/schema/mm_20120407000000.py b/src/mailman/database/schema/mm_20120407000000.py
index 8855df5af..463635fd9 100644
--- a/src/mailman/database/schema/mm_20120407000000.py
+++ b/src/mailman/database/schema/mm_20120407000000.py
@@ -48,11 +48,11 @@ __all__ = [
     ]
 
 
+from mailman.database.schema.helpers import pivot
 from mailman.interfaces.archiver import ArchivePolicy
 
 
 VERSION = '20120407000000'
-_helper = None
 
 
 
@@ -98,7 +98,7 @@ def upgrade_sqlite(database, store, version, module_path):
         list_id = '{0}.{1}'.format(list_name, mail_host)
         fqdn_listname = '{0}@{1}'.format(list_name, mail_host)
         store.execute("""
-            UPDATE ml_backup SET
+            UPDATE mailinglist_backup SET
                 allow_list_posts = {0},
                 newsgroup_moderation = {1},
                 nntp_prefix_subject_too = {2},
@@ -120,8 +120,7 @@ def upgrade_sqlite(database, store, version, module_path):
             WHERE mailing_list = '{1}';
             """.format(list_id, fqdn_listname))
     # Pivot the backup table to the real thing.
-    store.execute('DROP TABLE mailinglist;')
-    store.execute('ALTER TABLE ml_backup RENAME TO mailinglist;')
+    pivot(store, 'mailinglist')
     # Now add some indexes that were previously missing.
     store.execute(
         'CREATE INDEX ix_mailinglist_list_id ON mailinglist (list_id);')
@@ -137,12 +136,11 @@ def upgrade_sqlite(database, store, version, module_path):
         else:
             list_id = '{0}.{1}'.format(list_name, mail_host)
         store.execute("""
-            UPDATE mem_backup SET list_id = '{0}'
+            UPDATE member_backup SET list_id = '{0}'
             WHERE id = {1};
             """.format(list_id, id))
     # Pivot the backup table to the real thing.
-    store.execute('DROP TABLE member;')
-    store.execute('ALTER TABLE mem_backup RENAME TO member;')
+    pivot(store, 'member')
 
 
 
diff --git a/src/mailman/database/schema/mm_20121015000000.py b/src/mailman/database/schema/mm_20121015000000.py
index 09078901d..da3671998 100644
--- a/src/mailman/database/schema/mm_20121015000000.py
+++ b/src/mailman/database/schema/mm_20121015000000.py
@@ -33,6 +33,9 @@ __all__ = [
     ]
 
 
+from mailman.database.schema.helpers import make_listid, pivot
+
+
 VERSION = '20121015000000'
 
 
@@ -45,19 +48,9 @@ def upgrade(database, store, version, module_path):
 
 
 
-def _make_listid(fqdn_listname):
-    list_name, at, mail_host = fqdn_listname.partition('@')
-    if at == '':
-        # If there is no @ sign in the value, assume it already contains the
-        # list-id.
-        return fqdn_listname
-    return '{0}.{1}'.format(list_name, mail_host)
-
-
-
 def upgrade_sqlite(database, store, version, module_path):
     database.load_schema(
-        store, version, 'sqlite_{0}_01.sql'.format(version), module_path)
+        store, version, 'sqlite_{}_01.sql'.format(version), module_path)
     results = store.execute("""
         SELECT id, mailing_list
         FROM ban;
@@ -67,15 +60,12 @@ def upgrade_sqlite(database, store, version, module_path):
         if mailing_list is None:
             continue
         store.execute("""
-            UPDATE ban_backup SET list_id = '{0}'
-            WHERE id = {1};
-            """.format(_make_listid(mailing_list), id))
+            UPDATE ban_backup SET list_id = '{}'
+            WHERE id = {};
+            """.format(make_listid(mailing_list), id))
     # Pivot the bans backup table to the real thing.
-    store.execute('DROP TABLE ban;')
-    store.execute('ALTER TABLE ban_backup RENAME TO ban;')
-    # Pivot the mailinglist backup table to the real thing.
-    store.execute('DROP TABLE mailinglist;')
-    store.execute('ALTER TABLE ml_backup RENAME TO mailinglist;')
+    pivot(store, 'ban')
+    pivot(store, 'mailinglist')
 
 
 
@@ -90,7 +80,7 @@ def upgrade_postgres(database, store, version, module_path):
         store.execute("""
             UPDATE ban SET list_id = '{0}'
             WHERE id = {1};
-            """.format(_make_listid(mailing_list), id))
+            """.format(make_listid(mailing_list), id))
     store.execute('ALTER TABLE ban DROP COLUMN mailing_list;')
     store.execute('ALTER TABLE mailinglist DROP COLUMN new_member_options;')
     store.execute('ALTER TABLE mailinglist DROP COLUMN send_reminders;')
diff --git a/src/mailman/database/schema/mm_20130406000000.py b/src/mailman/database/schema/mm_20130406000000.py
new file mode 100644
index 000000000..11ba70869
--- /dev/null
+++ b/src/mailman/database/schema/mm_20130406000000.py
@@ -0,0 +1,65 @@
+# Copyright (C) 2013 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.0b3 -> 3.0b4 schema migrations.
+
+Renamed:
+ * bounceevent.list_name -> bounceevent.list_id
+"""
+
+
+from __future__ import absolute_import, print_function, unicode_literals
+
+__metaclass__ = type
+__all__ = [
+    'upgrade'
+    ]
+
+
+from mailman.database.schema.helpers import make_listid, pivot
+
+
+VERSION = '20130406000000'
+
+
+
+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 upgrade_sqlite(database, store, version, module_path):
+    database.load_schema(
+        store, version, 'sqlite_{}_01.sql'.format(version), module_path)
+    results = store.execute("""
+        SELECT id, list_name
+        FROM bounceevent;
+        """)
+    for id, list_name in results:
+        store.execute("""
+            UPDATE bounceevent_backup SET list_id = '{}'
+            WHERE id = {};
+            """.format(make_listid(list_name), id))
+    pivot(store, 'bounceevent')
+
+
+
+def upgrade_postgres(database, store, version, module_path):
+    pass
diff --git a/src/mailman/database/schema/sqlite_20120407000000_01.sql b/src/mailman/database/schema/sqlite_20120407000000_01.sql
index 5eacc4047..a8db75be9 100644
--- a/src/mailman/database/schema/sqlite_20120407000000_01.sql
+++ b/src/mailman/database/schema/sqlite_20120407000000_01.sql
@@ -1,12 +1,12 @@
--- THIS FILE CONTAINS THE SQLITE3 SCHEMA MIGRATION FROM
+-- This file contains the sqlite3 schema migration from
 -- 3.0b1 TO 3.0b2
 --
--- AFTER 3.0b2 IS RELEASED YOU MAY NOT EDIT THIS FILE.
+-- 3.0b2 has been released thus you MAY NOT edit this file.
 
 -- For SQLite3 migration strategy, see
 -- http://sqlite.org/faq.html#q11
 
--- REMOVALS from the mailinglist table.
+-- REMOVALS from the mailinglist table:
 -- REM archive
 -- REM archive_private
 -- REM archive_volume_frequency
@@ -15,7 +15,7 @@
 -- REM news_prefix_subject_too
 -- REM nntp_host
 --
--- ADDS to the mailing list table.
+-- ADDS to the mailing list table:
 -- ADD allow_list_posts
 -- ADD archive_policy
 -- ADD list_id
@@ -25,16 +25,16 @@
 -- LP: #971013
 -- LP: #967238
 
--- REMOVALS from the member table.
+-- REMOVALS from the member table:
 -- REM mailing_list
 
--- ADDS to the member table.
+-- ADDS to the member table:
 -- ADD list_id
 
 -- LP: #1024509
 
 
-CREATE TABLE ml_backup (
+CREATE TABLE mailinglist_backup (
     id INTEGER NOT NULL,
     -- List identity
     list_name TEXT,
@@ -142,7 +142,7 @@ CREATE TABLE ml_backup (
     PRIMARY KEY (id)
     );
 
-INSERT INTO ml_backup SELECT
+INSERT INTO mailinglist_backup SELECT
     id,
     -- List identity
     list_name,
@@ -249,7 +249,7 @@ INSERT INTO ml_backup SELECT
     welcome_message_uri
     FROM mailinglist;
 
-CREATE TABLE mem_backup(
+CREATE TABLE member_backup(
     id INTEGER NOT NULL,
     _member_id TEXT,
     role INTEGER,
@@ -260,7 +260,7 @@ CREATE TABLE mem_backup(
     PRIMARY KEY (id)
     );
 
-INSERT INTO mem_backup SELECT
+INSERT INTO member_backup SELECT
     id,
     _member_id,
     role,
@@ -272,9 +272,9 @@ INSERT INTO mem_backup SELECT
 
 
 -- Add the new columns.  They'll get inserted at the Python layer.
-ALTER TABLE ml_backup ADD COLUMN archive_policy INTEGER;
-ALTER TABLE ml_backup ADD COLUMN list_id TEXT;
-ALTER TABLE ml_backup ADD COLUMN nntp_prefix_subject_too INTEGER;
-ALTER TABLE ml_backup ADD COLUMN newsgroup_moderation INTEGER;
+ALTER TABLE mailinglist_backup ADD COLUMN archive_policy INTEGER;
+ALTER TABLE mailinglist_backup ADD COLUMN list_id TEXT;
+ALTER TABLE mailinglist_backup ADD COLUMN nntp_prefix_subject_too INTEGER;
+ALTER TABLE mailinglist_backup ADD COLUMN newsgroup_moderation INTEGER;
 
-ALTER TABLE mem_backup ADD COLUMN list_id TEXT;
+ALTER TABLE member_backup ADD COLUMN list_id TEXT;
diff --git a/src/mailman/database/schema/sqlite_20121015000000_01.sql b/src/mailman/database/schema/sqlite_20121015000000_01.sql
index 3e2410c3b..a80dc03df 100644
--- a/src/mailman/database/schema/sqlite_20121015000000_01.sql
+++ b/src/mailman/database/schema/sqlite_20121015000000_01.sql
@@ -1,12 +1,12 @@
--- THIS FILE CONTAINS THE SQLITE3 SCHEMA MIGRATION FROM
+-- This file contains the sqlite3 schema migration from
 -- 3.0b2 TO 3.0b3
 --
--- AFTER 3.0b3 IS RELEASED YOU MAY NOT EDIT THIS FILE.
+-- 3.0b3 has been released thus you MAY NOT edit this file.
 
--- REMOVALS from the ban table.
+-- REMOVALS from the ban table:
 -- REM mailing_list
 
--- ADDS to the ban table.
+-- ADDS to the ban table:
 -- ADD list_id
 
 CREATE TABLE ban_backup (
@@ -30,7 +30,7 @@ ALTER TABLE ban_backup ADD COLUMN list_id TEXT;
 -- REM private_roster
 -- REM admin_member_chunksize
 
-CREATE TABLE ml_backup (
+CREATE TABLE mailinglist_backup (
     id INTEGER NOT NULL,
     list_name TEXT,
     mail_host TEXT,
@@ -130,7 +130,7 @@ CREATE TABLE ml_backup (
     PRIMARY KEY (id)
     );
 
-INSERT INTO ml_backup SELECT
+INSERT INTO mailinglist_backup SELECT
     id,
     list_name,
     mail_host,
diff --git a/src/mailman/database/schema/sqlite_20130406000000_01.sql b/src/mailman/database/schema/sqlite_20130406000000_01.sql
new file mode 100644
index 000000000..d95a5fcc0
--- /dev/null
+++ b/src/mailman/database/schema/sqlite_20130406000000_01.sql
@@ -0,0 +1,31 @@
+-- This file contains the SQLite schema migration from
+-- 3.0b3 to 3.0b4
+--
+-- After 3.0b4 is released you may not edit this file.
+
+-- For SQLite3 migration strategy, see
+-- http://sqlite.org/faq.html#q11
+
+-- REMOVALS from the bounceevent table:
+-- REM list_name
+
+-- ADDS to the ban bounceevent table:
+-- ADD list_id
+
+CREATE TABLE bounceevent_backup (
+    id INTEGER NOT NULL,
+    email TEXT,
+    'timestamp' TIMESTAMP,
+    message_id TEXT,
+    context INTEGER,
+    processed BOOLEAN,
+    PRIMARY KEY (id)
+    );
+
+INSERT INTO bounceevent_backup SELECT
+    id, email, "timestamp", message_id,
+    context, processed
+    FROM bounceevent;
+
+ALTER TABLE bounceevent_backup ADD COLUMN list_id TEXT;
+