1 package mysql_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 mysqlDialectSuite 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 (mds *mysqlDialectSuite) GetDs(table string) *goqu.SelectDataset {
26 return goqu.Dialect("mysql").From(table)
27 }
28
29 func (mds *mysqlDialectSuite) assertSQL(cases ...sqlTestCase) {
30 for i, c := range cases {
31 actualSQL, actualArgs, err := c.ds.ToSQL()
32 if c.err == "" {
33 mds.NoError(err, "test case %d failed", i)
34 } else {
35 mds.EqualError(err, c.err, "test case %d failed", i)
36 }
37 mds.Equal(c.sql, actualSQL, "test case %d failed", i)
38 if c.isPrepared && c.args != nil || len(c.args) > 0 {
39 mds.Equal(c.args, actualArgs, "test case %d failed", i)
40 } else {
41 mds.Empty(actualArgs, "test case %d failed", i)
42 }
43 }
44 }
45
46 func (mds *mysqlDialectSuite) TestIdentifiers() {
47 ds := mds.GetDs("test")
48 mds.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 (mds *mysqlDialectSuite) TestLiteralString() {
59 ds := mds.GetDs("test")
60 col := goqu.C("a")
61 mds.assertSQL(
62 sqlTestCase{ds: ds.Where(col.Eq("test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test')"},
63 sqlTestCase{ds: ds.Where(col.Eq("test'test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\'test')"},
64 sqlTestCase{ds: ds.Where(col.Eq(`test"test`)), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\\"test')"},
65 sqlTestCase{ds: ds.Where(col.Eq(`test\test`)), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\\\test')"},
66 sqlTestCase{ds: ds.Where(col.Eq("test\ntest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\ntest')"},
67 sqlTestCase{ds: ds.Where(col.Eq("test\rtest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\rtest')"},
68 sqlTestCase{ds: ds.Where(col.Eq("test\x00test")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\x00test')"},
69 sqlTestCase{ds: ds.Where(col.Eq("test\x1atest")), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\x1atest')"},
70 )
71 }
72
73 func (mds *mysqlDialectSuite) TestLiteralBytes() {
74 col := goqu.C("a")
75 ds := mds.GetDs("test")
76 mds.assertSQL(
77 sqlTestCase{ds: ds.Where(col.Eq([]byte("test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test')"},
78 sqlTestCase{ds: ds.Where(col.Eq([]byte("test'test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\'test')"},
79 sqlTestCase{ds: ds.Where(col.Eq([]byte(`test"test`))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\\"test')"},
80 sqlTestCase{ds: ds.Where(col.Eq([]byte(`test\test`))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\\\test')"},
81 sqlTestCase{ds: ds.Where(col.Eq([]byte("test\ntest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\ntest')"},
82 sqlTestCase{ds: ds.Where(col.Eq([]byte("test\rtest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\rtest')"},
83 sqlTestCase{ds: ds.Where(col.Eq([]byte("test\x00test"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\x00test')"},
84 sqlTestCase{ds: ds.Where(col.Eq([]byte("test\x1atest"))), sql: "SELECT * FROM `test` WHERE (`a` = 'test\\x1atest')"},
85 )
86 }
87
88 func (mds *mysqlDialectSuite) TestBooleanOperations() {
89 col := goqu.C("a")
90 ds := mds.GetDs("test")
91 mds.assertSQL(
92 sqlTestCase{ds: ds.Where(col.Eq(true)), sql: "SELECT * FROM `test` WHERE (`a` IS TRUE)"},
93 sqlTestCase{ds: ds.Where(col.Eq(false)), sql: "SELECT * FROM `test` WHERE (`a` IS FALSE)"},
94 sqlTestCase{ds: ds.Where(col.Is(true)), sql: "SELECT * FROM `test` WHERE (`a` IS TRUE)"},
95 sqlTestCase{ds: ds.Where(col.Is(false)), sql: "SELECT * FROM `test` WHERE (`a` IS FALSE)"},
96 sqlTestCase{ds: ds.Where(col.IsTrue()), sql: "SELECT * FROM `test` WHERE (`a` IS TRUE)"},
97 sqlTestCase{ds: ds.Where(col.IsFalse()), sql: "SELECT * FROM `test` WHERE (`a` IS FALSE)"},
98 sqlTestCase{ds: ds.Where(col.Neq(true)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT TRUE)"},
99 sqlTestCase{ds: ds.Where(col.Neq(false)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT FALSE)"},
100 sqlTestCase{ds: ds.Where(col.IsNot(true)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT TRUE)"},
101 sqlTestCase{ds: ds.Where(col.IsNot(false)), sql: "SELECT * FROM `test` WHERE (`a` IS NOT FALSE)"},
102 sqlTestCase{ds: ds.Where(col.IsNotTrue()), sql: "SELECT * FROM `test` WHERE (`a` IS NOT TRUE)"},
103 sqlTestCase{ds: ds.Where(col.IsNotFalse()), sql: "SELECT * FROM `test` WHERE (`a` IS NOT FALSE)"},
104 sqlTestCase{ds: ds.Where(col.Like("a%")), sql: "SELECT * FROM `test` WHERE (`a` LIKE BINARY 'a%')"},
105 sqlTestCase{ds: ds.Where(col.NotLike("a%")), sql: "SELECT * FROM `test` WHERE (`a` NOT LIKE BINARY 'a%')"},
106 sqlTestCase{ds: ds.Where(col.ILike("a%")), sql: "SELECT * FROM `test` WHERE (`a` LIKE 'a%')"},
107 sqlTestCase{ds: ds.Where(col.NotILike("a%")), sql: "SELECT * FROM `test` WHERE (`a` NOT LIKE 'a%')"},
108 sqlTestCase{ds: ds.Where(col.Like(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` REGEXP BINARY '[ab]')"},
109 sqlTestCase{ds: ds.Where(col.NotLike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` NOT REGEXP BINARY '[ab]')"},
110 sqlTestCase{ds: ds.Where(col.ILike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` REGEXP '[ab]')"},
111 sqlTestCase{ds: ds.Where(col.NotILike(regexp.MustCompile("[ab]"))), sql: "SELECT * FROM `test` WHERE (`a` NOT REGEXP '[ab]')"},
112 )
113 }
114
115 func (mds *mysqlDialectSuite) TestBitwiseOperations() {
116 col := goqu.C("a")
117 ds := mds.GetDs("test")
118 mds.assertSQL(
119 sqlTestCase{ds: ds.Where(col.BitwiseInversion()), sql: "SELECT * FROM `test` WHERE (~ `a`)"},
120 sqlTestCase{ds: ds.Where(col.BitwiseAnd(1)), sql: "SELECT * FROM `test` WHERE (`a` & 1)"},
121 sqlTestCase{ds: ds.Where(col.BitwiseOr(1)), sql: "SELECT * FROM `test` WHERE (`a` | 1)"},
122 sqlTestCase{ds: ds.Where(col.BitwiseXor(1)), sql: "SELECT * FROM `test` WHERE (`a` ^ 1)"},
123 sqlTestCase{ds: ds.Where(col.BitwiseLeftShift(1)), sql: "SELECT * FROM `test` WHERE (`a` << 1)"},
124 sqlTestCase{ds: ds.Where(col.BitwiseRightShift(1)), sql: "SELECT * FROM `test` WHERE (`a` >> 1)"},
125 )
126 }
127
128 func (mds *mysqlDialectSuite) TestUpdateSQL() {
129 ds := mds.GetDs("test").Update()
130 mds.assertSQL(
131 sqlTestCase{
132 ds: ds.
133 Set(goqu.Record{"foo": "bar"}).
134 From("test_2").
135 Where(goqu.I("test.id").Eq(goqu.I("test_2.test_id"))),
136 sql: "UPDATE `test`,`test_2` SET `foo`='bar' WHERE (`test`.`id` = `test_2`.`test_id`)",
137 },
138 )
139 }
140
141 func TestDatasetAdapterSuite(t *testing.T) {
142 suite.Run(t, new(mysqlDialectSuite))
143 }
144
View as plain text