Remove dependency on elixir

3 files changed, 119 insertions, 70 deletions

diff --git a/controller.py b/controller.py
index 39e8d7a..66039ac 100644
--- a/controller.py
+++ b/controller.py
@@ -12,7 +12,7 @@ from renderer import render
 import datetime, decimal
 import itertools as iter
 import operator as op
-from sqlalchemy import sql
+from sqlalchemy import sql, func
 
 class Show:
     def GET(self, year = '', month = ''):
@@ -35,7 +35,7 @@ class Show:
         year = int(year)
         month = int(month)
 
-        ssum = sql.functions.sum(SingleExpense.expense)
+        ssum = func.sum(SingleExpense.expense)
         query = SingleExpense.of_month(month, year)
 
         result = query.group_by(SingleExpense.category_id).\
@@ -119,7 +119,13 @@ class Edit (Add):
         exp = SingleExpense.get(id)
         self.get_expense = lambda *x: exp
         
-        fvalues = { "date" : exp.date.strftime(self.dformat), "expense" : str(exp.expense), "description" : exp.description, "category" : exp.category.name }
+        fvalues = {
+                    "date" : exp.date.strftime(self.dformat),
+                    "expense" : str(exp.expense),
+                    "description" : exp.description,
+                    "category" : exp.category.name
+                  }
+
         f = self.form()
         f.fill(fvalues)
 
@@ -140,15 +146,19 @@ class Const:
         if id is None:           
             d = datetime.date.today()
 
-            current = ConstExpense.of_month(d.month, d.year)
+            expenses = ConstExpense.query.order_by(ConstExpense.start).all()
             
-            csub = current.subquery()
+            current = []
+            old = []
             
-            # if only mysql would allow "EXCEPT" -.-
-            old = ConstExpense.query.outerjoin((csub, csub.c.id == ConstExpense.id)).filter(csub.c.id == None)
-            old = old.order_by(ConstExpense.start)
-
-            return render("constlist", current = current.order_by(ConstExpense.start).all(), old = old.all())
+            for e in expenses:
+                if e.start <= d:
+                    if e.end >= d:
+                        current.append(e)
+                    else:
+                        old.append(e)
+
+            return render("constlist", current = current, old = old)
         
         else:
             exp = ConstExpense.get(id)
@@ -194,7 +204,9 @@ class ConstAdd:
             return render(self.template, form = f)
 
     def get_expense(self):
-        return ConstExpense()
+        ce = ConstExpense()
+        web.ctx.orm.add(ce)
+        return ce
 
     def form_fill(self, exp):
         return {
@@ -211,8 +223,8 @@ class ConstAdd:
         CE = ConstExpense
 
         # get the list of 'previous' expenses
+        cur = self.get_expense() # need to be before the next stmt, else 'next' might be undefined
         filter = (CE.next == None)
-        cur = self.get_expense()
 
         if cur.id is None: # empty
             web.ctx.orm.expunge(cur)
@@ -260,7 +272,7 @@ class ConstAdd:
                     ),
                 form.Dropdown(
                     "category",
-                    map(op.itemgetter(0), sorted(Category.query.values(Category.name))),
+                    map(op.itemgetter(0), Category.query.order_by(Category.name).values(Category.name)),
                     description = "Kategorie"
                     ),
                 form.Dropdown(
diff --git a/index.py b/index.py
index a7d32d9..d4ec756 100755
--- a/index.py
+++ b/index.py
@@ -24,19 +24,22 @@ urls = (
 # ORM
 #
 def handle_sql(handler):
-    web.ctx.orm = model.session
-    
+    web.ctx.orm = model.Session()
+
     try:
-        h = handler()
-    except web.HTTPError:
-        web.ctx.orm.commit()
-        raise
-    except:
-        web.ctx.orm.rollback()
-        raise
-    else:
-        web.ctx.orm.commit()
-        return h
+        try:
+            h = handler()
+        except web.HTTPError:
+            web.ctx.orm.commit()
+            raise
+        except:
+            web.ctx.orm.rollback()
+            raise
+        else:
+            web.ctx.orm.commit()
+            return h
+    finally:
+        model.Session.remove()
 
 #
 # Check for mobile (at least somewhat)
diff --git a/model.py b/model.py
index cf8c593..7a07ff0 100644
--- a/model.py
+++ b/model.py
@@ -1,42 +1,68 @@
-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, Index
+from sqlalchemy import sql, Index, Column, ForeignKey, create_engine
+from sqlalchemy.orm import relationship, backref, scoped_session, sessionmaker,\
+                           column_property
+from sqlalchemy.ext.declarative import declarative_base, declared_attr
+from sqlalchemy.ext.hybrid import hybrid_property
 
 import datetime
+import decimal
 from functools import partial
 from collections import namedtuple
 
-__all__ = ["Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense"]
+__all__ = ["Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense",
+           "Session"]
 
 #
 # DB Setup
 #
 
-elixir.metadata.bind = "sqlite:///test.sqlite"
-elixir.metadata.bind.echo = True
+engine = create_engine("sqlite:///test.sqlite")
+engine.echo = True
+
+Session = scoped_session(sessionmaker(bind=engine))
 
 #
 # Global definitions
 #
 
-ReqField = partial(Field, required = True)
-ExpNum = T.Numeric(scale = 2, precision = 10)
+class Base(object):
+    @declared_attr
+    def __tablename__ (cls):
+        return cls.__name__.lower()
+
+    id = Column(T.Integer, primary_key=True)
+
+    query = Session.query_property()
+
+    @classmethod
+    def get_by (cls, *args, **kwargs):
+        return cls.query.filter_by(*args, **kwargs).first()
+
+    @classmethod
+    def get (cls, *args, **kwargs):
+        return cls.query.get(*args, **kwargs)
+
 
-class Entity (elixir.Entity):
-    using_options(abstract = True)
+Base = declarative_base(cls=Base)
+
+ReqColumn = partial(Column, nullable = False)
+ExpNum = T.Numeric(scale = 2, precision = 10)
 
-    using_options_defaults(shortnames = True)
+def to_exp(d):
+    """Converts decimal into expense"""
+    return d.quantize(decimal.Decimal('.01'), rounding = decimal.ROUND_UP)
 
 #
 # Database Entities
 #
 
-class Category (Entity):
-    name = Field(T.Unicode(50), unique = True)
+class Category (Base):
+    name = ReqColumn(T.Unicode(50), unique = True)
+    parent_id = Column(T.Integer, ForeignKey('category.id'))
 
-    parent = ManyToOne('Category')
-    children = OneToMany('Category')
+    children = relationship('Category',
+                            backref=backref('parent', remote_side="Category.id"))
 
     def __repr__ (self):
         if self.parent:
@@ -44,17 +70,24 @@ class Category (Entity):
         else:
             return '<Category "%s">' % self.name
 
-class Expense (Entity):
-    using_options(abstract = True)
+class Expense (Base):
+    __abstract__ = True
 
-    description = Field(T.Unicode(50))
-    expense     = ReqField(ExpNum)
-    category    = ManyToOne('Category', required = True, innerjoin = True)
+    description = Column(T.Unicode(50))
+    expense     = ReqColumn(ExpNum)
+
+    @declared_attr
+    def category_id(cls):
+        return ReqColumn(T.Integer, ForeignKey(Category.id))
+
+    @declared_attr
+    def category(cls):
+        return relationship(Category, innerjoin = True)
 
 class SingleExpense (Expense):
-    year = ReqField(T.Integer)
-    month = ReqField(T.SmallInteger)
-    day = ReqField(T.SmallInteger)
+    year = ReqColumn(T.Integer)
+    month = ReqColumn(T.SmallInteger)
+    day = ReqColumn(T.SmallInteger)
 
     @classmethod
     def of_month (cls, month, year):
@@ -75,14 +108,17 @@ class SingleExpense (Expense):
         self.day = d.day
 
 class ConstExpense (Expense):
-    months  = ReqField(T.SmallInteger)
-    start   = ReqField(T.Date, index = True)
-    end     = ReqField(T.Date, index = True)
+    months  = ReqColumn(T.SmallInteger)
+    start   = ReqColumn(T.Date, index = True)
+    end     = ReqColumn(T.Date, index = True)
+    prev_id = Column(T.Integer, ForeignKey('constexpense.id'))
 
-    prev = ManyToOne('ConstExpense', ondelete = "SET NULL")
-    next = OneToOne('ConstExpense', inverse = 'prev')
-    
-    monthly = ColumnProperty(lambda c: sql.cast(c.expense / c.months, ExpNum))
+    prev = relationship('ConstExpense', remote_side = "ConstExpense.id", uselist = False,
+                         backref=backref('next', uselist = False))
+
+    @property
+    def monthly(self):
+        return to_exp(self.expense / self.months)
     
     @classmethod
     def of_month (cls, month, year):
@@ -100,16 +136,22 @@ class CatExpense (namedtuple('CatExpense', 'cat expense exps')):
         return self.exps.order_by(SingleExpense.day).all()
 
 class MonthExpense (namedtuple('MonthExpense', 'date catexps')):
-    __slots__ = ()
     
-    @property
-    def constsum (self):
-        c = ConstExpense.of_month(self.date.month, self.date.year)
-        return c.value(sql.functions.sum(ConstExpense.monthly)) or 0
+    def __init__ (self, *args, **kwargs):
+        self._consts = None
+        super(MonthExpense, self).__init__(*args, **kwargs)
 
     @property
     def consts (self):
-        return ConstExpense.of_month(self.date.month, self.date.year).all()
+        if self._consts is None:
+            self._consts = ConstExpense.of_month(self.date.month, self.date.year).all()
+
+        return self._consts
+
+    @property
+    def constsum (self):
+        s = sum(c.monthly for c in self.consts)
+        return s or 0
 
     @property
     def sum (self):
@@ -123,14 +165,6 @@ class MonthExpense (namedtuple('MonthExpense', 'date catexps')):
         return '<MonthExpense of "%s": %s>' % (self.date, self.sum)
 
 #
-# Rest
-#
-
-elixir.setup_all()
-
-session = elixir.session
-
-#
 # Extra indizes have to be here
 #
 
@@ -138,4 +172,4 @@ Index('idx_single_date', SingleExpense.year, SingleExpense.month)
 Index('idx_start_end', ConstExpense.start, ConstExpense.end)
 
 if __name__ == "__main__":
-    elixir.create_all()
+    Base.metadata.create_all(engine)