pg

package
v0.0.0-...-d31700d Latest Latest
Warning

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

Go to latest
Published: Mar 28, 2022 License: MIT Imports: 23 Imported by: 0

README

pg (postgresql)

If you don't like ORM, but need convenient utils to help you make sql, this library is for you.

Documentation

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func Array

func Array(data interface{}) string

Array return data in postgres array form.

Example
fmt.Println(Array([]int{1, 2, 3}))
fmt.Println(Array([]string{"a", "b", "c"}))
fmt.Println(Array([][]interface{}{
	{1, "a", true}, {2, "b", true}, {3, "c", false}, {4, "dd'ee", false},
}))
Output:

'{1,2,3}'
'{"a","b","c"}'
'{{1,"a",true},{2,"b",true},{3,"c",false},{4,"dd''ee",false}}'

func Columns2Fields

func Columns2Fields(columns []string) (result []string)
Example
var inputs = []string{"xiao_mei", "http_status", "you123", "price_p"}
fmt.Println(Columns2Fields(inputs))
Output:

[XiaoMei HttpStatus You123 PriceP]

func ColumnsComments

func ColumnsComments(table string, strct interface{}) (result string)
Example
type Test struct {
	Id          int64  `comment:"主键"`
	Name        string `comment:"名称"`
	notExported int
}

fmt.Println(ColumnsComments("tests", Test{}))
Output:

COMMENT ON COLUMN tests.id IS '主键';
COMMENT ON COLUMN tests.name IS '名称';

func ColumnsDefs

func ColumnsDefs(strct interface{}) string
Example
type Student struct {
	Id        int64
	Name      string
	FriendIds pq.Int64Array `sql:"int[]"`
	Cities    []string
	Scores    map[string]int
	Money     decimal.Decimal
	Status    **int8 `sql:"default 0"`
	CreatedAt time.Time
	UpdatedAt *time.Time
}

fmt.Println(ColumnsDefs(Student{}))
Output:

id serial8 NOT NULL PRIMARY KEY,
name text NOT NULL,
friend_ids int[] NOT NULL,
cities jsonb NOT NULL,
scores jsonb NOT NULL,
money decimal NOT NULL,
status int2 NOT NULL default 0,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL

func ColumnsFromStruct

func ColumnsFromStruct(strct interface{}, exclude []string) (result []string)

func ColumnsStrFromStruct

func ColumnsStrFromStruct(strct interface{}, exclude []string) string

func ConflictedUniqueIndex

func ConflictedUniqueIndex(err error) string

func ErrorWithPosition

func ErrorWithPosition(err error, sql string) error

func Field2Column

func Field2Column(field string) string
Example
var inputs = []string{"XiaoMei", "xiaoMei.HTTPStatus", "You123", "ILoveGolangAndJSONSoMuch"}
for i := range inputs {
	fmt.Println(Field2Column(inputs[i]))
}
Output:

xiao_mei
xiao_mei.http_status
you123
i_love_golang_and_json_so_much

func Fields2Columns

func Fields2Columns(fields []string) (result []string)
Example
var inputs = []string{"XiaoMei", "HTTPStatus", "You123",
	"PriceP", "4sPrice", "Price4s", "goodHTTP", "ILoveGolangAndJSONSoMuch",
}
fmt.Println(Fields2Columns(inputs))
Output:

[xiao_mei http_status you123 price_p 4s_price price4s good_http i_love_golang_and_json_so_much]

func Fields2ColumnsStr

func Fields2ColumnsStr(fields []string) string
Example
var inputs = []string{"XiaoMei", "HttpStatus", "You123", "PriceP"}
fmt.Println(Fields2ColumnsStr(inputs))
Output:

xiao_mei,http_status,you123,price_p

func FieldsFromStruct

func FieldsFromStruct(strct interface{}, exclude []string) (result []string)
Example
type TestT2 struct {
	T2Name string
}
type testT3 struct {
	T3Name string
}
type TestT4 int
type testT5 string

type TestT struct {
	Name        string
	notExported int
	TestT2
	*testT3
	TestT4
	testT5
}
fmt.Println(FieldsFromStruct(TestT{}, []string{"T2Name"}))
Output:

[Name T3Name TestT4]

func FieldsToColumns

func FieldsToColumns(fields []string, prefix string, exclude []string) (result []string)

func FieldsToColumnsStr

func FieldsToColumnsStr(fields []string, prefix string, exclude []string) string
Example
var inputs = []string{"XiaoMei", "HttpStatus", "You123", "PriceP"}
fmt.Println(FieldsToColumnsStr(inputs, "t.", []string{"PriceP"}))
Output:

t.xiao_mei,t.http_status,t.you123

func GetPosition

func GetPosition(err error, sql string) string

func IsNil

func IsNil(dbOrTx DbOrTx) bool

func Json

func Json(data interface{}) string
Example
fmt.Println(Json([]int{1, 2, 3}))
fmt.Println(Json([]string{"a", "b", "c"}))
fmt.Println(Json([][]interface{}{
	{1, "a", true}, {2, "b", true}, {3, "c", false}, {4, "dd'ee", false},
}))
Output:

'[1,2,3]'
'["a","b","c"]'
'[[1,"a",true],[2,"b",true],[3,"c",false],[4,"dd''ee",false]]'

func PatternEscape

func PatternEscape(s string) string

PatternEscape escape a string, replace '%' and '\'.

func PrettyPrint

func PrettyPrint(v interface{}) string
Example
var v = struct {
	A int
	B string
}{A: 1, B: "line1\nline2"}

fmt.Print(PrettyPrint(v))
Output:

{
  A: 1
  B: line1
line2
}

func Q

func Q(s string) string

Q quote a string, removing all zero byte('\000') in it.

Example
var data = []string{"bsql", "xi'ao'mei", "love'go", "a\000\000b\000c"}
for i := range data {
	fmt.Println(Q(data[i]))
}
Output:

'bsql'
'xi''ao''mei'
'love''go'
'abc'

func SingleColumnValues

func SingleColumnValues(data interface{}) string
Example
fmt.Println(SingleColumnValues(3))
fmt.Println(SingleColumnValues("a'bc"))
fmt.Println(SingleColumnValues([]int{1, 2, 3}))
fmt.Println(SingleColumnValues([]string{"a", "b", "c"}))
Output:

(3)
('a''bc')
(1),(2),(3)
('a'),('b'),('c')

func SliceContents

func SliceContents(value reflect.Value) string
Example
data1 := []interface{}{"jack", "rose", 1}
fmt.Println(SliceContents(reflect.ValueOf(data1)))

type people struct {
	Name string `json:"name"`
	Age  int    `json:"age"`
}
data2 := []people{
	{Name: "李雷", Age: 20},
	{Name: "韩梅梅", Age: 19},
}
fmt.Println(SliceContents(reflect.ValueOf(data2)))
data3 := []*people{
	{Name: "李雷", Age: 20},
	{Name: "韩梅梅", Age: 19},
}
fmt.Println(SliceContents(reflect.ValueOf(data3)))
Output:

'jack','rose',1
'{"name":"李雷","age":20}','{"name":"韩梅梅","age":19}'
'{"name":"李雷","age":20}','{"name":"韩梅梅","age":19}'

func StructFieldValues

func StructFieldValues(data interface{}, field string) string

func StructFields

func StructFields(value interface{}, fields []string) string
Example
type student struct {
	Id        int
	Name, Sex string
}
data1 := student{1, "李雷", "男"}
fmt.Println(StructFields(data1, []string{"Id", "Name"}))

data2 := &student{1, "李雷", "男"}
fmt.Println(StructFields(data2, []string{"Id", "Name"}))

var data3 interface{} = student{1, "李雷", "男"}
fmt.Println(StructFields(data3, []string{"Id", "Name"}))
Output:

1,'李雷'
1,'李雷'
1,'李雷'

func StructFieldsReflect

func StructFieldsReflect(value reflect.Value, fields []string) string

func StructFieldsWithType

func StructFieldsWithType(value reflect.Value, typ reflect.Type, fields []string) string

func StructValues

func StructValues(data interface{}, fields []string) string
Example
type student struct {
	Id        int
	Name, Sex string
}
data1 := []student{
	{1, "李雷", "男"}, {2, "韩梅梅", "女"},
	{3, "Lili", "女"}, {4, "Lucy", "女"},
}
fmt.Println(StructValues(data1, []string{"Id", "Name"}))

data2 := []*student{
	{1, "李雷", "男"}, {2, "韩梅梅", "女"},
	{3, "Lili", "女"}, {4, "Lucy", "女"},
}
fmt.Println(StructValues(data2, []string{"Id", "Name"}))

data3 := []interface{}{
	student{1, "李雷", "男"}, student{2, "韩梅梅", "女"},
	student{3, "Lili", "女"}, student{4, "Lucy", "女"},
}
fmt.Println(StructValues(data3, []string{"Id", "Name"}))
Output:

(1,'李雷'),(2,'韩梅梅'),(3,'Lili'),(4,'Lucy')
(1,'李雷'),(2,'韩梅梅'),(3,'Lili'),(4,'Lucy')
(1,'李雷'),(2,'韩梅梅'),(3,'Lili'),(4,'Lucy')
Example (Map)
type student struct {
	Id        int
	Name, Sex string
}
m := map[string]bool{"(1,'李雷'),(2,'韩梅梅')": true, "(2,'韩梅梅'),(1,'李雷')": true}
result := StructValues(map[student]int{
	student{1, "李雷", "男"}:  1,
	student{2, "韩梅梅", "女"}: 2,
}, []string{"Id", "Name"})

fmt.Println(m[result])
Output:

true

func StructValuesWithType

func StructValuesWithType(data interface{}, fields []string) string

func UpsertSql

func UpsertSql(table string, toInsert, conflictKeys, notToUpdate []string) string

func V

func V(i interface{}) string
Example (Bool)
var t, f = true, false
fmt.Println(V(t), V(f), V(&t), V(&f))
Output:

true false true false
Example (Bytes)
var b = []byte("abc")
fmt.Println(V(b), V(&b))
Output:

abc abc
Example (DriverValuer_date)
var d = time2.Date{}
fmt.Println(V(d), V(&d))

var d2 = time2.NewDate(2019, 1, 1)
fmt.Println(V(d2), V(&d2))
Output:

NULL NULL
'2019-01-01' '2019-01-01'
Example (DriverValuer_decimal)
var d = decimal.New(1234, -2)
fmt.Println(V(d), V(&d))
var p *decimal.Decimal
fmt.Println(V(p))
Output:

12.34 12.34
NULL
Example (Float32)
var f = 1.234
fmt.Println(V(f), V(&f))
Output:

1.234 1.234
Example (Float64)
var f float64 = 1.234567
fmt.Println(V(f), V(&f))
Output:

1.234567 1.234567
Example (Int)
var i = -1234567890
var p = &i
fmt.Println(V(i), V(p), V(&p))
Output:

-1234567890 -1234567890 -1234567890
Example (Json)
var j = map[int]bool{2: true, 3: false}
fmt.Println(V(j), V(&j))
Output:

'{"2":true,"3":false}' '{"2":true,"3":false}'
Example (Nil)

special types

var p *int
var m map[string]int
fmt.Println(V(nil), V(p), V(m))
Output:

NULL NULL 'null'
Example (String)

basic types

var s = "string"
fmt.Println(V(s), V(&s))
Output:

'string' 'string'
Example (Time)
var t time.Time
fmt.Println(V(t), V(&t))
t, err := time.Parse(time.RFC3339Nano, "2019-06-19T13:52:08.123456789+08:00")
fmt.Println(V(t), V(&t), err)
Output:

'0001-01-01T00:00:00Z' '0001-01-01T00:00:00Z'
'2019-06-19T13:52:08.123456+08:00' '2019-06-19T13:52:08.123456+08:00' <nil>
Example (Uint)
var i uint = 1234567890
fmt.Println(V(i), V(&i))
Output:

1234567890 1234567890

func Values

func Values(data interface{}) string

Values return the contents following the sql keyword "VALUES"

Example
fmt.Println(Values(3))
fmt.Println(Values("a'bc"))
fmt.Println(Values([]int{1, 2, 3}))
fmt.Println(Values([]string{"a", "b", "c"}))
fmt.Println(Values([][]interface{}{
	{1, "a", true}, {2, "b", true}, {3, "c", false},
}))
Output:

(3)
('a''bc')
(1,2,3)
('a','b','c')
(1,'a',true),(2,'b',true),(3,'c',false)
Example (Map)
m := map[string]bool{"('1','2')": true, "('2','1')": true}
result := Values(map[string]interface{}{"1": nil, "2": nil})
fmt.Println(m[result])

m = map[string]bool{"(1,2)": true, "(2,1)": true}
result = Values(map[int]interface{}{1: nil, 2: nil})
fmt.Println(m[result])

m = map[string]bool{"(1,2),(3,4)": true, "(3,4),(1,2)": true}
result = Values(map[[2]int]interface{}{[2]int{1, 2}: nil, [2]int{3, 4}: nil})
fmt.Println(m[result])
Output:

true
true
true

func WrapError

func WrapError(err error, sql string, fullSql bool) error

Types

type DB

type DB struct {
	DB            *sql.DB
	Context       context.Context
	Timeout       time.Duration // default timeout for Query, Exec and transactions.
	Debug         bool
	DebugOutput   io.Writer
	PutSqlInError bool // put sql into returned error if error happend .
}

func New

func New(db *sql.DB, timeout time.Duration) *DB
Example
var rawDB *sql.DB
db := New(rawDB, time.Second)
fmt.Println(db.Timeout)
Output:

1s

func (*DB) Exec

func (db *DB) Exec(sql string, args ...interface{}) (sql.Result, error)
Example
db := New(rawDB, time.Second)
result, err := db.Exec(`
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		);
	`)
if err != nil {
	log.Panic(err)
}
affectedRows, err := result.RowsAffected()
if err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

1

func (*DB) ExecCtx

func (db *DB) ExecCtx(
	ctx context.Context, opName string, sql string, args ...interface{},
) (sql.Result, error)
Example
db := New(rawDB, time.Second)
result, err := db.ExecCtx(
	context.Background(), `delete people`, `
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		),(
			'rose', array[99,88], '{"数学":90, "语文":100}', 0, now()
		);
	`)
if err != nil {
	log.Panic(err)
}
affectedRows, err := result.RowsAffected()
if err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

2

func (*DB) ExecT

func (db *DB) ExecT(duration time.Duration, sql string, args ...interface{}) (sql.Result, error)
Example
db := New(rawDB, time.Second)
result, err := db.ExecT(time.Second, `
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		),(
			'rose', array[99,88], '{"数学":90, "语文":100}', 0, now()
		);
	`)
if err != nil {
	log.Panic(err)
}
affectedRows, err := result.RowsAffected()
if err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

2

func (*DB) GetDB

func (db *DB) GetDB() *sql.DB

func (*DB) Query

func (db *DB) Query(data interface{}, sql string, args ...interface{}) error
Example
var people struct {
	Name string
	Age  int
}
db := New(rawDB, time.Second)
if err := db.Query(&people, `select 'jack' as name, 24 as age`); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

func (*DB) QueryCtx

func (db *DB) QueryCtx(ctx context.Context, opName string,
	data interface{}, sql string, args ...interface{},
) error
Example
var people struct {
	Name string
	Age  int
}
db := New(rawDB, time.Second)
if err := db.QueryCtx(
	context.Background(), `query people`, &people, `select 'jack' as name, 24 as age`,
); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

func (*DB) QueryT

func (db *DB) QueryT(duration time.Duration, data interface{}, sql string, args ...interface{}) error
Example
var people struct {
	Name string
	Age  int
}
db := New(rawDB, time.Second)
if err := db.QueryT(2*time.Second, &people, `select 'jack' as name, 24 as age`); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

func (*DB) RunInTransaction

func (db *DB) RunInTransaction(fn func(*Tx) error) error
Example
db := New(rawDB, time.Second)
var id int
if err := db.RunInTransaction(func(tx *Tx) error {
	if err := tx.Query(&id, `select 10 as id`); err != nil {
		return err
	}
	return nil
	return nil
}); err != nil {
	log.Panic(err)
}
fmt.Println(id)
Output:

10

func (*DB) RunInTransactionCtx

func (db *DB) RunInTransactionCtx(
	ctx context.Context, opName string, fn func(*Tx, context.Context) error,
) error
Example
db := New(rawDB, time.Second)
var id int
if err := db.RunInTransactionCtx(
	context.Background(), "test RunInTransactionCtx", func(tx *Tx, ctx context.Context) error {
		if err := tx.Query(&id, `select 10 as id`); err != nil {
			return err
		}
		return nil
	},
); err != nil {
	log.Panic(err)
}
fmt.Println(id)
Output:

10

func (*DB) RunInTransactionT

func (db *DB) RunInTransactionT(duration time.Duration, fn func(*Tx) error) error
Example
db := New(rawDB, time.Second)
var id int
if err := db.RunInTransactionT(5*time.Second, func(tx *Tx) error {
	if err := tx.Query(&id, `select 10 as id`); err != nil {
		return err
	}
	return nil
}); err != nil {
	log.Panic(err)
}
fmt.Println(id)
Output:

10

type DbOrTx

type DbOrTx interface {
	Query(data interface{}, sql string, args ...interface{}) error
	QueryCtx(ctx context.Context, opName string, data interface{}, sql string, args ...interface{}) error
	QueryT(duration time.Duration, data interface{}, sql string, args ...interface{}) error
	Exec(sql string, args ...interface{}) (sql.Result, error)
	ExecT(duration time.Duration, sql string, args ...interface{}) (sql.Result, error)
}

type Table

type Table struct {
	Name        string
	Desc        string
	Struct      interface{}
	Constraints []string
	Options     []string
	ExtraSqls   []string
}

func (Table) Sql

func (t Table) Sql() string

Sql add create table sql Using Name as table name, Desc as table description, Struct struct as table columns and comments.

type Tx

type Tx struct {
	Tx            *sql.Tx
	Context       context.Context
	Timeout       time.Duration // default timeout for Query or Exec.
	Debug         bool
	DebugOutput   io.Writer
	PutSqlInError bool // put sql into returned error if error happend .
}

func NewTx

func NewTx(tx *sql.Tx, timeout time.Duration) *Tx

func (*Tx) Exec

func (tx *Tx) Exec(sql string, args ...interface{}) (sql.Result, error)
Example
db := New(rawDB, time.Second)
var affectedRows int64
if err := db.RunInTransaction(func(tx *Tx) error {
	result, err := tx.Exec(`
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		);
	`)
	if err != nil {
		return err
	}
	affectedRows, err = result.RowsAffected()
	return err
}); err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

1

func (*Tx) ExecCtx

func (tx *Tx) ExecCtx(ctx context.Context, opName string, sql string, args ...interface{}) (sql.Result, error)
Example
db := New(rawDB, time.Second)
var affectedRows int64
if err := db.RunInTransaction(func(tx *Tx) error {
	result, err := tx.ExecCtx(
		context.Background(), `delete people`, `
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		),(
			'rose', array[99,88], '{"数学":90, "语文":100}', 0, now()
		);
	`)
	if err != nil {
		return err
	}
	affectedRows, err = result.RowsAffected()
	return err
}); err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

2

func (*Tx) ExecT

func (tx *Tx) ExecT(duration time.Duration, sql string, args ...interface{}) (sql.Result, error)
Example
db := New(rawDB, time.Second)
var affectedRows int64
if err := db.RunInTransaction(func(tx *Tx) error {
	result, err := tx.ExecT(time.Second, `
		drop table if exists students;
		create table if not exists students (
			id         bigserial,
			name       varchar(50),
			friend_ids bigint[],
			scores     json,
			status     smallint,
			created_at timestamptz,
			updated_at timestamptz default '0001-01-01Z'
		);
		insert into students (
			name, friend_ids, scores, status, created_at
		) values (
			'jack', array[55,66], '{"数学":100, "语文":90}', 0, now()
		),(
			'rose', array[99,88], '{"数学":90, "语文":100}', 0, now()
		);
	`)
	if err != nil {
		return err
	}
	affectedRows, err = result.RowsAffected()
	return err
}); err != nil {
	log.Panic(err)
}
fmt.Println(affectedRows)
Output:

2

func (*Tx) Query

func (tx *Tx) Query(data interface{}, sql string, args ...interface{}) error
Example
var people struct {
	Name string
	Age  int
}
rawTx, err := rawDB.Begin()
if err != nil {
	log.Panic(err)
}
var tx = &Tx{
	Tx:      rawTx,
	Timeout: time.Second,
}
if err := tx.Query(&people, `select 'jack' as name, 24 as age`); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

func (*Tx) QueryCtx

func (tx *Tx) QueryCtx(ctx context.Context, opName string,
	data interface{}, sql string, args ...interface{},
) error
Example
var people struct {
	Name string
	Age  int
}
rawTx, err := rawDB.Begin()
if err != nil {
	log.Panic(err)
}
var tx = &Tx{
	Tx:      rawTx,
	Timeout: time.Second,
}
if err := tx.QueryCtx(
	context.Background(), `query people`, &people, `select 'jack' as name, 24 as age`,
); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

func (*Tx) QueryT

func (tx *Tx) QueryT(duration time.Duration, data interface{}, sql string, args ...interface{}) error
Example
var people struct {
	Name string
	Age  int
}
rawTx, err := rawDB.Begin()
if err != nil {
	log.Panic(err)
}
var tx = &Tx{
	Tx:      rawTx,
	Timeout: time.Second,
}
if err := tx.QueryT(2*time.Second, &people, `select 'jack' as name, 24 as age`); err != nil {
	log.Panic(err)
}
fmt.Printf("%+v", people)
Output:

{Name:jack Age:24}

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL