Documentation

Overview

    A library for generating sql programmatically.

    SQL COMPATIBILITY NOTE: sqlbuilder is designed to generate valid MySQL sql statements. The generated statements may not work for other sql variants. For instances, the generated statements does not currently work for PostgreSQL since column identifiers are escaped with backquotes. Patches to support other sql flavors are welcome! (see https://godropbox/issues/33 for additional details).

    Known limitations for SELECT queries:

    - does not support subqueries (since mysql is bad at it)
    - does not currently support join table alias (and hence self join)
    - does not support NATURAL joins and join USING
    

    Known limitation for INSERT statements:

    - does not support "INSERT INTO SELECT"
    

    Known limitation for UPDATE statements:

    - does not support update without a WHERE clause (since it is dangerous)
    - does not support multi-table update
    

    Known limitation for DELETE statements:

    - does not support delete without a WHERE clause (since it is dangerous)
    - does not support multi-table delete
    

    Query building functions for expression components

    Example
    Output:
    
    SELECT `sfj`.`ns_id`,`sfj`.`sjid`,`parent_prefix`.`prefix`,`sfj`.`filename` FROM `shard1`.`sfj` LEFT JOIN `shard1`.`parent_prefix` ON `parent_prefix`.`ns_id`=`sfj`.`ns_id` WHERE (`sfj`.`ns_id`=456 AND `sfj`.`sjid` IN (1,2,3))
    

    Index

    Examples

    Constants

    View Source
    const (
    	INNER_JOIN joinType = iota
    	LEFT_JOIN
    	RIGHT_JOIN
    )

    Variables

    This section is empty.

    Functions

    func EscapeForLike

    func EscapeForLike(s string) string

    Types

    type BoolExpression

    type BoolExpression interface {
    	Clause
    	// contains filtered or unexported methods
    }

    func And

    func And(expressions ...BoolExpression) BoolExpression

      Returns a representation of "c[0] AND ... AND c[n-1]" for c in clauses

      func Eq

      func Eq(lhs, rhs Expression) BoolExpression

        Returns a representation of "a=b"

        func EqL

        func EqL(lhs Expression, val interface{}) BoolExpression

          Returns a representation of "a=b", where b is a literal

          func Gt

          func Gt(lhs, rhs Expression) BoolExpression

            Returns a representation of "a>b"

            func GtL

            func GtL(lhs Expression, val interface{}) BoolExpression

              Returns a representation of "a>b", where b is a literal

              func Gte

              func Gte(lhs, rhs Expression) BoolExpression

                Returns a representation of "a>=b"

                func GteL

                func GteL(lhs Expression, val interface{}) BoolExpression

                  Returns a representation of "a>=b", where b is a literal

                  func In

                  func In(lhs Expression, valList interface{}) BoolExpression

                    Returns a representation of "a IN (b[0], ..., b[n-1])", where b is a list of literals valList must be a slice type

                    func Like

                    func Like(lhs, rhs Expression) BoolExpression

                    func LikeL

                    func LikeL(lhs Expression, val string) BoolExpression

                    func Lt

                      Returns a representation of "a<b"

                      func LtL

                      func LtL(lhs Expression, val interface{}) BoolExpression

                        Returns a representation of "a<b", where b is a literal

                        func Lte

                        func Lte(lhs, rhs Expression) BoolExpression

                          Returns a representation of "a<=b"

                          func LteL

                          func LteL(lhs Expression, val interface{}) BoolExpression

                            Returns a representation of "a<=b", where b is a literal

                            func Neq

                            func Neq(lhs, rhs Expression) BoolExpression

                              Returns a representation of "a!=b"

                              func NeqL

                              func NeqL(lhs Expression, val interface{}) BoolExpression

                                Returns a representation of "a!=b", where b is a literal

                                func Not

                                  Returns a representation of "not expr"

                                  func Or

                                  func Or(expressions ...BoolExpression) BoolExpression

                                    Returns a representation of "c[0] OR ... OR c[n-1]" for c in clauses

                                    func Regexp

                                    func Regexp(lhs, rhs Expression) BoolExpression

                                    func RegexpL

                                    func RegexpL(lhs Expression, val string) BoolExpression

                                    type Charset

                                    type Charset string

                                      Representation of MySQL charsets

                                      const (
                                      	UTF8 Charset = "utf8"
                                      )

                                      type Clause

                                      type Clause interface {
                                      	SerializeSql(out *bytes.Buffer) error
                                      }

                                      type Collation

                                      type Collation string
                                      const (
                                      	UTF8CaseInsensitive Collation = "utf8_unicode_ci"
                                      	UTF8CaseSensitive   Collation = "utf8_unicode"
                                      	UTF8Binary          Collation = "utf8_bin"
                                      )

                                      type Column

                                      type Column interface {
                                      	Name() string
                                      	// Serialization for use in column lists
                                      	SerializeSqlForColumnList(out *bytes.Buffer) error
                                      	// Serialization for use in an expression (Clause)
                                      	SerializeSql(out *bytes.Buffer) error
                                      	// contains filtered or unexported methods
                                      }

                                        Representation of a table for query generation

                                        func Alias

                                        func Alias(name string, c Expression) Column

                                          Representation of aliased clauses (expression AS name)

                                          type DeleteStatement

                                          type DeleteStatement interface {
                                          	Statement
                                          
                                          	Where(expression BoolExpression) DeleteStatement
                                          	OrderBy(clauses ...OrderByClause) DeleteStatement
                                          	Limit(limit int64) DeleteStatement
                                          	Comment(comment string) DeleteStatement
                                          }

                                          type Expression

                                          type Expression interface {
                                          	Clause
                                          	// contains filtered or unexported methods
                                          }

                                            An expression

                                            func Add

                                            func Add(expressions ...Expression) Expression

                                              Returns a representation of "c[0] + ... + c[n-1]" for c in clauses

                                              func BitAnd

                                              func BitAnd(lhs, rhs Expression) Expression

                                              func BitOr

                                              func BitOr(lhs, rhs Expression) Expression

                                              func BitXor

                                              func BitXor(lhs, rhs Expression) Expression

                                              func ColumnValue

                                              func ColumnValue(col NonAliasColumn) Expression

                                              func Div

                                              func Div(expressions ...Expression) Expression

                                                Returns a representation of "c[0] / ... / c[n-1]" for c in clauses

                                                func If

                                                func If(conditional BoolExpression,
                                                	trueExpression Expression,
                                                	falseExpression Expression) Expression

                                                  Returns a representation of an if-expression, of the form:

                                                  IF (BOOLEAN TEST, VALUE-IF-TRUE, VALUE-IF-FALSE)
                                                  

                                                  func Interval

                                                  func Interval(duration time.Duration) Expression

                                                    Interval returns a representation of duration in a form "INTERVAL `hour:min:sec:microsec` HOUR_MICROSECOND"

                                                    func Literal

                                                    func Literal(v interface{}) Expression

                                                      Returns an escaped literal string

                                                      func Minus

                                                      func Minus(lhs, rhs Expression) Expression

                                                      func Mul

                                                      func Mul(expressions ...Expression) Expression

                                                        Returns a representation of "c[0] * ... * c[n-1]" for c in clauses

                                                        func Plus

                                                        func Plus(lhs, rhs Expression) Expression

                                                        func SqlFunc

                                                        func SqlFunc(funcName string, expressions ...Expression) Expression

                                                          Returns a representation of sql function call "func_call(c[0], ..., c[n-1])

                                                          func Sub

                                                          func Sub(expressions ...Expression) Expression

                                                            Returns a representation of "c[0] - ... - c[n-1]" for c in clauses

                                                            func Tuple

                                                            func Tuple(exprs ...Expression) Expression

                                                            type GtidNextStatement

                                                            type GtidNextStatement interface {
                                                            	Statement
                                                            }

                                                              SetGtidNextStatement returns a SQL statement that can be used to explicitly set the next GTID.

                                                              func NewGtidNextStatement

                                                              func NewGtidNextStatement(sid []byte, gno uint64) GtidNextStatement

                                                                Set GTID_NEXT statement returns a SQL statement that can be used to explicitly set the next GTID.

                                                                type InsertStatement

                                                                type InsertStatement interface {
                                                                	Statement
                                                                
                                                                	// Add a row of values to the insert statement.
                                                                	Add(row ...Expression) InsertStatement
                                                                	AddOnDuplicateKeyUpdate(col NonAliasColumn, expr Expression) InsertStatement
                                                                	Comment(comment string) InsertStatement
                                                                	IgnoreDuplicates(ignore bool) InsertStatement
                                                                }

                                                                type LockStatement

                                                                type LockStatement interface {
                                                                	Statement
                                                                
                                                                	AddReadLock(table *Table) LockStatement
                                                                	AddWriteLock(table *Table) LockStatement
                                                                }

                                                                  LockStatement is used to take Read/Write lock on tables. See http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

                                                                  func NewLockStatement

                                                                  func NewLockStatement() LockStatement

                                                                    NewLockStatement returns a SQL representing empty set of locks. You need to use AddReadLock/AddWriteLock to add tables that need to be locked. NOTE: You need at least one lock in the set for it to be a valid statement.

                                                                    type NonAliasColumn

                                                                    type NonAliasColumn interface {
                                                                    	Column
                                                                    	// contains filtered or unexported methods
                                                                    }

                                                                      A column that can be refer to outside of the projection list

                                                                      func BoolColumn

                                                                      func BoolColumn(name string, nullable NullableColumn) NonAliasColumn

                                                                        Representation of TINYINT used as a bool This function will panic if name is not valid

                                                                        func BytesColumn

                                                                        func BytesColumn(name string, nullable NullableColumn) NonAliasColumn

                                                                          Representation of VARBINARY/BLOB columns This function will panic if name is not valid

                                                                          func DateTimeColumn

                                                                          func DateTimeColumn(name string, nullable NullableColumn) NonAliasColumn

                                                                            Representation of DateTime columns This function will panic if name is not valid

                                                                            func DoubleColumn

                                                                            func DoubleColumn(name string, nullable NullableColumn) NonAliasColumn

                                                                              Representation of any double column This function will panic if name is not valid

                                                                              func IntColumn

                                                                              func IntColumn(name string, nullable NullableColumn) NonAliasColumn

                                                                                Representation of any integer column This function will panic if name is not valid

                                                                                func StrColumn

                                                                                func StrColumn(
                                                                                	name string,
                                                                                	charset Charset,
                                                                                	collation Collation,
                                                                                	nullable NullableColumn) NonAliasColumn

                                                                                  Representation of VARCHAR/TEXT columns This function will panic if name is not valid

                                                                                  type NullableColumn

                                                                                  type NullableColumn bool
                                                                                  const (
                                                                                  	Nullable    NullableColumn = true
                                                                                  	NotNullable NullableColumn = false
                                                                                  )

                                                                                  type OrderByClause

                                                                                  type OrderByClause interface {
                                                                                  	Clause
                                                                                  	// contains filtered or unexported methods
                                                                                  }

                                                                                    A clause that can be used in order by

                                                                                    func Asc

                                                                                    func Asc(expression Expression) OrderByClause

                                                                                    func Desc

                                                                                    func Desc(expression Expression) OrderByClause

                                                                                    type Projection

                                                                                    type Projection interface {
                                                                                    	Clause
                                                                                    
                                                                                    	SerializeSqlForColumnList(out *bytes.Buffer) error
                                                                                    	// contains filtered or unexported methods
                                                                                    }

                                                                                      A clause that is selectable.

                                                                                      type ReadableTable

                                                                                      type ReadableTable interface {
                                                                                      	// Returns the list of columns that are in the current table expression.
                                                                                      	Columns() []NonAliasColumn
                                                                                      
                                                                                      	// Generates the sql string for the current table expression.  Note: the
                                                                                      	// generated string may not be a valid/executable sql statement.
                                                                                      	// The database is the name of the database the table is on
                                                                                      	SerializeSql(database string, out *bytes.Buffer) error
                                                                                      
                                                                                      	// Generates a select query on the current table.
                                                                                      	Select(projections ...Projection) SelectStatement
                                                                                      
                                                                                      	// Creates a inner join table expression using onCondition.
                                                                                      	InnerJoinOn(table ReadableTable, onCondition BoolExpression) ReadableTable
                                                                                      
                                                                                      	// Creates a left join table expression using onCondition.
                                                                                      	LeftJoinOn(table ReadableTable, onCondition BoolExpression) ReadableTable
                                                                                      
                                                                                      	// Creates a right join table expression using onCondition.
                                                                                      	RightJoinOn(table ReadableTable, onCondition BoolExpression) ReadableTable
                                                                                      }

                                                                                        The sql table read interface. NOTE: NATURAL JOINs, and join "USING" clause are not supported.

                                                                                        func InnerJoinOn

                                                                                        func InnerJoinOn(
                                                                                        	lhs ReadableTable,
                                                                                        	rhs ReadableTable,
                                                                                        	onCondition BoolExpression) ReadableTable

                                                                                        func LeftJoinOn

                                                                                        func LeftJoinOn(
                                                                                        	lhs ReadableTable,
                                                                                        	rhs ReadableTable,
                                                                                        	onCondition BoolExpression) ReadableTable

                                                                                        func RightJoinOn

                                                                                        func RightJoinOn(
                                                                                        	lhs ReadableTable,
                                                                                        	rhs ReadableTable,
                                                                                        	onCondition BoolExpression) ReadableTable

                                                                                        type SelectStatement

                                                                                        type SelectStatement interface {
                                                                                        	Statement
                                                                                        
                                                                                        	Where(expression BoolExpression) SelectStatement
                                                                                        	AndWhere(expression BoolExpression) SelectStatement
                                                                                        	GroupBy(expressions ...Expression) SelectStatement
                                                                                        	OrderBy(clauses ...OrderByClause) SelectStatement
                                                                                        	Limit(limit int64) SelectStatement
                                                                                        	Distinct() SelectStatement
                                                                                        	WithSharedLock() SelectStatement
                                                                                        	ForUpdate() SelectStatement
                                                                                        	Offset(offset int64) SelectStatement
                                                                                        	Comment(comment string) SelectStatement
                                                                                        	Copy() SelectStatement
                                                                                        }

                                                                                        type Statement

                                                                                        type Statement interface {
                                                                                        	// String returns generated SQL as string.
                                                                                        	String(database string) (sql string, err error)
                                                                                        }

                                                                                        type Table

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

                                                                                        func NewTable

                                                                                        func NewTable(name string, columns ...NonAliasColumn) *Table

                                                                                          Defines a physical table in the database that is both readable and writable. This function will panic if name is not valid

                                                                                          func (*Table) C

                                                                                          func (t *Table) C(name string) NonAliasColumn

                                                                                            Returns a pseudo column representation of the column name. Error checking is deferred to SerializeSql.

                                                                                            func (*Table) Columns

                                                                                            func (t *Table) Columns() []NonAliasColumn

                                                                                              Returns a list of the table's columns

                                                                                              func (*Table) Delete

                                                                                              func (t *Table) Delete() DeleteStatement

                                                                                              func (*Table) ForceIndex

                                                                                              func (t *Table) ForceIndex(index string) *Table

                                                                                                Returns a copy of this table, but with the specified index forced.

                                                                                                func (*Table) InnerJoinOn

                                                                                                func (t *Table) InnerJoinOn(
                                                                                                	table ReadableTable,
                                                                                                	onCondition BoolExpression) ReadableTable

                                                                                                  Creates a inner join table expression using onCondition.

                                                                                                  func (*Table) Insert

                                                                                                  func (t *Table) Insert(columns ...NonAliasColumn) InsertStatement

                                                                                                  func (*Table) LeftJoinOn

                                                                                                  func (t *Table) LeftJoinOn(
                                                                                                  	table ReadableTable,
                                                                                                  	onCondition BoolExpression) ReadableTable

                                                                                                    Creates a left join table expression using onCondition.

                                                                                                    func (*Table) Name

                                                                                                    func (t *Table) Name() string

                                                                                                      Returns the table's name in the database

                                                                                                      func (*Table) Projections

                                                                                                      func (t *Table) Projections() []Projection

                                                                                                        Returns all columns for a table as a slice of projections

                                                                                                        func (*Table) RightJoinOn

                                                                                                        func (t *Table) RightJoinOn(
                                                                                                        	table ReadableTable,
                                                                                                        	onCondition BoolExpression) ReadableTable

                                                                                                          Creates a right join table expression using onCondition.

                                                                                                          func (*Table) Select

                                                                                                          func (t *Table) Select(projections ...Projection) SelectStatement

                                                                                                            Generates a select query on the current table.

                                                                                                            func (*Table) SerializeSql

                                                                                                            func (t *Table) SerializeSql(database string, out *bytes.Buffer) error

                                                                                                              Generates the sql string for the current table expression. Note: the generated string may not be a valid/executable sql statement.

                                                                                                              func (*Table) Update

                                                                                                              func (t *Table) Update() UpdateStatement

                                                                                                              type UnionStatement

                                                                                                              type UnionStatement interface {
                                                                                                              	Statement
                                                                                                              
                                                                                                              	// Warning! You cannot include table names for the next 4 clauses, or
                                                                                                              	// you'll get errors like:
                                                                                                              	//   Table 'server_file_journal' from one of the SELECTs cannot be used in
                                                                                                              	//   global ORDER clause
                                                                                                              	Where(expression BoolExpression) UnionStatement
                                                                                                              	AndWhere(expression BoolExpression) UnionStatement
                                                                                                              	GroupBy(expressions ...Expression) UnionStatement
                                                                                                              	OrderBy(clauses ...OrderByClause) UnionStatement
                                                                                                              
                                                                                                              	Limit(limit int64) UnionStatement
                                                                                                              	Offset(offset int64) UnionStatement
                                                                                                              }

                                                                                                                By default, rows selected by a UNION statement are out-of-order If you have an ORDER BY on an inner SELECT statement, the only thing it affects is the LIMIT clause on that inner statement (the ordering will still be out-of-order).

                                                                                                                func Union

                                                                                                                func Union(selects ...SelectStatement) UnionStatement

                                                                                                                func UnionAll

                                                                                                                func UnionAll(selects ...SelectStatement) UnionStatement

                                                                                                                type UnlockStatement

                                                                                                                type UnlockStatement interface {
                                                                                                                	Statement
                                                                                                                }

                                                                                                                  UnlockStatement can be used to release table locks taken using LockStatement. NOTE: You can not selectively release a lock and continue to hold lock on another table. UnlockStatement releases all the lock held in the current session.

                                                                                                                  func NewUnlockStatement

                                                                                                                  func NewUnlockStatement() UnlockStatement

                                                                                                                    NewUnlockStatement returns SQL statement that can be used to release table locks grabbed by the current session.

                                                                                                                    type UpdateStatement

                                                                                                                    type UpdateStatement interface {
                                                                                                                    	Statement
                                                                                                                    
                                                                                                                    	Set(column NonAliasColumn, expression Expression) UpdateStatement
                                                                                                                    	Where(expression BoolExpression) UpdateStatement
                                                                                                                    	OrderBy(clauses ...OrderByClause) UpdateStatement
                                                                                                                    	Limit(limit int64) UpdateStatement
                                                                                                                    	Comment(comment string) UpdateStatement
                                                                                                                    }

                                                                                                                    type WritableTable

                                                                                                                    type WritableTable interface {
                                                                                                                    	// Returns the list of columns that are in the table.
                                                                                                                    	Columns() []NonAliasColumn
                                                                                                                    
                                                                                                                    	// Generates the sql string for the current table expression.  Note: the
                                                                                                                    	// generated string may not be a valid/executable sql statement.
                                                                                                                    	// The database is the name of the database the table is on
                                                                                                                    	SerializeSql(database string, out *bytes.Buffer) error
                                                                                                                    
                                                                                                                    	Insert(columns ...NonAliasColumn) InsertStatement
                                                                                                                    	Update() UpdateStatement
                                                                                                                    	Delete() DeleteStatement
                                                                                                                    }

                                                                                                                      The sql table write interface.