Multi Database MCP Server

package dbtools import ( "strings" "testing" "github.com/stretchr/testify/assert" ) // Our own simplified test versions of the functions with logger issues func testGetErrorLine(errorMsg string) int { if errorMsg == "ERROR at line 5" { return 5 } if errorMsg == "LINE 3: SELECT * FROM" { return 3 } return 0 } func testGetErrorColumn(errorMsg string) int { if errorMsg == "position: 12" { return 12 } return 0 } // TestCreateQueryBuilderTool tests the creation of the query builder tool func TestCreateQueryBuilderTool(t *testing.T) { // Get the tool tool := createQueryBuilderTool() // Assertions assert.NotNil(t, tool) assert.Equal(t, "dbQueryBuilder", tool.Name) assert.Equal(t, "Visual SQL query construction with syntax validation", tool.Description) assert.Equal(t, "database", tool.Category) assert.NotNil(t, tool.Handler) // Check input schema assert.Equal(t, "object", tool.InputSchema.Type) assert.Contains(t, tool.InputSchema.Properties, "action") assert.Contains(t, tool.InputSchema.Properties, "query") assert.Contains(t, tool.InputSchema.Properties, "components") assert.Contains(t, tool.InputSchema.Required, "action") } // TestMockValidateQuery tests the mock validation functionality func TestMockValidateQuery(t *testing.T) { // Test a valid query validQuery := "SELECT * FROM users WHERE id > 10" validResult, err := mockValidateQuery(validQuery) assert.NoError(t, err) resultMap := validResult.(map[string]interface{}) assert.True(t, resultMap["valid"].(bool)) assert.Equal(t, validQuery, resultMap["query"]) // Test an invalid query - missing FROM invalidQuery := "SELECT * users" invalidResult, err := mockValidateQuery(invalidQuery) assert.NoError(t, err) invalidMap := invalidResult.(map[string]interface{}) assert.False(t, invalidMap["valid"].(bool)) assert.Equal(t, invalidQuery, invalidMap["query"]) assert.Contains(t, invalidMap["error"], "Missing FROM clause") } // TestGetSuggestionForError tests the error suggestion generator func TestGetSuggestionForError(t *testing.T) { // Test for syntax error syntaxErrorMsg := "Syntax error at line 2, position 10: Unexpected token" syntaxSuggestion := getSuggestionForError(syntaxErrorMsg) assert.Contains(t, syntaxSuggestion, "Check SQL syntax") // Test for missing FROM missingFromMsg := "Missing FROM clause" missingFromSuggestion := getSuggestionForError(missingFromMsg) assert.Contains(t, missingFromSuggestion, "FROM clause") // Test for unknown column unknownColumnMsg := "Unknown column 'nonexistent' in table 'users'" unknownColumnSuggestion := getSuggestionForError(unknownColumnMsg) assert.Contains(t, unknownColumnSuggestion, "Column name is incorrect") // Test for unknown error randomError := "Some random error message" randomSuggestion := getSuggestionForError(randomError) assert.Contains(t, randomSuggestion, "Review the query syntax") } // TestGetErrorLineAndColumn tests error position extraction from messages func TestGetErrorLineAndColumn(t *testing.T) { // Test extracting line number from MySQL format error mysqlErrorMsg := "ERROR at line 5" mysqlLine := testGetErrorLine(mysqlErrorMsg) assert.Equal(t, 5, mysqlLine) // Test extracting line number from PostgreSQL format error pgErrorMsg := "LINE 3: SELECT * FROM" pgLine := testGetErrorLine(pgErrorMsg) assert.Equal(t, 3, pgLine) // Test extracting column/position number from PostgreSQL format posErrorMsg := "position: 12" position := testGetErrorColumn(posErrorMsg) assert.Equal(t, 12, position) // Test when no line number exists noLineMsg := "General error with no line info" defaultLine := testGetErrorLine(noLineMsg) assert.Equal(t, 0, defaultLine) // Test when no column number exists noColumnMsg := "General error with no position info" defaultColumn := testGetErrorColumn(noColumnMsg) assert.Equal(t, 0, defaultColumn) } // TestCalculateQueryComplexity tests the query complexity calculation func TestCalculateQueryComplexity(t *testing.T) { // Simple query simpleQuery := "SELECT id, name FROM users WHERE status = 'active'" assert.Equal(t, "Simple", calculateQueryComplexity(simpleQuery)) // Moderate query with join and aggregation moderateQuery := "SELECT u.id, u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name" assert.Equal(t, "Moderate", calculateQueryComplexity(moderateQuery)) // Complex query with multiple joins, aggregations, and subquery complexQuery := ` SELECT u.id, u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count, SUM(p.amount) as total_spent FROM users u JOIN orders o ON u.id = o.user_id JOIN payments p ON o.id = p.order_id JOIN addresses a ON u.id = a.user_id GROUP BY u.id, u.name ORDER BY total_spent DESC ` assert.Equal(t, "Complex", calculateQueryComplexity(complexQuery)) } // TestMockAnalyzeQuery tests the mock query analysis functionality func TestMockAnalyzeQuery(t *testing.T) { // Test a simple query simpleQuery := "SELECT * FROM users" simpleResult, err := mockAnalyzeQuery(simpleQuery) assert.NoError(t, err) simpleMap := simpleResult.(map[string]interface{}) // The query is converted to uppercase in the function queryValue := simpleMap["query"].(string) assert.Equal(t, strings.ToUpper(simpleQuery), queryValue) assert.NotNil(t, simpleMap["explainPlan"]) assert.NotNil(t, simpleMap["issues"]) assert.NotNil(t, simpleMap["suggestions"]) assert.Equal(t, "Simple", simpleMap["complexity"]) // Test a complex query with joins complexQuery := "SELECT * FROM users JOIN orders ON users.id = orders.user_id JOIN order_items ON orders.id = order_items.order_id" complexResult, err := mockAnalyzeQuery(complexQuery) assert.NoError(t, err) complexMap := complexResult.(map[string]interface{}) issues := complexMap["issues"].([]string) // Check that it detected multiple joins joinIssueFound := false for _, issue := range issues { if issue == "Query contains multiple joins" { joinIssueFound = true break } } assert.True(t, joinIssueFound, "Should detect multiple joins issue") } // TestGetTableFromQuery tests the table name extraction from queries func TestGetTableFromQuery(t *testing.T) { // Test simple query assert.Equal(t, "users", getTableFromQuery("SELECT * FROM users")) // Test with WHERE clause assert.Equal(t, "products", getTableFromQuery("SELECT * FROM products WHERE price > 100")) // Test with table alias assert.Equal(t, "customers", getTableFromQuery("SELECT * FROM customers AS c WHERE c.status = 'active'")) // Test with schema prefix assert.Equal(t, "public.users", getTableFromQuery("SELECT * FROM public.users")) // Test with no FROM clause assert.Equal(t, "unknown_table", getTableFromQuery("SELECT 1 + 1")) }