sql

package
v0.55.2 Latest Latest
Warning

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

 Go to latest Published: Jul 21, 2024 License: BSD-3-Clause Imports: 13 Imported by: 0

Documentation

Overview

Package sql is an extension to standard library "database/sql.DB" that provide common functionality across DBMS.

Index

Examples

Constants

View Source 
const (
	DriverNameMysql    = "mysql"
	DriverNamePostgres = "postgres"
)

List of known driver name for database connection.

View Source 
const DefaultPlaceHolder = "?"

DefaultPlaceHolder define default placeholder for DML, which is placeholder for MySQL.

Variables

This section is empty.

Functions

func JoinValues 

func JoinValues(s ...[]any) (all []any)

JoinValues join list of slice of values into single slice.

Types

type Client 

type Client struct {
	*sql.DB
	ClientOptions
	TableNames []string // List of tables in database.
}

Client provide a wrapper for generic database instance.

func NewClient 

func NewClient(opts ClientOptions) (cl *Client, err error)

NewClient create and initialize new database client.

func (*Client) FetchTableNames 

func (cl *Client) FetchTableNames() (tableNames []string, err error)

FetchTableNames return the table names in current database schema sorted in ascending order.

func (*Client) Migrate 

func (cl *Client) Migrate(tableMigration string, fs http.FileSystem) (err error)

Migrate the database using list of SQL files inside a directory. Each SQL file in directory will be executed in alphabetical order based on the last state.

The table parameter contains the name of table where the state of migration will be saved. If its empty default to "_migration". The state including the SQL file name that has been executed and the timestamp.

func (*Client) TruncateTable 

func (cl *Client) TruncateTable(tableName string) (err error)

TruncateTable truncate all data on table `tableName` with cascade option. On PostgreSQL, any identity columns (for example, serial) will be reset back to its initial value.

type ClientOptions 

type ClientOptions struct {
	DriverName   string
	DSN          string
	MigrationDir string
}

ClientOptions contains options to connect to database server, including the migration directory.

type DMLKind 

type DMLKind string

DMLKind define the kind for Data Manipulation Language (DML). 

const (
	DMLKindDelete DMLKind = `DELETE`
	DMLKindInsert DMLKind = `INSERT`
	DMLKindSelect DMLKind = `SELECT`
	DMLKindUpdate DMLKind = `UPDATE`
)

List of valid DMLKind.

type Meta 

type Meta struct {

	// ListName contains list of column name.
	ListName []string

	// ListHolder contains list of column holder, as in "?" or "$x",
	// depends on the driver.
	ListHolder []string

	// ListValue contains list of column values, either for insert or
	// select.
	ListValue []any

	// ListWhereCond contains list of condition to be joined with
	// ListHolder.
	// The text is a free form, does not need to be a column name.
	ListWhereCond []string

	// ListWhereValue contains list of values for where condition.
	ListWhereValue []any

	// Index collect all holder integer value, as in "1,2,3,...".
	Index []any
	// contains filtered or unexported fields
}

Meta contains the DML meta data, including driver name, list of column names, list of column holders, and list of values.

Example (DeleteOnPostgresql) 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		qid   = 1
		qname = `hello`
	)

	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`OR name=`, qname)

	var q = fmt.Sprintf(`DELETE FROM t WHERE %s;`, meta.WhereFields())

	// db.Exec(q, meta.ListWhereValue...)

	fmt.Println(q)
	fmt.Println(meta.ListWhereValue)

}

Output:

DELETE FROM t WHERE id=$1 OR name=$2;
[1 hello]
Example (InsertOnPostgresql) 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindInsert)
		t    = Table{
			ID:   1,
			Name: `hello`,
		}
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)

	var q = fmt.Sprintf(`INSERT INTO t (%s) VALUES (%s);`, meta.Names(), meta.Holders())

	// db.Exec(q, meta.ListValue...)

	fmt.Println(q)
	fmt.Println(meta.ListValue)

}

Output:

INSERT INTO t (id,name) VALUES ($1,$2);
[1 hello]
Example (SelectOnPostgresql) 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t     = Table{}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)
	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`OR name=`, qname)

	var q = fmt.Sprintf(`SELECT %s FROM t WHERE %s;`, meta.Names(), meta.WhereFields())

	// db.QueryRow(q, meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(len(meta.ListValue))

}

Output:

SELECT id,name FROM t WHERE id=$1 OR name=$2;
WHERE= [1 hello]
2
Example (Subquery)

Sometime the query need to be stiched piece by piece. 

package main

import (
	"fmt"
	"strings"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name  string
		ID    int
		SubID int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		id    = 1
		subid = 500
		t     Table
		qb    strings.Builder
		idx   int
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`sub_id`, &t.SubID)
	meta.Bind(`name`, &t.Name)

	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())

	if id != 0 {
		idx = meta.BindWhere(``, id)
		fmt.Fprintf(&qb, ` AND id = $%d`, idx)
	}
	if subid != 0 {
		idx = meta.BindWhere(``, subid)
		fmt.Fprintf(&qb, ` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`, idx)
	}

	// db.Exec(qb.String(),meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(qb.String())
	fmt.Println(meta.ListWhereValue)

}

Output:

SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
[1 500]
Example (SubqueryWithIndex) 
package main

import (
	"fmt"
	"strings"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name  string
		ID    int
		SubID int
	}

	var (
		meta  = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		id    = 1
		subid = 500
		t     Table
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`sub_id`, &t.SubID)
	meta.Bind(`name`, &t.Name)

	var qb strings.Builder

	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())
	if id != 0 {
		qb.WriteString(` AND id = $%d`)
		meta.BindWhere(`id`, id)
	}
	if subid != 0 {
		qb.WriteString(` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`)
		meta.BindWhere(`sub_id`, subid)
	}

	var q = fmt.Sprintf(qb.String(), meta.Index...)

	// db.Exec(q, meta.ListWhereValue...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(meta.Index)
	fmt.Println(meta.ListWhereValue)

}

Output:

SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
[1 2]
[1 500]

func NewMeta 

func NewMeta(driverName string, dmlKind DMLKind) (meta *Meta)

NewMeta create new Meta using specific driver name. The driver affect the ListHolder value.

func (*Meta) Bind 

func (meta *Meta) Bind(colName string, val any)

Bind column name and variable for DML INSERT, SELECT, or UPDATE. It is a no-op for DML DELETE.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNameMysql, sql.DMLKindSelect)
		t    = Table{}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	var q = fmt.Sprintf(`SELECT %s FROM t;`, meta.Names())

	// db.Exec(q).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Printf("%T %T", meta.ListValue...)

}

Output:

SELECT id,name FROM t;
*int *string

func (*Meta) BindWhere 

func (meta *Meta) BindWhere(cond string, val any) int

BindWhere bind value for where condition.

The cond string is optional, can be a column name with operator or any text like "AND col=" or "OR col=".

It return the length of [Meta.ListHolder].

It is a no-operation for DML INSERT.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		vals = []any{
			int(1000),
			string(`JohnDoe`),
		}
		idx int
	)

	idx = meta.BindWhere(``, vals[0])
	fmt.Printf("WHERE id=$%d\n", idx)

	idx = meta.BindWhere(``, vals[1])
	fmt.Printf("AND name=$%d\n", idx)

	fmt.Println(meta.ListWhereValue)

}

Output:

WHERE id=$1
AND name=$2
[1000 JohnDoe]

func (*Meta) Holders 

func (meta *Meta) Holders() string

Holders generate string of holder, for example "$1, $2, ...", for DML INSERT-VALUES.

Example (Mysql) 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNameMysql, sql.DMLKindInsert)
		t    = Table{Name: `newname`, ID: 2}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("INSERT INTO t VALUES (%s);\n", meta.Holders())
}

Output:

INSERT INTO t VALUES (?,?);
Example (Postgres) 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindInsert)
		t    = Table{Name: `newname`, ID: 2}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("INSERT INTO t VALUES (%s);\n", meta.Holders())
}

Output:

INSERT INTO t VALUES ($1,$2);

func (*Meta) Names 

func (meta *Meta) Names() string

Names generate string of column names, for example "col1, col2, ...", for DML INSERT or SELECT.

It will return an empty string if kind is DML UPDATE or DELETE.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t    = Table{}
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)

	fmt.Printf("SELECT %s FROM t;\n", meta.Names())
}

Output:

SELECT id,name FROM t;

func (*Meta) Sub 

func (meta *Meta) Sub() (sub *Meta)

Sub return the child of Meta for building subquery.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
		t    = Table{}
		qid  = 1
	)

	meta.Bind(`id`, &t.ID)
	meta.Bind(`name`, &t.Name)
	meta.BindWhere(`id`, qid)

	var (
		metain = meta.Sub()
		qnames = []string{`hello`, `world`}
	)

	metain.BindWhere(``, qnames[0])
	metain.BindWhere(``, qnames[1])

	var q = fmt.Sprintf(`SELECT %s FROM t WHERE id=$1 OR name IN (%s);`,
		meta.Names(), metain.Holders())

	var qparams = sql.JoinValues(meta.ListWhereValue, metain.ListWhereValue)

	// db.QueryRow(q, qparams...).Scan(meta.ListValue...)

	fmt.Println(q)
	fmt.Println(`SELECT #n=`, len(meta.ListValue))
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`WHERE IN=`, metain.ListWhereValue)
	fmt.Println(`qparams=`, qparams)

}

Output:

SELECT id,name FROM t WHERE id=$1 OR name IN ($2,$3);
SELECT #n= 2
WHERE= [1]
WHERE IN= [hello world]
qparams= [1 hello world]

func (*Meta) UpdateFields 

func (meta *Meta) UpdateFields() string

UpdateFields generate string of "col1=<holder>, col2=<holder>, ..." for DML UPDATE.

It will return an empty string if kind is not UPDATE.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		t    = Table{
			ID:   2,
			Name: `world`,
		}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)
	meta.BindWhere(`id=`, qid)
	meta.BindWhere(`AND name=`, qname)

	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`, meta.UpdateFields(), meta.WhereFields())

	// db.Exec(q, meta.UpdateValues()...);

	fmt.Println(q)
	fmt.Println(`SET=`, meta.ListValue)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`Exec=`, meta.UpdateValues())

}

Output:

UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
SET= [2 world]
WHERE= [1 hello]
Exec= [2 world 1 hello]

func (*Meta) UpdateValues 

func (meta *Meta) UpdateValues() (listVal []any)

UpdateValues return the merged of ListValue and ListWhereValue for DML UPDATE.

It will return nil if kind is not DML UPDATE.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	type Table struct {
		Name string
		ID   int
	}

	var (
		meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindUpdate)
		t    = Table{
			ID:   2,
			Name: `world`,
		}
		qid   = 1
		qname = `hello`
	)

	meta.Bind(`id`, t.ID)
	meta.Bind(`name`, t.Name)
	meta.BindWhere(`id`, qid)
	meta.BindWhere(`name`, qname)

	var q = fmt.Sprintf(`UPDATE t SET id=$%d,name=$%d WHERE id=$%d AND name=$%d;`, meta.Index...)

	// db.Exec(q, meta.UpdateValues()...);

	fmt.Println(q)
	fmt.Println(`Index=`, meta.Index)
	fmt.Println(`SET=`, meta.ListValue)
	fmt.Println(`WHERE=`, meta.ListWhereValue)
	fmt.Println(`Exec=`, meta.UpdateValues())

}

Output:

UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
Index= [1 2 3 4]
SET= [2 world]
WHERE= [1 hello]
Exec= [2 world 1 hello]

func (*Meta) WhereFields 

func (meta *Meta) WhereFields() string

WhereFields merge the ListWhereCond and ListHolder.

It will return an empty string if kind is DML INSERT.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)

	meta.BindWhere(`id=`, 1000)
	meta.BindWhere(`AND name=`, `share`)

	fmt.Printf(`SELECT * FROM t WHERE %s;`, meta.WhereFields())
}

Output:

SELECT * FROM t WHERE id=$1 AND name=$2;

func (*Meta) WhereHolders 

func (meta *Meta) WhereHolders() string

WhereHolders generate string of holder, for example "$1,$2, ...", based on number of item added with Meta.BindWhere. Similar to method Holders but for where condition.

It will return an empty string if kind is DML INSERT.

Example 
package main

import (
	"fmt"

	"git.sr.ht/~shulhan/pakakeh.go/lib/sql"
)

func main() {
	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)

	meta.BindWhere(`id`, 1000)
	meta.BindWhere(`name`, `share`)

	fmt.Printf(`SELECT * FROM t WHERE id IN (%s);`, meta.WhereHolders())
}

Output:

SELECT * FROM t WHERE id IN ($1,$2);

type Session 

type Session interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
	Prepare(query string) (*sql.Stmt, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	Query(query string, args ...interface{}) (*sql.Rows, error)
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
	QueryRow(query string, args ...interface{}) *sql.Row
	QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}

Session is an interface that represent both sql.DB and sql.Tx.

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.