func TestUpdateReal(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var id int64
// Insert a George
s.InsertInto("people").Columns("name", "email").
Values("George", "*****@*****.**").
Returning("id").
QueryScalar(&id)
// Rename our George to Barack
_, err := s.Update("people").SetMap(map[string]interface{}{"name": "Barack", "email": "*****@*****.**"}).Where("id = $1", id).Exec()
assert.NoError(t, err)
var person Person
err = s.Select("*").From("people").Where("id = $1", id).QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.ID, id)
assert.Equal(t, person.Name, "Barack")
assert.Equal(t, person.Email.Valid, true)
assert.Equal(t, person.Email.String, "*****@*****.**")
}
func TestInterpolateJSON(t *testing.T) {
j, _ := NewJSON([]int{1, 3, 10})
sql, args, err := Interpolate("SELECT $1", []interface{}{j})
assert.NoError(t, err)
assert.Equal(t, "SELECT '[1,3,10]'", sql)
assert.Equal(t, 0, len(args))
}
func TestQueryObject(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var people jo.Object
err := s.
Select("id", "name", "email").
From("people").
OrderBy("id ASC").
QueryObject(&people)
assert.NoError(t, err)
assert.Equal(t, len(people.AsSlice(".")), 6)
// Make sure that the Ids are set. It's possible (maybe?) that different DBs set ids differently so
// don't assume they're 1 and 2.
assert.True(t, people.MustInt64("[0].id") > 0)
assert.True(t, people.MustInt64("[1].id") > people.MustInt64("[0].id"))
mario, _ := people.At("[0]")
john, _ := people.At("[1]")
assert.Equal(t, mario.MustString("name"), "Mario")
assert.Equal(t, mario.MustString("email"), "*****@*****.**")
assert.Equal(t, john.MustString("name"), "John")
assert.Equal(t, john.MustString("email"), "*****@*****.**")
}
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 TestSQLInjectionBuilder(t *testing.T) {
for _, fuzz := range strings.Split(fuzzList, "\n") {
if fuzz == "" {
continue
}
fuzz = strings.Trim(fuzz, " \t")
var id int64
var comment string
err := testDB.
InsertInto("comments").
Columns("comment").
Values(fuzz).
SetIsInterpolated(true).
Returning("id", "comment").
QueryScalar(&id, &comment)
assert.True(t, id > 0)
assert.Equal(t, fuzz, comment)
var result int
err = testDB.SQL(`
SELECT 42
FROM comments
WHERE id = $1 AND comment = $2
`, id, comment).QueryScalar(&result)
assert.NoError(t, err)
assert.Equal(t, 42, result)
}
}
func TestUpsertSQLRecord(t *testing.T) {
var rec = struct {
B int `db:"b"`
C int `db:"c"`
}{1, 2}
sql, args := Upsert("tab").
Columns("b", "c").
Record(rec).
Where("d=$1", 4).
Returning("f", "g").
ToSQL()
expected := `
WITH
upd AS (
UPDATE "tab"
SET "b" = $1, "c" = $2
WHERE (d=$3)
RETURNING "f","g"
), ins AS (
INSERT INTO "tab"("b","c")
SELECT $1,$2
WHERE NOT EXISTS (SELECT 1 FROM upd)
RETURNING "f","g"
)
SELECT * FROM ins UNION ALL SELECT * FROM upd
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Equal(t, []interface{}{1, 2, 4}, args)
}
func TestSelectDocRow(t *testing.T) {
assert := assert.New(t)
type Post struct {
ID int
Title string
}
type Person struct {
ID int
Name string
Posts []*Post
}
var person Person
err := testDB.
SelectDoc("id", "name").
Many("posts", `SELECT id, title FROM posts WHERE user_id = people.id`).
From("people").
Where("id = $1", 1).
QueryStruct(&person)
assert.NoError(err)
assert.Equal("Mario", person.Name)
assert.Equal(1, person.ID)
assert.Equal(2, len(person.Posts))
assert.Equal("Day 1", person.Posts[0].Title)
assert.Equal("Day 2", person.Posts[1].Title)
}
func TestSelectDocNested(t *testing.T) {
assert := assert.New(t)
var obj jo.Object
posts := dat.SelectDoc("id", "title").
Many("comments", `SELECT * FROM comments WHERE comments.id = posts.id`).
From("posts").
Where("user_id = people.id")
err := testDB.
SelectDoc("id", "name").
Many("posts", posts).
From("people").
Where("id = $1", 1).
SetIsInterpolated(true).
QueryStruct(&obj)
assert.NoError(err)
assert.Equal("Mario", obj.AsString("name"))
assert.Equal(1, obj.AsInt("id"))
assert.Equal("A very good day", obj.AsString("posts[0].comments[0].comment"))
assert.Equal("Yum. Apple pie.", obj.AsString("posts[1].comments[0].comment"))
}
func TestInsectSqlRecord(t *testing.T) {
var rec = struct {
B int
C int
}{1, 2}
sql, args := Insect("tab").
Columns("b", "c").
Record(rec).
Where("d = $1", 3).
Returning("id", "f", "g").
ToSQL()
expected := `
WITH
sel AS (SELECT id, f, g FROM tab WHERE (d = $1)),
ins AS (
INSERT INTO "tab"("b","c")
SELECT $2,$3
WHERE NOT EXISTS (SELECT 1 FROM sel)
RETURNING "id","f","g"
)
SELECT * FROM ins UNION ALL SELECT * FROM sel
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Equal(t, args, []interface{}{3, 1, 2})
}
func TestInsertDoubleDollarQuote(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
expected := common.RandomString(16)
var str string
err := s.
InsertInto("people").
Columns("name", "key").
Values("test", expected).
Returning("key").
QueryScalar(&str)
assert.NoError(t, err)
assert.Equal(t, expected, str)
// ensure the tag cannot be escaped by user
oldDollarTag := postgres.GetPgDollarTag()
expected = common.RandomString(1024) + "'" + oldDollarTag
builder := s.
InsertInto("people").
Columns("name", "key").
Values("test", expected).
Returning("key")
sql, _, _ := builder.SetIsInterpolated(true).Interpolate()
assert.NotEqual(t, oldDollarTag, postgres.GetPgDollarTag())
assert.True(t, strings.Contains(sql, postgres.GetPgDollarTag()))
builder.QueryScalar(&str)
assert.NoError(t, err)
assert.Equal(t, expected, str)
}
func TestLegacyIn(t *testing.T) {
var cat = "cat"
if isWindows {
cat = "cmd /c type"
}
//// Run
// in V2 BashOutput accepts an options map
out, err := RunOutput(cat+" foo.txt", M{"$in": "test"})
assert.NoError(t, err)
assert.Equal(t, "foo", str.Clean(out))
if isWindows {
return
}
//// Bash
// in V2 BashOutput accepts an options map
out, err = BashOutput("cat foo.txt", M{"$in": "test"})
assert.NoError(t, err)
assert.Equal(t, "foo", str.Clean(out))
}
func TestCacheSelectQuerySliceByHash(t *testing.T) {
Cache.FlushDB()
for i := 0; i < 2; i++ {
var names []string
err := testDB.
Select("name").
From("people").
Cache("", 1*time.Second, false).
QuerySlice(&names)
assert.NoError(t, err)
assert.Equal(t, len(names), 6)
assert.Equal(t, names, []string{"Mario", "John", "Grant", "Tony", "Ester", "Reggie"})
var ids []int64
err = testDB.
Select("id").
From("people").
Limit(1).
Cache("", 1*time.Second, false).
QuerySlice(&ids)
assert.NoError(t, err)
assert.Equal(t, len(ids), 1)
assert.Equal(t, ids, []int64{1})
}
}
func TestCacheSelectQueryStruct(t *testing.T) {
Cache.FlushDB()
for i := 0; i < 2; i++ {
// Found:
var person Person
err := testDB.
Select("id", "name", "email").
From("people").
Where("email = $1", "*****@*****.**").
Cache("selectdoc.5", 1*time.Second, false).
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
assert.Equal(t, person.Name, "John")
assert.True(t, person.Email.Valid)
assert.Equal(t, person.Email.String, "*****@*****.**")
// Not found:
var person2 Person
err = testDB.
Select("id", "name", "email").
From("people").Where("email = $1", "*****@*****.**").
Cache("selectdoc.6", 1*time.Second, false).
QueryStruct(&person2)
assert.Contains(t, err.Error(), "no rows")
}
}
func TestSQLInjectionSQL(t *testing.T) {
for _, fuzz := range strings.Split(fuzzList, "\n") {
if fuzz == "" {
continue
}
fuzz = strings.Trim(fuzz, " \t")
var id int64
var comment string
err := testDB.
SQL(`
INSERT INTO comments (comment)
VALUES ($1)
RETURNING id, comment
`, fuzz).
SetIsInterpolated(true).
QueryScalar(&id, &comment)
assert.True(t, id > 0)
assert.Equal(t, fuzz, comment)
var result int
err = testDB.SQL(`
SELECT 42
FROM comments
WHERE id = $1 AND comment = $2
`, id, comment).QueryScalar(&result)
assert.NoError(t, err)
assert.Equal(t, 42, result)
}
}
func TestUpdateBlacklist(t *testing.T) {
installFixtures()
// Insert by specifying a record (struct)
p := Person{Name: "Barack"}
p.Foo = "bar"
var foo string
var name string
var id int64
err := testDB.
InsertInto("people").
Whitelist("name", "foo").
Record(p).
Returning("id", "name", "foo").
QueryScalar(&id, &name, &foo)
assert.NoError(t, err)
assert.True(t, id > 0)
assert.Equal(t, name, "Barack")
assert.Equal(t, foo, "bar")
p2 := Person{Name: "oy"}
p2.Foo = "bah"
var name2 string
var foo2 string
err = testDB.
Update("people").
SetBlacklist(p2, "id", "name", "email", "key", "doc", "created_at").
Where("id = $1", id).
Returning("name", "foo").
QueryScalar(&name2, &foo2)
assert.NoError(t, err)
assert.True(t, id > 0)
assert.Equal(t, name2, "Barack")
assert.Equal(t, foo2, "bah")
}
func TestTransactionReal(t *testing.T) {
installFixtures()
tx, err := testDB.Begin()
assert.NoError(t, err)
var id int64
tx.InsertInto("people").Columns("name", "email").
Values("Barack", "*****@*****.**").
Returning("id").
QueryScalar(&id)
assert.True(t, id > 0)
var person Person
err = tx.
Select("*").
From("people").
Where("id = $1", id).
QueryStruct(&person)
assert.NoError(t, err)
assert.Equal(t, person.ID, id)
assert.Equal(t, person.Name, "Barack")
assert.Equal(t, person.Email.Valid, true)
assert.Equal(t, person.Email.String, "*****@*****.**")
err = tx.Commit()
assert.NoError(t, err)
}
func TestSelectQueryStruct(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
// Found:
var person Person
err := s.
Select("id", "name", "email").
From("people").
Where("email = $1", "*****@*****.**").
QueryStruct(&person)
assert.NoError(t, err)
assert.True(t, person.ID > 0)
assert.Equal(t, person.Name, "John")
assert.True(t, person.Email.Valid)
assert.Equal(t, person.Email.String, "*****@*****.**")
// Not found:
var person2 Person
err = s.
Select("id", "name", "email").
From("people").Where("email = $1", "*****@*****.**").
QueryStruct(&person2)
assert.Contains(t, err.Error(), "no rows")
}
func TestSelectDocSQLInnerSQL(t *testing.T) {
sql, args := SelectDoc("b", "c").
Many("f", `SELECT g, h FROM f WHERE id= $1`, 4).
Many("x", `SELECT id, y, z FROM x`).
InnerSQL(`
FROM a
WHERE d = $1
`, 4).
ToSQL()
expected := `
SELECT row_to_json(dat__item.*)
FROM (
SELECT
b,
c,
(SELECT array_agg(dat__f.*) FROM (SELECT g,h FROM f WHERE id=$1) AS dat__f) AS "f",
(SELECT array_agg(dat__x.*) FROM (SELECT id,y,z FROM x) AS dat__x) AS "x"
FROM a
WHERE d=$2
) as dat__item
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Equal(t, []interface{}{4, 4}, args)
}
func TestSelectQueryStructs(t *testing.T) {
s := beginTxWithFixtures()
defer s.AutoRollback()
var people []Person
err := s.
Select("id", "name", "email").
From("people").
OrderBy("id ASC").
QueryStructs(&people)
assert.NoError(t, err)
assert.Equal(t, len(people), 6)
// Make sure that the Ids are set. It's possible (maybe?) that different DBs set ids differently so
// don't assume they're 1 and 2.
assert.True(t, people[0].ID > 0)
assert.True(t, people[1].ID > people[0].ID)
assert.Equal(t, people[0].Name, "Mario")
assert.True(t, people[0].Email.Valid)
assert.Equal(t, people[0].Email.String, "*****@*****.**")
assert.Equal(t, people[1].Name, "John")
assert.True(t, people[1].Email.Valid)
assert.Equal(t, people[1].Email.String, "*****@*****.**")
// TODO: test map
}
func TestSelectDocScope(t *testing.T) {
now := NullTimeFrom(time.Now())
sql, args := SelectDoc("e", "f").
From("matches m").
Scope(`
WHERE m.game_id = $1
AND (
m.id > $3
OR (m.id >= $2 AND m.id <= $3 AND m.updated_at > $4)
)
`, 100, 1, 2, now).
ToSQL()
expected := `
SELECT row_to_json(dat__item.*)
FROM (
SELECT e, f
FROM matches m
WHERE (m.game_id=$1
AND (
m.id > $3
OR (m.id >= $2 AND m.id<=$3 AND m.updated_at>$4)
))
) as dat__item
`
assert.Equal(t, stripWS(expected), stripWS(sql))
assert.Equal(t, []interface{}{100, 1, 2, now}, args)
}
func TestCacheSelectQueryStructs(t *testing.T) {
Cache.FlushDB()
for i := 0; i < 2; i++ {
var people []Person
err := testDB.
Select("id", "name", "email").
From("people").
OrderBy("id ASC").
Cache("selectdoc.4", 1*time.Second, false).
QueryStructs(&people)
assert.NoError(t, err)
assert.Equal(t, len(people), 6)
// Make sure that the Ids are set. It's possible (maybe?) that different DBs set ids differently so
// don't assume they're 1 and 2.
assert.True(t, people[0].ID > 0)
assert.True(t, people[1].ID > people[0].ID)
assert.Equal(t, people[0].Name, "Mario")
assert.True(t, people[0].Email.Valid)
assert.Equal(t, people[0].Email.String, "*****@*****.**")
assert.Equal(t, people[1].Name, "John")
assert.True(t, people[1].Email.Valid)
assert.Equal(t, people[1].Email.String, "*****@*****.**")
}
}
func TestCapitalize(t *testing.T) {
assert := assert.New(t)
assert.Equal("Hello", Capitalize("hello"))
assert.Equal("Hello", Capitalize("Hello"))
assert.Equal("Hello world", Capitalize("hello world"))
assert.Equal("Èllo", Capitalize("èllo"))
}
func TestSelectWhereEqSql(t *testing.T) {
sql, args := Select("a").From("b").Where(Eq{"a": 1, "b": []int64{1, 2, 3}}).ToSQL()
if sql == quoteSQL("SELECT a FROM b WHERE (%s = $1) AND (%s IN $2)", "a", "b") {
assert.Equal(t, args, []interface{}{1, []int64{1, 2, 3}})
} else {
assert.Equal(t, sql, quoteSQL("SELECT a FROM b WHERE (%s IN $1) AND (%s = $2)", "b", "a"))
assert.Equal(t, args, []interface{}{[]int64{1, 2, 3}, 1})
}
}
func TestMockClientBadFetch(t *testing.T) {
client := NewMockClient(0, 100)
client.fetchResponseError = siesta.ErrBrokerNotAvailable
response, err := client.Fetch("test", 0, 0)
assert.Equal(t, nil, err)
assert.Len(t, response.Data, 1)
assert.Len(t, response.Data["test"], 1)
assert.Equal(t, siesta.ErrBrokerNotAvailable, response.Data["test"][0].Error)
}
func TestDirSymlinkElementRelativePath(t *testing.T) {
e := createElement(dirSymlink, pwd)
assert.True(t, e.IsExists())
assert.False(t, e.IsDir())
assert.True(t, e.IsSymLink())
assert.Equal(t, filepath.Join(pwd, dirSymlink), e.Path())
assert.Equal(t, filepath.Dir(filepath.Join(pwd, dirSymlink)), e.Dirname())
}
func TestUpdateSetMapToSql(t *testing.T) {
sql, args := Update("a").SetMap(map[string]interface{}{"b": 1, "c": 2}).Where("id = $1", 1).ToSQL()
if sql == quoteSQL(`UPDATE "a" SET %s = $1, %s = $2 WHERE (id = $3)`, "b", "c") {
assert.Equal(t, args, []interface{}{1, 2, 1})
} else {
assert.Equal(t, sql, quoteSQL(`UPDATE "a" SET %s = $1, %s = $2 WHERE (id = $3)`, "c", "b"))
assert.Equal(t, args, []interface{}{2, 1, 1})
}
}
func TestDirSymlinkElementFullPath(t *testing.T) {
fullPath := filepath.Join(pwd, dirSymlink)
e := createElement(fullPath, "/path/to/dummy")
assert.True(t, e.IsExists())
assert.False(t, e.IsDir())
assert.True(t, e.IsSymLink())
assert.Equal(t, fullPath, e.Path())
assert.Equal(t, filepath.Dir(fullPath), e.Dirname())
}
func TestNotExistsElementFullPath(t *testing.T) {
file := filepath.Join(pwd, "invalid")
e := createElement(file, "/path/to/dummy")
assert.False(t, e.IsExists())
assert.False(t, e.IsDir())
assert.False(t, e.IsSymLink())
assert.Equal(t, file, e.Path())
assert.Equal(t, pwd, e.Dirname())
}
func TestNotExistsElementRelativePath(t *testing.T) {
file := "invalid"
e := createElement(file, pwd)
assert.False(t, e.IsExists())
assert.False(t, e.IsDir())
assert.False(t, e.IsSymLink())
assert.Equal(t, filepath.Join(pwd, file), e.Path())
assert.Equal(t, pwd, e.Dirname())
}
func TestParseReader(t *testing.T) {
s := `
--@key=foo
SELECT *
FROM foo;
--@key=bar
SELECT *
FROM bar;
--@sproc
create function f_foo() as $$
begin
end; $$ language plpgsql;
`
r := bytes.NewBufferString(s)
a, err := PartitionKV(r, "--@", "=")
assert.NoError(t, err)
assert.Equal(t, "key", a[0]["_kind"])
assert.Equal(t, "SELECT *\nFROM foo;\n\n", a[0]["_body"])
assert.Equal(t, "foo", a[0]["key"])
assert.Equal(t, "key", a[1]["_kind"])
assert.Equal(t, "SELECT *\nFROM bar;\n\n", a[1]["_body"])
assert.Equal(t, "bar", a[1]["key"])
assert.Equal(t, "sproc", a[2]["_kind"])
assert.Equal(t, "create function f_foo() as $$\nbegin\nend; $$ language plpgsql;\n", a[2]["_body"])
assert.Equal(t, "", a[2]["sproc"])
}