gqt

package module
v2.0.2 Latest Latest
Warning

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

 Go to latest Published: Oct 30, 2022 License: BSD-3-Clause Imports: 23 Imported by: 5

README

GQT - Go(lang) SQL Templates

Go Report Card GoDoc

Package gqt is a template engine for SQL queries.

It helps to separate SQL code from Go code and permits to compose the queries with a simple syntax.

The template engine is the standard package "text/template".

Why this package? Read more about ORM is the Vietnam of computer science.

Install/update using go get (no dependencies required by gqt):

go get -u github.com/suifengpiao14/gqt
Benefits
  • SQL is the best language to write SQL.
  • Separation between Go and SQL source code (your DB administrator will thank you).
  • Simpler template syntax for composing queries than writing Go code.
  • Simplified maintenance of the SQL code.
Compatibility

Go >= 1.6

Usage

Create a template directory tree of .sql files. Here an example template with the definition of three blocks:

-- File /path/to/sql/repository/dir/example.sql
{{define "allUsers"}}
SELECT *
FROM users
WHERE 1=1
{{end}}

{{define "getUser"}}
SELECT *
FROM users
WHERE id=?
{{end}}

{{define "allPosts"}}
SELECT *
FROM posts
WHERE date>=?
{{if ne .Order ""}}ORDER BY date {{.Order}}{{end}}
{{end}}

Then, with Go, add the directory to the default repository and execute the queries:

// Setup
gqt.Add("/path/to/sql/repository/dir", "*.sql")

// Simple query without parameters
db.Query(gqt.Get("allUsers"))
// Query with parameters
db.QueryRow(gqt.Get("getuser"), 1)
// Query with context and parameters
db.Query(gqt.Exec("allPosts", map[string]interface{
	"Order": "DESC",
}), date)

The templates are parsed immediately and recursively.

Namespaces

The templates can be organized in namespaces and stored in multiple root directories.

templates1/
|-- roles/
|	|-- queries.sql
|-- users/
|	|-- queries.sql
|	|-- commands.sql

templates2/
|-- posts/
|	|-- queries.sql
|	|-- commands.sql
|-- users/
|	|-- queries.sql
|-- queries.sql

The blocks inside the sql files are merged, the blocks with the same namespace and name will be overridden following the alphabetical order.

The sub-directories are used as namespaces and accessed like:

gqt.Add("../templates1", "*.sql")
gqt.Add("../templates2", "*.sql")

// Will search inside templates1/users/*.sql and templates2/users/*.sql
gqt.Get("users/allUsers")

Multiple databases

When dealing with multiple databases at the same time, like PostgreSQL and MySQL, just create two repositories:

// Use a common directory
dir := "/path/to/sql/repository/dir"

// Create the PostgreSQL repository
pgsql := gqt.NewRepository()
pgsql.Add(dir, "*.pg.sql")

// Create a separated MySQL repository
mysql := gqt.NewRepository()
mysql.Add(dir, "*.my.sql")

// Then execute
pgsql.Get("queryName")
mysql.Get("queryName")

License

Copyright © 2016 Davide Muzzarelli. All right reserved.

Use of this source code is governed by a BSD-style license that can be found in the LICENSE file.

gqttool

gqttool sql -metaDir example -sqlDir example/gen -force true gqttool sqlEntity -sqlDir example -entity example/sql.entity.gen.go -force true

Documentation

Index

Constants

View Source 
const (
	EOF                  = "\n"
	WINDOW_EOF           = "\r\n"
	HTTP_HEAD_BODY_DELIM = EOF + EOF
)
View Source 
const TEMPLATE_MAP_KEY = "_templateMap"
View Source 
const URI_KEY = "__URI__" // 记录资源地址key(curl 请求地址、db 连接地址等)

Variables

View Source 
var CURLNamespaceSuffix = "curl"
View Source 
var ConfigNamespaceSuffix = "config"
View Source 
var DB_SOURCE = ""
View Source 
var DDLNamespaceSuffix = "ddl"
View Source 
var LeftDelim = "{{"
View Source 
var MetaNamespaceSuffix = "meta"
View Source 
var RepositoryFS *embed.FS

实际使用时，需要初始化该变量

View Source 
var RightDelim = "}}"
View Source 
var SQLNamespaceSuffix = "sql"
View Source 
var TPlSuffix = ".tpl"
View Source 
var TemplateDir = "template"
View Source 
var TemplatefuncMap = template.FuncMap{
	"zeroTime":      ZeroTime,
	"currentTime":   CurrentTime,
	"permanentTime": PermanentTime,
	"contains":      strings.Contains,
	"newPreComma":   NewPreComma,
	"in":            In,
	"toCamel":       ToCamel,
	"toLowerCamel":  ToLowerCamel,
	"snakeCase":     SnakeCase,
	"tplOutput":     TplOutput,
}

Functions

func AddTemplateByDir added in v2.0.1 

func AddTemplateByDir(root string, namespaceSuffix string, funcMap template.FuncMap, leftDelim string, rightDelim string) (templateMap map[string]*template.Template, err error)

func AddTemplateByFS added in v2.0.1 

func AddTemplateByFS(fsys fs.FS, root string, namespaceSuffix string, funcMap template.FuncMap, leftDelim string, rightDelim string) (templateMap map[string]*template.Template, err error)

func AddTemplateByStr added in v2.0.1 

func AddTemplateByStr(namespace string, content string, funcMap template.FuncMap, leftDelim string, rightDelim string) (t *template.Template, err error)

func BatchInsertUpdateDelSQL 

func BatchInsertUpdateDelSQL(args *BatchInsertUpdateDelSQLArgs)

func ConvertStruct 

func ConvertStruct(from interface{}, to interface{})

ConvertStruct 转换结构体

func CurrentTime 

func CurrentTime(tplEntity TplEntityInterface) (string, error)

func DBBatchExec 

func DBBatchExec(db func() *gorm.DB, sqlMap map[string]string) (err error)

func DBBatchSave 

func DBBatchSave(sqlRepository func() *RepositorySQL, db func() *gorm.DB, getByIDsEntity TplEntityInterface, args *DBBatchSaveArgs) (err error)

func DBCount 

func DBCount(sqlRepository func() *RepositorySQL, db func() *gorm.DB, entity TplEntityInterface, count *int) (err error)

func DBCreateTable 

func DBCreateTable(getrepositorySQL func() *RepositorySQL)

DBCreateTable 初始化数据表

func DBExec 

func DBExec(sqlRepository func() *RepositorySQL, db func() *gorm.DB, entity TplEntityInterface) (err error)

func DBRawScan 

func DBRawScan(sqlRepository func() *RepositorySQL, db func() *gorm.DB, entity TplEntityInterface, output interface{}) (err error)

func DBTryFind 

func DBTryFind(sqlRepository func() *RepositorySQL, db func() *gorm.DB, entity TplEntityInterface, output interface{}) (err error)

func ExecTpl added in v2.0.1 

func ExecTpl(dataVolume TplEntityInterface, fullname string) (output string, err error)

TplOutput 模板中执行模板，获取数据时使用 gqttool 生成的entity 会调用该方法，实现 TplEntityInterface 接口

func FileName2Namespace added in v2.0.1 

func FileName2Namespace(filename string, root string) (namespace string)

func Flight 

func Flight(sqlStr string, output interface{}, fn func() (interface{}, error)) (err error)

func GetDb 

func GetDb() *gorm.DB

GetDb is a signal DB

func GetMD5LOWER 

func GetMD5LOWER(s string) string

func GetTplFilesByDir added in v2.0.1 

func GetTplFilesByDir(dir string, namespaceSuffix string) (allFileList []string, err error)

GetTplFilesByDir get current and reverse dir tpl file

func GetTplFilesByFS added in v2.0.1 

func GetTplFilesByFS(fsys fs.FS, dir string, namespaceSuffix string) (allFileList []string, err error)

func Glob added in v2.0.1 

func Glob(fsys fs.FS, pattern string) ([]string, error)

Glob adds double-star support to the core path/filepath Glob function. It's useful when your globs might have double-stars, but you're not sure.

func GlobDirectory added in v2.0.1 

func GlobDirectory(dir string, pattern string) ([]string, error)

func In 

func In(tplEntity TplEntityInterface, data interface{}) (str string, err error)

func InitDB 

func InitDB() *gorm.DB

GetDb 获取db实例

func InitRepositorySQL 

func InitRepositorySQL()

func Model2TplEntity 

func Model2TplEntity(from interface{}, to TplEntityInterface)

Model2Entity copy model to entity ,some times input used to insert and update ,in this case input mybe model, copy model value to insertEntity and updateEntity

func NewPreComma 

func NewPreComma() *preComma

func PermanentTime 

func PermanentTime(tplEntity TplEntityInterface) (string, error)

func SnakeCase added in v2.0.1 

func SnakeCase(name string) string

func SpellFullname added in v2.0.1 

func SpellFullname(namespace string, name string) (fullname string)

func SplitFullname added in v2.0.1 

func SplitFullname(fullname string) (namespace string, name string)

func StandardizeSpaces added in v2.0.1 

func StandardizeSpaces(s string) string

func Statement2SQL added in v2.0.1 

func Statement2SQL(sqlStatement string, vars []interface{}) (sqlStr string)

func ToCamel added in v2.0.1 

func ToCamel(name string) string

封装 goa.design/goa/v3/codegen 方便后续可定制

func ToEOF added in v2.0.1 

func ToEOF(s string) string

func ToLowerCamel added in v2.0.1 

func ToLowerCamel(name string) string

func TplOutput 

func TplOutput(dataVolume TplEntityInterface, tplEntity TplEntityInterface) (output string, err error)

func TrimSpaces added in v2.0.1 

func TrimSpaces(s string) string

TrimSpaces 去除开头结尾的非有效字符

func WrapDBExecSQL 

func WrapDBExecSQL(db func() *gorm.DB) func(sqlRowList []*SQLRow) (err error)

func WrapDBScanSQL 

func WrapDBScanSQL(db func() *gorm.DB, throwNotFoundErr bool) func(sqlRowList []*SQLRow) (err error)

func ZeroTime 

func ZeroTime(tplEntity TplEntityInterface) (string, error)

Types

type BatchInsertUpdateDelSQLArgs 

type BatchInsertUpdateDelSQLArgs struct {
	ModelList       interface{}
	DBModelList     interface{}
	PrimaryKeyCamel string
	UpdateEntity    TplEntityInterface
	InsertEntity    TplEntityInterface
	DelEntity       TplEntityInterface
	SqlChain        *SQLChain
}

type DBBatchSaveArgs 

type DBBatchSaveArgs struct {
	ModelList       interface{}
	PrimaryKeyCamel string
	UpdateEntity    TplEntityInterface // 无需填充
	InsertEntity    TplEntityInterface // 无需填充
	DelEntity       TplEntityInterface // 无需填充
}

type RepositorySQL 

type RepositorySQL struct {
	// contains filtered or unexported fields
}

RepositorySQL stores SQL templates.

func GetRepositorySQL 

func GetRepositorySQL() *RepositorySQL

func NewRepositorySQL 

func NewRepositorySQL() *RepositorySQL

NewRepositorySQL create a new Repository.

func (*RepositorySQL) AddByDir 

func (r *RepositorySQL) AddByDir(root string, funcMap template.FuncMap) (err error)

func (*RepositorySQL) AddByFS 

func (r *RepositorySQL) AddByFS(fsys fs.FS, root string, funcMap template.FuncMap) (err error)

func (*RepositorySQL) AddByNamespace 

func (r *RepositorySQL) AddByNamespace(namespace string, content string, funcMap template.FuncMap) (err error)

func (*RepositorySQL) DefineResult2SQLRow 

func (r *RepositorySQL) DefineResult2SQLRow(defineResult TPLDefine) (sqlRow *SQLRow, err error)

func (*RepositorySQL) GetByNamespace 

func (r *RepositorySQL) GetByNamespace(namespace string, data TplEntityInterface) (sqlRowList []*SQLRow, err error)

GetByNamespace get all template under namespace

func (*RepositorySQL) GetDDLNamespace 

func (r *RepositorySQL) GetDDLNamespace() (ddlNamespace string, err error)

func (*RepositorySQL) GetDDLSQL 

func (r *RepositorySQL) GetDDLSQL() (ddlSQLRowList []*SQLRow, err error)

func (*RepositorySQL) GetSQL 

func (r *RepositorySQL) GetSQL(t TplEntityInterface) (*SQLRow, error)

将模板名称，模板中的变量，封装到结构体中，使用结构体访问，避免拼写错误以及分散的硬编码，可以配合 gqttool 自动生成响应的结构体

func (*RepositorySQL) GetSQLRef 

func (r *RepositorySQL) GetSQLRef(t TplEntityInterface, sqlStr *string) (err error)

GetSQLByTplEntityRef 支持只返回error 函数签名

func (*RepositorySQL) NewSQLChain 

func (r *RepositorySQL) NewSQLChain() *SQLChain

type RepositoryTemplate added in v2.0.1 

type RepositoryTemplate struct {
	// contains filtered or unexported fields
}

RepositoryTemplate stores templates.

func NewRepositoryTemplate added in v2.0.1 

func NewRepositoryTemplate() *RepositoryTemplate

NewRepositoryTemplate create a new RepositoryTemplate.

type SQLChain 

type SQLChain struct {
	// contains filtered or unexported fields
}

func NewSQLChain 

func NewSQLChain(sqlRepository func() *RepositorySQL) (s *SQLChain)

批量获取sql记录

func (*SQLChain) AddSQL 

func (s *SQLChain) AddSQL(namespace string, name string, sql string, result interface{})

AddSQL add one sql to SQLChain

func (*SQLChain) Error 

func (s *SQLChain) Error() (err error)

func (*SQLChain) Exec 

func (s *SQLChain) Exec(fn func(sqlRowList []*SQLRow) (e error)) (err error)

Exec exec sql

func (*SQLChain) ParseSQL 

func (s *SQLChain) ParseSQL(t TplEntityInterface, result interface{}) *SQLChain

func (*SQLChain) SQLRows 

func (s *SQLChain) SQLRows() (sqlRowList []*SQLRow, err error)

GetAllSQL get all sql from SQLChain

func (*SQLChain) Scan 

func (s *SQLChain) Scan(fn func(sqlRowList []*SQLRow) (e error)) (err error)

Exec exec sql ,get data

func (*SQLChain) SetError 

func (s *SQLChain) SetError(err error)

type SQLRow 

type SQLRow struct {
	Name        string
	Namespace   string
	SQL         string
	Statment    string
	Arguments   []interface{}
	NamedStmt   string
	NamedParams map[string]interface{}
	Result      interface{}
}

type TPLDefine added in v2.0.1 

type TPLDefine struct {
	Name      string
	Namespace string
	Output    string
	Input     interface{}
}

func ExecuteNamespaceTemplate added in v2.0.1 

func ExecuteNamespaceTemplate(templateMap map[string]*template.Template, namespace string, tplEntity TplEntityInterface) (tplDefineList []*TPLDefine, err error)

ExecuteNamespaceTemplate execute all template under namespace

func ExecuteTemplate added in v2.0.1 

func ExecuteTemplate(templateMap map[string]*template.Template, fullname string, tplEntity TplEntityInterface) (tplDefine *TPLDefine, err error)

ExecuteNamespaceTemplate execute all template under namespace

func ExecuteTemplateTry added in v2.0.1 

func ExecuteTemplateTry(templateMap map[string]*template.Template, fullname string, tplEntity TplEntityInterface) (tplDefine *TPLDefine, err error)

ExecuteTemplateTry 找不到模板的时候，返回null，不报错(curl 模板需要先执行xxxBody_模板)

type TplEmptyEntity added in v2.0.1 

type TplEmptyEntity map[string]interface{}

func (*TplEmptyEntity) GetDynamicValus added in v2.0.1 

func (v *TplEmptyEntity) GetDynamicValus() (values map[string]interface{})

func (*TplEmptyEntity) GetSQLRow added in v2.0.1 

func (v *TplEmptyEntity) GetSQLRow() (sqlRow *SQLRow, err error)

func (*TplEmptyEntity) GetValue added in v2.0.1 

func (v *TplEmptyEntity) GetValue(key string) (value interface{}, ok bool)

func (*TplEmptyEntity) SetValue added in v2.0.1 

func (v *TplEmptyEntity) SetValue(key string, value interface{})

func (*TplEmptyEntity) TplName added in v2.0.1 

func (v *TplEmptyEntity) TplName() string

func (*TplEmptyEntity) TplType added in v2.0.1 

func (v *TplEmptyEntity) TplType() string

type TplEntityInterface added in v2.0.1 

type TplEntityInterface interface {
	TplName() string
	TplType() string // 返回 TPL_DEFINE_TYPE 类型，方便后续根据类型获取资源(db、curl) 自动获取数据
	SetValue(key string, value interface{})
	GetValue(key string) (value interface{}, ok bool)
	GetDynamicValus() (values map[string]interface{})
	GetSQLRow() (sqlRow *SQLRow, err error)
}

TplEntityInterface 模板参数对象，由于sql、curl经常需要在模板中增加数据，所以直接在模板输入实体接口融合TplEntityInterface 接口功能，实体包含隐藏字段类型tplEntityMap，即可实现TplEntityInterface 功能

Source Files

View all Source files

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.