Yesql 解析一个 SQL 文件，提取出查询语句，自动生成对应的 Go 结构体，实现查询语句与代码分离，方便编写数据库查询逻辑。
安装
go get github.com/alimy/yesql
使用
创建sql文件
-- sql file yesql.sql
-- name: newest_tags@topic
-- get newest tag information
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 ?;
-- name: hot_tags@topic
-- get get host tag information
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 ?;
-- name: tags_by_keyword_a@topic
-- get tags by keyword
SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 ORDER BY quote_num DESC LIMIT 6;
-- name: tags_by_keyword_b@topic
SELECT id, user_id, tag, quote_num FROM @tag WHERE is_del = 0 AND tag LIKE ? ORDER BY quote_num DESC LIMIT 6;
-- name: insert_tag@topic
INSERT INTO @tag (user_id, tag, created_on, modified_on, quote_num) VALUES (?, ?, ?, ?, 1);
-- name: tags_by_id_a@topic
-- clause: in
SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0;
-- name: tags_by_id_b@topic
-- clause: in
SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?);
-- name: decr_tags_by_id@topic
-- clause: in
UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?);
-- name: tags_for_incr@topic
-- clause: in
SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?);
-- name: incr_tags_by_id@topic
-- clause: in
UPDATE @tag SET quote_num=quote_num+1, is_del=0, modified_on=? WHERE id IN (?);
使用Scan模式（方式一）
// file: topics.go
package topics
import (
"context"
"github.com/alimy/yesql"
"github.com/jmoiron/sqlx"
)
//go:embed yesql.sql
var yesqlBytes []byte
type Topic struct {
yesql.Namespace `yesql:"topic"`
DecrTagsById string `yesql:"decr_tags_by_id"`
IncrTagsById string `yesql:"incr_tags_by_id"`
TagsByIdA string `yesql:"tags_by_id_a"`
TagsByIdB string `yesql:"tags_by_id_b"`
TagsForIncr string `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"`
}
func NewTopic(db *sqlx.DB) (*Topic, error) {
// use *sqlx.DB as prepare context
yesql.UseSqlx(db)
// get sql query
query := yesql.MustParseBytes(yesqlBytes)
// scan object from sql query
obj := &Topic{}
if err := yesql.Scan(obj, query); err != nil {
return nil, err
}
return obj, nil
}
使用代码生成模式（方式二）
- 编写代码生成逻辑
/ file: gen.go
package main
import (
"log"
"github.com/alimy/yesql"
)
//go:generate go run $GOFILE
func main() {
log.Println("[Yesql] generate code start")
if err := 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.go
2023/03/31 19:34:44 [Yesql] generate code start
2023/03/31 19:34:44 [Yesql] generate code finish
- 生成代码如下（生成文件路径：auto/yesql.go）
// Code generated by Yesql. DO NOT EDIT.
// versions:
// - Yesql v1.1.2
package yesql
import (
"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 ?`
)
type Topic struct {
yesql.Namespace `yesql:"topic"`
DecrTagsById string `yesql:"decr_tags_by_id"`
IncrTagsById string `yesql:"incr_tags_by_id"`
TagsByIdA string `yesql:"tags_by_id_a"`
TagsByIdB string `yesql:"tags_by_id_b"`
TagsForIncr string `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"`
}
func BuildTopic(p yesql.PreparexBuilder, ctx ...context.Context) (obj *Topic, err error) {
var c context.Context
if len(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),
}
if obj.HotTags, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_HotTags_Topic))); err != nil {
return
}
if obj.InsertTag, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_InsertTag_Topic))); err != nil {
return
}
if obj.NewestTags, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_NewestTags_Topic))); err != nil {
return
}
if obj.TagsByKeywordA, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_TagsByKeywordA_Topic))); err != nil {
return
}
if obj.TagsByKeywordB, err = p.PreparexContext(c, p.Rebind(p.QueryHook(_TagsByKeywordB_Topic))); err != nil {
return
}
return
}
评论