func TestSelect(t *testing.T) {
// Connect to the database specified in the test db.json config
// Default to the Travis CI settings if no file is found
conn, tx := dbtest.WithConfig(t, "./db.json")
// Perform all tests in a transaction and always rollback
defer conn.Close()
defer tx.Rollback()
_, err := tx.Execute(users.Create())
require.Nil(t, err)
// Insert users as values or pointers
admin := user{Name: "admin", IsActive: true}
stmt := aspect.Insert(
users.C["name"],
users.C["password"],
users.C["is_active"],
)
_, err = tx.Execute(stmt.Values(admin))
require.Nil(t, err)
_, err = tx.Execute(stmt.Values(&admin))
require.Nil(t, err)
var u user
require.Nil(t, tx.QueryOne(users.Select(), &u))
assert.Equal(t, "admin", u.Name)
assert.True(t, u.IsActive)
// Select using a returning clause
client := user{Name: "client", Password: "******", IsActive: false}
returningStmt := Insert(
users.C["name"],
users.C["password"],
users.C["is_active"],
).Returning(
users.C["id"],
users.C["created_at"],
)
require.Nil(t, tx.QueryOne(returningStmt.Values(client), &client))
// The ID and time should be anything but zero
assert.NotEqual(t, int64(0), client.ID)
assert.False(t, client.CreatedAt.IsZero())
// Select into a struct that has extra columns
// TODO Skip unexported fields
var extraField testUser
require.Nil(t, tx.QueryOne(users.Select().Where(users.C["name"].Equals("client")), &extraField))
assert.Equal(t, "client", extraField.Name)
assert.False(t, extraField.IsActive)
// Query multiple users
var extraFields []testUser
assert.Nil(t, tx.QueryAll(users.Select(), &extraFields))
assert.Equal(t, 3, len(extraFields))
// Query ids directly
var ids []int64
orderBy := aspect.Select(users.C["id"]).OrderBy(users.C["id"].Desc())
assert.Nil(t, tx.QueryAll(orderBy, &ids))
assert.Equal(t, 3, len(ids))
var prev int64
for _, id := range ids {
if prev != 0 {
if prev < id {
t.Errorf("id results returned out of order")
}
}
}
// Scan into a Values map - all fields should be returned
selectByName := users.Select().OrderBy(users.C["name"])
values := aspect.Values{}
assert.Nil(t, tx.QueryOne(selectByName, values))
assert.Equal(t, 5, len(values))
assert.NotEqual(t, int64(0), values["id"])
assert.Equal(t, []byte("admin"), values["name"]) // Yup, strings are []byte
assert.EqualValues(t, true, values["is_active"])
assert.Equal(t, []byte{}, values["password"])
// Scan into a slice of Values maps
var allValues []aspect.Values
assert.Nil(t, tx.QueryAll(selectByName, &allValues))
assert.Equal(t, 3, len(allValues))
values = allValues[2]
assert.Equal(t, 5, len(values))
assert.NotEqual(t, int64(0), values["id"])
assert.Equal(t, []byte("client"), values["name"])
assert.EqualValues(t, false, values["is_active"])
assert.Equal(t, []byte("1234"), values["password"])
// TODO Test duplicate names in a table join
// TODO destination types that don't match the result
// Update
// ------
updateStmt := users.Update().Values(
aspect.Values{"name": "HELLO", "password": "******"},
).Where(
users.C["id"].Equals(client.ID),
)
result, err := tx.Execute(updateStmt)
require.Nil(t, err)
rowsAffected, err := result.RowsAffected()
assert.Nil(t, err)
assert.EqualValues(t, 1, rowsAffected)
// Delete
// ------
result, err = tx.Execute(users.Delete())
require.Nil(t, err)
rowsAffected, err = result.RowsAffected()
assert.Nil(t, err)
assert.EqualValues(t, 3, rowsAffected)
}
// Insert creates an INSERT ... RETURNING statement for the given columns.
// There must be at least one column and all columns must belong to the
// same table.
func Insert(s aspect.Selectable, ss ...aspect.Selectable) RetInsertStmt {
return RetInsertStmt{
InsertStmt: aspect.Insert(s, ss...),
}
}
