TsDB2/demo/full_test/main.go

package main

import (
	"TsDB2/DB"
	"TsDB2/DB/core/page"
	"fmt"
	"os"
	"time"
)

// ========== 数据模型 ==========
type Employee struct {
	DB.BaseModel
	Name  string `tsdb:"32byte"`
	Age   int64  `tsdb:"8byte"`
	Score int64  `tsdb:"8byte"`
	Dept  string `tsdb:"16byte"`
}

type Order struct {
	DB.BaseModel
	EmployeeID int64 `tsdb:"8byte"`
	Amount     int64 `tsdb:"8byte"`
	Status     int64 `tsdb:"8byte"`
}

const dbPath = "fulltest.db"
const N = 100_000

func main() {
	os.Remove(dbPath)
	passed := 0
	failed := 0

	check := func(name string, ok bool, detail string) {
		if ok {
			passed++
			fmt.Printf("  ✅ %s\n", name)
		} else {
			failed++
			fmt.Printf("  ❌ %s — %s\n", name, detail)
		}
	}

	// =============================================
	fmt.Println("========== 1. 打开数据库 + 建表 ==========")
	// =============================================
	db, err := DB.Open(dbPath)
	if err != nil {
		panic(err)
	}
	// 禁用自动提交, 手动控制 Commit 时机 (百万行插入时避免中间 Commit)
	db.SetAutoCommitDelay(0)

	empTbl, err := DB.UseTable[Employee](db, "employee")
	if err != nil {
		panic(err)
	}
	// 创建二级索引: age (B+树)
	empCore, _ := db.CoreDB().FindTable("employee")
	empCore.CreateIndex("idx_age", page.IndexTypeBTree, []byte{2})  // field index 2 = Age
	empCore.CreateIndex("idx_dept", page.IndexTypeBTree, []byte{4}) // field index 4 = Dept

	orderTbl, err := DB.UseTable[Order](db, "order")
	if err != nil {
		panic(err)
	}
	orderCore, _ := db.CoreDB().FindTable("order")
	orderCore.CreateIndex("idx_status", page.IndexTypeBTree, []byte{3}) // Status

	fmt.Println("  表 employee: ID + Name(32byte) + Age(8byte) + Score(8byte) + Dept(16byte)")
	fmt.Println("  索引: pk_employee, idx_age, idx_dept")
	fmt.Println("  表 order: ID + EmployeeID(8byte) + Amount(8byte) + Status(8byte)")
	fmt.Println("  索引: pk_order, idx_status")

	// =============================================
	fmt.Println("\n========== 2. 批量插入 100万行 ==========")
	// =============================================
	depts := []string{"Eng", "Sales", "HR", "Fin", "Ops"}
	t0 := time.Now()
	for i := 1; i <= N; i++ {
		err := empTbl.Insert(&Employee{
			Name:  fmt.Sprintf("Emp%07d", i),
			Age:   int64(20 + (i % 50)),
			Score: int64(i % 1000),
			Dept:  depts[i%5],
		})
		if err != nil {
			panic(fmt.Sprintf("insert row %d: %v", i, err))
		}
		if i%20000 == 0 {
			fmt.Printf("  已插入 %d 行 (%.1fs)\n", i, time.Since(t0).Seconds())
		}
	}
	// 手动 Commit 所有数据
	fmt.Println("  插入完成, 开始 Commit...")
	t1 := time.Now()
	if err := db.Commit(); err != nil {
		panic(err)
	}
	insertTime := time.Since(t0)
	fmt.Printf("  ✅ 插入 %d 行 + Commit 完成, 耗时 %.1fs (Commit %.1fs)\n", N, insertTime.Seconds(), time.Since(t1).Seconds())

	// =============================================
	fmt.Println("\n========== 3. 主键查询 ==========")
	// =============================================
	// 精确查找
	row, err := empTbl.Find(1)
	check("PK 精确查找 id=1", err == nil && row.Name == "Emp0000001",
		fmt.Sprintf("err=%v name=%s", err, row.Name))

	row, err = empTbl.Find(uint64(N / 2))
	rn := ""
	if row != nil {
		rn = row.Name
	}
	check(fmt.Sprintf("PK 精确查找 id=%d", N/2), err == nil && row != nil && row.Name == fmt.Sprintf("Emp%07d", N/2),
		fmt.Sprintf("err=%v name=%s", err, rn))

	row, err = empTbl.Find(uint64(N))
	rn = ""
	if row != nil {
		rn = row.Name
	}
	check(fmt.Sprintf("PK 精确查找 id=%d", N), err == nil && row != nil && row.Name == fmt.Sprintf("Emp%07d", N),
		fmt.Sprintf("err=%v name=%s", err, rn))

	// =============================================
	fmt.Println("\n========== 4. 条件查询 ==========")
	// =============================================
	// WHERE age = 25
	t2 := time.Now()
	rows, err := empTbl.Where("Age", "=", int64(25)).Find()
	check("WHERE Age=25", err == nil && len(rows) > 0,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	fmt.Printf("    返回 %d 行, 耗时 %.3fs\n", len(rows), time.Since(t2).Seconds())

	// WHERE age > 60
	t2 = time.Now()
	rows, err = empTbl.Where("Age", ">", int64(60)).Find()
	check("WHERE Age>60", err == nil && len(rows) > 0,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	fmt.Printf("    返回 %d 行, 耗时 %.3fs\n", len(rows), time.Since(t2).Seconds())

	// WHERE age >= 20 AND age <= 25
	t2 = time.Now()
	rows, err = empTbl.Where("Age", ">=", int64(20)).Where("Age", "<=", int64(25)).Find()
	check("WHERE Age>=20 AND Age<=25", err == nil && len(rows) > 0,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	fmt.Printf("    返回 %d 行, 耗时 %.3fs\n", len(rows), time.Since(t2).Seconds())

	// WHERE score = 999
	t2 = time.Now()
	rows, err = empTbl.Where("Score", "=", int64(999)).Find()
	check("WHERE Score=999 (全表扫描)", err == nil && len(rows) > 0,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	fmt.Printf("    返回 %d 行, 耗时 %.3fs\n", len(rows), time.Since(t2).Seconds())

	// =============================================
	fmt.Println("\n========== 5. 排序 + 分页 ==========")
	// =============================================
	// ORDER BY Age ASC LIMIT 10
	t2 = time.Now()
	rows, err = empTbl.OrderBy("Age").Limit(10).Find()
	check("ORDER BY Age ASC LIMIT 10", err == nil && len(rows) == 10,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	if len(rows) >= 2 {
		check("  排序正确 (前2行 Age 递增)", rows[0].Age <= rows[1].Age,
			fmt.Sprintf("age[0]=%d age[1]=%d", rows[0].Age, rows[1].Age))
	}
	fmt.Printf("    耗时 %.3fs\n", time.Since(t2).Seconds())

	// ORDER BY Age DESC LIMIT 5
	rows, err = empTbl.OrderBy("Age", true).Limit(5).Find()
	check("ORDER BY Age DESC LIMIT 5", err == nil && len(rows) == 5,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))
	if len(rows) >= 2 {
		check("  排序正确 (前2行 Age 递减)", rows[0].Age >= rows[1].Age,
			fmt.Sprintf("age[0]=%d age[1]=%d", rows[0].Age, rows[1].Age))
	}

	// OFFSET + LIMIT
	rows, err = empTbl.OrderBy("Age").Offset(100).Limit(5).Find()
	check("ORDER BY Age OFFSET 100 LIMIT 5", err == nil && len(rows) == 5,
		fmt.Sprintf("err=%v count=%d", err, len(rows)))

	// =============================================
	fmt.Println("\n========== 6. 聚合查询 ==========")
	// =============================================
	t2 = time.Now()
	cnt, err := empTbl.Count()
	check("COUNT(*)", err == nil && cnt == int64(N),
		fmt.Sprintf("err=%v count=%d", err, cnt))
	fmt.Printf("    COUNT=%d, 耗时 %.3fs\n", cnt, time.Since(t2).Seconds())

	// =============================================
	fmt.Println("\n========== 7. 第二张表: 插入 + 关联查询 ==========")
	// =============================================
	orderN := 10_000
	t2 = time.Now()
	for i := 1; i <= orderN; i++ {
		err := orderTbl.Insert(&Order{
			EmployeeID: int64(1 + (i % N)),
			Amount:     int64(100 + (i % 500)),
			Status:     int64(i % 4), // 0=pending 1=done 2=cancel 3=refund
		})
		if err != nil {
			panic(err)
		}
		if i%5000 == 0 {
			fmt.Printf("  已插入 %d 行 (%.1fs)\n", i, time.Since(t2).Seconds())
		}
	}
	db.Commit()
	fmt.Printf("  ✅ 插入 %d 行 Order + Commit, 耗时 %.1fs\n", orderN, time.Since(t2).Seconds())

	// 查 order 的 status
	rows2, _ := orderTbl.Where("Status", "=", int64(0)).Find()
	fmt.Printf("  Order WHERE Status=0: %d 行\n", len(rows2))
	check("Order WHERE Status=0", len(rows2) > 0, "should have results")

	// =============================================
	fmt.Println("\n========== 8. 更新操作 ==========")
	// =============================================
	// 更新 id=1 的 employee
	t2 = time.Now()
	err = empTbl.Update(&Employee{
		BaseModel: DB.BaseModel{ID: 1},
		Name:      "Updated_Emp0000001",
		Age:       99,
		Score:     0,
		Dept:      "VIP",
	})
	check("UPDATE id=1", err == nil, fmt.Sprintf("err=%v", err))
	db.Commit()

	// 验证更新
	row, err = empTbl.Find(1)
	check("UPDATE 后验证", err == nil && row.Name == "Updated_Emp0000001" && row.Age == 99,
		fmt.Sprintf("err=%v name=%s age=%d", err, row.Name, row.Age))
	fmt.Printf("    更新+Commit 耗时 %.3fs\n", time.Since(t2).Seconds())

	// =============================================
	fmt.Println("\n========== 9. 删除操作 ==========")
	// =============================================
	// 删除 id=2
	t2 = time.Now()
	err = empTbl.Delete(2)
	check("DELETE id=2", err == nil, fmt.Sprintf("err=%v", err))
	db.Commit()

	// 验证删除
	_, err = empTbl.Find(2)
	check("DELETE 后验证 (应找不到)", err != nil, "should not find id=2")

	// 删除 id=3~12 (10行)
	for i := 3; i <= 12; i++ {
		empTbl.Delete(uint64(i))
	}
	db.Commit()
	cnt, _ = empTbl.Count()
	check("DELETE 11行后 COUNT", cnt == int64(N-11),
		fmt.Sprintf("expected %d, got %d", N-11, cnt))
	fmt.Printf("    删除+Commit 耗时 %.3fs\n", time.Since(t2).Seconds())

	// =============================================
	fmt.Println("\n========== 10. 关闭 + 重开验证持久化 ==========")
	// =============================================
	db.Close()

	db2, err := DB.Open(dbPath)
	if err != nil {
		panic(err)
	}
	db2.SetAutoCommitDelay(0) // 手动模式

	empTbl2, _ := DB.UseTable[Employee](db2, "employee")
	orderTbl2, _ := DB.UseTable[Order](db2, "order")

	cnt, _ = empTbl2.Count()
	check("重开后 employee COUNT", cnt == int64(N-11),
		fmt.Sprintf("expected %d, got %d", N-11, cnt))

	cnt2, _ := orderTbl2.Count()
	check("重开后 order COUNT", cnt2 == int64(orderN),
		fmt.Sprintf("expected %d, got %d", orderN, cnt2))

	// 验证更新持久化
	row, _ = empTbl2.Find(1)
	check("重开后 UPDATE 持久化", row.Name == "Updated_Emp0000001" && row.Age == 99,
		fmt.Sprintf("name=%s age=%d", row.Name, row.Age))

	// 验证删除持久化
	_, err = empTbl2.Find(2)
	check("重开后 DELETE 持久化", err != nil, "id=2 should be deleted")

	// =============================================
	fmt.Println("\n========== 11. 防抖自动提交测试 ==========")
	// =============================================
	db2.SetAutoCommitDelay(500 * time.Millisecond)

	// 插入几行, 等自动提交
	for i := N + 1; i <= N+100; i++ {
		empTbl2.Insert(&Employee{
			Name: fmt.Sprintf("Auto%04d", i-N), Age: 30, Score: 50, Dept: "Auto",
		})
	}
	fmt.Println("  插入 100 行, 等待防抖自动提交 (500ms)...")
	time.Sleep(1 * time.Second)

	// 关闭再重开验证
	db2.Close()
	db3, _ := DB.Open(dbPath)
	empTbl3, _ := DB.UseTable[Employee](db3, "employee")
	cnt, _ = empTbl3.Count()
	expected := int64(N - 11 + 100)
	check("防抖自动提交后重开 COUNT", cnt == expected,
		fmt.Sprintf("expected %d, got %d", expected, cnt))
	db3.Close()

	// =============================================
	fmt.Println("\n========== 12. 删除表 + 空闲页回收 ==========")
	// =============================================
	db4, _ := DB.Open(dbPath)
	db4.SetAutoCommitDelay(0)

	// 记录删除前的空闲页数
	beforeFree := db4.CoreDB().DebugFreePageCount()
	fmt.Printf("  删除表前 FreePageCount=%d\n", beforeFree)

	// 删除 order 表
	err = db4.DropTable("order")
	check("DropTable order", err == nil, fmt.Sprintf("err=%v", err))
	db4.Commit()

	afterFree := db4.CoreDB().DebugFreePageCount()
	fmt.Printf("  删除表后 FreePageCount=%d (回收 %d 页)\n", afterFree, afterFree-beforeFree)
	check("删除表后空闲页增加", afterFree > beforeFree,
		fmt.Sprintf("before=%d after=%d", beforeFree, afterFree))

	// 验证 order 表已不存在
	_, ok := db4.CoreDB().FindTable("order")
	check("order 表已不存在", !ok, "order table should not exist")

	// employee 表仍正常
	empTbl4, _ := DB.UseTable[Employee](db4, "employee")
	cnt, _ = empTbl4.Count()
	check("employee 表不受影响", cnt == expected,
		fmt.Sprintf("expected %d, got %d", expected, cnt))

	db4.Close()

	// =============================================
	fmt.Printf("\n============================================\n")
	fmt.Printf("测试结果: %d 通过, %d 失败\n", passed, failed)
	if failed == 0 {
		fmt.Println("🎉 全部通过!")
	} else {
		fmt.Printf("⚠️  有 %d 项失败\n", failed)
	}
}