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) }