didi / gendry Goto Github PK

如果一个查询具有非常多条件会出现大量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=?

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

如下结构体：

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的

_orderby 是不是只支持单个排序字段呢？

我尝试这样使用

where := map[string]interface{}{
		"_orderby": "state,index_num asc",
}

出现了错误
[builder] the value of _orderby should be "field direction"

• 使用1.1.1版本正常，使用1.3.0版本报错，移除 _orderby 排序则正常。
• 报错信息：

$ 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

测试代码：https://github.com/dewuzeng/gendry-demo

因为开发过程中使用到了mysql的&运算
eg:select * from table where id & ?
请问builder.BuildSelect可以增加&运算符吗？

正打算基于这个做一个 sql2mongo,和 sql2es 的东西。 统一 mysql,mongo,es 三中数据库，屏蔽底层数据库。 前端只要传 map 就能实现 单表 万能查询。 请问这种东西 滴滴 是否 已经有了?

想知道可以把数据库字端和数据一一对应的返回吗？
目前看好像就是返回数据，我们自己解析到struct上

之前尝试过sqlx + sql builder，突然发现gendry后感觉眼前一亮，以下是一些个人建议

• 简化struct ddb tag
  目前每个Field都需要定义ddb，略显繁琐，可以在manager中增加设置，根据Field的大小写自动生成struct tag

// style: 'snakeCase' or 'camelCase'
manager.setSetTagNameStyle(style string)

• 增加debug mode
  调试时可在控制台查看完整的sql语句

// enable: true or false
manager.enableDebugMode(enable bool)

resolveDataFromRows中调用了rows.Next()来获取数据，当数据遍历完成后，该方法会自动调用rows.Close()

example

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.

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

• NullString: https://golang.org/pkg/database/sql/#NullString

目前看上去并不支持这些类型，错误信息如下，是否能够支持这些特殊类型？

scanner.ScanErr{structName:"config", fieldName:"name", from:(*reflect.rtype)(0x7c5b00), to:(*reflect.rtype)(0x812b40)}

最近项目更新依赖后(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()引起此问题

gendry有没有微信群 哈哈哈

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);

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)。想问下，当初你们这样设计是出于什么考虑呢

I konw "not in" is ineffective, it cant optimized by index.
But I really want to use "not in".

简单点就是Open(true)会创建一个脱缰的野马，没有 max life time...，默认交互式超时为：30S，没有意外你会遇到 invalid connection

建议
1、Open(false)
2、SetConnMaxLifetime
3、Ping()

利用cli tools工具生成的代码中Insert函数中 return nil 和返回参数int64不一致 语法检测错误

how to scan mysql Date_Time into golang time.TIME

CreateTime is int which is not AssignableBy []uint8

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 {{ .

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".

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.

优化一下，可以取倒数第一个空格做分割，否则就限制了key+op只能带一个空格

报错信息：
gopkg.in/DATA-DOG/[email protected]: invalid version: unknown revision 000000000000

$ 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?

举例来说:
每个应用可维护一个数据库 slave 列表,当某个从库宕机,会自动迁移到别的从库,当该从库恢复,可自动恢复到列表中?

这是测试代码
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之前和之后都没有正常显示字符串，请问是什么原因呢

如果使用结构体解析读取的数据就可以正常显示字符串

类似ORM里面loadRelated方法那样，gendry是否可以加载外键字段关联表的数据查询？或者这块使用方式有啥建议吗？

is null 和 is not null 这样的应该怎样使用？

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:

Don't mind about the format, just share it with us!

为了让gendry更好的升级和维护，我们想要知道有多少项目正在使用它。如果你在公司项目或者个人项目中使用了gendry，请告诉我们，同时你对gendry的建议对我们来说也很重要！如果我们能收集一份长长的用户列表，也会使项目更具有说服力，吸引更多用户。

目前对于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"},
}

的条件语句可能会更加的容易理解

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"

func main() {
	where := map[string]interface{}{
		"_orderby": "I_ID DESC",
	}
	qry, arg, _ := builder.BuildSelect("b_dealers", where, []string{"I_ID"})
	fmt.Println(qry, arg)
       // SELECT I_ID FROM b_dealers ORDER BY I_ID DESC []
}

compare to raw sql.
there is SQL Injection Attack in "_orderby".

The SQL I want to build:

SELECT `foo` FROM `bar` ORDER BY RAND() LIMIT 1;

How did I do using 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?

文档里面没有找到is null 条件的用法呢

如：
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)

怎么写?

Support transaction ?

bool type is saved as tinyint(1) in mysql, and I got error when scan tinyint(1) into a bool varaible

• 使用1.1.1版本正常，使用1.3.0版本报错，移除 _orderby 排序则正常。
• 报错信息：

$ 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

测试代码：https://github.com/dewuzeng/gendry-demo

表结构如下：
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是数据库关键字，造成报错，建议在字段前后使用``包裹

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...)