Store the full email in a separate table

5 files changed, 106 insertions, 11 deletions

diff --git a/kittystore/storm/model.py b/kittystore/storm/model.py
index 1ff16ed..10dfe1a 100644
--- a/kittystore/storm/model.py
+++ b/kittystore/storm/model.py
@@ -15,7 +15,7 @@ license.
 import datetime
 
 from zope.interface import implements
-from storm.locals import Unicode, RawStr, Int, ReferenceSet, Reference
+from storm.locals import Unicode, RawStr, Int, ReferenceSet, Reference, Proxy
 from storm.locals import Storm
 from storm.expr import Desc
 from mailman.interfaces.messages import IMessage
@@ -73,7 +73,6 @@ class Email(Storm):
     in_reply_to = Unicode()
     message_id_hash = Unicode()
     thread_id = Unicode()
-    full = RawStr()
     archived_date = DateTime(default_factory=datetime.datetime.now)
     # path is required by IMessage, but it makes no sense here
     path = None
@@ -87,6 +86,9 @@ class Email(Storm):
                     )
     thread = Reference((list_name, thread_id),
                        ("Thread.list_name", "Thread.thread_id"))
+    full_email = Reference((list_name, message_id),
+                     ("EmailFull.list_name", "EmailFull.message_id"))
+    full = Proxy(full_email, "EmailFull.full")
 
     def __init__(self, list_name, message_id):
         self.list_name = unicode(list_name)
@@ -94,6 +96,27 @@ class Email(Storm):
         self.message_id_hash = unicode(get_message_id_hash(self.message_id))
 
 
+class EmailFull(Storm):
+    """
+    The full contents of an archived email, for storage and post-processing
+    reasons.
+    """
+    __storm_table__ = "email_full"
+    __storm_primary__ = "list_name", "message_id"
+
+    list_name = Unicode()
+    message_id = Unicode()
+    full = RawStr()
+    email = Reference((list_name, message_id),
+                     ("Email.list_name", "Email.message_id"))
+
+    def __init__(self, list_name, message_id, full=None):
+        self.list_name = unicode(list_name)
+        self.message_id = unicode(message_id)
+        if full is not None:
+            self.full = full
+
+
 class Attachment(Storm):
 
     __storm_table__ = "attachment"
diff --git a/kittystore/storm/schema/__init__.py b/kittystore/storm/schema/__init__.py
index 0302d74..76c53f8 100644
--- a/kittystore/storm/schema/__init__.py
+++ b/kittystore/storm/schema/__init__.py
@@ -27,12 +27,19 @@ CREATES = {
             in_reply_to VARCHAR(255), -- How about replies from another list ?
             message_id_hash VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
-            "full" BLOB NOT NULL,
             archived_date DATETIME DEFAULT CURRENT_TIMESTAMP,
             PRIMARY KEY (list_name, message_id),
             FOREIGN KEY (list_name, thread_id)
                 REFERENCES thread(list_name, thread_id) ON DELETE CASCADE
         );""", """
+        CREATE TABLE "email_full" (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            "full" BLOB NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
         CREATE TABLE "attachment" (
             list_name VARCHAR(255) NOT NULL,
             message_id VARCHAR(255) NOT NULL,
@@ -78,12 +85,19 @@ CREATES = {
             in_reply_to VARCHAR(255), -- How about replies from another list ?
             message_id_hash VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
-            "full" BYTEA NOT NULL,
             archived_date TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
             PRIMARY KEY (list_name, message_id),
             FOREIGN KEY (list_name, thread_id)
                 REFERENCES thread(list_name, thread_id) ON DELETE CASCADE
         );""", """
+        CREATE TABLE "email_full" (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            "full" BYTEA NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
         CREATE TABLE "attachment" (
             list_name VARCHAR(255) NOT NULL,
             message_id VARCHAR(255) NOT NULL,
@@ -129,12 +143,19 @@ CREATES = {
             in_reply_to VARCHAR(255), -- How about replies from another list ?
             message_id_hash VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
-            `full` BLOB NOT NULL,
             archived_date DATETIME,
             PRIMARY KEY (list_name, message_id),
             FOREIGN KEY (list_name, thread_id)
                 REFERENCES thread(list_name, thread_id) ON DELETE CASCADE
         );""", """
+        CREATE TABLE `email_full` (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            `full` BLOB NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""", """
         CREATE TABLE `attachment` (
             list_name VARCHAR(255) NOT NULL,
             message_id VARCHAR(255) NOT NULL,
diff --git a/kittystore/storm/schema/patch_3.py b/kittystore/storm/schema/patch_3.py
index c7deb78..e7bc8e0 100644
--- a/kittystore/storm/schema/patch_3.py
+++ b/kittystore/storm/schema/patch_3.py
@@ -2,11 +2,7 @@
 
 from __future__ import absolute_import
 
-#from storm.expr import And
-#from storm.locals import Desc
-
 from . import get_db_type
-#from kittystore.storm.model import Thread, Email
 
 
 SQL = {
diff --git a/kittystore/storm/schema/patch_4.py b/kittystore/storm/schema/patch_4.py
new file mode 100644
index 0000000..5067a48
--- /dev/null
+++ b/kittystore/storm/schema/patch_4.py
@@ -0,0 +1,53 @@
+# -*- coding: utf-8 -*-
+
+from __future__ import absolute_import
+
+from . import get_db_type
+
+
+SQL = {
+    "sqlite": [ """
+        CREATE TABLE "email_full" (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            "full" BLOB NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""",
+        'INSERT INTO "email_full" SELECT list_name, message_id, "full" FROM "email";'
+        # No 'ALTER TABLE DROP COLUMN' in SQLite
+        ],
+    "postgres": [ """
+        CREATE TABLE "email_full" (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            "full" BYTEA NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""",
+        'INSERT INTO "email_full" SELECT list_name, message_id, "full" FROM "email";'
+        'ALTER TABLE "email" DROP COLUMN "full";',
+        ],
+    "mysql": [ """
+        CREATE TABLE `email_full` (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            `full` BLOB NOT NULL,
+            PRIMARY KEY (list_name, message_id),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""",
+        'INSERT INTO `email_full` SELECT list_name, message_id, `full` FROM `email`;'
+        ],
+    }
+
+
+def apply(store):
+    """Add the thread 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 272907b..3cb3db0 100644
--- a/kittystore/storm/store.py
+++ b/kittystore/storm/store.py
@@ -29,7 +29,7 @@ from kittystore.utils import header_to_unicode
 from kittystore.scrub import Scrubber
 from kittystore.utils import get_ref_and_thread_id
 
-from .model import List, Email, Attachment, Thread
+from .model import List, Email, Attachment, Thread, EmailFull
 
 
 class StormStore(object):
@@ -99,6 +99,8 @@ class StormStore(object):
                    (message['From'], message.get('Subject', '""')))
             return email.message_id_hash
 
+        # the message.as_string() call must be done before scrubbing
+        email_full = EmailFull(list_name, msg_id, message.as_string())
         # Find thread id
         new_thread = False
         ref, thread_id = get_ref_and_thread_id(message, list_name, self)
@@ -114,7 +116,6 @@ class StormStore(object):
         email.sender_name = from_name.strip()
         email.sender_email = unicode(from_email).strip()
         email.subject = header_to_unicode(message.get('Subject'))
-        email.full = message.as_string() # Before scrubbing
         msg_date = parsedate(message.get("Date"))
         if msg_date is None:
             # Absent or unparseable date
@@ -151,6 +152,7 @@ class StormStore(object):
         self.db.add(thread)
 
         self.db.add(email)
+        self.db.add(email_full)
         self.flush()
         for attachment in attachments:
             self.add_attachment(list_name, msg_id, *attachment)