starainrt/database.go

package starainrt

import (
	"database/sql"
	"errors"
	"reflect"
	"strconv"
	"time"
)

// DBRes 单连接下的数据库资源句柄
var DBRes *sql.DB

// DBRows 单连接下的数据库查询句柄
var DBRows *sql.Rows

// StarDB 一个简单封装的DB库
type StarDB struct {
	DB   *sql.DB
	Rows *sql.Rows
}

// StarRows 为查询结果集（按行）
type StarRows struct {
	Rows         *sql.Rows
	Length       int
	StringResult []map[string]string
	Columns      []string
	ColumnsType  []reflect.Type
	columnref    map[string]int
	result       [][]interface{}
}

// StarResult 为查询结果集（总）
type StarResult struct {
	Result      []interface{}
	Columns     []string
	columnref   map[string]int
	ColumnsType []reflect.Type
}

// StarResultCol 为查询结果集（按列）
type StarResultCol struct {
	Result []interface{}
}

// MustBytes 列查询结果转Bytes
func (star *StarResultCol) MustBytes() [][]byte {
	var res [][]byte
	for _, v := range star.Result {
		res = append(res, v.([]byte))
	}
	return res
}

// MustBool 列查询结果转Bool
func (star *StarResultCol) MustBool() []bool {
	var res []bool
	var tmp bool
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = false
		case bool:
			tmp = vtype
		case float64:
			if vtype > 0 {
				tmp = true
			} else {
				tmp = false
			}
		case float32:
			if vtype > 0 {
				tmp = true
			} else {
				tmp = false
			}
		case int:
			if vtype > 0 {
				tmp = true
			} else {
				tmp = false
			}
		case int32:
			if vtype > 0 {
				tmp = true
			} else {
				tmp = false
			}
		case int64:
			if vtype > 0 {
				tmp = true
			} else {
				tmp = false
			}
		case string:
			tmp, _ = strconv.ParseBool(vtype)
		default:
			tmp, _ = strconv.ParseBool(string(vtype.([]byte)))
		}
		res = append(res, tmp)
	}
	return res
}

// MustFloat32 列查询结果转Float32
func (star *StarResultCol) MustFloat32() []float32 {
	var res []float32
	var tmp float32
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = 0
		case float32:
			tmp = vtype
		case float64:
			tmp = float32(vtype)
		case string:
			tmps, _ := strconv.ParseFloat(vtype, 32)
			tmp = float32(tmps)
		case int:
			tmp = float32(vtype)
		case int32:
			tmp = float32(vtype)
		case int64:
			tmp = float32(vtype)
		case time.Time:
			tmp = float32(vtype.Unix())
		default:
			tmpt := string(vtype.([]byte))
			tmps, _ := strconv.ParseFloat(tmpt, 32)
			tmp = float32(tmps)
		}
		res = append(res, tmp)
	}
	return res
}

// MustFloat64 列查询结果转Float64
func (star *StarResultCol) MustFloat64() []float64 {
	var res []float64
	var tmp float64
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = 0
		case float64:
			tmp = vtype
		case float32:
			tmp = float64(vtype)
		case string:
			tmp, _ = strconv.ParseFloat(vtype, 64)
		case int:
			tmp = float64(vtype)
		case int32:
			tmp = float64(vtype)
		case int64:
			tmp = float64(vtype)
		case time.Time:
			tmp = float64(vtype.Unix())
		default:
			tmpt := string(vtype.([]byte))
			tmps, _ := strconv.ParseFloat(tmpt, 64)
			tmp = float64(tmps)
		}
		res = append(res, tmp)
	}
	return res
}

// MustString 列查询结果转String
func (star *StarResultCol) MustString() []string {
	var res []string
	var tmp string
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = ""
		case string:
			tmp = vtype
		case int64:
			tmp = strconv.FormatInt(vtype, 10)
		case int32:
			tmp = strconv.Itoa(int(vtype))
		case bool:
			tmp = strconv.FormatBool(vtype)
		case float64:
			tmp = strconv.FormatFloat(vtype, 'f', 10, 64)
		case float32:
			tmp = strconv.FormatFloat(float64(vtype), 'f', 10, 32)
		case int:
			tmp = strconv.Itoa(vtype)
		case time.Time:
			tmp = vtype.String()
		default:
			tmp = string(vtype.([]byte))
		}
		res = append(res, tmp)
	}
	return res
}

// MustInt32 列查询结果转Int32
func (star *StarResultCol) MustInt32() []int32 {
	var res []int32
	var tmp int32
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = 0
		case float64:
			tmp = int32(vtype)
		case float32:
			tmp = int32(vtype)
		case string:
			tmps, _ := strconv.ParseInt(vtype, 10, 32)
			tmp = int32(tmps)
		case int:
			tmp = int32(vtype)
		case int64:
			tmp = int32(vtype)
		case int32:
			tmp = vtype
		case time.Time:
			tmp = int32(vtype.Unix())
		default:
			tmpt := string(vtype.([]byte))
			tmps, _ := strconv.ParseInt(tmpt, 10, 32)
			tmp = int32(tmps)
		}
		res = append(res, tmp)
	}
	return res
}

// MustInt64 列查询结果转Int64
func (star *StarResultCol) MustInt64() []int64 {
	var res []int64
	var tmp int64
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = 0
		case float64:
			tmp = int64(vtype)
		case float32:
			tmp = int64(vtype)
		case string:
			tmps, _ := strconv.ParseInt(vtype, 10, 64)
			tmp = int64(tmps)
		case int:
			tmp = int64(vtype)
		case int32:
			tmp = int64(vtype)
		case int64:
			tmp = vtype
		case time.Time:
			tmp = vtype.Unix()
		default:
			tmpt := string(vtype.([]byte))
			tmp, _ = strconv.ParseInt(tmpt, 10, 64)
		}
		res = append(res, tmp)
	}
	return res
}

// MustInt 列查询结果转Int
func (star *StarResultCol) MustInt() []int {
	var res []int
	var tmp int
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = 0
		case float64:
			tmp = int(vtype)
		case float32:
			tmp = int(vtype)
		case string:
			tmps, _ := strconv.ParseInt(vtype, 10, 64)
			tmp = int(tmps)
		case int:
			tmp = vtype
		case int32:
			tmp = int(vtype)
		case int64:
			tmp = int(vtype)
		case time.Time:
			tmp = int(vtype.Unix())
		default:
			tmpt := string(vtype.([]byte))
			tmps, _ := strconv.ParseInt(tmpt, 10, 64)
			tmp = int(tmps)
		}
		res = append(res, tmp)
	}
	return res
}

// MustDate 列查询结果转Date(time.Time)
func (star *StarResultCol) MustDate(layout string) []time.Time {
	var res []time.Time
	var tmp time.Time
	for _, v := range star.Result {
		switch vtype := v.(type) {
		case nil:
			tmp = time.Time{}
		case float64:
			tmp = time.Unix(int64(vtype), int64(vtype-float64(int64(vtype)))*1000000000)
		case float32:
			tmp = time.Unix(int64(vtype), int64(vtype-float32(int64(vtype)))*1000000000)
		case string:
			tmp, _ = time.Parse(layout, vtype)
		case int:
			tmp = time.Unix(int64(vtype), 0)
		case int32:
			tmp = time.Unix(int64(vtype), 0)
		case int64:
			tmp = time.Unix(vtype, 0)
		case time.Time:
			tmp = vtype
		default:
			tmpt := string(vtype.([]byte))
			tmp, _ = time.Parse(layout, tmpt)
		}
		res = append(res, tmp)
	}
	return res
}

// IsNil 检测是不是nil 列查询结果是不是nil
func (star *StarResultCol) IsNil(name string) []bool {
	var res []bool
	var tmp bool
	for _, v := range star.Result {
		switch v.(type) {
		case nil:
			tmp = true
		default:
			tmp = false
		}
		res = append(res, tmp)
	}
	return res
}

// IsNil 检测是不是nil
func (star *StarResult) IsNil(name string) bool {
	num, ok := star.columnref[name]
	if !ok {
		return false
	}
	tmp := star.Result[num]
	switch tmp.(type) {
	case nil:
		return true
	default:
		return false
	}
}

// MustDate 列查询结果转Date
func (star *StarResult) MustDate(name, layout string) time.Time {
	var res time.Time
	num, ok := star.columnref[name]
	if !ok {
		return time.Time{}
	}
	tmp := star.Result[num]
	switch vtype := tmp.(type) {
	case nil:
		res = time.Time{}
	case float64:
		res = time.Unix(int64(vtype), int64(vtype-float64(int64(vtype)))*1000000000)
	case float32:
		res = time.Unix(int64(vtype), int64(vtype-float32(int64(vtype)))*1000000000)
	case string:
		res, _ = time.Parse(layout, vtype)
	case int:
		res = time.Unix(int64(vtype), 0)
	case int32:
		res = time.Unix(int64(vtype), 0)
	case int64:
		res = time.Unix(vtype, 0)
	case time.Time:
		res = vtype
	default:
		res, _ = time.Parse(layout, string(tmp.([]byte)))
	}
	return res
}

// MustInt64 列查询结果转int64
func (star *StarResult) MustInt64(name string) int64 {
	var res int64
	num, ok := star.columnref[name]
	if !ok {
		return 0
	}
	tmp := star.Result[num]
	switch vtype := tmp.(type) {
	case nil:
		res = 0
	case float64:
		res = int64(vtype)
	case float32:
		res = int64(vtype)
	case string:
		res, _ = strconv.ParseInt(vtype, 10, 64)
	case int:
		res = int64(vtype)
	case int32:
		res = int64(vtype)
	case int64:
		res = vtype
	case time.Time:
		res = int64(vtype.Unix())
	default:
		res, _ = strconv.ParseInt(string(tmp.([]byte)), 10, 64)
	}
	return res
}

// MustInt32 列查询结果转Int32
func (star *StarResult) MustInt32(name string) int32 {
	var res int32
	num, ok := star.columnref[name]
	if !ok {
		return 0
	}
	tmp := star.Result[num]
	switch vtype := tmp.(type) {
	case nil:
		res = 0
	case float64:
		res = int32(vtype)
	case float32:
		res = int32(vtype)
	case string:
		ress, _ := strconv.ParseInt(vtype, 10, 32)
		res = int32(ress)
	case int:
		res = int32(vtype)
	case int32:
		res = vtype
	case int64:
		res = int32(vtype)
	case time.Time:
		res = int32(vtype.Unix())
	default:
		ress, _ := strconv.ParseInt(string(tmp.([]byte)), 10, 32)
		res = int32(ress)
	}
	return res
}

// MustString 列查询结果转string
func (star *StarResult) MustString(name string) string {
	var res string
	num, ok := star.columnref[name]
	if !ok {
		return ""
	}
	switch vtype := star.Result[num].(type) {
	case nil:
		res = ""
	case string:
		res = vtype
	case int64:
		res = strconv.FormatInt(vtype, 10)
	case int32:
		res = strconv.Itoa(int(vtype))
	case bool:
		res = strconv.FormatBool(vtype)
	case float64:
		res = strconv.FormatFloat(vtype, 'f', 10, 64)
	case float32:
		res = strconv.FormatFloat(float64(vtype), 'f', 10, 32)
	case int:
		res = strconv.Itoa(vtype)
	case time.Time:
		res = vtype.String()
	default:
		res = string(vtype.([]byte))
	}
	return res
}

// MustFloat64 列查询结果转float64
func (star *StarResult) MustFloat64(name string) float64 {
	var res float64
	num, ok := star.columnref[name]
	if !ok {
		return 0
	}
	switch vtype := star.Result[num].(type) {
	case nil:
		res = 0
	case string:
		res, _ = strconv.ParseFloat(vtype, 64)
	case float64:
		res = vtype
	case int:
		res = float64(vtype)
	case int64:
		res = float64(vtype)
	case int32:
		res = float64(vtype)
	case float32:
		res = float64(vtype)
	case time.Time:
		res = float64(vtype.Unix())
	default:
		res, _ = strconv.ParseFloat(string(vtype.([]byte)), 64)
	}
	return res
}

// MustFloat32 列查询结果转float32
func (star *StarResult) MustFloat32(name string) float32 {
	var res float32
	num, ok := star.columnref[name]
	if !ok {
		return 0
	}
	switch vtype := star.Result[num].(type) {
	case nil:
		res = 0
	case string:
		tmp, _ := strconv.ParseFloat(vtype, 32)
		res = float32(tmp)
	case float64:
		res = float32(vtype)
	case float32:
		res = vtype
	case int:
		res = float32(vtype)
	case int64:
		res = float32(vtype)
	case int32:
		res = float32(vtype)
	case time.Time:
		res = float32(vtype.Unix())
	default:
		tmp, _ := strconv.ParseFloat(string(vtype.([]byte)), 32)
		res = float32(tmp)
	}
	return res
}

// MustInt 列查询结果转int
func (star *StarResult) MustInt(name string) int {
	var res int
	num, ok := star.columnref[name]
	if !ok {
		return 0
	}
	tmp := star.Result[num]
	switch vtype := tmp.(type) {
	case nil:
		res = 0
	case float64:
		res = int(vtype)
	case float32:
		res = int(vtype)
	case string:
		ress, _ := strconv.ParseInt(vtype, 10, 64)
		res = int(ress)
	case int:
		res = vtype
	case int32:
		res = int(vtype)
	case int64:
		res = int(vtype)
	case time.Time:
		res = int(vtype.Unix())
	default:
		ress, _ := strconv.ParseInt(string(tmp.([]byte)), 10, 64)
		res = int(ress)
	}
	return res
}

// MustBool 列查询结果转bool
func (star *StarResult) MustBool(name string) bool {
	var res bool
	num, ok := star.columnref[name]
	if !ok {
		return false
	}
	tmp := star.Result[num]
	switch vtype := tmp.(type) {
	case nil:
		res = false
	case bool:
		res = vtype
	case float64:
		if vtype > 0 {
			res = true
		} else {
			res = false
		}
	case float32:
		if vtype > 0 {
			res = true
		} else {
			res = false
		}
	case int:
		if vtype > 0 {
			res = true
		} else {
			res = false
		}
	case int32:
		if vtype > 0 {
			res = true
		} else {
			res = false
		}
	case int64:
		if vtype > 0 {
			res = true
		} else {
			res = false
		}
	case string:
		res, _ = strconv.ParseBool(vtype)
	default:
		res, _ = strconv.ParseBool(string(vtype.([]byte)))
	}
	return res
}

// MustBytes 列查询结果转byte
func (star *StarResult) MustBytes(name string) []byte {
	num, ok := star.columnref[name]
	if !ok {
		return []byte{}
	}
	res := star.Result[num].([]byte)
	return res
}

// Rescan 重新分析结果集
func (star *StarRows) Rescan() {
	star.parserows()
}

// Col 选择需要进行操作的数据结果列
func (star *StarRows) Col(name string) *StarResultCol {
	result := new(StarResultCol)
	if _, ok := star.columnref[name]; !ok {
		return result
	}
	var rescol []interface{}
	for _, v := range star.result {
		rescol = append(rescol, v[star.columnref[name]])
	}
	result.Result = rescol
	return result
}

// Row 选择需要进行操作的数据结果行
func (star *StarRows) Row(id int) *StarResult {
	result := new(StarResult)
	if id+1 > len(star.result) {
		return result
	}
	result.Result = star.result[id]
	result.Columns = star.Columns
	result.ColumnsType = star.ColumnsType
	result.columnref = star.columnref
	return result
}

// Close 关闭打开的结果集
func (star *StarRows) Close() error {
	return star.Rows.Close()
}

func (star *StarRows) parserows() {
	star.result = [][]interface{}{}
	star.columnref = make(map[string]int)
	star.StringResult = []map[string]string{}
	star.Columns, _ = star.Rows.Columns()
	types, _ := star.Rows.ColumnTypes()
	for _, v := range types {
		star.ColumnsType = append(star.ColumnsType, v.ScanType())
	}
	scanArgs := make([]interface{}, len(star.Columns))
	values := make([]interface{}, len(star.Columns))
	for i := range values {
		star.columnref[star.Columns[i]] = i
		scanArgs[i] = &values[i]
	}
	for star.Rows.Next() {
		if err := star.Rows.Scan(scanArgs...); err != nil {
			return
		}
		record := make(map[string]string)
		var rescopy []interface{}
		for i, col := range values {
			rescopy = append(rescopy, col)
			switch vtype := col.(type) {
			case float32:
				record[star.Columns[i]] = strconv.FormatFloat(float64(vtype), 'f', -1, 64)
			case float64:
				record[star.Columns[i]] = strconv.FormatFloat(vtype, 'f', -1, 64)
			case int64:
				record[star.Columns[i]] = strconv.FormatInt(vtype, 10)
			case int32:
				record[star.Columns[i]] = strconv.FormatInt(int64(vtype), 10)
			case int:
				record[star.Columns[i]] = strconv.Itoa(vtype)
			case string:
				record[star.Columns[i]] = vtype
			case bool:
				record[star.Columns[i]] = strconv.FormatBool(vtype)
			case time.Time:
				record[star.Columns[i]] = vtype.String()
			case nil:
				record[star.Columns[i]] = ""
			default:
				record[star.Columns[i]] = string(vtype.([]byte))
			}
		}
		star.result = append(star.result, rescopy)
		star.StringResult = append(star.StringResult, record)
	}
	star.Length = len(star.StringResult)
}

// Query 进行Query操作
func (star *StarDB) Query(args ...interface{}) (*StarRows, error) {
	var err error
	effect := new(StarRows)
	if err = star.DB.Ping(); err != nil {
		return effect, err
	}
	if len(args) == 0 {
		return effect, errors.New("no args")
	}
	if len(args) == 1 {
		sql := args[0]
		if star.Rows, err = star.DB.Query(sql.(string)); err != nil {
			return effect, err
		}
		effect.Rows = star.Rows
		effect.parserows()
		return effect, nil
	}
	sql := args[0]
	stmt, err := star.DB.Prepare(sql.(string))
	if err != nil {
		return effect, err
	}
	defer stmt.Close()
	var para []interface{}
	for k, v := range args {
		if k != 0 {
			switch vtype := v.(type) {
			default:
				para = append(para, vtype)
			}
		}
	}
	if star.Rows, err = stmt.Query(para...); err != nil {
		return effect, err
	}
	effect.Rows = star.Rows
	effect.parserows()
	return effect, nil
}

// Open 打开一个新的数据库
func (star *StarDB) Open(Method, ConnStr string) error {
	var err error
	star.DB, err = sql.Open(Method, ConnStr)
	if err != nil {
		return err
	}
	err = star.DB.Ping()
	return err
}

// Close 关闭打开的数据库
func (star *StarDB) Close() error {
	if err := star.DB.Close(); err != nil {
		return err
	}
	return star.DB.Close()
}

// Exec 执行Exec操作
func (star *StarDB) Exec(args ...interface{}) (sql.Result, error) {
	var err error
	var effect sql.Result
	if err = star.DB.Ping(); err != nil {
		return effect, err
	}
	if len(args) == 0 {
		return effect, errors.New("no args")
	}
	if len(args) == 1 {
		sql := args[0]
		if _, err = star.DB.Exec(sql.(string)); err != nil {
			return effect, err
		}
		return effect, nil
	}
	sql := args[0]
	stmt, err := star.DB.Prepare(sql.(string))
	if err != nil {
		return effect, err
	}
	defer stmt.Close()
	var para []interface{}
	for k, v := range args {
		if k != 0 {
			switch vtype := v.(type) {
			default:
				para = append(para, vtype)
			}
		}
	}
	if effect, err = stmt.Exec(para...); err != nil {
		return effect, err
	}
	return effect, nil
}

// FetchAll 把结果集全部转为key-value型<string>数据
func FetchAll(rows *sql.Rows) (error, map[int]map[string]string) {
	var ii int = 0
	records := make(map[int]map[string]string)
	columns, err := rows.Columns()
	if err != nil {
		return err, records
	}
	scanArgs := make([]interface{}, len(columns))
	values := make([]interface{}, len(columns))
	for i := range values {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		if err := rows.Scan(scanArgs...); err != nil {
			return err, records
		}
		record := make(map[string]string)
		for i, col := range values {
			switch vtype := col.(type) {
			case float64:
				record[columns[i]] = strconv.FormatFloat(vtype, 'f', -1, 64)
			case int64:
				record[columns[i]] = strconv.FormatInt(vtype, 10)
			case string:
				record[columns[i]] = vtype
			case nil:
				record[columns[i]] = ""
			default:
				record[columns[i]] = string(vtype.([]byte))
			}
		}
		records[ii] = record
		ii++
	}
	return nil, records
}

// OpenDB 打开一个数据库
func OpenDB(Method, ConnStr string) error {
	var err error
	DBRes, err = sql.Open(Method, ConnStr)
	if err != nil {
		return err
	}
	err = DBRes.Ping()
	return err
}

// CloseDB 关闭打开的数据库
func CloseDB() {
	DBRes.Close()
	DBRows.Close()
}

// Query 进行数据库查询操作
func Query(args ...interface{}) (error, map[int]map[string]string) {
	var err error
	records := make(map[int]map[string]string)
	if err = DBRes.Ping(); err != nil {
		return err, records
	}
	if len(args) == 0 {
		return errors.New("no args"), records
	}
	if len(args) == 1 {
		sql := args[0]
		if DBRows, err = DBRes.Query(sql.(string)); err != nil {
			return err, records
		}
		return FetchAll(DBRows)
	}
	sql := args[0]
	stmt, err := DBRes.Prepare(sql.(string))
	defer stmt.Close()
	if err != nil {
		return err, records
	}
	var para []interface{}
	for k, v := range args {
		if k != 0 {
			switch vtype := v.(type) {
			default:
				para = append(para, vtype)
			}
		}
	}
	if DBRows, err = stmt.Query(para...); err != nil {
		return err, records
	}
	return FetchAll(DBRows)

}

// DBExec 进行数据库EXEC查询操作
func DBExec(args ...interface{}) error {
	var err error
	if err = DBRes.Ping(); err != nil {
		return err
	}
	if len(args) == 0 {
		return errors.New("no args")
	}
	if len(args) == 1 {
		sql := args[0]
		if _, err = DBRes.Exec(sql.(string)); err != nil {
			return err
		}
		return nil
	}
	sql := args[0]
	stmt, err := DBRes.Prepare(sql.(string))
	defer stmt.Close()
	if err != nil {
		return err
	}
	var para []interface{}
	for k, v := range args {
		if k != 0 {
			switch vtype := v.(type) {
			default:
				para = append(para, vtype)
			}
		}
	}
	if _, err = stmt.Exec(para...); err != nil {
		return err
	}
	return nil

}