db/schema/dialect/sqlserver/sqlserver.go

package sqlserver

import (
	"strings"

	"git.fsdpf.net/go/db"
	"git.fsdpf.net/go/db/dialect/sqlserver"
	"git.fsdpf.net/go/db/internal/sb"
	"git.fsdpf.net/go/db/schema"
	"git.fsdpf.net/go/db/sqlgen"
)

type SQLServer struct {
	db  *db.Database
	esg sqlgen.ExpressionSQLGenerator
}

var sqlServerDefaultModifiers = []string{
	"Nullable", "Default", "Increment", "Comment",
}

func (this SQLServer) GetColumnListing(table string) ([]string, error) {
	cols := []string{}
	err := this.db.From(db.S("INFORMATION_SCHEMA").Table("COLUMNS")).Where(
		db.C("TABLE_NAME").Eq(table),
		db.C("TABLE_SCHEMA").Eq(db.L("SCHEMA_NAME()")),
	).Pluck(&cols, "COLUMN_NAME")
	return cols, err
}

func (this SQLServer) TableExists(table string) (bool, error) {
	result, err := this.db.From(db.S("INFORMATION_SCHEMA").Table("TABLES")).Where(db.Ex{
		"TABLE_NAME":   table,
		"TABLE_TYPE":   "BASE TABLE",
		"TABLE_SCHEMA": db.L("SCHEMA_NAME()"),
	}).Count()
	return result > 0, err
}

func (this SQLServer) CompileCreate(bp *schema.Blueprint) []string {
	temporary := db.L("CREATE")
	if bp.Temporary {
		temporary = db.L("CREATE")
	}
	columns := strings.Join(this.getAddedColumns(bp), ",\n")
	sql := this.GenerateSQL("? TABLE ? (\n?\n)", temporary, db.T(bp.GetTable()), db.L(columns))
	return []string{sql}
}

func (this SQLServer) CompileAdd(bp *schema.Blueprint) []string {
	columns := strings.Join(schema.PrefixArray("ADD", this.getAddedColumns(bp)), ",\n")
	sql := this.GenerateSQL("ALTER TABLE ?\n?", db.T(bp.GetTable()), db.L(columns))
	return []string{sql}
}

func (this SQLServer) CompileChange(bp *schema.Blueprint) []string {
	columns := strings.Join(schema.PrefixArray("ALTER COLUMN", this.getChangedColumns(bp)), ",\n")
	sql := this.GenerateSQL("ALTER TABLE ?\n?", db.T(bp.GetTable()), db.L(columns))
	return []string{sql}
}

func (this SQLServer) CompileDropColumn(bp *schema.Blueprint) []string {
	columns := []string{}
	for _, item := range bp.GetCommands() {
		if item.Type != "dropColumn" {
			continue
		}
		for _, col := range item.Columns {
			columns = append(columns, this.GenerateSQL("DROP COLUMN ?", db.C(col)))
		}
	}
	sql := this.GenerateSQL("ALTER TABLE ?\n?", db.T(bp.GetTable()), db.L(strings.Join(columns, ",\n")))
	return []string{sql}
}

func (this SQLServer) CompileDrop(bp *schema.Blueprint) []string {
	return []string{this.GenerateSQL("DROP TABLE ?", db.T(bp.GetTable()))}
}

func (this SQLServer) CompileDropIfExists(bp *schema.Blueprint) []string {
	return []string{this.GenerateSQL("DROP TABLE IF EXISTS ?", db.T(bp.GetTable()))}
}

func (this SQLServer) CompileRename(bp *schema.Blueprint) []string {
	toName := ""
	commands := bp.GetCommands()
	if len(commands) == 0 {
		panic("new table undefined")
	}
	toName = bp.GetCommands()[0].To
	if toName == "" {
		panic("new table undefined")
	}
	return []string{this.GenerateSQL("EXEC sp_rename ?, ?", db.T(bp.GetTable()), db.T(toName))}
}

// 修改表备注
func (this SQLServer) CompileModifyComment(bp *schema.Blueprint) []string {
	return []string{
		this.GenerateSQL(`
IF EXISTS (
    SELECT 1 FROM sys.extended_properties 
    WHERE major_id = OBJECT_ID(?) 
    AND name = 'MS_Description' 
    AND minor_id = 0
)
BEGIN
    EXEC sp_updateextendedproperty 
        @name = N'MS_Description', 
        @value = ?, 
        @level0type = N'SCHEMA', @level0name = 'dbo', 
        @level1type = N'TABLE',  @level1name = ?
END
ELSE
BEGIN
    EXEC sp_addextendedproperty 
        @name = N'MS_Description', 
        @value = ?, 
        @level0type = N'SCHEMA', @level0name = 'dbo', 
        @level1type = N'TABLE',  @level1name = ?
END
`, db.T(bp.GetTable()), db.V(bp.Comment), db.T(bp.GetTable()), db.V(bp.Comment), db.T(bp.GetTable())),
	}
}

func (this SQLServer) addModifiers(sql string, bp *schema.Blueprint, column *schema.ColumnDefinition) string {
	for _, modifier := range sqlServerDefaultModifiers {
		sql += this.GetColumnModifier(modifier, bp, column)
	}
	return sql
}

func (this SQLServer) GetColumnType(column *schema.ColumnDefinition) string {
	switch column.Type {
	case "char":
		return this.GenerateSQL("CHAR(?)", column.Length)
	case "string":
		return this.GenerateSQL("NVARCHAR(?)", column.Length)
	case "text":
		return "NVARCHAR(MAX)"
	case "integer":
		return "INT"
	case "bigInteger":
		return "BIGINT"
	case "smallInteger":
		return "SMALLINT"
	case "decimal":
		return this.GenerateSQL("DECIMAL(?,?)", column.Total, column.Places)
	case "boolean":
		return "BIT"
	case "enum":
		return this.GenerateSQL("NVARCHAR(?)", column.Length) // SQL Server doesn't have native enum
	case "json":
		return "NVARCHAR(MAX)" // SQL Server 2016+ has JSON support
	case "binary":
		return "VARBINARY(MAX)"
	case "datetime":
		return "DATETIME2"
	case "timestamp":
		return "DATETIME2"
	case "date":
		return "DATE"
	case "time":
		return "TIME"
	case "year":
		return "SMALLINT"
	case "uuid":
		return "UNIQUEIDENTIFIER"
	}
	panic("Unsupported data type: " + column.Type)
}

func (this SQLServer) GetColumnModifier(modifier string, bp *schema.Blueprint, column *schema.ColumnDefinition) string {
	switch modifier {
	case "Nullable":
		if column.IsNullable() {
			return " NULL"
		}
		return " NOT NULL"
	case "Default":
		v := column.GetDefault()
		if v == nil {
			if column.IsUseCurrent() {
				return " DEFAULT GETDATE()"
			}
			return ""
		}
		return this.GenerateSQL(" DEFAULT ?", v)
	case "Increment":
		if column.IsAutoIncrement() {
			return " IDENTITY(1,1) PRIMARY KEY"
		}
	case "Comment":
		// SQL Server handles comments via extended properties
		return ""
	}
	return ""
}

func (this SQLServer) getAddedColumns(bp *schema.Blueprint) (columns []string) {
	for _, column := range bp.GetAddedColumns() {
		sql := this.GenerateSQL("? ?", db.C(column.Name), db.L(this.GetColumnType(column)))
		columns = append(columns, this.addModifiers(sql, bp, column))
	}
	return
}

func (this SQLServer) getChangedColumns(bp *schema.Blueprint) (columns []string) {
	for _, column := range bp.GetChangedColumns() {
		sql := this.GenerateSQL("? ?", db.C(column.Name), db.L(this.GetColumnType(column)))
		columns = append(columns, this.addModifiers(sql, bp, column))
	}
	return
}

func (this SQLServer) GenerateSQL(sql string, args ...any) string {
	sb := sb.NewSQLBuilder(false)
	this.esg.Generate(sb, db.L(sql, args...))
	result, _, _ := sb.ToSQL()
	return result
}

func init() {
	schema.RegisterDialect("sqlserver", func(db *db.Database) schema.Schema {
		return &SQLServer{
			db:  db,
			esg: sqlgen.NewExpressionSQLGenerator("sqlserver", sqlserver.DialectOptions()),
		}
	})
}