func TestResultTypes(t *testing.T) {
conn, err := aspect.Connect("sqlite3", ":memory:")
require.Nil(t, err, "Failed to connect to in-memory sqlite3 instance")
defer conn.Close()
// Create the users table
_, err = conn.Execute(users.Create())
require.Nil(t, err, "Failed to create users table")
admin := embedUser{
id: id{ID: 1},
fullname: fullname{Name: "admin"},
Password: "******",
}
_, err = conn.Execute(users.Insert().Values(admin))
require.Nil(t, err, "Inserting a single user should not error")
// Tagless destination
var untagged tagless
conn.MustQueryOne(
aspect.Select(users.C["id"], users.C["name"]).Limit(1),
&untagged,
)
assert.EqualValues(t, 1, untagged.ID)
assert.Equal(t, "admin", untagged.Name)
var untaggeds []tagless
conn.MustQueryAll(
aspect.Select(users.C["id"], users.C["name"]),
&untaggeds,
)
require.Equal(t, 1, len(untaggeds))
assert.EqualValues(t, 1, untaggeds[0].ID)
assert.Equal(t, "admin", untaggeds[0].Name)
// Tagless insert - number of columns must match numebr of exported fields
noTag := fullTagless{
ID: 2,
Name: "tagless",
// Password is a blank string
}
_, err = conn.Execute(users.Insert().Values(noTag))
require.Nil(t, err, "Inserting a single tagless user should not error")
// Embedded destination
var embed embedUser
conn.MustQueryOne(
users.Select().Where(users.C["id"].Equals(1)).Limit(1),
&embed,
)
assert.EqualValues(t, 1, embed.id.ID)
assert.Equal(t, "admin", embed.fullname.Name)
assert.Equal(t, "secret", embed.Password)
}
// Connect to an in-memory sqlite3 instance and execute some statements.
func TestConnect(t *testing.T) {
assert := assert.New(t)
conn, err := aspect.Connect("sqlite3", ":memory:")
require.Nil(t, err, "Failed to connect to in-memory sqlite3 instance")
defer conn.Close()
// Create the users table
_, err = conn.Execute(users.Create())
require.Nil(t, err, "Failed to create users table")
// Insert a user
admin := user{
ID: 1,
Name: "admin",
Password: "******",
}
_, err = conn.Execute(users.Insert().Values(admin))
require.Nil(t, err, "Inserting a single user should not error")
// Insert multiple users
clients := []user{
user{ID: 2, Name: "client1"},
user{ID: 3, Name: "client2"},
}
_, err = conn.Execute(users.Insert().Values(clients))
require.Nil(t, err, "Inserting multiple users should not error")
// Select a user - Query must be given a pointer
var u user
require.Nil(t, conn.QueryOne(users.Select(), &u))
assert.Equal(admin.ID, u.ID)
assert.Equal(admin.Name, u.Name)
assert.Equal(admin.Password, u.Password)
// Select an incomplete, embedded struct
var embed embedded
require.Nil(t, conn.QueryOne(users.Select(), &embed))
assert.Equal(admin.ID, embed.id.ID)
assert.Equal(admin.Name, embed.Name)
// Select a wrapped struct
var wrap wrapped
require.Nil(t, conn.QueryOne(users.Select(), &wrap))
assert.Equal(admin.ID, wrap.user.ID)
assert.Equal(admin.Name, wrap.user.Name)
assert.Equal(admin.Password, wrap.user.Password)
assert.EqualValues(0, wrap.ID)
// Select multiple users
var us []user
require.Nil(t, conn.QueryAll(users.Select().OrderBy(users.C["id"]), &us))
require.Equal(t, 3, len(us))
assert.Equal(admin.ID, us[0].ID)
assert.Equal(admin.Name, us[0].Name)
assert.Equal(admin.Password, us[0].Password)
// Select multiple users with embedding
var embeds []embedded
require.Nil(t,
conn.QueryAll(users.Select().OrderBy(users.C["id"]), &embeds),
)
require.Equal(t, 3, len(us))
assert.Equal(admin.ID, embeds[0].id.ID)
assert.Equal(admin.Name, embeds[0].Name)
// Select multiple embedded users into a slice that is pre-populated
embeds = []embedded{
{Name: "a"},
{Name: "b"},
{Name: "c"},
}
require.Nil(t, conn.QueryAll(
aspect.Select(users.C["id"]).OrderBy(users.C["id"]), &embeds,
))
require.Equal(t, 3, len(us))
assert.EqualValues(1, embeds[0].id.ID)
assert.Equal("a", embeds[0].Name)
// Drop the table
_, err = conn.Execute(users.Drop())
assert.Nil(err, "Dropping the users table should not fail")
}
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)
}