Documentation ¶
Overview ¶
Package squalor provides SQL utility routines, such as validation of models against table schemas, marshalling and unmarshalling of model structs into table rows and programmatic construction of SQL statements.
Limitations ¶
While squalor uses the database/sql package, the SQL it utilizes is MySQL specific (e.g. REPLACE, INSERT ON DUPLICATE KEY UPDATE, etc).
Model Binding and Validation ¶
Given a simple table definition:
CREATE TABLE users ( id bigint PRIMARY KEY NOT NULL, name varchar(767) NOT NULL, INDEX (id, name) )
And a model for a row of the table:
type User struct { ID int64 `db:"id"` Name string `db:"name"` }
The BindModel method will validate that the model and the table definition are compatible. For example, it would be an error for the User.ID field to have the type string.
users, err := db.BindModel("users", User{})
The table definition is loaded from the database allowing custom checks such as verifying the existence of indexes.
if users.GetKey("id") == nil { // The index ("id") does not exist! } if users.GetKey("id", "name") == nil { // The index ("id", "name") does not exist! }
Custom Types ¶
While fields are often primitive types such as int64 or string, it is sometimes desirable to use a custom type. This can be accomplished by having the type implement the sql.Scanner and driver.Valuer interfaces. For example, you might create a GzippedText type which automatically compresses the value when it is written to the database and uncompressed when it is read:
type GzippedText []byte func (g GzippedText) Value() (driver.Value, error) { buf := &bytes.Buffer{} w := gzip.NewWriter(buf) defer w.Close() w.Write(g) return buf.Bytes(), nil } func (g *GzippedText) Scan(src interface{}) error { var source []byte switch t := src.(type) { case string: source = []byte(t) case []byte: source = t default: return errors.New("Incompatible type for GzippedText") } reader, err := gzip.NewReader(bytes.NewReader(source)) defer reader.Close() b, err := ioutil.ReadAll(reader) if err != nil { return err } *g = GzippedText(b) return nil }
Insert ¶
The Insert method is used to insert one or more rows in a table.
err := db.Insert(User{ID:42, Name:"Peter Mattis"})
You can pass either a struct or a pointer to a struct.
err := db.Insert(&User{ID:43, Name:"Spencer Kimball"})
Multiple rows can be inserted at once. Doing so offers convenience and performance.
err := db.Insert(&User{ID:42, Name:"Peter Mattis"}, &User{ID:43, Name:"Spencer Kimball"})
When multiple rows are batch inserted the returned error corresponds to the first SQL error encountered which may make it impossible to determine which row caused the error. If the rows correspond to different tables the order of insertion into the tables is undefined. If you care about the order of insertion into multiple tables and determining which row is causing an insertion error, structure your calls to Insert appropriately (i.e. insert into a single table or insert a single row).
After a successful insert on a table with an auto increment primary key, the auto increment will be set back in the corresponding field of the object. For example, if the user table was defined as:
CREATE TABLE users ( id bigint PRIMARY KEY AUTO INCREMENT NOT NULL, name varchar(767) NOT NULL, INDEX (id, name) )
Then we could create a new user by doing:
u := &User{Name:"Peter Mattis"} err := db.Insert(u) if err != nil { ... } // u.ID will be correctly populated at this point
Replace ¶
The Replace method replaces a row in table, either inserting the row if it doesn't exist, or deleting and then inserting the row. See the MySQL docs for the difference between REPLACE, UPDATE and INSERT ON DUPLICATE KEY UPDATE (Upsert).
err := db.Replace(&User{ID:42, Name:"Peter Mattis"})
Update ¶
The Update method updates a row in a table, returning the number of rows modified.
count, err := db.Update(&User{ID:42, Name:"Peter Mattis"})
Upsert ¶
The Upsert method inserts or updates a row.
err := db.Upsert(&User{ID:42, Name:"Peter Mattis"})
Get ¶
The Get method retrieves a single row by primary key and binds the result columns to a struct.
user := &User{} err := db.Get(user, 42) // Returns an error if user 42 cannot be found.
Delete ¶
The delete method deletes rows by primary key.
err := db.Delete(&User{ID:42})
See the documentation for DB.Delete for performance limitations when batch deleting multiple rows from a table with a primary key composed of multiple columns.
Optimistic Locking ¶
To support optimistic locking with a column storing the version number, one field in a model object can be marked to serve as the lock. Modifying the example above:
type User struct { ID int64 `db:"id"` Name string `db:"name"` Ver int `db:"version,optlock"` }
Now, the Update method will ensure that the object has not been concurrently modified when writing, by constraining the update by the version number. If the update is successful, the version number will be both incremented on the model (in-memory), as well as in the database.
Programmatic SQL Construction ¶
Programmatic construction of SQL queries prohibits SQL injection attacks while keeping query construction both readable and similar to SQL itself. Support is provided for most of the SQL DML (data manipulation language), though the constructed queries are targetted to MySQL.
q := users.Select("*").Where(users.C("id").Eq(foo)) // squalor.Serialize(q) == "SELECT `users`.* FROM users WHERE `users`.`id` = 'foo'", nil var results []User err := db.Select(&results, q)
DB provides wrappers for the sql.Query and sql.QueryRow interfaces. The Rows struct returned from Query has an additional StructScan method for binding the columns for a row result to a struct.
rows, err := db.Query(q) if err != nil { return err } defer rows.Close() for rows.Next() { u := User{} if err := rows.StructScan(&u); err != nil { return err } // Process u }
QueryRow can be used to easily query and scan a single value:
var count int err := db.QueryRow(users.Select(users.C("id").Count())).Scan(&count)
Performance ¶
In addition to the convenience of inserting, deleting and updating table rows using a struct, attention has been paid to performance. Since squalor is carefully constructing the SQL queries for insertion, deletion and update, it eschews the use of placeholders in favor of properly escaping values in the query. With the Go MySQL drivers, this saves a roundtrip to the database for each query because queries with placeholders must be prepared before being executed.
Marshalling and unmarshalling of data from structs utilizes reflection, but care is taken to minimize the use of reflection in order to improve performance. For example, reflection data for models is cached when the model is bound to a table.
Batch operations are utilized when possible. The Delete, Insert, Replace, Update and Upsert operations will perform multiple operations per SQL statement. This can provide an order of magnitude speed improvement over performing the mutations one row at a time due to minimizing the network overhead.
Index ¶
- Variables
- func QueryDeadlineMySQL57(db *DB) error
- func QueryDeadlineNone(db *DB) error
- func QueryDeadlinePercona56(db *DB) error
- func Serialize(s Serializer) (string, error)
- func SerializeWithPlaceholders(s Serializer) (string, error)
- type AliasedTableExpr
- type AndExpr
- type BinaryExpr
- type BoolExpr
- type BoolExprBuilder
- type BytesVal
- type CaseExpr
- type ColName
- type Column
- type Columns
- type Comments
- type ComparisonExpr
- type DB
- func (db *DB) Begin() (*Tx, error)
- func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
- func (db *DB) BindModel(name string, obj interface{}) (*Model, error)
- func (db *DB) Context() context.Context
- func (db *DB) Delete(list ...interface{}) (int64, error)
- func (db *DB) DeleteContext(ctx context.Context, list ...interface{}) (int64, error)
- func (db *DB) Exec(query interface{}, args ...interface{}) (sql.Result, error)
- func (db *DB) ExecContext(ctx context.Context, query interface{}, args ...interface{}) (sql.Result, error)
- func (db *DB) Get(dest interface{}, keys ...interface{}) error
- func (db *DB) GetContext(ctx context.Context, dest interface{}, keys ...interface{}) error
- func (db *DB) GetModel(obj interface{}) (*Model, error)
- func (db *DB) Insert(list ...interface{}) error
- func (db *DB) InsertContext(ctx context.Context, list ...interface{}) error
- func (db *DB) InsertIgnore(list ...interface{}) error
- func (db *DB) InsertIgnoreContext(ctx context.Context, list ...interface{}) error
- func (db *DB) MustBindModel(name string, obj interface{}) *Model
- func (db *DB) Query(q interface{}, args ...interface{}) (*Rows, error)
- func (db *DB) QueryContext(ctx context.Context, q interface{}, args ...interface{}) (*Rows, error)
- func (db *DB) QueryRow(q interface{}, args ...interface{}) *Row
- func (db *DB) QueryRowContext(ctx context.Context, q interface{}, args ...interface{}) *Row
- func (db *DB) Replace(list ...interface{}) error
- func (db *DB) ReplaceContext(ctx context.Context, list ...interface{}) error
- func (db *DB) Select(dest interface{}, q interface{}, args ...interface{}) error
- func (db *DB) SelectContext(ctx context.Context, dest interface{}, q interface{}, args ...interface{}) error
- func (db *DB) Transaction(fn func(tx *Tx) error) (err error)
- func (db *DB) Update(list ...interface{}) (int64, error)
- func (db *DB) UpdateContext(ctx context.Context, list ...interface{}) (int64, error)
- func (db *DB) Upsert(list ...interface{}) error
- func (db *DB) UpsertContext(ctx context.Context, list ...interface{}) error
- func (db *DB) WithContext(ctx context.Context) ExecutorContext
- type DBOption
- type Deletable
- type Delete
- type DeleteBuilder
- type EncodedVal
- type ErrVal
- type Executor
- type ExecutorContext
- type ExistsExpr
- type Expr
- type FuncExpr
- type GroupBy
- type IndexHints
- type Insert
- type InsertBuilder
- func (b *InsertBuilder) Add(vals ...interface{}) *InsertBuilder
- func (b *InsertBuilder) AddRows(rows Values) *InsertBuilder
- func (b *InsertBuilder) Comments(comments []string) *InsertBuilder
- func (b *InsertBuilder) OnDupKeyUpdate(col interface{}, val interface{}) *InsertBuilder
- func (b *InsertBuilder) OnDupKeyUpdateColumn(col interface{}) *InsertBuilder
- type InsertRows
- type JoinBuilder
- func (b *JoinBuilder) Delete(tables ...*Table) *DeleteBuilder
- func (b *JoinBuilder) InnerJoin(other *Table) *JoinBuilder
- func (b *JoinBuilder) LeftJoin(other *Table) *JoinBuilder
- func (b *JoinBuilder) On(expr BoolExpr) *JoinBuilder
- func (b *JoinBuilder) RightJoin(other *Table) *JoinBuilder
- func (b *JoinBuilder) Select(exprs ...interface{}) *SelectBuilder
- func (b *JoinBuilder) Using(cols ...interface{}) *JoinBuilder
- type JoinCond
- type JoinTableExpr
- type Key
- type Limit
- type Model
- type NoOpConfiguration
- type NonStarExpr
- type NotExpr
- type NullCheck
- type NullVal
- type NumVal
- type OnDup
- type OnJoinCond
- type OrExpr
- type Order
- type OrderBy
- type ParenBoolExpr
- type ParenTableExpr
- type PlaceholderVal
- type PostCommit
- type PostDelete
- type PostGet
- type PostInsert
- type PostReplace
- type PostUpdate
- type PostUpsert
- type PreCommit
- type PreDelete
- type PreInsert
- type PreReplace
- type PreUpdate
- type PreUpsert
- type QueryLogger
- type RangeCond
- type RawVal
- type ReplaceBuilder
- type RetryConfiguration
- type Retryable
- type Row
- type Rows
- type Select
- type SelectBuilder
- func (b *SelectBuilder) Comments(comments []string) *SelectBuilder
- func (b *SelectBuilder) Distinct() *SelectBuilder
- func (b *SelectBuilder) ForUpdate() *SelectBuilder
- func (b *SelectBuilder) GroupBy(vals ...ValExpr) *SelectBuilder
- func (b *SelectBuilder) Having(expr BoolExpr) *SelectBuilder
- func (b *SelectBuilder) Limit(count interface{}) *SelectBuilder
- func (b *SelectBuilder) Offset(offset interface{}) *SelectBuilder
- func (b *SelectBuilder) OrderBy(exprs ...interface{}) *SelectBuilder
- func (b *SelectBuilder) Where(expr BoolExpr) *SelectBuilder
- func (b *SelectBuilder) WithSharedLock() *SelectBuilder
- type SelectExpr
- type SelectExprs
- type SelectStatement
- type Selectable
- type Serializer
- type SimpleTableExpr
- type StandardLogger
- type StarExpr
- type Statement
- type StrVal
- type Subquery
- type Table
- func (t *Table) All() ValExprBuilder
- func (t *Table) C(cols ...string) ValExprBuilder
- func (t *Table) Delete(tables ...*Table) *DeleteBuilder
- func (t *Table) ForceIndex(indexes ...string) *Table
- func (t *Table) GetKey(cols ...string) *Key
- func (t *Table) IgnoreIndex(indexes ...string) *Table
- func (t *Table) InnerJoin(other *Table) *JoinBuilder
- func (t *Table) Insert(cols ...interface{}) *InsertBuilder
- func (t *Table) InsertIgnore(cols ...interface{}) *InsertBuilder
- func (t *Table) LeftJoin(other *Table) *JoinBuilder
- func (t *Table) Replace(cols ...interface{}) *ReplaceBuilder
- func (t *Table) RightJoin(other *Table) *JoinBuilder
- func (t *Table) Select(exprs ...interface{}) *SelectBuilder
- func (t *Table) String() string
- func (t *Table) Update() *UpdateBuilder
- func (t *Table) UseIndex(indexes ...string) *Table
- func (t *Table) ValidateModel(model interface{}) error
- type TableExpr
- type TableExprs
- type TableName
- type TableNames
- type Tuple
- type Tx
- func (tx *Tx) AddPostCommitHook(post PostCommit)
- func (tx *Tx) AddPreCommitHook(pre PreCommit)
- func (tx *Tx) Commit() error
- func (tx *Tx) Context() context.Context
- func (tx *Tx) Delete(list ...interface{}) (int64, error)
- func (tx *Tx) DeleteContext(ctx context.Context, list ...interface{}) (int64, error)
- func (tx *Tx) Exec(query interface{}, args ...interface{}) (sql.Result, error)
- func (tx *Tx) ExecContext(ctx context.Context, query interface{}, args ...interface{}) (sql.Result, error)
- func (tx *Tx) Get(dest interface{}, keys ...interface{}) error
- func (tx *Tx) GetContext(ctx context.Context, dest interface{}, keys ...interface{}) error
- func (tx *Tx) Insert(list ...interface{}) error
- func (tx *Tx) InsertContext(ctx context.Context, list ...interface{}) error
- func (tx *Tx) InsertIgnore(list ...interface{}) error
- func (tx *Tx) InsertIgnoreContext(ctx context.Context, list ...interface{}) error
- func (tx *Tx) Query(q interface{}, args ...interface{}) (*Rows, error)
- func (tx *Tx) QueryContext(ctx context.Context, q interface{}, args ...interface{}) (*Rows, error)
- func (tx *Tx) QueryRow(q interface{}, args ...interface{}) *Row
- func (tx *Tx) QueryRowContext(ctx context.Context, q interface{}, args ...interface{}) *Row
- func (tx *Tx) Replace(list ...interface{}) error
- func (tx *Tx) ReplaceContext(ctx context.Context, list ...interface{}) error
- func (tx *Tx) Select(dest interface{}, q interface{}, args ...interface{}) error
- func (tx *Tx) SelectContext(ctx context.Context, dest interface{}, q interface{}, args ...interface{}) error
- func (tx *Tx) Update(list ...interface{}) (int64, error)
- func (tx *Tx) UpdateContext(ctx context.Context, list ...interface{}) (int64, error)
- func (tx *Tx) Upsert(list ...interface{}) error
- func (tx *Tx) UpsertContext(ctx context.Context, list ...interface{}) error
- func (tx *Tx) WithContext(ctx context.Context) ExecutorContext
- type UnaryExpr
- type Union
- type Update
- type UpdateBuilder
- func (b *UpdateBuilder) Comments(comments []string) *UpdateBuilder
- func (b *UpdateBuilder) Limit(count interface{}) *UpdateBuilder
- func (b *UpdateBuilder) OrderBy(exprs ...interface{}) *UpdateBuilder
- func (b *UpdateBuilder) Set(col interface{}, val interface{}) *UpdateBuilder
- func (b *UpdateBuilder) Where(expr BoolExpr) *UpdateBuilder
- type UpdateExpr
- type UpdateExprs
- type UsingJoinCond
- type ValArg
- type ValExpr
- type ValExprBuilder
- func (b ValExprBuilder) As(s string) SelectExpr
- func (b ValExprBuilder) Ascending() *Order
- func (b ValExprBuilder) Between(from interface{}, to interface{}) BoolExprBuilder
- func (b ValExprBuilder) BitAnd(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) BitOr(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) BitXor(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) Coalesce() ValExprBuilder
- func (b ValExprBuilder) Count() ValExprBuilder
- func (b ValExprBuilder) CountDistinct() ValExprBuilder
- func (b ValExprBuilder) Descending() *Order
- func (b ValExprBuilder) Div(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) Eq(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Func(name string) ValExprBuilder
- func (b ValExprBuilder) FuncDistinct(name string) ValExprBuilder
- func (b ValExprBuilder) Gt(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Gte(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) In(list ...interface{}) BoolExprBuilder
- func (b ValExprBuilder) InTuple(tuple Tuple) BoolExprBuilder
- func (b ValExprBuilder) IsNotNull() BoolExprBuilder
- func (b ValExprBuilder) IsNull() BoolExprBuilder
- func (b ValExprBuilder) Like(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Lt(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Lte(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Max() ValExprBuilder
- func (b ValExprBuilder) Min() ValExprBuilder
- func (b ValExprBuilder) Minus(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) Mod(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) Mul(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) Neq(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) NotBetween(from interface{}, to interface{}) BoolExprBuilder
- func (b ValExprBuilder) NotIn(list ...interface{}) BoolExprBuilder
- func (b ValExprBuilder) NotInTuple(tuple Tuple) BoolExprBuilder
- func (b ValExprBuilder) NotLike(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) NotRegExp(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) NullSafeEq(val interface{}) BoolExprBuilder
- func (b ValExprBuilder) Plus(expr interface{}) ValExprBuilder
- func (b ValExprBuilder) RegExp(val interface{}) BoolExprBuilder
- type ValExprs
- type ValTuple
- type Values
- type When
- type Where
- type Writer
Examples ¶
Constants ¶
This section is empty.
Variables ¶
var BasicRetryConfiguration = RetryConfiguration{ 3, 300, map[error]bool{conn.ErrReadOnly: true, conn.ErrConnLost: true, conn.ErrConnCannotConnect: true, conn.ErrTimeout: true, mysql.ErrInvalidConn: true}}
var (
ContextKeyComments = contextKey("comments")
)
var ErrConcurrentModificationDetected = errors.New("sql: concurrent modification detected")
ErrConcurrentModificationDetected is returned when attempting to update versioned records, and concurrent modifications by another transaction are detected.
var ErrMixedAutoIncrIDs = errors.New("sql: auto increment column must be all set or unset")
ErrMixedAutoIncrIDs is returned when attempting to insert multiple records with a mixture of set and unset auto increment ids. This case is difficult to handle correctly, so for now either we update all the ids, or none at all.
var ( // Placeholder is a placeholder for a value in a SQL statement. It is replaced with // an actual value when the query is executed. Placeholder = PlaceholderVal{} )
Functions ¶
func QueryDeadlineMySQL57 ¶
When passed as an option to NewDB(), enables query deadlines on MySql 5.7.4 or later. Query deadlines only affect SELECT queries run via the Executor.Query function. It us up to the underlying database engine to enforce the deadline.
func QueryDeadlineNone ¶
When passed as an option to NewDB(), disables query deadlines.
func QueryDeadlinePercona56 ¶
When passed as an option to NewDB(), enables query deadlines on Percona Server 5.6 or later. Query deadlines only affect SELECT queries run via the Executor.Query function. It us up to the underlying database engine to enforce the deadline.
func Serialize ¶
func Serialize(s Serializer) (string, error)
Serialize serializes a serializer to a string.
func SerializeWithPlaceholders ¶
func SerializeWithPlaceholders(s Serializer) (string, error)
SerializeWithPlaceholders serializes a serializer to a string but without substituting values. It may be useful for logging.
Types ¶
type AliasedTableExpr ¶
type AliasedTableExpr struct { Expr SimpleTableExpr As string Hints *IndexHints }
AliasedTableExpr represents a table expression coupled with an optional alias or index hint.
func (*AliasedTableExpr) Serialize ¶
func (node *AliasedTableExpr) Serialize(w Writer) error
type BinaryExpr ¶
BinaryExpr represents a binary value expression.
func (*BinaryExpr) Serialize ¶
func (node *BinaryExpr) Serialize(w Writer) error
type BoolExpr ¶
type BoolExpr interface { Expr // contains filtered or unexported methods }
BoolExpr represents a boolean expression.
type BoolExprBuilder ¶
type BoolExprBuilder struct {
BoolExpr
}
BoolExprBuilder aids the construction of boolean expressions from other boolean expressions.
func (BoolExprBuilder) And ¶
func (e BoolExprBuilder) And(expr BoolExpr) BoolExprBuilder
And creates an AND expression.
func (BoolExprBuilder) Not ¶
func (e BoolExprBuilder) Not() BoolExprBuilder
Not creates a NOT expression.
func (BoolExprBuilder) Or ¶
func (e BoolExprBuilder) Or(expr BoolExpr) BoolExprBuilder
Or creates an OR expression.
type Column ¶
type Column struct { Name string AutoIncr bool Nullable bool // contains filtered or unexported fields }
Column models a database column.
type Columns ¶
type Columns []SelectExpr
Columns represents an insert column list. The syntax for Columns is a subset of SelectExprs. So, it's castable to a SelectExprs and can be analyzed as such.
type ComparisonExpr ¶
ComparisonExpr represents a two-value comparison expression.
func (*ComparisonExpr) Serialize ¶
func (node *ComparisonExpr) Serialize(w Writer) error
type DB ¶
type DB struct { *sql.DB Retryable Retryable AllowStringQueries bool // Whether to ignore missing columns referenced in models for the various DB // function calls such as StructScan, Select, Insert, BindModel, etc. // // The default is false, disallowing models to be bound when missing columns // are detected to avoid run time surprises (e.g. fields not being saved). IgnoreMissingCols bool // Whether to ignore unmapped columns for the various DB function calls such as StructScan, // Select, Insert, BindModel, etc. When set to true, it can suppress column mapping validation // errors at DB migration time when new columns are added but the previous version of the binary // is still in use, either actively running or getting started up. // // The default is true that ignores the unmapped columns. // NOTE: Unmapped columns in primary keys are still not allowed. IgnoreUnmappedCols bool Logger QueryLogger // Determines whether opentracing is enabled. // If enabled, it will create a new span for each squalor call. // // The default is false, tracers should be registered by the callers. OpentracingEnabled bool // contains filtered or unexported fields }
DB is a wrapper around a sql.DB which also implements the squalor.Executor interface. DB is safe for concurrent use by multiple goroutines.
func NewDB ¶
NewDB creates a new DB from an sql.DB. Zero or more DBOptions may be passed in and will be processed in order.
func (*DB) BindModel ¶
BindModel binds the supplied interface with the named table. You must bind the model for any object you wish to perform operations on. It is an error to bind the same model type more than once and a single model type can only be bound to a single table. note: name does not get escaped. The libary assumes the parameter is a literal string and is safe.
func (*DB) Delete ¶
Delete runs a batched SQL DELETE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
On success, returns the number of rows deleted.
Returns an error if an element in the list has not been registered with BindModel.
Due to MySQL limitations, batch deletions are more restricted than insertions. The most natural implementation would be something like:
DELETE FROM <table> WHERE (<cols>...) IN ((<vals1>), (<vals2>), ...)
This works except that it is spectactularly slow if there is more than one column in the primary key. MySQL changes this into a full table scan and then compares the primary key for each row with the "IN" set of values.
Instead, we batch up deletions based on the first n-1 primary key columns. For a two column primary key this looks like:
DELETE FROM <table> WHERE <cols1>=<val1> and <col2> IN (<val2>...)
If you're deleting a batch of objects where the first primary key column differs for each object this degrades to non-batched deletion. But if your first primary key column is identical then batching can work perfectly.
func (*DB) DeleteContext ¶
DeleteContext is the context version of Delete.
func (*DB) Exec ¶
Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.
func (*DB) ExecContext ¶
func (db *DB) ExecContext(ctx context.Context, query interface{}, args ...interface{}) (sql.Result, error)
ExecContext is the context version of Exec.
func (*DB) Get ¶
Get runs a SQL SELECT to fetch a single row. Keys must be the primary keys defined for the table. The order must match the order of the columns in the primary key.
Returns an error if the object type has not been registered with BindModel.
func (*DB) GetContext ¶
GetContext is the context version of Get.
func (*DB) GetModel ¶
GetModel retrieves the model for the specified object. Obj must be a struct. An error is returned if obj has not been bound to a table via a call to BindModel.
func (*DB) Insert ¶
Insert runs a batched SQL INSERT statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
An object bound to a table with an auto-increment column will have its corresponding field filled in with the generated value if a pointer to the object was passed in "list".
Returns an error if an element in the list has not been registered with BindModel.
func (*DB) InsertContext ¶
InsertContext is the context version of Insert.
func (*DB) InsertIgnore ¶
InsertIgnore runs a batched SQL INSERT IGNORE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
An object bound to a table with an auto-increment column will have its corresponding field filled in with the generated value if a pointer to the object was passed in "list".
Returns an error if an element in the list has not been registered with BindModel.
func (*DB) InsertIgnoreContext ¶
InsertIgnoreContext is the context version of InsertIgnore.
func (*DB) MustBindModel ¶
MustBindModel binds the supplied interface with the named table, panicking if an error occurs.
func (*DB) Query ¶
Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query. This is a small wrapper around sql.DB.Query that returns a *squalor.Rows instead.
func (*DB) QueryContext ¶
QueryContext is the context version of Query.
func (*DB) QueryRow ¶
QueryRow executes a query that is expected to return at most one row. QueryRow always return a non-nil value. Errors are deferred until Row's Scan method is called. This is a small wrapper around sql.DB.QueryRow that returns a *squalor.Row instead.
func (*DB) QueryRowContext ¶
QueryRowContext is the context version of QueryRow.
func (*DB) Replace ¶
Replace runs a batched SQL REPLACE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
Note that REPLACE is effectively an INSERT followed by a DELETE and INSERT if the object already exists. The REPLACE may fail if the DELETE would violate foreign key constraints. Due to the batched nature of the Replace implementation it is not possible to accurately return the assignment of auto-increment values. Updating of an existing object will cause the auto-increment column to change.
Returns an error if an element in the list has not been registered with BindModel.
func (*DB) ReplaceContext ¶
ReplaceContext is the context version of Replace.
func (*DB) Select ¶
Select runs an arbitrary SQL query, unmarshalling the matching rows into the fields on the struct specified by dest. Args are the parameters to the SQL query.
It is ok for dest to refer to a struct that has not been bound to a table. This allows querying for values that return transient columnts. For example, "SELECT count(*) ..." will return a "count" column.
dest must be a pointer to a slice. Either *[]struct{} or *[]*struct{} is allowed. It is mildly more efficient to use *[]struct{} due to the reduced use of reflection and allocation.
func (*DB) SelectContext ¶
func (db *DB) SelectContext(ctx context.Context, dest interface{}, q interface{}, args ...interface{}) error
SelectContext is the context version of Select.
func (*DB) Transaction ¶
Transaction begins a transaction as a block, if it errors it will rollback and return an error, otherwise it will commit.
func (*DB) Update ¶
Update runs a SQL UPDATE statement for each element in list. List elements may be structs or pointers to structs.
On success, returns the number of rows updated.
Returns an error if an element in the list has not been registered with BindModel.
func (*DB) UpdateContext ¶
UpdateContext is the context version of Update.
func (*DB) Upsert ¶
Upsert runs a SQL INSERT ON DUPLICATE KEY UPDATE statement for each element in list. List elements must be pointers to structs.
Returns an error if an element in the list has not been registered with BindModel.
func (*DB) UpsertContext ¶
UpsertContext is the context version of Upsert.
func (*DB) WithContext ¶
func (db *DB) WithContext(ctx context.Context) ExecutorContext
type DBOption ¶
func AllowStringQueries ¶
When passed as an option to NewDB(), determines whether to allow string queries.
func IgnoreMissingCols ¶
When passed as an option to NewDB(), determines whether to ignore missing database columns.
func IgnoreUnmappedCols ¶
When passed as an option to NewDB(), determines whether to ignore unmapped database columns.
func SetOpentracingEnabled ¶
When passed as an option to NewDB(), determines whether to create a new opentracing span for squalor call.
func SetQueryLogger ¶
func SetQueryLogger(logger QueryLogger) DBOption
When passed as an option to NewDB(), SetQueryLogger sets database logger to the given QueryLogger.
func SetRetryable ¶
type Deletable ¶
type Deletable interface {
Delete(table ...*Table) *DeleteBuilder
}
The Deletable interface is a type for structs that provide the Delete function. The types that implement this interface are Table and JoinBuilder. This interface is not used by Squalor itself, it is provided so that users can create functions that can accept either of these two types.
type Delete ¶
type Delete struct { Comments Comments // Table is either a TableName or a JoinBuilder Table abstractTable // TableNames is a list of tables to perform the delete on. This is only necessary when doing // joins, because you may not want to delete from all the tables involved in the join. // In deletes without joins, this may be empty or the table being deleted from. TableNames TableNames Where *Where OrderBy OrderBy Limit *Limit }
Delete represents a DELETE statement.
type DeleteBuilder ¶
type DeleteBuilder struct {
Delete
}
DeleteBuilder aids the construction of DELETE statements, providing methods for setting the clauses of a delete statement.
func (*DeleteBuilder) Comments ¶
func (b *DeleteBuilder) Comments(comments []string) *DeleteBuilder
Comments sets comments for the statement, replacing any existing comments.
func (*DeleteBuilder) Limit ¶
func (b *DeleteBuilder) Limit(count interface{}) *DeleteBuilder
Limit sets the LIMIT clause for the statement, replacing any existing limit clause.
func (*DeleteBuilder) OrderBy ¶
func (b *DeleteBuilder) OrderBy(exprs ...interface{}) *DeleteBuilder
OrderBy sets the ORDER BY clause for the statement, replacing any existing order by clause.
func (*DeleteBuilder) Where ¶
func (b *DeleteBuilder) Where(expr BoolExpr) *DeleteBuilder
Where sets the WHERE clause for the statement, replacing any existing where clause.
type EncodedVal ¶
type EncodedVal struct {
Val []byte
}
EncodedVal represents an already encoded value. This struct must be used with caution because misuse can provide an avenue for SQL injection attacks.
func (EncodedVal) Serialize ¶
func (node EncodedVal) Serialize(w Writer) error
type ErrVal ¶
type ErrVal struct {
Err error
}
ErrVal represents an error condition that occurred while constructing a tree.
type Executor ¶
type Executor interface { Delete(list ...interface{}) (int64, error) Exec(query interface{}, args ...interface{}) (sql.Result, error) Get(dest interface{}, keys ...interface{}) error Insert(list ...interface{}) error InsertIgnore(list ...interface{}) error Query(query interface{}, args ...interface{}) (*Rows, error) QueryRow(query interface{}, args ...interface{}) *Row Replace(list ...interface{}) error Select(dest interface{}, query interface{}, args ...interface{}) error Update(list ...interface{}) (int64, error) Upsert(list ...interface{}) error // Context versions of the general functions. DeleteContext(ctx context.Context, list ...interface{}) (int64, error) ExecContext(ctx context.Context, query interface{}, args ...interface{}) (sql.Result, error) GetContext(ctx context.Context, dest interface{}, keys ...interface{}) error InsertContext(ctx context.Context, list ...interface{}) error InsertIgnoreContext(ctx context.Context, list ...interface{}) error QueryContext(ctx context.Context, query interface{}, args ...interface{}) (*Rows, error) QueryRowContext(ctx context.Context, query interface{}, args ...interface{}) *Row ReplaceContext(ctx context.Context, list ...interface{}) error SelectContext(ctx context.Context, dest interface{}, query interface{}, args ...interface{}) error UpdateContext(ctx context.Context, list ...interface{}) (int64, error) UpsertContext(ctx context.Context, list ...interface{}) error }
Executor defines the common interface for executing operations on a DB or on a Tx.
type ExecutorContext ¶
type ExecutorContext interface { Executor // Returns an Executor that is equivalent to this Executor, except that the // returned Executor's Context() function will return the supplied Context. // This Executor is unchanged. The supplied Context must not be nil. // // If the Context has a deadline set, the query may be set to time out after the // given deadline (for read-only queries when run on a suitable database engine). WithContext(ctx context.Context) ExecutorContext // Returns the Context supplied when this Executor was created by WithContext, // or Context.Background() if WithContext was never called. Context() context.Context }
ExecutorContext extends the Executor interface by allowing a Context object to be supplied by the code issuing a query, and later accessed within a QueryLogger.
type ExistsExpr ¶
type ExistsExpr struct {
Subquery *Subquery
}
ExistsExpr represents an EXISTS expression.
type Expr ¶
type Expr interface { Serializer // contains filtered or unexported methods }
Expr represents an expression.
type FuncExpr ¶
type FuncExpr struct { Name string Distinct bool Exprs SelectExprs }
FuncExpr represents a function call.
type IndexHints ¶
IndexHints represents a list of index hints.
func (*IndexHints) Serialize ¶
func (node *IndexHints) Serialize(w Writer) error
type Insert ¶
type Insert struct { Kind string Comments Comments Table *TableName Columns Columns Rows InsertRows OnDup OnDup }
Insert represents an INSERT or REPLACE statement.
type InsertBuilder ¶
type InsertBuilder struct { Insert // contains filtered or unexported fields }
InsertBuilder aids the construction of INSERT statements, providing methods for adding rows to the statement.
func (*InsertBuilder) Add ¶
func (b *InsertBuilder) Add(vals ...interface{}) *InsertBuilder
Add appends a single row of values to the statement.
func (*InsertBuilder) AddRows ¶
func (b *InsertBuilder) AddRows(rows Values) *InsertBuilder
AddRows appends multiple rows of values to the statement.
func (*InsertBuilder) Comments ¶
func (b *InsertBuilder) Comments(comments []string) *InsertBuilder
Comments sets comments for the statement, replacing any existing comments.
func (*InsertBuilder) OnDupKeyUpdate ¶
func (b *InsertBuilder) OnDupKeyUpdate(col interface{}, val interface{}) *InsertBuilder
OnDupKeyUpdate specifies an ON DUPLICATE KEY UPDATE expression to be performed when a duplicate primary key is encountered during insertion. The specified column must exist within the table being inserted into.
func (*InsertBuilder) OnDupKeyUpdateColumn ¶
func (b *InsertBuilder) OnDupKeyUpdateColumn(col interface{}) *InsertBuilder
OnDupKeyUpdateColumn specifies on ON DUPLICATE KEY UPDATE expression to be performed when a duplicate primary key is encountered during insertion. The specified column must exist within the table being inserted into. The value to use for updating is taken from the corresponding column value in the row being inserted.
type InsertRows ¶
type InsertRows interface { Serializer // contains filtered or unexported methods }
InsertRows represents the rows for an INSERT statement.
type JoinBuilder ¶
type JoinBuilder struct { JoinTableExpr // contains filtered or unexported fields }
JoinBuilder aids the construction of JOIN expressions, providing methods for specifying the join condition.
func (*JoinBuilder) Delete ¶
func (b *JoinBuilder) Delete(tables ...*Table) *DeleteBuilder
Delete creates a DELETE statement builder.
func (*JoinBuilder) InnerJoin ¶
func (b *JoinBuilder) InnerJoin(other *Table) *JoinBuilder
InnerJoin creates a INNER JOIN statement builder
func (*JoinBuilder) LeftJoin ¶
func (b *JoinBuilder) LeftJoin(other *Table) *JoinBuilder
LeftJoin creates a LEFT JOIN statement builder.
func (*JoinBuilder) On ¶
func (b *JoinBuilder) On(expr BoolExpr) *JoinBuilder
On sets an ON join condition for the expression, replacing any existing join condition.
func (*JoinBuilder) RightJoin ¶
func (b *JoinBuilder) RightJoin(other *Table) *JoinBuilder
RightJoin creates a RIGHT JOIN statement builder.
func (*JoinBuilder) Select ¶
func (b *JoinBuilder) Select(exprs ...interface{}) *SelectBuilder
Select creates a SELECT statement builder.
func (*JoinBuilder) Using ¶
func (b *JoinBuilder) Using(cols ...interface{}) *JoinBuilder
Using sets a USING join condition for the expression, replacing any existing join condition. The columns must exist in both the left and right sides of the join expression.
type JoinCond ¶
type JoinCond interface { Serializer // contains filtered or unexported methods }
JoinCond represents a join condition.
type JoinTableExpr ¶
JoinTableExpr represents a TableExpr that's a JOIN operation.
func (*JoinTableExpr) Serialize ¶
func (node *JoinTableExpr) Serialize(w Writer) error
type Model ¶
type Model struct { // The table the model is associated with. Table // contains filtered or unexported fields }
A Model contains the precomputed data for a model binding to a table.
func (*Model) All ¶
func (m *Model) All() ValExprBuilder
All builds a val expression to select all columns on the model's Table - including unmapped columns. WARNING: This function does not respect the IgnoreUnmappedCols setting. Use of AllMapped() is preferred.
func (*Model) AllMapped ¶
func (m *Model) AllMapped() ValExprBuilder
AllMapped builds a val expression to select all columns that are mapped by the model.
type NoOpConfiguration ¶
type NoOpConfiguration struct{}
func (*NoOpConfiguration) Retry ¶
func (n *NoOpConfiguration) Retry(fn func() error) error
type NonStarExpr ¶
NonStarExpr defines a non-'*' select expr.
func (*NonStarExpr) Serialize ¶
func (node *NonStarExpr) Serialize(w Writer) error
type OnJoinCond ¶
type OnJoinCond struct {
Expr BoolExpr
}
OnJoinCond represents an ON join condition.
func (*OnJoinCond) Serialize ¶
func (node *OnJoinCond) Serialize(w Writer) error
type ParenBoolExpr ¶
type ParenBoolExpr struct {
Expr BoolExpr
}
ParenBoolExpr represents a parenthesized boolean expression.
func (*ParenBoolExpr) Serialize ¶
func (node *ParenBoolExpr) Serialize(w Writer) error
type ParenTableExpr ¶
type ParenTableExpr struct {
Expr TableExpr
}
ParenTableExpr represents a parenthesized TableExpr.
type PlaceholderVal ¶
type PlaceholderVal struct{}
PlaceholderVal represents a placeholder parameter that will be supplied when executing the query. It will be serialized as a ?.
func (PlaceholderVal) Serialize ¶
func (node PlaceholderVal) Serialize(w Writer) error
type PostCommit ¶
type PostCommit func(error)
PostCommit will be executed after a squalor.Tx.Commit(). The hook is provided with the result of the commit. The hook itself cannot fail.
type PostDelete ¶
PostDelete will be executed after the DELETE statement.
type PostInsert ¶
PostInsert will be executed after the INSERT statement.
type PostReplace ¶
PostReplace will be executed after the REPLACE statement.
type PostUpdate ¶
PostUpdate will be executed after the UPDATE statement.
type PostUpsert ¶
PostUpsert will be executed after the INSERT ON DUPLICATE UPDATE statement.
type PreReplace ¶
PreReplace will be executed before the REPLACE statement.
type QueryLogger ¶
type QueryLogger interface { // Log is called on completion of a query with a Serializer for the // query, the Executor it was called on, the execution time of the query // and an error if one occurred. // // The Executor may be used to trace queries within a transaction because // queries in the same transaction will use the same executor. Log(ctx context.Context, query Serializer, exec Executor, executionTime time.Duration, err error) }
QueryLogger defines an interface for query loggers.
type ReplaceBuilder ¶
type ReplaceBuilder struct { Insert // contains filtered or unexported fields }
ReplaceBuilder aids the construction of REPLACE expressions, providing methods for adding rows to the statement.
func (*ReplaceBuilder) Add ¶
func (b *ReplaceBuilder) Add(vals ...interface{}) *ReplaceBuilder
Add appends a single row of values to the statement.
func (*ReplaceBuilder) AddRows ¶
func (b *ReplaceBuilder) AddRows(rows Values) *ReplaceBuilder
AddRows appends multiple rows of values to the statement.
func (*ReplaceBuilder) Comments ¶
func (b *ReplaceBuilder) Comments(comments []string) *ReplaceBuilder
Comments sets comments for the statement, replacing any existing comments.
type RetryConfiguration ¶
type RetryConfiguration struct { Retries int SleepBetweenRetriesInMillis int64 RetryableExceptions map[error]bool }
func (*RetryConfiguration) Retry ¶
func (retryConfig *RetryConfiguration) Retry(fn func() error) error
type Row ¶
type Row struct {
// contains filtered or unexported fields
}
Row is a wrapper around sql.Row which adds a StructScan method.
func (*Row) Columns ¶
Columns returns the column names. Columns returns an error if the row is closed, or if there was a deferred error from processing the query.
func (*Row) Scan ¶
Scan copies the columns from the matched row into the values pointed at by dest. If more than one row matches the query, Scan uses the first row and discards the rest. If no row matches the query, Scan returns ErrNoRows.
func (*Row) StructScan ¶
StructScan copies the columns from the matched row into the struct pointed at by dest. If more than one row matches the query, Scan uses the first row and discards the rest. If no row matches the query, Scan returns ErrNoRows.
type Rows ¶
Rows is a wrapper around sql.Rows which adds a StructScan method.
func (*Rows) StructScan ¶
StructScan copies the columns in the current row into the struct pointed at by dest.
type Select ¶
type Select struct { Comments Comments Distinct string Exprs SelectExprs From TableExprs Where *Where GroupBy GroupBy Having *Where OrderBy OrderBy Limit *Limit Lock string }
Select represents a SELECT statement.
type SelectBuilder ¶
type SelectBuilder struct { Select // contains filtered or unexported fields }
SelectBuilder aids the construction of SELECT statements, providing methods for setting the clauses of the select statement.
func (*SelectBuilder) Comments ¶
func (b *SelectBuilder) Comments(comments []string) *SelectBuilder
Comments sets comments for the statement, replacing any existing comments.
func (*SelectBuilder) Distinct ¶
func (b *SelectBuilder) Distinct() *SelectBuilder
Distinct sets the DISTINCT tag on the statement causing duplicate row results to be removed.
func (*SelectBuilder) ForUpdate ¶
func (b *SelectBuilder) ForUpdate() *SelectBuilder
ForUpdate sets the FOR UPDATE tag on the statement causing the result rows to be locked (dependent on the specific MySQL storage engine).
func (*SelectBuilder) GroupBy ¶
func (b *SelectBuilder) GroupBy(vals ...ValExpr) *SelectBuilder
GroupBy sets the GROUP BY clause for the statement, replacing any existing group by clause.
func (*SelectBuilder) Having ¶
func (b *SelectBuilder) Having(expr BoolExpr) *SelectBuilder
Having sets the HAVING clause for the statement, replacing any existing having clause.
func (*SelectBuilder) Limit ¶
func (b *SelectBuilder) Limit(count interface{}) *SelectBuilder
Limit sets the LIMIT clause for the statement, replacing any existing limit clause.
func (*SelectBuilder) Offset ¶
func (b *SelectBuilder) Offset(offset interface{}) *SelectBuilder
Offset sets the OFFSET clause for the statement. It is an error to set the offset before setting the limit.
func (*SelectBuilder) OrderBy ¶
func (b *SelectBuilder) OrderBy(exprs ...interface{}) *SelectBuilder
OrderBy sets the ORDER BY clause for the statement, replacing any existing order by clause.
func (*SelectBuilder) Where ¶
func (b *SelectBuilder) Where(expr BoolExpr) *SelectBuilder
Where sets the WHERE clause for the statement, replacing any existing where clause.
func (*SelectBuilder) WithSharedLock ¶
func (b *SelectBuilder) WithSharedLock() *SelectBuilder
WithSharedLock sets the LOCK IN SHARE MODE tag on the statement causing the result rows to be read locked (dependent on the specific MySQL storage engine).
type SelectExpr ¶
type SelectExpr interface { Serializer // contains filtered or unexported methods }
SelectExpr represents a SELECT expression.
type SelectExprs ¶
type SelectExprs []SelectExpr
SelectExprs represents SELECT expressions.
func (SelectExprs) Serialize ¶
func (node SelectExprs) Serialize(w Writer) error
type SelectStatement ¶
type SelectStatement interface { Statement // contains filtered or unexported methods }
SelectStatement any SELECT statement.
type Selectable ¶
type Selectable interface {
Select(exprs ...interface{}) *SelectBuilder
}
The Selectable interface is a type for structs that provide the Select function. The types that implement this interface are Table and JoinBuilder. This interface is not used by Squalor itself, it is provided so that users can create functions that can accept either of these two types.
type Serializer ¶
type Serializer interface { // Serialize writes the statement/expression to the Writer. If an // error is returned the Writer may contain partial output. Serialize(w Writer) error }
The Serializer interface is implemented by all expressions/statements.
type SimpleTableExpr ¶
type SimpleTableExpr interface { Serializer // contains filtered or unexported methods }
SimpleTableExpr represents a simple table expression.
type StandardLogger ¶
StandardLogger implements the QueryLogger interface and wraps a log.Logger.
func (*StandardLogger) Log ¶
func (l *StandardLogger) Log(ctx context.Context, query Serializer, exec Executor, executionTime time.Duration, err error)
type StarExpr ¶
type StarExpr struct {
TableName string
}
StarExpr defines a '*' or 'table.*' expression.
type Statement ¶
type Statement interface { Serializer // contains filtered or unexported methods }
Statement represents a statement.
type Table ¶
type Table struct { Name string Alias string IndexHints *IndexHints Columns []*Column ColumnMap map[string]*Column PrimaryKey *Key Keys []*Key KeyMap map[string]*Key }
Table models a database table containing column and key definitions.
func NewAliasedTable ¶
NewAliasedTable constructs a table with an alias from a model struct. The alias will be used in column names and in joins. The resulting table is less detailed than one created from LoadTable due to the lack of keys of type info.
func NewTable ¶
NewTable constructs a table from a model struct. The resulting table is less detailed than one created from LoadTable due to the lack of keys of type info.
func (*Table) All ¶
func (t *Table) All() ValExprBuilder
All returns an expression for all of the columns in the table in the order in which they are defined in the table (the order of Table.Columns). Note that returned expression is a tuple of columns, not a star expression.
func (*Table) C ¶
func (t *Table) C(cols ...string) ValExprBuilder
C returns an expression for the specified list of columns. An error expression is created if any of the columns do not exist in the table. An error expression is created if no columns are specified.
func (*Table) Delete ¶
func (t *Table) Delete(tables ...*Table) *DeleteBuilder
Delete creates a DELETE statement builder.
func (*Table) ForceIndex ¶
Apply a "FORCE INDEX" hint to a table. This will replace any existing index hints.
func (*Table) IgnoreIndex ¶
Apply an "IGNORE INDEX" hint to a table. This will replace any existing index hints.
func (*Table) InnerJoin ¶
func (t *Table) InnerJoin(other *Table) *JoinBuilder
InnerJoin creates an INNER JOIN statement builder. Note that inner join and join are synonymous in MySQL. Inner join is used here for clarity.
func (*Table) Insert ¶
func (t *Table) Insert(cols ...interface{}) *InsertBuilder
Insert creates an INSERT statement builder.
func (*Table) InsertIgnore ¶
func (t *Table) InsertIgnore(cols ...interface{}) *InsertBuilder
Insert creates an INSERT statement builder.
func (*Table) LeftJoin ¶
func (t *Table) LeftJoin(other *Table) *JoinBuilder
LeftJoin creates a LEFT JOIN statement builder.
func (*Table) Replace ¶
func (t *Table) Replace(cols ...interface{}) *ReplaceBuilder
Replace creates a REPLACE statement builder.
func (*Table) RightJoin ¶
func (t *Table) RightJoin(other *Table) *JoinBuilder
RightJoin creates a RIGHT JOIN statement builder.
func (*Table) Select ¶
func (t *Table) Select(exprs ...interface{}) *SelectBuilder
Select creates a SELECT statement builder.
Example ¶
type User struct { ID string } users := NewTable("users", User{}) id := users.C("id") q := users.Select("*").Where(id.Eq("bar")) if sql, err := Serialize(q); err != nil { fmt.Println(err) } else { fmt.Println(sql) }
Output: SELECT * FROM `users` WHERE `users`.`id` = 'bar'
func (*Table) Update ¶
func (t *Table) Update() *UpdateBuilder
Update creates an UPDATE statement builder.
func (*Table) UseIndex ¶
Apply a "USE INDEX" hint to a table. This will replace any existing index hints.
func (*Table) ValidateModel ¶
ValidateModel validates that the model is compatible for the table's schema. It checks that every column in the database is mapped to a field in the model and every field in the model has a corresponding column in the table schema.
type TableExpr ¶
type TableExpr interface { Serializer // contains filtered or unexported methods }
TableExpr represents a table expression.
type TableExprs ¶
type TableExprs []TableExpr
TableExprs represents a list of table expressions.
func (TableExprs) Serialize ¶
func (node TableExprs) Serialize(w Writer) error
type TableName ¶
type TableName struct {
Name, Qualifier string
}
TableName represents a table name.
type TableNames ¶
type TableNames []*TableName
TableNames represents several table names. It is used in deletes that have joins.
func (TableNames) Serialize ¶
func (node TableNames) Serialize(w Writer) error
type Tuple ¶
type Tuple interface { ValExpr // contains filtered or unexported methods }
Tuple represents a tuple. It can be ValTuple, Subquery.
type Tx ¶
Tx is a wrapper around sql.Tx which also implements the squalor.Executor interface.
func (*Tx) AddPostCommitHook ¶
func (tx *Tx) AddPostCommitHook(post PostCommit)
AddPostCommitHook adds a post-commit hook to this transaction.
func (*Tx) AddPreCommitHook ¶
AddPreCommitHook adds a pre-commit hook to this transaction.
func (*Tx) Commit ¶
Commit is a wrapper around sql.Tx.Commit() which also provides pre- and post- commit hooks.
func (*Tx) Delete ¶
Delete runs a batched SQL DELETE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
On success, returns the number of rows deleted.
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) DeleteContext ¶
DeleteContext is the context version of Delete.
func (*Tx) Exec ¶
Exec executes a query that doesn't return rows. For example: an INSERT and UPDATE.
func (*Tx) ExecContext ¶
func (tx *Tx) ExecContext(ctx context.Context, query interface{}, args ...interface{}) (sql.Result, error)
ExecContext executes a query using the provided context.
func (*Tx) Get ¶
Get runs a SQL SELECT to fetch a single row. Keys must be the primary keys defined for the table. The order must match the order of the columns in the primary key.
Returns an error if the object type has not been registered with BindModel.
func (*Tx) GetContext ¶
GetContext is the context version of Get.
func (*Tx) Insert ¶
Insert runs a batched SQL INSERT statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
An object bound to a table with an auto-increment column will have its corresponding field filled in with the generated value if a pointer to the object was passed in "list".
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) InsertContext ¶
InsertContext is the context version of Insert.
func (*Tx) InsertIgnore ¶
InsertIgnore runs a batched SQL INSERT IGNORE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
An object bound to a table with an auto-increment column will have its corresponding field filled in with the generated value if a pointer to the object was passed in "list".
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) InsertIgnoreContext ¶
InsertIgnoreContext is the context version of InsertIgnore.
func (*Tx) Query ¶
Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query. This is a small wrapper around sql.Tx.Query that returns a *squalor.Rows instead.
func (*Tx) QueryContext ¶
QueryContext is the context version of Query.
func (*Tx) QueryRow ¶
QueryRow executes a query that is expected to return at most one row. QueryRow always return a non-nil value. Errors are deferred until Row's Scan method is called. This is a small wrapper around sql.Tx.QueryRow that returns a *squalor.Row instead.
func (*Tx) QueryRowContext ¶
QueryRowContext is the context version of QueryRow.
func (*Tx) Replace ¶
Replace runs a batched SQL REPLACE statement, grouping the objects by the model type of the list elements. List elements must be pointers to structs.
Note that REPLACE is effectively an INSERT followed by a DELETE and INSERT if the object already exists. The REPLACE may fail if the DELETE would violate foreign key constraints. Due to the batched nature of the Replace implementation it is not possible to accurately return the assignment of auto-increment values. Updating of an existing object will cause the auto-increment column to change.
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) ReplaceContext ¶
ReplaceContext is the context version of Replace.
func (*Tx) Select ¶
Select runs an arbitrary SQL query, unmarshalling the matching rows into the fields on the struct specified by dest. Args are the parameters to the SQL query.
It is ok for dest to refer to a struct that has not been bound to a table. This allows querying for values that return transient columnts. For example, "SELECT count(*) ..." will return a "count" column.
dest must be a pointer to a slice. Either *[]struct{} or *[]*struct{} is allowed. It is mildly more efficient to use *[]struct{} due to the reduced use of reflection and allocation.
func (*Tx) SelectContext ¶
func (tx *Tx) SelectContext(ctx context.Context, dest interface{}, q interface{}, args ...interface{}) error
SelectContext is the context version of Select.
func (*Tx) Update ¶
Update runs a SQL UPDATE statement for each element in list. List elements may be structs or pointers to structs.
On success, returns the number of rows updated.
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) UpdateContext ¶
UpdateContext is the context version of Update.
func (*Tx) Upsert ¶
Upsert runs a SQL INSERT ON DUPLICATE KEY UPDATE statement for each element in list. List elements must be pointers to structs.
Returns an error if an element in the list has not been registered with BindModel.
func (*Tx) UpsertContext ¶
UpsertContext is the context version of Upsert.
func (*Tx) WithContext ¶
func (tx *Tx) WithContext(ctx context.Context) ExecutorContext
type Union ¶
type Union struct { Type string Left, Right SelectStatement }
Union represents a UNION statement.
type Update ¶
type Update struct { Comments Comments Table *TableName Tables []*Table Exprs UpdateExprs Where *Where OrderBy OrderBy Limit *Limit }
Update represents an UPDATE statement.
type UpdateBuilder ¶
type UpdateBuilder struct { Update // contains filtered or unexported fields }
UpdateBuilder aids the construction of UPDATE statements, providing methods for specifying which columns are to be updated and setting other clauses of the update statement.
func (*UpdateBuilder) Comments ¶
func (b *UpdateBuilder) Comments(comments []string) *UpdateBuilder
Comments sets comments for the statement, replacing any existing comments.
func (*UpdateBuilder) Limit ¶
func (b *UpdateBuilder) Limit(count interface{}) *UpdateBuilder
Limit sets the limit clause for the statement, replacing any existing limit clause.
func (*UpdateBuilder) OrderBy ¶
func (b *UpdateBuilder) OrderBy(exprs ...interface{}) *UpdateBuilder
OrderBy sets the order by clause for the statement, replacing any existing order by clause.
func (*UpdateBuilder) Set ¶
func (b *UpdateBuilder) Set(col interface{}, val interface{}) *UpdateBuilder
Set appends a Set expression to the statement. The specified column must exist within the table being updated.
func (*UpdateBuilder) Where ¶
func (b *UpdateBuilder) Where(expr BoolExpr) *UpdateBuilder
Where sets the where clause for the statement, replacing any existing where clause.
type UpdateExpr ¶
UpdateExpr represents an update expression.
func (*UpdateExpr) Serialize ¶
func (node *UpdateExpr) Serialize(w Writer) error
type UpdateExprs ¶
type UpdateExprs []*UpdateExpr
UpdateExprs represents a list of update expressions.
func (UpdateExprs) Serialize ¶
func (node UpdateExprs) Serialize(w Writer) error
type UsingJoinCond ¶
type UsingJoinCond struct {
Cols Columns
}
UsingJoinCond represents a USING join condition.
func (*UsingJoinCond) Serialize ¶
func (node *UsingJoinCond) Serialize(w Writer) error
type ValExpr ¶
type ValExpr interface { Expr // contains filtered or unexported methods }
ValExpr represents a value expression.
type ValExprBuilder ¶
type ValExprBuilder struct {
ValExpr
}
ValExprBuilder aids the construction of boolean expressions from values such as "foo == 1" or "bar IN ('a', 'b', 'c')" and value expressions such as "count + 1".
func If ¶
func If(e BoolExprBuilder, lhs, rhs ValExpr) ValExprBuilder
If creates a IF(...) expression.
func L ¶
func L(val interface{}) ValExprBuilder
L creates a ValExpr from the supplied val, performing type conversions when possible. Val can be a builtin type like int or string, or an existing ValExpr such as a column returned by Table.C.
func (ValExprBuilder) As ¶
func (b ValExprBuilder) As(s string) SelectExpr
As creates an AS (alias) expression.
func (ValExprBuilder) Ascending ¶
func (b ValExprBuilder) Ascending() *Order
Ascending creates an ASC order expression.
func (ValExprBuilder) Between ¶
func (b ValExprBuilder) Between(from interface{}, to interface{}) BoolExprBuilder
Between creates a BETWEEN expression.
func (ValExprBuilder) BitAnd ¶
func (b ValExprBuilder) BitAnd(expr interface{}) ValExprBuilder
BitAnd creates a & expression.
func (ValExprBuilder) BitOr ¶
func (b ValExprBuilder) BitOr(expr interface{}) ValExprBuilder
BitOr creates a | expression.
func (ValExprBuilder) BitXor ¶
func (b ValExprBuilder) BitXor(expr interface{}) ValExprBuilder
BitXor creates a ^ expression.
func (ValExprBuilder) Coalesce ¶
func (b ValExprBuilder) Coalesce() ValExprBuilder
Coalesce creates a COALESCE(...) expression.
func (ValExprBuilder) Count ¶
func (b ValExprBuilder) Count() ValExprBuilder
Count creates a COUNT(...) expression.
func (ValExprBuilder) CountDistinct ¶
func (b ValExprBuilder) CountDistinct() ValExprBuilder
CountDistinct creates a COUNT(DISTINCT ...) expression.
func (ValExprBuilder) Descending ¶
func (b ValExprBuilder) Descending() *Order
Descending creates a DESC order expression.
func (ValExprBuilder) Div ¶
func (b ValExprBuilder) Div(expr interface{}) ValExprBuilder
Div creates a / expression.
func (ValExprBuilder) Eq ¶
func (b ValExprBuilder) Eq(val interface{}) BoolExprBuilder
Eq creates a = comparison expression.
func (ValExprBuilder) Func ¶
func (b ValExprBuilder) Func(name string) ValExprBuilder
Func creates a function expression where name is the name of the function. The function will be invoked as name(val).
func (ValExprBuilder) FuncDistinct ¶
func (b ValExprBuilder) FuncDistinct(name string) ValExprBuilder
FuncDistinct creates a function expression where name is the name of the function. The function will be invoked as name(DISTINCT val).
func (ValExprBuilder) Gt ¶
func (b ValExprBuilder) Gt(val interface{}) BoolExprBuilder
Gt creates a > comparison expression.
func (ValExprBuilder) Gte ¶
func (b ValExprBuilder) Gte(val interface{}) BoolExprBuilder
Gte creates a >= comparison expression.
func (ValExprBuilder) In ¶
func (b ValExprBuilder) In(list ...interface{}) BoolExprBuilder
In creates an IN expression from a list of values.
func (ValExprBuilder) InTuple ¶
func (b ValExprBuilder) InTuple(tuple Tuple) BoolExprBuilder
InTuple creates an IN expression from a tuple.
func (ValExprBuilder) IsNotNull ¶
func (b ValExprBuilder) IsNotNull() BoolExprBuilder
IsNotNull creates an IS NOT NULL expression.
func (ValExprBuilder) IsNull ¶
func (b ValExprBuilder) IsNull() BoolExprBuilder
IsNull creates an IS NULL expression.
func (ValExprBuilder) Like ¶
func (b ValExprBuilder) Like(val interface{}) BoolExprBuilder
Like creates a LIKE expression.
func (ValExprBuilder) Lt ¶
func (b ValExprBuilder) Lt(val interface{}) BoolExprBuilder
Lt creates a < comparison expression.
func (ValExprBuilder) Lte ¶
func (b ValExprBuilder) Lte(val interface{}) BoolExprBuilder
Lte creates a <= comparison expression.
func (ValExprBuilder) Max ¶
func (b ValExprBuilder) Max() ValExprBuilder
Max creates a MAX(...) expression.
func (ValExprBuilder) Min ¶
func (b ValExprBuilder) Min() ValExprBuilder
Min creates a MIN(...) expression.
func (ValExprBuilder) Minus ¶
func (b ValExprBuilder) Minus(expr interface{}) ValExprBuilder
Minus creates a - expression.
func (ValExprBuilder) Mod ¶
func (b ValExprBuilder) Mod(expr interface{}) ValExprBuilder
Mod creates a % expression.
func (ValExprBuilder) Mul ¶
func (b ValExprBuilder) Mul(expr interface{}) ValExprBuilder
Mul creates a * expression.
func (ValExprBuilder) Neq ¶
func (b ValExprBuilder) Neq(val interface{}) BoolExprBuilder
Neq creates a != comparison expression.
func (ValExprBuilder) NotBetween ¶
func (b ValExprBuilder) NotBetween(from interface{}, to interface{}) BoolExprBuilder
NotBetween creates a NOT BETWEEN expression.
func (ValExprBuilder) NotIn ¶
func (b ValExprBuilder) NotIn(list ...interface{}) BoolExprBuilder
NotIn creates a NOT IN expression from a list of values.
func (ValExprBuilder) NotInTuple ¶
func (b ValExprBuilder) NotInTuple(tuple Tuple) BoolExprBuilder
NotInTuple creates a NOT IN expression from a tuple.
func (ValExprBuilder) NotLike ¶
func (b ValExprBuilder) NotLike(val interface{}) BoolExprBuilder
NotLike creates a NOT LIKE expression.
func (ValExprBuilder) NotRegExp ¶
func (b ValExprBuilder) NotRegExp(val interface{}) BoolExprBuilder
NotRegExp creates a NOT REGEXP expression.
func (ValExprBuilder) NullSafeEq ¶
func (b ValExprBuilder) NullSafeEq(val interface{}) BoolExprBuilder
NullSafeEq creates a <=> comparison expression that is safe for use when the either the left or right value of the expression may be NULL. The null safe equal operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
func (ValExprBuilder) Plus ¶
func (b ValExprBuilder) Plus(expr interface{}) ValExprBuilder
Plus creates a + expression.
func (ValExprBuilder) RegExp ¶
func (b ValExprBuilder) RegExp(val interface{}) BoolExprBuilder
RegExp creates a REGEXP expression.
type ValExprs ¶
type ValExprs []ValExpr
ValExprs represents a list of value expressions. It's not a valid expression because it's not parenthesized.
type ValTuple ¶
type ValTuple struct {
Exprs ValExprs
}
ValTuple represents a tuple of actual values.
type Where ¶
Where represents a WHERE or HAVING clause.
type Writer ¶
type Writer interface { io.Writer // WriteBytes writes a string of unprintable value. WriteBytes(node BytesVal) error // WriteEncoded writes an already encoded value. WriteEncoded(node EncodedVal) error // WriteNum writes a number value. WriteNum(node NumVal) error // WriteRaw writes a raw Go value. WriteRaw(node RawVal) error // WriteStr writes a SQL string value. WriteStr(node StrVal) error }
Writer defines an interface for writing a AST as SQL.