1 package sqlite3_test
2
3 import (
4 "database/sql"
5 "fmt"
6 "testing"
7 "time"
8
9 "github.com/doug-martin/goqu/v9"
10 "github.com/doug-martin/goqu/v9/dialect/mysql"
11 "github.com/doug-martin/goqu/v9/dialect/sqlite3"
12 _ "github.com/mattn/go-sqlite3"
13
14 "github.com/stretchr/testify/suite"
15 )
16
17 const (
18 dropTable = "DROP TABLE IF EXISTS `entry`;"
19 createTable = "CREATE TABLE `entry` (" +
20 "`id` INTEGER PRIMARY KEY," +
21 "`int` INT NOT NULL ," +
22 "`float` FLOAT NOT NULL ," +
23 "`string` VARCHAR(255) NOT NULL ," +
24 "`time` DATETIME NOT NULL ," +
25 "`bool` TINYINT NOT NULL ," +
26 "`bytes` BLOB NOT NULL" +
27 ");"
28 insertDefaultRecords = "INSERT INTO `entry` (`int`, `float`, `string`, `time`, `bool`, `bytes`) VALUES" +
29 "(0, 0.000000, '0.000000', '2015-02-22T18:19:55.000000000-00:00', 1, '0.000000')," +
30 "(1, 0.100000, '0.100000', '2015-02-22T19:19:55.000000000-00:00', 0, '0.100000')," +
31 "(2, 0.200000, '0.200000', '2015-02-22T20:19:55.000000000-00:00', 1, '0.200000')," +
32 "(3, 0.300000, '0.300000', '2015-02-22T21:19:55.000000000-00:00', 0, '0.300000')," +
33 "(4, 0.400000, '0.400000', '2015-02-22T22:19:55.000000000-00:00', 1, '0.400000')," +
34 "(5, 0.500000, '0.500000', '2015-02-22T23:19:55.000000000-00:00', 0, '0.500000')," +
35 "(6, 0.600000, '0.600000', '2015-02-23T00:19:55.000000000-00:00', 1, '0.600000')," +
36 "(7, 0.700000, '0.700000', '2015-02-23T01:19:55.000000000-00:00', 0, '0.700000')," +
37 "(8, 0.800000, '0.800000', '2015-02-23T02:19:55.000000000-00:00', 1, '0.800000')," +
38 "(9, 0.900000, '0.900000', '2015-02-23T03:19:55.000000000-00:00', 0, '0.900000');"
39 )
40
41 var dbURI = ":memory:"
42
43 type (
44 sqlite3Suite struct {
45 suite.Suite
46 db *goqu.Database
47 }
48 entry struct {
49 ID uint32 `db:"id" goqu:"skipinsert,skipupdate"`
50 Int int `db:"int"`
51 Float float64 `db:"float"`
52 String string `db:"string"`
53 Time time.Time `db:"time"`
54 Bool bool `db:"bool"`
55 Bytes []byte `db:"bytes"`
56 }
57 entryTestCase struct {
58 ds *goqu.SelectDataset
59 len int
60 check func(entry entry, index int)
61 err string
62 }
63 )
64
65 func (st *sqlite3Suite) SetupSuite() {
66 db, err := sql.Open("sqlite3", dbURI)
67 if err != nil {
68 panic(err.Error())
69 }
70 st.db = goqu.New("sqlite3", db)
71 }
72
73 func (st *sqlite3Suite) assertSQL(cases ...sqlTestCase) {
74 for i, c := range cases {
75 actualSQL, actualArgs, err := c.ds.ToSQL()
76 if c.err == "" {
77 st.NoError(err, "test case %d failed", i)
78 } else {
79 st.EqualError(err, c.err, "test case %d failed", i)
80 }
81 st.Equal(c.sql, actualSQL, "test case %d failed", i)
82 if c.isPrepared && c.args != nil || len(c.args) > 0 {
83 st.Equal(c.args, actualArgs, "test case %d failed", i)
84 } else {
85 st.Empty(actualArgs, "test case %d failed", i)
86 }
87 }
88 }
89
90 func (st *sqlite3Suite) assertEntries(cases ...entryTestCase) {
91 for i, c := range cases {
92 var entries []entry
93 err := c.ds.ScanStructs(&entries)
94 if c.err == "" {
95 st.NoError(err, "test case %d failed", i)
96 } else {
97 st.EqualError(err, c.err, "test case %d failed", i)
98 }
99 st.Len(entries, c.len)
100 for index, entry := range entries {
101 c.check(entry, index)
102 }
103 }
104 }
105
106 func (st *sqlite3Suite) SetupTest() {
107 if _, err := st.db.Exec(dropTable); err != nil {
108 panic(err)
109 }
110 if _, err := st.db.Exec(createTable); err != nil {
111 panic(err)
112 }
113 if _, err := st.db.Exec(insertDefaultRecords); err != nil {
114 panic(err)
115 }
116 }
117
118 func (st *sqlite3Suite) TestSelectSQL() {
119 ds := st.db.From("entry")
120 st.assertSQL(
121 sqlTestCase{ds: ds.Select("id", "float", "string", "time", "bool"), sql: "SELECT `id`, `float`, `string`, `time`, `bool` FROM `entry`"},
122 sqlTestCase{ds: ds.Where(goqu.C("int").Eq(10)), sql: "SELECT * FROM `entry` WHERE (`int` = 10)"},
123 sqlTestCase{
124 ds: ds.Prepared(true).Where(goqu.L("? = ?", goqu.C("int"), 10)),
125 sql: "SELECT * FROM `entry` WHERE `int` = ?",
126 args: []interface{}{int64(10)},
127 },
128 )
129 }
130
131 func (st *sqlite3Suite) TestCompoundQueries() {
132 ds1 := st.db.From("entry").Select("int").Where(goqu.C("int").Gt(0))
133 ds2 := st.db.From("entry").Select("int").Where(goqu.C("int").Gt(5))
134
135 var ids []int64
136 err := ds1.Union(ds2).ScanVals(&ids)
137 st.NoError(err)
138 st.Equal([]int64{1, 2, 3, 4, 5, 6, 7, 8, 9}, ids)
139
140 ids = ids[0:0]
141 err = ds1.UnionAll(ds2).ScanVals(&ids)
142 st.NoError(err)
143 st.Equal([]int64{1, 2, 3, 4, 5, 6, 7, 8, 9, 6, 7, 8, 9}, ids)
144
145 ids = ids[0:0]
146 err = ds1.Intersect(ds2).ScanVals(&ids)
147 st.NoError(err)
148 st.Equal([]int64{6, 7, 8, 9}, ids)
149 }
150
151 func (st *sqlite3Suite) TestQuery() {
152 ds := st.db.From("entry")
153 floatVal := float64(0)
154 baseDate, err := time.Parse(sqlite3.DialectOptions().TimeFormat, "2015-02-22T18:19:55.000000000-00:00")
155 st.NoError(err)
156 st.assertEntries(
157 entryTestCase{ds: ds.Order(goqu.C("id").Asc()), len: 10, check: func(entry entry, index int) {
158 f := fmt.Sprintf("%f", floatVal)
159 st.Equal(uint32(index+1), entry.ID)
160 st.Equal(index, entry.Int)
161 st.Equal(f, fmt.Sprintf("%f", entry.Float))
162 st.Equal(f, entry.String)
163 st.Equal([]byte(f), entry.Bytes)
164 st.Equal(index%2 == 0, entry.Bool)
165 st.Equal(baseDate.Add(time.Duration(index)*time.Hour).Unix(), entry.Time.Unix())
166 floatVal += float64(0.1)
167 }},
168 entryTestCase{ds: ds.Where(goqu.C("bool").IsTrue()).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
169 st.True(entry.Bool)
170 }},
171 entryTestCase{ds: ds.Where(goqu.C("int").Gt(4)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
172 st.True(entry.Int > 4)
173 }},
174 entryTestCase{ds: ds.Where(goqu.C("int").Gte(5)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
175 st.True(entry.Int >= 5)
176 }},
177 entryTestCase{ds: ds.Where(goqu.C("int").Lt(5)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
178 st.True(entry.Int < 5)
179 }},
180 entryTestCase{ds: ds.Where(goqu.C("int").Lte(4)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
181 st.True(entry.Int <= 4)
182 }},
183 entryTestCase{ds: ds.Where(goqu.C("int").Between(goqu.Range(3, 6))).Order(goqu.C("id").Asc()), len: 4, check: func(entry entry, _ int) {
184 st.True(entry.Int >= 3)
185 st.True(entry.Int <= 6)
186 }},
187 entryTestCase{ds: ds.Where(goqu.C("string").Eq("0.100000")).Order(goqu.C("id").Asc()), len: 1, check: func(entry entry, _ int) {
188 st.Equal(entry.String, "0.100000")
189 }},
190 entryTestCase{ds: ds.Where(goqu.C("string").Like("0.1%")).Order(goqu.C("id").Asc()), len: 1, check: func(entry entry, _ int) {
191 st.Equal(entry.String, "0.100000")
192 }},
193 entryTestCase{ds: ds.Where(goqu.C("string").NotLike("0.1%")).Order(goqu.C("id").Asc()), len: 9, check: func(entry entry, _ int) {
194 st.NotEqual(entry.String, "0.100000")
195 }},
196 entryTestCase{ds: ds.Where(goqu.C("string").IsNull()).Order(goqu.C("id").Asc()), len: 0, check: func(entry entry, _ int) {
197 st.Fail("Should not have returned any records")
198 }},
199 )
200 }
201
202 func (st *sqlite3Suite) TestQuery_Prepared() {
203 ds := st.db.From("entry").Prepared(true)
204 floatVal := float64(0)
205 baseDate, err := time.Parse(sqlite3.DialectOptions().TimeFormat, "2015-02-22T18:19:55.000000000-00:00")
206 st.NoError(err)
207 st.assertEntries(
208 entryTestCase{ds: ds.Order(goqu.C("id").Asc()), len: 10, check: func(entry entry, index int) {
209 f := fmt.Sprintf("%f", floatVal)
210 st.Equal(uint32(index+1), entry.ID)
211 st.Equal(index, entry.Int)
212 st.Equal(f, fmt.Sprintf("%f", entry.Float))
213 st.Equal(f, entry.String)
214 st.Equal([]byte(f), entry.Bytes)
215 st.Equal(index%2 == 0, entry.Bool)
216 st.Equal(baseDate.Add(time.Duration(index)*time.Hour).Unix(), entry.Time.Unix())
217 floatVal += float64(0.1)
218 }},
219 entryTestCase{ds: ds.Where(goqu.C("bool").IsTrue()).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
220 st.True(entry.Bool)
221 }},
222 entryTestCase{ds: ds.Where(goqu.C("int").Gt(4)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
223 st.True(entry.Int > 4)
224 }},
225 entryTestCase{ds: ds.Where(goqu.C("int").Gte(5)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
226 st.True(entry.Int >= 5)
227 }},
228 entryTestCase{ds: ds.Where(goqu.C("int").Lt(5)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
229 st.True(entry.Int < 5)
230 }},
231 entryTestCase{ds: ds.Where(goqu.C("int").Lte(4)).Order(goqu.C("id").Asc()), len: 5, check: func(entry entry, _ int) {
232 st.True(entry.Int <= 4)
233 }},
234 entryTestCase{ds: ds.Where(goqu.C("int").Between(goqu.Range(3, 6))).Order(goqu.C("id").Asc()), len: 4, check: func(entry entry, _ int) {
235 st.True(entry.Int >= 3)
236 st.True(entry.Int <= 6)
237 }},
238 entryTestCase{ds: ds.Where(goqu.C("string").Eq("0.100000")).Order(goqu.C("id").Asc()), len: 1, check: func(entry entry, _ int) {
239 st.Equal(entry.String, "0.100000")
240 }},
241 entryTestCase{ds: ds.Where(goqu.C("string").Like("0.1%")).Order(goqu.C("id").Asc()), len: 1, check: func(entry entry, _ int) {
242 st.Equal(entry.String, "0.100000")
243 }},
244 entryTestCase{ds: ds.Where(goqu.C("string").NotLike("0.1%")).Order(goqu.C("id").Asc()), len: 9, check: func(entry entry, _ int) {
245 st.NotEqual(entry.String, "0.100000")
246 }},
247 entryTestCase{ds: ds.Where(goqu.C("string").IsNull()).Order(goqu.C("id").Asc()), len: 0, check: func(entry entry, _ int) {
248 st.Fail("Should not have returned any records")
249 }},
250 )
251 }
252
253 func (st *sqlite3Suite) TestQuery_ValueExpressions() {
254 type wrappedEntry struct {
255 entry
256 BoolValue bool `db:"bool_value"`
257 }
258 expectedDate, err := time.Parse("2006-01-02T15:04:05.000000000-00:00", "2015-02-22T19:19:55.000000000-00:00")
259 st.NoError(err)
260 ds := st.db.From("entry").Select(goqu.Star(), goqu.V(true).As("bool_value")).Where(goqu.Ex{"int": 1})
261 var we wrappedEntry
262 found, err := ds.ScanStruct(&we)
263 st.NoError(err)
264 st.True(found)
265 st.Equal(we, wrappedEntry{
266 entry{2, 1, 0.100000, "0.100000", expectedDate, false, []byte("0.100000")},
267 true,
268 })
269 }
270
271 func (st *sqlite3Suite) TestCount() {
272 ds := st.db.From("entry")
273 count, err := ds.Count()
274 st.NoError(err)
275 st.Equal(int64(10), count)
276 count, err = ds.Where(goqu.C("int").Gt(4)).Count()
277 st.NoError(err)
278 st.Equal(int64(5), count)
279 count, err = ds.Where(goqu.C("int").Gte(4)).Count()
280 st.NoError(err)
281 st.Equal(int64(6), count)
282 count, err = ds.Where(goqu.C("string").Like("0.1%")).Count()
283 st.NoError(err)
284 st.Equal(int64(1), count)
285 count, err = ds.Where(goqu.C("string").IsNull()).Count()
286 st.NoError(err)
287 st.Equal(int64(0), count)
288 }
289
290 func (st *sqlite3Suite) TestInsert() {
291 ds := st.db.From("entry")
292 now := time.Now()
293 e := entry{Int: 10, Float: 1.000000, String: "1.000000", Time: now, Bool: true, Bytes: []byte("1.000000")}
294 _, err := ds.Insert().Rows(e).Executor().Exec()
295 st.NoError(err)
296
297 var insertedEntry entry
298 found, err := ds.Where(goqu.C("int").Eq(10)).ScanStruct(&insertedEntry)
299 st.NoError(err)
300 st.True(found)
301 st.True(insertedEntry.ID > 0)
302
303 entries := []entry{
304 {Int: 11, Float: 1.100000, String: "1.100000", Time: now, Bool: false, Bytes: []byte("1.100000")},
305 {Int: 12, Float: 1.200000, String: "1.200000", Time: now, Bool: true, Bytes: []byte("1.200000")},
306 {Int: 13, Float: 1.300000, String: "1.300000", Time: now, Bool: false, Bytes: []byte("1.300000")},
307 {Int: 14, Float: 1.400000, String: "1.400000", Time: now, Bool: true, Bytes: []byte("1.400000")},
308 {Int: 14, Float: 1.400000, String: `abc'd"e"f\\gh\n\ri\x00`, Time: now, Bool: true, Bytes: []byte("1.400000")},
309 }
310 _, err = ds.Insert().Rows(entries).Executor().Exec()
311 st.NoError(err)
312
313 var newEntries []entry
314 st.NoError(ds.Where(goqu.C("int").In([]uint32{11, 12, 13, 14})).ScanStructs(&newEntries))
315 for i, e := range newEntries {
316 st.Equal(entries[i].Int, e.Int)
317 st.Equal(entries[i].Float, e.Float)
318 st.Equal(entries[i].String, e.String)
319 st.Equal(entries[i].Time.UTC().Format(mysql.DialectOptions().TimeFormat), e.Time.Format(mysql.DialectOptions().TimeFormat))
320 st.Equal(entries[i].Bool, e.Bool)
321 st.Equal(entries[i].Bytes, e.Bytes)
322 }
323
324 _, err = ds.Insert().Rows(
325 entry{Int: 15, Float: 1.500000, String: "1.500000", Time: now, Bool: false, Bytes: []byte("1.500000")},
326 entry{Int: 16, Float: 1.600000, String: "1.600000", Time: now, Bool: true, Bytes: []byte("1.600000")},
327 entry{Int: 17, Float: 1.700000, String: "1.700000", Time: now, Bool: false, Bytes: []byte("1.700000")},
328 entry{Int: 18, Float: 1.800000, String: "1.800000", Time: now, Bool: true, Bytes: []byte("1.800000")},
329 entry{Int: 18, Float: 1.800000, String: `abc'd"e"f\\gh\n\ri\x00`, Time: now, Bool: true, Bytes: []byte("1.800000")},
330 ).Executor().Exec()
331 st.NoError(err)
332
333 newEntries = newEntries[0:0]
334 st.NoError(ds.Where(goqu.C("int").In([]uint32{15, 16, 17, 18})).ScanStructs(&newEntries))
335 st.Len(newEntries, 5)
336 }
337
338 func (st *sqlite3Suite) TestInsert_returning() {
339 ds := st.db.From("entry")
340 now := time.Now()
341 e := entry{Int: 10, Float: 1.000000, String: "1.000000", Time: now, Bool: true, Bytes: []byte("1.000000")}
342 _, err := ds.Insert().Rows(e).Returning(goqu.Star()).Executor().ScanStruct(&e)
343 st.Error(err)
344 }
345
346 func (st *sqlite3Suite) TestUpdate() {
347 ds := st.db.From("entry")
348 var e entry
349 found, err := ds.Where(goqu.C("int").Eq(9)).Select("id").ScanStruct(&e)
350 st.NoError(err)
351 st.True(found)
352 e.Int = 11
353 _, err = ds.Where(goqu.C("id").Eq(e.ID)).Update().Set(e).Executor().Exec()
354 st.NoError(err)
355
356 count, err := ds.Where(goqu.C("int").Eq(11)).Count()
357 st.NoError(err)
358 st.Equal(int64(1), count)
359 }
360
361 func (st *sqlite3Suite) TestUpdateReturning() {
362 ds := st.db.From("entry")
363 var id uint32
364 _, err := ds.
365 Where(goqu.C("int").Eq(11)).
366 Update().
367 Set(map[string]interface{}{"int": 9}).
368 Returning("id").
369 Executor().ScanVal(&id)
370 st.Error(err)
371 st.EqualError(err, "goqu: dialect does not support RETURNING clause [dialect=sqlite3]")
372 }
373
374 func (st *sqlite3Suite) TestDelete() {
375 ds := st.db.From("entry")
376 var e entry
377 found, err := ds.Where(goqu.C("int").Eq(9)).Select("id").ScanStruct(&e)
378 st.NoError(err)
379 st.True(found)
380 _, err = ds.Where(goqu.C("id").Eq(e.ID)).Delete().Executor().Exec()
381 st.NoError(err)
382
383 count, err := ds.Count()
384 st.NoError(err)
385 st.Equal(int64(9), count)
386
387 var id uint32
388 found, err = ds.Where(goqu.C("id").Eq(e.ID)).ScanVal(&id)
389 st.NoError(err)
390 st.False(found)
391
392 e = entry{}
393 found, err = ds.Where(goqu.C("int").Eq(8)).Select("id").ScanStruct(&e)
394 st.NoError(err)
395 st.True(found)
396 st.NotEqual(int64(0), e.ID)
397
398 id = 0
399 _, err = ds.Where(goqu.C("id").Eq(e.ID)).Delete().Returning("id").Executor().ScanVal(&id)
400 st.EqualError(err, "goqu: dialect does not support RETURNING clause [dialect=sqlite3]")
401 }
402
403 func (st *sqlite3Suite) TestInsert_OnConflict() {
404 ds := st.db.From("entry")
405 now := time.Now()
406
407
408 e := entry{Int: 11, Float: 1.100000, String: "1.100000", Time: now, Bool: false, Bytes: []byte("1.100000")}
409 _, err := ds.Insert().Rows(e).OnConflict(goqu.DoNothing()).Executor().Exec()
410 st.NoError(err)
411
412 var entryActual entry
413 _, err = ds.Where(goqu.C("id").Eq(11)).ScanStruct(&entryActual)
414 st.NoError(err)
415 st.Equal("1.100000", entryActual.String)
416
417
418 _, err = ds.Insert().Rows(
419 goqu.Record{
420 "id": 11,
421 "int": 99999999,
422 "float": "0.99999999",
423 "string": "99999999",
424 "time": now,
425 "bool": true,
426 "bytes": []byte("0.99999999"),
427 },
428 ).OnConflict(goqu.DoNothing()).Executor().Exec()
429 st.NoError(err)
430
431 _, err = ds.Where(goqu.C("id").Eq(11)).ScanStruct(&entryActual)
432 st.NoError(err)
433 st.Equal("1.100000", entryActual.String)
434
435
436 _, err = ds.Insert().Rows(
437 goqu.Record{
438 "id": 11,
439 "int": 11,
440 "float": "1.100000",
441 "string": "1.100000",
442 "time": now,
443 "bool": true,
444 "bytes": []byte("1.100000"),
445 },
446 ).OnConflict(goqu.DoUpdate("id", goqu.Record{"string": "upsert"})).Executor().Exec()
447 st.NoError(err)
448
449 _, err = ds.Where(goqu.C("id").Eq(11)).ScanStruct(&entryActual)
450 st.NoError(err)
451 st.Equal("upsert", entryActual.String)
452
453
454 entries := []entry{
455 {Int: 8, Float: 6.100000, String: "6.100000", Time: now, Bytes: []byte("6.100000")},
456 {Int: 9, Float: 7.200000, String: "7.200000", Time: now, Bytes: []byte("7.200000")},
457 }
458 _, err = ds.Insert().
459 Rows(entries).
460 OnConflict(goqu.DoUpdate("id", goqu.Record{"string": "upsert"}).Where(goqu.C("id").Eq(9))).
461 Executor().Exec()
462 st.EqualError(err, "goqu: dialect does not support upsert with where clause [dialect=sqlite3]")
463 }
464
465 func TestSqlite3Suite(t *testing.T) {
466 suite.Run(t, new(sqlite3Suite))
467 }
468
View as plain text