Improved model ... indizes

1 files changed, 16 insertions, 29 deletions

diff --git a/model.py b/model.py
index 867849f..ea20a28 100644
--- a/model.py
+++ b/model.py
@@ -1,8 +1,9 @@
 import elixir
 from elixir import Field, ManyToOne, OneToMany, OneToOne, ColumnProperty, using_options, using_options_defaults
 from sqlalchemy import types as T
-from sqlalchemy import sql
+from sqlalchemy import sql, Index
 
+import datetime
 from functools import partial
 from collections import namedtuple
 
@@ -52,10 +53,9 @@ class Expense (Entity):
     category    = ManyToOne('Category', required = True, innerjoin = True)
 
 class SingleExpense (Expense):
-    date = ReqField(T.Date)
-    
-    year = ColumnProperty(lambda c: sql.extract('year', c.date))
-    month = ColumnProperty(lambda c: sql.extract('month', c.date))
+    year = ReqField(T.Integer)
+    month = ReqField(T.SmallInteger)
+    day = ReqField(T.SmallInteger)
 
     @classmethod
     def of_month (cls, month, year):
@@ -66,38 +66,19 @@ class SingleExpense (Expense):
         return cls.query.filter(comp)
 
 class ConstExpense (Expense):
-    months  = ReqField(T.Integer)
-    start   = ReqField(T.Date)
-    end     = ReqField(T.Date)
+    months  = ReqField(T.SmallInteger)
+    start   = ReqField(T.Date, index = True)
+    end     = ReqField(T.Date, index = True)
 
     next = OneToOne('ConstExpense', inverse = 'prev')
     prev = ManyToOne('ConstExpense')
     
     monthly = ColumnProperty(lambda c: sql.cast(c.expense / c.months, ExpNum), deferred = True)
     
-    start_year = ColumnProperty(lambda c: sql.extract('year', c.start))
-    start_month = ColumnProperty(lambda c: sql.extract('month', c.start))
-    
-    end_year = ColumnProperty(lambda c: sql.extract('year', c.end))
-    end_month = ColumnProperty(lambda c: sql.extract('month', c.end))
-
     @classmethod
     def of_month (cls, month, year):
-        c1 = sql.or_(
-                    cls.start_year < year,
-                    sql.and_(
-                        cls.start_year == year,
-                        cls.start_month <= month
-                    ))
-
-        c2 = sql.or_(
-                    cls.end_year > year,
-                    sql.and_(
-                        cls.end_year == year,
-                        cls.end_month >= month
-                    ))
-
-        return cls.query.filter(sql.and_(c1,c2))
+        d = datetime.date(year, month, 1)
+        return cls.query.filter(sql.between(d, cls.start, cls.end))
 
 #
 # Work entities (not stored in DB)
@@ -122,5 +103,11 @@ elixir.setup_all()
 
 session = elixir.session
 
+#
+# Extra indizes have to be here
+#
+
+Index('idx_single_date', SingleExpense.year, SingleExpense.month)
+
 if __name__ == "__main__":
     elixir.create_all()