gocommon/pkg/database/common_db/common.go

package common_db

import (
	_ "database/sql"
	"fmt"
	"os"
	"strconv"
	"time"

	"git.shuncheng.lu/bigthing/gocommon/pkg/conf"
	. "git.shuncheng.lu/bigthing/gocommon/pkg/internal/util"
	_ "github.com/go-sql-driver/mysql"
	"xorm.io/xorm"
	"xorm.io/xorm/log"
)

func getMysqlConfig(key string, assertNil SetAndAssertNil) (map[string]string, error) {
	var (
		mysqlConfig = make(map[string]string)
	)
	if err := assertNil(mysqlConfig, key, "dbname", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "host", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "port", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "user", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "password", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "charset", "utf8"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_dbname", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_host", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_port", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_user", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_password", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "slave_charset", "utf8"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "read_timeout", "5"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "write_timeout", "5"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "timeout", "10"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "max_idle", "10"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "max_conn", "100"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "conn_max_life_time", "-1"); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "log_level", strconv.FormatInt(int64(log.LOG_WARNING), 10)); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "log_file", ""); err != nil {
		return nil, err
	}
	if err := assertNil(mysqlConfig, key, "refresh_config_time", "30"); err != nil {
		return nil, err
	}
	//if err := assertNil(mysqlConfig, key, "ping_connection", "30"); err != nil {
	//	return nil, err
	//}
	mysqlConfig["read_timeout"] = mysqlConfig["read_timeout"] + "s"
	mysqlConfig["write_timeout"] = mysqlConfig["write_timeout"] + "s"
	mysqlConfig["timeout"] = mysqlConfig["timeout"] + "s"

	return mysqlConfig, nil
}

func fillDns(mysqlConfig map[string]string) string {
	return fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s&parseTime=True&loc=Local&readTimeout=%s&writeTimeout=%s&timeout=%s",
		mysqlConfig["user"],
		mysqlConfig["password"],
		mysqlConfig["host"],
		mysqlConfig["port"],
		mysqlConfig["dbname"],
		mysqlConfig["charset"],
		mysqlConfig["read_timeout"],
		mysqlConfig["write_timeout"],
		mysqlConfig["timeout"])
}

func fillDnsSlave(mysqlConfig map[string]string) string {
	return fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s&parseTime=True&loc=Local&readTimeout=%s&writeTimeout=%s&timeout=%s",
		mysqlConfig["slave_user"],
		mysqlConfig["slave_password"],
		mysqlConfig["slave_host"],
		mysqlConfig["slave_port"],
		mysqlConfig["slave_dbname"],
		mysqlConfig["slave_charset"],
		mysqlConfig["read_timeout"],
		mysqlConfig["write_timeout"],
		mysqlConfig["timeout"])
}

func newSqlLogFile(sqlLogFile string) (*os.File, error) {
	file, err := os.OpenFile(sqlLogFile, os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
	if err != nil {
		return nil, err
	}
	return file, err
}

func newEngine(dbName string) (*xorm.EngineGroup, map[string]string, error) {
	// 获取配置信息
	mysqlConfig, err := getMysqlConfig(dbName, conf.SetAndAssertNil)
	if err != nil {
		return nil, nil, err
	}
	//主库
	masterEngine, err := xorm.NewEngine("mysql", fillDns(mysqlConfig))
	if err != nil {
		return nil, nil, err
	}
	//从库
	slaveEngine, err := xorm.NewEngine("mysql", fillDnsSlave(mysqlConfig))
	if err != nil {
		return nil, nil, err
	}
	// 1主1从
	engineGroup, err := xorm.NewEngineGroup(masterEngine, []*xorm.Engine{slaveEngine})
	if err != nil {
		return nil, nil, err
	}
	// 最大空闲<最大连接
	engineGroup.SetMaxIdleConns(String2Int(mysqlConfig["max_idle"]))
	engineGroup.SetMaxOpenConns(String2Int(mysqlConfig["max_conn"]))
	engineGroup.SetConnMaxLifetime(time.Second * time.Duration(String2Int64(mysqlConfig["conn_max_life_time"])))
	file, err := newSqlLogFile(mysqlConfig["log_file"])
	if err != nil {
		// 异常，需要先释放资源
		nerr := engineGroup.Close()
		if nerr != nil {
			return nil, nil, nerr
		}
		return nil, nil, err
	}
	engineGroup.SetLogger(NewDbLog(log.NewSimpleLogger(file)))                                              // todo 可能存在重新reload文件没有close释放掉
	engineGroup.ShowSQL(true)                                                                               // 必须打印trace，否则会出现问题,span无法关闭，所以不把是否打印日志暴露出去了
	engineGroup.Logger().SetLevel(log.LogLevel(String2Int(mysqlConfig["log_level"], int(log.LOG_WARNING)))) // 默认打印warn级别

	Debugf("Db load %s config success, config=%+v.", dbName, mysqlConfig)
	return engineGroup, mysqlConfig, nil
}

func closeEngine(group *xorm.EngineGroup) error {
	if group == nil {
		return nil
	}
	return group.Close()
}