# SQL Parser Architecture Review

## Current Architecture (Updated: February 2026)

### ✅ Architecture Status: WELL-DESIGNED

The codebase uses a **namespace-based architecture** with inheritance, which is clean, maintainable, and follows .NET best practices.

---

## Architecture Pattern

### Namespace Organization

The architecture uses two namespaces to separate SQL Server (T-SQL) and Snowflake implementations:

- **`Strata.SqlTools.SqlServer`** - Base implementations for T-SQL
- **`Strata.SqlTools.Snowflake`** - Snowflake-specific implementations that inherit from SqlServer

### Class Structure

All classes use the same simple names in their respective namespaces, differentiated by namespace rather than class name prefix. This is the preferred .NET pattern.

#### Base Classes (SqlServer Namespace)

1. **`QueryBreakdown`** (SqlServer.QueryBreakdown)
   - Instance-based query breakdown
   - Manages query clauses and parameters
   - Uses `@param` syntax for T-SQL
   - Base functionality for all SQL dialects
   - **Key Methods:**
     - `GetClauses()` - Returns `SqlClauses` object from current properties
     - `ApplyClauses(SqlClauses?)` - Applies clauses to query (null-safe)
     - `AddWithClause()` - Adds Common Table Expressions (CTEs)
     - `Parse(string sql)` - Static parser for SQL strings

2. **`StatementParser`** (SqlServer.StatementParser)
   - Provides parsing utilities
   - Methods: `NormalizeSql()`, `RemoveSqlComments()`, `ExtractSetupClauses()`, etc.
   - Handles T-SQL specific parsing logic
   - Uses `[identifier]` syntax for identifiers

3. **`StatementExpressionParser`** (SqlServer.StatementExpressionParser)
   - Expression tree parsing for T-SQL
   - Uses `StatementReader` tokenizer
   - Converts SQL strings to expression trees

4. **`StatementReader`** (SqlServer.StatementReader)
   - Tokenizer/lexer for T-SQL
   - Handles `[identifier]` syntax
   - Character-by-character parsing
   - Returns tokens for parser consumption

#### Snowflake Classes (Snowflake Namespace)

All Snowflake classes inherit from their SqlServer counterparts and override only Snowflake-specific behavior:

1. **`QueryBreakdown`** (Snowflake.QueryBreakdown) - ✅ CORRECT PATTERN
   - **Inherits from:** `SqlServer.QueryBreakdown`
   - **Snowflake-specific features:**
     - Adds `:param` syntax support (in addition to `@param`)
     - Overrides `GetSql()` for Snowflake formatting
     - Handles Snowflake-specific parameter patterns
   - **Calls base class:** Yes, defers to parent where appropriate

2. **`StatementParser`** (Snowflake.StatementParser) - ✅ CORRECT PATTERN
   - **Inherits from:** `SqlServer.StatementParser`
   - **Snowflake-specific features:**
     - Handles `QUALIFY` and `LIMIT` keywords
     - Supports double-quote identifiers `"identifier"`
     - Understands `:parameter` syntax
     - Snowflake setup clauses (ALTER SESSION, CREATE STAGE)
   - **Calls base class:** Yes, reuses common parsing methods

3. **`StatementExpressionParser`** (Snowflake.StatementExpressionParser) - ✅ CORRECT PATTERN
   - **Inherits from:** `SqlServer.StatementExpressionParser`
   - **Snowflake-specific features:**
     - Uses Snowflake `StatementReader` instead of SqlServer version
     - Handles Snowflake identifier conventions (typically uppercase)
     - Supports double-quoted identifiers
   - **Calls base class:** Yes, inherits core parsing logic

4. **`StatementReader`** (Snowflake.StatementReader) - ✅ CORRECT PATTERN
   - **Inherits from:** `SqlServer.StatementReader`
   - **Snowflake-specific features:**
     - Adds double-quote identifier support `"identifier"`
     - Handles Snowflake naming conventions
   - **Calls base class:** Yes, overrides only tokenization of identifiers

---

## Key Architectural Strengths

### ✅ 1. Namespace-Based Organization
Instead of using class name prefixes (e.g., `SqlStatementParser`, `SnowflakeStatementParser`), the codebase uses namespace qualification:
```csharp
// Clean namespace-based approach (CURRENT)
using SqlServerParser = Strata.SqlTools.Statements.SqlServer.StatementParser;
using SnowflakeParser = Strata.SqlTools.Statements.Snowflake.StatementParser;

var sqlServerParser = new SqlServerParser();
var snowflakeParser = new SnowflakeParser();
```

**Benefits:**
- Shorter, cleaner class names
- Clear separation of concerns via namespaces
- Follows .NET Framework/Core conventions
- Easy to add new SQL dialects (PostgreSQL, MySQL, etc.)

### ✅ 2. Inheritance with Selective Overrides
Snowflake classes inherit from SqlServer base classes and override only dialect-specific behavior:
```csharp
public class StatementParser : SqlServer.StatementParser
{
    // Inherits all common SQL parsing logic
    // Only overrides Snowflake-specific methods
}
```

**Benefits:**
- DRY principle - shared logic in one place
- Bug fixes to common parsing benefit all dialects
- Clear identification of dialect-specific behavior
- Minimal code duplication

### ✅ 3. Proper Delegation Pattern
The Snowflake implementation properly delegates to base classes:
```csharp
// Example from Snowflake.QueryBreakdown
protected override string GetParameterPattern()
{
    // Snowflake supports both :param and @param
    return base.GetParameterPattern() + "|:\\w+";
}
```

### ✅ 4. Clear Separation of Concerns
- **SqlServer namespace:** T-SQL standard implementation (most widely used SQL dialect)
- **Snowflake namespace:** Snowflake-specific extensions
- **Classes folder:** Shared data structures including:
  - **Clause Types:** `SqlClause`, `SqlExpressionClause`, `WithClause`, `SqlClauses`
  - **Interfaces:** `ISqlClause`, `ISqlExpressionClause`, `IWithClause`
  - **SQL Structures:** `SqlTable`, `SqlJoin`, `SqlFrom`, `SqlFilter`
  - **Helpers:** `SelectClauseColumn`, `QueryParam`, `SqlBreakdownBase`, `SelectSource`
- **Expressions folder:** Expression tree components used by all dialects
- **Interfaces folder:** Core contracts (`IQueryBreakdown`, `IStatementReader`, `IStatementExpressionParser`)

---

## Architecture Diagrams

### High-Level Package Structure

```mermaid
graph TB
    subgraph "Strata.SqlTools"
        subgraph "SqlServer Namespace (Base)"
            SS_Parser[StatementParser]
            SS_Reader[StatementReader]
            SS_ExprParser[StatementExpressionParser]
            SS_Query[QueryBreakdown]
        end

        subgraph "Snowflake Namespace (Dialect)"
            SF_Parser[StatementParser]
            SF_Reader[StatementReader]
            SF_ExprParser[StatementExpressionParser]
            SF_Query[QueryBreakdown]
        end

        subgraph "Classes (Shared)"
            Clause[SqlClause, ISqlClause]
            ExprClause[SqlExpressionClause, ISqlExpressionClause]
            WithClause[WithClause, IWithClause]
            SqlClauses[SqlClauses]
            Tables[SqlTable, SqlJoin, SqlFrom]
            Filters[SqlFilter]
            Params[QueryParam, SelectClauseColumn]
            Base[SqlBreakdownBase, SelectSource]
        end

        subgraph "Expressions"
            Expr[Expression base]
            Binary[BinaryExpression]
            Column[ColumnExpression]
            Literal[LiteralExpression]
            Funcs[Functions: Sum, Avg, Count, etc.]
        end

        subgraph "Interfaces"
            IQuery[IQueryBreakdown]
            IReader[IStatementReader]
            IParser[IStatementExpressionParser]
        end
    end

    SF_Parser -.inherits.-> SS_Parser
    SF_Reader -.inherits.-> SS_Reader
    SF_ExprParser -.inherits.-> SS_ExprParser
    SF_Query -.inherits.-> SS_Query

    SS_Query -.implements.-> IQuery
    SF_Query -.implements.-> IQuery

    SS_Query -.uses.-> Clause
    SS_Query -.uses.-> ExprClause
    SS_Query -.uses.-> WithClause
    SS_Query -.uses.-> SqlClauses

    WithClause -.uses.-> IQuery
    WithClause -.uses.-> SqlClauses

    style SS_Parser fill:#e1f5ff
    style SS_Reader fill:#e1f5ff
    style SS_ExprParser fill:#e1f5ff
    style SS_Query fill:#e1f5ff
    style SF_Parser fill:#fff4e1
    style SF_Reader fill:#fff4e1
    style SF_ExprParser fill:#fff4e1
    style SF_Query fill:#fff4e1
```

### QueryBreakdown Class Hierarchy

```mermaid
classDiagram
    class IQueryBreakdown {
        <<interface>>
        +ISqlExpressionClause SelectClause
        +ISqlClause FromClause
        +ISqlExpressionClause WhereClause
        +ISqlExpressionClause GroupByClause
        +ISqlExpressionClause HavingClause
        +ISqlExpressionClause OrderByClause
        +void AddParameter()
        +void AddWhereClause()
        +void MergeWith()
        +string GetSql()
        +SqlClauses GetClauses()
        +void ApplyClauses()
    }

    class QueryBreakdown_SqlServer {
        <<SqlServer>>
        +ISqlExpressionClause SelectClause
        +ISqlClause FromClause
        +ISqlExpressionClause WhereClause
        +List~IWithClause~ WithClauses
        +Dictionary~string,object~ Parameters
        +void AddWithClause()
        +virtual SqlClauses GetClauses()
        +virtual void ApplyClauses()
        +virtual string GetSql()
        +static QueryBreakdown Parse()
    }

    class QueryBreakdown_Snowflake {
        <<Snowflake>>
        +override string GetSql()
        #override IStatementExpressionParser CreateExpressionParser()
    }

    IQueryBreakdown <|.. QueryBreakdown_SqlServer
    QueryBreakdown_SqlServer <|-- QueryBreakdown_Snowflake
```

### WITH Clause (CTE) Architecture

```mermaid
classDiagram
    class ISqlClause {
        <<interface>>
        +string? Clause
        +string? Comment
    }

    class IWithClause {
        <<interface>>
        +string TableName
        +SqlClauses? Sql
        +IQueryBreakdown? Query
    }

    class SqlClause {
        +string? Clause
        +string? Comment
    }

    class WithClause {
        -SqlClauses? _sql
        -IQueryBreakdown? _query
        +string TableName
        +SqlClauses? Sql
        +IQueryBreakdown? Query
        +WithClause()
        +WithClause(tableName, query)
        +WithClause(tableName, sql)
    }

    class SqlClauses {
        +ISqlExpressionClause? SelectClause
        +ISqlClause? FromClause
        +ISqlExpressionClause? WhereClause
        +ISqlExpressionClause? GroupByClause
        +ISqlExpressionClause? HavingClause
        +ISqlExpressionClause? OrderByClause
        +SqlClauses Copy()
    }

    class IQueryBreakdown {
        <<interface>>
        +SqlClauses GetClauses()
        +void ApplyClauses(SqlClauses?)
    }

    ISqlClause <|-- IWithClause
    ISqlClause <|.. SqlClause
    IWithClause <|.. WithClause
    SqlClause <|-- WithClause

    WithClause --> SqlClauses : uses
    WithClause --> IQueryBreakdown : references
    IQueryBreakdown --> SqlClauses : returns/accepts

    note for WithClause "Bi-directional sync between\nSql and Query properties"
```

### Clause Type Hierarchy

```mermaid
classDiagram
    class ISqlClause {
        <<interface>>
        +string? Clause
        +string? Comment
    }

    class ISqlExpressionClause {
        <<interface>>
        +IEnumerable~Expression~ GetExpressions()
    }

    class SqlClause {
        +string? Clause
        +string? Comment
    }

    class SqlExpressionClause {
        +bool SplitOnComma
        +IEnumerable~Expression~ GetExpressions()
    }

    class WithClause {
        +string TableName
        +SqlClauses? Sql
        +IQueryBreakdown? Query
    }

    ISqlClause <|-- ISqlExpressionClause
    ISqlClause <|.. SqlClause
    ISqlExpressionClause <|.. SqlExpressionClause
    SqlClause <|-- SqlExpressionClause
    SqlClause <|-- WithClause
    ISqlClause <|-- IWithClause
    IWithClause <|.. WithClause
```

---

## Code Examples

### Usage Pattern

#### Creating SQL Server Query Breakdown
```csharp
using Strata.SqlTools.Breakdowns.SqlServer;

var query = new QueryBreakdown();
query.SelectClause.Clause = "column1, column2";
query.FromClause.Clause = "myTable";

// AddWhereClause automatically extracts parameters
query.AddWhereClause("id = @id");
// Parameter @id is now in query.Parameters with null value

query.SetParameterValue("@id", 123);

string sql = query.GetSql(); // Returns T-SQL formatted query
```

#### Creating Query with Common Table Expression (CTE)
```csharp
using Strata.SqlTools.Breakdowns.SqlServer;

// Create inner CTE query
var cteQuery = new QueryBreakdown("id, name, active", "users", "active = 1");
cteQuery.AddParameter("@minDate", DateTime.Today.AddDays(-30));

// Create main query that uses the CTE
var mainQuery = new QueryBreakdown("*", "active_users");
mainQuery.AddWithClause("active_users", cteQuery);

string sql = mainQuery.GetSql();
/* Generates:
WITH active_users AS (
     SELECT id, name, active
     FROM users
     WHERE active = 1
)
SELECT *
FROM active_users
*/
```

#### Creating Snowflake Query Breakdown
```csharp
using Strata.SqlTools.Breakdowns.Snowflake;

var query = new QueryBreakdown();
query.SelectClause.Clause = "column1, column2";
query.FromClause.Clause = "myTable";

// AddWhereClause automatically extracts parameters (supports both :param and @param)
query.AddWhereClause("id = :id", false); // false = Snowflake parsing
// Parameter :id is now in query.Parameters with null value

query.SetParameterValue(":id", 123);

string sql = query.GetSql(); // Returns Snowflake formatted query
```

#### Parsing SQL Statements
```csharp
using Strata.SqlTools.Statements.SqlServer;

var parser = new StatementParser();
string normalized = parser.NormalizeSql(rawSql);
string cleaned = parser.RemoveSqlComments(normalized);

// For Snowflake
using SnowflakeParser = Strata.SqlTools.Statements.Snowflake.StatementParser;
var snowflakeParser = new SnowflakeParser();
string snowflakeSql = snowflakeParser.NormalizeSql(rawSql); // Handles :params and "identifiers"
```

#### Tokenizing SQL
```csharp
using Strata.SqlTools.Statements.SqlServer;

var reader = new StatementReader("SELECT [column1] FROM [table1]");
while (reader.Read())
{
    Console.WriteLine($"{reader.TokenType}: {reader.TokenValue}");
}

// For Snowflake double-quoted identifiers
using Strata.SqlTools.Statements.Snowflake;
var snowflakeReader = new StatementReader("SELECT \"column1\" FROM \"table1\"");
```

---

## Snowflake-Specific Features

The Snowflake implementations add these dialect-specific capabilities:

### 1. Parameter Syntax
- **SqlServer:** `@parameter` only
- **Snowflake:** `:parameter` and `@parameter` (both supported)

### 2. Identifier Quoting
- **SqlServer:** `[identifier]` (square brackets)
- **Snowflake:** `"identifier"` (double quotes) and `[identifier]`

### 3. Keywords
- **SqlServer:** Standard T-SQL keywords
- **Snowflake:** Additional `QUALIFY` and `LIMIT` keywords

### 4. Setup/Finish Clauses
- **Snowflake-specific:** `ALTER SESSION`, `CREATE STAGE`, `DROP STAGE`
- Used for session configuration and temporary objects

---

## Extensibility: Adding New SQL Dialects

The current architecture makes it easy to add new SQL dialects (PostgreSQL, MySQL, Oracle, etc.):

### Steps to Add a New Dialect

1. **Create new namespace:** `Strata.SqlTools.PostgreSQL`

2. **Inherit from SqlServer base classes:**
   ```csharp
   namespace Strata.SqlTools.PostgreSQL;

   public class StatementParser : SqlServer.StatementParser
   {
       // Override only PostgreSQL-specific behavior
   }

   public class StatementReader : SqlServer.StatementReader
   {
       // Override tokenization for PostgreSQL-specific syntax
   }

   public class QueryBreakdown : SqlServer.QueryBreakdown
   {
       // Override query generation for PostgreSQL
   }
   ```

3. **Override only dialect-specific methods:**
   - Don't duplicate common SQL logic
   - Call `base.Method()` where appropriate
   - Add dialect-specific constants/keywords

4. **Document differences:**
   - Add XML comments explaining what's dialect-specific
   - Reference PostgreSQL documentation for syntax

---

## Testing Strategy

### Unit Tests Organization
- **StatementReaderTests.cs** - Tests SqlServer.StatementReader
- **SnowflakeQueryBreakdownTests.cs** - Tests Snowflake.QueryBreakdown
- Additional test files as needed for each class

### Test Coverage Areas
1. **Tokenization:** Verify correct token identification
2. **Parsing:** Validate clause extraction and normalization
3. **Expression Trees:** Test expression parsing accuracy
4. **Parameter Handling:** Verify both `@param` and `:param` syntax
5. **Identifier Quoting:** Test `[brackets]` and `"double-quotes"`
6. **Dialect-Specific Features:** Test QUALIFY, LIMIT, setup clauses

---

## Recent Architectural Enhancements (February 2026)

### Automatic Parameter Extraction (February 2026)

Enhanced `AddWhereClause` with intelligent parameter management:

#### Key Features:
1. **Automatic Parameter Detection** - Extracts `@param` (SQL Server) and `:param` (Snowflake) from WHERE clauses
2. **Smart Update Logic** - Type-safe parameter value management with validation
3. **Protected Helper Methods** - `AddOrUpdateParameter()` and `ExtractAndAddParameters()`

#### Implementation Details:

```csharp
protected void AddOrUpdateParameter(string parameterName, object? value)
{
    // Normalizes parameter name (keeps : or @ prefix)
    // - New parameter: Adds with provided value
    // - Existing with null: Updates to new value
    // - Existing with non-null same type: Keeps existing value
    // - Existing with different type: Throws InvalidOperationException
}

protected void ExtractAndAddParameters(string sql)
{
    // Uses StatementParser to find parameters via regex
    // Calls AddOrUpdateParameter for each discovered parameter
}
```

#### Benefits:
- ✅ Automatic parameter registration when building WHERE clauses
- ✅ Type-safe parameter management prevents type mismatches
- ✅ Preserves existing parameter values during query composition
- ✅ Works seamlessly with both SQL Server (`@param`) and Snowflake (`:param`) syntax
- ✅ Reduces boilerplate - no manual `AddParameter` calls needed

#### Usage Example:
```csharp
var query = new QueryBreakdown("*", "Users");
query.AddWhereClause("UserID = @UserId AND Status = @Status");
// @UserId and @Status automatically added to Parameters dictionary

query.SetParameterValue("@UserId", 123);
query.SetParameterValue("@Status", "Active");
```

### WITH Clause (CTE) Implementation

A comprehensive Common Table Expression (CTE) architecture was added:

#### Key Components:
1. **`IWithClause` Interface** - Contract for CTE structure
2. **`WithClause` Class** - Concrete implementation with intelligent property synchronization
3. **`SqlClauses` Class** - Container for parsed SQL clause objects
4. **Enhanced `IQueryBreakdown`** - Added `GetClauses()` and `ApplyClauses()` methods

#### Architecture Highlights:
- **Bi-directional Synchronization:** `Sql` ↔ `Query` properties automatically sync
- **Query as Source of Truth:** When `Query` exists, `Sql` is computed from it
- **Polymorphic Design:** No type-checking required, works with any `IQueryBreakdown` implementation
- **Cognitive Complexity Reduction:** 68% reduction through `ApplyClauses()` method extraction

#### Benefits:
- ✅ Structured CTE management with parameter support
- ✅ Automatic synchronization prevents stale data
- ✅ Clean API with `GetClauses()` and `Copy()` methods
- ✅ Comment preservation for CTEs
- ✅ Support for both SQL Server and Snowflake dialects

```mermaid
sequenceDiagram
    participant User
    participant WithClause
    participant Query as IQueryBreakdown

    Note over WithClause: Scenario: Set Query, then Get Sql
    User->>WithClause: Set Query = queryBreakdown
    User->>WithClause: Get Sql
    WithClause->>Query: GetClauses()
    Query-->>WithClause: SqlClauses (computed)
    WithClause-->>User: SqlClauses

    Note over WithClause: Scenario: Set Sql with existing Query
    User->>WithClause: Set Sql = sqlClauses
    WithClause->>Query: ApplyClauses(sqlClauses)
    Note over WithClause: _sql cleared, Query is source
```

---

## Summary

### Current Status: ✅ WELL-ARCHITECTED

The codebase demonstrates:
- **Clean separation** via namespaces (SqlServer vs Snowflake)
- **Proper inheritance** with selective overrides
- **DRY principles** - shared logic in base classes
- **Extensibility** - easy to add new SQL dialects
- **Maintainability** - clear structure and delegation patterns
- **Modern patterns** - Interface-based design with bi-directional synchronization
- **Low cognitive complexity** - Method extraction and centralized logic

### No Action Required

The architecture is solid and follows .NET best practices. The namespace-based organization is superior to prefix-based naming and makes the codebase easier to navigate and extend.

### Future Considerations

If adding more SQL dialects:
1. Continue the namespace pattern
2. Inherit from SqlServer base classes (most common SQL standard)
3. Override only dialect-specific behavior
4. Add comprehensive unit tests for new dialect features
5. Document dialect differences clearly