be.ems/sshsvc/dborm/dborm.go

package dborm

import (
	"database/sql"
	"fmt"
	"time"

	"be.ems/lib/log"

	_ "github.com/go-sql-driver/mysql"
	"xorm.io/xorm"
	"xorm.io/xorm/core"
)

const (
	TableNameMeasureTask = "measure_task"
	TableNameNeInfo      = "ne_info"
)

type Menu struct {
	Id       int    `json:"id"`
	Title    string `json:"title"`
	Icon     string `json:"icon"`
	Href     string `json:"href"`
	ParentId int    `json:"parent_id"`
	Remark   int    `json:"remark"`
}

type DatabaseClient struct {
	dbType            string
	dbUrl             string
	dbConnMaxLifetime time.Duration
	dbMaxIdleConns    int
	dbMaxOpenConns    int
	IsShowSQL         bool

	XEngine *xorm.Engine
}

var DbClient DatabaseClient

func InitDbClient(dbType, dbUser, dbPassword, dbHost, dbPort, dbName, dbParam string) error {
	DbClient.dbUrl = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?%s",
		dbUser, dbPassword, dbHost, dbPort, dbName, dbParam)
	DbClient.dbType = dbType
	DbClient.dbConnMaxLifetime = 0
	DbClient.dbMaxIdleConns = 0
	DbClient.dbMaxOpenConns = 0
	if log.GetLevel() == log.LOG_TRACE {
		DbClient.IsShowSQL = true
	}
	log.Debugf("dbType:%s dbUrl:%s:", dbType, DbClient.dbUrl)

	var err error
	DbClient.XEngine, err = xorm.NewEngine(DbClient.dbType, DbClient.dbUrl)
	if err != nil {
		log.Error("Failed to connet database:", err)
		return err
	}
	DbClient.XEngine.SetConnMaxLifetime(DbClient.dbConnMaxLifetime)
	DbClient.XEngine.SetMaxIdleConns(DbClient.dbMaxIdleConns)
	DbClient.XEngine.SetMaxOpenConns(DbClient.dbMaxOpenConns)
	DbClient.XEngine.DatabaseTZ = time.Local // 必须
	DbClient.XEngine.TZLocation = time.Local // 必须
	if DbClient.IsShowSQL {
		DbClient.XEngine.ShowSQL(true)
	}

	xEngine = DbClient.XEngine

	return nil
}

// func InitDbClient() error {
// 	db := config.GetYamlConfig().Database
// 	DbClient.dbUrl = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", db.User, db.Password, db.Host, db.Port, db.Name)
// 	DbClient.dbType = db.Type
// 	DbClient.dbConnMaxLifetime = 0
// 	DbClient.dbMaxIdleConns = 0
// 	DbClient.dbMaxOpenConns = 0
// 	if log.GetLevel() == log.LOG_TRACE {
// 		DbClient.IsShowSQL = true
// 	}
// 	log.Debugf("dbType:%s dbUrl:%s:******@tcp(%s:%s)/%s", DbClient.dbType, db.User, db.Host, db.Port, db.Name)
// 	var err error
// 	DbClient.XEngine, err = xorm.NewEngine(DbClient.dbType, DbClient.dbUrl)
// 	if err != nil {
// 		log.Error("Failed to connet database:", err)
// 		return err
// 	}
// 	DbClient.XEngine.SetConnMaxLifetime(DbClient.dbConnMaxLifetime)
// 	DbClient.XEngine.SetMaxIdleConns(DbClient.dbMaxIdleConns)
// 	DbClient.XEngine.SetMaxOpenConns(DbClient.dbMaxOpenConns)
// 	if DbClient.IsShowSQL {
// 		DbClient.XEngine.ShowSQL(true)
// 	}
// 	xEngine = DbClient.XEngine

// 	return nil
// }

var xEngine *xorm.Engine

func XormConnectDatabase(dbType, dbUser, dbPassword, dbHost, dbPort, dbName string) (*xorm.Engine, error) {
	sqlStr := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&collation=utf8mb4_general_ci&parseTime=true&loc=Local",
		dbUser, dbPassword, dbHost, dbPort, dbName)
	log.Debugf("dbType:%s Connect to:%s:******@tcp(%s:%s)/%s?charset=utf8mb4&collation=utf8mb4_general_ci&parseTime=true&loc=Local",
		dbType, dbUser, dbHost, dbPort, dbName)
	var err error
	xEngine, err = xorm.NewEngine(dbType, sqlStr) //1、Create xorm engine
	if err != nil {
		log.Error("Failed to connect database:", err)
		return nil, err
	}
	if log.GetLevel() == log.LOG_TRACE {
		xEngine.ShowSQL(true)
	}
	return xEngine, nil
}

func XCoreDB() *core.DB {
	return xEngine.DB()
}

func XEngDB() *xorm.Engine {
	return xEngine
}

type Session struct {
	Id          int          `json:"id" xorm:"pk 'id' autoincr"`
	AccountId   string       `json:"accountId" xorm:"account_id"`
	Name        string       `json:"name" xorm:"name"`
	Host        string       `json:"host" xorm:"host"`
	AccessToken string       `json:"accessToken" xorm:"access_token"`
	Expires     uint32       `json:"expires" xorm:"expires"`
	Status      string       `json:"status" xorm:"status"`
	LoginTime   string       `json:"loginTime" xorm:"-"`
	ShakeTime   sql.NullTime `son:"shakeTime" xorm:"shake_time"`
	LogoutTime  sql.NullTime `json:"logoutTime" xorm:"logout_time"`
}

// XormUpdateSession update session
func XormLogoutUpdateSession(token string) (Session, error) {
	log.Info("XormLogoutUpdateSession processing... ")

	session := Session{Status: "offline", AccessToken: token}
	session.LogoutTime.Valid = true
	session.LogoutTime.Time = time.Now()

	xSession := xEngine.NewSession()
	defer xSession.Close()
	_, err := xSession.Table("session").Where("access_token = ?", token).Update(session)
	xSession.Commit()
	// 查询记录返回
	if err == nil {
		session := Session{}
		_, err = xSession.Table("session").Where("access_token = ?", token).Get(&session)
		return session, err
	}
	return session, err
}