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, 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.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):
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), deferred = True)
@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)
#
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)
@property
def all (self):
return SingleExpense.of_month(self.date.month, self.date.year).order_by(SingleExpense.day).all()
def __str__ (self):
return '<MonthExpense of "%s": %s>' % (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()
|
