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 import datetime from functools import partial from collections import namedtuple __all__ = ["Category", "SingleExpense", "ConstExpense", "CatExpense", "MonthExpense", "session"] # # DB Setup # elixir.metadata.bind = "sqlite:///test.sqlite" elixir.metadata.bind.echo = True # # Global definitions # ReqField = partial(Field, required = True) ExpNum = T.Numeric(scale = 2, precision = 10) class Entity (elixir.Entity): using_options(abstract = True) using_options_defaults(shortnames = True) # # Database Entities # class Category (Entity): name = Field(T.Unicode(50), unique = True) parent = ManyToOne('Category') children = OneToMany('Category') def __repr__ (self): if self.parent: return '' % (self.name, self.parent.name) else: return '' % self.name class Expense (Entity): using_options(abstract = True) description = Field(T.Unicode(50)) expense = ReqField(ExpNum) category = ManyToOne('Category', required = True, innerjoin = True) class SingleExpense (Expense): year = ReqField(T.Integer) month = ReqField(T.SmallInteger) day = ReqField(T.SmallInteger) @classmethod def of_month (cls, month, year): comp = sql.and_( cls.month == month, cls.year == year) return cls.query.filter(comp) @property def date (self): return datetime.date(self.year, self.month, self.day) @date.setter def date (self, d): self.year = d.year self.month = d.month self.day = d.day class ConstExpense (Expense): 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)) @classmethod def of_month (cls, month, year): d = datetime.date(year, month, 1) return cls.query.filter(sql.between(d, cls.start, cls.end)) # # Work entities (not stored in DB) # class CatExpense (namedtuple('CatExpense', 'cat expense exps')): __slots__ = () @property def all (self): 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 None @property def consts (self): return ConstExpense.of_month(self.date.month, self.date.year).all() @property def sum (self): return self.constsum + sum(x.expense for x in self.catexps) @property def all (self): return SingleExpense.of_month(self.date.month, self.date.year).order_by(SingleExpense.day).all() def __str__ (self): return '' % (self.date, self.sum) # # Rest # 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()