485 lines
12 KiB
Go
485 lines
12 KiB
Go
package db_test
|
|
|
|
import (
|
|
"fmt"
|
|
"os"
|
|
"regexp"
|
|
"strings"
|
|
"testing"
|
|
"time"
|
|
|
|
"apigo.cc/go/cast"
|
|
"apigo.cc/go/db"
|
|
"apigo.cc/go/shell"
|
|
|
|
_ "apigo.cc/go/db/mysql"
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
func TestMain(m *testing.M) {
|
|
code := m.Run()
|
|
os.Remove("test.db")
|
|
os.Exit(code)
|
|
}
|
|
|
|
var dbset = "sqlite://test.db"
|
|
|
|
type userInfo struct {
|
|
innerId int
|
|
Tag string
|
|
Id int
|
|
Name string
|
|
Phone *string
|
|
Email string
|
|
Parents []string
|
|
Active bool
|
|
Time string
|
|
}
|
|
|
|
type UserBaseModel struct {
|
|
Id int
|
|
Name string
|
|
Password string
|
|
}
|
|
|
|
type UserModel struct {
|
|
UserBaseModel
|
|
Phone string
|
|
Active bool
|
|
Parents []string
|
|
UserStatus int
|
|
Owner int
|
|
Salt string
|
|
}
|
|
|
|
func initDB(t *testing.T) *db.DB {
|
|
var er *db.ExecResult
|
|
dbInst := db.GetDB(dbset, nil)
|
|
fmt.Println("dbType", shell.BCyan(dbInst.Config.Type))
|
|
if dbInst.Error != nil {
|
|
t.Fatal("GetDB error", dbInst)
|
|
return nil
|
|
}
|
|
|
|
finishDB(dbInst, t)
|
|
if dbInst.Config.Type == "mysql" {
|
|
er = dbInst.Exec(`CREATE TABLE IF NOT EXISTS tempUsersForDBTest (
|
|
id INT NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(45) NOT NULL,
|
|
phone VARCHAR(45),
|
|
email VARCHAR(45),
|
|
parents JSON,
|
|
active TINYINT NOT NULL DEFAULT 0,
|
|
time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (id));`)
|
|
} else {
|
|
er = dbInst.Exec(`CREATE TABLE IF NOT EXISTS tempUsersForDBTest (
|
|
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
name VARCHAR(45) NOT NULL,
|
|
phone VARCHAR(45),
|
|
email VARCHAR(45),
|
|
parents JSON,
|
|
active TINYINT NOT NULL DEFAULT 0,
|
|
time DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')));`)
|
|
}
|
|
if er.Error != nil {
|
|
t.Fatal("Failed to create table", er)
|
|
}
|
|
return dbInst
|
|
}
|
|
|
|
func finishDB(dbInst *db.DB, t *testing.T) {
|
|
er := dbInst.Exec(`DROP TABLE IF EXISTS tempUsersForDBTest;`)
|
|
if er.Error != nil {
|
|
t.Fatal("Failed to drop table", er)
|
|
}
|
|
}
|
|
|
|
func TestMakeInsertSql(t *testing.T) {
|
|
user := &UserModel{
|
|
UserBaseModel: UserBaseModel{
|
|
Name: "王二小",
|
|
Password: "2121asds",
|
|
},
|
|
Parents: []string{"aa", "bb"},
|
|
Active: true,
|
|
UserStatus: 1,
|
|
Salt: "de312",
|
|
}
|
|
|
|
dbInst := db.GetDB(dbset, nil)
|
|
query, _ := dbInst.MakeInsertSql("table_name", user, false)
|
|
checkSql := `insert into "table_name" ("Id","Name","Password","Phone","Active","Parents","UserStatus","Owner","Salt") values (?,?,?,?,?,?,?,?,?)`
|
|
if dbInst.Config.Type == "mysql" {
|
|
checkSql = strings.ReplaceAll(checkSql, "\"", "`")
|
|
}
|
|
if query != checkSql {
|
|
t.Fatal("MakeInsertSql query error ", query)
|
|
}
|
|
}
|
|
|
|
func TestBaseSelect(t *testing.T) {
|
|
sqlStr := "SELECT 1002 id, '13800000001' phone"
|
|
dbInst := db.GetDB(dbset, nil)
|
|
if dbInst.Error != nil {
|
|
t.Fatal("GetDB error", dbInst.Error)
|
|
return
|
|
}
|
|
|
|
r := dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, r)
|
|
}
|
|
results1 := r.MapResults()
|
|
if cast.Int(results1[0]["id"]) != 1002 || cast.String(results1[0]["phone"]) != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results1, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, r)
|
|
}
|
|
results2 := r.StringMapResults()
|
|
if results2[0]["id"] != "1002" || results2[0]["phone"] != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results2, r)
|
|
}
|
|
|
|
results3 := make([]map[string]int, 0)
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, results3, r)
|
|
}
|
|
r.To(&results3)
|
|
if results3[0]["id"] != 1002 || results3[0]["phone"] != 13800000001 {
|
|
t.Fatal("Result error", sqlStr, results3, r)
|
|
}
|
|
|
|
results4 := make([]userInfo, 0)
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, results4, r)
|
|
}
|
|
r.To(&results4)
|
|
if results4[0].Id != 1002 || results4[0].Phone == nil || *results4[0].Phone != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results4, r)
|
|
}
|
|
|
|
results5 := dbInst.Query(sqlStr).StringSliceResults()
|
|
if results5[0][0] != "1002" || results5[0][1] != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results5, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, r)
|
|
}
|
|
results6 := r.StringsOnC1()
|
|
if results6[0] != "1002" {
|
|
t.Fatal("Result error", sqlStr, results6, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, r)
|
|
}
|
|
results7 := r.MapOnR1()
|
|
if cast.Int(results7["id"]) != 1002 || cast.String(results7["phone"]) != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results7, r)
|
|
}
|
|
|
|
results8 := userInfo{innerId: 2, Tag: "abc"}
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, results8, r)
|
|
}
|
|
r.To(&results8)
|
|
if results8.Id != 1002 || results8.Phone == nil || *results8.Phone != "13800000001" || results8.innerId != 2 || results8.Tag != "abc" {
|
|
t.Fatal("Result error", sqlStr, results8, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
t.Fatal("Query error", sqlStr, r)
|
|
}
|
|
results9 := r.IntOnR1C1()
|
|
if results9 != 1002 {
|
|
t.Fatal("Result error", sqlStr, results9, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
results10 := map[string]string{}
|
|
r.ToKV(&results10)
|
|
if results10["1002"] != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results10, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
results11 := map[string]map[string]string{}
|
|
r.ToKV(&results11)
|
|
if results11["1002"]["phone"] != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results11, r)
|
|
}
|
|
|
|
r = dbInst.Query(sqlStr)
|
|
results12 := map[string]userInfo{}
|
|
r.ToKV(&results12)
|
|
if results12["1002"].Phone == nil || *results12["1002"].Phone != "13800000001" {
|
|
t.Fatal("Result error", sqlStr, results12, r)
|
|
}
|
|
}
|
|
|
|
func TestInsertReplaceUpdateDelete(t *testing.T) {
|
|
dbInst := initDB(t)
|
|
data := map[string]any{
|
|
"phone": 18033336666,
|
|
"name": "Star",
|
|
"parents": []string{"dd", "mm"},
|
|
"time": ":(strftime('%Y-%m-%d %H:%M:%f', datetime('now', '-1 day'), 'localtime'))",
|
|
}
|
|
if dbInst.Config.Type == "mysql" {
|
|
data["time"] = ":DATE_SUB(NOW(), INTERVAL 1 DAY)"
|
|
}
|
|
er := dbInst.Insert("tempUsersForDBTest", data)
|
|
if er.Error != nil {
|
|
t.Fatal("Insert 1 error", er)
|
|
}
|
|
if er.Id() != 1 {
|
|
t.Fatal("insertId 1 error", er, er.Id())
|
|
}
|
|
|
|
er = dbInst.Insert("tempUsersForDBTest", map[string]any{
|
|
"phone": "18000000002",
|
|
"name": "Tom",
|
|
"active": true,
|
|
})
|
|
if er.Error != nil {
|
|
t.Fatal("Insert 2 error", er)
|
|
}
|
|
if er.Id() != 2 {
|
|
t.Fatal("insertId 2 error", er, er.Id())
|
|
}
|
|
|
|
er = dbInst.Update("tempUsersForDBTest", map[string]any{
|
|
"phone": "18000000222",
|
|
"name": "Tom Lee",
|
|
}, "id=?", 2)
|
|
if er.Error != nil {
|
|
t.Fatal("Update 2 error", er)
|
|
}
|
|
if er.Changes() != 1 {
|
|
t.Fatal("Update 2 num error", er, er.Changes())
|
|
}
|
|
|
|
er = dbInst.Replace("tempUsersForDBTest", map[string]any{
|
|
"phone": "18000000003",
|
|
"name": "Amy",
|
|
})
|
|
if er.Error != nil {
|
|
t.Fatal("Replace 3 error", er)
|
|
}
|
|
if er.Id() != 3 {
|
|
t.Fatal("insertId 3 error", er, er.Changes())
|
|
}
|
|
|
|
er = dbInst.Exec("delete from tempUsersForDBTest where id=3")
|
|
if er.Error != nil {
|
|
t.Fatal("Delete 3 error", er)
|
|
}
|
|
if er.Changes() != 1 {
|
|
t.Fatal("Delete 3 num error", er)
|
|
}
|
|
|
|
er = dbInst.Replace("tempUsersForDBTest", map[string]any{
|
|
"phone": "18000000004",
|
|
"name": "Jerry",
|
|
})
|
|
if er.Error != nil {
|
|
t.Fatal("Replace 4 error", er)
|
|
}
|
|
if er.Id() != 4 {
|
|
t.Fatal("insertId 4 error", er, er.Changes())
|
|
}
|
|
|
|
stmt := dbInst.Prepare("replace into `tempUsersForDBTest` (`id`,`phone`,`name`) values (?,?,?)")
|
|
if stmt.Error != nil {
|
|
t.Fatal("Prepare 4 error", stmt)
|
|
}
|
|
er = stmt.Exec(4, "18000000004", "Jerry's Mather")
|
|
stmt.Close()
|
|
|
|
if er.Error != nil {
|
|
t.Fatal("Replace 4 error", er)
|
|
}
|
|
if er.Id() != 4 {
|
|
t.Fatal("insertId 4 error", er)
|
|
}
|
|
|
|
userList := make([]userInfo, 0)
|
|
r := dbInst.Query("select * from tempUsersForDBTest")
|
|
if r.Error != nil {
|
|
t.Fatal("Select userList error", r)
|
|
}
|
|
r.To(&userList)
|
|
fmt.Println(">>>>", cast.PrettyToJSON(userList))
|
|
if strings.Split(userList[0].Time, " ")[0] != time.Now().Add(time.Hour*24*-1).Format("2006-01-02") || userList[0].Id != 1 || userList[0].Name != "Star" || userList[0].Phone == nil || *userList[0].Phone != "18033336666" || userList[0].Active != false {
|
|
t.Fatal("Select userList 0 error", userList, r)
|
|
}
|
|
if len(userList[0].Parents) != 2 || userList[0].Parents[0] != "dd" {
|
|
t.Fatal("Select userList 0 Parents error", userList, r)
|
|
}
|
|
if strings.Split(userList[1].Time, " ")[0] != time.Now().Format("2006-01-02") || userList[1].Id != 2 || userList[1].Name != "Tom Lee" || userList[1].Phone == nil || *userList[1].Phone != "18000000222" || userList[1].Active != true {
|
|
t.Fatal("Select userList 1 error", userList, r)
|
|
}
|
|
if userList[2].Id != 4 || userList[2].Name != "Jerry's Mather" || userList[2].Phone == nil || *userList[2].Phone != "18000000004" {
|
|
t.Fatal("Select userList 2 error", userList, r)
|
|
}
|
|
|
|
finishDB(dbInst, t)
|
|
}
|
|
|
|
func TestTransaction(t *testing.T) {
|
|
n1 := countConnection()
|
|
|
|
var userList []userInfo
|
|
|
|
dbInst := initDB(t)
|
|
tx := dbInst.Begin()
|
|
if tx.Error != nil {
|
|
t.Fatal("Begin error", tx)
|
|
}
|
|
|
|
data := map[string]any{
|
|
"phone": 18033336666,
|
|
"name": "Star",
|
|
"time": ":(strftime('%Y-%m-%d %H:%M:%f', datetime('now', '-1 day'), 'localtime'))",
|
|
}
|
|
if dbInst.Config.Type == "mysql" {
|
|
data["time"] = ":DATE_SUB(NOW(), INTERVAL 1 DAY)"
|
|
}
|
|
tx.Insert("tempUsersForDBTest", data)
|
|
|
|
userList = make([]userInfo, 0)
|
|
r := dbInst.Query("select * from tempUsersForDBTest")
|
|
r.To(&userList)
|
|
if r.Error != nil || len(userList) != 0 {
|
|
t.Fatal("Select Out Of TX", userList, r)
|
|
}
|
|
|
|
userList = make([]userInfo, 0)
|
|
r = tx.Query("select * from tempUsersForDBTest")
|
|
r.To(&userList)
|
|
if r.Error != nil || len(userList) != 1 {
|
|
t.Fatal("Select In TX", userList, r)
|
|
}
|
|
|
|
tx.Rollback()
|
|
|
|
userList = make([]userInfo, 0)
|
|
r = dbInst.Query("select * from tempUsersForDBTest")
|
|
r.To(&userList)
|
|
if r.Error != nil || len(userList) != 0 {
|
|
t.Fatal("Select When Rollback", userList, r)
|
|
}
|
|
|
|
tx = dbInst.Begin()
|
|
defer func() {
|
|
if err := tx.Finish(false); err != nil {
|
|
t.Error("tx rollback error", err)
|
|
}
|
|
finishDB(dbInst, t)
|
|
}()
|
|
if tx.Error != nil {
|
|
t.Fatal("Begin 2 error", tx)
|
|
}
|
|
|
|
stmt := tx.Prepare("insert into `tempUsersForDBTest` (`id`,`phone`,`name`) values (?,?,?)")
|
|
if stmt.Error != nil {
|
|
t.Fatal("Prepare 4 error", r)
|
|
}
|
|
stmt.Exec(4, "18000000004", "Jerry's Mather")
|
|
stmt.Close()
|
|
|
|
tx.Commit()
|
|
|
|
userList = make([]userInfo, 0)
|
|
r = dbInst.Query("select * from tempUsersForDBTest")
|
|
r.To(&userList)
|
|
if r.Error != nil || len(userList) != 1 {
|
|
t.Fatal("Select When Commit", userList, r)
|
|
}
|
|
|
|
n2 := countConnection()
|
|
fmt.Println("# connection count", n1, n2, cast.MustToJSON(dbInst.GetOriginDB().Stats()), ".")
|
|
}
|
|
|
|
func countConnection() int {
|
|
n := 0
|
|
res, _ := shell.RunCommand("netstat -ant", nil)
|
|
lines := strings.Split(string(res.Stdout), "\n")
|
|
spliter := regexp.MustCompile("\\s+")
|
|
for _, line := range lines {
|
|
if strings.Contains(line, ".3306") && strings.Contains(line, "ESTABLISHED") {
|
|
a := spliter.Split(line, 10)
|
|
if len(a) > 4 && strings.Contains(a[4], ".3306") {
|
|
n++
|
|
}
|
|
}
|
|
}
|
|
return n
|
|
}
|
|
|
|
func BenchmarkForPool(b *testing.B) {
|
|
b.StopTimer()
|
|
sqlStr := "SELECT 1002 id, '13800000001' phone"
|
|
dbInst := db.GetDB(dbset, nil)
|
|
if dbInst.Error != nil {
|
|
b.Fatal("GetDB error", dbInst)
|
|
return
|
|
}
|
|
|
|
b.StartTimer()
|
|
for i := 0; i < b.N; i++ {
|
|
results1 := make([]map[string]any, 0)
|
|
r := dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
b.Fatal("Query error", sqlStr, results1, r)
|
|
}
|
|
r.To(&results1)
|
|
if cast.Int(results1[0]["id"]) != 1002 || cast.String(results1[0]["phone"]) != "13800000001" {
|
|
b.Fatal("Result error", sqlStr, results1, r)
|
|
}
|
|
}
|
|
b.Log("OpenConnections", dbInst.GetOriginDB().Stats().OpenConnections)
|
|
}
|
|
|
|
func BenchmarkForPoolParallel(b *testing.B) {
|
|
n1 := countConnection()
|
|
|
|
b.StopTimer()
|
|
sqlStr := "SELECT 1002 id, '13800000001' phone"
|
|
dbInst := db.GetDB(dbset, nil)
|
|
if dbInst.Error != nil {
|
|
b.Fatal("GetDB error", dbInst)
|
|
return
|
|
}
|
|
b.StartTimer()
|
|
|
|
b.RunParallel(func(pb *testing.PB) {
|
|
for pb.Next() {
|
|
results1 := make([]map[string]any, 0)
|
|
r := dbInst.Query(sqlStr)
|
|
if r.Error != nil {
|
|
b.Fatal("Query error", sqlStr, results1, r)
|
|
}
|
|
r.To(&results1)
|
|
if cast.Int(results1[0]["id"]) != 1002 || cast.String(results1[0]["phone"]) != "13800000001" {
|
|
b.Fatal("Result error", sqlStr, results1, r)
|
|
}
|
|
}
|
|
})
|
|
b.Log("OpenConnections", dbInst.GetOriginDB().Stats().OpenConnections)
|
|
|
|
n2 := countConnection()
|
|
fmt.Println("# connection count", n1, n2, cast.MustToJSON(dbInst.GetOriginDB().Stats()), ".")
|
|
}
|