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 mysql
// Unit tests for formatViewBodySDL, the whitespace-only pretty-printer the SDL dump
// applies to MySQL's one-line stored view bodies (writeViewSDL).
//
// Golden corpus: testdata/format_view_body/<case>.stored.sql holds a REAL SHOW CREATE
// VIEW line captured from a live oracle (8.0.32 / 5.7.25 — sakila, the stock sys
// schema, and hand-built edge views), and <case>.golden.sql pins the pretty body the
// dump emits for it (after stripViewBodyDatabaseQualifier, exactly like writeViewSDL).
//
// Properties (each golden case + adversarial inline bodies):
// - token preservation: the quote/identifier-aware token stream of the output is
// byte-identical to the input's — the transformation inserts/normalizes whitespace
// BETWEEN tokens only, never inside a literal/identifier and never changing a token;
// - idempotence: formatViewBodySDL(formatViewBodySDL(x)) == formatViewBodySDL(x)
// (the A6/A8 dump-stability guards need same-input → same-bytes);
// - quoted tokens containing keyword lookalikes (' from ', 'union all', aliases with
// parens/commas/quotes) survive byte-for-byte.
import (
"os"
"path/filepath"
"regexp"
"strings"
"testing"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/stretchr/testify/require"
"github.com/bytebase/bytebase/backend/plugin/schema"
)
// fmtViewStoredLineMatcher mirrors the MySQL driver's viewDefMatcher
// (backend/plugin/db/mysql/sync.go): it extracts the database name and the stored body
// from a SHOW CREATE VIEW line, so the golden tests replay the exact production
// pipeline (sync regex → stripViewBodyDatabaseQualifier → formatViewBodySDL).
var fmtViewStoredLineMatcher = regexp.MustCompile("CREATE ALGORITHM=(UNDEFINED|MERGE|TEMPTABLE) DEFINER=`([^`]+)`@`([^`]+)` SQL SECURITY (DEFINER|INVOKER) VIEW `([^`]+)`(?:\\.`([^`]+)`)?( \\(`(?:[^`]|``)+`(?:, ?`(?:[^`]|``)+`)*\\))? AS (?P<def>.+)")
// loadStoredViewCase reads one .stored.sql capture and returns (db, storedBody).
func loadStoredViewCase(t *testing.T, path string) (string, string) {
t.Helper()
raw, err := os.ReadFile(path)
require.NoError(t, err)
line := strings.TrimRight(string(raw), "\n")
m := fmtViewStoredLineMatcher.FindStringSubmatch(line)
require.NotNil(t, m, "capture %s does not match the SHOW CREATE VIEW shape", path)
return m[5], m[len(m)-1]
}
// viewBodyTokens splits a body into its quote/identifier-aware token stream using the
// same scanner the formatter uses; whitespace between tokens is discarded, whitespace
// INSIDE string literals and backtick identifiers is part of the token.
func viewBodyTokens(body string) []string {
var toks []string
for i := 0; i < len(body); {
switch body[i] {
case ' ', '\t', '\n', '\r':
i++
continue
default:
}
end := scanViewBodyToken(body, i)
toks = append(toks, body[i:end])
i = end
}
return toks
}
// requireFormatProperties asserts the invariants every formatted body must satisfy.
func requireFormatProperties(t *testing.T, body string) string {
t.Helper()
got := formatViewBodySDL(body)
require.Equal(t, viewBodyTokens(body), viewBodyTokens(got),
"token stream must be preserved (whitespace-only transformation)")
require.Equal(t, got, formatViewBodySDL(got), "formatViewBodySDL must be idempotent")
require.Equal(t, got, formatViewBodySDL(body), "formatViewBodySDL must be deterministic")
return got
}
func TestFormatViewBodySDLGolden(t *testing.T) {
stored, err := filepath.Glob(filepath.Join("testdata", "format_view_body", "*.stored.sql"))
require.NoError(t, err)
require.NotEmpty(t, stored, "no golden captures found")
for _, path := range stored {
name := strings.TrimSuffix(filepath.Base(path), ".stored.sql")
t.Run(name, func(t *testing.T) {
db, def := loadStoredViewCase(t, path)
body := stripViewBodyDatabaseQualifier(def, db)
golden, err := os.ReadFile(filepath.Join("testdata", "format_view_body", name+".golden.sql"))
require.NoError(t, err)
got := requireFormatProperties(t, body)
require.Equal(t, strings.TrimRight(string(golden), "\n"), got)
})
}
}
func TestFormatViewBodySDLAdversarial(t *testing.T) {
cases := []struct {
name string
body string
// keep are byte sequences (quoted tokens with keyword lookalikes) that must
// survive verbatim.
keep []string
}{
{
name: "literal_with_from_and_union",
body: "select 'a from b' AS `x`,`t`.`b` AS `y` from `t` where (`t`.`b` <> 'union all')",
keep: []string{"'a from b'", "'union all'"},
},
{
name: "doubled_quote_escape",
body: "select 'it''s from x' AS `x` from `t`",
keep: []string{"'it''s from x'"},
},
{
name: "backslash_escape",
body: `select 'x\' from y' AS ` + "`x` from `t`",
keep: []string{`'x\' from y'`},
},
{
name: "identifier_named_from",
body: "select 1 AS ` from ` from `t`",
keep: []string{"` from `"},
},
{
name: "identifier_with_doubled_backtick",
body: "select `a``b`.`c` AS `x` from `a``b`",
keep: []string{"`a``b`"},
},
{
name: "alias_with_parens_quotes_commas",
body: "select count(0) AS `COUNT(*)`,concat('INDEX (',`t`.`b`,')') AS `CONCAT('INDEX (', INDEX_TYPE, ')')` from `t`",
keep: []string{"`COUNT(*)`", "`CONCAT('INDEX (', INDEX_TYPE, ')')`"},
},
{
name: "double_quoted_string",
body: `select "z leFT jOin q" AS ` + "`x` from `t`",
keep: []string{`"z leFT jOin q"`},
},
{
name: "unterminated_literal_tail",
body: "select 'abc from `t`",
keep: []string{"'abc from `t`"},
},
{
name: "unbalanced_parens",
body: "select concat(`t`.`b` AS `x` from `t`",
keep: nil,
},
{
name: "already_pretty_input_normalizes",
body: "select\n 1 AS `x`,\n 2 AS `y`\nfrom `t`",
keep: nil,
},
{
name: "weird_run_whitespace",
body: "select \t 1 AS `x` , 2 AS `y` from `t`",
keep: nil,
},
{
name: "empty",
body: "",
keep: nil,
},
}
for _, tc := range cases {
t.Run(tc.name, func(t *testing.T) {
got := requireFormatProperties(t, tc.body)
for _, k := range tc.keep {
require.Contains(t, got, k, "quoted token must survive verbatim")
}
})
}
}
// TestFormatViewBodySDLShapes pins the layout rules on small hand-written bodies where
// the whole output is easy to eyeball (the golden corpus covers the at-scale shapes).
func TestFormatViewBodySDLShapes(t *testing.T) {
cases := []struct {
name string
body string
want string
}{
{
name: "minimal",
body: "select 1 AS `one`",
want: "select\n 1 AS `one`",
},
{
name: "distinct_stays_on_select_line",
body: "select distinct `t`.`b` AS `b` from `t`",
want: "select distinct\n `t`.`b` AS `b`\nfrom `t`",
},
{
name: "join_tree",
body: "select `a`.`x` AS `x` from ((`a` join `b` on((`a`.`i` = `b`.`i`))) left join `c` on((`b`.`j` = `c`.`j`)))",
want: "select\n `a`.`x` AS `x`\nfrom ((`a`\n join `b` on((`a`.`i` = `b`.`i`)))\n left join `c` on((`b`.`j` = `c`.`j`)))",
},
{
name: "left_outer_join_stays_glued",
body: "select `a`.`x` AS `x` from (`a` left outer join `b` on((`a`.`i` = `b`.`i`)))",
want: "select\n `a`.`x` AS `x`\nfrom (`a`\n left outer join `b` on((`a`.`i` = `b`.`i`)))",
},
{
name: "subquery_stays_inline",
body: "select `a`.`x` AS `x`,(select max(`b`.`y`) from `b` where (`b`.`i` = `a`.`i`)) AS `m` from `a` group by `a`.`x`,`a`.`z` order by `a`.`x` desc limit 5",
want: "select\n `a`.`x` AS `x`,\n (select max(`b`.`y`) from `b` where (`b`.`i` = `a`.`i`)) AS `m`\nfrom `a`\ngroup by `a`.`x`,`a`.`z`\norder by `a`.`x` desc\nlimit 5",
},
{
name: "join_inside_derived_table_stays_inline",
body: "select `dt`.`x` AS `x` from (select `a`.`x` AS `x` from (`a` join `b` on((`a`.`i` = `b`.`i`)))) `dt`",
want: "select\n `dt`.`x` AS `x`\nfrom (select `a`.`x` AS `x` from (`a` join `b` on((`a`.`i` = `b`.`i`)))) `dt`",
},
{
name: "union_members_each_break",
body: "select `a`.`x` AS `x` from `a` union all select `b`.`x` AS `x` from `b` order by `x`",
want: "select\n `a`.`x` AS `x`\nfrom `a`\nunion all\nselect\n `b`.`x` AS `x`\nfrom `b`\norder by `x`",
},
{
name: "group_concat_order_by_stays_inline",
body: "select group_concat(`t`.`b` order by `t`.`b` ASC separator ', ') AS `g` from `t`",
want: "select\n group_concat(`t`.`b` order by `t`.`b` ASC separator ', ') AS `g`\nfrom `t`",
},
}
for _, tc := range cases {
t.Run(tc.name, func(t *testing.T) {
got := requireFormatProperties(t, tc.body)
require.Equal(t, tc.want, got)
})
}
}
// TestWriteViewSDLPretty pins the full writeViewSDL statement framing around the pretty
// body: header, AS on the header line, body from column 0, terminating ";\n\n".
func TestWriteViewSDLPretty(t *testing.T) {
db, def := loadStoredViewCase(t, filepath.Join("testdata", "format_view_body", "film_list_80.stored.sql"))
golden, err := os.ReadFile(filepath.Join("testdata", "format_view_body", "film_list_80.golden.sql"))
require.NoError(t, err)
var buf strings.Builder
require.NoError(t, writeViewSDL(&buf, db, &metadatapb.ViewMetadata{Name: "film_list", Definition: def}))
want := "CREATE OR REPLACE VIEW `film_list` AS\n" + strings.TrimRight(string(golden), "\n") + ";\n\n"
require.Equal(t, want, buf.String())
}
// TestMultiFileViewSDLPretty proves the multi-file export emits the identical pretty
// view content as the single-file dump (both share writeViewSDL — the multi-file ≡
// single-file concat invariant the live suite checks at scale).
func TestMultiFileViewSDLPretty(t *testing.T) {
meta := &metadatapb.DatabaseSchemaMetadata{
Name: "testdb",
Schemas: []*metadatapb.SchemaMetadata{{
Views: []*metadatapb.ViewMetadata{{
Name: "v_pretty",
Definition: "select `testdb`.`t1`.`a` AS `a` from `testdb`.`t1`",
}},
}},
}
res, err := GetMultiFileDatabaseDefinition(schema.GetDefinitionContext{}, meta)
require.NoError(t, err)
require.Len(t, res.Files, 1)
require.Equal(t, "views/v_pretty.sql", res.Files[0].Name)
require.Equal(t, "CREATE OR REPLACE VIEW `v_pretty` AS\nselect\n `t1`.`a` AS `a`\nfrom `t1`;\n\n", res.Files[0].Content)
single, err := getSDLFormat(meta)
require.NoError(t, err)
require.Contains(t, single, res.Files[0].Content, "single-file dump must contain the identical view slice")
}
// TestGetDatabaseMetadataParsesPrettyView proves the SDL→metadata parsers accept the
// pretty-printed view statement: the omni catalog path (GetDatabaseMetadata, the
// registered production entry) parses a writeViewSDL-produced multi-line
// CREATE OR REPLACE VIEW.
func TestGetDatabaseMetadataParsesPrettyView(t *testing.T) {
var buf strings.Builder
buf.WriteString("CREATE TABLE `t1` (\n `a` int NOT NULL,\n `b` varchar(10) DEFAULT NULL,\n PRIMARY KEY (`a`)\n) ENGINE=InnoDB;\n\n")
require.NoError(t, writeViewSDL(&buf, "testdb", &metadatapb.ViewMetadata{
Name: "v_pretty",
Definition: "select `testdb`.`t1`.`a` AS `a`,`testdb`.`t1`.`b` AS `b` from `testdb`.`t1` where (`testdb`.`t1`.`a` > 0)",
}))
sdl := buf.String()
require.Contains(t, sdl, "AS\nselect\n `t1`.`a` AS `a`,", "fixture must actually be pretty-printed")
t.Run("omni", func(t *testing.T) {
meta, err := GetDatabaseMetadata(sdl)
require.NoError(t, err)
require.Len(t, meta.Schemas, 1)
require.Len(t, meta.Schemas[0].Views, 1)
view := meta.Schemas[0].Views[0]
require.Equal(t, "v_pretty", view.Name)
require.NotContains(t, view.Definition, "\n",
"omni deparse must canonicalize the pretty body back to one line (whitespace washes out)")
})
}
// TestFormatViewBodySDLFailSafe pins the runtime guard (X8). NOTE: live probes against
// 5.7.25 and 8.0.32 show the server ALWAYS re-prints stored view bodies in the
// backslash-escaped canonical form (even for views created under NO_BACKSLASH_ESCAPES),
// so no driver-synced body can trip the scanner today — the guard is defense-in-depth
// for bodies of other origins and for future scanner gaps.
func TestFormatViewBodySDLFailSafe(t *testing.T) {
t.Run("no_backslash_escapes_style_body_left_unformatted", func(t *testing.T) {
// Read under NO_BACKSLASH_ESCAPES semantics this is: literal 'a\' (trailing
// backslash), then literal ' keep me '. The backslash-aware scanner instead
// swallows \' as an escape, mis-tokenizes the second literal's interior as bare
// words, and the unchecked formatter would collapse its double spaces — data
// corruption. The trailing mis-scan leaves an unterminated literal, which the
// guard detects: the body must come back byte-identical.
body := `select 'a\', ' keep me ' AS s from t`
require.NotEqual(t, body, formatViewBodyUnchecked(body),
"precondition: the unchecked formatter must actually corrupt this body, else the case is vacuous")
require.Equal(t, body, formatViewBodySDL(body))
})
t.Run("malformed_unterminated_literal_left_unformatted", func(t *testing.T) {
body := "select 'unterminated from t"
require.Equal(t, body, formatViewBodySDL(body))
})
t.Run("canonical_backslash_escaped_literal_still_formats", func(t *testing.T) {
// The stored-canonical form ('x\'y', as SHOW CREATE prints) must keep
// formatting — the guard must not regress legitimate bodies.
body := `select 'x\'y' AS a,'z"q' AS b from t`
got := requireFormatProperties(t, body)
require.Equal(t, "select\n 'x\\'y' AS a,\n 'z\"q' AS b\nfrom t", got)
})
t.Run("normal_body_still_formats", func(t *testing.T) {
got := requireFormatProperties(t, "select `a`.`x` AS `x` from `a`")
require.Equal(t, "select\n `a`.`x` AS `x`\nfrom `a`", got)
})
}