1 package goqu_test
2
3 import (
4 "context"
5 "fmt"
6 "strings"
7 "sync"
8 "testing"
9 "time"
10
11 "github.com/DATA-DOG/go-sqlmock"
12 "github.com/doug-martin/goqu/v9"
13 "github.com/doug-martin/goqu/v9/exp"
14 "github.com/stretchr/testify/suite"
15 )
16
17 type githubIssuesSuite struct {
18 suite.Suite
19 }
20
21 func (gis *githubIssuesSuite) AfterTest(suiteName, testName string) {
22 goqu.SetColumnRenameFunction(strings.ToLower)
23 }
24
25
26 func (gis *githubIssuesSuite) TestIssue49() {
27 dialect := goqu.Dialect("default")
28
29 filters := goqu.Or()
30 sql, args, err := dialect.From("table").Where(filters).ToSQL()
31 gis.NoError(err)
32 gis.Empty(args)
33 gis.Equal(`SELECT * FROM "table"`, sql)
34
35 sql, args, err = dialect.From("table").Where(goqu.Ex{}).ToSQL()
36 gis.NoError(err)
37 gis.Empty(args)
38 gis.Equal(`SELECT * FROM "table"`, sql)
39
40 sql, args, err = dialect.From("table").Where(goqu.ExOr{}).ToSQL()
41 gis.NoError(err)
42 gis.Empty(args)
43 gis.Equal(`SELECT * FROM "table"`, sql)
44 }
45
46
47 func (gis *githubIssuesSuite) TestIssue115() {
48 type TestStruct struct {
49 Field string
50 }
51 goqu.SetColumnRenameFunction(func(col string) string {
52 return ""
53 })
54
55 _, _, err := goqu.Insert("test").Rows(TestStruct{Field: "hello"}).ToSQL()
56 gis.EqualError(err, `goqu: a empty identifier was encountered, please specify a "schema", "table" or "column"`)
57 }
58
59
60 func (gis *githubIssuesSuite) TestIssue118_withEmbeddedStructWithoutExportedFields() {
61
62 type SimpleRole struct {
63 sync.RWMutex
64 permissions []string
65 }
66
67
68
69 type Role struct {
70 *SimpleRole
71
72 ID string `json:"id" db:"id" goqu:"skipinsert"`
73 Key string `json:"key" db:"key"`
74 Name string `json:"name" db:"name"`
75 CreatedAt time.Time `json:"-" db:"created_at" goqu:"skipinsert"`
76 }
77
78 rUser := &Role{
79 Key: `user`,
80 Name: `User role`,
81 }
82
83 sql, arg, err := goqu.Insert(`rbac_roles`).
84 Returning(goqu.C(`id`)).
85 Rows(rUser).
86 ToSQL()
87 gis.NoError(err)
88 gis.Empty(arg)
89 gis.Equal(`INSERT INTO "rbac_roles" ("key", "name") VALUES ('user', 'User role') RETURNING "id"`, sql)
90
91 sql, arg, err = goqu.Update(`rbac_roles`).
92 Returning(goqu.C(`id`)).
93 Set(rUser).
94 ToSQL()
95 gis.NoError(err)
96 gis.Empty(arg)
97 gis.Equal(
98 `UPDATE "rbac_roles" SET "created_at"='0001-01-01T00:00:00Z',"id"='',"key"='user',"name"='User role' RETURNING "id"`,
99 sql,
100 )
101
102 rUser = &Role{
103 SimpleRole: &SimpleRole{},
104 Key: `user`,
105 Name: `User role`,
106 }
107
108 sql, arg, err = goqu.Insert(`rbac_roles`).
109 Returning(goqu.C(`id`)).
110 Rows(rUser).
111 ToSQL()
112 gis.NoError(err)
113 gis.Empty(arg)
114 gis.Equal(`INSERT INTO "rbac_roles" ("key", "name") VALUES ('user', 'User role') RETURNING "id"`, sql)
115
116 sql, arg, err = goqu.Update(`rbac_roles`).
117 Returning(goqu.C(`id`)).
118 Set(rUser).
119 ToSQL()
120 gis.NoError(err)
121 gis.Empty(arg)
122 gis.Equal(
123 `UPDATE "rbac_roles" SET `+
124 `"created_at"='0001-01-01T00:00:00Z',"id"='',"key"='user',"name"='User role' RETURNING "id"`,
125 sql,
126 )
127 }
128
129
130 func (gis *githubIssuesSuite) TestIssue118_withNilEmbeddedStructWithExportedFields() {
131
132 type SimpleRole struct {
133 sync.RWMutex
134 permissions []string
135 IDStr string
136 }
137
138
139
140 type Role struct {
141 *SimpleRole
142
143 ID string `json:"id" db:"id" goqu:"skipinsert"`
144 Key string `json:"key" db:"key"`
145 Name string `json:"name" db:"name"`
146 CreatedAt time.Time `json:"-" db:"created_at" goqu:"skipinsert"`
147 }
148
149 rUser := &Role{
150 Key: `user`,
151 Name: `User role`,
152 }
153 sql, arg, err := goqu.Insert(`rbac_roles`).
154 Returning(goqu.C(`id`)).
155 Rows(rUser).
156 ToSQL()
157 gis.NoError(err)
158 gis.Empty(arg)
159
160 gis.Equal(`INSERT INTO "rbac_roles" ("key", "name") VALUES ('user', 'User role') RETURNING "id"`, sql)
161
162 sql, arg, err = goqu.Update(`rbac_roles`).
163 Returning(goqu.C(`id`)).
164 Set(rUser).
165 ToSQL()
166 gis.NoError(err)
167 gis.Empty(arg)
168
169 gis.Equal(
170 `UPDATE "rbac_roles" SET "created_at"='0001-01-01T00:00:00Z',"id"='',"key"='user',"name"='User role' RETURNING "id"`,
171 sql,
172 )
173
174 rUser = &Role{
175 SimpleRole: &SimpleRole{},
176 Key: `user`,
177 Name: `User role`,
178 }
179 sql, arg, err = goqu.Insert(`rbac_roles`).
180 Returning(goqu.C(`id`)).
181 Rows(rUser).
182 ToSQL()
183 gis.NoError(err)
184 gis.Empty(arg)
185
186 gis.Equal(
187 `INSERT INTO "rbac_roles" ("idstr", "key", "name") VALUES ('', 'user', 'User role') RETURNING "id"`,
188 sql,
189 )
190
191 sql, arg, err = goqu.Update(`rbac_roles`).
192 Returning(goqu.C(`id`)).
193 Set(rUser).
194 ToSQL()
195 gis.NoError(err)
196 gis.Empty(arg)
197
198 gis.Equal(
199 `UPDATE "rbac_roles" SET `+
200 `"created_at"='0001-01-01T00:00:00Z',"id"='',"idstr"='',"key"='user',"name"='User role' RETURNING "id"`,
201 sql,
202 )
203 }
204
205
206 func (gis *githubIssuesSuite) TestIssue140() {
207 sql, arg, err := goqu.Insert(`test`).Returning().ToSQL()
208 gis.NoError(err)
209 gis.Empty(arg)
210 gis.Equal(`INSERT INTO "test" DEFAULT VALUES`, sql)
211
212 sql, arg, err = goqu.Update(`test`).Set(goqu.Record{"a": "b"}).Returning().ToSQL()
213 gis.NoError(err)
214 gis.Empty(arg)
215 gis.Equal(
216 `UPDATE "test" SET "a"='b'`,
217 sql,
218 )
219
220 sql, arg, err = goqu.Delete(`test`).Returning().ToSQL()
221 gis.NoError(err)
222 gis.Empty(arg)
223 gis.Equal(
224 `DELETE FROM "test"`,
225 sql,
226 )
227
228 sql, arg, err = goqu.Insert(`test`).Returning(nil).ToSQL()
229 gis.NoError(err)
230 gis.Empty(arg)
231 gis.Equal(`INSERT INTO "test" DEFAULT VALUES`, sql)
232
233 sql, arg, err = goqu.Update(`test`).Set(goqu.Record{"a": "b"}).Returning(nil).ToSQL()
234 gis.NoError(err)
235 gis.Empty(arg)
236 gis.Equal(
237 `UPDATE "test" SET "a"='b'`,
238 sql,
239 )
240
241 sql, arg, err = goqu.Delete(`test`).Returning(nil).ToSQL()
242 gis.NoError(err)
243 gis.Empty(arg)
244 gis.Equal(
245 `DELETE FROM "test"`,
246 sql,
247 )
248 }
249
250
251 func (gis *githubIssuesSuite) TestIssue164() {
252 insertDs := goqu.Insert("foo").Rows(goqu.Record{"user_id": 10}).Returning("id")
253
254 ds := goqu.From("bar").
255 With("ins", insertDs).
256 Select("bar_name").
257 Where(goqu.Ex{"bar.user_id": goqu.I("ins.user_id")})
258
259 sql, args, err := ds.ToSQL()
260 gis.NoError(err)
261 gis.Empty(args)
262 gis.Equal(
263 `WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (10) RETURNING "id") `+
264 `SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")`,
265 sql,
266 )
267
268 sql, args, err = ds.Prepared(true).ToSQL()
269 gis.NoError(err)
270 gis.Equal([]interface{}{int64(10)}, args)
271 gis.Equal(
272 `WITH ins AS (INSERT INTO "foo" ("user_id") VALUES (?) RETURNING "id")`+
273 ` SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "ins"."user_id")`,
274 sql,
275 )
276
277 updateDs := goqu.Update("foo").Set(goqu.Record{"bar": "baz"}).Returning("id")
278
279 ds = goqu.From("bar").
280 With("upd", updateDs).
281 Select("bar_name").
282 Where(goqu.Ex{"bar.user_id": goqu.I("upd.user_id")})
283
284 sql, args, err = ds.ToSQL()
285 gis.NoError(err)
286 gis.Empty(args)
287 gis.Equal(
288 `WITH upd AS (UPDATE "foo" SET "bar"='baz' RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id")`,
289 sql,
290 )
291
292 sql, args, err = ds.Prepared(true).ToSQL()
293 gis.NoError(err)
294 gis.Equal([]interface{}{"baz"}, args)
295 gis.Equal(
296 `WITH upd AS (UPDATE "foo" SET "bar"=? RETURNING "id") SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "upd"."user_id")`,
297 sql,
298 )
299
300 deleteDs := goqu.Delete("foo").Where(goqu.Ex{"bar": "baz"}).Returning("id")
301
302 ds = goqu.From("bar").
303 With("del", deleteDs).
304 Select("bar_name").
305 Where(goqu.Ex{"bar.user_id": goqu.I("del.user_id")})
306
307 sql, args, err = ds.ToSQL()
308 gis.NoError(err)
309 gis.Empty(args)
310 gis.Equal(
311 `WITH del AS (DELETE FROM "foo" WHERE ("bar" = 'baz') RETURNING "id")`+
312 ` SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")`,
313 sql,
314 )
315
316 sql, args, err = ds.Prepared(true).ToSQL()
317 gis.NoError(err)
318 gis.Equal([]interface{}{"baz"}, args)
319 gis.Equal(
320 `WITH del AS (DELETE FROM "foo" WHERE ("bar" = ?) RETURNING "id")`+
321 ` SELECT "bar_name" FROM "bar" WHERE ("bar"."user_id" = "del"."user_id")`,
322 sql,
323 )
324 }
325
326
327 func (gis *githubIssuesSuite) TestIssue177() {
328 ds := goqu.Dialect("postgres").
329 From("ins1").
330 With("ins1",
331 goqu.Dialect("postgres").
332 Insert("account").
333 Rows(goqu.Record{"email": "email@email.com", "status": "active", "uuid": "XXX-XXX-XXXX"}).
334 Returning("*"),
335 ).
336 With("ins2",
337 goqu.Dialect("postgres").
338 Insert("account_user").
339 Cols("account_id", "user_id").
340 FromQuery(goqu.Dialect("postgres").
341 From("ins1").
342 Select(
343 "id",
344 goqu.V(1001),
345 ),
346 ),
347 ).
348 Select("*")
349 sql, args, err := ds.ToSQL()
350 gis.NoError(err)
351 gis.Equal(`WITH ins1 AS (`+
352 `INSERT INTO "account" ("email", "status", "uuid") VALUES ('email@email.com', 'active', 'XXX-XXX-XXXX') RETURNING *),`+
353 ` ins2 AS (INSERT INTO "account_user" ("account_id", "user_id") SELECT "id", 1001 FROM "ins1")`+
354 ` SELECT * FROM "ins1"`, sql)
355 gis.Len(args, 0)
356
357 sql, args, err = ds.Prepared(true).ToSQL()
358 gis.NoError(err)
359 gis.Equal(`WITH ins1 AS (INSERT INTO "account" ("email", "status", "uuid") VALUES ($1, $2, $3) RETURNING *), ins2`+
360 ` AS (INSERT INTO "account_user" ("account_id", "user_id") SELECT "id", $4 FROM "ins1") SELECT * FROM "ins1"`, sql)
361 gis.Equal(args, []interface{}{"email@email.com", "active", "XXX-XXX-XXXX", int64(1001)})
362 }
363
364
365 func (gis *githubIssuesSuite) TestIssue184() {
366 expectedErr := fmt.Errorf("an error")
367 testCases := []struct {
368 ds exp.AppendableExpression
369 }{
370 {ds: goqu.From("test").As("t").SetError(expectedErr)},
371 {ds: goqu.Insert("test").Rows(goqu.Record{"foo": "bar"}).Returning("foo").SetError(expectedErr)},
372 {ds: goqu.Update("test").Set(goqu.Record{"foo": "bar"}).Returning("foo").SetError(expectedErr)},
373 {ds: goqu.Update("test").Set(goqu.Record{"foo": "bar"}).Returning("foo").SetError(expectedErr)},
374 {ds: goqu.Delete("test").Returning("foo").SetError(expectedErr)},
375 }
376
377 for _, tc := range testCases {
378 ds := goqu.From(tc.ds)
379 sql, args, err := ds.ToSQL()
380 gis.Equal(expectedErr, err)
381 gis.Empty(sql)
382 gis.Empty(args)
383
384 sql, args, err = ds.Prepared(true).ToSQL()
385 gis.Equal(expectedErr, err)
386 gis.Empty(sql)
387 gis.Empty(args)
388
389 ds = goqu.From("test2").Where(goqu.Ex{"foo": tc.ds})
390
391 sql, args, err = ds.ToSQL()
392 gis.Equal(expectedErr, err)
393 gis.Empty(sql)
394 gis.Empty(args)
395
396 sql, args, err = ds.Prepared(true).ToSQL()
397 gis.Equal(expectedErr, err)
398 gis.Empty(sql)
399 gis.Empty(args)
400 }
401 }
402
403
404 func (gis *githubIssuesSuite) TestIssue185() {
405 mDB, sqlMock, err := sqlmock.New()
406 gis.NoError(err)
407 sqlMock.ExpectQuery(
408 `SELECT \* FROM \(SELECT "id" FROM "table" ORDER BY "id" ASC\) AS "t1" UNION
409 \(SELECT \* FROM \(SELECT "id" FROM "table" ORDER BY "id" ASC\) AS "t1"\)`,
410 ).
411 WillReturnRows(sqlmock.NewRows([]string{"id"}).FromCSVString("1\n2\n3\n4\n"))
412 db := goqu.New("mock", mDB)
413
414 ds := db.Select("id").From("table").Order(goqu.C("id").Asc()).
415 Union(
416 db.Select("id").From("table").Order(goqu.C("id").Asc()),
417 )
418
419 ctx := context.Background()
420 var i []int
421 gis.NoError(ds.ScanValsContext(ctx, &i))
422 gis.Equal([]int{1, 2, 3, 4}, i)
423 }
424
425
426 func (gis *githubIssuesSuite) TestIssue203() {
427
428 authSchema := goqu.S("company_auth")
429
430
431 usersTable := authSchema.Table("users")
432
433 u := usersTable.As("u")
434
435 ds := goqu.From(u).Select(
436 u.Col("id"),
437 u.Col("name"),
438 u.Col("created_at"),
439 u.Col("updated_at"),
440 )
441
442 sql, args, err := ds.ToSQL()
443 gis.NoError(err)
444 gis.Equal(`SELECT "u"."id", "u"."name", "u"."created_at", "u"."updated_at" FROM "company_auth"."users" AS "u"`, sql)
445 gis.Empty(args, []interface{}{})
446
447 sql, args, err = ds.Prepared(true).ToSQL()
448 gis.NoError(err)
449 gis.Equal(`SELECT "u"."id", "u"."name", "u"."created_at", "u"."updated_at" FROM "company_auth"."users" AS "u"`, sql)
450 gis.Empty(args, []interface{}{})
451 }
452
453 func (gis *githubIssuesSuite) TestIssue290() {
454 type OcomModel struct {
455 ID uint `json:"id" db:"id" goqu:"skipinsert"`
456 CreatedDate time.Time `json:"created_date" db:"created_date" goqu:"skipupdate"`
457 ModifiedDate time.Time `json:"modified_date" db:"modified_date"`
458 }
459
460 type ActiveModel struct {
461 OcomModel
462 ActiveStartDate time.Time `json:"active_start_date" db:"active_start_date"`
463 ActiveEndDate *time.Time `json:"active_end_date" db:"active_end_date"`
464 }
465
466 type CodeModel struct {
467 ActiveModel
468
469 Code string `json:"code" db:"code"`
470 Description string `json:"description" binding:"required" db:"description"`
471 }
472
473 type CodeExample struct {
474 CodeModel
475 }
476
477 var item CodeExample
478 item.Code = "Code"
479 item.Description = "Description"
480 item.ID = 1
481 item.CreatedDate = time.Date(
482 2021, 1, 1, 1, 1, 1, 1, time.UTC)
483 item.ModifiedDate = time.Date(
484 2021, 2, 2, 2, 2, 2, 2, time.UTC)
485 item.ActiveStartDate = time.Date(
486 2021, 3, 3, 3, 3, 3, 3, time.UTC)
487
488 updateQuery := goqu.From("example").Update().Set(item).Where(goqu.C("id").Eq(1))
489
490 sql, params, err := updateQuery.ToSQL()
491
492 gis.NoError(err)
493 gis.Empty(params)
494 gis.Equal(`UPDATE "example" SET "active_end_date"=NULL,"active_start_date"='2021-03-03T03:03:03.000000003Z',"code"='Code',"description"='Description',"id"=1,"modified_date"='2021-02-02T02:02:02.000000002Z' WHERE ("id" = 1)`, sql)
495 }
496
497 func TestGithubIssuesSuite(t *testing.T) {
498 suite.Run(t, new(githubIssuesSuite))
499 }
500
View as plain text