qsql

package module
v0.0.2 Latest Latest
Warning

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

 Go to latest Published: Jan 3, 2024 License: MIT Imports: 16 Imported by: 5

README

qsql is a supplement to the go sql package

Refere to:

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

Example:

More example see the example directory.

Using a manual cache

package db

import (
	"github.com/gwaylib/conf"
	"github.com/gwaylib/errors"
	"github.com/gwaylib/qsql"
	_ "github.com/go-sql-driver/mysql"
)

func init() {
    db, err := qsql.Open(qsql.DRV_NAME_MYSQL, dsn)
    if err != nil{
        panic(err)
    }
    qsql.RegCache("main", db)
}

Using etc cache

Assume that the configuration file path is: './etc/db.cfg'

The etc file content

[main]
driver: mysql
dsn: username:passwd@tcp(127.0.0.1:3306)/main?timeout=30s&strict=true&loc=Local&parseTime=true&allowOldPasswords=1
max_life_time:7200 # seconds
max_idle_time:0 # seconds
max_idle_conns:0 # num
max_open_conns:0 # num

[log]
driver: mysql
dsn: username:passwd@tcp(127.0.0.1:3306)/log?timeout=30s&strict=true&loc=Local&parseTime=true&allowOldPasswords=1
max_life_time:7200

Make a package for connection cache with ini

package db

import (
	"github.com/gwaylib/conf"
	"github.com/gwaylib/qsql"
	_ "github.com/go-sql-driver/mysql"
)

func init() {
    qsql.RegCacheWithIni(conf.RootDir() + "/etc/db.cfg")
}

func GetCache(section string) *qsql.DB {
	return qsql.GetCache(section)
}

func HasCache(section string) (*qsql.DB, error) {
	return qsql.HasCache(section)
}

func CloseCache() {
	qsql.CloseCache()
}

Call a cache

mdb := qsql.GetCache("main")

Call standar sql

mdb := qsql.GetCache("main") 

row := mdb.QueryRow("SELECT * ...")
// ...

rows, err := mdb.Query("SELECT * ...")
// ...

result, err := mdb.Exec("UPDATE ...")
// ...

Insert a struct to db(using reflect)

type User struct{
    Id     int64  `db:"id,auto_increment"` // flag "autoincrement", "auto_increment" are supported .
    Name   string `db:"name"`
    Ignore string `db:"-"` // ignore flag: "-"
}

func main() {
    mdb := qsql.GetCache("main") 

    var u = &User{
        Name:"testing",
    }

    // Insert data with driver.
    if _, err := mdb.InsertStruct(u, "testing"); err != nil{
        // ... 
    }
    // ...
}

Quick query way


// Way 1: query result to a struct.
type User struct{
    Id   int64 `db:"id"`
    Name string `db:"name"`
}

func main() {
    mdb := qsql.GetCache("main") 
    var u = *User{}
    if err := mdb.QueryStruct(u, "SELECT id, name FROM a WHERE id = ?", id)
    if err != nil{
        // sql.ErrNoRows has been replace by errors.ErrNoData
        if errors.ErrNoData.Equal(err) {
           // no data
        }
        // ...
    }
    // ..
    
    // Way 2: query row to struct
    mdb := qsql.GetCache("main") 
    var u = *User{}
    if err := mdb.ScanStruct(mdb.QueryRow("SELECT id, name FROM a WHERE id = ?", id), u); err != nil {
        // sql.ErrNoRows has been replace by errors.ErrNoData
        if errors.ErrNoData.Equal(err) {
           // no data
        }
        // ...
    }
    
    // Way 3: query result to structs
    mdb := qsql.GetCache("main") 
    var u = []*User{}
    if err := mdb.QueryStructs(&u, "SELECT id, name FROM a WHERE id = ?", id); err != nil {
        // ...
    }
    if len(u) == 0{
        // data not found
        // ...
    }
    // .. 
    
    // Way 4: query rows to structs
    mdb := qsql.GetCache("main") 
    rows, err := mdb.Query("SELECT id, name FROM a WHERE id = ?", id)
    if err != nil {
        // ...
    }
    defer qsql.Close(rows)
    var u = []*User{}
    if err := mdb.ScanStructs(rows, &u); err != nil{
        // ...
    }
    if len(u) == 0{
        // data not found
        // ...
    }

}

Query an element which is implemented sql.Scanner

func main() {
    mdb := qsql.GetCache("main") 
    count := 0
    if err := mdb.QueryElem(&count, "SELECT count(*) FROM a WHERE id = ?", id); err != nil{
        // sql.ErrNoRows has been replace by errors.ErrNoData
        if errors.ErrNoData.Equal(err) {
           // no data
        }
        // ...
    }
}

Extend the where in stmt

// Example for the first input:
func main() {
    mdb := qsql.GetCache("main") 
    args:=[]int{1,2,3}
    mdb.Query(fmt.Sprintf("select * from table_name where in (%s)", mdb.StmtWhereIn(0, len(args))), qsql.StmtSliceArgs(args)...)
    // Or
    mdb.Query(fmt.Sprintf("select * from table_name where in (%s)", mdb.StmtWhereIn(0, len(args), qsql.DRV_NAME_MYSQL), qsql.StmtSliceArgs(args)...)
    
    // Example for the second input:
    mdb.Query(fmt.Sprintf("select * from table_name where id=? in (%s)", qsql.StmtWhereIn(1,len(args)), qsql.StmtSliceArgs(id, args)...)
}

Mass query.

func main() {
    mdb := qsql.GetCache("main") 
    qSql = &qsql.Page{
         CountSql:`SELECT count(1) FROM user_info WHERE create_time >= ? AND create_time <= ?`,
         DataSql:`SELECT mobile, balance FROM user_info WHERE create_time >= ? AND create_time <= ?`
    }
    count, titles, result, err := qSql.QueryPageArray(db, true, condition, 0, 10)
    // ...
    // Or
    count, titles, result, err := qSql.QueryPageMap(db, true, condtion, 0, 10)
    // ...
    if err != nil {
    // ...
    }
}

Make a lazy tx commit

// commit the tx
func main() {
    mdb := qsql.GetCache("main") 
    tx, err := mdb.Begin()
    if err != nil{
        // ...
    }
    fn := func() error {
      if err := tx.Exec("UPDATE testing SET name = ? WHERE id = ?", id); err != nil{
        return err
      }
      return nil
    }
    if err := qsql.Commit(tx, fn); err != nil {
        // ...
    }
}

Documentation

Overview

Provides database connections in factory mode to optimize database connections

Example: 

qSql = &qsql.Page{
     CountSql:`SELECT count(1) FROM user_info WHERE create_time >= ? AND create_time <= ?`,
     DataSql:`SELECT mobile, balance FROM user_info WHERE create_time >= ? AND create_time <= ?`
}

count, titles, result, err := qSql.QueryPageArray(db, true, condition, 0, 10) ... Or count, titles, result, err := qSql.QueryPageMap(db, true, condtion, 0, 10) ... if err != nil { ... }

Index

Constants

View Source 
const (
	DRV_NAME_MYSQL     = "mysql"
	DRV_NAME_ORACLE    = "oracle" // or "oci8"
	DRV_NAME_POSTGRES  = "postgres"
	DRV_NAME_SQLITE3   = "sqlite3"
	DRV_NAME_SQLSERVER = "sqlserver" // or "mssql"

)

Variables

This section is empty.

Functions

func Close 

func Close(closer io.Closer)

A lazy function to closed the io.Closer

func CloseCache 

func CloseCache()

Close all instance in the cache.

func Commit 

func Commit(tx *sql.Tx, fn func() error) error

A lazy function to commit the *sql.Tx if will auto commit when the function is nil error, or do a rollback and return the function error.

func InsertStruct 

func InsertStruct(drvName string, exec Execer, obj interface{}, tbName string) (sql.Result, error)

Reflect one db data to the struct. the struct tag format like `db:"field_title"`, reference to: http://github.com/jmoiron/sqlx

func InsertStructContext 

func InsertStructContext(drvName string, exec Execer, ctx context.Context, obj interface{}, tbName string) (sql.Result, error)

func QueryElem 

func QueryElem(queryer Queryer, result interface{}, querySql string, args ...interface{}) error

Query one field to a sql.Scanner.

func QueryElemContext 

func QueryElemContext(queryer Queryer, ctx context.Context, result interface{}, querySql string, args ...interface{}) error

func QueryElems 

func QueryElems(queryer Queryer, result interface{}, querySql string, args ...interface{}) error

Query one field to a sql.Scanner array.

func QueryElemsContext 

func QueryElemsContext(queryer Queryer, ctx context.Context, result interface{}, querySql string, args ...interface{}) error

func QueryPageArr 

func QueryPageArr(queryer Queryer, querySql string, args ...interface{}) (titles []string, result [][]interface{}, err error)

Reflect the query result to a string array.

func QueryPageArrContext 

func QueryPageArrContext(queryer Queryer, ctx context.Context, querySql string, args ...interface{}) (titles []string, result [][]interface{}, err error)

func QueryPageMap 

func QueryPageMap(queryer Queryer, querySql string, args ...interface{}) (titles []string, result []map[string]interface{}, err error)

Reflect the query result to a string map.

func QueryPageMapContext 

func QueryPageMapContext(queryer Queryer, ctx context.Context, querySql string, args ...interface{}) (titles []string, result []map[string]interface{}, err error)

func QueryStruct 

func QueryStruct(queryer Queryer, obj interface{}, querySql string, args ...interface{}) error

Reflect the sql.Query result to a struct.

func QueryStructContext 

func QueryStructContext(queryer Queryer, ctx context.Context, obj interface{}, querySql string, args ...interface{}) error

func QueryStructs 

func QueryStructs(queryer Queryer, obj interface{}, querySql string, args ...interface{}) error

Reflect the sql.Query result to a struct array. Return empty array if data not found.

func QueryStructsContext 

func QueryStructsContext(queryer Queryer, ctx context.Context, obj interface{}, querySql string, args ...interface{}) error

func RegCache 

func RegCache(key string, db *DB)

Register a db to the connection pool by manully.

func RegCacheWithIni 

func RegCacheWithIni(iniPath string)

func Rollback 

func Rollback(tx *sql.Tx)

A lazy function to rollback the *sql.Tx

func ScanStruct 

func ScanStruct(rows Rows, obj interface{}) error

Relect the sql.Rows to a struct.

func ScanStructs 

func ScanStructs(rows Rows, obj interface{}) error

Reflect the sql.Rows to a struct array. Return empty array if data not found. Refere to: github.com/jmoiron/sqlx

func StmtSliceArgs 

func StmtSliceArgs(args ...interface{}) []interface{}

func StmtWhereIn 

func StmtWhereIn(drvName string, paramStartIdx, paramsLen int) string

Types

type Bool 

type Bool bool

Bool type

func (*Bool) Scan 

func (v *Bool) Scan(i interface{}) error

func (*Bool) Value 

func (v *Bool) Value() (driver.Value, error)

type DB 

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

func GetCache 

func GetCache(key string) *DB

Get the db instance from the cache.

func HasCache 

func HasCache(key string) (*DB, error)

Checking the cache does it have a db instance.

func NewDB 

func NewDB(drvName string, db *sql.DB) *DB

func Open 

func Open(drvName, dsn string) (*DB, error)

Implement the sql.Open

func (*DB) Close 

func (db *DB) Close() error

func (*DB) Commit 

func (db *DB) Commit(tx *sql.Tx, fn func() error) error

A lazy function to commit the *sql.Tx if will auto commit when the function is nil error, or do a rollback and return the function error.

func (*DB) DriverName 

func (db *DB) DriverName() string

func (*DB) InsertStruct 

func (db *DB) InsertStruct(obj interface{}, tbName string) (sql.Result, error)

Reflect one db data to the struct. the struct tag format like `db:"field_title"`, reference to: http://github.com/jmoiron/sqlx

func (*DB) InsertStructContext 

func (db *DB) InsertStructContext(ctx context.Context, obj interface{}, tbName string) (sql.Result, error)

func (*DB) IsClose 

func (db *DB) IsClose() bool

func (*DB) QueryElem 

func (db *DB) QueryElem(result interface{}, querySql string, args ...interface{}) error

Query one field to a sql.Scanner.

func (*DB) QueryElemContext 

func (db *DB) QueryElemContext(ctx context.Context, result interface{}, querySql string, args ...interface{}) error

func (*DB) QueryElems 

func (db *DB) QueryElems(result interface{}, querySql string, args ...interface{}) error

Query one field to a sql.Scanner array.

func (*DB) QueryElemsContext 

func (db *DB) QueryElemsContext(ctx context.Context, result interface{}, querySql string, args ...interface{}) error

func (*DB) QueryPageArr 

func (db *DB) QueryPageArr(querySql string, args ...interface{}) (titles []string, result [][]interface{}, err error)

Reflect the query result to a string array.

func (*DB) QueryPageArrContext 

func (db *DB) QueryPageArrContext(ctx context.Context, querySql string, args ...interface{}) (titles []string, result [][]interface{}, err error)

func (*DB) QueryPageMap 

func (db *DB) QueryPageMap(querySql string, args ...interface{}) (titles []string, result []map[string]interface{}, err error)

Reflect the query result to a string map.

func (*DB) QueryPageMapContext 

func (db *DB) QueryPageMapContext(ctx context.Context, querySql string, args ...interface{}) (titles []string, result []map[string]interface{}, err error)

func (*DB) QueryStruct 

func (db *DB) QueryStruct(obj interface{}, querySql string, args ...interface{}) error

Reflect the sql.Query result to a struct.

func (*DB) QueryStructContext 

func (db *DB) QueryStructContext(ctx context.Context, obj interface{}, querySql string, args ...interface{}) error

func (*DB) QueryStructs 

func (db *DB) QueryStructs(obj interface{}, querySql string, args ...interface{}) error

Reflect the sql.Query result to a struct array. Return empty array if data not found.

func (*DB) QueryStructsContext 

func (db *DB) QueryStructsContext(ctx context.Context, obj interface{}, querySql string, args ...interface{}) error

func (*DB) ScanStruct 

func (db *DB) ScanStruct(rows Rows, obj interface{}) error

Relect the sql.Rows to a struct.

func (*DB) ScanStructs 

func (db *DB) ScanStructs(rows Rows, obj interface{}) error

Reflect the sql.Rows to a struct array. Return empty array if data not found. Refere to: github.com/jmoiron/sqlx

func (*DB) StmtWhereIn 

func (db *DB) StmtWhereIn(paramStartIdx, paramsLen int) string

type DBData 

type DBData string

通用的字符串查询

func (*DBData) Scan 

func (d *DBData) Scan(i interface{}) error

func (*DBData) String 

func (d *DBData) String() string

type Execer 

type Execer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

type Float64 

type Float64 float64

Float64 type

func (*Float64) Scan 

func (v *Float64) Scan(i interface{}) error

func (Float64) Value 

func (v Float64) Value() (driver.Value, error)

type Int64 

type Int64 int64

Int64 type

func (*Int64) Scan 

func (v *Int64) Scan(i interface{}) error

func (Int64) Value 

func (v Int64) Value() (driver.Value, error)

type PageArgs 

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

func NewPageArgs 

func NewPageArgs(args ...interface{}) *PageArgs

func (*PageArgs) Limit 

func (p *PageArgs) Limit(offset, limit int64) *PageArgs

using offset and limit when limit is set.

type PageSql 

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

func NewPageSql 

func NewPageSql(countSql, dataSql string) *PageSql

func (PageSql) CountSql 

func (p PageSql) CountSql() string

func (PageSql) DataSql 

func (p PageSql) DataSql() string

func (PageSql) FmtPage 

func (p PageSql) FmtPage(args ...interface{}) PageSql

fill the page sql with fmt arg, and return a new page Typically used for table name formatting

func (*PageSql) QueryCount 

func (p *PageSql) QueryCount(db *DB, args ...interface{}) (int64, error)

func (*PageSql) QueryPageArr 

func (p *PageSql) QueryPageArr(db *DB, doCount bool, args *PageArgs) (int64, []string, [][]interface{}, error)

func (*PageSql) QueryPageMap 

func (p *PageSql) QueryPageMap(db *DB, doCount bool, args *PageArgs) (int64, []string, []map[string]interface{}, error)

type Queryer 

type Queryer interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row

	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

type Rows 

type Rows interface {
	Close() error
	Columns() ([]string, error)
	Err() error
	Next() bool
	Scan(...interface{}) error
}

type String 

type String string

String type

func (*String) Scan 

func (v *String) Scan(i interface{}) error

func (*String) String 

func (v *String) String() string

func (String) Value 

func (v String) Value() (driver.Value, error)

Source Files

View all Source files

Directories

Path Synopsis
example 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.