1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
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 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.String(50), unique = True)
parent = ManyToOne('Category')
children = OneToMany('Category')
def __repr__ (self):
if self.parent:
return '<Category "%s" of "%s">' % (self.name, self.parent.name)
else:
return '<Category "%s">' % self.name
class Expense (Entity):
using_options(abstract = True)
description = Field(T.String(50))
expense = ReqField(ExpNum)
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))
@classmethod
def of_month (cls, month, year):
comp = sql.and_(
cls.month == month,
cls.year == year)
return cls.query.filter(comp)
class ConstExpense (Expense):
months = ReqField(T.Integer)
start = ReqField(T.Date)
end = ReqField(T.Date)
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))
#
# Work entities (not stored in DB)
#
CatExpense = namedtuple('CatExpense', 'cat expense')
class MonthExpense (namedtuple('MonthExpense', 'date const catexps')):
__slots__ = ()
@property
def sum (self):
return self.const + sum(x.expense for x in self.catexps)
def __str__ (self):
return '<MonthExpense of "%s": %s>' % (self.date, self.sum)
#
# Rest
#
elixir.setup_all()
session = elixir.session
if __name__ == "__main__":
elixir.create_all()
|
