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
|
import testbase
import re
from sqlalchemy import *
from sqlalchemy.sql import table, column
from sqlalchemy.databases import mssql
from testlib import *
# TODO: migrate all MS-SQL tests here
class CompileTest(SQLCompileTest):
__dialect__ = mssql.MSSQLDialect()
def test_insert(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(t.insert(), "INSERT INTO sometable (somecolumn) VALUES (:somecolumn)")
def test_update(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(t.update(t.c.somecolumn==7), "UPDATE sometable SET somecolumn=:somecolumn WHERE sometable.somecolumn = :sometable_somecolumn", dict(somecolumn=10))
def test_count(self):
t = table('sometable', column('somecolumn'))
self.assert_compile(t.count(), "SELECT count(sometable.somecolumn) AS tbl_row_count FROM sometable")
def test_union(self):
t1 = table('t1',
column('col1'),
column('col2'),
column('col3'),
column('col4')
)
t2 = table('t2',
column('col1'),
column('col2'),
column('col3'),
column('col4'))
(s1, s2) = (
select([t1.c.col3.label('col3'), t1.c.col4.label('col4')], t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
select([t2.c.col3.label('col3'), t2.c.col4.label('col4')], t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
)
u = union(s1, s2, order_by=['col3', 'col4'])
self.assert_compile(u, "SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:t1_col2, :t1_col2_1) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2, :t2_col2_1) ORDER BY col3, col4")
self.assert_compile(u.alias('bar').select(), "SELECT bar.col3, bar.col4 FROM (SELECT t1.col3 AS col3, t1.col4 AS col4 FROM t1 WHERE t1.col2 IN (:t1_col2, :t1_col2_1) UNION SELECT t2.col3 AS col3, t2.col4 AS col4 FROM t2 WHERE t2.col2 IN (:t2_col2, :t2_col2_1)) AS bar")
def test_function(self):
self.assert_compile(func.foo(1, 2), "foo(:foo, :foo_1)")
self.assert_compile(func.current_time(), "current_time")
self.assert_compile(func.foo(), "foo()")
m = MetaData()
t = Table('sometable', m, Column('col1', Integer), Column('col2', Integer))
self.assert_compile(select([func.max(t.c.col1)]), "SELECT max(sometable.col1) AS max_1 FROM sometable")
def test_limit(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t]).limit(10).offset(20).order_by(t.c.col1).apply_labels()
self.assert_compile(s, "SELECT anon_1.sometable_col1 AS sometable_col1, anon_1.sometable_col2 AS sometable_col2 FROM (SELECT sometable.col1 AS sometable_col1, sometable.col2 AS sometable_col2, "
"ROW_NUMBER() OVER (ORDER BY sometable.col1) AS mssql_rn FROM sometable) AS anon_1 WHERE mssql_rn>20 AND mssql_rn<=30"
)
s = select([t]).limit(10).offset(20).order_by(t.c.col1)
self.assert_compile(s, "SELECT anon_1.col1 AS col1, anon_1.col2 AS col2 FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2, "
"ROW_NUMBER() OVER (ORDER BY sometable.col1) AS mssql_rn FROM sometable) AS anon_1 WHERE mssql_rn>20 AND mssql_rn<=30"
)
s = select([s.c.col1, s.c.col2])
self.assert_compile(s, "SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, anon_1.col2 AS col2 FROM "
"(SELECT sometable.col1 AS col1, sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.col1) AS mssql_rn FROM sometable) AS anon_1 "
"WHERE mssql_rn>20 AND mssql_rn<=30)")
# testing this twice to ensure oracle doesn't modify the original statement
self.assert_compile(s, "SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, anon_1.col2 AS col2 FROM "
"(SELECT sometable.col1 AS col1, sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.col1) AS mssql_rn FROM sometable) AS anon_1 "
"WHERE mssql_rn>20 AND mssql_rn<=30)")
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s, "SELECT anon_1.col1 AS col1, anon_1.col2 AS col2 FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2, ROW_NUMBER() OVER (ORDER BY sometable.col2) AS mssql_rn FROM sometable) AS anon_1 WHERE mssql_rn>20 AND mssql_rn<=30")
if __name__ == "__main__":
testbase.main()
|
