Pkg.go.dev is a new destination for Go discovery & docs. Check it out at pkg.go.dev/github.com/dropbox/godropbox/database/sqlbuilder and share your feedback.

package sqlbuilder

import "github.com/dropbox/godropbox/database/sqlbuilder"

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 Code: t1 := NewTable( "parent_prefix", IntColumn("ns_id", NotNullable), IntColumn("hash", NotNullable), StrColumn("prefix", UTF8, UTF8CaseInsensitive, NotNullable)) t2 := NewTable( "sfj", IntColumn("ns_id", NotNullable), IntColumn("sjid", NotNullable), StrColumn("filename", UTF8, UTF8CaseInsensitive, NotNullable)) ns_id1 := t1.C("ns_id") prefix := t1.C("prefix") ns_id2 := t2.C("ns_id") sjid := t2.C("sjid") filename := t2.C("filename") in := []int32{1, 2, 3} join := t2.LeftJoinOn(t1, Eq(ns_id1, ns_id2)) q := join.Select(ns_id2, sjid, prefix, filename).Where( And(EqL(ns_id2, 456), In(sjid, in))) text, _ := q.String("shard1") fmt.Println(text) 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))

column.go doc.go expression.go statement.go table.go test_utils.go types.go

❖ const ( INNER_JOIN joinType = iota LEFT_JOIN RIGHT_JOIN )

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

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

❖ func Eq(lhs, rhs Expression) BoolExpression

Returns a representation of "a=b"

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

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

❖ func Gt(lhs, rhs Expression) BoolExpression

Returns a representation of "a>b"

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

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

❖ func Gte(lhs, rhs Expression) BoolExpression

Returns a representation of "a>=b"

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

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

❖ 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(lhs, rhs Expression) BoolExpression

❖ func LikeL(lhs Expression, val string) BoolExpression

❖ func Lt(lhs Expression, rhs Expression) BoolExpression

Returns a representation of "a<b"

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

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

❖ func Lte(lhs, rhs Expression) BoolExpression

Returns a representation of "a<=b"

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

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

❖ func Neq(lhs, rhs Expression) BoolExpression

Returns a representation of "a!=b"

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

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

Returns a representation of "not expr"

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

❖ func Regexp(lhs, rhs Expression) BoolExpression

❖ func RegexpL(lhs Expression, val string) BoolExpression

❖ type Charset string

Representation of MySQL charsets

❖ const ( UTF8 Charset = "utf8" )

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

❖ type Collation string

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

❖ 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(name string, c Expression) Column

Representation of aliased clauses (expression AS name)

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

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

An expression

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

❖ func BitAnd(lhs, rhs Expression) Expression

❖ func BitOr(lhs, rhs Expression) Expression

❖ func BitXor(lhs, rhs Expression) Expression

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

❖ 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)

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

Returns an escaped literal string

❖ func Minus(lhs, rhs Expression) Expression

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

❖ func Plus(lhs, rhs Expression) Expression

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

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

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

❖ type GtidNextStatement interface { Statement }

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

❖ 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 interface { Statement // Add a row of values to the insert statement. Add (row ...Expression) InsertStatement (col NonAliasColumn, expr Expression) InsertStatement (comment string) InsertStatement IgnoreDuplicates (ignore bool) InsertStatement }

❖ 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

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 interface { Column // contains filtered or unexported methods }

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

❖ 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(name string, nullable NullableColumn) NonAliasColumn

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

❖ func DateTimeColumn(name string, nullable NullableColumn) NonAliasColumn

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

❖ func DoubleColumn(name string, nullable NullableColumn) NonAliasColumn

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

❖ func IntColumn(name string, nullable NullableColumn) NonAliasColumn

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

❖ 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 bool

❖ const ( Nullable NullableColumn = true NotNullable NullableColumn = false )

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

A clause that can be used in order by

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

A clause that is selectable.

❖ 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( lhs ReadableTable, rhs ReadableTable, onCondition BoolExpression) ReadableTable

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

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

❖ 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 () SelectStatement Offset (offset int64) SelectStatement (comment string) SelectStatement Copy () SelectStatement }

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

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

❖ 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

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

Returns a list of the table's columns

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

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

Creates a inner join table expression using onCondition.

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

Creates a left join table expression using onCondition.

Returns the table's name in the database

Returns all columns for a table as a slice of projections

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

Creates a right join table expression using onCondition.

Generates a select query on the current table.

❖ 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.

❖ 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).

❖ 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.

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

❖ type UpdateStatement interface { Statement (column NonAliasColumn, expression Expression) UpdateStatement (expression BoolExpression) UpdateStatement (clauses ...OrderByClause) UpdateStatement (limit int64) UpdateStatement (comment string) UpdateStatement }

❖ 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 () UpdateStatement Delete () DeleteStatement }

The sql table write interface.