Skip to content

Commit 92bfa36

Browse files
jmoironjmoiron
authored
Merge pull request jmoiron#734 from QuangTung97/fix/named-exec-values
Fix Bulk Insert: Single Column and MySQL earlier upsert syntax
2 parents e6cd7ae + 4b6b69e commit 92bfa36

File tree

2 files changed

+100
-28
lines changed

2 files changed

+100
-28
lines changed

‎named.go‎

Lines changed: 30 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -224,29 +224,47 @@ func bindStruct(bindType int, query string, arg interface{}, m *reflectx.Mapper)
224224
return bound, arglist, nil
225225
}
226226

227-
var valueBracketReg = regexp.MustCompile(`(?i)VALUES\s*(\([^(]*.[^(]\))`)
227+
var valuesReg = regexp.MustCompile(`\)\s*(?i)VALUES\s*\(`)
228228

229-
func fixBound(bound string, loop int) string {
229+
func findMatchingClosingBracketIndex(s string) int {
230+
count := 0
231+
for i, ch := range s {
232+
if ch == '(' {
233+
count++
234+
}
235+
if ch == ')' {
236+
count--
237+
if count == 0 {
238+
return i
239+
}
240+
}
241+
}
242+
return 0
243+
}
230244

231-
loc := valueBracketReg.FindAllStringSubmatchIndex(bound, -1)
232-
// Either no VALUES () found or more than one found??
233-
if len(loc) != 1 {
245+
func fixBound(bound string, loop int) string {
246+
loc := valuesReg.FindStringIndex(bound)
247+
// defensive guard when "VALUES (...)" not found
248+
if len(loc) < 2 {
234249
return bound
235250
}
236-
// defensive guard. loc should be len 4 representing the starting and
237-
// ending index for the whole regex match and the starting + ending
238-
// index for the single inside group
239-
if len(loc[0]) != 4 {
251+
252+
openingBracketIndex := loc[1] - 1
253+
index := findMatchingClosingBracketIndex(bound[openingBracketIndex:])
254+
// defensive guard. must have closing bracket
255+
if index == 0 {
240256
return bound
241257
}
258+
closingBracketIndex := openingBracketIndex + index + 1
259+
242260
var buffer bytes.Buffer
243261

244-
buffer.WriteString(bound[0:loc[0][1]])
262+
buffer.WriteString(bound[0:closingBracketIndex])
245263
for i := 0; i < loop-1; i++ {
246264
buffer.WriteString(",")
247-
buffer.WriteString(bound[loc[0][2]:loc[0][3]])
265+
buffer.WriteString(bound[openingBracketIndex:closingBracketIndex])
248266
}
249-
buffer.WriteString(bound[loc[0][1]:])
267+
buffer.WriteString(bound[closingBracketIndex:])
250268
return buffer.String()
251269
}
252270

‎named_test.go‎

Lines changed: 70 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,6 @@ package sqlx
33
import (
44
"database/sql"
55
"fmt"
6-
"regexp"
76
"testing"
87
)
98

@@ -105,6 +104,7 @@ type Test struct {
105104
}
106105

107106
func (t Test) Error(err error, msg ...interface{}) {
107+
t.t.Helper()
108108
if err != nil {
109109
if len(msg) == 0 {
110110
t.t.Error(err)
@@ -115,6 +115,7 @@ func (t Test) Error(err error, msg ...interface{}) {
115115
}
116116

117117
func (t Test) Errorf(err error, format string, args ...interface{}) {
118+
t.t.Helper()
118119
if err != nil {
119120
t.t.Errorf(format, args...)
120121
}
@@ -339,7 +340,7 @@ func TestFixBounds(t *testing.T) {
339340
{
340341
name: `found twice test`,
341342
query: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last) VALUES (:name, :age, :first, :last)`,
342-
expect: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last) VALUES (:name, :age, :first, :last)`,
343+
expect: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last),(:name, :age, :first, :last) VALUES (:name, :age, :first, :last)`,
343344
loop: 2,
344345
},
345346
{
@@ -354,6 +355,73 @@ func TestFixBounds(t *testing.T) {
354355
expect: `INSERT INTO foo (a,b) values(:a, :b),(:a, :b)`,
355356
loop: 2,
356357
},
358+
{
359+
name: `on duplicate key using VALUES`,
360+
query: `INSERT INTO foo (a,b) VALUES (:a, :b) ON DUPLICATE KEY UPDATE a=VALUES(a)`,
361+
expect: `INSERT INTO foo (a,b) VALUES (:a, :b),(:a, :b) ON DUPLICATE KEY UPDATE a=VALUES(a)`,
362+
loop: 2,
363+
},
364+
{
365+
name: `single column`,
366+
query: `INSERT INTO foo (a) VALUES (:a)`,
367+
expect: `INSERT INTO foo (a) VALUES (:a),(:a)`,
368+
loop: 2,
369+
},
370+
{
371+
name: `call now`,
372+
query: `INSERT INTO foo (a, b) VALUES (:a, NOW())`,
373+
expect: `INSERT INTO foo (a, b) VALUES (:a, NOW()),(:a, NOW())`,
374+
loop: 2,
375+
},
376+
{
377+
name: `two level depth function call`,
378+
query: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW()))`,
379+
expect: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())),(:a, YEAR(NOW()))`,
380+
loop: 2,
381+
},
382+
{
383+
name: `missing closing bracket`,
384+
query: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())`,
385+
expect: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())`,
386+
loop: 2,
387+
},
388+
{
389+
name: `table with "values" at the end`,
390+
query: `INSERT INTO table_values (a, b) VALUES (:a, :b)`,
391+
expect: `INSERT INTO table_values (a, b) VALUES (:a, :b),(:a, :b)`,
392+
loop: 2,
393+
},
394+
{
395+
name: `multiline indented query`,
396+
query: `INSERT INTO foo (
397+
a,
398+
b,
399+
c,
400+
d
401+
) VALUES (
402+
:name,
403+
:age,
404+
:first,
405+
:last
406+
)`,
407+
expect: `INSERT INTO foo (
408+
a,
409+
b,
410+
c,
411+
d
412+
) VALUES (
413+
:name,
414+
:age,
415+
:first,
416+
:last
417+
),(
418+
:name,
419+
:age,
420+
:first,
421+
:last
422+
)`,
423+
loop: 2,
424+
},
357425
}
358426

359427
for _, tc := range table {
@@ -364,18 +432,4 @@ func TestFixBounds(t *testing.T) {
364432
}
365433
})
366434
}
367-
368-
t.Run("regex changed", func(t *testing.T) {
369-
var valueBracketRegChanged = regexp.MustCompile(`(VALUES)\s+(\([^(]*.[^(]\))`)
370-
saveRegexp := valueBracketReg
371-
defer func() {
372-
valueBracketReg = saveRegexp
373-
}()
374-
valueBracketReg = valueBracketRegChanged
375-
376-
res := fixBound("VALUES (:a, :b)", 2)
377-
if res != "VALUES (:a, :b)" {
378-
t.Errorf("changed regex should return string")
379-
}
380-
})
381435
}

0 commit comments

Comments
 (0)