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 tidb
import (
"context"
"database/sql"
"fmt"
"log/slog"
"regexp"
"slices"
"strconv"
"strings"
"time"
"github.com/blang/semver/v4"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/common"
"github.com/bytebase/bytebase/backend/common/log"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/db"
"github.com/bytebase/bytebase/backend/plugin/db/mysql"
"github.com/bytebase/bytebase/backend/plugin/db/util"
)
const (
autoIncrementSymbol = "AUTO_INCREMENT"
autoRandSymbol = "AUTO_RANDOM"
pkAutoRandomBitsSymbol = "PK_AUTO_RANDOM_BITS"
virtualGenerated = "VIRTUAL GENERATED"
storedGenerated = "STORED GENERATED"
)
var (
systemDatabases = map[string]bool{
"information_schema": true,
"mysql": true,
"performance_schema": true,
"sys": true,
// TiDB only
"metrics_schema": true,
}
systemDatabaseClause = func() string {
var l []string
for k := range systemDatabases {
l = append(l, fmt.Sprintf("'%s'", k))
}
return strings.Join(l, ", ")
}()
pkAutoRandomBitsRegex = regexp.MustCompile(`PK_AUTO_RANDOM_BITS=(\d+)`)
rangeBitsRegex = regexp.MustCompile(`RANGE BITS=(\d+)`)
)
// SyncInstance syncs the instance.
func (d *Driver) SyncInstance(ctx context.Context) (*db.InstanceMetadata, error) {
version, err := d.getVersion(ctx)
if err != nil {
return nil, err
}
var lowerCaseTableNames int32
lowerCaseTableNamesText, err := d.getServerVariable(ctx, "lower_case_table_names")
if err != nil {
slog.Debug("failed to get lower_case_table_names variable", log.BBError(err))
} else {
v, err := strconv.ParseInt(lowerCaseTableNamesText, 10, 32)
if err != nil {
slog.Debug("failed to parse lower_case_table_names variable", log.BBError(err))
} else {
lowerCaseTableNames = int32(v)
}
}
instanceRoles := d.getInstanceRoles(ctx)
// Query db info
where := fmt.Sprintf("LOWER(SCHEMA_NAME) NOT IN (%s)", systemDatabaseClause)
query := `
SELECT
SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE ` + where
rows, err := d.db.QueryContext(ctx, query)
if err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
defer rows.Close()
var databases []*metadatapb.DatabaseSchemaMetadata
for rows.Next() {
database := &metadatapb.DatabaseSchemaMetadata{}
if err := rows.Scan(
&database.Name,
&database.CharacterSet,
&database.Collation,
); err != nil {
return nil, err
}
databases = append(databases, database)
}
if err := rows.Err(); err != nil {
return nil, err
}
return &db.InstanceMetadata{
Version: version,
Databases: databases,
Metadata: &storepb.Instance{
MysqlLowerCaseTableNames: lowerCaseTableNames,
Roles: instanceRoles,
},
}, nil
}
func (d *Driver) getServerVariable(ctx context.Context, varName string) (string, error) {
db := d.GetDB()
query := fmt.Sprintf("SHOW VARIABLES LIKE '%s'", varName)
var varNameFound, value string
if err := db.QueryRowContext(ctx, query).Scan(&varNameFound, &value); err != nil {
if err == sql.ErrNoRows {
return "", common.FormatDBErrorEmptyRowWithQuery(query)
}
return "", util.FormatErrorWithQuery(err, query)
}
if varName != varNameFound {
return "", errors.Errorf("expecting variable %s, but got %s", varName, varNameFound)
}
return value, nil
}
// SyncDBSchema syncs a single database schema.
func (d *Driver) SyncDBSchema(ctx context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
schemaMetadata := &metadatapb.SchemaMetadata{
Name: "",
}
// Query TiDB version for feature gating.
version, err := d.getVersion(ctx)
if err != nil {
return nil, err
}
// Query index info.
indexMap := make(map[db.TableKey]map[string]*metadatapb.IndexMetadata)
indexQuery := `
SELECT
TABLE_NAME,
KEY_NAME,
COLUMN_NAME,
SEQ_IN_INDEX,
NON_UNIQUE,
INDEX_COMMENT,
SUB_PART,
EXPRESSION
FROM information_schema.TIDB_INDEXES
WHERE TABLE_SCHEMA = ?`
indexRows, err := d.db.QueryContext(ctx, indexQuery, d.databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, indexQuery)
}
defer indexRows.Close()
for indexRows.Next() {
var tableName, keyName, comment string
var columnName, expressionName sql.NullString
var position int
var nonUnique bool
var subPart sql.NullInt64
if err := indexRows.Scan(
&tableName,
&keyName,
&columnName,
&position,
&nonUnique,
&comment,
&subPart,
&expressionName,
); err != nil {
return nil, err
}
// Determine expression from column name or expression field
var expression string
if expressionName.Valid {
expression = fmt.Sprintf("(%s)", expressionName.String)
} else if columnName.Valid {
expression = columnName.String
}
key := db.TableKey{Schema: "", Table: tableName}
if _, ok := indexMap[key]; !ok {
indexMap[key] = make(map[string]*metadatapb.IndexMetadata)
}
if _, ok := indexMap[key][keyName]; !ok {
indexMap[key][keyName] = &metadatapb.IndexMetadata{
Name: keyName,
Type: "BTREE", // Default to BTREE as TiDB generally uses BTREE indexes
Unique: !nonUnique,
Primary: keyName == "PRIMARY",
Visible: true, // Visible is always true for TiDB indexes
Comment: comment,
}
}
// Add expression to index metadata
indexMap[key][keyName].Expressions = append(indexMap[key][keyName].Expressions, expression)
// Add key length to index metadata
if subPart.Valid {
indexMap[key][keyName].KeyLength = append(indexMap[key][keyName].KeyLength, subPart.Int64)
} else {
// -1 means no key length limit.
indexMap[key][keyName].KeyLength = append(indexMap[key][keyName].KeyLength, -1)
}
}
if err := indexRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, indexQuery)
}
// Query column info.
columnMap := make(map[db.TableKey][]*metadatapb.ColumnMetadata)
columnQuery := `
SELECT
TABLE_NAME,
IFNULL(COLUMN_NAME, ''),
ORDINAL_POSITION,
CASE WHEN COLUMN_DEFAULT is NULL THEN NULL ELSE QUOTE(COLUMN_DEFAULT) END,
IS_NULLABLE,
COLUMN_TYPE,
IFNULL(CHARACTER_SET_NAME, ''),
IFNULL(COLLATION_NAME, ''),
QUOTE(COLUMN_COMMENT),
GENERATION_EXPRESSION,
EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ?
ORDER BY TABLE_NAME, ORDINAL_POSITION`
columnRows, err := d.db.QueryContext(ctx, columnQuery, d.databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, columnQuery)
}
defer columnRows.Close()
for columnRows.Next() {
column := &metadatapb.ColumnMetadata{}
var tableName, nullable, extra string
var defaultStr, generationExpr sql.NullString
if err := columnRows.Scan(
&tableName,
&column.Name,
&column.Position,
&defaultStr,
&nullable,
&column.Type,
&column.CharacterSet,
&column.Collation,
&column.Comment,
&generationExpr,
&extra,
); err != nil {
return nil, err
}
// Quoted string has a single quote around it and is escaped by QUOTE().
column.Comment = stripSingleQuote(column.Comment)
nullableBool, err := util.ConvertYesNo(nullable)
if err != nil {
return nil, err
}
column.Nullable = nullableBool
setColumnMetadataDefault(column, defaultStr, nullableBool, extra)
// Handle generated columns
if generationExpr.Valid && generationExpr.String != "" {
if strings.Contains(strings.ToUpper(extra), virtualGenerated) {
column.Generation = &metadatapb.GenerationMetadata{
Type: metadatapb.GenerationMetadata_TYPE_VIRTUAL,
Expression: generationExpr.String,
}
} else if strings.Contains(strings.ToUpper(extra), storedGenerated) {
column.Generation = &metadatapb.GenerationMetadata{
Type: metadatapb.GenerationMetadata_TYPE_STORED,
Expression: generationExpr.String,
}
}
}
key := db.TableKey{Schema: "", Table: tableName}
columnMap[key] = append(columnMap[key], column)
}
if err := columnRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, columnQuery)
}
// Query view info.
viewMap := make(map[db.TableKey]*metadatapb.ViewMetadata)
viewQuery := `
SELECT
TABLE_NAME,
VIEW_DEFINITION
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = ?`
viewRows, err := d.db.QueryContext(ctx, viewQuery, d.databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, viewQuery)
}
defer viewRows.Close()
for viewRows.Next() {
view := &metadatapb.ViewMetadata{}
if err := viewRows.Scan(
&view.Name,
&view.Definition,
); err != nil {
return nil, err
}
key := db.TableKey{Schema: "", Table: view.Name}
view.Columns = columnMap[key]
// Note: TiDB/MySQL does not support view comments, so view.Comment remains empty
viewMap[key] = view
}
if err := viewRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, viewQuery)
}
// Query foreign key info.
foreignKeysMap, err := d.getForeignKeyList(ctx, d.databaseName)
if err != nil {
return nil, err
}
// Query check constraint info.
checkMap := make(map[db.TableKey][]*metadatapb.CheckConstraintMetadata)
if supportsCheckConstraintTable(version) {
checkMap, err = d.getCheckConstraintList(ctx, d.databaseName)
if err != nil {
return nil, err
}
}
// Query sequence info.
sequences, err := d.getSequenceList(ctx, d.databaseName)
if err != nil {
return nil, err
}
// Query partition info.
partitionTables, err := d.listPartitionTables(ctx, d.databaseName)
if err != nil {
return nil, err
}
// Query table info.
tableQuery := `
SELECT
TABLE_NAME,
TABLE_TYPE,
IFNULL(ENGINE, ''),
IFNULL(TABLE_COLLATION, ''),
IFNULL(TABLE_ROWS, 0),
IFNULL(DATA_LENGTH, 0),
IFNULL(INDEX_LENGTH, 0),
IFNULL(DATA_FREE, 0),
IFNULL(CREATE_OPTIONS, ''),
QUOTE(IFNULL(TABLE_COMMENT, '')),
IFNULL(TIDB_ROW_ID_SHARDING_INFO, ''),
IFNULL(TIDB_PK_TYPE, '')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ?
ORDER BY TABLE_NAME`
tableRows, err := d.db.QueryContext(ctx, tableQuery, d.databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, tableQuery)
}
defer tableRows.Close()
for tableRows.Next() {
var tableName, tableType, engine, collation, createOptions, comment, shardingInfo, pkType string
var rowCount, dataSize, indexSize, dataFree int64
// Workaround TiDB bug https://github.com/pingcap/tidb/issues/27970
var tableCollation sql.NullString
if err := tableRows.Scan(
&tableName,
&tableType,
&engine,
&collation,
&rowCount,
&dataSize,
&indexSize,
&dataFree,
&createOptions,
&comment,
&shardingInfo,
&pkType,
); err != nil {
return nil, err
}
// Quoted string has a single quote around it and is escaped by QUOTE().
comment = stripSingleQuote(comment)
key := db.TableKey{Schema: "", Table: tableName}
switch tableType {
case baseTableType:
columns := columnMap[key]
// Set auto random default value for TiDB.
if strings.Contains(shardingInfo, pkAutoRandomBitsSymbol) {
autoRandText := autoRandSymbol
if randomBitsMatch := pkAutoRandomBitsRegex.FindStringSubmatch(shardingInfo); len(randomBitsMatch) > 1 {
if rangeBitsMatch := rangeBitsRegex.FindStringSubmatch(shardingInfo); len(rangeBitsMatch) > 1 {
autoRandText += fmt.Sprintf("(%s, %s)", randomBitsMatch[1], rangeBitsMatch[1])
} else {
autoRandText += fmt.Sprintf("(%s)", randomBitsMatch[1])
}
}
if indexes, ok := indexMap[key]; ok {
for _, index := range indexes {
if index.Primary {
if len(index.Expressions) > 0 {
columnName := index.Expressions[0]
for i, column := range columns {
if column.Name == columnName {
newColumn := columns[i]
// Store AUTO_RANDOM in Default field (migration from DefaultExpression to Default)
newColumn.Default = autoRandText
break
}
}
}
}
}
}
}
tableMetadata := &metadatapb.TableMetadata{
Name: tableName,
Columns: columns,
ForeignKeys: foreignKeysMap[key],
Engine: engine,
Collation: collation,
RowCount: rowCount,
DataSize: dataSize,
IndexSize: indexSize,
DataFree: dataFree,
CreateOptions: createOptions,
Comment: comment,
Charset: convertCollationToCharset(collation),
Partitions: partitionTables[key],
CheckConstraints: checkMap[key],
ShardingInfo: shardingInfo,
PrimaryKeyType: pkType,
}
if tableCollation.Valid {
tableMetadata.Collation = tableCollation.String
}
var indexNames []string
if indexes, ok := indexMap[key]; ok {
for indexName := range indexes {
indexNames = append(indexNames, indexName)
}
slices.Sort(indexNames)
for _, indexName := range indexNames {
tableMetadata.Indexes = append(tableMetadata.Indexes, indexes[indexName])
}
}
schemaMetadata.Tables = append(schemaMetadata.Tables, tableMetadata)
case viewTableType:
if view, ok := viewMap[key]; ok {
schemaMetadata.Views = append(schemaMetadata.Views, view)
}
default:
// Skip unknown table types (e.g., SEQUENCE, SYSTEM VERSIONED)
slog.Debug("skipping unknown table type", slog.String("tableType", tableType), slog.String("tableName", tableName))
}
}
if err := tableRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, tableQuery)
}
// Add sequences to schema metadata
schemaMetadata.Sequences = sequences
databaseMetadata := &metadatapb.DatabaseSchemaMetadata{
Name: d.databaseName,
Schemas: []*metadatapb.SchemaMetadata{schemaMetadata},
}
// Query db info.
databaseQuery := `
SELECT
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = ?`
if err := d.db.QueryRowContext(ctx, databaseQuery, d.databaseName).Scan(
&databaseMetadata.CharacterSet,
&databaseMetadata.Collation,
); err != nil {
if err == sql.ErrNoRows {
return nil, common.Errorf(common.NotFound, "database %q not found", d.databaseName)
}
return nil, err
}
return databaseMetadata, err
}
func convertCollationToCharset(collation string) string {
// See mappings from "SHOW CHARACTER SET;".
tokens := strings.Split(collation, "_")
return tokens[0]
}
func isTimeConstant(s string) bool {
// 0000-00-00 00:00:00 is a special case in TiDB.
if s == "0000-00-00 00:00:00" {
return true
}
_, err := time.Parse(time.DateTime, s)
return err == nil
}
func setColumnMetadataDefault(column *metadatapb.ColumnMetadata, defaultStr sql.NullString, nullableBool bool, extra string) {
if defaultStr.Valid {
// TiDB is MySQL-compatible, so use MySQL's default handling approach
unquotedDefault := mysql.UnquoteMySQLString(defaultStr.String)
switch {
case mysql.IsCurrentTimestampLike(unquotedDefault):
column.Default = unquotedDefault
case strings.Contains(extra, "DEFAULT_GENERATED"):
// for case:
// CREATE TABLE t1(
// update_time TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
// );
// In this case, the extra value is "DEFAULT_GENERATED on update CURRENT_TIMESTAMP".
// But the default value is a constant.
if isTimeConstant(defaultStr.String) {
column.Default = defaultStr.String
} else {
unescapedDefault := mysql.UnescapeExpressionDefault(unquotedDefault)
column.Default = fmt.Sprintf("(%s)", unescapedDefault)
}
default:
// For non-generated and non CURRENT_XXX default value, preserve quotes for mysqldump compatibility
column.Default = defaultStr.String
}
} else if strings.Contains(strings.ToUpper(extra), autoIncrementSymbol) {
// TODO(zp): refactor column default value.
// Use the upper case to consistent with MySQL Dump.
column.Default = autoIncrementSymbol
} else if nullableBool {
// This is NULL if the column has an explicit default of NULL,
// or if the column definition includes no DEFAULT clause.
// https://dev.mysql.com/doc/refman/8.0/en/information-schema-columns-table.html
column.Default = "NULL"
}
if strings.Contains(extra, "on update CURRENT_TIMESTAMP") {
re := regexp.MustCompile(`CURRENT_TIMESTAMP\((\d+)\)`)
match := re.FindStringSubmatch(extra)
if len(match) > 0 {
digits := match[1]
column.OnUpdate = fmt.Sprintf("CURRENT_TIMESTAMP(%s)", digits)
} else {
column.OnUpdate = "CURRENT_TIMESTAMP"
}
}
}
func (d *Driver) listPartitionTables(ctx context.Context, databaseName string) (map[db.TableKey][]*metadatapb.TablePartitionMetadata, error) {
const query string = `
SELECT
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
PARTITION_METHOD,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
SUBPARTITION_EXPRESSION,
PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = ? AND PARTITION_NAME IS NOT NULL
ORDER BY TABLE_NAME ASC, PARTITION_NAME ASC, SUBPARTITION_NAME ASC, PARTITION_ORDINAL_POSITION ASC, SUBPARTITION_ORDINAL_POSITION ASC;
`
// Prepare the query statement.
stmt, err := d.db.PrepareContext(ctx, query)
if err != nil {
return nil, errors.Wrapf(err, "failed to prepare query: %s", query)
}
defer stmt.Close()
rows, err := stmt.QueryContext(ctx, databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
defer rows.Close()
type partitionKey struct {
tableName string
partitionName string
}
partitionMap := make(map[partitionKey]int)
result := make(map[db.TableKey][]*metadatapb.TablePartitionMetadata)
for rows.Next() {
var tableName, partitionName, partitionMethod string
var subpartitionName, subpartitionMethod, subpartitionExpression, partitionExpression, partitionDescription sql.NullString
if err := rows.Scan(
&tableName,
&partitionName,
&subpartitionName,
&partitionMethod,
&subpartitionMethod,
&partitionExpression,
&subpartitionExpression,
&partitionDescription,
); err != nil {
return nil, errors.Wrapf(err, "failed to scan row")
}
partitionKey := partitionKey{tableName: tableName, partitionName: partitionName}
tableKey := db.TableKey{Schema: "", Table: tableName}
if _, ok := partitionMap[partitionKey]; !ok {
// Partition
tp := convertToStorepbTablePartitionType(partitionMethod)
if tp == metadatapb.TablePartitionMetadata_TYPE_UNSPECIFIED {
slog.Warn("unknown partition type", slog.String("partitionMethod", partitionMethod))
continue
}
// For the key partition, it can take zero or more columns, the partition expression is null if taken zero columns.
expression := ""
if partitionExpression.Valid {
expression = partitionExpression.String
}
value := ""
if partitionDescription.Valid {
value = partitionDescription.String
}
partition := &metadatapb.TablePartitionMetadata{
Name: partitionName,
Type: tp,
Expression: expression,
Value: value,
Subpartitions: []*metadatapb.TablePartitionMetadata{},
}
partitionMap[partitionKey] = len(result[tableKey])
result[tableKey] = append(result[tableKey], partition)
}
if subpartitionName.Valid {
tp := convertToStorepbTablePartitionType(subpartitionMethod.String)
if tp == metadatapb.TablePartitionMetadata_TYPE_UNSPECIFIED {
slog.Warn("unknown subpartition type", slog.String("subpartitionMethod", subpartitionMethod.String))
continue
}
// For the key partition, it can take zero or more columns, the partition expression is null if taken zero columns.
expression := ""
if partitionExpression.Valid {
expression = subpartitionExpression.String
}
value := ""
if partitionDescription.Valid {
value = partitionDescription.String
}
subPartition := &metadatapb.TablePartitionMetadata{
Name: subpartitionName.String,
Type: tp,
Expression: expression,
Value: value,
Subpartitions: []*metadatapb.TablePartitionMetadata{},
}
if idx, ok := partitionMap[partitionKey]; !ok {
slog.Warn("subpartition without partition", slog.String("tableName", tableName), slog.String("partitionName", partitionName), slog.String("subpartitionName", subpartitionName.String))
} else {
result[tableKey][idx].Subpartitions = append(result[tableKey][idx].Subpartitions, subPartition)
}
}
}
if err := rows.Err(); err != nil {
return nil, errors.Wrapf(err, "failed to scan row")
}
return result, nil
}
func convertToStorepbTablePartitionType(tp string) metadatapb.TablePartitionMetadata_Type {
switch strings.ToUpper(tp) {
case "RANGE":
return metadatapb.TablePartitionMetadata_RANGE
case "RANGE COLUMNS":
return metadatapb.TablePartitionMetadata_RANGE_COLUMNS
case "LIST":
return metadatapb.TablePartitionMetadata_LIST
case "LIST COLUMNS":
return metadatapb.TablePartitionMetadata_LIST_COLUMNS
case "HASH":
return metadatapb.TablePartitionMetadata_HASH
case "KEY":
return metadatapb.TablePartitionMetadata_KEY
case "LINEAR HASH":
return metadatapb.TablePartitionMetadata_LINEAR_HASH
case "LINEAR KEY":
return metadatapb.TablePartitionMetadata_LINEAR_KEY
default:
return metadatapb.TablePartitionMetadata_TYPE_UNSPECIFIED
}
}
func (d *Driver) getForeignKeyList(ctx context.Context, databaseName string) (map[db.TableKey][]*metadatapb.ForeignKeyMetadata, error) {
fkQuery := `
SELECT
fks.TABLE_NAME,
fks.CONSTRAINT_NAME,
kcu.COLUMN_NAME,
'',
fks.REFERENCED_TABLE_NAME,
kcu.REFERENCED_COLUMN_NAME,
fks.DELETE_RULE,
fks.UPDATE_RULE,
fks.MATCH_OPTION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fks
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON fks.CONSTRAINT_SCHEMA = kcu.TABLE_SCHEMA
AND fks.TABLE_NAME = kcu.TABLE_NAME
AND fks.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE kcu.POSITION_IN_UNIQUE_CONSTRAINT IS NOT NULL AND fks.CONSTRAINT_SCHEMA = ?
ORDER BY fks.TABLE_NAME, fks.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;
`
fkRows, err := d.db.QueryContext(ctx, fkQuery, databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, fkQuery)
}
defer fkRows.Close()
foreignKeysMap := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
var buildingFk *metadatapb.ForeignKeyMetadata
var buildingTable string
for fkRows.Next() {
var tableName string
var fk metadatapb.ForeignKeyMetadata
var column, referencedColumn string
if err := fkRows.Scan(
&tableName,
&fk.Name,
&column,
&fk.ReferencedSchema,
&fk.ReferencedTable,
&referencedColumn,
&fk.OnDelete,
&fk.OnUpdate,
&fk.MatchType,
); err != nil {
return nil, err
}
fk.Columns = append(fk.Columns, column)
fk.ReferencedColumns = append(fk.ReferencedColumns, referencedColumn)
if buildingFk == nil {
buildingTable = tableName
buildingFk = &fk
} else {
if tableName == buildingTable && buildingFk.Name == fk.Name {
buildingFk.Columns = append(buildingFk.Columns, fk.Columns[0])
buildingFk.ReferencedColumns = append(buildingFk.ReferencedColumns, fk.ReferencedColumns[0])
} else {
key := db.TableKey{Schema: "", Table: buildingTable}
foreignKeysMap[key] = append(foreignKeysMap[key], buildingFk)
buildingTable = tableName
buildingFk = &fk
}
}
}
if err := fkRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, fkQuery)
}
if buildingFk != nil {
key := db.TableKey{Schema: "", Table: buildingTable}
foreignKeysMap[key] = append(foreignKeysMap[key], buildingFk)
}
return foreignKeysMap, nil
}
func (d *Driver) getCheckConstraintList(ctx context.Context, databaseName string) (map[db.TableKey][]*metadatapb.CheckConstraintMetadata, error) {
checkQuery := `
SELECT
tc.TABLE_NAME,
cc.CONSTRAINT_NAME,
cc.CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS cc
JOIN information_schema.TABLE_CONSTRAINTS tc
ON cc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND cc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
WHERE tc.CONSTRAINT_TYPE = 'CHECK' AND tc.TABLE_SCHEMA = ? AND cc.CONSTRAINT_SCHEMA = ?
`
checkRows, err := d.db.QueryContext(ctx, checkQuery, databaseName, databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, checkQuery)
}
defer checkRows.Close()
checkMap := make(map[db.TableKey][]*metadatapb.CheckConstraintMetadata)
for checkRows.Next() {
check := &metadatapb.CheckConstraintMetadata{}
var tableName string
if err := checkRows.Scan(
&tableName,
&check.Name,
&check.Expression,
); err != nil {
return nil, err
}
key := db.TableKey{Schema: "", Table: tableName}
checkMap[key] = append(checkMap[key], check)
}
if err := checkRows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, checkQuery)
}
return checkMap, nil
}
var (
// The TiDB version within a VERSION() string, e.g. "8.0.11-TiDB-v8.5.0" and
// "8.0.11-TiDB-v7.5.2-serverless".
tidbSemverRegex = regexp.MustCompile(`v\d+\.\d+\.\d+`)
// The leading MySQL compatibility version every TiDB build reports.
mysqlCompatVersionRegex = regexp.MustCompile(`^\d+\.\d+\.\d+`)
)
// supportsCheckConstraintTable reports whether the server exposes
// information_schema.CHECK_CONSTRAINTS, which TiDB added in v7.4.0. rawVersion is the
// verbatim VERSION() output, carrying a MySQL compatibility version and, usually, the
// TiDB version: "8.0.11-TiDB-v8.5.0".
//
// The TiDB version decides whenever it is readable. When it is not — TiDB Cloud reports a
// calendar version ("8.0.11-TiDB-CLOUD.202603.4"), and a custom server-version can drop it
// altogether — fall back to the MySQL prefix, which marks the same boundary: TiDB moved
// that prefix from 5.7.25 to 8.0.11 in v7.4.0, the very release that added the table.
//
// Do not read the prefix as a real MySQL version. MySQL added the table in 8.0.16, so
// comparing against that would answer false for every TiDB build, all of which report
// 8.0.11.
func supportsCheckConstraintTable(rawVersion string) bool {
if loc := tidbSemverRegex.FindStringIndex(rawVersion); loc != nil {
// Skip the leading "v" that semver.Make does not accept.
if tidbVersion, err := semver.Make(rawVersion[loc[0]+1 : loc[1]]); err == nil {
return tidbVersion.GE(semver.MustParse("7.4.0"))
}
}
loc := mysqlCompatVersionRegex.FindStringIndex(rawVersion)
if loc == nil {
return false
}
mysqlVersion, err := semver.Make(rawVersion[loc[0]:loc[1]])
if err != nil {
return false
}
return mysqlVersion.GE(semver.MustParse("8.0.0"))
}
func stripSingleQuote(s string) string {
if len(s) >= 2 && s[0] == '\'' && s[len(s)-1] == '\'' {
return s[1 : len(s)-1]
}
return s
}
func (d *Driver) getSequenceList(ctx context.Context, databaseName string) ([]*metadatapb.SequenceMetadata, error) {
query := `
SELECT
SEQUENCE_NAME,
START,
MIN_VALUE,
MAX_VALUE,
INCREMENT,
CYCLE,
CACHE_VALUE,
IFNULL(COMMENT, '')
FROM information_schema.SEQUENCES
WHERE SEQUENCE_SCHEMA = ?
ORDER BY SEQUENCE_NAME`
rows, err := d.db.QueryContext(ctx, query, databaseName)
if err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
defer rows.Close()
var sequences []*metadatapb.SequenceMetadata
for rows.Next() {
sequence := &metadatapb.SequenceMetadata{}
var cycleOption int64
if err := rows.Scan(
&sequence.Name,
&sequence.Start,
&sequence.MinValue,
&sequence.MaxValue,
&sequence.Increment,
&cycleOption,
&sequence.CacheSize,
&sequence.Comment,
); err != nil {
return nil, err
}
// TiDB sequences are always numeric, set default data type
sequence.DataType = "BIGINT"
// Convert cycle option to boolean (TiDB uses 0/1 instead of YES/NO)
sequence.Cycle = cycleOption != 0
sequences = append(sequences, sequence)
}
if err := rows.Err(); err != nil {
return nil, util.FormatErrorWithQuery(err, query)
}
return sequences, nil
}