Debian Codegraph @

django/db/backends/sqlite3/_functions.py

raw
1 """
2 Implementations of SQL functions for SQLite.
3 """
4
5 import functools
6 import random
7 import statistics
8 import zoneinfo
9 from datetime import timedelta
10 from hashlib import md5, sha1, sha224, sha256, sha384, sha512
11 from math import (
12 acos,
13 asin,
14 atan,
15 atan2,
16 ceil,
17 cos,
18 degrees,
19 exp,
20 floor,
21 fmod,
22 log,
23 pi,
24 radians,
25 sin,
26 sqrt,
27 tan,
28 )
29 from re import search as re_search
30
31 from django.db.backends.utils import (
32 split_tzname_delta,
33 typecast_time,
34 typecast_timestamp,
35 )
36 from django.utils import timezone
37 from django.utils.duration import duration_microseconds
38
39
40 def register(connection):
41 create_deterministic_function = functools.partial(
42 connection.create_function,
43 deterministic=True,
44 )
45 create_deterministic_function("django_date_extract", 2, _sqlite_datetime_extract)
46 create_deterministic_function("django_date_trunc", 4, _sqlite_date_trunc)
47 create_deterministic_function(
48 "django_datetime_cast_date", 3, _sqlite_datetime_cast_date
49 )
50 create_deterministic_function(
51 "django_datetime_cast_time", 3, _sqlite_datetime_cast_time
52 )
53 create_deterministic_function(
54 "django_datetime_extract", 4, _sqlite_datetime_extract
55 )
56 create_deterministic_function("django_datetime_trunc", 4, _sqlite_datetime_trunc)
57 create_deterministic_function("django_time_extract", 2, _sqlite_time_extract)
58 create_deterministic_function("django_time_trunc", 4, _sqlite_time_trunc)
59 create_deterministic_function("django_time_diff", 2, _sqlite_time_diff)
60 create_deterministic_function("django_timestamp_diff", 2, _sqlite_timestamp_diff)
61 create_deterministic_function("django_format_dtdelta", 3, _sqlite_format_dtdelta)
62 create_deterministic_function("regexp", 2, _sqlite_regexp)
63 create_deterministic_function("BITXOR", 2, _sqlite_bitxor)
64 create_deterministic_function("COT", 1, _sqlite_cot)
65 create_deterministic_function("LPAD", 3, _sqlite_lpad)
66 create_deterministic_function("MD5", 1, _sqlite_md5)
67 create_deterministic_function("REPEAT", 2, _sqlite_repeat)
68 create_deterministic_function("REVERSE", 1, _sqlite_reverse)
69 create_deterministic_function("RPAD", 3, _sqlite_rpad)
70 create_deterministic_function("SHA1", 1, _sqlite_sha1)
71 create_deterministic_function("SHA224", 1, _sqlite_sha224)
72 create_deterministic_function("SHA256", 1, _sqlite_sha256)
73 create_deterministic_function("SHA384", 1, _sqlite_sha384)
74 create_deterministic_function("SHA512", 1, _sqlite_sha512)
75 create_deterministic_function("SIGN", 1, _sqlite_sign)
76 # Don't use the built-in RANDOM() function because it returns a value
77 # in the range [-1 * 2^63, 2^63 - 1] instead of [0, 1).
78 connection.create_function("RAND", 0, random.random)
79 connection.create_aggregate("STDDEV_POP", 1, StdDevPop)
80 connection.create_aggregate("STDDEV_SAMP", 1, StdDevSamp)
81 connection.create_aggregate("VAR_POP", 1, VarPop)
82 connection.create_aggregate("VAR_SAMP", 1, VarSamp)
83 # Some math functions are enabled by default in SQLite 3.35+.
84 sql = "select sqlite_compileoption_used('ENABLE_MATH_FUNCTIONS')"
85 if not connection.execute(sql).fetchone()[0]:
86 create_deterministic_function("ACOS", 1, _sqlite_acos)
87 create_deterministic_function("ASIN", 1, _sqlite_asin)
88 create_deterministic_function("ATAN", 1, _sqlite_atan)
89 create_deterministic_function("ATAN2", 2, _sqlite_atan2)
90 create_deterministic_function("CEILING", 1, _sqlite_ceiling)
91 create_deterministic_function("COS", 1, _sqlite_cos)
92 create_deterministic_function("DEGREES", 1, _sqlite_degrees)
93 create_deterministic_function("EXP", 1, _sqlite_exp)
94 create_deterministic_function("FLOOR", 1, _sqlite_floor)
95 create_deterministic_function("LN", 1, _sqlite_ln)
96 create_deterministic_function("LOG", 2, _sqlite_log)
97 create_deterministic_function("MOD", 2, _sqlite_mod)
98 create_deterministic_function("PI", 0, _sqlite_pi)
99 create_deterministic_function("POWER", 2, _sqlite_power)
100 create_deterministic_function("RADIANS", 1, _sqlite_radians)
101 create_deterministic_function("SIN", 1, _sqlite_sin)
102 create_deterministic_function("SQRT", 1, _sqlite_sqrt)
103 create_deterministic_function("TAN", 1, _sqlite_tan)
104
105
106 def _sqlite_datetime_parse(dt, tzname=None, conn_tzname=None):
107 if dt is None:
108 return None
109 try:
110 dt = typecast_timestamp(dt)
111 except (TypeError, ValueError):
112 return None
113 if conn_tzname:
114 dt = dt.replace(tzinfo=zoneinfo.ZoneInfo(conn_tzname))
115 if tzname is not None and tzname != conn_tzname:
116 tzname, sign, offset = split_tzname_delta(tzname)
117 if offset:
118 hours, minutes = offset.split(":")
119 offset_delta = timedelta(hours=int(hours), minutes=int(minutes))
120 dt += offset_delta if sign == "+" else -offset_delta
121 # The tzname may originally be just the offset e.g. "+3:00",
122 # which becomes an empty string after splitting the sign and offset.
123 # In this case, use the conn_tzname as fallback.
124 dt = timezone.localtime(dt, zoneinfo.ZoneInfo(tzname or conn_tzname))
125 return dt
126
127
128 def _sqlite_date_trunc(lookup_type, dt, tzname, conn_tzname):
129 dt = _sqlite_datetime_parse(dt, tzname, conn_tzname)
130 if dt is None:
131 return None
132 if lookup_type == "year":
133 return f"{dt.year:04d}-01-01"
134 elif lookup_type == "quarter":
135 month_in_quarter = dt.month - (dt.month - 1) % 3
136 return f"{dt.year:04d}-{month_in_quarter:02d}-01"
137 elif lookup_type == "month":
138 return f"{dt.year:04d}-{dt.month:02d}-01"
139 elif lookup_type == "week":
140 dt -= timedelta(days=dt.weekday())
141 return f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d}"
142 elif lookup_type == "day":
143 return f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d}"
144 raise ValueError(f"Unsupported lookup type: {lookup_type!r}")
145
146
147 def _sqlite_time_trunc(lookup_type, dt, tzname, conn_tzname):
148 if dt is None:
149 return None
150 dt_parsed = _sqlite_datetime_parse(dt, tzname, conn_tzname)
151 if dt_parsed is None:
152 try:
153 dt = typecast_time(dt)
154 except (ValueError, TypeError):
155 return None
156 else:
157 dt = dt_parsed
158 if lookup_type == "hour":
159 return f"{dt.hour:02d}:00:00"
160 elif lookup_type == "minute":
161 return f"{dt.hour:02d}:{dt.minute:02d}:00"
162 elif lookup_type == "second":
163 return f"{dt.hour:02d}:{dt.minute:02d}:{dt.second:02d}"
164 raise ValueError(f"Unsupported lookup type: {lookup_type!r}")
165
166
167 def _sqlite_datetime_cast_date(dt, tzname, conn_tzname):
168 dt = _sqlite_datetime_parse(dt, tzname, conn_tzname)
169 if dt is None:
170 return None
171 return dt.date().isoformat()
172
173
174 def _sqlite_datetime_cast_time(dt, tzname, conn_tzname):
175 dt = _sqlite_datetime_parse(dt, tzname, conn_tzname)
176 if dt is None:
177 return None
178 return dt.time().isoformat()
179
180
181 def _sqlite_datetime_extract(lookup_type, dt, tzname=None, conn_tzname=None):
182 dt = _sqlite_datetime_parse(dt, tzname, conn_tzname)
183 if dt is None:
184 return None
185 if lookup_type == "week_day":
186 return (dt.isoweekday() % 7) + 1
187 elif lookup_type == "iso_week_day":
188 return dt.isoweekday()
189 elif lookup_type == "week":
190 return dt.isocalendar().week
191 elif lookup_type == "quarter":
192 return ceil(dt.month / 3)
193 elif lookup_type == "iso_year":
194 return dt.isocalendar().year
195 else:
196 return getattr(dt, lookup_type)
197
198
199 def _sqlite_datetime_trunc(lookup_type, dt, tzname, conn_tzname):
200 dt = _sqlite_datetime_parse(dt, tzname, conn_tzname)
201 if dt is None:
202 return None
203 if lookup_type == "year":
204 return f"{dt.year:04d}-01-01 00:00:00"
205 elif lookup_type == "quarter":
206 month_in_quarter = dt.month - (dt.month - 1) % 3
207 return f"{dt.year:04d}-{month_in_quarter:02d}-01 00:00:00"
208 elif lookup_type == "month":
209 return f"{dt.year:04d}-{dt.month:02d}-01 00:00:00"
210 elif lookup_type == "week":
211 dt -= timedelta(days=dt.weekday())
212 return f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d} 00:00:00"
213 elif lookup_type == "day":
214 return f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d} 00:00:00"
215 elif lookup_type == "hour":
216 return f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d} {dt.hour:02d}:00:00"
217 elif lookup_type == "minute":
218 return (
219 f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d} "
220 f"{dt.hour:02d}:{dt.minute:02d}:00"
221 )
222 elif lookup_type == "second":
223 return (
224 f"{dt.year:04d}-{dt.month:02d}-{dt.day:02d} "
225 f"{dt.hour:02d}:{dt.minute:02d}:{dt.second:02d}"
226 )
227 raise ValueError(f"Unsupported lookup type: {lookup_type!r}")
228
229
230 def _sqlite_time_extract(lookup_type, dt):
231 if dt is None:
232 return None
233 try:
234 dt = typecast_time(dt)
235 except (ValueError, TypeError):
236 return None
237 return getattr(dt, lookup_type)
238
239
240 def _sqlite_prepare_dtdelta_param(conn, param):
241 if conn in ["+", "-"]:
242 if isinstance(param, int):
243 return timedelta(0, 0, param)
244 else:
245 return typecast_timestamp(param)
246 return param
247
248
249 def _sqlite_format_dtdelta(connector, lhs, rhs):
250 """
251 LHS and RHS can be either:
252 - An integer number of microseconds
253 - A string representing a datetime
254 - A scalar value, e.g. float
255 """
256 if connector is None or lhs is None or rhs is None:
257 return None
258 connector = connector.strip()
259 try:
260 real_lhs = _sqlite_prepare_dtdelta_param(connector, lhs)
261 real_rhs = _sqlite_prepare_dtdelta_param(connector, rhs)
262 except (ValueError, TypeError):
263 return None
264 if connector == "+":
265 # typecast_timestamp() returns a date or a datetime without timezone.
266 # It will be formatted as "%Y-%m-%d" or "%Y-%m-%d %H:%M:%S[.%f]"
267 out = str(real_lhs + real_rhs)
268 elif connector == "-":
269 out = str(real_lhs - real_rhs)
270 elif connector == "*":
271 out = real_lhs * real_rhs
272 else:
273 out = real_lhs / real_rhs
274 return out
275
276
277 def _sqlite_time_diff(lhs, rhs):
278 if lhs is None or rhs is None:
279 return None
280 left = typecast_time(lhs)
281 right = typecast_time(rhs)
282 return (
283 (left.hour * 60 * 60 * 1000000)
284 + (left.minute * 60 * 1000000)
285 + (left.second * 1000000)
286 + (left.microsecond)
287 - (right.hour * 60 * 60 * 1000000)
288 - (right.minute * 60 * 1000000)
289 - (right.second * 1000000)
290 - (right.microsecond)
291 )
292
293
294 def _sqlite_timestamp_diff(lhs, rhs):
295 if lhs is None or rhs is None:
296 return None
297 left = typecast_timestamp(lhs)
298 right = typecast_timestamp(rhs)
299 return duration_microseconds(left - right)
300
301
302 def _sqlite_regexp(pattern, string):
303 if pattern is None or string is None:
304 return None
305 if not isinstance(string, str):
306 string = str(string)
307 return bool(re_search(pattern, string))
308
309
310 def _sqlite_acos(x):
311 if x is None:
312 return None
313 return acos(x)
314
315
316 def _sqlite_asin(x):
317 if x is None:
318 return None
319 return asin(x)
320
321
322 def _sqlite_atan(x):
323 if x is None:
324 return None
325 return atan(x)
326
327
328 def _sqlite_atan2(y, x):
329 if y is None or x is None:
330 return None
331 return atan2(y, x)
332
333
334 def _sqlite_bitxor(x, y):
335 if x is None or y is None:
336 return None
337 return x ^ y
338
339
340 def _sqlite_ceiling(x):
341 if x is None:
342 return None
343 return ceil(x)
344
345
346 def _sqlite_cos(x):
347 if x is None:
348 return None
349 return cos(x)
350
351
352 def _sqlite_cot(x):
353 if x is None:
354 return None
355 return 1 / tan(x)
356
357
358 def _sqlite_degrees(x):
359 if x is None:
360 return None
361 return degrees(x)
362
363
364 def _sqlite_exp(x):
365 if x is None:
366 return None
367 return exp(x)
368
369
370 def _sqlite_floor(x):
371 if x is None:
372 return None
373 return floor(x)
374
375
376 def _sqlite_ln(x):
377 if x is None:
378 return None
379 return log(x)
380
381
382 def _sqlite_log(base, x):
383 if base is None or x is None:
384 return None
385 # Arguments reversed to match SQL standard.
386 return log(x, base)
387
388
389 def _sqlite_lpad(text, length, fill_text):
390 if text is None or length is None or fill_text is None:
391 return None
392 delta = length - len(text)
393 if delta <= 0:
394 return text[:length]
395 return (fill_text * length)[:delta] + text
396
397
398 def _sqlite_md5(text):
399 if text is None:
400 return None
401 return md5(text.encode()).hexdigest()
402
403
404 def _sqlite_mod(x, y):
405 if x is None or y is None:
406 return None
407 return fmod(x, y)
408
409
410 def _sqlite_pi():
411 return pi
412
413
414 def _sqlite_power(x, y):
415 if x is None or y is None:
416 return None
417 return x**y
418
419
420 def _sqlite_radians(x):
421 if x is None:
422 return None
423 return radians(x)
424
425
426 def _sqlite_repeat(text, count):
427 if text is None or count is None:
428 return None
429 return text * count
430
431
432 def _sqlite_reverse(text):
433 if text is None:
434 return None
435 return text[::-1]
436
437
438 def _sqlite_rpad(text, length, fill_text):
439 if text is None or length is None or fill_text is None:
440 return None
441 return (text + fill_text * length)[:length]
442
443
444 def _sqlite_sha1(text):
445 if text is None:
446 return None
447 return sha1(text.encode()).hexdigest()
448
449
450 def _sqlite_sha224(text):
451 if text is None:
452 return None
453 return sha224(text.encode()).hexdigest()
454
455
456 def _sqlite_sha256(text):
457 if text is None:
458 return None
459 return sha256(text.encode()).hexdigest()
460
461
462 def _sqlite_sha384(text):
463 if text is None:
464 return None
465 return sha384(text.encode()).hexdigest()
466
467
468 def _sqlite_sha512(text):
469 if text is None:
470 return None
471 return sha512(text.encode()).hexdigest()
472
473
474 def _sqlite_sign(x):
475 if x is None:
476 return None
477 return (x > 0) - (x < 0)
478
479
480 def _sqlite_sin(x):
481 if x is None:
482 return None
483 return sin(x)
484
485
486 def _sqlite_sqrt(x):
487 if x is None:
488 return None
489 return sqrt(x)
490
491
492 def _sqlite_tan(x):
493 if x is None:
494 return None
495 return tan(x)
496
497
498 class ListAggregate(list):
499 step = list.append
500
501
502 class StdDevPop(ListAggregate):
503 finalize = statistics.pstdev
504
505
506 class StdDevSamp(ListAggregate):
507 finalize = statistics.stdev
508
509
510 class VarPop(ListAggregate):
511 finalize = statistics.pvariance
512
513
514 class VarSamp(ListAggregate):
515 finalize = statistics.variance