diff options
-rw-r--r-- | kittystore/storm/model.py | 35 | ||||
-rw-r--r-- | kittystore/storm/schema/__init__.py | 39 | ||||
-rw-r--r-- | kittystore/storm/schema/patch_8.py | 55 | ||||
-rw-r--r-- | kittystore/storm/store.py | 8 |
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, |