Add categories to threads

4 files changed, 129 insertions, 8 deletions

diff --git a/kittystore/storm/model.py b/kittystore/storm/model.py
index 86dbe89..4c9cf06 100644
--- a/kittystore/storm/model.py
+++ b/kittystore/storm/model.py
@@ -37,10 +37,6 @@ class List(Storm):
     # pylint: disable-msg=E0102
     """
     An archived mailing-list.
-
-    Not strictly necessary yet since the list name is used in the email table,
-    but at some point we'll want to store more information on lists in the
-    database.
     """
 
     __storm_table__ = "list"
@@ -150,6 +146,7 @@ class Thread(Storm):
     list_name = Unicode()
     thread_id = Unicode()
     date_active = DateTime()
+    category_id = Int()
     emails = ReferenceSet(
                 (list_name, thread_id),
                 (Email.list_name, Email.thread_id),
@@ -160,6 +157,7 @@ class Thread(Storm):
                 (Email.list_name, Email.thread_id),
                 order_by=Email.thread_order
              )
+    category_obj = Reference(category_id, "Category.id")
     _starting_email = None
 
     def __init__(self, list_name, thread_id, date_active=None):
@@ -220,6 +218,20 @@ class Thread(Storm):
     def replies_after(self, date):
         return self.emails.find(Email.date > date)
 
+    def _get_category(self):
+        if not self.category_obj:
+            return None
+        return self.category_obj.name
+    def _set_category(self, name):
+        if not name:
+            self.category_id = None
+            return
+        # XXX: this is VERY hackish
+        store = self.__storm_object_info__["store"]
+        category = store.find(Category, Category.name == name).one()
+        self.category_id = category.id
+    category = property(_get_category, _set_category)
+
     def __storm_pre_flush__(self):
         """Auto-set the active date from the last email in thread"""
         if self.date_active is not None:
@@ -230,3 +242,18 @@ class Thread(Storm):
             self.date_active = email_dates[0]
         else:
             self.date_active = datetime.datetime.now()
+
+
+class Category(Storm):
+    """
+    A thread category
+    """
+
+    __storm_table__ = "category"
+
+    id = Int(primary=True)
+    name = Unicode()
+    threads = ReferenceSet(id, Thread.category_id)
+
+    def __init__(self, name):
+        self.name = unicode(name)
diff --git a/kittystore/storm/schema/__init__.py b/kittystore/storm/schema/__init__.py
index 5f5deab..6dc2f4c 100644
--- a/kittystore/storm/schema/__init__.py
+++ b/kittystore/storm/schema/__init__.py
@@ -14,7 +14,13 @@ CREATES = {
             list_name VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
             date_active DATETIME NOT NULL,
-            PRIMARY KEY (list_name, thread_id)
+            category_id INTEGER,
+            PRIMARY KEY (list_name, thread_id),
+            FOREIGN KEY (category_id) REFERENCES category(id)
+        );""", """
+        CREATE TABLE "category" (
+            id INTEGER NOT NULL PRIMARY KEY,
+            name VARCHAR(255) NOT NULL
         );""", """
         CREATE TABLE "email" (
             list_name VARCHAR(255) NOT NULL,
@@ -63,6 +69,7 @@ CREATES = {
         'CREATE INDEX "ix_email_thread_id" ON "email" (thread_id);',
         '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);',
         ],
 
     "postgres": [ """
@@ -76,8 +83,25 @@ CREATES = {
             list_name VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
             date_active TIMESTAMP WITHOUT TIME ZONE NOT NULL,
-            PRIMARY KEY (list_name, thread_id)
+            category_id INTEGER,
+            PRIMARY KEY (list_name, thread_id),
+            FOREIGN KEY (category_id) REFERENCES category(id)
+        );""", """
+        CREATE TABLE "category" (
+            id INTEGER NOT NULL,
+            name VARCHAR(255) NOT NULL,
+            PRIMARY KEY (id)
         );""", """
+        CREATE SEQUENCE category_id_seq
+            START WITH 1
+            INCREMENT BY 1
+            NO MAXVALUE
+            NO MINVALUE
+            CACHE 1
+        ;""",
+        "ALTER SEQUENCE category_id_seq OWNED BY category.id;",
+        "ALTER TABLE ONLY category ALTER COLUMN id SET DEFAULT nextval('category_id_seq'::regclass);",
+        """
         CREATE TABLE "email" (
             list_name VARCHAR(255) NOT NULL,
             message_id VARCHAR(255) NOT NULL,
@@ -125,6 +149,7 @@ CREATES = {
         'CREATE INDEX "ix_email_thread_id" ON "email" USING btree (thread_id);',
         '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);',
         ],
 
     "mysql": [ """
@@ -138,7 +163,14 @@ CREATES = {
             list_name VARCHAR(255) NOT NULL,
             thread_id VARCHAR(255) NOT NULL,
             date_active DATETIME NOT NULL,
-            PRIMARY KEY (list_name, thread_id)
+            category_id INTEGER,
+            PRIMARY KEY (list_name, thread_id),
+            FOREIGN KEY (category_id) REFERENCES category(id)
+        );""", """
+        CREATE TABLE `category` (
+            id INTEGER NOT NULL AUTO_INCREMENT,
+            name VARCHAR(255) NOT NULL,
+            PRIMARY KEY (id)
         );""", """
         CREATE TABLE `email` (
             list_name VARCHAR(255) NOT NULL,
@@ -187,6 +219,7 @@ CREATES = {
         'CREATE INDEX `ix_email_thread_id` ON `email` (thread_id);',
         '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);',
         ],
 
 }
diff --git a/kittystore/storm/schema/patch_8.py b/kittystore/storm/schema/patch_8.py
new file mode 100644
index 0000000..a70c68a
--- /dev/null
+++ b/kittystore/storm/schema/patch_8.py
@@ -0,0 +1,55 @@
+# -*- coding: utf-8 -*-
+
+from __future__ import absolute_import
+
+from . import get_db_type
+
+
+SQL = {
+    "sqlite": ["""
+        CREATE TABLE "category" (
+            id INTEGER NOT NULL,
+            name VARCHAR(255) NOT NULL,
+            PRIMARY KEY (id)
+        );""",
+        'ALTER TABLE "thread" ADD COLUMN category_id INTEGER;',
+        'CREATE UNIQUE INDEX "ix_category_name" ON "category" (name);',
+        ],
+    "postgres": ["""
+        CREATE TABLE "category" (
+            id INTEGER NOT NULL,
+            name VARCHAR(255) NOT NULL,
+            PRIMARY KEY (id)
+        );""", """
+        CREATE SEQUENCE category_id_seq
+            START WITH 1
+            INCREMENT BY 1
+            NO MAXVALUE
+            NO MINVALUE
+            CACHE 1
+        ;""",
+        "ALTER SEQUENCE category_id_seq OWNED BY category.id;",
+        "ALTER TABLE ONLY category ALTER COLUMN id SET DEFAULT nextval('category_id_seq'::regclass);",
+        'ALTER TABLE "thread" ADD COLUMN category_id INTEGER;',
+        'ALTER TABLE "thread" ADD FOREIGN KEY (category_id) REFERENCES category(id);',
+        'CREATE UNIQUE INDEX "ix_category_name" ON "category" USING btree (name);',
+        ],
+    "mysql": ["""
+        CREATE TABLE `category` (
+            id INTEGER NOT NULL AUTO_INCREMENT,
+            name VARCHAR(255) NOT NULL,
+            PRIMARY KEY (id)
+        );""",
+        'ALTER TABLE `thread` ADD COLUMN category_id INTEGER;',
+        'ALTER TABLE `thread` ADD FOREIGN KEY (category_id) REFERENCES category(id);',
+        'CREATE UNIQUE INDEX `ix_category_name` ON `category` (name);',
+        ],
+    }
+
+
+def apply(store):
+    """Add the subject_prefix column and delete the description column"""
+    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 3b596db..542fd5e 100644
--- a/kittystore/storm/store.py
+++ b/kittystore/storm/store.py
@@ -30,7 +30,7 @@ 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
+from .model import List, Email, Attachment, Thread, EmailFull, Category
 
 
 class StormStore(object):
@@ -513,6 +513,12 @@ class StormStore(object):
         return list(part)
 
 
+    def get_categories(self):
+        """ Return the list of available categories
+        """
+        return list(self.db.find(Category.name).order_by(Category.name))
+
+
     # Attachments
 
     def add_attachment(self, mlist, msg_id, counter, name, content_type,