Example #1
0
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, "*****@*****.**")
}
Example #2
0
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))
}
Example #3
0
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"), "*****@*****.**")
}
Example #4
0
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")
}
Example #5
0
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)
	}
}
Example #6
0
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)
}
Example #7
0
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)
}
Example #8
0
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"))
}
Example #9
0
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})
}
Example #10
0
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)
}
Example #11
0
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))
}
Example #12
0
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})
	}
}
Example #13
0
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")
	}
}
Example #14
0
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)
	}
}
Example #15
0
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")
}
Example #16
0
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)
}
Example #17
0
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")
}
Example #18
0
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)
}
Example #19
0
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
}
Example #20
0
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)
}
Example #21
0
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"))
}
Example #23
0
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})
	}
}
Example #24
0
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)
}
Example #25
0
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())
}
Example #26
0
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})
	}
}
Example #27
0
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())
}
Example #28
0
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())
}
Example #29
0
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())
}
Example #30
0
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"])
}