q

package module
Version: v0.0.0-...-8b6df7a Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Dec 28, 2015 License: MIT Imports: 3 Imported by: 1

Documentation

Overview

Package q implements a SQL builder.

Example
package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	sel := q.Select().From(
		user,
	).Column(
		user.C("id"),
		user.C("name"),
	).Where(
		q.Eq(user.C("age"), 18),
	)
	// You can use sel by performing the following steps.
	// sql, args := sel.ToSQL()
	// rows, err := db.Query(sql, args...)
	// ...
	fmt.Println(sel)
}
Output:

SELECT "user"."id", "user"."name" FROM "user" WHERE "user"."age" = ? [18]
Example (Complicated)
package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user", "u")
	age := user.C("age")
	sel := q.Select().From(user).Column(
		q.Sum(
			q.Case().When(
				q.And(
					q.Gte(age, 13),
					q.Lte(age, 19),
				),
				1,
			).Else(0),
		).C("teen"),
		q.Sum(q.Case().When(q.Gte(age, 20), 1).Else(0)).C("adult"),
	)
	fmt.Println(sel)
}
Output:

SELECT SUM(CASE WHEN ("u"."age" >= ?)AND("u"."age" <= ?) THEN ? ELSE ? END) AS "teen", SUM(CASE WHEN "u"."age" >= ? THEN ? ELSE ? END) AS "adult" FROM "user" AS "u" [13 19 1 0 20 1 0]
Example (Prepared)
package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	sel := q.Select().From(
		user,
	).Column(
		user.C("id"),
		user.C("name"),
	).Where(
		q.Eq(user.C("age"), q.V(18, "findAge")),
	)
	// You can use sel by performing the following steps.
	// sql, argsBuilderGenerator := sel.ToPrepared()
	// stmt, err := db.Prepare(sql)
	// ...
	// ab := argsBuilderGenerator()
	// ab.Set("findAge", 24)
	// rows, err := stmt.Query(ab.Args...)
	fmt.Println(sel)
}
Output:

SELECT "user"."id", "user"."name" FROM "user" WHERE "user"."age" = ? [18]
Example (Unsafe)
package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user", "u")
	age := user.C("age")
	sel := q.Select().From(user).Column(
		q.Unsafe(`SUM(CASE WHEN (`, age, ` >= 13)AND(`, age, ` <= 19) THEN 1 ELSE 0 END)`).C("teen"),
		q.Unsafe(`SUM(CASE WHEN `, age, ` >= 20 THEN 1 ELSE 0 END)`).C("adult"),
	)
	fmt.Println(sel)

	lastName := user.C("last_name")
	maliciousUserInput := "' OR '' = '"
	sel = q.Select().From(user).Where(
		// Safe
		q.Eq(lastName, maliciousUserInput),
		// Safe
		q.Unsafe(lastName, ` = `, q.V(maliciousUserInput)),
		// Unsafe - DO NOT THIS
		q.Unsafe(lastName, ` = '`, maliciousUserInput, `'`),
	)
	fmt.Println(sel)
}
Output:

SELECT SUM(CASE WHEN ("u"."age" >= 13)AND("u"."age" <= 19) THEN 1 ELSE 0 END) AS "teen", SUM(CASE WHEN "u"."age" >= 20 THEN 1 ELSE 0 END) AS "adult" FROM "user" AS "u" []
SELECT * FROM "user" AS "u" WHERE ("u"."last_name" = ?)AND("u"."last_name" = ?)AND("u"."last_name" = '' OR '' = '') [' OR '' = ' ' OR '' = ']

Index

Examples

Constants

This section is empty.

Variables

View Source
var (
	// DefaultDialect is default setting of builder's dialect.
	// When not set a Dialect in builder, this value is used.
	DefaultDialect qutil.Dialect

	// MySQL implements a dialect in MySQL.
	MySQL = qutil.MySQL
	// PostgreSQL implements a dialect in PostgreSQL.
	PostgreSQL = qutil.PostgreSQL
	// SQLite implements a dialect in SQLite.
	SQLite = qutil.SQLite
)

Functions

This section is empty.

Types

type Column

type Column interface {
	Expression

	// for internal use.
	WriteColumn(ctx *qutil.Context, buf []byte) []byte
	WriteDefinition(ctx *qutil.Context, buf []byte) []byte
}

Column represents database table column. You can create it from C or Table.C or Expression.C.

Example
package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	fmt.Println("q.C(name):               ", q.C("id"))
	fmt.Println("q.C(name, alias):        ", q.C("age", "ag"))
	fmt.Println("Table.C(name):           ", q.T("user").C("age"))
	fmt.Println("Table.C(name, alias):    ", q.T("user").C("age", "ag"))
	fmt.Println("Expression.C():          ", q.CountAll().C())
	fmt.Println("Expression.C(alias):     ", q.CountAll().C("cnt"))

	country := q.T("country")
	sel := q.Select().Column(
		country.C("name"),
	).From(
		country,
	).Where(
		q.Eq(country.C("id"), 100),
	)
	fmt.Println("*ZSelectBuilder.C():     ", sel.C())
	fmt.Println("*ZSelectBuilder.C(alias):", sel.C("cname"))
}
Output:

q.C(name):                "id" []
q.C(name, alias):         "age" AS "ag" []
Table.C(name):            "user"."age" []
Table.C(name, alias):     "user"."age" AS "ag" []
Expression.C():           COUNT(*) []
Expression.C(alias):      COUNT(*) AS "cnt" []
*ZSelectBuilder.C():      (SELECT "country"."name" FROM "country" WHERE "country"."id" = ?) [100]
*ZSelectBuilder.C(alias): (SELECT "country"."name" FROM "country" WHERE "country"."id" = ?) AS "cname" [100]

func C

func C(columnName string, aliasName ...string) Column

C creates Column.

Example

This is an example of how to use C. Actually, using Table.C is more useful in many cases than using C directly because Table.C adds the table name before column name.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	fmt.Println("name:        ", q.C("id"))
	fmt.Println("name + alias:", q.C("age", "ag"))
}
Output:

name:         "id" []
name + alias: "age" AS "ag" []

type Expression

type Expression interface {
	// C creates Column from Expression.
	C(aliasName ...string) Column
	// for internal use.
	WriteExpression(ctx *qutil.Context, buf []byte) []byte
}

Expression represents expressions.

Example

This is an example of how to use Expression.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	id := q.C("id")
	fmt.Println("Eq(id, 100):     ", q.Eq(id, 100))
	fmt.Println("Eq(id, nil):     ", q.Eq(id, nil))
	fmt.Println("Eq(id, []int):   ", q.Eq(id, []int{1, 2}))
	fmt.Println("In(id, []int):   ", q.In(id, []int{1, 2}))
	fmt.Println("Neq(id, 100):    ", q.Neq(id, 100))
	fmt.Println("Neq(id, nil):    ", q.Neq(id, nil))
	fmt.Println("Neq(id, []int):  ", q.Neq(id, []int{1, 2}))
	fmt.Println("NotIn(id, []int):", q.NotIn(id, []int{1, 2}))
	fmt.Println("Gt(id, 100):     ", q.Gt(id, 100))
	fmt.Println("Gte(id, 100):    ", q.Gte(id, 100))
	fmt.Println("Lt(id, 100):     ", q.Lt(id, 100))
	fmt.Println("Lte(id, 100):    ", q.Lte(id, 100))
}
Output:

Eq(id, 100):      "id" = ? [100]
Eq(id, nil):      "id" IS NULL []
Eq(id, []int):    "id" IN (?,?) [1 2]
In(id, []int):    "id" IN (?,?) [1 2]
Neq(id, 100):     "id" != ? [100]
Neq(id, nil):     "id" IS NOT NULL []
Neq(id, []int):   "id" NOT IN (?,?) [1 2]
NotIn(id, []int): "id" NOT IN (?,?) [1 2]
Gt(id, 100):      "id" > ? [100]
Gte(id, 100):     "id" >= ? [100]
Lt(id, 100):      "id" < ? [100]
Lte(id, 100):     "id" <= ? [100]

func Eq

func Eq(l, r interface{}) Expression

Eq creates Expression such as "l = r". But when you pass nil to one of a pair, Eq creates "x IS NULL" instead. In the same way, when you pass slice of any type to r, Eq creates "x IN (?)".

func Gt

func Gt(l, r interface{}) Expression

Gt creates Expression such as "l > r".

func Gte

func Gte(l, r interface{}) Expression

Gte creates Expression such as "l >= r".

func In

func In(l, r interface{}) Expression

In creates Expression such as "l IN r".

func Lt

func Lt(l, r interface{}) Expression

Lt creates Expression such as "l < r".

func Lte

func Lte(l, r interface{}) Expression

Lte creates Expression such as "l <= r".

func Neq

func Neq(l, r interface{}) Expression

Neq creates Expression such as "l != r". But when you pass nil to one of a pair, Neq creates "x IS NOT NULL" instead. In the same way, when you pass slice of any type to r, Neq creates "x NOT IN (?)".

func NotIn

func NotIn(l, r interface{}) Expression

NotIn creates Expression such as "l NOT IN r".

type Expressions

type Expressions Expression

Expressions represents combination of an expression.

Example

This is an example of how to use Expressions.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("And:       ", q.And(
		q.Eq(user.C("age"), 15),
		q.Eq(user.C("gender"), "female"),
		q.Eq(user.C("nickname"), "Shipon"),
	))
	fmt.Println("Or:        ", q.Or(
		q.Neq(user.C("name"), nil),
		q.Neq(user.C("nickname"), nil),
	))
	fmt.Println("And(empty):", q.And())
	fmt.Println("Or(empty): ", q.Or())
}
Output:

And:        ("user"."age" = ?)AND("user"."gender" = ?)AND("user"."nickname" = ?) [15 female Shipon]
Or:         ("user"."name" IS NOT NULL)OR("user"."nickname" IS NOT NULL) []
And(empty): ('empty' = 'AND') []
Or(empty):  ('empty' = 'OR') []

func And

func And(exprs ...Expression) Expressions

And creates Expression such as "(exprs[0])AND(exprs[1])AND(exprs[2])".

If you output expression which isn't adding Expression at all, it generates "('empty' = 'AND')".

func Or

func Or(exprs ...Expression) Expressions

Or creates Expression such as "(exprs[0])OR(exprs[1])OR(exprs[2])".

If you output expression which isn't adding Expression at all, it generates "('empty' = 'OR')".

type Function

type Function Expression

Function represents functions.

func AddInterval

func AddInterval(v interface{}, intervals ...Interval) Function

AddInterval creates Function such as "v + INTERVAL intervals[n] YEAR + ...".

func Avg

func Avg(v interface{}) Function

Avg creates Function such as "AVG(v)".

func CharLength

func CharLength(v interface{}) Function

CharLength creates Function such as "CHAR_LENGTH(v)".

func Count

func Count(v interface{}) Function

Count creates Function such as "COUNT(v)".

func CountAll

func CountAll() Function

CountAll creates Function "COUNT(*)".

func Max

func Max(v interface{}) Function

Max creates Function such as "MAX(v)".

func Min

func Min(v interface{}) Function

Min creates Function such as "MIN(v)".

func Now

func Now() Function

Now creates Function "CURRENT_TIMESTAMP".

func Sum

func Sum(v interface{}) Function

Sum creates Function such as "SUM(v)".

type Interval

type Interval interface {
	Value() int
	Unit() qutil.IntervalUnit
}

Interval represents intervals in SQL statements.

func Days

func Days(n int) Interval

Days creates Interval such as "INTERVAL n DAY".

func Hours

func Hours(n int) Interval

Hours creates Interval such as "INTERVAL n HOUR".

func Minutes

func Minutes(n int) Interval

Minutes creates Interval such as "INTERVAL n MINUTE".

func Months

func Months(n int) Interval

Months creates Interval such as "INTERVAL n MONTH".

func Seconds

func Seconds(n int) Interval

Seconds creates Interval such as "INTERVAL n SECOND".

func Years

func Years(n int) Interval

Years creates Interval such as "INTERVAL n YEAR".

type Table

type Table interface {
	C(columnName string, aliasName ...string) Column

	InnerJoin(table Table, conds ...Expression) Table
	LeftJoin(table Table, conds ...Expression) Table
	CrossJoin(table Table) Table

	JoinIndex(i int) (string, Table, Expressions)
	JoinLen() int

	// for internal use.
	WriteTable(ctx *qutil.Context, buf []byte) []byte
	WriteJoins(ctx *qutil.Context, buf []byte) []byte
	WriteDefinition(ctx *qutil.Context, buf []byte) []byte
}

Table represents database table. You can create it from T or *ZSelectBuilder.T.

Example

This is an example of how to use Table.InnerJoin.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user", "usr")
	post := q.T("post", "pst")
	// user.id -> post.user_id
	user.InnerJoin(post, q.Eq(user.C("id"), post.C("user_id")))
	fmt.Println("Short:", user)

	postTag := q.T("posttag", "rel")
	tag := q.T("tag", "tg")
	// post.id -> posttag.post_id
	post.InnerJoin(postTag, q.Eq(post.C("id"), postTag.C("post_id")))
	// posttag.tag_id -> tag.id
	postTag.InnerJoin(tag, q.Eq(postTag.C("tag_id"), tag.C("id")))
	fmt.Println("Long: ", user)
}
Output:

Short: "user" AS "usr" INNER JOIN "post" AS "pst" ON "usr"."id" = "pst"."user_id" []
Long:  "user" AS "usr" INNER JOIN ("post" AS "pst" INNER JOIN ("posttag" AS "rel" INNER JOIN "tag" AS "tg" ON "rel"."tag_id" = "tg"."id") ON "pst"."id" = "rel"."post_id") ON "usr"."id" = "pst"."user_id" []

func T

func T(tableName string, aliasName ...string) Table

T creates Table.

Example

This is an example of how to use T.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	fmt.Println("name:        ", q.T("user"))
	fmt.Println("name + alias:", q.T("user", "usr"))
}
Output:

name:         "user" []
name + alias: "user" AS "usr" []

type UnsafeExpression

type UnsafeExpression Expression

UnsafeExpression represents unsafe expression.

func Unsafe

func Unsafe(v ...interface{}) UnsafeExpression

Unsafe creates any custom expressions.

But IT DOES NOT ESCAPE so if want to use input from outside, should wrap by V or InV.

The basic how to use is similar to fmt.Print. Please refer to the example for more details.

Example

This is an example of how to use Unsafe, V and InV.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	id, name, age := user.C("id"), user.C("name"), user.C("age")
	expr := q.Unsafe(
		"(", id, " % 2 = 1)AND",
		"(", name, " != ", q.V("yourname"), ")AND",
		"(", age, " IN ", q.InV([]int{16, 17, 18}), ")",
	)
	fmt.Println(expr)
}
Output:

("user"."id" % 2 = 1)AND("user"."name" != ?)AND("user"."age" IN (?,?,?)) [yourname 16 17 18]

type Variable

type Variable Expression

Variable represents the argument to which is given from outside.

func InV

func InV(slice interface{}) Variable

InV creates Variable from slice. It can be used with IN operator.

func V

func V(v interface{}, aliasForPrepared ...interface{}) Variable

V creates Variable from a single input.

If aliasForPrepared is given, the value can be rewritten using an alias name in ZArgsBuilder.Set. Please refer to the Select.ToPrepared example for more details.

type ZAndExpr

type ZAndExpr []Expression

ZAndExpr represents AND Expression.

func (ZAndExpr) C

func (e ZAndExpr) C(aliasName ...string) Column

C implements Expression interface method.

func (ZAndExpr) String

func (e ZAndExpr) String() string

String implements fmt.Stringer interface method.

func (ZAndExpr) WriteExpression

func (e ZAndExpr) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface method.

type ZArgsBuilder

type ZArgsBuilder struct {
	Args []interface{}
	// contains filtered or unexported fields
}

ZArgsBuilder is query arguments builder.

func (*ZArgsBuilder) Set

func (b *ZArgsBuilder) Set(key, value interface{})

Set sets the index entries associated with key to the single element value.

type ZCaseBuilder

type ZCaseBuilder struct {
	Base     Expression
	WhenThen [][2]Expression
	ElseThen Expression
}

ZCaseBuilder implements a CASE expression builder. This also implements Expression interface, so it can use in many place.

func Case

func Case(base ...Expression) *ZCaseBuilder

Case creates ZCaseBuilder. If omitting an argument, it'll be a searched CASE builder.

Simple CASE:
	CASE base WHEN 0 THEN 'false' THEN 1 THEN 'true' END
Searched CASE:
	CASE WHEN base = 0 THEN 'false' THEN base = 1 THEN 'true' END
Example

This is an example of how to use Case.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	cs := q.Case().When(
		q.Eq(user.C("id"), 100),
		10,
	).Else(
		0,
	)
	fmt.Println(cs)
	fmt.Println(q.Select().From(user).Column(cs.C("bonus")))
}
Output:

CASE WHEN "user"."id" = ? THEN ? ELSE ? END [100 10 0]
SELECT CASE WHEN "user"."id" = ? THEN ? ELSE ? END AS "bonus" FROM "user" [100 10 0]

func (*ZCaseBuilder) C

func (b *ZCaseBuilder) C(aliasName ...string) Column

C implements Expression interface.

func (*ZCaseBuilder) Else

func (b *ZCaseBuilder) Else(then interface{}) *ZCaseBuilder

Else sets "ELSE then" to the builder.

func (*ZCaseBuilder) String

func (b *ZCaseBuilder) String() string

String implements fmt.Stringer interface.

func (*ZCaseBuilder) When

func (b *ZCaseBuilder) When(cond, then interface{}) *ZCaseBuilder

When adds "WHEN cond THEN then" to the builder.

func (*ZCaseBuilder) WriteExpression

func (b *ZCaseBuilder) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface.

type ZDeleteBuilder

type ZDeleteBuilder struct {
	Dialect qutil.Dialect
	Table   Table
	Wheres  ZAndExpr
}

ZDeleteBuilder implements a DELETE builder.

func Delete

func Delete(table ...Table) *ZDeleteBuilder

Delete creates ZDeleteBuilder.

Example

This is an example of how to use Delete.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	del := q.Delete(user).Where(q.Eq(user.C("id"), 1))
	// del := q.Delete().From(user).Where(q.Eq(user.C("id"), 1)) // same
	fmt.Println(del)

	// Even in this case, the original name is used as a table and a column name
	// because Insert, Delete and Update aren't supporting "AS" syntax.
	u := q.T("user", "u")
	fmt.Println(q.Delete(u).Where(q.Eq(u.C("id", "i"), 1)))
}
Output:

DELETE FROM "user" WHERE "id" = ? [1]
DELETE FROM "user" WHERE "id" = ? [1]

func (*ZDeleteBuilder) From

func (b *ZDeleteBuilder) From(table Table) *ZDeleteBuilder

From sets a table to the FROM clause.

func (*ZDeleteBuilder) SetDialect

func (b *ZDeleteBuilder) SetDialect(d qutil.Dialect) *ZDeleteBuilder

SetDialect sets a Dialect to the builder.

func (*ZDeleteBuilder) String

func (b *ZDeleteBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZDeleteBuilder) ToPrepared

func (b *ZDeleteBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZDeleteBuilder) ToSQL

func (b *ZDeleteBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZDeleteBuilder) Where

func (b *ZDeleteBuilder) Where(conds ...Expression) *ZDeleteBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

type ZInsertBuilder

type ZInsertBuilder struct {
	Dialect   qutil.Dialect
	Beginning string
	Table     Table
	Sets      []struct {
		Name string
		Column
		Expression
	}
	Returnings []Column
}

ZInsertBuilder implements a INSERT builder.

func Insert

func Insert(beginning ...string) *ZInsertBuilder

Insert creates ZInsertBuilder.

Example

This is an example of how to use Insert.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	ins := q.Insert().Into(user).Set(user.C("name"), "hackme")
	fmt.Println(ins)
}
Output:

INSERT INTO "user"("name") VALUES (?) [hackme]

func (*ZInsertBuilder) Into

func (b *ZInsertBuilder) Into(table Table) *ZInsertBuilder

Into sets a table to the builder.

func (*ZInsertBuilder) Returning

func (b *ZInsertBuilder) Returning(columns ...Column) *ZInsertBuilder

Returning appends a column to RETURNING clause. This feature is available for PostgreSQL only.

Example

This is an example of how to use Insert.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	ins := q.Insert().Into(user).
		Set(user.C("name"), "hackme").
		Returning(user.C("id"), user.C("name", "n"))
	fmt.Println("PostgreSQL", ins.SetDialect(q.PostgreSQL))
	fmt.Println("MySQL", ins.SetDialect(q.MySQL))
}
Output:

PostgreSQL INSERT INTO "user"("name") VALUES ($1) RETURNING "id", "name" AS "n" [hackme]
MySQL INSERT INTO `user`(`name`) VALUES (?) [hackme]

func (*ZInsertBuilder) Set

func (b *ZInsertBuilder) Set(c Column, v interface{}) *ZInsertBuilder

Set adds assignment expression to the builder.

func (*ZInsertBuilder) SetDialect

func (b *ZInsertBuilder) SetDialect(d qutil.Dialect) *ZInsertBuilder

SetDialect sets a Dialect to the builder.

func (*ZInsertBuilder) String

func (b *ZInsertBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZInsertBuilder) ToPrepared

func (b *ZInsertBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZInsertBuilder) ToSQL

func (b *ZInsertBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZInsertBuilder) Unset

func (b *ZInsertBuilder) Unset(c Column) *ZInsertBuilder

Unset removes assignment expression from the builder.

type ZOrExpr

type ZOrExpr []Expression

ZOrExpr represents OR Expression.

func (ZOrExpr) C

func (e ZOrExpr) C(aliasName ...string) Column

C implements Expression interface method.

func (ZOrExpr) String

func (e ZOrExpr) String() string

String implements fmt.Stringer interface method.

func (ZOrExpr) WriteExpression

func (e ZOrExpr) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface method.

type ZSelectBuilder

type ZSelectBuilder struct {
	Dialect   qutil.Dialect
	Beginning string
	Columns   []Column
	Tables    []Table
	Wheres    ZAndExpr
	Groups    []Expression
	Havings   ZAndExpr
	Orders    []struct {
		Expression
		Ascending bool
	}
	LimitCount  Expression
	StartOffset Expression
}

ZSelectBuilder implemenets a SELECT builder. This also implements Expression interface, so it can use in many place.

func Select

func Select(beginning ...string) *ZSelectBuilder

Select creates ZSelectBuilder. If not needing an additional keyword around "SELECT", the argument can be omitted.

Example

This is an example of how to use Select.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	post, user := q.T("post"), q.T("user")
	sel := q.Select().From(
		post.InnerJoin(
			user,
			q.Eq(post.C("user_id"), user.C("id")),
		),
	).Column(
		user.C("name"),
		post.C("message"),
	).Where(
		q.Eq(post.C("id"), 100),
	)
	// You can also use `q.DefaultDialect = q.MySQL` instead of SetDialect.
	fmt.Println(sel.SetDialect(q.MySQL).ToSQL())
}
Output:

SELECT `user`.`name`, `post`.`message` FROM `post` INNER JOIN `user` ON `post`.`user_id` = `user`.`id` WHERE `post`.`id` = ? [100]
Example (Beginning)

This is an example of how to use the beginning argument.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("Default:     ", q.Select().From(user))
	fmt.Println("SQL_NO_CACHE:", q.Select("SELECT SQL_NO_CACHE").From(user))
	fmt.Println("EXPLAIN:     ", q.Select("EXPLAIN SELECT").From(user))
}
Output:

Default:      SELECT * FROM "user" []
SQL_NO_CACHE: SELECT SQL_NO_CACHE * FROM "user" []
EXPLAIN:      EXPLAIN SELECT * FROM "user" []

func (*ZSelectBuilder) C

func (b *ZSelectBuilder) C(aliasName ...string) Column

C implements Expression interface.

func (*ZSelectBuilder) Column

func (b *ZSelectBuilder) Column(columns ...Column) *ZSelectBuilder

Column appends a column to the column list.

Example

This is an example of how to use ZSelectBuilder.Column.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("Default:  ", q.Select().From(user))
	fmt.Println("Append:   ", q.Select().Column(user.C("id")).From(user))
	fmt.Println("Aggregate:", q.Select().Column(q.CountAll().C("count")).From(user))
}
Output:

Default:   SELECT * FROM "user" []
Append:    SELECT "user"."id" FROM "user" []
Aggregate: SELECT COUNT(*) AS "count" FROM "user" []

func (*ZSelectBuilder) From

func (b *ZSelectBuilder) From(tables ...Table) *ZSelectBuilder

From appends a table to the FROM clause.

Example

This is an example of how to use ZSelectBuilder.From.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("Simple: ", q.Select().From(user))
	post := q.T("post")
	fmt.Println("Complex:", q.Select().From(user, post).Where(
		q.Eq(user.C("id"), post.C("user_id")),
	))
	fmt.Println("Builder:", q.Select().From(q.Select().From(q.T("post")).T("p")))
}
Output:

Simple:  SELECT * FROM "user" []
Complex: SELECT * FROM "user", "post" WHERE "user"."id" = "post"."user_id" []
Builder: SELECT * FROM (SELECT * FROM "post") AS "p" []

func (*ZSelectBuilder) GroupBy

func (b *ZSelectBuilder) GroupBy(e ...Expression) *ZSelectBuilder

GroupBy adds condition to the GROUP BY clause.

Example

This is an example of how to use ZSelectBuilder.GroupBy.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println(
		q.Select().Column(q.CountAll().C("count")).From(user).GroupBy(user.C("age")),
	)
}
Output:

SELECT COUNT(*) AS "count" FROM "user" GROUP BY "user"."age" []

func (*ZSelectBuilder) Having

func (b *ZSelectBuilder) Having(conds ...Expression) *ZSelectBuilder

Having adds HAVING condition to the GROUP BY clause. More than one condition is connected by AND.

func (*ZSelectBuilder) Limit

func (b *ZSelectBuilder) Limit(count interface{}) *ZSelectBuilder

Limit sets LIMIT clause to the builder.

Example

This is an example of how to use ZSelectBuilder.Limit.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("int:     ", q.Select().From(user).Limit(10))
	fmt.Println("q.Unsafe:", q.Select().From(user).Limit(q.Unsafe(10, "*", 20)))
}
Output:

int:      SELECT * FROM "user" LIMIT ? [10]
q.Unsafe: SELECT * FROM "user" LIMIT 10*20 []

func (*ZSelectBuilder) Offset

func (b *ZSelectBuilder) Offset(start interface{}) *ZSelectBuilder

Offset sets OFFSET clause to the builder.

Example

This is an example of how to use ZSelectBuilder.Offset.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("int:     ", q.Select().From(user).Limit(10).Offset(10))
	fmt.Println("q.Unsafe:", q.Select().From(user).Limit(10).Offset(q.Unsafe(10, "*", 20)))
}
Output:

int:      SELECT * FROM "user" LIMIT ? OFFSET ? [10 10]
q.Unsafe: SELECT * FROM "user" LIMIT ? OFFSET 10*20 [10]

func (*ZSelectBuilder) OrderBy

func (b *ZSelectBuilder) OrderBy(e Expression, asc bool) *ZSelectBuilder

OrderBy adds condition to the ORDER BY clause.

Example

This is an example of how to use ZSelectBuilder.OrderBy.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println(
		"Single order:  ",
		q.Select().From(user).OrderBy(user.C("age"), true),
	)
	fmt.Println(
		"Multiple order:",
		q.Select().From(user).OrderBy(user.C("age"), true).OrderBy(
			q.CharLength(user.C("name")), false),
	)
}
Output:

Single order:   SELECT * FROM "user" ORDER BY "user"."age" ASC []
Multiple order: SELECT * FROM "user" ORDER BY "user"."age" ASC, CHAR_LENGTH("user"."name") DESC []

func (*ZSelectBuilder) SetDialect

func (b *ZSelectBuilder) SetDialect(d qutil.Dialect) *ZSelectBuilder

SetDialect sets a Dialect to the builder.

func (*ZSelectBuilder) String

func (b *ZSelectBuilder) String() string

String implements fmt.Stringer interface.

func (*ZSelectBuilder) T

func (b *ZSelectBuilder) T(aliasName string) Table

T creates Table from this builder.

func (*ZSelectBuilder) ToPrepared

func (b *ZSelectBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and query arguments builder generator.

Example

This is an example of how to use ZSelectBuilder.ToPrepared and V.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	sql, gen := q.Select().From(q.T("user")).Where(
		q.Lte(q.C("id"), 100),
		q.Lte(q.C("age"), q.V(18, "findAge")),
	).ToPrepared()

	// // You can use by performing the following steps.
	// stmt, err := db.Prepare(sql)
	// if err != nil {
	//   return err
	// }
	// defer stmt.Close()
	//
	// ab := gen()
	// ab.Set("findAge", 24)
	// stmt.Query(ab.Args...)

	fmt.Println("SQL:", sql)

	// build arguments
	ab := gen()
	fmt.Println("Default  Args:", ab.Args)
	ab.Set("findAge", 24)
	fmt.Println("Modified Args:", ab.Args)
	// You can also rewrite other values by using an index,
	// but there is a problem readability and weak to SQL change,
	// so it isn't recommended.
	// ab.Args[0] = 123
	// ab.Args[1] = 24

}
Output:

SQL: SELECT * FROM "user" WHERE ("id" <= ?)AND("age" <= ?)
Default  Args: [100 18]
Modified Args: [100 24]

func (*ZSelectBuilder) ToSQL

func (b *ZSelectBuilder) ToSQL() (string, []interface{})

ToSQL returns generated SQL and arguments.

Example

This is an example of how to use ZSelectBuilder.ToSQL.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	fmt.Println(q.Select().From(q.T("user")).Where(q.Lte(q.C("age"), 18)).ToSQL())
}
Output:

SELECT * FROM "user" WHERE "age" <= ? [18]

func (*ZSelectBuilder) Where

func (b *ZSelectBuilder) Where(conds ...Expression) *ZSelectBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

Example

This is an example of how to use ZSelectBuilder.Where.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	user := q.T("user")
	fmt.Println("Simple: ", q.Select().From(user).Where(q.Neq(user.C("id"), nil)))
	post := q.T("post")
	fmt.Println("Complex:", q.Select().From(user, post).Where(
		q.Neq(user.C("id"), nil),
		q.Gt(user.C("id"), 100),
	))
}
Output:

Simple:  SELECT * FROM "user" WHERE "user"."id" IS NOT NULL []
Complex: SELECT * FROM "user", "post" WHERE ("user"."id" IS NOT NULL)AND("user"."id" > ?) [100]

func (*ZSelectBuilder) WriteExpression

func (b *ZSelectBuilder) WriteExpression(ctx *qutil.Context, buf []byte) []byte

WriteExpression implements Expression interface.

type ZUpdateBuilder

type ZUpdateBuilder struct {
	Dialect   qutil.Dialect
	Beginning string
	Table     Table
	Sets      []struct {
		Name string
		Column
		Expression
	}
	Wheres ZAndExpr
}

ZUpdateBuilder implements a UPDATE builder.

func Update

func Update(table Table, beginning ...string) *ZUpdateBuilder

Update creates ZUpdateBuilder.

Example

This is an example of how to use Update.

package main

import (
	"fmt"

	"github.com/oov/q"
)

func main() {
	upd := q.Update(q.T("user")).Set(q.C("name"), "hackme").Where(q.Eq(q.C("id"), 1))
	fmt.Println(upd)
	// Even in this case, the original name is used as a table and a column name
	// because Insert, Delete and Update aren't supporting "AS" syntax.
	u := q.T("user", "u")
	fmt.Println(q.Update(u).Set(u.C("name"), "hackme").Where(q.Eq(u.C("id"), 1)))
	// When overwriting in the same name, the last one is effective.
	fmt.Println(q.Update(u).Set(u.C("name"), "hackyou").Set(u.C("name"), "hackme").Where(q.Eq(u.C("id"), 1)))
}
Output:

UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]
UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]
UPDATE "user" SET "name" = ? WHERE "id" = ? [hackme 1]

func (*ZUpdateBuilder) Set

func (b *ZUpdateBuilder) Set(c Column, v interface{}) *ZUpdateBuilder

Set adds assignment expression to the SET clause.

func (*ZUpdateBuilder) SetDialect

func (b *ZUpdateBuilder) SetDialect(d qutil.Dialect) *ZUpdateBuilder

SetDialect sets a Dialect to the builder.

func (*ZUpdateBuilder) String

func (b *ZUpdateBuilder) String() string

String implemenets fmt.Stringer interface.

func (*ZUpdateBuilder) ToPrepared

func (b *ZUpdateBuilder) ToPrepared() (string, func() *ZArgsBuilder)

ToPrepared returns generated SQL and arguments builder generator.

func (*ZUpdateBuilder) ToSQL

func (b *ZUpdateBuilder) ToSQL() (string, []interface{})

ToSQL builds SQL and arguments.

func (*ZUpdateBuilder) Unset

func (b *ZUpdateBuilder) Unset(c Column) *ZUpdateBuilder

Unset removes assignment expression from the SET clause.

func (*ZUpdateBuilder) Where

func (b *ZUpdateBuilder) Where(conds ...Expression) *ZUpdateBuilder

Where adds condition to the WHERE clause. More than one condition is connected by AND.

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
t or T : Toggle theme light dark auto
y or Y : Canonical URL