// The following environment variables, if set, will be used:

//

// * SQLX_SQLITE_DSN

// * SQLX_POSTGRES_DSN

// * SQLX_MYSQL_DSN

//

// Set any of these variables to 'skip' to skip them. Note that for MySQL,

// the string '?parseTime=True' will be appended to the DSN if it's not there

// already.

//

package sqlx

import (

"database/sql"

"database/sql/driver"

"encoding/json"

"fmt"

"log"

"os"

"reflect"

"strings"

"testing"

"time"

_ "github.com/go-sql-driver/mysql"

"github.com/jmoiron/sqlx/reflectx"

_ "github.com/lib/pq"

_ "github.com/mattn/go-sqlite3"

)

/* compile time checks that Db, Tx, Stmt (qStmt) implement expected interfaces */

var _, _ Ext = &DB{}, &Tx{}

var _, _ ColScanner = &Row{}, &Rows{}

var _ Queryer = &qStmt{}

var _ Execer = &qStmt{}

var TestPostgres = true

var TestSqlite = true

var TestMysql = true

var sldb *DB

var pgdb *DB

var mysqldb *DB

var active = []*DB{}

func init() {

ConnectAll()

}

func ConnectAll() {

var err error

pgdsn := os.Getenv("SQLX_POSTGRES_DSN")

mydsn := os.Getenv("SQLX_MYSQL_DSN")

sqdsn := os.Getenv("SQLX_SQLITE_DSN")

TestPostgres = pgdsn != "skip"

TestMysql = mydsn != "skip"

TestSqlite = sqdsn != "skip"

if !strings.Contains(mydsn, "parseTime=true") {

mydsn += "?parseTime=true"

}

if TestPostgres {

pgdb, err = Connect("postgres", pgdsn)

if err != nil {

fmt.Printf("Disabling PG tests:\n %v\n", err)

TestPostgres = false

}

} else {

fmt.Println("Disabling Postgres tests.")

}

if TestMysql {

mysqldb, err = Connect("mysql", mydsn)

if err != nil {

fmt.Printf("Disabling MySQL tests:\n %v", err)

TestMysql = false

}

} else {

fmt.Println("Disabling MySQL tests.")

}

if TestSqlite {

sldb, err = Connect("sqlite3", sqdsn)

if err != nil {

fmt.Printf("Disabling SQLite:\n %v", err)

TestSqlite = false

}

} else {

fmt.Println("Disabling SQLite tests.")

}

}

type Schema struct {

create string

drop string

}

func (s Schema) Postgres() (string, string) {

return s.create, s.drop

}

func (s Schema) MySQL() (string, string) {

return strings.Replace(s.create, `"`, "`", -1), s.drop

}

func (s Schema) Sqlite3() (string, string) {

return strings.Replace(s.create, `now()`, `CURRENT_TIMESTAMP`, -1), s.drop

}

var defaultSchema = Schema{

create: `

CREATE TABLE person (

first_name text,

last_name text,

email text,

added_at timestamp default now()

);

CREATE TABLE place (

country text,

city text NULL,

telcode integer

);

CREATE TABLE capplace (

"COUNTRY" text,

"CITY" text NULL,

"TELCODE" integer

);

CREATE TABLE nullperson (

first_name text NULL,

last_name text NULL,

email text NULL

);

CREATE TABLE employees (

name text,

id integer,

boss_id integer

);

`,

drop: `

drop table person;

drop table place;

drop table capplace;

drop table nullperson;

drop table employees;

`,

}

type Person struct {

FirstName string `db:"first_name"`

LastName string `db:"last_name"`

Email string

AddedAt time.Time `db:"added_at"`

}

type Person2 struct {

FirstName sql.NullString `db:"first_name"`

LastName sql.NullString `db:"last_name"`

Email sql.NullString

}

type Place struct {

Country string

City sql.NullString

TelCode int

}

type PlacePtr struct {

Country string

City *string

TelCode int

}

type PersonPlace struct {

Person

Place

}

type PersonPlacePtr struct {

*Person

*Place

}

type EmbedConflict struct {

FirstName string `db:"first_name"`

Person

}

type SliceMember struct {

Country string

City sql.NullString

TelCode int

People []Person `db:"-"`

Addresses []Place `db:"-"`

}

// Note that because of field map caching, we need a new type here

// if we've used Place already somewhere in sqlx

type CPlace Place

func MultiExec(e Execer, query string) {

stmts := strings.Split(query, ";\n")

if len(strings.Trim(stmts[len(stmts)-1], " \n\t\r")) == 0 {

stmts = stmts[:len(stmts)-1]

}

for _, s := range stmts {

_, err := e.Exec(s)

if err != nil {

fmt.Println(err, s)

}

}

}

func RunWithSchema(schema Schema, t *testing.T, test func(db *DB, t *testing.T)) {

runner := func(db *DB, t *testing.T, create, drop string) {

defer func() {

MultiExec(db, drop)

}()

MultiExec(db, create)

test(db, t)

}

if TestPostgres {

create, drop := schema.Postgres()

runner(pgdb, t, create, drop)

}

if TestSqlite {

create, drop := schema.Sqlite3()

runner(sldb, t, create, drop)

}

if TestMysql {

create, drop := schema.MySQL()

runner(mysqldb, t, create, drop)

}

}

func loadDefaultFixture(db *DB, t *testing.T) {

tx := db.MustBegin()

tx.MustExec(tx.Rebind("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)"), "Jason", "Moiron", "[email protected]")

tx.MustExec(tx.Rebind("INSERT INTO person (first_name, last_name, email) VALUES (?, ?, ?)"), "John", "Doe", "[email protected]")

tx.MustExec(tx.Rebind("INSERT INTO place (country, city, telcode) VALUES (?, ?, ?)"), "United States", "New York", "1")

tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Hong Kong", "852")

tx.MustExec(tx.Rebind("INSERT INTO place (country, telcode) VALUES (?, ?)"), "Singapore", "65")

if db.DriverName() == "mysql" {

tx.MustExec(tx.Rebind("INSERT INTO capplace (`COUNTRY`, `TELCODE`) VALUES (?, ?)"), "Sarf Efrica", "27")

} else {

tx.MustExec(tx.Rebind("INSERT INTO capplace (\"COUNTRY\", \"TELCODE\") VALUES (?, ?)"), "Sarf Efrica", "27")

}

tx.MustExec(tx.Rebind("INSERT INTO employees (name, id) VALUES (?, ?)"), "Peter", "4444")

tx.MustExec(tx.Rebind("INSERT INTO employees (name, id, boss_id) VALUES (?, ?, ?)"), "Joe", "1", "4444")

tx.MustExec(tx.Rebind("INSERT INTO employees (name, id, boss_id) VALUES (?, ?, ?)"), "Martin", "2", "4444")

tx.Commit()

}

// Test a new backwards compatible feature, that missing scan destinations

// will silently scan into sql.RawText rather than failing/panicing

func TestMissingNames(t *testing.T) {

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

type PersonPlus struct {

FirstName string `db:"first_name"`

LastName string `db:"last_name"`

Email string

//AddedAt time.Time `db:"added_at"`

}

// test Select first

pps := []PersonPlus{}

// pps lacks added_at destination

err := db.Select(&pps, "SELECT * FROM person")

if err == nil {

t.Error("Expected missing name from Select to fail, but it did not.")

}

// test Get

pp := PersonPlus{}

err = db.Get(&pp, "SELECT * FROM person LIMIT 1")

if err == nil {

t.Error("Expected missing name Get to fail, but it did not.")

}

// test naked StructScan

pps = []PersonPlus{}

rows, err := db.Query("SELECT * FROM person LIMIT 1")

if err != nil {

t.Fatal(err)

}

rows.Next()

err = StructScan(rows, &pps)

if err == nil {

t.Error("Expected missing name in StructScan to fail, but it did not.")

}

rows.Close()

// now try various things with unsafe set.

db = db.Unsafe()

pps = []PersonPlus{}

err = db.Select(&pps, "SELECT * FROM person")

if err != nil {

t.Error(err)

}

// test Get

pp = PersonPlus{}

err = db.Get(&pp, "SELECT * FROM person LIMIT 1")

if err != nil {

t.Error(err)

}

// test naked StructScan

pps = []PersonPlus{}

rowsx, err := db.Queryx("SELECT * FROM person LIMIT 1")

if err != nil {

t.Fatal(err)

}

rowsx.Next()

err = StructScan(rowsx, &pps)

if err != nil {

t.Error(err)

}

rowsx.Close()

// test Named stmt

if !isUnsafe(db) {

t.Error("Expected db to be unsafe, but it isn't")

}

nstmt, err := db.PrepareNamed(`SELECT * FROM person WHERE first_name != :name`)

if err != nil {

t.Fatal(err)

}

// its internal stmt should be marked unsafe

if !nstmt.Stmt.unsafe {

t.Error("expected NamedStmt to be unsafe but its underlying stmt did not inherit safety")

}

pps = []PersonPlus{}

err = nstmt.Select(&pps, map[string]interface{}{"name": "Jason"})

if err != nil {

t.Fatal(err)

}

if len(pps) != 1 {

t.Errorf("Expected 1 person back, got %d", len(pps))

}

// test it with a safe db

db.unsafe = false

if isUnsafe(db) {

t.Error("expected db to be safe but it isn't")

}

nstmt, err = db.PrepareNamed(`SELECT * FROM person WHERE first_name != :name`)

if err != nil {

t.Fatal(err)

}

// it should be safe

if isUnsafe(nstmt) {

t.Error("NamedStmt did not inherit safety")

}

nstmt.Unsafe()

if !isUnsafe(nstmt) {

t.Error("expected newly unsafed NamedStmt to be unsafe")

}

pps = []PersonPlus{}

err = nstmt.Select(&pps, map[string]interface{}{"name": "Jason"})

if err != nil {

t.Fatal(err)

}

if len(pps) != 1 {

t.Errorf("Expected 1 person back, got %d", len(pps))

}

})

}

func TestEmbeddedStructs(t *testing.T) {

type Loop1 struct{ Person }

type Loop2 struct{ Loop1 }

type Loop3 struct{ Loop2 }

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

peopleAndPlaces := []PersonPlace{}

err := db.Select(

&peopleAndPlaces,

`SELECT person.*, place.* FROM

person natural join place`)

if err != nil {

t.Fatal(err)

}

for _, pp := range peopleAndPlaces {

if len(pp.Person.FirstName) == 0 {

t.Errorf("Expected non zero lengthed first name.")

}

if len(pp.Place.Country) == 0 {

t.Errorf("Expected non zero lengthed country.")

}

}

// test embedded structs with StructScan

rows, err := db.Queryx(

`SELECT person.*, place.* FROM

person natural join place`)

if err != nil {

t.Error(err)

}

perp := PersonPlace{}

rows.Next()

err = rows.StructScan(&perp)

if err != nil {

t.Error(err)

}

if len(perp.Person.FirstName) == 0 {

t.Errorf("Expected non zero lengthed first name.")

}

if len(perp.Place.Country) == 0 {

t.Errorf("Expected non zero lengthed country.")

}

rows.Close()

// test the same for embedded pointer structs

peopleAndPlacesPtrs := []PersonPlacePtr{}

err = db.Select(

&peopleAndPlacesPtrs,

`SELECT person.*, place.* FROM

person natural join place`)

if err != nil {

t.Fatal(err)

}

for _, pp := range peopleAndPlacesPtrs {

if len(pp.Person.FirstName) == 0 {

t.Errorf("Expected non zero lengthed first name.")

}

if len(pp.Place.Country) == 0 {

t.Errorf("Expected non zero lengthed country.")

}

}

// test "deep nesting"

l3s := []Loop3{}

err = db.Select(&l3s, `select * from person`)

if err != nil {

t.Fatal(err)

}

for _, l3 := range l3s {

if len(l3.Loop2.Loop1.Person.FirstName) == 0 {

t.Errorf("Expected non zero lengthed first name.")

}

}

// test "embed conflicts"

ec := []EmbedConflict{}

err = db.Select(&ec, `select * from person`)

// I'm torn between erroring here or having some kind of working behavior

// in order to allow for more flexibility in destination structs

if err != nil {

t.Errorf("Was not expecting an error on embed conflicts.")

}

})

}

func TestJoinQuery(t *testing.T) {

type Employee struct {

Name string

ID int64

// BossID is an id into the employee table

BossID sql.NullInt64 `db:"boss_id"`

}

type Boss Employee

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

var employees []struct {

Employee

Boss `db:"boss"`

}

err := db.Select(

&employees,

`SELECT employees.*, boss.id "boss.id", boss.name "boss.name" FROM employees

JOIN employees AS boss ON employees.boss_id = boss.id`)

if err != nil {

t.Fatal(err)

}

for _, em := range employees {

if len(em.Employee.Name) == 0 {

t.Errorf("Expected non zero lengthed name.")

}

if em.Employee.BossID.Int64 != em.Boss.ID {

t.Errorf("Expected boss ids to match")

}

}

})

}

func TestJoinQueryNamedPointerStructs(t *testing.T) {

type Employee struct {

Name string

ID int64

// BossID is an id into the employee table

BossID sql.NullInt64 `db:"boss_id"`

}

type Boss Employee

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

var employees []struct {

Emp1 *Employee `db:"emp1"`

Emp2 *Employee `db:"emp2"`

*Boss `db:"boss"`

}

err := db.Select(

&employees,

`SELECT emp.name "emp1.name", emp.id "emp1.id", emp.boss_id "emp1.boss_id",

emp.name "emp2.name", emp.id "emp2.id", emp.boss_id "emp2.boss_id",

boss.id "boss.id", boss.name "boss.name" FROM employees AS emp

JOIN employees AS boss ON emp.boss_id = boss.id

`)

if err != nil {

t.Fatal(err)

}

for _, em := range employees {

if len(em.Emp1.Name) == 0 || len(em.Emp2.Name) == 0 {

t.Errorf("Expected non zero lengthed name.")

}

if em.Emp1.BossID.Int64 != em.Boss.ID || em.Emp2.BossID.Int64 != em.Boss.ID {

t.Errorf("Expected boss ids to match")

}

}

})

}

func TestSelectSliceMapTime(t *testing.T) {

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

rows, err := db.Queryx("SELECT * FROM person")

if err != nil {

t.Fatal(err)

}

for rows.Next() {

_, err := rows.SliceScan()

if err != nil {

t.Error(err)

}

}

rows, err = db.Queryx("SELECT * FROM person")

if err != nil {

t.Fatal(err)

}

for rows.Next() {

m := map[string]interface{}{}

err := rows.MapScan(m)

if err != nil {

t.Error(err)

}

}

})

}

func TestNilReceiver(t *testing.T) {

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

var p *Person

err := db.Get(p, "SELECT * FROM person LIMIT 1")

if err == nil {

t.Error("Expected error when getting into nil struct ptr.")

}

var pp *[]Person

err = db.Select(pp, "SELECT * FROM person")

if err == nil {

t.Error("Expected an error when selecting into nil slice ptr.")

}

})

}

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: "[email protected]", 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: "[email protected]", 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 != "[email protected]" {

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

})

}

func TestNilInserts(t *testing.T) {

var schema = Schema{

create: `

CREATE TABLE tt (

id integer,

value text NULL DEFAULT NULL

);`,

drop: "drop table tt;",

}

RunWithSchema(schema, t, func(db *DB, t *testing.T) {

type TT struct {

ID int

Value *string

}

var v, v2 TT

r := db.Rebind

db.MustExec(r(`INSERT INTO tt (id) VALUES (1)`))

db.Get(&v, r(`SELECT * FROM tt`))

if v.ID != 1 {

t.Errorf("Expecting id of 1, got %v", v.ID)

}

if v.Value != nil {

t.Errorf("Expecting NULL to map to nil, got %s", *v.Value)

}

v.ID = 2

// NOTE: this incidentally uncovered a bug which was that named queries with

// pointer destinations would not work if the passed value here was not addressable,

// as reflectx.FieldByIndexes attempts to allocate nil pointer receivers for

// writing. This was fixed by creating & using the reflectx.FieldByIndexesReadOnly

// function. This next line is important as it provides the only coverage for this.

db.NamedExec(`INSERT INTO tt (id, value) VALUES (:id, :value)`, v)

db.Get(&v2, r(`SELECT * FROM tt WHERE id=2`))

if v.ID != v2.ID {

t.Errorf("%v != %v", v.ID, v2.ID)

}

if v2.Value != nil {

t.Errorf("Expecting NULL to map to nil, got %s", *v.Value)

}

})

}

func TestScanError(t *testing.T) {

var schema = Schema{

create: `

CREATE TABLE kv (

k text,

v integer

);`,

drop: `drop table kv;`,

}

RunWithSchema(schema, t, func(db *DB, t *testing.T) {

type WrongTypes struct {

K int

V string

}

_, err := db.Exec(db.Rebind("INSERT INTO kv (k, v) VALUES (?, ?)"), "hi", 1)

if err != nil {

t.Error(err)

}

rows, err := db.Queryx("SELECT * FROM kv")

if err != nil {

t.Error(err)

}

for rows.Next() {

var wt WrongTypes

err := rows.StructScan(&wt)

if err == nil {

t.Errorf("%s: Scanning wrong types into keys should have errored.", db.DriverName())

}

}

})

}

// FIXME: this function is kinda big but it slows things down to be constantly

// loading and reloading the schema..

func TestUsage(t *testing.T) {

RunWithSchema(defaultSchema, t, func(db *DB, t *testing.T) {

loadDefaultFixture(db, t)

slicemembers := []SliceMember{}

err := db.Select(&slicemembers, "SELECT * FROM place ORDER BY telcode ASC")

if err != nil {

t.Fatal(err)

}

people := []Person{}

err = db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

if err != nil {

t.Fatal(err)

}

jason, john := people[0], people[1]

if jason.FirstName != "Jason" {

t.Errorf("Expecting FirstName of Jason, got %s", jason.FirstName)

}

if jason.LastName != "Moiron" {

t.Errorf("Expecting LastName of Moiron, got %s", jason.LastName)

}

if jason.Email != "[email protected]" {

t.Errorf("Expecting Email of [email protected], got %s", jason.Email)

}

if john.FirstName != "John" || john.LastName != "Doe" || john.Email != "[email protected]" {

t.Errorf("John Doe's person record not what expected: Got %v\n", john)

}

jason = Person{}

err = db.Get(&jason, db.Rebind("SELECT * FROM person WHERE first_name=?"), "Jason")

if err != nil {

t.Fatal(err)

}

if jason.FirstName != "Jason" {

t.Errorf("Expecting to get back Jason, but got %v\n", jason.FirstName)

}

err = db.Get(&jason, db.Rebind("SELECT * FROM person WHERE first_name=?"), "Foobar")

if err == nil {

t.Errorf("Expecting an error, got nil\n")

}

if err != sql.ErrNoRows {

t.Errorf("Expected sql.ErrNoRows, got %v\n", err)

}

// The following tests check statement reuse, which was actually a problem

// due to copying being done when creating Stmt's which was eventually removed

stmt1, err := db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))

if err != nil {

t.Fatal(err)

}

jason = Person{}

row := stmt1.QueryRowx("DoesNotExist")

row.Scan(&jason)

row = stmt1.QueryRowx("DoesNotExist")

row.Scan(&jason)

err = stmt1.Get(&jason, "DoesNotExist User")

if err == nil {

t.Error("Expected an error")

}

err = stmt1.Get(&jason, "DoesNotExist User 2")

stmt2, err := db.Preparex(db.Rebind("SELECT * FROM person WHERE first_name=?"))

if err != nil {

t.Fatal(err)

}

jason = Person{}

tx, err := db.Beginx()

if err != nil {

t.Fatal(err)

}

tstmt2 := tx.Stmtx(stmt2)

row2 := tstmt2.QueryRowx("Jason")

err = row2.StructScan(&jason)

if err != nil {

t.Error(err)

}

tx.Commit()

places := []*Place{}

err = db.Select(&places, "SELECT telcode FROM place ORDER BY telcode ASC")

usa, singsing, honkers := places[0], places[1], places[2]

if usa.TelCode != 1 || honkers.TelCode != 852 || singsing.TelCode != 65 {

t.Errorf("Expected integer telcodes to work, got %#v", places)

}

placesptr := []PlacePtr{}

err = db.Select(&placesptr, "SELECT * FROM place ORDER BY telcode ASC")

if err != nil {

t.Error(err)

}

//fmt.Printf("%#v\n%#v\n%#v\n", placesptr[0], placesptr[1], placesptr[2])

// if you have null fields and use SELECT *, you must use sql.Null* in your struct

// this test also verifies that you can use either a []Struct{} or a []*Struct{}

places2 := []Place{}

err = db.Select(&places2, "SELECT * FROM place ORDER BY telcode ASC")

usa, singsing, honkers = &places2[0], &places2[1], &places2[2]

// this should return a type error that &p is not a pointer to a struct slice

p := Place{}

err = db.Select(&p, "SELECT * FROM place ORDER BY telcode ASC")

if err == nil {

t.Errorf("Expected an error, argument to select should be a pointer to a struct slice")

}

// this should be an error

pl := []Place{}

err = db.Select(pl, "SELECT * FROM place ORDER BY telcode ASC")

if err == nil {

t.Errorf("Expected an error, argument to select should be a pointer to a struct slice, not a slice.")

}

if usa.TelCode != 1 || honkers.TelCode != 852 || singsing.TelCode != 65 {

t.Errorf("Expected integer telcodes to work, got %#v", places)

}

stmt, err := db.Preparex(db.Rebind("SELECT country, telcode FROM place WHERE telcode > ? ORDER BY telcode ASC"))

if err != nil {

t.Error(err)

}

places = []*Place{}

err = stmt.Select(&places, 10)

if len(places) != 2 {

t.Error("Expected 2 places, got 0.")

}

if err != nil {

t.Fatal(err)

}

singsing, honkers = places[0], places[1]

if singsing.TelCode != 65 || honkers.TelCode != 852 {

t.Errorf("Expected the right telcodes, got %#v", places)

}

rows, err := db.Queryx("SELECT * FROM place")

if err != nil {

t.Fatal(err)

}

place := Place{}

for rows.Next() {

err = rows.StructScan(&place)

if err != nil {

t.Fatal(err)

}

}

rows, err = db.Queryx("SELECT * FROM place")

if err != nil {

t.Fatal(err)

}

m := map[string]interface{}{}

for rows.Next() {

err = rows.MapScan(m)

if err != nil {

t.Fatal(err)

}

_, ok := m["country"]

if !ok {

t.Errorf("Expected key `country` in map but could not find it (%#v)\n", m)

}

}

rows, err = db.Queryx("SELECT * FROM place")

if err != nil {

t.Fatal(err)

}

for rows.Next() {

s, err := rows.SliceScan()

if err != nil {

t.Error(err)

}

if len(s) != 3 {