genbrief/db/query.go

package db

import (
	"database/sql"
	"fmt"
	"genBrief/common/log"
	"strings"
)

type Query struct {
	db    *sql.DB
	table string
	attr  string
	where string
	from  uint32
	to    uint32
	order string
	quote string
	args  []interface{}
	rows  *sql.Rows
}

func New(table string) *Query {
	quote := "`"
	if strings.Contains(table, " ") {
		quote = ""
	}
	return &Query{db: p1.Db, table: table, quote: quote, args: []interface{}{}}
}

func (sl *Query) LeftJoin(format string, args ...interface{}) *Query {
	sl.table += " LEFT JOIN " + format
	sl.args = append(sl.args, args...)
	sl.quote = ""
	return sl
}

func (sl *Query) Match(attrs, value string) *Query {
	sl.init_where()
	//sl.where += " MATCH(" + attrs + ") AGAINST(?)"
	//sl.where += " MATCH(" + attrs + ") AGAINST(? IN BOOLEAN MODE)"
	sl.where += " MATCH(" + attrs + ") AGAINST(? IN NATURAL LANGUAGE MODE)"
	sl.args = append(sl.args, value)
	return sl
}

func (sl *Query) Locate(attrs, value string) *Query {
	sl.init_where()
	sl.where += fmt.Sprintf(" LOCATE(?,%v)>0 ", attrs)
	sl.args = append(sl.args, value)
	return sl
}

func (sl *Query) Attr(attr string) *Query {
	sl.attr = attr
	return sl
}

func (sl *Query) init_where() {
	if sl.where == "" {
		sl.where = " where "
	} else {
		sl.where += " and "
	}
}

func (sl *Query) Where(attr string, value interface{}) *Query {
	sl.init_where()
	sl.where += sl.quote + attr + sl.quote + "=?"
	sl.args = append(sl.args, value)
	return sl
}

func (sl *Query) WhereF(attr string, value ...interface{}) *Query {
	sl.init_where()
	sl.where += attr
	sl.args = append(sl.args, value...)
	return sl
}

func (sl *Query) Like(attr string, value interface{}) *Query {
	sl.init_where()
	sl.where += sl.quote + attr + sl.quote + " like ?"
	sl.args = append(sl.args, "%"+value.(string)+"%")
	return sl
}

func (sl *Query) In(attr string, param ...interface{}) *Query {
	if len(param) < 1 {
		return sl
	}
	sl.init_where()
	what := "?"
	for i := 1; i < len(param); i++ {
		what += ",?"
	}
	sl.where += sl.quote + attr + sl.quote + " in (" + what + ")"
	sl.args = append(sl.args, param...)
	return sl
}

func (sl *Query) Order(order string) *Query {
	sl.order = order
	return sl
}

func (sl *Query) Limit(from, to uint32) *Query {
	sl.from = from
	sl.to = to
	return sl
}

func (sl *Query) GetRow() *sql.Row {
	//sqlString := "select " + sl.attr + " from " + sl.table + sl.where + " " + sl.order + " limit 1"
	sqlString := fmt.Sprintf("SELECT %v FROM %v %v %v LIMIT 1", sl.attr, sl.table, sl.where, sl.order)
	row := sl.db.QueryRow(sqlString, sl.args...)
	return row
}

func (sl *Query) GetAll() (tableData []map[string]interface{}, err error) {
	//sqlString := "select " + sl.attr + " from " + sl.table + sl.where + sl.order
	sqlString := fmt.Sprintf("SELECT %v FROM %v %v %v", sl.attr, sl.table, sl.where, sl.order)
	// limit 语句
	if sl.to > sl.from {
		sqlString += " limit ?,?"
		sl.args = append(sl.args, sl.from, sl.to)
	}
	var rows *sql.Rows
	if rows, err = sl.db.Query(sqlString, sl.args...); err != nil {
		log.Info("getData", err.Error(), sqlString)
		return
	}
	if tableData, err = FetchData(rows); err != nil {
		log.Info("getData error", err.Error(), sqlString)
	}
	return
}

func (sl *Query) GetOne() (data map[string]interface{}, err error) {
	//sqlString := "select " + sl.attr + " from " + sl.table + sl.where + sl.order
	sqlString := fmt.Sprintf("SELECT %v FROM %v %v %v", sl.attr, sl.table, sl.where, sl.order)
	// limit 语句
	sqlString += " limit 1"
	rows, err := sl.db.Query(sqlString, sl.args...)
	if err != nil {
		log.Info("getData", err.Error(), sqlString)
		return
	}
	return FetchOne(rows)
}

func (sl *Query) GetWithTotal() (tableData []map[string]interface{}, total int) {
	sqltotal := "select count(1) from " + sl.table + sl.where
	log.Debugf("sqltotal:%v", sqltotal)
	err := sl.db.QueryRow(sqltotal, sl.args...).Scan(&total)
	if err != nil {
		log.Info("gettotal", err.Error(), sqltotal)
		return nil, 0
	}
	//sqlString := "select " + sl.attr + " from " + sl.table + sl.where + sl.order + " limit ?,?"
	sqlString := fmt.Sprintf("SELECT %v FROM %v %v %v LIMIT ?,?", sl.attr, sl.table, sl.where, sl.order)
	sl.args = append(sl.args, sl.from, sl.to)
	log.Debugf("sqlString:%v", sqlString)
	rows, err := sl.db.Query(sqlString, sl.args...)
	if err != nil {
		log.Info("getData", err.Error(), sqlString)
		return nil, 0
	}
	tableData, _ = FetchData(rows)
	return tableData, total
}

func SqlOne(sqlString string, args ...interface{}) (map[string]interface{}, error) {
	rows, err := Pool().Db.Query(sqlString, args...)
	if err != nil {
		log.Warnf("SqlOne[%s] err:%v", sqlString, err)
		return nil, err
	}
	return FetchOne(rows)
}

func SqlAll(sqlString string, args ...interface{}) []map[string]interface{} {
	tableData := make([]map[string]interface{}, 0)
	rows, err := Pool().Db.Query(sqlString, args...)
	if err != nil {
		log.Warnf("SqlAll[%s] err:%v", sqlString, err)
		return tableData
	}
	tableData, err = FetchData(rows)
	if err != nil {
		log.Warnf("SqlAll[%s] err:%v", sqlString, err)
		return tableData
	}
	return tableData
}