rsql

package
v0.0.0-...-bac8e5f Latest Latest
Warning

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

 Go to latest Published: Apr 14, 2021 License: GPL-3.0 Imports: 22 Imported by: 0

README

rsql

基于 sqlx 和 gosql

使用

Quick start!

import (
    "github.com/dawei101/gor/rsql"
)

func main(){
    rsql.QueryRowx("select * from users where id = 1")
}

数据库的连接基于下的封装，默认使用default connection, 如果需要使用其他connection，可以指定

// default db connection
rsql.Queryx("select * from users")

// appcourse db connection
rsql.Use("appcourse").Queryx("select * from users")

rsql包裹一层sqlx，所以可以直接使用sqlx的相关 function

使用sqlx的原始方法, 请查看 https://github.com/jmoiron/sqlx

//Exec
rsql.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","test@gmail.com",time.Now(),time.Now())

//Queryx
rows,err := rsql.Queryx("select * from users")
for rows.Next() {
    user := &User{}
    err = rows.StructScan(user)
}
rows.Close()

//QueryRowx
user := &User{}
err := rsql.QueryRowx("select * from users where id = ?",1).StructScan(user)

//Get
user := &User{}
err := rsql.Get(user,"select * from users where id = ?",1)

//Select
users := make([]User)
err := rsql.Select(&users,"select * from users")

//Change database
db := rsql.Use("test")
db.Queryx("select * from tests")

你可以使用其他database的connection

rsql.Use("appcourse").Queryx("select * from users")

因此你所有使用的方法都是基于 connection apocourse

使用结构体

Model interface 的定义
type IModel interface {
	TableName() string
	PK() string
}
结构体demo
type User struct {
	Id        int       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Status    int       `db:"status"`
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
}

func (u *User) TableName() string {
	return "users"
}

func (u *User) PK() string {
	return "id" // 返回table中的field名
}

//Get
user := &User{}
rsql.Model(user).Where("id=?",1).Get()

//All
user := make([]User,0)
rsql.Model(&user).All()

//Create and auto set CreatedAt
rsql.Model(&User{Name:"test",Email:"test@gmail.com"}).Create()

//Update
rsql.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update()

//Delete
rsql.Model(&User{}).Where("id=?",1).Delete()

如果使用 struct 直接当做where的条件查询:

//Get where id = 1 and name = "test1"
user := &User{Id:1,Name:"test1"}
rsql.Model(&user).Get()

//Update 默认使用主键值作为where条件
// update user set id = 1, name = "test2" where id = 1
rsql.Model(&User{Id:1,Name:"test2"}).Update()

//Use custom conditions
//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)
rsql.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()

//Delete
rsql.Model(&User{Id:1}).Delete()

⚠️ 特别注意：结构体中的int default=0的问题处理

user := &User{Id:1,Status:0}
// 如果你需要status加入where筛选中，需要在Get() 指定zero int字段
// where id=1 and status=0
rsql.Model(&user).Get("status")

// where id=1;
rsql.Model(&user).Get()


//如果你想update status=0 你需要在Update() 指定zero int字段
// update user set status=0 where id = 1;
rsql.Model(&User{Status:0}).Where("id=?",1).Update("status")

// 直接使用struct Update时，where 只使用主键筛选
// update user set id = 1, name = "test2" where id = 1
rsql.Model(&User{Id:1,Name:"test2"}).Update()

从db中生成结构体小工具 genstruct

事务

Tx 有一个实例方法，如果方法返回error，事务会回滚

rsql.Tx(func(tx *rsql.DB) error {
    for id := 1; id < 10; id++ {
        user := &User{
            Id:    id,
            Name:  "test" + strconv.Itoa(id),
            Email: "test" + strconv.Itoa(id) + "@test.com",
        }
		
		//do some database operations in the transaction (use 'tx' from this point, not 'rsql')
        tx.Model(user).Create()

        if id == 8 {
            return errors.New("interrupt the transaction")
        }
    }

    //query with transaction
    var num int
    err := tx.QueryRowx("select count(*) from user_id = 1").Scan(&num)

    if err != nil {
        return err
    }

    return nil
})

带context的事务

rsql.Txx(r.Context(), func(ctx context.Context, tx *rsql.DB) error {
    // do something
})

事务也可以使用流程化的 rsql.Begin() / rsql.Use("other").Begin() 完成:

tx, err := rsql.Begin()
if err != nil {
    return err
}

for id := 1; id < 10; id++ {
    _, err := tx.Exec("INSERT INTO users(id,name,status,created_at,updated_at) VALUES(?,?,?,?,?)", id, "test"+strconv.Itoa(id), 1, time.Now(), time.Now())
    if err != nil {
        return tx.Rollback()
    }
}

return tx.Commit()

创建/更新时间自动添加

如果表结构里有以下字段， 将会自动添加上当前时间

AUTO_CREATE_TIME_FIELDS = []string{
    "create_time",
    "create_at",
    "created_at",
    "update_time",
    "update_at",
    "updated_at",
}
AUTO_UPDATE_TIME_FIELDS = []string{
    "update_time",
    "update_at",
    "updated_at",
}

使用 Map

Create Update Delete Count 支持 map[string]interface, 举例:

//Create
rsql.Table("users").Create(map[string]interface{}{
    "id":         1,
    "name":       "test",
    "email":      "test@test.com",
    "created_at": "2018-07-11 11:58:21",
    "updated_at": "2018-07-11 11:58:21",
})

//Update
rsql.Table("users").Where("id = ?", 1).Update(map[string]interface{}{
    "name":  "fifsky",
    "email": "fifsky@test.com",
})

//Delete
rsql.Table("users").Where("id = ?", 1).Delete()

//Count
rsql.Table("users").Where("id = ?", 1).Count()

//Change database
rsql.Use("db2").Table("users").Where("id = ?", 1).Count()

//Transaction `tx`
tx.Table("users").Where("id = ?", 1}).Count()

rsql.Expr

怎么使用raw sql，原始语句?

rsql.Table("users").Update(map[string]interface{}{
    "id":2,
    "count":rsql.Expr("count+?",1)
})
//Builder SQL
//UPDATE `users` SET `count`=count + ?,`id`=?; [1 2]

"In" 语句

database/sql 使用in语法，需要自己组合 sql，总是比较复杂, 现在你可以使用sqlx的简单写法：



//SELECT * FROM users WHERE level IN (?);

var levels = []int{4, 6, 7}
rows, err := rsql.Queryx("SELECT * FROM users WHERE level IN (?);", levels)

//or

user := make([]User, 0)
err := rsql.Select(&user, "select * from users where id in(?)",[]int{1,2,3})

Relation

rsql 使用 golang structure 来描述 tables 间的relationships, 你需要使用 relation Tag 来定义相关关系, 实例：

⚠️ 跨db的查询，可以直接使用connection tag， 来定义对应的connection

type RichMoment struct {
	models.Moment
	User   *models.User    `json:"user" db:"-" relation:"user_id,id"`         //one-to-one
	Photos []models.Photos `json:"photos" db:"-" relation:"id,moment_id" connection:"db2"`     //one-to-many
}

单条查询

moment := &RichMoment{}
err := rsql.Model(moment).Where("status = 1 and id = ?",14).Get()
//output User and Photos and you get the result

SQL:

2018/12/06 13:27:54
	Query: SELECT * FROM `moments` WHERE (status = 1 and id = ?);
	Args:  []interface {}{14}
	Time:  0.00300s

2018/12/06 13:27:54
	Query: SELECT * FROM `moment_users` WHERE (id=?);
	Args:  []interface {}{5}
	Time:  0.00081s

2018/12/06 13:27:54
	Query: SELECT * FROM `photos` WHERE (moment_id=?);
	Args:  []interface {}{14}
	Time:  0.00093s

多条查询结果, many-to-many

var moments = make([]RichMoment, 0)
err := rsql.Model(&moments).Where("status = 1").Limit(10).All()
//You get the total result  for *UserMoment slice

SQL:

2018/12/06 13:50:59
	Query: SELECT * FROM `moments` WHERE (status = 1) LIMIT 10;
	Time:  0.00319s

2018/12/06 13:50:59
	Query: SELECT * FROM `moment_users` WHERE (id in(?));
	Args:  []interface {}{[]interface {}{5}}
	Time:  0.00094s

2018/12/06 13:50:59
	Query: SELECT * FROM `photos` WHERE (moment_id in(?, ?, ?, ?, ?, ?, ?, ?, ?, ?));
	Args:  []interface {}{[]interface {}{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}}
	Time:  0.00087s

Relation中的Where:

moment := &RichMoment{}
err := rsql.Relation("User" , func(b *rsql.Builder) {
    //this is builder instance,
    b.Where("gender = 0")
}).Get(moment , "select * from moments")

使用钩子-Hooks

在一个Model的执行创建、查询、修改、删除语句时，你可以添加执行前或执行后的方法。

您仅需要为 Model 定义相关约定的方法即可，rsql 会在相应的操作时，自动调用钩子。如果任意一个钩子方法返回了error，rsql会停止接下来的操作，并将当前操作的事务回滚。

// begin transaction
BeforeChange
BeforeCreate
// update timestamp `CreatedAt`, `UpdatedAt`
// save
AfterCreate
AfterChange
// commit or rollback transaction

Example:

func (u *User) BeforeCreate() (err error) {
  if u.IsValid() {
    err = errors.New("can't save invalid data")
  }
  return
}

func (u *User) AfterCreate(tx *rsql.DB) (err error) {
  if u.Id == 1 {
    u.Email = "after@test.com"
    tx.Model(u).Update()
  }
  return
}

BeforeChange / AfterChange 仅在创建、更新、删除时执行

所有钩子方法Hooks:

BeforeChange
AfterChange
BeforeCreate
AfterCreate
BeforeUpdate
AfterUpdate
BeforeDelete
AfterDelete
BeforeFind
AfterFind

钩子方法支持多种传参和返回值的组合:

func (u *User) BeforeCreate()
func (u *User) BeforeCreate() (err error)
func (u *User) BeforeCreate(tx *rsql.DB)
func (u *User) BeforeCreate(tx *rsql.DB) (err error)

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (

	// Insert database automatically updates fields
	AUTO_CREATE_TIME_FIELDS = []string{
		"create_time",
		"create_at",
		"created_at",
		"update_time",
		"update_at",
		"updated_at",
	}
	// Update database automatically updates fields
	AUTO_UPDATE_TIME_FIELDS = []string{
		"update_time",
		"update_at",
		"updated_at",
	}
)

Functions

func Conn 

func Conn(name string) *sql.DB

获得 name 的 *sql.DB

获得前必须保证 InitDB 过，否则会 panic

func DefConn 

func DefConn() *sql.DB

获得 name 的 *sql.DB

获得前必须保证 `InitDB("default", "xxxx")` 过，否则会 panic

func DefXConn 

func DefXConn() *sqlx.DB

获得default *sqlx.DB

获得前必须保证 Reg 过，否则会 panic

请不要使用migration特性

@see github.com/jmoiron/sqlx

func Exec 

func Exec(query string, args ...interface{}) (sql.Result, error)

Exec default database

func Expr 

func Expr(expression string, args ...interface{}) *expr

Expr generate raw SQL expression, for example: 

gosql.Table("user").Update(map[string]interface{}{"price", gorm.Expr("price * ? + ?", 2, 100)})

func Get 

func Get(dest interface{}, query string, args ...interface{}) error

Get default database

func Import 

func Import(f string) ([]sql.Result, error)

Import SQL DDL from io.Reader

func IsZero 

func IsZero(val reflect.Value) bool

IsZero assert value is zero value

func JsonObject 

func JsonObject(value interface{}) (json.RawMessage, error)

func QueryRowx 

func QueryRowx(query string, args ...interface{}) *sqlx.Row

QueryRowx default database

func Queryx 

func Queryx(query string, args ...interface{}) (*sqlx.Rows, error)

Queryx default database

func Reg 

func Reg(name, dbType string, dataSource string, maxOpenConns int) error

注册DB

获得前必须保证 InitDB 过，否则会 panic

func RegisterDialect 

func RegisterDialect(name string, dialect Dialect)

RegisterDialect register new dialect

func RelationAll 

func RelationAll(data interface{}, chains map[string]BuilderChainFunc) error

RelationAll is gets the associated relational data for multiple pieces of data

func RelationOne 

func RelationOne(data interface{}, chains map[string]BuilderChainFunc) error

RelationOne is get the associated relational data for a single piece of data

func Select 

func Select(dest interface{}, query string, args ...interface{}) error

Select default database

func SetLogger 

func SetLogger(l Logger)

func SetLogging 

func SetLogging(logging bool)

SetLogging set default logger

func Tx 

func Tx(fn func(tx *DB) error) error

Tx default database the transaction

func Txx 

func Txx(ctx context.Context, fn func(ctx context.Context, tx *DB) error) error

Txx default database the transaction with context

func XConn 

func XConn(name string) *sqlx.DB

获得*sqlx.DB

获得前必须保证 InitDB 过，否则会 panic

请不要使用migration特性

@see github.com/jmoiron/sqlx

Types

type Builder 

type Builder struct {
	SQLBuilder
	// contains filtered or unexported fields
}

func Model 

func Model(model interface{}) *Builder

Model construct SQL from Struct

func (*Builder) All 

func (b *Builder) All() (err error)

All get data rows from to Struct

func (*Builder) Count 

func (b *Builder) Count(zeroValues ...string) (num int64, err error)

gosql.Model(&User{}).Where("status = 0").Count()

func (*Builder) Create 

func (b *Builder) Create() (lastInsertId int64, err error)

Create data from to Struct

func (*Builder) Delete 

func (b *Builder) Delete(zeroValues ...string) (affected int64, err error)

gosql.Model(&User{Id:1}).Delete()

func (*Builder) ForceIndex 

func (b *Builder) ForceIndex(i string) *Builder

ForceIndex

func (*Builder) Get 

func (b *Builder) Get(zeroValues ...string) (err error)

All get data row from to Struct

func (*Builder) Hint 

func (b *Builder) Hint(hint string) *Builder

Hint is set TDDL "/*+TDDL:slave()*/"

func (*Builder) Limit 

func (b *Builder) Limit(i int) *Builder

Limit

func (*Builder) Offset 

func (b *Builder) Offset(i int) *Builder

Offset

func (*Builder) OrderBy 

func (b *Builder) OrderBy(str string) *Builder

OrderBy for example "id desc"

func (*Builder) Relation 

func (b *Builder) Relation(fieldName string, fn BuilderChainFunc) *Builder

Relation association table builder handle

func (*Builder) Select 

func (b *Builder) Select(fields string) *Builder

Select filter column

func (*Builder) ShowSQL 

func (b *Builder) ShowSQL() *Builder

ShowSQL output single sql

func (*Builder) Update 

func (b *Builder) Update(zeroValues ...string) (affected int64, err error)

gosql.Model(&User{Id:1,Status:0}).Update("status")

func (*Builder) Where 

func (b *Builder) Where(str string, args ...interface{}) *Builder

Where for example Where("id = ? and name = ?",1,"test")

type BuilderChainFunc 

type BuilderChainFunc func(b *Builder)

type DB 

type DB struct {
	RelationMap map[string]BuilderChainFunc
	// contains filtered or unexported fields
}

func Begin 

func Begin() (*DB, error)

Beginx begins a transaction for default database and returns an *gosql.DB instead of an *sql.Tx.

func Relation 

func Relation(name string, fn BuilderChainFunc) *DB

Relation association table builder handle

func ShowSql 

func ShowSql() *DB

ShowSql single show sql log

func Use 

func Use(db string) *DB

Use is change database

func UseDefault 

func UseDefault() *DB

func (*DB) Begin 

func (w *DB) Begin() (*DB, error)

Beginx begins a transaction and returns an *gosql.DB instead of an *sql.Tx.

func (*DB) Commit 

func (w *DB) Commit() error

Commit commits the transaction.

func (*DB) DriverName 

func (w *DB) DriverName() string

DriverName wrapper sqlx.DriverName

func (*DB) Exec 

func (w *DB) Exec(query string, args ...interface{}) (result sql.Result, err error)

Exec wrapper sqlx.Exec

func (*DB) Get 

func (w *DB) Get(dest interface{}, query string, args ...interface{}) (err error)

Get wrapper sqlx.Get

func (*DB) Import 

func (w *DB) Import(f string) ([]sql.Result, error)

Import SQL DDL from sql file

func (*DB) Model 

func (w *DB) Model(m interface{}) *Builder

Model database handler from to struct for example: gosql.Use("db2").Model(&users{})

func (*DB) Preparex 

func (w *DB) Preparex(query string) (*sqlx.Stmt, error)

Preparex wrapper sqlx.Preparex

func (*DB) QueryRowx 

func (w *DB) QueryRowx(query string, args ...interface{}) (rows *sqlx.Row)

QueryRowx wrapper sqlx.QueryRowx

func (*DB) Queryx 

func (w *DB) Queryx(query string, args ...interface{}) (rows *sqlx.Rows, err error)

Queryx wrapper sqlx.Queryx

func (*DB) Rebind 

func (w *DB) Rebind(query string) string

Rebind wrapper sqlx.Rebind

func (*DB) Relation 

func (w *DB) Relation(name string, fn BuilderChainFunc) *DB

Relation association table builder handle

func (*DB) Rollback 

func (w *DB) Rollback() error

Rollback aborts the transaction.

func (*DB) Select 

func (w *DB) Select(dest interface{}, query string, args ...interface{}) (err error)

Select wrapper sqlx.Select

func (*DB) ShowSql 

func (w *DB) ShowSql() *DB

func (*DB) Table 

func (w *DB) Table(t string) *Mapper

Table database handler from to table name for example: gosql.Use("db2").Table("users")

func (*DB) Tx 

func (w *DB) Tx(fn func(w *DB) error) (err error)

Tx the transaction

func (*DB) Txx 

func (w *DB) Txx(ctx context.Context, fn func(ctx context.Context, tx *DB) error) (err error)

Txx the transaction with context

type DBConfig 

type DBConfig struct {
	DataSource   string `yaml:"dataSource"`
	MaxOpenConns int    `yaml:"maxOpenConns"`
	DBType       string `yaml:"dbType"`
}

type DBConn 

type DBConn struct {
	*sql.DB
	// contains filtered or unexported fields
}

type Dialect 

type Dialect interface {
	// GetName get dialect's name
	GetName() string

	// Quote quotes field name to avoid SQL parsing exceptions by using a reserved word as a field name
	Quote(key string) string
}

Dialect interface contains behaviors that differ across SQL database

func GetDialect 

func GetDialect(name string) (dialect Dialect, ok bool)

GetDialect gets the dialect for the specified dialect name

type Hook 

type Hook struct {
	Errs []error
	// contains filtered or unexported fields
}

func NewHook 

func NewHook(db *DB) *Hook

func (*Hook) Err 

func (h *Hook) Err(err error) error

Err add error

func (*Hook) Error 

func (h *Hook) Error() error

Error format happened errors

func (*Hook) HasError 

func (h *Hook) HasError() int

HasError has errors

type IModel 

type IModel interface {
	TableName() string
	PK() string
}

Model interface

type ISqlx 

type ISqlx interface {
	Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
	QueryRowx(query string, args ...interface{}) *sqlx.Row
	Get(dest interface{}, query string, args ...interface{}) error
	Select(dest interface{}, query string, args ...interface{}) error
	Exec(query string, args ...interface{}) (sql.Result, error)
	Preparex(query string) (*sqlx.Stmt, error)
	Rebind(query string) string
	DriverName() string
}

type JSONText 

type JSONText json.RawMessage

JSONText is a json.RawMessage, which is a []byte underneath. Value() validates the json format in the source, and returns an error if the json is not valid. Scan does no validation. JSONText additionally implements `Unmarshal`, which unmarshals the json within to an interface{}

func (JSONText) MarshalBinary 

func (j JSONText) MarshalBinary() ([]byte, error)

func (JSONText) MarshalJSON 

func (j JSONText) MarshalJSON() ([]byte, error)

MarshalJSON returns the *j as the JSON encoding of j.

func (*JSONText) Scan 

func (j *JSONText) Scan(src interface{}) error

Scan stores the src in *j. No validation is done.

func (JSONText) String 

func (j JSONText) String() string

String supports pretty printing for JSONText types.

func (*JSONText) Unmarshal 

func (j *JSONText) Unmarshal(v interface{}) error

Unmarshal unmarshal's the json in j to v, as in json.Unmarshal.

func (*JSONText) UnmarshalBinary 

func (j *JSONText) UnmarshalBinary(data []byte) error

func (*JSONText) UnmarshalJSON 

func (j *JSONText) UnmarshalJSON(data []byte) error

UnmarshalJSON sets *j to a copy of data

func (JSONText) Value 

func (j JSONText) Value() (driver.Value, error)

Value returns j as a value. This does a validating unmarshal into another RawMessage. If j is invalid json, it returns an error.

type Logger 

type Logger interface {
	Printf(format string, v ...interface{})
}

Logger represents a logging collector. You can pass a logging collector to gosql.SetLogger(myCollector) to make it collect QueryStatus messages after executing a query.

type Mapper 

type Mapper struct {
	SQLBuilder
	// contains filtered or unexported fields
}

func Table 

func Table(t string) *Mapper

Table select table name

func (*Mapper) Count 

func (m *Mapper) Count() (num int64, err error)

Count data from to map[string]interface

func (*Mapper) Create 

func (m *Mapper) Create(data map[string]interface{}) (lastInsertId int64, err error)

Create data from to map[string]interface

func (*Mapper) Delete 

func (m *Mapper) Delete() (affected int64, err error)

Delete data from to map[string]interface

func (*Mapper) ShowSQL 

func (m *Mapper) ShowSQL() *Mapper

func (*Mapper) Update 

func (m *Mapper) Update(data map[string]interface{}) (affected int64, err error)

Update data from to map[string]interface

func (*Mapper) Where 

func (m *Mapper) Where(str string, args ...interface{}) *Mapper

Where

type QueryStatus 

type QueryStatus struct {
	Query string
	Args  interface{}

	Start time.Time
	End   time.Time

	Err error
}

QueryStatus represents the status of a query after being executed.

func (*QueryStatus) String 

func (q *QueryStatus) String() string

String returns a formatted log message.

type ReflectMapper 

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

func NewReflectMapper 

func NewReflectMapper(tagName string) *ReflectMapper

func (*ReflectMapper) FieldByName 

func (r *ReflectMapper) FieldByName(v reflect.Value, name string) reflect.Value

FieldByName returns a field by its mapped name as a reflect.Value. Panics if v's Kind is not Struct or v is not Indirectable to a struct Kind. Returns zero Value if the name is not found.

func (*ReflectMapper) FieldMap 

func (r *ReflectMapper) FieldMap(v reflect.Value) map[string]reflect.Value

FieldMap returns the mapper's mapping of field names to reflect values. Panics if v's Kind is not Struct, or v is not Indirectable to a struct kind.

type SQLBuilder 

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

func (*SQLBuilder) Where 

func (s *SQLBuilder) Where(str string, args ...interface{})

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.