db/schema/dialect/sqlite3/sqlite3_test.go

package sqlite3_test

import (
	"testing"

	"git.fsdpf.net/go/db"
	"git.fsdpf.net/go/db/engine"
	"git.fsdpf.net/go/db/schema"
	sqlmock "github.com/DATA-DOG/go-sqlmock"
	"github.com/stretchr/testify/suite"
)

var tableName = "entry"

type sqlite3Test struct {
	suite.Suite
	schema schema.Schema
}

func TestSqlite3Suite(t *testing.T) {
	suite.Run(t, new(sqlite3Test))
}

func (t *sqlite3Test) SetupSuite() {
	mockDB, mock, _ := sqlmock.New()

	// TestGetColumnListing: PRAGMA table_info(entry)
	mock.ExpectQuery("PRAGMA table_info").
		WillReturnRows(sqlmock.NewRows([]string{"cid", "name", "type", "notnull", "dflt_value", "pk"}).
			AddRow(0, "id", "INTEGER", 1, nil, 1).
			AddRow(1, "int", "INT", 1, nil, 0).
			AddRow(2, "float", "FLOAT", 1, nil, 0).
			AddRow(3, "string", "VARCHAR(255)", 1, nil, 0).
			AddRow(4, "time", "DATETIME", 1, nil, 0).
			AddRow(5, "bool", "TINYINT", 1, nil, 0).
			AddRow(6, "bytes", "BLOB", 1, nil, 0))

	// TestTableExists: SELECT COUNT(*) FROM sqlite_master WHERE ...
	mock.ExpectQuery("SELECT.*COUNT.*sqlite_master").
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))

	conn := engine.Mock(map[string]engine.MockDBConfig{
		"test-sqlite3": {Driver: "sqlite3", Mock: mockDB},
	}).Connection("test-sqlite3")

	t.schema = schema.GetSchemaDialect(conn)
}

func (t *sqlite3Test) TestTableExists() {
	result, err := t.schema.TableExists(tableName)
	t.Require().NoError(err)
	t.Require().Equal(result, true)
}

func (t *sqlite3Test) TestGetColumnListing() {
	result, err := t.schema.GetColumnListing(tableName)
	t.Require().NoError(err)
	t.Require().ElementsMatch([]string{"bool", "bytes", "float", "id", "int", "string", "time"}, result)
}

// 创建列表
func (t *sqlite3Test) TestCompileCreate() {
	bp := schema.NewBlueprint("users")
	bp.Create()
	bp.Charset = "utf8mb4"

	bp.BigIncrements("id").AutoIncrement().Comment("ID")
	bp.Boolean("enabled").Default("1").Comment("是否有效")
	bp.Char("created_user", 36).Default("00000000-0000-0000-0000-000000000000").Comment("创建者")
	bp.Char("owned_user", 36).Default("00000000-0000-0000-0000-000000000000").Comment("拥有者")
	bp.Timestamp("created_at").UseCurrent().Comment("创建时间")
	bp.Timestamp("updated_at").UseCurrent().Default(db.L("ON UPDATE CURRENT_TIMESTAMP")).Comment("更新时间")
	bp.DateTime("deleted_at").Nullable().Comment("删除时间")

	sql := t.schema.CompileCreate(bp)

	t.Equal([]string{
		"CREATE TABLE IF NOT EXISTS `users` (\n" +
			"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- ID\n" +
			"`enabled` tinyint(1) NOT NULL DEFAULT '1', -- 是否有效\n" +
			"`created_user` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', -- 创建者\n" +
			"`owned_user` char(36) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000', -- 拥有者\n" +
			"`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间\n" +
			"`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 更新时间\n" +
			"`deleted_at` datetime NULL -- 删除时间\n" +
			")",
	}, sql)

	t.T().Log(sql)
}

// 添加字段
func (t *sqlite3Test) TestCompileAdd() {
	bp := schema.NewBlueprint("users")

	bp.String("name", 50).Comment("用户名")
	bp.SmallInteger("age").Default("18").Comment("年龄")
	bp.Enum("sex", []string{"0", "1"}).Default("0").Comment("性别")

	sql := t.schema.CompileAdd(bp)

	t.Equal([]string{
		"ALTER TABLE `users` ADD COLUMN `name` varchar(50) NOT NULL",
		"ALTER TABLE `users` ADD COLUMN `age` smallint(4) NOT NULL DEFAULT '18'",
		"ALTER TABLE `users` ADD COLUMN `sex` varchar check('sex' in('0', '1')) NOT NULL DEFAULT '0'",
	}, sql)

	t.T().Log(sql)
}

// 修改字段
func (t *sqlite3Test) TestCompileChange() {
	bp := schema.NewBlueprint("users")

	bp.String("name", 50).Change("username")
	bp.SmallInteger("age").Default("19").Comment("年龄").Change()

	sql := t.schema.CompileChange(bp)
	t.Equal(len([]string{
		"ALTER TABLE `users` ADD COLUMN `name_tmp` varchar(50) NOT NULL",
		"UPDATE `users` SET `name_tmp`=`name`",
		"ALTER TABLE `users` DROP COLUMN `name`",
		"ALTER TABLE `users` RENAME COLUMN `name_tmp` TO `username`",
		"ALTER TABLE `users` ADD COLUMN `age_tmp` smallint(4) NOT NULL DEFAULT '19'",
		"UPDATE `users` SET `age_tmp`=`age`",
		"ALTER TABLE `users` DROP COLUMN `age`",
		"ALTER TABLE `users` RENAME COLUMN `age_tmp` TO `age`",
	}), len(sql))

	t.T().Log(sql)
}

// 删除字段
func (t *sqlite3Test) TestCompileDropColumn() {
	bp := schema.NewBlueprint("users")

	bp.DropColumn("age", "sex")

	sql := t.schema.CompileDropColumn(bp)

	t.Equal([]string{
		"ALTER TABLE `users` DROP COLUMN `age`",
		"ALTER TABLE `users` DROP COLUMN `sex`",
	}, sql)

	t.T().Log(sql)
}

// 删除表
func (t *sqlite3Test) TestCompileDrop() {
	bp := schema.NewBlueprint("users")

	bp.Drop()

	sql := t.schema.CompileDrop(bp)

	t.Equal([]string{"DROP TABLE `users`"}, sql)

	t.T().Log(sql)
}

// 删除表
func (t *sqlite3Test) TestCompileDropIfExists() {
	bp := schema.NewBlueprint("users")

	bp.Drop()

	sql := t.schema.CompileDropIfExists(bp)

	t.Equal([]string{"DROP TABLE IF EXISTS `users`"}, sql)

	t.T().Log(sql)
}

// HIDDEN 列（虚拟表参数列）
func (t *sqlite3Test) TestCompileCreate_HiddenColumn() {
	bp := schema.NewBlueprint("api_users")
	bp.Create()

	bp.BigIncrements("id").AutoIncrement()
	bp.String("name", 100).Nullable()
	bp.Integer("age")
	bp.String("token", 255).Hidden().Nullable()
	bp.Integer("page_size").Hidden().Nullable()

	sql := t.schema.CompileCreate(bp)

	t.Equal([]string{
		"CREATE TABLE IF NOT EXISTS `api_users` (\n" +
			"`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT\n" +
			"`name` varchar(100) NULL\n" +
			"`age` INTEGER NOT NULL\n" +
			"`token` varchar(255) HIDDEN\n" +
			"`page_size` INTEGER HIDDEN\n" +
			")",
	}, sql)

	t.T().Log(sql)
}

// 表重命名
func (t *sqlite3Test) TestCompileRename() {
	bp := schema.NewBlueprint("users")

	bp.Rename("user")

	sql := t.schema.CompileRename(bp)

	t.Equal([]string{"RENAME TABLE `users` TO `user`"}, sql)

	t.T().Log(sql)
}