xg_autotest/internal/dbBase/db.go

package dbBase

import (
	"database/sql"
	"fmt"
	"io"
	"log"
	"os"
	"runtime"
	"strings"
	"time"
	"unsafe"
	"xgAutoTest/internal/global"
	_ "xgAutoTest/pkg/go-driver-xugusql"
)
import "C"

var db *sql.DB

func InitDb(ip string, port string, dbBase string, user string, pwd string) {
	var err error
	dbLink := fmt.Sprintf("IP=%s;DB=%s;User=%s;"+"PWD=%s;Port=%s;AUTO_COMMIT=on;CHAR_SET=UTF8", ip, dbBase, user, pwd, port)
	db, err = sql.Open("xugusql", dbLink)
	if err != nil {

		global.Logs.Fatal("db open fail", err)
	}
	err = db.Ping()
	if err != nil {
		global.Logs.Fatal("db Ping fail", err)
	} else {
		global.Logs.Println("db Ping ok")
	}
	db.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
}

func GetDb() *sql.DB {

	return db
}

func QueryString(db *sql.DB, sql string) string {
	fmt.Println("执行查询sql: ", sql)
	var sqlResult string
	var records int
	// 记录开始时间
	start := time.Now()

	rows, err := db.Query(sql)
	if err != nil {
		if strings.Contains(sql, "exec") || strings.Contains(sql, "call") {
			sqlResult += fmt.Sprintf("SQL> %s \nConnect ok.\n%s", sql, err.Error())

			sqlResult += fmt.Sprintf("\n\nExecute successful.\n")

			sqlResult += fmt.Sprintf("Use time:1 ms.")
			fmt.Println("sqlResult: ", sqlResult)
			return sqlResult
		}

		global.Logs.Errorln("Query执行错误", err)
		sqlResult += fmt.Sprintf("SQL> %s\nError: %s", sql, err.Error())
		return sqlResult
	}
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		global.Logs.Fatal(err)
	}

	var cols []string
	cols, err = rows.Columns()
	if err != nil {
		global.Logs.Fatal(err)
	}
	pvals := make([]interface{}, len(cols))
	for key, _ := range pvals {
		dest := make([]byte, 216)
		pvals[key] = &dest
	} /* end for */

	sqlResult += fmt.Sprintf("SQL> %s", sql+"\n\n")

	for _, v := range cols {
		//fmt.Printf("%s | ", v)
		sqlResult += fmt.Sprintf("%s | ", v)
	}
	sqlResult += "\n------------------------------------------------------------------------------\n"

	records = 0
	for rows.Next() {
		err = rows.Scan(pvals...)
		if err != nil {
			global.Logs.Fatal(err)
		}
		for i, v := range pvals {
			//fmt.Println("colTypes[i].DatabaseTypeName(): ", colTypes[i].DatabaseTypeName())
			switch colTypes[i].DatabaseTypeName() {
			case "BLOB":
				if string(*(v.(*[]byte))) == "" {
					//	fmt.Printf("%s| ", "<NULL>")
					sqlResult += fmt.Sprintf("%s | ", "<NULL>")
				} else {
					//	fmt.Printf("%s | ", string(*(v.(*[]byte))))
					sqlResult += fmt.Sprintf("%s | ", "<BLOB>")
				}

			default:
				if string(*(v.(*[]byte))) == "" {
					//	fmt.Printf("%s| ", "<NULL>")
					sqlResult += fmt.Sprintf("%s | ", "<NULL>")
				} else {
					//	fmt.Printf("%s | ", string(*(v.(*[]byte))))
					sqlResult += fmt.Sprintf("%s | ", string(*(v.(*[]byte))))
				}

			}

		}
		records++
		//fmt.Printf("\n")
		sqlResult += "\n"
	}

	//fmt.Println("--------------------------------")

	// 记录结束时间
	end := time.Now()

	// 计算操作耗时
	duration := end.Sub(start)
	durationInt := int64(duration.Milliseconds())
	if durationInt == 0 {
		durationInt = 1
	}
	sqlResult += fmt.Sprintf("\nTotal %d records.\n", records)

	sqlResult += fmt.Sprintf("\nUse time:%d ms.", durationInt)
	//fmt.Println("sqlResult:", sqlResult)
	rows.Close()
	return sqlResult
}

func ExecString(db *sql.DB, sql string) string {
	fmt.Println("执行插入sql: ", sql)

	var sqlResult string
	start := time.Now()
	result, err := db.Exec(sql)
	if err != nil {
		global.Logs.Errorln("Exe执行错误", err)
		sqlResult += fmt.Sprintf("SQL> %s\nError: %s", sql, err.Error())
		return sqlResult
	}
	// 记录结束时间
	end := time.Now()

	// 计算操作耗时
	duration := end.Sub(start)
	sqlResult += fmt.Sprintf("SQL> %s", sql+"\n\n")
	// 获取影响的行数
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		global.Logs.Fatal(err)

	}
	//fmt.Printf("Rows affected: %d\n", rowsAffected)

	durationInt := int64(duration.Milliseconds())
	if durationInt == 0 {
		durationInt = 1
	}

	if strings.Contains(sql, "grant") {
		sqlResult += "Execute successful."
		sqlResult += fmt.Sprintf("\nUse time:%d ms.", durationInt)
		return sqlResult
	} else if strings.Contains(sql, "insert") {
		sqlResult += fmt.Sprintf("Total %d records effected.\n", rowsAffected)
		sqlResult += fmt.Sprintf("\nUse time:%d ms.", durationInt)
		return sqlResult
	} else {
		sqlResult += "Execute successful."
		sqlResult += fmt.Sprintf("\nUse time:%d ms.", durationInt)
		return sqlResult
	}

	//fmt.Println("sqlResult", sqlResult)

	return sqlResult
}

func ExecFiles(db *sql.DB, sql string, fileLocal []string, partLine string) string {

	var sqlResult string
	fmt.Println("fileLocal的数量: ", len(fileLocal))
	var args []interface{}
	for _, v := range fileLocal {
		fmt.Println("文件地址:", v)
		// 读取图像文件
		v := strings.ReplaceAll(v, " ", "")
		fmt.Println("文件地址:", v)
		imageData, err := os.ReadFile(v)
		if err != nil {
			global.Logs.Fatal(err)
		}

		args = append(args, &imageData)
	}

	fmt.Println("sql查询:", sql)
	//ret := "insert into t4 values(?,?,2);"

	runtime.KeepAlive(sql)
	ptr := unsafe.Pointer(&sql)

	runtime.KeepAlive(ptr)
	stmt, err := db.Prepare(sql)
	if err != nil {
		global.Logs.Fatal(err)
	}

	result, err := stmt.Exec(args...)
	if err != nil {

		global.Logs.Errorln("Exe执行错误", err)
		sqlResult += fmt.Sprintf("SQL> %s\nError: %s", partLine, err.Error())
		return sqlResult

	}

	// 获取影响的行数
	_, err = result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	sqlResult += fmt.Sprintf("SQL> %s", partLine+"\n\n")
	//sqlResult += fmt.Sprintf("Total %d records effected.", rowsAffected)
	sqlResult += fmt.Sprintf("Use time:1 ms.")
	return sqlResult
}

func ExecClob(db *sql.DB, sql string, fileLocal []string, partLine string) string {
	var sqlResult string
	fmt.Println("fileLocal的数量: ", len(fileLocal))
	var args []interface{}
	for _, v := range fileLocal {
		// 打开文件
		file, err := os.Open(v)
		if err != nil {
			log.Fatal(err)
		}
		defer file.Close()
		// 读取文件内容
		content, err := io.ReadAll(file)
		if err != nil {
			log.Fatal(err)
		}
		// 将内容转换为字符串
		fileContent := string(content)

		args = append(args, &fileContent)
	}

	fmt.Println("sql查询:", sql)
	//ret := "insert into t4 values(?,?,2);"

	stmt, err := db.Prepare(sql)
	if err != nil {
		global.Logs.Fatal(err)
	}

	result, err := stmt.Exec(args...)
	if err != nil {
		if err != nil {
			global.Logs.Errorln("Exe执行错误", err)
			sqlResult += fmt.Sprintf("SQL> %s\nError: %s", partLine, err.Error())
			return sqlResult
		}

	}

	// 获取影响的行数
	_, err = result.RowsAffected()
	if err != nil {
		log.Fatal(err)
	}
	sqlResult += fmt.Sprintf("SQL> %s", partLine+"\n\n")
	//sqlResult += fmt.Sprintf("Total %d records effected.", rowsAffected)
	sqlResult += fmt.Sprintf("Use time:1 ms.")
	return sqlResult
}

func QueryUserTableName(db *sql.DB, sql string) []string {
	rows, err := db.Query(sql)
	if err != nil {
		global.Logs.Errorln("Query执行错误", err)
		return nil
	}
	_, err = rows.ColumnTypes()
	if err != nil {
		global.Logs.Fatal(err)
	}

	var cols []string
	cols, err = rows.Columns()
	if err != nil {
		global.Logs.Fatal(err)
	}
	pvals := make([]interface{}, len(cols))
	for key, _ := range pvals {
		dest := make([]byte, 216)
		pvals[key] = &dest
	} /* end for */
	var tableNames []string
	for rows.Next() {
		err = rows.Scan(pvals...)
		if err != nil {
			global.Logs.Fatal(err)
		}
		for _, v := range pvals {

			tableNames = append(tableNames, string(*(v.(*[]byte))))

		}
	}
	return tableNames
}

func ExecDorpTable(db *sql.DB, tableNames []string) {

	_, err := db.Exec("COMMIT;")
	if err != nil {
		global.Logs.Errorln("Exe执行错误", err)
		return
	}
	for _, v := range tableNames {
		sql := fmt.Sprintf("drop table %s;", v)
		_, err := db.Exec(sql)
		if err != nil {
			global.Logs.Errorln("Exe执行错误", err)
			return
		}
	}

}