// mapper returns a valid mapper using the configured NameMapper func.
func mapper() *reflectx.Mapper {
if mpr == nil {
mpr = reflectx.NewMapperFunc("db", NameMapper)
} else if origMapper != reflect.ValueOf(NameMapper) {
// if NameMapper has changed, create a new mapper
mpr = reflectx.NewMapperFunc("db", NameMapper)
origMapper = reflect.ValueOf(NameMapper)
}
return mpr
}
func TestNamedQuery(t *testing.T) {
var schema = Schema{
create: `
CREATE TABLE person (
first_name text NULL,
last_name text NULL,
email text NULL
);
CREATE TABLE jsperson (
"FIRST" text NULL,
last_name text NULL,
"EMAIL" text NULL
);`,
drop: `
drop table person;
drop table jsperson;
`,
}
RunWithSchema(schema, t, func(db *DB, t *testing.T) {
type Person struct {
FirstName sql.NullString `db:"first_name"`
LastName sql.NullString `db:"last_name"`
Email sql.NullString
}
p := Person{
FirstName: sql.NullString{String: "ben", Valid: true},
LastName: sql.NullString{String: "doe", Valid: true},
Email: sql.NullString{String: "*****@*****.**", Valid: true},
}
q1 := `INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)`
_, err := db.NamedExec(q1, p)
if err != nil {
log.Fatal(err)
}
p2 := &Person{}
rows, err := db.NamedQuery("SELECT * FROM person WHERE first_name=:first_name", p)
if err != nil {
log.Fatal(err)
}
for rows.Next() {
err = rows.StructScan(p2)
if err != nil {
t.Error(err)
}
if p2.FirstName.String != "ben" {
t.Error("Expected first name of `ben`, got " + p2.FirstName.String)
}
if p2.LastName.String != "doe" {
t.Error("Expected first name of `doe`, got " + p2.LastName.String)
}
}
// these are tests for #73; they verify that named queries work if you've
// changed the db mapper. This code checks both NamedQuery "ad-hoc" style
// queries and NamedStmt queries, which use different code paths internally.
old := *db.Mapper
type JSONPerson struct {
FirstName sql.NullString `json:"FIRST"`
LastName sql.NullString `json:"last_name"`
Email sql.NullString
}
jp := JSONPerson{
FirstName: sql.NullString{String: "ben", Valid: true},
LastName: sql.NullString{String: "smith", Valid: true},
Email: sql.NullString{String: "*****@*****.**", Valid: true},
}
db.Mapper = reflectx.NewMapperFunc("json", strings.ToUpper)
// prepare queries for case sensitivity to test our ToUpper function.
// postgres and sqlite accept "", but mysql uses ``; since Go's multi-line
// strings are `` we use "" by default and swap out for MySQL
pdb := func(s string, db *DB) string {
if db.DriverName() == "mysql" {
return strings.Replace(s, `"`, "`", -1)
}
return s
}
q1 = `INSERT INTO jsperson ("FIRST", last_name, "EMAIL") VALUES (:FIRST, :last_name, :EMAIL)`
_, err = db.NamedExec(pdb(q1, db), jp)
if err != nil {
t.Fatal(err, db.DriverName())
}
// Checks that a person pulled out of the db matches the one we put in
check := func(t *testing.T, rows *Rows) {
jp = JSONPerson{}
for rows.Next() {
err = rows.StructScan(&jp)
if err != nil {
t.Error(err)
}
if jp.FirstName.String != "ben" {
t.Errorf("Expected first name of `ben`, got `%s` (%s) ", jp.FirstName.String, db.DriverName())
}
if jp.LastName.String != "smith" {
t.Errorf("Expected LastName of `smith`, got `%s` (%s)", jp.LastName.String, db.DriverName())
}
if jp.Email.String != "*****@*****.**" {
t.Errorf("Expected first name of `doe`, got `%s` (%s)", jp.Email.String, db.DriverName())
}
}
}
ns, err := db.PrepareNamed(pdb(`
SELECT * FROM jsperson
WHERE
"FIRST"=:FIRST AND
last_name=:last_name AND
"EMAIL"=:EMAIL
`, db))
if err != nil {
t.Fatal(err)
}
rows, err = ns.Queryx(jp)
if err != nil {
t.Fatal(err)
}
check(t, rows)
// Check exactly the same thing, but with db.NamedQuery, which does not go
// through the PrepareNamed/NamedStmt path.
rows, err = db.NamedQuery(pdb(`
SELECT * FROM jsperson
WHERE
"FIRST"=:FIRST AND
last_name=:last_name AND
"EMAIL"=:EMAIL
`, db), jp)
if err != nil {
t.Fatal(err)
}
check(t, rows)
db.Mapper = &old
})
}
// MapperFunc sets a new mapper for this db using the default sqlx struct tag
// and the provided mapper function.
func (db *DB) MapperFunc(mf func(string) string) {
db.Mapper = reflectx.NewMapperFunc("db", mf)
}