diff options
Diffstat (limited to '')
-rw-r--r-- | dependencies/pkg/mod/github.com/jmoiron/sqlx@v1.3.5/named_test.go | 435 |
1 files changed, 435 insertions, 0 deletions
diff --git a/dependencies/pkg/mod/github.com/jmoiron/sqlx@v1.3.5/named_test.go b/dependencies/pkg/mod/github.com/jmoiron/sqlx@v1.3.5/named_test.go new file mode 100644 index 0000000..8481b35 --- /dev/null +++ b/dependencies/pkg/mod/github.com/jmoiron/sqlx@v1.3.5/named_test.go @@ -0,0 +1,435 @@ +package sqlx + +import ( + "database/sql" + "fmt" + "testing" +) + +func TestCompileQuery(t *testing.T) { + table := []struct { + Q, R, D, T, N string + V []string + }{ + // basic test for named parameters, invalid char ',' terminating + { + Q: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`, + R: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`, + D: `INSERT INTO foo (a,b,c,d) VALUES ($1, $2, $3, $4)`, + T: `INSERT INTO foo (a,b,c,d) VALUES (@p1, @p2, @p3, @p4)`, + N: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`, + V: []string{"name", "age", "first", "last"}, + }, + // This query tests a named parameter ending the string as well as numbers + { + Q: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`, + R: `SELECT * FROM a WHERE first_name=? AND last_name=?`, + D: `SELECT * FROM a WHERE first_name=$1 AND last_name=$2`, + T: `SELECT * FROM a WHERE first_name=@p1 AND last_name=@p2`, + N: `SELECT * FROM a WHERE first_name=:name1 AND last_name=:name2`, + V: []string{"name1", "name2"}, + }, + { + Q: `SELECT "::foo" FROM a WHERE first_name=:name1 AND last_name=:name2`, + R: `SELECT ":foo" FROM a WHERE first_name=? AND last_name=?`, + D: `SELECT ":foo" FROM a WHERE first_name=$1 AND last_name=$2`, + T: `SELECT ":foo" FROM a WHERE first_name=@p1 AND last_name=@p2`, + N: `SELECT ":foo" FROM a WHERE first_name=:name1 AND last_name=:name2`, + V: []string{"name1", "name2"}, + }, + { + Q: `SELECT 'a::b::c' || first_name, '::::ABC::_::' FROM person WHERE first_name=:first_name AND last_name=:last_name`, + R: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=? AND last_name=?`, + D: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=$1 AND last_name=$2`, + T: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=@p1 AND last_name=@p2`, + N: `SELECT 'a:b:c' || first_name, '::ABC:_:' FROM person WHERE first_name=:first_name AND last_name=:last_name`, + V: []string{"first_name", "last_name"}, + }, + { + Q: `SELECT @name := "name", :age, :first, :last`, + R: `SELECT @name := "name", ?, ?, ?`, + D: `SELECT @name := "name", $1, $2, $3`, + N: `SELECT @name := "name", :age, :first, :last`, + T: `SELECT @name := "name", @p1, @p2, @p3`, + V: []string{"age", "first", "last"}, + }, + /* This unicode awareness test sadly fails, because of our byte-wise worldview. + * We could certainly iterate by Rune instead, though it's a great deal slower, + * it's probably the RightWay(tm) + { + Q: `INSERT INTO foo (a,b,c,d) VALUES (:あ, :b, :キコ, :名前)`, + R: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`, + D: `INSERT INTO foo (a,b,c,d) VALUES ($1, $2, $3, $4)`, + N: []string{"name", "age", "first", "last"}, + }, + */ + } + + for _, test := range table { + qr, names, err := compileNamedQuery([]byte(test.Q), QUESTION) + if err != nil { + t.Error(err) + } + if qr != test.R { + t.Errorf("expected %s, got %s", test.R, qr) + } + if len(names) != len(test.V) { + t.Errorf("expected %#v, got %#v", test.V, names) + } else { + for i, name := range names { + if name != test.V[i] { + t.Errorf("expected %dth name to be %s, got %s", i+1, test.V[i], name) + } + } + } + qd, _, _ := compileNamedQuery([]byte(test.Q), DOLLAR) + if qd != test.D { + t.Errorf("\nexpected: `%s`\ngot: `%s`", test.D, qd) + } + + qt, _, _ := compileNamedQuery([]byte(test.Q), AT) + if qt != test.T { + t.Errorf("\nexpected: `%s`\ngot: `%s`", test.T, qt) + } + + qq, _, _ := compileNamedQuery([]byte(test.Q), NAMED) + if qq != test.N { + t.Errorf("\nexpected: `%s`\ngot: `%s`\n(len: %d vs %d)", test.N, qq, len(test.N), len(qq)) + } + } +} + +type Test struct { + t *testing.T +} + +func (t Test) Error(err error, msg ...interface{}) { + t.t.Helper() + if err != nil { + if len(msg) == 0 { + t.t.Error(err) + } else { + t.t.Error(msg...) + } + } +} + +func (t Test) Errorf(err error, format string, args ...interface{}) { + t.t.Helper() + if err != nil { + t.t.Errorf(format, args...) + } +} + +func TestEscapedColons(t *testing.T) { + t.Skip("not sure it is possible to support this in general case without an SQL parser") + var qs = `SELECT * FROM testtable WHERE timeposted BETWEEN (now() AT TIME ZONE 'utc') AND + (now() AT TIME ZONE 'utc') - interval '01:30:00') AND name = '\'this is a test\'' and id = :id` + _, _, err := compileNamedQuery([]byte(qs), DOLLAR) + if err != nil { + t.Error("Didn't handle colons correctly when inside a string") + } +} + +func TestNamedQueries(t *testing.T) { + RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T, now string) { + loadDefaultFixture(db, t) + test := Test{t} + var ns *NamedStmt + var err error + + // Check that invalid preparations fail + ns, err = db.PrepareNamed("SELECT * FROM person WHERE first_name=:first:name") + if err == nil { + t.Error("Expected an error with invalid prepared statement.") + } + + ns, err = db.PrepareNamed("invalid sql") + if err == nil { + t.Error("Expected an error with invalid prepared statement.") + } + + // Check closing works as anticipated + ns, err = db.PrepareNamed("SELECT * FROM person WHERE first_name=:first_name") + test.Error(err) + err = ns.Close() + test.Error(err) + + ns, err = db.PrepareNamed(` + SELECT first_name, last_name, email + FROM person WHERE first_name=:first_name AND email=:email`) + test.Error(err) + + // test Queryx w/ uses Query + p := Person{FirstName: "Jason", LastName: "Moiron", Email: "jmoiron@jmoiron.net"} + + rows, err := ns.Queryx(p) + test.Error(err) + for rows.Next() { + var p2 Person + rows.StructScan(&p2) + if p.FirstName != p2.FirstName { + t.Errorf("got %s, expected %s", p.FirstName, p2.FirstName) + } + if p.LastName != p2.LastName { + t.Errorf("got %s, expected %s", p.LastName, p2.LastName) + } + if p.Email != p2.Email { + t.Errorf("got %s, expected %s", p.Email, p2.Email) + } + } + + // test Select + people := make([]Person, 0, 5) + err = ns.Select(&people, p) + test.Error(err) + + if len(people) != 1 { + t.Errorf("got %d results, expected %d", len(people), 1) + } + if p.FirstName != people[0].FirstName { + t.Errorf("got %s, expected %s", p.FirstName, people[0].FirstName) + } + if p.LastName != people[0].LastName { + t.Errorf("got %s, expected %s", p.LastName, people[0].LastName) + } + if p.Email != people[0].Email { + t.Errorf("got %s, expected %s", p.Email, people[0].Email) + } + + // test struct batch inserts + sls := []Person{ + {FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"}, + {FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"}, + {FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"}, + } + + insert := fmt.Sprintf( + "INSERT INTO person (first_name, last_name, email, added_at) VALUES (:first_name, :last_name, :email, %v)\n", + now, + ) + _, err = db.NamedExec(insert, sls) + test.Error(err) + + // test map batch inserts + slsMap := []map[string]interface{}{ + {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"}, + {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"}, + {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"}, + } + + _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) + VALUES (:first_name, :last_name, :email) ;--`, slsMap) + test.Error(err) + + type A map[string]interface{} + + typedMap := []A{ + {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"}, + {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"}, + {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"}, + } + + _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email) + VALUES (:first_name, :last_name, :email) ;--`, typedMap) + test.Error(err) + + for _, p := range sls { + dest := Person{} + err = db.Get(&dest, db.Rebind("SELECT * FROM person WHERE email=?"), p.Email) + test.Error(err) + if dest.Email != p.Email { + t.Errorf("expected %s, got %s", p.Email, dest.Email) + } + } + + // test Exec + ns, err = db.PrepareNamed(` + INSERT INTO person (first_name, last_name, email) + VALUES (:first_name, :last_name, :email)`) + test.Error(err) + + js := Person{ + FirstName: "Julien", + LastName: "Savea", + Email: "jsavea@ab.co.nz", + } + _, err = ns.Exec(js) + test.Error(err) + + // Make sure we can pull him out again + p2 := Person{} + db.Get(&p2, db.Rebind("SELECT * FROM person WHERE email=?"), js.Email) + if p2.Email != js.Email { + t.Errorf("expected %s, got %s", js.Email, p2.Email) + } + + // test Txn NamedStmts + tx := db.MustBegin() + txns := tx.NamedStmt(ns) + + // We're going to add Steven in this txn + sl := Person{ + FirstName: "Steven", + LastName: "Luatua", + Email: "sluatua@ab.co.nz", + } + + _, err = txns.Exec(sl) + test.Error(err) + // then rollback... + tx.Rollback() + // looking for Steven after a rollback should fail + err = db.Get(&p2, db.Rebind("SELECT * FROM person WHERE email=?"), sl.Email) + if err != sql.ErrNoRows { + t.Errorf("expected no rows error, got %v", err) + } + + // now do the same, but commit + tx = db.MustBegin() + txns = tx.NamedStmt(ns) + _, err = txns.Exec(sl) + test.Error(err) + tx.Commit() + + // looking for Steven after a Commit should succeed + err = db.Get(&p2, db.Rebind("SELECT * FROM person WHERE email=?"), sl.Email) + test.Error(err) + if p2.Email != sl.Email { + t.Errorf("expected %s, got %s", sl.Email, p2.Email) + } + + }) +} + +func TestFixBounds(t *testing.T) { + table := []struct { + name, query, expect string + loop int + }{ + { + name: `named syntax`, + query: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last)`, + expect: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last),(:name, :age, :first, :last)`, + loop: 2, + }, + { + name: `mysql syntax`, + query: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?)`, + expect: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?),(?, ?, ?, ?)`, + loop: 2, + }, + { + name: `named syntax w/ trailer`, + query: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last) ;--`, + expect: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last),(:name, :age, :first, :last) ;--`, + loop: 2, + }, + { + name: `mysql syntax w/ trailer`, + query: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?) ;--`, + expect: `INSERT INTO foo (a,b,c,d) VALUES (?, ?, ?, ?),(?, ?, ?, ?) ;--`, + loop: 2, + }, + { + name: `not found test`, + query: `INSERT INTO foo (a,b,c,d) (:name, :age, :first, :last)`, + expect: `INSERT INTO foo (a,b,c,d) (:name, :age, :first, :last)`, + loop: 2, + }, + { + name: `found twice test`, + query: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last) VALUES (:name, :age, :first, :last)`, + expect: `INSERT INTO foo (a,b,c,d) VALUES (:name, :age, :first, :last),(:name, :age, :first, :last) VALUES (:name, :age, :first, :last)`, + loop: 2, + }, + { + name: `nospace`, + query: `INSERT INTO foo (a,b) VALUES(:a, :b)`, + expect: `INSERT INTO foo (a,b) VALUES(:a, :b),(:a, :b)`, + loop: 2, + }, + { + name: `lowercase`, + query: `INSERT INTO foo (a,b) values(:a, :b)`, + expect: `INSERT INTO foo (a,b) values(:a, :b),(:a, :b)`, + loop: 2, + }, + { + name: `on duplicate key using VALUES`, + query: `INSERT INTO foo (a,b) VALUES (:a, :b) ON DUPLICATE KEY UPDATE a=VALUES(a)`, + expect: `INSERT INTO foo (a,b) VALUES (:a, :b),(:a, :b) ON DUPLICATE KEY UPDATE a=VALUES(a)`, + loop: 2, + }, + { + name: `single column`, + query: `INSERT INTO foo (a) VALUES (:a)`, + expect: `INSERT INTO foo (a) VALUES (:a),(:a)`, + loop: 2, + }, + { + name: `call now`, + query: `INSERT INTO foo (a, b) VALUES (:a, NOW())`, + expect: `INSERT INTO foo (a, b) VALUES (:a, NOW()),(:a, NOW())`, + loop: 2, + }, + { + name: `two level depth function call`, + query: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW()))`, + expect: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())),(:a, YEAR(NOW()))`, + loop: 2, + }, + { + name: `missing closing bracket`, + query: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())`, + expect: `INSERT INTO foo (a, b) VALUES (:a, YEAR(NOW())`, + loop: 2, + }, + { + name: `table with "values" at the end`, + query: `INSERT INTO table_values (a, b) VALUES (:a, :b)`, + expect: `INSERT INTO table_values (a, b) VALUES (:a, :b),(:a, :b)`, + loop: 2, + }, + { + name: `multiline indented query`, + query: `INSERT INTO foo ( + a, + b, + c, + d + ) VALUES ( + :name, + :age, + :first, + :last + )`, + expect: `INSERT INTO foo ( + a, + b, + c, + d + ) VALUES ( + :name, + :age, + :first, + :last + ),( + :name, + :age, + :first, + :last + )`, + loop: 2, + }, + } + + for _, tc := range table { + t.Run(tc.name, func(t *testing.T) { + res := fixBound(tc.query, tc.loop) + if res != tc.expect { + t.Errorf("mismatched results") + } + }) + } +} |