Skip to content

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

13.2 KB
Raw Blame
package plsql

import (
	"context"
	"io"
	"os"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	oracleast "github.com/bytebase/omni/oracle/ast"
	"github.com/pkg/errors"
	"github.com/stretchr/testify/require"
	"gopkg.in/yaml.v3"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/yamltest"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	"github.com/bytebase/bytebase/backend/store/model"
)

const (
	instanceIDA = "INSTANCE_ID_A"
	instanceIDB = "INSTANCE_ID_B"
)

func TestGetQuerySpan(t *testing.T) {
	type testCase struct {
		Description     string `yaml:"description,omitempty"`
		Statement       string `yaml:"statement,omitempty"`
		DefaultDatabase string `yaml:"defaultDatabase,omitempty"`
		// Metadata is the protojson encoded metadatapb.DatabaseSchemaMetadata,
		// if it's empty, we will use the defaultDatabaseMetadata.
		Metadata              string              `yaml:"metadata,omitempty"`
		CrossDatabaseMetadata string              `yaml:"crossDatabaseMetadata,omitempty"`
		QuerySpan             *base.YamlQuerySpan `yaml:"querySpan,omitempty"`
	}

	const (
		record       = false
		testDataPath = "test-data/query_span.yaml"
	)

	a := require.New(t)
	yamlFile, err := os.Open(testDataPath)
	a.NoError(err)

	var testCases []testCase
	byteValue, err := io.ReadAll(yamlFile)
	a.NoError(err)
	a.NoError(yamlFile.Close())
	a.NoError(yaml.Unmarshal(byteValue, &testCases))

	for i, tc := range testCases {
		metadata := &metadatapb.DatabaseSchemaMetadata{}
		a.NoError(common.ProtojsonUnmarshaler.Unmarshal([]byte(tc.Metadata), metadata))
		list := []*metadatapb.DatabaseSchemaMetadata{metadata}
		crossDatabase := &metadatapb.DatabaseSchemaMetadata{}
		if tc.CrossDatabaseMetadata != "" {
			a.NoError(common.ProtojsonUnmarshaler.Unmarshal([]byte(tc.CrossDatabaseMetadata), crossDatabase))
			list = append(list, crossDatabase)
		}
		databaseMetadataGetter, databaseNamesLister, linkedDatabaseMetadataGetter := buildMockDatabaseMetadataGetter(list)
		result, err := GetQuerySpan(context.TODO(), base.GetQuerySpanContext{
			InstanceID:                    instanceIDA,
			GetDatabaseMetadataFunc:       databaseMetadataGetter,
			ListDatabaseNamesFunc:         databaseNamesLister,
			GetLinkedDatabaseMetadataFunc: linkedDatabaseMetadataGetter,
		}, base.Statement{Text: tc.Statement}, tc.DefaultDatabase, "", false)
		a.NoError(err)
		a.NotNil(result)
		resultYaml := result.ToYaml()
		if record {
			testCases[i].QuerySpan = resultYaml
		} else {
			a.Equal(tc.QuerySpan, resultYaml, "statement: %s", tc.Statement)
		}
	}

	if record {
		yamltest.Record(t, testDataPath, testCases)
	}
}

func buildMockDatabaseMetadataGetter(defaultMetadata []*metadatapb.DatabaseSchemaMetadata) (base.GetDatabaseMetadataFunc, base.ListDatabaseNamesFunc, base.GetLinkedDatabaseMetadataFunc) {
	return func(_ context.Context, instanceID, databaseName string) (string, *model.DatabaseMetadata, error) {
			databaseMetadata := defaultMetadata
			if instanceID == instanceIDB {
				databaseMetadata = getLinkedDatabaseMetadata()
			}
			m := make(map[string]*model.DatabaseMetadata)
			for _, metadata := range databaseMetadata {
				m[metadata.Name] = model.NewDatabaseMetadata(metadata, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */)
			}

			if databaseMetadata, ok := m[databaseName]; ok {
				return databaseName, databaseMetadata, nil
			}

			return "", nil, errors.Errorf("database %q not found", databaseName)
		}, func(_ context.Context, instanceID string) ([]string, error) {
			databaseMetadata := defaultMetadata
			if instanceID == instanceIDB {
				return listLinkedDatabaseNames()
			}
			names := make([]string, 0, len(databaseMetadata))
			for _, metadata := range databaseMetadata {
				names = append(names, metadata.Name)
			}
			return names, nil
		}, func(_ context.Context, _, linkedDatabaseName, schemaName string) (string, string, *model.DatabaseMetadata, error) {
			databaseMetadata := defaultMetadata
			var linkedDBInfo *metadatapb.LinkedDatabaseMetadata
			for _, metadata := range databaseMetadata {
				for _, linkedDatabase := range metadata.GetLinkedDatabases() {
					if linkedDatabase.Name == linkedDatabaseName {
						linkedDBInfo = linkedDatabase
						break
					}
				}
				if linkedDBInfo != nil {
					break
				}
			}
			if linkedDBInfo == nil {
				// Production resolves an unknown link to nothing, not to an error.
				return "", "", nil, nil
			}

			// As in production, the written schema wins over the link user, and a link resolves to
			// nothing when no database of that name exists. LOOPBACK points back at the connected
			// instance; every other link reaches instance B.
			databaseName := linkedDBInfo.Username
			if schemaName != "" {
				databaseName = schemaName
			}
			candidates, instanceID := getLinkedDatabaseMetadata(), instanceIDB
			if linkedDBInfo.Name == "LOOPBACK" {
				candidates, instanceID = defaultMetadata, instanceIDA
			}
			for _, metadata := range candidates {
				if metadata.Name == databaseName {
					return instanceID, metadata.Name, model.NewDatabaseMetadata(metadata, nil, nil, storepb.Engine_ORACLE, true /* isObjectCaseSensitive */), nil
				}
			}

			return "", "", nil, nil
		}
}

func listLinkedDatabaseNames() ([]string, error) {
	return []string{"SCHEMA1", "SCHEMA2"}, nil
}

func getLinkedDatabaseMetadata() []*metadatapb.DatabaseSchemaMetadata {
	return []*metadatapb.DatabaseSchemaMetadata{
		{
			Name: "SCHEMA1",
			Schemas: []*metadatapb.SchemaMetadata{
				{
					Name: "",
					Tables: []*metadatapb.TableMetadata{
						{
							Name: "LT1",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "LC1",
									Type: "int",
								},
							},
						},
						{
							Name: "LT2",
							Columns: []*metadatapb.ColumnMetadata{
								{
									Name: "LC1",
									Type: "int",
								},
								{
									Name: "LC2",
									Type: "int",
								},
							},
						},
					},
					Views: []*metadatapb.ViewMetadata{
						{
							Name: "LV1",
							Definition: `SELECT LC1, LC2
											FROM LT2
							`,
						},
					},
				},
			},
		},
	}
}

func TestGetQuerySpanRefusesSystemTableWithLink(t *testing.T) {
	metadata := &metadatapb.DatabaseSchemaMetadata{
		Name:            "PUBLIC",
		Schemas:         []*metadatapb.SchemaMetadata{{Name: "", Tables: []*metadatapb.TableMetadata{{Name: "T", Columns: []*metadatapb.ColumnMetadata{{Name: "A"}}}}}},
		LinkedDatabases: []*metadatapb.LinkedDatabaseMetadata{{Name: "REMOTE", Username: "SCHEMA1"}},
	}
	databaseMetadataGetter, databaseNamesLister, linkedDatabaseMetadataGetter := buildMockDatabaseMetadataGetter([]*metadatapb.DatabaseSchemaMetadata{metadata})
	gCtx := base.GetQuerySpanContext{
		InstanceID:                    instanceIDA,
		GetDatabaseMetadataFunc:       databaseMetadataGetter,
		ListDatabaseNamesFunc:         databaseNamesLister,
		GetLinkedDatabaseMetadataFunc: linkedDatabaseMetadataGetter,
	}
	// A linked table is a user table: next to a local system table the statement is mixed,
	// and on its own it is never an info-schema read.
	_, err := GetQuerySpan(context.TODO(), gCtx, base.Statement{Text: "SELECT * FROM SYS.DBA_USERS, T@REMOTE;"}, "PUBLIC", "", false)
	require.ErrorIs(t, err, base.MixUserSystemTablesError)
	span, err := GetQuerySpan(context.TODO(), gCtx, base.Statement{Text: "SELECT * FROM SYS.DBA_USERS@REMOTE;"}, "PUBLIC", "", false)
	require.NoError(t, err)
	require.Equal(t, base.Select, span.Type)
}

func TestGetQuerySpanParenthesizedQuery(t *testing.T) {
	metadata := &metadatapb.DatabaseSchemaMetadata{
		Name:    "PUBLIC",
		Schemas: []*metadatapb.SchemaMetadata{{Name: "", Tables: []*metadatapb.TableMetadata{{Name: "T", Columns: []*metadatapb.ColumnMetadata{{Name: "A"}, {Name: "B"}}}}}},
	}
	databaseMetadataGetter, databaseNamesLister, linkedDatabaseMetadataGetter := buildMockDatabaseMetadataGetter([]*metadatapb.DatabaseSchemaMetadata{metadata})
	gCtx := base.GetQuerySpanContext{
		InstanceID:                    instanceIDA,
		GetDatabaseMetadataFunc:       databaseMetadataGetter,
		ListDatabaseNamesFunc:         databaseNamesLister,
		GetLinkedDatabaseMetadataFunc: linkedDatabaseMetadataGetter,
	}
	columnA := base.ColumnResource{Database: "PUBLIC", Table: "T", Column: "A"}
	columnB := base.ColumnResource{Database: "PUBLIC", Table: "T", Column: "B"}
	tests := []struct {
		statement string
		want      base.SourceColumnSet
	}{
		{statement: "(SELECT a FROM t)", want: base.SourceColumnSet{columnA: true}},
		{statement: "(SELECT a FROM t) ORDER BY a", want: base.SourceColumnSet{columnA: true}},
		{statement: "((SELECT a FROM t)) ORDER BY 1", want: base.SourceColumnSet{columnA: true}},
		{statement: "(SELECT a FROM t) FETCH FIRST 5 ROWS ONLY", want: base.SourceColumnSet{columnA: true}},
		{statement: "WITH w AS (SELECT a FROM t) (SELECT a FROM w)", want: base.SourceColumnSet{columnA: true}},
		{statement: "(SELECT a FROM t) UNION (SELECT b FROM t) ORDER BY 1", want: base.SourceColumnSet{columnA: true, columnB: true}},
		{statement: "SELECT a FROM t EXCEPT SELECT b FROM t", want: base.SourceColumnSet{columnA: true, columnB: true}},
	}
	for _, tc := range tests {
		t.Run(tc.statement, func(t *testing.T) {
			span, err := GetQuerySpan(context.TODO(), gCtx, base.Statement{Text: tc.statement}, "PUBLIC", "", false)
			require.NoError(t, err)
			require.Len(t, span.Results, 1)
			require.Equal(t, "A", span.Results[0].Name)
			require.Equal(t, tc.want, span.Results[0].SourceColumns)
		})
	}
}

func TestGetAccessTables(t *testing.T) {
	tests := []struct {
		statement string
		expected  []base.SchemaResource
		linked    []base.SchemaResource
	}{
		{
			statement: "SELECT * FROM t1 WHERE c1 = 1",
			expected: []base.SchemaResource{
				{
					Database: "DB",
					Table:    "T1",
				},
			},
		},
		{
			statement: "SELECT * FROM schema1.t1 JOIN schema2.t2 ON t1.c1 = t2.c1;",
			expected: []base.SchemaResource{
				{
					Database: "SCHEMA1",
					Table:    "T1",
				},
				{
					Database: "SCHEMA2",
					Table:    "T2",
				},
			},
		},
		{
			statement: "SELECT a > (select max(a) from t1) FROM t2;",
			expected: []base.SchemaResource{
				{
					Database: "DB",
					Table:    "T1",
				},
				{
					Database: "DB",
					Table:    "T2",
				},
			},
		},
		{
			statement: "SELECT * FROM schema1.t1@remote;",
			linked: []base.SchemaResource{
				{
					Database:     "SCHEMA1",
					Table:        "T1",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT * FROM t1@remote;",
			linked: []base.SchemaResource{
				{
					Table:        "T1",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT * FROM t1, schema1.t2@remote WHERE EXISTS (SELECT 1 FROM t3@remote);",
			expected: []base.SchemaResource{
				{
					Database: "DB",
					Table:    "T1",
				},
			},
			linked: []base.SchemaResource{
				{
					Database:     "SCHEMA1",
					Table:        "T2",
					LinkedServer: "REMOTE",
				},
				{
					Table:        "T3",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT * FROM sys.dba_users@remote;",
			linked: []base.SchemaResource{
				{
					Database:     "SYS",
					Table:        "DBA_USERS",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT sysdate FROM dual;",
		},
		// Through a link an unqualified DUAL resolves in the link user's schema first.
		{
			statement: "SELECT sysdate FROM dual@remote;",
			linked: []base.SchemaResource{
				{
					Table:        "DUAL",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT * FROM t1, sys.dual;",
			expected: []base.SchemaResource{
				{
					Database: "DB",
					Table:    "T1",
				},
			},
		},
		{
			statement: "SELECT sysdate FROM sys.dual@remote;",
		},
		// Oracle accepts the public synonym's owner only quoted (ORA-00903 unquoted).
		{
			statement: `SELECT sysdate FROM "PUBLIC".dual;`,
		},
		// A schema-qualified DUAL is a table in that schema, local or remote.
		{
			statement: "SELECT * FROM s.dual@remote;",
			linked: []base.SchemaResource{
				{
					Database:     "S",
					Table:        "DUAL",
					LinkedServer: "REMOTE",
				},
			},
		},
		{
			statement: "SELECT * FROM app.dual;",
			expected: []base.SchemaResource{
				{
					Database: "APP",
					Table:    "DUAL",
				},
			},
		},
		// Oracle rejects a link after a partition clause (ORA-03048), so it names none.
		{
			statement: "SELECT * FROM t1 PARTITION (p1)@remote;",
			expected: []base.SchemaResource{
				{
					Database: "DB",
					Table:    "T1",
				},
			},
		},
		// `t@remote@q` names the link REMOTE@Q.
		{
			statement: "SELECT * FROM s.t@remote@q;",
			linked: []base.SchemaResource{
				{
					Database:     "S",
					Table:        "T",
					LinkedServer: "REMOTE@Q",
				},
			},
		},
		{
			statement: "SELECT * FROM t1@remote@q, t1@remote;",
			linked: []base.SchemaResource{
				{
					Table:        "T1",
					LinkedServer: "REMOTE@Q",
				},
				{
					Table:        "T1",
					LinkedServer: "REMOTE",
				},
			},
		},
	}

	for _, test := range tests {
		results, err := ParsePLSQL(test.statement)
		require.NoError(t, err)
		require.NotEmpty(t, results)
		require.NotEmpty(t, results.Items)
		raw, ok := results.Items[0].(*oracleast.RawStmt)
		require.True(t, ok)
		local, linked := collectOmniAccessTables("DB", raw.Stmt)
		require.Equal(t, test.expected, local, test.statement)
		require.Equal(t, test.linked, linked, test.statement)
	}
}