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 updateTestCase struct {
14 clause exp.UpdateClauses
15 sql string
16 isPrepared bool
17 args []interface{}
18 err string
19 }
20 updateSQLGeneratorSuite struct {
21 baseSQLGeneratorSuite
22 }
23 )
24
25 func (usgs *updateSQLGeneratorSuite) assertCases(usg sqlgen.UpdateSQLGenerator, testCases ...updateTestCase) {
26 for _, tc := range testCases {
27 b := sb.NewSQLBuilder(tc.isPrepared)
28 usg.Generate(b, tc.clause)
29 switch {
30 case len(tc.err) > 0:
31 usgs.assertErrorSQL(b, tc.err)
32 case tc.isPrepared:
33 usgs.assertPreparedSQL(b, tc.sql, tc.args)
34 default:
35 usgs.assertNotPreparedSQL(b, tc.sql)
36 }
37 }
38 }
39
40 func (usgs *updateSQLGeneratorSuite) TestDialect() {
41 opts := sqlgen.DefaultDialectOptions()
42 d := sqlgen.NewUpdateSQLGenerator("test", opts)
43 usgs.Equal("test", d.Dialect())
44
45 opts2 := sqlgen.DefaultDialectOptions()
46 d2 := sqlgen.NewUpdateSQLGenerator("test2", opts2)
47 usgs.Equal("test2", d2.Dialect())
48 }
49
50 func (usgs *updateSQLGeneratorSuite) TestGenerate_unsupportedFragment() {
51 opts := sqlgen.DefaultDialectOptions()
52 opts.UpdateSQLOrder = []sqlgen.SQLFragmentType{sqlgen.InsertBeingSQLFragment}
53
54 uc := exp.NewUpdateClauses().
55 SetTable(exp.NewIdentifierExpression("", "test", "")).
56 SetSetValues(exp.Record{"a": "b", "b": "c"})
57 expectedErr := "goqu: unsupported UPDATE SQL fragment InsertBeingSQLFragment"
58 usgs.assertCases(
59 sqlgen.NewUpdateSQLGenerator("test", opts),
60 updateTestCase{clause: uc, err: expectedErr},
61 updateTestCase{clause: uc, err: expectedErr, isPrepared: true},
62 )
63 }
64
65 func (usgs *updateSQLGeneratorSuite) TestGenerate_empty() {
66 uc := exp.NewUpdateClauses()
67 usgs.assertCases(
68 sqlgen.NewUpdateSQLGenerator("test", sqlgen.DefaultDialectOptions()),
69 updateTestCase{clause: uc, err: sqlgen.ErrNoSourceForUpdate.Error()},
70 updateTestCase{clause: uc, err: sqlgen.ErrNoSourceForUpdate.Error(), isPrepared: true},
71 )
72 }
73
74 func (usgs *updateSQLGeneratorSuite) TestGenerate_withBadUpdateValues() {
75 uc := exp.NewUpdateClauses().
76 SetTable(exp.NewIdentifierExpression("", "test", "")).
77 SetSetValues(true)
78
79 expectedErr := "goqu: unsupported update interface type bool"
80 usgs.assertCases(
81 sqlgen.NewUpdateSQLGenerator("test", sqlgen.DefaultDialectOptions()),
82 updateTestCase{clause: uc, err: expectedErr},
83 updateTestCase{clause: uc, err: expectedErr, isPrepared: true},
84 )
85 }
86
87 func (usgs *updateSQLGeneratorSuite) TestGenerate_noSetValues() {
88 uc := exp.NewUpdateClauses().SetTable(exp.NewIdentifierExpression("", "test", ""))
89
90 expectedErr := sqlgen.ErrNoSetValuesForUpdate.Error()
91 usgs.assertCases(
92 sqlgen.NewUpdateSQLGenerator("test", sqlgen.DefaultDialectOptions()),
93 updateTestCase{clause: uc, err: expectedErr},
94 updateTestCase{clause: uc, err: expectedErr, isPrepared: true},
95 )
96 }
97
98 func (usgs *updateSQLGeneratorSuite) TestGenerate_withFrom() {
99 uc := exp.NewUpdateClauses().
100 SetTable(exp.NewIdentifierExpression("", "test", "")).
101 SetSetValues(exp.Record{"foo": "bar"}).
102 SetFrom(exp.NewColumnListExpression("other_test"))
103
104 ucNullSet := exp.NewUpdateClauses().
105 SetTable(exp.NewIdentifierExpression("", "test", "")).
106 SetSetValues(exp.Record{"foo": nil}).
107 SetFrom(exp.NewColumnListExpression("other_test"))
108
109 opts := sqlgen.DefaultDialectOptions()
110 usgs.assertCases(
111 sqlgen.NewUpdateSQLGenerator("test", opts),
112 updateTestCase{clause: uc, sql: `UPDATE "test" SET "foo"='bar' FROM "other_test"`},
113 updateTestCase{clause: uc, sql: `UPDATE "test" SET "foo"=? FROM "other_test"`, isPrepared: true, args: []interface{}{"bar"}},
114
115 updateTestCase{clause: ucNullSet, sql: `UPDATE "test" SET "foo"=NULL FROM "other_test"`},
116 updateTestCase{clause: ucNullSet, sql: `UPDATE "test" SET "foo"=? FROM "other_test"`, isPrepared: true, args: []interface{}{nil}},
117 )
118
119 opts = sqlgen.DefaultDialectOptions()
120 opts.UseFromClauseForMultipleUpdateTables = false
121 usgs.assertCases(
122 sqlgen.NewUpdateSQLGenerator("test", opts),
123 updateTestCase{clause: uc, sql: `UPDATE "test","other_test" SET "foo"='bar'`},
124 updateTestCase{clause: uc, sql: `UPDATE "test","other_test" SET "foo"=?`, isPrepared: true, args: []interface{}{"bar"}},
125
126 updateTestCase{clause: ucNullSet, sql: `UPDATE "test","other_test" SET "foo"=NULL`},
127 updateTestCase{clause: ucNullSet, sql: `UPDATE "test","other_test" SET "foo"=?`, isPrepared: true, args: []interface{}{nil}},
128 )
129
130 opts = sqlgen.DefaultDialectOptions()
131 opts.SupportsMultipleUpdateTables = false
132 expectedErr := "goqu: test dialect does not support multiple tables in UPDATE"
133 usgs.assertCases(
134 sqlgen.NewUpdateSQLGenerator("test", opts),
135 updateTestCase{clause: uc, err: expectedErr},
136 updateTestCase{clause: uc, err: expectedErr, isPrepared: true},
137 )
138 }
139
140 func (usgs *updateSQLGeneratorSuite) TestGenerate_withUpdateExpression() {
141 opts := sqlgen.DefaultDialectOptions()
142
143 opts.SetFragment = []byte(" set ")
144 uc := exp.NewUpdateClauses().
145 SetTable(exp.NewIdentifierExpression("", "test", ""))
146 ucRecord := uc.SetSetValues(exp.Record{"a": "b", "b": "c"})
147 ucRecordNullVal := uc.SetSetValues(exp.Record{"a": "b", "b": nil})
148 ucRecordBoolVals := uc.SetSetValues(exp.Record{"a": true, "b": false})
149 ucEmptyRecord := uc.SetSetValues(exp.Record{})
150
151 usgs.assertCases(
152 sqlgen.NewUpdateSQLGenerator("test", opts),
153 updateTestCase{clause: ucRecord, sql: `UPDATE "test" set "a"='b',"b"='c'`},
154 updateTestCase{clause: ucRecord, sql: `UPDATE "test" set "a"=?,"b"=?`, isPrepared: true, args: []interface{}{"b", "c"}},
155
156 updateTestCase{clause: ucRecordNullVal, sql: `UPDATE "test" set "a"='b',"b"=NULL`},
157 updateTestCase{clause: ucRecordNullVal, sql: `UPDATE "test" set "a"=?,"b"=?`, isPrepared: true, args: []interface{}{"b", nil}},
158
159 updateTestCase{clause: ucRecordBoolVals, sql: `UPDATE "test" set "a"=TRUE,"b"=FALSE`},
160 updateTestCase{clause: ucRecordBoolVals, sql: `UPDATE "test" set "a"=?,"b"=?`, isPrepared: true, args: []interface{}{true, false}},
161
162 updateTestCase{clause: ucEmptyRecord, err: sqlgen.ErrNoUpdatedValuesProvided.Error()},
163 updateTestCase{clause: ucEmptyRecord, err: sqlgen.ErrNoUpdatedValuesProvided.Error(), isPrepared: true},
164 )
165 }
166
167 func (usgs *updateSQLGeneratorSuite) TestGenerate_withOrder() {
168 uc := exp.NewUpdateClauses().
169 SetTable(exp.NewIdentifierExpression("", "test", "")).
170 SetSetValues(exp.Record{"a": "b", "b": "c"}).
171 SetOrder(
172 exp.NewIdentifierExpression("", "", "a").Asc(),
173 exp.NewIdentifierExpression("", "", "b").Desc(),
174 )
175
176 opts := sqlgen.DefaultDialectOptions()
177 opts.SupportsOrderByOnUpdate = true
178
179 usgs.assertCases(
180 sqlgen.NewUpdateSQLGenerator("test", opts),
181 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"='b',"b"='c' ORDER BY "a" ASC, "b" DESC`},
182 updateTestCase{
183 clause: uc,
184 sql: `UPDATE "test" SET "a"=?,"b"=? ORDER BY "a" ASC, "b" DESC`,
185 isPrepared: true,
186 args: []interface{}{"b", "c"},
187 },
188 )
189
190 opts = sqlgen.DefaultDialectOptions()
191 opts.SupportsOrderByOnUpdate = false
192 usgs.assertCases(
193 sqlgen.NewUpdateSQLGenerator("test", opts),
194 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"='b',"b"='c'`},
195 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"=?,"b"=?`, isPrepared: true, args: []interface{}{"b", "c"}},
196 )
197 }
198
199 func (usgs *updateSQLGeneratorSuite) TestGenerate_withLimit() {
200 uc := exp.NewUpdateClauses().
201 SetTable(exp.NewIdentifierExpression("", "test", "")).
202 SetSetValues(exp.Record{"a": "b", "b": "c"}).
203 SetLimit(10)
204
205 opts := sqlgen.DefaultDialectOptions()
206 opts.SupportsLimitOnUpdate = true
207
208 usgs.assertCases(
209 sqlgen.NewUpdateSQLGenerator("test", opts),
210 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"='b',"b"='c' LIMIT 10`},
211 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"=?,"b"=? LIMIT ?`, isPrepared: true, args: []interface{}{"b", "c", int64(10)}},
212 )
213
214 opts = sqlgen.DefaultDialectOptions()
215 opts.SupportsLimitOnUpdate = false
216 usgs.assertCases(
217 sqlgen.NewUpdateSQLGenerator("test", opts),
218 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"='b',"b"='c'`},
219 updateTestCase{clause: uc, sql: `UPDATE "test" SET "a"=?,"b"=?`, isPrepared: true, args: []interface{}{"b", "c"}},
220 )
221 }
222
223 func (usgs *updateSQLGeneratorSuite) TestGenerate_withCommonTables() {
224 tse := newTestAppendableExpression("select * from foo", emptyArgs, nil, nil)
225 uc := exp.NewUpdateClauses().
226 SetTable(exp.NewIdentifierExpression("", "test_cte", "")).
227 SetSetValues(exp.Record{"a": "b", "b": "c"})
228 ucCte1 := uc.CommonTablesAppend(exp.NewCommonTableExpression(false, "test_cte", tse))
229 ucCte2 := uc.CommonTablesAppend(exp.NewCommonTableExpression(true, "test_cte", tse))
230
231 usgs.assertCases(
232 sqlgen.NewUpdateSQLGenerator("test", sqlgen.DefaultDialectOptions()),
233 updateTestCase{
234 clause: ucCte1,
235 sql: `WITH test_cte AS (select * from foo) UPDATE "test_cte" SET "a"='b',"b"='c'`,
236 },
237 updateTestCase{
238 clause: ucCte1,
239 sql: `WITH test_cte AS (select * from foo) UPDATE "test_cte" SET "a"=?,"b"=?`,
240 isPrepared: true,
241 args: []interface{}{"b", "c"},
242 },
243
244 updateTestCase{
245 clause: ucCte2,
246 sql: `WITH RECURSIVE test_cte AS (select * from foo) UPDATE "test_cte" SET "a"='b',"b"='c'`,
247 },
248 updateTestCase{
249 clause: ucCte2,
250 sql: `WITH RECURSIVE test_cte AS (select * from foo) UPDATE "test_cte" SET "a"=?,"b"=?`,
251 isPrepared: true,
252 args: []interface{}{"b", "c"},
253 },
254 )
255 }
256
257 func TestUpdateSQLGenerator(t *testing.T) {
258 suite.Run(t, new(updateSQLGeneratorSuite))
259 }
260
View as plain text