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
|
"""tests that various From objects properly export their columns, as well as useable primary keys
and foreign keys. Full relational algebra depends on every selectable unit behaving
nicely with others.."""
import testbase
import unittest, sys, datetime
db = testbase.db
from sqlalchemy import *
table = Table('table1', db,
Column('col1', Integer, primary_key=True),
Column('col2', String(20)),
Column('col3', Integer),
Column('colx', Integer),
redefine=True
)
table2 = Table('table2', db,
Column('col1', Integer, primary_key=True),
Column('col2', Integer, ForeignKey('table1.col1')),
Column('col3', String(20)),
Column('coly', Integer),
redefine=True
)
class SelectableTest(testbase.AssertMixin):
def testtablealias(self):
a = table.alias('a')
j = join(a, table2)
criterion = a.c.col1 == table2.c.col2
print
print str(j)
self.assert_(criterion.compare(j.onclause))
def testunion(self):
# tests that we can correspond a column in a Select statement with a certain Table, against
# a column in a Union where one of its underlying Selects matches to that same Table
u = select([table.c.col1, table.c.col2, table.c.col3, table.c.colx, null().label('coly')]).union(
select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly])
)
s1 = table.select(use_labels=True)
s2 = table2.select(use_labels=True)
print ["%d %s" % (id(c),c.key) for c in u.c]
c = u.corresponding_column(s1.c.table1_col2)
print "%d %s" % (id(c), c.key)
assert u.corresponding_column(s1.c.table1_col2) is u.c.col2
assert u.corresponding_column(s2.c.table2_col2) is u.c.col2
def testaliasunion(self):
# same as testunion, except its an alias of the union
u = select([table.c.col1, table.c.col2, table.c.col3, table.c.colx, null().label('coly')]).union(
select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly])
).alias('analias')
s1 = table.select(use_labels=True)
s2 = table2.select(use_labels=True)
assert u.corresponding_column(s1.c.table1_col2) is u.c.col2
assert u.corresponding_column(s2.c.table2_col2) is u.c.col2
assert u.corresponding_column(s2.c.table2_coly) is u.c.coly
assert s2.corresponding_column(u.c.coly) is s2.c.table2_coly
def testselectunion(self):
# like testaliasunion, but off a Select off the union.
u = select([table.c.col1, table.c.col2, table.c.col3, table.c.colx, null().label('coly')]).union(
select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly])
).alias('analias')
s = select([u])
s1 = table.select(use_labels=True)
s2 = table2.select(use_labels=True)
assert s.corresponding_column(s1.c.table1_col2) is s.c.col2
assert s.corresponding_column(s2.c.table2_col2) is s.c.col2
def testunionagainstjoin(self):
# same as testunion, except its an alias of the union
u = select([table.c.col1, table.c.col2, table.c.col3, table.c.colx, null().label('coly')]).union(
select([table2.c.col1, table2.c.col2, table2.c.col3, null().label('colx'), table2.c.coly])
).alias('analias')
j1 = table.join(table2)
assert u.corresponding_column(j1.c.table1_colx) is u.c.colx
assert j1.corresponding_column(u.c.colx) is j1.c.table1_colx
def testjoin(self):
a = join(table, table2)
print str(a.select(use_labels=True))
b = table2.alias('b')
j = join(a, b)
print str(j)
criterion = a.c.table1_col1 == b.c.col2
self.assert_(criterion.compare(j.onclause))
def testselectalias(self):
a = table.select().alias('a')
print str(a.select())
j = join(a, table2)
criterion = a.c.col1 == table2.c.col2
print
print str(j)
self.assert_(criterion.compare(j.onclause))
def testselectlabels(self):
a = table.select(use_labels=True)
print str(a.select())
j = join(a, table2)
criterion = a.c.table1_col1 == table2.c.col2
print
print str(j)
self.assert_(criterion.compare(j.onclause))
def testcolumnlabels(self):
a = select([table.c.col1.label('acol1'), table.c.col2.label('acol2'), table.c.col3.label('acol3')])
print str(a)
print [c for c in a.columns]
print str(a.select())
j = join(a, table2)
criterion = a.c.acol1 == table2.c.col2
print str(j)
self.assert_(criterion.compare(j.onclause))
def testselectaliaslabels(self):
a = table2.select(use_labels=True).alias('a')
print str(a.select())
j = join(a, table)
criterion = table.c.col1 == a.c.table2_col2
print str(criterion)
print str(j.onclause)
self.assert_(criterion.compare(j.onclause))
if __name__ == "__main__":
testbase.main()
|
