bsq

package
v0.1.0 Latest Latest
Warning

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

 Go to latest Published: Sep 18, 2021 License: MIT Imports: 11 Imported by: 0

Documentation

Overview

Package bsq helps to build maintainable structured queries for SQL. Unlike many query builders out there it does not strive to formalize SQL as Go DSL. Its primary focus it code maintainability when working with SQL in your code. I.e. consider the problem of keeping the Go code in line with the DB model to be the primary maintenance task: It must easy to find all parts of the code that have to change when the DB model changes.

Now, consider some imaginary fluent SQL builder API that let's you do something like this: 

sql := Select("name", "age").From("users").Where(EqArg("id"))

And this might yield the expected string: 

SELECT name, age FROM users WHERE id=?

That's fine and the fluent API might have helped to spell SQL keywords correctly. But what if the users table renames a column or gets a new column? How to you find the Go code that relate to the users table? At best a clever tool can search for all uses of the From() method that gets the string "users" as an argument. Without such tool you have to try a full text search.

bsq takes a different approach. This approach starts with a minimal replica of the DB model made of Go objects: 

var tUsers = struct {
	Table
	ID, Name, Age Column
}{
	Table: Table{TableName: "users"},
}

The extra effort for this replica helps much with the maintainability problem. Consider this conceptual example (that omits some syntactic sugar which actually would improve the code): 

sql := WriteSQL("SELECT ", &tUsers.Name, ", ", &tUsers.Age,
                " FROM ", &tUsers,
                " WHERE ", &tUsers.ID, '=', P("id"))

While this does not help with SQL syntax, one now can simply use standard developer tools to locate all references to the tUsers variable. And renaming a column can be done at one single location where the Name column is declared in the replica.

Package bsq builds on this concept to simplify the creation of SQL statements. E.g. to get the example SELECT statement one would actually write with bsq: 

sql := MustSQLString(DefaultDialect, // Std SQL with positional parameters
    Select{
        Cols:   ColsOf(&tUsers, &tUsers.ID),
        EqCols: Cols(&tUsers.ID),
    })

For the further details read the following

Example 
tUsers := struct {
	Table
	ID, Name, Age Column
}{
	Table: Table{Name: "users"},
}
InitDefns("", nil, &tUsers)
sql, _ := MustSQLString(
	DefaultDialect, // Std SQL with positional parameters
	0,
	Select{
		Columns:   ColsOf(&tUsers, &tUsers.ID),
		EqColumns: Cols(&tUsers.ID),
	})
fmt.Println(sql)

Output:

SELECT Name, Age FROM users WHERE ID=?

Index

Examples

Constants

This section is empty.

Variables

View Source 
var (
	DefaultDialect = StdSQL(PositionalParams("?"))
)

Functions

func AddDialect 

func AddDialect(name string, d Dialect) error

func CreateArgs 

func CreateArgs(db sqlize.SQL, q *QueryDefn, args ...interface{}) (id int64, err error)

CreateArgs executes a Create query defined as q with argument values passed as args. Args must match the argument mapping of the used sql driver.

func CreateNamedArgs 

func CreateNamedArgs(db sqlize.SQL, q *QueryDefn, args ...sql.NamedArg) (
	id int64,
	err error,
)

CreateNamedArgs executes a Create query defined as q with arguments passed as sql.NamedArgs. Is uses the ArgMap computed by q's Dialect to map the arguments.

func ForEachDefn 

func ForEachDefn(do func(Tabler) (done bool)) bool

func InitDefns 

func InitDefns(schema string, opts *InitOpts, tableDefns ...Tabler) error

Initialize Table definitions of the replica DB model. Without initialization the model cannot be used. See package bsq example.

func MustAddDialect 

func MustAddDialect(name string, d Dialect)

func MustInitDefns 

func MustInitDefns(schema string, opts *InitOpts, tableDefns ...Tabler)

func MustSQLString 

func MustSQLString(d Dialect, flags WriteFlag, stmt ...interface{}) (string, interface{})

func QuoteKeywords 

func QuoteKeywords(keywords []string, defn string) (escaped string)

func SQLString 

func SQLString(d Dialect, flags WriteFlag, stmt ...interface{}) (string, interface{}, error)

func UpsertOnConflict 

func UpsertOnConflict(tbl *Table, conflict, set []Columner) []interface{}
Example 
defn := UpsertOnConflict(
	&testTable.Table,
	Cols(&testTable.Name),
	ColsOf(&testTable, &testTable.Name),
)
stmt, _ := MustSQLString(DefaultDialect, 0, defn...)
fmt.Println(stmt)

Output:

INSERT INTO bsq.users (Name, ID, Age) VALUES (?, ?, ?) ON CONFLICT (Name) DO UPDATE SET ID=?, Age=?

func VisitDefn 

func VisitDefn(tdefn Tabler, v DefnVisitor) error

func WriteSQL 

func WriteSQL(wr io.Writer, d Dialect, flags WriteFlag, stmt ...interface{}) (
	params interface{},
	err error,
)

WriteSQL writes all elements of an SQL stmt to wr using the SQL Dialect d. Elements may be runes, strings, query parameters P and everything that implements SQLWriterTo. WriteSQL is the core of bsq but in most caes it will not be used diretcly by applications. E.g. QueryDefn lets you define queries as global variables that lazily build the query protected by sync.Once.

Example 
var tbl = struct {
	Table
	Col1 Column
}{
	Table: Table{Name: "users", Schema: "bsq"},
}
InitDefns("", nil, &tbl)
var sb strings.Builder
alias := tbl.Alias("u")
WriteSQL(&sb, DefaultDialect, 0,
	"SELECT ", ColList(tbl.Col1.Q(""), alias.Q(&tbl.Col1)),
	" FROM ", alias, WHERE, &tbl.Col1, '=', P("foo"),
)
fmt.Println(sb.String())

Output:

SELECT bsq.users.Col1, u.Col1 FROM bsq.users u WHERE Col1=?

Types

type ArgMap 

type ArgMap = func([]sql.NamedArg) []interface{}

type CRUD 

type CRUD struct {
	CreateQuery QueryDefn
	ReadQuery   QueryDefn
	UpdateQuery QueryDefn
	DeleteQuery QueryDefn
	// contains filtered or unexported fields
}

func NewCRUD 

func NewCRUD(d Dialect, id Columner) *CRUD

func (*CRUD) Create 

func (crud *CRUD) Create(db sqlize.SQL, args ...interface{}) (id int64, err error)

func (*CRUD) CreateNamed 

func (crud *CRUD) CreateNamed(db sqlize.SQL, args ...sql.NamedArg) (id int64, err error)

func (*CRUD) Delete 

func (crud *CRUD) Delete(db sqlize.SQL, id interface{}) (sql.Result, error)

func (*CRUD) Read 

func (crud *CRUD) Read(db sqlize.SQL, id interface{}) *sql.Row

func (*CRUD) Update 

func (crud *CRUD) Update(db sqlize.SQL, id interface{}, args ...interface{}) (sql.Result, error)

func (*CRUD) UpdateNamed 

func (crud *CRUD) UpdateNamed(db sqlize.SQL, id interface{}, args ...sql.NamedArg) (sql.Result, error)

type Column 

type Column struct {
	Name string
	// contains filtered or unexported fields
}

func (*Column) ColumnDefn 

func (col *Column) ColumnDefn() *Column

func (*Column) Q 

func (col *Column) Q(qualifier string) Columner

func (Column) Table 

func (col Column) Table() Tabler

func (Column) WriteTo 

func (col Column) WriteTo(w io.Writer, ctx *writeContext) error

type Columner 

type Columner interface {
	SQLWriterTo
	Table() Tabler
	Q(qualifier string) Columner
	ColumnDefn() *Column
}

Columner is an abstraction for model columns that can be written as SQL. E.g. Column is a Columner.

func Cols 

func Cols(cs ...Columner) []Columner

func ColsOf 

func ColsOf(t Tabler, exclude ...Columner) []Columner

type Create 

type Create struct {
	IDColumn Columner
	Set      []Columner
}

Create writes an SQL statement that inserts data into a table and returns an automatically generated int64 id for the new row. This corresponds to the sql.Result.LastInsertId() feature, which—unfortunately—is not implemented by all sql drivers. Dialects for bsq shall find a reasonable implementation.

func (Create) WriteTo 

func (cre Create) WriteTo(w io.Writer, ctx *writeContext) error

WriteTo implements SQLWriterTo.

type DefnVisitor 

type DefnVisitor interface {
	Table(defn Tabler) error
	Column(c *Column, field *reflect.StructField) error
}

type Delete 

type Delete struct {
	From      *Table
	EqColumns []Columner
}
Example 
del := Delete{EqColumns: Cols(&testTable.ID)}
stmt, _ := MustSQLString(DefaultDialect, 0, del)
fmt.Println(stmt)

Output:

DELETE FROM bsq.users WHERE ID=?

func (Delete) WriteTo 

func (del Delete) WriteTo(w io.Writer, ctx *writeContext) error

type Dialect 

type Dialect interface {
	ParamNamer
	QuoteName(defn string) (escaped string)

	CreateStatement(tbl *Table, idCol Columner, setCols []Columner) []interface{}
	Create(db sqlize.SQL, query string, args ...interface{}) (id int64, err error)

	UpsertStatement(tbl *Table, conflict, set []Columner) []interface{}
}

func GetDialect 

func GetDialect(name string) Dialect

func StdSQL 

func StdSQL(pn ParamNamer) Dialect

type IndexedParams 

type IndexedParams string

func (IndexedParams) ArgMapper 

func (_ IndexedParams) ArgMapper(ctx interface{}) func([]sql.NamedArg) []interface{}

func (IndexedParams) ParamName 

func (ip IndexedParams) ParamName(p P, ctx interface{}) (string, interface{})

type InitDefn 

type InitDefn struct {
	Schema  string
	NameMap func(fieldname string) (columnname string)
	// contains filtered or unexported fields
}

func (*InitDefn) Column 

func (id *InitDefn) Column(c *Column, f *reflect.StructField) error

func (*InitDefn) Reset 

func (id *InitDefn) Reset()

func (*InitDefn) Table 

func (id *InitDefn) Table(t Tabler) error

type InitOpts 

type InitOpts struct {
	MustAlias bool
	NameMap   func(fieldname string) (columname string)
}

type Insert 

type Insert struct {
	Columns []Columner
}
Example 
ins := Insert{Columns: ColsOf(&testTable)}
stmt, _ := MustSQLString(DefaultDialect, 0, ins)
fmt.Println(stmt)

Output:

INSERT INTO bsq.users (ID, Name, Age) VALUES (?, ?, ?)

func (Insert) WriteTo 

func (ins Insert) WriteTo(w io.Writer, ctx *writeContext) error

type JoinEq 

type JoinEq struct {
	From, To Columner
	Outer    OuterJoin
}
Example 
join := JoinEq{From: &testTable.ID, To: &testTable.Name}
stmt, _ := MustSQLString(DefaultDialect, 0, join)
fmt.Println(stmt)

Output:

JOIN bsq.users ON (ID=Name)

func (JoinEq) WriteTo 

func (jeq JoinEq) WriteTo(w io.Writer, ctx *writeContext) error

type List 

type List struct {
	Pre, Sep, Post string
	Elems          interface{}
}

func ColList 

func ColList(cols ...Columner) List

func (List) WriteTo 

func (ls List) WriteTo(w io.Writer, ctx *writeContext) (err error)

type NamedParams 

type NamedParams string

func (NamedParams) ArgMapper 

func (_ NamedParams) ArgMapper(ctx interface{}) func([]sql.NamedArg) []interface{}

func (NamedParams) ParamName 

func (np NamedParams) ParamName(p P, ctx interface{}) (string, interface{})

type OuterJoin 

type OuterJoin string
const (
	Left  OuterJoin = " LEFT"
	Right OuterJoin = " RIGHT"
	Full  OuterJoin = " FULL"
)

type P 

type P string

P is used to declare query parameters in queries.

type PEq 

type PEq struct {
	Columner
}

func (PEq) WriteTo 

func (ceq PEq) WriteTo(w io.Writer, ctx *writeContext) (err error)

type ParamNamer 

type ParamNamer interface {
	ParamName(p P, ctx interface{}) (string, interface{})
	ArgMapper(ctx interface{}) ArgMap
}

type PositionalParams 

type PositionalParams string

func (PositionalParams) ArgMapper 

func (_ PositionalParams) ArgMapper(ctx interface{}) func([]sql.NamedArg) []interface{}

func (PositionalParams) ParamName 

func (pp PositionalParams) ParamName(p P, ctx interface{}) (string, interface{})

type QueryDefn 

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

func Query 

func Query(d Dialect, flags WriteFlag, query ...interface{}) QueryDefn

If d is nil, the dialect will be determined on first use.

func (*QueryDefn) Bind 

func (qdefn *QueryDefn) Bind(params ...sql.NamedArg) []interface{}

func (*QueryDefn) Dialect 

func (qdefn *QueryDefn) Dialect() Dialect

func (*QueryDefn) Exec 

func (qdefn *QueryDefn) Exec(
	db sqlize.SQL,
	args ...interface{},
) (sql.Result, error)

func (*QueryDefn) ExecNamed 

func (qdefn *QueryDefn) ExecNamed(
	db sqlize.SQL,
	args ...sql.NamedArg,
) (sql.Result, error)

func (*QueryDefn) MustSQL 

func (qdefn *QueryDefn) MustSQL() string

func (*QueryDefn) Prepare 

func (qdefn *QueryDefn) Prepare(db sqlize.SQL) (*sql.Stmt, error)

func (*QueryDefn) Query 

func (qdefn *QueryDefn) Query(
	db sqlize.SQL,
	args ...interface{},
) (*sql.Rows, error)

func (*QueryDefn) QueryNamed 

func (qdefn *QueryDefn) QueryNamed(
	db sqlize.SQL,
	args ...sql.NamedArg,
) (*sql.Rows, error)

func (*QueryDefn) QueryRow 

func (qdefn *QueryDefn) QueryRow(
	db sqlize.SQL,
	args ...interface{},
) *sql.Row

func (*QueryDefn) QueryRowNamed 

func (qdefn *QueryDefn) QueryRowNamed(
	db sqlize.SQL,
	args ...sql.NamedArg,
) *sql.Row

func (*QueryDefn) SQL 

func (qdefn *QueryDefn) SQL() (string, error)

func (QueryDefn) With 

func (qdefn QueryDefn) With(d Dialect, flags WriteFlag) QueryDefn

func (QueryDefn) WithDialect 

func (qdefn QueryDefn) WithDialect(d Dialect) QueryDefn

func (QueryDefn) WithFlags 

func (qdefn QueryDefn) WithFlags(flags WriteFlag) QueryDefn

type SQLWriterTo 

type SQLWriterTo interface {
	WriteTo(w io.Writer, ctx *writeContext) error
}

type Select 

type Select struct {
	Columns   []Columner
	From      Tabler
	EqColumns []Columner
}
Example 
sel := Select{
	Columns:   ColsOf(&testTable, &testTable.ID),
	EqColumns: Cols(&testTable.ID),
}
stmt, _ := MustSQLString(DefaultDialect, 0, sel)
fmt.Println(stmt)

Output:

SELECT Name, Age FROM bsq.users WHERE ID=?

func (Select) WriteTo 

func (sel Select) WriteTo(w io.Writer, ctx *writeContext) error

type Table 

type Table struct {
	// The schema name of the declared table. If left empty it can be
	// set with InitDefns.
	Schema string
	// The table name.
	Name string
	// A default alias for SQL queries. Specific aliases can be
	// created with the Alias method.
	DefaultAlias string
	// contains filtered or unexported fields
}

Table is used to declare the table data for the DB model replica. Replica are used to define SQL queries.

Example 
// Declare the table
var tableDefn = struct {
	Table
	ID, Name, Age Column
}{
	Table: Table{Name: "users", DefaultAlias: "u"},
}
// Fully initialize and set the schema name to "bsq"
InitDefns("bsq", nil, &tableDefn)
// Create a query
query, _ := MustSQLString(DefaultDialect, Alias|Qualify,
	Select{EqColumns: Cols(&tableDefn.ID)})
fmt.Println(query)

Output:

SELECT u.Name, u.Age FROM bsq.users u WHERE u.ID=?

func (*Table) Alias 

func (tbl *Table) Alias(alias string) TableAlias

func (*Table) String 

func (tbl *Table) String() string

func (*Table) TableData 

func (tbl *Table) TableData() *Table

func (Table) WriteTo 

func (tbl Table) WriteTo(w io.Writer, ctx *writeContext) error

type TableAlias 

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

func (TableAlias) Q 

func (a TableAlias) Q(col Columner) Columner

func (TableAlias) TableData 

func (a TableAlias) TableData() *Table

func (TableAlias) WriteTo 

func (a TableAlias) WriteTo(w io.Writer, ctx *writeContext) error

type Tabler 

type Tabler interface {
	SQLWriterTo
	TableData() *Table
}

Tabler is an abstraction for model tables that can be written as SQL. E.g. Table is a Tabler.

type Update 

type Update struct {
	Set       []Columner
	EqColumns []Columner
}
Example 
upd := Update{
	Set:       ColsOf(&testTable, &testTable.ID),
	EqColumns: Cols(&testTable.ID),
}
stmt, _ := MustSQLString(DefaultDialect, 0, upd)
fmt.Println(stmt)

Output:

UPDATE bsq.users SET Name=?, Age=? WHERE ID=?

func (Update) WriteTo 

func (upd Update) WriteTo(w io.Writer, ctx *writeContext) error

TODO Handle empty EqColumns

type Upsert 

type Upsert struct {
	Conflict []Columner
	Set      []Columner
}

func (Upsert) WriteTo 

func (ups Upsert) WriteTo(w io.Writer, ctx *writeContext) error

type WriteFlag 

type WriteFlag uint
const (
	// Write a Table's DefaultAlias to SQL statements if the alias is defined.
	Alias WriteFlag = (1 << iota)
	// Write qualified column names to SQL statements. If the table has an alias
	// the column is qualified with that alias. Otherwiese it is qualified with
	// the table name.
	Qualify

	// Wirte aliases and qualified columns to SQL statements.
	Explicit = Alias | Qualify
)

Source Files

View all Source files

Directories

Path Synopsis
Package keywords defines some handy SQL keywords as const strings for use with the bsq query builder.
 Package keywords defines some handy SQL keywords as const strings for use with the bsq query builder.
Package postgres implements the PostgreSQL dialect for bsq.
 Package postgres implements the PostgreSQL dialect for bsq.
Package sqlite3 implements the SQLite 3 dialect for bsq.
 Package sqlite3 implements the SQLite 3 dialect for bsq.

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.