query

package
v0.1.13 Latest Latest
Warning

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

 Go to latest Published: Jun 9, 2021 License: MIT Imports: 14 Imported by: 0

Documentation

Overview

Package query provides a simple programmatically sql query builder. The idea was to create a unique query builder which can be used with any database driver in go.

Features: Unique Placeholder for all database drivers, Batching function for large Inserts, Whitelist, Quote Identifiers, SQL queries and durations log debugging TODO: ForeignKeys should already hold the information of the relation type 1:1 1:n,... TODO: Create a slow query log (WARN) lvl with a config TODO mysql Query must return a new instance to avoid race problems (tx).

Index

Constants

View Source 
const (
	EQ      = "= ?"
	NEQ     = "!= ?"
	NULL    = "IS NULL"
	NOTNULL = "IS NOT NULL"
	GT      = "> ?"
	GTE     = ">= ?"
	LT      = "< ?"
	LTE     = "<= ?"
	LIKE    = "LIKE ?"
	NOTLIKE = "NOT LIKE ?"
	IN      = "IN (?)"
	NOTIN   = "NOT IN (?)"
)

all allowed sql operators.

Variables

View Source 
var (
	ErrValueMissing = "query: no %s value is set (%s)"
	ErrColumn       = "query: column (%s) does not exist in (%s)"
	ErrLastID       = errors.New("query: last id must be a ptr")
)

Error messages.

View Source 
var (
	ErrNoTx     = errors.New("query: no tx exists")
	ErrTxExists = errors.New("query: tx already exists")
)

Error messages.

View Source 
var (
	ErrDbNotSet = errors.New("query: DB is not set")
)

Error messages.

View Source 
var (
	ErrSanitize = "query: can not sanitize value %v of type %s"
)

Error messages.

Functions

func DbExpr 

func DbExpr(s string) string

DbExpr expressions will not get quoted.

func IsOperatorAllowed 

func IsOperatorAllowed(s string) bool

IsOperatorAllowed will return false if the operator is not implemented.

func Register 

func Register(name string, p providerFn) error

Register the query provider.

func SanitizeInterfaceValue 

func SanitizeInterfaceValue(value interface{}) (interface{}, error)

SanitizeInterfaceValue will convert any int, uint or NullInt to int64 and NullString to string. Error will return if the type is different or not implemented.

func SanitizeToString 

func SanitizeToString(i interface{}) (string, error)

SanitizeToString will convert any type to a string. Error will return if the type is not implemented in SanitizeInterfaceValue.

Types

type Base 

type Base struct {
	Config   Config
	Logger   logger.Manager
	Provider Provider

	TransactionBase
	// contains filtered or unexported fields
}

Base struct includes the configuration, logger and transaction logic.

func (*Base) All 

func (b *Base) All(stmt string, args []interface{}) (*sql.Rows, error)

All will return the sql.Rows. If a logger is defined, the query will be logged on `DEBUG` lvl with a timer. If a transaction is set, it will run in the transaction.

func (*Base) DB 

func (b *Base) DB() *sql.DB

DB returns the *sql.DB.

func (*Base) Exec 

func (b *Base) Exec(stmt []string, args [][]interface{}) ([]sql.Result, error)

Exec will execute the statement. Because of the Insert.Batch, multiple statements and arguments can be added and therefore an slice of sql.Result returns. If a transaction is set, it will run in the transaction. If its a batch exec and no transaction is set, it will automatically create one and commits it.

func (*Base) First 

func (b *Base) First(stmt string, args []interface{}) (*sql.Row, error)

First will return a sql.Row. If a logger is defined, the query will be logged on `DEBUG` lvl with a timer. If a transaction is set, it will run in the transaction.

func (*Base) Open 

func (b *Base) Open() error

Open will set some basic sql Settings and check the connection. all defined config.Prequeries will run here.

func (*Base) QuoteIdentifier 

func (b *Base) QuoteIdentifier(columns ...string) string

QuoteIdentifier quotes every string with the providers quote-identifier-character. If query.DbExpr was used, the string will not be quoted. "go.users AS u" will be converted to `go`.`users` AS `u`

func (*Base) SetDB 

func (b *Base) SetDB(db *sql.DB)

SetDB sets the *sql.DB.

func (*Base) SetLogger 

func (b *Base) SetLogger(logger logger.Manager)

SetLogger will set the logger for the query.

func (*Base) Tx 

func (b *Base) Tx() (Tx, error)

Tx will create a sql.Tx. Error will return if a tx was already set or the provider returns an error.

type Builder 

type Builder interface {
	SetLogger(logger.Manager)
	Query(...Tx) Query
	Config() Config
	QuoteIdentifier(string) string
}

Builder interface.

func New 

func New(name string, config interface{}) (Builder, error)

New creates a new builder instance with the given query provider and configuration. Error will return if the query provider was not registered, query provider factory or the query provider Open function will return one.

type Column 

type Column struct {
	Table         string
	Name          string
	Position      int
	NullAble      bool
	PrimaryKey    bool
	Unique        bool
	Type          Type
	DefaultValue  NullString
	Length        NullInt
	Autoincrement bool
}

Column represents a database table column.

type Config 

type Config struct {
	Provider string // used for gofer.Server

	Username string
	Password string
	Host     string
	Port     int
	Database string

	MaxIdleConnections int
	MaxOpenConnections int
	MaxConnLifetime    time.Duration
	Timeout            string

	PreQuery []string `mapstructure:",omitempty"`
}

Config sql struct.

type Delete 

type Delete interface {
	Condition(c condition.Condition) Delete
	Where(string, ...interface{}) Delete

	String() (string, []interface{}, error)
	Exec() (sql.Result, error)
}

Delete interface.

type DeleteBase 

type DeleteBase struct {
	Provider Provider

	DTable     string
	DCondition cond.Condition
}

DeleteBase can be embedded and changed for different providers. All functions and variables are therefore exported.

func (*DeleteBase) Condition 

func (d *DeleteBase) Condition(c cond.Condition) Delete

Condition adds your own condition to the stmt. Only WHERE conditions will be used.

func (*DeleteBase) Exec 

func (d *DeleteBase) Exec() (sql.Result, error)

Exec the statement.

func (*DeleteBase) Render 

func (d *DeleteBase) Render() (stmt string, args []interface{}, err error)

Render the sql query.

func (*DeleteBase) String 

func (d *DeleteBase) String() (stmt string, args []interface{}, err error)

String returns the rendered statement and arguments.

func (*DeleteBase) Where 

func (d *DeleteBase) Where(condition string, args ...interface{}) Delete

Where - please see the Condition.Where documentation.

type ForeignKey 

type ForeignKey struct {
	Name      string
	Primary   Relation
	Secondary Relation
}

ForeignKey represents a table relation.

type Information 

type Information interface {
	Describe(columns ...string) ([]Column, error)
	ForeignKey() ([]ForeignKey, error)
}

Information interface

type Insert 

type Insert interface {
	Batch(int) Insert
	Columns(...string) Insert
	Values([]map[string]interface{}) Insert
	LastInsertedID(...interface{}) Insert

	String() ([]string, [][]interface{}, error)
	Exec() ([]sql.Result, error)
}

Insert interface.

type InsertBase 

type InsertBase struct {
	Provider Provider

	ITable     string
	IValues    []map[string]interface{}
	IColumns   []string
	IBatchSize int
	IArguments [][]interface{}
	ILastID    interface{}
}

InsertBase can be embedded and changed for different providers. All functions and variables are therefore exported.

func (*InsertBase) Batch 

func (i *InsertBase) Batch(size int) Insert

Batch sets the batching size. Default batching size is 50.

func (*InsertBase) Columns 

func (i *InsertBase) Columns(c ...string) Insert

Columns define a fixed column order for the insert. If the columns are not set manually, all keys of the Values will be added. Only Values will be inserted which are defined here. This means, you can use Columns as a whitelist.

func (*InsertBase) Exec 

func (i *InsertBase) Exec() ([]sql.Result, error)

Exec the statement.

func (*InsertBase) LastInsertedID 

func (i *InsertBase) LastInsertedID(id ...interface{}) Insert

LastInsertedID gets the last id over different drivers. The first argument must be a ptr to the value field. The second argument should be the name of the ID column - if needed.

func (*InsertBase) Render 

func (i *InsertBase) Render() ([]string, [][]interface{}, error)

Render the sql query.

func (*InsertBase) String 

func (i *InsertBase) String() ([]string, [][]interface{}, error)

String returns the rendered statement and arguments.

func (*InsertBase) Values 

func (i *InsertBase) Values(values []map[string]interface{}) Insert

Values sets the insert data.

type NullBool 

type NullBool null.Bool

NullBool wraps gopkg.in/guregu/null.Bool

func NewNullBool 

func NewNullBool(b bool, valid bool) NullBool

NewNullBool creates a new NullBool.

func (NullBool) MarshalJSON 

func (b NullBool) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler. It will encode null if this Bool is null.

func (NullBool) MarshalText 

func (b NullBool) MarshalText() ([]byte, error)

MarshalText implements encoding.TextMarshaler. It will encode a blank string if this Bool is null.

func (*NullBool) UnmarshalJSON 

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

UnmarshalJSON implements json.Unmarshaler. It supports number and null input. 0 will not be considered a null Bool.

func (*NullBool) UnmarshalText 

func (b *NullBool) UnmarshalText(text []byte) error

UnmarshalText implements encoding.TextUnmarshaler. It will unmarshal to a null Bool if the input is blank. It will return an error if the input is not an integer, blank, or "null".

type NullFloat 

type NullFloat null.Float

NullFloat wraps gopkg.in/guregu/null.Float

func NewNullFloat 

func NewNullFloat(f float64, v bool) NullFloat

NewNullFloat creates a new NullFloat.

func (NullFloat) MarshalJSON 

func (f NullFloat) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler. It will encode null if this Float is null.

func (NullFloat) MarshalText 

func (f NullFloat) MarshalText() ([]byte, error)

MarshalText implements encoding.TextMarshaler. It will encode a blank string if this Float is null.

func (*NullFloat) UnmarshalJSON 

func (f *NullFloat) UnmarshalJSON(data []byte) error

UnmarshalJSON implements json.Unmarshaler. It supports number and null input. 0 will not be considered a null Float.

func (*NullFloat) UnmarshalText 

func (f *NullFloat) UnmarshalText(text []byte) error

UnmarshalText implements encoding.TextUnmarshaler. It will unmarshal to a null Float if the input is blank. It will return an error if the input is not an integer, blank, or "null".

type NullInt 

type NullInt null.Int

NullInt wraps gopkg.in/guregu/null.Int

func NewNullInt 

func NewNullInt(i int64, valid bool) NullInt

NewNullInt creates a new NullInt.

func (NullInt) MarshalJSON 

func (i NullInt) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler. It will encode null if this Int is null.

func (NullInt) MarshalText 

func (i NullInt) MarshalText() ([]byte, error)

MarshalText implements encoding.TextMarshaler. It will encode a blank string if this Int is null.

func (*NullInt) UnmarshalJSON 

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

UnmarshalJSON implements json.Unmarshaler. It supports number, string, and null input. 0 will not be considered a null Int.

func (*NullInt) UnmarshalText 

func (i *NullInt) UnmarshalText(text []byte) error

UnmarshalText implements encoding.TextUnmarshaler. It will unmarshal to a null Int if the input is blank. It will return an error if the input is not an integer, blank, or "null".

type NullString 

type NullString null.String

NullString wraps gopkg.in/guregu/null.String

func NewNullString 

func NewNullString(s string, valid bool) NullString

NewNullString creates a new NullString.

func (NullString) MarshalJSON 

func (s NullString) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler. It will encode null if this String is null.

func (NullString) MarshalText 

func (s NullString) MarshalText() ([]byte, error)

MarshalText implements encoding.TextMarshaler. It will encode a blank string when this String is null.

func (*NullString) UnmarshalJSON 

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

UnmarshalJSON implements json.Unmarshaler. It supports string and null input. Blank string input does not produce a null String.

func (*NullString) UnmarshalText 

func (s *NullString) UnmarshalText(text []byte) error

UnmarshalText implements encoding.TextUnmarshaler. It will unmarshal to a null String if the input is a blank string.

type NullTime 

type NullTime null.Time

NullTime wraps gopkg.in/guregu/null.Time

func NewNullTime 

func NewNullTime(t time.Time, valid bool) NullTime

NewNullTime creates a new NullTime.

func (NullTime) MarshalJSON 

func (t NullTime) MarshalJSON() ([]byte, error)

MarshalJSON implements json.Marshaler. It will encode null if this time is null.

func (NullTime) MarshalText 

func (t NullTime) MarshalText() ([]byte, error)

MarshalText implements encoding.TextMarshaler. It returns an empty string if invalid, otherwise time.Time's MarshalText.

func (NullTime) String 

func (t NullTime) String() string

func (*NullTime) UnmarshalJSON 

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

UnmarshalJSON implements json.Unmarshaler. It supports string and null input.

func (*NullTime) UnmarshalText 

func (t *NullTime) UnmarshalText(text []byte) error

UnmarshalText implements encoding.TextUnmarshaler. It has backwards compatibility with v3 in that the string "null" is considered equivalent to an empty string and unmarshaling will succeed. This may be removed in a future version.

type Provider 

type Provider interface {
	Open() error
	Config() Config
	Placeholder() condition.Placeholder
	QuoteIdentifier(...string) string
	QuoteIdentifierChar() string
	SetLogger(logger.Manager)
	Query
	Tx
	Query() Query
	Exec([]string, [][]interface{}) ([]sql.Result, error)
	First(string, []interface{}) (*sql.Row, error)
	All(string, []interface{}) (*sql.Rows, error)
}

Provider interface.

type Query 

type Query interface {
	Tx() (Tx, error)
	HasTx() bool
	Commit() error
	Rollback() error

	DB() *sql.DB

	Select(string) Select
	Insert(string) Insert
	Update(string) Update
	Delete(string) Delete
	Information(string) Information
}

Query interface.

type Relation 

type Relation struct {
	Table  string
	Column string
}

Relation defines the table and column of a relation.

type Select 

type Select interface {
	Columns(...string) Select
	First() (*sql.Row, error)
	All() (*sql.Rows, error)
	String() (string, []interface{}, error)

	Condition(c condition.Condition) Select
	Join(joinType int, table string, condition string, args ...interface{}) Select
	Where(condition string, args ...interface{}) Select
	Group(group ...string) Select
	Having(condition string, args ...interface{}) Select
	Order(order ...string) Select
	Limit(limit int) Select
	Offset(offset int) Select
}

Select interface.

type SelectBase 

type SelectBase struct {
	Provider Provider

	STable     string
	SColumns   []string
	SCondition condition.Condition
}

SelectBase can be embedded and changed for different providers. All functions and variables are therefore exported.

func (*SelectBase) All 

func (s *SelectBase) All() (*sql.Rows, error)

All will return sql.Rows.

func (*SelectBase) Columns 

func (s *SelectBase) Columns(columns ...string) Select

Columns define a fixed column order for the insert. If the columns are not set manually, * will be used. Only Values will be inserted which are defined here. This means, you can use Columns as a whitelist.

func (*SelectBase) Condition 

func (s *SelectBase) Condition(c condition.Condition) Select

Condition adds your own condition to the stmt.

func (*SelectBase) First 

func (s *SelectBase) First() (*sql.Row, error)

First will return a sql.Row. condition.LIMIT and condition.OFFSET will be removed - if set.

func (*SelectBase) Group 

func (s *SelectBase) Group(group ...string) Select

Group - please see the condition.Group documentation.

func (*SelectBase) Having 

func (s *SelectBase) Having(condition string, args ...interface{}) Select

Having - please see the condition.Having documentation.

func (*SelectBase) Join 

func (s *SelectBase) Join(joinType int, table string, condition string, args ...interface{}) Select

Join - please see the condition.Join documentation.

func (*SelectBase) Limit 

func (s *SelectBase) Limit(limit int) Select

Limit - please see the condition.Limit documentation.

func (*SelectBase) Offset 

func (s *SelectBase) Offset(offset int) Select

Offset - please see the condition.Offset documentation.

func (*SelectBase) Order 

func (s *SelectBase) Order(order ...string) Select

Order - please see the condition.Order documentation.

func (*SelectBase) Render 

func (s *SelectBase) Render() (string, []interface{}, error)

Render the sql query.

func (*SelectBase) String 

func (s *SelectBase) String() (string, []interface{}, error)

String returns the rendered statement and arguments.

func (*SelectBase) Where 

func (s *SelectBase) Where(condition string, args ...interface{}) Select

Where - please see the condition.Where documentation.

type TransactionBase 

type TransactionBase struct {
	Tx *sql.Tx
}

TransactionBase can be embedded and changed for different providers. All functions and variables are therefore exported.

func (*TransactionBase) Commit 

func (t *TransactionBase) Commit() error

Commit a transaction. Error returns if there was no sql.Tx set ot it returns one.

func (*TransactionBase) HasTx 

func (t *TransactionBase) HasTx() bool

HasTx returns true if a sql.Tx exists.

func (*TransactionBase) Rollback 

func (t *TransactionBase) Rollback() error

Rollback a transaction. Error returns if there was no sql.Tx set ot it returns one.

type Tx 

type Tx interface {
	HasTx() bool
	Commit() error
	Rollback() error

	DB() *sql.DB

	Select(string) Select
	Insert(string) Insert
	Update(string) Update
	Delete(string) Delete
	Information(string) Information
}

Tx interface.

type Type 

type Type interface {
	Kind() string
	Raw() string
}

Type interface

type Update 

type Update interface {
	Set(map[string]interface{}) Update
	Columns(...string) Update
	Condition(condition.Condition) Update
	Where(string, ...interface{}) Update

	String() (string, []interface{}, error)
	Exec() (sql.Result, error)
}

Update interface.

type UpdateBase 

type UpdateBase struct {
	Provider Provider

	UTable     string
	UColumns   []string
	UValues    map[string]interface{}
	UCondition condition.Condition
	UArguments []interface{}
}

UpdateBase can be embedded and changed for different providers. All functions and variables are therefore exported.

func (*UpdateBase) Columns 

func (u *UpdateBase) Columns(cols ...string) Update

Columns define a fixed column order for the insert. If the columns are not set manually, all keys of the Values will be added. Only Values will be inserted which are defined here. This means, you can use Columns as a whitelist.

func (*UpdateBase) Condition 

func (u *UpdateBase) Condition(c condition.Condition) Update

Condition adds your own condition to the stmt. Only WHERE conditions will be used.

func (*UpdateBase) Exec 

func (u *UpdateBase) Exec() (sql.Result, error)

Exec the statement.

func (*UpdateBase) Render 

func (u *UpdateBase) Render() (stmt string, args []interface{}, err error)

Render the sql query.

func (*UpdateBase) Set 

func (u *UpdateBase) Set(values map[string]interface{}) Update

Set the values.

func (*UpdateBase) String 

func (u *UpdateBase) String() (stmt string, args []interface{}, err error)

String returns the rendered statement and arguments.

func (*UpdateBase) Where 

func (u *UpdateBase) Where(condition string, args ...interface{}) Update

Where - please see the condition.Where documentation.

Source Files

View all Source files

Directories

Path Synopsis
Package condition provides a sql condition builder.
 Package condition provides a sql condition builder.

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.