deathkel / sqlbuilder

builder.go

package sqlbuilder

import (
    "reflect"
    "strconv"
)

type Builder struct {

exported type Builder should have comment or be unexported

    //The query value bindings
    bindings

    //The Columns that should be return
    columns []string

    //The table which the query is targeting
    table string

    //Update columns
    update []interface{}

    //Insert columns
    insert []string

    //Indicates is delete sql.
    delete bool

    //Indicates if the query return district results.
    district bool

    //The where constraints for the query.
    wheres []*where

    //The groupings for the query.
    groups []string

    //The havings constraints for the query.
    havings []*where

    //The table joins for the query.
    joins []*join

    //The orderings for the query.
    orders []*order

    //The maximum number of records to return.
    limit string

    //The number of records to skip.
    offset string
}

type bindings struct {
    selected []string
    insert   []string
    update   []string
    from     []string
    join     []string
    where    []string
    having   []string
    order    []string
}

//Expression for raw sql
type Expression struct {
    Value string
}

type where struct {
    column   string
    operator string
    value    interface{}
    boolean  string
}

type join struct {
    table     string
    condition string
    joinType  string // inner left right
}

type order struct {
    column    string
    direction string
}

//All of the available clause operators.
var operators = []string{"=", "<", ">", "<=", ">=", "<>", "!=", "<=>", "like", "like binary", "not like", "ilike",
    "&", "|", "^", "<<", ">>", "rlike", "regexp", "not regexp", "~", "~*", "!~", "!~*", "similar to", "not similar to",
    "not ilike", "~~*", "!~~*", "in", "not in",
}

//Is val in array
func in_array(val interface{}, array interface{}) (exists bool, index int) {

don't use underscores in Go names; func in_array should be inArray

    exists = false
    index = -1

    switch reflect.TypeOf(array).Kind() {
    case reflect.Slice:
        s := reflect.ValueOf(array)

        for i := 0; i < s.Len(); i++ {
            if reflect.DeepEqual(val, s.Index(i).Interface()) == true {
                index = i
                exists = true
                return
            }
        }
    }

    return
}

func (b *Builder) invalidOperator(operator string) bool {
    exists, _ := in_array(operator, operators)
    return exists
}

func (b *Builder) Select(columns []string) *Builder {

exported method Builder.Select should have comment or be unexported

    b.columns = columns
    return b
}

func (b *Builder) From(table string) *Builder {

exported method Builder.From should have comment or be unexported

    b.table = table
    return b
}

/*

comment on exported method Builder.Where should be of the form "Where ..."

Where("column", "1")
Where("column", "=","1")
Where("column", "in", []string{"1","2","3"})
Where("column", "=", "1", "or")
Where(map[string]string{column1:"1", column2: "2"})
*/
func (b *Builder) Where(column interface{}, args ...interface{}) *Builder {
    switch column.(type) {
    case string:
        operator := "="
        value := ""
        booll := "and"
        lenArgs := len(args)
        if lenArgs == 1 {
            value = args[0].(string)
        } else if lenArgs >= 2 {
            operator = args[0].(string)

            if lenArgs >= 3 {
                booll = args[2].(string)
            }

            switch args[1].(type) {
            case string:
                value = args[1].(string)

            case []string:
                //支持 where in 操作
                if !b.invalidOperator(operator) {
                    operator = "="
                }
                condition := &where{column.(string), operator, args[1], booll}
                b.wheres = append(b.wheres, condition)
                b.bindings.where = append(b.bindings.where, args[1].([]string)...)
                goto end
            }
        }

        if !b.invalidOperator(operator) {
            operator = "="
        }

        condition := &where{column.(string), operator, value, booll}
        b.wheres = append(b.wheres, condition)
        b.bindings.where = append(b.bindings.where, value)
    case map[string]string:
        return b.addArrayOfWheres(column.(map[string]string), "and")
    }

end:
    return b
}

func (b *Builder) addArrayOfWheres(wheres map[string]string, boolean string) *Builder {
    for k, v := range wheres {
        condition := &where{k, "=", v, boolean}
        b.wheres = append(b.wheres, condition)
        b.bindings.where = append(b.bindings.where, v)
    }
    return b
}

/*

comment on exported method Builder.Join should be of the form "Join ..."

Join("tableB", "tableB.id = tableA.bId")
*/
func (b *Builder) Join(table string, condition string, args ...string) *Builder {
    joinType := "inner"
    lenArgs := len(args)
    if lenArgs > 0 {
        joinType = args[0]
    }
    j := &join{table, condition, joinType}
    b.joins = append(b.joins, j)
    return b
}

func (b *Builder) LeftJoin(table string, condition string) *Builder {

exported method Builder.LeftJoin should have comment or be unexported

    return b.Join(table, condition, "left")
}

func (b *Builder) RightJoin(table string, condition string) *Builder {

exported method Builder.RightJoin should have comment or be unexported

    return b.Join(table, condition, "right")
}

func (b *Builder) GroupBy(group interface{}) *Builder {

exported method Builder.GroupBy should have comment or be unexported

    switch group.(type) {
    case []string:
        b.groups = append(b.groups, group.([]string)...)
    case string:
        b.groups = append(b.groups, group.(string))
    }
    return b
}

/*

comment on exported method Builder.Having should be of the form "Having ..."

Having("column", "1")
Having("column", "=","1")
Having("column", "=", "1", "or")
Having(map[string]string{column1:"1", column2: "2"})
*/
func (b *Builder) Having(column interface{}, operator string, args ...string) *Builder {
    switch column.(type) {
    case string:
        value := ""
        booll := "and"
        lenArgs := len(args)
        if lenArgs == 0 {
            value = operator
        } else if lenArgs >= 1 {
            value = args[0]
        } else if lenArgs >= 2 {
            booll = args[1]
        }

        if !b.invalidOperator(operator) {
            operator = "="
        }

        condition := &where{column.(string), operator, value, booll}
        b.havings = append(b.havings, condition)
        b.bindings.having = append(b.bindings.having, value)
    case map[string]string:
        return b.addArrayOfHavings(column.(map[string]string), "and")
    }

    return b
}

func (b *Builder) addArrayOfHavings(wheres map[string]string, boolean string) *Builder {
    for k, v := range wheres {
        condition := &where{k, "=", v, boolean}
        b.havings = append(b.wheres, condition)
        b.bindings.having = append(b.bindings.having, v)
    }
    return b
}

func (b *Builder) OrderBy(column string, direction string) *Builder {

exported method Builder.OrderBy should have comment or be unexported

    b.orders = append(b.orders, &order{column, direction})
    return b
}

func (b *Builder) OrderByAsc(column string) *Builder {

exported method Builder.OrderByAsc should have comment or be unexported

    return b.OrderBy(column, "asc")
}

func (b *Builder) OrderByDesc(column string) *Builder {

exported method Builder.OrderByDesc should have comment or be unexported

    return b.OrderBy(column, "desc")
}

func (b *Builder) Offset(offset interface{}) *Builder {

exported method Builder.Offset should have comment or be unexported

    switch offset.(type) {
    case string:
        b.offset = offset.(string)
    case int:
        b.offset = strconv.Itoa(offset.(int))
    }
    return b
}

func (b *Builder) Limit(limit interface{}) *Builder {

exported method Builder.Limit should have comment or be unexported

    switch limit.(type) {
    case string:
        b.limit = limit.(string)
    case int:
        b.limit = strconv.Itoa(limit.(int))
    }
    return b
}

func (b *Builder) Insert(table string, info map[string]string) *Builder {

exported method Builder.Insert should have comment or be unexported

    b.table = table

    for column, value := range info {
        b.insert = append(b.insert, column)
        b.bindings.insert = append(b.bindings.insert, value)
    }
    return b
}

func (b *Builder) Update(table string, info map[string]interface{}) *Builder {

exported method Builder.Update should have comment or be unexported

    b.table = table
    for column, value := range info {

        switch value.(type) {
        case string:
            b.update = append(b.update, column)
            b.bindings.update = append(b.bindings.update, value.(string))
        case *Expression:
            //表达式： 如 a = a + 1
            b.update = append(b.update, value)
        }
    }
    return b
}

func (b *Builder) Delete(table string) *Builder {

exported method Builder.Delete should have comment or be unexported

    b.table = table
    b.delete = true
    return b
}

func (b *Builder) ToSql() (sql string, bindings []string) {

exported method Builder.ToSql should have comment or be unexported

method ToSql should be ToSQL

    if b.delete {
        return CompileDelete(b)
    } else if len(b.insert) > 0 {
        return CompileInsert(b)
    } else if len(b.update) > 0 {
        return CompileUpdate(b)
    } else {
        return CompileSelect(b)
    }
}