videoplayer/dao/dbm.go

package dao

import (
	"gorm.io/gorm"
	"videoplayer/proto"
)

func CreateDBManage(db_info proto.DBManage) (uint, error) {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Create(&db_info)
	if res.Error != nil {
		return 0, res.Error
	}
	return db_info.ID, nil
}

func CreateDBRunHistory(history *proto.SQLRunHistory) (uint, error) {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Create(history)
	if res.Error != nil {
		return 0, res.Error
	}
	return history.ID, nil
}

func RunSQL(sql string, db_ *gorm.DB) (res []map[string]interface{}, err error) {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = db_.Debug()
	} else {
		db2 = db_
	}
	err = db2.Raw(sql).Scan(&res).Error
	return res, err
}

func FindDBManageByID(id uint) (proto.DBManage, error) {
	var db_info proto.DBManage
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("id = ?", id).First(&db_info)
	if res.Error != nil {
		return proto.DBManage{}, res.Error
	}
	return db_info, nil
}

func FindDBManageByAuthID(auth_id uint) ([]proto.DBManage, error) {
	var db_infos []proto.DBManage
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("user_id = ?", auth_id).Find(&db_infos)
	if res.Error != nil {
		return nil, res.Error
	}
	return db_infos, nil
}

func FindAllDBManage() ([]proto.DBManage, error) {
	var db_infos []proto.DBManage
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Find(&db_infos)
	if res.Error != nil {
		return nil, res.Error
	}
	return db_infos, nil
}

func UpdateDBManage(id uint, db_info *proto.DBManage) error {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Model(&proto.DBManage{}).Where("id = ?", id).Updates(db_info)
	return res.Error
}

func DeleteDBManageByID(id uint) error {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("id = ?", id).Delete(&proto.DBManage{})
	return res.Error
}

func DeleteDBManageByUserID(id uint) error {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("user_id = ?", id).Delete(&proto.DBManage{})
	return res.Error
}

func FindDBRunHistoryByID(id uint) (proto.SQLRunHistory, error) {
	var history proto.SQLRunHistory
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("id = ?", id).First(&history)
	if res.Error != nil {
		return proto.SQLRunHistory{}, res.Error
	}
	return history, nil
}
func FindDBRunHistoryByAuthID(auth_id int) ([]proto.SQLRunHistory, error) {
	var histories []proto.SQLRunHistory
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("user_id = ?", auth_id).Find(&histories)
	if res.Error != nil {
		return nil, res.Error
	}
	return histories, nil
}

func FindDBRunHistoryByAuthIDAndDbId(auth_id int, db_id uint) ([]proto.SQLRunHistory, error) {
	var histories []proto.SQLRunHistory
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("user_id = ? and db_id = ?", auth_id, db_id).Find(&histories)
	if res.Error != nil {
		return nil, res.Error
	}
	return histories, nil
}

func DelSQLRunHistoryByID(id uint) error {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("id = ?", id).Delete(&proto.SQLRunHistory{})
	return res.Error
}

func DelSQLRunHistoryByAuthID(auth_id int) error {
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Where("user_id = ?", auth_id).Delete(&proto.SQLRunHistory{})
	return res.Error
}

func FindAllSQLRunHistory() ([]proto.SQLRunHistory, error) {
	var histories []proto.SQLRunHistory
	var db2 *gorm.DB
	if proto.Config.SERVER_SQL_LOG {
		db2 = DB.Debug()
	} else {
		db2 = DB
	}
	res := db2.Find(&histories)
	if res.Error != nil {
		return nil, res.Error
	}
	return histories, nil
}

func RunSQLWithOrder(sql string, db_ *gorm.DB) (result proto.SQLResult, err error) {
	var db2 *gorm.DB
	// 保留 Debug 模式
	if proto.Config.SERVER_SQL_LOG {
		db2 = db_.Debug()
	} else {
		db2 = db_
	}

	// 执行 SQL 并获取底层 Rows 对象
	rows, err := db2.Raw(sql).Rows()
	if err != nil {
		return result, err
	}
	defer rows.Close() // 确保关闭 Rows

	// 获取列名顺序（关键：这里的顺序与 SQL 查询的列顺序一致）
	columns, err := rows.Columns()
	if err != nil {
		return result, err
	}
	for _, col := range columns {
		result.Columns = append(result.Columns, proto.SQLResultColumnsValue{Prop: col, Label: col})
	}

	// 遍历每行数据
	for rows.Next() {
		// 准备接收每行数据的容器（按列顺序）
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns)) // 用于 Scan 的指针切片

		// 为每个列绑定指针（Scan 要求传入指针）
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		// 扫描当前行数据到指针切片
		if err2 := rows.Scan(valuePtrs...); err2 != nil {
			return result, err2
		}

		// 将当前行数据存入 map（便于按列名访问）
		rowMap := make(map[string]interface{})
		for i, col := range columns {
			rowMap[col] = values[i]
		}
		result.Rows = append(result.Rows, rowMap)
	}

	// 检查遍历过程中是否有错误
	if err = rows.Err(); err != nil {
		return result, err
	}

	return result, nil
}