from flask.ext.sqlalchemy import SQLAlchemy from sqlalchemy import sql from sqlalchemy.ext.declarative import declared_attr import datetime import decimal from functools import partial from collections import namedtuple from . import app db = SQLAlchemy(app) __all__ = ['db', \ 'Category', 'SingleExpense', 'ConstExpense', \ 'CatExpense', 'MonthExpense'] Column = db.Column ReqColumn = partial(Column, nullable = False) ExpNum = db.Numeric(scale = 2, precision = 10) def to_exp(d): """Converts decimal into expense""" return d.quantize(decimal.Decimal('.01'), rounding = decimal.ROUND_UP) # # Database Entities # class Model (db.Model): """Abstract base class for all models. Adds an id PK and several convenience accessors. """ __abstract__ = True id = Column(db.Integer, primary_key=True) @declared_attr def __tablename__ (cls): return cls.__name__.lower() @classmethod def get_by (cls, *args, **kwargs): return cls.query.filter_by(*args, **kwargs).first() @classmethod def get_by_or_404 (cls, *args, **kwargs): return cls.query.filter_by(*args, **kwargs).first_or_404() @classmethod def get (cls, *args, **kwargs): return cls.query.get(*args, **kwargs) @classmethod def get_or_404 (cls, *args, **kwargs): return cls.query.get_or_404(*args, **kwargs) class User (Model): # NB: This is abstract, the flesh is added in login.py __abstract__ = True name = ReqColumn(db.Unicode(50), unique = True) pwd = ReqColumn(db.Unicode(255)) description = Column(db.Unicode(100)) class UserModel (Model): """Abstract base class for tables that have a user column.""" __abstract__ = True @declared_attr def user_id(cls): return ReqColumn(db.Integer, db.ForeignKey('user.id')) @declared_attr def user(cls): return db.relationship('User') @classmethod def of (cls, user): return cls.query.filter_by(user = user) class Category (UserModel): name = ReqColumn(db.Unicode(50)) parent_id = Column(db.Integer, db.ForeignKey('category.id')) children = db.relationship('Category', backref=db.backref('parent', remote_side="Category.id")) def __init__ (self, name, user, parent_id = None): Model.__init__(self) self.name = name self.user = user self.parent_id = parent_id def __repr__ (self): if self.parent: return '' % (self.name, self.parent.name) else: return '' % self.name class Expense (UserModel): """Abstract base class for expenses: Adds the common fields and establishes the connection to `Category`. """ __abstract__ = True description = Column(db.Unicode(50)) expense = ReqColumn(ExpNum) @declared_attr def category_id(cls): return ReqColumn(db.Integer, db.ForeignKey(Category.id)) @declared_attr def category(cls): return db.relationship(Category, innerjoin = True) class SingleExpense (Expense): year = ReqColumn(db.Integer) month = ReqColumn(db.SmallInteger) day = ReqColumn(db.SmallInteger) @classmethod def of_month (cls, user, month, year): return cls.of(user).filter_by(month = month, year = year) @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 = ReqColumn(db.SmallInteger) start = ReqColumn(db.Date, index = True) end = ReqColumn(db.Date, index = True) prev_id = Column(db.Integer, db.ForeignKey('constexpense.id')) prev = db.relationship('ConstExpense', remote_side = 'ConstExpense.id', uselist = False, backref=db.backref('next', uselist = False)) @property def monthly(self): return to_exp(self.expense / self.months) @classmethod def of_month (cls, user, month, year): d = datetime.date(year, month, 1) return cls.of(user).filter(sql.between(d, cls.start, cls.end)) # # Work entities (not stored in DB) # class CatExpense (namedtuple('CatExpense', 'cat sum exps')): __slots__ = () @property def all (self): return self.exps.order_by(SingleExpense.day).all() class MonthExpense (namedtuple('MonthExpense', 'user date catexps')): def __init__ (self, *args, **kwargs): self._consts = None super(MonthExpense, self).__init__(*args, **kwargs) @property def consts (self): if self._consts is None: self._consts = ConstExpense.of_month(self.user, 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): return self.constsum + sum(x.sum for x in self.catexps) @property def all (self): return SingleExpense.of_month(self.user, self.date.month, self.date.year).order_by(SingleExpense.day).all() def __str__ (self): return '' % (self.user.name, self.date, self.sum) # # Extra indices have to be here # db.Index('idx_single_date', SingleExpense.user_id, SingleExpense.year, SingleExpense.month) db.Index('idx_start_end', ConstExpense.user_id, ConstExpense.start, ConstExpense.end)