builder

package
v1.9.0 Latest Latest
Warning

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

 Go to latest Published: Jul 9, 2024 License: Apache-2.0 Imports: 9 Imported by: 41

README

Builder

It's only a tool helping you build your queries.You should also use the database/sql to operate database

complex sql always need special optimization,which is hard to do it here.So, for very comlex sql, I suggest you write it manually, Exported WhereIn Helper will be added soon

QuickStart

example_1

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "github.com/didi/gendry/builder"
)

func main() {
    db,err := sql.Open("mysql", "xxxxxxxxxxx")
    if nil != err {
        panic(err)
    }
    where := map[string]interface{}{
        "_custom_0": builder.Custom("name=?", "name0"),
        "_custom_1": builder.JsonContains("my_json->'$.list'", 1),
        "country": "China",
        "role": "driver",
        "age >": 45,
        "gmt_create <": builder.Raw("gmt_modified"),
        "_or": []map[string]interface{}{
            {
                "x1":    11,
                "x2 >=": 45,
            },
            {
                "x3":    "234",
                "x4 <>": "tx2",
            },
        },
        "_groupby": "name",
        "_having": map[string]interface{}{
            "total >": 1000,
            "total <=": 50000,
        },
    	"_orderby": "age desc",
    }
    cond,vals,err := builder.BuildSelect("tableName", where, []string{"name", "count(price) as total", "age"})
    
    //cond: SELECT name,count(price) as total,age FROM tableName WHERE (name=? AND (? MEMBER OF(my_json->'$.list')) AND ((x1=? AND x2>=?) OR (x3=? AND x4!=?)) AND country=? AND role=? AND age>? AND gmt_create<gmt_modified) GROUP BY name HAVING (total>? AND total<=?) ORDER BY age desc
    //vals: []interface{}{"name0", 1, 11, 45, "234", "tx2", "China", "driver", 45, 1000, 50000}

	if nil != err {
		panic(err)
	}	

    rows,err := db.Query(cond, vals...)
    if nil != err {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name,phone string
        rows.Scan(&id, &name, &phone)
        fmt.Println(id, name, phone)
    }

    //have fun !!
}

API

BuildSelect

sign: BuildSelect(table string, where map[string]interface{}, field []string) (string,[]interface{},error)

operators supported(case-insensitive):

  • =
  • >
  • <
  • =
  • <=
  • >=
  • !=
  • <>
  • in
  • not in
  • like
  • not like
  • between
  • not between
where := map[string]interface{}{
    "foo <>": "aha",
    "bar <=": 45,
    "sex in": []interface{}{"girl", "boy"},
    "name like": "%James",
}

others supported:

  • _or
  • _orderby
  • _groupby
  • _having
  • _limit
  • _lockMode
  • _custom_xxx
where := map[string]interface{}{
    "age >": 100,
    "_custom_1":    builder.JsonContains("my_json->'$.list'", 1),
    "_or": []map[string]interface{}{
        {
            "x1":    11,
            "x2 >=": 45,
        },
        {
            "x3":    "234",
            "x4 <>": "tx2",
        },
    },
    "_orderby": "fieldName asc",
    "_groupby": "fieldName",
    "_having": map[string]interface{}{"foo":"bar",},
    "_limit": []uint{offset, row_count},
    "_lockMode": "share",
}

Note:

  • _having will be ignored if _groupby isn't setted
  • value of _limit could be:
    • "_limit": []uint{a,b} => LIMIT a,b
    • "_limit": []uint{a} => LIMIT 0,a
  • value of _lockMode only supports share and exclusive temporarily:
    • share representative SELECT ... LOCK IN SHARE MODE. Unfortunately, the current version does not support SELECT ... FOR SHARE, It'll be supported in the future.
    • exclusive representative SELECT ... FOR UPDATE
  • if key starts with _custom_, the corresponding value must be a builder.Comparable. We provide builtin type such as Custom and JsonContains. You can also provide your own implementation if you want
  • JsonSet,JsonArrayAppend,JsonArrayInsert,JsonRemove should be used in update map rather than where map

Aggregate

sign: AggregateQuery(ctx context.Context, db *sql.DB, table string, where map[string]interface{}, aggregate AggregateSymbleBuilder) (ResultResolver, error)

Aggregate is a helper function to help executing some aggregate queries such as:

  • sum
  • avg
  • max
  • min
  • count

example:

where := map[string]interface{}{
    "score > ": 100,
    "city in": []interface{}{"Beijing", "Shijiazhuang",}
}
// supported: AggregateSum,AggregateMax,AggregateMin,AggregateCount,AggregateAvg
result, err := AggregateQuery(ctx, db, "tableName", where, AggregateSum("age"))
sumAge := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateCount("*")) 
numberOfRecords := result.Int64()

result,err = AggregateQuery(ctx, db, "tableName", where, AggregateAvg("score"))
averageScore := result.Float64()

BuildUpdate

sign: BuildUpdate(table string, where map[string]interface{}, update map[string]interface{}) (string, []interface{}, error)

BuildUpdate is very likely to BuildSelect but it doesn't support:

  • _orderby
  • _groupby
  • _having
where := map[string]interface{}{
    "foo <>": "aha",
    "bar <=": 45,
    "sex in": []interface{}{"girl", "boy"},
    "_or": []map[string]interface{}{
        {
            "x1":    11,
            "x2 >=": 45,
        },
        {
            "x3":    "234",
            "x4 <>": "tx2",
        },
    },
    "_limit": uint(10),
}
update := map[string]interface{}{
	"role": "primaryschoolstudent",
	"rank": 5,
	"_custom_0": qb.JsonArrayAppend("my_json", "$", 0, "$", 1),
}
cond,vals,err := qb.BuildUpdate("table_name", where, update)

db.Exec(cond, vals...)

BuildInsert

sign: BuildInsert(table string, data []map[string]interface{}) (string, []interface{}, error)

data is a slice and every element(map) in it must have the same keys:

var data []map[string]interface{}
data = append(data, map[string]interface{}{
    "name": "deen",
    "age":  23,
})
data = append(data, map[string]interface{}{
    "name": "Tony",
    "age":  30,
})
cond, vals, err := qb.BuildInsert(table, data)
db.Exec(cond, vals...)

BuildInsertIgnore

sign: BuildInsertIgnore(table string, data []map[string]interface{}) (string, []interface{}, error)

data is a slice and every element(map) in it must have the same keys:

var data []map[string]interface{}
data = append(data, map[string]interface{}{
    "name": "deen",
    "age":  23,
})
data = append(data, map[string]interface{}{
    "name": "Tony",
    "age":  30,
})
cond, vals, err := qb.BuildInsertIgnore(table, data)
db.Exec(cond, vals...)

BuildReplaceInsert

sign: BuildReplaceInsert(table string, data []map[string]interface{}) (string, []interface{}, error)

data is a slice and every element(map) in it must have the same keys:

var data []map[string]interface{}
data = append(data, map[string]interface{}{
    "name": "deen",
    "age":  23,
})
data = append(data, map[string]interface{}{
    "name": "Tony",
    "age":  30,
})
cond, vals, err := qb.BuildReplaceInsert(table, data)
db.Exec(cond, vals...)

BuildInsertOnDuplicate

sign: BuildInsertOnDuplicate(table string, data []map[string]interface{}, update map[string]interface{}) (string, []interface{}, error)

data is a slice and every element(map) in it must have the same keys:

data := []map[string]interface{}{
    {
        "name": "deen",
        "age":  23,
    },
    {
        "name": "Tony",
        "age":  30,
    },
}
update := map[string]interface{}{
    "role": "primaryschoolstudent",
    "rank": 5,
}
cond, vals, err := qb.BuildInsertOnDuplicate(table, data, update)
db.Exec(cond, vals...)


// update support builder.Raw to update when duplicate with value in insert data
update = map[string]interface{}{
    "code": builder.Raw("VALUES(code)"), // mysql 8.x  builder.Raw("new.code")
    "name": builder.Raw("VALUES(name)"), // mysql 8.x  builder.Raw("new.name")
}
cond, values, err := builder.BuildInsertOnDuplicate(table, data, update)
// INSERT INTO country (id, code, name) VALUES (?,?,?),(?,?,?),(?,?,?) 
// ON DUPLICATE KEY UPDATE code=VALUES(code),name=VALUES(name)

NamedQuery

sign: func NamedQuery(sql string, data map[string]interface{}) (string, []interface{}, error)

For very complex query, this might be helpful. And for critical system, this is recommended.

cond, vals, err := builder.NamedQuery("select * from tb where name={{name}} and id in (select uid from anothertable where score in {{m_score}})", map[string]interface{}{
	"name": "caibirdme",
	"m_score": []float64{3.0, 5.8, 7.9},
})

assert.Equal("select * from tb where name=? and id in (select uid from anothertable where score in (?,?,?))", cond)
assert.Equal([]interface{}{"caibirdme", 3.0, 5.8, 7.9}, vals)

BuildDelete

sign: BuildDelete(table string, where map[string]interface{}) (string, []interface{}, error)

Safety

If you use Prepare && stmt.SomeMethods then You have no need to worry about the safety. Prepare is a safety mechanism backed by mysql, it makes sql injection out of work.

So builder doesn't escape the string values it received -- it's unnecessary

If you call db.Query(cond, vals...) directly, and you don't set interpolateParams which is one of the driver's variables to true, the driver actually will still prepare a stmt.So it's safe.

Remember:

  • don't assemble raw sql yourself,use builder instead.
  • don't set interpolateParams to true(default false) if you're not aware of the consequence.

Obey instructions above there's no safety issues for most cases.

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (

	// ErrUnsupportedOperator reports there's unsupported operators in where-condition
	ErrUnsupportedOperator = errors.New("[builder] unsupported operator")
)
View Source 
var IsZeroType = reflect.TypeOf((*IsZeroer)(nil)).Elem()

Functions

func BuildDelete 

func BuildDelete(table string, where map[string]interface{}) (string, []interface{}, error)

BuildDelete work as its name says

func BuildInsert 

func BuildInsert(table string, data []map[string]interface{}) (string, []interface{}, error)

BuildInsert work as its name says

func BuildInsertIgnore added in v1.1.0 

func BuildInsertIgnore(table string, data []map[string]interface{}) (string, []interface{}, error)

BuildInsertIgnore work as its name says

func BuildInsertOnDuplicate added in v1.5.0 

func BuildInsertOnDuplicate(table string, data []map[string]interface{}, update map[string]interface{}) (string, []interface{}, error)

BuildInsertOnDuplicateKey builds an INSERT ... ON DUPLICATE KEY UPDATE clause.

func BuildReplaceInsert added in v1.1.0 

func BuildReplaceInsert(table string, data []map[string]interface{}) (string, []interface{}, error)

BuildReplaceInsert work as its name says

func BuildSelect 

func BuildSelect(table string, where map[string]interface{}, selectField []string) (cond string, vals []interface{}, err error)

BuildSelect work as its name says. supported operators including: =,in,>,>=,<,<=,<>,!=. key without operator will be regarded as =. special key begin with _: _orderby,_groupby,_limit,_having. the value of _limit must be a slice whose type should be []uint and must contain two uints(ie: []uint{0, 100}). the value of _having must be a map just like where but only support =,in,>,>=,<,<=,<>,!= for more examples,see README.md or open a issue.

func BuildUpdate 

func BuildUpdate(table string, where map[string]interface{}, update map[string]interface{}) (string, []interface{}, error)

BuildUpdate work as its name says

func NamedQuery 

func NamedQuery(sql string, data map[string]interface{}) (string, []interface{}, error)

NamedQuery is used for expressing complex query

func OmitEmpty 

func OmitEmpty(where map[string]interface{}, omitKey []string) map[string]interface{}

OmitEmpty is a helper function to clear where map zero value

Types

type AggregateSymbleBuilder 

type AggregateSymbleBuilder interface {
	Symble() string
}

AggregateSymbleBuilder need to be implemented so that executor can get what should be put into `select Symble() from xxx where yyy`

func AggregateAvg 

func AggregateAvg(col string) AggregateSymbleBuilder

AggregateAvg avg(col)

func AggregateCount 

func AggregateCount(col string) AggregateSymbleBuilder

AggregateCount count(col)

func AggregateMax 

func AggregateMax(col string) AggregateSymbleBuilder

AggregateMax max(col)

func AggregateMin 

func AggregateMin(col string) AggregateSymbleBuilder

AggregateMin min(col)

func AggregateSum 

func AggregateSum(col string) AggregateSymbleBuilder

AggregateSum sum(col)

type Between added in v1.2.0 

type Between map[string][]interface{}

func (Between) Build added in v1.2.0 

func (bt Between) Build() ([]string, []interface{})

type Comparable 

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

Comparable requires type implements the Build method

func Custom added in v1.9.0 

func Custom(query string, args ...interface{}) Comparable

func JsonArrayAppend added in v1.9.0 

func JsonArrayAppend(field string, pathAndValuePair ...interface{}) Comparable

JsonArrayAppend gen JsonObj and call MySQL JSON_ARRAY_APPEND function; usage update := map[string]interface{}{"_custom_xxx": builder.JsonArrayAppend(field, "$", 1, "$[last]", []string{"2","3"}}

func JsonArrayInsert added in v1.9.0 

func JsonArrayInsert(field string, pathAndValuePair ...interface{}) Comparable

JsonArrayInsert gen JsonObj and call MySQL JSON_ARRAY_INSERT function; insert at index usage update := map[string]interface{}{"_custom_xxx": builder.JsonArrayInsert(field, "$[0]", 1, "$[0]", []string{"2","3"}}

func JsonContains added in v1.9.0 

func JsonContains(fullJsonPath string, jsonLike interface{}) Comparable

JsonContains aim to check target json contains all items in given obj;if check certain value just use direct where := map[string]interface{}{"your_json_field.'$.path_to_key' =": val}

notice: fullJsonPath should hard code, never from user input; jsonLike only support json element like array,map,string,number etc., struct input will result panic!!!

usage where := map[string]interface{}{"_custom_xxx": builder.JsonContains("my_json->'$.my_data.list'", 7)}

usage where := map[string]interface{}{"_custom_xxx": builder.JsonContains("my_json->'$'", []int{1,2})}

usage where := map[string]interface{}{"_custom_xxx": builder.JsonContains("my_json->'$.user_info'", map[string]any{"name": "", "age": 18})}

func JsonRemove added in v1.9.0 

func JsonRemove(field string, path ...string) Comparable

JsonRemove call MySQL JSON_REMOVE function; remove element from Array or Map path removed in order, prev remove affect the later operation, maybe the array shrink

remove last array element; update := map[string]interface{}{"_custom_xxx":builder.JsonRemove(field,'$.list[last]')} remove element; update := map[string]interface{}{"_custom_xxx":builder.JsonRemove(field,'$.key0')}

func JsonSet added in v1.9.0 

func JsonSet(field string, pathAndValuePair ...interface{}) Comparable

JsonSet aim to simply set/update json field operation;

notice: jsonPath should hard code, never from user input;

usage update := map[string]interface{}{"_custom_xxx": builder.JsonSet(field, "$.code", 1, "$.user_info", map[string]any{"name": "", "age": 18})}

type Eq 

type Eq map[string]interface{}

Eq means equal(=)

func (Eq) Build 

func (e Eq) Build() ([]string, []interface{})

Build implements the Comparable interface

type Gt 

type Gt map[string]interface{}

Gt means greater than(>)

func (Gt) Build 

func (g Gt) Build() ([]string, []interface{})

Build implements the Comparable interface

type Gte 

type Gte map[string]interface{}

Gte means greater than or equal(>=)

func (Gte) Build 

func (g Gte) Build() ([]string, []interface{})

Build implements the Comparable interface

type In 

type In map[string][]interface{}

In means in

func (In) Build 

func (i In) Build() ([]string, []interface{})

Build implements the Comparable interface

type IsZeroer added in v1.8.0 

type IsZeroer interface {
	IsZero() bool
}

type Like 

type Like map[string]interface{}

Like means like

func (Like) Build 

func (l Like) Build() ([]string, []interface{})

Build implements the Comparable interface

type Lt 

type Lt map[string]interface{}

Lt means less than(<)

func (Lt) Build 

func (l Lt) Build() ([]string, []interface{})

Build implements the Comparable interface

type Lte 

type Lte map[string]interface{}

Lte means less than or equal(<=)

func (Lte) Build 

func (l Lte) Build() ([]string, []interface{})

Build implements the Comparable interface

type Ne 

type Ne map[string]interface{}

Ne means Not Equal(!=)

func (Ne) Build 

func (n Ne) Build() ([]string, []interface{})

Build implements the Comparable interface

type NestWhere added in v1.4.0 

type NestWhere []Comparable

func (NestWhere) Build added in v1.4.0 

func (nw NestWhere) Build() ([]string, []interface{})

type NotBetween added in v1.2.0 

type NotBetween map[string][]interface{}

func (NotBetween) Build added in v1.2.0 

func (nbt NotBetween) Build() ([]string, []interface{})

type NotIn 

type NotIn map[string][]interface{}

NotIn means not in

func (NotIn) Build 

func (i NotIn) Build() ([]string, []interface{})

Build implements the Comparable interface

type NotLike added in v1.2.1 

type NotLike map[string]interface{}

func (NotLike) Build added in v1.2.1 

func (l NotLike) Build() ([]string, []interface{})

Build implements the Comparable interface

type NullType 

type NullType byte

NullType is the NULL type in mysql 

const (

	// IsNull the same as `is null`
	IsNull NullType
	// IsNotNull the same as `is not null`
	IsNotNull
)

func (NullType) String 

func (nt NullType) String() string

type OrWhere added in v1.4.0 

type OrWhere []Comparable

func (OrWhere) Build added in v1.4.0 

func (ow OrWhere) Build() ([]string, []interface{})

type Raw added in v1.8.2 

type Raw string

type ResultResolver 

type ResultResolver interface {
	Int64() int64
	Float64() float64
}

ResultResolver is a helper for retrieving data caller should know the type and call the responding method

func AggregateQuery 

func AggregateQuery(ctx context.Context, db *sql.DB, table string, where map[string]interface{}, aggregate AggregateSymbleBuilder) (ResultResolver, error)

AggregateQuery is a helper function to execute the aggregate query and return the result

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.