go-xorm / builder Goto Github PK
View Code? Open in Web Editor NEWLightweight and fast SQL builder for Go language, moved to https://gitea.com/xorm/builder
License: BSD 3-Clause "New" or "Revised" License
Lightweight and fast SQL builder for Go language, moved to https://gitea.com/xorm/builder
License: BSD 3-Clause "New" or "Revised" License
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"
mysql:
select * from user where name regexp '^tom';
postgresql:
select * from user where name ~* '^[a-h]'
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
?
Line 23 in 145c996
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 👍 !
代码:
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)过滤掉
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.
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.
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.
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 👍
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.
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)
).
Lines 278 to 281 in ef80bec
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!)
Struct to sql query for create table and drop table
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>
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.