dml

package
v0.0.0-...-202847b Latest Latest
Warning

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

 Go to latest Published: Jan 1, 2023 License: Apache-2.0 Imports: 26 Imported by: 27

Documentation

Overview

Package dml handles the SQL DML for super fast performance, type safety and convenience.

Aim: Allow a developer to easily modify a SQL query without type assertion of parts of the query. No reflection magic has been used so we must achieve type safety with code generation.

This package works only with MySQL and its derivates like MariaDB or Percona.

Abbreviations

DML (https://en.wikipedia.org/wiki/Data_manipulation_language) Select, Insert, Update and Delete.

DDL (https://en.wikipedia.org/wiki/Data_definition_language) Create, Drop, Alter, and Rename.

DCL (https://en.wikipedia.org/wiki/Data_control_language) Grant and Revoke.

CRUD (https://en.wikipedia.org/wiki/Create,_read,_update_and_delete) Create, Read, Update and Delete.

https://mariadb.com/kb/en/mariadb/documentation/

Practical Guide to SQL Transaction Isolation: https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html

NetSPI SQL Injection Wiki: https://sqlwiki.netspi.com/

TODO(CyS) think about named locks: https://news.ycombinator.com/item?id=14907679 https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock Database locks should not be used by the average developer. Understand optimistic concurrency and use serializable isolation.

TODO(CyS) Soft deletion with system versioned tables (MariaDB only) see here 

 where they discuss various concepts about soft deletion:
	https://news.ycombinator.com/item?id=34202606 and here https://news.ycombinator.com/item?id=32156009

TODO(CyS) refactor some parts of the code once Go implements generics ;-)

TODO(CyS) implement usage of window functions:

Index

Examples

Constants

View Source 
const (
	ColumnMapEntityReadAll     columnMapMode = 'a'
	ColumnMapEntityReadSet     columnMapMode = 'r'
	ColumnMapCollectionReadSet columnMapMode = 'R'
	ColumnMapScan              columnMapMode = 'S' // can be used for both
)

Those four constants represents the modes for ColumnMap.Mode. An upper case letter defines a collection and a lower case letter an entity.

View Source 
const EnvDSN string = "CS_DSN"

EnvDSN is the name of the environment variable

View Source 
const MaxIdentifierLength = 64

MaxIdentifierLength see http://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Variables

View Source 
var Now = nowSentinel{}

Now is a value that serializes to the current time. Should only be used to testing purposes. This exported variable can be removed at any time.

View Source 
var Quoter = MysqlQuoter{
	// contains filtered or unexported fields
}

Quoter at the quoter to use for quoting text; use Mysql quoting by default.

Functions

func ExecValidateOneAffectedRow 

func ExecValidateOneAffectedRow(res sql.Result, err error) error

ExecValidateOneAffectedRow checks the sql.Result.RowsAffected if it returns one. If not returns an error of type NotValid. This function is useful for ExecContext function.

func Interpolate 

func Interpolate(sql string) *ip

Interpolate takes a SQL byte slice with placeholders and a list of arguments to replace them with. It returns a blank string or an error if the number of placeholders does not match the number of arguments. Implements the ExpandPlaceHolders function.

Example 
package main

import (
	"fmt"

	"github.com/corestoreio/pkg/sql/dml"
)

func main() {
	sqlStr := dml.Interpolate("SELECT * FROM x WHERE a IN ? AND b IN ? AND c NOT IN ? AND d BETWEEN ? AND ?").
		Ints(1).
		Ints(1, 2, 3).
		Int64s(5, 6, 7).
		Str("wat").
		Str("ok").
		// `MustString` panics on error, or use `String` which prints the error into
		// the returned string and hence generates invalid SQL. Alternatively use
		// `ToSQL`.
		MustString()

	fmt.Printf("%s\n", sqlStr)
}

Output:

SELECT * FROM x WHERE a IN (1) AND b IN (1,2,3) AND c NOT IN (5,6,7) AND d BETWEEN 'wat' AND 'ok'

func IsValidIdentifier 

func IsValidIdentifier(objectName string) (err error)

IsValidIdentifier checks the permissible syntax for identifiers. Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers. ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore) Max length 63 characters. It is recommended that you do not use names that begin with Me or MeN, where M and N are integers. For example, avoid using 1e as an identifier, because an expression such as 1e+3 is ambiguous. Depending on context, it might be interpreted as the expression 1e + 3 or as the number 1e+3.

Returns 0 if the identifier is valid.

http://dev.mysql.com/doc/refman/5.7/en/identifiers.html

func MakeIdentifier 

func MakeIdentifier(name string) id

MakeIdentifier creates a new quoted name with an optional alias `a`, which can be empty.

func MySQLMessageFromError 

func MySQLMessageFromError(err error) string

MySQLMessageFromError returns the textual message of the MySQL error. The error has been generated by the driver go-sql-driver/mysql. Returns an empty string if the type of error is not *mysql.MySQLError.

func MySQLNumberFromError 

func MySQLNumberFromError(err error) uint16

MySQLNumberFromError returns the error code number from an error. The error has been generated by the driver go-sql-driver/mysql. A list of error codes can be accessed here: https://mariadb.com/kb/en/mariadb-error-codes/ Returns 0 if the type of error is not *mysql.MySQLError.

func WithContextQueryOptions 

func WithContextQueryOptions(ctx context.Context, qo QueryOptions) context.Context

WithContextQueryOptions adds options for executing queries, mostly in generated code.

Types

type BuilderBase 

type BuilderBase struct {
	Table id
	// IsUnsafe if set to true the functions AddColumn* will turn any
	// non valid identifier (not `{a-z}[a-z0-9$_]+`i) into an expression.
	IsUnsafe bool
	// contains filtered or unexported fields
}

BuilderBase contains fields which all SQL query builder have in common, the same base. Exported for documentation reasons.

func (BuilderBase) Clone 

func (bb BuilderBase) Clone() BuilderBase

Clone creates a clone of the current object.

type BuilderConditional 

type BuilderConditional struct {
	Joins    Joins
	Wheres   Conditions
	OrderBys ids
	// OrderByRandColumnName defines the column name of the single primary key
	// in a table to build the optimized ORDER BY RAND() JOIN clause.
	OrderByRandColumnName string
	LimitCount            uint64
	LimitValid            bool
}

BuilderConditional defines base fields used in statements which can have conditional constraints like WHERE, JOIN, ORDER, etc. Exported for documentation reasons.

func (BuilderConditional) Clone 

func (b BuilderConditional) Clone() BuilderConditional

Clone creates a new clone of the current object.

type ColumnMap 

type ColumnMap struct {

	// CheckValidUTF8 if enabled checks if strings contains valid UTF-8 characters.
	CheckValidUTF8 bool
	// HasRows set to true if at least one row has been found.
	HasRows bool
	// Count increments on call to Scan.
	Count uint64
	// contains filtered or unexported fields
}

ColumnMap takes care that the table/view/identifiers are getting properly mapped to ColumnMapper interface. ColumnMap has two run modes either collect arguments from a type for running a SQL query OR to convert the sql.RawBytes into the desired final type. ColumnMap scans a *sql.Rows into a *sql.RawBytes slice without having a big memory overhead and not a single use of reflection. The conversion into the desired final type can happen without allocating of memory. It does not support streaming because neither database/sql does :-( The method receiver functions have the same names as in type ColumnMap.

func NewColumnMap 

func NewColumnMap(cap int, columns ...string) *ColumnMap

NewColumnMap exported for testing reasons.

func (*ColumnMap) Binary 

func (b *ColumnMap) Binary(enc interface {
	encoding.BinaryMarshaler
	encoding.BinaryUnmarshaler
}) *ColumnMap

Binary allows to encode an object to its binary representation when arguments are requested and to decode a byte slice into its object when data is retrieved from the server. Use this function for GOB, Protocol Buffers, etc formats.

func (*ColumnMap) Bool 

func (b *ColumnMap) Bool(ptr *bool) *ColumnMap

Bool reads a bool value and appends it to the arguments slice or assigns the bool value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Byte 

func (b *ColumnMap) Byte(ptr *[]byte) *ColumnMap

Byte reads a []byte value and appends it to the arguments slice or assigns the []byte value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Column 

func (b *ColumnMap) Column() string

Column returns the current column name after calling `Next`.

func (*ColumnMap) Debug 

func (b *ColumnMap) Debug(w ioWriter) (err error)

Debug writes the column names with their values into `w`. The output format might change.

func (*ColumnMap) Decimal 

func (b *ColumnMap) Decimal(ptr *null.Decimal) *ColumnMap

Decimal reads a Decimal value and appends it to the arguments slice or assigns the numeric value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Err 

func (b *ColumnMap) Err() error

Err returns the delayed error from one of the scans and parsings. Function is idempotent.

func (*ColumnMap) Float64 

func (b *ColumnMap) Float64(ptr *float64) *ColumnMap

Float64 reads a float64 value and appends it to the arguments slice or assigns the float64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int 

func (b *ColumnMap) Int(ptr *int) *ColumnMap

Int reads an int value and appends it to the arguments slice or assigns the int value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int16 

func (b *ColumnMap) Int16(ptr *int16) *ColumnMap

Int16 reads a int16 value and appends it to the arguments slice or assigns the int16 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int16s 

func (b *ColumnMap) Int16s(values ...int16) *ColumnMap

func (*ColumnMap) Int32 

func (b *ColumnMap) Int32(ptr *int32) *ColumnMap

Int32 reads a int32 value and appends it to the arguments slice or assigns the int32 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int32s 

func (b *ColumnMap) Int32s(values ...int32) *ColumnMap

func (*ColumnMap) Int64 

func (b *ColumnMap) Int64(ptr *int64) *ColumnMap

Int64 reads a int64 value and appends it to the arguments slice or assigns the int64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int64s 

func (b *ColumnMap) Int64s(values ...int64) *ColumnMap

func (*ColumnMap) Int8 

func (b *ColumnMap) Int8(ptr *int8) *ColumnMap

Int8 reads a int8 value and appends it to the arguments slice or assigns the int8 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Int8s 

func (b *ColumnMap) Int8s(values ...int8) *ColumnMap

func (*ColumnMap) Mode 

func (b *ColumnMap) Mode() (m columnMapMode)

Mode returns a status byte of four different states. These states are getting used in the implementation of ColumnMapper. Each state represents a different action while scanning from the query or collecting arguments. ColumnMapper can be implemented by either a single type or a slice/map type. Slice or not slice requires different states. A primitive type must only handle mode ColumnMapEntityReadAll to return all requested fields. A slice type must handle additionally the cases ColumnMapEntityReadSet, ColumnMapCollectionReadSet and ColumnMapScan. See the examples. Documentation needs to be written better.

func (*ColumnMap) Next 

func (b *ColumnMap) Next(fieldCount int) bool

Next moves the internal index to the next position. It may return false if during RawBytes scanning an error has occurred.

func (*ColumnMap) NullBool 

func (b *ColumnMap) NullBool(ptr *null.Bool) *ColumnMap

NullBool reads a bool value and appends it to the arguments slice or assigns the bool value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullFloat64 

func (b *ColumnMap) NullFloat64(ptr *null.Float64) *ColumnMap

NullFloat64 reads a float64 value and appends it to the arguments slice or assigns the float64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullInt16 

func (b *ColumnMap) NullInt16(ptr *null.Int16) *ColumnMap

NullInt16 reads an int16 value and appends it to the arguments slice or assigns the int16 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullInt32 

func (b *ColumnMap) NullInt32(ptr *null.Int32) *ColumnMap

NullInt32 reads an int32 value and appends it to the arguments slice or assigns the int32 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullInt64 

func (b *ColumnMap) NullInt64(ptr *null.Int64) *ColumnMap

NullInt64 reads an int64 value and appends it to the arguments slice or assigns the int64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullInt8 

func (b *ColumnMap) NullInt8(ptr *null.Int8) *ColumnMap

NullInt8 reads an int8 value and appends it to the arguments slice or assigns the int8 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullString 

func (b *ColumnMap) NullString(ptr *null.String) *ColumnMap

NullString reads a string value and appends it to the arguments slice or assigns the string value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullStrings 

func (b *ColumnMap) NullStrings(values ...null.String) *ColumnMap

func (*ColumnMap) NullTime 

func (b *ColumnMap) NullTime(ptr *null.Time) *ColumnMap

NullTime reads a time value and appends it to the arguments slice or assigns the NullTime value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullUint16 

func (b *ColumnMap) NullUint16(ptr *null.Uint16) *ColumnMap

NullInt16 reads an int16 value and appends it to the arguments slice or assigns the int16 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullUint32 

func (b *ColumnMap) NullUint32(ptr *null.Uint32) *ColumnMap

NullInt32 reads an int32 value and appends it to the arguments slice or assigns the int32 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullUint64 

func (b *ColumnMap) NullUint64(ptr *null.Uint64) *ColumnMap

NullInt64 reads an int64 value and appends it to the arguments slice or assigns the int64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) NullUint8 

func (b *ColumnMap) NullUint8(ptr *null.Uint8) *ColumnMap

NullInt8 reads an int8 value and appends it to the arguments slice or assigns the int8 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Scan 

func (b *ColumnMap) Scan(r *sql.Rows) error

Scan calls rows.Scan and builds an internal stack of sql.RawBytes for further processing and type conversion.

Each function for a specific type converts the underlying byte slice at the current applied index (see function Index) to the appropriate type. You can call as many times as you want the specific functions. The underlying byte slice value is valid until the next call to rows.Next, rows.Scan or rows.Close. See the example for further usages.

func (*ColumnMap) String 

func (b *ColumnMap) String(ptr *string) *ColumnMap

String reads a string value and appends it to the arguments slice or assigns the string value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Strings 

func (b *ColumnMap) Strings(values ...string) *ColumnMap

func (*ColumnMap) Text 

func (b *ColumnMap) Text(enc interface {
	encoding.TextMarshaler
	encoding.TextUnmarshaler
}) *ColumnMap

Text allows to encode an object to its text representation when arguments are requested and to decode a byte slice into its object when data is retrieved from the server. Use this function for JSON, XML, YAML, etc formats. This function can check for valid UTF8 characters, see field CheckValidUTF8.

func (*ColumnMap) Time 

func (b *ColumnMap) Time(ptr *time.Time) *ColumnMap

Time reads a time.Time value and appends it to the arguments slice or assigns the time.Time value stored in sql.RawBytes to the pointer. See the documentation for function Scan. It supports all MySQL/MariaDB date/time types.

func (*ColumnMap) Times 

func (b *ColumnMap) Times(values ...time.Time) *ColumnMap

func (*ColumnMap) Uint 

func (b *ColumnMap) Uint(ptr *uint) *ColumnMap

Uint reads an uint value and appends it to the arguments slice or assigns the uint value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Uint16 

func (b *ColumnMap) Uint16(ptr *uint16) *ColumnMap

Uint16 reads an uint16 value and appends it to the arguments slice or assigns the uint16 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Uint16s 

func (b *ColumnMap) Uint16s(values ...uint16) *ColumnMap

func (*ColumnMap) Uint32 

func (b *ColumnMap) Uint32(ptr *uint32) *ColumnMap

Uint32 reads an uint32 value and appends it to the arguments slice or assigns the uint32 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Uint32s 

func (b *ColumnMap) Uint32s(values ...uint32) *ColumnMap

func (*ColumnMap) Uint64 

func (b *ColumnMap) Uint64(ptr *uint64) *ColumnMap

Uint64 reads an uint64 value and appends it to the arguments slice or assigns the uint64 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Uint64s 

func (b *ColumnMap) Uint64s(values ...uint64) *ColumnMap

func (*ColumnMap) Uint8 

func (b *ColumnMap) Uint8(ptr *uint8) *ColumnMap

Uint8 reads an uint8 value and appends it to the arguments slice or assigns the uint8 value stored in sql.RawBytes to the pointer. See the documentation for function Scan.

func (*ColumnMap) Uint8s 

func (b *ColumnMap) Uint8s(values ...uint8) *ColumnMap

type ColumnMapper 

type ColumnMapper interface {
	// RowScan implementation must use function `Scan` to scan the values of the
	// query into its own type. See database/sql package for examples.
	MapColumns(rc *ColumnMap) error
}

ColumnMapper allows a type to load data from database query into its fields or return the fields values as arguments for a query. It's used in the rows.Next() for-loop. A ColumnMapper is usually a single record/row or in case of a slice a complete query result.

Example

ExampleColumnMapper implementation POC for interface ColumnMapper. Retrieving and loading/scanning rows from a database mock. 

package main

import (
	"context"
	"fmt"
	"strings"

	"github.com/corestoreio/errors"
	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/sql/dmltest"
	"github.com/corestoreio/pkg/storage/null"
)

// Make sure that type customerEntity implements interface.
var (
	_ dml.ColumnMapper = (*customerCollection)(nil)
	_ dml.ColumnMapper = (*customerEntity)(nil)
)

// customerCollection a slice of customer entities.
type customerCollection struct {
	Data []*customerEntity
	// AfterScan gets called in mode ColumnMapScan after the new
	// customerEntity has been created and assigned with values from the query.
	AfterScan []func(*customerEntity)
}

// customerEntity has been generated from the SQL table customer_entities.
type customerEntity struct {
	EntityID      uint64 // Auto Increment, supports until MaxUint64
	Firstname     string
	StoreID       uint16
	LifetimeSales null.Float64
	// VoucherCodes contains list of refunded codes, stored as CSV. Or even
	// stored in another table or even encrypted and the function decrypts it on
	// load. Same as the M2 EAV models.
	VoucherCodes exampleStringSlice
}

type exampleStringSlice []string

func (sl exampleStringSlice) ToString() string {
	return strings.Join(sl, "|")
}

func (sl exampleStringSlice) FromString(s string) []string {
	return strings.Split(s, "|")
}

func newCustomerEntity() *customerEntity {
	return &customerEntity{}
}

// MapColumns implements interface ColumnMapper only partially.
func (p *customerEntity) MapColumns(cm *dml.ColumnMap) error {
	for cm.Next(5) {
		switch c := cm.Column(); c {
		case "entity_id", "customer_id", "0": // customer_id is an alias
			cm.Uint64(&p.EntityID)
		case "firstname", "1":
			cm.String(&p.Firstname)
		case "store_id", "2":
			cm.Uint16(&p.StoreID)
		case "lifetime_sales", "3":
			cm.NullFloat64(&p.LifetimeSales)
		case "voucher_codes", "4":
			if cm.Mode() == dml.ColumnMapScan {
				var voucherCodes string
				cm.String(&voucherCodes)
				p.VoucherCodes = p.VoucherCodes.FromString(voucherCodes)
			} else {
				voucherCodes := p.VoucherCodes.ToString()
				cm.String(&voucherCodes)
			}
		default:
			return errors.NotFound.Newf("[dml_test] customerEntity Column %q not found", c)
		}
	}
	return cm.Err()
}

func (cc *customerCollection) MapColumns(cm *dml.ColumnMap) error {
	switch m := cm.Mode(); m {
	case dml.ColumnMapEntityReadAll, dml.ColumnMapEntityReadSet:
		for _, p := range cc.Data {
			if err := p.MapColumns(cm); err != nil {
				return errors.WithStack(err)
			}
		}
	case dml.ColumnMapScan:
		if cm.Count == 0 {
			cc.Data = cc.Data[:0]
		}
		p := newCustomerEntity()
		if err := p.MapColumns(cm); err != nil {
			return errors.WithStack(err)
		}
		for _, fn := range cc.AfterScan {
			fn(p)
		}
		cc.Data = append(cc.Data, p)
	case dml.ColumnMapCollectionReadSet:
		for cm.Next(0) {
			switch c := cm.Column(); c {
			case "entity_id", "customer_id":
				cm.Uint64s(cc.EntityIDs()...)
			case "firstname":
				cm.Strings(cc.Firstnames()...)
			default:
				return errors.NotFound.Newf("[dml_test] customerCollection Column %q not found", c)
			}
		}
	default:
		return errors.NotSupported.Newf("[dml] Unknown Mode: %q", string(m))
	}
	return cm.Err()
}

func (ps *customerCollection) EntityIDs(ret ...uint64) []uint64 {
	if ret == nil {
		ret = make([]uint64, 0, len(ps.Data))
	}
	for _, p := range ps.Data {
		ret = append(ret, p.EntityID)
	}
	return ret
}

func (ps *customerCollection) Firstnames(ret ...string) []string {
	if ret == nil {
		ret = make([]string, 0, len(ps.Data))
	}
	for _, p := range ps.Data {
		ret = append(ret, p.Firstname)
	}
	return ret // can be made unique
}

// ExampleColumnMapper implementation POC for interface ColumnMapper. Retrieving
// and loading/scanning rows from a database mock.
func main() {
	// <ignore_this>
	dbc, dbMock := dmltest.MockDB(nil)
	defer dmltest.MockClose(nil, dbc, dbMock)

	r := dmltest.MustMockRows(dmltest.WithFile("testdata", "customer_entity_example.csv"))
	dbMock.ExpectQuery(dmltest.SQLMockQuoteMeta("SELECT * FROM `customer_entity`")).WillReturnRows(r)
	// </ignore_this>

	_ = dbc.RegisterByQueryBuilder(map[string]dml.QueryBuilder{
		"select001": dml.NewSelect("*").From("customer_entity"),
	})
	customers := new(customerCollection)

	_, err := dbc.WithCacheKey("select001").Load(context.TODO(), customers)
	if err != nil {
		panic(err)
	}
	fmt.Printf("Result of %v query:\n", dbc.CachedQueries())
	fmt.Println("[entity_id firstname store_id lifetime_sales voucher_codes]")
	for _, c := range customers.Data {
		fmt.Printf("%v\n", *c)
	}

}

Output:

Result of map[select001:SELECT * FROM `customer_entity`] query:
[entity_id firstname store_id lifetime_sales voucher_codes]
{18446744073700551613 Karl Gopher 7 47.11 [1FE9983E 28E76FBC]}
{18446744073700551614 Fung Go Roo 7 28.94 [4FE7787E 15E59FBB 794EFDE8]}
{18446744073700551615 John Doe 6 138.54 []}
Example (InsertCollectionWithoutColumns) 
q := dml.NewInsert("customer_entity"). // AddColumns("firstname", "lifetime_sales", "store_id", "voucher_codes"). .
					SetRowCount(len(cmCustomers.Data)).WithDBR(dbMock{}).TestWithArgs(dml.Qualify("", cmCustomers))

writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
INSERT INTO `customer_entity` VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)
Arguments: [11 Karl Gopher 7 47.11 1FE9983E|28E76FBC 12 Fung Go Roo 7 28.94 4FE7787E|15E59FBB|794EFDE8 13 John Doe 6 138.54 ]

Interpolated Statement:
INSERT INTO `customer_entity` VALUES (11,'Karl
Gopher',7,47.11,'1FE9983E|28E76FBC'),(12,'Fung Go
Roo',7,28.94,'4FE7787E|15E59FBB|794EFDE8'),(13,'John Doe',6,138.54,'')
Example (InsertEntitiesWithColumns)

ExampleColumnMapper_insertEntities inserts multiple entities into a table. Collection not yet supported. 

q := dml.NewInsert("customer_entity").AddColumns("firstname", "lifetime_sales", "store_id", "voucher_codes").
	WithDBR(dbMock{}).TestWithArgs(
	// might get optimized in the future, but it depends.
	dml.Qualify("", cmCustomers.Data[0]),
	dml.Qualify("", cmCustomers.Data[1]),
	dml.Qualify("", cmCustomers.Data[2]),
)
writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
INSERT INTO `customer_entity`
(`firstname`,`lifetime_sales`,`store_id`,`voucher_codes`) VALUES
(?,?,?,?),(?,?,?,?),(?,?,?,?)
Arguments: [Karl Gopher 47.11 7 1FE9983E|28E76FBC Fung Go Roo 28.94 7 4FE7787E|15E59FBB|794EFDE8 John Doe 138.54 6 ]

Interpolated Statement:
INSERT INTO `customer_entity`
(`firstname`,`lifetime_sales`,`store_id`,`voucher_codes`) VALUES ('Karl
Gopher',47.11,7,'1FE9983E|28E76FBC'),('Fung Go
Roo',28.94,7,'4FE7787E|15E59FBB|794EFDE8'),('John Doe',138.54,6,'')
Example (InsertEntitiesWithoutColumns)

ExampleColumnMapper_insertEntitiesWithoutColumns inserts multiple entities into a table. It includes all fields in the sruct. In this case 5 fields including the autoincrement field. 

q := dml.NewInsert("customer_entity").
	WithDBR(dbMock{}).TestWithArgs(
	dml.Qualify("", cmCustomers.Data[0]),
	dml.Qualify("", cmCustomers.Data[1]),
	dml.Qualify("", cmCustomers.Data[2]),
)
writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
INSERT INTO `customer_entity` VALUES (?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)
Arguments: [11 Karl Gopher 7 47.11 1FE9983E|28E76FBC 12 Fung Go Roo 7 28.94 4FE7787E|15E59FBB|794EFDE8 13 John Doe 6 138.54 ]

Interpolated Statement:
INSERT INTO `customer_entity` VALUES (11,'Karl
Gopher',7,47.11,'1FE9983E|28E76FBC'),(12,'Fung Go
Roo',7,28.94,'4FE7787E|15E59FBB|794EFDE8'),(13,'John Doe',6,138.54,'')
Example (SelectJoinCollection)

ExampleColumnMapper_selectJoinCollection uses a qualified customer collection. The qualifier maps to the alias name of the customer_entity table. 

q := dml.NewSelect("ce.entity_id", "ce.firstname", "cg.customer_group_code", "cg.tax_class_id").FromAlias("customer_entity", "ce").
	Join(dml.MakeIdentifier("customer_group").Alias("cg"),
		dml.Column("ce.group_id").Equal().Column("cg.customer_group_id"),
	).
	Where(
		dml.Column("ce.entity_id").In().PlaceHolder(),
	).
	WithDBR(dbMock{}).TestWithArgs(dml.Qualify("ce", cmCustomers))

writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
SELECT `ce`.`entity_id`, `ce`.`firstname`, `cg`.`customer_group_code`,
`cg`.`tax_class_id` FROM `customer_entity` AS `ce` INNER JOIN `customer_group`
AS `cg` ON (`ce`.`group_id` = `cg`.`customer_group_id`) WHERE (`ce`.`entity_id`
IN ?)
Arguments: [11 12 13]

Interpolated Statement:
SELECT `ce`.`entity_id`, `ce`.`firstname`, `cg`.`customer_group_code`,
`cg`.`tax_class_id` FROM `customer_entity` AS `ce` INNER JOIN `customer_group`
AS `cg` ON (`ce`.`group_id` = `cg`.`customer_group_id`) WHERE (`ce`.`entity_id`
IN (11,12,13))
Example (SelectSalesOrdersFromSpecificCustomers)

ExampleColumnMapper_selectSalesOrdersFromSpecificCustomers this query should return all sales orders from different customers which are loaded within a collection. The challenge depict to map the customer_entity.entity_id column to the sales_order_entity.customer_id column. 

// Column `customer_id` has been hard coded into the switch statement of the
// ColumnMapper in customerCollection and customerEntity. `customer_id` acts
// as an alias to `entity_id`.
q := dml.NewSelect("entity_id", "status", "increment_id", "grand_total", "tax_total").From("sales_order_entity").
	Where(dml.Column("customer_id").In().PlaceHolder()).WithDBR(dbMock{}).TestWithArgs(dml.Qualify("", cmCustomers))

writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
SELECT `entity_id`, `status`, `increment_id`, `grand_total`, `tax_total` FROM
`sales_order_entity` WHERE (`customer_id` IN ?)
Arguments: [11 12 13]

Interpolated Statement:
SELECT `entity_id`, `status`, `increment_id`, `grand_total`, `tax_total` FROM
`sales_order_entity` WHERE (`customer_id` IN (11,12,13))
Example (SelectWhereInCollection)

ExampleColumnMapper_selectWhereInCollection uses a customer collection to retrieve all entity_ids to be used in an IN condition. The customer collection does not get qualified because SELECT happens from one table without an alias. 

q := dml.NewSelect("entity_id", "firstname", "lifetime_sales").From("customer_entity").
	Where(
		dml.Column("entity_id").In().PlaceHolder(),
	).
	// for variable customers see ExampleColumnMapper
	WithDBR(dbMock{}).TestWithArgs(dml.Qualify("", cmCustomers))
writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
SELECT `entity_id`, `firstname`, `lifetime_sales` FROM `customer_entity` WHERE
(`entity_id` IN ?)
Arguments: [11 12 13]

Interpolated Statement:
SELECT `entity_id`, `firstname`, `lifetime_sales` FROM `customer_entity` WHERE
(`entity_id` IN (11,12,13))
Example (UpdateEntity)

ExampleColumnMapper_updateEntity updates an entity with the defined fields. 

q := dml.NewUpdate("customer_entity").AddColumns("firstname", "lifetime_sales", "voucher_codes").
	Where(dml.Column("entity_id").Equal().PlaceHolder()).
	WithDBR(dbMock{}).TestWithArgs(dml.Qualify("", cmCustomers.Data[0])) // Empty string is the qualifier

writeToSQLAndInterpolate(q)

Output:

Prepared Statement:
UPDATE `customer_entity` SET `firstname`=?, `lifetime_sales`=?,
`voucher_codes`=? WHERE (`entity_id` = ?)
Arguments: [Karl Gopher 47.11 1FE9983E|28E76FBC 11]

Interpolated Statement:
UPDATE `customer_entity` SET `firstname`='Karl Gopher', `lifetime_sales`=47.11,
`voucher_codes`='1FE9983E|28E76FBC' WHERE (`entity_id` = 11)

type Condition 

type Condition struct {
	Aliased string
	// Left can contain either a valid identifier or an expression. Set field
	// `IsLeftExpression` to true to avoid quoting of the this field. Left can also
	// contain a string in the format `qualifier.identifier`.
	Left string
	// Right defines the right hand side for an assignment which can be either a
	// single argument, multiple arguments in case of an expression, a sub
	// select or a name of a column.
	Right struct {
		// Column defines a column name to compare to. The column, with an
		// optional qualifier, gets quoted, in case IsExpression is false.
		Column string
		// PlaceHolder can be a :named or the MySQL/MariaDB place holder
		// character `?`. If set, the current condition just acts as a place
		// holder for a prepared statement or an interpolation. In case of a
		// :named place holder for a prepared statement, the :named string gets
		// replaced with the `?`. The allowed characters are unicode letters and
		// digits.
		PlaceHolder string

		// Select adds a sub-select to the where statement. Column must be
		// either a column name or anything else which can handle the result of
		// a sub-select.
		Sub *Select
		// IsExpression if true field `Column` gets treated as an expression.
		// Additionally the field Right.args will be read to extract any
		// given args.
		IsExpression bool
		// contains filtered or unexported fields
	}
	// Operator contains the comparison logic like LIKE, IN, GREATER, etc ...
	// defaults to EQUAL.
	Operator Op
	// IsLeftExpression if set to true, the field Left won't get quoted and
	// treated as an expression. Additionally the field Right.args will be
	// read to extract any given args.
	IsLeftExpression bool
	// Logical states how multiple WHERE statements will be connected.
	// Default to AND. Possible values are a=AND, o=OR, x=XOR, n=NOT
	Logical byte
	// Columns is a list of column names which get quoted during SQL statement
	// creation in the JOIN part for the USING syntax. Additionally used in ON
	// DUPLICATE KEY.
	Columns []string
	// contains filtered or unexported fields
}

Condition implements a single condition often used in WHERE, ON, SET and ON DUPLICATE KEY UPDATE. Please use the helper functions instead of using this type directly.

Example

ExampleCondition is duplicate of ExampleColumn 

package main

import (
	"fmt"

	"github.com/corestoreio/pkg/sql/dml"
)

func argPrinter(wf *dml.Condition) {
	sqlStr, args, err := dml.NewSelect().AddColumns("a", "b").
		From("c").Where(wf).ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
	} else {
		fmt.Printf("%q", sqlStr)
		if len(args) > 0 {
			fmt.Printf(" Arguments: %v", args)
		}
		fmt.Print("\n")
	}
}

func main() {
	argPrinter(dml.Column("d").Null())
	argPrinter(dml.Column("d").NotNull())
	argPrinter(dml.Column("d").Int(2))
	argPrinter(dml.Column("d").Int(3).Null())
	argPrinter(dml.Column("d").Int(4).NotNull())
	argPrinter(dml.Column("d").In().Ints(7, 8, 9))
	argPrinter(dml.Column("d").NotIn().Ints(10, 11, 12))
	argPrinter(dml.Column("d").Between().Ints(13, 14))
	argPrinter(dml.Column("d").NotBetween().Ints(15, 16))
	argPrinter(dml.Column("d").Greatest().Ints(17, 18, 19))
	argPrinter(dml.Column("d").Least().Ints(20, 21, 22))
	argPrinter(dml.Column("d").Equal().Int(30))
	argPrinter(dml.Column("d").NotEqual().Int(31))
	argPrinter(dml.Column("alias.column").SpaceShip().Float64(3.14159))

	argPrinter(dml.Column("d").Less().Int(32))
	argPrinter(dml.Column("d").Greater().Int(33))
	argPrinter(dml.Column("d").LessOrEqual().Int(34))
	argPrinter(dml.Column("d").GreaterOrEqual().Int(35))

	argPrinter(dml.Column("d").Like().Str("Goph%"))
	argPrinter(dml.Column("d").NotLike().Str("Cat%"))

}

Output:

"SELECT `a`, `b` FROM `c` WHERE (`d` IS NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NOT NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` = 2)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NOT NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IN (7,8,9))"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT IN (10,11,12))"
"SELECT `a`, `b` FROM `c` WHERE (`d` BETWEEN 13 AND 14)"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT BETWEEN 15 AND 16)"
"SELECT `a`, `b` FROM `c` WHERE (`d` GREATEST (17,18,19))"
"SELECT `a`, `b` FROM `c` WHERE (`d` LEAST (20,21,22))"
"SELECT `a`, `b` FROM `c` WHERE (`d` = 30)"
"SELECT `a`, `b` FROM `c` WHERE (`d` != 31)"
"SELECT `a`, `b` FROM `c` WHERE (`alias`.`column` <=> 3.14159)"
"SELECT `a`, `b` FROM `c` WHERE (`d` < 32)"
"SELECT `a`, `b` FROM `c` WHERE (`d` > 33)"
"SELECT `a`, `b` FROM `c` WHERE (`d` <= 34)"
"SELECT `a`, `b` FROM `c` WHERE (`d` >= 35)"
"SELECT `a`, `b` FROM `c` WHERE (`d` LIKE 'Goph%')"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT LIKE 'Cat%')"

func Column 

func Column(columnName string) *Condition

Column adds a new condition.

Example

ExampleColumn is a duplicate of ExampleCondition 

package main

import (
	"fmt"

	"github.com/corestoreio/pkg/sql/dml"
)

func argPrinter(wf *dml.Condition) {
	sqlStr, args, err := dml.NewSelect().AddColumns("a", "b").
		From("c").Where(wf).ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
	} else {
		fmt.Printf("%q", sqlStr)
		if len(args) > 0 {
			fmt.Printf(" Arguments: %v", args)
		}
		fmt.Print("\n")
	}
}

func main() {
	argPrinter(dml.Column("d").Null())
	argPrinter(dml.Column("d").NotNull())
	argPrinter(dml.Column("d").Int(2))
	argPrinter(dml.Column("d").Int(3).Null())
	argPrinter(dml.Column("d").Int(4).NotNull())
	argPrinter(dml.Column("d").In().Ints(7, 8, 9))
	argPrinter(dml.Column("d").NotIn().Ints(10, 11, 12))
	argPrinter(dml.Column("d").Between().Ints(13, 14))
	argPrinter(dml.Column("d").NotBetween().Ints(15, 16))
	argPrinter(dml.Column("d").Greatest().Ints(17, 18, 19))
	argPrinter(dml.Column("d").Least().Ints(20, 21, 22))
	argPrinter(dml.Column("d").Equal().Int(30))
	argPrinter(dml.Column("d").NotEqual().Int(31))
	argPrinter(dml.Column("alias.column").SpaceShip().Float64(3.14159))

	argPrinter(dml.Column("d").Less().Int(32))
	argPrinter(dml.Column("d").Greater().Int(33))
	argPrinter(dml.Column("d").LessOrEqual().Int(34))
	argPrinter(dml.Column("d").GreaterOrEqual().Int(35))

	argPrinter(dml.Column("d").Like().Str("Goph%"))
	argPrinter(dml.Column("d").NotLike().Str("Cat%"))

}

Output:

"SELECT `a`, `b` FROM `c` WHERE (`d` IS NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NOT NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` = 2)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IS NOT NULL)"
"SELECT `a`, `b` FROM `c` WHERE (`d` IN (7,8,9))"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT IN (10,11,12))"
"SELECT `a`, `b` FROM `c` WHERE (`d` BETWEEN 13 AND 14)"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT BETWEEN 15 AND 16)"
"SELECT `a`, `b` FROM `c` WHERE (`d` GREATEST (17,18,19))"
"SELECT `a`, `b` FROM `c` WHERE (`d` LEAST (20,21,22))"
"SELECT `a`, `b` FROM `c` WHERE (`d` = 30)"
"SELECT `a`, `b` FROM `c` WHERE (`d` != 31)"
"SELECT `a`, `b` FROM `c` WHERE (`alias`.`column` <=> 3.14159)"
"SELECT `a`, `b` FROM `c` WHERE (`d` < 32)"
"SELECT `a`, `b` FROM `c` WHERE (`d` > 33)"
"SELECT `a`, `b` FROM `c` WHERE (`d` <= 34)"
"SELECT `a`, `b` FROM `c` WHERE (`d` >= 35)"
"SELECT `a`, `b` FROM `c` WHERE (`d` LIKE 'Goph%')"
"SELECT `a`, `b` FROM `c` WHERE (`d` NOT LIKE 'Cat%')"

func Columns 

func Columns(columns ...string) *Condition

Columns add syntactic sugar to a JOIN or ON DUPLICATE KEY statement: In case of JOIN: The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables: 

a LEFT JOIN b USING (c1, c2, c3)

The columns list gets quoted while writing the query string. In case of ON DUPLICATE KEY each column gets written like: `column`=VALUES(`column`). Any other field in *Condition gets ignored once field Columns has been set.

func Expr 

func Expr(expression string) *Condition

Expr adds an unquoted SQL expression to a column, WHERE, HAVING, SET or ON DUPLICATE KEY statement. Each item of an expression gets written into the buffer without a separator.

func ParenthesisClose 

func ParenthesisClose() *Condition

ParenthesisClose sets a closing parenthesis ")". Mostly used for OR conditions in combination with AND conditions.

func ParenthesisOpen 

func ParenthesisOpen() *Condition

ParenthesisOpen sets an open parenthesis "(". Mostly used for OR conditions in combination with AND conditions.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	s := dml.NewSelect("columnA", "columnB").
		Distinct().
		FromAlias("tableC", "ccc").
		Where(
			dml.ParenthesisOpen(),
			dml.Column("d").Int(1),
			dml.Column("e").Str("wat").Or(),
			dml.ParenthesisClose(),
			dml.Column("f").Int(2),
		).
		GroupBy("ab").
		Having(
			dml.Expr("j = k"),
			dml.ParenthesisOpen(),
			dml.Column("m").Int(33),
			dml.Column("n").Str("wh3r3").Or(),
			dml.ParenthesisClose(),
		).
		OrderBy("l").
		Limit(8, 7)
	writeToSQLAndInterpolate(s)

}

Output:

Statement:
SELECT DISTINCT `columnA`, `columnB` FROM `tableC` AS `ccc` WHERE ((`d` = 1) OR
(`e` = 'wat')) AND (`f` = 2) GROUP BY `ab` HAVING (j = k) AND ((`m` = 33) OR
(`n` = 'wh3r3')) ORDER BY `l` LIMIT 8,7

func SQLCase 

func SQLCase(value, defaultValue string, compareResult ...string) *Condition

SQLCase generates a CASE ... WHEN ... THEN ... ELSE ... END statement. `value` argument can be empty. defaultValue used in the ELSE part can also be empty and then won't get written. `compareResult` must be a balanced sliced where index `i` represents the case part and index `i+1` the result. If the slice is imbalanced the function assumes that the last item of compareResult should be printed as an alias. https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case

Example (Select)

ExampleSQLCase_select is a duplicate of ExampleSelect_AddArguments 

// time stamp has no special meaning ;-)
start := time.Unix(1257894000, 0).In(time.UTC)
end := time.Unix(1257980400, 0).In(time.UTC)

s := dml.NewSelect().AddColumns("price", "sku", "name", "title", "description").
	AddColumnsConditions(
		dml.SQLCase("", "`closed`",
			"date_start <= ? AND date_end >= ?", "`open`",
			"date_start > ? AND date_end > ?", "`upcoming`",
		).Alias("is_on_sale"),
	).
	From("catalog_promotions").Where(
	dml.Column("promotion_id").NotIn().PlaceHolders(3)).
	WithDBR(dbMock{}).TestWithArgs(start, end, start, end, 4711, 815, 42)
writeToSQLAndInterpolate(s)

Output:

Prepared Statement:
SELECT `price`, `sku`, `name`, `title`, `description`, CASE  WHEN date_start <=
? AND date_end >= ? THEN `open` WHEN date_start > ? AND date_end > ? THEN
`upcoming` ELSE `closed` END AS `is_on_sale` FROM `catalog_promotions` WHERE
(`promotion_id` NOT IN (?,?,?))
Arguments: [2009-11-10 23:00:00 +0000 UTC 2009-11-11 23:00:00 +0000 UTC 2009-11-10 23:00:00 +0000 UTC 2009-11-11 23:00:00 +0000 UTC 4711 815 42]

Interpolated Statement:
SELECT `price`, `sku`, `name`, `title`, `description`, CASE  WHEN date_start <=
'2009-11-10 23:00:00' AND date_end >= '2009-11-11 23:00:00' THEN `open` WHEN
date_start > '2009-11-10 23:00:00' AND date_end > '2009-11-11 23:00:00' THEN
`upcoming` ELSE `closed` END AS `is_on_sale` FROM `catalog_promotions` WHERE
(`promotion_id` NOT IN (4711,815,42))
Example (Update) 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	u := dml.NewUpdate("cataloginventory_stock_item").
		AddClauses(dml.Column("qty").SQLCase("`product_id`", "qty",
			"3456", "qty+?",
			"3457", "qty+?",
			"3458", "qty+?",
		).Int(3).Int(4).Int(5)).
		Where(
			dml.Column("product_id").In().Int64s(345, 567, 897),
			dml.Column("website_id").Int64(6),
		)
	writeToSQLAndInterpolate(u)

}

Output:

Statement:
UPDATE `cataloginventory_stock_item` SET `qty`=CASE `product_id` WHEN 3456 THEN
qty+3 WHEN 3457 THEN qty+4 WHEN 3458 THEN qty+5 ELSE qty END WHERE (`product_id`
IN (345,567,897)) AND (`website_id` = 6)

func SQLIf 

func SQLIf(expression, true, false string) *Condition

SQLIf writes a SQL IF() expression. 

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used. Returns a []string.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	s := dml.NewSelect().
		AddColumns("a", "b", "c").
		From("table1").
		Where(
			dml.SQLIf("a > 0", "b", "c").Greater().Int(4711),
		)
	writeToSQLAndInterpolate(s)

}

Output:

Statement:
SELECT `a`, `b`, `c` FROM `table1` WHERE (IF((a > 0), b, c) > 4711)

func SQLIfNull 

func SQLIfNull(expression ...string) *Condition

SQLIfNull creates an IFNULL expression. Argument count can be either 1, 2 or 4. A single expression can contain a qualified or unqualified identifier. See the examples.

IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

Example 
package main

import (
	"fmt"
	"strings"

	"github.com/corestoreio/pkg/sql/dml"
)

func main() {
	s := dml.NewSelect().AddColumnsConditions(
		dml.SQLIfNull("column1"),
		dml.SQLIfNull("table1.column1"),
		dml.SQLIfNull("column1", "column2"),
		dml.SQLIfNull("table1.column1", "table2.column2"),
		dml.SQLIfNull("column2", "1/0").Alias("alias"),
		dml.SQLIfNull("SELECT * FROM x", "8").Alias("alias"),
		dml.SQLIfNull("SELECT * FROM x", "9 ").Alias("alias"),
		dml.SQLIfNull("column1", "column2").Alias("alias"),
		dml.SQLIfNull("table1.column1", "table2.column2").Alias("alias"),
		dml.SQLIfNull("table1", "column1", "table2", "column2"),
		dml.SQLIfNull("table1", "column1", "table2", "column2").Alias("alias"),
	).From("table1")
	sStr, _, _ := s.ToSQL()
	fmt.Print(strings.Replace(sStr, ", ", ",\n", -1))

}

Output:

SELECT IFNULL(`column1`,NULL),
IFNULL(`table1`.`column1`,NULL),
IFNULL(`column1`,`column2`),
IFNULL(`table1`.`column1`,`table2`.`column2`),
IFNULL(`column2`,1/0) AS `alias`,
IFNULL(SELECT * FROM x,8) AS `alias`,
IFNULL(SELECT * FROM x,9 ) AS `alias`,
IFNULL(`column1`,`column2`) AS `alias`,
IFNULL(`table1`.`column1`,`table2`.`column2`) AS `alias`,
IFNULL(`table1`.`column1`,`table2`.`column2`),
IFNULL(`table1`.`column1`,`table2`.`column2`) AS `alias` FROM `table1`

func (*Condition) Alias 

func (c *Condition) Alias(a string) *Condition

Alias assigns an alias name to the condition.

func (*Condition) And 

func (c *Condition) And() *Condition

And sets the logical AND operator

func (*Condition) Between 

func (c *Condition) Between() *Condition

func (*Condition) Bool 

func (c *Condition) Bool(b bool) *Condition

func (*Condition) Bools 

func (c *Condition) Bools(b ...bool) *Condition

func (*Condition) Bytes 

func (c *Condition) Bytes(p []byte) *Condition

Bytes uses a byte slice for comparison. Providing a nil value returns a NULL type. Detects between valid UTF-8 strings and binary data. Later gets hex encoded.

func (*Condition) BytesSlice 

func (c *Condition) BytesSlice(p ...[]byte) *Condition

func (*Condition) Clone 

func (c *Condition) Clone() *Condition

Clone creates a new clone of the current object. It resets the internal error field.

func (*Condition) Coalesce 

func (c *Condition) Coalesce() *Condition

func (*Condition) Column 

func (c *Condition) Column(col string) *Condition

Column compares the left hand side with this column name.

func (*Condition) Decimal 

func (c *Condition) Decimal(d null.Decimal) *Condition

func (*Condition) DriverValue 

func (c *Condition) DriverValue(dv ...driver.Valuer) *Condition

DriverValue adds multiple of the same underlying values to the argument slice. When using different values, the last applied value wins and gets added to the argument slice. For example driver.Values of type `int` will result in []int.

func (*Condition) DriverValues 

func (c *Condition) DriverValues(dv ...driver.Valuer) *Condition

DriverValues adds each driver.Value as its own argument to the argument slice. It panics if the underlying type is not one of the allowed of interface driver.Valuer.

func (*Condition) Equal 

func (c *Condition) Equal() *Condition

func (*Condition) Exists 

func (c *Condition) Exists() *Condition

func (*Condition) Expr 

func (c *Condition) Expr(expression string) *Condition

Expr compares the left hand side with the expression of the right hand side.

func (*Condition) Float64 

func (c *Condition) Float64(f float64) *Condition

func (*Condition) Float64s 

func (c *Condition) Float64s(f ...float64) *Condition

func (*Condition) Greater 

func (c *Condition) Greater() *Condition

func (*Condition) GreaterOrEqual 

func (c *Condition) GreaterOrEqual() *Condition

func (*Condition) Greatest 

func (c *Condition) Greatest() *Condition

func (*Condition) In 

func (c *Condition) In() *Condition

func (*Condition) Int 

func (c *Condition) Int(i int) *Condition

func (*Condition) Int64 

func (c *Condition) Int64(i int64) *Condition

func (*Condition) Int64s 

func (c *Condition) Int64s(i ...int64) *Condition

func (*Condition) Ints 

func (c *Condition) Ints(i ...int) *Condition

func (*Condition) Least 

func (c *Condition) Least() *Condition

func (*Condition) Less 

func (c *Condition) Less() *Condition

func (*Condition) LessOrEqual 

func (c *Condition) LessOrEqual() *Condition

func (*Condition) Like 

func (c *Condition) Like() *Condition

func (*Condition) NamedArg 

func (c *Condition) NamedArg(n string) *Condition

NamedArg treats a condition as a place holder. If set the MySQL/MariaDB placeholder `?` will be used and the provided name gets replaced. Records which implement ColumnMapper must also use this name. A dot in the name (for e.g. setting a qualifier) is not allowed.

func (*Condition) NotBetween 

func (c *Condition) NotBetween() *Condition

func (*Condition) NotEqual 

func (c *Condition) NotEqual() *Condition

func (*Condition) NotExists 

func (c *Condition) NotExists() *Condition

func (*Condition) NotIn 

func (c *Condition) NotIn() *Condition

func (*Condition) NotLike 

func (c *Condition) NotLike() *Condition

func (*Condition) NotNull 

func (c *Condition) NotNull() *Condition

func (*Condition) NotRegexp 

func (c *Condition) NotRegexp() *Condition

func (*Condition) Null 

func (c *Condition) Null() *Condition

func (*Condition) NullBool 

func (c *Condition) NullBool(nv null.Bool) *Condition

func (*Condition) NullBools 

func (c *Condition) NullBools(nv ...null.Bool) *Condition

func (*Condition) NullFloat64 

func (c *Condition) NullFloat64(nv null.Float64) *Condition

func (*Condition) NullFloat64s 

func (c *Condition) NullFloat64s(nv ...null.Float64) *Condition

func (*Condition) NullInt64 

func (c *Condition) NullInt64(nv null.Int64) *Condition

func (*Condition) NullInt64s 

func (c *Condition) NullInt64s(nv ...null.Int64) *Condition

func (*Condition) NullString 

func (c *Condition) NullString(nv null.String) *Condition

func (*Condition) NullStrings 

func (c *Condition) NullStrings(nv ...null.String) *Condition

func (*Condition) NullTime 

func (c *Condition) NullTime(nv null.Time) *Condition

func (*Condition) NullTimes 

func (c *Condition) NullTimes(nv ...null.Time) *Condition

func (*Condition) Op 

func (c *Condition) Op(o Op) *Condition

Op sets a custom operator

func (*Condition) Or 

func (c *Condition) Or() *Condition

Or sets the logical OR operator

func (*Condition) PlaceHolder 

func (c *Condition) PlaceHolder() *Condition

PlaceHolder treats a condition as a placeholder. Sets the database specific placeholder character "?". Mostly used in prepared statements and for interpolation.

func (*Condition) PlaceHolders 

func (c *Condition) PlaceHolders(count int) *Condition

PlaceHolders treats a condition as a string with multiple placeholders. Sets the database specific placeholder character "?" as many times as specified in variable count. Mostly used in prepared statements and for interpolation and when using the IN clause.

func (*Condition) Regexp 

func (c *Condition) Regexp() *Condition

func (*Condition) SQLCase 

func (c *Condition) SQLCase(value, defaultValue string, compareResult ...string) *Condition

SQLCase see description at function SQLCase.

func (*Condition) SQLIfNull 

func (c *Condition) SQLIfNull(expression ...string) *Condition

SQLIfNull see description at function SQLIfNull.

func (*Condition) SpaceShip 

func (c *Condition) SpaceShip() *Condition

func (*Condition) Str 

func (c *Condition) Str(s string) *Condition

func (*Condition) Strs 

func (c *Condition) Strs(s ...string) *Condition

func (*Condition) Sub 

func (c *Condition) Sub(sub *Select) *Condition

Sub compares the left hand side with the SELECT of the right hand side. Choose the appropriate comparison operator, default is IN.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	s := dml.NewSelect("sku", "type_id").
		From("catalog_product_entity").
		Where(dml.Column("entity_id").In().Sub(
			dml.NewSelect().From("catalog_category_product").
				AddColumns("entity_id").Where(dml.Column("category_id").Int64(234)),
		))
	writeToSQLAndInterpolate(s)
}

Output:

Statement:
SELECT `sku`, `type_id` FROM `catalog_product_entity` WHERE (`entity_id` IN
(SELECT `entity_id` FROM `catalog_category_product` WHERE (`category_id` =
234)))

func (*Condition) Time 

func (c *Condition) Time(t time.Time) *Condition

func (*Condition) Times 

func (c *Condition) Times(t ...time.Time) *Condition

func (*Condition) Tuples 

func (c *Condition) Tuples() *Condition

Tuples allows to build a query string for tuple comparison. 

SELECT * FROM catalog_product_index_decimal_idx WHERE
(entity_id,attribute_id,store_id,source_id) IN (
	(4,4,4,4), (3,3,3,3), (dynamical values)
);

See test ... TBC

func (*Condition) Uint64 

func (c *Condition) Uint64(i uint64) *Condition

func (*Condition) Uint64s 

func (c *Condition) Uint64s(i ...uint64) *Condition

func (*Condition) Values 

func (c *Condition) Values() *Condition

Values only usable in case for ON DUPLICATE KEY to generate a statement like: 

column=VALUES(column)

func (*Condition) Xor 

func (c *Condition) Xor() *Condition

type Conditions 

type Conditions []*Condition

Conditions provides a list where the left hand side gets an assignment from the right hand side. Mostly used in

func (Conditions) Clone 

func (cs Conditions) Clone() Conditions

Clone creates a clone of the current object.

func (*Conditions) Reset 

func (cs *Conditions) Reset() Conditions

Reset resets the slice to length zero and retains the allocated memory.

type Conn 

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

Conn represents a single database session rather a pool of database sessions. Prefer running queries from DB unless there is a specific need for a continuous single database session.

A Conn must call Close to return the connection to the database pool and may do so concurrently with a running query.

After a call to Close, all operations on the connection fail with ErrConnDone.

func (*Conn) BeginTx 

func (c *Conn) 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 (*Conn) CacheKeyExists 

func (c *Conn) CacheKeyExists(cacheKey string) bool

CacheKeyExists returns true if a given key already exists.

func (*Conn) Close 

func (c *Conn) Close() error

Close returns the connection to the connection pool. All operations after a Close will return with ErrConnDone. Close is safe to call concurrently with other operations and will block until all other operations finish. It may be useful to first cancel any used context and then call close directly after. It logs the time taken, if a logger has been set with Info logging enabled.

func (*Conn) Transaction 

func (c *Conn) Transaction(ctx context.Context, opts *sql.TxOptions, f func(*Tx) error) error

Transaction is a helper method that will automatically BEGIN a transaction and COMMIT or ROLLBACK once the supplied functions are done executing. 

     if err := con.Transaction(
			func(tx *dml.Tx) error {
         	// SQL
		        return nil
     	},
		); err != nil{
          panic(err.Error()) // you could gracefully handle the error also
     }

It logs the time taken, if a logger has been set with Debug logging enabled. The provided context gets used only for starting the transaction.

func (*Conn) WithCacheKey 

func (c *Conn) WithCacheKey(cacheKey string, opts ...DBRFunc) *DBR

WithCacheKey creates a DBR object from a cached query.

func (*Conn) WithPrepare 

func (c *Conn) WithPrepare(ctx context.Context, qb QueryBuilder, opts ...DBRFunc) *DBR

WithPrepare adds the query to the cache and returns a prepared statement which must be closed after its use.

func (*Conn) WithPrepareCacheKey 

func (c *Conn) WithPrepareCacheKey(ctx context.Context, cacheKey string, opts ...DBRFunc) *DBR

WithPrepareCacheKey creates a DBR object from a prepared cached query. The statement must be closed after its use.

func (*Conn) WithQueryBuilder 

func (c *Conn) WithQueryBuilder(qb QueryBuilder, opts ...DBRFunc) *DBR

WithQueryBuilder creates a new DBR for handling the arguments with the assigned connection and builds the SQL string. The returned arguments and errors of the QueryBuilder will be forwarded to the DBR type. It generates a unique cache key based on the SQL string. The cache key can be retrieved via DBR object.

type ConnPool 

type ConnPool struct {

	// DB must be set using one of the ConnPoolOption function.
	DB *sql.DB
	// contains filtered or unexported fields
}

ConnPool at a connection to the database with an EventReceiver to send events, errors, and timings to

func MustConnectAndVerify 

func MustConnectAndVerify(opts ...ConnPoolOption) *ConnPool

MustConnectAndVerify at like NewConnPool but it verifies the connection and panics on errors.

func NewConnPool 

func NewConnPool(opts ...ConnPoolOption) (*ConnPool, error)

NewConnPool instantiates a ConnPool for a given database/sql connection and event receiver. An invalid driver name causes a NotImplemented error to be returned. You can either apply a DSN or a pre configured *sql.DB type. For full UTF-8 support you must set the charset in the SQL driver to utf8mb4.

Quote: http://techblog.en.klab-blogs.com/archives/31093990.html Recommended sql.DB Settings:

Definitely set SetMaxOpenConns(). You need this in order to stop opening new connections and sending queries when the load is high and server response slows. If possible, it’s good to do a load test and set the minimum number of connections to ensure maximum throughput, but even if you can’t do that, you should decide on a reasonably appropriate number based on max_connection and the number of cores.

Configure SetMaxIdleConns() to be equal to or higher than SetMaxOpenConns(). Let SetConnMaxLifetime handle closing idle connections.

Set SetConnMaxLifetime() to be the maximum number of connections x 1 second. In most environments, a load of one connection per second won’t be a problem. When you want to set it for longer than an hour, discuss that with an infrastructure/network engineer.

func (*ConnPool) BeginTx 

func (c *ConnPool) 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.

Practical Guide to SQL Transaction Isolation: https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html

func (*ConnPool) CacheKeyExists 

func (c *ConnPool) CacheKeyExists(cacheKey string) bool

CacheKeyExists returns true if a given key already exists.

func (*ConnPool) CachedQueries 

func (c *ConnPool) CachedQueries() map[string]string

func (*ConnPool) Close 

func (c *ConnPool) Close() (err error)

Close closes the database, releasing any open resources.

It is rare to Close a DB, as the DB handle is meant to be long-lived and shared between many goroutines. It logs the time taken, if a logger has been set with Info logging enabled. It runs the ConnPoolOption, marked for running before close.

func (*ConnPool) Conn 

func (c *ConnPool) Conn(ctx context.Context) (*Conn, error)

Conn returns a single connection by either opening a new connection or returning an existing connection from the connection pool. Conn will block until either a connection is returned or ctx is canceled. Queries run on the same Conn will be run in the same database session.

Every Conn must be returned to the database pool after use by calling Conn.Close.

func (*ConnPool) DSN 

func (c *ConnPool) DSN() string

DSN returns the formatted DSN. Will leak the password.

func (*ConnPool) DeregisterByCacheKey 

func (c *ConnPool) DeregisterByCacheKey(cacheKey string) error

func (*ConnPool) RegisterByQueryBuilder 

func (c *ConnPool) RegisterByQueryBuilder(cacheKeyQB map[string]QueryBuilder) error

RegisterByQueryBuilder adds the SQL queries to the local internal cache. The cacheKeyQB map gets iterated in alphabetical order.

func (*ConnPool) Schema 

func (c *ConnPool) Schema() string

Schema returns the database name as provided in the DSN. Returns an empty string if no DSN has been set.

func (*ConnPool) Transaction 

func (c *ConnPool) Transaction(ctx context.Context, opts *sql.TxOptions, fn func(*Tx) error) (err error)

Transaction is a helper method that will automatically BEGIN a transaction and COMMIT or ROLLBACK once the supplied functions are done executing. 

     if err := con.Transaction(
			func(tx *dml.Tx) error {
         	// SQL
		        return nil
     	}
		); err != nil{
          panic(err.Error()) // you could gracefully handle the error also
     }

It logs the time taken, if a logger has been set with Debug logging enabled. The provided context gets used only for starting the transaction.

func (*ConnPool) WithCacheKey 

func (c *ConnPool) WithCacheKey(cacheKey string, opts ...DBRFunc) *DBR

WithCacheKey creates a DBR object from a cached query.

func (*ConnPool) WithDisabledForeignKeyChecks 

func (c *ConnPool) WithDisabledForeignKeyChecks(ctx context.Context, callBack func(*Conn) error) (err error)

WithDisabledForeignKeyChecks runs the callBack with disabled foreign key checks in a dedicated connection session. Foreign key checks are getting automatically re-enabled. The context is used to disable and enable the FK check.

func (*ConnPool) WithPrepare 

func (c *ConnPool) WithPrepare(ctx context.Context, qb QueryBuilder, opts ...DBRFunc) *DBR

Prepare executes the statement represented by the Select to create a prepared statement. It returns a custom statement type or an error if there was one. Provided arguments or records in the Select are getting ignored. The provided context is used for the preparation of the statement, not for the execution of the statement. The returned DBR is not safe for concurrent use, despite the underlying *sql.Stmt is. It generates a unique cache key based on the SQL string. The cache key can be retrieved via DBR object.

func (*ConnPool) WithPrepareCacheKey 

func (c *ConnPool) WithPrepareCacheKey(ctx context.Context, cacheKey string, opts ...DBRFunc) *DBR

WithPrepareCacheKey creates a DBR object from a prepared cached query.

func (*ConnPool) WithQueryBuilder 

func (c *ConnPool) WithQueryBuilder(qb QueryBuilder, opts ...DBRFunc) *DBR

WithQueryBuilder creates a new DBR for handling the arguments with the assigned connection and builds the SQL string. The returned arguments and errors of the QueryBuilder will be forwarded to the DBR type. It generates a unique cache key based on the SQL string. The cache key can be retrieved via DBR object.

type ConnPoolOption 

type ConnPoolOption struct {

	// WithUniqueIDFn applies a unique ID generator function without an applied
	// logger as in WithLogger. For more details see WithLogger function.
	// Sort Order 8.
	UniqueIDFn func() string
	// TableNameMapper maps the old name in the DML query to a new name. E.g.
	// for adding a prefix and/or a suffix.
	TableNameMapper func(oldName string) (newName string)
	// OptimisticLock is enabled all queries with Exec will have a `version` field.
	// UPDATE user SET ..., version = version + 1 WHERE id = ? AND version = ?
	// TODO implement OptimisticLock
	OptimisticLock bool
	// OptimisticLockFieldName custom global column name, defaults to `version
	// uint64`.
	// TODO implement OptimisticLock
	OptimisticLockColumnName string
	// contains filtered or unexported fields
}

ConnPoolOption can be used at an argument in NewConnPool to configure a connection.

func WithCreateDatabase 

func WithCreateDatabase(ctx context.Context, databaseName string) ConnPoolOption

WithCreateDatabase creates the database and sets the utf8mb4 option. It does not drop the database. If databaseName is empty, the DB name gets derived from the DSN.

func WithDB 

func WithDB(db *sql.DB) ConnPoolOption

WithDB sets the DB value to an existing connection. Mainly used for testing. Does not support DriverCallBack.

func WithDSN 

func WithDSN(dsn string) ConnPoolOption

WithDSN sets the data source name for a connection. Second argument DriverCallBack adds a low level call back function on MySQL driver level to create a a new instrumented driver. No need to call `sql.Register`! If the DSN contains as database name the word "random", then the name will be "test_[unixtimestamp_nano]", especially useful in tests. The environment variable SKIP_CLEANUP=1 skips dropping the test database. 

$ SKIP_CLEANUP=1 go test -v -run=TestX

func WithDSNFromEnv 

func WithDSNFromEnv(dsnEnvName string) ConnPoolOption

WithDSNFromEnv loads the DSN string from an environment variable named by `dsnEnvName`. If `dsnEnvName` is empty, then it falls back to the environment variable name of constant `EnvDSN`.

func WithDriverCallBack 

func WithDriverCallBack(cb DriverCallBack) ConnPoolOption

WithDriverCallBack allows low level query logging and argument inspection.

func WithExecSQLOnConnClose 

func WithExecSQLOnConnClose(ctx context.Context, sqlQuery ...string) ConnPoolOption

WithExecSQLOnConnClose runs the sqlQuery arguments before closing a DB connection. More than one queries are running in a transaction, a single query not.

func WithExecSQLOnConnOpen 

func WithExecSQLOnConnOpen(ctx context.Context, sqlQuery ...string) ConnPoolOption

WithExecSQLOnConnOpen runs the sqlQuery arguments after successful opening a DB connection. More than one queries are running in a transaction, a single query not.

func WithLogger 

func WithLogger(l log.Logger, uniqueIDFn func() string) ConnPoolOption

WithLogger sets the customer logger to be used across the package. The logger gets inherited to type Conn and Tx and also to all statement types. Each heredity creates new fields as a prefix. Argument `uniqueID` generates for each heredity a new unique ID for tracing in Info logging. Those IDs will be assigned to a new connection or a new statement. The function signature is equal to fmt.Stringer so one can use for example: 

uuid.NewV4().String

The returned unique ID from `uniqueIDFn` gets used in logging and inserted as a comment into the SQL string for tracing in server log files and PROCESS LIST. The returned string must not contain the comment-end-termination pattern: `*/`. The `uniqueIDFn` must be thread safe.

func WithSetNamesUTF8MB4 

func WithSetNamesUTF8MB4() ConnPoolOption

WithSetNamesUTF8MB4 sets the utf8mb4 charset and collation.

func WithVerifyConnection 

func WithVerifyConnection(ctx context.Context, pingRetry time.Duration) ConnPoolOption

WithVerifyConnection checks if the connection to the server is valid and can be established.

type DBR 

type DBR struct {

	// DB can be either a *sql.DB (connection pool), a *sql.Conn (a single
	// dedicated database session) or a *sql.Tx (an in-progress database
	// transaction).
	DB QueryExecPreparer

	// Options like enable interpolation or expanding placeholders.
	Options uint

	// ResultCheckFn custom function to check for affected rows or last insert ID.
	// Only used in generated code.
	ResultCheckFn func(tableName string, expectedAffectedRows int, res sql.Result, err error) error

	// QualifiedColumnsAliases allows to overwrite the internal qualified
	// columns slice with custom names. Only in the use case when records are
	// applied. The list of column names in `QualifiedColumnsAliases` gets
	// passed to the ColumnMapper and back to the provided object. The
	// `QualifiedColumnsAliases` slice must have the same length as the
	// qualified columns slice. The order of the alias names must be in the same
	// order as the qualified columns or as the placeholders occur.
	QualifiedColumnsAliases []string
	OrderBys                ids
	LimitValid              bool
	OffsetValid             bool
	LimitCount              uint64
	OffsetCount             uint64
	// contains filtered or unexported fields
}

DBR is a DataBaseRunner which prepares the SQL string from a DML type, collects and build a list of arguments for later sending and execution in the database server. Arguments are collections of primitive types or slices of primitive types. An DBR type acts like a prepared statement. In fact it can contain under the hood different connection types. DBR is optimized for reuse and allow saving memory allocations. It can't be used in concurrent context.

func (*DBR) CacheKey 

func (bc *DBR) CacheKey() string

CacheKey returns the cache key used when registering a SQL statement with the ConnPool.

func (*DBR) Close 

func (a *DBR) Close() error

Close tries to close the underlying DB connection. Useful in cases of prepared statements. If the underlying DB connection does not implement io.Closer, nothing will happen.

func (*DBR) ExecContext 

func (a *DBR) ExecContext(ctx context.Context, args ...any) (sql.Result, error)

ExecContext executes the statement represented by the Update/Insert object. It returns the raw database/sql Result or an error if there was one. Regarding LastInsertID(): If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this at to make it possible to reproduce easily the same INSERT statement against some other server. If a record resp. and object implements the interface LastInsertIDAssigner then the LastInsertID gets assigned incrementally to the objects. Pro tip: you can use function ExecValidateOneAffectedRow to check if the underlying SQL statement has affected only one row.

func (*DBR) ExpandPlaceHolders 

func (a *DBR) ExpandPlaceHolders() *DBR

ExpandPlaceHolders repeats the place holders with the provided argument count. If the amount of arguments does not match the number of place holders, a mismatch error gets returned. 

ExpandPlaceHolders("SELECT * FROM table WHERE id IN (?) AND status IN (?)", Int(myIntSlice...), String(myStrSlice...))

Gets converted to: 

SELECT * FROM table WHERE id IN (?,?) AND status IN (?,?,?)

The place holders are of course depending on the values in the Arg* functions. This function should be generally used when dealing with prepared statements or interpolation.

func (*DBR) Interpolate 

func (a *DBR) Interpolate() *DBR

Interpolate if set stringyfies the arguments into the SQL string and returns pre-processed SQL command when calling the function ToSQL. Not suitable for prepared statements. ToSQLs second argument `args` will then be nil.

func (*DBR) IterateParallel 

func (a *DBR) IterateParallel(ctx context.Context, concurrencyLevel int, callBack func(*ColumnMap) error, args ...any) (err error)

IterateParallel starts a number of workers as defined by variable concurrencyLevel and executes the query. Each database row gets evenly distributed to the workers. The callback function gets called within a worker. concurrencyLevel should be the number of CPUs. You should use this function when you expect to process large amount of rows returned from a query.

func (*DBR) IterateSerial 

func (a *DBR) IterateSerial(ctx context.Context, callBack func(*ColumnMap) error, args ...any) (err error)

IterateSerial iterates in serial order over the result set by loading one row each iteration and then discarding it. Handles records one by one. The context gets only used in the Query function.

func (*DBR) Limit 

func (a *DBR) Limit(offset, limit uint64) *DBR

Limit sets a LIMIT clause for the statement; overrides any existing LIMIT. This LIMIT clause gets appended to the current internal cached SQL string independently if the SQL statement supports it or not or if there exists already a LIMIT clause.

func (*DBR) Load 

func (a *DBR) Load(ctx context.Context, s ColumnMapper, args ...any) (rowCount uint64, err error)

Load loads data from a query into an object. Load can load a single row or multiple-rows. It checks on top if ColumnMapper `s` implements io.Closer, to call the custom close function. This is useful for e.g. unlocking a mutex.

func (*DBR) LoadDecimal 

func (a *DBR) LoadDecimal(ctx context.Context, args ...any) (nv null.Decimal, found bool, err error)

LoadDecimal executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows.

func (*DBR) LoadFloat64s 

func (a *DBR) LoadFloat64s(ctx context.Context, dest []float64, args ...any) (_ []float64, err error)

LoadFloat64s executes the query and returns the values appended to slice dest. It ignores and skips NULL values.

func (*DBR) LoadInt64s 

func (a *DBR) LoadInt64s(ctx context.Context, dest []int64, args ...any) (_ []int64, err error)

LoadInt64s executes the query and returns the values appended to slice dest. It ignores and skips NULL values.

func (*DBR) LoadNullFloat64 

func (a *DBR) LoadNullFloat64(ctx context.Context, args ...any) (nv null.Float64, found bool, err error)

LoadNullFloat64 executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows.

func (*DBR) LoadNullInt64 

func (a *DBR) LoadNullInt64(ctx context.Context, args ...any) (nv null.Int64, found bool, err error)

LoadNullInt64 executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows.

func (*DBR) LoadNullString 

func (a *DBR) LoadNullString(ctx context.Context, args ...any) (nv null.String, found bool, err error)

LoadNullString executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows.

func (*DBR) LoadNullTime 

func (a *DBR) LoadNullTime(ctx context.Context, args ...any) (nv null.Time, found bool, err error)

LoadNullTime executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows.

func (*DBR) LoadNullUint64 

func (a *DBR) LoadNullUint64(ctx context.Context, args ...any) (nv null.Uint64, found bool, err error)

LoadNullUint64 executes the query and returns the first row parsed into the current type. `Found` might be false if there are no matching rows. This function with ptr type uint64 comes in handy when performing a COUNT(*) query. See function `Select.Count`.

func (*DBR) LoadStrings 

func (a *DBR) LoadStrings(ctx context.Context, dest []string, args ...any) (_ []string, err error)

LoadStrings executes the query and returns the values appended to slice dest. It ignores and skips NULL values.

func (*DBR) LoadUint64s 

func (a *DBR) LoadUint64s(ctx context.Context, dest []uint64, args ...any) (_ []uint64, err error)

LoadUint64s executes the query and returns the values appended to slice dest. It ignores and skips NULL values.

func (*DBR) OrderBy 

func (a *DBR) OrderBy(columns ...string) *DBR

OrderBy appends columns to the ORDER BY statement for ascending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. This ORDER BY clause gets appended to the current internal cached SQL string independently if the SQL statement supports it or not or if there exists already an ORDER BY clause. A column name can also contain the suffix words " ASC" or " DESC" to indicate the sorting. This avoids using the method OrderByDesc when sorting certain columns descending.

func (*DBR) OrderByDesc 

func (a *DBR) OrderByDesc(columns ...string) *DBR

OrderByDesc appends columns to the ORDER BY statement for descending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. This ORDER BY clause gets appended to the current internal cached SQL string independently if the SQL statement supports it or not or if there exists already an ORDER BY clause.

func (*DBR) Paginate 

func (a *DBR) Paginate(page, perPage uint64) *DBR

Paginate sets LIMIT/OFFSET for the statement based on the given page/perPage Assumes page/perPage are valid. Page and perPage must be >= 1

func (*DBR) Prepare 

func (a *DBR) Prepare(ctx context.Context) (*DBR, error)

Prepare generates a prepared statement from the underlying SQL and assigns the *sql.Stmt to the DB field. It fails if it contains an already prepared statement.

func (*DBR) PreviousError 

func (a *DBR) PreviousError() error

PreviousError returns the previous error. Mostly used for testing.

func (*DBR) QueryContext 

func (a *DBR) QueryContext(ctx context.Context, args ...any) (*sql.Rows, error)

QueryContext traditional way of the databasel/sql package.

func (*DBR) QueryRowContext 

func (a *DBR) QueryRowContext(ctx context.Context, args ...any) *sql.Row

QueryRowContext traditional way of the databasel/sql package.

func (*DBR) Reset 

func (a *DBR) Reset() *DBR

Reset resets the internal slices for new usage retaining the already allocated memory. Reset gets called automatically in many Load* functions. In case of an INSERT statement, Reset triggers a new build of the VALUES part. This function must be called when the number of argument changes for an INSERT query.

func (*DBR) TestWithArgs 

func (a *DBR) TestWithArgs(args ...any) QueryBuilder

TestWithArgs returns a QueryBuilder with resolved arguments. Mostly used for testing and in examples to skip the calls to ExecContext or QueryContext. Every 2nd call arguments are getting interpolated.

func (*DBR) ToSQL 

func (a *DBR) ToSQL() (string, []any, error)

ToSQL generates the SQL string.

func (*DBR) TupleCount 

func (a *DBR) TupleCount(tuples, rows uint) *DBR

TupleCount sets the amount of tuples and its rows. Only needed in case of a prepared statement with tuples. WHERE clause contains: 

dml.Columns("entity_id", "attribute_id", "store_id", "source_id").In().Tuples(),

and set to 4,2 because 4 columns with two rows = 8 arguments. 

TupleCount(4,2)

results into 

WHERE ((`entity_id`, `attribute_id`, `store_id`, `source_id`) IN ((?,?,?,?),(?,?,?,?)))

func (*DBR) WithCacheKey 

func (bc *DBR) WithCacheKey(cacheKey string) *DBR

WithCacheKey allows to set a custom cache key in generated code to change the underlying SQL query.

func (*DBR) WithDB 

func (a *DBR) WithDB(db QueryExecPreparer) *DBR

WithDB sets the database query object.

func (*DBR) WithPreparedStmt 

func (a *DBR) WithPreparedStmt(stmt *sql.Stmt) *DBR

WithPreparedStmt uses a SQL statement as DB connection.

func (*DBR) WithQualifiedColumnsAliases 

func (a *DBR) WithQualifiedColumnsAliases(aliases ...string) *DBR

WithQualifiedColumnsAliases for documentation please see: DBR.QualifiedColumnsAliases.

func (*DBR) WithTx 

func (a *DBR) WithTx(tx *Tx) *DBR

WithTx sets the transaction query executor and the logger to run this query within a transaction.

type DBRFunc 

type DBRFunc func(*DBR)

DBRFunc defines a call back function used in other packages to allow modifications to the DBR object.

func DBRValidateMinAffectedRow 

func DBRValidateMinAffectedRow(minExpectedRows int64) DBRFunc

DBRValidateMinAffectedRow is an option argument to provide a basic helper function to check that at least one row has been deleted.

func DBRWithTx 

func DBRWithTx(tx *Tx, opts []DBRFunc) []DBRFunc

type Delete 

type Delete struct {
	BuilderBase
	BuilderConditional
	// MultiTables specifies the additional tables to delete from. Use function
	// `FromTables` to conveniently set it.
	MultiTables ids
	// Returning allows from MariaDB 10.0.5, it is possible to return a
	// resultset of the deleted rows for a single table to the client by using
	// the syntax DELETE ... RETURNING select_expr [, select_expr2 ...]] Any of
	// SQL expression that can be calculated from a single row fields is
	// allowed. Subqueries are allowed. The AS keyword is allowed, so it is
	// possible to use aliases. The use of aggregate functions is not allowed.
	// RETURNING cannot be used in multi-table DELETEs.
	Returning *Select
}

Delete contains the clauses for a DELETE statement.

InnoDB Tables: If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

Select the rows not to be deleted into an empty table that has the same structure as the original table: 

INSERT INTO t_copy SELECT * FROM t WHERE ... ;

Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name: 

RENAME TABLE t TO t_old, t_copy TO t;

Drop the original table: 

DROP TABLE t_old;

No other sessions can access the tables involved while RENAME TABLE executes, so the rename operation is not subject to concurrency problems.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	d := dml.NewDelete("tableA").Where(
		dml.Column("a").Like().Str("b'%"),
		dml.Column("b").In().Ints(3, 4, 5, 6),
	).
		Limit(1).OrderBy("id")
	writeToSQLAndInterpolate(d)
}

Output:

Statement:
DELETE FROM `tableA` WHERE (`a` LIKE 'b\'%') AND (`b` IN (3,4,5,6)) ORDER BY
`id` LIMIT 1

func NewDelete 

func NewDelete(from string) *Delete

NewDelete creates a new Delete object.

func (*Delete) Alias 

func (b *Delete) Alias(alias string) *Delete

Alias sets an alias for the table name.

func (*Delete) Clone 

func (b *Delete) Clone() *Delete

Clone creates a clone of the current object, leaving fields DB and Log untouched.

func (*Delete) CrossJoin 

func (b *Delete) CrossJoin(table id, onConditions ...*Condition) *Delete

CrossJoin creates a CROSS join construct. By default, the onConditions are glued together with AND.

func (*Delete) FromTables 

func (b *Delete) FromTables(tables ...string) *Delete

FromTables specifies additional tables to delete from besides the default table.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	d := dml.NewDelete("customer_entity").Alias("ce").
		FromTables("customer_address", "customer_company").
		Join(
			dml.MakeIdentifier("customer_company").Alias("cc"),
			dml.Columns("ce.entity_id", "cc.customer_id"),
		).
		RightJoin(
			dml.MakeIdentifier("customer_address").Alias("ca"),
			dml.Column("ce.entity_id").Equal().Column("ca.parent_id"),
		).
		Where(
			dml.Column("ce.created_at").Less().PlaceHolder(),
		).
		Limit(1).OrderBy("id")
	writeToSQLAndInterpolate(d)
}

Output:

Statement:
DELETE `ce`,`customer_address`,`customer_company` FROM `customer_entity` AS `ce`
INNER JOIN `customer_company` AS `cc` USING (`ce.entity_id`,`cc.customer_id`)
RIGHT JOIN `customer_address` AS `ca` ON (`ce`.`entity_id` = `ca`.`parent_id`)
WHERE (`ce`.`created_at` < ?) ORDER BY `id` LIMIT 1

func (*Delete) Join 

func (b *Delete) Join(table id, onConditions ...*Condition) *Delete

Join creates an INNER join construct. By default, the onConditions are glued together with AND. Same Source and Target Table: Until MariaDB 10.3.1, deleting from a table with the same source and target was not possible. From MariaDB 10.3.1, this is now possible. For example: 

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

func (*Delete) LeftJoin 

func (b *Delete) LeftJoin(table id, onConditions ...*Condition) *Delete

LeftJoin creates a LEFT join construct. By default, the onConditions are glued together with AND.

func (*Delete) Limit 

func (b *Delete) Limit(limit uint64) *Delete

Limit sets a LIMIT clause for the statement; overrides any existing LIMIT

func (*Delete) OrderBy 

func (b *Delete) OrderBy(columns ...string) *Delete

OrderBy appends columns to the ORDER BY statement for ascending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a DELETE, the server sorts arguments using only the initial number of bytes indicated by the max_sort_length system variable. A column name can also contain the suffix words " ASC" or " DESC" to indicate the sorting. This avoids using the method OrderByDesc when sorting certain columns descending.

func (*Delete) OrderByDesc 

func (b *Delete) OrderByDesc(columns ...string) *Delete

OrderByDesc appends columns to the ORDER BY statement for descending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a DELETE, the server sorts arguments using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Delete) OuterJoin 

func (b *Delete) OuterJoin(table id, onConditions ...*Condition) *Delete

OuterJoin creates an OUTER join construct. By default, the onConditions are glued together with AND.

func (*Delete) RightJoin 

func (b *Delete) RightJoin(table id, onConditions ...*Condition) *Delete

RightJoin creates a RIGHT join construct. By default, the onConditions are glued together with AND.

func (*Delete) String 

func (b *Delete) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Delete) ToSQL 

func (b *Delete) ToSQL() (string, []any, error)

ToSQL generates the SQL string and might caches it internally, if not disabled. The returned interface slice is always nil.

func (*Delete) Unsafe 

func (b *Delete) Unsafe() *Delete

Unsafe see BuilderBase.IsUnsafe which weakens security when building the SQL string. This function must be called before calling any other function.

func (*Delete) Where 

func (b *Delete) Where(wf ...*Condition) *Delete

Where appends a WHERE clause to the statement whereSQLOrMap can be a string or map. If it'ab a string, args wil replaces any places holders.

func (*Delete) WithDBR 

func (b *Delete) WithDBR(db QueryExecPreparer) *DBR

type DriverCallBack 

type DriverCallBack func(fnName string) func(err error, query string, args []driver.NamedValue) error

DriverCallBack defines the call back signature used in every driver function. The returned function gets called in a defer. `fnName` states the name of the parent function like PrepareContext or Query, etc. The call to the first function can be used to e.g. start a timer. The call to second function can log the query and its args and also measure the time spend. The error as first argument in the returned function comes from the parent called function and should be returned or wrapped into a new one. `namedArgs` contains the, sometimes, named arguments. It can also be nil. context.Context can be added later.

type Error 

type Error struct {
	Err     error
	Message string
	Marker  string
	Query   string
}

func (*Error) Error 

func (e *Error) Error() string

func (*Error) Unwrap 

func (e *Error) Unwrap() error

type EventFlag 

type EventFlag uint8

EventFlag describes where and when an event might get dispatched. 

const (
	EventFlagUndefined EventFlag = iota
	EventFlagBeforeSelect
	EventFlagAfterSelect
	EventFlagBeforeInsert
	EventFlagAfterInsert
	EventFlagBeforeUpdate
	EventFlagAfterUpdate
	EventFlagBeforeUpsert
	EventFlagAfterUpsert
	EventFlagBeforeDelete
	EventFlagAfterDelete
	EventFlagMax // indicates maximum events available. Might change without notice.
)

EventFlag constants define the concrete locations of dispatched events.

type Execer 

type Execer interface {
	// ExecContext executes a query that doesn't return rows.
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

Execer can execute a non-returning query. The underlying type can be either a *sql.DB (connection pool), a *sql.Conn (a single dedicated database session) or a *sql.Tx (an in-progress database transaction).

type Insert 

type Insert struct {
	BuilderBase
	Into    string
	Columns []string
	// RowCount defines the number of expected rows.
	RowCount int // See SetRowCount()
	// RecordPlaceHolderCount defines the number of place holders for each set
	// within the brackets. Must only be set when Records have been applied
	// and `Columns` field has been omitted.
	RecordPlaceHolderCount int
	// Select used to create an "INSERT INTO `table` SELECT ..." statement.
	Select *Select
	Pairs  Conditions
	// OnDuplicateKeys updates the referenced columns. See documentation for
	// type `Conditions`. For more details
	// https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
	// Conditions contains the column/argument association for either the SET
	// clause in an UPDATE statement or to be used in an INSERT ... ON DUPLICATE
	// KEY statement. For each column there must be one argument which can
	// either be nil or has an actual value.
	//
	// When using the ON DUPLICATE KEY feature in the Insert builder:
	//
	// The function dml.ExpressionValue is supported and allows SQL
	// constructs like (ib == InsertBuilder builds INSERT statements):
	// 		`columnA`=VALUES(`columnB`)+2
	// by writing the Go code:
	//		ib.AddOnDuplicateKey("columnA", ExpressionValue("VALUES(`columnB`)+?", Int(2)))
	// Omitting the argument and using the keyword nil will turn this Go code:
	//		ib.AddOnDuplicateKey("columnA", nil)
	// into that SQL:
	// 		`columnA`=VALUES(`columnA`)
	// Same applies as when the columns gets only assigned without any arguments:
	//		ib.OnDuplicateKeys.Columns = []string{"name","sku"}
	// will turn into:
	// 		`name`=VALUES(`name`), `sku`=VALUES(`sku`)
	// Type `Conditions` gets used in type `Update` with field
	// `SetClauses` and in type `Insert` with field OnDuplicateKeys.
	OnDuplicateKeys Conditions
	// OnDuplicateKeyExclude excludes the mentioned columns to the ON DUPLICATE
	// KEY UPDATE section. Otherwise all columns in the field `Columns` will be
	// added to the ON DUPLICATE KEY UPDATE expression. Usually the slice
	// `OnDuplicateKeyExclude` contains the primary key columns. Case-sensitive
	// comparison.
	OnDuplicateKeyExclude []string
	// IsOnDuplicateKey if enabled adds all columns to the ON DUPLICATE KEY
	// claus. Takes the OnDuplicateKeyExclude field into consideration.
	IsOnDuplicateKey bool
	// IsReplace uses the REPLACE syntax. See function Replace().
	IsReplace bool
	// IsIgnore ignores error. See function Ignore().
	IsIgnore bool
	// IsBuildValues if true the VALUES part gets build when calling ToSQL.
	// VALUES do not need to get build by default because mostly WithDBR gets
	// called to build the VALUES part dynamically.
	IsBuildValues bool
}

Insert contains the clauses for an INSERT statement

Example (ExpressionInVALUES)

ExampleInsert_expressionInVALUES contains an expression in the VALUES part. You must provide the column names. 

package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	ins := dml.NewInsert("catalog_product_customer_relation").
		AddColumns("product_id", "sort_order").
		WithPairs(
			dml.Column("customer_id").Expr("IFNULL(SELECT entity_id FROM customer_entity WHERE email like ?,0)"),
			dml.Column("group_id").Sub(
				dml.NewSelect("group_id").From("customer_group").Where(
					dml.Column("name").Equal().PlaceHolder(),
				),
			),
		).BuildValues()
	writeToSQLAndInterpolate(ins)
}

Output:

Statement:
INSERT INTO `catalog_product_customer_relation`
(`product_id`,`sort_order`,`customer_id`,`group_id`) VALUES (?,?,IFNULL(SELECT
entity_id FROM customer_entity WHERE email like ?,0),(SELECT `group_id` FROM
`customer_group` WHERE (`name` = ?)))

func NewInsert 

func NewInsert(into string) *Insert

NewInsert creates a new Insert object.

Example 
i := dml.NewInsert("tableA").
	AddColumns("b", "c", "d", "e").SetRowCount(2).WithDBR(dbMock{}).TestWithArgs(
	1, 2, "Three", nil,
	5, 6, "Seven", 3.14156,
)

writeToSQLAndInterpolate(i)

Output:

Prepared Statement:
INSERT INTO `tableA` (`b`,`c`,`d`,`e`) VALUES (?,?,?,?),(?,?,?,?)
Arguments: [1 2 Three <nil> 5 6 Seven 3.14156]

Interpolated Statement:
INSERT INTO `tableA` (`b`,`c`,`d`,`e`) VALUES
(1,2,'Three',NULL),(5,6,'Seven',3.14156)

func (*Insert) AddColumns 

func (b *Insert) AddColumns(columns ...string) *Insert

AddColumns appends columns and increases the `RecordPlaceHolderCount` variable.

func (*Insert) AddOnDuplicateKey 

func (b *Insert) AddOnDuplicateKey(c ...*Condition) *Insert

AddOnDuplicateKey has some hidden features for best flexibility. You can only set the Columns itself to allow the following SQL construct: 

`columnA`=VALUES(`columnA`)

Means columnA gets automatically mapped to the VALUES column name.

Example

ExampleInsert_AddOnDuplicateKey this example assumes you are not using a any place holders. Be aware of SQL injections. 

i := dml.NewInsert("dml_people").
	AddColumns("id", "name", "email").
	AddOnDuplicateKey(
		dml.Column("name").Str("Pik3"),
		dml.Column("email").Values(),
	).WithDBR(dbMock{}).TestWithArgs(1, "Pik'e", "pikes@peak.com")
writeToSQLAndInterpolate(i)

Output:

Prepared Statement:
INSERT INTO `dml_people` (`id`,`name`,`email`) VALUES (?,?,?) ON DUPLICATE KEY
UPDATE `name`='Pik3', `email`=VALUES(`email`)
Arguments: [1 Pik'e pikes@peak.com]

Interpolated Statement:
INSERT INTO `dml_people` (`id`,`name`,`email`) VALUES
(1,'Pik\'e','pikes@peak.com') ON DUPLICATE KEY UPDATE `name`='Pik3',
`email`=VALUES(`email`)

func (*Insert) AddOnDuplicateKeyExclude 

func (b *Insert) AddOnDuplicateKeyExclude(columns ...string) *Insert

AddOnDuplicateKeyExclude adds a column to the exclude list. As soon as a column gets set with this function the ON DUPLICATE KEY clause gets generated. Usually the slice `OnDuplicateKeyExclude` contains the primary/unique key columns. Case-sensitive comparison.

func (*Insert) BuildValues 

func (b *Insert) BuildValues() *Insert

BuildValues see IsBuildValues.

Example

ExampleInsert_BuildValues does not call WithDBR but call to BuildValues must be made to enable building the VALUES part. 

package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	ins := dml.NewInsert("catalog_product_link").
		WithPairs(
			// First row
			dml.Column("product_id").Int64(2046),
			dml.Column("linked_product_id").Int64(33),
			dml.Column("link_type_id").Int64(3),

			// second row
			dml.Column("product_id").Int64(2046),
			dml.Column("linked_product_id").Int64(34),
			dml.Column("link_type_id").Int64(3),
		).BuildValues()
	writeToSQLAndInterpolate(ins)
}

Output:

Statement:
INSERT INTO `catalog_product_link`
(`product_id`,`linked_product_id`,`link_type_id`) VALUES (2046,33,3),(2046,34,3)

func (*Insert) Clone 

func (b *Insert) Clone() *Insert

Clone creates a clone of the current object, leaving fields DB and Log untouched.

func (*Insert) FromSelect 

func (b *Insert) FromSelect(s *Select) *Insert

FromSelect creates an "INSERT INTO `table` SELECT ..." statement from a previously created SELECT statement.

Example (WithPlaceHolders) 
ins := dml.NewInsert("tableA").FromSelect(
	dml.NewSelect().AddColumns("something_id", "user_id").
		AddColumns("other").
		From("some_table").
		Where(
			dml.ParenthesisOpen(),
			dml.Column("int64A").GreaterOrEqual().PlaceHolder(),
			dml.Column("string").Str("wat").Or(),
			dml.ParenthesisClose(),
			dml.Column("int64B").In().NamedArg("i64BIn"),
		).
		OrderByDesc("id").
		Paginate(1, 20),
).WithDBR(dbMock{}).TestWithArgs(4, sql.Named("i64BIn", []int64{9, 8, 7}))
writeToSQLAndInterpolate(ins)

Output:

Prepared Statement:
INSERT INTO `tableA` SELECT `something_id`, `user_id`, `other` FROM `some_table`
WHERE ((`int64A` >= ?) OR (`string` = 'wat')) AND (`int64B` IN ?) ORDER BY `id`
DESC LIMIT 0,20
Arguments: [4 9 8 7]

Interpolated Statement:
INSERT INTO `tableA` SELECT `something_id`, `user_id`, `other` FROM `some_table`
WHERE ((`int64A` >= 4) OR (`string` = 'wat')) AND (`int64B` IN (9,8,7)) ORDER BY
`id` DESC LIMIT 0,20
Example (WithoutPlaceHolders) 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	ins := dml.NewInsert("tableA")

	ins.FromSelect(
		dml.NewSelect().AddColumns("something_id", "user_id").
			AddColumns("other").
			From("some_table").
			Where(
				dml.ParenthesisOpen(),
				dml.Column("int64A").GreaterOrEqual().Int64(1),
				dml.Column("string").Str("wat").Or(),
				dml.ParenthesisClose(),
				dml.Column("int64B").In().Int64s(1, 2, 3),
			).
			OrderByDesc("id").
			Paginate(1, 20),
	)
	writeToSQLAndInterpolate(ins)
}

Output:

Statement:
INSERT INTO `tableA` SELECT `something_id`, `user_id`, `other` FROM `some_table`
WHERE ((`int64A` >= 1) OR (`string` = 'wat')) AND (`int64B` IN (1,2,3)) ORDER BY
`id` DESC LIMIT 0,20

func (*Insert) Ignore 

func (b *Insert) Ignore() *Insert

Ignore modifier enables errors that occur while executing the INSERT statement are getting ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead. https://dev.mysql.com/doc/refman/5.7/en/insert.html

func (*Insert) OnDuplicateKey 

func (b *Insert) OnDuplicateKey() *Insert

OnDuplicateKey enables for all columns to be written into the ON DUPLICATE KEY claus. Takes the field OnDuplicateKeyExclude into consideration.

func (*Insert) Replace 

func (b *Insert) Replace() *Insert

Replace instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded. https://dev.mysql.com/doc/refman/5.7/en/replace.html

func (*Insert) SetRecordPlaceHolderCount 

func (b *Insert) SetRecordPlaceHolderCount(valueCount int) *Insert

SetRecordPlaceHolderCount number of expected place holders within each set. Only needed in case the SQL string gets build without any arguments. 

INSERT INTO tableX (?,?,?)

SetRecordPlaceHolderCount would now be 3 because of the three place holders.

func (*Insert) SetRowCount 

func (b *Insert) SetRowCount(rows int) *Insert

SetRowCount defines the number of expected rows. Each set of place holders within the brackets defines a row. This setting defaults to one. It gets applied when fields `args` and `Records` have been left empty. For each defined column the QueryBuilder creates a place holder. Use when creating a prepared statement. See the example for more details. 

RowCount = 2 ==> (?,?,?),(?,?,?)
RowCount = 3 ==> (?,?,?),(?,?,?),(?,?,?)
Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	// RowCount of 4 allows to insert four rows with a single INSERT query.
	// Useful when creating prepared statements.
	i := dml.NewInsert("dml_people").AddColumns("id", "name", "email").SetRowCount(4).BuildValues()
	writeToSQLAndInterpolate(i)

}

Output:

Statement:
INSERT INTO `dml_people` (`id`,`name`,`email`) VALUES
(?,?,?),(?,?,?),(?,?,?),(?,?,?)
Example (Withdata) 
i := dml.NewInsert("catalog_product_link").SetRowCount(3).WithDBR(dbMock{}).TestWithArgs(
	2046, 33, 3,
	2046, 34, 3,
	2046, 35, 3,
)
writeToSQLAndInterpolate(i)

Output:

Prepared Statement:
INSERT INTO `catalog_product_link` VALUES (?,?,?),(?,?,?),(?,?,?)
Arguments: [2046 33 3 2046 34 3 2046 35 3]

Interpolated Statement:
INSERT INTO `catalog_product_link` VALUES (2046,33,3),(2046,34,3),(2046,35,3)

func (*Insert) String 

func (b *Insert) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Insert) ToSQL 

func (b *Insert) ToSQL() (string, []any, error)

ToSQL serialized the Insert to a SQL string It returns the string with placeholders and a slice of query arguments.

func (*Insert) WithDBR 

func (b *Insert) WithDBR(db QueryExecPreparer) *DBR

func (*Insert) WithPairs 

func (b *Insert) WithPairs(cvs ...*Condition) *Insert

WithPairs appends a column/value pair to the statement. Calling this function multiple times with the same column name will trigger an error. Slice values and right/left side expressions are not supported and ignored. You must call WithDBR afterwards to activate the `Pairs`.

Example

ExampleInsert_WithPairs this example uses WithDBR to build the final SQL string. 

ins := dml.NewInsert("catalog_product_link").AddColumns("product_id", "linked_product_id", "link_type_id").
	WithDBR(dbMock{}).TestWithArgs([]sql.NamedArg{
	{Name: "product_id", Value: 2046},
	{Name: "linked_product_id", Value: 33},
	{Name: "link_type_id", Value: 3},

	{Name: "product_id", Value: 2046},
	{Name: "linked_product_id", Value: 34},
	{Name: "link_type_id", Value: 3},
})
writeToSQLAndInterpolate(ins)

Output:

Prepared Statement:
INSERT INTO `catalog_product_link`
(`product_id`,`linked_product_id`,`link_type_id`) VALUES (?,?,?),(?,?,?)
Arguments: [2046 33 3 2046 34 3]

Interpolated Statement:
INSERT INTO `catalog_product_link`
(`product_id`,`linked_product_id`,`link_type_id`) VALUES (2046,33,3),(2046,34,3)

type Joins 

type Joins []*join

Joins defines multiple join conditions.

func (Joins) Clone 

func (js Joins) Clone() Joins

Clone creates a new clone of the current object.

type LastInsertIDAssigner 

type LastInsertIDAssigner interface {
	AssignLastInsertID(int64)
}

LastInsertIDAssigner assigns the last insert ID of an auto increment column back to the objects.

type MysqlQuoter 

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

MysqlQuoter implements Mysql-specific quoting

func (MysqlQuoter) ColumnsWithQualifier 

func (mq MysqlQuoter) ColumnsWithQualifier(t string, cols ...string) []string

ColumnsWithQualifier prefixes all columns in the slice `cols` with a qualifier and applies backticks. If a column name has already been prefixed with a qualifier or an alias it will be ignored. This functions modifies the argument slice `cols`.

func (MysqlQuoter) Name 

func (mq MysqlQuoter) Name(n string) string

Name quotes securely a name. 

Name("tableName") => `tableName`
Name("table`Name") => `tableName`

https://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html

func (MysqlQuoter) NameAlias 

func (mq MysqlQuoter) NameAlias(name, alias string) string

NameAlias quotes with back ticks and splits at a dot into the qualified or unqualified identifier. First argument table and/or column name (separated by a dot) and second argument can be an alias. Both parts will get quoted. 

NameAlias("f", "g") 			// "`f` AS `g`"
NameAlias("e.entity_id", "ee") 	// `e`.`entity_id` AS `ee`
NameAlias("e.entity_id", "") 	// `e`.`entity_id`

func (MysqlQuoter) QualifierName 

func (mq MysqlQuoter) QualifierName(q, n string) string

QualifierName quotes securely a qualifier and its name. 

QualifierName("dbName", "tableName") => `dbName`.`tableName`
QualifierName("db`Name", "`tableName`") => `dbName`.`tableName`

https://dev.mysql.com/doc/refman/5.7/en/identifier-qualifiers.html

func (MysqlQuoter) WriteIdentifier 

func (mq MysqlQuoter) WriteIdentifier(w *bytes.Buffer, name string)

WriteIdentifier quotes with back ticks and splits at a dot into the qualified or unqualified identifier. First argument table and/or column name (separated by a dot). It quotes always and each part. If a string contains quotes, they won't get stripped. 

WriteIdentifier(&buf,"tableName.ColumnName") -> `tableName`.`ColumnName`

func (MysqlQuoter) WriteQualifierName 

func (mq MysqlQuoter) WriteQualifierName(w *bytes.Buffer, qualifier, name string)

WriteQualifierName same as function QualifierName but writes into w.

type Op 

type Op rune

Op the Operator, defines comparison and operator functions used in any conditions. The upper case letter always negates. https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html https://mariadb.com/kb/en/mariadb/comparison-operators/ 

const (
	Null           Op = 'n'          // IS NULL
	NotNull        Op = 'N'          // IS NOT NULL
	In             Op = '∈'          // IN ?
	NotIn          Op = '∉'          // NOT IN ?
	Between        Op = 'b'          // BETWEEN ? AND ?
	NotBetween     Op = 'B'          // NOT BETWEEN ? AND ?
	Like           Op = 'l'          // LIKE ?
	NotLike        Op = 'L'          // NOT LIKE ?
	Greatest       Op = '≫'          // GREATEST(?,?,?) returns NULL if any value is NULL.
	Least          Op = '≪'          // LEAST(?,?,?) If any value is NULL, the result is NULL.
	Equal          Op = '='          // = ?
	NotEqual       Op = '≠'          // != ?
	Exists         Op = '∃'          // EXISTS(subquery)
	NotExists      Op = '∄'          // NOT EXISTS(subquery)
	Less           Op = '<'          // <
	Greater        Op = '>'          // >
	LessOrEqual    Op = '≤'          // <=
	GreaterOrEqual Op = '≥'          // >=
	Regexp         Op = 'r'          // REGEXP ?
	NotRegexp      Op = 'R'          // NOT REGEXP ?
	Xor            Op = '⊻'          // XOR ?
	SpaceShip      Op = '\U0001f680' // a <=> b is equivalent to a = b OR (a IS NULL AND b IS NULL) NULL-safe equal to operator
	Coalesce       Op = 'c'          // Returns the first non-NULL value in the list, or NULL if there are no non-NULL arguments.
)

Comparison functions and operators describe all available possibilities.

func (Op) String 

func (o Op) String() string

String transforms the rune into a string.

type Preparer 

type Preparer interface {
	// PrepareContext - the provided context is used for the preparation of the
	// statement, not for the execution of the statement.
	// PrepareContext creates a prepared statement for later queries or
	// executions. Multiple queries or executions may be run concurrently from
	// the returned statement. The caller must call the statement's Close method
	// when the statement is no longer needed.
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

Preparer prepares a query in the server. The underlying type can be either a *sql.DB (connection pool), a *sql.Conn (a single dedicated database session) or a *sql.Tx (an in-progress database transaction).

type QualifiedRecord 

type QualifiedRecord struct {
	// Qualifier is the name of the table or view or procedure or can be their
	// alias. It must be a valid MySQL/MariaDB identifier.
	//
	// If empty, the main table or its alias of a query will be used. We call it
	// the default qualifier. Each query can only contain one default qualifier.
	// If you provide multiple default qualifier, the last one wins and
	// overwrites the previous.
	Qualifier string
	Record    ColumnMapper
}

QualifiedRecord is a ColumnMapper with a qualifier. A QualifiedRecord gets used as arguments to ExecRecord or WithRecords in the SQL statement. If you use an alias for the main table/view you must set the alias as the qualifier.

func Qualify 

func Qualify(q string, record ColumnMapper) QualifiedRecord

Qualify provides a more concise way to create QualifiedRecord values.

type Querier 

type Querier interface {
	// QueryContext executes a query that returns rows, typically a SELECT. The
	// args are for any placeholder parameters in the query.
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
}

Querier can execute a returning query. The underlying type can be either a *sql.DB (connection pool), a *sql.Conn (a single dedicated database session) or a *sql.Tx (an in-progress database transaction).

type QueryBuilder 

type QueryBuilder interface {
	ToSQL() (string, []any, error)
}

QueryBuilder assembles a query and returns the raw SQL without parameter substitution and the arguments. The input arguments might be modified and returned as plain primitive types.

type QueryExecPreparer 

type QueryExecPreparer interface {
	Preparer
	Querier
	Execer
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

QueryExecPreparer can execute a returning query and prepare a returning query. The underlying type can be either a *sql.DB (connection pool), a *sql.Conn (a single dedicated database session) or a *sql.Tx (an in-progress database transaction). ExecPreparer a composite interface which can execute and prepare a query. The underlying type can be either a *sql.DB (connection pool), a *sql.Conn (a single dedicated database session) or a *sql.Tx (an in-progress database transaction).

type QueryOptions 

type QueryOptions struct {
	SkipEvents     bool // skips above defined EventFlag
	SkipTimestamps bool // skips generating timestamps (TODO)
	SkipRelations  bool // skips executing relation based SQL code
}

QueryOptions provides different options while executing code for SQL queries.

func FromContextQueryOptions 

func FromContextQueryOptions(ctx context.Context) QueryOptions

FromContextQueryOptions returns the options from the context.

type QuerySQL 

type QuerySQL string

QuerySQL simple type to satisfy the QueryBuilder interface.

func (QuerySQL) ToSQL 

func (qs QuerySQL) ToSQL() (string, []any, error)

ToSQL satisfies interface QueryBuilder and returns always nil arguments and nil error.

type QuerySQLFn 

type QuerySQLFn func() (string, []any, error)

QuerySQL a helper type to transform a string into a QueryBuilder compatible type.

func (QuerySQLFn) ToSQL 

func (fn QuerySQLFn) ToSQL() (string, []any, error)

ToSQL satisfies interface QueryBuilder and returns always nil arguments and nil error.

type Select 

type Select struct {
	BuilderBase
	BuilderConditional

	// Columns represents a slice of names and its optional identifiers. Wildcard
	// `SELECT *` statements are not really supported:
	// http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful
	Columns ids

	GroupBys             ids
	Havings              Conditions
	IsStar               bool // IsStar generates a SELECT * FROM query
	IsCountStar          bool // IsCountStar retains the column names but executes a COUNT(*) query.
	IsDistinct           bool // See Distinct()
	IsStraightJoin       bool // See StraightJoin()
	IsSQLNoCache         bool // See SQLNoCache()
	IsForUpdate          bool // See ForUpdate()
	IsLockInShareMode    bool // See LockInShareMode()
	IsOrderByDeactivated bool // See OrderByDeactivated()
	IsOrderByRand        bool // enables the original slow ORDER BY RAND() clause
	OffsetCount          uint64
}

Select contains the clauses for a SELECT statement. Wildcard `SELECT *` statements are not really supported. http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful

func NewSelect 

func NewSelect(columns ...string) *Select

NewSelect creates a new Select object.

func NewSelectWithDerivedTable 

func NewSelectWithDerivedTable(subSelect *Select, aliasName string) *Select

NewSelectWithDerivedTable creates a new derived table (Subquery in the FROM Clause) using the provided sub-select in the FROM part together with an alias name. Appends the arguments of the sub-select to the parent *Select pointer arguments list. SQL result may look like: 

SELECT a,b FROM (SELECT x,y FROM `product` AS `p`) AS `t`

https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	sel3 := dml.NewSelect().Unsafe().FromAlias("sales_bestsellers_aggregated_daily", "t3").
		AddColumnsAliases("DATE_FORMAT(t3.period, '%Y-%m-01')", "period").
		AddColumns("t3.store_id", "t3.product_id", "t3.product_name").
		AddColumnsAliases("AVG(`t3`.`product_price`)", "avg_price", "SUM(t3.qty_ordered)", "total_qty").
		Where(dml.Column("product_name").Str("Canon%")).
		GroupBy("t3.store_id").
		GroupBy("DATE_FORMAT(t3.period, '%Y-%m-01')").
		GroupBy("t3.product_id", "t3.product_name").
		OrderBy("t3.store_id").
		OrderBy("DATE_FORMAT(t3.period, '%Y-%m-01')").
		OrderByDesc("total_qty")

	sel1 := dml.NewSelectWithDerivedTable(sel3, "t1").
		AddColumns("t1.period", "t1.store_id", "t1.product_id", "t1.product_name", "t1.avg_price", "t1.qty_ordered").
		Where(dml.Column("product_name").Str("Sony%")).
		OrderBy("t1.period", "t1.product_id")
	writeToSQLAndInterpolate(sel1)
}

Output:

Statement:
SELECT `t1`.`period`, `t1`.`store_id`, `t1`.`product_id`, `t1`.`product_name`,
`t1`.`avg_price`, `t1`.`qty_ordered` FROM (SELECT DATE_FORMAT(t3.period,
'%Y-%m-01') AS `period`, `t3`.`store_id`, `t3`.`product_id`,
`t3`.`product_name`, AVG(`t3`.`product_price`) AS `avg_price`,
SUM(t3.qty_ordered) AS `total_qty` FROM `sales_bestsellers_aggregated_daily` AS
`t3` WHERE (`product_name` = 'Canon%') GROUP BY `t3`.`store_id`,
DATE_FORMAT(t3.period, '%Y-%m-01'), `t3`.`product_id`, `t3`.`product_name` ORDER
BY `t3`.`store_id`, DATE_FORMAT(t3.period, '%Y-%m-01'), `total_qty` DESC) AS
`t1` WHERE (`product_name` = 'Sony%') ORDER BY `t1`.`period`, `t1`.`product_id`

func (*Select) AddColumns 

func (b *Select) AddColumns(cols ...string) *Select

AddColumns appends more columns to the Columns slice. If a column name is not valid identifier that column gets switched into an expression. 

AddColumns("a","b") 		// `a`,`b`
AddColumns("a,b","z","c,d")	// a,b,`z`,c,d
AddColumns("t1.name","t1.sku","price") // `t1`.`name`, `t1`.`sku`,`price`

func (*Select) AddColumnsAliases 

func (b *Select) AddColumnsAliases(columnAliases ...string) *Select

AddColumnsAliases expects a balanced slice of "Column1, Alias1, Column2, Alias2" and adds both to the Columns slice. An imbalanced slice will cause a panic. If a column name is not valid identifier that column gets switched into an expression. 

AddColumnsAliases("t1.name","t1Name","t1.sku","t1SKU") // `t1`.`name` AS `t1Name`, `t1`.`sku` AS `t1SKU`
AddColumnsAliases("(e.price*x.tax*t.weee)", "final_price") // error: `(e.price*x.tax*t.weee)` AS `final_price`

func (*Select) AddColumnsConditions 

func (b *Select) AddColumnsConditions(expressions ...*Condition) *Select

AddColumnsConditions adds a condition as a column to the statement. The operator field gets ignored. DBR in the condition gets applied to the RawArguments field to maintain the correct order of arguments. 

AddColumnsConditions(Expr("(e.price*x.tax*t.weee)").Alias("final_price")) // (e.price*x.tax*t.weee) AS `final_price`
Example

ExampleSelect_AddColumnsConditions is duplicate of ExampleSQLCase_select 

start := time.Unix(1257894000, 0).In(time.UTC)
end := time.Unix(1257980400, 0).In(time.UTC)

s := dml.NewSelect().AddColumns("price", "sku", "name", "title", "description").
	AddColumnsConditions(
		dml.SQLCase("", "`closed`",
			"date_start <= ? AND date_end >= ?", "`open`",
			"date_start > ? AND date_end > ?", "`upcoming`",
		).Alias("is_on_sale").Time(start).Time(end).Time(start).Time(end),
	).
	From("catalog_promotions").Where(
	dml.Column("promotion_id").NotIn().Ints(4711, 815, 42))
writeToSQLAndInterpolate(s.WithDBR(dbMock{}))

Output:

Statement:
SELECT `price`, `sku`, `name`, `title`, `description`, CASE  WHEN date_start <=
'2009-11-10 23:00:00' AND date_end >= '2009-11-11 23:00:00' THEN `open` WHEN
date_start > '2009-11-10 23:00:00' AND date_end > '2009-11-11 23:00:00' THEN
`upcoming` ELSE `closed` END AS `is_on_sale` FROM `catalog_promotions` WHERE
(`promotion_id` NOT IN (4711,815,42))

func (*Select) Clone 

func (b *Select) Clone() *Select

Clone creates a clone of the current object, leaving fields DB and Log untouched.

func (*Select) Count 

func (b *Select) Count() *Select

Count executes a COUNT(*) as `counted` query without touching or changing the currently set columns.

func (*Select) CrossJoin 

func (b *Select) CrossJoin(table id, onConditions ...*Condition) *Select

CrossJoin creates a CROSS join construct. By default, the onConditions are glued together with AND.

func (*Select) Distinct 

func (b *Select) Distinct() *Select

Distinct marks the statement at a DISTINCT SELECT. It specifies removal of duplicate rows from the result set.

func (*Select) ForUpdate 

func (b *Select) ForUpdate() *Select

ForUpdate sets for index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.) Note: Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked. https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

func (*Select) From 

func (b *Select) From(from string) *Select

From sets the table for the SELECT FROM part.

func (*Select) FromAlias 

func (b *Select) FromAlias(from, alias string) *Select

FromAlias sets the table and its alias name for a `SELECT ... FROM table AS alias` query.

func (*Select) GroupBy 

func (b *Select) GroupBy(columns ...string) *Select

GroupBy appends columns to group the statement. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. MySQL does not sort the results set. To avoid the overhead of sorting that GROUP BY produces this function should add an ORDER BY NULL with function `OrderByDeactivated`.

func (*Select) GroupByAsc 

func (b *Select) GroupByAsc(columns ...string) *Select

GroupByAsc sorts the groups in ascending order. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. No need to add an ORDER BY clause. When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Select) GroupByDesc 

func (b *Select) GroupByDesc(columns ...string) *Select

GroupByDesc sorts the groups in descending order. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. No need to add an ORDER BY clause. When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Select) Having 

func (b *Select) Having(wf ...*Condition) *Select

Having appends a HAVING clause to the statement

func (*Select) Join 

func (b *Select) Join(table id, onConditions ...*Condition) *Select

Join creates an INNER join construct. By default, the onConditions are glued together with AND.

func (*Select) LeftJoin 

func (b *Select) LeftJoin(table id, onConditions ...*Condition) *Select

LeftJoin creates a LEFT join construct. By default, the onConditions are glued together with AND.

func (*Select) Limit 

func (b *Select) Limit(offset uint64, limit uint64) *Select

Limit sets a limit for the statement; overrides any existing LIMIT. Don't build a pagination with offset or you go straight to hell.

func (*Select) LockInShareMode 

func (b *Select) LockInShareMode() *Select

LockInShareMode sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits. If any of these rows were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values. https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html

func (*Select) OrderBy 

func (b *Select) OrderBy(columns ...string) *Select

OrderBy appends columns to the ORDER BY statement for ascending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable. A column name can also contain the suffix words " ASC" or " DESC" to indicate the sorting. This avoids using the method OrderByDesc when sorting certain columns descending.

func (*Select) OrderByDeactivated 

func (b *Select) OrderByDeactivated() *Select

OrderByDeactivated deactivates ordering of the result set by applying ORDER BY NULL to the SELECT statement. Very useful for GROUP BY queries.

func (*Select) OrderByDesc 

func (b *Select) OrderByDesc(columns ...string) *Select

OrderByDesc appends columns to the ORDER BY statement for descending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Select) OrderByRandom 

func (b *Select) OrderByRandom(idColumnName string, limit uint64) *Select

OrderByRandom sorts the table randomly by not using ORDER BY RAND() rather using a JOIN with the single primary key column. This function overwrites previously set ORDER BY statements and the field LimitCount. The generated SQL by this function is about 3-4 times faster than ORDER BY RAND(). The generated SQL does not work for all queries. The underlying SQL statement might change without notice.

func (*Select) OuterJoin 

func (b *Select) OuterJoin(table id, onConditions ...*Condition) *Select

OuterJoin creates an OUTER join construct. By default, the onConditions are glued together with AND.

func (*Select) Paginate 

func (b *Select) Paginate(page, perPage uint64) *Select

Paginate sets LIMIT/OFFSET for the statement based on the given page/perPage Assumes page/perPage are valid. Page and perPage must be >= 1. Deprecated see a talk from Marcus Wienand - Modern SQL

func (*Select) RightJoin 

func (b *Select) RightJoin(table id, onConditions ...*Condition) *Select

RightJoin creates a RIGHT join construct. By default, the onConditions are glued together with AND.

func (*Select) SQLNoCache 

func (b *Select) SQLNoCache() *Select

SQLNoCache tells the server that it does not use the query cache. It neither checks the query cache to see whether the result is already cached, nor does it cache the query result.

func (*Select) Star 

func (b *Select) Star() *Select

Star creates a SELECT * FROM query. Such queries are discouraged from using.

func (*Select) StraightJoin 

func (b *Select) StraightJoin() *Select

StraightJoin forces the optimizer to join the tables in the order in which they are listed in the FROM clause. You can use this to speed up a query if the optimizer joins the tables in nonoptimal order.

func (*Select) String 

func (b *Select) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Select) ToSQL 

func (b *Select) ToSQL() (string, []any, error)

ToSQL generates the SQL string and might caches it internally, if not disabled.

func (*Select) Unless 

func (b *Select) Unless(test bool, fn func(*Select), defaultFn func(*Select)) *Select

Unless applies the function `fn` query changes if the given "test" is false. Providing the optional second function, uses it as the default value, if test is false. `defaultFn` can be nil.

func (*Select) Unsafe 

func (b *Select) Unsafe() *Select

Unsafe see BuilderBase.IsUnsafe which weakens security when building the SQL string. This function must be called before calling any other function.

func (*Select) When 

func (b *Select) When(test bool, fn func(*Select), defaultFn func(*Select)) *Select

When applies the function `fn` query changes if the given "test" is true. Providing the optional second function, uses it as the default value, if test is false. `defaultFn` can be nil.

func (*Select) Where 

func (b *Select) Where(wf ...*Condition) *Select

Where appends a WHERE clause to the statement for the given string and args or map of column/value pairs.

func (*Select) WithDBR 

func (b *Select) WithDBR(db QueryExecPreparer) *DBR

WithDBR sets the database query object and creates a database runner.

type Show 

type Show struct {
	BuilderBase
	// Type bitwise flag containing the type of the SHOW statement.
	Type uint
	// LikeCondition supports only one argument. Either `LikeCondition` or
	// `WhereFragments` can be set.
	LikeCondition  bool
	WhereFragments Conditions
}

Show represents the SHOW syntax

func NewShow 

func NewShow() *Show

NewShow creates a new Truman SHOW.

func (*Show) BinaryLog 

func (b *Show) BinaryLog() *Show

BinaryLog lists the binary log files on the server.

func (*Show) Global 

func (b *Show) Global() *Show

Global displays with a GLOBAL modifier, the statement displays global system variable values. These are the values used to initialize the corresponding session variables for new connections to MySQL. If a variable has no global value, no value is displayed.

func (*Show) Like 

func (b *Show) Like() *Show

Like sets the comparisons LIKE condition. Either WHERE or LIKE can be used. Only the first argument supported.

func (*Show) MasterStatus 

func (b *Show) MasterStatus() *Show

MasterStatus provides status information about the binary log files of the master. It requires either the SUPER or REPLICATION CLIENT privilege.

func (*Show) Session 

func (b *Show) Session() *Show

Session displays with a SESSION modifier, the statement displays the system variable values that are in effect for the current connection. If a variable has no session value, the global value is displayed. LOCAL is a synonym for SESSION. If no modifier is present, the default is SESSION.

func (*Show) Status 

func (b *Show) Status() *Show

Status provides server status information. This statement does not require any privilege. It requires only the ability to connect to the server.

func (*Show) String 

func (b *Show) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Show) TableStatus 

func (b *Show) TableStatus() *Show

TableStatus works likes SHOW TABLES, but provides a lot of information about each non-TEMPORARY table. The LIKE clause, if present, indicates which table names to match. The WHERE clause can be given to select rows using more general conditions. This statement also displays information about views.

func (*Show) ToSQL 

func (b *Show) ToSQL() (string, []any, error)

ToSQL converts the select statement into a string and returns its arguments.

func (*Show) Variable 

func (b *Show) Variable() *Show

Variable shows the values of MySQL|MariaDB system variables (“Server System Variables”). This statement does not require any privilege. It requires only the ability to connect to the server.

func (*Show) Where 

func (b *Show) Where(wf ...*Condition) *Show

Where appends a WHERE clause to the statement for the given string and args or map of column/value pairs. Either WHERE or LIKE can be used.

func (*Show) WithDBR 

func (b *Show) WithDBR(db QueryExecPreparer) *DBR

type StaticSQLResult 

type StaticSQLResult struct {
	LID  int64
	Rows int64
	Err  error
}

StaticSQLResult implements sql.Result for mocking reasons.

func (StaticSQLResult) LastInsertId 

func (r StaticSQLResult) LastInsertId() (int64, error)

func (StaticSQLResult) RowsAffected 

func (r StaticSQLResult) RowsAffected() (int64, error)

type Stmt 

type Stmt struct {
	Stmt *sql.Stmt
}

Stmt wraps a *sql.Stmt (a prepared statement) with a specific SQL query. To create a Stmt call the Prepare function of a specific DML type. Stmt is not yet safe for concurrent use, despite the underlying *sql.Stmt is. Don't forget to call Close!

func (*Stmt) Close 

func (st *Stmt) Close() error

Close closes the statement in the database and frees its resources.

type StmtExecer 

type StmtExecer interface {
	// ExecContext executes a query that doesn't return rows.
	ExecContext(ctx context.Context, args ...any) (sql.Result, error)
}

StmtExecer executes a prepared statement.

type StmtQuerier 

type StmtQuerier interface {
	// QueryContext executes a query that returns rows, typically a SELECT. The
	// args are for any placeholder parameters in the query.
	QueryContext(ctx context.Context, args ...any) (*sql.Rows, error)
}

StmtQuerier executes a prepared statement query.

type Tx 

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

Tx is an in-progress database transaction.

A transaction must end with a call to Commit or Rollback.

After a call to Commit or Rollback, all operations on the transaction fail with ErrTxDone.

The statements prepared for a transaction by calling the transaction's Prepare or Stmt methods are closed by the call to Commit or Rollback. Practical Guide to SQL Transaction Isolation: https://begriffs.com/posts/2017-08-01-practical-guide-sql-isolation.html

func (*Tx) CacheKeyExists 

func (tx *Tx) CacheKeyExists(cacheKey string) bool

CacheKeyExists returns true if a given key already exists.

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit finishes the transaction. It logs the time taken, if a logger has been set with Info logging enabled.

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback cancels the transaction. It logs the time taken, if a logger has been set with Info logging enabled.

func (*Tx) WithCacheKey 

func (tx *Tx) WithCacheKey(cacheKey string, opts ...DBRFunc) *DBR

WithCacheKey creates a DBR object from a cached query.

func (*Tx) WithPrepare 

func (tx *Tx) WithPrepare(ctx context.Context, qb QueryBuilder, opts ...DBRFunc) *DBR

WithPrepare executes the statement represented by the Select to create a prepared statement. It returns a custom statement type or an error if there was one. Provided arguments or records in the Select are getting ignored. The provided context is used for the preparation of the statement, not for the execution of the statement. The returned Stmter is not safe for concurrent use, despite the underlying *sql.Stmt is. You must close DBR after its use. It generates a unique cache key based on the SQL string. The cache key can be retrieved via DBR object.

func (*Tx) WithPrepareCacheKey 

func (tx *Tx) WithPrepareCacheKey(ctx context.Context, cacheKey string, opts ...DBRFunc) *DBR

WithPrepareCacheKey creates a DBR object from a prepared cached query. After use the query statement must be closed.

func (*Tx) WithQueryBuilder 

func (tx *Tx) WithQueryBuilder(qb QueryBuilder, opts ...DBRFunc) *DBR

WithQueryBuilder creates a new DBR for handling the arguments with the assigned connection and builds the SQL string. The returned arguments and errors of the QueryBuilder will be forwarded to the DBR type. It generates a unique cache key based on the SQL string. The cache key can be retrieved via DBR object.

type Union 

type Union struct {
	BuilderBase
	Selects     []*Select
	OrderBys    ids
	IsAll       bool // IsAll enables UNION ALL
	IsIntersect bool // See Intersect()
	IsExcept    bool // See Except()
	// contains filtered or unexported fields
}

Union represents a UNION SQL statement. UNION is used to combine the result from multiple SELECT statements into a single result set. With template usage enabled, it builds multiple select statements joined by UNION and all based on a common template.

func NewUnion 

func NewUnion(selects ...*Select) *Union

NewUnion creates a new Union object. If using as a template, only one *Select object can be provided.

Example

ExampleNewUnion constructs a UNION with three SELECTs. It preserves the results sets of each SELECT by simply adding an internal index to the columns list and sort ascending with the internal index. 

package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	u := dml.NewUnion(
		dml.NewSelect().AddColumnsAliases("a1", "A", "a2", "B").From("tableA").Where(dml.Column("a1").Int64(3)),
		dml.NewSelect().AddColumnsAliases("b1", "A", "b2", "B").From("tableB").Where(dml.Column("b1").Int64(4)),
	)
	// Maybe more of your code ...
	u.Append(
		dml.NewSelect().AddColumnsConditions(
			dml.Expr("concat(c1,?,c2)").Alias("A").Str("-"),
		).
			AddColumnsAliases("c2", "B").
			From("tableC").Where(dml.Column("c2").Str("ArgForC2")),
	).
		OrderBy("A").       // Ascending by A
		OrderByDesc("B").   // Descending by B
		All().              // Enables UNION ALL syntax
		PreserveResultSet() // Maintains the correct order of the result set for all SELECTs.
	// Note that the final ORDER BY statement of a UNION creates a temporary
	// table in MySQL.
	writeToSQLAndInterpolate(u)
}

Output:

Statement:
(SELECT `a1` AS `A`, `a2` AS `B`, 0 AS `_preserve_result_set` FROM `tableA`
WHERE (`a1` = 3))
UNION ALL
(SELECT `b1` AS `A`, `b2` AS `B`, 1 AS `_preserve_result_set` FROM `tableB`
WHERE (`b1` = 4))
UNION ALL
(SELECT concat(c1,'-',c2) AS `A`, `c2` AS `B`, 2 AS `_preserve_result_set` FROM
`tableC` WHERE (`c2` = 'ArgForC2'))
ORDER BY `_preserve_result_set`, `A`, `B` DESC
Example (Template)

ExampleNewUnion_template interpolates the SQL string with its placeholders and puts for each placeholder the correct encoded and escaped value into it. Eliminates the need for prepared statements. Avoids an additional round trip to the database server by sending the query and its arguments directly. If you execute a query multiple times within a short time you should use prepared statements. 

package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	u := dml.NewUnion(
		dml.NewSelect().AddColumns("t.value", "t.attribute_id", "t.store_id").
			FromAlias("catalog_product_entity_$type$", "t").
			Where(dml.Column("entity_id").Int64(1561), dml.Column("store_id").In().Int64s(1, 0)),
	).
		StringReplace("$type$", "varchar", "int", "decimal", "datetime", "text").
		PreserveResultSet().
		All().OrderBy("attribute_id", "store_id")
	writeToSQLAndInterpolate(u)
}

Output:

Statement:
(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id`, 0 AS
`_preserve_result_set` FROM `catalog_product_entity_varchar` AS `t` WHERE
(`entity_id` = 1561) AND (`store_id` IN (1,0)))
UNION ALL
(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id`, 1 AS
`_preserve_result_set` FROM `catalog_product_entity_int` AS `t` WHERE
(`entity_id` = 1561) AND (`store_id` IN (1,0)))
UNION ALL
(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id`, 2 AS
`_preserve_result_set` FROM `catalog_product_entity_decimal` AS `t` WHERE
(`entity_id` = 1561) AND (`store_id` IN (1,0)))
UNION ALL
(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id`, 3 AS
`_preserve_result_set` FROM `catalog_product_entity_datetime` AS `t` WHERE
(`entity_id` = 1561) AND (`store_id` IN (1,0)))
UNION ALL
(SELECT `t`.`value`, `t`.`attribute_id`, `t`.`store_id`, 4 AS
`_preserve_result_set` FROM `catalog_product_entity_text` AS `t` WHERE
(`entity_id` = 1561) AND (`store_id` IN (1,0)))
ORDER BY `_preserve_result_set`, `attribute_id`, `store_id`

func (*Union) All 

func (u *Union) All() *Union

All returns all rows. The default behavior for UNION is that duplicate rows are removed from the result. Enabling ALL returns all rows.

func (*Union) Append 

func (u *Union) Append(selects ...*Select) *Union

Append adds more *Select objects to the Union object. When using Union as a template only one *Select object can be provided.

func (*Union) Clone 

func (u *Union) Clone() *Union

Clone creates a clone of the current object, leaving fields DB and Log untouched. Additionally the fields for replacing strings also won't get copied.

func (*Union) Except 

func (u *Union) Except() *Union

Except switches the query from UNION to EXCEPT. The result of EXCEPT is all records of the left SELECT result except records which are in right SELECT result set, i.e. it is subtraction of two result sets. EXCEPT and UNION have the same operation precedence. Only supported in MariaDB >=10.3

func (*Union) Intersect 

func (u *Union) Intersect() *Union

Intersect switches the query type from UNION to INTERSECT. The result of an intersect is the intersection of right and left SELECT results, i.e. only records that are present in both result sets will be included in the result of the operation. INTERSECT has higher precedence than UNION and EXCEPT. If possible it will be executed linearly but if not it will be translated to a subquery in the FROM clause. Only supported in MariaDB >=10.3

func (*Union) OrderBy 

func (u *Union) OrderBy(columns ...string) *Union

OrderBy appends a column to ORDER the statement ascending. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. MySQL might order the result set in a temporary table, which is slow. Under different conditions sorting can skip the temporary table. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html A column name can also contain the suffix words " ASC" or " DESC" to indicate the sorting. This avoids using the method OrderByDesc when sorting certain columns descending.

func (*Union) OrderByDesc 

func (u *Union) OrderByDesc(columns ...string) *Union

OrderByDesc appends columns to the ORDER BY statement for descending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a DELETE, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Union) PreserveResultSet 

func (u *Union) PreserveResultSet() *Union

PreserveResultSet enables the correct ordering of the result set from the Select statements. UNION by default produces an unordered set of rows. To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY following the last SELECT.

func (*Union) String 

func (u *Union) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Union) StringReplace 

func (u *Union) StringReplace(key string, values ...string) *Union

StringReplace is only applicable when using *Union as a template. StringReplace replaces the `key` with one of the `values`. Each value defines a generated SELECT query. Repeating calls of StringReplace must provide the same amount of `values` as the first or an index of bound stack trace happens. This function is just a simple string replacement. Make sure that your key does not match other parts of the SQL query.

func (*Union) ToSQL 

func (u *Union) ToSQL() (string, []any, error)

ToSQL converts the statements into a string and returns its arguments.

func (*Union) Unsafe 

func (u *Union) Unsafe() *Union

Unsafe see BuilderBase.IsUnsafe which weakens security when building the SQL string. This function must be called before calling any other function.

func (*Union) WithDBR 

func (b *Union) WithDBR(db QueryExecPreparer) *DBR

type Update 

type Update struct {
	BuilderBase
	BuilderConditional
	// SetClauses contains the column/argument association. For each column
	// there must be one argument.
	SetClauses Conditions
}

Update contains the logic for an UPDATE statement. TODO: add UPDATE JOINS

func NewUpdate 

func NewUpdate(table string) *Update

NewUpdate creates a new Update object.

func (*Update) AddClauses 

func (b *Update) AddClauses(c ...*Condition) *Update

AddClauses appends a column/value pair for the statement.

func (*Update) AddColumns 

func (b *Update) AddColumns(columnNames ...string) *Update

AddColumns adds columns whose values gets later derived from a ColumnMapper. Those columns will get passed to the ColumnMapper implementation.

func (*Update) Alias 

func (b *Update) Alias(alias string) *Update

Alias sets an alias for the table name.

func (*Update) Clone 

func (b *Update) Clone() *Update

Clone creates a clone of the current object, leaving fields DB and Log untouched.

func (*Update) Limit 

func (b *Update) Limit(limit uint64) *Update

Limit sets a limit for the statement; overrides any existing LIMIT

func (*Update) OrderBy 

func (b *Update) OrderBy(columns ...string) *Update

OrderBy appends columns to the ORDER BY statement for ascending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a UPDATE, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable. A column name can also contain the suffix words " ASC" or " DESC" to indicate the sorting. This avoids using the method OrderByDesc when sorting certain columns descending.

func (*Update) OrderByDesc 

func (b *Update) OrderByDesc(columns ...string) *Update

OrderByDesc appends columns to the ORDER BY statement for descending sorting. A column gets always quoted if it is a valid identifier otherwise it will be treated as an expression. When you use ORDER BY or GROUP BY to sort a column in a UPDATE, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable.

func (*Update) SetColumns 

func (b *Update) SetColumns(columnNames ...string) *Update

SetColumns resets the SetClauses slice and adds the columns. Same behaviour as AddColumns.

func (*Update) String 

func (b *Update) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*Update) ToSQL 

func (b *Update) ToSQL() (string, []any, error)

ToSQL converts the select statement into a string and returns its arguments.

func (*Update) Unsafe 

func (b *Update) Unsafe() *Update

Unsafe see BuilderBase.IsUnsafe which weakens security when building the SQL string. This function must be called before calling any other function.

func (*Update) Where 

func (b *Update) Where(wf ...*Condition) *Update

Where appends a WHERE clause to the statement

func (*Update) WithDBR 

func (b *Update) WithDBR(db QueryExecPreparer) *DBR

type With 

type With struct {
	BuilderBase
	Subclauses []WithCTE
	// TopLevel a union type which allows only one of the fields to be set.
	TopLevel struct {
		Select *Select
		Union  *Union
		Update *Update
		Delete *Delete
	}
	IsRecursive bool // See Recursive()
}

With represents a common table expression. Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. Non-recursive CTES are basically 'query-local VIEWs'. One CTE can refer to another. The syntax is more readable than nested FROM (SELECT ...). One can refer to a CTE from multiple places. They are better than copy-pasting FROM(SELECT ...)

Common Table Expression versus Derived Table: Better readability; Can be referenced multiple times; Can refer to other CTEs; Improved performance.

https://dev.mysql.com/doc/refman/8.0/en/with.html

https://mariadb.com/kb/en/mariadb/non-recursive-common-table-expressions-overview/

http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes/

http://dankleiman.com/2018/02/06/3-ways-to-level-up-your-sql-as-a-software-engineer/

Supported in: MySQL >=8.0.1 and MariaDb >=10.2

func NewWith 

func NewWith(expressions ...WithCTE) *With

NewWith creates a new WITH statement with multiple common table expressions (CTE).

func (*With) Clone 

func (b *With) Clone() *With

Clone creates a clone of the current object, leaving fields DB and Log untouched.

func (*With) Delete 

func (b *With) Delete(topLevel *Delete) *With

Delete gets used in the top level statement.

func (*With) Recursive 

func (b *With) Recursive() *With

Recursive common table expressions are one having a subquery that refers to its own name. The WITH clause must begin with WITH RECURSIVE if any CTE in the WITH clause refers to itself. (If no CTE refers to itself, RECURSIVE is permitted but not required.) Common applications of recursive CTEs include series generation and traversal of hierarchical or tree-structured data. It is simpler, when experimenting with WITH RECURSIVE, to put this at the start of your session: `SET max_execution_time = 10000;` so that the runaway query aborts automatically after 10 seconds, if the WHERE clause wasn’t correct.

func (*With) Select 

func (b *With) Select(topLevel *Select) *With

Select gets used in the top level statement.

func (*With) String 

func (b *With) String() string

String returns a string representing a preprocessed, interpolated, query. On error, the error gets printed. Fulfills interface fmt.Stringer.

func (*With) ToSQL 

func (b *With) ToSQL() (string, []any, error)

ToSQL converts the select statement into a string and returns its arguments.

func (*With) Union 

func (b *With) Union(topLevel *Union) *With

Union gets used in the top level statement.

Example 
package main

import (
	"fmt"
	"os"

	"github.com/fatih/color"

	"github.com/corestoreio/pkg/sql/dml"
	"github.com/corestoreio/pkg/util/strs"
)

func writeToSQLAndInterpolate(qb dml.QueryBuilder) {
	sqlStr, args, err := qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	if len(args) > 0 {
		fmt.Print("Prepared ")
	}
	fmt.Println("Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	fmt.Print("\n")
	if len(args) > 0 {
		fmt.Printf("Arguments: %v\n\n", args)
	} else {
		return
	}

	switch dmlArg := qb.(type) {
	case dml.QuerySQLFn:

	case *dml.DBR:
		prev := dmlArg.Options
		qb = dmlArg.Interpolate()
		defer func() { dmlArg.Options = prev; qb = dmlArg }()
	case *dml.Insert:
		return
	default:
		panic(fmt.Sprintf("func compareToSQL: the type %T is not (yet) supported.", qb))
	}

	sqlStr, args, err = qb.ToSQL()
	if err != nil {
		fmt.Printf("%+v\n", err)
		return
	}
	fmt.Println("Interpolated Statement:")
	strs.FwordWrap(os.Stdout, sqlStr, 80)
	if len(args) > 0 {

		fmt.Println(color.RedString("\nfunc compareToSQL should not return arguments when interpolation is enabled.\nGot: %#v\n", args))
	}
}

func main() {
	// Non-recursive CTE
	// Sales: Find best and worst month:
	cte := dml.NewWith(
		dml.WithCTE{
			Name: "sales_by_month", Columns: []string{"month", "total"},
			Select: dml.NewSelect().Unsafe().AddColumns("Month(day_of_sale)", "Sum(amount)").From("sales_days").
				Where(dml.Expr("Year(day_of_sale) = ?").Int(2015)).
				GroupBy("Month(day_of_sale))"),
		},
		dml.WithCTE{
			Name: "best_month", Columns: []string{"month", "total", "award"},
			Select: dml.NewSelect().Unsafe().AddColumns("month", "total").AddColumns(`"best"`).From("sales_by_month").
				Where(dml.Column("total").Equal().Sub(dml.NewSelect().Unsafe().AddColumns("Max(total)").From("sales_by_month"))),
		},
		dml.WithCTE{
			Name: "worst_month", Columns: []string{"month", "total", "award"},
			Select: dml.NewSelect().Unsafe().AddColumns("month", "total").AddColumns(`"worst"`).From("sales_by_month").
				Where(dml.Column("total").Equal().Sub(dml.NewSelect().Unsafe().AddColumns("Min(total)").From("sales_by_month"))),
		},
	).Union(dml.NewUnion(
		dml.NewSelect().Star().From("best_month"),
		dml.NewSelect().Star().From("worst_month"),
	).All())
	writeToSQLAndInterpolate(cte)

	// Result:
	//+-------+-------+-------+
	//| month | total | award |
	//+-------+-------+-------+
	//|     1 |   300 | best  |
	//|     3 |    11 | worst |
	//+-------+-------+-------+

}

Output:

Statement:
WITH `sales_by_month` (`month`,`total`) AS (SELECT Month(day_of_sale),
Sum(amount) FROM `sales_days` WHERE (Year(day_of_sale) = 2015) GROUP BY
Month(day_of_sale))),
`best_month` (`month`,`total`,`award`) AS (SELECT `month`, `total`, "best" FROM
`sales_by_month` WHERE (`total` = (SELECT Max(total) FROM `sales_by_month`))),
`worst_month` (`month`,`total`,`award`) AS (SELECT `month`, `total`, "worst"
FROM `sales_by_month` WHERE (`total` = (SELECT Min(total) FROM
`sales_by_month`)))
(SELECT * FROM `best_month`)
UNION ALL
(SELECT * FROM `worst_month`)

func (*With) Update 

func (b *With) Update(topLevel *Update) *With

Update gets used in the top level statement.

func (*With) WithDBR 

func (b *With) WithDBR(db QueryExecPreparer) *DBR

type WithCTE 

type WithCTE struct {
	Name string
	// Columns, optionally, the number of names in the list must be the same as
	// the number of columns in the result set.
	Columns []string
	// Select clause as a common table expression. Has precedence over the Union field.
	Select *Select
	// Union clause as a common table expression. Select field pointer must be
	// nil to trigger SQL generation of this field.
	Union *Union
}

WithCTE defines a common table expression used in the type `With`.

func (WithCTE) Clone 

func (cte WithCTE) Clone() WithCTE

Clone creates a cloned object of the current one.

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.