Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

10.5 KB
Raw Blame
package spanner

import (
	"context"
	"slices"

	"cloud.google.com/go/spanner"
	"cloud.google.com/go/spanner/admin/database/apiv1/databasepb"
	metadatapb "github.com/bytebase/omni/metadata"
	"google.golang.org/api/iterator"
	"google.golang.org/grpc/codes"
	"google.golang.org/grpc/status"

	"github.com/pkg/errors"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/plugin/db"
)

// SyncInstance syncs the instance.
func (d *Driver) SyncInstance(ctx context.Context) (*db.InstanceMetadata, error) {
	var databases []*metadatapb.DatabaseSchemaMetadata
	iter := d.dbClient.ListDatabases(ctx, &databasepb.ListDatabasesRequest{
		Parent: d.instancePath(),
	})
	for {
		database, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}

		// filter out databases using postgresql dialect which are not supported currently
		// TODO(p0ny): remove this after supporting postgresql dialect
		if database.DatabaseDialect == databasepb.DatabaseDialect_POSTGRESQL {
			continue
		}

		// database.Name is of the form `projects/<project>/instances/<instance>/databases/<database>`
		// We use regular expression to extract <database> from it.
		databaseName, err := getDatabaseFromDSN(database.Name)
		if err != nil {
			return nil, errors.Wrapf(err, "failed to get database name from %s", database.Name)
		}

		databases = append(databases, &metadatapb.DatabaseSchemaMetadata{Name: databaseName})
	}

	return &db.InstanceMetadata{
		Databases: databases,
	}, nil
}

// SyncDBSchema syncs a single database schema.
func (d *Driver) SyncDBSchema(ctx context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
	notFound, err := d.notFoundDatabase(ctx, d.databaseName)
	if err != nil {
		return nil, errors.Wrap(err, "failed to check if database exists")
	}
	if notFound {
		return nil, common.Errorf(common.NotFound, "database %q not found", d.databaseName)
	}

	tx := d.client.ReadOnlyTransaction()
	defer tx.Close()

	databaseMetadata := &metadatapb.DatabaseSchemaMetadata{
		Name: d.databaseName,
	}
	columnMap, err := getColumn(ctx, tx)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get table columns from database %q", d.databaseName)
	}
	tableMap, err := getTable(ctx, tx, columnMap)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get tables from database %q", d.databaseName)
	}
	viewMap, err := getView(ctx, tx, columnMap)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get views from database %q", d.databaseName)
	}

	schemaNameMap := make(map[string]bool)
	for schemaName := range tableMap {
		schemaNameMap[schemaName] = true
	}
	for schemaName := range viewMap {
		schemaNameMap[schemaName] = true
	}
	var schemaNames []string
	for schemaName := range schemaNameMap {
		schemaNames = append(schemaNames, schemaName)
	}
	slices.Sort(schemaNames)
	for _, schemaName := range schemaNames {
		databaseMetadata.Schemas = append(databaseMetadata.Schemas, &metadatapb.SchemaMetadata{
			Name:   schemaName,
			Tables: tableMap[schemaName],
			Views:  viewMap[schemaName],
		})
	}

	return databaseMetadata, err
}

func (d *Driver) notFoundDatabase(ctx context.Context, databaseName string) (bool, error) {
	dsn := getDSN(d.instancePath(), databaseName)
	_, err := d.dbClient.GetDatabase(ctx, &databasepb.GetDatabaseRequest{Name: dsn})
	if status.Code(err) == codes.NotFound {
		return true, nil
	}
	if err != nil {
		return false, err
	}
	return false, nil
}

func getTable(ctx context.Context, tx *spanner.ReadOnlyTransaction, columnMap map[db.TableKey][]*metadatapb.ColumnMetadata) (map[string][]*metadatapb.TableMetadata, error) {
	indexMap, err := getIndex(ctx, tx)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get indices")
	}
	foreignKeyMap, err := getForeignKey(ctx, tx)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get foreign keys")
	}
	tableMap := make(map[string][]*metadatapb.TableMetadata)
	query := `
    SELECT
      TABLE_SCHEMA,
      TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'SPANNER_SYS') AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY TABLE_SCHEMA, TABLE_NAME
  `
	iter := tx.Query(ctx, spanner.NewStatement(query))
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}
		var table metadatapb.TableMetadata
		var schema string
		if err := row.Columns(&schema, &table.Name); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schema, Table: table.Name}
		table.Columns = columnMap[key]
		table.Indexes = indexMap[key]
		table.ForeignKeys = foreignKeyMap[key]

		tableMap[schema] = append(tableMap[schema], &table)
	}
	return tableMap, nil
}

func getColumn(ctx context.Context, tx *spanner.ReadOnlyTransaction) (map[db.TableKey][]*metadatapb.ColumnMetadata, error) {
	columnsMap := make(map[db.TableKey][]*metadatapb.ColumnMetadata)
	query := `
    SELECT 
      TABLE_SCHEMA,
      TABLE_NAME,
      COLUMN_NAME,
      ORDINAL_POSITION,
      COLUMN_DEFAULT,
      IS_NULLABLE = 'YES',
      SPANNER_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'SPANNER_SYS')
    ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
  `
	iter := tx.Query(ctx, spanner.NewStatement(query))
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}
		column := &metadatapb.ColumnMetadata{}
		var (
			schemaName string
			tableName  string
			position   int64
			defaultStr spanner.NullString
		)
		if err := row.Columns(&schemaName, &tableName, &column.Name, &position, &defaultStr, &column.Nullable, &column.Type); err != nil {
			return nil, err
		}
		column.Position = int32(position)
		if defaultStr.Valid {
			// Store in Default field (migration from DefaultExpression to Default)
			column.Default = defaultStr.StringVal
		}
		key := db.TableKey{Schema: schemaName, Table: tableName}
		columnsMap[key] = append(columnsMap[key], column)
	}
	return columnsMap, nil
}

func getIndex(ctx context.Context, tx *spanner.ReadOnlyTransaction) (map[db.TableKey][]*metadatapb.IndexMetadata, error) {
	query := `
    SELECT
      TABLE_SCHEMA,
      TABLE_NAME,
      INDEX_NAME,
      IS_UNIQUE,
      INDEX_TYPE = 'PRIMARY_KEY' AS IS_PRIMARY,
      ARRAY (
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.INDEX_COLUMNS AS ic
        WHERE ic.TABLE_SCHEMA = i.TABLE_SCHEMA AND ic.TABLE_NAME = i.TABLE_NAME AND ic.INDEX_NAME = i.INDEX_NAME
        ORDER BY ic.ORDINAL_POSITION
      )
    FROM INFORMATION_SCHEMA.INDEXES as i
    WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'SPANNER_SYS') AND SPANNER_IS_MANAGED = FALSE
    ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
  `

	keyIndexMap := make(map[db.TableKey][]*metadatapb.IndexMetadata)

	iter := tx.Query(ctx, spanner.NewStatement(query))
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}
		var idx metadatapb.IndexMetadata
		var schema, table string
		if err := row.Columns(
			&schema,
			&table,
			&idx.Name,
			&idx.Unique,
			&idx.Primary,
			&idx.Expressions,
		); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schema, Table: table}
		keyIndexMap[key] = append(keyIndexMap[key], &idx)
	}

	return keyIndexMap, nil
}

func getForeignKey(ctx context.Context, tx *spanner.ReadOnlyTransaction) (map[db.TableKey][]*metadatapb.ForeignKeyMetadata, error) {
	foreignKeyMap := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
	query := `
    WITH t AS (
      SELECT DISTINCT
        tc.CONSTRAINT_SCHEMA,
        tc.CONSTRAINT_NAME,
        tc.TABLE_NAME,
        rc.UNIQUE_CONSTRAINT_SCHEMA,
        rc.UNIQUE_CONSTRAINT_NAME,
        ccu.TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA,
        ccu.TABLE_NAME AS REFERENCED_TABLE_NAME,
        rc.DELETE_RULE,
        rc.UPDATE_RULE,
        rc.MATCH_OPTION
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
      JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
      ON tc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
      JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu
      ON tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME 
      WHERE tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
    )
    SELECT
      t.CONSTRAINT_SCHEMA,
      t.CONSTRAINT_NAME,
      t.TABLE_NAME,
      t.REFERENCED_TABLE_SCHEMA,
      t.REFERENCED_TABLE_NAME,
      ARRAY (
        SELECT
          COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
        WHERE t.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA AND t.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
        ORDER BY kcu.ORDINAL_POSITION
      ) AS TABLE_COLUMNS,
      ARRAY (
        SELECT
          COLUMN_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
        WHERE t.UNIQUE_CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA AND t.UNIQUE_CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
        ORDER BY kcu.ORDINAL_POSITION
      ) AS REFERENCED_TABLE_COLUMNS,
      t.DELETE_RULE,
      t.UPDATE_RULE,
      t.MATCH_OPTION
    FROM t
  `
	iter := tx.Query(ctx, spanner.NewStatement(query))
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}
		var fk metadatapb.ForeignKeyMetadata
		var schema, table string
		var columns, referencedColumns []string
		if err := row.Columns(
			&schema,
			&fk.Name,
			&table,
			&fk.ReferencedSchema,
			&fk.ReferencedTable,
			&columns,
			&referencedColumns,
			&fk.OnDelete,
			&fk.OnUpdate,
			&fk.MatchType,
		); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schema, Table: table}
		foreignKeyMap[key] = append(foreignKeyMap[key], &fk)
	}
	return foreignKeyMap, nil
}

func getView(ctx context.Context, tx *spanner.ReadOnlyTransaction, columnMap map[db.TableKey][]*metadatapb.ColumnMetadata) (map[string][]*metadatapb.ViewMetadata, error) {
	viewMap := make(map[string][]*metadatapb.ViewMetadata)
	query := `
  SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    VIEW_DEFINITION
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'SPANNER_SYS')
  `
	iter := tx.Query(ctx, spanner.NewStatement(query))
	for {
		row, err := iter.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return nil, err
		}
		var (
			schema     string
			name       string
			definition string
		)
		if err := row.Columns(&schema, &name, &definition); err != nil {
			return nil, err
		}

		key := db.TableKey{Schema: schema, Table: name}

		viewMap[schema] = append(viewMap[schema], &metadatapb.ViewMetadata{
			Name:       name,
			Definition: definition,
			Columns:    columnMap[key],
		})
	}
	return viewMap, nil
}