1 package sqlite3_test
2
3 import (
4 "regexp"
5 "testing"
6
7 "github.com/doug-martin/goqu/v9"
8 "github.com/doug-martin/goqu/v9/exp"
9 "github.com/stretchr/testify/suite"
10 )
11
12 type (
13 sqlite3DialectSuite struct {
14 suite.Suite
15 }
16 sqlTestCase struct {
17 ds exp.SQLExpression
18 sql string
19 err string
20 isPrepared bool
21 args []interface{}
22 }
23 )
24
25 func (sds *sqlite3DialectSuite) GetDs(table string) *goqu.SelectDataset {
26 return goqu.Dialect("sqlite3").From(table)
27 }
28
29 func (sds *sqlite3DialectSuite) assertSQL(cases ...sqlTestCase) {
30 for i, c := range cases {
31 actualSQL, actualArgs, err := c.ds.ToSQL()
32 if c.err == "" {
33 sds.NoError(err, "test case %d failed", i)
34 } else {
35 sds.EqualError(err, c.err, "test case %d failed", i)
36 }
37 sds.Equal(c.sql, actualSQL, "test case %d failed", i)
38 if c.isPrepared && c.args != nil || len(c.args) > 0 {
39 sds.Equal(c.args, actualArgs, "test case %d failed", i)
40 } else {
41 sds.Empty(actualArgs, "test case %d failed", i)
42 }
43 }
44 }
45
46 func (sds *sqlite3DialectSuite) TestIdentifiers() {
47 ds := sds.GetDs("test")
48 sds.assertSQL(
49 sqlTestCase{ds: ds.Select(
50 "a",
51 goqu.I("a.b.c"),
52 goqu.I("c.d"),
53 goqu.C("test").As("test"),
54 ), sql: "SELECT `a`, `a`.`b`.`c`, `c`.`d`, `test` AS `test` FROM `test`"},
55 )
56 }
57
58 func (sds *sqlite3DialectSuite) TestUpdateSQL_multipleTables() {
59 ds := sds.GetDs("test").Update()
60 sds.assertSQL(
61 sqlTestCase{
62 ds: ds.
63 Set(goqu.Record{"foo": "bar"}).
64 From("test_2").
65 Where(goqu.I("test.id").Eq(goqu.I("test_2.test_id"))),
66 err: "goqu: sqlite3 dialect does not support multiple tables in UPDATE",
67 },
68 )
69 }
70
71 func (sds *sqlite3DialectSuite) TestCompoundExpressions() {
72 ds1 := sds.GetDs("test").Select("a")
73 ds2 := sds.GetDs("test2").Select("b")
74 sds.assertSQL(
75 sqlTestCase{ds: ds1.Union(ds2), sql: "SELECT `a` FROM `test` UNION SELECT `b` FROM `test2`"},
76 sqlTestCase{ds: ds1.UnionAll(ds2), sql: "SELECT `a` FROM `test` UNION ALL SELECT `b` FROM `test2`"},
77 sqlTestCase{ds: ds1.Intersect(ds2), sql: "SELECT `a` FROM `test` INTERSECT SELECT `b` FROM `test2`"},
78 )
79 }
80
81 func (sds *sqlite3DialectSuite) TestLiteralString() {
82 ds := sds.GetDs("test")
83 sds.assertSQL(
84 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test')"},
85 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test'test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test''test')"},
86 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(`test"test`)), sql: "SELECT * FROM `test` WHERE (`a` = 'test\"test')"},
87 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(`test\test`)), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\test')"},
88 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test\ntest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\ntest')"},
89 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test\rtest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\rtest')"},
90 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test\x00test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\x00test')"},
91 sqlTestCase{ds: ds.Where(goqu.C("a").Eq("test\x1atest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\x1atest')"},
92 )
93 }
94
95 func (sds *sqlite3DialectSuite) TestLiteralBytes() {
96 ds := sds.GetDs("test")
97 sds.assertSQL(
98 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test')"},
99 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test'test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test''test')"},
100 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte(`test"test`))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\"test')"},
101 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte(`test\test`))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\test')"},
102 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test\ntest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\ntest')"},
103 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test\rtest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\rtest')"},
104 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test\x00test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\x00test')"},
105 sqlTestCase{ds: ds.Where(goqu.C("a").Eq([]byte("test\x1atest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\x1atest')"},
106 )
107 }
108
109 func (sds *sqlite3DialectSuite) TestBooleanOperations() {
110 ds := sds.GetDs("test")
111 sds.assertSQL(
112 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(true)), sql: "SELECT * FROM `test` WHERE (`a` IS 1)"},
113 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(false)), sql: "SELECT * FROM `test` WHERE (`a` IS 0)"},
114 sqlTestCase{ds: ds.Where(goqu.C("a").Is(true)), sql: "SELECT * FROM `test` WHERE (`a` IS 1)"},
115 sqlTestCase{ds: ds.Where(goqu.C("a").Is(false)), sql: "SELECT * FROM `test` WHERE (`a` IS 0)"},
116 sqlTestCase{ds: ds.Where(goqu.C("a").IsTrue()), sql: "SELECT * FROM `test` WHERE (`a` IS 1)"},
117 sqlTestCase{ds: ds.Where(goqu.C("a").IsFalse()), sql: "SELECT * FROM `test` WHERE (`a` IS 0)"},
118 sqlTestCase{ds: ds.Where(goqu.C("a").Neq(true)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 1)"},
119 sqlTestCase{ds: ds.Where(goqu.C("a").Neq(false)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 0)"},
120 sqlTestCase{ds: ds.Where(goqu.C("a").IsNot(true)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 1)"},
121 sqlTestCase{ds: ds.Where(goqu.C("a").IsNot(false)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 0)"},
122 sqlTestCase{ds: ds.Where(goqu.C("a").IsNotTrue()), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 1)"},
123 sqlTestCase{ds: ds.Where(goqu.C("a").IsNotFalse()), sql: "SELECT * FROM `test` WHERE (`a` IS NOT 0)"},
124 sqlTestCase{ds: ds.Where(goqu.C("a").Like("a%")), sql: "SELECT * FROM `test` WHERE (`a` LIKE 'a%')"},
125 sqlTestCase{ds: ds.Where(goqu.C("a").NotLike("a%")), sql: "SELECT * FROM `test` WHERE (`a` NOT LIKE 'a%')"},
126 sqlTestCase{ds: ds.Where(goqu.C("a").ILike("a%")), sql: "SELECT * FROM `test` WHERE (`a` LIKE 'a%')"},
127 sqlTestCase{ds: ds.Where(goqu.C("a").NotILike("a%")), sql: "SELECT * FROM `test` WHERE (`a` NOT LIKE 'a%')"},
128 sqlTestCase{ds: ds.Where(goqu.C("a").Like(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` REGEXP '[ab]')"},
129 sqlTestCase{ds: ds.Where(goqu.C("a").NotLike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` NOT REGEXP '[ab]')"},
130 sqlTestCase{ds: ds.Where(goqu.C("a").ILike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` REGEXP '[ab]')"},
131 sqlTestCase{ds: ds.Where(goqu.C("a").NotILike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` NOT REGEXP '[ab]')"},
132 )
133 }
134
135 func (sds *sqlite3DialectSuite) TestBitwiseOperations() {
136 col := goqu.C("a")
137 ds := sds.GetDs("test")
138 sds.assertSQL(
139 sqlTestCase{ds: ds.Where(col.BitwiseInversion()), err: "goqu: bitwise operator 'Inversion' not supported"},
140 sqlTestCase{ds: ds.Where(col.BitwiseAnd(1)), sql: "SELECT * FROM `test` WHERE (`a` & 1)"},
141 sqlTestCase{ds: ds.Where(col.BitwiseOr(1)), sql: "SELECT * FROM `test` WHERE (`a` | 1)"},
142 sqlTestCase{ds: ds.Where(col.BitwiseXor(1)), err: "goqu: bitwise operator 'XOR' not supported"},
143 sqlTestCase{ds: ds.Where(col.BitwiseLeftShift(1)), sql: "SELECT * FROM `test` WHERE (`a` << 1)"},
144 sqlTestCase{ds: ds.Where(col.BitwiseRightShift(1)), sql: "SELECT * FROM `test` WHERE (`a` >> 1)"},
145 )
146 }
147
148 func (sds *sqlite3DialectSuite) TestForUpdate() {
149 ds := sds.GetDs("test")
150 sds.assertSQL(
151 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(1)).ForUpdate(goqu.Wait), sql: "SELECT * FROM `test` WHERE (`a` = 1)"},
152 sqlTestCase{ds: ds.Where(goqu.C("a").Eq(1)).ForUpdate(goqu.NoWait), sql: "SELECT * FROM `test` WHERE (`a` = 1)"},
153 )
154 }
155
156 func TestDatasetAdapterSuite(t *testing.T) {
157 suite.Run(t, new(sqlite3DialectSuite))
158 }
159
View as plain text