Back to godoc.org

Package sqlbuilder

v0.0.0-...-8a58875
Latest Go to latest

The highest tagged major version is .

Published: Oct 22, 2014 | License: BSD-3-Clause | Module: github.com/1fei/godropbox

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://github.com/dropbox/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:

package main

import (
	"fmt"
	sb "github.com/dropbox/godropbox/database/sqlbuilder"
)

func main() {
	t1 := sb.NewTable(
		"parent_prefix",
		sb.IntColumn("ns_id", false),
		sb.IntColumn("hash", false),
		sb.StrColumn(
			"prefix",
			sb.UTF8,
			sb.UTF8CaseInsensitive,
			false))

	t2 := sb.NewTable(
		"sfj",
		sb.IntColumn("ns_id", false),
		sb.IntColumn("sjid", false),
		sb.StrColumn(
			"filename",
			sb.UTF8,
			sb.UTF8CaseInsensitive,
			false))

	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, sb.Eq(ns_id1, ns_id2))
	q := join.Select(ns_id2, sjid, prefix, filename).Where(
		sb.And(sb.EqL(ns_id2, 123), sb.In(sjid, in)))
	fmt.Println(q.String("shard1"))
}

Index

Examples

Constants

const (
	INNER_JOIN joinType = iota
	LEFT_JOIN
	RIGHT_JOIN
)

func EscapeForLike

func EscapeForLike(s string) string

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

func Lt(lhs Expression, rhs Expression) BoolExpression

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

func Not(expr BoolExpression) BoolExpression

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

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

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

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

func BytesColumn

func BytesColumn(name string, nullable bool) NonAliasColumn

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

func DateTimeColumn

func DateTimeColumn(name string, nullable bool) NonAliasColumn

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

func DoubleColumn

func DoubleColumn(name string, nullable bool) NonAliasColumn

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

func IntColumn

func IntColumn(name string, nullable bool) 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 bool) NonAliasColumn

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

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 on_condition.
	InnerJoinOn(table ReadableTable, on_condition BoolExpression) ReadableTable

	// Creates a left join table expression using on_condition.
	LeftJoinOn(table ReadableTable, on_condition BoolExpression) ReadableTable

	// Creates a right join table expression using on_condition.
	RightJoinOn(table ReadableTable, on_condition 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,
	on_condition BoolExpression) ReadableTable

func LeftJoinOn

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

func RightJoinOn

func RightJoinOn(
	lhs ReadableTable,
	rhs ReadableTable,
	on_condition 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
	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)
}

func Union

func Union(selects ...SelectStatement) Statement

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

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

Creates a inner join table expression using on_condition.

func (*Table) Insert

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

func (*Table) LeftJoinOn

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

Creates a left join table expression using on_condition.

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,
	on_condition BoolExpression) ReadableTable

Creates a right join table expression using on_condition.

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

Package Files

Documentation was rendered with GOOS=linux and GOARCH=amd64.

Jump to identifier

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to identifier