Skip to content

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

7.4 KB
Raw Blame
package tsql

import (
	"context"
	"io"
	"os"
	"slices"
	"strings"
	"testing"

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

	"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"
)

type candidatesTest struct {
	Description string           `yaml:"description"`
	Input       string           `yaml:"input"`
	Want        []base.Candidate `yaml:"want"`
}

// TestCompletion tests the Transact-SQL auto-completion, all the test cases are stored in the file.
//
// - Description: The description of the test case.
//
// - Input: The input statement with the caret position marked by "|".
//
// - Want: The expected completion candidates.
//
// Our Test suite will determine the caret position - line(0-based) and column(1-based) by the position of the "|", actually,
// this caret position is as same as the position in the monaco-editor(LSP?).
func TestCompletion(t *testing.T) {
	tests := []candidatesTest{}

	const (
		record = false
	)
	var (
		filepath = "test-data/test_completion.yaml"
	)

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

	byteValue, err := io.ReadAll(yamlFile)
	a.NoError(yamlFile.Close())
	a.NoError(err)
	a.NoError(yaml.Unmarshal(byteValue, &tests))

	for i, t := range tests {
		statement, caretLine, caretPosition := getCaretPosition(t.Input)
		getter, lister := buildMockDatabaseMetadataGetterLister()
		results, err := Completion(context.Background(), base.CompletionContext{
			Scene:             base.SceneTypeAll,
			DefaultDatabase:   "Company",
			Metadata:          getter,
			ListDatabaseNames: lister,
		}, statement, caretLine, caretPosition)
		a.NoErrorf(err, "Case %02d: %s", i, t.Description)
		var filteredResult []base.Candidate
		for _, r := range results {
			switch r.Type {
			case base.CandidateTypeKeyword, base.CandidateTypeFunction:
				continue
			default:
				filteredResult = append(filteredResult, r)
			}
		}
		slices.SortFunc(filteredResult, func(a, b base.Candidate) int {
			if a.Type != b.Type {
				if a.Type < b.Type {
					return -1
				}
				return 1
			}
			if a.Text != b.Text {
				if a.Text < b.Text {
					return -1
				}
				return 1
			}
			if a.Definition < b.Definition {
				return -1
			}
			if a.Definition > b.Definition {
				return 1
			}
			return 0
		})

		if record {
			tests[i].Want = filteredResult
		} else {
			a.Equalf(t.Want, filteredResult, "Case %02d: %s", i, t.Input)
		}
	}

	if record {
		yamltest.Record(t, filepath, tests)
	}
}

func TestCompletionOmniTableRefRule(t *testing.T) {
	statement, caretLine, caretPosition := getCaretPosition("TRUNCATE TABLE |")
	getter, lister := buildMockDatabaseMetadataGetterLister()
	results, err := Completion(context.Background(), base.CompletionContext{
		Scene:             base.SceneTypeAll,
		DefaultDatabase:   "Company",
		Metadata:          getter,
		ListDatabaseNames: lister,
	}, statement, caretLine, caretPosition)
	require.NoError(t, err)

	require.Contains(t, results, base.Candidate{
		Text: "Employees",
		Type: base.CandidateTypeTable,
	})
}

func TestCompletionUnsyncedDatabase(t *testing.T) {
	for _, input := range []string{"SELECT * FROM |", "SELECT |"} {
		statement, caretLine, caretPosition := getCaretPosition(input)
		results, err := Completion(context.Background(), base.CompletionContext{
			Scene:           base.SceneTypeAll,
			DefaultDatabase: "Company",
			Metadata: func(context.Context, string, string) (string, *model.DatabaseMetadata, error) {
				return "", nil, nil
			},
			ListDatabaseNames: func(context.Context, string) ([]string, error) {
				return []string{"Company"}, nil
			},
		}, statement, caretLine, caretPosition)
		require.NoError(t, err, input)
		for _, candidate := range results {
			require.NotContains(t, []base.CandidateType{base.CandidateTypeSchema, base.CandidateTypeTable, base.CandidateTypeColumn}, candidate.Type, input)
		}
	}
}

func TestCompletionDoesNotDependOnANTLR(t *testing.T) {
	content, err := os.ReadFile("completion.go")
	require.NoError(t, err)
	source := string(content)
	require.NotContains(t, source, "github.com/antlr4-go/antlr/v4")
	require.NotContains(t, source, "github.com/bytebase/parser/tsql")
}

func getCaretPosition(statement string) (string, int, int) {
	lines := strings.Split(statement, "\n")
	for i, line := range lines {
		if offset := strings.Index(line, "|"); offset != -1 {
			newLine := strings.Replace(line, "|", "", 1)
			lines[i] = newLine
			return strings.Join(lines, "\n"), i + 1, offset
		}
	}
	panic("caret position not found")
}

var databaseMetadatas = []*metadatapb.DatabaseSchemaMetadata{
	{
		Name: "Company",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "dbo",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "Employees",
						Columns: []*metadatapb.ColumnMetadata{
							{
								Name: "Id",
								Type: "int",
							},
							{
								Name: "Name",
								Type: "varchar",
							},
						},
					},
					{
						Name: "Address",
						Columns: []*metadatapb.ColumnMetadata{
							{
								Name: "EmployeeId",
								Type: "int",
							},
							{
								Name: "Street",
								Type: "varchar",
							},
						},
					},
				},
				Sequences: []*metadatapb.SequenceMetadata{
					{
						Name: "EmployeeIdSeq",
					},
					{
						Name: "OrderSeq",
					},
				},
				Procedures: []*metadatapb.ProcedureMetadata{
					{
						Name: "SyncEmployees",
					},
				},
			},
			{
				Name: "MySchema",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "SalaryLevel",
						Columns: []*metadatapb.ColumnMetadata{
							{
								Name: "Id",
								Type: "int",
							},
							{
								Name: "SalaryUpBound",
								Type: "int",
							},
						},
					},
				},
				Views: []*metadatapb.ViewMetadata{
					{
						Name: "SalaryView",
					},
				},
				Sequences: []*metadatapb.SequenceMetadata{
					{
						Name: "SalarySeq",
					},
				},
				Procedures: []*metadatapb.ProcedureMetadata{
					{
						Name: "SyncSalary",
					},
				},
			},
		},
	},
	{
		Name: "School",
		Schemas: []*metadatapb.SchemaMetadata{
			{
				Name: "dbo",
				Tables: []*metadatapb.TableMetadata{
					{
						Name: "Student",
						Columns: []*metadatapb.ColumnMetadata{
							{
								Name: "Id",
								Type: "int",
							},
							{
								Name: "ParentName",
								Type: "varchar",
							},
						},
					},
				},
				Sequences: []*metadatapb.SequenceMetadata{
					{
						Name: "StudentSeq",
					},
				},
				Procedures: []*metadatapb.ProcedureMetadata{
					{
						Name: "SyncStudents",
					},
				},
			},
		},
	},
}

func buildMockDatabaseMetadataGetterLister() (base.GetDatabaseMetadataFunc, base.ListDatabaseNamesFunc) {
	return func(_ context.Context, _, databaseName string) (string, *model.DatabaseMetadata, error) {
			m := make(map[string]*model.DatabaseMetadata)
			for _, metadata := range databaseMetadatas {
				m[metadata.Name] = model.NewDatabaseMetadata(metadata, nil, nil, storepb.Engine_MSSQL, false /* isObjectCaseSensitive */)
			}

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

			return "", nil, errors.Errorf("database %q not found", databaseName)
		}, func(context.Context, string) ([]string, error) {
			var names []string
			for _, metadata := range databaseMetadatas {
				names = append(names, metadata.Name)
			}
			return names, nil
		}
}