Add MySQL support

This requires splitting the timezone offset into a separate column,
since MySQL does not have a timezone-aware datetime data type.

5 files changed, 106 insertions, 9 deletions

diff --git a/kittystore/storm/model.py b/kittystore/storm/model.py
index 0af038f..458f2fd 100644
--- a/kittystore/storm/model.py
+++ b/kittystore/storm/model.py
@@ -68,6 +68,7 @@ class Email(object):
     subject = Unicode()
     content = Unicode()
     date = DateTime()
+    timezone = Int()
     in_reply_to = Unicode()
     message_id_hash = Unicode()
     thread_id = Unicode()
diff --git a/kittystore/storm/schema/__init__.py b/kittystore/storm/schema/__init__.py
index 50fda8d..0302d74 100644
--- a/kittystore/storm/schema/__init__.py
+++ b/kittystore/storm/schema/__init__.py
@@ -23,6 +23,7 @@ CREATES = {
             subject TEXT NOT NULL,
             content TEXT NOT NULL,
             date DATETIME NOT NULL,
+            timezone INTEGER NOT NULL,
             in_reply_to VARCHAR(255), -- How about replies from another list ?
             message_id_hash VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
@@ -62,7 +63,7 @@ CREATES = {
         CREATE TABLE "thread" (
             list_name VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
-            date_active TIMESTAMP WITH TIME ZONE NOT NULL,
+            date_active TIMESTAMP WITHOUT TIME ZONE NOT NULL,
             PRIMARY KEY (list_name, thread_id)
         );""", """
         CREATE TABLE "email" (
@@ -72,7 +73,8 @@ CREATES = {
             sender_email VARCHAR(255) NOT NULL,
             subject TEXT NOT NULL,
             content TEXT NOT NULL,
-            date TIMESTAMP WITH TIME ZONE NOT NULL,
+            date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
+            timezone INTEGER NOT NULL,
             in_reply_to VARCHAR(255), -- How about replies from another list ?
             message_id_hash VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
@@ -103,6 +105,57 @@ CREATES = {
         'CREATE INDEX "ix_thread_date_active" ON "thread" USING btree (date_active);',
         ],
 
+    "mysql": [ """
+        CREATE TABLE `list` (
+            name VARCHAR(255) NOT NULL,
+            display_name TEXT,
+            PRIMARY KEY (name)
+        );""", """
+        CREATE TABLE `thread` (
+            list_name VARCHAR(255) NOT NULL,
+            thread_id VARCHAR(255) NOT NULL,
+            date_active DATETIME NOT NULL,
+            PRIMARY KEY (list_name, thread_id)
+        );""", """
+        CREATE TABLE `email` (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            sender_name VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
+            sender_email VARCHAR(255) NOT NULL,
+            subject TEXT NOT NULL COLLATE utf8_general_ci,
+            content TEXT NOT NULL COLLATE utf8_general_ci,
+            date DATETIME NOT NULL,
+            timezone INTEGER NOT NULL,
+            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 `attachment` (
+            list_name VARCHAR(255) NOT NULL,
+            message_id VARCHAR(255) NOT NULL,
+            counter INTEGER NOT NULL,
+            content_type VARCHAR(255) NOT NULL,
+            encoding VARCHAR(50),
+            name VARCHAR(255),
+            size INTEGER NOT NULL,
+            content BLOB NOT NULL,
+            PRIMARY KEY (list_name, message_id, counter),
+            FOREIGN KEY (list_name, message_id)
+                REFERENCES email(list_name, message_id) ON DELETE CASCADE
+        );""",
+        'CREATE INDEX `ix_email_list_name` ON `email` (list_name);',
+        'CREATE INDEX `ix_email_date` ON `email` (date);',
+        'CREATE UNIQUE INDEX `ix_email_list_name_message_id_hash` ON `email` (list_name, message_id_hash);',
+        'CREATE INDEX `ix_email_subject` ON `email` (subject(255));',
+        'CREATE INDEX `ix_email_thread_id` ON `email` (thread_id);',
+        'CREATE INDEX `ix_thread_date_active` ON `thread` (date_active);',
+        ],
+
 }
 
 
diff --git a/kittystore/storm/schema/patch_2.py b/kittystore/storm/schema/patch_2.py
index 8a96df1..b65dbb5 100644
--- a/kittystore/storm/schema/patch_2.py
+++ b/kittystore/storm/schema/patch_2.py
@@ -28,6 +28,7 @@ SQL = {
         );""",
         'CREATE INDEX "ix_thread_date_active" ON "thread" USING btree (date_active);',
         ],
+    "mysql": [],
     }
 
 
diff --git a/kittystore/storm/schema/patch_3.py b/kittystore/storm/schema/patch_3.py
new file mode 100644
index 0000000..c7deb78
--- /dev/null
+++ b/kittystore/storm/schema/patch_3.py
@@ -0,0 +1,33 @@
+# -*- coding: utf-8 -*-
+
+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 = {
+    "sqlite": [
+        'ALTER TABLE "email" ADD COLUMN "timezone" INTEGER NOT NULL DEFAULT 0;',
+        ],
+    "postgres": [
+        'ALTER TABLE "email" ADD COLUMN "timezone" INTEGER;',
+        'UPDATE "email" SET "timezone" = EXTRACT(TIMEZONE_MINUTE FROM date)',
+        'UPDATE "email" SET date = date AT TIME ZONE \'UTC\';',
+        'ALTER TABLE "email" ALTER COLUMN "date" TYPE TIMESTAMP WITHOUT TIME ZONE;',
+        'ALTER TABLE "email" ALTER COLUMN "timezone" SET NOT NULL;',
+        'ALTER TABLE "thread" ALTER COLUMN "date_active" TYPE TIMESTAMP WITHOUT TIME ZONE;',
+        ],
+    "mysql": [],
+    }
+
+
+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 5d1f8ed..75983d8 100644
--- a/kittystore/storm/store.py
+++ b/kittystore/storm/store.py
@@ -17,17 +17,18 @@ from __future__ import absolute_import
 import datetime
 from email.utils import unquote
 
+from zope.interface import implements
+from mailman.interfaces.messages import IMessageStore
+from storm.locals import Desc
+from storm.expr import And, Or
+from dateutil.tz import tzutc
+
 from kittystore import MessageNotFound
 from kittystore.utils import parseaddr, parsedate
 from kittystore.utils import header_to_unicode
 from kittystore.scrub import Scrubber
 from kittystore.utils import get_ref_and_thread_id
 
-from zope.interface import implements
-from mailman.interfaces.messages import IMessageStore
-from storm.locals import Desc
-from storm.expr import And, Or
-
 from .model import List, Email, Attachment, Thread
 
 
@@ -116,10 +117,18 @@ class StormStore(object):
         email.full = message.as_string() # Before scrubbing
         scrubber = Scrubber(list_name, message, self)
         email.content = scrubber.scrub() # warning: modifies the msg in-place
-        email.date = parsedate(message.get("Date"))
-        if email.date is None:
+        msg_date = parsedate(message.get("Date"))
+        if msg_date is None:
             # Absent or unparseable date
             email.date = datetime.datetime.now()
+        email.date = msg_date.astimezone(tzutc()).replace(tzinfo=None)
+        utcoffset = msg_date.utcoffset()
+        if utcoffset is None:
+            email.timezone = 0
+        else:
+            # in minutes
+            email.timezone = ( (utcoffset.days * 24 * 60 * 60)
+                               + utcoffset.seconds) / 60
 
         #category = 'Question' # TODO: enum + i18n ?
         #if ('agenda' in message.get('Subject', '').lower() or