Add a table for the Mailman user

7 files changed, 109 insertions, 2 deletions

diff --git a/kittystore.spec b/kittystore.spec
index 277e6fd..e03d78c 100644
--- a/kittystore.spec
+++ b/kittystore.spec
@@ -21,6 +21,7 @@ BuildRequires:  python-zope-interface
 BuildRequires:  python-networkx
 BuildRequires:  mailman >= 3:3.0.0
 BuildRequires:  python-whoosh
+BuildRequires:  python-mailman-client
 Requires:  python-mock
 Requires:  python-dateutil < 2.0
 Requires:  python-storm
@@ -28,6 +29,7 @@ Requires:  python-zope-interface
 Requires:  python-networkx
 Requires:  mailman >= 3:3.0.0
 Requires:  python-whoosh
+Requires:  python-mailman-client
 
 %description
 KittyStore is the archiving library for HyperKitty, the Mailman 3 archiver.
diff --git a/kittystore/__init__.py b/kittystore/__init__.py
index 6210c95..272c19c 100644
--- a/kittystore/__init__.py
+++ b/kittystore/__init__.py
@@ -20,7 +20,9 @@ __all__ = ("get_store", "MessageNotFound", )
 
 def get_store(settings, debug=None):
     """Factory for a KittyStore subclass"""
-    required_keys = ("KITTYSTORE_URL", "KITTYSTORE_SEARCH_INDEX")
+    required_keys = ("KITTYSTORE_URL", "KITTYSTORE_SEARCH_INDEX",
+                     "MAILMAN_REST_SERVER", "MAILMAN_API_USER",
+                     "MAILMAN_API_PASS")
     for req_key in required_keys:
         try:
             getattr(settings, req_key)
diff --git a/kittystore/storm/model.py b/kittystore/storm/model.py
index 90cbe18..f7bb82e 100644
--- a/kittystore/storm/model.py
+++ b/kittystore/storm/model.py
@@ -261,3 +261,20 @@ class Category(Storm):
 
     def __init__(self, name):
         self.name = unicode(name)
+
+
+class UserAddress(Storm):
+    """
+    The link between an email address and the Mailman user UUID.
+    """
+    __storm_table__ = "user_address"
+    __storm_primary__ = "user_id", "address"
+
+    user_id = Unicode()
+    address = Unicode()
+    emails = ReferenceSet(address, Email.sender_email,
+                          order_by=Email.date)
+
+    def __init__(self, user_id, address):
+        self.user_id = unicode(user_id)
+        self.address = unicode(address)
diff --git a/kittystore/storm/schema/__init__.py b/kittystore/storm/schema/__init__.py
index 7220b38..89f1df6 100644
--- a/kittystore/storm/schema/__init__.py
+++ b/kittystore/storm/schema/__init__.py
@@ -61,6 +61,11 @@ CREATES = {
             PRIMARY KEY (list_name, message_id, counter),
             FOREIGN KEY (list_name, message_id)
                 REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
+        CREATE TABLE "user_address" (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
         );""",
         'CREATE INDEX "ix_email_list_name" ON "email" (list_name);',
         'CREATE INDEX "ix_email_date" ON "email" (date);',
@@ -71,6 +76,8 @@ CREATES = {
         'CREATE INDEX "ix_email_thread_order" ON "email" (thread_order);',
         'CREATE INDEX "ix_thread_date_active" ON "thread" (date_active);',
         'CREATE UNIQUE INDEX "ix_category_name" ON "category" (name);',
+        'CREATE INDEX "ix_user_address_user_id" ON "user_address" (user_id);',
+        'CREATE UNIQUE INDEX "ix_user_address_address" ON "user_address" (address);',
         ],
 
     "postgres": [ """
@@ -142,6 +149,11 @@ CREATES = {
             PRIMARY KEY (list_name, message_id, counter),
             FOREIGN KEY (list_name, message_id)
                 REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
+        CREATE TABLE "user_address" (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
         );""",
         'CREATE INDEX "ix_email_list_name" ON "email" USING btree (list_name);',
         'CREATE INDEX "ix_email_date" ON "email" USING btree (date);',
@@ -152,6 +164,8 @@ CREATES = {
         'CREATE INDEX "ix_email_thread_order" ON "email" USING btree (thread_order);',
         'CREATE INDEX "ix_thread_date_active" ON "thread" USING btree (date_active);',
         'CREATE UNIQUE INDEX "ix_category_name" ON "category" USING btree (name);',
+        'CREATE INDEX "ix_user_address_user_id" ON "user_address" USING btree (user_id);',
+        'CREATE UNIQUE INDEX "ix_user_address_address" ON "user_address" USING btree (address);',
         ],
 
     "mysql": [ """
@@ -213,6 +227,11 @@ CREATES = {
             PRIMARY KEY (list_name, message_id, counter),
             FOREIGN KEY (list_name, message_id)
                 REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
+        CREATE TABLE `user_address` (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
         );""",
         'CREATE INDEX `ix_email_list_name` ON `email` (list_name);',
         'CREATE INDEX `ix_email_date` ON `email` (date);',
@@ -223,6 +242,8 @@ CREATES = {
         'CREATE INDEX `ix_email_thread_order` ON `email` (thread_order);',
         'CREATE INDEX `ix_thread_date_active` ON `thread` (date_active);',
         'CREATE UNIQUE INDEX `ix_category_name` ON `category` (name);',
+        'CREATE INDEX `ix_user_address_user_id` ON `user_address` (user_id);',
+        'CREATE UNIQUE INDEX `ix_user_address_address` ON `user_address` (address);',
         ],
 
 }
diff --git a/kittystore/storm/schema/patch_10.py b/kittystore/storm/schema/patch_10.py
new file mode 100644
index 0000000..f04f8e8
--- /dev/null
+++ b/kittystore/storm/schema/patch_10.py
@@ -0,0 +1,44 @@
+# -*- coding: utf-8 -*-
+
+from __future__ import absolute_import
+
+from . import get_db_type
+
+
+SQL = {
+    "sqlite": ["""
+        CREATE TABLE "user_address" (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
+        );""",
+        'CREATE INDEX "ix_user_address_user_id" ON "user_address" (user_id);',
+        'CREATE UNIQUE INDEX "ix_user_address_address" ON "user_address" (address);',
+        ],
+    "postgres": ["""
+        CREATE TABLE "user_address" (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
+        );""",
+        'CREATE INDEX "ix_user_address_user_id" ON "user_address" USING btree (user_id);',
+        'CREATE UNIQUE INDEX "ix_user_address_address" ON "user_address" USING btree (address);',
+        ],
+    "mysql": ["""
+        CREATE TABLE `user_address` (
+            user_id VARCHAR(255) NOT NULL,
+            address VARCHAR(255) NOT NULL,
+            PRIMARY KEY (user_id, address)
+        );""",
+        'CREATE INDEX `ix_user_address_user_id` ON `user_address` (user_id);',
+        'CREATE UNIQUE INDEX `ix_user_address_address` ON `user_address` (address);',
+        ],
+    }
+
+
+def apply(store):
+    """Add the user_address table"""
+    dbtype = get_db_type(store)
+    for statement in SQL[dbtype]:
+        store.execute(statement)
+    store.commit()
diff --git a/kittystore/storm/store.py b/kittystore/storm/store.py
index 4d09a1b..172242a 100644
--- a/kittystore/storm/store.py
+++ b/kittystore/storm/store.py
@@ -16,12 +16,14 @@ from __future__ import absolute_import
 
 import datetime
 from email.utils import unquote
+from urllib2 import HTTPError
 
 from zope.interface import implements
 from mailman.interfaces.messages import IMessageStore
 from storm.locals import Desc
 from storm.expr import And, Or, Count, Alias
 from dateutil.tz import tzutc
+import mailmanclient
 
 from kittystore import MessageNotFound
 from kittystore.utils import parseaddr, parsedate
@@ -30,7 +32,8 @@ from kittystore.scrub import Scrubber
 from kittystore.utils import get_ref_and_thread_id
 from kittystore.analysis import compute_thread_order_and_depth
 
-from .model import List, Email, Attachment, Thread, EmailFull, Category
+from .model import (List, Email, Attachment, Thread, EmailFull, Category,
+                    UserAddress)
 
 
 class StormStore(object):
@@ -139,6 +142,23 @@ class StormStore(object):
         # warning: scrubbing modifies the msg in-place
         email.content, attachments = scrubber.scrub()
 
+        # get the Mailman user
+        try:
+            mm_client = mailmanclient.Client('%s/3.0' %
+                            self.settings.MAILMAN_REST_SERVER,
+                            self.settings.MAILMAN_API_USER,
+                            self.settings.MAILMAN_API_PASS)
+            mm_user = mm_client.get_user(email.sender_email)
+        except (HTTPError, mailmanclient.MailmanConnectionError), e:
+            if self.debug:
+                print "Can't get the user from Mailman: %s" % e
+        else:
+            user_already_there = self.db.find(UserAddress,
+                    address=email.sender_email).count()
+            if not user_already_there:
+                user = UserAddress(mm_user.user_id, email.sender_email)
+                self.db.add(user)
+
         #category = 'Question' # TODO: enum + i18n ?
         #if ('agenda' in message.get('Subject', '').lower() or
         #        'reminder' in message.get('Subject', '').lower()):
diff --git a/requirements.txt b/requirements.txt
index 7ea03fa..377334b 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -5,3 +5,4 @@ python-dateutil < 2.0 # 2.0+ is for Python 3
 mock
 networkx
 Whoosh
+mailmanclient