Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package tsql
import (
"context"
"fmt"
"strings"
"github.com/bytebase/omni/mssql/ast"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/common"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/store/model"
)
const (
maxCommentLength = 1000
)
func init() {
base.RegisterGenerateRestoreSQL(storepb.Engine_MSSQL, GenerateRestoreSQL)
}
func GenerateRestoreSQL(ctx context.Context, rCtx base.RestoreContext, statement string, backupItem *storepb.PriorBackupDetail_Item) (string, error) {
originalSQL, err := extractStatement(statement, backupItem)
if err != nil {
return "", errors.Errorf("failed to extract single SQL: %v", err)
}
if len(originalSQL) == 0 {
return "", errors.Errorf("no original SQL")
}
parsedStatements, err := parseTSQLStatements(statement)
if err != nil {
return "", err
}
// Find the AST that contains the statement at the backup position
var targetResult ast.Node
for _, parsedStatement := range parsedStatements {
node, ok := GetOmniNode(parsedStatement.AST)
if !ok || node == nil || !isRestorableDML(node) {
continue
}
start, end := statementPositions(parsedStatement.Start, parsedStatement.Text, dmlNodeLoc(node))
if inRange(start, end, backupItem.StartPosition, backupItem.EndPosition) {
targetResult = node
break
}
}
if targetResult == nil {
return "", errors.Errorf("could not find statement at position (line %d:%d - %d:%d)",
backupItem.StartPosition.Line, backupItem.StartPosition.Column,
backupItem.EndPosition.Line, backupItem.EndPosition.Column)
}
sqlForComment, truncated := common.TruncateString(originalSQL, maxCommentLength)
if truncated {
sqlForComment += "..."
}
return doGenerate(ctx, rCtx, sqlForComment, targetResult, backupItem)
}
func doGenerate(ctx context.Context, rCtx base.RestoreContext, sqlForComment string, node ast.Node, backupItem *storepb.PriorBackupDetail_Item) (string, error) {
_, _, sourceDatabase, err := common.GetDatabaseResourceName(backupItem.SourceTable.Database)
if err != nil {
return "", errors.Wrapf(err, "failed to get source database ID for %s", backupItem.SourceTable.Database)
}
_, _, targetDatabase, err := common.GetDatabaseResourceName(backupItem.TargetTable.Database)
if err != nil {
return "", errors.Wrapf(err, "failed to get target database ID for %s", backupItem.TargetTable.Database)
}
if rCtx.GetDatabaseMetadataFunc == nil {
return "", errors.Errorf("GetDatabaseMetadataFunc is required")
}
_, metadata, err := rCtx.GetDatabaseMetadataFunc(ctx, rCtx.InstanceID, sourceDatabase)
if err != nil {
return "", errors.Wrapf(err, "failed to get database metadata for %s", sourceDatabase)
}
if metadata == nil {
return "", errors.Errorf("database metadata is nil")
}
schema := backupItem.SourceTable.Schema
if schema == "" {
schema = "dbo"
}
schemaMetadata := metadata.GetSchemaMetadata(schema)
if schemaMetadata == nil {
return "", errors.Errorf("schema metadata not found for %s", schema)
}
tableMetadata := schemaMetadata.GetTable(backupItem.SourceTable.Table)
if tableMetadata == nil {
return "", errors.Errorf("table metadata not found for %s.%s", schema, backupItem.SourceTable.Table)
}
g := &generator{
ctx: ctx,
rCtx: rCtx,
backupDatabase: targetDatabase,
backupTable: backupItem.TargetTable.Table,
originalDatabase: sourceDatabase,
originalSchema: schema,
originalTable: backupItem.SourceTable.Table,
pk: tableMetadata.GetPrimaryKey(),
table: tableMetadata,
}
result, err := g.generate(node)
if err != nil {
return "", err
}
return fmt.Sprintf("/*\nOriginal SQL:\n%s\n*/\n%s", sqlForComment, result), nil
}
type generator struct {
ctx context.Context
rCtx base.RestoreContext
backupDatabase string
backupTable string
originalDatabase string
originalSchema string
originalTable string
pk *model.IndexMetadata
table *model.TableMetadata
}
// hasIdentityColumn checks if the table has any IDENTITY columns
func (g *generator) hasIdentityColumn() bool {
if g.table == nil {
return false
}
for _, col := range g.table.GetProto().GetColumns() {
if col.IsIdentity {
return true
}
}
return false
}
func (g *generator) restorableColumns() []string {
var columns []string
for _, column := range g.table.GetProto().GetColumns() {
if column.GetGeneration() != nil {
continue
}
columns = append(columns, column.Name)
}
return columns
}
func quoteTSQLColumns(columns []string) string {
var quotedColumns []string
for _, column := range columns {
quotedColumns = append(quotedColumns, fmt.Sprintf("[%s]", column))
}
return strings.Join(quotedColumns, ", ")
}
func (g *generator) generate(node ast.Node) (string, error) {
switch n := node.(type) {
case *ast.DeleteStmt:
return g.generateDelete()
case *ast.UpdateStmt:
return g.generateUpdate(n)
default:
return "", errors.Errorf("unsupported restore statement %T", node)
}
}
func (g *generator) generateDelete() (string, error) {
// Check if the table has IDENTITY columns
hasIdentity := g.hasIdentityColumn()
columnList := quoteTSQLColumns(g.restorableColumns())
if hasIdentity {
// For tables with IDENTITY columns, we need to enable IDENTITY_INSERT
// and use explicit column lists
var buf strings.Builder
fmt.Fprintf(&buf, "SET IDENTITY_INSERT [%s].[%s].[%s] ON;\n",
g.originalDatabase, g.originalSchema, g.originalTable)
fmt.Fprintf(&buf, "INSERT INTO [%s].[%s].[%s] (%s) SELECT %s FROM [%s].[dbo].[%s];\n",
g.originalDatabase, g.originalSchema, g.originalTable,
columnList,
columnList,
g.backupDatabase, g.backupTable)
fmt.Fprintf(&buf, "SET IDENTITY_INSERT [%s].[%s].[%s] OFF;\n",
g.originalDatabase, g.originalSchema, g.originalTable)
fmt.Fprintf(&buf, "EXEC('DBCC CHECKIDENT (''[%s].[%s].[%s]'', RESEED)');",
g.originalDatabase, g.originalSchema, g.originalTable)
return buf.String(), nil
}
// Simple INSERT for tables without IDENTITY columns
return fmt.Sprintf(`INSERT INTO [%s].[%s].[%s] (%s) SELECT %s FROM [%s].[dbo].[%s];`,
g.originalDatabase, g.originalSchema, g.originalTable,
columnList,
columnList,
g.backupDatabase, g.backupTable), nil
}
func disjoint(a []string, b map[string]bool) bool {
for _, item := range a {
if _, ok := b[item]; ok {
return false
}
}
return true
}
func (g *generator) findDisjointUniqueKey(updateColumns []string) ([]string, error) {
columnMap := make(map[string]bool)
for _, column := range updateColumns {
columnMap[column] = true
}
if g.pk != nil {
if disjoint(g.pk.GetProto().Expressions, columnMap) {
return g.pk.GetProto().Expressions, nil
}
}
for _, index := range g.table.GetProto().Indexes {
if index.Primary {
continue
}
if !index.Unique {
continue
}
if disjoint(index.Expressions, columnMap) {
return index.Expressions, nil
}
}
return nil, errors.Errorf("no disjoint unique key found for %s.%s.%s", g.originalDatabase, g.originalSchema, g.originalTable)
}
func (g *generator) generateUpdate(stmt *ast.UpdateStmt) (string, error) {
updateColumns := updateSetColumns(stmt.SetClause)
uk, err := g.findDisjointUniqueKey(updateColumns)
if err != nil {
return "", err
}
var buf strings.Builder
// Check if the table has IDENTITY columns
hasIdentity := g.hasIdentityColumn()
if hasIdentity {
// Enable IDENTITY_INSERT for MERGE statement
if _, err := fmt.Fprintf(&buf, "SET IDENTITY_INSERT [%s].[%s].[%s] ON;\n",
g.originalDatabase, g.originalSchema, g.originalTable); err != nil {
return "", err
}
}
if _, err := fmt.Fprintf(&buf, "MERGE INTO [%s].[%s].[%s] AS t\nUSING [%s].[dbo].[%s] AS b\n ON", g.originalDatabase, g.originalSchema, g.originalTable, g.backupDatabase, g.backupTable); err != nil {
return "", err
}
for i, column := range uk {
if i > 0 {
if _, err := fmt.Fprint(&buf, " AND"); err != nil {
return "", err
}
}
if _, err := fmt.Fprintf(&buf, " t.[%s] = b.[%s]", column, column); err != nil {
return "", err
}
}
if _, err := fmt.Fprint(&buf, "\nWHEN MATCHED THEN\n UPDATE SET"); err != nil {
return "", err
}
for i, field := range updateColumns {
if i > 0 {
if _, err := fmt.Fprint(&buf, ","); err != nil {
return "", err
}
}
if _, err := fmt.Fprintf(&buf, " t.[%s] = b.[%s]", field, field); err != nil {
return "", err
}
}
if _, err := fmt.Fprint(&buf, "\nWHEN NOT MATCHED THEN\n INSERT ("); err != nil {
return "", err
}
for i, column := range g.restorableColumns() {
if i > 0 {
if _, err := fmt.Fprint(&buf, ", "); err != nil {
return "", err
}
}
if _, err := fmt.Fprintf(&buf, "[%s]", column); err != nil {
return "", err
}
}
if _, err := fmt.Fprint(&buf, ") VALUES ("); err != nil {
return "", err
}
for i, column := range g.restorableColumns() {
if i > 0 {
if _, err := fmt.Fprint(&buf, ", "); err != nil {
return "", err
}
}
if _, err := fmt.Fprintf(&buf, "b.[%s]", column); err != nil {
return "", err
}
}
if _, err := fmt.Fprint(&buf, ");"); err != nil {
return "", err
}
// Check if we need to disable IDENTITY_INSERT and reseed
if hasIdentity {
if _, err := fmt.Fprintf(&buf, "\n\nSET IDENTITY_INSERT [%s].[%s].[%s] OFF;\nEXEC('DBCC CHECKIDENT (''[%s].[%s].[%s]'', RESEED)');",
g.originalDatabase, g.originalSchema, g.originalTable,
g.originalDatabase, g.originalSchema, g.originalTable); err != nil {
return "", err
}
}
return buf.String(), nil
}
func updateSetColumns(setClause *ast.List) []string {
if setClause == nil {
return nil
}
var columns []string
for _, item := range setClause.Items {
setExpr, ok := item.(*ast.SetExpr)
if !ok {
continue
}
if setExpr.Column != nil {
columns = append(columns, setExpr.Column.Column)
continue
}
if setExpr.VarColumn != nil {
columns = append(columns, setExpr.VarColumn.Column)
continue
}
if setExpr.Variable != "" {
if column := updateColumnFromDualAssignment(setExpr.Value); column != "" {
columns = append(columns, column)
}
}
}
return columns
}
func updateColumnFromDualAssignment(expr ast.ExprNode) string {
switch e := expr.(type) {
case *ast.BinaryExpr:
if e.Op == ast.BinOpEq {
return updateColumnFromExpr(e.Left)
}
case *ast.ParenExpr:
return updateColumnFromDualAssignment(e.Expr)
default:
}
return ""
}
func updateColumnFromExpr(expr ast.ExprNode) string {
switch e := expr.(type) {
case *ast.ColumnRef:
return e.Column
case *ast.ParenExpr:
return updateColumnFromExpr(e.Expr)
default:
}
return ""
}
func extractStatement(statement string, backupItem *storepb.PriorBackupDetail_Item) (string, error) {
if backupItem == nil {
return "", errors.Errorf("backup item is nil")
}
parseResults, err := parseTSQLStatements(statement)
if err != nil {
return "", errors.Wrapf(err, "failed to parse statement")
}
var originalSQL []string
for _, parsedStatement := range parseResults {
node, ok := GetOmniNode(parsedStatement.AST)
if !ok || node == nil || !isRestorableDML(node) {
continue
}
start, end := statementPositions(parsedStatement.Start, parsedStatement.Text, dmlNodeLoc(node))
if !inRange(start, end, backupItem.StartPosition, backupItem.EndPosition) {
continue
}
sql := strings.TrimSuffix(sourceFromLoc(parsedStatement.Text, dmlNodeLoc(node)), ";")
originalSQL = append(originalSQL, sql)
}
if len(originalSQL) == 0 {
return "", nil
}
// Join with semicolons and add a trailing semicolon
return strings.Join(originalSQL, ";\n") + ";", nil
}
func isRestorableDML(node ast.Node) bool {
switch node.(type) {
case *ast.UpdateStmt, *ast.DeleteStmt:
return true
default:
return false
}
}
func statementPositions(start *storepb.Position, source string, loc ast.Loc) (*storepb.Position, *storepb.Position) {
return positionFromByteOffset(start, source, loc.Start), positionFromByteOffset(start, source, dmlEndOffset(source, loc))
}
// inRange checks if a statement's position range is within the target range.
func inRange(start, end, targetStart, targetEnd *storepb.Position) bool {
if start.Line < targetStart.Line || (start.Line == targetStart.Line && start.Column < targetStart.Column) {
return false
}
if end.Line > targetEnd.Line || (end.Line == targetEnd.Line && end.Column > targetEnd.Column) {
return false
}
return true
}