scd_tools_go/service/db/db_mysql.go

package db

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

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

var DbCfg = DbConfig{}

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"`
}

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

func ConnDB(cnfpath string) (*MYSQL.DB, error) {
	fmt.Println(tools.NowTime()+" 加载数据库配置文件", cnfpath)
	fileHanlder, err := os.Open(cnfpath)
	if err != nil {
		fmt.Printf(tools.NowTime()+" 配置文件%s加载失败：%s\r", cnfpath, err)
		os.Exit(1)
	}
	txt, _ := ioutil.ReadAll(fileHanlder)
	fileHanlder.Close()
	txtStr := string(txt)
	if txtStr == "" {
		fmt.Println(tools.NowTime() + " 配置文件格式不正确，请正确配置")
		os.Exit(1)
	}
	cfgdata := DbConfig{}
	err = json.Unmarshal(txt, &cfgdata)
	if err != nil {
		fmt.Println(tools.NowTime()+" 配置文件解析失败:", err)
		os.Exit(1)
	}
	DbCfg = cfgdata
	if cfgdata.Port == "" {
		cfgdata.Port = "3306"
	}
	if cfgdata.Charset == "" {
		cfgdata.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", cfgdata.User, cfgdata.Pwd, cfgdata.Host, cfgdata.Port, cfgdata.Db, cfgdata.Charset)
	conn, err := MYSQL.Open("mysql", url)
	if err != nil {
		return conn, err
	}
	fmt.Println(tools.NowTime() + " Mysql数据库连接成功")
	global.MysqlConn = conn
	err = orm.RegisterDataBase("default", "mysql", url, 1, 100)
	if err != nil {
		logger.Logger.Debug("连接本地数据库" + cfgdata.Db + "失败:" + err.Error())
		if strings.Index(err.Error(), "Unknown database") > -1 {
			//数据库未创建,连接到mysql数据再创建业务数据库
			crdbSql := "CREATE DATABASE " + cfgdata.Db + " DEFAULT CHARSET utf8 COLLATE utf8_general_ci"
			adminConn, cerr := MYSQL.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", cfgdata.User, cfgdata.Pwd, cfgdata.Host, cfgdata.Port, "mysql", cfgdata.Charset))
			if cerr != nil {
				tools.Error("连接本地数据库mysql失败，" + err.Error())
				os.Exit(1)
			}
			_, cerr = adminConn.Exec(crdbSql)
			if cerr != nil {
				logger.Logger.Error(cerr, fmt.Sprintf("判断并创建数据库"+cfgdata.Db+"时失败："+cerr.Error()))
				os.Exit(1)
			}
			err = orm.RegisterDataBase("default", "mysql", url, 30)
			if err != nil {
				logger.Logger.Error(err.Error())
				os.Exit(1)
			}
		}
	}
	if conf.GlobalConfig["loglevel"] == "5" {
		orm.Debug = true
	}
	orm.SetMaxIdleConns("default", 5)
	orm.SetMaxOpenConns("default", 200)
	db, _ := orm.GetDB("default")
	db.SetConnMaxLifetime(60 * time.Minute)
	sql := "select param_value from global_sys_param where param_name=? "
	o := orm.NewOrm()
	datas := []orm.Params{}
	_, dberr := o.Raw(sql, "db_script_version").Values(&datas)
	//CacheMonitorPoint()
	f, _ := exec.LookPath(os.Args[0])
	fp, _ := filepath.Abs(f)
	i := strings.LastIndex(fp, string(os.PathSeparator))
	prjDir := string(fp[0:i])
	prjDir = prjDir + string(os.PathSeparator) + "db" + string(os.PathSeparator)
	if dberr != nil {
		logger.Logger.Println("检测到系统数据库还未进行初始化创建，开始进行...")
		fileHanlder, err = os.Open("db/db_init.sql")
		if err != nil {
			logger.Logger.Println(" 数据库初始化脚本文件db/db_init.sql加载失败：" + err.Error())
			return nil, err
		}
		//txt, _ = ioutil.ReadAll(fileHanlder)
		fileHanlder.Close()
		var commandName = "mysql"
		logger.Logger.Println("正在执行数据库初始化脚本文件db/db_init.sql")
		logger.Logger.Println(commandName + " -h" + cfgdata.Host + " -P" + cfgdata.Port + " -u" + cfgdata.User + " -p" + cfgdata.Pwd + " -D" + cfgdata.Db + " <" + prjDir + "db_init.sql")
		var cmdErr bytes.Buffer
		if runtime.GOOS == "windows" {
			cmd := exec.Command(commandName, "-h"+cfgdata.Host, "-P"+cfgdata.Port, "-u"+cfgdata.User, "-p"+cfgdata.Pwd, "-D"+cfgdata.Db, "-e", "source "+prjDir+"db_init.sql")
			cmd.Stderr = &cmdErr
			err = cmd.Run()
		} else {
			cmd := exec.Command("sh", "-c", "mysql -h"+cfgdata.Host+" -P"+cfgdata.Port+" -u"+cfgdata.User+" -p"+cfgdata.Pwd+" -D"+cfgdata.Db+" <"+prjDir+"db_init.sql")
			cmd.Stderr = &cmdErr
			err = cmd.Run()
		}
		if err != nil {
			logger.Logger.Println("数据库更新脚本文件db/db_init.sql执行失败：" + cmdErr.String())
			logger.Logger.Error(err)
			logger.Logger.Println(cmdErr.String())
			return nil, err
		}
	}
	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()
	}
	//检查是否有数据库更新脚本
	//数据库更新脚本名称规则：db_update_v{NO}.sql，其中{NO}为版本号。版本号为脚本编写者指定，但必须为比上一版本号大的整数
	//数据库更新为单次迭代执行，所以可以同时放置多次更新脚本文件
	//如需要从初始化脚本开始进行数据库还原，可以手动清除global_sys_param表中db_script_version项值为0或指定版本号
	dbUpdatefile, err2 := ioutil.ReadDir("./db")
	if err2 == nil {
		var commandName = "mysql"
		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.Println(" 数据库更新脚本文件db/" + fs_n + "加载失败：" + err.Error())
							return nil
						}
						fileHanlder.Close()
						logger.Logger.Println(tools.NowTime() + " 正在执行数据库更新脚本文件" + prjDir + fs_n)
						var cmdErr bytes.Buffer
						if runtime.GOOS == "windows" {
							cmd := exec.Command(commandName, "-h"+cfgdata.Host, "-P"+cfgdata.Port, "-u"+cfgdata.User, "-p"+cfgdata.Pwd, "-D"+cfgdata.Db, "-e", "source "+prjDir+fs_n)
							cmd.Stderr = &cmdErr
							err = cmd.Run()
						} else {
							cmd := exec.Command("sh", "-c", "mysql -h"+cfgdata.Host+" -P"+cfgdata.Port+" -u"+cfgdata.User+" -p"+cfgdata.Pwd+" -D"+cfgdata.Db+" <"+prjDir+fs_n)
							cmd.Stderr = &cmdErr
							err = cmd.Run()
						}
						if err != nil {
							logger.Logger.Println(fmt.Sprintf("%v", cfgdata))
							logger.Logger.Println(err)
							logger.Logger.Println(cmdErr.String())
							logger.Logger.Println("数据库更新脚本文件db/" + fs_n + "执行失败：" + cmdErr.String())
							return err
						}
						o.Raw("update global_sys_param set param_value=? where param_name=?", script_v, "db_script_version").Exec()
					}
				}
				return nil
			})
		}
	} else {
		logger.Logger.Error(err2)
	}
	for i := 0; i < 5; i++ {
		global.DbConnOK <- true
		time.Sleep(20 * time.Millisecond)
	}
	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)
	}

}

//获取Mysql安装目录
func getMysqlInsall() (string, error) {
	mysqlInstall := ""
	o := orm.NewOrm()
	var sqlCommandText = "select @@basedir as base_path from dual"
	var tableData []orm.Params
	_, err := o.Raw(sqlCommandText).Values(&tableData)
	if err == nil {
		mysqlInstall = tableData[0]["base_path"].(string)
	}
	return mysqlInstall, err
}