godropbox: github.com/dropbox/godropbox/database/sqlbuilder Index | Examples | Files

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

Index

Examples

Package Files

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

Constants

❖
const (
    INNER_JOIN joinType = iota
    LEFT_JOIN
    RIGHT_JOIN
)

func EscapeForLike Uses

❖
func EscapeForLike(s string) string

type BoolExpression Uses

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

func And Uses

❖
func And(expressions ...BoolExpression) BoolExpression

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

func Eq Uses

❖
func Eq(lhs, rhs Expression) BoolExpression

Returns a representation of "a=b"

func EqL Uses

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

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

func Gt Uses

❖
func Gt(lhs, rhs Expression) BoolExpression

Returns a representation of "a>b"

func GtL Uses

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

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

func Gte Uses

❖
func Gte(lhs, rhs Expression) BoolExpression

Returns a representation of "a>=b"

func GteL Uses

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

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

func In Uses

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

❖
func Like(lhs, rhs Expression) BoolExpression

func LikeL Uses

❖
func LikeL(lhs Expression, val string) BoolExpression

func Lt Uses

❖
func Lt(lhs Expression, rhs Expression) BoolExpression

Returns a representation of "a<b"

func LtL Uses

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

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

func Lte Uses

❖
func Lte(lhs, rhs Expression) BoolExpression

Returns a representation of "a<=b"

func LteL Uses

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

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

func Neq Uses

❖
func Neq(lhs, rhs Expression) BoolExpression

Returns a representation of "a!=b"

func NeqL Uses

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

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

func Not Uses

❖
func Not(expr BoolExpression) BoolExpression

Returns a representation of "not expr"

func Or Uses

❖
func Or(expressions ...BoolExpression) BoolExpression

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

func Regexp Uses

❖
func Regexp(lhs, rhs Expression) BoolExpression

func RegexpL Uses

❖
func RegexpL(lhs Expression, val string) BoolExpression

type Charset Uses

❖
type Charset string

Representation of MySQL charsets

❖
const (
    UTF8 Charset = "utf8"
)

type Clause Uses

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

type Collation Uses

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

type Column Uses

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

❖
func Alias(name string, c Expression) Column

Representation of aliased clauses (expression AS name)

type DeleteStatement Uses

❖
type DeleteStatement interface {
    Statement

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

type Expression Uses

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

An expression

func Add Uses

❖
func Add(expressions ...Expression) Expression

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

func BitAnd Uses

❖
func BitAnd(lhs, rhs Expression) Expression

func BitOr Uses

❖
func BitOr(lhs, rhs Expression) Expression

func BitXor Uses

❖
func BitXor(lhs, rhs Expression) Expression

func ColumnValue Uses

❖
func ColumnValue(col NonAliasColumn) Expression

func Div Uses

❖
func Div(expressions ...Expression) Expression

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

func If Uses

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

❖
func Interval(duration time.Duration) Expression

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

func Literal Uses

❖
func Literal(v interface{}) Expression

Returns an escaped literal string

func Minus Uses

❖
func Minus(lhs, rhs Expression) Expression

func Mul Uses

❖
func Mul(expressions ...Expression) Expression

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

func Plus Uses

❖
func Plus(lhs, rhs Expression) Expression

func SqlFunc Uses

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

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

func Sub Uses

❖
func Sub(expressions ...Expression) Expression

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

func Tuple Uses

❖
func Tuple(exprs ...Expression) Expression

type GtidNextStatement Uses

❖
type GtidNextStatement interface {
    Statement
}

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

func NewGtidNextStatement Uses

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

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

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

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

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

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

func BoolColumn Uses

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

❖
func BytesColumn(name string, nullable NullableColumn) NonAliasColumn

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

func DateTimeColumn Uses

❖
func DateTimeColumn(name string, nullable NullableColumn) NonAliasColumn

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

func DoubleColumn Uses

❖
func DoubleColumn(name string, nullable NullableColumn) NonAliasColumn

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

func IntColumn Uses

❖
func IntColumn(name string, nullable NullableColumn) NonAliasColumn

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

func StrColumn Uses

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

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

type OrderByClause Uses

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

A clause that can be used in order by

func Asc Uses

❖
func Asc(expression Expression) OrderByClause

func Desc Uses

❖
func Desc(expression Expression) OrderByClause

type Projection Uses

❖
type Projection interface {
    Clause

    SerializeSqlForColumnList(out *bytes.Buffer) error
    // contains filtered or unexported methods
}

A clause that is selectable.

type ReadableTable Uses

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

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

func LeftJoinOn Uses

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

func RightJoinOn Uses

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

type SelectStatement Uses

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

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

type Table Uses

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

func NewTable Uses

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

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

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

Returns a list of the table's columns

func (*Table) Delete Uses

❖
func (t *Table) Delete() DeleteStatement

func (*Table) ForceIndex Uses

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

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

func (*Table) InnerJoinOn Uses

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

Creates a inner join table expression using onCondition.

func (*Table) Insert Uses

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

func (*Table) LeftJoinOn Uses

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

Creates a left join table expression using onCondition.

func (*Table) Name Uses

❖
func (t *Table) Name() string

Returns the table's name in the database

func (*Table) Projections Uses

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

Returns all columns for a table as a slice of projections

func (*Table) RightJoinOn Uses

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

Creates a right join table expression using onCondition.

func (*Table) Select Uses

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

Generates a select query on the current table.

func (*Table) SerializeSql Uses

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

❖
func (t *Table) Update() UpdateStatement

type UnionStatement Uses

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

❖
func Union(selects ...SelectStatement) UnionStatement

func UnionAll Uses

❖
func UnionAll(selects ...SelectStatement) UnionStatement

type UnlockStatement Uses

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

❖
func NewUnlockStatement() UnlockStatement

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

type UpdateStatement Uses

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

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

Package sqlbuilder imports 9 packages (graph). Updated 2017-10-13. Refresh now. Tools for package owners.