> 科技资讯 > Yesql v1.1.5 发布了,从 SQL 文件自动生成 Go 结构体代码,实现查询语句与代码分离

Yesql v1.1.5 发布了,从 SQL 文件自动生成 Go 结构体代码,实现查询语句与代码分离

科技资讯 投稿 6800℃ 0 评论

Yesql v1.1.5 发布了,从 SQL 文件自动生成 Go 结构体代码,实现查询语句与代码分离

​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" _ "embed" "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
}

使用 Yesql 的项目

    examples - 项目自带examples
  • paopao-ce - 一个清新文艺的微社区

编程笔记 » Yesql v1.1.5 发布了,从 SQL 文件自动生成 Go 结构体代码,实现查询语句与代码分离

赞同 (34) or 分享 (0)
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽