gosql

package module
v1.0.4 Latest Latest
Warning

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

 Go to latest Published: Sep 26, 2018 License: MIT Imports: 16 Imported by: 0

README

gosql

The package based on sqlx, It's simple and keep simple

Build Status codecov Go Report Card GoDoc

Usage

Connection database and use sqlx original function,See the https://github.com/jmoiron/sqlx

import (
    _ "github.com/go-sql-driver/mysql" //mysql driver
    "github.com/ilibs/gosql"
)

func main(){
    configs := make(map[string]*gosql.Config)

    configs["default"] = &gosql.Config{
        Enable:  true,
        Driver:  "mysql",
        Dsn:     "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Asia%2FShanghai",
        ShowSql: true,
    }

    //connection database
    gosql.Connect(configs)

    gosql.DB().QueryRowx("select * from users where id = 1")
}

Use default database, So you can use wrapper function

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

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

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

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

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

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

Using struct

type Users 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 *Users) DbName() string {
	return "default"
}

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

func (u *Users) PK() string {
	return "id"
}

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

//All
user := make([]*Users,0)
gosql.Model(&user).All()

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

//Update
gosql.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update()
//If you need to update the zero value, you can do so
gosql.Model(&User{Status:0}).Where("id=?",1).Update("status")

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

If you use struct to generate where conditions

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

//Update default use primary key as the condition
gosql.Model(&User{Id:1,Name:"test2"}).Update()
//Use custom conditions
//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)
gosql.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()

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

But the zero value is filtered by default, you can specify fields that are not filtered. For example

user := &Users{Id:1,Status:0}
gosql.Model(&user).Get("status")

You can use the genstruct tool to quickly generate database structs

Transaction

The Tx function has a callback function, if an error is returned, the transaction rollback

gosql.Tx(func(tx *sqlx.Tx) error {
    for id := 1; id < 10; id++ {
        user := &Users{
            Id:    id,
            Name:  "test" + strconv.Itoa(id),
            Email: "test" + strconv.Itoa(id) + "@test.com",
        }

        gosql.Model(user, tx).Create()

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

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

    if err != nil {
        return err
    }

    return nil
})

If you need to invoke context, you can use gosql.Txx

Automatic time

If your fields contain the following field names, they will be updated automatically

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",
}

Using Map

Create Update Delete Count support map[string]interface,For example:

//Create
gosql.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
gosql.Table("users").Where("id = ?", 1).Update(map[string]interface{}{
    "name":  "fifsky",
    "email": "fifsky@test.com",
})

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

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

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

//Transaction `tx` is *sqlx.Tx
gosql.Table("users",tx).Where("id = ?", 1}).Count()

sql.Null*

Now Model support sql.Null* field's, Note, however, that if sql.Null* is also filtered by zero values,For example

type Users struct {
	Id          int            `db:"id"`
	Name        string         `db:"name"`
	Email       string         `db:"email"`
	Status      int            `db:"status"`
	SuccessTime sql.NullString `db:"success_time" json:"success_time"`
	CreatedAt   time.Time      `db:"created_at" json:"created_at"`
	UpdatedAt   time.Time      `db:"updated_at" json:"updated_at"`
}

user := &Users{
    Id: 1,
    SuccessTime: sql.NullString{
        String: "2018-09-03 00:00:00",
        Valid:  false,
    }
}

err := Model(user).Get()

Builder SQL:

Query: SELECT * FROM users WHERE (id=?);
Args:  []interface {}{1}
Time:  0.00082s

If sql.NullString of Valid attribute is false, SQL builder will ignore this zero value

Hooks

Hooks are functions that are called before or after creation/querying/updating/deletion.

If you have defiend specified methods for a model, it will be called automatically when creating, updating, querying, deleting, and if any callback returns an error, gosql will stop future operations and rollback current transaction.

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

Example:

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

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

BeforeChange and AfterChange only used in create/update/delete

All Hooks:

BeforeChange
AfterChange
BeforeCreate
AfterCreate
BeforeUpdate
AfterUpdate
BeforeDelete
AfterDelete
BeforeFind
AfterFind

Hook func type supports multiple ways:

func (u *Users) BeforeCreate()
func (u *Users) BeforeCreate() (err error)
func (u *Users) BeforeCreate(tx *sqlx.Tx)
func (u *Users) BeforeCreate(tx *sqlx.Tx) (err error)

Thanks

sqlx https://github.com/jmoiron/sqlx

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",
	}
)
View Source 
var Default = "default"

Default set database default tag name

View Source 
var FatalExit = true

If database fatal exit

Functions

func Connect 

func Connect(configs map[string]*Config) (err error)

Connect database

func DB 

func DB(name ...string) *sqlx.DB

DB gets the specified database engine, or the default DB if no name is specified.

func Exec 

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

Exec default database

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 List 

func List() map[string]*sqlx.DB

List gets the list of database engines

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 Select 

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

Select default database

func SetLogger 

func SetLogger(l Logger)

func SetLogging added in v1.0.1 

func SetLogging(logging bool)

SetLogging set default logger

func Tx 

func Tx(fn func(tx *sqlx.Tx) error) error

Tx default database the transaction

func Txx 

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

Txx default database the transaction with context

Types

type Builder 

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

func Model 

func Model(model interface{}, tx ...*sqlx.Tx) *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) 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) ShowSQL 

func (b *Builder) ShowSQL() *Builder

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 Config 

type Config struct {
	Enable       bool   `toml:"enable" json:"enable"`
	Driver       string `toml:"driver" json:"driver"`
	Dsn          string `toml:"dsn" json:"dsn"`
	MaxOpenConns int    `toml:"max_open_conns" json:"max_open_conns"`
	MaxIdleConns int    `toml:"max_idle_conns" json:"max_idle_conns"`
	MaxLifetime  int    `toml:"max_lefttime" json:"max_lefttime"`
	ShowSql      bool   `toml:"show_sql" json:"show_sql"`
}

Config is database connection configuration

type Hook added in v1.0.1 

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

func NewHook added in v1.0.1 

func NewHook(wrapper *Wrapper) *Hook

func (*Hook) Err added in v1.0.1 

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

Err add error

func (*Hook) Error added in v1.0.1 

func (h *Hook) Error() error

Error format happened errors

func (*Hook) HasError added in v1.0.1 

func (h *Hook) HasError() int

HasError has errors

type IModel 

type IModel interface {
	TableName() string
	DbName() 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)
}

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, tx ...*sqlx.Tx) *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) 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{})

type Wrapper 

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

func ShowSql 

func ShowSql() *Wrapper

func Use 

func Use(db string) *Wrapper

Use is change database

func WithTx 

func WithTx(tx *sqlx.Tx) *Wrapper

WithTx use the specified transaction session

func (*Wrapper) Exec 

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

Exec wrapper sqlx.Exec

func (*Wrapper) Get 

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

Get wrapper sqlx.Get

func (*Wrapper) Import 

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

Import SQL DDL from sql file

func (*Wrapper) QueryRowx 

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

QueryRowx wrapper sqlx.QueryRowx

func (*Wrapper) Queryx 

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

Queryx wrapper sqlx.Queryx

func (*Wrapper) Select 

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

Select wrapper sqlx.Select

func (*Wrapper) Table 

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

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

func (*Wrapper) Tx 

func (w *Wrapper) Tx(fn func(tx *sqlx.Tx) error) (err error)

Tx the transaction

func (*Wrapper) Txx 

func (w *Wrapper) Txx(ctx context.Context, fn func(ctx context.Context, tx *sqlx.Tx) error) (err error)

Txx the transaction with context

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.