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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
# This file is part of python-sql. The COPYRIGHT file at the top level of
# this repository contains the full copyright notices and license terms.
import unittest
from sql import AliasManager, Flavor, Table, Window
from sql.functions import (
Abs, AtTimeZone, CurrentTime, Div, Function, FunctionKeyword,
FunctionNotCallable, Overlay, Rank, Trim)
class TestFunctions(unittest.TestCase):
table = Table('t')
def test_abs(self):
abs_ = Abs(self.table.c1)
self.assertEqual(str(abs_), 'ABS("c1")')
self.assertEqual(abs_.params, ())
abs_ = Abs(-12)
self.assertEqual(str(abs_), 'ABS(%s)')
self.assertEqual(abs_.params, (-12,))
def test_mapping(self):
class MyAbs(Function):
_function = 'MY_ABS'
params = ('test',)
class MyOverlay(FunctionKeyword):
_function = 'MY_OVERLAY'
_keywords = ('', 'PLACING', 'FROM', 'FOR')
class MyCurrentTime(FunctionNotCallable):
_function = 'MY_CURRENT_TIME'
class MyTrim(Trim):
_function = 'MY_TRIM'
abs_ = Abs(self.table.c1)
overlay = Overlay(self.table.c1, 'test', 2)
current_time = CurrentTime()
trim = Trim(' test ')
flavor = Flavor(function_mapping={
Abs: MyAbs,
Overlay: MyOverlay,
CurrentTime: MyCurrentTime,
Trim: MyTrim,
})
Flavor.set(flavor)
try:
self.assertEqual(str(abs_), 'MY_ABS("c1")')
self.assertEqual(abs_.params, ('test',))
self.assertEqual(str(overlay),
'MY_OVERLAY("c1" PLACING %s FROM %s)')
self.assertEqual(overlay.params, ('test', 2))
self.assertEqual(str(current_time), 'MY_CURRENT_TIME')
self.assertEqual(current_time.params, ())
self.assertEqual(str(trim), 'MY_TRIM(BOTH %s FROM %s)')
self.assertEqual(trim.params, (' ', ' test ',))
finally:
Flavor.set(Flavor())
def test_sql(self):
abs_ = Abs(self.table.select(self.table.c1,
where=self.table.c2 == 'foo'))
self.assertEqual(str(abs_),
'ABS((SELECT "a"."c1" FROM "t" AS "a" WHERE ("a"."c2" = %s)))')
self.assertEqual(abs_.params, ('foo',))
def test_overlay(self):
overlay = Overlay(self.table.c1, 'test', 3)
self.assertEqual(str(overlay), 'OVERLAY("c1" PLACING %s FROM %s)')
self.assertEqual(overlay.params, ('test', 3))
overlay = Overlay(self.table.c1, 'test', 3, 7)
self.assertEqual(str(overlay),
'OVERLAY("c1" PLACING %s FROM %s FOR %s)')
self.assertEqual(overlay.params, ('test', 3, 7))
def test_trim(self):
trim = Trim(' test ')
self.assertEqual(str(trim), 'TRIM(BOTH %s FROM %s)')
self.assertEqual(trim.params, (' ', ' test ',))
trim = Trim(self.table.c1)
self.assertEqual(str(trim), 'TRIM(BOTH %s FROM "c1")')
self.assertEqual(trim.params, (' ',))
def test_at_time_zone(self):
time_zone = AtTimeZone(self.table.c1, 'UTC')
self.assertEqual(str(time_zone), '"c1" AT TIME ZONE %s')
self.assertEqual(time_zone.params, ('UTC',))
def test_at_time_zone_expression(self):
time_zone = AtTimeZone(self.table.c1, self.table.zone)
self.assertEqual(str(time_zone), '"c1" AT TIME ZONE "zone"')
self.assertEqual(time_zone.params, ())
def test_at_time_zone_sql(self):
time_zone = AtTimeZone(self.table.c1,
self.table.select(self.table.tz, where=self.table.c1 == 'foo'))
self.assertEqual(str(time_zone),
'"c1" AT TIME ZONE '
'(SELECT "a"."tz" FROM "t" AS "a" WHERE ("a"."c1" = %s))')
self.assertEqual(time_zone.params, ('foo',))
def test_at_time_zone_mapping(self):
class MyAtTimeZone(Function):
_function = 'MY_TIMEZONE'
time_zone = AtTimeZone(self.table.c1, 'UTC')
flavor = Flavor(function_mapping={
AtTimeZone: MyAtTimeZone,
})
Flavor.set(flavor)
try:
self.assertEqual(str(time_zone), 'MY_TIMEZONE("c1", %s)')
self.assertEqual(time_zone.params, ('UTC',))
finally:
Flavor.set(Flavor())
def test_div(self):
for div in [Div(self.table.c1, self.table.c2),
self.table.c1 // self.table.c2]:
self.assertEqual(str(div), 'DIV("c1", "c2")')
self.assertEqual(div.params, ())
def test_current_time(self):
current_time = CurrentTime()
self.assertEqual(str(current_time), 'CURRENT_TIME')
self.assertEqual(current_time.params, ())
class TestWindowFunction(unittest.TestCase):
def test_window(self):
t = Table('t')
function = Rank(t.c, window=Window([]))
with AliasManager():
self.assertEqual(str(function), 'RANK("a"."c") OVER ()')
self.assertEqual(function.params, ())
def test_filter(self):
t = Table('t')
function = Rank(t.c, filter_=t.c > 0, window=Window([]))
with AliasManager():
self.assertEqual(str(function),
'RANK("a"."c") FILTER (WHERE ("a"."c" > %s)) OVER ()')
self.assertEqual(function.params, (0,))
|
