func TestUpdateReturningStar(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
// Insert a George
var insertPerson Person
err := s.InsertInto("people").Columns("name", "email").
Values("George", "*****@*****.**").
Returning("*").
QueryStruct(&insertPerson)
assert.NoError(t, err)
assert.NotEmpty(t, insertPerson.ID)
assert.Equal(t, insertPerson.Name, "George")
assert.Equal(t, insertPerson.Email.Valid, true)
assert.Equal(t, insertPerson.Email.String, "*****@*****.**")
var updatePerson Person
err = s.Update("people").
Set("name", "Barack").
Set("email", "*****@*****.**").
Where("id = $1", insertPerson.ID).
Returning("*").
QueryStruct(&updatePerson)
assert.NoError(t, err)
assert.Equal(t, insertPerson.ID, updatePerson.ID)
assert.Equal(t, updatePerson.Name, "Barack")
assert.Equal(t, updatePerson.Email.Valid, true)
assert.Equal(t, updatePerson.Email.String, "*****@*****.**")
}
func TestUpdateBlacklist(t *testing.T) {
installFixtures()
// Insert by specifying a record (struct)
p := Person{Name: "Barack"}
p.Foo = "bar"
var foo string
var name string
var id int64
err := testDB.
InsertInto("people").
Whitelist("name", "foo").
Record(p).
Returning("id", "name", "foo").
QueryScalar(&id, &name, &foo)
assert.NoError(t, err)
assert.True(t, id > 0)
assert.Equal(t, name, "Barack")
assert.Equal(t, foo, "bar")
p2 := Person{Name: "oy"}
p2.Foo = "bah"
var name2 string
var foo2 string
err = testDB.
Update("people").
SetBlacklist(p2, "id", "name", "email", "key", "doc", "created_at").
Where("id = $1", id).
Returning("name", "foo").
QueryScalar(&name2, &foo2)
assert.NoError(t, err)
assert.True(t, id > 0)
assert.Equal(t, name2, "Barack")
assert.Equal(t, foo2, "bah")
}
func TestUpdateReal(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var id int64
// Insert a George
s.InsertInto("people").Columns("name", "email").
Values("George", "*****@*****.**").
Returning("id").
QueryScalar(&id)
// Rename our George to Barack
_, err := s.Update("people").SetMap(map[string]interface{}{"name": "Barack", "email": "*****@*****.**"}).Where("id = $1", id).Exec()
assert.NoError(t, err)
var person Person
err = s.Select("*").From("people").Where("id = $1", id).QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.ID, id)
assert.Equal(t, person.Name, "Barack")
assert.Equal(t, person.Email.Valid, true)
assert.Equal(t, person.Email.String, "*****@*****.**")
}
func TestCacheSelectQuerySliceByHash(t *testing.T) {
Cache.FlushDB()
for i := 0; i < 2; i++ {
var names []string
err := testDB.
Select("name").
From("people").
Cache("", 1*time.Second, false).
QuerySlice(&names)
assert.NoError(t, err)
assert.Equal(t, len(names), 6)
assert.Equal(t, names, []string{"Mario", "John", "Grant", "Tony", "Ester", "Reggie"})
var ids []int64
err = testDB.
Select("id").
From("people").
Limit(1).
Cache("", 1*time.Second, false).
QuerySlice(&ids)
assert.NoError(t, err)
assert.Equal(t, len(ids), 1)
assert.Equal(t, ids, []int64{1})
}
}
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 TestCommitWithNestedNestedCommit(t *testing.T) {
log.Suppress(true)
defer log.Suppress(false)
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
err = nestedNestedCommit(tx)
assert.NoError(t, err)
err = tx.Commit()
assert.NoError(t, err)
var person Person
err = testDB.
Select("*").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
err = testDB.
Select("*").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
}
func TestTransactionReal(t *testing.T) {
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
var id int64
tx.InsertInto("people").Columns("name", "email").
Values("Barack", "*****@*****.**").
Returning("id").
QueryScalar(&id)
assert.True(t, id > 0)
var person Person
err = tx.
Select("*").
From("people").
Where("id = $1", id).
QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.ID, id)
assert.Equal(t, person.Name, "Barack")
assert.Equal(t, person.Email.Valid, true)
assert.Equal(t, person.Email.String, "*****@*****.**")
err = tx.Commit()
assert.NoError(t, err)
}
func TestInsect(t *testing.T) {
// Insert by specifying values
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.True(t, id > 0)
// Insert by specifying a record (ptr to struct)
person := Person{Name: "Barack"}
person.Email.Valid = true
person.Email.String = "*****@*****.**"
err = s.
Insect("people").
Columns("name", "email").
Record(&person).
Returning("id", "created_at").
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
assert.NotEqual(t, person.CreatedAt, dat.NullTime{})
}
func TestInsertDoubleDollarQuote(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
expected := common.RandomString(16)
var str string
err := s.
InsertInto("people").
Columns("name", "key").
Values("test", expected).
Returning("key").
QueryScalar(&str)
assert.NoError(t, err)
assert.Equal(t, expected, str)
// ensure the tag cannot be escaped by user
oldDollarTag := postgres.GetPgDollarTag()
expected = common.RandomString(1024) + "'" + oldDollarTag
builder := s.
InsertInto("people").
Columns("name", "key").
Values("test", expected).
Returning("key")
sql, _, _ := builder.SetIsInterpolated(true).Interpolate()
assert.NotEqual(t, oldDollarTag, postgres.GetPgDollarTag())
assert.True(t, strings.Contains(sql, postgres.GetPgDollarTag()))
builder.QueryScalar(&str)
assert.NoError(t, err)
assert.Equal(t, expected, str)
}
func TestLegacyIn(t *testing.T) {
var cat = "cat"
if isWindows {
cat = "cmd /c type"
}
//// Run
// in V2 BashOutput accepts an options map
out, err := RunOutput(cat+" foo.txt", M{"$in": "test"})
assert.NoError(t, err)
assert.Equal(t, "foo", str.Clean(out))
if isWindows {
return
}
//// Bash
// in V2 BashOutput accepts an options map
out, err = BashOutput("cat foo.txt", M{"$in": "test"})
assert.NoError(t, err)
assert.Equal(t, "foo", str.Clean(out))
}
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 TestCacheSelectQueryScalar(t *testing.T) {
Cache.FlushDB()
for i := 0; i < 2; i++ {
var name string
err := testDB.
Select("name").
From("people").
Where("email = '*****@*****.**'").
Cache("selectdoc.8", 1*time.Second, false).
QueryScalar(&name)
assert.NoError(t, err)
assert.Equal(t, name, "John")
var id int64
err = testDB.
Select("id").
From("people").
Limit(1).
Cache("selectdoc.9", 1*time.Second, false).
QueryScalar(&id)
assert.NoError(t, err)
assert.True(t, id > 0)
}
}
func TestErrorInBeginIfRollbacked(t *testing.T) {
log.Suppress(true)
defer log.Suppress(false)
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
err = tx.Rollback()
assert.NoError(t, err)
_, err = tx.Begin()
assert.Exactly(t, ErrTxRollbacked, err)
}
func TestInterpolateInvalidNullTime(t *testing.T) {
now := time.Now()
invalid := NullTime{pq.NullTime{Valid: false}}
valid := NullTime{pq.NullTime{Time: now, Valid: true}}
sql, _, err := Interpolate("SELECT * FROM foo WHERE valid = $1", []interface{}{valid})
assert.NoError(t, err)
assert.Equal(t, "SELECT * FROM foo WHERE valid = '"+valid.Time.Format(time.RFC3339Nano)+"'", sql)
sql, _, err = Interpolate("SELECT * FROM foo WHERE invalid = $1", []interface{}{invalid})
assert.NoError(t, err)
assert.Equal(t, stripWS("SELECT * FROM foo WHERE invalid=NULL"), stripWS(sql))
}
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 TestTransactionRollbackReal(t *testing.T) {
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
var person Person
err = tx.Select("*").From("people").Where("email = $1", "*****@*****.**").QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.Name, "John")
err = tx.Rollback()
assert.NoError(t, err)
}
func TestSelectDocBytes(t *testing.T) {
b, err := testDB.SelectDoc("id", "comment").
From("comments").
OrderBy("id").
QueryJSON()
assert.NoError(t, err)
var comments jo.Object
err = json.Unmarshal(b, &comments)
assert.NoError(t, err)
assert.Equal(t, "A very good day", comments.MustString("[0].comment"))
assert.Equal(t, "Yum. Apple pie.", comments.MustString("[1].comment"))
}
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 TestInterpolateSlices(t *testing.T) {
args := []interface{}{[]int{1}, []int{1, 2, 3}, []uint32{5, 6, 7}, []string{"wat", "ok"}}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1 AND b = $2 AND c = $3 AND d = $4", args)
assert.NoError(t, err)
assert.Equal(t, str, "SELECT * FROM x WHERE a = (1) AND b = (1,2,3) AND c = (5,6,7) AND d = ('wat','ok')")
}
func TestInterpolateEscapeStrings(t *testing.T) {
args := []interface{}{"hello", "\"pg's world\" \\\b\f\n\r\t\x1a"}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1 AND b = $2", args)
assert.NoError(t, err)
assert.Equal(t, "SELECT * FROM x WHERE a = 'hello' AND b = '\"pg''s world\" \\\b\f\n\r\t\x1a'", str)
}
func TestInterpolateFloats(t *testing.T) {
args := []interface{}{float32(0.15625), float64(3.14159)}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1 AND b = $2", args)
assert.NoError(t, err)
assert.Equal(t, str, "SELECT * FROM x WHERE a = 0.15625 AND b = 3.14159")
}
func TestInterpolateBools(t *testing.T) {
args := []interface{}{true, false}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1 AND b = $2", args)
assert.NoError(t, err)
assert.Equal(t, str, "SELECT * FROM x WHERE a = 't' AND b = 'f'")
}
func TestInterpolateNil(t *testing.T) {
args := []interface{}{nil}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1", args)
assert.NoError(t, err)
assert.Equal(t, str, "SELECT * FROM x WHERE a = NULL")
}
func TestInterpolateInvalidNullTime(t *testing.T) {
invalid := NullTime{pq.NullTime{Valid: false}}
sql, _, err := Interpolate("SELECT * FROM foo WHERE invalid = $1", []interface{}{invalid})
assert.NoError(t, err)
assert.Equal(t, stripWS("SELECT * FROM foo WHERE invalid=NULL"), stripWS(sql))
}
func TestInterpolateJSON(t *testing.T) {
j, _ := NewJSON([]int{1, 3, 10})
sql, args, err := Interpolate("SELECT $1", []interface{}{j})
assert.NoError(t, err)
assert.Equal(t, "SELECT '[1,3,10]'", sql)
assert.Equal(t, 0, len(args))
}
func TestIntepolatingValuers(t *testing.T) {
args := []interface{}{myString{true, "wat"}, myString{false, "fry"}}
str, _, err := Interpolate("SELECT * FROM x WHERE a = $1 AND b = $2", args)
assert.NoError(t, err)
assert.Equal(t, str, "SELECT * FROM x WHERE a = 'wat' AND b = NULL")
}
func TestSelectDocOneNoRows(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
type User struct {
ID int64
}
type PostEmbedded struct {
ID int `db:"id"`
State string `db:"state"`
User *User
}
var post PostEmbedded
err := s.SelectDoc("id", "state").
One("user", `select * from people where id = 1232345`).
From("posts").
Where("id = $1", 1).
QueryStruct(&post)
assert.NoError(t, err)
assert.Equal(t, 1, post.ID)
assert.Nil(t, post.User)
}
func TestSelectQueryStruct(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
// Found:
var person Person
err := s.
Select("id", "name", "email").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
assert.Equal(t, person.Name, "John")
assert.True(t, person.Email.Valid)
assert.Equal(t, person.Email.String, "*****@*****.**")
// Not found:
var person2 Person
err = s.
Select("id", "name", "email").
From("people").Where("email = $1", "*****@*****.**").
QueryStruct(&person2)
assert.Contains(t, err.Error(), "no rows")
}
func TestSelectQueryStructs(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var people []Person
err := s.
Select("id", "name", "email").
From("people").
OrderBy("id ASC").
QueryStructs(&people)
assert.NoError(t, err)
assert.Equal(t, len(people), 6)
// Make sure that the Ids are set. It's possible (maybe?) that different DBs set ids differently so
// don't assume they're 1 and 2.
assert.True(t, people[0].ID > 0)
assert.True(t, people[1].ID > people[0].ID)
assert.Equal(t, people[0].Name, "Mario")
assert.True(t, people[0].Email.Valid)
assert.Equal(t, people[0].Email.String, "*****@*****.**")
assert.Equal(t, people[1].Name, "John")
assert.True(t, people[1].Email.Valid)
assert.Equal(t, people[1].Email.String, "*****@*****.**")
// TODO: test map
}
func TestSelectDocNested(t *testing.T) {
assert := assert.New(t)
var obj jo.Object
posts := dat.SelectDoc("id", "title").
Many("comments", `SELECT * FROM comments WHERE comments.id = posts.id`).
From("posts").
Where("user_id = people.id")
err := testDB.
SelectDoc("id", "name").
Many("posts", posts).
From("people").
Where("id = $1", 1).
SetIsInterpolated(true).
QueryStruct(&obj)
assert.NoError(err)
assert.Equal("Mario", obj.AsString("name"))
assert.Equal(1, obj.AsInt("id"))
assert.Equal("A very good day", obj.AsString("posts[0].comments[0].comment"))
assert.Equal("Yum. Apple pie.", obj.AsString("posts[1].comments[0].comment"))
}