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 starrocks
import (
"context"
"database/sql"
"fmt"
"io"
"log/slog"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/go-sql-driver/mysql"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/common"
)
const (
// MySQL error code for "Unknown table".
errUnknownTable = 1051
)
// Dump and restore.
const (
tableStmtFmt = "" +
"--\n" +
"-- Table structure for `%s`\n" +
"--\n" +
"%s;\n"
viewStmtFmt = "" +
"--\n" +
"-- View structure for `%s`\n" +
"--\n" +
"%s;\n"
materializedViewStmtFmt = "" +
"--\n" +
"-- Materialized view structure for `%s`\n" +
"--\n" +
"%s;\n"
tempViewStmtFmt = "" +
"--\n" +
"-- Temporary view structure for `%s`\n" +
"--\n" +
"%s\n"
tempMaterializedViewStmtFmt = "" +
"--\n" +
"-- Temporary view structure for materialized view `%s`\n" +
"--\n" +
"%s\n"
)
// Dump dumps the database.
func (d *Driver) Dump(_ context.Context, out io.Writer, _ *metadatapb.DatabaseSchemaMetadata) error {
slog.Debug("begin to dump database", slog.String("database", d.databaseName))
db := d.GetDB()
database := d.databaseName
// mysqldump brackets a dump with UNIQUE_CHECKS/FOREIGN_KEY_CHECKS guards. StarRocks has
// neither session variable and does not enforce foreign keys, so emitting them makes the
// dump fail to replay with "Unknown system variable 'UNIQUE_CHECKS'".
// Table and view statement.
// We have to dump the table before views because of the structure dependency.
tables, err := getTables(db, database)
if err != nil {
return errors.Wrapf(err, "failed to get tables of database %q", database)
}
// Construct temporal views and materialized views.
// Create a temporary view with the same name as the view and with columns of
// the same name in order to satisfy views that depend on this view.
// This temporary view will be removed when the actual view is created.
// The properties of each column, are not preserved in this temporary
// view. They are not necessary because other views only need to reference
// the column name, thus we generate SELECT 1 AS colName1, 1 AS colName2.
// This will not be necessary once we can determine dependencies
// between views and can simply dump them in the appropriate order.
// https://sourcegraph.com/github.com/mysql/mysql-server/-/blob/client/mysqldump.cc?L2781
for _, tbl := range tables {
if tbl.TableType != viewTableType && tbl.TableType != materializedViewType {
continue
}
if tbl.InvalidView != "" {
// We will write the invalid view error string to schema.
stmtFmt := viewStmtFmt
if tbl.TableType == materializedViewType {
stmtFmt = materializedViewStmtFmt
}
if _, err := io.WriteString(out, fmt.Sprintf("%s\n", fmt.Sprintf(stmtFmt, tbl.Name, fmt.Sprintf("-- %s", tbl.InvalidView)))); err != nil {
return err
}
} else {
tempView := getTemporaryView(tbl.Name, tbl.ViewColumns)
if tbl.TableType == materializedViewType {
tempView = getTemporaryMaterializedView(tbl.Name, tbl.ViewColumns)
}
if _, err := io.WriteString(out, fmt.Sprintf("%s\n", tempView)); err != nil {
return err
}
}
}
// Construct tables.
for _, tbl := range tables {
if tbl.TableType == viewTableType || tbl.TableType == materializedViewType {
continue
}
if _, err := io.WriteString(out, fmt.Sprintf("%s\n", tbl.Statement)); err != nil {
return err
}
}
// Construct final views, then materialized views (see finalEmitOrder: an MV is
// materialized from its sources at CREATE time, so its source views must be real
// first, not the temporary SELECT 1 placeholders).
for _, tbl := range finalEmitOrder(tables) {
// The temporary placeholder created above is always a regular view (even for
// materialized views — see getTemporaryMaterializedView), so it must be dropped
// with DROP VIEW. DROP MATERIALIZED VIEW would not match the placeholder, leaving
// it in the shared namespace so the real CREATE MATERIALIZED VIEW below collides.
if _, err := io.WriteString(out, dropPlaceholderStmt(tbl.Name)); err != nil {
return err
}
if _, err := io.WriteString(out, fmt.Sprintf("%s\n", tbl.Statement)); err != nil {
return err
}
}
return nil
}
// finalEmitOrder returns the views and materialized views in the order their real
// definitions must be emitted: all regular views first, then materialized views, each
// group keeping its original relative order. information_schema.tables is unordered, and
// a materialized view can be defined on a regular view and is materialized from its
// sources at CREATE time, so its source views must already be real (not the temporary
// SELECT 1 placeholders). Base tables are emitted separately and are excluded here.
//
// MVs keep their incoming order relative to each other, so a materialized view built on
// another materialized view (nested async MVs) is not yet dependency-ordered and could
// restore inactive if listed before its parent — tracked in BYT-9718.
func finalEmitOrder(tables []*TableSchema) []*TableSchema {
var views, materializedViews []*TableSchema
for _, tbl := range tables {
switch tbl.TableType {
case viewTableType:
views = append(views, tbl)
case materializedViewType:
materializedViews = append(materializedViews, tbl)
default:
// Base tables and any other types are emitted elsewhere; skip here.
}
}
return append(views, materializedViews...)
}
// dropPlaceholderStmt returns the statement that drops the temporary placeholder emitted
// for a view or materialized view before its real definition. The placeholder is always a
// regular view (see getTemporaryMaterializedView), so it is dropped with DROP VIEW
// regardless of the final object type.
func dropPlaceholderStmt(name string) string {
return fmt.Sprintf("DROP VIEW IF EXISTS `%s`;\n", name)
}
func getTemporaryView(name string, columns []string) string {
var parts []string
for _, col := range columns {
parts = append(parts, fmt.Sprintf("1 AS `%s`", col))
}
stmt := fmt.Sprintf("CREATE VIEW `%s` AS SELECT\n %s;\n", name, strings.Join(parts, ",\n "))
return fmt.Sprintf(tempViewStmtFmt, name, stmt)
}
func getTemporaryMaterializedView(name string, columns []string) string {
var parts []string
for _, col := range columns {
parts = append(parts, fmt.Sprintf("1 AS `%s`", col))
}
// For materialized views, we create a temporary regular view as a placeholder
// since the actual materialized view will be created later with the full definition.
stmt := fmt.Sprintf("CREATE VIEW `%s` AS SELECT\n %s;\n", name, strings.Join(parts, ",\n "))
return fmt.Sprintf(tempMaterializedViewStmtFmt, name, stmt)
}
// TableSchema describes the schema of a table or view.
type TableSchema struct {
Name string
TableType string
Statement string
ViewColumns []string
// InvalidView is the error message indicating an invalid view object.
InvalidView string
}
// getTables gets all tables of a database using the provided database instance.
func getTables(db *sql.DB, dbName string) ([]*TableSchema, error) {
var tables []*TableSchema
query := "SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?"
rows, err := db.Query(query, dbName)
if err != nil {
return nil, err
}
defer rows.Close()
columns, err := rows.Columns()
if err != nil {
return nil, err
}
for rows.Next() {
var tbl TableSchema
// Some versions add a third, unused storage-format column.
var unusedStorageFormat string
if len(columns) == 3 {
if err := rows.Scan(&tbl.Name, &tbl.TableType, &unusedStorageFormat); err != nil {
return nil, err
}
} else {
if err := rows.Scan(&tbl.Name, &tbl.TableType); err != nil {
return nil, err
}
}
tables = append(tables, &tbl)
}
if err := rows.Err(); err != nil {
return nil, err
}
// Materialized views need a separate catalog lookup and re-tagging so the dump emits
// SHOW CREATE MATERIALIZED VIEW for them, since StarRocks reports them as VIEW.
materializedViewNames, err := getMaterializedViewNames(db, dbName)
if err != nil {
return nil, err
}
markMaterializedViews(tables, materializedViewNames)
var result []*TableSchema
for _, tbl := range tables {
stmt, err := getTableStmt(db, dbName, tbl.Name, tbl.TableType)
if err != nil {
// Skip tables that were dropped between listing and fetching CREATE statement.
var mysqlErr *mysql.MySQLError
if errors.As(err, &mysqlErr) && mysqlErr.Number == errUnknownTable {
slog.Debug("table was dropped during dump, skipping",
slog.String("database", dbName),
slog.String("table", tbl.Name))
continue
}
return nil, errors.Wrapf(err, "failed to call getTableStmt(%q, %q, %q)", dbName, tbl.Name, tbl.TableType)
}
tbl.Statement = stmt
if tbl.TableType == viewTableType || tbl.TableType == materializedViewType {
viewColumns, err := getViewColumns(db, dbName, tbl.Name)
if err != nil {
tbl.InvalidView = err.Error()
} else {
tbl.ViewColumns = viewColumns
}
}
result = append(result, tbl)
}
return result, nil
}
// getMaterializedViewNames returns the set of materialized-view names in the database.
// The query is best-effort: an unsupported version logs and yields an empty set. Sync
// rollups (REFRESH_TYPE='ROLLUP') are excluded: they are rollup indexes on the base
// table, not standalone views.
func getMaterializedViewNames(db *sql.DB, dbName string) (map[string]bool, error) {
names := make(map[string]bool)
// IFNULL guards the bare-string scan below: database/sql errors on a NULL->string scan.
query := fmt.Sprintf(`SELECT TABLE_NAME, IFNULL(REFRESH_TYPE, '') FROM information_schema.materialized_views WHERE TABLE_SCHEMA = '%s'`, dbName)
rows, err := db.Query(query)
if err != nil {
// The catalog may be unavailable on older engine versions; log and continue.
slog.Debug("failed to query materialized views, might not be supported", slog.String("error", err.Error()))
return names, nil
}
defer rows.Close()
for rows.Next() {
var name, refreshType string
if err := rows.Scan(&name, &refreshType); err != nil {
return nil, err
}
if isSyncRollup(refreshType) {
continue
}
names[name] = true
}
if err := rows.Err(); err != nil {
return nil, err
}
return names, nil
}
// markMaterializedViews re-tags rows that are actually materialized views so the dump
// emits SHOW CREATE MATERIALIZED VIEW for them. StarRocks reports MVs as 'VIEW', which
// becomes 'MATERIALIZED VIEW' when the name is in the materialized-view set. Regular
// tables and views are left untouched.
func markMaterializedViews(tables []*TableSchema, materializedViewNames map[string]bool) {
for _, tbl := range tables {
if !materializedViewNames[tbl.Name] {
continue
}
if tbl.TableType == baseTableType || tbl.TableType == viewTableType {
tbl.TableType = materializedViewType
}
}
}
// getTableStmt gets the create statement of a table.
func getTableStmt(db *sql.DB, dbName, tblName, tblType string) (string, error) {
switch tblType {
case baseTableType:
query := fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`;", dbName, tblName)
var stmt, unused string
if err := db.QueryRow(query).Scan(&unused, &stmt); err != nil {
if err == sql.ErrNoRows {
return "", common.FormatDBErrorEmptyRowWithQuery(query)
}
return "", err
}
// Remove trailing semicolon if present, as the format string will add one
stmt = strings.TrimSuffix(stmt, ";")
return fmt.Sprintf(tableStmtFmt, tblName, stmt), nil
case viewTableType:
// This differs from mysqldump as it includes.
query := fmt.Sprintf("SHOW CREATE VIEW `%s`.`%s`;", dbName, tblName)
var createStmt, unused string
if err := db.QueryRow(query).Scan(&unused, &createStmt, &unused, &unused); err != nil {
if err == sql.ErrNoRows {
return "", common.FormatDBErrorEmptyRowWithQuery(query)
}
return "", err
}
// Remove trailing semicolon if present, as the format string will add one
createStmt = strings.TrimSuffix(createStmt, ";")
return fmt.Sprintf(viewStmtFmt, tblName, createStmt), nil
case materializedViewType:
// For materialized views we use SHOW CREATE MATERIALIZED VIEW.
// This command returns 2 columns: materialized view name and create statement.
query := fmt.Sprintf("SHOW CREATE MATERIALIZED VIEW `%s`.`%s`;", dbName, tblName)
var createStmt, unused string
if err := db.QueryRow(query).Scan(&unused, &createStmt); err != nil {
if err == sql.ErrNoRows {
return "", common.FormatDBErrorEmptyRowWithQuery(query)
}
return "", err
}
// Remove trailing semicolon if present, as the format string will add one
createStmt = strings.TrimSuffix(createStmt, ";")
return fmt.Sprintf(materializedViewStmtFmt, tblName, createStmt), nil
default:
slog.Debug("skip unsupported table type", slog.String("dbName", dbName), slog.String("tableName", tblName), slog.String("tableType", tblType))
return "", nil
}
}
// getViewColumns gets the create statement of a table.
func getViewColumns(db *sql.DB, dbName, tblName string) ([]string, error) {
query := fmt.Sprintf("SHOW COLUMNS FROM `%s`.`%s`;", dbName, tblName)
// https://dev.mysql.com/doc/refman/8.0/en/show-columns.html
// Field, Type, Null, Key, Default, Extra.
rows, err := db.Query(query)
if err != nil {
return nil, err
}
defer rows.Close()
var fields []string
for rows.Next() {
var field string
var unused sql.NullString
if err := rows.Scan(&field, &unused, &unused, &unused, &unused, &unused); err != nil {
return nil, err
}
fields = append(fields, field)
}
if err := rows.Err(); err != nil {
return nil, err
}
return fields, nil
}