* The link between members and the mailing lists they are subscribed to, is

  now via the RFC 2369 `list_id` instead of the fqdn listname (i.e. posting
  address).  This is because while the posting address can change if the
  mailing list is moved to a new server, the list id is fixed.
  (LP: #1024509)
  + IListManager.get_by_list_id() added.
  + IListManager.list_ids added.
  + IMailingList.list_id added.
  + Several internal APIs that accepted fqdn list names now require list ids,
    e.g. ISubscriptionService.join() and .find_members().
  + IMember.list_id attribute added; .mailing_list is now an alias that
    retrieves and returns the IMailingList.
  - list_id added (LP: #1024509)

3 files changed, 70 insertions, 27 deletions

diff --git a/src/mailman/database/schema/mm_20120407000000.py b/src/mailman/database/schema/mm_20120407000000.py
index 068a05834..f6dd60be5 100644
--- a/src/mailman/database/schema/mm_20120407000000.py
+++ b/src/mailman/database/schema/mm_20120407000000.py
@@ -31,6 +31,12 @@ All column changes are in the `mailinglist` table.
  - generic_nonmember_action
  - nntp_host
 
+* Added:
+ - list_id
+
+* Changes:
+  member.mailing_list holds the list_id not the fqdn_listname
+
 See https://bugs.launchpad.net/mailman/+bug/971013 for details.
 """
 
@@ -77,29 +83,51 @@ def upgrade_sqlite(database, store, version, module_path):
     # rename the temporary table to its place.
     database.load_schema(
         store, version, 'sqlite_{0}_01.sql'.format(version), module_path)
-    results = store.execute(
-        'SELECT id, include_list_post_header, '
-        'news_prefix_subject_too, news_moderation, '
-        'archive, archive_private FROM mailinglist;')
+    results = store.execute("""
+        SELECT id, include_list_post_header,
+        news_prefix_subject_too, news_moderation,
+        archive, archive_private, list_name, mail_host
+        FROM mailinglist;
+        """)
     for value in results:
         (id, list_post,
          news_prefix, news_moderation,
-         archive, archive_private) = value
+         archive, archive_private,
+         list_name, mail_host) = value
         # Figure out what the new archive_policy column value should be.
-        store.execute(
-            'UPDATE ml_backup SET '
-            '    allow_list_posts = {0}, '
-            '    newsgroup_moderation = {1}, '
-            '    nntp_prefix_subject_too = {2}, '
-            '    archive_policy = {3} '
-            'WHERE id = {4};'.format(
+        list_id = '{0}.{1}'.format(list_name, mail_host)
+        fqdn_listname = '{0}@{1}'.format(list_name, mail_host)
+        store.execute("""
+            UPDATE ml_backup SET
+                allow_list_posts = {0},
+                newsgroup_moderation = {1},
+                nntp_prefix_subject_too = {2},
+                archive_policy = {3},
+                list_id = '{4}'
+            WHERE id = {5};
+            """.format(
                 list_post,
                 news_moderation,
                 news_prefix,
                 archive_policy(archive, archive_private),
+                list_id,
                 id))
+        # Also update the member.mailing_list column to hold the list_id
+        # instead of the fqdn_listname.
+        store.execute("""
+            UPDATE member SET
+                mailing_list = '{0}'
+            WHERE mailing_list = '{1}';
+            """.format(list_id, fqdn_listname))
+    # Pivot the backup table to the real thing.
     store.execute('DROP TABLE mailinglist;')
     store.execute('ALTER TABLE ml_backup RENAME TO mailinglist;')
+    # Now add some indexes that were previously missing.
+    store.execute(
+        'CREATE INDEX ix_mailinglist_list_id ON mailinglist (list_id);')
+    store.execute(
+        'CREATE INDEX ix_mailinglist_fqdn_listname '
+        'ON mailinglist (list_name, mail_host);')
 
 
 
@@ -108,17 +136,20 @@ def upgrade_postgres(database, store, version, module_path):
     results = store.execute(
         'SELECT id, archive, archive_private FROM mailinglist;')
     # Do the simple renames first.
-    store.execute(
-        'ALTER TABLE mailinglist '
-        '   RENAME COLUMN news_prefix_subject_too TO nntp_prefix_subject_too;')
-    store.execute(
-        'ALTER TABLE mailinglist '
-        '   RENAME COLUMN news_moderation TO newsgroup_moderation;')
-    store.execute(
-        'ALTER TABLE mailinglist '
-        '   RENAME COLUMN include_list_post_header TO allow_list_posts;')
+    store.execute("""
+        ALTER TABLE mailinglist
+           RENAME COLUMN news_prefix_subject_too TO nntp_prefix_subject_too;
+        """)
+    store.execute("""
+        ALTER TABLE mailinglist
+           RENAME COLUMN news_moderation TO newsgroup_moderation;
+        """)
+    store.execute("""
+        ALTER TABLE mailinglist
+           RENAME COLUMN include_list_post_header TO allow_list_posts;
+        """)
     # Do the easy column drops next.
-    for column in ('archive_volume_frequency', 
+    for column in ('archive_volume_frequency',
                    'generic_nonmember_action',
                    'nntp_host'):
         store.execute(
@@ -130,11 +161,11 @@ def upgrade_postgres(database, store, version, module_path):
     # archive_policy from the old values.
     for value in results:
         id, archive, archive_private = value
-        store.execute('UPDATE mailinglist SET '
-                      '    archive_policy = {0} '
-                      'WHERE id = {1};'.format(
-                          archive_policy(archive, archive_private),
-                          id))
+        store.execute("""
+            UPDATE mailinglist SET
+                archive_policy = {0}
+            WHERE id = {1};
+            """.format(archive_policy(archive, archive_private), id))
     # Now drop the old columns.
     for column in ('archive', 'archive_private'):
         store.execute(
diff --git a/src/mailman/database/schema/sqlite_20120407000000_01.sql b/src/mailman/database/schema/sqlite_20120407000000_01.sql
index a4eb0adce..c0e752d68 100644
--- a/src/mailman/database/schema/sqlite_20120407000000_01.sql
+++ b/src/mailman/database/schema/sqlite_20120407000000_01.sql
@@ -18,6 +18,7 @@
 -- THESE COLUMNS ARE ADDED BY THE PYTHON MIGRATION LAYER:
 -- ADD allow_list_posts
 -- ADD archive_policy
+-- ADD list_id
 -- ADD newsgroup_moderation
 -- ADD nntp_prefix_subject_too
 
@@ -242,5 +243,6 @@ INSERT INTO ml_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;
diff --git a/src/mailman/database/tests/test_migrations.py b/src/mailman/database/tests/test_migrations.py
index c69a8c545..4ad709c16 100644
--- a/src/mailman/database/tests/test_migrations.py
+++ b/src/mailman/database/tests/test_migrations.py
@@ -54,6 +54,7 @@ class MigrationTestBase(unittest.TestCase):
     * news_prefix_subject_too -> nntp_prefix_subject_too
     * include_list_post_header -> allow_list_posts
     * ADD archive_policy
+    * ADD list_id
     * REMOVE archive
     * REMOVE archive_private
     * REMOVE archive_volume_frequency
@@ -83,6 +84,7 @@ class TestMigration20120407Schema(MigrationTestBase):
         # Verify that the database has not yet been migrated.
         for missing in ('allow_list_posts',
                         'archive_policy',
+                        'list_id',
                         'nntp_prefix_subject_too'):
             self.assertRaises(DatabaseError,
                               self._database.store.execute,
@@ -111,6 +113,7 @@ class TestMigration20120407Schema(MigrationTestBase):
         # Verify that the database has been migrated.
         for present in ('allow_list_posts',
                         'archive_policy',
+                        'list_id',
                         'nntp_prefix_subject_too'):
             # This should not produce an exception.  Is there some better test
             # that we can perform?
@@ -263,6 +266,13 @@ class TestMigration20120407MigratedData(MigrationTestBase):
             mlist = getUtility(IListManager).get('test@example.com')
             self.assertEqual(mlist.archive_policy, ArchivePolicy.public)
 
+    def test_list_id(self):
+        # Test that the mailinglist table gets a list_id column.
+        self._upgrade()
+        with temporary_db(self._database):
+            mlist = getUtility(IListManager).get('test@example.com')
+            self.assertEqual(mlist.list_id, 'test.example.com')
+
     def test_news_moderation_none(self):
         # Test that news_moderation becomes newsgroup_moderation.
         self._database.store.execute(