func TestDeleteReal(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var id int64
// Insert a Barack
s.InsertInto("people").
Columns("name", "email").
Values("Barack", "*****@*****.**").
Returning("id").
QueryScalar(&id)
// Delete Barack
res, err := s.DeleteFrom("people").Where("id = $1", id).Exec()
assert.NoError(t, err)
// Ensure we only reflected one row and that the id no longer exists
assert.EqualValues(t, res.RowsAffected, 1)
var count int64
err = s.Select("count(*)").
From("people").
Where("id = $1", id).
QueryScalar(&count)
assert.NoError(t, err)
assert.EqualValues(t, count, 0)
}
func TestInsertMultipleRecords(t *testing.T) {
assert := assert.New(t)
s := beginTxWithFixtures()
defer s.AutoRollback()
res, err := s.
InsertInto("people").
Columns("name", "email").
Values("apple", "*****@*****.**").
Values("orange", "*****@*****.**").
Values("pear", "*****@*****.**").
Exec()
assert.NoError(err)
assert.EqualValues(res.RowsAffected, 3)
person1 := Person{Name: "john_timr"}
person2 := Person{Name: "jane_timr"}
res, err = s.InsertInto("people").
Columns("name", "email").
Record(&person1).
Record(&person2).
Exec()
assert.NoError(err)
n := res.RowsAffected
assert.EqualValues(n, 2)
people := []Person{}
err = s.
Select("name").
From("people").
Where("name like $1", "%_timr").
QueryStructs(&people)
assert.NoError(err)
assert.Equal(len(people), 2)
n = 0
for _, person := range people {
if person.Name == "john_timr" {
n++
}
if person.Name == "jane_timr" {
n++
}
}
assert.EqualValues(n, 2)
}
func TestUpdateKeywordColumnName(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
// Insert a user with a key
res, err := s.
InsertInto("people").
Columns("name", "email", "key").
Values("Benjamin", "*****@*****.**", "6").
Exec()
assert.NoError(t, err)
// Update the key
res, err = s.Update("people").Set("key", "6-revoked").Where(dat.Eq{"key": "6"}).Exec()
assert.NoError(t, err)
// Assert our record was updated (and only our record)
assert.EqualValues(t, res.RowsAffected, 1)
var person Person
err = s.Select("*").From("people").Where(dat.Eq{"email": "*****@*****.**"}).QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.Name, "Benjamin")
assert.Equal(t, person.Key.String, "6-revoked")
}
func TestSelectQueryEmbeddedJSON(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
type PostEmbedded struct {
ID int `db:"id"`
State string `db:"state"`
User struct {
ID int64
}
}
type User struct {
ID int64
}
type PostEmbedded2 struct {
ID int `db:"id"`
State string `db:"state"`
User *User
}
var post PostEmbedded
err := s.SelectDoc("id", "state").
One("user", `select 42 as id`).
From("posts").
Where("id = $1", 1).
QueryStruct(&post)
assert.NoError(t, err)
assert.Equal(t, 1, post.ID)
assert.EqualValues(t, 42, post.User.ID)
var post2 PostEmbedded2
err = s.SelectDoc("id", "state").
One("user", `select 42 as id`).
From("posts").
Where("id = $1", 1).
QueryStruct(&post2)
assert.NoError(t, err)
assert.Equal(t, 1, post2.ID)
assert.EqualValues(t, 42, post2.User.ID)
}
// Insect should select existing record.
func TestInsectSelect(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var id int64
err := s.Insect("people").
Columns("name", "email").
Values("Mario", "*****@*****.**").
Returning("id").
QueryScalar(&id)
assert.NoError(t, err)
assert.EqualValues(t, 1, id)
}
func TestScalar(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var name string
err := s.Select("name").From("people").Where("email = '*****@*****.**'").QueryScalar(&name)
assert.NoError(t, err)
assert.Equal(t, name, "John")
var count int64
err = s.Select("COUNT(*)").From("people").QueryScalar(&count)
assert.NoError(t, err)
assert.EqualValues(t, count, 6)
}
func TestInsertKeywordColumnName(t *testing.T) {
// Insert a column whose name is reserved
s := beginTxWithFixtures()
defer s.AutoRollback()
res, err := s.
InsertInto("people").
Columns("name", "key").
Values("Barack", "44").
Exec()
assert.NoError(t, err)
assert.EqualValues(t, res.RowsAffected, 1)
}
// Insect should select existing record without updating it (see Upsert)
func TestInsectSelectWhere(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var p Person
err := s.Insect("people").
Columns("name", "email").
Values("Foo", "*****@*****.**").
Where("id = $1", 1).
Returning("id", "name", "email").
QueryStruct(&p)
assert.NoError(t, err)
assert.EqualValues(t, 1, p.ID)
assert.Equal(t, "Mario", p.Name)
assert.Equal(t, "*****@*****.**", p.Email.String)
}
func TestSelectBySqlQueryStructs(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var people []*Person
dat.EnableInterpolation = true
err := s.
SQL("SELECT name FROM people WHERE email IN $1", []string{"*****@*****.**"}).
QueryStructs(&people)
dat.EnableInterpolation = false
assert.NoError(t, err)
assert.Equal(t, len(people), 1)
assert.Equal(t, people[0].Name, "John")
assert.EqualValues(t, people[0].ID, 0) // not set
assert.Equal(t, people[0].Email.Valid, false) // not set
assert.Equal(t, people[0].Email.String, "") // not set
}
func TestSelectQueryEmbedded(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
type PostEmbedded struct {
ID int `db:"id"`
State string `db:"state"`
User struct {
ID int64 `db:"author_id"`
}
}
type User struct {
ID int64 `db:"user_id"`
}
type PostEmbedded2 struct {
ID int `db:"id"`
State string `db:"state"`
User
}
var post2 PostEmbedded2
// THIS RESULTS IN ERROR
// var post PostEmbedded
// err := s.Select("id", "state", "42 as user_id").
// From("posts").
// Where("id = $1", 1).
// QueryStruct(&post)
// assert.Error(t, err)
err := s.Select("id", "state", "42 as user_id").
From("posts").
Where("id = $1", 1).
QueryStruct(&post2)
assert.NoError(t, err)
assert.Equal(t, 1, post2.ID)
assert.EqualValues(t, 42, post2.User.ID)
}
func TestRollbackWithNestedCommit(t *testing.T) {
log.Suppress(true)
defer log.Suppress(false)
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
err = nestedCommit(tx)
assert.NoError(t, err)
err = tx.Rollback()
assert.NoError(t, err)
var person Person
err = testDB.
Select("*").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.Exactly(t, sql.ErrNoRows, err)
assert.EqualValues(t, 0, person.ID)
}