Example #1
0
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)
}
Example #2
0
// 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...),
	}
}