Refactor the SubscriptionService.find_members method to be pure SQL

Additional cleanups by Barry.

Fix the SubscriptionService.find_members SQL query on PostgreSQL.

Assert that an unsupported use case is actually unsupported.

Various little clean ups.

1 files changed, 202 insertions, 2 deletions

diff --git a/src/mailman/app/tests/test_subscriptions.py b/src/mailman/app/tests/test_subscriptions.py
index fa51184a9..62b2a8d8c 100644
--- a/src/mailman/app/tests/test_subscriptions.py
+++ b/src/mailman/app/tests/test_subscriptions.py
@@ -27,9 +27,9 @@ import unittest
 from mailman.app.lifecycle import create_list
 from mailman.app.subscriptions import SubscriptionWorkflow
 from mailman.interfaces.bans import IBanManager
-from mailman.interfaces.member import MembershipIsBannedError
+from mailman.interfaces.member import MemberRole, MembershipIsBannedError
 from mailman.interfaces.pending import IPendings
-from mailman.interfaces.subscriptions import TokenOwner
+from mailman.interfaces.subscriptions import ISubscriptionService, TokenOwner
 from mailman.testing.helpers import LogFileMark, get_queue_messages
 from mailman.testing.layers import ConfigLayer
 from mailman.interfaces.mailinglist import SubscriptionPolicy
@@ -641,3 +641,203 @@ approval:
         self.assertIsNone(workflow.token)
         self.assertEqual(workflow.token_owner, TokenOwner.no_one)
         self.assertEqual(workflow.member.address, anne)
+
+
+
+class TestSubscriptionService(unittest.TestCase):
+    layer = ConfigLayer
+
+    def setUp(self):
+        self._mlist = create_list('test@example.com')
+        self._mlist.admin_immed_notify = False
+        self._user_manager = getUtility(IUserManager)
+        self._service = getUtility(ISubscriptionService)
+
+    def test_find_member_address_no_user(self):
+        # Find address-based memberships when no user is linked to the address.
+        address = self._user_manager.create_address(
+            'anne@example.com', 'Anne Address')
+        self._mlist.subscribe(address)
+        members = self._service.find_members('anne@example.com')
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].address, address)
+
+    def test_find_member_address_with_user(self):
+        # Find address-based memberships when a user is linked to the address.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Subscribe the address.
+        self._mlist.subscribe(address)
+        members = self._service.find_members('anne@example.com')
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].user, user)
+
+    def test_find_member_user(self):
+        # Find user-based memberships by address.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Subscribe the user.
+        self._mlist.subscribe(user)
+        members = self._service.find_members('anne@example.com')
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].user, user)
+
+    def test_find_member_user_secondary_address(self):
+        # Find user-based memberships using a secondary address.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Create a secondary address.
+        address_2 = self._user_manager.create_address(
+            'anne2@example.com', 'Anne User 2')
+        address_2.user = user
+        # Subscribe the user.
+        self._mlist.subscribe(user)
+        # Search for the secondary address.
+        members = self._service.find_members('anne2@example.com')
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].user, user)
+
+    def test_wont_find_member_secondary_address(self):
+        # A user is subscribed with one of their address, and a search is
+        # performed on another of their addresses.  This is not supported; the
+        # subscription is not returned.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Create a secondary address.
+        address_2 = self._user_manager.create_address(
+            'anne2@example.com', 'Anne User 2')
+        address_2.verified_on = now()
+        address_2.user = user
+        # Subscribe the secondary address.
+        self._mlist.subscribe(address_2)
+        # Search for the primary address.
+        members = self._service.find_members('anne@example.com')
+        self.assertEqual(len(members), 0)
+
+    def test_find_member_user_id(self):
+        # Find user-based memberships by user_id.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Subscribe the user.
+        self._mlist.subscribe(user)
+        members = self._service.find_members(user.user_id)
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].user, user)
+
+    def test_find_member_user_id_controlled_addresses(self):
+        # Find address-based memberships by user_id when a secondary address is
+        # subscribed.
+        user = self._user_manager.create_user(
+            'anne@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Create a secondary address.
+        address_2 = self._user_manager.create_address(
+            'anne2@example.com', 'Anne User 2')
+        address_2.verified_on = now()
+        address_2.user = user
+        # Create a third address.
+        address_3 = self._user_manager.create_address(
+            'anne3@example.com', 'Anne User 3')
+        address_3.verified_on = now()
+        address_3.user = user
+        # Subscribe the secondary address only.
+        self._mlist.subscribe(address_2)
+        members = self._service.find_members(user.user_id)
+        self.assertEqual(len(members), 1)
+        self.assertEqual(members[0].address, address_2)
+
+    def test_find_member_sorting(self):
+        # Check that the memberships are properly sorted.
+        user = self._user_manager.create_user(
+            'anne1@example.com', 'Anne User')
+        address = user.addresses[0]
+        address.verified_on = now()
+        user.preferred_address = address
+        # Create a secondary address.
+        address_2 = self._user_manager.create_address(
+            'anne2@example.com', 'Anne User 2')
+        address_2.verified_on = now()
+        address_2.user = user
+        # Create a third address.
+        address_3 = self._user_manager.create_address(
+            'anne3@example.com', 'Anne User 3')
+        address_3.verified_on = now()
+        address_3.user = user
+        # Create three lists.
+        mlist1 = create_list('test1@example.com')
+        mlist1.admin_immed_notify = False
+        mlist2 = create_list('test2@example.com')
+        mlist2.admin_immed_notify = False
+        mlist3 = create_list('test3@example.com')
+        mlist3.admin_immed_notify = False
+        # Subscribe the addresses in random order
+        # https://www.xkcd.com/221/
+        mlist3.subscribe(address_3, MemberRole.moderator)
+        mlist3.subscribe(address_3, MemberRole.owner)
+        mlist3.subscribe(address_3, MemberRole.member)
+        mlist3.subscribe(address_2, MemberRole.member)
+        mlist3.subscribe(address_2, MemberRole.owner)
+        mlist3.subscribe(address_2, MemberRole.moderator)
+        mlist3.subscribe(address, MemberRole.owner)
+        mlist3.subscribe(address, MemberRole.member)
+        mlist3.subscribe(address, MemberRole.moderator)
+        mlist2.subscribe(address_2, MemberRole.moderator)
+        mlist2.subscribe(address_2, MemberRole.member)
+        mlist2.subscribe(address_2, MemberRole.owner)
+        mlist2.subscribe(address_3, MemberRole.moderator)
+        mlist2.subscribe(address_3, MemberRole.member)
+        mlist2.subscribe(address_3, MemberRole.owner)
+        mlist2.subscribe(address, MemberRole.owner)
+        mlist2.subscribe(address, MemberRole.moderator)
+        mlist2.subscribe(address, MemberRole.member)
+        mlist1.subscribe(address_2, MemberRole.moderator)
+        mlist1.subscribe(address, MemberRole.member)
+        mlist1.subscribe(address_3, MemberRole.owner)
+        # The results should be sorted first by list id, then by address, then
+        # by member role.
+        members = self._service.find_members(user.user_id)
+        self.assertEqual(len(members), 21)
+        self.assertListEqual(
+            [(m.list_id.partition('.')[0],
+              m.address.email.partition('@')[0],
+              m.role)
+             for m in members],
+            [('test1', 'anne1', MemberRole.member),
+             ('test1', 'anne2', MemberRole.moderator),
+             ('test1', 'anne3', MemberRole.owner),
+             ('test2', 'anne1', MemberRole.member),
+             ('test2', 'anne1', MemberRole.owner),
+             ('test2', 'anne1', MemberRole.moderator),
+             ('test2', 'anne2', MemberRole.member),
+             ('test2', 'anne2', MemberRole.owner),
+             ('test2', 'anne2', MemberRole.moderator),
+             ('test2', 'anne3', MemberRole.member),
+             ('test2', 'anne3', MemberRole.owner),
+             ('test2', 'anne3', MemberRole.moderator),
+             ('test3', 'anne1', MemberRole.member),
+             ('test3', 'anne1', MemberRole.owner),
+             ('test3', 'anne1', MemberRole.moderator),
+             ('test3', 'anne2', MemberRole.member),
+             ('test3', 'anne2', MemberRole.owner),
+             ('test3', 'anne2', MemberRole.moderator),
+             ('test3', 'anne3', MemberRole.member),
+             ('test3', 'anne3', MemberRole.owner),
+             ('test3', 'anne3', MemberRole.moderator),
+             ])