q

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

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.