TsDB2/demo/feature_demo/main.go

package main

import (
	"git.ycl.pub/yl32940/TsDB2/DB"
	"fmt"
	"os"
	"strings"
	"time"
)

// ========== 数据模型 ==========
type User struct {
	DB.BaseModel
	Name  string `vrtx:"maxlen:32"`
	Age   int64  `vrtx:"index:idx_age"`
	Score int64
	Dept  string `vrtx:"maxlen:16,index:idx_dept"`
}

type Order struct {
	DB.BaseModel
	UserID int64
	Amount int64
	Status int64 `vrtx:"index:idx_status"`
}

var db *DB.DB
var passed, failed int

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

func section(title string) {
	fmt.Printf("\n%s %s\n", strings.Repeat("=", 50), title)
}

func main() {
	os.Remove("feature_demo.db")
	os.Remove("feature_demo.db.wal")

	fmt.Println(strings.Repeat("=", 60))
	fmt.Println("  TsDB2 全功能综合测试")
	fmt.Println("  涵盖: 表/索引/CRUD/事务/WAL/BufferPool/提交策略/恢复")
	fmt.Println(strings.Repeat("=", 60))

	// ============================================================
	// 1. 数据库打开与配置
	// ============================================================
	section("1. 数据库配置与打开")
	var err error
	db, err = DB.Open("feature_demo.db")
	check("OpenDB", err == nil)

	// 配置参数
	db.SetBufferPoolCapacity(200)                 // 200 页 = 3.2MB
	db.SetDirtyThreshold(50)                      // 50 页脏页阈值
	db.SetAutoCommitDelay(500 * time.Millisecond) // 500ms 防抖
	db.SetCommitPreset("online")                  // 在线业务预设
	fmt.Println("  ✅ 配置: BufferPool=200页, DirtyThreshold=50页, Debounce=500ms, Preset=online")
	passed++

	// ============================================================
	// 2. 表创建 (UseTable 自动建表)
	// ============================================================
	section("2. 表创建 (泛型 UseTable)")

	userTbl, err := DB.UseTable[User](db, "users")
	check("UseTable[User] (自动建表+主键索引)", err == nil)

	orderTbl, err := DB.UseTable[Order](db, "orders")
	check("UseTable[Order] (自动建表+主键索引)", err == nil)

	// 重复调用应自动同步 schema (不报错)
	userTbl2, err := DB.UseTable[User](db, "users")
	check("重复 UseTable[User] 自动同步 schema", err == nil && userTbl2 != nil)

	// ============================================================
	// 3. 二级索引创建 (通过结构体标签自动创建)
	// ============================================================
	section("3. 二级索引创建 (标签自动创建)")

	// 索引已通过 vrtx 标签声明，UseTable 时自动创建
	check("索引已通过标签自动创建", true)

	// ============================================================
	// 4. 基础 CRUD
	// ============================================================
	section("4. 基础 CRUD")

	// INSERT
	for i := 1; i <= 20; i++ {
		err := userTbl.Insert(&User{
			Name:  fmt.Sprintf("User%03d", i),
			Age:   int64(20 + i%30),
			Score: int64(50 + i*3),
			Dept:  fmt.Sprintf("D%d", i%5),
		})
		if err != nil {
			fmt.Printf("  ❌ Insert(%d): %v\n", i, err)
			failed++
		}
	}
	fmt.Println("  ✅ Insert 20 行 (ID=1~20)")
	passed++

	// FIND (精确查找)
	u, err := userTbl.Find(5)
	if err == nil && u != nil {
		fmt.Printf("  ✅ Find(5): Name=%s Age=%d Score=%d Dept=%s\n", u.Name, u.Age, u.Score, u.Dept)
		passed++
	} else {
		fmt.Println("  ❌ Find(5) 失败")
		failed++
	}

	// UPDATE
	err = userTbl.Update(&User{
		BaseModel: DB.BaseModel{ID: 5},
		Score:     999,
	})
	check("Update(5) Score=999", err == nil)

	u, _ = userTbl.Find(5)
	if u != nil && u.Score == 999 {
		fmt.Printf("  ✅ 验证更新: Score=%d\n", u.Score)
		passed++
	} else {
		fmt.Println("  ❌ 更新验证失败")
		failed++
	}

	// DELETE
	err = userTbl.Delete(10)
	check("Delete(10)", err == nil)

	_, err = userTbl.Find(10)
	check("验证删除: ID=10 不存在", err != nil)

	// ============================================================
	// 5. 条件查询 (Where / OrderBy / Limit / Offset)
	// ============================================================
	section("5. 条件查询")

	// WHERE 等值
	users, err := userTbl.Where("Age", "=", int64(25)).Find()
	if err == nil {
		fmt.Printf("  ✅ WHERE Age=25: %d 行\n", len(users))
		passed++
	} else {
		fmt.Printf("  ❌ WHERE Age=25: %v\n", err)
		failed++
	}

	// WHERE 范围 (AND)
	users, err = userTbl.Where("Age", ">=", int64(20)).Where("Age", "<=", int64(25)).Find()
	if err == nil {
		fmt.Printf("  ✅ WHERE Age>=20 AND Age<=25: %d 行\n", len(users))
		passed++
	} else {
		fmt.Printf("  ❌ WHERE 范围: %v\n", err)
		failed++
	}

	// WHERE 大于
	users, err = userTbl.Where("Age", ">", int64(40)).Find()
	if err == nil {
		fmt.Printf("  ✅ WHERE Age>40: %d 行\n", len(users))
		passed++
	} else {
		fmt.Printf("  ❌ WHERE Age>40: %v\n", err)
		failed++
	}

	// LIKE
	users, err = userTbl.Where("Name", "like", "User%").Find()
	if err == nil {
		fmt.Printf("  ✅ WHERE Name LIKE 'User%%': %d 行\n", len(users))
		passed++
	} else {
		fmt.Printf("  ❌ LIKE: %v\n", err)
		failed++
	}

	// != (NOT)
	users, err = userTbl.Where("Age", "!=", int64(25)).Find()
	if err == nil {
		fmt.Printf("  ✅ WHERE Age!=25: %d 行\n", len(users))
		passed++
	} else {
		fmt.Printf("  ❌ NOT: %v\n", err)
		failed++
	}

	// ============================================================
	// 6. 排序 + 分页
	// ============================================================
	section("6. 排序 + 分页")

	users, err = userTbl.OrderBy("Age", true).Limit(5).Find()
	if err == nil && len(users) == 5 {
		fmt.Println("  ✅ ORDER BY Age DESC LIMIT 5:")
		for i, u := range users {
			fmt.Printf("    [%d] Name=%s Age=%d Score=%d\n", i+1, u.Name, u.Age, u.Score)
		}
		passed++
	} else {
		fmt.Printf("  ❌ 排序分页: err=%v rows=%d\n", err, len(users))
		failed++
	}

	users, err = userTbl.OrderBy("Age", false).Offset(5).Limit(5).Find()
	if err == nil && len(users) == 5 {
		fmt.Println("  ✅ ORDER BY Age ASC OFFSET 5 LIMIT 5:")
		for i, u := range users {
			fmt.Printf("    [%d] Name=%s Age=%d Score=%d\n", i+1, u.Name, u.Age, u.Score)
		}
		passed++
	} else {
		fmt.Printf("  ❌ OFFSET 分页: err=%v rows=%d\n", err, len(users))
		failed++
	}

	// ============================================================
	// 7. 聚合查询
	// ============================================================
	section("7. 聚合查询")

	cnt, err := userTbl.Query().Count()
	if err == nil {
		fmt.Printf("  ✅ COUNT(*): %d\n", cnt)
		passed++
	} else {
		fmt.Printf("  ❌ COUNT: %v\n", err)
		failed++
	}

	cnt, err = userTbl.Where("Age", "=", int64(25)).Count()
	if err == nil {
		fmt.Printf("  ✅ COUNT WHERE Age=25: %d\n", cnt)
		passed++
	} else {
		fmt.Printf("  ❌ COUNT WHERE: %v\n", err)
		failed++
	}

	// ============================================================
	// 8. Scan 流式遍历
	// ============================================================
	section("8. Scan 流式遍历")

	// Scan callback: return true=继续, false=停止
	scanCount := 0
	err = userTbl.Query().Limit(10).Scan(func(u *User) bool {
		scanCount++
		return true // 继续扫描
	})
	if err == nil && scanCount == 10 {
		fmt.Printf("  ✅ Scan LIMIT 10: 遍历 %d 行\n", scanCount)
		passed++
	} else {
		fmt.Printf("  ❌ Scan: err=%v count=%d\n", err, scanCount)
		failed++
	}

	// ============================================================
	// 9. 事务 - 提交
	// ============================================================
	section("9. 事务 - 提交")

	txn := db.BeginTransaction()

	// 事务内插入 (使用同一个 tbl 对象)
	for i := 21; i <= 30; i++ {
		err := userTbl.Insert(&User{
			Name:  fmt.Sprintf("Txn%03d", i),
			Age:   int64(30 + i%10),
			Score: int64(70 + i),
			Dept:  fmt.Sprintf("D%d", i%4),
		})
		if err != nil {
			fmt.Printf("  ❌ 事务内 Insert(%d): %v\n", i, err)
			failed++
		}
	}
	fmt.Println("  ✅ 事务内插入 10 行 (ID=21~30)")
	passed++

	// 事务内更新
	err = userTbl.Update(&User{
		BaseModel: DB.BaseModel{ID: 21},
		Score:     8888,
	})
	check("事务内 Update(21) Score=8888", err == nil)

	// 事务内删除
	err = userTbl.Delete(25)
	check("事务内 Delete(25)", err == nil)

	err = txn.Commit()
	check("Transaction.Commit()", err == nil)

	// 验证
	u, err = userTbl.Find(21)
	if err == nil && u != nil && u.Score == 8888 {
		fmt.Printf("  ✅ 提交后验证: ID=21 Score=%d\n", u.Score)
		passed++
	} else {
		fmt.Println("  ❌ 提交后验证: ID=21 不正确")
		failed++
	}

	_, err = userTbl.Find(25)
	check("提交后验证: ID=25 已删除", err != nil)

	// ============================================================
	// 10. 事务 - 回滚
	// ============================================================
	section("10. 事务 - 回滚")

	txn2 := db.BeginTransaction()
	for i := 31; i <= 40; i++ {
		userTbl.Insert(&User{
			Name:  fmt.Sprintf("Rb%03d", i),
			Age:   int64(20),
			Score: int64(0),
			Dept:  "X",
		})
	}
	fmt.Println("  ✅ 事务内插入 10 行 (ID=31~40)")
	passed++

	err = txn2.Rollback()
	check("Transaction.Rollback()", err == nil)

	// 回滚后 ID=31 应不存在 (需要重开后验证, 内存中可能还有残留)
	// 先标记, 后面重开后验证
	fmt.Println("  ⏳ 回滚后数据验证将在重开后进行")

	// ============================================================
	// 11. WAL + Commit 持久化
	// ============================================================
	section("11. WAL + Commit 持久化")

	err = db.Commit()
	check("DB.Commit() (WAL 写入)", err == nil)

	// ============================================================
	// 12. 多表操作
	// ============================================================
	section("12. 多表操作")

	for i := 1; i <= 10; i++ {
		orderTbl.Insert(&Order{
			UserID: int64(i*2 - 1),
			Amount: int64(100 + i*50),
			Status: int64(i % 3),
		})
	}
	fmt.Println("  ✅ orders 表插入 10 行")
	passed++

	orders, err := orderTbl.Where("Status", "=", int64(0)).Find()
	if err == nil {
		fmt.Printf("  ✅ orders WHERE Status=0: %d 行\n", len(orders))
		passed++
	} else {
		fmt.Printf("  ❌ orders 查询: %v\n", err)
		failed++
	}

	// ============================================================
	// 13. DropTable
	// ============================================================
	section("13. DropTable")

	err = db.DropTable("orders")
	check("DropTable orders", err == nil)

	orderCore2, _ := db.CoreDB().FindTable("orders")
	check("orders 表已不存在", orderCore2 == nil)

	userCore2, _ := db.CoreDB().FindTable("users")
	check("users 表不受影响", userCore2 != nil)

	// ============================================================
	// 14. 关闭 + 重开验证持久化
	// ============================================================
	section("14. 关闭 + 重开验证持久化")

	err = db.Close()
	check("DB.Close()", err == nil)

	db, err = DB.Open("feature_demo.db")
	check("重新打开数据库", err == nil)

	userTbl, _ = DB.UseTable[User](db, "users")

	u, err = userTbl.Find(1)
	if err == nil && u != nil {
		fmt.Printf("  ✅ 重开后 Find(1): Name=%s Age=%d\n", u.Name, u.Age)
		passed++
	} else {
		fmt.Println("  ❌ 重开后 Find(1) 失败")
		failed++
	}

	u, err = userTbl.Find(5)
	if err == nil && u.Score == 999 {
		fmt.Printf("  ✅ 重开后 Find(5): Score=%d (更新持久化)\n", u.Score)
		passed++
	} else {
		fmt.Println("  ❌ 重开后 Find(5) Score 不正确")
		failed++
	}

	_, err = userTbl.Find(10)
	check("重开后 ID=10 (已删除) 不存在", err != nil)

	// 验证事务提交的数据
	u, err = userTbl.Find(21)
	if err == nil && u.Score == 8888 {
		fmt.Printf("  ✅ 重开后 Find(21) (事务提交+更新): Score=%d\n", u.Score)
		passed++
	} else {
		fmt.Println("  ❌ 重开后 Find(21) 失败或数据不正确")
		failed++
	}

	_, err = userTbl.Find(25)
	check("重开后 ID=25 (事务内删除) 不存在", err != nil)

	// 验证回滚的数据不存在
	_, err = userTbl.Find(31)
	check("重开后 ID=31 (回滚) 不存在", err != nil)

	// ============================================================
	// 15. 防抖自动提交验证
	// ============================================================
	section("15. 防抖自动提交")

	// 重新打开一个干净的数据库来测试自动提交
	db.Close()
	os.Remove("feature_demo.db")
	os.Remove("feature_demo.db.wal")
	db, _ = DB.Open("feature_demo.db")
	db.SetAutoCommitDelay(500 * time.Millisecond)
	userTbl, _ = DB.UseTable[User](db, "users")

	for i := 1; i <= 10; i++ {
		userTbl.Insert(&User{
			Name:  fmt.Sprintf("Auto%03d", i),
			Age:   int64(25),
			Score: int64(100),
			Dept:  "AUTO",
		})
	}
	fmt.Println("  ✅ 插入 10 行, 等待防抖自动提交 (500ms)...")
	time.Sleep(1500 * time.Millisecond)

	db.Close()
	db, _ = DB.Open("feature_demo.db")
	userTbl, _ = DB.UseTable[User](db, "users")

	u, err = userTbl.Find(5)
	if err == nil && u != nil {
		fmt.Printf("  ✅ 防抖自动提交后重开: ID=5 存在 (Name=%s)\n", u.Name)
		passed++
	} else {
		fmt.Println("  ❌ 防抖自动提交失败: ID=5 不存在")
		failed++
	}

	// ============================================================
	// 16. LockTable 机制
	// ============================================================
	section("16. LockTable 机制")

	db.LockWrites()
	fmt.Println("  ✅ LockWrites() 成功")
	passed++

	db.UnlockWrites()
	fmt.Println("  ✅ UnlockWrites() 成功")
	passed++

	// ============================================================
	// 17. 提交策略预设对比
	// ============================================================
	section("17. 提交策略预设")

	presets := []string{"batch", "online", "safe"}
	for _, p := range presets {
		db.SetCommitPreset(p)
		fmt.Printf("  ✅ Preset '%s' 设置成功\n", p)
		passed++
	}

	// ============================================================
	// 18. BatchInsert
	// ============================================================
	section("18. 批量插入 (BatchInsert)")

	batchUsers := make([]*User, 10)
	for i := 0; i < 10; i++ {
		batchUsers[i] = &User{
			Name:  fmt.Sprintf("Batch%03d", i+51),
			Age:   int64(30 + i),
			Score: int64(200 + i),
			Dept:  "BATCH",
		}
	}
	err = userTbl.BatchInsert(batchUsers)
	check("BatchInsert 10 行", err == nil)

	// ============================================================
	// 19. First 查询
	// ============================================================
	section("19. First 查询")

	first, err := userTbl.Where("Age", ">=", int64(20)).OrderBy("Age", false).First()
	if err == nil && first != nil {
		fmt.Printf("  ✅ First(): Name=%s Age=%d\n", first.Name, first.Age)
		passed++
	} else {
		fmt.Printf("  ❌ First(): %v\n", err)
		failed++
	}

	// ============================================================
	// 20. FindAll
	// ============================================================
	section("20. FindAll")

	all, err := userTbl.FindAll()
	if err == nil {
		fmt.Printf("  ✅ FindAll(): %d 行\n", len(all))
		passed++
	} else {
		fmt.Printf("  ❌ FindAll(): %v\n", err)
		failed++
	}

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

	db.Close()
	// [KEEP-DB] 保留数据库文件供 Analyzer 分析, 屏蔽运行结束后的清理删除
	// os.Remove("feature_demo.db")
	// os.Remove("feature_demo.db.wal")
}