db/schema/dialect/duckdb/example_test.go

package duckdb_test

import (
	"fmt"

	"git.fsdpf.net/go/db/engine"
	"git.fsdpf.net/go/db/schema"

	_ "git.fsdpf.net/go/db/dialect/duckdb"
	_ "git.fsdpf.net/go/db/schema/dialect/duckdb"
	_ "github.com/marcboeker/go-duckdb"
)

func Example_createTable() {
	// 创建内存数据库连接
	db := engine.Open(map[string]engine.DBConfig{
		"duckdb": engine.NewDBConfig("duckdb",
			engine.WithDuckDBFile(""), // 空字符串表示内存数据库
		),
	}).Connection("duckdb")

	// 获取 schema 方言实现
	s := schema.GetSchemaDialect(db)

	// 创建表结构定义
	bp := schema.NewBlueprint("users")
	bp.Create()

	bp.BigIncrements("id").AutoIncrement().Comment("用户ID")
	bp.String("name", 50).Comment("用户名")
	bp.String("email", 100).Comment("邮箱")
	bp.Integer("age").Nullable().Comment("年龄")
	bp.Boolean("active").Default("1").Comment("是否激活")
	bp.Timestamp("created_at").UseCurrent().Comment("创建时间")
	bp.Timestamp("updated_at").UseCurrent().Comment("更新时间")

	// 编译生成 CREATE TABLE SQL
	sqls := s.CompileCreate(bp)
	for _, sql := range sqls {
		fmt.Println(sql)
	}

	// Output:
	// CREATE SEQUENCE IF NOT EXISTS "users_id_seq"
	// CREATE TABLE IF NOT EXISTS "users" (
	// "id" BIGINT DEFAULT nextval('users_id_seq') PRIMARY KEY,
	// "name" VARCHAR(50) NOT NULL,
	// "email" VARCHAR(100) NOT NULL,
	// "age" INTEGER NULL,
	// "active" BOOLEAN NOT NULL DEFAULT '1',
	// "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	// "updated_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
	// )
	// COMMENT ON COLUMN "users"."id" IS '用户ID'
	// COMMENT ON COLUMN "users"."name" IS '用户名'
	// COMMENT ON COLUMN "users"."email" IS '邮箱'
	// COMMENT ON COLUMN "users"."age" IS '年龄'
	// COMMENT ON COLUMN "users"."active" IS '是否激活'
	// COMMENT ON COLUMN "users"."created_at" IS '创建时间'
	// COMMENT ON COLUMN "users"."updated_at" IS '更新时间'
}

func Example_addColumn() {
	db := engine.Open(map[string]engine.DBConfig{
		"duckdb": engine.NewDBConfig("duckdb",
			engine.WithDuckDBFile(""),
		),
	}).Connection("duckdb")

	s := schema.GetSchemaDialect(db)

	// 添加新列
	bp := schema.NewBlueprint("users")
	bp.String("phone", 20).Nullable().Comment("电话号码")
	bp.Text("address").Nullable().Comment("地址")

	sqls := s.CompileAdd(bp)
	for _, sql := range sqls {
		fmt.Println(sql)
	}

	// Output:
	// ALTER TABLE "users"
	// ADD COLUMN "phone" VARCHAR(20) NULL,
	// ADD COLUMN "address" TEXT NULL
	// COMMENT ON COLUMN "users"."phone" IS '电话号码'
	// COMMENT ON COLUMN "users"."address" IS '地址'
}

func Example_dropColumn() {
	db := engine.Open(map[string]engine.DBConfig{
		"duckdb": engine.NewDBConfig("duckdb",
			engine.WithDuckDBFile(""),
		),
	}).Connection("duckdb")

	s := schema.GetSchemaDialect(db)

	// 删除列
	bp := schema.NewBlueprint("users")
	bp.DropColumn("phone", "address")

	sqls := s.CompileDropColumn(bp)
	for _, sql := range sqls {
		fmt.Println(sql)
	}

	// Output:
	// ALTER TABLE "users"
	// DROP COLUMN "phone",
	// DROP COLUMN "address"
}

func Example_renameTable() {
	db := engine.Open(map[string]engine.DBConfig{
		"duckdb": engine.NewDBConfig("duckdb",
			engine.WithDuckDBFile(""),
		),
	}).Connection("duckdb")

	s := schema.GetSchemaDialect(db)

	// 重命名表
	bp := schema.NewBlueprint("users")
	bp.Rename("customers")

	sqls := s.CompileRename(bp)
	for _, sql := range sqls {
		fmt.Println(sql)
	}

	// Output:
	// ALTER TABLE "users" RENAME TO "customers"
}

func Example_modifyComment() {
	db := engine.Open(map[string]engine.DBConfig{
		"duckdb": engine.NewDBConfig("duckdb",
			engine.WithDuckDBFile(""),
		),
	}).Connection("duckdb")

	s := schema.GetSchemaDialect(db)

	// 修改表注释
	bp := schema.NewBlueprint("users")
	bp.Comment = "用户信息表"

	sqls := s.CompileModifyComment(bp)
	for _, sql := range sqls {
		fmt.Println(sql)
	}

	// Output:
	// COMMENT ON TABLE "users" IS '用户信息表'
}