1 package sqlgen_test
2
3 import (
4 "testing"
5
6 "github.com/doug-martin/goqu/v9/exp"
7 "github.com/doug-martin/goqu/v9/internal/sb"
8 "github.com/doug-martin/goqu/v9/sqlgen"
9 "github.com/stretchr/testify/suite"
10 )
11
12 type (
13 commonSQLTestCase struct {
14 gen func(builder sb.SQLBuilder)
15 sql string
16 isPrepared bool
17 err string
18 args []interface{}
19 }
20 commonSQLGeneratorSuite struct {
21 baseSQLGeneratorSuite
22 }
23 )
24
25 func (csgs *commonSQLGeneratorSuite) assertCases(testCases ...commonSQLTestCase) {
26 for _, tc := range testCases {
27 b := sb.NewSQLBuilder(tc.isPrepared)
28 tc.gen(b)
29 switch {
30 case len(tc.err) > 0:
31 csgs.assertErrorSQL(b, tc.err)
32 case tc.isPrepared:
33 csgs.assertPreparedSQL(b, tc.sql, tc.args)
34 default:
35 csgs.assertNotPreparedSQL(b, tc.sql)
36 }
37 }
38 }
39
40 func (csgs *commonSQLGeneratorSuite) TestReturningSQL() {
41 returningGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
42 return func(sb sb.SQLBuilder) {
43 csgs.ReturningSQL(sb, exp.NewColumnListExpression("a", "b"))
44 }
45 }
46
47 returningNoColsGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
48 return func(sb sb.SQLBuilder) {
49 csgs.ReturningSQL(sb, exp.NewColumnListExpression())
50 }
51 }
52
53 returningNilExpGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
54 return func(sb sb.SQLBuilder) {
55 csgs.ReturningSQL(sb, nil)
56 }
57 }
58
59 opts := sqlgen.DefaultDialectOptions()
60 opts.SupportsReturn = true
61 csgs1 := sqlgen.NewCommonSQLGenerator("test", opts)
62
63 opts2 := sqlgen.DefaultDialectOptions()
64 opts2.SupportsReturn = false
65 csgs2 := sqlgen.NewCommonSQLGenerator("test", opts2)
66
67 csgs.assertCases(
68 commonSQLTestCase{gen: returningGen(csgs1), sql: ` RETURNING "a", "b"`},
69 commonSQLTestCase{gen: returningGen(csgs1), sql: ` RETURNING "a", "b"`, isPrepared: true, args: emptyArgs},
70
71 commonSQLTestCase{gen: returningNoColsGen(csgs1), sql: ``},
72 commonSQLTestCase{gen: returningNoColsGen(csgs1), sql: ``, isPrepared: true, args: emptyArgs},
73
74 commonSQLTestCase{gen: returningNilExpGen(csgs1), sql: ``},
75 commonSQLTestCase{gen: returningNilExpGen(csgs1), sql: ``, isPrepared: true, args: emptyArgs},
76
77 commonSQLTestCase{gen: returningGen(csgs2), err: `goqu: dialect does not support RETURNING clause [dialect=test]`},
78 commonSQLTestCase{gen: returningGen(csgs2), err: `goqu: dialect does not support RETURNING clause [dialect=test]`},
79 )
80 }
81
82 func (csgs *commonSQLGeneratorSuite) TestFromSQL() {
83 fromGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
84 return func(sb sb.SQLBuilder) {
85 csgs.FromSQL(sb, exp.NewColumnListExpression("a", "b"))
86 }
87 }
88
89 fromNoColsGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
90 return func(sb sb.SQLBuilder) {
91 csgs.FromSQL(sb, exp.NewColumnListExpression())
92 }
93 }
94
95 fromNilExpGen := func(csgs sqlgen.CommonSQLGenerator) func(sb.SQLBuilder) {
96 return func(sb sb.SQLBuilder) {
97 csgs.FromSQL(sb, nil)
98 }
99 }
100
101 csg := sqlgen.NewCommonSQLGenerator("test", sqlgen.DefaultDialectOptions())
102
103 opts := sqlgen.DefaultDialectOptions()
104 opts.FromFragment = []byte(" from")
105 csgFromFrag := sqlgen.NewCommonSQLGenerator("test", opts)
106
107 csgs.assertCases(
108 commonSQLTestCase{gen: fromGen(csg), sql: ` FROM "a", "b"`},
109 commonSQLTestCase{gen: fromGen(csg), sql: ` FROM "a", "b"`, isPrepared: true, args: emptyArgs},
110
111 commonSQLTestCase{gen: fromNoColsGen(csg), sql: ``},
112 commonSQLTestCase{gen: fromNoColsGen(csg), sql: ``, isPrepared: true, args: emptyArgs},
113
114 commonSQLTestCase{gen: fromNilExpGen(csg), sql: ``},
115 commonSQLTestCase{gen: fromNilExpGen(csg), sql: ``, isPrepared: true, args: emptyArgs},
116
117 commonSQLTestCase{gen: fromGen(csgFromFrag), sql: ` from "a", "b"`},
118 commonSQLTestCase{gen: fromGen(csgFromFrag), sql: ` from "a", "b"`, isPrepared: true, args: emptyArgs},
119
120 commonSQLTestCase{gen: fromNoColsGen(csgFromFrag), sql: ``},
121 commonSQLTestCase{gen: fromNoColsGen(csgFromFrag), sql: ``, isPrepared: true, args: emptyArgs},
122
123 commonSQLTestCase{gen: fromNilExpGen(csgFromFrag), sql: ``},
124 commonSQLTestCase{gen: fromNilExpGen(csgFromFrag), sql: ``, isPrepared: true, args: emptyArgs},
125 )
126 }
127
128 func (csgs *commonSQLGeneratorSuite) TestWhereSQL() {
129 whereAndGen := func(csgs sqlgen.CommonSQLGenerator, exps ...exp.Expression) func(sb.SQLBuilder) {
130 return func(sb sb.SQLBuilder) {
131 csgs.WhereSQL(sb, exp.NewExpressionList(exp.AndType, exps...))
132 }
133 }
134
135 whereOrGen := func(csgs sqlgen.CommonSQLGenerator, exps ...exp.Expression) func(sb.SQLBuilder) {
136 return func(sb sb.SQLBuilder) {
137 csgs.WhereSQL(sb, exp.NewExpressionList(exp.OrType, exps...))
138 }
139 }
140
141 csg := sqlgen.NewCommonSQLGenerator("test", sqlgen.DefaultDialectOptions())
142
143 opts := sqlgen.DefaultDialectOptions()
144 opts.WhereFragment = []byte(" where ")
145 csgWhereFrag := sqlgen.NewCommonSQLGenerator("test", opts)
146
147 w := exp.Ex{"a": "b"}
148 w2 := exp.Ex{"b": "c"}
149
150 csgs.assertCases(
151 commonSQLTestCase{gen: whereAndGen(csg), sql: ``},
152 commonSQLTestCase{gen: whereAndGen(csg), sql: ``, isPrepared: true, args: emptyArgs},
153
154 commonSQLTestCase{gen: whereAndGen(csg, w), sql: ` WHERE ("a" = 'b')`},
155 commonSQLTestCase{gen: whereAndGen(csg, w), sql: ` WHERE ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
156
157 commonSQLTestCase{gen: whereAndGen(csg, w, w2), sql: ` WHERE (("a" = 'b') AND ("b" = 'c'))`},
158 commonSQLTestCase{gen: whereAndGen(csg, w, w2), sql: ` WHERE (("a" = ?) AND ("b" = ?))`, isPrepared: true, args: []interface{}{"b", "c"}},
159
160 commonSQLTestCase{gen: whereOrGen(csg), sql: ``},
161 commonSQLTestCase{gen: whereOrGen(csg), sql: ``, isPrepared: true, args: emptyArgs},
162
163 commonSQLTestCase{gen: whereOrGen(csg, w), sql: ` WHERE ("a" = 'b')`},
164 commonSQLTestCase{gen: whereOrGen(csg, w), sql: ` WHERE ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
165
166 commonSQLTestCase{gen: whereOrGen(csg, w, w2), sql: ` WHERE (("a" = 'b') OR ("b" = 'c'))`},
167 commonSQLTestCase{gen: whereOrGen(csg, w, w2), sql: ` WHERE (("a" = ?) OR ("b" = ?))`, isPrepared: true, args: []interface{}{"b", "c"}},
168
169 commonSQLTestCase{gen: whereAndGen(csgWhereFrag), sql: ``},
170 commonSQLTestCase{gen: whereAndGen(csgWhereFrag), sql: ``, isPrepared: true, args: emptyArgs},
171
172 commonSQLTestCase{gen: whereAndGen(csgWhereFrag, w), sql: ` where ("a" = 'b')`},
173 commonSQLTestCase{gen: whereAndGen(csgWhereFrag, w), sql: ` where ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
174
175 commonSQLTestCase{gen: whereAndGen(csgWhereFrag, w, w2), sql: ` where (("a" = 'b') AND ("b" = 'c'))`},
176 commonSQLTestCase{
177 gen: whereAndGen(csgWhereFrag, w, w2),
178 sql: ` where (("a" = ?) AND ("b" = ?))`,
179 isPrepared: true,
180 args: []interface{}{"b", "c"},
181 },
182
183 commonSQLTestCase{gen: whereOrGen(csgWhereFrag), sql: ``},
184 commonSQLTestCase{gen: whereOrGen(csgWhereFrag), sql: ``, isPrepared: true, args: emptyArgs},
185
186 commonSQLTestCase{gen: whereOrGen(csgWhereFrag, w), sql: ` where ("a" = 'b')`},
187 commonSQLTestCase{gen: whereOrGen(csgWhereFrag, w), sql: ` where ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
188
189 commonSQLTestCase{gen: whereOrGen(csgWhereFrag, w, w2), sql: ` where (("a" = 'b') OR ("b" = 'c'))`},
190 commonSQLTestCase{
191 gen: whereOrGen(csgWhereFrag, w, w2),
192 sql: ` where (("a" = ?) OR ("b" = ?))`,
193 isPrepared: true,
194 args: []interface{}{"b", "c"},
195 },
196 )
197 }
198
199 func (csgs *commonSQLGeneratorSuite) TestOrderSQL() {
200 orderGen := func(csgs sqlgen.CommonSQLGenerator, o ...exp.OrderedExpression) func(sb.SQLBuilder) {
201 return func(sb sb.SQLBuilder) {
202 csgs.OrderSQL(sb, exp.NewOrderedColumnList(o...))
203 }
204 }
205
206 csg := sqlgen.NewCommonSQLGenerator("test", sqlgen.DefaultDialectOptions())
207
208 opts := sqlgen.DefaultDialectOptions()
209
210 opts.OrderByFragment = []byte(" order by ")
211 opts.AscFragment = []byte(" asc")
212 opts.DescFragment = []byte(" desc")
213 opts.NullsFirstFragment = []byte(" nulls first")
214 opts.NullsLastFragment = []byte(" nulls last")
215 csgCustom := sqlgen.NewCommonSQLGenerator("test", opts)
216
217 ident := exp.NewIdentifierExpression("", "", "a")
218 oa := ident.Asc()
219 oanf := ident.Asc().NullsFirst()
220 oanl := ident.Asc().NullsLast()
221
222 od := ident.Desc()
223 odnf := ident.Desc().NullsFirst()
224 odnl := ident.Desc().NullsLast()
225
226 csgs.assertCases(
227 commonSQLTestCase{gen: orderGen(csg), sql: ``},
228 commonSQLTestCase{gen: orderGen(csg), sql: ``, isPrepared: true, args: emptyArgs},
229
230 commonSQLTestCase{gen: orderGen(csg, oa), sql: ` ORDER BY "a" ASC`},
231 commonSQLTestCase{gen: orderGen(csg, oa), sql: ` ORDER BY "a" ASC`, isPrepared: true, args: emptyArgs},
232
233 commonSQLTestCase{gen: orderGen(csg, oanf), sql: ` ORDER BY "a" ASC NULLS FIRST`},
234 commonSQLTestCase{gen: orderGen(csg, oanf), sql: ` ORDER BY "a" ASC NULLS FIRST`, isPrepared: true, args: emptyArgs},
235
236 commonSQLTestCase{gen: orderGen(csg, oanl), sql: ` ORDER BY "a" ASC NULLS LAST`},
237 commonSQLTestCase{gen: orderGen(csg, oanl), sql: ` ORDER BY "a" ASC NULLS LAST`, isPrepared: true, args: emptyArgs},
238
239 commonSQLTestCase{gen: orderGen(csg, od), sql: ` ORDER BY "a" DESC`},
240 commonSQLTestCase{gen: orderGen(csg, od), sql: ` ORDER BY "a" DESC`, isPrepared: true, args: emptyArgs},
241
242 commonSQLTestCase{gen: orderGen(csg, odnf), sql: ` ORDER BY "a" DESC NULLS FIRST`},
243 commonSQLTestCase{gen: orderGen(csg, odnf), sql: ` ORDER BY "a" DESC NULLS FIRST`, isPrepared: true, args: emptyArgs},
244
245 commonSQLTestCase{gen: orderGen(csg, odnl), sql: ` ORDER BY "a" DESC NULLS LAST`},
246 commonSQLTestCase{gen: orderGen(csg, odnl), sql: ` ORDER BY "a" DESC NULLS LAST`, isPrepared: true, args: emptyArgs},
247
248 commonSQLTestCase{gen: orderGen(csg, oa, od), sql: ` ORDER BY "a" ASC, "a" DESC`},
249 commonSQLTestCase{gen: orderGen(csg, oa, od), sql: ` ORDER BY "a" ASC, "a" DESC`, isPrepared: true, args: emptyArgs},
250
251 commonSQLTestCase{gen: orderGen(csgCustom), sql: ``},
252 commonSQLTestCase{gen: orderGen(csgCustom), sql: ``, isPrepared: true, args: emptyArgs},
253
254 commonSQLTestCase{gen: orderGen(csgCustom, oa), sql: ` order by "a" asc`},
255 commonSQLTestCase{gen: orderGen(csgCustom, oa), sql: ` order by "a" asc`, isPrepared: true, args: emptyArgs},
256
257 commonSQLTestCase{gen: orderGen(csgCustom, oanf), sql: ` order by "a" asc nulls first`},
258 commonSQLTestCase{gen: orderGen(csgCustom, oanf), sql: ` order by "a" asc nulls first`, isPrepared: true, args: emptyArgs},
259
260 commonSQLTestCase{gen: orderGen(csgCustom, oanl), sql: ` order by "a" asc nulls last`},
261 commonSQLTestCase{gen: orderGen(csgCustom, oanl), sql: ` order by "a" asc nulls last`, isPrepared: true, args: emptyArgs},
262
263 commonSQLTestCase{gen: orderGen(csgCustom, od), sql: ` order by "a" desc`},
264 commonSQLTestCase{gen: orderGen(csgCustom, od), sql: ` order by "a" desc`, isPrepared: true, args: emptyArgs},
265
266 commonSQLTestCase{gen: orderGen(csgCustom, odnf), sql: ` order by "a" desc nulls first`},
267 commonSQLTestCase{gen: orderGen(csgCustom, odnf), sql: ` order by "a" desc nulls first`, isPrepared: true, args: emptyArgs},
268
269 commonSQLTestCase{gen: orderGen(csgCustom, odnl), sql: ` order by "a" desc nulls last`},
270 commonSQLTestCase{gen: orderGen(csgCustom, odnl), sql: ` order by "a" desc nulls last`, isPrepared: true, args: emptyArgs},
271
272 commonSQLTestCase{gen: orderGen(csgCustom, oa, od), sql: ` order by "a" asc, "a" desc`},
273 commonSQLTestCase{gen: orderGen(csgCustom, oa, od), sql: ` order by "a" asc, "a" desc`, isPrepared: true, args: emptyArgs},
274 )
275 }
276
277 func (csgs *commonSQLGeneratorSuite) TestLimitSQL() {
278 limitGen := func(csgs sqlgen.CommonSQLGenerator, l interface{}) func(sb.SQLBuilder) {
279 return func(sb sb.SQLBuilder) {
280 csgs.LimitSQL(sb, l)
281 }
282 }
283
284 csg := sqlgen.NewCommonSQLGenerator("test", sqlgen.DefaultDialectOptions())
285
286 opts := sqlgen.DefaultDialectOptions()
287 opts.LimitFragment = []byte(" limit ")
288 csgCustom := sqlgen.NewCommonSQLGenerator("test", opts)
289
290 l := int64(10)
291 la := exp.NewLiteralExpression("ALL")
292
293 csgs.assertCases(
294 commonSQLTestCase{gen: limitGen(csg, nil), sql: ``},
295 commonSQLTestCase{gen: limitGen(csg, nil), sql: ``, isPrepared: true, args: emptyArgs},
296
297 commonSQLTestCase{gen: limitGen(csg, l), sql: ` LIMIT 10`},
298 commonSQLTestCase{gen: limitGen(csg, l), sql: ` LIMIT ?`, isPrepared: true, args: []interface{}{l}},
299
300 commonSQLTestCase{gen: limitGen(csg, la), sql: ` LIMIT ALL`},
301 commonSQLTestCase{gen: limitGen(csg, la), sql: ` LIMIT ALL`, isPrepared: true, args: emptyArgs},
302
303 commonSQLTestCase{gen: limitGen(csgCustom, nil), sql: ``},
304 commonSQLTestCase{gen: limitGen(csgCustom, nil), sql: ``, isPrepared: true, args: emptyArgs},
305
306 commonSQLTestCase{gen: limitGen(csgCustom, l), sql: ` limit 10`},
307 commonSQLTestCase{gen: limitGen(csgCustom, l), sql: ` limit ?`, isPrepared: true, args: []interface{}{l}},
308
309 commonSQLTestCase{gen: limitGen(csgCustom, la), sql: ` limit ALL`},
310 commonSQLTestCase{gen: limitGen(csgCustom, la), sql: ` limit ALL`, isPrepared: true, args: emptyArgs},
311 )
312 }
313
314 func (csgs *commonSQLGeneratorSuite) TestUpdateExpressionSQL() {
315 updateGen := func(csgs sqlgen.CommonSQLGenerator, ues ...exp.UpdateExpression) func(sb.SQLBuilder) {
316 return func(sb sb.SQLBuilder) {
317 csgs.UpdateExpressionSQL(sb, ues...)
318 }
319 }
320
321 csg := sqlgen.NewCommonSQLGenerator("test", sqlgen.DefaultDialectOptions())
322 ue := exp.NewIdentifierExpression("", "", "col").Set("a")
323 ue2 := exp.NewIdentifierExpression("", "", "col2").Set("b")
324
325 csgs.assertCases(
326 commonSQLTestCase{gen: updateGen(csg), err: sqlgen.ErrNoUpdatedValuesProvided.Error()},
327 commonSQLTestCase{gen: updateGen(csg), err: sqlgen.ErrNoUpdatedValuesProvided.Error()},
328
329 commonSQLTestCase{gen: updateGen(csg, ue), sql: `"col"='a'`},
330 commonSQLTestCase{gen: updateGen(csg, ue), sql: `"col"=?`, isPrepared: true, args: []interface{}{"a"}},
331
332 commonSQLTestCase{gen: updateGen(csg, ue, ue2), sql: `"col"='a',"col2"='b'`},
333 commonSQLTestCase{gen: updateGen(csg, ue, ue2), sql: `"col"=?,"col2"=?`, isPrepared: true, args: []interface{}{"a", "b"}},
334 )
335 }
336
337 func TestCommonSQLGenerator(t *testing.T) {
338 suite.Run(t, new(commonSQLGeneratorSuite))
339 }
340
View as plain text