yesql

package module
v1.9.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 23, 2023 License: BSD-2-Clause Imports: 17 Imported by: 4

README

Yesql

Go GoDoc Sourcegraph

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 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
-- prepare: raw
-- clause: in
SELECT id FROM @tag WHERE id IN (?) AND is_del = 0 AND quote_num > 0;

-- name: tags_by_id_b@topic
-- prepare: raw
-- clause: in
SELECT id, user_id, tag, quote_num FROM @tag WHERE id IN (?);

-- name: decr_tags_by_id@topic
-- prepare: raw
-- clause: in
UPDATE @tag SET quote_num=quote_num-1, modified_on=? WHERE id IN (?);

-- name: tags_for_incr@topic
-- prepare: raw
-- clause: in
SELECT id, user_id, tag, quote_num FROM @tag WHERE tag IN (?);

-- name: incr_tags_by_id@topic
-- prepare: raw
-- 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"
	"strings"

	"github.com/alimy/yesql"
)

//go:generate go run $GOFILE
func main() {
	log.Println("[Yesql] generate code start")
	yesql.SetDefaultQueryHook(func(query *yesql.Query) (*yesql.Query, error) {
		query.Query = strings.TrimRight(query.Query, ";")
		return query, nil
	})
	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 的项目

Documentation

Overview

Package yesql is a Go port of Yesql

It allows you to write SQL queries in separate files. See rationale at https://github.com/krisajenkins/yesql#rationale

Index

Constants

View Source 
const (
	PrepareStyleStmt      = "stmt"
	PrepareStyleNamedStmt = "named_stmt"
	PrepareStyleRaw       = "raw"
	PrepareStyleUnknow    = "unknow"
)

Variables

View Source 
var Version = "v1.9.0"

Functions

func Generate added in v0.8.0 

func Generate(conf ...string) (err error)

Generate generate struct type autumatic by configFile with default generator

func GenerateBy added in v1.9.0 

func GenerateBy(sqlFilePath string, dstPath string, pkgName string, opts ...option) error

GenerateBy generate struct type autumatic by sql file with default generator

func GenerateFrom added in v1.8.3 

func GenerateFrom(infos []SqlInfo, opts ...option) (err error)

GenerateFrom generate struct type autumatic from SqlInfo's inforamation with default generator

func MustBuild added in v1.1.2 

func MustBuild(p PrepareContext, fn func(PrepareBuilder, ...context.Context) (*sql.Stmt, error), hook ...func(query string) string) *sql.Stmt

MustBuild build a struct object than type of T

func MustBuildx added in v1.1.2 

func MustBuildx[T, S any](p PreparexContext[T, S], fn func(PreparexBuilder[T, S], ...context.Context) (T, error), hook ...func(query string) string) T

MustBuildx[T, S] build a struct object than type of T

func Scan 

func Scan(obj any, query SQLQuery, hook ...PrepareHook) error

Scan scan object from a SQLQuery

func ScanContext 

func ScanContext(ctx context.Context, obj any, query SQLQuery, hook ...PrepareHook) error

ScanContext scan object from a SQLQuery with context.Context

func SetDefaultGenerator added in v0.9.0 

func SetDefaultGenerator(g Generator)

SetDefaultGenerator set default generator The default generator is NewSqlxGenerator() instance in first start

func SetDefaultPrepareHook added in v0.6.0 

func SetDefaultPrepareHook(hook PrepareHook)

SetDefaultPrepareHook set default prepare hook Reset default prepare hook if hook is nil

func SetDefaultQueryHook added in v0.7.0 

func SetDefaultQueryHook(hooks ...func(query *Query) (*Query, error))

SetDefaultQueryHook set default query hooks

func SetDefaultTag 

func SetDefaultTag(tag string)

SetDeafultTag set default struct tag

func Use added in v0.2.0 

func Use(p PrepareContext)

Use use default prepare scanner with prepare that implement PrepareContext

func UseSqlx added in v0.2.0 

func UseSqlx[T, S any](p PreparexContext[T, S])

UseSqlx[T, S] use default prepare scanner withprepare that implement PreparexContext

Types

type Generator added in v0.8.0 

type Generator interface {
	Generate(dstPath string, pkgName string, query SQLQuery, opts ...option) error
}

Generator generate struct code automatic base SQLQuery

func NewSqlGenerator added in v0.8.0 

func NewSqlGenerator() Generator

NewSqlGenerator create a sql generator use std sql

func NewSqlxGenerator added in v0.8.0 

func NewSqlxGenerator() Generator

NewSqlxGenerator create a sqlx generator use sqlx

type Namespace 

type Namespace struct{}

Namespace just a placeholder type for indicate namespace of object

type OptFn added in v1.8.1 

type OptFn func(opt *generateOption)

OptFn option function

func DefaultStructNameOpt added in v0.8.0 

func DefaultStructNameOpt(name string) OptFn

DefaultStructNameOpt set custom default global sql query struct name

func GoFileNameOpt added in v0.8.0 

func GoFileNameOpt(name string) OptFn

GoFileNameOpt set custom go file name to generate

func SqlxPkgNameOpt added in v1.9.0 

func SqlxPkgNameOpt(name string) OptFn

SqlxPkgNameOpt set sqlx pakcage name

type PrepareBuilder added in v1.1.0 

type PrepareBuilder interface {
	PrepareContext
	QueryHook(query string) string
}

PrepareBuilder prepare builder interface sql

func NewPrepareBuilder added in v1.1.1 

func NewPrepareBuilder(p PrepareContext, hooks ...func(string) string) PrepareBuilder

NewPrepareBuilder create a simple prepare builder instance

type PrepareContext added in v0.4.0 

type PrepareContext interface {
	// PreparexContext returns a prepared statement, bound to this connection.
	// context is for the preparation of the statement,
	// it must not store the context within the statement itself.
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

PrepareContext enhances the Conn interface with context.

type PrepareHook 

type PrepareHook interface {
	PrepareContext(ctx context.Context, field reflect.Type, query string) (any, error)
}

PrepareHook prepare hook for scan object

func NewPrepareHook 

func NewPrepareHook(p PrepareContext) PrepareHook

NewPrepareHook create a prepare hook with prepare that implement PrepareContext

func NewSqlxPrepareHook 

func NewSqlxPrepareHook[T, S any](p PreparexContext[T, S]) PrepareHook

NewSqlxPrepareHook[T] create a prepare hook prepare that implement PreparexContext

type PrepareScanner 

type PrepareScanner interface {
	SetPrepareHook(hook PrepareHook)
	ScanContext(ctx context.Context, obj any, query SQLQuery) error
}

PrepareScanner scan object interface

func NewPrepareScanner 

func NewPrepareScanner(prepareHook PrepareHook) PrepareScanner

NewPrepareScanner create prepare scnanner instance

type PreparexBuilder added in v1.1.0 

type PreparexBuilder[T, S any] interface {
	PreparexContext[T, S]
	QueryHook(query string) string
}

PreparexBuilder[T, S] preparex builder interface for sqlx

func NewPreparexBuilder added in v1.1.1 

func NewPreparexBuilder[T, S any](p PreparexContext[T, S], hooks ...func(string) string) PreparexBuilder[T, S]

NewPreprarexBuilder[T, S] create a simple preparex builder instance

type PreparexContext added in v0.4.0 

type PreparexContext[T, S any] interface {
	// PrepareContext prepares a statement.
	// The provided context is used for the preparation of the statement, not for
	// the execution of the statement.
	PreparexContext(ctx context.Context, query string) (T, error)

	// PrepareNamedContext returns an sqlx.NamedStmt
	PrepareNamedContext(ctx context.Context, query string) (S, error)

	// Rebind rebind query to adapte SQL Driver
	Rebind(query string) string
}

PreparexContext[T, S] enhances the Conn interface with context.

type Query 

type Query struct {
	Scope string
	Name  string
	Query string
	Tags  map[string]string
}

Query is a parsed query along with tags.

func (*Query) PrepareStyle added in v0.8.0 

func (q *Query) PrepareStyle() string

type QueryList added in v1.1.6 

type QueryList []*Query

QueryList query list

func (QueryList) Len added in v1.1.6 

func (q QueryList) Len() int

func (QueryList) Less added in v1.1.6 

func (q QueryList) Less(i, j int) bool

func (QueryList) Swap added in v1.1.6 

func (q QueryList) Swap(i, j int)

type QueryMap 

type QueryMap map[string]*Query

QueryMap is a map associating a Tag to its Query

func (QueryMap) FilterByStyle added in v0.8.0 

func (q QueryMap) FilterByStyle(style string) QueryMap

func (QueryMap) IsNotEmpty added in v0.8.0 

func (q QueryMap) IsNotEmpty() bool

func (QueryMap) IsRawQueryNotEmpty added in v0.8.0 

func (q QueryMap) IsRawQueryNotEmpty() bool

func (QueryMap) IsStmtQueryNotEmpty added in v1.3.0 

func (q QueryMap) IsStmtQueryNotEmpty() bool

type SQLParser added in v0.7.0 

type SQLParser interface {
	AddHooks(hooks ...func(query *Query) (*Query, error))
	ParseReader(reader io.Reader) (SQLQuery, error)
}

SQLParser sql file parser interface

type SQLQuery 

type SQLQuery interface {
	// SqlQuery get default QueryMap and namespace's QueryMap.
	// return default QueryMap if namespace is empty string
	SqlQuery(namespace string) (QueryMap, QueryMap, error)

	// ListQuery get QuryMap by namespace
	// get default QueryMap if namespace is not give or an empty name
	ListQuery(namespace ...string) (QueryMap, error)

	// ListScope get all namespace Querymap
	ListScope() ScopeQuery

	// AllQuery get all *Query list
	AllQuery() QueryList
}

SQLQuery sql query information interface

func MustParseBytes 

func MustParseBytes(b []byte, hooks ...func(query *Query) (*Query, error)) SQLQuery

MustParseBytes parses bytes but panics if an error occurs.

func MustParseFile 

func MustParseFile(path string, hooks ...func(query *Query) (*Query, error)) SQLQuery

MustParseFile calls ParseFile but panic if an error occurs

func ParseBytes 

func ParseBytes(b []byte, hooks ...func(query *Query) (*Query, error)) (SQLQuery, error)

ParseBytes parses bytes and returns Queries or an error.

func ParseFile 

func ParseFile(path string, hooks ...func(query *Query) (*Query, error)) (SQLQuery, error)

ParseFile reads a file and return Queries or an error

func ParseReader 

func ParseReader(reader io.Reader, hooks ...func(query *Query) (*Query, error)) (SQLQuery, error)

ParseReader takes an io.Reader and returns Queries or an error.

type ScopeQuery added in v0.8.0 

type ScopeQuery map[string]QueryMap

ScopeQuery is a namespace QueryMap

type SqlInfo added in v1.8.3 

type SqlInfo struct {
	FilePath string
	DestPath string
	PkgName  string
}

SqlInfo contain info for generate code

func NewSqlInfo added in v1.8.3 

func NewSqlInfo(sqlFilePath string, dstPath string, pkgName string) SqlInfo

NewSqlInfo create a SqlInfo instance

Source Files

View all Source files

Directories

Path Synopsis
examples module

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.