Skip to content

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

15.3 KB
Raw Blame
package advisor

import (
	"context"
	"database/sql"
	"io"
	"os"
	"path/filepath"
	"slices"
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/require"
	"google.golang.org/protobuf/proto"
	"gopkg.in/yaml.v3"

	"github.com/bytebase/bytebase/backend/common/yamltest"
	"github.com/bytebase/bytebase/backend/component/sheet"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	database "github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/store/model"
)

var (
	_ database.Driver = (*MockDriver)(nil)
)

const (
	// MockOldIndexName is the mock old index for test.
	MockOldIndexName = "old_index"
	// MockOldUKName is the mock old unique key for test.
	MockOldUKName = "old_uk"
	// MockOldMySQLPKName is the mock old primary key for MySQL test.
	MockOldMySQLPKName = "PRIMARY"
	// MockOldPostgreSQLPKName is the mock old primary key for PostgreSQL test.
	MockOldPostgreSQLPKName = "old_pk"
	// MockTableName is the mock table for test.
	MockTableName = "tech_book"
)

var (
	// MockIndexColumnList is the mock index column list for test.
	MockIndexColumnList = []string{"id", "name"}
	// MockMySQLDatabase is the mock MySQL database for test.
	MockMySQLDatabase = &metadatapb.DatabaseSchemaMetadata{
		Name: "test",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Tables: []*metadatapb.TableMetadata{
					{
						Name: MockTableName,
						Columns: []*metadatapb.ColumnMetadata{
							{
								Name: "id",
								Type: "int",
							},
							{
								Name: "name",
								Type: "varchar(255)",
							},
							{
								Name: "creator",
								Type: "varchar(255)",
							},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{
								Name:        MockOldMySQLPKName,
								Expressions: []string{"id", "name"},
								Unique:      true,
								Primary:     true,
							},
							{
								Name:        MockOldUKName,
								Expressions: []string{"id", "name"},
								Unique:      true,
							},
							{
								Name:        MockOldIndexName,
								Expressions: []string{"id", "name"},
							},
						},
					},
					{
						Name: "orders",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "order_id", Type: "int"},
							{Name: "customer_name", Type: "varchar(255)"},
							{Name: "amount", Type: "decimal(10,2)"},
							{Name: "note", Type: "varchar(255)"},
							// name also exists here (non-indexed) so tests can verify
							// qualified lookups distinguish tech_book.name (indexed) from orders.name.
							{Name: "name", Type: "varchar(255)"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{
								Name:        "PRIMARY",
								Expressions: []string{"order_id"},
								Unique:      true,
								Primary:     true,
							},
							{
								Name:        "idx_customer",
								Expressions: []string{"customer_name"},
							},
							{
								Name:        "idx_amount",
								Expressions: []string{"amount"},
							},
						},
					},
				},
			},
		},
	}
	// MockPostgreSQLDatabase is the mock PostgreSQL database for test.
	MockPostgreSQLDatabase = &metadatapb.DatabaseSchemaMetadata{
		Name: "test",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "public",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: MockTableName,
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer", Position: 1},
							{Name: "name", Type: "text", Position: 2},
							{Name: "creator", Type: "text", Position: 3},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{
								Name:        MockOldPostgreSQLPKName,
								Expressions: []string{"id", "name"},
								Unique:      true,
								Primary:     true,
							},
							{
								Name:        MockOldUKName,
								Expressions: []string{"id", "name"},
								Unique:      true,
							},
							{
								Name:        MockOldIndexName,
								Expressions: []string{"id", "name"},
							},
						},
					},
					{
						Name: "orders",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "order_id", Type: "integer", Position: 1},
							{Name: "customer_name", Type: "text", Position: 2},
							{Name: "amount", Type: "numeric", Position: 3},
							{Name: "note", Type: "text", Position: 4},
							{Name: "name", Type: "text", Position: 5},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{Name: "orders_pkey", Expressions: []string{"order_id"}, Unique: true, Primary: true},
							{Name: "idx_orders_customer", Expressions: []string{"customer_name"}},
							{Name: "idx_orders_amount", Expressions: []string{"amount"}},
						},
					},
					{
						Name: "products",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "product_id", Type: "integer", Position: 1},
							{Name: "title", Type: "text", Position: 2},
							{Name: "price", Type: "numeric", Position: 3},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{Name: "products_pkey", Expressions: []string{"product_id"}, Unique: true, Primary: true},
							{Name: "idx_products_price", Expressions: []string{"price"}},
						},
					},
					{
						Name: "no_index_table",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "col_a", Type: "integer", Position: 1},
							{Name: "col_b", Type: "text", Position: 2},
						},
					},
					{
						Name: "insert_target",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "id", Type: "integer", Position: 1},
							{Name: "name", Type: "text", Position: 2},
						},
					},
				},
			},
			{
				Name: "bbdataarchive", // MySQL backup database for testing
			},
		},
	}
	MockMSSQLDatabase = &metadatapb.DatabaseSchemaMetadata{
		Name: "master",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "dbo",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "pokes",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "c1", Type: "int"},
							{Name: "c2", Type: "int"},
							{Name: "c3", Type: "int"},
							{Name: "c10", Type: "int"},
							{Name: "c20", Type: "int"},
							{Name: "foo", Type: "int"},
							{Name: "bar", Type: "int"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{
								Name:        "idx_0",
								Expressions: []string{"c1", "c2", "c3"},
							},
							{
								Name:        "idx_1",
								Expressions: []string{"c10", "c20"},
							},
						},
					},
					{
						Name: "pokes2",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "foo", Type: "int"},
							{Name: "bar", Type: "int"},
							// c1 also exists here (non-indexed) so tests can verify
							// qualified lookups distinguish pokes.c1 (indexed) from pokes2.c1.
							{Name: "c1", Type: "int"},
						},
					},
					{Name: "pokes3"},
				},
			},
			{
				Name: "sales",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "orders",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "order_id", Type: "int"},
							{Name: "customer_id", Type: "int"},
							{Name: "note", Type: "varchar"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{
								Name:        "PRIMARY",
								Expressions: []string{"order_id"},
								Unique:      true,
								Primary:     true,
							},
							{
								Name:        "idx_customer",
								Expressions: []string{"customer_id"},
							},
						},
					},
				},
			},
		},
	}
	// MockOracleDatabase is the mock Oracle database for test. Oracle sync
	// (`backend/plugin/db/oracle/sync.go`) stores SchemaMetadata{Name: ""}
	// — a single anonymous schema containing every object. The mock mirrors
	// this so advisors resolve tables the same way in tests and production.
	MockOracleDatabase = &metadatapb.DatabaseSchemaMetadata{
		Name: "TEST_DB",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "TECH_BOOK",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "ID", Type: "NUMBER"},
							{Name: "NAME", Type: "VARCHAR2(255)"},
							{Name: "CREATOR", Type: "VARCHAR2(255)"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{Name: "TECH_BOOK_PK", Expressions: []string{"ID", "NAME"}, Unique: true, Primary: true},
							{Name: "IDX_TECH_BOOK_NAME", Expressions: []string{"ID", "NAME"}},
						},
					},
					{
						Name: "ORDERS",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "ORDER_ID", Type: "NUMBER"},
							{Name: "CUSTOMER_NAME", Type: "VARCHAR2(255)"},
							{Name: "AMOUNT", Type: "NUMBER"},
							{Name: "NOTE", Type: "VARCHAR2(255)"},
							{Name: "NAME", Type: "VARCHAR2(255)"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{Name: "ORDERS_PK", Expressions: []string{"ORDER_ID"}, Unique: true, Primary: true},
							{Name: "IDX_ORDERS_CUSTOMER", Expressions: []string{"CUSTOMER_NAME"}},
							{Name: "IDX_ORDERS_AMOUNT", Expressions: []string{"AMOUNT"}},
						},
					},
					{
						Name: "PRODUCTS",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "PRODUCT_ID", Type: "NUMBER"},
							{Name: "TITLE", Type: "VARCHAR2(255)"},
							{Name: "PRICE", Type: "NUMBER"},
						},
						Indexes: []*metadatapb.IndexMetadata{
							{Name: "PRODUCTS_PK", Expressions: []string{"PRODUCT_ID"}, Unique: true, Primary: true},
							{Name: "IDX_PRODUCTS_PRICE", Expressions: []string{"PRICE"}},
						},
					},
					{
						Name: "NO_INDEX_TABLE",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "COL_A", Type: "NUMBER"},
							{Name: "COL_B", Type: "VARCHAR2(255)"},
						},
					},
					{
						Name: "INSERT_TARGET",
						Columns: []*metadatapb.ColumnMetadata{
							{Name: "ID", Type: "NUMBER"},
							{Name: "NAME", Type: "VARCHAR2(255)"},
						},
					},
				},
			},
		},
	}
)

// TestCase is the data struct for test.
type TestCase struct {
	Statement string            `yaml:"statement"`
	Want      []*storepb.Advice `yaml:"want,omitempty"`
}

// RunSQLReviewRuleTest helps to test the SQL review rule.
// The rule parameter should be a complete rule with Type, Level, and Payload already set.
func RunSQLReviewRuleTest(t *testing.T, rule *storepb.SQLReviewRule, dbType storepb.Engine, record bool) {
	require.NotNil(t, rule, "rule must not be nil")
	require.NotEqual(t, storepb.SQLReviewRule_TYPE_UNSPECIFIED, rule.Type, "rule type must be specified")

	var tests []TestCase

	fileName := strings.Map(func(r rune) rune {
		switch r {
		case '.', '-':
			return '_'
		default:
			return r
		}
	}, strings.ToLower(rule.Type.String()))
	filepath := filepath.Join("test", fileName+".yaml")
	yamlFile, err := os.Open(filepath)
	require.NoError(t, err)
	defer yamlFile.Close()

	byteValue, err := io.ReadAll(yamlFile)
	require.NoError(t, err)
	err = yaml.Unmarshal(byteValue, &tests)
	require.NoError(t, err, rule.Type)

	sm := sheet.NewManager()
	for i, tc := range tests {
		// Set metadata and database-specific settings based on engine type
		var schemaMetadata *metadatapb.DatabaseSchemaMetadata
		curDB := "TEST_DB"
		isCaseSensitive := false

		switch dbType {
		case storepb.Engine_POSTGRES:
			schemaMetadata = MockPostgreSQLDatabase
			isCaseSensitive = true
		case storepb.Engine_MSSQL:
			schemaMetadata = MockMSSQLDatabase
			curDB = "master"
		case storepb.Engine_MYSQL:
			schemaMetadata = MockMySQLDatabase
		case storepb.Engine_ORACLE:
			schemaMetadata = MockOracleDatabase
			curDB = "TEST_DB"
			// Match production: store.IsObjectCaseSensitive(Oracle) returns
			// true via the default branch in backend/store/instance.go. Test
			// fixtures use upper-case identifiers like the real Oracle sync.
			isCaseSensitive = true
		default:
			// Fallback to MySQL for engines without specific mock
			schemaMetadata = MockMySQLDatabase
		}

		// Create OriginalMetadata as DatabaseMetadata (read-only)
		// Clone to avoid mutations affecting future test cases
		metadata, ok := proto.Clone(schemaMetadata).(*metadatapb.DatabaseSchemaMetadata)
		require.True(t, ok, "failed to clone metadata")
		originalMetadata := model.NewDatabaseMetadata(metadata, nil, nil, dbType, isCaseSensitive)

		// Create FinalMetadata as DatabaseMetadata (mutable for walk-through)
		// Clone to avoid mutations affecting future test cases
		metadata, ok = proto.Clone(schemaMetadata).(*metadatapb.DatabaseSchemaMetadata)
		require.True(t, ok, "failed to clone metadata")
		finalMetadata := model.NewDatabaseMetadata(metadata, nil, nil, dbType, isCaseSensitive)

		// Use the rule provided by the caller (already has Type, Level, and Payload set).
		// Old test fixtures omit Engine for brevity; production rule configs must set it.
		testRule, ok := proto.Clone(rule).(*storepb.SQLReviewRule)
		require.True(t, ok, "failed to clone rule")
		if testRule.Engine == storepb.Engine_ENGINE_UNSPECIFIED {
			testRule.Engine = dbType
		}
		ruleList := []*storepb.SQLReviewRule{testRule}

		checkCtx := Context{
			DBType:                dbType,
			OriginalMetadata:      originalMetadata,
			FinalMetadata:         finalMetadata,
			Driver:                nil,
			CurrentDatabase:       curDB,
			DBSchema:              schemaMetadata,
			IsObjectCaseSensitive: isCaseSensitive,
			EnablePriorBackup:     true, // Enable backup for testing
			NoAppendBuiltin:       true,
			TenantMode:            true,
		}

		adviceList, err := SQLReviewCheck(t.Context(), sm, tc.Statement, ruleList, checkCtx)
		// Sort adviceList by (line, content)
		slices.SortFunc(adviceList, func(x, y *storepb.Advice) int {
			if x.GetStartPosition() == nil || y.GetStartPosition() == nil {
				if x.GetStartPosition() == nil && y.GetStartPosition() == nil {
					return 0
				} else if x.GetStartPosition() == nil {
					return -1
				}
				return 1
			}
			if x.GetStartPosition().Line != y.GetStartPosition().Line {
				if x.GetStartPosition().Line < y.GetStartPosition().Line {
					return -1
				}
				return 1
			}
			if x.Content < y.Content {
				return -1
			} else if x.Content > y.Content {
				return 1
			}
			return 0
		})

		require.NoError(t, err)
		if record {
			tests[i].Want = adviceList
		} else {
			require.Equalf(t, tc.Want, adviceList, "rule: %s, statements: %s", rule.Type, tc.Statement)
		}
	}

	if record {
		err := yamlFile.Close()
		require.NoError(t, err)
		yamltest.Record(t, filepath, tests)
	}
}

// MockDriver is the driver for test only.
type MockDriver struct {
}

// Open implements the Driver interface.
func (d *MockDriver) Open(_ context.Context, _ storepb.Engine, _ database.ConnectionConfig) (database.Driver, error) {
	return d, nil
}

// Close implements the Driver interface.
func (*MockDriver) Close(_ context.Context) error {
	return nil
}

// Ping implements the Driver interface.
func (*MockDriver) Ping(_ context.Context) error {
	return nil
}

// GetDB gets the database.
func (*MockDriver) GetDB() *sql.DB {
	return nil
}

// Execute implements the Driver interface.
func (*MockDriver) Execute(_ context.Context, _ string, _ database.ExecuteOptions) (int64, error) {
	return 0, nil
}

// QueryConn queries a SQL statement in a given connection.
func (*MockDriver) QueryConn(_ context.Context, _ *sql.Conn, _ string, _ database.QueryContext) ([]*v1pb.QueryResult, error) {
	return nil, nil
}

// SyncInstance implements the Driver interface.
func (*MockDriver) SyncInstance(_ context.Context) (*database.InstanceMetadata, error) {
	return nil, nil
}

// SyncDBSchema implements the Driver interface.
func (*MockDriver) SyncDBSchema(_ context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
	return nil, nil
}

// Dump implements the Driver interface.
func (*MockDriver) Dump(_ context.Context, _ io.Writer, _ *metadatapb.DatabaseSchemaMetadata) error {
	return nil
}