database

package module
v0.0.0-...-4e5982d Latest Latest
Warning

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

 Go to latest Published: Sep 3, 2023 License: MIT Imports: 15 Imported by: 7

README

** This is project is archived and renamed to qsql **

Refere to:

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

Example:

More example see the example directory.

Using etc cache

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

The etc file content

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

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

Make a package for connection cache

package db

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

var dbFile = conf.RootDir() + "/etc/db.cfg"

func init() {
   database.REFLECT_DRV_NAME = database.DRV_NAME_MYSQL 
}

func GetCache(section string) *database.DB {
	return database.GetCache(dbFile, section)
}

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

func CloseCache() {
	database.CloseCache()
}

Call a cache

mdb := db.GetCache("master")

Call standar sql

mdb := db.GetCache("master") 
// or mdb = <sql.Tx>

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

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

// result, err := mdb.Exec("UPDATE ...")
result, err := database.Exec(mdb, "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: "-"
}

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

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

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

Quick query way


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

mdb := db.GetCache("master") 
// or mdb = <sql.Tx>
var u = *User{}
if err := database.QueryStruct(mdb, u, "SELECT id, name FROM a WHERE id = ?", id)
if err != nil{
    // ...
}
// ..

// Way 2: query row to struct
mdb := db.GetCache("master") 
// or mdb = <sql.Tx>
var u = *User{}
if err := database.ScanStruct(database.QueryRow(mdb, "SELECT id, name FROM a WHERE id = ?", id), u); err != nil {
    // ...
}

// Way 3: query result to structs
mdb := db.GetCache("master") 
// or mdb = <sql.Tx>
var u = []*User{}
if err := database.QueryStructs(mdb, &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 := db.GetCache("master") 
// or mdb = <sql.Tx>
rows, err := database.Query(mdb, "SELECT id, name FROM a WHERE id = ?", id)
if err != nil {
    // ...
}
defer database.Close(rows)
var u = []*User{}
if err := database.ScanStructs(rows, &u); err != nil{
    // ...
}
if len(u) == 0{
    // data not found
    // ...
}

Query an element which is implemented sql.Scanner

mdb := db.GetCache("master") 
// or mdb = <sql.Tx>
count := 0
if err := database.QueryElem(mdb, &count, "SELECT count(*) FROM a WHERE id = ?", id); err != nil{
    // ...
}

Mass query.

mdb := db.GetCache("master") 
qSql = &database.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 MultiTx

multiTx := []*database.MultiTx{}
multiTx = append(multiTx, database.NewMultiTx(
    "UPDATE testing SET name = ? WHERE id = ?",
    id,
))
multiTx = append(multiTx, database.NewMultiTx(
    "UPDATE testing SET name = ? WHERE id = ?",
    id,
))

// do exec multi tx
mdb := db.GetCache("master") 
tx, err := mdb.Begin()
if err != nil{
    // ...
}
if err := database.ExecMutlTx(tx, multiTx); err != nil {
    database.Rollback(tx)
    // ...
}
if err := tx.Commit(); err != nil {
    database.Rollback(tx)
    // ...
}

Documentation

Overview

Provides database connections in factory mode to optimize database connections

以工厂的模式构建数据库，以避免数据库被多次打开。 因database/sql本身已实现连接池，因此没有必要创建多个同一的数据库连接实例

Example: 

qSql = &database.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

View Source 
var (
	// Whe reflect the QueryStruct, InsertStruct, it need set the Driver first.
	// For example:
	// func init(){
	//     database.REFLECT_DRV_NAME = database.DEV_NAME_SQLITE3
	// }
	// Default is using the mysql driver.
	REFLECT_DRV_NAME = DRV_NAME_MYSQL
)

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 Exec 

func Exec(db Execer, querySql string, args ...interface{}) (sql.Result, error)

A way implement the sql.Exec

func ExecContext 

func ExecContext(db Execer, ctx context.Context, querySql string, args ...interface{}) (sql.Result, error)

func ExecMultiTx 

func ExecMultiTx(tx *sql.Tx, mTx []*MultiTx) error

A way to ran multiply tx

func ExecMultiTxContext 

func ExecMultiTxContext(tx *sql.Tx, ctx context.Context, mTx []*MultiTx) error

func InsertStruct 

func InsertStruct(exec Execer, obj interface{}, tbName string, drvNames ...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 When you no set the REFLECT_DRV_NAME, you can point out with the drvName

func InsertStructContext 

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

func Query 

func Query(db Queryer, querySql string, args ...interface{}) (*sql.Rows, error)

A sql.Query implements

func QueryContext 

func QueryContext(db Queryer, ctx context.Context, querySql string, args ...interface{}) (*sql.Rows, error)

func QueryElem 

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

Query one field to a sql.Scanner.

func QueryElemContext 

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

func QueryElems 

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

Query one field to a sql.Scanner array.

func QueryElemsContext 

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

func QueryPageArr 

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

Reflect the query result to a string array.

func QueryPageArrContext 

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

func QueryPageMap 

func QueryPageMap(db 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(db Queryer, ctx context.Context, querySql string, args ...interface{}) (titles []string, result []map[string]interface{}, err error)

func QueryRow 

func QueryRow(db Queryer, querySql string, args ...interface{}) *sql.Row

A sql.QueryRow implements

func QueryRowContext 

func QueryRowContext(db Queryer, ctx context.Context, querySql string, args ...interface{}) *sql.Row

func QueryStruct 

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

Reflect the sql.Query result to a struct.

func QueryStructContext 

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

func QueryStructs 

func QueryStructs(db 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(db Queryer, ctx context.Context, obj interface{}, querySql string, args ...interface{}) error

func RegCache 

func RegCache(iniFileName, sectionName string, db *DB)

Register a db to the connection pool by manully.

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

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
}

仅继承并重写sql.DB, 不增加新的方法， 以便可直接使用sql.DB的方法，提高访问效率与降低使用复杂性

func GetCache 

func GetCache(iniFileName, sectionName string) *DB

Get the db instance from the cache. If the db not in the cache, it will create a new instance from the ini file.

func HasCache 

func HasCache(etcFileName, sectionName 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) DriverName 

func (db *DB) DriverName() string

func (*DB) IsClose 

func (db *DB) IsClose() bool

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 MultiTx 

type MultiTx struct {
	Query string
	Args  []interface{}
}

func NewMultiTx 

func NewMultiTx(query string, args ...interface{}) *MultiTx

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.