sg

package module
v1.0.3 Latest Latest
Warning

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

 Go to latest Published: Apr 25, 2022 License: Apache-2.0 Imports: 2 Imported by: 2

README

sg

sg: A simple standard SQL generator written in Go.

CircleCI codecov Go Report Card GoDoc

Overview

Builders
Insert Builder
package main

import (
  "fmt"
  . "github.com/go-the-way/sg"
)

func main() {
  builder := InsertBuilder().
    Table(T("table_person")).
    Column(C("col1"), C("col2")).
    Value(Arg(100), Arg(200))
  fmt.Println(builder.Build())
  // Output:
  // INSERT INTO table_person (col1, col2) VALUES (?, ?) [100 200]
}
Delete Builder
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := DeleteBuilder().
		Delete(T("t1.*")).
		From(As(C("table1"), "t1"), As(C("table2"), "t2")).
		Where(AndGroup(Gt("t1.col1", 100), Gt("t2.col2", 200)))
	fmt.Println(builder.Build())
	// Output:
	// DELETE t1.* FROM table1 AS t1, table2 AS t2 WHERE ((t1.col1 > ?) AND (t2.col2 > ?)) [100 200]
}
Update Builder
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := UpdateBuilder().
		Update(As(T("table_person"), "t")).
		Join(LeftJoin(As(T("table_a"), "ta"), On(C("t.col1 = ta.col1")))).
		Set(SetEq("col1", 100), SetEq("col2", 200)).
		Where(AndGroup(Eq("a", 100), Eq("b", 200)))
	fmt.Println(builder.Build())
	// Output:
	// UPDATE table_person AS t LEFT JOIN table_a AS ta ON (t.col1 = ta.col1) SET col1 = ?, col2 = ? WHERE ((a = ?) AND (b = ?)) [100 200 100 200]
}
Select Builder
package main

import (
	"fmt"
	. "github.com/go-the-way/sg"
)

func main() {
	builder := SelectBuilder().
		Select(C("a"), C("b")).
		From(T("table_person")).
		Join(LeftJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1")))).
		Where(AndGroup(Eq("a", 100), Eq("b", 200))).
		OrderBy(DescGroup(C("a"), C("b")))
	fmt.Println(builder.Build())
	// Output:
	// SELECT a, b FROM table_person LEFT JOIN table_a AS ta ON (ta.col1 = tb.col1) WHERE ((a = ?) AND (b = ?)) ORDER BY a DESC, b DESC [100 200]
}
Generators
Create view
CreateView(P("vm_nowTime"), P(`select NOW() AS t`)
Create index
CreateIndex(false, P("idx_name"), T("table"), C("name"))
Create unique index
CreateUniqueIndex(P("idx_name"), T("table"), C("name"))
Index definition
IndexDefinition(false, P("idx_name"), C("name"))
Column definition
ColumnDefinition(P("id"), P("int"), false, true, false, "", "ID")
Primary key
PrimaryKey(C("id"))
Default
Default(C("1"))
Delete
Delete([]Ge{}, T("table_a"))
Delete from
DeleteFrom(T("table_a"))
Drop table
DropTable(T("table"))
Drop view
DropView(T("view"))
Drop event
DropEvent(T("event"))
Drop procedure
DropProcedure(T("procedure"))
Insert
Insert(C("table"), C("aa"))
Values
Values(Arg(100))
Alias
Alias(C("hello"), "hello_lo")
Arg
Arg(100)
From
From(T("table_a"), T("table_b"))
Left join
LeftJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
Right join
RightJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
Inner join
InnerJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1"))
On
On(C("ta.col1 = tb.col1"))
Select
Select(C("t.col_a"))
Order by
OrderBy(AscGroup(C("t.abc"), C("t.xxx")))
Asc
Asc(C("t.abc"))
Desc
Desc(C("t.abc"))
Asc group
AscGroup(C("t.abc"), C("t.xxx"))
Desc group
DescGroup(C("t.abc"), C("t.xxx"))
Group by
GroupBy(C("t.abc"), C("t.xyz"))
Having
Having(AndGroup(Eq("a", 1)))
Update
Update(T("table_a"))
Set
Set(C("t.a = t.b"))
Set eq
SetEq("col1", 100)
Where
Where(AndGroup(Eq("a", 1)))
And
And(Eq("c", 100))
Or
Or(Eq("c", 100))
Not
Not(Eq("c", 100))
And group
AndGroup(Gt("t1.col1", 100), Gt("t2.col2", 200))
Or group
OrGroup(Eq("a", 1), Eq("b", 100))
Eq
Eq("a", 1))
Not eq
NotEq("a", 1))
Gt
Gt("a", 1))
Gt eq
Lt("a", 1))
Lt
Lt("a", 1))
Lt eq
LtEq("a", 1))
Like
Like("a", 1)
Left like
LeftLike("a", 1)
Right like
RightLike("a", 1)
Instr
Instr("a", 1)
In
In("a", 1)
Between and
BetweenAnd(c, rune(100), rune(100))

Documentation

Overview

Package sg

sg: A simple standard SQL generator written in Go.

Build a create table SQL: 

builder := CreateTableBuilder().
	Table(T("table_person")).
	ColumnDefinition(
		ColumnDefinition(P("id"), P("int"), false, true, false, "", "ID"),
		ColumnDefinition(P("name"), P("varchar(50)"), false, false, true, "000", "Name"),
	).
	PrimaryKey(C("id")).
	Index(IndexDefinition(false, P("idx_name"), C("name")))
sql, _ := builder.Build()
fmt.Println(sql)

Build a insert SQL: 

builder := InsertBuilder().
	Table(T("table_person")).
	Column(C("col1"), C("col2")).
	Value(Arg(100), Arg(200))
sql, _ := builder.Build()
fmt.Println(sql)

Build a delete SQL: 

builder := DeleteBuilder().
	Delete(T("t1.*")).
	From(As(C("table1"), "t1"), As(C("table2"), "t2")).
	Where(AndGroup(Gt("t1.col1", 100), Gt("t2.col2", 200)))
sql, _ := builder.Build()
fmt.Println(sql)

Build a update SQL: 

builder := UpdateBuilder().
	Update(As(T("table_person"), "t")).
	Join(LeftJoin(As(T("table_a"), "ta"), On(C("t.col1 = ta.col1")))).
	Set(SetEq("col1", 100), SetEq("col2", 200)).
	Where(AndGroup(Eq("a", 100), Eq("b", 200)))
sql, _ := builder.Build()
fmt.Println(sql)

Build a select SQL: 

builder := SelectBuilder().
	Select(C("a"), C("b")).
	From(T("table_person")).
	Join(LeftJoin(As(T("table_a"), "ta"), On(C("ta.col1 = tb.col1")))).
	Where(AndGroup(Eq("a", 100), Eq("b", 200))).
	OrderBy(DescGroup(C("a"), C("b")))
sql, _ := builder.Build()
fmt.Println(sql)

Index

Constants

View Source 
const (
	Table         = P("TABLE")
	Create        = P("CREATE")
	Drop          = P("DROP")
	View          = P("VIEW")
	Unique        = P("UNIQUE")
	Index         = P("INDEX")
	Event         = P("EVENT")
	Procedure     = P("PROCEDURE")
	AutoIncrement = P("AUTO_INCREMENT")
	NotNull       = P("NOT NULL")
	Null          = P("NULL")
)

Variables

View Source 
var (
	// CreateView
	//
	// CREATE VIEW vm_hello_world AS
	// SELECT UUID() AS uuid
	CreateView = func(view Ge, viewDefinition Ge) Ge {
		return NewJoiner([]Ge{NewJoiner([]Ge{Create, View, view}, " ", "", "", false), viewDefinition}, " AS ", "", "", false)
	}
	// CreateIndex
	//
	// CREATE INDEX idx_name ON table(id, name)
	CreateIndex = func(unique bool, index Ge, table Ge, columns ...Ge) Ge {
		return NewJoiner([]Ge{
			NewJoiner([]Ge{
				Create, map[bool]Ge{true: Unique, false: P("")}[unique], Index, index, P("ON"), table,
			}, " ", "", "", false),
			NewJoiner(columns, ", ", " ", "", true),
		}, "", "", "", false)
	}
	// CreateUniqueIndex
	//
	// CREATE UNIQUE INDEX idx_name ON table(id, name)
	CreateUniqueIndex = func(index Ge, table Ge, columns ...Ge) Ge {
		return CreateIndex(true, index, table, columns...)
	}
	// IndexDefinition defines gen SQL like `unique? index idx_name (id, name)`
	IndexDefinition = func(unique bool, name Ge, columns ...Ge) Ge {
		return NewJoiner([]Ge{
			map[bool]Ge{true: Unique, false: P("")}[unique], Index, name,
			NewJoiner(columns, ", ", "", "", true),
		}, " ", "", "", false)
	}
	// ColumnDefinition defines gen SQL like `name varchar(50) not null default '000' comment 'Name'`
	ColumnDefinition = func(name Ge, typ Ge, null, autoIncrement, defaultSet bool, defaultVal, comment string) Ge {
		return NewJoiner([]Ge{
			name,
			typ,
			map[bool]Ge{true: Null, false: NotNull}[null],
			map[bool]Ge{true: Default(P(fmt.Sprintf("%s", defaultVal))), false: P("")}[defaultSet],
			map[bool]Ge{true: AutoIncrement, false: P("")}[autoIncrement],
			Comment(comment),
		}, " ", "", "", false)
	}
	// PrimaryKey defines gen SQL like `PRIMARY KEY(id, name)`
	PrimaryKey = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "PRIMARY KEY ", "", true) }
	// Default defines gen SQL like `DEFAULT '0'`
	Default = func(ge Ge) Ge { return NewJoiner([]Ge{P("DEFAULT "), ge}, "", "", "", false) }
	// Comment defines gen SQL like `COMMENT 'comment'`
	Comment = func(comment string) Ge { return P(fmt.Sprintf("COMMENT '%s'", comment)) }
)
View Source 
var (
	Delete = func(dgs []Ge, fgs ...Ge) Ge {
		if dgs == nil || len(dgs) <= 0 {
			return DeleteFrom(fgs...)
		}
		return NewJoiner(
			[]Ge{NewJoiner(dgs, ", ", "DELETE ", "", false),
				NewJoiner(fgs, ", ", "FROM ", "", false)}, " ", "", "", false,
		)
	}
	DeleteFrom = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "DELETE FROM ", "", false) }
)
View Source 
var (
	// DropTable defines gen SQL like `DROP TABLE table_name`
	DropTable = func(table Ge) Ge { return NewJoiner([]Ge{Drop, Table, table}, " ", "", "", false) }
	// DropView defines gen SQL like `DROP VIEW view_name`
	DropView = func(view Ge) Ge { return NewJoiner([]Ge{Drop, View, view}, " ", "", "", false) }
	// DropEvent defines gen SQL like `DROP EVENT event_name`
	DropEvent = func(event Ge) Ge { return NewJoiner([]Ge{Drop, Event, event}, " ", "", "", false) }
	// DropProcedure defines gen SQL like `DROP PROCEDURE procedure_name`
	DropProcedure = func(procedure Ge) Ge { return NewJoiner([]Ge{Drop, Procedure, procedure}, " ", "", "", false) }
)
View Source 
var (
	Insert = func(table Ge, gs ...Ge) Ge {
		return NewJoiner([]Ge{P("INSERT INTO"), table,
			NewJoiner(gs, ", ", "", "", true)},
			" ", "", "", false)
	}
	Values = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "VALUES ", "", true) }
)
View Source 
var (
	// As alias for Alias
	As = Alias
	// Alias defines gen SQL like `column AS c` or `table AS t`
	Alias = func(g Ge, as string) Ge { return &alias{g, as} }
	// Arg defines gen SQL like `?` only means an argument
	Arg = func(p interface{}) Ge { return &arg{p} }
	// From defines gen SQL like `FROM ...`
	From = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "FROM ", "", false) }
)
View Source 
var (
	LeftJoin  = func(gs ...Ge) Ge { return NewJoiner(gs, " ", "LEFT JOIN ", "", false) }
	RightJoin = func(gs ...Ge) Ge { return NewJoiner(gs, " ", "RIGHT JOIN ", "", false) }
	InnerJoin = func(gs ...Ge) Ge { return NewJoiner(gs, " ", "INNER JOIN ", "", false) }

	On = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "ON ", "", true) }
)
View Source 
var (
	Select = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "SELECT ", "", false) }

	OrderBy   = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "ORDER BY ", "", false) }
	Asc       = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "", " ASC", false) }
	Desc      = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "", " DESC", false) }
	AscGroup  = func(gs ...Ge) Ge { return NewJoinerWithAppend(gs, ", ", "", "", " ASC", false) }
	DescGroup = func(gs ...Ge) Ge { return NewJoinerWithAppend(gs, ", ", "", "", " DESC", false) }

	GroupBy = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "GROUP BY ", "", false) }

	Having = func(gs ...Ge) Ge { return NewJoiner(gs, "", "HAVING ", "", false) }
)
View Source 
var (
	Update = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "UPDATE ", "", false) }
	Set    = func(gs ...Ge) Ge { return NewJoiner(gs, ", ", "SET ", "", false) }
	SetEq  = func(c C, v interface{}) Ge { return &wC{c, "=", v, "", ""} }
)
View Source 
var (
	Where      = func(gs ...Ge) Ge { return NewJoiner(gs, "", "WHERE ", "", false) }
	And        = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "AND ", "", false) }
	Or         = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "OR ", "", false) }
	Not        = func(g Ge) Ge { return NewJoiner([]Ge{g}, "", "!", "", false) }
	AndGroup   = func(gs ...Ge) Ge { return NewJoiner(gs, " AND ", "", "", true) }
	OrGroup    = func(gs ...Ge) Ge { return NewJoiner(gs, " OR ", "", "", true) }
	Eq         = func(c C, v interface{}) Ge { return &wC{c, "=", v, "(", ")"} }
	NotEq      = func(c C, v interface{}) Ge { return &wC{c, "!=", v, "(", ")"} }
	Gt         = func(c C, v interface{}) Ge { return &wC{c, ">", v, "(", ")"} }
	GtEq       = func(c C, v interface{}) Ge { return &wC{c, ">=", v, "(", ")"} }
	Lt         = func(c C, v interface{}) Ge { return &wC{c, "<", v, "(", ")"} }
	LtEq       = func(c C, v interface{}) Ge { return &wC{c, "<=", v, "(", ")"} }
	Like       = func(c C, v interface{}) Ge { return &wLC{c, "%", "%", v} }
	LeftLike   = func(c C, v interface{}) Ge { return &wLC{c, "%", "", v} }
	RightLike  = func(c C, v interface{}) Ge { return &wLC{c, "", "%", v} }
	Instr      = func(c C, v interface{}) Ge { return &wITC{c, v} }
	In         = func(c C, vs ...interface{}) Ge { return &wIC{c, vs} }
	BetweenAnd = func(c C, l, r interface{}) Ge { return &wBAC{c, l, r} }
)

Functions

func CreateTableBuilder 

func CreateTableBuilder() *createTableBuilder

func DeleteBuilder 

func DeleteBuilder() *deleteBuilder

func InsertBuilder 

func InsertBuilder() *insertBuilder

func SelectBuilder 

func SelectBuilder() *selectBuilder

func UpdateBuilder 

func UpdateBuilder() *updateBuilder

Types

type Builder 

type Builder interface {
	Build() (string, []interface{})
}

type C 

type C = Column

C type alias for Column

type Column 

type Column string

Column type def for string

func (Column) SQL 

func (c Column) SQL() (string, []interface{})

type Ge 

type Ge = Generator

Ge type alias for Generator

type Generator 

type Generator interface {
	SQL() (string, []interface{})
}

Generator defines Interface its

type Joiner 

type Joiner struct {
	// contains filtered or unexported fields
}

func NewJoiner 

func NewJoiner(gs []Generator, sep, prefix, suffix string, group bool) *Joiner

func NewJoinerWithAppend 

func NewJoinerWithAppend(gs []Generator, sep, prefix, suffix, append string, group bool) *Joiner

func (*Joiner) SQL 

func (j *Joiner) SQL() (string, []interface{})

type P 

type P = C

P type alias for C

type T 

type T = C

T type alias for C

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.