1#!/usr/bin/env python3
2
3"""Aggregate functions as Python objects.
4These can be passed to the db.ts.select function fields parameter e.g.:
5
6db.ts.select(...,
7 fields=(Min('NEDT'), Avg('UZHB')),
8 ...)
9
10or
11
12db.ts.select(fields=Count(), ...)
13
14."""
15
16from chart.common.traits import name_of_thing
17
18# Clients can explicitly request no ordering clauses in ts retrieval
19UNORDERED = object()
20
21
22class Any:
23 """Parameter meaning to match any values."""
24
25 def __str__(self):
26 return '<ANY>'
27
28ANY = Any()
29
30
31class Reversed:
32 """Allow client code to specify reverse ordering without using SQL
33 e.g. db.ts.select(..., ordering=Desc('SENSING_TINE')).
34 """
35
36 def __init__(self, field):
37 self.field = field
38
39 def __call__(self):
40 return '{field} DESC'.format(field=self.field)
41
42DONT_CHANGE = object()
43
44
45def unfunc(obj):
46 """If `obj` is a string return it.
47 If `obj` is a Func object then return the expanded sql'
48
49 >>> [unfunc(i) for i in ('one', Max('two'), Count())]
50 ['one', 'max(two)', 'count(*)']
51 """
52
53 if isinstance(obj, str):
54 return obj
55
56 elif isinstance(obj, Func):
57 return obj.sql()
58
59 else:
60 raise ValueError
61
62
63class Func:
64 """Base to for aggregate function operators."""
65 pass
66
67
68class Field(Func):
69 """Dummy aggregate function that returns the field name."""
70
71 def __init__(self, field):
72 self.field = field
73
74 def sql(self):
75 """Our SQL representation."""
76 return self.field
77
78
79class Count(Func):
80 """Count number of rows."""
81
82 def sql(self):
83 """Our SQL representation."""
84 return 'count(*)'
85
86
87class Min(Func):
88 """Find the minimum value across many rows."""
89
90 def __init__(self, field):
91 self.field = field
92 # test, bad, jcs
93 # self.name = field
94
95 def sql(self):
96 """Our SQL representation."""
97 return 'min({f})'.format(f=name_of_thing(self.field))
98
99 def sql_manual(self, field):
100 """Used in jsonb retrieval."""
101 return 'min({f})'.format(f=field)
102
103 def sql_stat(self, suffix):
104 """Our SQL representation."""
105 return 'min({f}_{suffix})'.format(f=name_of_thing(self.field), suffix=suffix)
106
107
108class Max(Func):
109 """Find the maximum value across many rows."""
110
111 def __init__(self, field):
112 self.field = field
113 self.name = field
114
115 def sql(self):
116 """Our SQL representation."""
117 return 'max({f})'.format(f=name_of_thing(self.field))
118
119 def sql_manual(self, field):
120 """Used in jsonb retrieval."""
121 return 'max({f})'.format(f=field)
122
123 def sql_stat(self, suffix):
124 """Our SQL representation."""
125 return 'max({f}_{suffix})'.format(f=name_of_thing(self.field), suffix=suffix)
126
127
128class Sum(Func):
129 """Find the sum of many rows."""
130
131 def __init__(self, field):
132 self.field = field
133
134 def sql(self):
135 """Our SQL representation."""
136 return 'sum({f})'.format(f=name_of_thing(self.field))
137
138 def sql_stat(self, suffix):
139 """Our SQL representation."""
140 return 'sum({f}_{suffix})'.format(f=name_of_thing(self.field), suffix=suffix)
141
142 def sql_manual(self, field):
143 """Our SQL representation, used in JSONB decoding."""
144 return 'sum({f})'.format(f=field)
145
146
147class Avg(Func):
148 """Find the average value across many rows."""
149
150 def __init__(self, field):
151 self.field = field
152
153 def sql(self):
154 """Our SQL representation."""
155 return 'avg({f})'.format(f=name_of_thing(self.field))
156
157 def sql_manual(self, field):
158 """Used in jsonb retrieval."""
159 return 'avg({f})'.format(f=field)
160
161 def sql_stat(self, suffix):
162 """Our SQL representation."""
163 return 'avg({f}_{suffix})'.format(f=name_of_thing(self.field), suffix=suffix)
164
165
166class Std(Func):
167 """Find the standard deviation across many rows."""
168
169 def __init__(self, field):
170 self.field = field
171
172 def sql(self):
173 """Our SQL representation."""
174 return 'stddev({f})'.format(f=name_of_thing(self.field))
175
176 def sql_stat(self, suffix):
177 """Our SQL representation."""
178 return 'stddev({f}_{suffix})'.format(f=self.field, suffix=suffix)
179
180
181class Median(Func):
182 """Find the median value across many rows."""
183
184 def __init__(self, field):
185 self.field = field
186
187 def sql(self):
188 """Our SQL representation."""
189 return 'median({f})'.format(f=self.field)
190
191 def sql_stat(self, suffix):
192 """Our SQL representation."""
193 return 'median({f}_{suffix})'.format(f=self.field, suffix=suffix)
194
195
196# Use RowcountFieldInfo instead
197# class Rowcount:
198 # """Class to pass to ts.select() to request counts of parameters."""
199 # def __init__(self, field=None):
200 # self.field = field
201
202 # @property
203 # def name(self):
204 # """Convenience function for ts.select()."""
205 # This does have to be capitals due to a bit of weirdness where flat.py tests for
206 # various strings
207 # return 'ROWCOUNT'
208
209
210SensingTime = Func()
211 # """Object to pass to ts.select() to request sensing times."""
212SensingTime.name = 'SENSING_TIME'
213SensingTime.calibration_name = None
214SensingTime.db_name = 'SENSING_TIME'
215SensingTime.field = 'SENSING_TIME'
216def sensing_time_sql_stat(_):
217 return 'SENSING_TIME'
218
219SensingTime.sql_stat = sensing_time_sql_stat
220def sensing_time_sql():
221 return 'SENSING_TIME'
222
223SensingTime.sql = sensing_time_sql