backend-linksee-api/utils/db.go

package utils

import (
	"fmt"

	"backend-linksee-api/models"

	"github.com/aiscrm/redisgo"
	"github.com/casbin/casbin"
	gormadapter "github.com/casbin/gorm-adapter"
	goredis "github.com/go-redis/redis"
	"github.com/jinzhu/gorm"
)

// 创建全局的mssqldb与mysqldb
var LzmssqlDb *gorm.DB

//var U8sqlserverDB *gorm.DB
var BtkLinuxMysqlDb *gorm.DB

var LzmssqlDbJdmSub *gorm.DB
var Enforcer *casbin.Enforcer
var PO *gormadapter.Adapter

func InitDatabase() bool {

	var mssqlConnErr error
	var mysqlConnErr error

	mssqlConn1 := fmt.Sprintf("server=%s;port=%d;database=%s;user id=%s;password=%s",
		"47.97.221.101", 14335, "JDMSub", "sa", "N5OUlZg0S2m8")
	LzmssqlDb, mssqlConnErr = gorm.Open("mssql", mssqlConn1)
	if mssqlConnErr != nil || LzmssqlDb == nil {
		fmt.Printf("mssql database connect failed: %s", mssqlConnErr)
	}

	mssqlConn := fmt.Sprintf("server=%s;port=%d;database=%s;user id=%s;password=%s",
		"47.97.221.101", 14335, "LZBase", "sa", "N5OUlZg0S2m8")
	LzmssqlDb, mssqlConnErr = gorm.Open("mssql", mssqlConn)
	if mssqlConnErr != nil || LzmssqlDb == nil {
		fmt.Printf("mssql database connect failed: %s", mssqlConnErr)
	}

	//u8 mssql
	//u8mssqlConn := fmt.Sprintf("server=%s;port=%d;database=%s;user id=%s;password=%s",
	//	"47.111.19.91",  3433, "UFDATA_111_2017", "sa", "T%Sy8Xb3#a45")
	//U8sqlserverDB, mssqlConnErr = gorm.Open("mssql", u8mssqlConn)
	//if mssqlConnErr != nil || LzmssqlDb == nil {
	//	fmt.Printf("mssql database connect failed: %s", mssqlConnErr)
	//}

	mysqlConn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8&parseTime=True&loc=Local",
		"root", "root811123", "47.96.113.251", 3306, "lz")
	BtkLinuxMysqlDb, mysqlConnErr = gorm.Open("mysql", mysqlConn)
	if mysqlConnErr != nil || BtkLinuxMysqlDb == nil {
		fmt.Printf("mysql database connect failed: %s", mysqlConnErr)
	}

	if mssqlConnErr != nil || mysqlConnErr != nil {
		return false
	}

	return true
}

func InitCasbinDB() {
	db, err := OpenConnection()
	if err != nil {
		fmt.Println(err.Error())
	}
	defer db.Close()
	// 将数据库连接同步给插件， 插件用来操作数据库
	PO = gormadapter.NewAdapterByDB(db)
	// 这里也可以使用原生字符串方式
	//
	Enforcer = casbin.NewEnforcer("./auth_model.conf", PO)
	// 开启权限认证日志
	Enforcer.EnableLog(true)
	// 加载数据库中的策略
	err = Enforcer.LoadPolicy()
	if err != nil {
		fmt.Println("loadPolicy error")
		panic(err)
	}
	// 创建一个角色,并赋于权限

	//获取角色可以访问的路由
	var casbinRules []gormadapter.CasbinRule
	db.Raw(`select * from casbin_rule where LENGTH(v2)>0`).Find(&casbinRules)
	for i := 0; i < len(casbinRules); i++ {
		// admin 这个角色可以访问GET 方式访问 /api/v2/ping
		fmt.Println("给角色授权可以访问的路由", casbinRules[i].V0, casbinRules[i].V1, casbinRules[i].V2)
		res := Enforcer.AddPolicy(casbinRules[i].V0, casbinRules[i].V1, casbinRules[i].V2)
		if !res {
			fmt.Println("policy is exist")
		} else {
			fmt.Println("policy is not exist, adding")
		}
	}

	// 将 test 用户加入一个角色中
	//Enforcer.AddRoleForUser("test", "root")
	//Enforcer.AddRoleForUser("tom", "admin")
	// 请看规则中如果用户名为 root 则不受限制
	//数据库中查找出对应的用户角色关联表后Enforcer

	var user_role_relations []models.UserRoleRelation
	sql := `select
    casbin_user_role_relation.role_id,
    casbin_user_role_relation.user_id,
    casbin_user.user_name,
    casbin_role.role_name  
from
    casbin_user_role_relation  
left join
    casbin_user   
        on casbin_user_role_relation.user_id = casbin_user.id  
left join
    casbin_role  
        on casbin_user_role_relation.role_id = casbin_role.role_id`
	db.Raw(sql).Find(&user_role_relations)
	for i := 0; i < len(user_role_relations); i++ {
		_ = Enforcer.AddRoleForUser(user_role_relations[i].UserName, user_role_relations[i].RoleName)
	}
}

//内网地址：r-bp1g868m1d89jycev0487.redis.rds.aliyuncs.com
//外网地址：r-bp1g868m1d89jycev0pd.redis.rds.aliyuncs.com
func GetRedisConn() (c *goredis.Client) {
	client := goredis.NewClient(&goredis.Options{
		Addr:        "r-bp1g868m1d89jycev0487.redis.rds.aliyuncs.com",
		Password:    "Meiyoumima1234",
		PoolSize:    500,
		PoolTimeout: 2000,
		IdleTimeout: 2000,
		MaxRetries:  3,
		//DB: 0,
	})
	return client
}

func GetRedisgo() (*redisgo.Cacher, error) {
	//内网地址：r-bp1g868m1d89jycev0487.redis.rds.aliyuncs.com
	//外网地址：r-bp1g868m1d89jycev0pd.redis.rds.aliyuncs.com
	c, err := redisgo.New(redisgo.Options{
		Addr:     "r-bp1g868m1d89jycev0487.redis.rds.aliyuncs.com:6379",
		Password: "Meiyoumima1234",
		Db:       0,
	})
	return c, err
}