658 lines
20 KiB
Markdown
658 lines
20 KiB
Markdown
# 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
|