bileiqi/05.code/backend/db/db_mysql.go

package db

import (
	MYSQL "database/sql"
	"encoding/json"
	"fmt"
	"io/ioutil"
	"os"
	"os/exec"
	"path"
	"path/filepath"
	"rtzh_elec_temperature/conf"
	"rtzh_elec_temperature/global"
	"rtzh_elec_temperature/logger"
	"rtzh_elec_temperature/tools"
	"strings"

	"github.com/astaxie/beego/orm"
	_ "github.com/go-sql-driver/mysql"
)

type DbConfig struct {
	Host    string `json:"host"`
	Port    string `json:"port"`
	User    string `json:"user"`
	Pwd     string `json:"pwd"`
	Db      string `json:"dbname"`
	Charset string `json:"charset"`
}

var DbConfigJSON DbConfig

//返回一个新的orm对象
func CreateOrm() orm.Ormer {
	return orm.NewOrm()
}

func ConnDB(cnfpath string) (*MYSQL.DB, error) {
	logger.Logger.Println("加载数据库配置文件:" + cnfpath)
	fileHanlder, err := os.Open(cnfpath)
	if err != nil {
		logger.Logger.Error(fmt.Sprintf(" 配置文件%s加载失败：%s\r", cnfpath, err))
		os.Exit(1)
	}
	txt, _ := ioutil.ReadAll(fileHanlder)
	fileHanlder.Close()
	txtStr := strings.Trim(string(txt), " ")
	if txtStr == "" {
		logger.Logger.Error("数据库配置文件内容为空，请正确配置")
		os.Exit(1)
	}
	logger.Logger.Debug(txtStr)
	if txtStr[0:1] != "[" {
		txtStr = "[" + txtStr + "]"
	}
	cfgdata := []DbConfig{}
	err = json.Unmarshal([]byte(txtStr), &cfgdata)
	if err != nil {
		logger.Logger.Error("配置文件解析失败:" + err.Error())
		os.Exit(1)
	}
	var conn *MYSQL.DB
	for i, cfgItem := range cfgdata {
		if cfgItem.Port == "" {
			cfgItem.Port = "3306"
		}
		if cfgItem.Charset == "" {
			cfgItem.Charset = "utf8"
		}
		//urll:=root:11111111@tcp(127.0.0.1:3306)/mytest?charset=utf8
		url := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", cfgItem.User, cfgItem.Pwd, cfgItem.Host, cfgItem.Port, cfgItem.Db, cfgItem.Charset)
		conn, err := MYSQL.Open("mysql", url)
		if err != nil {
			return conn, err
		}
		global.MysqlConn = conn
		aliasName := cfgItem.Db
		if i == 0 {
			aliasName = "default" //第一个定义的数据库为默认数据库
		}
		err = orm.RegisterDataBase(aliasName, "mysql", url, 30)
		if err != nil {
			logger.Logger.Error("数据库[" + cfgItem.Db + "]连接失败：" + err.Error())
			os.Exit(0)
		}
		logger.Logger.Println("Mysql数据库[" + cfgItem.Db + "]连接成功")
		if conf.GlobalConfig["loglevel"] == "5" {
			orm.Debug = true
		}
		orm.SetMaxIdleConns(aliasName, 2)
		orm.SetMaxOpenConns(aliasName, 50)
		//测试数据库连接
		sql := "select 1 "
		testdb := orm.NewOrm()
		testdb.Using(aliasName)
		lst := []orm.Params{}
		_, err = testdb.Raw(sql).Values(&lst)
		if err != nil {
			logger.Logger.Println(fmt.Sprintf("数据库%s访问失败", cfgItem.Db))
			logger.Logger.Error(err)
			os.Exit(0)
		}
		if cfgItem.Db == "iss" {
			new(IssDB).Conn(cfgItem)
		}
	}
	//检查默认数据是否初始化
	sql := "select param_value from global_sys_param where param_name=? "
	o := orm.NewOrm()
	o.Using("default")
	datas := []orm.Params{}
	_, dberr := o.Raw(sql, "db_script_version").Values(&datas)
	//CacheMonitorPoint()
	if dberr != nil {
		fileHanlder, err = os.Open("db/db_init.sql")
		if err != nil {
			logger.Logger.Error("数据库初始化脚本文件db/db_init.sql加载失败：" + err.Error())
			return nil, err
		}
		//txt, _ = ioutil.ReadAll(fileHanlder)
		fileHanlder.Close()
		cmd := exec.Command("mysql", "-h"+cfgdata[0].Host, "-P"+cfgdata[0].Port,
			"-u"+cfgdata[0].User, "-p"+cfgdata[0].Pwd, "-D"+cfgdata[0].Db,
			"-e", "source db/db_init.sql")
		err = cmd.Start()
		if err != nil {
			logger.Logger.Error(err)
		}
		cmd.Wait()
	}

	db_script_version := ""
	if len(datas) > 0 {
		db_script_version = tools.IsEmpty(datas[0]["param_value"])
	} else {
		o.Raw("insert into global_sys_param (param_name,param_value,param_memo)values(?,?,?)", "db_script_version", "0", "数据库更新版本").Exec()
	}
	//检查是否有数据库更新脚本
	dbUpdatefile, err2 := ioutil.ReadDir("db")
	if err2 == nil {
		for _, fsitem := range dbUpdatefile {
			fs_n := fsitem.Name()
			filepath.Walk(fs_n, func(fpath string, fi os.FileInfo, err error) error {
				if path.Ext(fpath) == ".sql" && strings.HasPrefix(fpath, "db_update_v") {
					script_v := strings.Replace(fs_n, "db_update_v", "", 1)
					script_v = strings.Trim(strings.Replace(script_v, ".sql", "", 1), " ")
					if db_script_version == "" || script_v > db_script_version {
						fileHanlder, err = os.Open("db/" + fs_n)
						if err != nil {
							logger.Logger.Error("数据库初始化脚本文件db/" + fs_n + "加载失败：" + err.Error())
							return nil
						}
						//txt, _ = ioutil.ReadAll(fileHanlder)
						fileHanlder.Close()
						cmd := exec.Command("mysql", "-h"+cfgdata[0].Host, "-P"+cfgdata[0].Port,
							"-u"+cfgdata[0].User, "-p"+cfgdata[0].Pwd, "-D"+cfgdata[0].Db,
							"-e", "source db/"+fs_n)
						err = cmd.Start()
						if err != nil {
							logger.Logger.Error(err)
						} else {
							o.Raw("update global_sys_param set param_value=? where param_name=?", script_v, "db_script_version").Exec()
						}
						cmd.Wait()
					}
				}
				return nil
			})
		}
	} else {
		logger.Logger.Error(err2)
	}
	DbConfigJSON = cfgdata[0]
	return conn, nil
}

func CloseDB() {
	if global.MysqlConn != nil {
		global.MysqlConn.Close()
	}
}

func CheckErr(err error) {
	if err != nil {
		fmt.Println(tools.NowTime() + ":" + err.Error())
		panic(err)
	}

}

//测试数据库切换
func DbSwitchTest() {
	db1 := orm.NewOrm() //操作默认数据库
	tmplst1 := []orm.Params{}
	db1.Raw("select * from global_sys_param").Values(&tmplst1)
	fmt.Println(tmplst1)
	err := db1.Using("gzpd1") //切换到其他数据库
	if err != nil {
		logger.Logger.Println(err.Error())
	} else {
		tmplst2 := []orm.Params{}
		db1.Raw("select * from global_sys_param").Values(&tmplst2)
		fmt.Println(tmplst2)
	}
	db1.Using("default") //切换到默认数据库
	db1.Raw("select * from global_const_code").Values(&tmplst1)
	fmt.Println(tmplst1)
}