Skip to content

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

9.8 KB
Raw Blame
package export

import (
	"context"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/assert"

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	_ "github.com/bytebase/bytebase/backend/plugin/parser/tidb"
	"github.com/bytebase/bytebase/backend/store"
	"github.com/bytebase/bytebase/backend/store/model"
)

func TestGetSQLStatementPrefix(t *testing.T) {
	tests := []struct {
		engine       storepb.Engine
		resourceList []base.SchemaResource
		columnNames  []string
		want         string
	}{
		{
			engine:       storepb.Engine_MYSQL,
			resourceList: nil,
			columnNames:  []string{"a"},
			want:         "INSERT INTO `<table_name>` (`a`) VALUES (",
		},
		{
			engine:       storepb.Engine_MYSQL,
			resourceList: []base.SchemaResource{{Database: "db1", Schema: "", Table: "table1"}},
			columnNames:  []string{"a", "b"},
			want:         "INSERT INTO `table1` (`a`,`b`) VALUES (",
		},
		{
			engine:       storepb.Engine_TIDB,
			resourceList: []base.SchemaResource{{Database: "db1", Schema: "", Table: "cbt_plans"}},
			columnNames:  []string{"id", "app_code", "locale"},
			want:         "INSERT INTO `cbt_plans` (`id`,`app_code`,`locale`) VALUES (",
		},
		{
			engine:       storepb.Engine_POSTGRES,
			resourceList: nil,
			columnNames:  []string{"a"},
			want:         "INSERT INTO \"<table_name>\" (\"a\") VALUES (",
		},
		{
			engine:       storepb.Engine_POSTGRES,
			resourceList: []base.SchemaResource{{Database: "db1", Schema: "", Table: "table1"}},
			columnNames:  []string{"a"},
			want:         "INSERT INTO \"table1\" (\"a\") VALUES (",
		},
		{
			engine:       storepb.Engine_POSTGRES,
			resourceList: []base.SchemaResource{{Database: "db1", Schema: "schema1", Table: "table1"}},
			columnNames:  []string{"a"},
			want:         "INSERT INTO \"schema1\".\"table1\" (\"a\") VALUES (",
		},
	}
	a := assert.New(t)

	for _, test := range tests {
		got, err := SQLStatementPrefix(test.engine, test.resourceList, test.columnNames)
		a.NoError(err)
		a.Equal(test.want, got)
	}
}

func TestExportSQL(t *testing.T) {
	tests := []struct {
		engine          storepb.Engine
		statementPrefix string
		result          *v1pb.QueryResult
		want            string
	}{
		{
			engine:          storepb.Engine_MYSQL,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_BoolValue{BoolValue: true},
							},
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "abc"},
							},
							{
								Kind: &v1pb.RowValue_NullValue{},
							},
						},
					},
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_BoolValue{BoolValue: false},
							},
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "abc"},
							},
							{
								Kind: &v1pb.RowValue_NullValue{},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES (true,'abc',NULL);\nINSERT INTO `<table_name>` (`a`) VALUES (false,'abc',NULL);",
		},
		{
			engine:          storepb.Engine_MYSQL,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "a\nbc"},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES ('a\\nbc');",
		},
		{
			engine:          storepb.Engine_MYSQL,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "a'b"},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES ('a''b');",
		},
		{
			engine:          storepb.Engine_MYSQL,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "a\b"},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES ('a\\b');",
		},
		{
			engine:          storepb.Engine_POSTGRES,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "a\nbc"},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES ('a\nbc');",
		},
		{
			engine:          storepb.Engine_POSTGRES,
			statementPrefix: "INSERT INTO `<table_name>` (`b`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "a\\bc"},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`b`) VALUES ( E'a\\\\bc');",
		},
		{
			engine:          storepb.Engine_MYSQL,
			statementPrefix: "INSERT INTO `<table_name>` (`a`) VALUES (",
			result: &v1pb.QueryResult{
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_BytesValue{BytesValue: []byte{0b101}},
							},
						},
					},
				},
			},
			want: "INSERT INTO `<table_name>` (`a`) VALUES (0x05);",
		},
	}
	a := assert.New(t)

	for _, test := range tests {
		got, err := SQL(test.engine, test.statementPrefix, test.result)
		a.NoError(err)
		a.Equal(test.want, string(got))
	}
}

func TestGetExcelColumnName(t *testing.T) {
	a := assert.New(t)

	tests := []struct {
		index int
		want  string
	}{
		{
			index: 0,
			want:  "A",
		},
		{
			index: 3,
			want:  "D",
		},
		{
			index: 25,
			want:  "Z",
		},
		{
			index: 26,
			want:  "AA",
		},
		{
			index: 27,
			want:  "AB",
		},
		{
			index: ExcelMaxColumn - 1,
			want:  "ZZZ",
		},
	}

	for _, test := range tests {
		got, err := ExcelColumnName(test.index)
		a.NoError(err)
		a.Equal(test.want, got)
	}
}

func TestExportJSON(t *testing.T) {
	tests := []struct {
		result *v1pb.QueryResult
		want   string
	}{
		{
			result: &v1pb.QueryResult{
				ColumnNames: []string{"a"},
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_BytesValue{BytesValue: []byte{0b101}},
							},
						},
					},
				},
			},
			want: `[
  {
    "a": "BQ=="
  }
]`,
		},
		{
			result: &v1pb.QueryResult{
				ColumnNames: []string{"id", "name", "email", "age"},
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{
								Kind: &v1pb.RowValue_Int32Value{Int32Value: 1},
							},
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "Alice"},
							},
							{
								Kind: &v1pb.RowValue_StringValue{StringValue: "[email protected]"},
							},
							{
								Kind: &v1pb.RowValue_Int32Value{Int32Value: 20},
							},
						},
					},
				},
			},
			want: `[
  {
    "age": 20,
    "email": "[email protected]",
    "id": 1,
    "name": "Alice"
  }
]`,
		},
	}

	a := assert.New(t)
	for _, test := range tests {
		got, err := JSON(test.result)
		a.NoError(err)
		a.Equal(test.want, string(got))
	}
}

func TestExportCSV(t *testing.T) {
	tests := []struct {
		result *v1pb.QueryResult
		want   string
	}{
		{
			// Byte values render as quoted hex. This was only ever asserted through a
			// MySQL BIT column in backend/tests; it belongs here, where the rendering
			// lives, and covers every engine that returns bytes.
			result: &v1pb.QueryResult{
				ColumnNames: []string{"gender", "height"},
				Rows: []*v1pb.QueryRow{
					{
						Values: []*v1pb.RowValue{
							{Kind: &v1pb.RowValue_BytesValue{BytesValue: []byte{0x00}}},
							{Kind: &v1pb.RowValue_BytesValue{BytesValue: []byte{0x7f}}},
						},
					},
				},
			},
			want: "gender,height\n\"0x00\",\"0x7f\"",
		},
		{
			result: &v1pb.QueryResult{
				ColumnNames: []string{"a"},
				Rows: []*v1pb.QueryRow{
					{Values: []*v1pb.RowValue{{Kind: &v1pb.RowValue_Int64Value{Int64Value: 1}}}},
				},
			},
			want: "a\n1",
		},
	}
	a := assert.New(t)

	for _, test := range tests {
		got, err := CSV(test.result)
		a.NoError(err)
		a.Equal(test.want, string(got))
	}
}

func TestGetResourcesTiDB(t *testing.T) {
	a := assert.New(t)
	ctx := context.Background()

	instance := &store.InstanceMessage{
		ResourceID: "test-tidb-instance",
		Metadata: &storepb.Instance{
			Engine: storepb.Engine_TIDB,
		},
	}

	getDatabaseMetadataFunc := func(_ context.Context, _ string, _ string) (string, *model.DatabaseMetadata, error) {
		dbMeta := model.NewDatabaseMetadata(
			&metadatapb.DatabaseSchemaMetadata{
				Name: "",
				Schemas: []*metadatapb.SchemaMetadata{
					{
						Name: "",
						Tables: []*metadatapb.TableMetadata{
							{
								Name: "cbt_plans",
								Columns: []*metadatapb.ColumnMetadata{
									{Name: "id", Type: "bigint"},
								},
							},
						},
					},
				},
			},
			nil,
			nil,
			storepb.Engine_TIDB,
			false,
		)
		return "", dbMeta, nil
	}

	listDatabaseNamesFunc := func(_ context.Context, _ string) ([]string, error) {
		return nil, nil
	}

	getLinkedDatabaseMetadataFunc := func(_ context.Context, _, _, _ string) (string, string, *model.DatabaseMetadata, error) {
		return "", "", nil, nil
	}

	// With databaseName = "":
	// - If TiDB hits default case: returns error "database must be specified"
	// - If TiDB hits MySQL case: calls GetQuerySpan and returns resources
	resources, err := GetResources(
		ctx,
		nil,
		storepb.Engine_TIDB,
		"",
		"SELECT id FROM cbt_plans;",
		instance,
		getDatabaseMetadataFunc,
		listDatabaseNamesFunc,
		getLinkedDatabaseMetadataFunc,
	)

	a.NoError(err, "TiDB should not fall through to default case which returns 'database must be specified'")
	a.Len(resources, 1)
	a.Equal("cbt_plans", resources[0].Table)
}