Skip to content

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

7.8 KB
Raw Blame
package store_test

import (
	"context"
	"database/sql"
	"testing"
	"time"

	"github.com/bytebase/bytebase/backend/common/testcontainer"

	"github.com/stretchr/testify/require"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/store"
)

func newInstanceProjectFixture(t *testing.T) (context.Context, *sql.DB, *store.Store) {
	t.Helper()
	ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
	t.Cleanup(cancel)
	db, s, _ := testcontainer.NewMetadataDB(t)
	_, err := db.ExecContext(ctx, `
		INSERT INTO workspace (resource_id) VALUES ('default');
		INSERT INTO project (resource_id, workspace, name, deleted) VALUES
			('default', 'default', 'Default', FALSE),
			('project-a', 'default', 'Project A', FALSE),
			('deleted-project', 'default', 'Deleted Project', TRUE);
	`)
	require.NoError(t, err)
	return ctx, db, s
}

func testInstanceMetadata() *storepb.Instance {
	return &storepb.Instance{
		DataSources: []*storepb.DataSource{{
			Id:   "admin",
			Type: storepb.DataSourceType_ADMIN,
		}},
	}
}

func TestCreateAndListProjectInstance(t *testing.T) {
	t.Parallel()
	ctx, db, s := newInstanceProjectFixture(t)
	projectID := "project-a"
	instance, err := s.CreateInstance(ctx, &store.InstanceMessage{
		ResourceID: "project-instance",
		Workspace:  "default",
		ProjectID:  &projectID,
		Metadata:   testInstanceMetadata(),
	})
	require.NoError(t, err)
	require.Equal(t, &projectID, instance.ProjectID)
	instanceID := "project-instance"
	updated, err := s.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instanceID,
		Workspace:  "default",
		Metadata:   testInstanceMetadata(),
	})
	require.NoError(t, err)
	require.Equal(t, &projectID, updated.ProjectID)
	_, err = s.CreateInstance(ctx, &store.InstanceMessage{
		ResourceID: "workspace-instance",
		Workspace:  "default",
		Metadata:   testInstanceMetadata(),
	})
	require.NoError(t, err)

	var persistedProjectID string
	require.NoError(t, db.QueryRowContext(ctx, `
		SELECT project FROM instance WHERE resource_id = 'project-instance'
	`).Scan(&persistedProjectID))
	require.Equal(t, projectID, persistedProjectID)

	projectInstances, err := s.ListInstances(ctx, &store.FindInstanceMessage{
		Workspace: "default",
		ProjectID: &projectID,
	})
	require.NoError(t, err)
	require.Len(t, projectInstances, 1)
	require.Equal(t, "project-instance", projectInstances[0].ResourceID)
	wrongScope, err := s.GetInstance(ctx, &store.FindInstanceMessage{
		Workspace:     "default",
		WorkspaceOnly: true,
		ResourceID:    &instanceID,
	})
	require.NoError(t, err)
	require.Nil(t, wrongScope)

	unfilteredInstances, err := s.ListInstances(ctx, &store.FindInstanceMessage{Workspace: "default"})
	require.NoError(t, err)
	require.Len(t, unfilteredInstances, 2)

	workspaceInstances, err := s.ListInstances(ctx, &store.FindInstanceMessage{
		Workspace:     "default",
		WorkspaceOnly: true,
	})
	require.NoError(t, err)
	require.Len(t, workspaceInstances, 1)
	require.Equal(t, "workspace-instance", workspaceInstances[0].ResourceID)

	_, err = s.ListInstances(ctx, &store.FindInstanceMessage{
		Workspace:     "default",
		ProjectID:     &projectID,
		WorkspaceOnly: true,
	})
	require.Error(t, err)

	_, err = s.GetInstance(ctx, &store.FindInstanceMessage{
		Workspace:     "default",
		ProjectID:     &projectID,
		WorkspaceOnly: true,
		ResourceID:    &instanceID,
	})
	require.Error(t, err)

	instanceByResourceID, err := s.GetInstanceByResourceID(ctx, "project-instance")
	require.NoError(t, err)
	require.Equal(t, &projectID, instanceByResourceID.ProjectID)

	allInstances, err := s.ListAllInstances(ctx, false)
	require.NoError(t, err)
	require.Len(t, allInstances, 2)
	require.Equal(t, &projectID, allInstances[0].ProjectID)
}

func TestListAllInstancesDeobfuscatesAcrossWorkspaces(t *testing.T) {
	t.Parallel()
	ctx, db, s := newInstanceProjectFixture(t)
	_, err := db.ExecContext(ctx, `INSERT INTO workspace (resource_id) VALUES ('other')`)
	require.NoError(t, err)
	for _, workspace := range []string{"default", "other"} {
		metadata := testInstanceMetadata()
		metadata.DataSources[0].Password = workspace + "-password"
		_, err := s.CreateInstance(ctx, &store.InstanceMessage{
			ResourceID: workspace + "-instance",
			Workspace:  workspace,
			Metadata:   metadata,
		})
		require.NoError(t, err)
	}

	instances, err := s.ListAllInstances(ctx, false)
	require.NoError(t, err)
	require.Len(t, instances, 2)
	for _, instance := range instances {
		require.Equal(t, instance.Workspace+"-password", instance.Metadata.GetDataSources()[0].GetPassword())
	}
}

func TestUpdateInstanceWithoutWorkspace(t *testing.T) {
	t.Parallel()
	ctx, _, s := newInstanceProjectFixture(t)
	instance, err := s.CreateInstance(ctx, &store.InstanceMessage{
		ResourceID: "workspace-instance",
		Workspace:  "default",
		Metadata:   testInstanceMetadata(),
	})
	require.NoError(t, err)

	updated, err := s.UpdateInstance(ctx, &store.UpdateInstanceMessage{
		ResourceID: &instance.ResourceID,
		Metadata:   testInstanceMetadata(),
	})
	require.NoError(t, err)
	require.Equal(t, "default", updated.Workspace)
	require.Nil(t, updated.ProjectID)
}

func TestCreateProjectInstanceRejectsDefaultDeletedAndMissingProject(t *testing.T) {
	t.Parallel()
	ctx, _, s := newInstanceProjectFixture(t)
	for _, projectID := range []string{"default", "deleted-project", "missing-project"} {
		t.Run(projectID, func(t *testing.T) {
			t.Parallel()
			_, err := s.CreateInstance(ctx, &store.InstanceMessage{
				ResourceID: projectID + "-instance",
				Workspace:  "default",
				ProjectID:  &projectID,
				Metadata:   testInstanceMetadata(),
			})
			require.Error(t, err)
		})
	}
}

func TestDeleteProjectDeletesProjectInstancesAndKeepsWorkspaceInstanceDatabases(t *testing.T) {
	t.Parallel()
	ctx, db, s := newInstanceProjectFixture(t)
	_, err := db.ExecContext(ctx, `
		UPDATE project SET deleted = TRUE WHERE resource_id = 'project-a';
		INSERT INTO instance (resource_id, workspace, project) VALUES
			('project-instance', 'default', 'project-a'),
			('workspace-instance', 'default', NULL);
		INSERT INTO db (instance, name, project) VALUES
			('project-instance', 'project-db', 'project-a'),
			('workspace-instance', 'workspace-db', 'project-a');
	`)
	require.NoError(t, err)

	require.NoError(t, s.DeleteProjects(ctx, "default", "project-a"))

	var projectInstanceCount, projectDatabaseCount int
	require.NoError(t, db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM instance WHERE resource_id = 'project-instance'
	`).Scan(&projectInstanceCount))
	require.NoError(t, db.QueryRowContext(ctx, `
		SELECT COUNT(*) FROM db WHERE instance = 'project-instance'
	`).Scan(&projectDatabaseCount))
	require.Zero(t, projectInstanceCount)
	require.Zero(t, projectDatabaseCount)

	var workspaceDatabaseProject string
	require.NoError(t, db.QueryRowContext(ctx, `
		SELECT project FROM db WHERE instance = 'workspace-instance' AND name = 'workspace-db'
	`).Scan(&workspaceDatabaseProject))
	require.Equal(t, "default", workspaceDatabaseProject)
}

func TestDeleteProjectInstancePurgesHistory(t *testing.T) {
	t.Parallel()
	ctx, db, s := newInstanceProjectFixture(t)
	_, err := db.ExecContext(ctx, `
		INSERT INTO instance (resource_id, workspace, project, deleted) VALUES
			('project-instance', 'default', 'project-a', TRUE);
		INSERT INTO db (instance, name, project) VALUES
			('project-instance', 'project-db', 'project-a');
		INSERT INTO query_history (resource_id, creator, project, database, statement, type) VALUES
			('history-a', '[email protected]', 'project-a',
			 'projects/project-a/instances/project-instance/databases/project-db', '', 'QUERY');
	`)
	require.NoError(t, err)

	require.NoError(t, s.DeleteInstance(ctx, "default", "project-instance"))

	for _, table := range []string{"instance", "db", "query_history"} {
		var count int
		require.NoError(t, db.QueryRowContext(ctx, "SELECT COUNT(*) FROM "+table).Scan(&count))
		require.Zero(t, count, "%s should be removed", table)
	}
}