1 package sqlgen_test
2
3 import (
4 "testing"
5
6 "github.com/doug-martin/goqu/v9/exp"
7 "github.com/doug-martin/goqu/v9/internal/sb"
8 "github.com/doug-martin/goqu/v9/sqlgen"
9 "github.com/stretchr/testify/suite"
10 )
11
12 type (
13 insertTestCase struct {
14 clause exp.InsertClauses
15 sql string
16 isPrepared bool
17 args []interface{}
18 err string
19 }
20 insertSQLGeneratorSuite struct {
21 baseSQLGeneratorSuite
22 }
23 )
24
25 func (igs *insertSQLGeneratorSuite) assertCases(isg sqlgen.InsertSQLGenerator, testCases ...insertTestCase) {
26 for _, tc := range testCases {
27 b := sb.NewSQLBuilder(tc.isPrepared)
28 isg.Generate(b, tc.clause)
29 switch {
30 case len(tc.err) > 0:
31 igs.assertErrorSQL(b, tc.err)
32 case tc.isPrepared:
33 igs.assertPreparedSQL(b, tc.sql, tc.args)
34 default:
35 igs.assertNotPreparedSQL(b, tc.sql)
36 }
37 }
38 }
39
40 func (igs *insertSQLGeneratorSuite) TestDialect() {
41 opts := sqlgen.DefaultDialectOptions()
42 d := sqlgen.NewInsertSQLGenerator("test", opts)
43 igs.Equal("test", d.Dialect())
44
45 opts2 := sqlgen.DefaultDialectOptions()
46 d2 := sqlgen.NewInsertSQLGenerator("test2", opts2)
47 igs.Equal("test2", d2.Dialect())
48 }
49
50 func (igs *insertSQLGeneratorSuite) TestGenerate_UnsupportedFragment() {
51 opts := sqlgen.DefaultDialectOptions()
52 opts.InsertSQLOrder = []sqlgen.SQLFragmentType{sqlgen.UpdateBeginSQLFragment}
53 d := sqlgen.NewInsertSQLGenerator("test", opts)
54
55 b := sb.NewSQLBuilder(true)
56 ic := exp.NewInsertClauses().
57 SetInto(exp.NewIdentifierExpression("", "test", ""))
58 d.Generate(b, ic)
59 igs.assertErrorSQL(b, `goqu: unsupported INSERT SQL fragment UpdateBeginSQLFragment`)
60 }
61
62 func (igs *insertSQLGeneratorSuite) TestGenerate_empty() {
63 ic := exp.NewInsertClauses().
64 SetInto(exp.NewIdentifierExpression("", "test", ""))
65
66 igs.assertCases(
67 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
68 insertTestCase{clause: ic, sql: `INSERT INTO "test" DEFAULT VALUES`},
69 insertTestCase{clause: ic, sql: `INSERT INTO "test" DEFAULT VALUES`, isPrepared: true},
70 )
71
72 opts2 := sqlgen.DefaultDialectOptions()
73 opts2.DefaultValuesFragment = []byte(" default values")
74
75 igs.assertCases(
76 sqlgen.NewInsertSQLGenerator("test", opts2),
77 insertTestCase{clause: ic, sql: `INSERT INTO "test" default values`},
78 insertTestCase{clause: ic, sql: `INSERT INTO "test" default values`, isPrepared: true},
79 )
80 }
81
82 func (igs *insertSQLGeneratorSuite) TestGenerate_nilValues() {
83 ic := exp.NewInsertClauses().
84 SetInto(exp.NewIdentifierExpression("", "test", "")).
85 SetCols(exp.NewColumnListExpression("a")).
86 SetVals([][]interface{}{
87 {nil},
88 })
89
90 igs.assertCases(
91 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
92 insertTestCase{clause: ic, sql: `INSERT INTO "test" ("a") VALUES (NULL)`},
93 insertTestCase{clause: ic, sql: `INSERT INTO "test" ("a") VALUES (?)`, isPrepared: true, args: []interface{}{nil}},
94 )
95 }
96
97 func (igs *insertSQLGeneratorSuite) TestGenerate_colsAndVals() {
98 opts := sqlgen.DefaultDialectOptions()
99 opts.LeftParenRune = '{'
100 opts.RightParenRune = '}'
101 opts.ValuesFragment = []byte(" values ")
102 opts.LeftParenRune = '{'
103 opts.RightParenRune = '}'
104 opts.CommaRune = ';'
105 opts.PlaceHolderFragment = []byte("#")
106
107 ic := exp.NewInsertClauses().
108 SetInto(exp.NewIdentifierExpression("", "test", "")).
109 SetCols(exp.NewColumnListExpression("a", "b")).
110 SetVals([][]interface{}{
111 {"a1", "b1"},
112 {"a2", "b2"},
113 {"a3", "b3"},
114 })
115
116 bic := ic.SetCols(exp.NewColumnListExpression("a", "b")).
117 SetVals([][]interface{}{
118 {"a1"},
119 {"a2", "b2"},
120 {"a3", "b3"},
121 })
122
123 igs.assertCases(
124 sqlgen.NewInsertSQLGenerator("test", opts),
125 insertTestCase{clause: ic, sql: `INSERT INTO "test" {"a"; "b"} values {'a1'; 'b1'}; {'a2'; 'b2'}; {'a3'; 'b3'}`},
126 insertTestCase{clause: ic, sql: `INSERT INTO "test" {"a"; "b"} values {#; #}; {#; #}; {#; #}`, isPrepared: true, args: []interface{}{
127 "a1", "b1", "a2", "b2", "a3", "b3",
128 }},
129
130 insertTestCase{clause: bic, err: `goqu: rows with different value length expected 1 got 2`},
131 insertTestCase{clause: bic, err: `goqu: rows with different value length expected 1 got 2`, isPrepared: true},
132 )
133 }
134
135 func (igs *insertSQLGeneratorSuite) TestGenerate_withNoInto() {
136 opts := sqlgen.DefaultDialectOptions()
137 opts.LeftParenRune = '{'
138 opts.RightParenRune = '}'
139 opts.ValuesFragment = []byte(" values ")
140 opts.LeftParenRune = '{'
141 opts.RightParenRune = '}'
142 opts.CommaRune = ';'
143 opts.PlaceHolderFragment = []byte("#")
144
145 ic := exp.NewInsertClauses().
146 SetCols(exp.NewColumnListExpression("a", "b")).
147 SetVals([][]interface{}{
148 {"a1", "b1"},
149 {"a2", "b2"},
150 {"a3", "b3"},
151 })
152 expectedErr := "goqu: no source found when generating insert sql"
153 igs.assertCases(
154 sqlgen.NewInsertSQLGenerator("test", opts),
155 insertTestCase{clause: ic, err: expectedErr},
156 insertTestCase{clause: ic, err: expectedErr, isPrepared: true},
157 )
158 }
159
160 func (igs *insertSQLGeneratorSuite) TestGenerate_withRows() {
161 opts := sqlgen.DefaultDialectOptions()
162 opts.LeftParenRune = '{'
163 opts.RightParenRune = '}'
164 opts.ValuesFragment = []byte(" values ")
165 opts.LeftParenRune = '{'
166 opts.RightParenRune = '}'
167 opts.CommaRune = ';'
168 opts.PlaceHolderFragment = []byte("#")
169
170 ic := exp.NewInsertClauses().
171 SetInto(exp.NewIdentifierExpression("", "test", "")).
172 SetRows([]interface{}{
173 exp.Record{"a": "a1", "b": "b1"},
174 exp.Record{"a": "a2", "b": "b2"},
175 exp.Record{"a": "a3", "b": "b3"},
176 })
177
178 bic := ic.SetRows([]interface{}{
179 exp.Record{"a": "a1"},
180 exp.Record{"a": "a2", "b": "b2"},
181 exp.Record{"a": "a3", "b": "b3"},
182 })
183
184 igs.assertCases(
185 sqlgen.NewInsertSQLGenerator("test", opts),
186 insertTestCase{clause: ic, sql: `INSERT INTO "test" {"a"; "b"} values {'a1'; 'b1'}; {'a2'; 'b2'}; {'a3'; 'b3'}`},
187 insertTestCase{clause: ic, sql: `INSERT INTO "test" {"a"; "b"} values {#; #}; {#; #}; {#; #}`, isPrepared: true, args: []interface{}{
188 "a1", "b1", "a2", "b2", "a3", "b3",
189 }},
190
191 insertTestCase{clause: bic, err: `goqu: rows with different value length expected 1 got 2`},
192 insertTestCase{clause: bic, err: `goqu: rows with different value length expected 1 got 2`, isPrepared: true},
193 )
194 }
195
196 func (igs *insertSQLGeneratorSuite) TestGenerate_withEmptyRows() {
197 ic := exp.NewInsertClauses().
198 SetInto(exp.NewIdentifierExpression("", "test", "")).
199 SetRows([]interface{}{exp.Record{}})
200
201 igs.assertCases(
202 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
203 insertTestCase{clause: ic, sql: `INSERT INTO "test" DEFAULT VALUES`},
204 insertTestCase{clause: ic, sql: `INSERT INTO "test" DEFAULT VALUES`, isPrepared: true},
205 )
206
207 opts2 := sqlgen.DefaultDialectOptions()
208 opts2.DefaultValuesFragment = []byte(" default values")
209
210 igs.assertCases(
211 sqlgen.NewInsertSQLGenerator("test", opts2),
212 insertTestCase{clause: ic, sql: `INSERT INTO "test" default values`},
213 insertTestCase{clause: ic, sql: `INSERT INTO "test" default values`, isPrepared: true},
214 )
215 }
216
217 func (igs *insertSQLGeneratorSuite) TestGenerate_withRowsAppendableExpression() {
218 ic := exp.NewInsertClauses().
219 SetInto(exp.NewIdentifierExpression("", "test", "")).
220 SetRows([]interface{}{newTestAppendableExpression(`select * from "other"`, emptyArgs, nil, nil)})
221
222 igs.assertCases(
223 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
224 insertTestCase{clause: ic, sql: `INSERT INTO "test" select * from "other"`},
225 insertTestCase{clause: ic, sql: `INSERT INTO "test" select * from "other"`, isPrepared: true},
226 )
227 }
228
229 func (igs *insertSQLGeneratorSuite) TestGenerate_withFrom() {
230 ic := exp.NewInsertClauses().
231 SetInto(exp.NewIdentifierExpression("", "test", "")).
232 SetFrom(newTestAppendableExpression(`select c, d from test where a = 'b'`, nil, nil, nil))
233
234 icCols := ic.SetCols(exp.NewColumnListExpression("a", "b"))
235 igs.assertCases(
236 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
237 insertTestCase{clause: ic, sql: `INSERT INTO "test" select c, d from test where a = 'b'`},
238 insertTestCase{clause: ic, sql: `INSERT INTO "test" select c, d from test where a = 'b'`, isPrepared: true},
239
240 insertTestCase{clause: icCols, sql: `INSERT INTO "test" ("a", "b") select c, d from test where a = 'b'`},
241 insertTestCase{clause: icCols, sql: `INSERT INTO "test" ("a", "b") select c, d from test where a = 'b'`, isPrepared: true},
242 )
243 }
244
245 func (igs *insertSQLGeneratorSuite) TestGenerate_onConflict() {
246 opts := sqlgen.DefaultDialectOptions()
247
248 opts.ConflictFragment = []byte(" on conflict")
249 opts.ConflictDoNothingFragment = []byte(" do nothing")
250 opts.ConflictDoUpdateFragment = []byte(" do update set ")
251
252 ic := exp.NewInsertClauses().
253 SetInto(exp.NewIdentifierExpression("", "test", "")).
254 SetCols(exp.NewColumnListExpression("a")).
255 SetVals([][]interface{}{
256 {"a1"},
257 })
258 icDn := ic.SetOnConflict(exp.NewDoNothingConflictExpression())
259 icDu := ic.SetOnConflict(exp.NewDoUpdateConflictExpression("test", exp.Record{"a": "b"}))
260 icAsDu := ic.SetAlias(exp.NewIdentifierExpression("", "new", "")).SetOnConflict(
261 exp.NewDoUpdateConflictExpression("test", exp.Record{"a": exp.NewIdentifierExpression("", "new", "a")}),
262 )
263 icDoc := ic.SetOnConflict(exp.NewDoUpdateConflictExpression("on constraint test", exp.Record{"a": "b"}))
264 icDuw := ic.SetOnConflict(
265 exp.NewDoUpdateConflictExpression("test", exp.Record{"a": "b"}).Where(exp.Ex{"foo": true}),
266 )
267
268 icDuNil := ic.SetOnConflict(exp.NewDoUpdateConflictExpression("test", nil))
269 icDuBad := ic.SetOnConflict(exp.NewDoUpdateConflictExpression("test", true))
270
271 igs.assertCases(
272 sqlgen.NewInsertSQLGenerator("test", opts),
273 insertTestCase{clause: icDn, sql: `INSERT INTO "test" ("a") VALUES ('a1') on conflict do nothing`},
274 insertTestCase{
275 clause: icDn,
276 sql: `INSERT INTO "test" ("a") VALUES (?) on conflict do nothing`,
277 isPrepared: true,
278 args: []interface{}{"a1"},
279 },
280
281 insertTestCase{clause: icDu, sql: `INSERT INTO "test" ("a") VALUES ('a1') on conflict (test) do update set "a"='b'`},
282 insertTestCase{
283 clause: icDu,
284 sql: `INSERT INTO "test" ("a") VALUES (?) on conflict (test) do update set "a"=?`,
285 isPrepared: true,
286 args: []interface{}{"a1", "b"},
287 },
288
289 insertTestCase{clause: icAsDu, sql: `INSERT INTO "test" ("a") VALUES ('a1') AS "new" on conflict (test) do update set "a"="new"."a"`},
290 insertTestCase{
291 clause: icAsDu,
292 sql: `INSERT INTO "test" ("a") VALUES (?) AS "new" on conflict (test) do update set "a"="new"."a"`,
293 isPrepared: true,
294 args: []interface{}{"a1"},
295 },
296
297 insertTestCase{clause: icDoc, sql: `INSERT INTO "test" ("a") VALUES ('a1') on conflict on constraint test do update set "a"='b'`},
298 insertTestCase{
299 clause: icDoc,
300 sql: `INSERT INTO "test" ("a") VALUES (?) on conflict on constraint test do update set "a"=?`,
301 isPrepared: true,
302 args: []interface{}{"a1", "b"},
303 },
304
305 insertTestCase{
306 clause: icDuw,
307 sql: `INSERT INTO "test" ("a") VALUES ('a1') on conflict (test) do update set "a"='b' WHERE ("foo" IS TRUE)`,
308 },
309 insertTestCase{
310 clause: icDuw,
311 sql: `INSERT INTO "test" ("a") VALUES (?) on conflict (test) do update set "a"=? WHERE ("foo" IS TRUE)`,
312 isPrepared: true,
313 args: []interface{}{"a1", "b"},
314 },
315
316 insertTestCase{clause: icDuNil, err: sqlgen.ErrConflictUpdateValuesRequired.Error()},
317 insertTestCase{clause: icDuNil, err: sqlgen.ErrConflictUpdateValuesRequired.Error(), isPrepared: true},
318
319 insertTestCase{clause: icDuBad, err: "goqu: unsupported update interface type bool"},
320 insertTestCase{clause: icDuBad, err: "goqu: unsupported update interface type bool", isPrepared: true},
321 )
322 opts.SupportsInsertIgnoreSyntax = true
323 opts.InsertIgnoreClause = []byte("insert ignore into")
324 igs.assertCases(
325 sqlgen.NewInsertSQLGenerator("test", opts),
326 insertTestCase{clause: icDn, sql: `insert ignore into "test" ("a") VALUES ('a1') on conflict do nothing`},
327 insertTestCase{
328 clause: icDn,
329 sql: `insert ignore into "test" ("a") VALUES (?) on conflict do nothing`,
330 isPrepared: true,
331 args: []interface{}{"a1"},
332 },
333
334 insertTestCase{
335 clause: icDu,
336 sql: `insert ignore into "test" ("a") VALUES ('a1') on conflict (test) do update set "a"='b'`,
337 },
338 insertTestCase{
339 clause: icDu,
340 sql: `insert ignore into "test" ("a") VALUES (?) on conflict (test) do update set "a"=?`,
341 isPrepared: true,
342 args: []interface{}{"a1", "b"},
343 },
344
345 insertTestCase{
346 clause: icDoc,
347 sql: `insert ignore into "test" ("a") VALUES ('a1') on conflict on constraint test do update set "a"='b'`,
348 },
349 insertTestCase{
350 clause: icDoc,
351 sql: `insert ignore into "test" ("a") VALUES (?) on conflict on constraint test do update set "a"=?`,
352 isPrepared: true,
353 args: []interface{}{"a1", "b"},
354 },
355
356 insertTestCase{
357 clause: icDuw,
358 sql: `insert ignore into "test" ("a") VALUES ('a1') on conflict (test) do update set "a"='b' WHERE ("foo" IS TRUE)`,
359 },
360 insertTestCase{
361 clause: icDuw,
362 sql: `insert ignore into "test" ("a") VALUES (?) on conflict (test) do update set "a"=? WHERE ("foo" IS TRUE)`,
363 isPrepared: true,
364 args: []interface{}{"a1", "b"},
365 },
366
367 insertTestCase{clause: icDuNil, err: sqlgen.ErrConflictUpdateValuesRequired.Error()},
368 insertTestCase{clause: icDuNil, err: sqlgen.ErrConflictUpdateValuesRequired.Error(), isPrepared: true},
369
370 insertTestCase{clause: icDuBad, err: "goqu: unsupported update interface type bool"},
371 insertTestCase{clause: icDuBad, err: "goqu: unsupported update interface type bool", isPrepared: true},
372 )
373
374 opts.SupportsConflictUpdateWhere = false
375 expectedErr := "goqu: dialect does not support upsert with where clause [dialect=test]"
376 igs.assertCases(
377 sqlgen.NewInsertSQLGenerator("test", opts),
378 insertTestCase{clause: icDuw, err: expectedErr},
379 insertTestCase{clause: icDuw, err: expectedErr, isPrepared: true},
380 )
381 }
382
383 func (igs *insertSQLGeneratorSuite) TestGenerate_withCommonTables() {
384 opts := sqlgen.DefaultDialectOptions()
385 opts.WithFragment = []byte("with ")
386 opts.RecursiveFragment = []byte("recursive ")
387
388 tse := newTestAppendableExpression("select * from foo", emptyArgs, nil, nil)
389
390 ic := exp.NewInsertClauses().SetInto(exp.NewIdentifierExpression("", "test_cte", ""))
391 icCte1 := ic.CommonTablesAppend(exp.NewCommonTableExpression(false, "test_cte", tse))
392 icCte2 := ic.CommonTablesAppend(exp.NewCommonTableExpression(true, "test_cte", tse))
393
394 igs.assertCases(
395 sqlgen.NewInsertSQLGenerator("test", opts),
396 insertTestCase{
397 clause: icCte1,
398 sql: `with test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
399 },
400 insertTestCase{
401 clause: icCte1,
402 sql: `with test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
403 isPrepared: true,
404 },
405
406 insertTestCase{
407 clause: icCte2,
408 sql: `with recursive test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
409 },
410 insertTestCase{
411 clause: icCte2,
412 sql: `with recursive test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
413 isPrepared: true,
414 },
415 )
416
417 opts.SupportsWithCTE = false
418 expectedErr := "goqu: dialect does not support CTE WITH clause [dialect=test]"
419 igs.assertCases(
420 sqlgen.NewInsertSQLGenerator("test", opts),
421 insertTestCase{clause: icCte1, err: expectedErr},
422 insertTestCase{clause: icCte1, err: expectedErr, isPrepared: true},
423
424 insertTestCase{clause: icCte2, err: expectedErr},
425 insertTestCase{clause: icCte2, err: expectedErr, isPrepared: true},
426 )
427
428 opts.SupportsWithCTE = true
429 opts.SupportsWithCTERecursive = false
430 expectedErr = "goqu: dialect does not support CTE WITH RECURSIVE clause [dialect=test]"
431 igs.assertCases(
432 sqlgen.NewInsertSQLGenerator("test", opts),
433 insertTestCase{
434 clause: icCte1,
435 sql: `with test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
436 },
437 insertTestCase{
438 clause: icCte1,
439 sql: `with test_cte AS (select * from foo) INSERT INTO "test_cte" DEFAULT VALUES`,
440 isPrepared: true,
441 },
442
443 insertTestCase{clause: icCte2, err: expectedErr},
444 insertTestCase{clause: icCte2, err: expectedErr, isPrepared: true},
445 )
446 }
447
448 func (igs *insertSQLGeneratorSuite) TestGenerate_withReturning() {
449 ic := exp.NewInsertClauses().
450 SetInto(exp.NewIdentifierExpression("", "test", "")).
451 SetCols(exp.NewColumnListExpression("a", "b")).
452 SetVals([][]interface{}{
453 {"a1", "b1"},
454 }).
455 SetReturning(exp.NewColumnListExpression("a", "b"))
456
457 igs.assertCases(
458 sqlgen.NewInsertSQLGenerator("test", sqlgen.DefaultDialectOptions()),
459 insertTestCase{clause: ic, sql: `INSERT INTO "test" ("a", "b") VALUES ('a1', 'b1') RETURNING "a", "b"`},
460 insertTestCase{clause: ic, sql: `INSERT INTO "test" ("a", "b") VALUES (?, ?) RETURNING "a", "b"`, isPrepared: true, args: []interface{}{
461 "a1", "b1",
462 }},
463 )
464 }
465
466 func TestInsertSQLGenerator(t *testing.T) {
467 suite.Run(t, new(insertSQLGeneratorSuite))
468 }
469
View as plain text