dxp-core/dxp-engine/service/business_database.go

package service

import (
	"DataShare/dao"
	"DataShare/global"
	"DataShare/middleware"
	"DataShare/model"
	"bytes"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/sirupsen/logrus"
)

func UserLogin(req *model.LoginRequest)(*model.ResponseToken,error){

	//1、先检查key是否存在
	//2、创建token
	ts, err := middleware.CreateToken(req.AppId,req.AppSecret)
	if err != nil {
		return nil, errors.New("创建token失败")
	}
	// save token to redis
	uidValue := model.UuidValue{
		AppId: req.AppId,
		AppSecret: req.AppSecret,
	}
	jsonUidValue,_ := json.Marshal(&uidValue)
	dao.DeleteTokenByUserNameAndType(req.AppId,req.AppSecret)
	err = dao.CreateAuth(string(jsonUidValue), ts)
	if err != nil {
		return nil, err
	}

	result := model.ResponseToken{
		AccessToken:  ts.AccessToken,
		RefreshToken: ts.RefreshToken,
		ExpiresAt:    ts.AtExpires,
	}
	return &result, nil
}

func UserLogout()error{

	return nil
}

func getDataBaseInfoById(id int)(*global.DbInfo){

	for i:=0;i<len(global.Databases);i++{
		if global.Databases[i].Id == id {
			return &global.Databases[i]
		}
	}
	return nil
}

//数据库连接
func DatabaseConnect(req *model.DbConnInfo)(error){

	db, err := dao.DbConnect(req.Type,req.Connection)
	if err != nil {
		return err
	}

	if req.Mode == "test"{
		db.Close()
	}else if req.Mode == "add"{
		//添加到队列
		dbInfo := global.DbInfo{
			DbHandle: db,
			ConnInfo: req.Connection,
			Status:   true,
			SqlType: req.Type,
			DbName: req.DbName,
			Id: req.Id,
		}
		for i:=0;i<len(global.Databases);i++{
			if global.Databases[i].Id == req.Id{
				//已经存在，则删除它，重新添加
				global.Databases = append(global.Databases[:i], global.Databases[i+1:]...)
			}
		}
		global.Databases = append(global.Databases, dbInfo)
	}

	return nil
}

//获取缓存中的数据库列表
func DatabaseList(req *model.Page)(*model.CommonList,error){

	total := len(global.Databases)

	if (req.PageNo-1)*req.PageSize >= total {
		return &model.CommonList{total,nil},nil
	}
	//分页处理
	var result []global.DbInfo
	start := (req.PageNo-1) * req.PageSize
	end := start + req.PageSize
	if end < total {
		result = global.Databases[start:end]
	}else {
		result = global.Databases[start:]
	}

	tableData := make([]map[string]interface{}, 0)
	entry := make(map[string]interface{})
	for i:=0;i<len(result);i++{
		entry["id"] = result[i].Id
		entry["status"] = result[i].Status
		entry["connInfo"] = result[i].ConnInfo
		entry["sqlType"] = result[i].SqlType
	}
	tableData = append(tableData, entry)

	return &model.CommonList{total,tableData},nil
}

//更新
func DatabaseUpdate()error{

	return nil
}

//查询
func QueryTableList(dbId int)(*model.CommonList,error){

	dbInfo := getDataBaseInfoById(dbId)
	if dbInfo==nil{
		return nil,errors.New("数据库不存在!")
	}
	db := dbInfo.DbHandle
	dbName := dbInfo.DbName

	if dbInfo.SqlType == "mysql"{

		sql := "select SQL_CALC_FOUND_ROWS TABLE_NAME as name,TABLE_COMMENT as comments FROM information_schema.TABLES WHERE TABLE_SCHEMA=?"
		data,_,_,total,err := dao.GetData(db,sql,dbName)
		if err != nil {
			return nil,err
		}
		return &model.CommonList{
			Total: total,
			Items: data,
		},nil
	}else if dbInfo.SqlType == "dm8"{

		sql := fmt.Sprintf("select t.object_id, t.object_name as name, t.object_type as comments from dba_objects t where t.owner='%s' and t.object_type='TABLE'",dbName)
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}
		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil
	}else if dbInfo.SqlType == "postgres"{

		sql := "select tablename as name,tableowner as comments from pg_tables where schemaname='public'"
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}
		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil
	}else if dbInfo.SqlType == "kingbase"{

		//sql := "select SQL_CALC_FOUND_ROWS TABLE_NAME as name,TABLE_COMMENT as comments FROM information_schema.TABLES WHERE TABLE_SCHEMA=?"
		sql := "select tablename as name,tableowner as comments from pg_tables where schemaname='public'"
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}

		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil
	}

	return nil,nil
}

func QueryTableColnList(dbId int, tbName string)(*model.CommonList,error){

	dbInfo := getDataBaseInfoById(dbId)
	if dbInfo==nil{
		return nil,errors.New("数据库不存在!")
	}

	db := dbInfo.DbHandle
	dbName := dbInfo.DbName

	if dbInfo.SqlType == "mysql"{

		sql := "select SQL_CALC_FOUND_ROWS COLUMN_NAME as name,COLUMN_COMMENT as comments,DATA_TYPE as type from information_schema.columns where table_schema=? and table_name=?"
		data,_,_,total,err := dao.GetData(db,sql,dbName,tbName)
		if err != nil {
			return nil,err
		}

		return &model.CommonList{
			Total: total,
			Items: data,
		},nil
	}else if dbInfo.SqlType == "dm8"{

		sql := fmt.Sprintf("select t.TABLE_NAME,t.COLUMN_NAME as name,t.COLUMN_ID,t.DATA_TYPE as type,t.DATA_LENGTH as dataLength,t.DATA_PRECISION as comments," +
			"t.DATA_SCALE,t.NULLABLE,t.DATA_DEFAULT from user_tab_columns t where t.TABLE_NAME = '%s' order by t.COLUMN_ID",tbName)
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}
		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil
	}else if dbInfo.SqlType == "postgres"{

		sql := fmt.Sprintf("SELECT col_description(a.attrelid,a.attnum) as comments,pg_type.typname as type,a.attname as name, a.attnotnull as notnull" +
			" FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid " +
			"where c.relname = '%s' and a.attrelid = c.oid and a.attnum>0",tbName)
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}
		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil

	}else if dbInfo.SqlType == "kingbase" {

		sql := fmt.Sprintf("SELECT col_description(a.attrelid,a.attnum) as comments,pg_type.typname as type,a.attname as name, a.attnotnull as notnull" +
			" FROM pg_class as c,pg_attribute as a inner join pg_type on pg_type.oid = a.atttypid " +
			"where c.relname = '%s' and a.attrelid = c.oid and a.attnum>0",tbName)
		data,_,_,_,err := dao.GetData(db,sql)
		if err != nil {
			return nil,err
		}

		return &model.CommonList{
			Total: len(data),
			Items: data,
		},nil
	}

	return nil,nil
}
//删除数据库
func DatabaseDel(id int) error{

	dbInfo := getDataBaseInfoById(id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	for i:=0;i<len(global.Databases);i++{
		if id == global.Databases[i].Id {
			if global.Databases[i].DbHandle != nil {
				global.Databases[i].DbHandle.Close()
			}
			//移除它
			global.Databases = append(global.Databases[:i], global.Databases[i+1:]...)
			break;
		}
	}

	return nil
}
//动态创建表
func DatabaseCreate(req *model.DatabaseCreateRequest)error{

	dbInfo := getDataBaseInfoById(req.Id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}

	findPK := false
	for n:=0;n<len(req.Columns);n++ {
		if req.Columns[n].IsPk == 1 {
			findPK = true
			break
		}
	}
	if req.DbType == "mysql"{
		args := make([]interface{},0)
		sql := "create table %s"
		args = append(args,req.TbName)

		sql +="(\n"
		for n:=0;n<len(req.Columns);n++{
			if req.Columns[n].DataLength > 0 {
				sql +="%s %s(%d)"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
				args = append(args,req.Columns[n].DataLength)
			}else {
				sql +="%s %s"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
			}
			if req.Columns[n].IsIncrement == 1 {
				sql += " AUTO_INCREMENT"
			}
			if req.Columns[n].IsRequired == 1 {
				sql += " not null"
			}

			if n == (len(req.Columns)-1){
				if findPK == false {
					sql += "\n"
					break
				}
			}
			sql += ",\n"
		}
		for n:=0;n<len(req.Columns);n++ {
			if req.Columns[n].IsPk == 1 {
				sql += "PRIMARY KEY (%s)"
				args = append(args,req.Columns[n].ColName)
				break
			}
		}
		sql += "\n)ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;"

		sqlStr := fmt.Sprintf(sql,args...)

		fmt.Println("----------------")
		fmt.Println(sqlStr)
		fmt.Println("----------------")

		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}else if req.DbType == "dm8"{
		args := make([]interface{},0)
		sql := "create table %s"
		args = append(args,req.TbName)

		sql +="(\n"
		for n:=0;n<len(req.Columns);n++{
			if req.Columns[n].DataLength > 0 {
				sql +="%s %s(%d)"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
				args = append(args,req.Columns[n].DataLength)
			}else {
				sql +="%s %s"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
			}
			if req.Columns[n].IsIncrement == 1 {
				sql += " IDENTITY(1,1)"
			}
			if req.Columns[n].IsRequired == 1 {
				sql += " not null"
			}

			if n == (len(req.Columns)-1){
				if findPK == false {
					sql += "\n"
					break
				}
			}
			sql += ",\n"
		}
		for n:=0;n<len(req.Columns);n++ {
			if req.Columns[n].IsPk == 1 {
				sql += "PRIMARY KEY (%s)"
				args = append(args,req.Columns[n].ColName)
				break
			}
		}
		sql += "\n)"

		sqlStr := fmt.Sprintf(sql,args...)

		fmt.Println("----------------")
		fmt.Println(sqlStr)
		fmt.Println("----------------")

		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}else if req.DbType == "postgres" {

		args := make([]interface{},0)
		sql := "create table %s"
		args = append(args,req.TbName)

		sql +="(\n"
		for n:=0;n<len(req.Columns);n++{
			if req.Columns[n].DataLength > 0 {
				sql +="%s %s(%d)"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
				args = append(args,req.Columns[n].DataLength)
			}else {
				sql +="%s %s"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
			}
			if req.Columns[n].IsIncrement == 1 {
				//sql += " AUTO_INCREMENT"
			}
			if req.Columns[n].IsRequired == 1 {
				sql += " not null"
			}

			if n == (len(req.Columns)-1){
				if findPK == false {
					sql += "\n"
					break
				}
			}
			sql += ",\n"
		}
		for n:=0;n<len(req.Columns);n++ {
			if req.Columns[n].IsPk == 1 {
				sql += "PRIMARY KEY (%s)"
				args = append(args,req.Columns[n].ColName)
				break
			}
		}
		sql += "\n)"
		sqlStr := fmt.Sprintf(sql,args...)
		fmt.Println(sqlStr)
		return dao.Exec(dbInfo.DbHandle,sqlStr)

	}else if req.DbType == "kingbase" {

		args := make([]interface{},0)
		sql := "create table %s"
		args = append(args,req.TbName)

		sql +="(\n"
		for n:=0;n<len(req.Columns);n++{
			if req.Columns[n].DataLength > 0 {
				sql +="%s %s(%d)"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
				args = append(args,req.Columns[n].DataLength)
			}else {
				sql +="%s %s"
				args = append(args,req.Columns[n].ColName)
				args = append(args,req.Columns[n].DataType)
			}
			if req.Columns[n].IsIncrement == 1 {
				//sql += " AUTO_INCREMENT"
			}
			if req.Columns[n].IsRequired == 1 {
				sql += " not null"
			}

			if n == (len(req.Columns)-1){
				if findPK == false {
					sql += "\n"
					break
				}
			}
			sql += ",\n"
		}
		for n:=0;n<len(req.Columns);n++ {
			if req.Columns[n].IsPk == 1 {
				sql += "PRIMARY KEY (%s)"
				args = append(args,req.Columns[n].ColName)
				break
			}
		}
		sql += "\n)"
		sqlStr := fmt.Sprintf(sql,args...)
		fmt.Println(sqlStr)
		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}

	return errors.New("not support database type")
}
//动态添加索引
func DatabaseAddIndex(req* model.TbIndex) error{

	dbInfo := getDataBaseInfoById(req.Id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}

	if dbInfo.SqlType == "mysql"{
		//ALTER TABLE `taxi_dispatch`
		//ADD unique uix_taxi_dispatch_document_code(`document_code`);
		//
		args := make([]interface{},0)
		sql := "ALTER TABLE %s ADD UNIQUE KEY ("
		args = append(args,req.TbName)
		first := 1
		for i:=0;i<len(req.IndexArray);i++{
			if first == 0 {
				sql += ","
			}else{
				first = 0
			}
			sql += "%s"
			args = append(args,req.IndexArray[i])
		}
		sql += ")"

		sqlStr := fmt.Sprintf(sql,args...)

		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}else if dbInfo.SqlType == "dm8"{
		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		args := make([]interface{},0)
		sql := "create index %s on %s.%s("
		args = append(args,index_name)
		args = append(args,dbInfo.DbName)
		args = append(args,req.TbName)
		first := 1
		for i:=0;i<len(req.IndexArray);i++{
			if first == 0 {
				sql += ","
			}else{
				first = 0
			}
			sql += "%s"
			args = append(args,req.IndexArray[i])
		}
		sql += ")"

		sqlStr := fmt.Sprintf(sql,args...)

		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}else if dbInfo.SqlType == "kingbase"{
		//create index username on tb_test(username)
		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		args := make([]interface{},0)
		sql := "create index %s on %s("
		args = append(args,index_name)
		args = append(args,req.TbName)
		first := 1
		for i:=0;i<len(req.IndexArray);i++{
			if first == 0 {
				sql += ","
			}else{
				first = 0
			}
			sql += "%s"
			args = append(args,req.IndexArray[i])
		}
		sql += ")"

		sqlStr := fmt.Sprintf(sql,args...)

		return dao.Exec(dbInfo.DbHandle,sqlStr)

	}else if dbInfo.SqlType == "postgres" {
		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		args := make([]interface{},0)
		sql := "create index %s on %s("
		args = append(args,index_name)
		args = append(args,req.TbName)
		first := 1
		for i:=0;i<len(req.IndexArray);i++{
			if first == 0 {
				sql += ","
			}else{
				first = 0
			}
			sql += "%s"
			args = append(args,req.IndexArray[i])
		}
		sql += ")"

		sqlStr := fmt.Sprintf(sql,args...)

		return dao.Exec(dbInfo.DbHandle,sqlStr)
	}

	return nil
}
//动态删除索引
func DatabaseDeleteIndex(req* model.TbIndex)error{

	dbInfo := getDataBaseInfoById(req.Id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}

	if dbInfo.SqlType == "mysql"{
		//ALTER TABLE `taxi_dispatch` DROP INDEX ix_document_code,
		//drop index idx_driver_id;
		args := make([]interface{},0)
		sql := "ALTER TABLE %s"
		args = append(args,req.TbName)
		first := 1
		for i:=0;i<len(req.IndexArray);i++{
			if first == 0 {
				sql += ","
			}else{
				first = 0
			}
			sql += " DROP INDEX %s"
			args = append(args,req.IndexArray[i])
		}

		sqlStr := fmt.Sprintf(sql,args...)
		return dao.Exec(dbInfo.DbHandle,sqlStr)

	}else if dbInfo.SqlType == "dm8"{
		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		return dao.Exec(dbInfo.DbHandle,fmt.Sprintf("drop index %s",index_name))
	} else if dbInfo.SqlType == "kingbase" {
		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		return dao.Exec(dbInfo.DbHandle,fmt.Sprintf("drop index %s",index_name))

	}else if dbInfo.SqlType == "postgres"{

		index_name := fmt.Sprintf("%s_",req.TbName)
		for i:=0;i<len(req.IndexArray);i++ {
			index_name += fmt.Sprintf("%s_",req.IndexArray[i])
		}
		index_name += "idx"

		return dao.Exec(dbInfo.DbHandle,fmt.Sprintf("drop index %s",index_name))
	}

	return nil
}
//动态插入数据
func DatabaseInsert(req *model.ColnDataInsert)error{

	dbInfo := getDataBaseInfoById(req.Id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}

	if len(req.ColNameArray) == 0 {
		return errors.New("请指定列名称.")
	}
	if len(req.TbName) == 0 {
		return errors.New("请指定数据表名称.")
	}
	if dbInfo.SqlType == "mysql" || dbInfo.SqlType=="kingbase" || dbInfo.SqlType == "postgres" || dbInfo.SqlType=="dm8" {
		fmtStr := ``
		sql := fmt.Sprintf("insert into `%s` (", req.TbName)

		var buffer bytes.Buffer
		for i := 0; i < len(req.ColNameArray); i++ {
			if i == len(req.ColNameArray)-1 {
				sql += fmt.Sprintf("`%s`) values ", req.ColNameArray[i])
				fmtStr += `"%s"`

			} else {
				sql += fmt.Sprintf("`%s`,", req.ColNameArray[i])
				fmtStr += `"%s",`
			}
		}
		if _, err := buffer.WriteString(sql); err != nil {
			return err
		}

		//rowWidth := len(req.ColNameArray)
		//n := len(req.Rows)/rowWidth
		//for i:=0;i<n;i++{
		//	offset := i*rowWidth
		//	if i == n-1 {
		//		args := make([]interface{},0)
		//		args = append(args,req.Rows[offset:])
		//		buffer.WriteString(`(`)
		//		buffer.WriteString(fmt.Sprintf(fmtStr, args...))
		//		buffer.WriteString(`);`)
		//	}else{
		//		offset2 := (i+1)*rowWidth
		//		args := make([]interface{},0)
		//		args = append(args,req.Rows[offset:offset2])
		//		buffer.WriteString(`(`)
		//		buffer.WriteString(fmt.Sprintf(fmtStr, args...))
		//		buffer.WriteString(`),`)
		//	}
		//}

		for i := 0; i < len(req.Rows); i++ {
			if i == len(req.Rows)-1 {
				args := make([]interface{}, 0)
				for j := 0; j < len(req.Rows[i].List); j++ {
					args = append(args, req.Rows[i].List[j])
				}
				buffer.WriteString(`(`)
				buffer.WriteString(fmt.Sprintf(fmtStr, args...))
				buffer.WriteString(`);`)
			} else {
				args := make([]interface{}, 0)
				for j := 0; j < len(req.Rows[i].List); j++ {
					args = append(args, req.Rows[i].List[j])
				}
				buffer.WriteString(`(`)
				buffer.WriteString(fmt.Sprintf(fmtStr, args...))
				buffer.WriteString(`),`)
			}
		}
		//fmt.Println("insert data:",buffer.String())
		return dao.Exec(dbInfo.DbHandle,buffer.String())
	}

	return nil
}
//删除指定表的内容
func DatabaseDeleteTable(id int, tbName string)error{

	dbInfo := getDataBaseInfoById(id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}
	sql := fmt.Sprintf("delete from %s",tbName)

	return dao.Exec(dbInfo.DbHandle,sql)
}
//删除表
func DatabaseDropTable(id int, tbName string)error{

	dbInfo := getDataBaseInfoById(id)
	if dbInfo==nil{
		return errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}
	sql := fmt.Sprintf("drop table %s",tbName)

	return dao.Exec(dbInfo.DbHandle,sql)
}
//查询数据库
func DatabaseQuery(req *model.DbQuery)(*model.CommonList,error){

	dbInfo := getDataBaseInfoById(req.Id)
	if dbInfo==nil{
		return nil,errors.New("数据库不存在!")
	}

	if dbInfo.Status == false {
		return nil,errors.New(fmt.Sprintf("database:%s not connect",dbInfo.DbName))
	}

	if dbInfo.SqlType == "mysql" {
		sql := "select"
		if req.PageSize > 0 {
			sql += " SQL_CALC_FOUND_ROWS"
		}
		args := make([]interface{},0)
		first := 1
		if req.QueryColn == nil {
			sql += " *"
		}else{
			for i:=0;i<len(req.QueryColn);i++{
				if first == 0{
					sql += ","
				}else{
					first = 0
				}
				sql +=" %s"
				args = append(args,req.QueryColn[i])
			}
			if len(req.QueryColn) == 0 {
				sql += " *"
			}
		}

		sql += " from %s"
		args = append(args,req.TbName)
		where := 0
		for zz:=0;zz<len(req.Query);zz++{
			if len(req.Query[zz].ColName) > 0 {
				if where == 0 {
					sql += " where"
					where = 1
				}else{
					sql += " and"
				}

				if req.Query[zz].Cond == 0 {
					sql += " %s='%s'"
				}else if req.Query[zz].Cond == 1 {
					sql += " %s>'%s'"
				}else{
					sql += " %s<'%s'"
				}
				args = append(args,req.Query[zz].ColName)
				args = append(args,req.Query[zz].ColVal)
			}
		}

		if len(req.Sort.ColName) > 0 {
			if req.Sort.IsDesc == 1 {
				sql += " order by %s desc"
			}else {
				sql += " order by %s"
			}
			args = append(args,req.Sort.ColName)
		}
		if req.PageSize > 0 {
			sql += " limit %d,%d"
			args = append(args,(req.PageNo-1)*req.PageSize)
			args = append(args,req.PageSize)
		}
		sqlStr := fmt.Sprintf(sql,args...)
		fmt.Println("------------")
		fmt.Println(sqlStr)
		fmt.Println("------------")
		items,_,_,total,_ := dao.GetData(dbInfo.DbHandle,sqlStr)

		result := &model.CommonList{
			Total: total,
			Items: items,
		}

		return result,nil
	}else if dbInfo.SqlType == "dm8"{
		sql := "select"
		sqlCount := "select count(*)"
		args_count := make([]interface{},0)
		args := make([]interface{},0)
		first := 1
		if req.QueryColn == nil {
			sql += " *"
		}else{
			for i:=0;i<len(req.QueryColn);i++{
				if first == 0{
					sql += ","
				}else{
					first = 0
				}
				sql +=" %s"
				args = append(args,req.QueryColn[i])
			}
			if len(req.QueryColn) == 0 {
				sql += " *"
			}
		}

		sql += " from %s"
		sqlCount += " from %s"
		args = append(args,req.TbName)
		args_count = append(args_count,req.TbName)
		where := 0
		for zz:=0;zz<len(req.Query);zz++{
			if len(req.Query[zz].ColName) > 0 {
				if where == 0 {
					sql += " where"
					sqlCount += " where"
					where = 1
				}else{
					sql += " and"
					sqlCount += " and"
				}

				if req.Query[zz].Cond == 0 {
					sql += " %s='%s'"
					sqlCount += " %s='%s'"
				}else if req.Query[zz].Cond == 1 {
					sql += " %s>'%s'"
					sqlCount += " %s>'%s'"
				}else{
					sql += " %s<'%s'"
					sqlCount += " %s<'%s'"
				}
				args = append(args,req.Query[zz].ColName)
				args = append(args,req.Query[zz].ColVal)
				args_count = append(args_count,req.Query[zz].ColName)
				args_count = append(args_count,req.Query[zz].ColVal)

			}
		}

		sqlCntStr := fmt.Sprintf(sqlCount,args_count...)
		total,_:= dao.GetCount(dbInfo.DbHandle,sqlCntStr)

		if len(req.Sort.ColName) > 0 {
			if req.Sort.IsDesc == 1 {
				sql += " order by %s desc"
			}else {
				sql += " order by %s"
			}
			args = append(args,req.Sort.ColName)
		}
		if req.PageSize > 0 {
			sql += " limit %d,%d"
			args = append(args,req.PageSize)
			args = append(args,req.PageNo-1)
		}
		sqlStr := fmt.Sprintf(sql,args...)
		fmt.Println("------------")
		fmt.Println(sqlStr)
		fmt.Println("------------")
		items,_,_,_,_ := dao.GetData(dbInfo.DbHandle,sqlStr)

		result := &model.CommonList{
			Total: total,
			Items: items,
		}

		return result,nil
	}else if dbInfo.SqlType == "postgres" || dbInfo.SqlType == "kingbase"{
		sql := "select"
		sqlCount := "select count(*)"
		args_count := make([]interface{},0)
		args := make([]interface{},0)
		first := 1
		if req.QueryColn == nil {
			sql += " *"
		}else{
			for i:=0;i<len(req.QueryColn);i++{
				if first == 0{
					sql += ","
				}else{
					first = 0
				}
				sql +=" %s"
				args = append(args,req.QueryColn[i])
			}
			if len(req.QueryColn) == 0 {
				sql += " *"
			}
		}

		sql += " from %s"
		sqlCount += " from %s"
		args = append(args,req.TbName)
		args_count = append(args_count,req.TbName)
		where := 0
		for zz:=0;zz<len(req.Query);zz++{
			if len(req.Query[zz].ColName) > 0 {
				if where == 0 {
					sql += " where"
					sqlCount += " where"
					where = 1
				}else{
					sql += " and"
					sqlCount += " and"
				}

				if req.Query[zz].Cond == 0 {
					sql += " %s='%s'"
					sqlCount += " %s='%s'"
				}else if req.Query[zz].Cond == 1 {
					sql += " %s>'%s'"
					sqlCount += " %s>'%s'"
				}else{
					sql += " %s<'%s'"
					sqlCount += " %s<'%s'"
				}
				args = append(args,req.Query[zz].ColName)
				args = append(args,req.Query[zz].ColVal)
				args_count = append(args_count,req.Query[zz].ColName)
				args_count = append(args_count,req.Query[zz].ColVal)

			}
		}

		sqlCntStr := fmt.Sprintf(sqlCount,args_count...)
		total,_:= dao.GetCount(dbInfo.DbHandle,sqlCntStr)

		if len(req.Sort.ColName) > 0 {
			if req.Sort.IsDesc == 1 {
				sql += " order by %s desc"
			}else {
				sql += " order by %s"
			}
			args = append(args,req.Sort.ColName)
		}
		if req.PageSize > 0 {
			sql += " limit %d offset %d"
			args = append(args,req.PageSize)
			args = append(args,req.PageNo-1)
		}
		sqlStr := fmt.Sprintf(sql,args...)
		fmt.Println("------------")
		fmt.Println(sqlStr)
		fmt.Println("------------")
		items,_,_,_,_ := dao.GetData(dbInfo.DbHandle,sqlStr)

		result := &model.CommonList{
			Total: total,
			Items: items,
		}

		return result,nil
	}
	return nil,nil
}
//加载默认连接
func LoadDefaultDbConnect(){

	if global.RedisClient == nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"redis connect failed,load default db connect exit.")
		return
	}
	conn_list := dao.LoadConnectInfo()
	for i:=0;i<len(conn_list);i++{
		err := DatabaseConnect(&conn_list[i])
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,fmt.Sprintf("connect db:%s failed,connect string:%s,error message:%s",
				conn_list[i].DbName,conn_list[i].Connection,err.Error()))
		}
	}
}
//查看数据库的连接状态
func QueryDatabaseStatusByDbId(id int)(bool){

	for i:=0;i<len(global.Databases);i++{
		if id == global.Databases[i].Id {
			return global.Databases[i].Status
		}
	}

	return false
}
//更新连接
func UpdateOfDbConnect(){

	if global.RedisClient == nil {
		global.SystemLogger.Log(logrus.ErrorLevel,"redis connect failed,load default db connect exit.")
		return
	}
	conn_list := dao.LoadConnectInfo()
	//移除数据
	for i:=0;i<len(global.Databases);i++{
		find := false
		for j:=0;j<len(conn_list);j++{
			if global.Databases[i].Id == conn_list[j].Id{
				find = true
				break
			}
		}
		if find == false {
			global.Databases = append(global.Databases[:i], global.Databases[i+1:]...)
			i = 0
		}
	}
	//追加数据
	for i:=0;i<len(conn_list);i++{
		if QueryDatabaseStatusByDbId(conn_list[i].Id) == true{
			continue
		}
		err := DatabaseConnect(&conn_list[i])
		if err != nil {
			global.SystemLogger.Log(logrus.ErrorLevel,fmt.Sprintf("connect db:%s failed,connect string:%s,error message:%s",
				conn_list[i].DbName,conn_list[i].Connection,err.Error()))
		}
	}
}