ploto

package module
v0.4.0 Latest Latest
Warning

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

 Go to latest Published: Apr 5, 2024 License: MIT Imports: 14 Imported by: 0

README

ploto

A go Library for scan database/sql rows to struct、slice、other types, and support SQL logging

extensions to golang's database/sql

功能

  • Scan rows, 支持struct,slice,map,其他基本类型
  • 多数据库配置连接管理
  • sql日志

说明

仅对database/sql的DB.Query,DB.QueryContext,DB.QueryRow,DB.QueryRowContext进行封装，其他使用保持不变.

  • Query/QueryContext 结果Scan支持*Slice、*Struct、*Map、*int等基本类型.
  • QueryRow/QueryRowContext 结果Scan支持*Struct、*Map、*int等基本类型. 结果为空返回sql.ErrNoRows
  • Tx 事务增加transaction_id

Using

配合多数据库管理一起使用
package main

import (
    "encoding/json"
    "fmt"
    "github.com/feiin/ploto"
     _ "github.com/go-sql-driver/mysql"
)

//Sql日志输出
type MyStdLogger struct {
}

func (m *MyStdLogger) Info(ctx context.Context, format string, v ...interface{}) {
	//...
}
func (m *MyStdLogger) Debug(ctx context.Context, format string, v ...interface{}) {
	//....
}

func (m *MyStdLogger) Warn(ctx context.Context, format string, v ...interface{}) {
	//...
}

func (m *MyStdLogger) Error(ctx context.Context, format string, v ...interface{}) {
	//....
}


func getConfig() (config Configs) {
    testConfig := `{"mysql": {
        "clients": {
            "test":{
                "host": "127.0.0.1",
                "port": 3306,
                "user": "root",
                "password": "root",
                "database": "test"
            }
        },
        "default": {
            "port": 3306,
            "dialect": "mysql",
            "pool": {
                "maxIdleConns": 2,
                "maxLeftTime": 60000, 
                "maxOpenConns": 5
            },
            "dialectOptions": {
                "parseTime":true,
                "multiStatements": true,
                "writeTimeout": "3000ms",
                "readTimeout": "3000ms",
                "timeout":"3000ms",
				"parseTime": true,
				"loc":"Local",
            }   
        }
    }}`

    var conf Configs

    json.Unmarshal([]byte(testConfig), &conf)

    // fmt.Printf("conf %+v", conf)
    return conf

}

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}

type Configs struct {
    Mysql ploto.DialectConfig `json:"mysql"`
   // Mssql ploto.DialectConfig `json:"mssql"`
}

func main() {

    configs := getConfig()
	defaultLogger := &MyStdLogger{}

    db, err := ploto.Open(configs.Mysql, defaultLogger)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    
    var users []User
    err = db.Use("test").Query("select * from users where id<100").Scan(&users)
    if err != nil {
        panic(err)
    }
    fmt.Printf("users %+v", users)

	//Exec....
	result, err := db.Use("test").Exec("update users set name=? where  id=?","xxx",1)
    if err != nil {
		//...
        panic(err)
    }
	
	
	//Exec....
	result, err := db.Use("test").Exec("insert uesrs(name,created_time) values(?,now())","xxx")
    if err != nil {
		//...
        panic(err)
    }

}
只用Scan功能

支持对rows结果转化到struct,slice，int等

struct定义字段tag为db

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}


package main

import (
	"database/sql"
	"fmt"
	"github.com/feiin/ploto"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:password@/database")
	if err != nil {
		panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
	}
	defer db.Close()

	//scan rows to slices
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var user User
		err := ploto.Scan(rows, &user)
		users = append(users, user)
	}


	//ScanResult等同上代码
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}

	//No need to Close
	err := ploto.ScanResult(rows, &users)

	//.....
	// select count(1) as cnt from users

	if rows.Next() {
		var a int64
		ploto.Scan(rows,&a)
	}
	//.....

	// select * from users where id=1

	if rows.Next() {
		var user User 
		ploto.Scan(rows,&user)
	}
	//.....
}

数据库配置

配置支持多数据库连接，格式如下：

mysql
{"mysql": {
		"clients": {
			"test":{
				"host": "127.0.0.1",
				"port": 3307,
				"user": "test",
				"password": "asfasdf@#sddfsdf",
				"database": "test"
			}
		},
		"default": {
			"port": 3306,
			"dialect": "mysql",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000, 
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"parseTime":true,
				"multiStatements": true,
				"writeTimeout": "3000ms",
				"readTimeout": "3000ms",
				"timeout":"3000ms",
				"parseTime": true,
				"loc":"Local",

			}	
		}
	}}

更多dialectOptions参数见: https://github.com/go-sql-driver/mysql#parameters

mssql
{"mssql": {
		"clients": {
	 
			"test":{
				"host": "127.0.0.1",
				"user": "sa",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"dial timeout": "10"

				}
			}
		},
		"default": {
			"port": 1433,
			"dialect": "sqlserver", //or mssql
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"dial timeout": "3"
			}
		}
	}}

更多dialectOptions 参数见:https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Scan 

func Scan(rows *sql.Rows, dest interface{}) error

Scan

func ScanResult 

func ScanResult(rows *sql.Rows, dest interface{}) error

func ScanSlice 

func ScanSlice(rows *sql.Rows, dest interface{}) error

Types

type DB 

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

func (*DB) Begin added in v0.3.5 

func (db *DB) Begin() (*Tx, error)

Begin starts a transaction. The default isolation level is dependent on the driver.

func (*DB) BeginTx added in v0.3.5 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx starts a transaction.

The provided context is used until the transaction is committed or rolled back. If the context is canceled, the sql package will roll back the transaction. Tx.Commit will return an error if the context provided to BeginTx is canceled.

The provided TxOptions is optional and may be nil if defaults should be used. If a non-default isolation level is used that the driver doesn't support, an error will be returned.

func (*DB) Exec added in v0.3.4 

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

Exec executes a query without returning any rows. The args are for any placeholder parameters in the query

func (*DB) ExecContext added in v0.3.4 

func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext executes a query without returning any rows. The args are for any placeholder parameters in the query.

func (*DB) Query 

func (db *DB) Query(query string, args ...interface{}) *RowsResult

Query executes a query that returns RowsResult, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryContext 

func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) *RowsResult

QueryContext executes a query that returns RowsResult, typically a SELECT. The args are for any placeholder parameters in the query.

func (*DB) QueryRow added in v0.2.3 

func (db *DB) QueryRow(query string, args ...interface{}) *RowResult

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*DB) QueryRowContext added in v0.2.3 

func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *RowResult

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. If the query selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*DB) RawDB 

func (db *DB) RawDB() *sql.DB

RawDB return the *sql.DB

type DefaultLogger 

type DefaultLogger struct {
}

func (DefaultLogger) Debug 

func (l DefaultLogger) Debug(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Error 

func (l DefaultLogger) Error(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Info 

func (l DefaultLogger) Info(ctx context.Context, format string, v ...interface{})

func (DefaultLogger) Warn 

func (l DefaultLogger) Warn(ctx context.Context, format string, v ...interface{})

type Dialect 

type Dialect struct {
	Clients map[string]*DB
	Configs DialectConfig
	// contains filtered or unexported fields
}

func Open 

func Open(configs DialectConfig, log LoggerInterface) (*Dialect, error)

Init init all the database clients

func (*Dialect) Close 

func (dialect *Dialect) Close() error

Close Close the database

func (*Dialect) CreateClient 

func (dialect *Dialect) CreateClient(database string) (db *DB, err error)

CreateClient create the db pool for the database

func (*Dialect) Use 

func (dialect *Dialect) Use(database string) (db *DB)

Use get the db's conn

type DialectConfig 

type DialectConfig struct {
	Clients map[string]interface{} `json:"clients"`
	Default map[string]interface{} `json:"default"`
}

type DialectDSN 

type DialectDSN interface {
	GetDialectDSN(database string, config map[string]interface{}) string
}

type LoggerInterface 

type LoggerInterface interface {
	Debug(context.Context, string, ...interface{})
	Info(context.Context, string, ...interface{})
	Warn(context.Context, string, ...interface{})
	Error(context.Context, string, ...interface{})
}

type Mssql 

type Mssql struct {
}

Mssql mssql dialector

func (Mssql) GetDialectDSN 

func (m Mssql) GetDialectDSN(database string, config map[string]interface{}) string

GetDialectDSN 

**config:{
	 	"clients": {
			"share":{
				"host": "127.0.0.1",
				"user": "sa",
				"password": "test123",
				"database": "test"
			},
			"test":{
				"host": "127.0.0.1",
				"port": 1433,
				"user": "sa",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"dial timeout": "10"

				}
			}
		},
		"default": {
			"port": 1433,
			"dialect": "sqlserver",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"dial timeout": "3"
			}
		}
	}

*

type Mysql 

type Mysql struct {
}

Mysql mysql dialector

func (Mysql) GetDialectDSN 

func (m Mysql) GetDialectDSN(database string, config map[string]interface{}) string

GetDialectDSN 

**config:{
	 	"clients": {
			"share":{
				"host": "127.0.0.1",
				"user": "test",
				"password": "test123",
				"database": "test"
			},
			"test":{
				"host": "127.0.0.1",
				"port": 3307,
				"user": "test",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"writeTimeout": "2000ms",
					"readTimeout": "2000ms",
					"timeout":"2000ms"
				}
			}
		},
		"default": {
			"port": 3306,
			"dialect": "mysql",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"writeTimeout": "3000ms",
				"readTimeout": "3000ms",
				"timeout":"3000ms"
			}
		}
	}

*

type RowResult 

type RowResult struct {
	LastError error
	// contains filtered or unexported fields
}

func (*RowResult) Err added in v0.2.3 

func (r *RowResult) Err() error

RowResult return the error of RowResult

func (*RowResult) Scan added in v0.2.3 

func (r *RowResult) Scan(dest interface{}) error

Scan RowResult's scan

type RowsResult 

type RowsResult struct {
	*sql.Rows
	LastError error
}

func (RowsResult) Close 

func (r RowsResult) Close() error

Close returns the connection to the connection pool

func (*RowsResult) Raw 

func (r *RowsResult) Raw() (*sql.Rows, error)

Raw

func (*RowsResult) Scan 

func (r *RowsResult) Scan(dest interface{}) error

Scan

type Tx added in v0.3.5 

type Tx struct {
	*sql.Tx
	DB             *DB
	TransactionID  string
	TransactionCtx context.Context
}

func (*Tx) Commit added in v0.3.5 

func (tx *Tx) Commit() error

Commit commits the transaction.

func (*Tx) Exec added in v0.3.5 

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

Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (*Tx) ExecContext added in v0.3.5 

func (tx *Tx) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)

ExecContext executes a query that doesn't return rows. For example: an INSERT and UPDATE.

func (*Tx) Query added in v0.3.5 

func (tx *Tx) Query(query string, args ...interface{}) *RowsResult

Query executes a query that returns rows, typically a SELECT.

func (*Tx) QueryContext added in v0.3.5 

func (tx *Tx) QueryContext(ctx context.Context, query string, args ...interface{}) *RowsResult

QueryContext executes a query that returns rows, typically a SELECT.

func (*Tx) QueryRow added in v0.3.5 

func (tx *Tx) QueryRow(query string, args ...interface{}) *RowResult

QueryRow executes a query that is expected to return at most one row. QueryRow always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Tx) QueryRowContext added in v0.3.5 

func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...interface{}) *RowResult

QueryRowContext executes a query that is expected to return at most one row. QueryRowContext always returns a non-nil value. Errors are deferred until Row's Scan method is called. Otherwise, the *Row's Scan scans the first selected row and discards the rest.

func (*Tx) Rollback added in v0.3.5 

func (tx *Tx) Rollback() error

Rollback aborts the transaction.

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.