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"
"io"
"os"
"slices"
"strings"
"testing"
"github.com/stretchr/testify/require"
"gopkg.in/yaml.v3"
"github.com/bytebase/bytebase/backend/common/yamltest"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
)
type rollbackCase struct {
Input string
Result []base.BackupStatement
}
func TestBackupRestoreDoNotDependOnANTLR(t *testing.T) {
for _, path := range []string{"backup.go", "restore.go"} {
content, err := os.ReadFile(path)
require.NoError(t, err)
source := string(content)
require.NotContains(t, source, "github.com/antlr4-go/antlr/v4", path)
require.NotContains(t, source, "github.com/bytebase/parser/tsql", path)
require.NotContains(t, source, "ParseTSQL(", path)
}
}
func TestBackupOmniBoundaryCases(t *testing.T) {
tests := []struct {
name string
input string
wantSQL string
wantSQLs []string
wantErrPart string
}{
{
name: "update top option",
input: "UPDATE TOP (3) test SET c1 = 1 WHERE c2 = 2 OPTION (RECOMPILE);",
wantSQL: strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* TOP (3) FROM test WHERE c2 = 2 OPTION (RECOMPILE)) AS backup_table;",
}, "\n"),
},
{
name: "where keyword in comment",
input: "UPDATE test SET c1 = 1 WHERE /* WHERE */ c2 = 2;",
wantSQL: strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test WHERE /* WHERE */ c2 = 2) AS backup_table;",
}, "\n"),
},
{
name: "option keyword in comment",
input: "UPDATE test SET c1 = 1 /* OPTION */ OPTION (RECOMPILE);",
wantSQL: strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test OPTION (RECOMPILE)) AS backup_table;",
}, "\n"),
},
{
name: "option keyword in nested comment",
input: "UPDATE test SET c1 = 1 /* outer /* inner */ OPTION in outer */ OPTION (RECOMPILE);",
wantSQL: strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test OPTION (RECOMPILE)) AS backup_table;",
}, "\n"),
},
{
name: "delete alias from join",
input: strings.Join([]string{
"DELETE FROM t_alias",
"FROM test AS t_alias JOIN test2 AS t2 ON t_alias.c1 = t2.c1",
"WHERE t_alias.c1 = 1;",
}, "\n"),
wantSQL: strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [t_alias].* FROM test AS t_alias JOIN test2 AS t2 ON t_alias.c1 = t2.c1 WHERE t_alias.c1 = 1) AS backup_table;",
}, "\n"),
},
{
name: "update current of rejected",
input: "UPDATE test SET c1 = 1 WHERE CURRENT OF my_cursor;",
wantErrPart: "CURSOR clause is not supported",
},
{
name: "update with cte",
input: "WITH c AS (SELECT id FROM src) UPDATE dbo.test SET c1 = 1 WHERE EXISTS (SELECT 1 FROM c WHERE c.id = test.id);",
wantSQL: strings.Join([]string{
"WITH c AS (SELECT id FROM src)",
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM dbo.test WHERE EXISTS (SELECT 1 FROM c WHERE c.id = test.id)) AS backup_table;",
}, "\n"),
},
{
name: "delete with cte",
input: "WITH c (id) AS (SELECT id FROM src), d AS (SELECT 1 AS n) DELETE FROM test WHERE id IN (SELECT id FROM c);",
wantSQL: strings.Join([]string{
"WITH c (id) AS (SELECT id FROM src), d AS (SELECT 1 AS n)",
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test WHERE id IN (SELECT id FROM c)) AS backup_table;",
}, "\n"),
},
{
name: "update joined to cte",
input: "WITH c AS (SELECT id FROM src) UPDATE t SET c1 = 1 FROM test AS t JOIN c ON t.id = c.id;",
wantSQL: strings.Join([]string{
"WITH c AS (SELECT id FROM src)",
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [t].* FROM test AS t JOIN c ON t.id = c.id) AS backup_table;",
}, "\n"),
},
{
name: "identical with clause across statements is hoisted once",
input: strings.Join([]string{
"WITH c AS (SELECT id FROM src) UPDATE test SET c1 = 1 WHERE id IN (SELECT id FROM c);",
"WITH c AS (SELECT id FROM src) UPDATE test SET c1 = 2 WHERE id IN (SELECT id FROM c);",
}, "\n"),
wantSQL: strings.Join([]string{
"WITH c AS (SELECT id FROM src)",
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test WHERE id IN (SELECT id FROM c)",
" UNION",
" SELECT [db].[dbo].[test].* FROM test WHERE id IN (SELECT id FROM c)) AS backup_table;",
}, "\n"),
},
{
name: "different with clauses on the same table rejected",
input: strings.Join([]string{
"WITH c AS (SELECT id FROM src) UPDATE test SET c1 = 1 WHERE id IN (SELECT id FROM c);",
"WITH c AS (SELECT id FROM src), d AS (SELECT id FROM other) UPDATE test SET c1 = 2 WHERE id IN (SELECT id FROM d);",
}, "\n"),
wantErrPart: "different WITH clauses on the same table",
},
{
name: "with clause would capture a base table name in another statement",
input: strings.Join([]string{
"WITH c AS (SELECT id FROM src) UPDATE test SET c1 = 1 WHERE id IN (SELECT id FROM c);",
"UPDATE test SET c1 = 2 WHERE id IN (SELECT id FROM c);",
}, "\n"),
wantErrPart: "different WITH clauses on the same table",
},
{
name: "with clause on another table is scoped to its own backup",
input: strings.Join([]string{
"WITH c AS (SELECT id FROM src) UPDATE test SET c1 = 1 WHERE id IN (SELECT id FROM c);",
"UPDATE c SET c1 = 2 WHERE id = 1;",
}, "\n"),
wantSQLs: []string{
strings.Join([]string{
"SELECT * INTO [backupDB].[dbo].[rollback_c_db] FROM (",
" SELECT [db].[dbo].[c].* FROM c WHERE id = 1) AS backup_table;",
}, "\n"),
strings.Join([]string{
"WITH c AS (SELECT id FROM src)",
"SELECT * INTO [backupDB].[dbo].[rollback_test_db] FROM (",
" SELECT [db].[dbo].[test].* FROM test WHERE id IN (SELECT id FROM c)) AS backup_table;",
}, "\n"),
},
},
{
name: "xmlnamespaces rejected",
input: "WITH XMLNAMESPACES ('uri' AS ns), c AS (SELECT id FROM src) UPDATE test SET c1 = 1 WHERE id IN (SELECT id FROM c);",
wantErrPart: "WITH XMLNAMESPACES",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{}, tc.input, "db", "backupDB", "rollback")
if tc.wantErrPart != "" {
require.Error(t, err)
require.Contains(t, err.Error(), tc.wantErrPart)
return
}
require.NoError(t, err)
if tc.wantSQLs != nil {
var got []string
for _, item := range result {
got = append(got, item.Statement)
}
slices.Sort(got)
require.Equal(t, tc.wantSQLs, got)
return
}
require.Len(t, result, 1)
require.Equal(t, tc.wantSQL, result[0].Statement)
})
}
}
func TestBackupRejectsCTETarget(t *testing.T) {
for name, input := range map[string]string{
"direct": "WITH c AS (SELECT id, c1 FROM test) UPDATE c SET c1 = 1 WHERE id = 1;",
"alias": "WITH c AS (SELECT id, c1 FROM test) UPDATE x SET c1 = 1 FROM c AS x WHERE x.id = 1;",
"delete": "WITH c AS (SELECT id FROM test) DELETE FROM c WHERE id = 1;",
"mixed case": "WITH C AS (SELECT id, c1 FROM test) UPDATE c SET c1 = 1 WHERE id = 1;",
"after other dml": "DELETE FROM test WHERE id = 2; WITH c AS (SELECT id FROM test) UPDATE c SET c1 = 1 WHERE id = 1;",
} {
t.Run(name, func(t *testing.T) {
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{}, input, "db", "backupDB", "rollback")
require.Error(t, err)
require.Contains(t, err.Error(), `does not support DML targeting CTE "c"`)
require.Empty(t, result)
})
}
// A schema-qualified target is a real table even when a CTE shares its name.
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{}, "WITH c AS (SELECT id FROM src) UPDATE dbo.c SET c1 = 1 WHERE id IN (SELECT id FROM c);", "db", "backupDB", "rollback")
require.NoError(t, err)
require.Len(t, result, 1)
require.Equal(t, "c", result[0].SourceTableName)
require.True(t, strings.HasPrefix(result[0].Statement, "WITH c AS (SELECT id FROM src)\n"), result[0].Statement)
// Under a case-sensitive collation, c and C are different identifiers.
caseSensitive := base.TransformContext{IsCaseSensitive: true}
result, err = TransformDMLToSelect(context.Background(), caseSensitive, "WITH C AS (SELECT id FROM src) UPDATE c SET c1 = 1 WHERE id IN (SELECT id FROM C);", "db", "backupDB", "rollback")
require.NoError(t, err)
require.Len(t, result, 1)
require.Equal(t, "c", result[0].SourceTableName)
require.Equal(t, strings.Join([]string{
"WITH C AS (SELECT id FROM src)",
"SELECT * INTO [backupDB].[dbo].[rollback_c_db] FROM (",
" SELECT [db].[dbo].[c].* FROM c WHERE id IN (SELECT id FROM C)) AS backup_table;",
}, "\n"), result[0].Statement)
_, err = TransformDMLToSelect(context.Background(), caseSensitive, "WITH c AS (SELECT id, c1 FROM test) UPDATE c SET c1 = 1 WHERE id = 1;", "db", "backupDB", "rollback")
require.Error(t, err)
require.Contains(t, err.Error(), `does not support DML targeting CTE "c"`)
}
func TestBackup(t *testing.T) {
tests := []rollbackCase{}
const (
record = false
)
var (
filepath = "test-data/test_backup.yaml"
)
a := require.New(t)
yamlFile, err := os.Open(filepath)
a.NoError(err)
byteValue, err := io.ReadAll(yamlFile)
a.NoError(yamlFile.Close())
a.NoError(err)
a.NoError(yaml.Unmarshal(byteValue, &tests))
for i, t := range tests {
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{}, t.Input, "db", "backupDB", "rollback")
a.NoError(err)
slices.SortFunc(result, func(a, b base.BackupStatement) int {
if a.TargetTableName == b.TargetTableName {
if a.Statement < b.Statement {
return -1
}
if a.Statement > b.Statement {
return 1
}
return 0
}
if a.TargetTableName < b.TargetTableName {
return -1
}
if a.TargetTableName > b.TargetTableName {
return 1
}
return 0
})
if record {
tests[i].Result = result
} else {
a.Equal(t.Result, result, t.Input)
}
}
if record {
yamltest.Record(t, filepath, tests)
}
}
// TestIdentityColumnHandling validates that our implementation correctly handles IDENTITY columns
// This test verifies:
// 1. The backup uses simple SELECT INTO (copying IDENTITY properties naturally)
// 2. The restore.go handles IDENTITY_INSERT during rollback
func TestIdentityColumnHandling(t *testing.T) {
a := require.New(t)
// Test case: DELETE from a table that typically has IDENTITY columns
input := `DELETE FROM positions WHERE position_id = 1;`
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{},
input, "db", "backupDB", "rollback")
a.NoError(err)
a.Len(result, 1)
// Verify the generated SQL uses simple SELECT INTO
stmt := result[0].Statement
// Key assertions about the generated SQL:
// 1. Uses simple SELECT INTO
a.Contains(stmt, "SELECT * INTO [backupDB].[dbo].[rollback_positions_db]")
// 2. Selects from the original table with proper bracketing
a.Contains(stmt, "SELECT [db].[dbo].[positions].* FROM")
// 3. Includes the WHERE clause
a.Contains(stmt, "WHERE position_id = 1")
// The approach:
// Backup: Simple SELECT * INTO backup_table (copies IDENTITY property naturally)
// Restore: restore.go handles IDENTITY_INSERT ON/OFF for rollback
}
// TestBackupStatementStructure validates the structure of backup statements
func TestBackupStatementStructure(t *testing.T) {
a := require.New(t)
// Test that UPDATE statements generate correct backup SQL
input := `UPDATE employees SET salary = salary * 1.1 WHERE department_id = 5;`
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{},
input, "production", "backup", "migration")
a.NoError(err)
a.Len(result, 1)
backupStmt := result[0]
// Verify backup statement metadata
a.Equal("dbo", backupStmt.SourceSchema)
a.Equal("employees", backupStmt.SourceTableName)
a.Equal("migration_employees_production", backupStmt.TargetTableName)
// Verify the SQL structure handles the WHERE clause properly
a.Contains(backupStmt.Statement, "WHERE department_id = 5")
}
// TestBackupWithQuotedStrings validates that single quotes in WHERE clauses are properly handled
func TestBackupWithQuotedStrings(t *testing.T) {
a := require.New(t)
// Test case with single quotes
input := `DELETE FROM AdminPosition WHERE positionName = 'BPM Admin';`
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{},
input, "TestIdentityDB", "bbdataarchive", "backup")
a.NoError(err)
a.Len(result, 1)
stmt := result[0].Statement
// With simple SELECT INTO, quotes are handled naturally by SQL Server
// The WHERE clause should appear as-is
a.Contains(stmt, "WHERE positionName = 'BPM Admin'")
// Also test with apostrophes in the string
input2 := `UPDATE positions SET title = 'O''Reilly''s Manager' WHERE id = 1;`
result2, err := TransformDMLToSelect(context.Background(), base.TransformContext{},
input2, "db", "backup", "rollback")
a.NoError(err)
a.Len(result2, 1)
// For UPDATE, we only backup the rows that will be changed (WHERE clause)
// The SET clause is not part of the backup, only the WHERE clause matters
stmt2 := result2[0].Statement
// The WHERE clause should be present
a.Contains(stmt2, "WHERE id = 1")
}
// TestBackupSkipsTempTableTargets validates BYT-9359: prior backup must skip
// any UPDATE/DELETE whose target table is a temp table (#name local, ##name
// global). Temp tables are session-scoped, so a backup query running in a
// separate session would fail with "Invalid object name". Skipping is the
// only sane outcome — the rows can't be reconstructed across sessions and
// have no rollback semantics anyway.
func TestBackupSkipsTempTableTargets(t *testing.T) {
tests := []struct {
name string
input string
wantLen int
wantTargetTable string // only checked when wantLen == 1
}{
{
name: "update local temp table only",
input: "UPDATE #tmp SET c1 = 1 WHERE c2 = 2;",
wantLen: 0,
},
{
name: "delete from local temp table only",
input: "DELETE FROM #tmp WHERE c1 = 1;",
wantLen: 0,
},
{
name: "update global temp table only",
input: "UPDATE ##gtmp SET c1 = 1 WHERE c2 = 2;",
wantLen: 0,
},
{
name: "mixed temp and real targets",
input: strings.Join([]string{
"UPDATE #tmp SET c1 = 1 WHERE c2 = 2;",
"UPDATE test SET c1 = 2 WHERE c1 = 1;",
}, "\n"),
wantLen: 1,
wantTargetTable: "rollback_test_db",
},
}
for _, tc := range tests {
t.Run(tc.name, func(t *testing.T) {
result, err := TransformDMLToSelect(context.Background(), base.TransformContext{}, tc.input, "db", "backupDB", "rollback")
require.NoError(t, err)
require.Len(t, result, tc.wantLen)
if tc.wantLen == 1 {
require.Equal(t, tc.wantTargetTable, result[0].TargetTableName)
}
})
}
}