sqlbuilder

package
v0.0.0-...-83adff0 Latest Latest
Warning

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

 Go to latest Published: Jul 6, 2020 License: GPL-3.0 Imports: 7 Imported by: 0

Documentation

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	// DefaultFlavor is the default flavor for all builders.
	DefaultFlavor = MySQL
)

Functions

func Escape 

func Escape(ident string) string

Escape replaces `$` with `$$` in ident.

func EscapeAll 

func EscapeAll(ident ...string) []string

EscapeAll replaces `$` with `$$` in all strings of ident.

func Flatten 

func Flatten(slices interface{}) (flattened []interface{})

Flatten recursively extracts values in slices and returns a flattened []interface{} with all values. If slices is not a slice, return `[]interface{}{slices}`.

func List 

func List(arg interface{}) interface{}

List marks arg as a list of data. If arg is `[]int{1, 2, 3}`, it will be compiled to `?, ?, ?` with args `[1 2 3]`.

func Named 

func Named(name string, arg interface{}) interface{}

Named creates a named argument. Unlike `sql.Named`, this named argument works only with `Build` or `BuildNamed` for convenience and will be replaced to a `?` after `Compile`.

func Raw 

func Raw(expr string) interface{}

Raw marks the expr as a raw value which will not be added to args.

Types

type Args 

type Args struct {
	// The default flavor used by `Args#Compile`
	Flavor Flavor
	// contains filtered or unexported fields
}

Args stores arguments associated with a SQL.

func (*Args) Add 

func (args *Args) Add(arg interface{}) string

Add adds an arg to Args and returns a placeholder.

func (*Args) Compile 

func (args *Args) Compile(format string, intialValue ...interface{}) (query string, values []interface{})

Compile compiles builder's format to standard sql and returns associated args.

The format string uses a special syntax to represent arguments. 

$? refers successive arguments passed in the call. It works similar as `%v` in `fmt.Sprintf`.
$0 $1 ... $n refers nth-argument passed in the call. Next $? will use arguments n+1.
${name} refers a named argument created by `Named` with `name`.
$$ is a "$" string.

func (*Args) CompileWithFlavor 

func (args *Args) CompileWithFlavor(format string, flavor Flavor, intialValue ...interface{}) (query string, values []interface{})

CompileWithFlavor compiles builder's format to standard sql with flavor and returns associated args.

See doc for `Compile` to learn details.

func (*Args) Copy 

func (args *Args) Copy() *Args

Copy is

type Builder 

type Builder interface {
	Build() (sql string, args []interface{})
	BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})
}

Builder is a general SQL builder. It's used by Args to create nested SQL like the `IN` expression in `SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)`.

func Build 

func Build(format string, arg ...interface{}) Builder

Build creates a Builder from a format string. The format string uses special syntax to represent arguments. See doc in `Args#Compile` for syntax details.

Example 
sb := NewSelectBuilder()
sb.Select("id").From("user").Where(sb.In("status", 1, 2))

b := Build("EXPLAIN $? LEFT JOIN SELECT * FROM $? WHERE created_at > $? AND state IN (${states}) AND modified_at BETWEEN $2 AND $?",
	sb, Raw("banned"), 1514458225, 1514544625, Named("states", List([]int{3, 4, 5})))
sql, args := b.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

EXPLAIN SELECT id FROM user WHERE status IN (?, ?) LEFT JOIN SELECT * FROM banned WHERE created_at > ? AND state IN (?, ?, ?) AND modified_at BETWEEN ? AND ?
[1 2 1514458225 3 4 5 1514458225 1514544625]

func BuildNamed 

func BuildNamed(format string, named map[string]interface{}) Builder

BuildNamed creates a Builder from a format string. The format string uses `${key}` to refer the value of named by key.

Example 
b := BuildNamed("SELECT * FROM ${table} WHERE status IN (${status}) AND name LIKE ${name} AND created_at > ${time} AND modified_at < ${time} + 86400",
	map[string]interface{}{
		"time":   sql.Named("start", 1234567890),
		"status": List([]int{1, 2, 5}),
		"name":   "Huan%",
		"table":  Raw("user"),
	})
sql, args := b.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT * FROM user WHERE status IN (?, ?, ?) AND name LIKE ? AND created_at > @start AND modified_at < @start + 86400
[1 2 5 Huan% {{} start 1234567890}]

func Buildf 

func Buildf(format string, arg ...interface{}) Builder

Buildf creates a Builder from a format string using `fmt.Sprintf`-like syntax. As all arguments will be converted to a string internally, e.g. "$0", only `%v` and `%s` are valid.

Example 
sb := NewSelectBuilder()
sb.Select("id").From("user")

explain := Buildf("EXPLAIN %v LEFT JOIN SELECT * FROM banned WHERE state IN (%v, %v)", sb, 1, 2)
sql, args := explain.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

EXPLAIN SELECT id FROM user LEFT JOIN SELECT * FROM banned WHERE state IN (?, ?)
[1 2]

func WithFlavor 

func WithFlavor(builder Builder, flavor Flavor) Builder

WithFlavor creates a new Builder based on builder with a default flavor.

Example 
sql, args := WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), PostgreSQL).Build()

fmt.Println(sql)
fmt.Println(args)

// Explicitly use MySQL as the flavor.
sql, args = WithFlavor(Buildf("SELECT * FROM foo WHERE id = %v", 1234), PostgreSQL).BuildWithFlavor(MySQL)

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT * FROM foo WHERE id = $1
[1234]
SELECT * FROM foo WHERE id = ?
[1234]

type Cond 

type Cond struct {
	Args *Args
}

Cond provides several helper methods to build conditions.

func (*Cond) Between 

func (c *Cond) Between(field string, lower, upper interface{}) string

Between represents "field BETWEEN lower AND upper".

func (*Cond) E 

func (c *Cond) E(field string, value interface{}) string

E is an alias of Equal.

func (*Cond) Equal 

func (c *Cond) Equal(field string, value interface{}) string

Equal represents "field = value".

func (*Cond) G 

func (c *Cond) G(field string, value interface{}) string

G is an alias of GreaterThan.

func (*Cond) GE 

func (c *Cond) GE(field string, value interface{}) string

GE is an alias of GreaterEqualThan.

func (*Cond) GreaterEqualThan 

func (c *Cond) GreaterEqualThan(field string, value interface{}) string

GreaterEqualThan represents "field >= value".

func (*Cond) GreaterThan 

func (c *Cond) GreaterThan(field string, value interface{}) string

GreaterThan represents "field > value".

func (*Cond) In 

func (c *Cond) In(field string, value ...interface{}) string

In represents "field IN (value...)".

func (*Cond) IsNotNull 

func (c *Cond) IsNotNull(field string) string

IsNotNull represents "field IS NOT NULL".

func (*Cond) IsNull 

func (c *Cond) IsNull(field string) string

IsNull represents "field IS NULL".

func (*Cond) L 

func (c *Cond) L(field string, value interface{}) string

L is an alias of LessThan.

func (*Cond) LE 

func (c *Cond) LE(field string, value interface{}) string

LE is an alias of LessEqualThan.

func (*Cond) LessEqualThan 

func (c *Cond) LessEqualThan(field string, value interface{}) string

LessEqualThan represents "field <= value".

func (*Cond) LessThan 

func (c *Cond) LessThan(field string, value interface{}) string

LessThan represents "field < value".

func (*Cond) Like 

func (c *Cond) Like(field string, value interface{}) string

Like represents "field LIKE value".

func (*Cond) NE 

func (c *Cond) NE(field string, value interface{}) string

NE is an alias of NotEqual.

func (*Cond) NotBetween 

func (c *Cond) NotBetween(field string, lower, upper interface{}) string

NotBetween represents "field NOT BETWEEN lower AND upper".

func (*Cond) NotEqual 

func (c *Cond) NotEqual(field string, value interface{}) string

NotEqual represents "field != value".

func (*Cond) NotIn 

func (c *Cond) NotIn(field string, value ...interface{}) string

NotIn represents "field NOT IN (value...)".

func (*Cond) NotLike 

func (c *Cond) NotLike(field string, value interface{}) string

NotLike represents "field NOT LIKE value".

func (*Cond) Or 

func (c *Cond) Or(orExpr ...string) string

Or represents OR logic like "expr1 OR expr2 OR expr3".

func (*Cond) Var 

func (c *Cond) Var(value interface{}) string

Var returns a placeholder for value.

type Flavor 

type Flavor int

Flavor is the flag to control the format of compiled sql.

Example 
// Create a flavored builder.
sb := PostgreSQL.NewSelectBuilder()
sb.Select("name").From("user").Where(
	sb.E("id", 1234),
	sb.G("rank", 3),
)
sql, args := sb.Build()

fmt.Println(sql)
fmt.Println(args)

Output:

SELECT name FROM user WHERE id = $1 AND rank > $2
[1234 3]

const (
	MySQL Flavor
	PostgreSQL
)

Supported flavors.

func (Flavor) NewSelectBuilder 

func (f Flavor) NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder with flavor.

func (Flavor) Quote 

func (f Flavor) Quote(name string) string

Quote adds quote for name to make sure the name can be used safely as table name or field name.

* For MySQL, use back quote (`) to quote name; * For PostgreSQL, use double quote (") to quote name.

func (Flavor) String 

func (f Flavor) String() string

String returns the name of f.

type SelectBuilder 

type SelectBuilder struct {
	Cond
	// contains filtered or unexported fields
}

SelectBuilder is a builder to build SELECT.

Example 
sb := NewSelectBuilder()
sb.Distinct().Select("id", "name", sb.As("COUNT(*)", "t"))
sb.From("demo.user")
sb.Where(
	sb.GreaterThan("id", 1234),
	sb.Like("name", "%Du"),
	sb.Or(
		sb.IsNull("id_card"),
		sb.In("status", 1, 2, 5),
	),
	sb.NotIn(
		"id",
		NewSelectBuilder().Select("id").From("banned"),
	), // Nested SELECT.
	"modified_at > created_at + "+sb.Var(86400), // It's allowed to write arbitrary SQL.
)
sb.GroupBy("status").Having(sb.NotIn("status", 4, 5))
sb.OrderBy("modified_at").Asc()
sb.Limit(10).Offset(5)

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

SELECT DISTINCT id, name, COUNT(*) AS t FROM demo.user WHERE id > ? AND name LIKE ? AND (id_card IS NULL OR status IN (?, ?, ?)) AND id NOT IN (SELECT id FROM banned) AND modified_at > created_at + ? GROUP BY status HAVING status NOT IN (?, ?) ORDER BY modified_at ASC LIMIT 10 OFFSET 5
[1234 %Du 1 2 5 86400 4 5]
Example (AdvancedUsage) 
sb := NewSelectBuilder()
innerSb := NewSelectBuilder()

sb.Select("id", "name")
sb.From(
	sb.BuilderAs(innerSb, "user"),
)
sb.Where(
	sb.In("status", Flatten([]int{1, 2, 3})...),
	sb.Between("created_at", sql.Named("start", 1234567890), sql.Named("end", 1234599999)),
)

innerSb.Select("*")
innerSb.From("banned")
innerSb.Where(
	innerSb.NotIn("name", Flatten([]string{"Huan Du", "Charmy Liu"})...),
)

sql, args := sb.Build()
fmt.Println(sql)
fmt.Println(args)

Output:

SELECT id, name FROM (SELECT * FROM banned WHERE name NOT IN (?, ?)) AS user WHERE status IN (?, ?, ?) AND created_at BETWEEN @start AND @end
[Huan Du Charmy Liu 1 2 3 {{} start 1234567890} {{} end 1234599999}]

func NewSelectBuilder 

func NewSelectBuilder() *SelectBuilder

NewSelectBuilder creates a new SELECT builder.

func (*SelectBuilder) As 

func (sb *SelectBuilder) As(col, alias string) string

As returns an AS expression.

func (*SelectBuilder) Asc 

func (sb *SelectBuilder) Asc() *SelectBuilder

Asc sets order of ORDER BY to ASC.

func (*SelectBuilder) Build 

func (sb *SelectBuilder) Build() (sql string, args []interface{})

Build returns compiled SELECT string and args. They can be used in `DB#Query` of package `database/sql` directly.

func (*SelectBuilder) BuildWithFlavor 

func (sb *SelectBuilder) BuildWithFlavor(flavor Flavor, initialArg ...interface{}) (sql string, args []interface{})

BuildWithFlavor returns compiled SELECT string and args with flavor and initial args. They can be used in `DB#Query` of package `database/sql` directly.

func (*SelectBuilder) BuilderAs 

func (sb *SelectBuilder) BuilderAs(builder Builder, alias string) string

BuilderAs returns an AS expression wrapping a complex SQL. According to SQL syntax, SQL built by builder is surrounded by parens.

func (*SelectBuilder) Copy 

func (sb *SelectBuilder) Copy() *SelectBuilder

Copy the builder

func (*SelectBuilder) Desc 

func (sb *SelectBuilder) Desc() *SelectBuilder

Desc sets order of ORDER BY to DESC.

func (*SelectBuilder) Distinct 

func (sb *SelectBuilder) Distinct() *SelectBuilder

Distinct marks this SELECT as DISTINCT.

func (*SelectBuilder) From 

func (sb *SelectBuilder) From(table ...string) *SelectBuilder

From sets table names in SELECT.

func (*SelectBuilder) GroupBy 

func (sb *SelectBuilder) GroupBy(col ...string) *SelectBuilder

GroupBy sets columns of GROUP BY in SELECT.

func (*SelectBuilder) Having 

func (sb *SelectBuilder) Having(andExpr ...string) *SelectBuilder

Having sets expressions of HAVING in SELECT.

func (*SelectBuilder) Limit 

func (sb *SelectBuilder) Limit(limit int) *SelectBuilder

Limit sets the LIMIT in SELECT.

func (*SelectBuilder) Offset 

func (sb *SelectBuilder) Offset(offset int) *SelectBuilder

Offset sets the LIMIT offset in SELECT.

func (*SelectBuilder) OrderBy 

func (sb *SelectBuilder) OrderBy(col ...string) *SelectBuilder

OrderBy sets columns of ORDER BY in SELECT.

func (*SelectBuilder) Select 

func (sb *SelectBuilder) Select(col ...string) *SelectBuilder

Select sets columns in SELECT.

func (*SelectBuilder) SetFlavor 

func (sb *SelectBuilder) SetFlavor(flavor Flavor) (old Flavor)

SetFlavor sets the flavor of compiled sql.

func (*SelectBuilder) String 

func (sb *SelectBuilder) String() string

String returns the compiled SELECT string.

func (*SelectBuilder) Where 

func (sb *SelectBuilder) Where(andExpr ...string) *SelectBuilder

Where sets expressions of WHERE in SELECT.

Source Files

View all Source files

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.