bsq

package
v0.9.1 Latest Latest
Warning

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

 Go to latest Published: Dec 19, 2024 License: AGPL-3.0 Imports: 10 Imported by: 3

Documentation

Overview

Package bsq helps to Build Structured Queries for SQL in a maintainable manner. 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. – Why do we need something different from the “usual” approach?

I.e. consider the problem of keeping the Go code in line with the DB model to be the primary maintenance task: It must be easy to find all parts of the code that have to change when the DB model changes. Often, query builders present an 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=?

While this may help if you are not familiar with SQL, it doesn't do much. What if the users table gets a column renamed, removed or even gets a complely new column? How do you find all the relevant 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. Package 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): 

query, _ := NewQuery(dialect,
	"SELECT ", &tUsers.Name, ", ", &tUsers.Age,
	" FROM ", &tUsers,
	" WHERE ", &tUsers.ID, '=', P("id"),
)
fmt.Println(query.String())

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: 

q, _ := NewQuery(dialect, Select{
	Columns: ColsLs(&tUsers, &tUsers.ID), // all from tUsers without ID
	Key: Cols{&tUsers.ID},
})
sql := q.String()

Defining a DB Schema in Go

TODO!

See also: dbtest.VerifyTables, dbtest.VerifyTable

Writing Queries

TODO!

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func CamelToSnake added in v0.7.2 

func CamelToSnake(name string) string

func ColP added in v0.7.2 

func ColP(sql ...any) any

func ColsOf 

func ColsOf(t TableDef, without ...*Column) (cols []any)

func DefaultNameMap added in v0.7.2 

func DefaultNameMap() func(string) string

func EachColumn added in v0.7.2 

func EachColumn(def TableDef, do func(*Column, *reflect.StructField) error) error
Example 
type Entity struct {
	Table
	ID Column
}
tbl := struct {
	Entity
	Name Column
}{
	Entity: Entity{
		Table: Table{Name: "yet_another_table"},
		ID:    Column{Name: "id"},
	},
	Name: Column{Name: "name"},
}
err := EachColumn(&tbl, func(c *Column, f *reflect.StructField) error {
	fmt.Println(c.Name, "->", f.Name)
	return nil
})
fmt.Println(err)

Output:

id -> ID
name -> Name
<nil>

func EachTable added in v0.7.2 

func EachTable(do func(TableDef) error) error

func FullJoinEq added in v0.7.2 

func FullJoinEq(from, to ColumnRef) any

func IndexedVars added in v0.7.2 

func IndexedVars(fmt string) func() BindVars

func InitSchema added in v0.7.2 

func InitSchema(schema string, opts *InitOpts, tables ...TableDef) error

InitSchema adds the tables to the list of known tables. It does not check for duplicates because there may be reasonable applications that hold table definitions for equally named tables from different databases. The schema name of each table is set according to opts.ForceSchema and the default alias of each table is checked according to opts.MustAlias.

func JoinEq 

func JoinEq(from, to ColumnRef) any

func LeftJoinEq added in v0.7.2 

func LeftJoinEq(from, to ColumnRef) any

func NamedVars added in v0.7.2 

func NamedVars(fmt string) func() BindVars

func Out added in v0.7.2 

func Out(sql ...any) any

func P 

func P(key any) any

P adds a parameter to a query definition. The parameter key must either be a string or a ColumnRef to identify the parameter. The key lets the query generator recognize the same parameter when used several times in a query.

Example 
example := func(bv func() BindVars, p, q any) {
	qry, _ := NewQuery(DefaultSQL(bv, nil), p, AND, q)
	fmt.Println(len(qry.ParamCols()), "parameters:", qry.String())
}

example(PositionalVars("?"), P("p1"), P("p1"))

example(IndexedVars("$%d"), P("p1"), P("p1"))
example(IndexedVars("$%d"), P("p1"), P("p2"))

example(NamedVars(":%s"), P("p1"), P("p1"))
example(NamedVars(":%s"), P("p1"), P("p2"))

Output:

2 parameters: ? AND ?
1 parameters: $1 AND $1
2 parameters: $1 AND $2
1 parameters: :p1 AND :p1
2 parameters: :p1 AND :p2

func PEq 

func PEq(col ColumnRef) any

PEq adds 'column-name'='bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PEq(&testTable.Name))
fmt.Println(q.String())

Output:

Name=?

func PGe added in v0.7.2 

func PGe(col ColumnRef) any

PGe adds 'column-name'>='bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PGe(&testTable.Name))
fmt.Println(q.String())

Output:

Name>=?

func PGt added in v0.7.2 

func PGt(col ColumnRef) any

PGt adds 'column-name'>'bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PGt(&testTable.Name))
fmt.Println(q.String())

Output:

Name>?

func PLe added in v0.7.2 

func PLe(col ColumnRef) any

PLe adds 'column-name'<='bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PLe(&testTable.Name))
fmt.Println(q.String())

Output:

Name<=?

func PLt added in v0.7.2 

func PLt(col ColumnRef) any

PLt adds 'column-name'<'bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PLt(&testTable.Name))
fmt.Println(q.String())

Output:

Name<?

func PNe added in v0.7.2 

func PNe(col ColumnRef) any

PNe adds 'column-name'<>'bind-var' to the query

Example 
q, _ := NewQuery(testDialect, PNe(&testTable.Name))
fmt.Println(q.String())

Output:

Name<>?

func PositionalVars added in v0.7.2 

func PositionalVars(sym string) func() BindVars

func QueryCreate added in v0.2.0 

func QueryCreate(db sqlize.Querier, q *Query, args ...any) (id int64, err error)

TODO what about prepared queries

func QueryCreateContext added in v0.9.0 

func QueryCreateContext(ctx context.Context, db sqlize.Querier, q *Query, args ...any) (id int64, err error)

func RightJoinEq added in v0.7.2 

func RightJoinEq(from, to ColumnRef) any

Types

type BindVars added in v0.7.2 

type BindVars interface {
	Param(name string, col *Column) (string, error)
	ParamColumns() []*Column
}

type Column 

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

func (*Column) AName added in v0.7.2 

func (c *Column) AName() string

func (*Column) ColumnRef added in v0.7.2 

func (c *Column) ColumnRef() *Column

func (*Column) In added in v0.7.2 

func (c *Column) In(t TableDef) ColumnRef

In panics if c's table is different from t

func (*Column) Index added in v0.7.2 

func (c *Column) Index() int

func (*Column) QName added in v0.7.2 

func (c *Column) QName() string

QName returns the column's name qualified with its table's name. If qualifyTable is ture the table name itself will be qualified as well.

func (*Column) Table 

func (c *Column) Table() TableDef

type ColumnRef added in v0.7.2 

type ColumnRef interface {
	ColumnRef() *Column
	Table() TableDef
	QName() string
	AName() string
}

type Columns added in v0.7.2 

type Columns []*Column

func Cols 

func Cols(t TableDef, without ...*Column) (cols Columns)

Cols returns all columns of t except the columns in 'without'.

func (Columns) Defn added in v0.7.2 

func (cs Columns) Defn(add []any) []any

type CreateDialect added in v0.7.2 

type CreateDialect interface {
	DefCreate(idColumn *Column, cols ...*Column) *Query
	QueryCreateContext(ctx context.Context, db sqlize.Querier, q *Query, args ...any) (id int64, err error)
}

type Delete 

type Delete struct {
	Key Columns
}
Example 
q := must.Ret(NewQuery(testDialect, Delete{
	Key: Columns{&testTable.ID},
}))
fmt.Println(q.String())

q = must.Ret(NewQuery(testDialect, Delete{
	Key: Columns{&testTable.ID, &testTable.Name},
}))
fmt.Println(q.String())

Output:

DELETE FROM thing WHERE ID=?
DELETE FROM thing WHERE ID=? AND Name=?

func (Delete) Defn added in v0.7.2 

func (del Delete) Defn() []any

type Dialect 

type Dialect interface {
	NewContext() DialectCtx
	Quote(name string) string
	Parameter(ctx DialectCtx, key string, col *Column) (string, error)
}

func DefaultSQL added in v0.7.2 

func DefaultSQL(bv func() BindVars, quote map[string]string) Dialect

type DialectCtx added in v0.7.2 

type DialectCtx interface {
	WriteContext() *WriteCtx
	ParamColumns() []*Column
	OutColumns() []*Column
	Close()
}

type ExplicitMap added in v0.7.2 

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

func NewExplicitMap added in v0.7.2 

func NewExplicitMap(mapping map[string]string, fallback func(string) string) *ExplicitMap

func (ExplicitMap) Map added in v0.7.2 

func (m ExplicitMap) Map(name string) string

func (ExplicitMap) With added in v0.7.2 

func (m ExplicitMap) With(mapping map[string]string) ExplicitMap

type IndexBindVars added in v0.7.2 

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

func NewIndexBindVars added in v0.7.2 

func NewIndexBindVars(format string) *IndexBindVars

func (*IndexBindVars) Param added in v0.7.2 

func (bv *IndexBindVars) Param(key string, c *Column) (string, error)

func (*IndexBindVars) ParamColumns added in v0.7.2 

func (bv *IndexBindVars) ParamColumns() []*Column

type InitOpts 

type InitOpts struct {
	// If true InitSchema will set the passed schema on all tables. Otherwise,
	// the schema will be set only if the table does not yet have a schema name.
	ForceSchema bool

	// If true InitSchema will return an error if a table does not have a
	// default alias.
	MustAlias bool

	// Used to set column names from struct field names. This is only used on
	// columns that do not yet have a name set. If ColNameMap is nil, the struct
	// filed name will be used as is.
	ColNameMap func(fieldname string) (columname string)
}

type Insert 

type Insert struct {
	Table   TableDef
	Columns Columns
}
Example 
ins := Insert{Table: &testTable} // Isert all columns of table

q := must.Ret(NewQuery(testDialect, ins))
fmt.Println(q.String())

Output:

INSERT INTO thing (ID, Name) VALUES (?, ?)

func (Insert) Defn added in v0.7.2 

func (ins Insert) Defn() []any

type L added in v0.7.2 

type L struct {
	Elems           []any
	Start, Sep, End string
}

func Ls added in v0.7.2 

func Ls(es ...any) L

type NameBindVars added in v0.7.2 

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

func NewNamedBindVars added in v0.7.2 

func NewNamedBindVars(format string) *NameBindVars

func (*NameBindVars) Param added in v0.7.2 

func (bv *NameBindVars) Param(key string, c *Column) (string, error)

func (*NameBindVars) ParamColumns added in v0.7.2 

func (bv *NameBindVars) ParamColumns() []*Column

type PositionBindVars added in v0.7.2 

type PositionBindVars struct {
	Symbol string
	// contains filtered or unexported fields
}

func (*PositionBindVars) Param added in v0.7.2 

func (bv *PositionBindVars) Param(_ string, c *Column) (string, error)

func (*PositionBindVars) ParamColumns added in v0.7.2 

func (bv *PositionBindVars) ParamColumns() []*Column

type Prunable added in v0.7.2 

type Prunable int
const (
	PruneDefn Prunable = (1 << iota)
	PruneTabs
	PruneString
)

type Queries added in v0.7.2 

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

Queries is used to collect a set of queries and later apply an operation to all queries in the set - typically to initialise and/or prepare them. A zero value is valid to start collecting queries.

func NewQueries added in v0.7.2 

func NewQueries(sub ...*Queries) *Queries

NewQueries is used to hierachically create a collection of queries. The sub-collections are passed as arguemnts. Then the new Queries object can be used to add more queries.

func (*Queries) Add added in v0.7.2 

func (qs *Queries) Add(q *Query) *Query

Add adds a queriy to the collection. Add is safe for concurrent use, also with ForEach.

func (*Queries) ForEach added in v0.7.2 

func (qs *Queries) ForEach(clear bool, do func(*Query) error) error

func (*Queries) Init added in v0.7.2 

func (qs *Queries) Init(clear bool, d Dialect) error

func (*Queries) MustAdd added in v0.7.2 

func (qs *Queries) MustAdd(q *Query, err error) *Query

MustAdd panics with err if err is not nil. Otherwise it calls qs.Add(q).

func (*Queries) Prepare added in v0.7.2 

func (qs *Queries) Prepare(clear bool, db *sql.DB, initWith Dialect) error

type Query 

type Query struct {
	// contains filtered or unexported fields
}
Example 
testTable := struct {
	Table
	ID, Name Column
}{Table: Table{Name: "test", DefaultAlias: "t"}}
InitSchema("", nil, &testTable)

testAlias := testTable.As("t2")

q, err := NewQuery(testDialect,
	SELECT, Out(&testTable.ID, testTable.Name.In(testAlias)),
	FROM, Ls(&testTable, testAlias),
	WHERE, P("name"), IS_NOT_NULL,
	AND, PGe(&testTable.ID), AND, PLt(&testTable.ID),
)
if err != nil {
	fmt.Println(err)
	return
}
fmt.Println(q.String())
fmt.Println("       ^ o1  ^o2                                ^ p1              ^ p2        ^ p3")
fmt.Print("Param Columns")
for i, c := range q.pCols {
	if c == nil {
		fmt.Printf(" %d:-", i+1)
	} else {
		fmt.Printf(" %d:%s", i+1, c.QName())
	}
}
fmt.Print("\nOutput")
for i, c := range q.oCols {
	if c == nil {
		fmt.Printf(" %d:-", i+1)
	} else {
		fmt.Printf(" %d:%s", i+1, c.QName())
	}
}

Output:

SELECT t.ID, t2.Name FROM test t, test t2 WHERE ? IS NOT NULL AND t.ID>=? AND t.ID<?
       ^ o1  ^o2                                ^ p1              ^ p2        ^ p3
Param Columns 1:- 2:test.ID 3:test.ID
Output 1:test.ID 2:test.Name

func DefCreateQuery added in v0.7.2 

func DefCreateQuery(d Dialect, idColumn *Column, cols ...*Column) (*Query, error)

func DefQuery added in v0.7.2 

func DefQuery(defn ...any) *Query

func DefUpsertQuery added in v0.7.2 

func DefUpsertQuery(d Dialect, ups Upsert) (*Query, error)

func NewCreateQuery added in v0.7.2 

func NewCreateQuery(d Dialect, idColumn *Column, cols ...*Column) (*Query, error)

func NewQuery added in v0.2.0 

func NewQuery(d Dialect, defn ...any) (*Query, error)

NewQuery first checks once if defn has only one element with a Defn() []any method. If so, it uses the result from Defn() to replace defn. Then it creates and Inits a new Query with Dialect=d and Defn=defn.

func NewQueryPrep added in v0.7.2 

func NewQueryPrep(db *sql.DB, d Dialect, defn ...any) (*Query, error)

func NewQueryPrepContext added in v0.7.2 

func NewQueryPrepContext(ctx context.Context, db *sql.DB, d Dialect, defn ...any) (*Query, error)

func NewUpsertQuery added in v0.7.2 

func NewUpsertQuery(d Dialect, ups Upsert) (*Query, error)

func (*Query) Dialect added in v0.2.0 

func (q *Query) Dialect() Dialect

func (*Query) Exec added in v0.2.0 

func (q *Query) Exec(db sqlize.Querier, args ...any) (sql.Result, error)

func (*Query) ExecContext added in v0.2.0 

func (q *Query) ExecContext(ctx context.Context, db sqlize.Querier, args ...any) (sql.Result, error)

func (*Query) Init added in v0.7.2 

func (q *Query) Init(d Dialect) error

Init initializes the Query according to its definition. If d is not nil it will initialize q.dialect. Trying to override an already set dialect results in an error. Init must not be called before InitSchema for the tables used in the query. One may use Queries to initialize a set of global Query variables.

func (*Query) OutCols added in v0.7.2 

func (q *Query) OutCols() []*Column

func (*Query) ParamCols added in v0.7.2 

func (q *Query) ParamCols() []*Column

func (*Query) Prepare added in v0.2.0 

func (q *Query) Prepare(db *sql.DB) (err error)

func (*Query) PrepareContext added in v0.2.0 

func (q *Query) PrepareContext(ctx context.Context, db *sql.DB) (err error)

func (*Query) Prune added in v0.7.2 

func (q *Query) Prune(p Prunable)

func (*Query) Query added in v0.2.0 

func (q *Query) Query(db sqlize.Querier, args ...any) (*sql.Rows, error)

func (*Query) QueryContext added in v0.2.0 

func (q *Query) QueryContext(ctx context.Context, db sqlize.Querier, args ...any) (*sql.Rows, error)

func (*Query) QueryRow added in v0.2.0 

func (q *Query) QueryRow(db sqlize.Querier, args ...any) *sql.Row

func (*Query) QueryRowContext added in v0.2.0 

func (q *Query) QueryRowContext(ctx context.Context, db sqlize.Querier, args ...any) *sql.Row

func (*Query) Stmt added in v0.7.2 

func (q *Query) Stmt() *sql.Stmt

func (*Query) String added in v0.7.2 

func (q *Query) String() string

type Select 

type Select tabColsKey
Example 
sel := Select{Table: &testTable} // Select * from table
q := must.Ret(NewQuery(testDialect, sel))
fmt.Println(q.String())

sel = Select{Columns: Cols(&testTable)} // Select all columns from table
q = must.Ret(NewQuery(testDialect, sel))
fmt.Println(q.String())

sel = Select{ // Select column(s) by key
	Columns: nil,
	Key:     Columns{&testTable.ID},
}
q = must.Ret(NewQuery(testDialect, sel))
fmt.Println(q.String())

sel = Select{ // Select column(s) by key
	Columns: Columns{&testTable.Name},
	Key:     Columns{&testTable.ID},
}
q = must.Ret(NewQuery(testDialect, sel))
fmt.Println(q.String())

sel = Select{ // Select column(s) by key
	Columns: Columns{&testTable.Name},
	Key:     Columns{&testTable.ID, &testTable.Name},
}
q = must.Ret(NewQuery(testDialect, sel))
fmt.Println(q.String())

Output:

SELECT * FROM thing
SELECT ID, Name FROM thing
SELECT Name FROM thing WHERE ID=?
SELECT Name FROM thing WHERE ID=?
SELECT Name FROM thing WHERE ID=? AND Name=?

func (Select) Defn added in v0.7.2 

func (sel Select) Defn() []any

type Table 

type Table struct {

	// The schema name of the declared table. It can also be set with
	// InitSchema.
	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.

To see how to use it, read example of EachColumn and InitSchema.

func (*Table) Alias 

func (t *Table) Alias() string

func (*Table) As added in v0.7.2 

func (t *Table) As(alias string) TableDef

As creates a new alias of the table.

func (*Table) QName added in v0.7.2 

func (t *Table) QName() string

func (*Table) TableDef added in v0.7.2 

func (t *Table) TableDef() *Table

type TableDef added in v0.7.2 

type TableDef interface {
	TableDef() *Table
	Alias() string
}

type Update 

type Update tabColsKey
Example 
upd := Update{Table: &testTable} // Update all columns
q := must.Ret(NewQuery(DefaultSQL(IndexedVars("$%d"), nil), upd))
fmt.Println(q.String())

upd = Update{Key: Columns{&testTable.ID}} // Update all but key columns
q = must.Ret(NewQuery(DefaultSQL(IndexedVars("$%d"), nil), upd))
fmt.Println(q.String())

upd = Update{
	Columns: Columns{&testTable.Name},
	Key:     Columns{&testTable.ID, &testTable.Name},
}
q = must.Ret(NewQuery(DefaultSQL(IndexedVars("$%d"), nil), upd))
fmt.Println(q.String())

Output:

UPDATE thing SET ID=$1, Name=$2
UPDATE thing SET Name=$1 WHERE ID=$2
UPDATE thing SET Name=$1 WHERE ID=$2 AND Name=$1

func (Update) Defn added in v0.7.2 

func (upd Update) Defn() []any

type Upsert 

type Upsert tabColsKey

func (*Upsert) Complete added in v0.7.2 

func (ups *Upsert) Complete() error

type UpsertDialect added in v0.7.2 

type UpsertDialect interface {
	DefUpsert(defn Upsert) *Query
}

type WriteCtx added in v0.7.2 

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

func (*WriteCtx) OutColumns added in v0.7.2 

func (c *WriteCtx) OutColumns() []*Column

func (*WriteCtx) WriteContext added in v0.7.2 

func (c *WriteCtx) WriteContext() *WriteCtx

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.

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.