Graph & Search Extensions - Advanced Query Language Features

[mathiasrw]

Priority: 5-7 (Low)
Impact: Vendor-Specific Extensions
Test Files: test/test315.js, test/test316.js, test/test322.js, test/test338.js, test/test339.js
Test Count: 88 tests

Problem Description

Multiple test files contain skipped tests for AlaSQL's proprietary graph and search extensions including SEARCH operations, UNION operations with SEARCH, and advanced query language features. These are vendor-specific extensions that provide powerful data navigation and analysis capabilities.

Specific Test Cases

test315.js - SEARCH Brackets (20 tests)

• Lines 8-147: SEARCH operations with brackets and complex navigation
• Tests graph traversal, bracket syntax, and advanced SEARCH features

test316.js - SEARCH DISTINCT (2 tests)

• Lines 9-35: SEARCH DISTINCT operations
• Tests distinct search operations and UNION combinations

test322.js - SEARCH UNION (3 tests)

• Lines 14-51: SEARCH UNION operations with CORRESPONDING
• Tests search operations combined with set operations

test338.js - SEARCH with EXCEPT (3 tests)

• Lines 39-73: SEARCH operations with EXCEPT and CTEs
• Tests complex search with set operations

test339.js - Complex SEARCH Operations (13 tests)

• Lines 35-202: SEARCH with UNION, INTERSECT, EXCEPT
• Tests advanced search combinations and operations

Expected Behavior

AlaSQL should support its proprietary graph and search extensions:

1. SEARCH Operations: Graph traversal and data navigation
2. Bracket Syntax: Complex navigation patterns
3. SEARCH with Set Operations: UNION, INTERSECT, EXCEPT integration
4. SEARCH DISTINCT: Deduplication in search results
5. Complex Combinations: Advanced query language features

Current Status

• Test Status: All skipped (it.skip)
• Error: Unknown (tests not executed)
• Root Cause: Graph and search extensions not implemented or incomplete

Implementation Requirements

1. SEARCH Parser Support

Add SEARCH syntax to src/alasqlparser.jison:

-- Basic SEARCH
SEARCH column FROM data_source

-- SEARCH with brackets
SEARCH (path) FROM data_source

-- SEARCH with navigation
SEARCH /path/to/element FROM data_source

-- SEARCH with operators
SEARCH /+ element FROM data_source
SEARCH /* element FROM data_source
SEARCH /? element FROM data_source

-- SEARCH with set operations
SEARCH /element UNION SEARCH /other_element
SEARCH /element INTERSECT SEARCH /other_element
SEARCH /element EXCEPT SEARCH /other_element

2. Graph Navigation Engine

• Path Traversal: Navigate object hierarchies and arrays
• Bracket Syntax: Complex navigation patterns
• Operator Support: +, *, ?, and other navigation operators
• Result Aggregation: Combine search results appropriately

3. Set Operation Integration

• SEARCH UNION: Combine search results
• SEARCH INTERSECT: Find common elements
• SEARCH EXCEPT: Find difference between searches
• SEARCH DISTINCT: Remove duplicates from results

4. Advanced Features

• Complex Paths: Multi-level navigation
• Conditional Navigation: Path-based filtering
• Result Ordering: Sort search results
• Performance: Efficient graph traversal

5. Data Source Support

• JSON Navigation: Traverse JSON structures
• Array Navigation: Navigate array elements
• Object Navigation: Access object properties
• Mixed Sources: Handle complex data structures

AlaSQL-Specific Features Involved

• SEARCH statement (proprietary graph query language)
• Bracket syntax for complex navigation
• Navigation operators (+, *, ?, etc.)
• SEARCH with set operations integration
• Graph traversal algorithms
• Data structure navigation

Dependencies

• AlaSQL parser (src/alasqlparser.jison)
• Graph navigation engine
• Set operations implementation
• Data structure processing
• Result aggregation system

Acceptance Criteria

Basic SEARCH Operations (test315.js)

• All 20 tests enabled and passing
• Basic SEARCH syntax support
• Bracket navigation syntax
• Navigation operators (+, *, ?)
• Complex path traversal
• Result aggregation

SEARCH with Set Operations (test316.js, test322.js, test338.js, test339.js)

• All 21 tests enabled and passing
• SEARCH DISTINCT operations
• SEARCH UNION operations
• SEARCH INTERSECT operations
• SEARCH EXCEPT operations
• Complex search combinations

Advanced Navigation (test315.js)

• Complex bracket syntax
• Multi-level navigation
• Conditional navigation
• Performance optimization
• Edge case handling

Implementation Strategy

Phase 1: Basic SEARCH Engine

1. Parser Support for SEARCH syntax
2. Simple Navigation: Basic path traversal
3. Bracket Syntax: Complex navigation patterns
4. Operator Support: +, *, ? operators

Phase 2: Set Operation Integration

1. SEARCH UNION: Result combination
2. SEARCH INTERSECT: Common element finding
3. SEARCH EXCEPT: Difference calculation
4. SEARCH DISTINCT: Deduplication

Phase 3: Advanced Features

1. Complex Navigation: Multi-level paths
2. Performance Optimization: Efficient traversal
3. Error Handling: Invalid path handling
4. Data Source Support: Various data structures

Phase 4: Integration and Optimization

1. Query Optimization: Efficient execution
2. Memory Management: Large dataset handling
3. Edge Cases: Complex scenarios
4. Documentation: Feature documentation

Test Implementation Examples

-- Basic SEARCH
SEARCH a FROM ?  -- Find all 'a' properties

-- Bracket navigation
SEARCH WITH(c d) FROM ?  -- Navigate to c.d

-- Path navigation
SEARCH /a/b/c FROM ?  -- Navigate through path

-- Operator navigation
SEARCH /+ a FROM ?  -- All 'a' elements at any level
SEARCH /* a FROM ?  -- First 'a' element
SEARCH /? a FROM ?  -- Optional 'a' element

-- SEARCH with set operations
SEARCH /a UNION SEARCH /b
SEARCH /a INTERSECT SEARCH /b
SEARCH /a EXCEPT SEARCH /b

-- Complex combinations
SEARCH ALL((/+) a) ORDER BY(DESC) FROM ?

Performance Considerations

• Graph Traversal: Efficient path navigation algorithms
• Memory Usage: Optimize for large data structures
• Result Aggregation: Efficient set operations
• Caching: Cache frequently accessed paths
• Indexing: Optimize navigation where possible

Edge Cases to Handle

• Circular References: Prevent infinite loops
• Invalid Paths: Handle non-existent paths gracefully
• Large Datasets: Memory-efficient processing
• Mixed Data Types: Handle various data structures
• Complex Queries: Optimize nested operations

Integration Points

• JSON Parser: Integrate with JSON data sources
• Set Operations: Use existing UNION/INTERSECT/EXCEPT
• Query Engine: Integrate with execution pipeline
• Data Sources: Support various input formats

Notes

Graph and search extensions are powerful AlaSQL-specific features that provide:

• Graph Query Capabilities: Navigate complex data structures
• Set Operation Integration: Combine with standard SQL operations
• Performance: Efficient data traversal and analysis
• Flexibility: Handle various data formats and structures

These extensions differentiate AlaSQL from standard SQL databases and provide unique capabilities for data analysis and manipulation.