didi / gendry Goto Github PK
View Code? Open in Web Editor NEWa golang library for sql builder
License: Apache License 2.0
a golang library for sql builder
License: Apache License 2.0
Hi, I am Go beginner and confused about the parameter ctx context.Context for function AggregateQuery.
Could you please provide a example and explain why using context.Context?
Thanks.
报错信息:
gopkg.in/DATA-DOG/[email protected]: invalid version: unknown revision 000000000000
想知道可以把数据库字端和数据一一对应的返回吗?
目前看好像就是返回数据,我们自己解析到struct上
优化一下,可以取倒数第一个空格做分割,否则就限制了key+op只能带一个空格
is null 和 is not null 这样的应该怎样使用?
类似ORM里面loadRelated方法那样,gendry是否可以加载外键字段关联表的数据查询?或者这块使用方式有啥建议吗?
表结构如下:
CREATE TABLE gateway
(
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
name
varchar(64) NOT NULL DEFAULT '' COMMENT '网关名',
desc
varchar(256) NOT NULL DEFAULT '' COMMENT '描述',
deleted
tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否已删除, 0:否、1:是',
create_time
int(10) unsigned NOT NULL DEFAULT '0' COMMENT '记录添加时间',
update_time
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='网关配置表';
使用gendry拼接的sql类似这样:
[exec sql] sql: INSERT INTO gateway (create_time,deleted,desc,name) VALUES (?,?,?,?), args: [31 0 测试嘿嘿哈哈哈1053 gateway1053]
注意: 这里的desc是数据库关键字,造成报错,建议在字段前后使用``包裹
hello ,
我这边遇到一个问题,mysql的一个字段 eg: user_name
varchar(50) NOT NULL DEFAULT '', 存在数据库的值是“string(nan)”,经过"github.com/didi/gendry/scanner" 中的ScanMapDecode方法,就会变成float64=NaN。我看你里面有会先将string转为[110 97 110]uint8,然后再转出float64(NaN)。想问下,当初你们这样设计是出于什么考虑呢
最近项目更新依赖后(vendor),测试环境环境大量的panic,更新之前线上一切正常
Panic: runtime error: invalid memory address or nil pointer dereference
goroutine 33 [running]:
centerserver | runtime/debug.Stack(0x48, 0x0, 0x0)
centerserver | /usr/local/go/src/runtime/debug/stack.go:24 +0xa7
centerserver | runtime/debug.PrintStack()
centerserver | /usr/local/go/src/runtime/debug/stack.go:16 +0x22
centerserver | be/vendor/github.com/go-chi/chi/middleware.Recoverer.func1.1(0xc4200a4600, 0xc28fa0, 0xc42013c1c0)
centerserver | /go/src/be/vendor/github.com/go-chi/chi/middleware/recoverer.go:28 +0x1b6
centerserver | panic(0xa53520, 0xef3f50)
centerserver | /usr/local/go/src/runtime/panic.go:502 +0x229
centerserver | be/vendor/github.com/didi/gendry/scanner.CloseErr.Error(0x0, 0x0, 0x0, 0x0)
centerserver | /go/src/be/vendor/github.com/didi/gendry/scanner/scanner.go:117 +0x22
查看更新记录后发现ScanClose方法更新了
更新前
func ScanClose(rows Rows, target interface{}) error {
err := Scan(rows, target)
if nil != rows {
if nil == err {
err = rows.Close()
}
}
return err
}
更新后
func ScanClose(rows Rows, target interface{}) error {
err := Scan(rows, target)
if nil != rows {
errClose := rows.Close()
if err == nil { // ==> if errClose != nil {
err = newCloseErr(errClose)
}
}
return err
}
panic原因:
系统在调用ScanClose后获取返回的err, 并在后续代码中使用了err.Error()引起此问题
I konw "not in" is ineffective, it cant optimized by index.
But I really want to use "not in".
https://github.com/didi/gendry/blob/master/builder/builder.go#L394
this regex is greedy. It should be non-greedy.
for example,
"select {{name}},{{comment}},value from table"
In mysql, this is ok. but gendry will parser it as "name}},{{comment".
I must insert an space between }} and {{ .
Hi,
I can't find any support for the offset clause
select
*
from
"table"
where
age > 30
offset 20
limit 10
Do you have reasons for not supporting this or is this an omission .
Thanks
gendry
is a good tool to build SQL except for OR statements. Can we add a new operator to support it? Just like:
cond, vals, err := builder.BuildSelect(
"test",
map[string]interface{}{
"id >": 1,
"_or": map[string]interface{}{
"foo": 2,
"bar >": 3,
},
},
[]string{
"foo",
},
)
it equals:
SELECT `foo` FROM `test` WHERE `id` > 1 AND (`foo` = 2 OR `bar` > 3);
如果一个查询具有非常多条件会出现大量if判断如:
if age != 0 {
where["age"]=age
}
if name != "" {
where["name"]=name
}
查询函数冗长可读性差,能否支持如:
age := 10
name := ""
where := map[string]interface{}{
"age notnull": age,
"name notnull": name,
}
// cond = SELECT name,age FROM some_table WHERE age=?
目前对于map[string]interface{}
类型的条件拼接我觉得很不错,但是在有些需求上还不能完全满足,希望可以添加。
比如在比较高版本的mysql上已经支持在innodb引擎中建立全文索引,而对应的查询语句则类似于
SELECT * FROM table1 WHERE MATCH(somecolumns) AGAINST('keyword' IN BOOLEAN MODE) AND someCondition
在这里语句中可能需要将参数使用插值的方式插入到条件中去,而目前的SQL拼接的where map结构中还不支持以下这种方式
where := map[string]interface{}{
"MATCH(somecolumns) AGAINST(? IN BOOLEAN MODE)":"keyword",
}
由于还同时可能存在其他的普通的where条件,例如>, =
等等,无法完全使用NamedQuery
代替,所以希望能加入类似原生的sql.DB
中查询语句可以使用以?
为标记的插值参数的条件的支持
如果添加了以上需求,在构造查询条件时,类似
where := map[string]interface{}{
"name = ?":"Bob",
"age > ?":10,
"country IN ?":[]string{"china","korea","japan"},
}
的条件语句可能会更加的容易理解
如:
update set a = a+1 where id=(select max(id) from tb)
insert into tb(id,score) values(1, case when now() > 0 then 1 else 0 end)
怎么写?
正打算基于这个做一个 sql2mongo,和 sql2es 的东西。 统一 mysql,mongo,es 三中数据库,屏蔽底层数据库。 前端只要传 map 就能实现 单表 万能查询。 请问这种东西 滴滴 是否 已经有了?
如下结构体:
type ModelMeta struct {
ID int `json:"id" ddb:"id"`
Ctime time.Time `json:"ctime" ddb:"ctime"`
Mtime time.Time `json:"mtime" ddb:"mtime"`
}
type App struct {
ModelMeta
Project string `json:"project" ddb:"project"`
Name string `json:"name" ddb:"name"`
}
查询时候拿不到数据,将嵌套去掉的话则是OK的
cond, vals, err := builder.BuildInsert("testTable", data)
if nil != err {
return 0, err
}
result, err := db.Exec(cond, vals...)
最后执行 db.Exec(cond, vals...)的时候有错误产生 我用的是mysql
data:<*errors.errorString>
s:"sql: converting argument $1 type: unsupported type []map[string]...+28 more"
文档里面没有找到is null 条件的用法呢
目前看上去并不支持这些类型,错误信息如下,是否能够支持这些特殊类型?
scanner.ScanErr{structName:"config", fieldName:"name", from:(*reflect.rtype)(0x7c5b00), to:(*reflect.rtype)(0x812b40)}
mysql中加锁的语句为SELECT * FROM `test` WHERE `id` = 1 FOR UPDATE
目前gendry中的builder中没有对FOR UPDATE
进行支持,只能在构造好的sql后面拼接,如下
cond, vals, _ := builder.BuildSelect(tableName, where, nil)
cond = cond + " for update"
db.Query(cond, vals...)
希望可以有单独的方法可以直接构造for update语句,示例(如有其它更好方式也可):
cond, vals, _ := builder.BuildForUpdateSelect(tableName, where, nil)
db.Query(cond, vals...)
To make gendry better and better, we want to know how many of you used it in your project and what functions gendry could provide to ease your burden. Please tell us about it, your company name, or your personal project. A long list could make gendry more convincing and attractive. eg:
company | project | suggestion |
---|---|---|
Didi滴滴出行 | widely used in many projects | could it be faster |
personal | myproject_foo | could you support sql.NullXXX type in scanner |
Don't mind about the format, just share it with us!
为了让gendry更好的升级和维护,我们想要知道有多少项目正在使用它。如果你在公司项目或者个人项目中使用了gendry,请告诉我们,同时你对gendry的建议对我们来说也很重要!如果我们能收集一份长长的用户列表,也会使项目更具有说服力,吸引更多用户。
举例来说:
每个应用可维护一个数据库 slave 列表,当某个从库宕机,会自动迁移到别的从库,当该从库恢复,可自动恢复到列表中?
之前尝试过sqlx
+ sql builder
,突然发现gendry
后感觉眼前一亮,以下是一些个人建议
struct ddb tag
ddb
,略显繁琐,可以在manager中增加设置,根据Field的大小写自动生成struct tag
// style: 'snakeCase' or 'camelCase'
manager.setSetTagNameStyle(style string)
debug mode
// enable: true or false
manager.enableDebugMode(enable bool)
因为开发过程中使用到了mysql的&运算
eg:select * from table where id & ?
请问builder.BuildSelect
可以增加&
运算符吗?
$ go test
github.com/didi/gendry/builder [github.com/didi/gendry/builder.test]
.\utils_test.go:90:48: constant 9223372036854775807 overflows int
note: module requires Go 1.13
FAIL github.com/didi/gendry/builder [build failed]
As describe above, a panic occurs when I test the new test case. The input of AddRow is actually untyped ...interface{}
, so constant math.MaxInt64
causes a compile-time error. If AddRow(int64(math.MaxInt64))
could be better for this case?
gendry有没有微信群 哈哈哈
简单点就是Open(true)会创建一个脱缰的野马,没有 max life time...,默认交互式超时为:30S,没有意外你会遇到 invalid connection
建议
1、Open(false)
2、SetConnMaxLifetime
3、Ping()
resolveDataFromRows中调用了rows.Next()来获取数据,当数据遍历完成后,该方法会自动调用rows.Close()
for example:
where := map[string]interface{}{
"_orderby": "IFNULL(I_VALUE,0) DESC, I_ID DESC",
"_limit": []uint{0, 1},
}
query, args, err := builder.BuildSelect("src_table", where, []string{"I_ID,I_VALUE"})
if err != nil {
return
}
err = db.Select(&ret, query, args...)
in this case,gendry will split the "," in function "IFNULL".
bool type is saved as tinyint(1) in mysql, and I got error when scan tinyint(1) into a bool varaible
select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})
if 'm_score' is empty, then the sql change to
select * from tb where name={{name}}
so is there any method to take out of whole sub query when the param is null.
SELECT `foo` FROM `bar` ORDER BY RAND() LIMIT 1;
didi/gendry
:cond, vals, err := builder.BuildSelect(
"bar",
map[string]interface{}{
"_orderby": "RAND()",
"_limit": []uint{1},
},
[]string{
"foo",
},
)
Then an error occurred:
[builder] the value of _orderby should be "fieldName direction [,fieldName direction]
As stated in Mysql SELECT Statement :
SELECT
...
[ORDER BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
not only fieldName direction [,fieldName direction]
can be followed after ORDER BY
, so should builder
remove the syntax restriction of operator _orderby
or just add a new operator which permits users to use some built-in (native) functions?
$ go run main.go
panic: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
这是测试代码
db, err = manager.New(dbName, userName, password, ip).Set(
manager.SetCharset("utf8"),
manager.SetAllowCleartextPasswords(true),
manager.SetInterpolateParams(true),
manager.SetTimeout(1time.Second),
manager.SetReadTimeout(1time.Second)).Open(true)
if err != nil {
fmt.Printf("error=%v\n", err)
} else {
cond, _, _ := builder.BuildSelect("test_table", nil, nil)
if db != nil {
rows, _ := db.Query(cond)
resultMap, _ := scanner.ScanMapClose(rows)
for _,record := range resultMap {
fmt.Printf("record==%v \n", record["name"])
}
data, _ := json2.Marshal(resultMap)
fmt.Printf("data==%v \n", string(data))
} else {
fmt.Printf("db=%v\n", db)
}
}
这是打印结果
record==[229 130 178 231 132 182]
record==[229 133 182 228 187 150]
data==[{"age":"MA==","id":"MQ==","name":"5YKy54S2","sex":"YQ=="},{"age":"MQ==","id":"Mg==","name":"5YW25LuW","sex":"Yg=="}]
转Json之前和之后都没有正常显示字符串,请问是什么原因呢
如果使用结构体解析读取的数据就可以正常显示字符串
when sql like this
SELECT count(*) FROM tb1
the result in rows.Scan will be []uint8, not just int or float, so ResultResolver will fail, and the count will be zero.
But add the where clause fix that.
SELECT count(*) FROM tb1 where 1 == 1
how to scan mysql Date_Time into golang time.TIME
CreateTime is int which is not AssignableBy []uint8
Support transaction ?
_orderby 是不是只支持单个排序字段呢?
我尝试这样使用
where := map[string]interface{}{
"_orderby": "state,index_num asc",
}
出现了错误
[builder] the value of _orderby should be "field direction"
$ go run main.go
panic: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
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.