gobackend-qingma/util/mysql.go

package util

import (
	"crypto/md5"
	"encoding/hex"
	"log"
	"math"
	"reflect"
	"strings"
	"sync"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"github.com/pborman/uuid"
	"xiaoniaokuaiyan.com/xiaoniao/config"
)

var instance *sqlx.DB
var once sync.Once

var str = "xiaoniao_n:xnC+21%@Ydm@tcp(114.67.232.21:3308)/xn_db_clear"

//var str = "xiaoniao_web:sy1xn8902^!(@@tcp(101.201.196.166:3308)/xiaoniao_db"

func createSqlDB() {
	var err error
	var (
		//dsec     = config.IniConf.Section("database")
		//host     = dsec.Key("mysql.host").Value()
		//user     = dsec.Key("mysql.user").Value()
		//password = dsec.Key("mysql.password").Value()
		//port     = dsec.Key("mysql.port").Value()
		//database = dsec.Key("mysql.database").Value()
		//dns      = fmt.Sprintf("%s:%s@(%s:%s)/%s", user, password, host, port, database)
	)

	if instance != nil {
		instance.Close()
	}
	//instance, err = sqlx.Connect("mysql", dns)
	//instance, err = sqlx.Connect("mysql", "xiaoniao:xn321#@!@tcp(123.57.224.234:3306)/jeecg")
	instance, err = sqlx.Connect("mysql", str)
	if err != nil {
		log.Println(err)
	}
	instance = instance.Unsafe()
}

func GetSqlDB() *sqlx.DB {
	once.Do(func() {
		createSqlDB()
		config.RegistChangeCallback(createSqlDB)
	})
	return instance
}

var winstance *sqlx.DB
var wonce sync.Once

func createWriteSqlDB() {
	var err error
	var (
	//dsec     = config.IniConf.Section("database")
	//host     = dsec.Key("mysql.write.host").Value()
	//user     = dsec.Key("mysql.write.user").Value()
	//password = dsec.Key("mysql.write.password").Value()
	//port     = dsec.Key("mysql.write.port").Value()
	//database = dsec.Key("mysql.database").Value()
	//dns      = fmt.Sprintf("%s:%s@(%s:%s)/%s", user, password, host, port, database)
	)
	if winstance != nil {
		winstance.Close()
	}
	winstance, err = sqlx.Connect("mysql", str)
	//instance, err = sqlx.Connect("mysql", "xiaoniao:xn321#@!@tcp(123.57.224.234:3306)/jeecg")
	if err != nil {
		log.Println(err)
	}
	winstance = winstance.Unsafe()

}

func GetWriteSqlDB() *sqlx.DB {
	wonce.Do(func() {
		createWriteSqlDB()
		config.RegistChangeCallback(createWriteSqlDB)
	})
	return winstance
}

func GenerateUpdateSql(tableName string, fields map[string]interface{}, where string) string {
	if len(fields) < 1 {
		return ""
	}
	var sqlStr = "update " + tableName + " set "
	var fstr = ""
	for key, _ := range fields {
		fstr += key + "=:" + key + ","
	}
	return sqlStr + fstr[0:len(fstr)-1] + where
}

func structToMap(fields interface{}) map[string]interface{} {
	rv := reflect.ValueOf(fields).Elem()
	rt := rv.Type()
	fnum := rt.NumField()
	resultMap := map[string]interface{}{}
	var fv reflect.Value
	for i := 0; i < fnum; i++ {
		fv = rv.Field(i)
		zero := reflect.Zero(fv.Type()).Interface()
		current := fv.Interface()
		tag := rt.Field(i).Tag.Get("db")
		tags := strings.Split(tag, ",")
		if reflect.DeepEqual(zero, current) {
			continue
		}
		if len(tags) > 1 && tags[1] == "positive" {
			if reflect.DeepEqual(current, -1) {
				current = zero
			}
		}
		tag = tags[0]
		if tag == "null" || tag == "-" {
			continue
		}
		if tag == "" {
			tag = rt.Field(i).Name
		}
		resultMap[tag] = current
	}
	return resultMap
}

func GenerateUpdateSqlFromStruct(tableName string, fields interface{}, where string) (string, map[string]interface{}) {
	kvm := structToMap(fields)
	sqlStr := GenerateUpdateSql(tableName, kvm, where)
	return sqlStr, kvm
}

func GenerateInsertSql(tableName string, fields map[string]interface{}) string {
	var sqlStr = "insert into " + tableName + "("
	var (
		fstr = ""
		vstr = " values("
	)
	for key, _ := range fields {
		fstr += key + ","
		vstr += ":" + key + ","
	}
	fstr = fstr[0:len(fstr)-1] + ")"
	return sqlStr + fstr + vstr[0:len(vstr)-1] + ")"
}

func GenerateInsertSqlFromStruct(tableName string, fields interface{}) (string, map[string]interface{}) {
	kvm := structToMap(fields)
	sqlStr := GenerateInsertSql(tableName, kvm)
	return sqlStr, kvm
}

func GenerateSelectSql(tableName string, selFields []string, where string) string {
	var sqlStr = "select "
	for _, key := range selFields {
		sqlStr += key + ","
	}
	sqlStr = sqlStr[0:len(sqlStr)-1] + " from " + tableName + "  " + where
	return sqlStr
}

func GenerateWhereSql(params []string) string {
	mlen := len(params) - 1
	var whereStr string = " where "
	for i, v := range params {
		whereStr += v + "= ? "
		if i < mlen {
			whereStr += "and "
		}
	}
	return whereStr
}

func GenerateDBID() string {
	randBuf := uuid.NewRandom()
	ret := md5.Sum(randBuf)
	id := hex.EncodeToString(ret[0:])
	return strings.ToLower(id)
}

type TagIndex struct {
	Idx         int
	IsOmitEmpty bool
}

func ToFix(num float64, places int) float64 {
	pow := math.Pow(10, float64(places+1))
	digit := num * pow
	mod := math.Mod(digit, float64(10))
	if mod >= 5 {
		return (math.Floor(digit/10) + 1) * 10 / pow
	}
	return math.Floor(digit) / pow
}

/*func ScanStruct(rows *sql.Rows, dest interface{}) error {
	rv := reflect.ValueOf(dest)
	if rv.Kind() != reflect.Ptr {
		return errors.New("must pass a pointer, not a value, to StructScan destination")
	}
	if rv.IsNil() {
		return errors.New("nil pointer passed to StructScan destination")
	}
	direct := rv.Elem()
	dtype := direct.Type()
	if dtype.Kind() != reflect.Slice {
		return fmt.Errorf("expected %s but got %s", reflect.Slice, dtype.Kind())
	}
	elemType := dtype.Elem()
	var (
		field     reflect.StructField
		fmap      = map[string]TagIndex{}
		dbtagVal  string
		tagSplits []string
		fimap     = map[TagIndex]int{}
	)
	cols, _ := rows.Columns()
	for i := 0; i < elemType.NumField(); i++ {
		field = elemType.Field(i)
		dbtagVal = field.Tag.Get("db")
		tagIdx := TagIndex{}
		tagSplits = strings.Split(dbtagVal, ",")
		if len(tagSplits) > 1 {
			dbtagVal = tagSplits[0]
			if tagSplits[1] == "omitempty" {
				tagIdx.IsOmitEmpty = true
			}
		}
		if dbtagVal == "" {
			dbtagVal = field.Name
		}
		tagIdx.Idx = i
		fmap[dbtagVal] = tagIdx
	}
	for rows.Next() {
		nelemVal := reflect.New(elemType)
		valArr := make([]interface{}, len(cols))
		for i, col := range cols {
			if fieldIdx, ok := fmap[col]; !ok {
				valArr[i] = &struct{}{}
			} else {
				field = elemType.Field(fieldIdx.Idx)
				if fieldIdx.IsOmitEmpty {
					valArr[i] = &[]byte{}
				} else {
					valArr[i] = reflect.New(field.Type).Interface()
				}
				fimap[fieldIdx] = i
			}
		}
		rows.Scan(valArr...)
		for k, v := range fimap {
			fval := reflect.ValueOf(valArr[v])
			if k.IsOmitEmpty && fval.IsValid() {
				if fval.Elem().Len() <= 0 {
					continue
				}
				field := elemType.Field(k.Idx)
				dstFval := reflect.New(field.Type)
				switch field.Type.Kind() {
				case reflect.Float64:
					//bits := binary.LittleEndian.Uint64(fval.Elem().Bytes())
					//float := math.Float64frombits(bits)
					float, _ := strconv.ParseFloat(string(fval.Elem().Bytes()), 64)
					float = ToFix(float, 2)
					dstFval.Elem().Set(reflect.ValueOf(float))
				case reflect.String:
					str := string(fval.Elem().Bytes())
					dstFval.Elem().Set(reflect.ValueOf(str))
				case reflect.Int:
					//bits := binary.LittleEndian.Uint64(fval.Elem().Bytes())
					intVal, _ := strconv.ParseInt(string(fval.Elem().Bytes()), 10, 0)
					dstFval.Elem().Set(reflect.ValueOf(int(intVal)))
				}
				//fmt.Println(dstFval.Elem().Interface())
				nelemVal.Elem().Field(k.Idx).Set(dstFval.Elem())
			} else {
				nelemVal.Elem().Field(k.Idx).Set(fval.Elem())
			}
		}
		direct.Set(reflect.Append(direct, nelemVal.Elem()))
	}
	return nil
}
func ScanStructOld(rows *sql.Rows, dest interface{}) error {
	rv := reflect.ValueOf(dest)
	if rv.Kind() != reflect.Ptr {
		return errors.New("must pass a pointer, not a value, to StructScan destination")
	}
	if rv.IsNil() {
		return errors.New("nil pointer passed to StructScan destination")
	}
	direct := rv.Elem()
	dtype := direct.Type()
	if dtype.Kind() != reflect.Slice {
		return fmt.Errorf("expected %s but got %s", reflect.Slice, dtype.Kind())
	}
	elemType := dtype.Elem()
	var (
		field    reflect.StructField
		fmap     = map[string]int{}
		dbtagVal string
		fimap    = map[int]int{}
	)
	cols, _ := rows.Columns()
	for i := 0; i < elemType.NumField(); i++ {
		field = elemType.Field(i)
		dbtagVal = field.Tag.Get("db")
		if dbtagVal == "null" {
			continue
		}
		if dbtagVal == "" {
			dbtagVal = field.Name
		}
		fmap[dbtagVal] = i
	}
	for rows.Next() {
		nelemVal := reflect.New(elemType)
		valArr := make([]interface{}, len(cols))
		for i, col := range cols {
			if fieldIdx, ok := fmap[col]; !ok {
				valArr[i] = &struct{}{}
			} else {
				field = elemType.Field(fieldIdx)
				fval := reflect.New(field.Type)
				valArr[i] = fval.Interface()
				fimap[fieldIdx] = i
			}
		}
		rows.Scan(valArr...)
		fmt.Println(reflect.ValueOf(valArr[len(cols)-3]).Elem().Interface())
		for k, v := range fimap {
			nelemVal.Elem().Field(k).Set(reflect.ValueOf(valArr[v]).Elem())
		}
		direct.Set(reflect.Append(direct, nelemVal.Elem()))
	}
	return nil
}*/