1 package sqlgen_test
2
3 import (
4 "testing"
5
6 "github.com/doug-martin/goqu/v9"
7 "github.com/doug-martin/goqu/v9/exp"
8 "github.com/doug-martin/goqu/v9/internal/errors"
9 "github.com/doug-martin/goqu/v9/internal/sb"
10 "github.com/doug-martin/goqu/v9/sqlgen"
11 "github.com/stretchr/testify/suite"
12 )
13
14 type (
15 selectTestCase struct {
16 clause exp.SelectClauses
17 sql string
18 isPrepared bool
19 args []interface{}
20 err string
21 }
22 selectSQLGeneratorSuite struct {
23 baseSQLGeneratorSuite
24 }
25 )
26
27 func (ssgs *selectSQLGeneratorSuite) assertCases(ssg sqlgen.SelectSQLGenerator, testCases ...selectTestCase) {
28 for _, tc := range testCases {
29 b := sb.NewSQLBuilder(tc.isPrepared)
30 ssg.Generate(b, tc.clause)
31 switch {
32 case len(tc.err) > 0:
33 ssgs.assertErrorSQL(b, tc.err)
34 case tc.isPrepared:
35 ssgs.assertPreparedSQL(b, tc.sql, tc.args)
36 default:
37 ssgs.assertNotPreparedSQL(b, tc.sql)
38 }
39 }
40 }
41
42 func (ssgs *selectSQLGeneratorSuite) TestDialect() {
43 opts := sqlgen.DefaultDialectOptions()
44 d := sqlgen.NewSelectSQLGenerator("test", opts)
45 ssgs.Equal("test", d.Dialect())
46
47 opts2 := sqlgen.DefaultDialectOptions()
48 d2 := sqlgen.NewSelectSQLGenerator("test2", opts2)
49 ssgs.Equal("test2", d2.Dialect())
50 }
51
52 func (ssgs *selectSQLGeneratorSuite) TestGenerate() {
53 opts := sqlgen.DefaultDialectOptions()
54 opts.SelectClause = []byte("select")
55 opts.StarRune = '#'
56
57 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
58 scWithCols := sc.SetSelect(exp.NewColumnListExpression("a", "b"))
59
60 ident := exp.NewIdentifierExpression("", "", "a")
61 scWithBooExpAliased := sc.SetSelect(
62 exp.NewColumnListExpression(
63 ident.Eq(1).As("x"),
64 ident.IsNull().As("y"),
65 ),
66 )
67
68 ssgs.assertCases(
69 sqlgen.NewSelectSQLGenerator("test", opts),
70 selectTestCase{clause: sc, sql: `select # FROM "test"`},
71 selectTestCase{clause: sc, sql: `select # FROM "test"`, isPrepared: true},
72
73 selectTestCase{clause: scWithCols, sql: `select "a", "b" FROM "test"`},
74 selectTestCase{clause: scWithCols, sql: `select "a", "b" FROM "test"`, isPrepared: true},
75
76 selectTestCase{
77 clause: scWithBooExpAliased,
78 sql: `select ("a" = 1) AS "x", ("a" IS NULL) AS "y" FROM "test"`,
79 },
80 selectTestCase{
81 clause: scWithBooExpAliased,
82 sql: `select ("a" = ?) AS "x", ("a" IS NULL) AS "y" FROM "test"`,
83 isPrepared: true,
84 args: []interface{}{int64(1)},
85 },
86 )
87 }
88
89 func (ssgs *selectSQLGeneratorSuite) TestGenerate_UnsupportedFragment() {
90 opts := sqlgen.DefaultDialectOptions()
91 opts.SelectSQLOrder = []sqlgen.SQLFragmentType{sqlgen.InsertBeingSQLFragment}
92
93 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
94 expectedErr := "goqu: unsupported SELECT SQL fragment InsertBeingSQLFragment"
95 ssgs.assertCases(
96 sqlgen.NewSelectSQLGenerator("test", opts),
97 selectTestCase{clause: sc, err: expectedErr},
98 selectTestCase{clause: sc, err: expectedErr, isPrepared: true},
99 )
100 }
101
102 func (ssgs *selectSQLGeneratorSuite) TestGenerate_WithErroredBuilder() {
103 opts := sqlgen.DefaultDialectOptions()
104 opts.SelectSQLOrder = []sqlgen.SQLFragmentType{sqlgen.InsertBeingSQLFragment}
105 d := sqlgen.NewSelectSQLGenerator("test", opts)
106
107 b := sb.NewSQLBuilder(true).SetError(errors.New("test error"))
108 c := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
109 d.Generate(b, c)
110 ssgs.assertErrorSQL(b, `goqu: test error`)
111 }
112
113 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withSelectedColumns() {
114 opts := sqlgen.DefaultDialectOptions()
115
116 opts.SelectClause = []byte("select")
117 opts.StarRune = '#'
118 opts.SupportsDistinctOn = true
119
120 sc := exp.NewSelectClauses()
121 scCols := sc.SetSelect(exp.NewColumnListExpression("a", "b"))
122 scFuncs := sc.SetSelect(exp.NewColumnListExpression(
123 exp.NewSQLFunctionExpression("COUNT", exp.Star()),
124 exp.NewSQLFunctionExpression("RANK"),
125 ))
126
127 we := exp.NewWindowExpression(
128 nil,
129 nil,
130 exp.NewColumnListExpression("a", "b"),
131 exp.NewOrderedColumnList(exp.ParseIdentifier("c").Asc()),
132 )
133 scFuncsPartition := sc.SetSelect(exp.NewColumnListExpression(
134 exp.NewSQLFunctionExpression("COUNT", exp.Star()).Over(we),
135 exp.NewSQLFunctionExpression("RANK").Over(we.Inherit("w")),
136 ))
137
138 ssgs.assertCases(
139 sqlgen.NewSelectSQLGenerator("test", opts),
140 selectTestCase{clause: sc, sql: `select #`},
141 selectTestCase{clause: sc, sql: `select #`, isPrepared: true},
142
143 selectTestCase{clause: scCols, sql: `select "a", "b"`},
144 selectTestCase{clause: scCols, sql: `select "a", "b"`, isPrepared: true},
145
146 selectTestCase{clause: scFuncs, sql: `select COUNT(*), RANK()`},
147 selectTestCase{clause: scFuncs, sql: `select COUNT(*), RANK()`, isPrepared: true},
148
149 selectTestCase{
150 clause: scFuncsPartition,
151 sql: `select COUNT(*) OVER (PARTITION BY "a", "b" ORDER BY "c" ASC), RANK() OVER ("w" PARTITION BY "a", "b" ORDER BY "c" ASC)`,
152 },
153 selectTestCase{
154 clause: scFuncsPartition,
155 sql: `select COUNT(*) OVER (PARTITION BY "a", "b" ORDER BY "c" ASC), RANK() OVER ("w" PARTITION BY "a", "b" ORDER BY "c" ASC)`,
156 isPrepared: true,
157 },
158 )
159 }
160
161 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withDistinct() {
162 opts := sqlgen.DefaultDialectOptions()
163
164 opts.SelectClause = []byte("select")
165 opts.StarRune = '#'
166 opts.DistinctFragment = []byte("distinct")
167 opts.OnFragment = []byte(" on ")
168 opts.SupportsDistinctOn = true
169
170 sc := exp.NewSelectClauses().SetDistinct(exp.NewColumnListExpression())
171 scDistinctOn := sc.SetDistinct(exp.NewColumnListExpression("a", "b"))
172
173 ssgs.assertCases(
174 sqlgen.NewSelectSQLGenerator("test", opts),
175 selectTestCase{clause: sc, sql: `select distinct #`},
176 selectTestCase{clause: sc, sql: `select distinct #`, isPrepared: true},
177
178 selectTestCase{clause: scDistinctOn, sql: `select distinct on ("a", "b") #`},
179 selectTestCase{clause: scDistinctOn, sql: `select distinct on ("a", "b") #`, isPrepared: true},
180 )
181
182 opts = sqlgen.DefaultDialectOptions()
183 opts.SupportsDistinctOn = false
184 expectedErr := "goqu: dialect does not support DISTINCT ON clause [dialect=test]"
185 ssgs.assertCases(
186 sqlgen.NewSelectSQLGenerator("test", opts),
187 selectTestCase{clause: sc, sql: `SELECT DISTINCT *`},
188 selectTestCase{clause: sc, sql: `SELECT DISTINCT *`, isPrepared: true},
189
190 selectTestCase{clause: scDistinctOn, err: expectedErr},
191 selectTestCase{clause: scDistinctOn, err: expectedErr, isPrepared: true},
192 )
193 }
194
195 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withFromSQL() {
196 opts := sqlgen.DefaultDialectOptions()
197 opts.FromFragment = []byte(" from")
198
199 sc := exp.NewSelectClauses()
200 scFrom := sc.SetFrom(exp.NewColumnListExpression("a", "b"))
201 ssgs.assertCases(
202 sqlgen.NewSelectSQLGenerator("test", opts),
203 selectTestCase{clause: sc, sql: `SELECT *`},
204 selectTestCase{clause: sc, sql: `SELECT *`, isPrepared: true},
205
206 selectTestCase{clause: scFrom, sql: `SELECT * from "a", "b"`},
207 selectTestCase{clause: scFrom, sql: `SELECT * from "a", "b"`, isPrepared: true},
208 )
209 }
210
211 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withJoin() {
212 opts := sqlgen.DefaultDialectOptions()
213
214 opts.UsingFragment = []byte(" using ")
215 opts.OnFragment = []byte(" on ")
216 opts.JoinTypeLookup = map[exp.JoinType][]byte{
217 exp.LeftJoinType: []byte(" left join "),
218 exp.NaturalJoinType: []byte(" natural join "),
219 }
220
221 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
222 ti := exp.NewIdentifierExpression("", "test2", "")
223 uj := exp.NewUnConditionedJoinExpression(exp.NaturalJoinType, ti)
224 cjo := exp.NewConditionedJoinExpression(exp.LeftJoinType, ti, exp.NewJoinOnCondition(exp.Ex{"a": "foo"}))
225 cju := exp.NewConditionedJoinExpression(exp.LeftJoinType, ti, exp.NewJoinUsingCondition("a"))
226 rj := exp.NewConditionedJoinExpression(exp.RightJoinType, ti, exp.NewJoinUsingCondition(exp.NewIdentifierExpression("", "", "a")))
227 badJoin := exp.NewConditionedJoinExpression(exp.LeftJoinType, ti, exp.NewJoinUsingCondition())
228
229 expectedRjError := "goqu: dialect does not support RightJoinType"
230 expectedJoinCondError := "goqu: join condition required for conditioned join LeftJoinType"
231 ssgs.assertCases(
232 sqlgen.NewSelectSQLGenerator("test", opts),
233 selectTestCase{clause: sc.JoinsAppend(uj), sql: `SELECT * FROM "test" natural join "test2"`},
234 selectTestCase{clause: sc.JoinsAppend(uj), sql: `SELECT * FROM "test" natural join "test2"`, isPrepared: true},
235
236 selectTestCase{clause: sc.JoinsAppend(cjo), sql: `SELECT * FROM "test" left join "test2" on ("a" = 'foo')`},
237 selectTestCase{
238 clause: sc.JoinsAppend(cjo),
239 sql: `SELECT * FROM "test" left join "test2" on ("a" = ?)`,
240 isPrepared: true,
241 args: []interface{}{"foo"},
242 },
243
244 selectTestCase{clause: sc.JoinsAppend(cju), sql: `SELECT * FROM "test" left join "test2" using ("a")`},
245 selectTestCase{clause: sc.JoinsAppend(cju), sql: `SELECT * FROM "test" left join "test2" using ("a")`, isPrepared: true},
246
247 selectTestCase{
248 clause: sc.JoinsAppend(uj).JoinsAppend(cjo).JoinsAppend(cju),
249 sql: `SELECT * FROM "test" natural join "test2" left join "test2" on ("a" = 'foo') left join "test2" using ("a")`,
250 },
251 selectTestCase{
252 clause: sc.JoinsAppend(uj).JoinsAppend(cjo).JoinsAppend(cju),
253 sql: `SELECT * FROM "test" natural join "test2" left join "test2" on ("a" = ?) left join "test2" using ("a")`,
254 isPrepared: true,
255 args: []interface{}{"foo"},
256 },
257
258 selectTestCase{clause: sc.JoinsAppend(rj), err: expectedRjError},
259 selectTestCase{clause: sc.JoinsAppend(rj), err: expectedRjError, isPrepared: true},
260
261 selectTestCase{clause: sc.JoinsAppend(badJoin), err: expectedJoinCondError},
262 selectTestCase{clause: sc.JoinsAppend(badJoin), err: expectedJoinCondError, isPrepared: true},
263 )
264 }
265
266 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withWhere() {
267 opts := sqlgen.DefaultDialectOptions()
268 opts.WhereFragment = []byte(" where ")
269
270 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
271 w := exp.Ex{"a": "b"}
272 w2 := exp.Ex{"b": "c"}
273 scWhere1 := sc.WhereAppend(w)
274 scWhere2 := sc.WhereAppend(w, w2)
275
276 ssgs.assertCases(
277 sqlgen.NewSelectSQLGenerator("test", opts),
278 selectTestCase{clause: scWhere1, sql: `SELECT * FROM "test" where ("a" = 'b')`},
279 selectTestCase{clause: scWhere1, sql: `SELECT * FROM "test" where ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
280
281 selectTestCase{clause: scWhere2, sql: `SELECT * FROM "test" where (("a" = 'b') AND ("b" = 'c'))`},
282 selectTestCase{
283 clause: scWhere2,
284 sql: `SELECT * FROM "test" where (("a" = ?) AND ("b" = ?))`,
285 isPrepared: true,
286 args: []interface{}{"b", "c"},
287 },
288 )
289 }
290
291 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withGroupBy() {
292 opts := sqlgen.DefaultDialectOptions()
293 opts.GroupByFragment = []byte(" group by ")
294
295 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
296 scGroup := sc.SetGroupBy(exp.NewColumnListExpression("a"))
297 scGroupMulti := sc.SetGroupBy(exp.NewColumnListExpression("a", "b"))
298
299 ssgs.assertCases(
300 sqlgen.NewSelectSQLGenerator("test", opts),
301 selectTestCase{clause: scGroup, sql: `SELECT * FROM "test" group by "a"`},
302 selectTestCase{clause: scGroup, sql: `SELECT * FROM "test" group by "a"`, isPrepared: true},
303
304 selectTestCase{clause: scGroupMulti, sql: `SELECT * FROM "test" group by "a", "b"`},
305 selectTestCase{clause: scGroupMulti, sql: `SELECT * FROM "test" group by "a", "b"`, isPrepared: true},
306 )
307 }
308
309 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withHaving() {
310 opts := sqlgen.DefaultDialectOptions()
311 opts.HavingFragment = []byte(" having ")
312
313 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
314 w := exp.Ex{"a": "b"}
315 w2 := exp.Ex{"b": "c"}
316 scHaving1 := sc.HavingAppend(w)
317 scHaving2 := sc.HavingAppend(w, w2)
318
319 ssgs.assertCases(
320 sqlgen.NewSelectSQLGenerator("test", opts),
321 selectTestCase{clause: scHaving1, sql: `SELECT * FROM "test" having ("a" = 'b')`},
322 selectTestCase{clause: scHaving1, sql: `SELECT * FROM "test" having ("a" = ?)`, isPrepared: true, args: []interface{}{"b"}},
323
324 selectTestCase{clause: scHaving2, sql: `SELECT * FROM "test" having (("a" = 'b') AND ("b" = 'c'))`},
325 selectTestCase{
326 clause: scHaving2,
327 sql: `SELECT * FROM "test" having (("a" = ?) AND ("b" = ?))`,
328 isPrepared: true,
329 args: []interface{}{"b", "c"},
330 },
331 )
332 }
333
334 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withWindow() {
335 opts := sqlgen.DefaultDialectOptions()
336 opts.WindowFragment = []byte(" window ")
337 opts.WindowPartitionByFragment = []byte("partition by ")
338 opts.WindowOrderByFragment = []byte("order by ")
339
340 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
341 we1 := exp.NewWindowExpression(
342 exp.NewIdentifierExpression("", "", "w"),
343 nil,
344 nil,
345 nil,
346 )
347 wePartitionBy := we1.PartitionBy("a", "b")
348 weOrderBy := we1.OrderBy("a", "b")
349
350 weOrderAndPartitionBy := we1.PartitionBy("a", "b").OrderBy("a", "b")
351
352 weInherits := exp.NewWindowExpression(
353 exp.NewIdentifierExpression("", "", "w2"),
354 exp.NewIdentifierExpression("", "", "w"),
355 nil,
356 nil,
357 )
358 weInheritsPartitionBy := weInherits.PartitionBy("c", "d")
359 weInheritsOrderBy := weInherits.OrderBy("c", "d")
360
361 weInheritsOrderAndPartitionBy := weInherits.PartitionBy("c", "d").OrderBy("c", "d")
362
363 scNoName := sc.WindowsAppend(exp.NewWindowExpression(nil, nil, nil, nil))
364
365 scWindow1 := sc.WindowsAppend(we1)
366 scWindow2 := sc.WindowsAppend(wePartitionBy)
367 scWindow3 := sc.WindowsAppend(weOrderBy)
368 scWindow4 := sc.WindowsAppend(weOrderAndPartitionBy)
369
370 scWindow5 := sc.WindowsAppend(we1, weInherits)
371 scWindow6 := sc.WindowsAppend(we1, weInheritsPartitionBy)
372 scWindow7 := sc.WindowsAppend(we1, weInheritsOrderBy)
373 scWindow8 := sc.WindowsAppend(we1, weInheritsOrderAndPartitionBy)
374
375 ssgs.assertCases(
376 sqlgen.NewSelectSQLGenerator("test", opts),
377
378 selectTestCase{clause: scNoName, err: sqlgen.ErrNoWindowName.Error()},
379 selectTestCase{clause: scNoName, err: sqlgen.ErrNoWindowName.Error(), isPrepared: true},
380
381 selectTestCase{clause: scWindow1, sql: `SELECT * FROM "test" window "w" AS ()`},
382 selectTestCase{clause: scWindow1, sql: `SELECT * FROM "test" window "w" AS ()`, isPrepared: true},
383
384 selectTestCase{clause: scWindow2, sql: `SELECT * FROM "test" window "w" AS (partition by "a", "b")`},
385 selectTestCase{
386 clause: scWindow2,
387 sql: `SELECT * FROM "test" window "w" AS (partition by "a", "b")`,
388 isPrepared: true,
389 },
390
391 selectTestCase{clause: scWindow3, sql: `SELECT * FROM "test" window "w" AS (order by "a", "b")`},
392 selectTestCase{
393 clause: scWindow3,
394 sql: `SELECT * FROM "test" window "w" AS (order by "a", "b")`,
395 isPrepared: true,
396 },
397
398 selectTestCase{
399 clause: scWindow4,
400 sql: `SELECT * FROM "test" window "w" AS (partition by "a", "b" order by "a", "b")`,
401 },
402 selectTestCase{
403 clause: scWindow4,
404 sql: `SELECT * FROM "test" window "w" AS (partition by "a", "b" order by "a", "b")`,
405 isPrepared: true,
406 },
407
408 selectTestCase{
409 clause: scWindow5,
410 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w")`,
411 },
412 selectTestCase{
413 clause: scWindow5,
414 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w")`,
415 isPrepared: true,
416 },
417
418 selectTestCase{
419 clause: scWindow6,
420 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" partition by "c", "d")`,
421 },
422 selectTestCase{
423 clause: scWindow6,
424 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" partition by "c", "d")`,
425 isPrepared: true,
426 },
427
428 selectTestCase{
429 clause: scWindow7,
430 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" order by "c", "d")`,
431 },
432 selectTestCase{
433 clause: scWindow7,
434 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" order by "c", "d")`,
435 isPrepared: true,
436 },
437
438 selectTestCase{
439 clause: scWindow8,
440 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" partition by "c", "d" order by "c", "d")`,
441 },
442 selectTestCase{
443 clause: scWindow8,
444 sql: `SELECT * FROM "test" window "w" AS (), "w2" AS ("w" partition by "c", "d" order by "c", "d")`,
445 isPrepared: true,
446 },
447 )
448
449 opts = sqlgen.DefaultDialectOptions()
450 opts.SupportsWindowFunction = false
451 ssgs.assertCases(
452 sqlgen.NewSelectSQLGenerator("test", opts),
453
454 selectTestCase{clause: scWindow1, err: sqlgen.ErrWindowNotSupported("test").Error()},
455 selectTestCase{clause: scWindow1, err: sqlgen.ErrWindowNotSupported("test").Error(), isPrepared: true},
456 )
457 }
458
459 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withOrder() {
460 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test")).
461 SetOrder(
462 exp.NewIdentifierExpression("", "", "a").Asc(),
463 exp.NewIdentifierExpression("", "", "b").Desc(),
464 )
465 ssgs.assertCases(
466 sqlgen.NewSelectSQLGenerator("test", sqlgen.DefaultDialectOptions()),
467 selectTestCase{clause: sc, sql: `SELECT * FROM "test" ORDER BY "a" ASC, "b" DESC`},
468 selectTestCase{clause: sc, sql: `SELECT * FROM "test" ORDER BY "a" ASC, "b" DESC`, isPrepared: true},
469 )
470 }
471
472 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withLimit() {
473 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test")).
474 SetLimit(10)
475 ssgs.assertCases(
476 sqlgen.NewSelectSQLGenerator("test", sqlgen.DefaultDialectOptions()),
477 selectTestCase{clause: sc, sql: `SELECT * FROM "test" LIMIT 10`},
478 selectTestCase{clause: sc, sql: `SELECT * FROM "test" LIMIT ?`, isPrepared: true, args: []interface{}{int64(10)}},
479 )
480 }
481
482 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withOffset() {
483 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test")).
484 SetOffset(10)
485 ssgs.assertCases(
486 sqlgen.NewSelectSQLGenerator("test", sqlgen.DefaultDialectOptions()),
487 selectTestCase{clause: sc, sql: `SELECT * FROM "test" OFFSET 10`},
488 selectTestCase{clause: sc, sql: `SELECT * FROM "test" OFFSET ?`, isPrepared: true, args: []interface{}{int64(10)}},
489 )
490 }
491
492 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withCommonTables() {
493 tse := newTestAppendableExpression("select * from foo", emptyArgs, nil, nil)
494
495 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test_cte"))
496 scCte1 := sc.CommonTablesAppend(exp.NewCommonTableExpression(false, "test_cte", tse))
497 scCte2 := sc.CommonTablesAppend(exp.NewCommonTableExpression(true, "test_cte", tse))
498
499 ssgs.assertCases(
500 sqlgen.NewSelectSQLGenerator("test", sqlgen.DefaultDialectOptions()),
501 selectTestCase{clause: scCte1, sql: `WITH test_cte AS (select * from foo) SELECT * FROM "test_cte"`},
502 selectTestCase{clause: scCte1, sql: `WITH test_cte AS (select * from foo) SELECT * FROM "test_cte"`, isPrepared: true},
503
504 selectTestCase{clause: scCte2, sql: `WITH RECURSIVE test_cte AS (select * from foo) SELECT * FROM "test_cte"`},
505 selectTestCase{clause: scCte2, sql: `WITH RECURSIVE test_cte AS (select * from foo) SELECT * FROM "test_cte"`, isPrepared: true},
506 )
507 }
508
509 func (ssgs *selectSQLGeneratorSuite) TestGenerate_withCompounds() {
510 tse := newTestAppendableExpression("select * from foo", emptyArgs, nil, nil)
511 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test")).
512 CompoundsAppend(exp.NewCompoundExpression(exp.UnionCompoundType, tse)).
513 CompoundsAppend(exp.NewCompoundExpression(exp.IntersectCompoundType, tse))
514
515 expectedSQL := `SELECT * FROM "test" UNION (select * from foo) INTERSECT (select * from foo)`
516 ssgs.assertCases(
517 sqlgen.NewSelectSQLGenerator("test", sqlgen.DefaultDialectOptions()),
518 selectTestCase{clause: sc, sql: expectedSQL},
519 selectTestCase{clause: sc, sql: expectedSQL, isPrepared: true},
520 )
521 }
522
523 func (ssgs *selectSQLGeneratorSuite) TestToSelectSQL_withFor() {
524 opts := sqlgen.DefaultDialectOptions()
525 opts.ForUpdateFragment = []byte(" for update ")
526 opts.ForNoKeyUpdateFragment = []byte(" for no key update ")
527 opts.ForShareFragment = []byte(" for share ")
528 opts.ForKeyShareFragment = []byte(" for key share ")
529 opts.OfFragment = []byte("of ")
530 opts.NowaitFragment = []byte("nowait")
531 opts.SkipLockedFragment = []byte("skip locked")
532
533 sc := exp.NewSelectClauses().SetFrom(exp.NewColumnListExpression("test"))
534 scFnW := sc.SetLock(exp.NewLock(exp.ForNolock, exp.Wait))
535 scFnNw := sc.SetLock(exp.NewLock(exp.ForNolock, exp.NoWait))
536 scFnSl := sc.SetLock(exp.NewLock(exp.ForNolock, exp.SkipLocked))
537 scFnSlOf := sc.SetLock(exp.NewLock(exp.ForNolock, exp.SkipLocked, goqu.T("my_table")))
538
539 scFsW := sc.SetLock(exp.NewLock(exp.ForShare, exp.Wait))
540 scFsNw := sc.SetLock(exp.NewLock(exp.ForShare, exp.NoWait))
541 scFsSl := sc.SetLock(exp.NewLock(exp.ForShare, exp.SkipLocked))
542 scFsSlOf := sc.SetLock(exp.NewLock(exp.ForShare, exp.SkipLocked, goqu.T("my_table")))
543 scFsSlOfMulti := sc.SetLock(exp.NewLock(exp.ForShare, exp.SkipLocked, goqu.T("my_table"), goqu.T("table2")))
544
545 scFksW := sc.SetLock(exp.NewLock(exp.ForKeyShare, exp.Wait))
546 scFksNw := sc.SetLock(exp.NewLock(exp.ForKeyShare, exp.NoWait))
547 scFksSl := sc.SetLock(exp.NewLock(exp.ForKeyShare, exp.SkipLocked))
548
549 scFuW := sc.SetLock(exp.NewLock(exp.ForUpdate, exp.Wait))
550 scFuNw := sc.SetLock(exp.NewLock(exp.ForUpdate, exp.NoWait))
551 scFuSl := sc.SetLock(exp.NewLock(exp.ForUpdate, exp.SkipLocked))
552
553 scFkuW := sc.SetLock(exp.NewLock(exp.ForNoKeyUpdate, exp.Wait))
554 scFkuNw := sc.SetLock(exp.NewLock(exp.ForNoKeyUpdate, exp.NoWait))
555 scFkuSl := sc.SetLock(exp.NewLock(exp.ForNoKeyUpdate, exp.SkipLocked))
556 ssgs.assertCases(
557 sqlgen.NewSelectSQLGenerator("test", opts),
558 selectTestCase{clause: scFnW, sql: `SELECT * FROM "test"`},
559 selectTestCase{clause: scFnW, sql: `SELECT * FROM "test"`, isPrepared: true},
560
561 selectTestCase{clause: scFnNw, sql: `SELECT * FROM "test"`},
562 selectTestCase{clause: scFnNw, sql: `SELECT * FROM "test"`, isPrepared: true},
563
564 selectTestCase{clause: scFnSl, sql: `SELECT * FROM "test"`},
565 selectTestCase{clause: scFnSl, sql: `SELECT * FROM "test"`, isPrepared: true},
566 selectTestCase{clause: scFnSlOf, sql: `SELECT * FROM "test"`},
567 selectTestCase{clause: scFnSlOf, sql: `SELECT * FROM "test"`, isPrepared: true, args: []interface{}{}},
568
569 selectTestCase{clause: scFsW, sql: `SELECT * FROM "test" for share `},
570 selectTestCase{clause: scFsW, sql: `SELECT * FROM "test" for share `, isPrepared: true},
571
572 selectTestCase{clause: scFsNw, sql: `SELECT * FROM "test" for share nowait`},
573 selectTestCase{clause: scFsNw, sql: `SELECT * FROM "test" for share nowait`, isPrepared: true},
574
575 selectTestCase{clause: scFsSl, sql: `SELECT * FROM "test" for share skip locked`},
576 selectTestCase{clause: scFsSl, sql: `SELECT * FROM "test" for share skip locked`, isPrepared: true},
577
578 selectTestCase{clause: scFsSlOf, sql: `SELECT * FROM "test" for share of "my_table" skip locked`},
579 selectTestCase{clause: scFsSlOf, sql: `SELECT * FROM "test" for share of "my_table" skip locked`, isPrepared: true},
580
581 selectTestCase{clause: scFsSlOfMulti, sql: `SELECT * FROM "test" for share of "my_table", "table2" skip locked`},
582 selectTestCase{clause: scFsSlOfMulti, sql: `SELECT * FROM "test" for share of "my_table", "table2" skip locked`, isPrepared: true},
583
584 selectTestCase{clause: scFksW, sql: `SELECT * FROM "test" for key share `},
585 selectTestCase{clause: scFksW, sql: `SELECT * FROM "test" for key share `, isPrepared: true},
586
587 selectTestCase{clause: scFksNw, sql: `SELECT * FROM "test" for key share nowait`},
588 selectTestCase{clause: scFksNw, sql: `SELECT * FROM "test" for key share nowait`, isPrepared: true},
589
590 selectTestCase{clause: scFksSl, sql: `SELECT * FROM "test" for key share skip locked`},
591 selectTestCase{clause: scFksSl, sql: `SELECT * FROM "test" for key share skip locked`, isPrepared: true},
592
593 selectTestCase{clause: scFuW, sql: `SELECT * FROM "test" for update `},
594 selectTestCase{clause: scFuW, sql: `SELECT * FROM "test" for update `, isPrepared: true},
595
596 selectTestCase{clause: scFuNw, sql: `SELECT * FROM "test" for update nowait`},
597 selectTestCase{clause: scFuNw, sql: `SELECT * FROM "test" for update nowait`, isPrepared: true},
598
599 selectTestCase{clause: scFuSl, sql: `SELECT * FROM "test" for update skip locked`},
600 selectTestCase{clause: scFuSl, sql: `SELECT * FROM "test" for update skip locked`, isPrepared: true},
601
602 selectTestCase{clause: scFkuW, sql: `SELECT * FROM "test" for no key update `},
603 selectTestCase{clause: scFkuW, sql: `SELECT * FROM "test" for no key update `, isPrepared: true},
604
605 selectTestCase{clause: scFkuNw, sql: `SELECT * FROM "test" for no key update nowait`},
606 selectTestCase{clause: scFkuNw, sql: `SELECT * FROM "test" for no key update nowait`, isPrepared: true},
607
608 selectTestCase{clause: scFkuSl, sql: `SELECT * FROM "test" for no key update skip locked`},
609 selectTestCase{clause: scFkuSl, sql: `SELECT * FROM "test" for no key update skip locked`, isPrepared: true},
610 )
611 }
612
613 func TestSelectSQLGenerator(t *testing.T) {
614 suite.Run(t, new(selectSQLGeneratorSuite))
615 }
616
View as plain text