go-xorm / builder Goto Github PK

Currently, builder never quotes anything:

[10.515µs] SELECT profile_id,game_id,order FROM profile_games WHERE profile_id=? [389]

SQLITE_ERROR: near "order": syntax error (SELECT profile_id,game_id,order FROM profile_games WHERE profile_id=?)

Would you be open to making it escape columns and tables, so that instead of:

SELECT profile_id,game_id,order FROM profile_games WHERE profile_id=?

It would generate:

SELECT "profile_id","game_id","order" FROM "profile_games" WHERE "profile_id"=?

(I'm using builder with SQLite, but I believe at least MySQL & PostgreSQL both support escaping colum and table names with double quotes ("))

As usual I can work on a PR as soon as I get your 👍

395bcf3 adds sorting to the builder.Insert function, but sorts incorrectly, resulting in mangled column/value mapping (e.g. an Insert(builder.Eq{"y":0, "x":1}) may result in INSERT INTO foo (x, y) VALUES (0, 1) instead of the correct (1, 0)).

Due to how the "sort" package works, Swap() and Less() are called interleaved - using sort.Slice() where the Less() function uses the original order of the slice won't work - it needs to use the current order.

See https://play.golang.org/p/ITcqKVBvdbW for an example of how this goes wrong - both keys and values are in the same order to start with, but due to Less() using the original order, the sorting puts the values slice in a semi-random order dependent on the original order of the keys slice. Uncommenting line 26, i.e. keeping the keys and values slices in the same order as the sorting algorithm progresses, fixes the sort (and makes the sort.Ints() call redundant as a bonus!)

So the following code:

b := builder.Select("*").From("day").Where(builder.Eq{"symbol": ticker}).And(builder.Gt{"date": dates[0]})
query, args, err := b.ToSQL()

produces the following sql: SELECT * FROM day WHERE symbol=? AND date>?, to which Postgres responds operator does not exist: character varying =?.

When I rewrite the query to SELECT * FROM day WHERE symbol= ? AND date> ? (spaces before the ?'s), the error changes to syntax error at or near "AND".

However, if I rewrite the query using positional parameters as SELECT * FROM day WHERE symbol = $1 AND date > $2, it works.

It would be nice to have a "Group by" support. Seems simple enough.

When outputting SQL for debugging( using Builder.ToSQL ) before executing SQL, only LIMIT and OFFSET conditions were lost.

I looked up and found the following sentence. Why does builder erase b.limitation ?

Cond interface needs iterator to edit the conditions.

Suppose ID=10 is already given. If in some cases, we might change the ID=15. But Now we have to create new condition.

Struct to sql query for create table and drop table

代码：
name := "cat;sleep(1)"
build := builder.MySQL()
sql, err := build.Select("*").From(table).Where(builder.Cond(builder.Eq{"name": name})).ToBoundSQL()

生成的sql语句是：SELECT * FROM t_tmp_info WHERE name='cat;sleep(1)'
并没有将sleep(1)过滤掉

I write example code

	sql, args, err1 := builder.Dialect(builder.MYSQL).
		Insert(builder.Eq{"issue_id": 1, "username": "vit1251", "body": "Hello"}).Into("comment").
		ToSQL()
	log.Printf("sql = %v", sql)
	log.Printf("args = %v", args)
	log.Printf("err1 = %v", err1)

my output is

sql = INSERT INTO comment (body,issue_id,username) Values (?,?,?)
args = [1 Hello vit1251]
err1 = <nil>

As you can see insert order is changes and as result have wrong mapping on insert.

I try this code,

sql, args, _ := builder.Select("*").
		From("user").
		Where(builder.Eq{"uid": 1}).
		ToSQL()
res, err := orm.QueryInterface(sql, args)

but it goes wrong ,"sql: converting argument $1 type: unsupported type []interface {}, a slice of interface"

I write some builder SELECT request:

var users []Users
query := builder.Select().From().LeftJoin().Where().Order().Limit()
sql, args, err1 := query.ToSQL()
qr := session.SQL(sql, args...)
err2 := qr.Find(&users)

and receive in err1 message "sql: converting argument $1 type: unsupported type []interface {}, a slice of interface".

You fix in #9 and make possible use with Query() but result of query provide []map[string]interface{} and it require additional effort to populate struct.

These insert statements are generated with go-xorm/builder:

[37.61µs] INSERT INTO honors (title,id) Values (?,?) [Best Picture 0]
[37.67µs] INSERT INTO honors (id,title) Values (?,?) [1 Best Supporting Actor]
[11.215µs] INSERT INTO honors (id,title) Values (?,?) [2 Best Muffins]
[8.328µs] INSERT INTO honors (id,title) Values (?,?) [3 Second Best Muffins]

As you can see, sometimes it generates (title, id), and sometimes it generates (id, title). This is problematic in my usecase, all statements are prepared (and cached by their SQL string).

The reason this happens is because builder.Insert takes a builder.Eq, which is a golang map, and map iteration order in golang is random (by design).

A possible solution would be to sort the keys, so we get consistent ordering (at a slight performance cost).

Would you be open to this change? I'll happily open a PR once I get your 👍 !

Expr seems not good enough..

subb := builder.Select("id").From("table_b").Where(builder.And(builder.Eq{"b": "a"}))
b := builder.Select("a, b").From("table_a").Where(
    builder.And(builder.Eq{
        "id":   23,
        "b_id": subb,
    }),
)
sql, args, err := b.ToSQL()
// args will contain the args from subquery:
//  subsql, subargs, err := subb.ToSQL()
//  args = append(args, subargs...)

fmt.Println(sql)
fmt.Println(args)
fmt.Println(err)

Expected output:

SELECT a, b FROM table_a WHERE id=? AND b_id=(SELECT id FROM table_b WHERE b=?)
[23 "a"]
<nil>

mysql:
select * from user where name regexp '^tom';
postgresql:
select * from user where name ~* '^[a-h]'