func TestDocDistinctOn(t *testing.T) {
published := `
INNER JOIN posts p on (p.author_id = u.id)
WHERE
p.state = $1
`
sql, args := SelectDoc("u.*, p.*").
DistinctOn("aa", "bb").
From(`users u`).
Scope(published, "published").
Where(`u.id = $1`, 1).
ToSQL()
expected := `
SELECT row_to_json(dat__item.*)
FROM (
SELECT DISTINCT ON (aa, bb)
u.*, p.*
FROM users u
INNER JOIN posts p on (p.author_id = u.id)
WHERE (u.id = $1) AND ( p.state = $2 )
) as dat__item
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Exactly(t, args, []interface{}{1, "published"})
}
func TestCommitWithNestedNestedRollback(t *testing.T) {
log.Suppress(true)
defer log.Suppress(false)
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
err = nestedNestedRollback(tx)
assert.NoError(t, err)
err = tx.Commit()
assert.Exactly(t, ErrTxRollbacked, err)
var person Person
err = testDB.
Select("*").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.Exactly(t, sql.ErrNoRows, err)
}
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 TestInnerJoin(t *testing.T) {
sql, args := Select("u.*, p.*").
From(`
users u
INNER JOIN posts p on (p.author_id = u.id)
`).
Where(`u.id = $1`, 1).
ToSQL()
sql = str.Clean(sql)
assert.Equal(t, sql, "SELECT u.*, p.* FROM users u INNER JOIN posts p on (p.author_id = u.id) WHERE (u.id = $1)")
assert.Exactly(t, args, []interface{}{1})
}
func TestScopeJoinOnly(t *testing.T) {
published := `
INNER JOIN posts p on (p.author_id = u.id)
`
sql, args := Select("u.*, p.*").
From(`users u`).
Scope(published).
Where(`u.id = $1`, 1).
ToSQL()
sql = str.Clean(sql)
assert.Equal(t, "SELECT u.*, p.* FROM users u INNER JOIN posts p on (p.author_id = u.id) WHERE (u.id = $1)", sql)
assert.Exactly(t, args, []interface{}{1})
}
func TestInsertBytes(t *testing.T) {
b := []byte{0, 0, 0}
var image []byte
var id int32
sql := `
INSERT INTO people (name, image)
VALUES ($1, $2)
RETURNING id, image
`
dat.EnableInterpolation = true
err := testDB.SQL(sql, "foo", b).QueryScalar(&id, &image)
assert.NoError(t, err)
assert.Exactly(t, b, image)
dat.EnableInterpolation = false
}
func TestDistinctOn(t *testing.T) {
published := `
INNER JOIN posts p on (p.author_id = u.id)
`
sql, args := Select("u.*, p.*").
DistinctOn("foo", "bar").
From(`users u`).
Scope(published).
Where(`u.id = $1`, 1).
ToSQL()
assert.Equal(t, stripWS(`
SELECT DISTINCT ON (foo, bar) u.*, p.*
FROM users u
INNER JOIN posts p on (p.author_id = u.id)
WHERE (u.id = $1)`), stripWS(sql))
assert.Exactly(t, args, []interface{}{1})
}
func TestEmbeddedStructMapping(t *testing.T) {
type Realm struct {
RealmUUID string `db:"realm_uuid"`
}
type Group struct {
GroupUUID string `db:"group_uuid"`
*Realm
}
g := &Group{Realm: &Realm{"11"}, GroupUUID: "22"}
sql, args := InsertInto("groups").Columns("group_uuid", "realm_uuid").Record(g).ToSQL()
expected := `
INSERT INTO groups ("group_uuid", "realm_uuid")
VALUES ($1, $2)
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Exactly(t, []interface{}{"22", "11"}, args)
}
func TestNestedSelecDocWhere(t *testing.T) {
user := SelectDoc("id", "user_name").
Many("comments", `SELECT * FROM comments WHERE id = u.id`).
From("users u").
Where("u.id = $1", 1)
sql, args := SelectDoc("id").
One("user", user).
From(`games`).
Where(`id = $1`, 10).
ToSQL()
expected := `
SELECT row_to_json(dat__item.*)
FROM (
SELECT id,
(
SELECT row_to_json(dat__user.*)
FROM (
SELECT id, user_name,
(
SELECT array_agg(dat__comments.*)
FROM (SELECT * FROM comments WHERE id = u.id)
AS dat__comments
) AS "comments"
FROM users u
WHERE (u.id = $1)
) AS dat__user
) AS "user"
FROM games
WHERE (id = $2)
) as dat__item
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Exactly(t, args, []interface{}{1, 10})
}
func TestUpdateWhereExprSql(t *testing.T) {
expr := Expr("id=$1", 100)
sql, args := Update("a").Set("b", 10).Where(expr).ToSQL()
assert.Equal(t, sql, `UPDATE "a" SET "b" = $1 WHERE (id=$2)`)
assert.Exactly(t, args, []interface{}{10, 100})
}
func TestSelectScope(t *testing.T) {
scope := NewScope("WHERE :TABLE.id = :id and name = :name", M{"id": 1, "name": "foo"})
sql, args := Select("a").From("b").ScopeMap(scope, M{"name": "mario"}).ToSQL()
assert.Equal(t, `SELECT a FROM b WHERE ( "b".id = $1 and name = $2)`, sql)
assert.Exactly(t, args, []interface{}{1, "mario"})
}
func TestSelectWhereExprSql(t *testing.T) {
expr := Expr("id=$1", 100)
sql, args := Select("a").From("b").Where(expr).ToSQL()
assert.Equal(t, sql, "SELECT a FROM b WHERE (id=$1)")
assert.Exactly(t, args, []interface{}{100})
}
func TestCallSql(t *testing.T) {
sql, args := Call("foo", 1, "two").ToSQL()
assert.Equal(t, "SELECT * FROM foo($1,$2)", sql)
assert.Exactly(t, []interface{}{1, "two"}, args)
}
func TestDeleteWhereExprSql(t *testing.T) {
expr := Expr("id=$1", 100)
sql, args := DeleteFrom("a").Where("foo = $1", "bar").Where(expr).ToSQL()
assert.Equal(t, sql, `DELETE FROM a WHERE (foo = $1) AND (id=$2)`)
assert.Exactly(t, args, []interface{}{"bar", 100})
}