Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up

Yesql parse SQL files with multiple named queries and automatically prepare and scan them into structs.

License

NotificationsYou must be signed in to change notification settings

alimy/yesql

Repository files navigation

GoGoDocSourcegraph

Logo

Yesql

Yesql解析一个SQL文件,提取出查询语句,自动生成对应的Go结构体,实现查询语句与代码分离,方便编写数据库查询逻辑。

SQL解析核心基于knadh/goyesql,但是采用了不同的使用方式与接口定义。

安装

$ go get github.com/alimy/yesql

使用

创建sql文件

-- sql file yesql.sql-- name: newest_tags@topic-- get newest tag informationSELECTt.id id,t.user_id user_id,t.tag tag,t.quote_num quote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM @tag tJOIN @user uONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDER BYt.idDESCLIMIT ? OFFSET ?;-- name: hot_tags@topic-- get get host tag informationSELECTt.id id,t.user_id user_id,t.tag tag,t.quote_num quote_num,u.id,u.nickname,u.username,u.status,u.avatar,u.is_adminFROM @tag tJOIN @user uONt.user_id=u.idWHEREt.is_del=0ANDt.quote_num>0ORDER BYt.quote_numDESCLIMIT ? OFFSET ?;-- name: tags_by_keyword_a@topic-- get tags by keywordSELECT id, user_id, tag, quote_numFROM @tagWHERE is_del=0ORDER BY quote_numDESCLIMIT6;-- name: tags_by_keyword_b@topicSELECT id, user_id, tag, quote_numFROM @tagWHERE is_del=0AND tagLIKE ?ORDER BY quote_numDESCLIMIT6;-- name: insert_tag@topicINSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num)VALUES (?, ?, ?, ?,1);-- name: tags_by_id_a@topic-- prepare: raw-- clause: inSELECT idFROM @tagWHERE idIN (?)AND is_del=0AND quote_num>0;-- name: tags_by_id_b@topic-- prepare: raw-- clause: inSELECT id, user_id, tag, quote_numFROM @tagWHERE idIN (?);-- name: decr_tags_by_id@topic-- prepare: raw-- clause: inUPDATE @tagSET quote_num=quote_num-1, modified_on=?WHERE idIN (?);-- name: tags_for_incr@topic-- prepare: raw-- clause: inSELECT id, user_id, tag, quote_numFROM @tagWHERE tagIN (?);-- name: incr_tags_by_id@topic-- prepare: raw-- clause: inUPDATE @tagSET quote_num=quote_num+1, is_del=0, modified_on=?WHERE idIN (?);

使用Scan模式(方式一)

// file: topics.gopackage topicsimport ("context"_"embed""github.com/alimy/yesql""github.com/jmoiron/sqlx")//go:embed yesql.sqlvaryesqlBytes []bytetypeTopicstruct {yesql.Namespace`yesql:"topic"`DecrTagsByIdstring`yesql:"decr_tags_by_id"`IncrTagsByIdstring`yesql:"incr_tags_by_id"`TagsByIdAstring`yesql:"tags_by_id_a"`TagsByIdBstring`yesql:"tags_by_id_b"`TagsForIncrstring`yesql:"tags_for_incr"`HotTags*sqlx.Stmt`yesql:"hot_tags"`InsertTag*sqlx.Stmt`yesql:"insert_tag"`NewestTags*sqlx.Stmt`yesql:"newest_tags"`TagsByKeywordA*sqlx.Stmt`yesql:"tags_by_keyword_a"`TagsByKeywordB*sqlx.Stmt`yesql:"tags_by_keyword_b"`}funcNewTopic(db*sqlx.DB) (*Topic,error) {// use *sqlx.DB as prepare contextyesql.UseSqlx(db)// get sql queryquery:=yesql.MustParseBytes(yesqlBytes)// scan object from sql queryobj:=&Topic{}iferr:=yesql.Scan(obj,query);err!=nil {returnnil,err}returnobj,nil}

使用代码生成模式(方式二)

  • 编写代码生成逻辑
// file: gen.gopackage mainimport ("log""strings""github.com/alimy/yesql")//go:generate go run $GOFILEfuncmain() {log.Println("[Yesql] generate code start")yesql.SetDefaultQueryHook(func(query*yesql.Query) (*yesql.Query,error) {query.Query=strings.TrimRight(query.Query,";")returnquery,nil})iferr:=yesql.Generate("yesql.sql","auto","yesql");err!=nil {log.Fatalf("generate code occurs error: %s",err)}log.Println("[Yesql] generate code finish")}
  • 自动生成Go代码
% go generate gen.go2023/03/31 19:34:44 [Yesql] generate code start2023/03/31 19:34:44 [Yesql] generate code finish
  • 生成的代码如下(生成文件路径: auto/yesql.go)
// Code generated by Yesql. DO NOT EDIT.// versions:// - Yesql v1.1.2package yesqlimport ("context""github.com/alimy/yesql""github.com/jmoiron/sqlx")const (_TagsByKeywordB_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 AND tag LIKE ? ORDER BY quote_num DESC LIMIT 6`_InsertTag_Topic=`INSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num) VALUES (?, ?, ?, ?, 1)`_TagsByIdA_Topic=`SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0`_TagsByIdB_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?)`_TagsForIncr_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?)`_IncrTagsById_Topic=`UPDATE @tag SET quote_num=quote_num+1, is_del=0, modified_on=? WHERE id IN (?)`_NewestTags_Topic=`SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.id DESC LIMIT ? OFFSET ?`_TagsByKeywordA_Topic=`SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 ORDER BY quote_num DESC LIMIT 6`_DecrTagsById_Topic=`UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?)`_HotTags_Topic=`SELECT t.id id, t.user_id user_id, t.tag tag, t.quote_num quote_num, u.id, u.nickname, u.username, u.status, u.avatar, u.is_admin FROM @tag t JOIN @user u ON t.user_id = u.id WHERE t.is_del = 0 AND t.quote_num > 0 ORDER BY t.quote_num DESC LIMIT ? OFFSET ?`)typeTopicstruct {yesql.Namespace`yesql:"topic"`DecrTagsByIdstring`yesql:"decr_tags_by_id"`IncrTagsByIdstring`yesql:"incr_tags_by_id"`TagsByIdAstring`yesql:"tags_by_id_a"`TagsByIdBstring`yesql:"tags_by_id_b"`TagsForIncrstring`yesql:"tags_for_incr"`HotTags*sqlx.Stmt`yesql:"hot_tags"`InsertTag*sqlx.Stmt`yesql:"insert_tag"`NewestTags*sqlx.Stmt`yesql:"newest_tags"`TagsByKeywordA*sqlx.Stmt`yesql:"tags_by_keyword_a"`TagsByKeywordB*sqlx.Stmt`yesql:"tags_by_keyword_b"`}funcBuildTopic(p yesql.PreparexBuilder,ctx...context.Context) (obj*Topic,errerror) {varc context.Contextiflen(ctx)>0&&ctx[0]!=nil {c=ctx[0]}else {c=context.Background()}obj=&Topic{DecrTagsById:p.QueryHook(_DecrTagsById_Topic),IncrTagsById:p.QueryHook(_IncrTagsById_Topic),TagsByIdA:p.QueryHook(_TagsByIdA_Topic),TagsByIdB:p.QueryHook(_TagsByIdB_Topic),TagsForIncr:p.QueryHook(_TagsForIncr_Topic),}ifobj.HotTags,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_HotTags_Topic)));err!=nil {return}ifobj.InsertTag,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_InsertTag_Topic)));err!=nil {return}ifobj.NewestTags,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_NewestTags_Topic)));err!=nil {return}ifobj.TagsByKeywordA,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_TagsByKeywordA_Topic)));err!=nil {return}ifobj.TagsByKeywordB,err=p.PreparexContext(c,p.Rebind(p.QueryHook(_TagsByKeywordB_Topic)));err!=nil {return}return}

使用Yesql 的项目

About

Yesql parse SQL files with multiple named queries and automatically prepare and scan them into structs.

Topics

Resources

License

Stars

Watchers

Forks


[8]ページ先頭

©2009-2025 Movatter.jp