SQL CLI Project - AI Context Guide

Vim-like terminal SQL editor with in-memory query engine for ultra-fast navigation and data exploration. Built in Rust using ratatui.

🚀 QUICK START - Essential Commands

# Run all tests (Rust + Python + Examples)
./run_all_tests.sh                                  # Complete test suite

# When in doubt about available functions:
./target/release/sql-cli --list-functions           # List all SQL functions
./target/release/sql-cli --function-help CONVERT    # Get help for specific function

# Test SQL queries quickly:
./target/release/sql-cli -q "SELECT CONVERT(100, 'km', 'miles')" -o csv
./target/release/sql-cli data/test.csv -q "SELECT * FROM test WHERE id > 5" -o csv

# Test examples:
uv run python tests/integration/test_examples.py    # Run all examples (formal + smoke tests)
uv run python tests/integration/test_examples.py physics_astronomy_showcase  # Run specific
uv run python tests/integration/test_examples.py --capture qualified_names   # Capture expectations

🏗️ CORE ARCHITECTURE - Key Files

Entry Points & CLI

src/main.rs - CLI entry point, argument parsing, mode selection (TUI vs non-interactive)
src/non_interactive.rs - Handles -q queries, script execution, output formatting

SQL Parsing & Execution Pipeline

Parser: src/sql/recursive_parser.rs - Recursive descent parser, builds AST
- Parses SELECT, WHERE, GROUP BY, ORDER BY, functions, expressions
- Returns SelectStatement AST structure
Evaluator: src/data/arithmetic_evaluator.rs - Evaluates expressions against data
- Handles arithmetic, comparisons, function calls
- IMPORTANT: Do NOT add function implementations here - use function registry
Query Executor: src/data/query_executor.rs - Orchestrates query execution
- Applies WHERE filters, GROUP BY, ORDER BY, LIMIT

Function System (CRITICAL)

Registry: src/sql/functions/mod.rs - Central function registry
- ALL new functions must be registered here
- Categories: Mathematical, String, Date, Conversion, etc.
Adding New Functions:
1. Create implementation in src/sql/functions/<category>.rs
2. Implement SqlFunction trait
3. Register in mod.rs under appropriate category
4. Function automatically available in CLI and help
Example Function Modules:
- src/sql/functions/math.rs - Mathematical functions
- src/sql/functions/string_methods.rs - String manipulation
- src/sql/functions/convert.rs - Unit conversions (CONVERT function)
- src/sql/functions/astronomy.rs - Astronomical constants
- src/sql/functions/chemistry.rs - Chemical elements

Data Structures

src/data/datatable.rs - Core data table structure (columns, rows, types)
src/data/data_view.rs - View layer with sorting, filtering, column operations
src/data/csv_datasource.rs - CSV loading and parsing
src/data/json_datasource.rs - JSON data handling

TUI Components (for interactive mode)

src/ui/enhanced_tui.rs - Main TUI interface
src/app_state_container.rs - Central state management
src/action.rs - Action system for state updates
src/handlers/ - Event handlers for keyboard input

Unit Conversion System

src/data/unit_converter.rs - Core conversion logic
src/sql/functions/convert.rs - CONVERT() SQL function
Supports: temperature, distance, weight, volume, area, speed, pressure, time, energy

📝 DEVELOPMENT WORKFLOW

Adding a New SQL Function

Choose the right category or create new one in src/sql/functions/mod.rs:

pub enum FunctionCategory {
    Mathematical,
    String,
    Date,
    Conversion,  // etc.
}

Create function implementation:

// In src/sql/functions/your_category.rs
pub struct YourFunction;

impl SqlFunction for YourFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "YOUR_FUNC",
            category: FunctionCategory::YourCategory,
            arg_count: ArgCount::Fixed(2),
            description: "What it does",
            returns: "Return type",
            examples: vec!["SELECT YOUR_FUNC(arg1, arg2)"],
        }
    }
    
    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        // Implementation
    }
}

Register in function registry (src/sql/functions/mod.rs):

fn register_your_category(&mut self) {
    self.register(Box::new(YourFunction));
}

Test it:

./target/release/sql-cli -q "SELECT YOUR_FUNC(1, 2)" -o csv
./target/release/sql-cli --function-help YOUR_FUNC

Parser Modifications

File: src/sql/recursive_parser.rs
Key methods:
- parse_select_list() - SELECT clause items
- parse_where_clause() - WHERE conditions
- parse_expression() - Expressions and operators
- parse_function_call() - Function parsing
ALWAYS add Python tests after parser changes

Testing Strategy

Quick command-line testing:

# Test new feature
./target/release/sql-cli -q "YOUR_QUERY" -o csv

# Debug parser
./target/release/sql-cli -q "YOUR_QUERY" --query-plan

Run test suites:

cargo test                                        # Rust unit tests
./run_python_tests.sh                            # Python integration tests
uv run python tests/integration/test_examples.py # Examples (formal + smoke)
./run_all_tests.sh                               # ALL THREE test suites

Examples testing (new Python-based framework):

# Run all examples (2 formal with expectations, 117 smoke tests)
uv run python tests/integration/test_examples.py

# Run specific example
uv run python tests/integration/test_examples.py qualified_names

# Capture expected output for formal testing
uv run python tests/integration/test_examples.py --capture physics_astronomy_showcase

# Only fails if FORMAL tests fail (expectations not met)
# SMOKE test failures are reported but non-blocking

Always run before committing:

cargo fmt                    # Required formatting
cargo clippy                 # Linting
./run_all_tests.sh          # All tests (Rust + Python + Examples)

🗂️ Project Structure

sql-cli/
├── src/
│   ├── main.rs                 # Entry point
│   ├── non_interactive.rs      # CLI query mode
│   ├── sql/
│   │   ├── recursive_parser.rs # SQL parser (builds AST)
│   │   ├── functions/          # Function implementations
│   │   │   ├── mod.rs         # Function registry
│   │   │   ├── math.rs        # Math functions
│   │   │   ├── string_methods.rs # String functions
│   │   │   └── convert.rs     # Unit conversions
│   │   └── script_parser.rs   # Script with GO separators
│   ├── data/
│   │   ├── datatable.rs       # Core data structure
│   │   ├── arithmetic_evaluator.rs # Expression evaluation
│   │   ├── query_executor.rs  # Query orchestration
│   │   └── unit_converter.rs  # Unit conversion logic
│   └── ui/                     # TUI components
├── examples/                   # SQL example files
│   └── expectations/          # Captured JSON output for formal tests
├── data/                       # Test data files
├── docs/                       # Technical documentation
├── tests/
│   ├── python_tests/          # Python integration tests
│   ├── integration/           # Integration test scripts
│   │   └── test_examples.py   # Examples test framework (formal + smoke)
│   └── sql_examples/          # Test SQL queries
├── scripts/
│   ├── test_all_examples.sh   # Legacy bash example runner (deprecated)
│   └── capture_expectation.sh # Helper to capture example expectations
└── nvim-plugin/               # Neovim plugin

📁 File Organization Guidelines

When creating new files, please follow these conventions:

Keep in Root Directory:

README.md - Main project documentation
CHANGELOG.md - Version history
RELEASE_NOTES.md - Release information
CLAUDE.md - This AI context guide
Cargo.toml, Cargo.lock - Rust project files
.gitignore, .github/ - Git configuration

Place in Appropriate Directories:

Test SQL files (test_*.sql) → tests/sql_examples/
Test scripts (test_*.sh, test_*.lua) → tests/integration/
Python tests → tests/python_tests/
Technical docs (implementation details, TODOs) → docs/
Sample data → data/
SQL examples → examples/

Naming Conventions:

Test files should start with test_
Data files should have descriptive names (e.g., trade_reconciliation.csv)
Documentation should use UPPER_SNAKE_CASE for visibility (e.g., MIGRATION_TODO.md)

🎯 Key Principles

Function Registry: ALL functions go through the registry - no special cases in parser/evaluator
Test Everything: Add Python tests for SQL features, Rust tests for internals
Use Non-Interactive Mode: Test queries with -q flag before TUI testing
Format Always: Run cargo fmt before every commit
Check Functions: Use --list-functions when unsure about available functions
CTE Pattern for Aggregate Expressions: Window functions can't handle expressions directly. Use CTEs to pre-calculate expressions, then apply window functions to the resulting columns.
Window Function Performance: Window functions use batch evaluation by default for 86% better performance. Set SQL_CLI_BATCH_WINDOW=0 to opt-out if needed.

🔧 Common Tasks

Finding Functions

# List all functions
./target/release/sql-cli --list-functions

# Search for specific function
./target/release/sql-cli --list-functions | grep -i convert

# Get function help
./target/release/sql-cli --function-help CONVERT

Testing Queries

# Simple query
./target/release/sql-cli -q "SELECT 1+1" -o csv

# Query with data file
./target/release/sql-cli data/test.csv -q "SELECT * FROM test" -o csv

# Query with functions
./target/release/sql-cli -q "SELECT CONVERT(100, 'celsius', 'fahrenheit')" -o csv

# Show query plan (AST)
./target/release/sql-cli -q "SELECT * FROM test WHERE id > 5" --query-plan

Running Tests

# All tests
./run_all_tests.sh

# Just Rust tests
cargo test

# Just Python tests
./run_python_tests.sh

# Test examples
./scripts/test_all_examples.sh

# Specific test
cargo test test_convert
python tests/python_tests/test_unit_conversions.py

📚 Test Data Files

data/test_simple_strings.csv - String operations testing
data/test_simple_math.csv - Math operations testing
data/sales_data.csv - Window functions, aggregates
data/solar_system.csv - Astronomical calculations
data/trades.json - JSON data source testing

✍️ Writing SQL Examples

When creating SQL examples in examples/*.sql, follow these conventions:

File Structure

Use examples/accounting_format.sql as a template:

Data file hint at top - Use shebang-style comment to specify data file:
```
-- #! ../data/international_sales.csv
```
This allows the CLI to find the data file relative to the SQL file.
Statement termination - EVERY SQL statement must end with:
- Semicolon (;) - Marks end of SQL statement
- GO on its own line - Tells the script parser to execute the batch
```
SELECT * FROM table
WHERE condition = true;
GO
```

Multiple statements - Each statement needs its own ; and GO:

-- First query
SELECT COUNT(*) FROM sales;
GO

-- Second query with CTE
WITH summary AS (
    SELECT region, SUM(amount) as total
    FROM sales
    GROUP BY region
)
SELECT * FROM summary
ORDER BY total DESC;
GO

Example Template

-- #! ../data/your_data.csv

-- Description of what this example demonstrates
SELECT
    column1,
    column2
FROM your_data
WHERE some_condition;
GO

-- Another example query
WITH cte_name AS (
    SELECT * FROM your_data
)
SELECT * FROM cte_name;
GO

Testing Examples

Examples are automatically tested by:

./scripts/test_all_examples.sh

The script parser is basic - it chunks on GO statements, so proper formatting is essential.

🚨 Important Notes

ALWAYS run cargo fmt before committing - Required for all commits
NULL handling: Empty CSV fields are NULL, use IS NULL/IS NOT NULL
CONVERT function: Use for all unit conversions, don't create individual functions
GO separator: Supported in script files for batch execution
F5 in TUI: Shows debug view with internal state

🔗 Quick Links

Function Registry: src/sql/functions/mod.rs
Parser: src/sql/recursive_parser.rs
Expression Evaluator: src/data/arithmetic_evaluator.rs
Query Executor: src/data/query_executor.rs
Unit Converter: src/data/unit_converter.rs

Agents (IMPORTANT: Always delegate to these specialized agents)

rust-build-fixer

ALWAYS delegate to this agent when:

Any cargo build or cargo build --release fails
User reports compilation errors (e.g., "I'm getting an error...")
After writing new Rust code (proactively check compilation)
Formatting issues reported (cargo fmt failures)
Type errors, borrow checker issues, or any Rust compilation problems

rust-test-failure-investigator

ALWAYS delegate to this agent when:

cargo test reports ANY failures
User mentions test failures (e.g., "tests are failing", "test broken")
After implementing features that might affect tests
CI pipeline test failures are reported
Integration test failures occur

debug-analyzer

ALWAYS delegate to this agent when:

User provides F5 debug output
Large debug dumps need analysis
State inconsistency issues in TUI
Performance bottlenecks need investigation

CRITICAL: Do NOT try to fix compilation errors or test failures yourself. ALWAYS delegate to the appropriate agent immediately.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL CLI Project - AI Context Guide

🚀 QUICK START - Essential Commands

🏗️ CORE ARCHITECTURE - Key Files

Entry Points & CLI

SQL Parsing & Execution Pipeline

Function System (CRITICAL)

Data Structures

TUI Components (for interactive mode)

Unit Conversion System

📝 DEVELOPMENT WORKFLOW

Adding a New SQL Function

Parser Modifications

Testing Strategy

🗂️ Project Structure

📁 File Organization Guidelines

Keep in Root Directory:

Place in Appropriate Directories:

Naming Conventions:

🎯 Key Principles

🔧 Common Tasks

Finding Functions

Testing Queries

Running Tests

📚 Test Data Files

✍️ Writing SQL Examples

File Structure

Example Template

Testing Examples

🚨 Important Notes

🔗 Quick Links

Agents (IMPORTANT: Always delegate to these specialized agents)

rust-build-fixer

rust-test-failure-investigator

debug-analyzer

FilesExpand file tree

CLAUDE.md

Latest commit

History

CLAUDE.md

File metadata and controls

SQL CLI Project - AI Context Guide

🚀 QUICK START - Essential Commands

🏗️ CORE ARCHITECTURE - Key Files

Entry Points & CLI

SQL Parsing & Execution Pipeline

Function System (CRITICAL)

Data Structures

TUI Components (for interactive mode)

Unit Conversion System

📝 DEVELOPMENT WORKFLOW

Adding a New SQL Function

Parser Modifications

Testing Strategy

🗂️ Project Structure

📁 File Organization Guidelines

Keep in Root Directory:

Place in Appropriate Directories:

Naming Conventions:

🎯 Key Principles

🔧 Common Tasks

Finding Functions

Testing Queries

Running Tests

📚 Test Data Files

✍️ Writing SQL Examples

File Structure

Example Template

Testing Examples

🚨 Important Notes

🔗 Quick Links

Agents (IMPORTANT: Always delegate to these specialized agents)

rust-build-fixer

rust-test-failure-investigator

debug-analyzer