sql

package
v5.0.0-...-03972fd Latest Latest
Warning

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

 Go to latest Published: Jan 20, 2022 License: MIT Imports: 13 Imported by: 0

README

Features

  • There is a wrapper over the standard database/sql library.
  • Allows you to work through Scope, while hiding the differences between the database and the transaction.
  • Allows you to transparently pass Scope through Context.
  • Allows transparent work with replicas.
  • Supports metrics.
  • Supports nested transactions.
  • Automatic processing of deadlocks.
  • Works with various databases (just provide the appropriate adapter).

Usage

package main

import (
	"context"
	"fmt"
    "log"

    "github.com/adverax/echo/database/sql"
    _ "github.com/go-sql-driver/mysql" // Any sql.DB works
)

type MyRepository struct {
	sql.Repository
}

func (repo *MyRepository) Register(
	ctx context.Context,
	a, b int,
) error {
    return repo.Transaction(
    	ctx,
    	func(ctx context.Context)error{
            scope := repo.Scope(ctx)
            // Working with scope
            _, err := scope.Exec("INSERT INTO sometable1 SET somefield = ?", a)
    		if err != nil {
    			return err
    		}
            _, err = scope.Exec("INSERT INTO sometable2 SET somefield = ?", b)
            return err
    	},
    )
}

func main() {
  // The first DSN is assumed to be the master and all
  // other to be slaves
  dsc := &sql.DSC{
  	Driver: "mysql",
  	DSN: []*sql.DSN{
      {
        Host: "127.0.0.1",
        Database: "echo",
        Username: "root",
        Password: "password",
      },
  	},
  }

  // Use real tracer in next sentence
  db, err := dsc.Open(sql.OpenWithProfiler(nil, "", nil))
  if err != nil {
    log.Fatal(err)
  }
  
  if err := db.Ping(); err != nil {
    log.Fatalf("Some physical database is unreachable: %s", err)
  }

  // Read queries are directed to slaves with Query and QueryRow.
  // Always use Query or QueryRow for SELECTS
  // Load distribution is round-robin only for now.
  var count int
  err = db.QueryRow("SELECT COUNT(*) FROM sometable").Scan(&count)
  if err != nil {
    log.Fatal(err)
  }

  // Write queries are directed to the master with Exec.
  // Always use Exec for INSERTS, UPDATES
  _, err = db.Exec("UPDATE sometable SET something = 1")
  if err != nil {
    log.Fatal(err)
  }

  // Prepared statements are aggregates. If any of the underlying
  // physical databases fails to prepare the statement, the call will
  // return an error. On success, if Exec is called, then the
  // master is used, if Query or QueryRow are called, then a slave
  // is used.
  stmt, err := db.Prepare("SELECT * FROM sometable WHERE something = ?")
  if err != nil {
    log.Fatal(err)
  }
  if _, err := stmt.Exec(); err != nil {
  	log.Fatal(err)
  }

  // Transactions always use the master
  tx, err := db.Begin()
  if err != nil {
    log.Fatal(err)
  }
  // Do something transactional ...
  if err = tx.Commit(); err != nil {
    log.Fatal(err)
  }
  
  // Register data in the repository
  r := &MyRepository{Repository: sql.NewRepository(db)}
  err = r.Register(context.Background(), 10, 20)
  if err != nil {
  	log.Fatal(err)
  }

  // If needed, one can access the master or a slave explicitly.
  master, slave := db.Master(), db.Slave()
  fmt.Println(master.Adapter().DatabaseName(master))
  fmt.Println(slave.Adapter().DatabaseName(slave))
}

Todo

  • Support other slave load balancing algorithms.
  • Support failovers.

Documentation

Index

Constants

View Source 
const (
	MaxDeadlockDuration = time.Second
)

Variables

View Source 
var (
	ErrNoRows         = data.ErrNoMatch
	ErrTxDone         = sql.ErrTxDone
	ErrUnknownDriver  = errors.New("unknown driver")
	ErrCaptureLock    = errors.New("timeout of latch")
	ErrReleaseLock    = errors.New("can not release lock")
	ErrReleaseInvalid = errors.New("unknown latch or invalid thread")
)
View Source 
var (
	DefaultTime = time.Date(2000, 1, 1, 0, 0, 0, 0, time.UTC)
)

Functions

func CloseOnError 

func CloseOnError(ctx context.Context, db DB, err error)

func ExternalBool 

func ExternalBool(value bool) interface{}

func ExternalFloat64 

func ExternalFloat64(value float64) interface{}

func ExternalInt 

func ExternalInt(value int) interface{}

func ExternalInt16 

func ExternalInt16(value int16) interface{}

func ExternalInt32 

func ExternalInt32(value int32) interface{}

func ExternalInt64 

func ExternalInt64(value int64) interface{}

func ExternalInt8 

func ExternalInt8(value int8) interface{}

func ExternalString 

func ExternalString(value string) interface{}

func ExternalTime 

func ExternalTime(value time.Time) interface{}

func ExternalUint 

func ExternalUint(value uint) interface{}

func ExternalUint16 

func ExternalUint16(value uint16) interface{}

func ExternalUint32 

func ExternalUint32(value uint32) interface{}

func ExternalUint64 

func ExternalUint64(value uint64) interface{}

func ExternalUint8 

func ExternalUint8(value uint8) interface{}

func Heartbeart 

func Heartbeart(
	ctx context.Context,
	db DB,
	interval time.Duration,
) error

func Register 

func Register(driver string, adapter Adapter)

func ToContext 

func ToContext(
	ctx context.Context,
	scope Scope,
) context.Context

Append scope into context

Types

type Activator 

type Activator func(dsc DSC) (DB, error)

func OpenExclusive 

func OpenExclusive(
	ctx context.Context,
	timeout int,
	activator Activator,
) Activator

Open exclusive access for required database If control is not null, than for latch opens with heartbeard.

func OpenWithProfiler 

func OpenWithProfiler(
	tracer Tracer,
	indent string,
	activator Activator,
) Activator

Open database with profiler

type Adapter 

type Adapter interface {
	// Get driver name
	Driver() string
	//Get database name
	DatabaseName(db DB) (name string, err error)
	// Make connection string for open database
	MakeConnectionString(dsn *DSN) string
	// Check error for deadlock criteria
	IsDeadlock(db DB, err error) bool
	// Acquire local lock
	LockLocal(ctx context.Context, tx Tx, latch string, timeout int) error
	// Release local lock
	UnlockLocal(ctx context.Context, tx Tx, latch string) error
	// Acquire local lock
	LockGlobal(ctx context.Context, tx Tx, latch string, timeout int) error
	// Release local lock
	UnlockGlobal(ctx context.Context, tx Tx, latch string) error
}

type Args 

type Args map[string]interface{}

Named arguments

func (*Args) Extract 

func (args *Args) Extract() []sql.NamedArg

Extract args to usage in Query/Exec

type Array 

type Array []interface{}

Array of abstract data

func (Array) Scan 

func (a Array) Scan(dest ...interface{}) error

type Arrays 

type Arrays []Array

type Auditor 

type Auditor interface {
	AuditDatabase(Metrics Metrics) error
}

type Composer 

type Composer interface {
	Add(delta int)
	Done()
	Abort() <-chan struct{}
}

Composer is interface for coordinate threads

type DB 

type DB interface {
	Scope
	Composer
	Close(ctx context.Context) error
	Driver() driver.Driver
	Ping() error
	Slave() DB
	Master() DB
	Prepare(query string) (Stmt, error)
	PrepareContext(ctx context.Context, query string) (Stmt, error)
	SetMaxIdleConns(n int)
	SetMaxOpenConns(n int)
	SetConnMaxLifetime(d time.Duration)
	IsCluster() bool
	GetMetrics() Metrics
	Audit(auditor interface{}) error
	Interface(detective func(interface{}) interface{}) (interface{}, bool)
	DSC() DSC
	// contains filtered or unexported methods
}

DB is a logical database with multiple underlying physical databases forming a single master multiple slaves topology. Reads and writes are automatically directed to the correct physical db.

func Open 

func Open(
	dsc DSC,
	activator Activator,
) (db DB, err error)

Open complex database with decorators Example: 

db, err := sql.OpenEx(dsc, OpenWithHeartbeat(10*time.Second, nil))

func WithProfiler 

func WithProfiler(
	db DB,
	tracer Tracer,
	indent string,
) DB

Wrap database profiler

type DSC 

type DSC struct {
	Driver string `json:"Driver"`
	DbId   DbId   `json:"-"`
	DSN    []*DSN `json:"dsn"`
}

DataSource nodes cluster (first node is master)

func (*DSC) Open 

func (dsc *DSC) Open(
	activator Activator,
) (DB, error)

func (DSC) OpenForTest 

func (dsc DSC) OpenForTest(
	ctx context.Context,
) DB

Exclusive open database for escape any concurrency.

func (*DSC) Primary 

func (dsc *DSC) Primary() DSN

func (*DSC) String 

func (dsc *DSC) String() (string, error)

type DSN 

type DSN struct {
	Host     string            `json:"host"`     // Host address
	Port     uint16            `json:"port"`     // Host port
	Database string            `json:"database"` // Database name
	Username string            `json:"username"` // User name
	Password string            `json:"password"` // User password
	Params   map[string]string `json:"params"`   // Other parameters
}

Single dataSource node

func (*DSN) AddParam 

func (dsn *DSN) AddParam(key string, value string)

func (*DSN) Open 

func (dsn *DSN) Open(
	driver string,
	activator Activator,
) (DB, error)

type DbId 

type DbId int
const PrimaryDatabase DbId = 1

type Fetcher 

type Fetcher interface {
	Scan(dest ...interface{}) error
}

Abstract row fetcher

type HalfMetrics 

type HalfMetrics struct {
	Count int32 `json:"count"` // Count of executed queries
	Time  int64 `json:"time"`  // Elapsed time (microseconds)
}

type IsolationLevel 

type IsolationLevel int

IsolationLevel is the transaction isolation level used in TxOptions. 

const (
	LevelDefault IsolationLevel = iota
	LevelReadUncommitted
	LevelReadCommitted
	LevelWriteCommitted
	LevelRepeatableRead
	LevelSnapshot
	LevelSerializable
	LevelLinearizable
)

Various isolation levels that drivers may support in BeginTx. If a driver does not support a given isolation level an error may be returned.

See https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels.

type Metrics 

type Metrics struct {
	Query    HalfMetrics `json:"query"`
	Exec     HalfMetrics `json:"exec"`
	Transact HalfMetrics `json:"transact"`
}

Metrics of database

func (*Metrics) Audit 

func (metrics *Metrics) Audit(auditor interface{}) error

func (*Metrics) GetMetrics 

func (metrics *Metrics) GetMetrics() Metrics

type NullBool 

type NullBool struct {
	Bool  bool
	Valid bool // Valid is true if Bool is not NULL
}

NullBool represents a bool that may be null. NullBool implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullBool) External 

func (n NullBool) External() interface{}

func (NullBool) Internal 

func (n NullBool) Internal() driver.Value

func (*NullBool) MarshalJSON 

func (n *NullBool) MarshalJSON() ([]byte, error)

func (*NullBool) Scan 

func (n *NullBool) Scan(value interface{}) error

func (*NullBool) UnmarshalJSON 

func (n *NullBool) UnmarshalJSON(data []byte) error

func (NullBool) Value 

func (n NullBool) Value() (driver.Value, error)

type NullFloat64 

type NullFloat64 struct {
	Float64 float64
	Valid   bool // Valid is true if Float64 is not NULL
}

NullFloat64 represents a float64 that may be null. NullFloat64 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullFloat64) External 

func (n NullFloat64) External() interface{}

func (NullFloat64) Internal 

func (n NullFloat64) Internal() driver.Value

func (*NullFloat64) MarshalJSON 

func (n *NullFloat64) MarshalJSON() ([]byte, error)

func (*NullFloat64) Scan 

func (n *NullFloat64) Scan(value interface{}) error

func (*NullFloat64) UnmarshalJSON 

func (n *NullFloat64) UnmarshalJSON(data []byte) error

func (NullFloat64) Value 

func (n NullFloat64) Value() (driver.Value, error)

type NullInt 

type NullInt struct {
	Int   int
	Valid bool // Valid is true if Int is not NULL
}

NullInt represents an int that may be null. NullInt implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullInt) External 

func (n NullInt) External() interface{}

func (NullInt) Internal 

func (n NullInt) Internal() driver.Value

func (*NullInt) MarshalJSON 

func (n *NullInt) MarshalJSON() ([]byte, error)

func (*NullInt) Scan 

func (n *NullInt) Scan(value interface{}) error

func (*NullInt) UnmarshalJSON 

func (n *NullInt) UnmarshalJSON(data []byte) error

func (NullInt) Value 

func (n NullInt) Value() (driver.Value, error)

type NullInt16 

type NullInt16 struct {
	Int16 int16
	Valid bool // Valid is true if Int16 is not NULL
}

NullInt16 represents an int16 that may be null. NullInt16 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullInt16) External 

func (n NullInt16) External() interface{}

func (NullInt16) Internal 

func (n NullInt16) Internal() driver.Value

func (*NullInt16) MarshalJSON 

func (n *NullInt16) MarshalJSON() ([]byte, error)

func (*NullInt16) Scan 

func (n *NullInt16) Scan(value interface{}) error

func (*NullInt16) UnmarshalJSON 

func (n *NullInt16) UnmarshalJSON(data []byte) error

func (NullInt16) Value 

func (n NullInt16) Value() (driver.Value, error)

type NullInt32 

type NullInt32 struct {
	Int32 int32
	Valid bool // Valid is true if Int32 is not NULL
}

NullInt32 represents an int32 that may be null. NullInt32 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullInt32) External 

func (n NullInt32) External() interface{}

func (NullInt32) Internal 

func (n NullInt32) Internal() driver.Value

func (*NullInt32) MarshalJSON 

func (n *NullInt32) MarshalJSON() ([]byte, error)

func (*NullInt32) Scan 

func (n *NullInt32) Scan(value interface{}) error

func (*NullInt32) UnmarshalJSON 

func (n *NullInt32) UnmarshalJSON(data []byte) error

func (NullInt32) Value 

func (n NullInt32) Value() (driver.Value, error)

type NullInt64 

type NullInt64 struct {
	Int64 int64
	Valid bool // Valid is true if Int64 is not NULL
}

NullInt64 represents an int64 that may be null. NullInt64 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullInt64) External 

func (n NullInt64) External() interface{}

func (NullInt64) Internal 

func (n NullInt64) Internal() driver.Value

func (*NullInt64) MarshalJSON 

func (n *NullInt64) MarshalJSON() ([]byte, error)

func (*NullInt64) Scan 

func (n *NullInt64) Scan(value interface{}) error

func (*NullInt64) UnmarshalJSON 

func (n *NullInt64) UnmarshalJSON(data []byte) error

func (NullInt64) Value 

func (n NullInt64) Value() (driver.Value, error)

type NullInt8 

type NullInt8 struct {
	Int8  int8
	Valid bool // Valid is true if Int8 is not NULL
}

NullInt8 represents an int8 that may be null. NullInt8 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullInt8) External 

func (n NullInt8) External() interface{}

func (NullInt8) Internal 

func (n NullInt8) Internal() driver.Value

func (*NullInt8) MarshalJSON 

func (n *NullInt8) MarshalJSON() ([]byte, error)

func (*NullInt8) Scan 

func (n *NullInt8) Scan(value interface{}) error

func (*NullInt8) UnmarshalJSON 

func (n *NullInt8) UnmarshalJSON(data []byte) error

func (NullInt8) Value 

func (n NullInt8) Value() (driver.Value, error)

type NullString 

type NullString struct {
	String string
	Valid  bool // Valid is true if String is not NULL
}

NullString represents a string that may be null. NullString implements the Scanner interface so it can be used as a scan destination: 

var s NullString
err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)
...
if s.Valid {
   // use s.String
} else {
   // NULL value
}

func (NullString) External 

func (n NullString) External() interface{}

func (NullString) Internal 

func (n NullString) Internal() driver.Value

func (*NullString) MarshalJSON 

func (n *NullString) MarshalJSON() ([]byte, error)

func (*NullString) Scan 

func (n *NullString) Scan(value interface{}) error

func (*NullString) UnmarshalJSON 

func (n *NullString) UnmarshalJSON(data []byte) error

func (NullString) Value 

func (n NullString) Value() (driver.Value, error)

type NullTime 

type NullTime struct {
	Time  time.Time
	Valid bool // Valid is true if Time is not NULL
}

NullTime represents a time that may be null. NullTime implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullTime) External 

func (n NullTime) External() interface{}

func (NullTime) Internal 

func (n NullTime) Internal() driver.Value

func (*NullTime) MarshalJSON 

func (n *NullTime) MarshalJSON() ([]byte, error)

func (*NullTime) Scan 

func (n *NullTime) Scan(value interface{}) error

func (*NullTime) UnmarshalJSON 

func (n *NullTime) UnmarshalJSON(data []byte) error

func (NullTime) Value 

func (n NullTime) Value() (driver.Value, error)

type NullUint 

type NullUint struct {
	Uint  uint
	Valid bool // Valid is true if UInt is not NULL
}

NullUInt represents an uInt that may be null. NullUInt implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullUint) External 

func (n NullUint) External() interface{}

func (NullUint) Internal 

func (n NullUint) Internal() driver.Value

func (*NullUint) MarshalJSON 

func (n *NullUint) MarshalJSON() ([]byte, error)

func (*NullUint) Scan 

func (n *NullUint) Scan(value interface{}) error

func (*NullUint) UnmarshalJSON 

func (n *NullUint) UnmarshalJSON(data []byte) error

func (NullUint) Value 

func (n NullUint) Value() (driver.Value, error)

type NullUint16 

type NullUint16 struct {
	Uint16 uint16
	Valid  bool // Valid is true if UInt16 is not NULL
}

NullUInt16 represents an uInt16 that may be null. NullUInt16 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullUint16) External 

func (n NullUint16) External() interface{}

func (NullUint16) Internal 

func (n NullUint16) Internal() driver.Value

func (*NullUint16) MarshalJSON 

func (n *NullUint16) MarshalJSON() ([]byte, error)

func (*NullUint16) Scan 

func (n *NullUint16) Scan(value interface{}) error

func (*NullUint16) UnmarshalJSON 

func (n *NullUint16) UnmarshalJSON(data []byte) error

func (NullUint16) Value 

func (n NullUint16) Value() (driver.Value, error)

type NullUint32 

type NullUint32 struct {
	Uint32 uint32
	Valid  bool // Valid is true if UInt32 is not NULL
}

NullUInt32 represents an uInt32 that may be null. NullUInt32 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullUint32) External 

func (n NullUint32) External() interface{}

func (NullUint32) Internal 

func (n NullUint32) Internal() driver.Value

func (*NullUint32) MarshalJSON 

func (n *NullUint32) MarshalJSON() ([]byte, error)

func (*NullUint32) Scan 

func (n *NullUint32) Scan(value interface{}) error

func (*NullUint32) UnmarshalJSON 

func (n *NullUint32) UnmarshalJSON(data []byte) error

func (NullUint32) Value 

func (n NullUint32) Value() (driver.Value, error)

type NullUint64 

type NullUint64 struct {
	Uint64 uint64
	Valid  bool // Valid is true if UInt64 is not NULL
}

NullUInt64 represents an uInt64 that may be null. NullUInt64 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullUint64) External 

func (n NullUint64) External() interface{}

func (NullUint64) Internal 

func (n NullUint64) Internal() driver.Value

func (*NullUint64) MarshalJSON 

func (n *NullUint64) MarshalJSON() ([]byte, error)

func (*NullUint64) Scan 

func (n *NullUint64) Scan(value interface{}) error

func (*NullUint64) UnmarshalJSON 

func (n *NullUint64) UnmarshalJSON(data []byte) error

func (NullUint64) Value 

func (n NullUint64) Value() (driver.Value, error)

type NullUint8 

type NullUint8 struct {
	Uint8 uint8
	Valid bool // Valid is true if UInt8 is not NULL
}

NullUInt8 represents an uInt8 that may be null. NullUInt8 implements the Scanner interface so it can be used as a scan destination, similar to NullString.

func (NullUint8) External 

func (n NullUint8) External() interface{}

func (NullUint8) Internal 

func (n NullUint8) Internal() driver.Value

func (*NullUint8) MarshalJSON 

func (n *NullUint8) MarshalJSON() ([]byte, error)

func (*NullUint8) Scan 

func (n *NullUint8) Scan(value interface{}) error

func (*NullUint8) UnmarshalJSON 

func (n *NullUint8) UnmarshalJSON(data []byte) error

func (NullUint8) Value 

func (n NullUint8) Value() (driver.Value, error)

type Repository 

type Repository interface {
	// Find actual scope
	Scope(ctx context.Context) Scope
	// Get database
	Database() DB
	// Execute transaction
	Transaction(
		ctx context.Context,
		action func(ctx context.Context) error,
	) error
}

func NewRepository 

func NewRepository(
	db DB,
) Repository

type Result 

type Result interface {
	LastInsertId() (int64, error)
	RowsAffected() (int64, error)
}

Executor result

type Row 

type Row interface {
	Scan(dest ...interface{}) error
}

Abstract row

type Rows 

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

Record set

type Scanner 

type Scanner = sql.Scanner

Scanner is an interface used by Scan.

type Scope 

type Scope interface {
	Begin() (Tx, error)
	BeginTx(ctx context.Context, opts *TxOptions) (Tx, error)
	Exec(query string, args ...interface{}) (Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
	Query(query string, args ...interface{}) (Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (Rows, error)
	QueryRow(query string, args ...interface{}) Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) Row
	DbId() DbId
	Adapter() Adapter
}

Scope is abstract processor

func FromContext 

func FromContext(ctx context.Context, key DbId) Scope

Extract database context from context

type Stmt 

type Stmt interface {
	Close() error
	Exec(args ...interface{}) (Result, error)
	ExecContext(ctx context.Context, args ...interface{}) (Result, error)
	Query(args ...interface{}) (Rows, error)
	QueryContext(ctx context.Context, args ...interface{}) (Rows, error)
	QueryRow(args ...interface{}) Row
	QueryRowContext(ctx context.Context, args ...interface{}) Row
}

Stmt is an aggregate prepared statement. It holds a prepared statement for each underlying physical db.

type Tracer 

type Tracer interface {
	// Profiler secondary information (skip in production)
	Trace(msg interface{})
}

type Tx 

type Tx interface {
	Scope
	Level() int16
	Commit() error
	Rollback() error
}

Transaction

type TxOptions 

type TxOptions = sql.TxOptions

TxOptions holds the transaction options to be used in DB.BeginTx.

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.