Skip to content

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

17.2 KB
Raw Blame
package v1

import (
	"context"
	"fmt"
	"strings"

	"github.com/pkg/errors"
	"google.golang.org/protobuf/proto"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/component/ghost"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/store"
	"github.com/bytebase/bytebase/backend/utils"
)

func applyDatabaseGroupSpecTransformations(ctx context.Context, s *store.Store, specs []*storepb.PlanConfig_Spec, projectID string) ([]*storepb.PlanConfig_Spec, error) {
	var result []*storepb.PlanConfig_Spec
	for _, spec := range specs {
		// Clone the spec to avoid modifying the original
		clonedSpec := proto.CloneOf(spec)

		if config := clonedSpec.GetChangeDatabaseConfig(); config != nil {
			// transform database group.
			if len(config.Targets) == 1 {
				if _, databaseGroupID, err := common.GetProjectIDDatabaseGroupID(config.Targets[0]); err == nil {
					// Re-evaluate database group matches live instead of using deployment snapshot
					databaseGroup, err := s.GetDatabaseGroup(ctx, &store.FindDatabaseGroupMessage{
						ResourceID: &databaseGroupID,
						ProjectIDs: []string{projectID},
					})
					if err != nil {
						return nil, errors.Wrapf(err, "failed to get database group")
					}
					if databaseGroup == nil {
						return nil, errors.Errorf("database group %q not found", config.Targets[0])
					}

					allDatabases, err := s.ListDatabases(ctx, &store.FindDatabaseMessage{Workspace: common.GetWorkspaceIDFromContext(ctx), ProjectID: &projectID})
					if err != nil {
						return nil, errors.Wrapf(err, "failed to list databases for project %q", projectID)
					}

					matchedDatabases, err := utils.GetMatchedDatabasesInDatabaseGroup(ctx, databaseGroup, allDatabases)
					if err != nil {
						return nil, errors.Wrapf(err, "failed to get matched databases in database group %q", databaseGroupID)
					}

					var databases []string
					for _, db := range matchedDatabases {
						databases = append(databases, db.ResourceName())
					}
					config.Targets = databases
				}
			}
		}
		result = append(result, clonedSpec)
	}
	return result, nil
}

func getTaskCreatesFromSpec(ctx context.Context, s *store.Store, spec *storepb.PlanConfig_Spec, projectID string) ([]*store.TaskMessage, error) {
	switch config := spec.Config.(type) {
	case *storepb.PlanConfig_Spec_CreateDatabaseConfig:
		return getTaskCreatesFromCreateDatabaseConfig(ctx, s, spec, config.CreateDatabaseConfig, projectID)
	case *storepb.PlanConfig_Spec_ChangeDatabaseConfig:
		return getTaskCreatesFromChangeDatabaseConfig(ctx, s, spec, config.ChangeDatabaseConfig, projectID)
	}

	return nil, errors.Errorf("invalid spec config type %T", spec.Config)
}

func getTaskCreatesFromCreateDatabaseConfig(ctx context.Context, s *store.Store, spec *storepb.PlanConfig_Spec, c *storepb.PlanConfig_CreateDatabaseConfig, projectID string) ([]*store.TaskMessage, error) {
	if c.Database == "" {
		return nil, errors.Errorf("database name is required")
	}

	instance, err := getInstanceMessage(ctx, s, c.Target)
	if err != nil {
		return nil, err
	}
	if instance.Metadata.GetEngine() == storepb.Engine_ORACLE {
		return nil, errors.Errorf("creating Oracle database is not supported")
	}

	dbEnvironmentID := ""
	if c.Environment != "" {
		dbEnvironmentID = strings.TrimPrefix(c.Environment, common.EnvironmentNamePrefix)
	}
	// Fallback to instance.EnvironmentID if user-set environment is not present.
	var effectiveEnvironmentID string
	if dbEnvironmentID != "" {
		effectiveEnvironmentID = dbEnvironmentID
	} else if instance.EnvironmentID != nil && *instance.EnvironmentID != "" {
		effectiveEnvironmentID = *instance.EnvironmentID
	}

	if instance.Metadata.GetEngine() == storepb.Engine_MONGODB && c.Table == "" {
		return nil, errors.Errorf("collection name is required for MongoDB")
	}

	taskCreates, err := func() ([]*store.TaskMessage, error) {
		if err := checkCharacterSetCollationOwner(instance.Metadata.GetEngine(), c.CharacterSet, c.Collation, c.Owner); err != nil {
			return nil, err
		}
		if c.Database == "" {
			return nil, errors.Errorf("database name is required")
		}
		if instance.Metadata.GetEngine() == storepb.Engine_SNOWFLAKE {
			// Snowflake needs to use upper case of DatabaseName.
			c.Database = strings.ToUpper(c.Database)
		}
		if instance.Metadata.GetEngine() == storepb.Engine_MONGODB && c.Table == "" {
			return nil, common.Errorf(common.Invalid, "Failed to create issue, collection name missing for MongoDB")
		}

		// Get admin data source username.
		adminDataSource := utils.DataSourceFromInstanceWithType(instance, storepb.DataSourceType_ADMIN)
		if adminDataSource == nil {
			return nil, common.Errorf(common.Internal, "admin data source not found for instance %q", instance.ResourceID)
		}
		databaseName := c.Database
		switch instance.Metadata.GetEngine() {
		case storepb.Engine_SNOWFLAKE:
			// Snowflake needs to use upper case of DatabaseName.
			databaseName = strings.ToUpper(databaseName)
		case storepb.Engine_MYSQL, storepb.Engine_MARIADB, storepb.Engine_OCEANBASE:
			// For MySQL, we need to use different case of DatabaseName depends on the variable `lower_case_table_names`.
			// https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
			if !store.IsObjectCaseSensitive(instance) {
				databaseName = strings.ToLower(databaseName)
			}
		default:
			// Other engines use the original database name
		}

		statement, err := getCreateDatabaseStatement(instance.Metadata.GetEngine(), c, databaseName, adminDataSource.GetUsername())
		if err != nil {
			return nil, err
		}
		sheets, err := s.CreateSheets(ctx, projectID, &store.SheetMessage{
			Statement: statement,
		})
		if err != nil {
			return nil, errors.Wrap(err, "failed to create database creation sheet")
		}
		sheet := sheets[0]

		v := &store.TaskMessage{
			InstanceID:   instance.ResourceID,
			DatabaseName: &databaseName,
			Environment:  effectiveEnvironmentID,
			Type:         storepb.Task_DATABASE_CREATE,
			Payload: &storepb.Task{
				SpecId: spec.Id,
				Source: &storepb.Task_SheetSha256{
					SheetSha256: sheet.Sha256,
				},
			},
		}
		return []*store.TaskMessage{
			v,
		}, nil
	}()
	if err != nil {
		return nil, err
	}

	return taskCreates, nil
}

func getTaskCreatesFromChangeDatabaseConfig(
	ctx context.Context,
	s *store.Store,
	spec *storepb.PlanConfig_Spec,
	c *storepb.PlanConfig_ChangeDatabaseConfig,
	projectID string,
) ([]*store.TaskMessage, error) {
	databases, err := getDatabaseMessagesByTargets(ctx, s, c.Targets)
	if err != nil {
		return nil, err
	}

	// Validate ghost directive at task creation time (parsed at execution
	// time). c.SheetSha256 is loop-invariant: fetch it once, not once per
	// target database.
	if c.SheetSha256 != "" {
		sheetContent, err := getSheetContentBySha256(ctx, s, projectID, c.SheetSha256)
		if err != nil {
			return nil, errors.Wrapf(err, "failed to get sheet content")
		}

		if ghost.IsGhostEnabled(sheetContent) {
			ghostFlags, err := ghost.ParseGhostDirective(sheetContent)
			if err != nil {
				return nil, errors.Wrapf(err, "failed to parse ghost directive")
			}
			if _, err := ghost.GetUserFlags(ghostFlags); err != nil {
				return nil, errors.Wrapf(err, "invalid ghost flags %q", ghostFlags)
			}
		}
	}

	var tasks []*store.TaskMessage
	for _, database := range databases {
		if database.EffectiveEnvironmentID == nil {
			continue
		}
		env := *database.EffectiveEnvironmentID

		// All change database tasks use DATABASE_MIGRATE type
		taskType := storepb.Task_DATABASE_MIGRATE

		// Build task payload
		payload := &storepb.Task{
			SpecId:            spec.Id,
			EnablePriorBackup: c.EnablePriorBackup,
		}

		// Set source: either release or sheet
		if c.Release != "" {
			payload.Source = &storepb.Task_Release{
				Release: c.Release,
			}
		} else {
			payload.Source = &storepb.Task_SheetSha256{
				SheetSha256: c.SheetSha256,
			}
		}

		taskCreate := &store.TaskMessage{
			InstanceID:   database.InstanceID,
			DatabaseName: &database.DatabaseName,
			Environment:  env,
			Type:         taskType,
			Payload:      payload,
		}
		tasks = append(tasks, taskCreate)
	}

	if len(tasks) == 0 {
		return nil, errors.Errorf("cannot create tasks for spec %s: all target databases are missing an environment", spec.Id)
	}

	return tasks, nil
}

func getDatabaseMessagesByTargets(ctx context.Context, s *store.Store, targets []string) ([]*store.DatabaseMessage, error) {
	databases := []*store.DatabaseMessage{}

	for _, target := range targets {
		if targetProjectID, _, err := common.GetProjectIDDatabaseGroupID(target); err == nil {
			databaseGroup, err := getDatabaseGroupByName(ctx, s, target, v1pb.DatabaseGroupView_DATABASE_GROUP_VIEW_FULL)
			if err != nil {
				return nil, errors.Wrapf(err, "failed to get database group %q", target)
			}
			for _, matched := range databaseGroup.MatchedDatabases {
				_, instanceID, databaseName, err := common.GetDatabaseResourceName(matched.Name)
				if err != nil {
					return nil, errors.Wrapf(err, "failed to parse %q", matched.Name)
				}
				database, err := s.GetDatabase(ctx, &store.FindDatabaseMessage{
					Workspace:    common.GetWorkspaceIDFromContext(ctx),
					InstanceID:   &instanceID,
					DatabaseName: &databaseName,
				})
				if err != nil {
					return nil, errors.Wrapf(err, "failed to get database")
				}
				if database == nil {
					return nil, errors.Errorf("database %q not found", matched.Name)
				}
				if database.ProjectID != targetProjectID {
					return nil, errors.Errorf("database %q does not belong to project %q", matched.Name, targetProjectID)
				}
				if database.ResourceName() != matched.Name {
					return nil, errors.Errorf("database target %q is not canonical for its instance", matched.Name)
				}
				databases = append(databases, database)
			}
		} else if targetProjectID, instanceID, databaseName, err := common.GetDatabaseResourceName(target); err == nil {
			database, err := s.GetDatabase(ctx, &store.FindDatabaseMessage{
				Workspace:    common.GetWorkspaceIDFromContext(ctx),
				InstanceID:   &instanceID,
				DatabaseName: &databaseName,
			})
			if err != nil {
				return nil, errors.Wrapf(err, "failed to get database")
			}
			if database == nil {
				return nil, errors.Errorf("database %q not found", target)
			}
			if targetProjectID != nil && database.ProjectID != *targetProjectID {
				return nil, errors.Errorf("database %q does not belong to project %q", target, *targetProjectID)
			}
			if database.ResourceName() != target {
				return nil, errors.Errorf("database target %q is not canonical for its instance", target)
			}
			databases = append(databases, database)
		} else {
			return nil, errors.Errorf("invalid target %q", target)
		}
	}
	if err := validateRolloutDatabaseInstances(ctx, s, databases); err != nil {
		return nil, err
	}
	return databases, nil
}

func validateRolloutDatabaseInstances(ctx context.Context, s *store.Store, databases []*store.DatabaseMessage) error {
	instanceIDs := make([]string, 0, len(databases))
	seen := make(map[string]bool, len(databases))
	for _, database := range databases {
		if !seen[database.InstanceID] {
			seen[database.InstanceID] = true
			instanceIDs = append(instanceIDs, database.InstanceID)
		}
	}
	if len(instanceIDs) == 0 {
		return nil
	}

	instances, err := s.ListInstances(ctx, &store.FindInstanceMessage{
		Workspace:   common.GetWorkspaceIDFromContext(ctx),
		ResourceIDs: &instanceIDs,
		ShowDeleted: true,
	})
	if err != nil {
		return errors.Wrap(err, "failed to list rollout target instances")
	}
	instanceByID := make(map[string]*store.InstanceMessage, len(instances))
	for _, instance := range instances {
		instanceByID[instance.ResourceID] = instance
	}
	for _, database := range databases {
		instance := instanceByID[database.InstanceID]
		if instance == nil {
			return errors.Errorf("instance %q not found", database.InstanceID)
		}
		if instance.Deleted {
			return errors.Errorf("instance %q has been deleted", database.InstanceID)
		}
	}
	return nil
}

// checkCharacterSetCollationOwner checks if the character set, collation and owner are legal according to the dbType.
func checkCharacterSetCollationOwner(dbType storepb.Engine, characterSet, collation, owner string) error {
	switch dbType {
	case storepb.Engine_SPANNER:
		// Spanner does not support character set and collation at the database level.
		if characterSet != "" {
			return errors.Errorf("Spanner does not support character set, but got %s", characterSet)
		}
		if collation != "" {
			return errors.Errorf("Spanner does not support collation, but got %s", collation)
		}
	case storepb.Engine_CLICKHOUSE:
		// ClickHouse does not support character set and collation at the database level.
		if characterSet != "" {
			return errors.Errorf("ClickHouse does not support character set, but got %s", characterSet)
		}
		if collation != "" {
			return errors.Errorf("ClickHouse does not support collation, but got %s", collation)
		}
	case storepb.Engine_SNOWFLAKE:
		if characterSet != "" {
			return errors.Errorf("Snowflake does not support character set, but got %s", characterSet)
		}
		if collation != "" {
			return errors.Errorf("Snowflake does not support collation, but got %s", collation)
		}
	case storepb.Engine_POSTGRES:
		if owner == "" {
			return errors.Errorf("database owner is required for PostgreSQL")
		}
	case storepb.Engine_REDSHIFT:
		if owner == "" {
			return errors.Errorf("database owner is required for Redshift")
		}
	case storepb.Engine_COCKROACHDB:
		if owner == "" {
			return errors.Errorf("database owner is required for CockroachDB")
		}
	case storepb.Engine_MONGODB, storepb.Engine_MSSQL, storepb.Engine_DORIS, storepb.Engine_STARROCKS:
		// no-op.
	default:
		if characterSet == "" {
			return errors.Errorf("character set missing for %s", dbType.String())
		}
		// For postgres, we don't explicitly specify a default since the default might be UNSET (denoted by "C").
		// If that's the case, setting an explicit default such as "en_US.UTF-8" might fail if the instance doesn't
		// install it.
		if collation == "" {
			return errors.Errorf("collation missing for %s", dbType.String())
		}
	}
	return nil
}

func getCreateDatabaseStatement(dbType storepb.Engine, c *storepb.PlanConfig_CreateDatabaseConfig, databaseName, adminDatasourceUser string) (string, error) {
	var stmt string
	switch dbType {
	case storepb.Engine_MYSQL, storepb.Engine_TIDB, storepb.Engine_MARIADB, storepb.Engine_OCEANBASE:
		return fmt.Sprintf("CREATE DATABASE `%s` CHARACTER SET %s COLLATE %s;", databaseName, c.CharacterSet, c.Collation), nil
	case storepb.Engine_MSSQL:
		return fmt.Sprintf(`CREATE DATABASE "%s";`, databaseName), nil
	case storepb.Engine_POSTGRES, storepb.Engine_COCKROACHDB:
		collationPart := ""
		if c.Collation != "" {
			collationPart = fmt.Sprintf(" LC_COLLATE %q", c.Collation)
		}
		stmt = fmt.Sprintf("%sCREATE DATABASE \"%s\" ENCODING %q%s;", stmt, databaseName, c.CharacterSet, collationPart)
		// Set the database owner.
		// We didn't use CREATE DATABASE WITH OWNER because RDS requires the current role to be a member of the database owner.
		// However, people can still use ALTER DATABASE to change the owner afterwards.
		// Error string below:
		// query: CREATE DATABASE h1 WITH OWNER hello;
		// ERROR:  must be member of role "hello"
		return fmt.Sprintf("%s\nALTER DATABASE \"%s\" OWNER TO \"%s\";", stmt, databaseName, c.Owner), nil
	case storepb.Engine_CLICKHOUSE:
		clusterPart := ""
		if c.Cluster != "" {
			clusterPart = fmt.Sprintf(" ON CLUSTER `%s`", c.Cluster)
		}
		return fmt.Sprintf("CREATE DATABASE `%s`%s;", databaseName, clusterPart), nil
	case storepb.Engine_SNOWFLAKE:
		return fmt.Sprintf("CREATE DATABASE %s;", databaseName), nil
	case storepb.Engine_MONGODB:
		// MongoDB has no top-level CREATE DATABASE — a database is implicitly created
		// when its first collection is. We pass the database name to the engine driver
		// (which uses it to build the connection URI) and emit createCollection.
		return fmt.Sprintf(`db.createCollection("%s");`, c.Table), nil
	case storepb.Engine_SPANNER:
		return fmt.Sprintf("CREATE DATABASE %s;", databaseName), nil
	case storepb.Engine_ORACLE:
		return fmt.Sprintf("CREATE DATABASE %s;", databaseName), nil
	case storepb.Engine_REDSHIFT:
		options := make(map[string]string)
		if adminDatasourceUser != "" && c.Owner != adminDatasourceUser {
			options["OWNER"] = fmt.Sprintf("%q", c.Owner)
		}
		stmt := fmt.Sprintf("CREATE DATABASE \"%s\"", databaseName)
		if len(options) > 0 {
			list := make([]string, 0, len(options))
			for k, v := range options {
				list = append(list, fmt.Sprintf("%s=%s", k, v))
			}
			stmt = fmt.Sprintf("%s WITH\n\t%s", stmt, strings.Join(list, "\n\t"))
		}
		return fmt.Sprintf("%s;", stmt), nil
	case storepb.Engine_HIVE:
		return fmt.Sprintf("CREATE DATABASE %s;", databaseName), nil
	case storepb.Engine_DORIS:
		return fmt.Sprintf("CREATE DATABASE %s;", databaseName), nil
	case storepb.Engine_STARROCKS:
		return fmt.Sprintf("CREATE DATABASE `%s`;", databaseName), nil
	default:
		return "", errors.Errorf("unsupported database type %s", dbType)
	}
}

func getSheetContentBySha256(ctx context.Context, s *store.Store, projectID, sha256 string) (string, error) {
	sheet, err := s.GetSheetForProject(ctx, projectID, sha256, true)
	if err != nil {
		return "", err
	}
	if sheet == nil {
		return "", errors.Errorf("sheet not found for sha256: %s", sha256)
	}
	return sheet.Statement, nil
}