-
Notifications
You must be signed in to change notification settings - Fork 28
Expand file tree
/
Copy pathtest_joins_conditional.py
More file actions
143 lines (109 loc) · 3.97 KB
/
Copy pathtest_joins_conditional.py
File metadata and controls
143 lines (109 loc) · 3.97 KB
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
from sqlobject import SQLObject, StringCol
from sqlobject.sqlbuilder import JOIN, LEFTJOIN, LEFTJOINConditional, \
LEFTJOINOn, LEFTJOINUsing
from sqlobject.tests.dbtest import getConnection, setupClass
########################################
# Condiotional joins
########################################
class SOTestJoin1(SQLObject):
col1 = StringCol()
class SOTestJoin2(SQLObject):
col2 = StringCol()
class SOTestJoin3(SQLObject):
col3 = StringCol()
class SOTestJoin4(SQLObject):
col4 = StringCol()
class SOTestJoin5(SQLObject):
col5 = StringCol()
def setup():
setupClass(SOTestJoin1)
setupClass(SOTestJoin2)
def test_1syntax():
setup()
join = JOIN("table1", "table2")
assert str(join) == "table1 JOIN table2"
join = LEFTJOIN("table1", "table2")
assert str(join) == "table1 LEFT JOIN table2"
join = LEFTJOINOn("table1", "table2", "tabl1.col1 = table2.col2")
assert getConnection().sqlrepr(join) == \
"table1 LEFT JOIN table2 ON tabl1.col1 = table2.col2"
def test_2select_syntax():
setup()
select = SOTestJoin1.select(
join=LEFTJOINConditional(SOTestJoin1, SOTestJoin2,
on_condition=(
SOTestJoin1.q.col1 == SOTestJoin2.q.col2))
)
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1 " \
"LEFT JOIN so_test_join2 " \
"ON ((so_test_join1.col1) = (so_test_join2.col2)) WHERE 1 = 1"
def test_3perform_join():
setup()
SOTestJoin1(col1="test1")
SOTestJoin1(col1="test2")
SOTestJoin1(col1="test3")
SOTestJoin2(col2="test1")
SOTestJoin2(col2="test2")
select = SOTestJoin1.select(
join=LEFTJOINOn(SOTestJoin1, SOTestJoin2,
SOTestJoin1.q.col1 == SOTestJoin2.q.col2)
)
assert select.count() == 3
def test_4join_3tables_syntax():
setup()
setupClass(SOTestJoin3)
select = SOTestJoin1.select(
join=LEFTJOIN(SOTestJoin2, SOTestJoin3)
)
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1, so_test_join2 LEFT JOIN so_test_join3 WHERE 1 = 1"
def test_5join_3tables_syntax2():
setup()
setupClass(SOTestJoin3)
select = SOTestJoin1.select(
join=(LEFTJOIN(None, SOTestJoin2), LEFTJOIN(None, SOTestJoin3))
)
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1 " \
"LEFT JOIN so_test_join2 LEFT JOIN so_test_join3 WHERE 1 = 1"
select = SOTestJoin1.select(
join=(LEFTJOIN(SOTestJoin1, SOTestJoin2),
LEFTJOIN(SOTestJoin1, SOTestJoin3))
)
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1 " \
"LEFT JOIN so_test_join2, so_test_join1 " \
"LEFT JOIN so_test_join3 WHERE 1 = 1"
def test_6join_using():
setup()
setupClass(SOTestJoin3)
select = SOTestJoin1.select(
join=LEFTJOINUsing(None, SOTestJoin2, [SOTestJoin2.q.id])
)
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1 " \
"LEFT JOIN so_test_join2 USING (so_test_join2.id) WHERE 1 = 1"
def test_7join_on():
setup()
setupClass(SOTestJoin3)
setupClass(SOTestJoin4)
setupClass(SOTestJoin5)
select = SOTestJoin1.select(join=(
LEFTJOINOn(SOTestJoin2, SOTestJoin3,
SOTestJoin2.q.col2 == SOTestJoin3.q.col3),
LEFTJOINOn(SOTestJoin4, SOTestJoin5,
SOTestJoin4.q.col4 == SOTestJoin5.q.col5)
))
assert str(select) == \
"SELECT so_test_join1.id, so_test_join1.col1 " \
"FROM so_test_join1, so_test_join2 " \
"LEFT JOIN so_test_join3 " \
"ON ((so_test_join2.col2) = (so_test_join3.col3)), so_test_join4 " \
"LEFT JOIN so_test_join5 " \
"ON ((so_test_join4.col4) = (so_test_join5.col5)) WHERE 1 = 1"