This repository has been archived by the owner on Jun 14, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 38
/
builder_limit_test.go
124 lines (108 loc) · 6.42 KB
/
builder_limit_test.go
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
// Copyright 2018 The Xorm Authors. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
package builder
/*
func TestBuilder_Limit4Mssql(t *testing.T) {
sqlFromFile, err := readPreparationSQLFromFile("testdata/mssql_fiddle_data.sql")
assert.NoError(t, err)
f, err := newFiddler("", MSSQL, sqlFromFile)
assert.NoError(t, err)
assert.NotEmpty(t, f.sessionCode)
// simple -- MsSQL style
sql, err := Dialect(MSSQL).Select("a", "b", "c").From("table1").
OrderBy("a ASC").Limit(5).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 5 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 ORDER BY a ASC) at", sql)
assert.NoError(t, f.executableCheck(sql))
// simple with where -- MsSQL style
sql, err = Dialect(MSSQL).Select("a", "b", "c").From("table1").
Where(Neq{"a": "3"}).OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 15 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE a<>'3' ORDER BY a ASC) at WHERE at.RN>10", sql)
assert.NoError(t, f.executableCheck(sql))
// union with limit -- MsSQL style
sql, err = Dialect(MSSQL).Select("a", "b", "c").From(
Dialect(MSSQL).Select("a", "b", "c").From("table1").Where(Neq{"a": "1"}).
OrderBy("a ASC").Limit(5, 6).Union("ALL",
Select("a", "b", "c").From("table1").Where(Neq{"b": "2"}).OrderBy("a DESC").Limit(10)), "at").
OrderBy("b DESC").Limit(7, 9).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT TOP 16 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM ((SELECT a,b,c FROM (SELECT TOP 11 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE a<>'1' ORDER BY a ASC) at WHERE at.RN>6) UNION ALL (SELECT a,b,c FROM (SELECT TOP 10 a,b,c,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RN FROM table1 WHERE b<>'2' ORDER BY a DESC) at)) at ORDER BY b DESC) at WHERE at.RN>9", sql)
assert.NoError(t, f.executableCheck(sql))
}
func TestBuilder_Limit4MysqlLike(t *testing.T) {
sqlFromFile, err := readPreparationSQLFromFile("testdata/mysql_fiddle_data.sql")
assert.NoError(t, err)
f, err := newFiddler("", MYSQL, sqlFromFile)
assert.NoError(t, err)
assert.NotEmpty(t, f.sessionCode)
// simple -- MySQL/SQLite/PostgreSQL style
sql, err := Dialect(MYSQL).Select("a", "b", "c").From("table1").OrderBy("a ASC").
Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM table1 ORDER BY a ASC LIMIT 5 OFFSET 10", sql)
assert.NoError(t, f.executableCheck(sql))
// simple -- MySQL/SQLite/PostgreSQL style
sql, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").
OrderBy("a ASC").Limit(5).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM table1 ORDER BY a ASC LIMIT 5", sql)
assert.NoError(t, f.executableCheck(sql))
// simple with where -- MySQL/SQLite/PostgreSQL style
sql, err = Dialect(MYSQL).Select("a", "b", "c").From("table1").
Where(Eq{"a": "1", "b": "1"}).OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM table1 WHERE a='1' AND b='1' ORDER BY a ASC LIMIT 5 OFFSET 10", sql)
assert.NoError(t, f.executableCheck(sql))
// union -- MySQL/SQLite/PostgreSQL style
sql, err = Dialect(MYSQL).Select("a", "b", "c").From(
Dialect(MYSQL).Select("a", "b", "c").From("table1").Where(Eq{"a": "1"}).OrderBy("a ASC").
Limit(5, 9).Union("ALL",
Select("a", "b", "c").From("table1").Where(Eq{"a": "2"}).OrderBy("a DESC").Limit(10)), "at").
Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM ((SELECT a,b,c FROM table1 WHERE a='1' ORDER BY a ASC LIMIT 5 OFFSET 9) UNION ALL (SELECT a,b,c FROM table1 WHERE a='2' ORDER BY a DESC LIMIT 10)) at LIMIT 5 OFFSET 10", sql)
assert.NoError(t, f.executableCheck(sql))
}
func TestBuilder_Limit4Oracle(t *testing.T) {
sqlFromFile, err := readPreparationSQLFromFile("testdata/oracle_fiddle_data.sql")
assert.NoError(t, err)
f, err := newFiddler("", ORACLE, sqlFromFile)
assert.NoError(t, err)
assert.NotEmpty(t, f.sessionCode)
// simple -- OracleSQL style
sql, err := Dialect(ORACLE).Select("a", "b", "c").From("table1").OrderBy("a ASC").
Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10", sql)
assert.NoError(t, f.executableCheck(sql))
// simple with join -- OracleSQL style
sql, err = Dialect(ORACLE).Select("a", "b", "c", "d").From("table1 t1").
InnerJoin("table2 t2", "t1.id = t2.ref_id").OrderBy("a ASC").Limit(5, 10).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c,d FROM (SELECT * FROM (SELECT a,b,c,d,ROWNUM RN FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.ref_id ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10", sql)
assert.NoError(t, f.executableCheck(sql))
// simple -- OracleSQL style
sql, err = Dialect(ORACLE).Select("a", "b", "c").From("table1").
OrderBy("a ASC").Limit(5).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM table1 ORDER BY a ASC) at WHERE at.RN<=5", sql)
assert.NoError(t, f.executableCheck(sql))
// simple with where -- OracleSQL style
sql, err = Dialect(ORACLE).Select("a", "b", "c").From("table1").Where(Neq{"a": "10", "b": "20"}).
OrderBy("a ASC").Limit(5, 1).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE a<>'10' AND b<>'20' ORDER BY a ASC) at WHERE at.RN<=6) att WHERE att.RN>1", sql)
assert.NoError(t, f.executableCheck(sql))
// union with limit -- OracleSQL style
sql, err = Dialect(ORACLE).Select("a", "b", "c").From(
Dialect(ORACLE).Select("a", "b", "c").From("table1").
Where(Neq{"a": "0"}).OrderBy("a ASC").Limit(5, 10).Union("ALL",
Select("a", "b", "c").From("table1").Where(Neq{"b": "48"}).
OrderBy("a DESC").Limit(10)), "at").
Limit(3).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM ((SELECT a,b,c FROM (SELECT * FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE a<>'0' ORDER BY a ASC) at WHERE at.RN<=15) att WHERE att.RN>10) UNION ALL (SELECT a,b,c FROM (SELECT a,b,c,ROWNUM RN FROM table1 WHERE b<>'48' ORDER BY a DESC) at WHERE at.RN<=10)) at) at WHERE at.RN<=3", sql)
assert.NoError(t, f.executableCheck(sql))
}*/