1 package sqlgen_test
2
3 import (
4 "database/sql/driver"
5 "fmt"
6 "regexp"
7 "testing"
8 "time"
9
10 "github.com/doug-martin/goqu/v9/exp"
11 "github.com/doug-martin/goqu/v9/internal/errors"
12 "github.com/doug-martin/goqu/v9/internal/sb"
13 "github.com/doug-martin/goqu/v9/sqlgen"
14 "github.com/stretchr/testify/suite"
15 )
16
17 var emptyArgs = make([]interface{}, 0)
18
19 type testAppendableExpression struct {
20 sql string
21 args []interface{}
22 err error
23 alias exp.IdentifierExpression
24 returnsColumns bool
25 }
26
27 func newTestAppendableExpression(
28 sql string,
29 args []interface{},
30 err error,
31 alias exp.IdentifierExpression) exp.AppendableExpression {
32 return &testAppendableExpression{sql: sql, args: args, err: err, alias: alias}
33 }
34
35 func (tae *testAppendableExpression) Expression() exp.Expression {
36 return tae
37 }
38
39 func (tae *testAppendableExpression) Clone() exp.Expression {
40 return tae
41 }
42
43 func (tae *testAppendableExpression) GetAs() exp.IdentifierExpression {
44 return tae.alias
45 }
46
47 func (tae *testAppendableExpression) ReturnsColumns() bool {
48 return tae.returnsColumns
49 }
50
51 func (tae *testAppendableExpression) AppendSQL(b sb.SQLBuilder) {
52 if tae.err != nil {
53 b.SetError(tae.err)
54 return
55 }
56 b.WriteStrings(tae.sql)
57 if len(tae.args) > 0 {
58 b.WriteArg(tae.args...)
59 }
60 }
61
62 type (
63 expressionTestCase struct {
64 val interface{}
65 sql string
66 err string
67 isPrepared bool
68 args []interface{}
69 }
70 expressionSQLGeneratorSuite struct {
71 suite.Suite
72 }
73 )
74
75 func (esgs *expressionSQLGeneratorSuite) assertCases(esg sqlgen.ExpressionSQLGenerator, cases ...expressionTestCase) {
76 for i, c := range cases {
77 b := sb.NewSQLBuilder(c.isPrepared)
78 esg.Generate(b, c.val)
79 actualSQL, actualArgs, err := b.ToSQL()
80 if c.err == "" {
81 esgs.NoError(err, "test case %d failed", i)
82 } else {
83 esgs.EqualError(err, c.err, "test case %d failed", i)
84 }
85 esgs.Equal(c.sql, actualSQL, "test case %d failed", i)
86 if c.isPrepared && c.args != nil || len(c.args) > 0 {
87 esgs.Equal(c.args, actualArgs, "test case %d failed", i)
88 } else {
89 esgs.Empty(actualArgs, "test case %d failed", i)
90 }
91 }
92 }
93
94 func (esgs *expressionSQLGeneratorSuite) TestDialect() {
95 esg := sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions())
96 esgs.Equal("test", esg.Dialect())
97 }
98
99 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ErroredBuilder() {
100 esg := sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions())
101 expectedErr := errors.New("test error")
102 b := sb.NewSQLBuilder(false).SetError(expectedErr)
103 esg.Generate(b, 1)
104 sql, args, err := b.ToSQL()
105 esgs.Equal(expectedErr, err)
106 esgs.Empty(sql)
107 esgs.Empty(args)
108
109 b = sb.NewSQLBuilder(true).SetError(err)
110 esg.Generate(b, true)
111 sql, args, err = b.ToSQL()
112 esgs.Equal(expectedErr, err)
113 esgs.Empty(sql)
114 esgs.Empty(args)
115 }
116
117 func (esgs *expressionSQLGeneratorSuite) TestGenerate_Invalid() {
118 var b *bool
119 esgs.assertCases(
120 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
121 expressionTestCase{val: b, sql: "NULL"},
122 expressionTestCase{val: b, sql: "?", isPrepared: true, args: []interface{}{nil}},
123 )
124 }
125
126 func (esgs *expressionSQLGeneratorSuite) TestGenerate_UnsupportedType() {
127 type strct struct{}
128 esgs.assertCases(
129 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
130 expressionTestCase{val: strct{}, err: "goqu_encode_error: Unable to encode value {}"},
131 expressionTestCase{val: strct{}, err: "goqu_encode_error: Unable to encode value {}", isPrepared: true},
132 )
133 }
134
135 func (esgs *expressionSQLGeneratorSuite) TestGenerate_IncludePlaceholderNum() {
136 opts := sqlgen.DefaultDialectOptions()
137 opts.IncludePlaceholderNum = true
138 opts.PlaceHolderFragment = []byte("$")
139 ex := exp.Ex{
140 "a": 1,
141 "b": true,
142 "c": false,
143 "d": []string{"a", "b", "c"},
144 }
145 esgs.assertCases(
146 sqlgen.NewExpressionSQLGenerator("test", opts),
147 expressionTestCase{
148 val: ex,
149 sql: `(("a" = 1) AND ("b" IS TRUE) AND ("c" IS FALSE) AND ("d" IN ('a', 'b', 'c')))`,
150 },
151 expressionTestCase{
152 val: ex,
153 sql: `(("a" = $1) AND ("b" IS TRUE) AND ("c" IS FALSE) AND ("d" IN ($2, $3, $4)))`,
154 isPrepared: true,
155 args: []interface{}{int64(1), "a", "b", "c"},
156 },
157 )
158 }
159
160 func (esgs *expressionSQLGeneratorSuite) TestGenerate_FloatTypes() {
161 var float float64
162 esgs.assertCases(
163 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
164 expressionTestCase{val: float32(10.01), sql: "10.010000228881836"},
165 expressionTestCase{val: float32(10.01), sql: "?", isPrepared: true, args: []interface{}{float64(float32(10.01))}},
166
167 expressionTestCase{val: float64(10.01), sql: "10.01"},
168 expressionTestCase{val: float64(10.01), sql: "?", isPrepared: true, args: []interface{}{float64(10.01)}},
169
170 expressionTestCase{val: &float, sql: "0"},
171 expressionTestCase{val: &float, sql: "?", isPrepared: true, args: []interface{}{float}},
172 )
173 }
174
175 func (esgs *expressionSQLGeneratorSuite) TestGenerate_IntTypes() {
176 var i int64
177 ints := []interface{}{
178 int(10),
179 int16(10),
180 int32(10),
181 int64(10),
182 uint(10),
183 uint16(10),
184 uint32(10),
185 uint64(10),
186 }
187 for _, i := range ints {
188 esgs.assertCases(
189 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
190 expressionTestCase{val: i, sql: "10"},
191 expressionTestCase{val: i, sql: "?", isPrepared: true, args: []interface{}{int64(10)}},
192 )
193 }
194 esgs.assertCases(
195 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
196 expressionTestCase{val: &i, sql: "0"},
197 expressionTestCase{val: &i, sql: "?", isPrepared: true, args: []interface{}{i}},
198 )
199 }
200
201 func (esgs *expressionSQLGeneratorSuite) TestGenerate_StringTypes() {
202 var str string
203 esgs.assertCases(
204 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
205 expressionTestCase{val: "Hello", sql: "'Hello'"},
206 expressionTestCase{val: "Hello", sql: "?", isPrepared: true, args: []interface{}{"Hello"}},
207
208 expressionTestCase{val: "Hello'", sql: "'Hello'''"},
209 expressionTestCase{val: "Hello'", sql: "?", isPrepared: true, args: []interface{}{"Hello'"}},
210
211 expressionTestCase{val: &str, sql: "''"},
212 expressionTestCase{val: &str, sql: "?", isPrepared: true, args: []interface{}{str}},
213 )
214 }
215
216 func (esgs *expressionSQLGeneratorSuite) TestGenerate_BytesTypes() {
217 esgs.assertCases(
218 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
219 expressionTestCase{val: []byte("Hello"), sql: "'Hello'"},
220 expressionTestCase{val: []byte("Hello"), sql: "?", isPrepared: true, args: []interface{}{[]byte("Hello")}},
221
222 expressionTestCase{val: []byte("Hello'"), sql: "'Hello'''"},
223 expressionTestCase{val: []byte("Hello'"), sql: "?", isPrepared: true, args: []interface{}{[]byte("Hello'")}},
224 )
225 }
226
227 func (esgs *expressionSQLGeneratorSuite) TestGenerate_BoolTypes() {
228 var bl bool
229 esgs.assertCases(
230 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
231 expressionTestCase{val: true, sql: "TRUE"},
232 expressionTestCase{val: true, sql: "?", isPrepared: true, args: []interface{}{true}},
233
234 expressionTestCase{val: false, sql: "FALSE"},
235 expressionTestCase{val: false, sql: "?", isPrepared: true, args: []interface{}{false}},
236
237 expressionTestCase{val: &bl, sql: "FALSE"},
238 expressionTestCase{val: &bl, sql: "?", isPrepared: true, args: []interface{}{bl}},
239 )
240 }
241
242 func (esgs *expressionSQLGeneratorSuite) TestGenerate_TimeTypes() {
243 var nt *time.Time
244
245 ts, err := time.Parse(time.RFC3339, "2019-10-01T15:01:00Z")
246 esgs.Require().NoError(err)
247 originalLoc := sqlgen.GetTimeLocation()
248
249 loc, err := time.LoadLocation("Asia/Shanghai")
250 esgs.Require().NoError(err)
251
252 sqlgen.SetTimeLocation(loc)
253
254 esgs.assertCases(
255 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
256 expressionTestCase{val: ts, sql: "'2019-10-01T23:01:00+08:00'"},
257 expressionTestCase{val: ts, sql: "?", isPrepared: true, args: []interface{}{ts}},
258
259 expressionTestCase{val: &ts, sql: "'2019-10-01T23:01:00+08:00'"},
260 expressionTestCase{val: &ts, sql: "?", isPrepared: true, args: []interface{}{ts}},
261 )
262 sqlgen.SetTimeLocation(time.UTC)
263
264 esgs.assertCases(
265 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
266 expressionTestCase{val: ts, sql: "'2019-10-01T15:01:00Z'"},
267 expressionTestCase{val: ts, sql: "?", isPrepared: true, args: []interface{}{ts}},
268
269 expressionTestCase{val: &ts, sql: "'2019-10-01T15:01:00Z'"},
270 expressionTestCase{val: &ts, sql: "?", isPrepared: true, args: []interface{}{ts}},
271 )
272 esgs.assertCases(
273 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
274 expressionTestCase{val: nt, sql: "NULL"},
275 expressionTestCase{val: nt, sql: "?", isPrepared: true, args: []interface{}{nil}},
276 )
277 sqlgen.SetTimeLocation(originalLoc)
278 }
279
280 func (esgs *expressionSQLGeneratorSuite) TestGenerate_NilTypes() {
281 esgs.assertCases(
282 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
283 expressionTestCase{val: nil, sql: "NULL"},
284 expressionTestCase{val: nil, sql: "?", isPrepared: true, args: []interface{}{nil}},
285 )
286 }
287
288 type datasetValuerType struct {
289 int int64
290 err error
291 }
292
293 func (j datasetValuerType) Value() (driver.Value, error) {
294 if j.err != nil {
295 return nil, j.err
296 }
297 return []byte(fmt.Sprintf("Hello World %d", j.int)), nil
298 }
299
300 func (esgs *expressionSQLGeneratorSuite) TestGenerate_Valuer() {
301 err := errors.New("valuer error")
302 var val *datasetValuerType
303 esgs.assertCases(
304 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
305 expressionTestCase{val: datasetValuerType{int: 10}, sql: "'Hello World 10'"},
306 expressionTestCase{
307 val: datasetValuerType{int: 10}, sql: "?", isPrepared: true, args: []interface{}{[]byte("Hello World 10")},
308 },
309
310 expressionTestCase{val: datasetValuerType{err: err}, err: "goqu: valuer error"},
311 expressionTestCase{
312 val: datasetValuerType{err: err}, isPrepared: true, err: "goqu: valuer error",
313 },
314 expressionTestCase{
315 val: val, sql: "NULL",
316 },
317 )
318 }
319
320 func (esgs *expressionSQLGeneratorSuite) TestGenerate_Slice() {
321 esgs.assertCases(
322 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
323 expressionTestCase{val: []string{"a", "b", "c"}, sql: `('a', 'b', 'c')`},
324 expressionTestCase{
325 val: []string{"a", "b", "c"}, sql: "(?, ?, ?)", isPrepared: true, args: []interface{}{"a", "b", "c"},
326 },
327
328 expressionTestCase{val: []byte{'a', 'b', 'c'}, sql: `'abc'`},
329 expressionTestCase{
330 val: []byte{'a', 'b', 'c'}, sql: "?", isPrepared: true, args: []interface{}{[]byte{'a', 'b', 'c'}},
331 },
332 )
333 }
334
335 type unknownExpression struct{}
336
337 func (ue unknownExpression) Expression() exp.Expression {
338 return ue
339 }
340
341 func (ue unknownExpression) Clone() exp.Expression {
342 return ue
343 }
344
345 func (esgs *expressionSQLGeneratorSuite) TestGenerateUnsupportedExpression() {
346 errMsg := "goqu: unsupported expression type sqlgen_test.unknownExpression"
347 esgs.assertCases(
348 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
349 expressionTestCase{val: unknownExpression{}, err: errMsg},
350 expressionTestCase{
351 val: unknownExpression{}, isPrepared: true, err: errMsg,
352 },
353 )
354 }
355
356 func (esgs *expressionSQLGeneratorSuite) TestGenerate_AppendableExpression() {
357 ti := exp.NewIdentifierExpression("", "b", "")
358 a := newTestAppendableExpression(`select * from "a"`, []interface{}{}, nil, nil)
359 aliasedA := newTestAppendableExpression(`select * from "a"`, []interface{}{}, nil, ti)
360 argsA := newTestAppendableExpression(`select * from "a" where x=?`, []interface{}{true}, nil, ti)
361 ae := newTestAppendableExpression(`select * from "a"`, emptyArgs, errors.New("expected error"), nil)
362
363 esgs.assertCases(
364 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
365 expressionTestCase{val: a, sql: `(select * from "a")`},
366 expressionTestCase{val: a, sql: `(select * from "a")`, isPrepared: true},
367
368 expressionTestCase{val: aliasedA, sql: `(select * from "a") AS "b"`},
369 expressionTestCase{val: aliasedA, sql: `(select * from "a") AS "b"`, isPrepared: true},
370
371 expressionTestCase{val: ae, err: "goqu: expected error"},
372 expressionTestCase{val: ae, err: "goqu: expected error", isPrepared: true},
373
374 expressionTestCase{val: argsA, sql: `(select * from "a" where x=?) AS "b"`, args: []interface{}{true}},
375 expressionTestCase{val: argsA, sql: `(select * from "a" where x=?) AS "b"`, isPrepared: true, args: []interface{}{true}},
376 )
377 }
378
379 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ColumnList() {
380 cl := exp.NewColumnListExpression("a", exp.NewLiteralExpression("true"))
381 esgs.assertCases(
382 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
383 expressionTestCase{val: cl, sql: `"a", true`},
384 expressionTestCase{val: cl, sql: `"a", true`, isPrepared: true},
385 )
386 }
387
388 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionList() {
389 andEl := exp.NewExpressionList(
390 exp.AndType,
391 exp.NewIdentifierExpression("", "", "a").Eq("b"),
392 exp.NewIdentifierExpression("", "", "c").Neq(1),
393 )
394
395 orEl := exp.NewExpressionList(
396 exp.OrType,
397 exp.NewIdentifierExpression("", "", "a").Eq("b"),
398 exp.NewIdentifierExpression("", "", "c").Neq(1),
399 )
400
401 andOrEl := exp.NewExpressionList(exp.OrType,
402 exp.NewIdentifierExpression("", "", "a").Eq("b"),
403 exp.NewExpressionList(exp.AndType,
404 exp.NewIdentifierExpression("", "", "c").Neq(1),
405 exp.NewIdentifierExpression("", "", "d").Eq(exp.NewLiteralExpression("NOW()")),
406 ),
407 )
408
409 esgs.assertCases(
410 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
411 expressionTestCase{val: andEl, sql: `(("a" = 'b') AND ("c" != 1))`},
412 expressionTestCase{
413 val: andEl, sql: `(("a" = ?) AND ("c" != ?))`, isPrepared: true, args: []interface{}{"b", int64(1)},
414 },
415
416 expressionTestCase{val: orEl, sql: `(("a" = 'b') OR ("c" != 1))`},
417 expressionTestCase{
418 val: orEl, sql: `(("a" = ?) OR ("c" != ?))`, isPrepared: true, args: []interface{}{"b", int64(1)},
419 },
420
421 expressionTestCase{val: andOrEl, sql: `(("a" = 'b') OR (("c" != 1) AND ("d" = NOW())))`},
422 expressionTestCase{
423 val: andOrEl,
424 sql: `(("a" = ?) OR (("c" != ?) AND ("d" = NOW())))`,
425 isPrepared: true,
426 args: []interface{}{"b", int64(1)},
427 },
428 )
429 }
430
431 func (esgs *expressionSQLGeneratorSuite) TestGenerate_LiteralExpression() {
432 noArgsL := exp.NewLiteralExpression(`"b"::DATE = '2010-09-02'`)
433 argsL := exp.NewLiteralExpression(`"b" = ? or "c" = ? or d IN ?`, "a", 1, []int{1, 2, 3, 4})
434
435 esgs.assertCases(
436 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
437 expressionTestCase{val: noArgsL, sql: `"b"::DATE = '2010-09-02'`},
438 expressionTestCase{val: noArgsL, sql: `"b"::DATE = '2010-09-02'`, isPrepared: true},
439
440 expressionTestCase{val: argsL, sql: `"b" = 'a' or "c" = 1 or d IN (1, 2, 3, 4)`},
441 expressionTestCase{
442 val: argsL,
443 sql: `"b" = ? or "c" = ? or d IN (?, ?, ?, ?)`,
444 isPrepared: true,
445 args: []interface{}{
446 "a",
447 int64(1),
448 int64(1),
449 int64(2),
450 int64(3),
451 int64(4),
452 },
453 },
454 )
455 }
456
457 func (esgs *expressionSQLGeneratorSuite) TestGenerate_AliasedExpression() {
458 aliasedI := exp.NewIdentifierExpression("", "", "a").As("b")
459 aliasedWithII := exp.NewIdentifierExpression("", "", "a").
460 As(exp.NewIdentifierExpression("", "", "b"))
461 aliasedL := exp.NewLiteralExpression("count(*)").As("count")
462
463 esgs.assertCases(
464 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
465 expressionTestCase{val: aliasedI, sql: `"a" AS "b"`},
466 expressionTestCase{val: aliasedI, sql: `"a" AS "b"`, isPrepared: true},
467
468 expressionTestCase{val: aliasedWithII, sql: `"a" AS "b"`},
469 expressionTestCase{val: aliasedWithII, sql: `"a" AS "b"`, isPrepared: true},
470
471 expressionTestCase{val: aliasedL, sql: `count(*) AS "count"`},
472 expressionTestCase{val: aliasedL, sql: `count(*) AS "count"`, isPrepared: true},
473 )
474 }
475
476 func (esgs *expressionSQLGeneratorSuite) TestGenerate_BooleanExpressionAliased() {
477 ident := exp.NewIdentifierExpression("", "", "a")
478
479 esgs.assertCases(
480 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
481 expressionTestCase{val: ident.Eq(1).As("b"), sql: `("a" = 1) AS "b"`},
482 expressionTestCase{val: ident.Eq(1).As("b"), sql: `("a" = ?) AS "b"`,
483 isPrepared: true, args: []interface{}{int64(1)}},
484 )
485 }
486 func (esgs *expressionSQLGeneratorSuite) TestGenerate_BooleanExpression() {
487 ae := newTestAppendableExpression(`SELECT "id" FROM "test2"`, emptyArgs, nil, nil)
488 re := regexp.MustCompile("[ab]")
489 ident := exp.NewIdentifierExpression("", "", "a")
490
491 esgs.assertCases(
492 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
493 expressionTestCase{val: ident.Eq(1), sql: `("a" = 1)`},
494 expressionTestCase{val: ident.Eq(1), sql: `("a" = ?)`, isPrepared: true, args: []interface{}{int64(1)}},
495
496 expressionTestCase{val: ident.Eq(true), sql: `("a" IS TRUE)`},
497 expressionTestCase{val: ident.Eq(true), sql: `("a" IS TRUE)`, isPrepared: true},
498
499 expressionTestCase{val: ident.Eq(false), sql: `("a" IS FALSE)`},
500 expressionTestCase{val: ident.Eq(false), sql: `("a" IS FALSE)`, isPrepared: true},
501
502 expressionTestCase{val: ident.Eq(nil), sql: `("a" IS NULL)`},
503 expressionTestCase{val: ident.Eq(nil), sql: `("a" IS NULL)`, isPrepared: true},
504
505 expressionTestCase{val: ident.Eq([]int64{1, 2, 3}), sql: `("a" IN (1, 2, 3))`},
506 expressionTestCase{val: ident.Eq([]int64{1, 2, 3}), sql: `("a" IN (?, ?, ?))`, isPrepared: true, args: []interface{}{
507 int64(1), int64(2), int64(3),
508 }},
509
510 expressionTestCase{val: ident.Eq(ae), sql: `("a" IN (SELECT "id" FROM "test2"))`},
511 expressionTestCase{val: ident.Eq(ae), sql: `("a" IN (SELECT "id" FROM "test2"))`, isPrepared: true},
512
513 expressionTestCase{val: ident.Neq(1), sql: `("a" != 1)`},
514 expressionTestCase{val: ident.Neq(1), sql: `("a" != ?)`, isPrepared: true, args: []interface{}{int64(1)}},
515
516 expressionTestCase{val: ident.Neq(true), sql: `("a" IS NOT TRUE)`},
517 expressionTestCase{val: ident.Neq(true), sql: `("a" IS NOT TRUE)`, isPrepared: true},
518
519 expressionTestCase{val: ident.Neq(false), sql: `("a" IS NOT FALSE)`},
520 expressionTestCase{val: ident.Neq(false), sql: `("a" IS NOT FALSE)`, isPrepared: true},
521
522 expressionTestCase{val: ident.Neq(nil), sql: `("a" IS NOT NULL)`},
523 expressionTestCase{val: ident.Neq(nil), sql: `("a" IS NOT NULL)`, isPrepared: true},
524
525 expressionTestCase{val: ident.Neq([]int64{1, 2, 3}), sql: `("a" NOT IN (1, 2, 3))`},
526 expressionTestCase{val: ident.Neq([]int64{1, 2, 3}), sql: `("a" NOT IN (?, ?, ?))`, isPrepared: true, args: []interface{}{
527 int64(1), int64(2), int64(3),
528 }},
529
530 expressionTestCase{val: ident.Neq(ae), sql: `("a" NOT IN (SELECT "id" FROM "test2"))`},
531 expressionTestCase{val: ident.Neq(ae), sql: `("a" NOT IN (SELECT "id" FROM "test2"))`, isPrepared: true},
532
533 expressionTestCase{val: ident.Is(true), sql: `("a" IS TRUE)`},
534 expressionTestCase{val: ident.Is(true), sql: `("a" IS TRUE)`, isPrepared: true},
535
536 expressionTestCase{val: ident.Is(false), sql: `("a" IS FALSE)`},
537 expressionTestCase{val: ident.Is(false), sql: `("a" IS FALSE)`, isPrepared: true},
538
539 expressionTestCase{val: ident.Is(nil), sql: `("a" IS NULL)`},
540 expressionTestCase{val: ident.Is(nil), sql: `("a" IS NULL)`, isPrepared: true},
541
542 expressionTestCase{val: ident.IsNot(true), sql: `("a" IS NOT TRUE)`},
543 expressionTestCase{val: ident.IsNot(true), sql: `("a" IS NOT TRUE)`, isPrepared: true},
544
545 expressionTestCase{val: ident.IsNot(false), sql: `("a" IS NOT FALSE)`},
546 expressionTestCase{val: ident.IsNot(false), sql: `("a" IS NOT FALSE)`, isPrepared: true},
547
548 expressionTestCase{val: ident.IsNot(nil), sql: `("a" IS NOT NULL)`},
549 expressionTestCase{val: ident.IsNot(nil), sql: `("a" IS NOT NULL)`, isPrepared: true},
550
551 expressionTestCase{val: ident.Gt(1), sql: `("a" > 1)`},
552 expressionTestCase{val: ident.Gt(1), sql: `("a" > ?)`, isPrepared: true, args: []interface{}{int64(1)}},
553
554 expressionTestCase{val: ident.Gte(1), sql: `("a" >= 1)`},
555 expressionTestCase{val: ident.Gte(1), sql: `("a" >= ?)`, isPrepared: true, args: []interface{}{int64(1)}},
556
557 expressionTestCase{val: ident.Lt(1), sql: `("a" < 1)`},
558 expressionTestCase{val: ident.Lt(1), sql: `("a" < ?)`, isPrepared: true, args: []interface{}{int64(1)}},
559
560 expressionTestCase{val: ident.Lte(1), sql: `("a" <= 1)`},
561 expressionTestCase{val: ident.Lte(1), sql: `("a" <= ?)`, isPrepared: true, args: []interface{}{int64(1)}},
562
563 expressionTestCase{val: ident.In([]int64{1, 2, 3}), sql: `("a" IN (1, 2, 3))`},
564 expressionTestCase{val: ident.In([]int64{1, 2, 3}), sql: `("a" IN (?, ?, ?))`, isPrepared: true, args: []interface{}{
565 int64(1), int64(2), int64(3),
566 }},
567
568 expressionTestCase{val: ident.In(ae), sql: `("a" IN ((SELECT "id" FROM "test2")))`},
569 expressionTestCase{val: ident.In(ae), sql: `("a" IN ((SELECT "id" FROM "test2")))`, isPrepared: true},
570
571 expressionTestCase{val: ident.NotIn([]int64{1, 2, 3}), sql: `("a" NOT IN (1, 2, 3))`},
572 expressionTestCase{val: ident.NotIn([]int64{1, 2, 3}), sql: `("a" NOT IN (?, ?, ?))`, isPrepared: true, args: []interface{}{
573 int64(1), int64(2), int64(3),
574 }},
575
576 expressionTestCase{val: ident.NotIn(ae), sql: `("a" NOT IN ((SELECT "id" FROM "test2")))`},
577 expressionTestCase{val: ident.NotIn(ae), sql: `("a" NOT IN ((SELECT "id" FROM "test2")))`, isPrepared: true},
578
579 expressionTestCase{val: ident.Like("a%"), sql: `("a" LIKE 'a%')`},
580 expressionTestCase{val: ident.Like("a%"), sql: `("a" LIKE ?)`, isPrepared: true, args: []interface{}{"a%"}},
581
582 expressionTestCase{val: ident.Like(re), sql: `("a" ~ '[ab]')`},
583 expressionTestCase{val: ident.Like(re), sql: `("a" ~ ?)`, isPrepared: true, args: []interface{}{"[ab]"}},
584
585 expressionTestCase{val: ident.ILike("a%"), sql: `("a" ILIKE 'a%')`},
586 expressionTestCase{val: ident.ILike("a%"), sql: `("a" ILIKE ?)`, isPrepared: true, args: []interface{}{"a%"}},
587
588 expressionTestCase{val: ident.ILike(re), sql: `("a" ~* '[ab]')`},
589 expressionTestCase{val: ident.ILike(re), sql: `("a" ~* ?)`, isPrepared: true, args: []interface{}{"[ab]"}},
590
591 expressionTestCase{val: ident.NotLike("a%"), sql: `("a" NOT LIKE 'a%')`},
592 expressionTestCase{val: ident.NotLike("a%"), sql: `("a" NOT LIKE ?)`, isPrepared: true, args: []interface{}{"a%"}},
593
594 expressionTestCase{val: ident.NotLike(re), sql: `("a" !~ '[ab]')`},
595 expressionTestCase{val: ident.NotLike(re), sql: `("a" !~ ?)`, isPrepared: true, args: []interface{}{"[ab]"}},
596
597 expressionTestCase{val: ident.NotILike("a%"), sql: `("a" NOT ILIKE 'a%')`},
598 expressionTestCase{val: ident.NotILike("a%"), sql: `("a" NOT ILIKE ?)`, isPrepared: true, args: []interface{}{"a%"}},
599
600 expressionTestCase{val: ident.NotILike(re), sql: `("a" !~* '[ab]')`},
601 expressionTestCase{val: ident.NotILike(re), sql: `("a" !~* ?)`, isPrepared: true, args: []interface{}{"[ab]"}},
602 )
603
604 opts := sqlgen.DefaultDialectOptions()
605 opts.BooleanOperatorLookup = map[exp.BooleanOperation][]byte{}
606 esgs.assertCases(
607 sqlgen.NewExpressionSQLGenerator("test", opts),
608 expressionTestCase{val: ident.Eq(1), err: "goqu: boolean operator 'eq' not supported"},
609 expressionTestCase{val: ident.Neq(1), err: "goqu: boolean operator 'neq' not supported"},
610 expressionTestCase{val: ident.Is(true), err: "goqu: boolean operator 'is' not supported"},
611 expressionTestCase{val: ident.IsNot(true), err: "goqu: boolean operator 'isnot' not supported"},
612 expressionTestCase{val: ident.Gt(1), err: "goqu: boolean operator 'gt' not supported"},
613 expressionTestCase{val: ident.Gte(1), err: "goqu: boolean operator 'gte' not supported"},
614 expressionTestCase{val: ident.Lt(1), err: "goqu: boolean operator 'lt' not supported"},
615 expressionTestCase{val: ident.Lte(1), err: "goqu: boolean operator 'lte' not supported"},
616 expressionTestCase{val: ident.In([]int64{1, 2, 3}), err: "goqu: boolean operator 'in' not supported"},
617 expressionTestCase{val: ident.NotIn([]int64{1, 2, 3}), err: "goqu: boolean operator 'notin' not supported"},
618 expressionTestCase{val: ident.Like("a%"), err: "goqu: boolean operator 'like' not supported"},
619 expressionTestCase{val: ident.Like(re), err: "goqu: boolean operator 'regexplike' not supported"},
620 expressionTestCase{val: ident.ILike("a%"), err: "goqu: boolean operator 'ilike' not supported"},
621 expressionTestCase{val: ident.ILike(re), err: "goqu: boolean operator 'regexpilike' not supported"},
622 expressionTestCase{val: ident.NotLike("a%"), err: "goqu: boolean operator 'notlike' not supported"},
623 expressionTestCase{val: ident.NotLike(re), err: "goqu: boolean operator 'regexpnotlike' not supported"},
624 expressionTestCase{val: ident.NotILike("a%"), err: "goqu: boolean operator 'notilike' not supported"},
625 expressionTestCase{val: ident.NotILike(re), err: "goqu: boolean operator 'regexpnotilike' not supported"},
626 )
627 }
628
629 func (esgs *expressionSQLGeneratorSuite) TestGenerate_BitwiseExpression() {
630 ident := exp.NewIdentifierExpression("", "", "a")
631 esgs.assertCases(
632 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
633 expressionTestCase{val: ident.BitwiseInversion(), sql: `(~ "a")`},
634 expressionTestCase{val: ident.BitwiseInversion(), sql: `(~ "a")`, isPrepared: true},
635
636 expressionTestCase{val: ident.BitwiseAnd(1), sql: `("a" & 1)`},
637 expressionTestCase{val: ident.BitwiseAnd(1), sql: `("a" & ?)`, isPrepared: true, args: []interface{}{int64(1)}},
638
639 expressionTestCase{val: ident.BitwiseOr(1), sql: `("a" | 1)`},
640 expressionTestCase{val: ident.BitwiseOr(1), sql: `("a" | ?)`, isPrepared: true, args: []interface{}{int64(1)}},
641
642 expressionTestCase{val: ident.BitwiseXor(1), sql: `("a" # 1)`},
643 expressionTestCase{val: ident.BitwiseXor(1), sql: `("a" # ?)`, isPrepared: true, args: []interface{}{int64(1)}},
644
645 expressionTestCase{val: ident.BitwiseLeftShift(1), sql: `("a" << 1)`},
646 expressionTestCase{val: ident.BitwiseLeftShift(1), sql: `("a" << ?)`, isPrepared: true, args: []interface{}{int64(1)}},
647
648 expressionTestCase{val: ident.BitwiseRightShift(1), sql: `("a" >> 1)`},
649 expressionTestCase{val: ident.BitwiseRightShift(1), sql: `("a" >> ?)`, isPrepared: true, args: []interface{}{int64(1)}},
650 )
651
652 opts := sqlgen.DefaultDialectOptions()
653 opts.BitwiseOperatorLookup = map[exp.BitwiseOperation][]byte{}
654 esgs.assertCases(
655 sqlgen.NewExpressionSQLGenerator("test", opts),
656 expressionTestCase{val: ident.BitwiseInversion(), err: "goqu: bitwise operator 'Inversion' not supported"},
657 expressionTestCase{val: ident.BitwiseAnd(1), err: "goqu: bitwise operator 'AND' not supported"},
658 expressionTestCase{val: ident.BitwiseOr(1), err: "goqu: bitwise operator 'OR' not supported"},
659 expressionTestCase{val: ident.BitwiseXor(1), err: "goqu: bitwise operator 'XOR' not supported"},
660 expressionTestCase{val: ident.BitwiseLeftShift(1), err: "goqu: bitwise operator 'Left Shift' not supported"},
661 expressionTestCase{val: ident.BitwiseRightShift(1), err: "goqu: bitwise operator 'Right Shift' not supported"},
662 )
663 }
664 func (esgs *expressionSQLGeneratorSuite) TestGenerate_RangeExpression() {
665 betweenNum := exp.NewIdentifierExpression("", "", "a").
666 Between(exp.NewRangeVal(1, 2))
667 notBetweenNum := exp.NewIdentifierExpression("", "", "a").
668 NotBetween(exp.NewRangeVal(1, 2))
669
670 betweenStr := exp.NewIdentifierExpression("", "", "a").
671 Between(exp.NewRangeVal("aaa", "zzz"))
672 notBetweenStr := exp.NewIdentifierExpression("", "", "a").
673 NotBetween(exp.NewRangeVal("aaa", "zzz"))
674 esgs.assertCases(
675 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
676 expressionTestCase{val: betweenNum, sql: `("a" BETWEEN 1 AND 2)`},
677 expressionTestCase{val: betweenNum, sql: `("a" BETWEEN ? AND ?)`, isPrepared: true, args: []interface{}{
678 int64(1),
679 int64(2),
680 }},
681
682 expressionTestCase{val: notBetweenNum, sql: `("a" NOT BETWEEN 1 AND 2)`},
683 expressionTestCase{val: notBetweenNum, sql: `("a" NOT BETWEEN ? AND ?)`, isPrepared: true, args: []interface{}{
684 int64(1),
685 int64(2),
686 }},
687
688 expressionTestCase{val: betweenStr, sql: `("a" BETWEEN 'aaa' AND 'zzz')`},
689 expressionTestCase{val: betweenStr, sql: `("a" BETWEEN ? AND ?)`, isPrepared: true, args: []interface{}{
690 "aaa",
691 "zzz",
692 }},
693
694 expressionTestCase{val: notBetweenStr, sql: `("a" NOT BETWEEN 'aaa' AND 'zzz')`},
695 expressionTestCase{val: notBetweenStr, sql: `("a" NOT BETWEEN ? AND ?)`, isPrepared: true, args: []interface{}{
696 "aaa",
697 "zzz",
698 }},
699 )
700
701 opts := sqlgen.DefaultDialectOptions()
702 opts.RangeOperatorLookup = map[exp.RangeOperation][]byte{}
703 esgs.assertCases(
704 sqlgen.NewExpressionSQLGenerator("test", opts),
705 expressionTestCase{val: betweenNum, err: "goqu: range operator between not supported"},
706 expressionTestCase{val: betweenNum, err: "goqu: range operator between not supported"},
707
708 expressionTestCase{val: notBetweenNum, err: "goqu: range operator not between not supported"},
709 expressionTestCase{val: notBetweenNum, err: "goqu: range operator not between not supported"},
710
711 expressionTestCase{val: betweenStr, err: "goqu: range operator between not supported"},
712 expressionTestCase{val: betweenStr, err: "goqu: range operator between not supported"},
713
714 expressionTestCase{val: notBetweenStr, err: "goqu: range operator not between not supported"},
715 expressionTestCase{val: notBetweenStr, err: "goqu: range operator not between not supported"},
716 )
717 }
718
719 func (esgs *expressionSQLGeneratorSuite) TestGenerate_OrderedExpression() {
720 asc := exp.NewIdentifierExpression("", "", "a").Asc()
721 ascNf := exp.NewIdentifierExpression("", "", "a").Asc().NullsFirst()
722 ascNl := exp.NewIdentifierExpression("", "", "a").Asc().NullsLast()
723
724 desc := exp.NewIdentifierExpression("", "", "a").Desc()
725 descNf := exp.NewIdentifierExpression("", "", "a").Desc().NullsFirst()
726 descNl := exp.NewIdentifierExpression("", "", "a").Desc().NullsLast()
727
728 esgs.assertCases(
729 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
730 expressionTestCase{val: asc, sql: `"a" ASC`},
731 expressionTestCase{val: asc, sql: `"a" ASC`, isPrepared: true},
732
733 expressionTestCase{val: ascNf, sql: `"a" ASC NULLS FIRST`},
734 expressionTestCase{val: ascNf, sql: `"a" ASC NULLS FIRST`, isPrepared: true},
735
736 expressionTestCase{val: ascNl, sql: `"a" ASC NULLS LAST`},
737 expressionTestCase{val: ascNl, sql: `"a" ASC NULLS LAST`, isPrepared: true},
738
739 expressionTestCase{val: desc, sql: `"a" DESC`},
740 expressionTestCase{val: desc, sql: `"a" DESC`, isPrepared: true},
741
742 expressionTestCase{val: descNf, sql: `"a" DESC NULLS FIRST`},
743 expressionTestCase{val: descNf, sql: `"a" DESC NULLS FIRST`, isPrepared: true},
744
745 expressionTestCase{val: descNl, sql: `"a" DESC NULLS LAST`},
746 expressionTestCase{val: descNl, sql: `"a" DESC NULLS LAST`, isPrepared: true},
747 )
748 }
749
750 func (esgs *expressionSQLGeneratorSuite) TestGenerate_UpdateExpression() {
751 ue := exp.NewIdentifierExpression("", "", "a").Set(1)
752 esgs.assertCases(
753 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
754 expressionTestCase{val: ue, sql: `"a"=1`},
755 expressionTestCase{val: ue, sql: `"a"=?`, isPrepared: true, args: []interface{}{int64(1)}},
756 )
757 }
758
759 func (esgs *expressionSQLGeneratorSuite) TestGenerate_SQLFunctionExpression() {
760 min := exp.NewSQLFunctionExpression("MIN", exp.NewIdentifierExpression("", "", "a"))
761 coalesce := exp.NewSQLFunctionExpression("COALESCE", exp.NewIdentifierExpression("", "", "a"), "a")
762 esgs.assertCases(
763 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
764 expressionTestCase{val: min, sql: `MIN("a")`},
765 expressionTestCase{val: min, sql: `MIN("a")`, isPrepared: true},
766
767 expressionTestCase{val: coalesce, sql: `COALESCE("a", 'a')`},
768 expressionTestCase{val: coalesce, sql: `COALESCE("a", ?)`, isPrepared: true, args: []interface{}{"a"}},
769 )
770 }
771
772 func (esgs *expressionSQLGeneratorSuite) TestGenerate_SQLWindowFunctionExpression() {
773 sqlWinFunc := exp.NewSQLWindowFunctionExpression(
774 exp.NewSQLFunctionExpression("some_func"),
775 nil,
776 exp.NewWindowExpression(
777 nil,
778 exp.NewIdentifierExpression("", "", "win"),
779 nil,
780 nil,
781 ),
782 )
783 sqlWinFuncFromWindow := exp.NewSQLWindowFunctionExpression(
784 exp.NewSQLFunctionExpression("some_func"),
785 exp.NewIdentifierExpression("", "", "win"),
786 nil,
787 )
788
789 emptyWinFunc := exp.NewSQLWindowFunctionExpression(
790 exp.NewSQLFunctionExpression("some_func"),
791 nil,
792 nil,
793 )
794 badNamedSQLWinFuncInherit := exp.NewSQLWindowFunctionExpression(
795 exp.NewSQLFunctionExpression("some_func"),
796 nil,
797 exp.NewWindowExpression(
798 exp.NewIdentifierExpression("", "", "w"),
799 nil,
800 nil,
801 nil,
802 ),
803 )
804 esgs.assertCases(
805 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
806 expressionTestCase{val: sqlWinFunc, sql: `some_func() OVER ("win")`},
807 expressionTestCase{val: sqlWinFunc, sql: `some_func() OVER ("win")`, isPrepared: true},
808
809 expressionTestCase{val: sqlWinFuncFromWindow, sql: `some_func() OVER "win"`},
810 expressionTestCase{val: sqlWinFuncFromWindow, sql: `some_func() OVER "win"`, isPrepared: true},
811
812 expressionTestCase{val: emptyWinFunc, sql: `some_func() OVER ()`},
813 expressionTestCase{val: emptyWinFunc, sql: `some_func() OVER ()`, isPrepared: true},
814
815 expressionTestCase{val: badNamedSQLWinFuncInherit, err: sqlgen.ErrUnexpectedNamedWindow.Error()},
816 expressionTestCase{val: badNamedSQLWinFuncInherit, err: sqlgen.ErrUnexpectedNamedWindow.Error(), isPrepared: true},
817 )
818 opts := sqlgen.DefaultDialectOptions()
819 opts.SupportsWindowFunction = false
820 esgs.assertCases(
821 sqlgen.NewExpressionSQLGenerator("test", opts),
822 expressionTestCase{val: sqlWinFunc, err: sqlgen.ErrWindowNotSupported("test").Error()},
823 expressionTestCase{val: sqlWinFunc, err: sqlgen.ErrWindowNotSupported("test").Error(), isPrepared: true},
824 )
825 }
826
827 func (esgs *expressionSQLGeneratorSuite) TestGenerate_WindowExpression() {
828 opts := sqlgen.DefaultDialectOptions()
829 opts.WindowPartitionByFragment = []byte("partition by ")
830 opts.WindowOrderByFragment = []byte("order by ")
831
832 emptySQLWinFunc := exp.NewWindowExpression(nil, nil, nil, nil)
833 namedSQLWinFunc := exp.NewWindowExpression(
834 exp.NewIdentifierExpression("", "", "w"), nil, nil, nil,
835 )
836 inheritSQLWinFunc := exp.NewWindowExpression(
837 nil, exp.NewIdentifierExpression("", "", "w"), nil, nil,
838 )
839 partitionBySQLWinFunc := exp.NewWindowExpression(
840 nil, nil, exp.NewColumnListExpression("a", "b"), nil,
841 )
842 orderBySQLWinFunc := exp.NewWindowExpression(
843 nil, nil, nil, exp.NewOrderedColumnList(
844 exp.NewIdentifierExpression("", "", "a").Asc(),
845 exp.NewIdentifierExpression("", "", "b").Desc(),
846 ),
847 )
848
849 namedInheritPartitionOrderSQLWinFunc := exp.NewWindowExpression(
850 exp.NewIdentifierExpression("", "", "w1"),
851 exp.NewIdentifierExpression("", "", "w2"),
852 exp.NewColumnListExpression("a", "b"),
853 exp.NewOrderedColumnList(
854 exp.NewIdentifierExpression("", "", "a").Asc(),
855 exp.NewIdentifierExpression("", "", "b").Desc(),
856 ),
857 )
858
859 esgs.assertCases(
860 sqlgen.NewExpressionSQLGenerator("test", opts),
861 expressionTestCase{val: emptySQLWinFunc, sql: `()`},
862 expressionTestCase{val: emptySQLWinFunc, sql: `()`, isPrepared: true},
863
864 expressionTestCase{val: namedSQLWinFunc, sql: `"w" AS ()`},
865 expressionTestCase{val: namedSQLWinFunc, sql: `"w" AS ()`, isPrepared: true},
866
867 expressionTestCase{val: inheritSQLWinFunc, sql: `("w")`},
868 expressionTestCase{val: inheritSQLWinFunc, sql: `("w")`, isPrepared: true},
869
870 expressionTestCase{val: partitionBySQLWinFunc, sql: `(partition by "a", "b")`},
871 expressionTestCase{val: partitionBySQLWinFunc, sql: `(partition by "a", "b")`, isPrepared: true},
872
873 expressionTestCase{val: orderBySQLWinFunc, sql: `(order by "a" ASC, "b" DESC)`},
874 expressionTestCase{val: orderBySQLWinFunc, sql: `(order by "a" ASC, "b" DESC)`, isPrepared: true},
875
876 expressionTestCase{
877 val: namedInheritPartitionOrderSQLWinFunc,
878 sql: `"w1" AS ("w2" partition by "a", "b" order by "a" ASC, "b" DESC)`,
879 },
880 expressionTestCase{
881 val: namedInheritPartitionOrderSQLWinFunc,
882 sql: `"w1" AS ("w2" partition by "a", "b" order by "a" ASC, "b" DESC)`,
883 isPrepared: true,
884 },
885 )
886
887 opts = sqlgen.DefaultDialectOptions()
888 opts.SupportsWindowFunction = false
889 esgs.assertCases(
890 sqlgen.NewExpressionSQLGenerator("test", opts),
891 expressionTestCase{val: emptySQLWinFunc, err: sqlgen.ErrWindowNotSupported("test").Error()},
892 expressionTestCase{val: emptySQLWinFunc, err: sqlgen.ErrWindowNotSupported("test").Error(), isPrepared: true},
893 )
894 }
895
896 func (esgs *expressionSQLGeneratorSuite) TestGenerate_CastExpression() {
897 cast := exp.NewIdentifierExpression("", "", "a").Cast("DATE")
898 esgs.assertCases(
899 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
900 expressionTestCase{val: cast, sql: `CAST("a" AS DATE)`},
901 expressionTestCase{val: cast, sql: `CAST("a" AS DATE)`, isPrepared: true},
902 )
903 }
904
905
906 func (esgs *expressionSQLGeneratorSuite) TestGenerate_CommonTableExpressionSlice() {
907 ae := newTestAppendableExpression(`SELECT * FROM "b"`, emptyArgs, nil, nil)
908
909 cteNoArgs := []exp.CommonTableExpression{
910 exp.NewCommonTableExpression(false, "a", ae),
911 }
912 cteArgs := []exp.CommonTableExpression{
913 exp.NewCommonTableExpression(false, "a(x,y)", ae),
914 }
915
916 cteRecursiveNoArgs := []exp.CommonTableExpression{
917 exp.NewCommonTableExpression(true, "a", ae),
918 }
919 cteRecursiveArgs := []exp.CommonTableExpression{
920 exp.NewCommonTableExpression(true, "a(x,y)", ae),
921 }
922
923 allCtes := []exp.CommonTableExpression{
924 exp.NewCommonTableExpression(false, "a", ae),
925 exp.NewCommonTableExpression(false, "a(x,y)", ae),
926 }
927
928 allRecursiveCtes := []exp.CommonTableExpression{
929 exp.NewCommonTableExpression(true, "a", ae),
930 exp.NewCommonTableExpression(true, "a(x,y)", ae),
931 }
932
933 esgs.assertCases(
934 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
935 expressionTestCase{val: cteNoArgs, sql: `WITH a AS (SELECT * FROM "b") `},
936 expressionTestCase{val: cteNoArgs, sql: `WITH a AS (SELECT * FROM "b") `, isPrepared: true},
937
938 expressionTestCase{val: cteArgs, sql: `WITH a(x,y) AS (SELECT * FROM "b") `},
939 expressionTestCase{val: cteArgs, sql: `WITH a(x,y) AS (SELECT * FROM "b") `, isPrepared: true},
940
941 expressionTestCase{val: cteRecursiveNoArgs, sql: `WITH RECURSIVE a AS (SELECT * FROM "b") `},
942 expressionTestCase{val: cteRecursiveNoArgs, sql: `WITH RECURSIVE a AS (SELECT * FROM "b") `, isPrepared: true},
943
944 expressionTestCase{val: cteRecursiveArgs, sql: `WITH RECURSIVE a(x,y) AS (SELECT * FROM "b") `},
945 expressionTestCase{val: cteRecursiveArgs, sql: `WITH RECURSIVE a(x,y) AS (SELECT * FROM "b") `, isPrepared: true},
946
947 expressionTestCase{val: allCtes, sql: `WITH a AS (SELECT * FROM "b"), a(x,y) AS (SELECT * FROM "b") `},
948 expressionTestCase{val: allCtes, sql: `WITH a AS (SELECT * FROM "b"), a(x,y) AS (SELECT * FROM "b") `, isPrepared: true},
949
950 expressionTestCase{val: allRecursiveCtes, sql: `WITH RECURSIVE a AS (SELECT * FROM "b"), a(x,y) AS (SELECT * FROM "b") `},
951 expressionTestCase{
952 val: allRecursiveCtes,
953 sql: `WITH RECURSIVE a AS (SELECT * FROM "b"), a(x,y) AS (SELECT * FROM "b") `,
954 isPrepared: true,
955 },
956 )
957 opts := sqlgen.DefaultDialectOptions()
958 opts.SupportsWithCTE = false
959 esgs.assertCases(
960 sqlgen.NewExpressionSQLGenerator("test", opts),
961 expressionTestCase{val: cteNoArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]"},
962 expressionTestCase{val: cteNoArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]", isPrepared: true},
963
964 expressionTestCase{val: cteArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]"},
965 expressionTestCase{val: cteArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]", isPrepared: true},
966
967 expressionTestCase{val: cteRecursiveNoArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]"},
968 expressionTestCase{val: cteRecursiveNoArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]", isPrepared: true},
969
970 expressionTestCase{val: cteRecursiveArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]"},
971 expressionTestCase{val: cteRecursiveArgs, err: "goqu: dialect does not support CTE WITH clause [dialect=test]", isPrepared: true},
972 )
973 opts = sqlgen.DefaultDialectOptions()
974 opts.SupportsWithCTERecursive = false
975 esgs.assertCases(
976 sqlgen.NewExpressionSQLGenerator("test", opts),
977 expressionTestCase{val: cteNoArgs, sql: `WITH a AS (SELECT * FROM "b") `},
978 expressionTestCase{val: cteNoArgs, sql: `WITH a AS (SELECT * FROM "b") `, isPrepared: true},
979
980 expressionTestCase{val: cteArgs, sql: `WITH a(x,y) AS (SELECT * FROM "b") `},
981 expressionTestCase{val: cteArgs, sql: `WITH a(x,y) AS (SELECT * FROM "b") `, isPrepared: true},
982
983 expressionTestCase{
984 val: cteRecursiveNoArgs,
985 err: "goqu: dialect does not support CTE WITH RECURSIVE clause [dialect=test]",
986 },
987 expressionTestCase{
988 val: cteRecursiveNoArgs,
989 err: "goqu: dialect does not support CTE WITH RECURSIVE clause [dialect=test]",
990 isPrepared: true,
991 },
992
993 expressionTestCase{
994 val: cteRecursiveArgs,
995 err: "goqu: dialect does not support CTE WITH RECURSIVE clause [dialect=test]",
996 },
997 expressionTestCase{
998 val: cteRecursiveArgs,
999 err: "goqu: dialect does not support CTE WITH RECURSIVE clause [dialect=test]",
1000 isPrepared: true,
1001 },
1002 )
1003 }
1004
1005 func (esgs *expressionSQLGeneratorSuite) TestGenerate_CommonTableExpression() {
1006 ae := newTestAppendableExpression(`SELECT * FROM "b"`, emptyArgs, nil, nil)
1007
1008 cteNoArgs := exp.NewCommonTableExpression(false, "a", ae)
1009 cteArgs := exp.NewCommonTableExpression(false, "a(x,y)", ae)
1010
1011 cteRecursiveNoArgs := exp.NewCommonTableExpression(true, "a", ae)
1012 cteRecursiveArgs := exp.NewCommonTableExpression(true, "a(x,y)", ae)
1013
1014 esgs.assertCases(
1015 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1016 expressionTestCase{val: cteNoArgs, sql: `a AS (SELECT * FROM "b")`},
1017 expressionTestCase{val: cteNoArgs, sql: `a AS (SELECT * FROM "b")`, isPrepared: true},
1018
1019 expressionTestCase{val: cteArgs, sql: `a(x,y) AS (SELECT * FROM "b")`},
1020 expressionTestCase{val: cteArgs, sql: `a(x,y) AS (SELECT * FROM "b")`, isPrepared: true},
1021
1022 expressionTestCase{val: cteRecursiveNoArgs, sql: `a AS (SELECT * FROM "b")`},
1023 expressionTestCase{val: cteRecursiveNoArgs, sql: `a AS (SELECT * FROM "b")`, isPrepared: true},
1024
1025 expressionTestCase{val: cteRecursiveArgs, sql: `a(x,y) AS (SELECT * FROM "b")`},
1026 expressionTestCase{val: cteRecursiveArgs, sql: `a(x,y) AS (SELECT * FROM "b")`, isPrepared: true},
1027 )
1028 }
1029
1030 func (esgs *expressionSQLGeneratorSuite) TestGenerate_CompoundExpression() {
1031 ae := newTestAppendableExpression(`SELECT * FROM "b"`, emptyArgs, nil, nil)
1032
1033 u := exp.NewCompoundExpression(exp.UnionCompoundType, ae)
1034 ua := exp.NewCompoundExpression(exp.UnionAllCompoundType, ae)
1035
1036 i := exp.NewCompoundExpression(exp.IntersectCompoundType, ae)
1037 ia := exp.NewCompoundExpression(exp.IntersectAllCompoundType, ae)
1038
1039 esgs.assertCases(
1040 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1041 expressionTestCase{val: u, sql: ` UNION (SELECT * FROM "b")`},
1042 expressionTestCase{val: u, sql: ` UNION (SELECT * FROM "b")`, isPrepared: true},
1043
1044 expressionTestCase{val: ua, sql: ` UNION ALL (SELECT * FROM "b")`},
1045 expressionTestCase{val: ua, sql: ` UNION ALL (SELECT * FROM "b")`, isPrepared: true},
1046
1047 expressionTestCase{val: i, sql: ` INTERSECT (SELECT * FROM "b")`},
1048 expressionTestCase{val: i, sql: ` INTERSECT (SELECT * FROM "b")`, isPrepared: true},
1049
1050 expressionTestCase{val: ia, sql: ` INTERSECT ALL (SELECT * FROM "b")`},
1051 expressionTestCase{val: ia, sql: ` INTERSECT ALL (SELECT * FROM "b")`, isPrepared: true},
1052 )
1053
1054 opts := sqlgen.DefaultDialectOptions()
1055 opts.WrapCompoundsInParens = false
1056 esgs.assertCases(
1057 sqlgen.NewExpressionSQLGenerator("test", opts),
1058 expressionTestCase{val: u, sql: ` UNION SELECT * FROM "b"`},
1059 expressionTestCase{val: u, sql: ` UNION SELECT * FROM "b"`, isPrepared: true},
1060
1061 expressionTestCase{val: ua, sql: ` UNION ALL SELECT * FROM "b"`},
1062 expressionTestCase{val: ua, sql: ` UNION ALL SELECT * FROM "b"`, isPrepared: true},
1063
1064 expressionTestCase{val: i, sql: ` INTERSECT SELECT * FROM "b"`},
1065 expressionTestCase{val: i, sql: ` INTERSECT SELECT * FROM "b"`, isPrepared: true},
1066
1067 expressionTestCase{val: ia, sql: ` INTERSECT ALL SELECT * FROM "b"`},
1068 expressionTestCase{val: ia, sql: ` INTERSECT ALL SELECT * FROM "b"`, isPrepared: true},
1069 )
1070 }
1071
1072 func (esgs *expressionSQLGeneratorSuite) TestGenerate_IdentifierExpression() {
1073 col := exp.NewIdentifierExpression("", "", "col")
1074 colStar := exp.NewIdentifierExpression("", "", "*")
1075 table := exp.NewIdentifierExpression("", "table", "")
1076 schema := exp.NewIdentifierExpression("schema", "", "")
1077 tableCol := exp.NewIdentifierExpression("", "table", "col")
1078 schemaTableCol := exp.NewIdentifierExpression("schema", "table", "col")
1079
1080 parsedCol := exp.ParseIdentifier("col")
1081 parsedTableCol := exp.ParseIdentifier("table.col")
1082 parsedSchemaTableCol := exp.ParseIdentifier("schema.table.col")
1083
1084 esgs.assertCases(
1085 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1086 expressionTestCase{
1087 val: exp.NewIdentifierExpression("", "", ""),
1088 err: `goqu: a empty identifier was encountered, please specify a "schema", "table" or "column"`,
1089 },
1090 expressionTestCase{
1091 val: exp.NewIdentifierExpression("", "", nil),
1092 err: `goqu: a empty identifier was encountered, please specify a "schema", "table" or "column"`,
1093 },
1094 expressionTestCase{
1095 val: exp.NewIdentifierExpression("", "", false),
1096 err: `goqu: unexpected col type must be string or LiteralExpression received bool`,
1097 },
1098
1099 expressionTestCase{val: col, sql: `"col"`},
1100 expressionTestCase{val: col, sql: `"col"`, isPrepared: true},
1101
1102 expressionTestCase{val: col.Table("table"), sql: `"table"."col"`},
1103 expressionTestCase{val: col.Table("table"), sql: `"table"."col"`, isPrepared: true},
1104
1105 expressionTestCase{val: col.Table("table").Schema("schema"), sql: `"schema"."table"."col"`},
1106 expressionTestCase{val: col.Table("table").Schema("schema"), sql: `"schema"."table"."col"`, isPrepared: true},
1107
1108 expressionTestCase{val: colStar, sql: `*`},
1109 expressionTestCase{val: colStar, sql: `*`, isPrepared: true},
1110
1111 expressionTestCase{val: colStar.Table("table"), sql: `"table".*`},
1112 expressionTestCase{val: colStar.Table("table"), sql: `"table".*`, isPrepared: true},
1113
1114 expressionTestCase{val: colStar.Table("table").Schema("schema"), sql: `"schema"."table".*`},
1115 expressionTestCase{val: colStar.Table("table").Schema("schema"), sql: `"schema"."table".*`, isPrepared: true},
1116
1117 expressionTestCase{val: table, sql: `"table"`},
1118 expressionTestCase{val: table, sql: `"table"`, isPrepared: true},
1119
1120 expressionTestCase{val: table.Col("col"), sql: `"table"."col"`},
1121 expressionTestCase{val: table.Col("col"), sql: `"table"."col"`, isPrepared: true},
1122
1123 expressionTestCase{val: table.Col(nil), sql: `"table"`},
1124 expressionTestCase{val: table.Col(nil), sql: `"table"`, isPrepared: true},
1125
1126 expressionTestCase{val: table.Col("*"), sql: `"table".*`},
1127 expressionTestCase{val: table.Col("*"), sql: `"table".*`, isPrepared: true},
1128
1129 expressionTestCase{val: table.Schema("schema").Col("col"), sql: `"schema"."table"."col"`},
1130 expressionTestCase{val: table.Schema("schema").Col("col"), sql: `"schema"."table"."col"`, isPrepared: true},
1131
1132 expressionTestCase{val: schema, sql: `"schema"`},
1133 expressionTestCase{val: schema, sql: `"schema"`, isPrepared: true},
1134
1135 expressionTestCase{val: schema.Table("table"), sql: `"schema"."table"`},
1136 expressionTestCase{val: schema.Table("table"), sql: `"schema"."table"`, isPrepared: true},
1137
1138 expressionTestCase{val: schema.Table("table").Col("col"), sql: `"schema"."table"."col"`},
1139 expressionTestCase{val: schema.Table("table").Col("col"), sql: `"schema"."table"."col"`, isPrepared: true},
1140
1141 expressionTestCase{val: schema.Table("table").Col(nil), sql: `"schema"."table"`},
1142 expressionTestCase{val: schema.Table("table").Col(nil), sql: `"schema"."table"`, isPrepared: true},
1143
1144 expressionTestCase{val: schema.Table("table").Col("*"), sql: `"schema"."table".*`},
1145 expressionTestCase{val: schema.Table("table").Col("*"), sql: `"schema"."table".*`, isPrepared: true},
1146
1147 expressionTestCase{val: tableCol, sql: `"table"."col"`},
1148 expressionTestCase{val: tableCol, sql: `"table"."col"`, isPrepared: true},
1149
1150 expressionTestCase{val: schemaTableCol, sql: `"schema"."table"."col"`},
1151 expressionTestCase{val: schemaTableCol, sql: `"schema"."table"."col"`, isPrepared: true},
1152
1153 expressionTestCase{val: parsedCol, sql: `"col"`},
1154 expressionTestCase{val: parsedCol, sql: `"col"`, isPrepared: true},
1155
1156 expressionTestCase{val: parsedTableCol, sql: `"table"."col"`},
1157 expressionTestCase{val: parsedTableCol, sql: `"table"."col"`, isPrepared: true},
1158
1159 expressionTestCase{val: parsedSchemaTableCol, sql: `"schema"."table"."col"`},
1160 expressionTestCase{val: parsedSchemaTableCol, sql: `"schema"."table"."col"`, isPrepared: true},
1161 )
1162 }
1163
1164 func (esgs *expressionSQLGeneratorSuite) TestGenerate_LateralExpression() {
1165 lateralExp := exp.NewLateralExpression(newTestAppendableExpression(`SELECT * FROM "test"`, emptyArgs, nil, nil))
1166
1167 do := sqlgen.DefaultDialectOptions()
1168 esgs.assertCases(
1169 sqlgen.NewExpressionSQLGenerator("test", do),
1170 expressionTestCase{val: lateralExp, sql: `LATERAL (SELECT * FROM "test")`},
1171 expressionTestCase{val: lateralExp, sql: `LATERAL (SELECT * FROM "test")`, isPrepared: true},
1172 )
1173
1174 do = sqlgen.DefaultDialectOptions()
1175 do.LateralFragment = []byte("lateral ")
1176 esgs.assertCases(
1177 sqlgen.NewExpressionSQLGenerator("test", do),
1178 expressionTestCase{val: lateralExp, sql: `lateral (SELECT * FROM "test")`},
1179 expressionTestCase{val: lateralExp, sql: `lateral (SELECT * FROM "test")`, isPrepared: true},
1180 )
1181 do = sqlgen.DefaultDialectOptions()
1182 do.SupportsLateral = false
1183 esgs.assertCases(
1184 sqlgen.NewExpressionSQLGenerator("test", do),
1185 expressionTestCase{val: lateralExp, err: "goqu: dialect does not support lateral expressions [dialect=test]"},
1186 expressionTestCase{val: lateralExp, err: "goqu: dialect does not support lateral expressions [dialect=test]", isPrepared: true},
1187 )
1188 }
1189
1190 func (esgs *expressionSQLGeneratorSuite) TestGenerate_CaseExpression() {
1191 ident := exp.NewIdentifierExpression("", "", "col")
1192 valueCase := exp.NewCaseExpression().
1193 Value(ident).
1194 When(true, "one").
1195 When(false, "two")
1196 valueElseCase := exp.NewCaseExpression().
1197 Value(ident).
1198 When(1, "one").
1199 When(2, "two").
1200 Else("three")
1201 searchCase := exp.NewCaseExpression().
1202 When(ident.Gt(1), exp.NewLiteralExpression("? - 1", ident)).
1203 When(ident.Lt(0), exp.NewLiteralExpression("? + 1", ident))
1204 searchElseCase := exp.NewCaseExpression().
1205 When(ident.Gt(1), exp.NewLiteralExpression("? - 1", ident)).
1206 When(ident.Lt(0), exp.NewLiteralExpression("? + 1", ident)).
1207 Else(ident)
1208
1209 esgs.assertCases(
1210 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1211 expressionTestCase{val: valueCase, sql: `CASE "col" WHEN TRUE THEN 'one' WHEN FALSE THEN 'two' END`},
1212 expressionTestCase{
1213 val: valueCase,
1214 sql: `CASE "col" WHEN ? THEN ? WHEN ? THEN ? END`,
1215 isPrepared: true,
1216 args: []interface{}{true, "one", false, "two"},
1217 },
1218
1219 expressionTestCase{val: valueElseCase, sql: `CASE "col" WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'three' END`},
1220 expressionTestCase{
1221 val: valueElseCase,
1222 sql: `CASE "col" WHEN ? THEN ? WHEN ? THEN ? ELSE ? END`,
1223 isPrepared: true,
1224 args: []interface{}{int64(1), "one", int64(2), "two", "three"},
1225 },
1226
1227 expressionTestCase{val: searchCase, sql: `CASE WHEN ("col" > 1) THEN "col" - 1 WHEN ("col" < 0) THEN "col" + 1 END`},
1228 expressionTestCase{
1229 val: searchCase,
1230 sql: `CASE WHEN ("col" > ?) THEN "col" - 1 WHEN ("col" < ?) THEN "col" + 1 END`,
1231 isPrepared: true,
1232 args: []interface{}{int64(1), int64(0)},
1233 },
1234
1235 expressionTestCase{val: searchElseCase, sql: `CASE WHEN ("col" > 1) THEN "col" - 1 WHEN ("col" < 0) THEN "col" + 1 ELSE "col" END`},
1236 expressionTestCase{
1237 val: searchElseCase,
1238 sql: `CASE WHEN ("col" > ?) THEN "col" - 1 WHEN ("col" < ?) THEN "col" + 1 ELSE "col" END`,
1239 isPrepared: true,
1240 args: []interface{}{int64(1), int64(0)},
1241 },
1242 expressionTestCase{
1243 val: exp.NewCaseExpression(),
1244 err: "goqu: when conditions not found for case statement",
1245 },
1246 )
1247
1248 opts := sqlgen.DefaultDialectOptions()
1249 opts.CaseFragment = []byte("case ")
1250 opts.WhenFragment = []byte(" when ")
1251 opts.ThenFragment = []byte(" then ")
1252 opts.ElseFragment = []byte(" else ")
1253 opts.EndFragment = []byte(" end")
1254 esgs.assertCases(
1255 sqlgen.NewExpressionSQLGenerator("test", opts),
1256 expressionTestCase{val: valueCase, sql: `case "col" when TRUE then 'one' when FALSE then 'two' end`},
1257 expressionTestCase{
1258 val: valueCase,
1259 sql: `case "col" when ? then ? when ? then ? end`,
1260 isPrepared: true,
1261 args: []interface{}{true, "one", false, "two"},
1262 },
1263
1264 expressionTestCase{val: valueElseCase, sql: `case "col" when 1 then 'one' when 2 then 'two' else 'three' end`},
1265 expressionTestCase{
1266 val: valueElseCase,
1267 sql: `case "col" when ? then ? when ? then ? else ? end`,
1268 isPrepared: true,
1269 args: []interface{}{int64(1), "one", int64(2), "two", "three"},
1270 },
1271
1272 expressionTestCase{val: searchCase, sql: `case when ("col" > 1) then "col" - 1 when ("col" < 0) then "col" + 1 end`},
1273 expressionTestCase{
1274 val: searchCase,
1275 sql: `case when ("col" > ?) then "col" - 1 when ("col" < ?) then "col" + 1 end`,
1276 isPrepared: true,
1277 args: []interface{}{int64(1), int64(0)},
1278 },
1279
1280 expressionTestCase{val: searchElseCase, sql: `case when ("col" > 1) then "col" - 1 when ("col" < 0) then "col" + 1 else "col" end`},
1281 expressionTestCase{
1282 val: searchElseCase,
1283 sql: `case when ("col" > ?) then "col" - 1 when ("col" < ?) then "col" + 1 else "col" end`,
1284 isPrepared: true,
1285 args: []interface{}{int64(1), int64(0)},
1286 },
1287 expressionTestCase{
1288 val: exp.NewCaseExpression(),
1289 err: "goqu: when conditions not found for case statement",
1290 },
1291 )
1292 }
1293
1294 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMap() {
1295 esgs.assertCases(
1296 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1297 expressionTestCase{val: exp.Ex{}},
1298 expressionTestCase{val: exp.Ex{}, isPrepared: true},
1299
1300 expressionTestCase{val: exp.Ex{"a": 1}, sql: `("a" = 1)`},
1301 expressionTestCase{val: exp.Ex{"a": 1}, sql: `("a" = ?)`, isPrepared: true, args: []interface{}{int64(1)}},
1302
1303 expressionTestCase{val: exp.Ex{"a": true}, sql: `("a" IS TRUE)`},
1304 expressionTestCase{val: exp.Ex{"a": true}, sql: `("a" IS TRUE)`, isPrepared: true},
1305
1306 expressionTestCase{val: exp.Ex{"a": false}, sql: `("a" IS FALSE)`},
1307 expressionTestCase{val: exp.Ex{"a": false}, sql: `("a" IS FALSE)`, isPrepared: true},
1308
1309 expressionTestCase{val: exp.Ex{"a": nil}, sql: `("a" IS NULL)`},
1310 expressionTestCase{val: exp.Ex{"a": nil}, sql: `("a" IS NULL)`, isPrepared: true},
1311
1312 expressionTestCase{val: exp.Ex{"a": []string{"a", "b", "c"}}, sql: `("a" IN ('a', 'b', 'c'))`},
1313 expressionTestCase{
1314 val: exp.Ex{"a": []string{"a", "b", "c"}},
1315 sql: `("a" IN (?, ?, ?))`,
1316 isPrepared: true,
1317 args: []interface{}{"a", "b", "c"},
1318 },
1319 )
1320 }
1321
1322 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithABadOp() {
1323 esgs.assertCases(
1324 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1325 expressionTestCase{
1326 val: exp.Ex{"a": exp.Op{"badOp": true}},
1327 err: "goqu: unsupported expression type badOp",
1328 },
1329 expressionTestCase{
1330 val: exp.Ex{"a": exp.Op{"badOp": true}},
1331 isPrepared: true,
1332 err: "goqu: unsupported expression type badOp",
1333 },
1334 )
1335 }
1336
1337 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithNeqOp() {
1338 esgs.assertCases(
1339 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1340 expressionTestCase{val: exp.Ex{"a": exp.Op{"neq": 1}}, sql: `("a" != 1)`},
1341 expressionTestCase{val: exp.Ex{"a": exp.Op{"neq": 1}}, sql: `("a" != ?)`, isPrepared: true, args: []interface{}{
1342 int64(1),
1343 }},
1344 )
1345 }
1346
1347 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithIsNotOp() {
1348 esgs.assertCases(
1349 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1350 expressionTestCase{val: exp.Ex{"a": exp.Op{"isnot": true}}, sql: `("a" IS NOT TRUE)`},
1351 expressionTestCase{val: exp.Ex{"a": exp.Op{"isnot": true}}, sql: `("a" IS NOT TRUE)`, isPrepared: true},
1352 )
1353 }
1354
1355 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithGtOp() {
1356 esgs.assertCases(
1357 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1358 expressionTestCase{val: exp.Ex{"a": exp.Op{"gt": 1}}, sql: `("a" > 1)`},
1359 expressionTestCase{val: exp.Ex{"a": exp.Op{"gt": 1}}, sql: `("a" > ?)`, isPrepared: true, args: []interface{}{
1360 int64(1),
1361 }},
1362 )
1363 }
1364
1365 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithGteOp() {
1366 esgs.assertCases(
1367 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1368 expressionTestCase{val: exp.Ex{"a": exp.Op{"gte": 1}}, sql: `("a" >= 1)`},
1369 expressionTestCase{val: exp.Ex{"a": exp.Op{"gte": 1}}, sql: `("a" >= ?)`, isPrepared: true, args: []interface{}{
1370 int64(1),
1371 }},
1372 )
1373 }
1374
1375 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithLtOp() {
1376 esgs.assertCases(
1377 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1378 expressionTestCase{val: exp.Ex{"a": exp.Op{"lt": 1}}, sql: `("a" < 1)`},
1379 expressionTestCase{val: exp.Ex{"a": exp.Op{"lt": 1}}, sql: `("a" < ?)`, isPrepared: true, args: []interface{}{
1380 int64(1),
1381 }},
1382 )
1383 }
1384
1385 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithLteOp() {
1386 esgs.assertCases(
1387 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1388 expressionTestCase{val: exp.Ex{"a": exp.Op{"lte": 1}}, sql: `("a" <= 1)`},
1389 expressionTestCase{val: exp.Ex{"a": exp.Op{"lte": 1}}, sql: `("a" <= ?)`, isPrepared: true, args: []interface{}{
1390 int64(1),
1391 }},
1392 )
1393 }
1394
1395 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithLikeOp() {
1396 re := regexp.MustCompile("[ab]")
1397 esgs.assertCases(
1398 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1399 expressionTestCase{val: exp.Ex{"a": exp.Op{"like": "a%"}}, sql: `("a" LIKE 'a%')`},
1400 expressionTestCase{
1401 val: exp.Ex{"a": exp.Op{"like": "a%"}},
1402 sql: `("a" LIKE ?)`,
1403 isPrepared: true,
1404 args: []interface{}{"a%"},
1405 },
1406
1407 expressionTestCase{val: exp.Ex{"a": exp.Op{"like": re}}, sql: `("a" ~ '[ab]')`},
1408 expressionTestCase{
1409 val: exp.Ex{"a": exp.Op{"like": re}},
1410 sql: `("a" ~ ?)`,
1411 isPrepared: true,
1412 args: []interface{}{"[ab]"},
1413 },
1414 )
1415 }
1416
1417 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithNotLikeOp() {
1418 re := regexp.MustCompile("[ab]")
1419 esgs.assertCases(
1420 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1421 expressionTestCase{val: exp.Ex{"a": exp.Op{"notLike": "a%"}}, sql: `("a" NOT LIKE 'a%')`},
1422 expressionTestCase{
1423 val: exp.Ex{"a": exp.Op{"notLike": "a%"}},
1424 sql: `("a" NOT LIKE ?)`,
1425 isPrepared: true,
1426 args: []interface{}{"a%"},
1427 },
1428
1429 expressionTestCase{val: exp.Ex{"a": exp.Op{"notLike": re}}, sql: `("a" !~ '[ab]')`},
1430 expressionTestCase{
1431 val: exp.Ex{"a": exp.Op{"notLike": re}},
1432 sql: `("a" !~ ?)`,
1433 isPrepared: true,
1434 args: []interface{}{"[ab]"},
1435 },
1436 )
1437 }
1438
1439 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithILikeOp() {
1440 re := regexp.MustCompile("[ab]")
1441 esgs.assertCases(
1442 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1443 expressionTestCase{val: exp.Ex{"a": exp.Op{"iLike": "a%"}}, sql: `("a" ILIKE 'a%')`},
1444 expressionTestCase{
1445 val: exp.Ex{"a": exp.Op{"iLike": "a%"}},
1446 sql: `("a" ILIKE ?)`,
1447 isPrepared: true,
1448 args: []interface{}{"a%"},
1449 },
1450
1451 expressionTestCase{val: exp.Ex{"a": exp.Op{"iLike": re}}, sql: `("a" ~* '[ab]')`},
1452 expressionTestCase{
1453 val: exp.Ex{"a": exp.Op{"iLike": re}},
1454 sql: `("a" ~* ?)`,
1455 isPrepared: true,
1456 args: []interface{}{"[ab]"},
1457 },
1458 )
1459 }
1460
1461 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithNotILikeOp() {
1462 re := regexp.MustCompile("[ab]")
1463 esgs.assertCases(
1464 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1465 expressionTestCase{val: exp.Ex{"a": exp.Op{"notILike": "a%"}}, sql: `("a" NOT ILIKE 'a%')`},
1466 expressionTestCase{
1467 val: exp.Ex{"a": exp.Op{"notILike": "a%"}},
1468 sql: `("a" NOT ILIKE ?)`,
1469 isPrepared: true,
1470 args: []interface{}{"a%"},
1471 },
1472
1473 expressionTestCase{val: exp.Ex{"a": exp.Op{"notILike": re}}, sql: `("a" !~* '[ab]')`},
1474 expressionTestCase{
1475 val: exp.Ex{"a": exp.Op{"notILike": re}},
1476 sql: `("a" !~* ?)`,
1477 isPrepared: true,
1478 args: []interface{}{"[ab]"},
1479 },
1480 )
1481 }
1482
1483 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithRegExpLikeOp() {
1484 esgs.assertCases(
1485 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1486
1487 expressionTestCase{val: exp.Ex{"a": exp.Op{"regexpLike": "[ab]"}}, sql: `("a" ~ '[ab]')`},
1488 expressionTestCase{
1489 val: exp.Ex{"a": exp.Op{"regexpLike": "[ab]"}},
1490 sql: `("a" ~ ?)`,
1491 isPrepared: true,
1492 args: []interface{}{"[ab]"},
1493 },
1494 )
1495 }
1496
1497 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithRegExpILikeOp() {
1498 esgs.assertCases(
1499 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1500 expressionTestCase{val: exp.Ex{"a": exp.Op{"regexpILike": "[ab]"}}, sql: `("a" ~* '[ab]')`},
1501 expressionTestCase{
1502 val: exp.Ex{"a": exp.Op{"regexpILike": "[ab]"}},
1503 sql: `("a" ~* ?)`,
1504 isPrepared: true,
1505 args: []interface{}{"[ab]"},
1506 },
1507 )
1508 }
1509
1510 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithRegExpNotLikeOp() {
1511 esgs.assertCases(
1512 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1513 expressionTestCase{val: exp.Ex{"a": exp.Op{"regexpNotLike": "[ab]"}}, sql: `("a" !~ '[ab]')`},
1514 expressionTestCase{
1515 val: exp.Ex{"a": exp.Op{"regexpNotLike": "[ab]"}},
1516 sql: `("a" !~ ?)`,
1517 isPrepared: true,
1518 args: []interface{}{"[ab]"},
1519 },
1520 )
1521 }
1522
1523 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithInOp() {
1524 esgs.assertCases(
1525 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1526 expressionTestCase{val: exp.Ex{"a": exp.Op{"in": []string{"a", "b", "c"}}}, sql: `("a" IN ('a', 'b', 'c'))`},
1527 expressionTestCase{
1528 val: exp.Ex{"a": exp.Op{"in": []string{"a", "b", "c"}}},
1529 sql: `("a" IN (?, ?, ?))`,
1530 isPrepared: true,
1531 args: []interface{}{"a", "b", "c"},
1532 },
1533 )
1534 }
1535
1536 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapWithNotInOp() {
1537 esgs.assertCases(
1538 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1539 expressionTestCase{
1540 val: exp.Ex{"a": exp.Op{"notIn": []string{"a", "b", "c"}}},
1541 sql: `("a" NOT IN ('a', 'b', 'c'))`,
1542 },
1543 expressionTestCase{
1544 val: exp.Ex{"a": exp.Op{"notIn": []string{"a", "b", "c"}}},
1545 sql: `("a" NOT IN (?, ?, ?))`,
1546 isPrepared: true,
1547 args: []interface{}{"a", "b", "c"},
1548 },
1549 )
1550 }
1551
1552 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapBetweenOp() {
1553 esgs.assertCases(
1554 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1555 expressionTestCase{
1556 val: exp.Ex{"a": exp.Op{"between": exp.NewRangeVal("aaa", "zzz")}},
1557 sql: `("a" BETWEEN 'aaa' AND 'zzz')`,
1558 },
1559 expressionTestCase{
1560 val: exp.Ex{"a": exp.Op{"between": exp.NewRangeVal("aaa", "zzz")}},
1561 sql: `("a" BETWEEN ? AND ?)`,
1562 isPrepared: true,
1563 args: []interface{}{"aaa", "zzz"},
1564 },
1565 )
1566 }
1567
1568 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapNotBetweenOp() {
1569 esgs.assertCases(
1570 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1571 expressionTestCase{
1572 val: exp.Ex{"a": exp.Op{"notBetween": exp.NewRangeVal("aaa", "zzz")}},
1573 sql: `("a" NOT BETWEEN 'aaa' AND 'zzz')`,
1574 },
1575 expressionTestCase{
1576 val: exp.Ex{"a": exp.Op{"notBetween": exp.NewRangeVal("aaa", "zzz")}},
1577 sql: `("a" NOT BETWEEN ? AND ?)`,
1578 isPrepared: true,
1579 args: []interface{}{"aaa", "zzz"},
1580 },
1581 )
1582 }
1583
1584 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionMapIsOp() {
1585 esgs.assertCases(
1586 sqlgen.NewExpressionSQLGenerator("test", sqlgen.DefaultDialectOptions()),
1587 expressionTestCase{
1588 val: exp.Ex{"a": exp.Op{"is": nil, "eq": 10}},
1589 sql: `(("a" = 10) OR ("a" IS NULL))`,
1590 },
1591 expressionTestCase{
1592 val: exp.Ex{"a": exp.Op{"is": nil, "eq": 10}},
1593 sql: `(("a" = ?) OR ("a" IS NULL))`,
1594 isPrepared: true,
1595 args: []interface{}{int64(10)},
1596 },
1597 )
1598 }
1599
1600 func (esgs *expressionSQLGeneratorSuite) TestGenerate_ExpressionOrMap() {
1601 esgs.assertCases(
1602 sqlgen.NewExpressionSQLGenerator("default", sqlgen.DefaultDialectOptions()),
1603 expressionTestCase{val: exp.ExOr{}},
1604 expressionTestCase{val: exp.ExOr{}, isPrepared: true},
1605
1606 expressionTestCase{val: exp.ExOr{"a": exp.Op{"regexpLike": "[ab]"}}, sql: `("a" ~ '[ab]')`},
1607 expressionTestCase{
1608 val: exp.ExOr{"a": exp.Op{"regexpLike": "[ab]"}},
1609 sql: `("a" ~ ?)`,
1610 isPrepared: true,
1611 args: []interface{}{"[ab]"},
1612 },
1613
1614 expressionTestCase{val: exp.ExOr{"a": exp.Op{"regexpNotLike": "[ab]"}}, sql: `("a" !~ '[ab]')`},
1615 expressionTestCase{
1616 val: exp.ExOr{"a": exp.Op{"regexpNotLike": "[ab]"}},
1617 sql: `("a" !~ ?)`,
1618 isPrepared: true,
1619 args: []interface{}{"[ab]"},
1620 },
1621
1622 expressionTestCase{val: exp.ExOr{"a": exp.Op{"regexpILike": "[ab]"}}, sql: `("a" ~* '[ab]')`},
1623 expressionTestCase{
1624 val: exp.ExOr{"a": exp.Op{"regexpILike": "[ab]"}},
1625 sql: `("a" ~* ?)`,
1626 isPrepared: true,
1627 args: []interface{}{"[ab]"},
1628 },
1629 expressionTestCase{val: exp.ExOr{"a": exp.Op{"regexpNotILike": "[ab]"}}, sql: `("a" !~* '[ab]')`},
1630 expressionTestCase{
1631 val: exp.ExOr{"a": exp.Op{"regexpNotILike": "[ab]"}},
1632 sql: `("a" !~* ?)`,
1633 isPrepared: true,
1634 args: []interface{}{"[ab]"},
1635 },
1636
1637 expressionTestCase{
1638 val: exp.ExOr{"a": exp.Op{"badOp": true}},
1639 err: "goqu: unsupported expression type badOp",
1640 },
1641 expressionTestCase{
1642 val: exp.ExOr{"a": exp.Op{"badOp": true}},
1643 isPrepared: true,
1644 err: "goqu: unsupported expression type badOp",
1645 },
1646
1647 expressionTestCase{val: exp.ExOr{"a": 1, "b": true}, sql: `(("a" = 1) OR ("b" IS TRUE))`},
1648 expressionTestCase{
1649 val: exp.ExOr{"a": 1, "b": true},
1650 sql: `(("a" = ?) OR ("b" IS TRUE))`,
1651 isPrepared: true,
1652 args: []interface{}{int64(1)},
1653 },
1654
1655 expressionTestCase{
1656 val: exp.ExOr{"a": 1, "b": []string{"a", "b", "c"}},
1657 sql: `(("a" = 1) OR ("b" IN ('a', 'b', 'c')))`,
1658 },
1659 expressionTestCase{
1660 val: exp.ExOr{"a": 1, "b": []string{"a", "b", "c"}},
1661 sql: `(("a" = ?) OR ("b" IN (?, ?, ?)))`,
1662 isPrepared: true,
1663 args: []interface{}{int64(1), "a", "b", "c"},
1664 },
1665 )
1666 }
1667
1668 func TestExpressionSQLGenerator(t *testing.T) {
1669 suite.Run(t, new(expressionSQLGeneratorSuite))
1670 }
1671
View as plain text