TsDB2/demo/sql_driver/main.go

package main

// =============================================================================
// sql_driver — database/sql 驱动演示: 标准库 API 全特性走查 (M1/M2/M3)
//
// 	go run main.go
//
// 覆盖: CREATE TABLE (含主键/唯一/普通索引) → INSERT (显式/自增主键, NULL) →
// SELECT (投影/WHERE 全条件族/ORDER BY/LIMIT/聚合) → UPDATE → DELETE →
// 事务 (Rollback 与 Commit) → 唯一约束冲突 → 不支持语法的明确报错。
// 任何一步断言失败立即非零退出。
// =============================================================================

import (
	"database/sql"
	"fmt"
	"os"

	_ "git.ycl.pub/yl32940/TsDB2/DB/driver"
)

const dbPath = "sql_driver_demo.db"

var failed bool

func check(err error, what string) {
	if err != nil {
		fmt.Printf("❌ %s: %v\n", what, err)
		os.Exit(1)
	}
}

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

func main() {
	os.Remove(dbPath)
	db, err := sql.Open("vrtx", dbPath)
	check(err, "open")
	defer db.Close()
	// 引擎单写者模型: 连接池串行化, 设 1 路连接避免无谓排队
	db.SetMaxOpenConns(1)

	// ---------- M3: DDL ----------
	section("M3: CREATE TABLE (主键/唯一/普通索引)")
	_, err = db.Exec(`CREATE TABLE users (
		id   BIGINT PRIMARY KEY,
		name VARCHAR(32) NOT NULL,
		age  INT,
		email VARCHAR(64) UNIQUE,
		city VARCHAR(32)
	)`)
	check(err, "create table")
	_, err = db.Exec(`CREATE INDEX idx_city ON users (city)`)
	check(err, "create index")
	fmt.Println("✅ 建表 users (id 主键 / email 唯一 / city 普通索引)")

	// ---------- M1: INSERT ----------
	section("M1: INSERT (显式主键 + 省略列=NULL)")
	res, err := db.Exec(`INSERT INTO users (id, name, age, email, city) VALUES (?, ?, ?, ?, ?)`,
		1, "alice", 30, "alice@x.com", "beijing")
	check(err, "insert alice")
	id, _ := res.LastInsertId()
	fmt.Printf("✅ 插入 alice LastInsertId=%d\n", id)

	res, err = db.Exec(`INSERT INTO users (id, name, city) VALUES (?, ?, ?)`, 2, "bob", "shanghai") // age/email = NULL
	check(err, "insert bob")
	id, _ = res.LastInsertId()
	fmt.Printf("✅ 插入 bob (age/email 省略 → NULL) LastInsertId=%d\n", id)

	// 唯一约束: 重复 email 必须报错
	_, err = db.Exec(`INSERT INTO users (id, name, email) VALUES (?, ?, ?)`, 3, "mallory", "alice@x.com")
	if err == nil {
		fmt.Println("❌ 唯一约束未生效: 重复 email 插入成功")
		os.Exit(1)
	}
	fmt.Printf("✅ 唯一索引拦截重复 email: %v\n", err)

	// ---------- M1: SELECT ----------
	section("M1: SELECT (投影 + WHERE + ORDER BY + LIMIT)")
	rows, err := db.Query(`SELECT id, name, age FROM users WHERE age >= ? ORDER BY age DESC LIMIT 10`, 18)
	check(err, "query")
	type user struct {
		id   int64
		name string
		age  sql.NullInt64
	}
	var users []user
	for rows.Next() {
		var u user
		var nm []byte
		check(rows.Scan(&u.id, &nm, &u.age), "scan")
		u.name = string(nm)
		users = append(users, u)
	}
	rows.Close()
	for _, u := range users {
		age := "NULL"
		if u.age.Valid {
			age = fmt.Sprint(u.age.Int64)
		}
		fmt.Printf("   id=%d name=%-8s age=%v\n", u.id, u.name, age)
	}
	// SQL 语义: NULL >= 18 不为真 → bob (age=NULL) 不在结果里
	if len(users) != 1 || users[0].name != "alice" || !users[0].age.Valid {
		fmt.Println("❌ SELECT 结果不对")
		os.Exit(1)
	}
	fmt.Println("✅ 投影/条件/排序/分页正确 (NULL 不参与比较: bob 被 age>=18 过滤)")

	// ---------- M2: WHERE 全条件族 ----------
	section("M2: WHERE 条件族 (IN / OR / NOT / IS NULL / LIKE)")
	show := func(label, q string, args ...interface{}) int64 {
		var n int64
		check(db.QueryRow(q, args...).Scan(&n), label)
		fmt.Printf("   %-14s = %d\n", label, n)
		return n
	}
	if show("IN", `SELECT COUNT(*) FROM users WHERE id IN (?, ?)`, 1, 2) != 2 ||
		show("OR", `SELECT COUNT(*) FROM users WHERE city = ? OR city = ?`, "beijing", "shanghai") != 2 ||
		show("NOT", `SELECT COUNT(*) FROM users WHERE NOT (id = ?)`, 1) != 1 ||
		show("IS NULL", `SELECT COUNT(*) FROM users WHERE age IS NULL`) != 1 ||
		show("LIKE", `SELECT COUNT(*) FROM users WHERE name LIKE ?`, "al%") != 1 {
		fmt.Println("❌ WHERE 条件族结果不对")
		os.Exit(1)
	}
	fmt.Println("✅ 全条件族通过")

	// ---------- M3: 聚合 ----------
	section("M3: 聚合 (COUNT/SUM/AVG/MIN/MAX)")
	agg := func(label, q string) float64 {
		var v float64
		check(db.QueryRow(q).Scan(&v), label)
		fmt.Printf("   %-10s = %v\n", label, v)
		return v
	}
	if agg("SUM(age)", `SELECT SUM(age) FROM users`) != 30 ||
		agg("COUNT(*)", `SELECT COUNT(*) FROM users`) != 2 ||
		agg("AVG(age)", `SELECT AVG(age) FROM users`) != 30 {
		fmt.Println("❌ 聚合结果不对")
		os.Exit(1)
	}
	agg("MIN(age)", `SELECT MIN(age) FROM users`)
	agg("MAX(age)", `SELECT MAX(age) FROM users`)
	fmt.Println("✅ 聚合通过")

	// ---------- M2: UPDATE / DELETE ----------
	section("M2: UPDATE / DELETE")
	res, err = db.Exec(`UPDATE users SET age = ? WHERE name = ?`, 31, "alice")
	check(err, "update")
	if n, _ := res.RowsAffected(); n != 1 {
		fmt.Printf("❌ UPDATE affected=%d\n", n)
		os.Exit(1)
	}
	var age int64
	check(db.QueryRow(`SELECT age FROM users WHERE id = ?`, 1).Scan(&age), "select age")
	if age != 31 {
		fmt.Printf("❌ UPDATE 未生效 age=%d\n", age)
		os.Exit(1)
	}
	fmt.Println("✅ UPDATE 生效 (alice.age 30→31)")

	res, err = db.Exec(`DELETE FROM users WHERE city = ?`, "shanghai")
	check(err, "delete")
	if n, _ := res.RowsAffected(); n != 1 {
		fmt.Printf("❌ DELETE affected=%d\n", n)
		os.Exit(1)
	}
	fmt.Println("✅ DELETE 生效 (bob 已删)")

	// ---------- M2: 事务 ----------
	section("M2: 事务 (Rollback 回滚 / Commit 提交)")
	tx, err := db.Begin()
	check(err, "begin")
	_, err = tx.Exec(`INSERT INTO users (id, name, age) VALUES (?, ?, ?)`, 10, "temp", 99)
	check(err, "tx insert")
	check(tx.Rollback(), "rollback")
	var cnt int64
	check(db.QueryRow(`SELECT COUNT(*) FROM users WHERE id = ?`, 10).Scan(&cnt), "count after rollback")
	if cnt != 0 {
		fmt.Println("❌ Rollback 后 temp 仍在")
		os.Exit(1)
	}
	fmt.Println("✅ Rollback 撤销了事务内插入")

	tx2, err := db.Begin()
	check(err, "begin2")
	_, err = tx2.Exec(`INSERT INTO users (id, name, age) VALUES (?, ?, ?)`, 11, "carol", 22)
	check(err, "tx2 insert")
	check(tx2.Commit(), "commit")
	check(db.QueryRow(`SELECT COUNT(*) FROM users WHERE id = ?`, 11).Scan(&cnt), "count after commit")
	if cnt != 1 {
		fmt.Println("❌ Commit 后 carol 不在")
		os.Exit(1)
	}
	fmt.Println("✅ Commit 提交了事务内插入")

	// ---------- Prepare 路径 ----------
	section("附加: Prepare (预编译语句)")
	stmt, err := db.Prepare(`SELECT name FROM users WHERE id = ?`)
	check(err, "prepare")
	defer stmt.Close()
	var nm string
	check(stmt.QueryRow(11).Scan(&nm), "prepared query")
	if nm != "carol" {
		fmt.Printf("❌ prepared 查询结果 %q\n", nm)
		os.Exit(1)
	}
	fmt.Println("✅ Prepare/QueryRow 通过")

	// ---------- 不支持语法的明确报错 ----------
	section("附加: 不支持语法明确报错 (不静默)")
	if _, err := db.Query(`SELECT * FROM users GROUP BY city`); err == nil {
		fmt.Println("❌ GROUP BY 应报错")
		os.Exit(1)
	} else {
		fmt.Printf("✅ GROUP BY 明确拒绝: %v\n", err)
	}

	fmt.Println("\n🎉 全部通过 — database/sql 驱动 M1/M2/M3 可用")
	_ = failed
}