sql-utilities/docs/SqlUtilities.Core.md

# Strata.SqlTools

A comprehensive SQL query parsing and generation library for Microsoft T-SQL and Snowflake SQL, providing robust decomposition and reconstruction of SELECT queries with support for Common Table Expressions (CTEs), expression trees, and type-safe query building.

## Overview

The `Strata.SqlTools` namespace provides powerful classes for working with SQL queries programmatically:

- **QueryBreakdown**: Parse and generate Microsoft T-SQL SELECT queries with expression-based query building
- **SnowflakeQueryBreakdown**: Parse and generate Snowflake SQL SELECT queries with dialect-specific features
- **Expression System**: Type-safe expression trees for building SQL queries programmatically
- **Statement Parsing**: Token-based SQL parsing with support for both SQL Server and Snowflake syntax
- **Visitor Pattern**: Extensible SQL generation with dialect-specific formatting

Both QueryBreakdown classes support:
- Full SQL clause decomposition (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
- Common Table Expressions (WITH clause) with multiple CTEs
- Parameter extraction and management (@parameter for T-SQL, :parameter or @parameter for Snowflake)
- Query merging and composition
- Expression-based query building with `AddSelectExpression` and `AddWhereExpression`
- Setup and finish clauses for complex scripts
- Deep cloning and serialization
- Comment preservation during parsing

## Features

### Core Capabilities
- ✅ Parse SQL SELECT statements into structured components
- ✅ Generate properly formatted SQL from components
- ✅ Support for nested subqueries and complex expressions
- ✅ Multiple CTE support with ordered list management
- ✅ Parameter extraction and value binding
- ✅ Query merging and composition
- ✅ WHERE clause builder with AND/OR operators
- ✅ Round-trip parsing and generation

### Dialect Support
- **T-SQL**: Microsoft SQL Server syntax with @parameters and 5-space indentation
- **Snowflake**: Snowflake-specific features including:
  - Colon parameters (`:param`)
  - Double colon casting (`::VARCHAR`)
  - VARIANT/JSON data types
  - LIMIT clause
  - QUALIFY clause
  - Time Travel (AT)
  - 4-space indentation

## Architecture

### Project Structure

The library is organized into focused folders for maintainability and clarity:

```mermaid
graph TD
    A[Strata.SqlTools] --> B[SqlServer/]
    A --> C[Snowflake/]
    A --> D[Classes/]
    A --> E[Enums/SQL/]
    A --> F[Utilities/]
    A --> G[Extensions/]
    A --> H[Expressions/]
    A --> I[Interfaces/]
    A --> J[QueryBuilders/]
    A --> K[Breakdowns/]

    B --> B1[StatementParser]
    B --> B2[StatementReader]
    B --> B3[StatementExpressionParser]
    B --> B4[CommandVisitor]
    B --> B5[QueryBreakdown]
    B --> B6[SqlClause]

    C --> C1[StatementParser]
    C --> C2[StatementReader]
    C --> C3[StatementExpressionParser]
    C --> C4[CommandVisitor]
    C --> C5[QueryBreakdown]

    D --> D1[Token]
    D --> D2[SqlClause]
    D --> D3[SqlClauses]
    D --> D4[SqlFilter]
    D --> D5[QueryParam]

    E --> E1[TokenType]
    E --> E2[SqlDataType]
    E --> E3[FilterOperation]
    E --> E4[AggregateFunction]

    F --> F1[SqlUtils]
    F --> F2[ArrayUtils]
    F --> F3[GuidUtils]
    F --> F4[StringUtils]
    F --> F5[SqlUtils.Filters]
    F --> F6[SqlColumnHelpers]
    F --> F7[SqlDataTypeHelpers]

    H --> H1[Expression]
    H --> H2[Literals/]
    H --> H3[Functions/]
    H --> H4[Operators/]
```

### Key Components

#### 1. Statement Parsing

The library uses a multi-stage parsing approach:

```mermaid
graph LR
    A[SQL String] --> B[StatementReader]
    B --> C[Tokens]
    C --> D[StatementExpressionParser]
    D --> E[Expression Tree]
    E --> F[QueryBreakdown]

    style B fill:#e1f5ff
    style D fill:#e1f5ff
    style F fill:#ffe1e1
```

- **StatementReader**: Tokenizes SQL into individual elements (identifiers, operators, literals)
- **StatementExpressionParser**: Converts tokens into expression trees
- **StatementParser**: Extracts SQL clauses with comment preservation
- **QueryBreakdown**: High-level query representation with all clauses

#### 2. Visitor Pattern for SQL Generation

SQL generation uses the Template Method pattern for dialect flexibility:

```mermaid
classDiagram
    class CommandVisitor {
        +Visit(Expression) string
        #FormatIdentifier(string) string
        #FormatParameterName(string) string
        #FormatBooleanLiteral(bool) string
        #FormatStringLiteral(string) string
        #FormatCaseInsensitiveLike() string
    }

    class SqlServerCommandVisitor {
        #FormatIdentifier("[", "]")
        #FormatParameterName("@")
        #FormatBooleanLiteral("1"/"0")
    }

    class SnowflakeCommandVisitor {
        #FormatIdentifier(no brackets)
        #FormatParameterName(":")
        #FormatBooleanLiteral("TRUE"/"FALSE")
    }

    CommandVisitor <|-- SqlServerCommandVisitor
    CommandVisitor <|-- SnowflakeCommandVisitor
```

**82% Code Reduction**: The Template Method pattern reduced Snowflake-specific code from 280 lines to 50 lines by extracting common visitor logic into the base class.

#### 3. Expression System

Type-safe expression building with operator overloads:

```mermaid
graph TD
    A[Expression] --> B[GenericColumnExpression]
    A --> C[LiteralExpression]
    A --> D[OperatorExpression]
    A --> E[FunctionExpression]

    C --> C1[NumberLiteralExpression]
    C --> C2[StringLiteralExpression]
    C --> C3[BooleanLiteralExpression]

    D --> D1[ComparisonOperatorExpression]
    D --> D2[ArithmeticOperatorExpression]
    D --> D3[LogicalOperatorExpression]

    E --> E1[AggregateFunctionExpression]
    E --> E2[ScalarFunctionExpression]

    style A fill:#ffe1e1
    style B fill:#e1ffe1
    style C fill:#e1ffe1
    style D fill:#e1ffe1
    style E fill:#e1ffe1
```

#### 4. Data Flow

Complete query building workflow:

```mermaid
sequenceDiagram
    participant User
    participant QueryBreakdown
    participant Expression
    participant CommandVisitor
    participant StatementParser

    User->>QueryBreakdown: AddSelectExpression(expr)
    QueryBreakdown->>CommandVisitor: Visit(expr)
    CommandVisitor-->>QueryBreakdown: SQL string
    QueryBreakdown->>StatementParser: ExtractSqlComments()
    StatementParser-->>QueryBreakdown: SqlClause

    User->>QueryBreakdown: GetSql()
    QueryBreakdown->>QueryBreakdown: Build SELECT clause
    QueryBreakdown->>QueryBreakdown: Build FROM clause
    QueryBreakdown->>QueryBreakdown: Build WHERE clause
    QueryBreakdown-->>User: Complete SQL
```

### Namespace Organization

| Namespace | Purpose | Key Classes |
|-----------|---------|-------------|
| `Strata.SqlTools.Breakdowns.SqlServer` | SQL Server-specific implementations | `QueryBreakdown`, `CommandVisitor` |
| `Strata.SqlTools.Breakdowns.Snowflake` | Snowflake-specific implementations | `QueryBreakdown`, `CommandVisitor` |
| `Strata.SqlTools.Statements.SqlServer` | SQL Server statement parsing | `StatementParser`, `StatementReader`, `StatementExpressionParser` |
| `Strata.SqlTools.Statements.Snowflake` | Snowflake statement parsing | `StatementParser`, `StatementReader`, `StatementExpressionParser` |
| `Strata.SqlTools.Classes` | Core data structures | `SqlClause`, `SqlClauses`, `Token` |
| `Strata.SqlTools.Enums.SQL` | SQL-related enumerations | `TokenType`, `SqlDataType`, `FilterOperation` |
| `Strata.SqlTools.Utilities` | Helper utilities | `SqlUtils`, `ArrayUtils`, `GuidUtils`, `StringUtils` |
| `Strata.SqlTools.Extensions` | Extension methods | `StringBuilderEx` |
| `Strata.SqlTools.Expressions` | Expression tree components | `Expression`, literal/operator/function types |
| `Strata.SqlTools.Interfaces` | Contracts and abstractions | `IQueryBreakdown`, `IStatementReader` |

## Installation

Reference the `Strata.Base` assembly in your project to access the `Strata.SqlTools` namespace.

```csharp
using Strata.SqlTools;
```

## Usage

### Basic Query Construction (T-SQL)

```csharp
// Simple query
var query = new QueryBreakdown("ID, Name, Email", "Users");
query.WhereClause.Clause = "Active = 1";
query.OrderByClause.Clause = "Name ASC";

string sql = query.GetSql();
// Output:
// SELECT
//      ID, Name, Email
// FROM
//      Users
// WHERE
//      Active = 1
// ORDER BY
//      Name ASC
```

### Adding Parameters

```csharp
var query = new QueryBreakdown("*", "Users");
query.WhereClause.Clause = "UserID = @UserId AND Status = @Status";

// Add parameters
query.AddParameter("UserId", 123);
query.AddParameter("Status", "Active");

// Set parameter values
query.SetParameterValue("UserId", 456);

// Get parameter value
var userId = query.GetParameterValue("UserId"); // Returns 456
```

### Automatic Parameter Extraction

When using `AddWhereClause`, parameters are automatically extracted and added to the Parameters dictionary:

```csharp
var query = new QueryBreakdown("*", "Users");

// Parameters are automatically extracted and added
query.AddWhereClause("UserID = @UserId AND Status = @Status");

// Parameters dictionary now contains @UserId and @Status (both initially null)
Assert.That(query.Parameters, Does.ContainKey("@UserId"));
Assert.That(query.Parameters, Does.ContainKey("@Status"));

// Set parameter values
query.SetParameterValue("UserId", 123);
query.SetParameterValue("Status", "Active");

// Adding additional WHERE conditions automatically extracts new parameters
query.AddWhereClause("CreatedDate > @StartDate", "and");
Assert.That(query.Parameters, Does.ContainKey("@StartDate"));
```

**Smart Parameter Management:**
- If a parameter doesn't exist, it's created with a `null` value
- If a parameter exists with a `null` value, setting a value updates it
- If a parameter exists with a non-null value, it's preserved (not overwritten by repeated AddWhereClause calls)
- If you attempt to set a parameter to a different type than its existing value, an `InvalidOperationException` is thrown

```csharp
// Example: Type-safe parameter management
var query = new QueryBreakdown("*", "Orders");

query.SetParameterValue("@OrderId", 123); // Set as int
query.AddWhereClause("OrderID = @OrderId"); // Preserves existing value (123)

// This would throw InvalidOperationException:
// query.Parameters["@OrderId"] = "string_value"; // Different type!
```

**Snowflake Parameter Support:**

Snowflake QueryBreakdown supports both `:parameter` and `@parameter` syntax:

```csharp
// Snowflake with colon parameters
var query = new Snowflake.QueryBreakdown("*", "USERS");
query.AddWhereClause("USER_ID = :UserId AND STATUS = :Status", false);

// Parameters are extracted with colon prefix
Assert.That(query.Parameters, Does.ContainKey(":UserId"));
Assert.That(query.Parameters, Does.ContainKey(":Status"));
```

### Using Constructors

```csharp
// Constructor with SELECT and FROM
var query1 = new QueryBreakdown("ID, Name", "Users");

// Constructor with SELECT, FROM, and WHERE
var query2 = new QueryBreakdown("ID, Name", "Users", "Active = 1");

// Constructor with all common clauses
var query3 = new QueryBreakdown("ID, Name", "Users", "Active = 1", "Name ASC");
```

### Building WHERE Clauses Dynamically

```csharp
var query = new QueryBreakdown("*", "Orders");

// Add conditions with AND (default)
query.AddWhereClause("Status = 'Open'");
query.AddWhereClause("Amount > 100");

// Add condition with OR
query.AddWhereClause("Priority = 'High'", "or");

// Result: WHERE Status = 'Open' and Amount > 100 or Priority = 'High'

// Parameters are automatically extracted from WHERE clauses
query.AddWhereClause("CustomerID = @CustomerId");
query.AddWhereClause("Region = @Region", "and");

// Set the parameter values
query.SetParameterValue("@CustomerId", 456);
query.SetParameterValue("@Region", "West");

// Result: WHERE Status = 'Open' and Amount > 100 or Priority = 'High' and CustomerID = @CustomerId and Region = @Region
```

### Expression-Based Query Building

Build queries type-safely using expression trees with operator overloads:

```csharp
using Strata.SqlTools.Expressions;
using Strata.SqlTools.Expressions.Literals;

var query = new QueryBreakdown();
query.FromClause.Clause = "Products";

// Create column expressions
var productId = new GenericColumnExpression(1, "ProductID", "ID");
var price = new GenericColumnExpression(2, "Price", "Price");
var category = new GenericColumnExpression(3, "Category", "Category");

// Add SELECT expressions with operator overloads
query.AddSelectExpression(productId);  // ProductID AS ID
query.AddSelectExpression(price * 1.1, "PriceWithTax");  // Price * 1.1 AS PriceWithTax
query.AddSelectExpression(price + 10, "PriceWithFee");   // Price + 10 AS PriceWithFee

// Add WHERE expressions with comparison operators
query.AddWhereExpression(price > 100);  // Price > 100
query.AddWhereExpression(category == "Electronics", null, "AND");  // AND Category = 'Electronics'

string sql = query.GetSql();
// SELECT
//      ProductID AS ID, (Price * 1.1) AS PriceWithTax, (Price + 10) AS PriceWithFee
// FROM
//      Products
// WHERE
//      Price > 100 AND Category = 'Electronics'
```

### Advanced Expression Operations

```csharp
// Complex arithmetic expressions
var revenue = new GenericColumnExpression(1, "Quantity", "Quantity");
var unitPrice = new GenericColumnExpression(2, "UnitPrice", "UnitPrice");
var discount = new GenericColumnExpression(3, "Discount", "Discount");

var totalRevenue = (revenue * unitPrice) * (1 - discount);
query.AddSelectExpression(totalRevenue, "TotalRevenue");

// Multiple comparison operators
var minPrice = new GenericColumnExpression(4, "MinPrice", "MinPrice");
var maxPrice = new GenericColumnExpression(5, "MaxPrice", "MaxPrice");

query.AddWhereExpression(unitPrice >= minPrice, null, "AND");
query.AddWhereExpression(unitPrice <= maxPrice, null, "AND");

// Boolean literal comparisons
var isActive = new GenericColumnExpression(6, "IsActive", "IsActive");
query.AddWhereExpression(isActive == new BooleanLiteralExpression(true), null, "AND");

// Result: WHERE UnitPrice >= MinPrice AND UnitPrice <= MaxPrice AND IsActive = 1
```

### Expression Comments

Add SQL comments to generated expressions for better readability:

```csharp
var salesAmount = new GenericColumnExpression(1, "SalesAmount", "Amount");

// Add expression with inline comment
query.AddSelectExpression(
    salesAmount * 1.08,
    "-- Calculate amount including 8% tax");

// Add WHERE expression with comment
query.AddWhereExpression(
    salesAmount > 1000,
    "-- Filter for high-value transactions",
    "AND");

// Generated SQL includes comments:
// SELECT
//      (SalesAmount * 1.08) AS AmountWithTax -- Calculate amount including 8% tax
// FROM ...
// WHERE
//      SalesAmount > 1000 -- Filter for high-value transactions
```

### Adding Expressions to GROUP BY, ORDER BY, and HAVING Clauses

Build GROUP BY, ORDER BY, and HAVING clauses using expressions:

```csharp
using Strata.SqlTools.Expressions;

var query = new QueryBreakdown();
query.FromClause.Clause = "Sales";

// Create column expressions
var region = new GenericColumnExpression(1, "Region", "Region");
var salesAmount = new GenericColumnExpression(2, "SalesAmount", "Amount");
var year = new GenericColumnExpression(3, "Year", "Year");

// Add SELECT with aggregation
query.AddSelectExpression(region);
query.AddSelectExpression(year);
query.AddSelectExpression(new SumExpression(salesAmount), "TotalSales");

// Add GROUP BY expressions
query.AddGroupByExpression(region);
query.AddGroupByExpression(year);

// Add HAVING expression with aggregation filter
var totalSalesExpr = new SumExpression(salesAmount);
query.AddHavingExpression(totalSalesExpr > 10000);

// Add ORDER BY expressions
query.AddOrderByExpression(new SumExpression(salesAmount), "DESC");

string sql = query.GetSql();
// SELECT
//      Region, Year, SUM(Amount) AS TotalSales
// FROM
//      Sales
// GROUP BY
//      Region, Year
// HAVING
//      SUM(Amount) > 10000
// ORDER BY
//      SUM(Amount) DESC
```

### Common Table Expressions (CTEs)

```csharp
// Create main query
var mainQuery = new QueryBreakdown(
    "p.ProductName, ps.TotalQuantity, ps.TotalRevenue",
    "Products p INNER JOIN ProductSummary ps ON p.ProductID = ps.ProductID");

// Create CTE using QueryBreakdown object
var cte = new QueryBreakdown(
    "ProductID, SUM(Quantity) AS TotalQuantity, SUM(Price * Quantity) AS TotalRevenue",
    "OrderDetails")
{
    GroupByClause = "ProductID",
    HavingClause = "SUM(Quantity) > 10"
};

// Add CTE to main query
mainQuery.AddWithClause("ProductSummary", cte);

string sql = mainQuery.GetSql();
// Output:
// WITH
//      ProductSummary AS (
//           SELECT
//                ProductID, SUM(Quantity) AS TotalQuantity, SUM(Price * Quantity) AS TotalRevenue
//           FROM
//                OrderDetails
//           GROUP BY
//                ProductID
//           HAVING
//                SUM(Quantity) > 10
//      )
// SELECT
//      p.ProductName, ps.TotalQuantity, ps.TotalRevenue
// FROM
//      Products p INNER JOIN ProductSummary ps ON p.ProductID = ps.ProductID
```

### Multiple CTEs

```csharp
var mainQuery = new QueryBreakdown("*", "FinalData");

// Add multiple CTEs in order
var cte1 = new QueryBreakdown("OrderID, CustomerID, OrderDate", "Orders")
{
    WhereClause = "OrderDate >= '2024-01-01'"
};

var cte2 = new QueryBreakdown("CustomerID, COUNT(*) AS OrderCount", "RecentOrders")
{
    GroupByClause = "CustomerID"
};

var cte3 = new QueryBreakdown("CustomerID, Name, OrderCount",
    "Customers c INNER JOIN CustomerOrders co ON c.CustomerID = co.CustomerID");

mainQuery.AddWithClause("RecentOrders", cte1);
mainQuery.AddWithClause("CustomerOrders", cte2);
mainQuery.AddWithClause("FinalData", cte3);

// CTEs are generated in the order they were added with proper comma separation
string sql = mainQuery.GetSql();
```

### Accessing WITH Clause Objects

```csharp
var mainQuery = new QueryBreakdown("*", "Products");
var cte = new QueryBreakdown("ProductID, COUNT(*) AS OrderCount", "Orders");
((IQueryBreakdown)cte).GroupByClause = "ProductID";

mainQuery.AddWithClause("ProductOrders", cte);

// Access WithClause objects via the WithClauses collection
IWithClause withClause = mainQuery.WithClauses[0];

Console.WriteLine(withClause.TableName);     // "ProductOrders"
Console.WriteLine(withClause.Query);          // The IQueryBreakdown object
Console.WriteLine(withClause.Sql);            // Parsed SqlClauses structure (if available)
Console.WriteLine(withClause.Comment);        // Any associated SQL comments

// Query property provides access to parameters
if (withClause.Query != null)
{
    var parameters = withClause.Query.Parameters;
    // Work with CTE parameters
}
```

### Adding CTE from SQL String

```csharp
var mainQuery = new QueryBreakdown("*", "Products");

// Parse SQL string and add as CTE
string cteSql = "SELECT ProductID, COUNT(*) AS OrderCount FROM Orders GROUP BY ProductID";
mainQuery.AddWithClause("ProductOrders", cteSql, isMicrosoftSql: true);

// The SQL is parsed into a QueryBreakdown and added to the CTE list
```

### Parsing Existing SQL

```csharp
string sql = @"
    SELECT
        u.UserID, u.Name, u.Email,
        (SELECT COUNT(*) FROM Orders o WHERE o.UserID = u.UserID) AS OrderCount
    FROM Users u
    WHERE u.Active = 1
    ORDER BY u.Name";

// Parse the SQL
var query = QueryBreakdown.Parse(sql);

// Access components
Console.WriteLine(query.SelectClause);
Console.WriteLine(query.FromClause);
Console.WriteLine(query.WhereClause);
Console.WriteLine(query.OrderByClause);

// Modify and regenerate
query.WhereClause += " AND u.CreatedDate > '2024-01-01'";
string modifiedSql = query.GetSql();
```

### Safe Parsing with TryParse

```csharp
string sql = "SELECT * FROM Users WHERE UserID = @UserId";

if (QueryBreakdown.TryParse(sql, out var query, out var error))
{
    Console.WriteLine("Parsed successfully!");
    Console.WriteLine($"Parameters found: {query.Parameters.Count}");
}
else
{
    Console.WriteLine($"Parse failed: {error}");
}
```

### Merging Queries

```csharp
var baseQuery = new QueryBreakdown("u.ID, u.Name", "Users u");
baseQuery.WhereClause.Clause = "u.Active = 1";

var joinQuery = new QueryBreakdown();
joinQuery.FromClause.Clause = "INNER JOIN Orders o ON u.ID = o.UserID";
joinQuery.WhereClause.Clause = "o.Status = 'Completed'";

// Merge queries
baseQuery.MergeWith(joinQuery);

// Result: FROM Users u INNER JOIN Orders o ON u.ID = o.UserID
//         WHERE u.Active = 1 AND o.Status = 'Completed'
```

### Cloning Queries

```csharp
var original = new QueryBreakdown("*", "Users")
{
    WhereClause = "Active = 1",
    OrderByClause = "Name ASC"
};
original.AddParameter("Status", "Active");

// Create deep clone
var clone = (QueryBreakdown)original.Clone();

// Modify clone without affecting original
clone.WhereClause = "Active = 0";
```

### Setup and Finish Clauses

```csharp
var query = new QueryBreakdown("*", "#TempUsers");

// Add setup clauses (executed before main query)
query.SetupClauses.Add("CREATE TABLE #TempUsers (ID INT, Name VARCHAR(100))");
query.SetupClauses.Add("INSERT INTO #TempUsers VALUES (1, 'John'), (2, 'Jane')");

// Add finish clauses (cleanup after main query)
query.FinishClauses.Add("DROP TABLE #TempUsers");

// Generate complete script
string script = query.GetSql(includeSetupFinish: true);

// Or generate only the SELECT without setup/finish
string selectOnly = query.GetSql(includeSetupFinish: false);
```

## Snowflake SQL Usage

### Basic Snowflake Query

```csharp
var query = new SnowflakeQueryBreakdown("ID, NAME, EMAIL", "USERS");
query.WhereClause.Clause = "ACTIVE = 1";
query.OrderByClause.Clause = "NAME ASC LIMIT 100";

string sql = query.GetSql();
// Output uses 4-space Snowflake indentation:
// SELECT
//     ID, NAME, EMAIL
// FROM
//     USERS
// WHERE
//     ACTIVE = 1
// ORDER BY
//     NAME ASC LIMIT 100
```

### Snowflake Parameters (Colon Syntax)

```csharp
string sql = "SELECT * FROM USERS WHERE USER_ID = :userId AND STATUS = :status";

var query = SnowflakeQueryBreakdown.Parse(sql, isMicrosoftSql: false);

// Snowflake parameters extracted with colon prefix
Console.WriteLine(query.Parameters.ContainsKey(":userId"));  // true
Console.WriteLine(query.Parameters.ContainsKey(":status"));  // true

// Set parameter values
query.SetParameterValue("userId", 123);
query.SetParameterValue("status", "ACTIVE");
```

### Snowflake Expression-Based Query Building

Snowflake supports the same expression API with dialect-specific formatting:

```csharp
using Strata.SqlTools.Expressions;
using Strata.SqlTools.Breakdowns.Snowflake;

var query = new QueryBreakdown();
query.FromClause.Clause = "SALES";

var region = new GenericColumnExpression(1, "REGION", "REGION");
var salesAmount = new GenericColumnExpression(2, "SALES_AMOUNT", "AMOUNT");
var year = new GenericColumnExpression(3, "YEAR", "YEAR");

// Add SELECT expressions with Snowflake-specific SQL generation
query.AddSelectExpression(region, null, isMicrosoftSql: false);
query.AddSelectExpression(year, null, isMicrosoftSql: false);
query.AddSelectExpression(new SumExpression(salesAmount), null, isMicrosoftSql: false);

// Add GROUP BY expressions with dialect control
query.AddGroupByExpression(region, null, isMicrosoftSql: false);
query.AddGroupByExpression(year, null, isMicrosoftSql: false);

// Add HAVING expression
var totalSales = new SumExpression(salesAmount);
query.AddHavingExpression(totalSales > 10000, null, "AND", isMicrosoftSql: false);

// Add ORDER BY expression
query.AddOrderByExpression(totalSales, "DESC", isMicrosoftSql: false);

// Generates Snowflake-formatted SQL:
// SELECT
//     REGION, YEAR, SUM(AMOUNT)
// FROM
//     SALES
// GROUP BY
//     REGION, YEAR
// HAVING
//     SUM(AMOUNT) > 10000
// ORDER BY
//     SUM(AMOUNT) DESC
```

### Snowflake Type Casting (Double Colon)

```csharp
var query = new SnowflakeQueryBreakdown(
    "ID::VARCHAR, AMOUNT::DECIMAL(10,2), CREATE_DATE::TIMESTAMP",
    "ORDERS");

string sql = query.GetSql();
// Preserves Snowflake :: casting syntax
```

### Snowflake JSON/VARIANT Data

```csharp
var query = new SnowflakeQueryBreakdown(
    "JSON_DATA:name::STRING AS NAME, JSON_DATA:age::NUMBER AS AGE",
    "USER_JSON");

string sql = query.GetSql();
// Handles Snowflake JSON path notation
```

### Snowflake CTEs

```csharp
var mainQuery = new SnowflakeQueryBreakdown(
    "R.REGION_NAME, S.TOTAL_SALES",
    "REGIONS R INNER JOIN SALES_SUMMARY S ON R.REGION_ID = S.REGION_ID");

var salesCte = new SnowflakeQueryBreakdown(
    "REGION_ID, SUM(AMOUNT::DECIMAL(18,2)) AS TOTAL_SALES",
    "SALES")
{
    WhereClause = "SALE_DATE >= :startDate",
    GroupByClause = "REGION_ID"
};

mainQuery.AddWithClause("SALES_SUMMARY", salesCte);

// Uses Snowflake 4-space indentation for CTEs
string sql = mainQuery.GetSql();
```

### Snowflake Window Functions with QUALIFY

```csharp
string sql = @"
    SELECT
        ID, NAME, DEPARTMENT, SALARY,
        ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) AS RN
    FROM EMPLOYEES
    QUALIFY ROW_NUMBER() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) = 1";

var query = SnowflakeQueryBreakdown.Parse(sql, isMicrosoftSql: false);

// QUALIFY clause parsed correctly
Console.WriteLine(query.SelectClause); // Contains window function
```

### Parsing Snowflake vs T-SQL

```csharp
string sql = "SELECT ProductID, COUNT(*) AS OrderCount FROM Orders GROUP BY ProductID";

// Parse as Snowflake SQL (default)
var snowflakeQuery = SnowflakeQueryBreakdown.Parse(sql, isMicrosoftSql: false);

// Parse as T-SQL (delegates to base QueryBreakdown)
var tsqlQuery = SnowflakeQueryBreakdown.Parse(sql, isMicrosoftSql: true);

// Both work, but generate different formatting
string snowflakeSql = snowflakeQuery.GetSql(); // 4-space indent
string tsqlSql = tsqlQuery.GetSql(); // 5-space indent
```

### Real-World Snowflake Example

```csharp
// Complex analytical query with multiple CTEs
var salesCte = new SnowflakeQueryBreakdown(
    "REGION_ID, PRODUCT_ID, SUM(AMOUNT::DECIMAL(18,2)) AS TOTAL_SALES",
    "SALES_DATA")
{
    WhereClause = "SALE_DATE >= :startDate AND SALE_DATE < :endDate",
    GroupByClause = "REGION_ID, PRODUCT_ID"
};

var topProductsCte = new SnowflakeQueryBreakdown(
    "REGION_ID, PRODUCT_ID, TOTAL_SALES, ROW_NUMBER() OVER (PARTITION BY REGION_ID ORDER BY TOTAL_SALES DESC) AS RN",
    "SALES_SUMMARY");

var mainQuery = new SnowflakeQueryBreakdown(
    "R.REGION_NAME, P.PRODUCT_NAME, T.TOTAL_SALES",
    "TOP_PRODUCTS T INNER JOIN REGIONS R ON T.REGION_ID = R.ID INNER JOIN PRODUCTS P ON T.PRODUCT_ID = P.ID")
{
    WhereClause = "T.RN <= 10",
    OrderByClause = "R.REGION_NAME, T.TOTAL_SALES DESC LIMIT 100"
};

mainQuery.AddWithClause("SALES_SUMMARY", salesCte);
mainQuery.AddWithClause("TOP_PRODUCTS", topProductsCte);
mainQuery.AddParameter("startDate", "2024-01-01");
mainQuery.AddParameter("endDate", "2024-12-31");

string sql = mainQuery.GetSql();
// Generates properly formatted Snowflake SQL with:
// - Multiple CTEs
// - Window functions
// - Parameters
// - LIMIT clause
```

## API Reference

### QueryBreakdown Class

#### Constructors
- `QueryBreakdown()` - Empty constructor
- `QueryBreakdown(string selectClause, string fromClause)` - Basic query with comment extraction
- `QueryBreakdown(string selectClause, string fromClause, string whereClause)` - With WHERE and comment extraction
- `QueryBreakdown(string selectClause, string fromClause, string whereClause, string orderByClause)` - Full constructor with comment extraction

#### Properties

**Clause Properties**
Each clause property contains both the SQL text and any associated comments:

- `ISqlExpressionClause SelectClause` - SELECT clause with comments (can parse into Expression objects)
  - `SelectClause.Clause` - SELECT clause SQL text
  - `SelectClause.Comment` - Associated comments
- `ISqlClause FromClause` - FROM clause with comments
- `ISqlExpressionClause WhereClause` - WHERE clause with comments (can parse into Expression objects)
- `ISqlExpressionClause GroupByClause` - GROUP BY clause with comments (can parse into Expression objects)
- `ISqlExpressionClause HavingClause` - HAVING clause with comments (can parse into Expression objects)
- `ISqlExpressionClause OrderByClause` - ORDER BY clause with comments (can parse into Expression objects)

**String Interface Properties** (via `IQueryBreakdown`)
For backward compatibility, explicit interface implementation provides string accessors:
- `string IQueryBreakdown.SelectClause` - Gets/sets SELECT clause text (auto-updates SqlClause)
- `string IQueryBreakdown.FromClause` - Gets/sets FROM clause text
- `string IQueryBreakdown.WhereClause` - Gets/sets WHERE clause text
- `string IQueryBreakdown.OrderByClause` - Gets/sets ORDER BY clause text
- `string IQueryBreakdown.GroupByClause` - Gets/sets GROUP BY clause text
- `string IQueryBreakdown.HavingClause` - Gets/sets HAVING clause text

**Other Properties**
- `IReadOnlyList<IWithClause> WithClauses` - Ordered list of WITH clauses (CTEs), each containing TableName, Query, and optional parsed SqlClauses
- `Dictionary<string, object> Parameters` - Parameter dictionary
- `IEnumerable<IQueryParam> ParameterList` - Parameter list
- `List<string> SetupClauses` - Setup statements
- `ArrayList FinishClauses` - Cleanup statements
- `bool IsUsingWithClause` - Has WITH clause
- `bool IsUsingFromClause` - Has FROM clause
- `bool IsUsingWhereClause` - Has WHERE clause
- `bool IsUsingOrderByClause` - Has ORDER BY
- `bool IsUsingGroupByClause` - Has GROUP BY
- `bool IsUsingHavingClause` - Has HAVING

#### Core Methods
- `string GetSql(bool includeSetupFinish = true)` - Generate SQL string
- `virtual SqlClauses GetClauses()` - Get a SqlClauses object containing the current clause properties
- `virtual void ApplyClauses(SqlClauses? clauses)` - Apply SQL clauses from a SqlClauses object (only non-null clauses are applied)
- `void AddParameter(string name, object value)` - Add single parameter
- `void AddParameter(IEnumerable<IQueryParam> params)` - Add multiple parameters
- `void SetParameterValue(string name, object value)` - Update parameter value
- `object GetParameterValue(string name)` - Get parameter value
- `void AddWhereClause(string sql)` - Add WHERE condition with AND (automatically extracts parameters)
- `void AddWhereClause(string sql, string operation)` - Add WHERE with AND/OR (automatically extracts parameters)
- `void AddWithClause(string name, IQueryBreakdown query)` - Add CTE from QueryBreakdown
- `virtual void AddWithClause(string name, string sql, bool isMicrosoftSql = true)` - Add CTE from SQL string
- `void MergeWith(IQueryBreakdown query)` - Merge another query
- `object Clone()` - Create deep clone
- `string ToString()` - Get SQL string

#### Expression Methods
- `void AddSelectExpression(Expression expression, string? comment = null)` - Add expression to SELECT clause
- `void AddWhereExpression(Expression expression, string? comment = null, string operation = "AND")` - Add expression to WHERE clause with AND/OR
- `virtual void AddGroupByExpression(Expression expression, string? comment = null)` - Add expression to GROUP BY clause
- `virtual void AddOrderByExpression(Expression expression, string? comment = null)` - Add expression to ORDER BY clause
- `virtual void AddHavingExpression(Expression expression, string? comment = null, string operation = "AND")` - Add expression to HAVING clause with AND/OR

#### Expression Parsing Methods
- `virtual IEnumerable<Expression> GetSelectExpressions()` - Parse SELECT clause into Expression objects
- `virtual Expression? GetWhereExpression()` - Parse WHERE clause into Expression object
- `virtual Expression? GetHavingExpression()` - Parse HAVING clause into Expression object
- `protected virtual IStatementExpressionParser CreateExpressionParser()` - Factory method for creating dialect-specific parsers

#### Static Parsing Methods
- `static QueryBreakdown Parse(string sql)` - Parse SQL (throws on error)
- `static bool TryParse(string sql, out QueryBreakdown result)` - Safe parse
- `static bool TryParse(string sql, out QueryBreakdown result, out string error)` - Safe parse with error

### SnowflakeQueryBreakdown Class

Inherits all members from `QueryBreakdown` with Snowflake-specific overrides.

#### Additional/Overridden Methods
- `override string GetSql(bool includeSetupFinish = true)` - Snowflake formatting (4-space indent)
- `override void AddWithClause(string name, string sql, bool isMicrosoftSql = false)` - Snowflake parsing by default
- `void AddSelectExpression(Expression expression, string? comment, bool isMicrosoftSql = false)` - Snowflake expression formatting
- `void AddWhereExpression(Expression expression, string? comment, string operation, bool isMicrosoftSql = false)` - Snowflake WHERE expressions
- `void AddGroupByExpression(Expression expression, string? comment, bool isMicrosoftSql = false)` - Snowflake GROUP BY expressions
- `void AddOrderByExpression(Expression expression, string? comment, bool isMicrosoftSql = false)` - Snowflake ORDER BY expressions
- `void AddHavingExpression(Expression expression, string? comment, string operation, bool isMicrosoftSql = false)` - Snowflake HAVING expressions
- `override protected IStatementExpressionParser CreateExpressionParser()` - Returns Snowflake-specific parser
- `static new SnowflakeQueryBreakdown Parse(string sql, bool isMicrosoftSql = false)` - Snowflake-aware parsing
- `static bool TryParse(string sql, out SnowflakeQueryBreakdown result, bool isMicrosoftSql = false)`
- `static bool TryParse(string sql, out SnowflakeQueryBreakdown result, out string error, bool isMicrosoftSql = false)`

#### Key Differences from Base Class
- 4-space indentation instead of 5-space
- Supports `:parameter` syntax in addition to `@parameter`
- Handles Snowflake-specific keywords (LIMIT, QUALIFY)
- Default `isMicrosoftSql = false` for parsing and expression methods
- Uses `SnowflakeCommandVisitor` for expression SQL generation

### Expression Classes

#### Core Expression Types
- `Expression` - Base class for all expressions
- `GenericColumnExpression` - Column reference with alias support
- `NumberLiteralExpression` - Numeric literals
- `StringLiteralExpression` - String literals
- `BooleanLiteralExpression` - Boolean literals (true/false)
- `ComparisonOperatorExpression` - Comparison operators (==, !=, >, <, >=, <=)
- `ArithmeticOperatorExpression` - Arithmetic operators (+, -, *, /)

#### Expression Operator Overloads
```csharp
// Arithmetic
Expression + Expression  // Addition
Expression - Expression  // Subtraction
Expression * Expression  // Multiplication
Expression / Expression  // Division

// Comparison
Expression == Expression  // Equality
Expression != Expression  // Inequality
Expression > Expression   // Greater than
Expression < Expression   // Less than
Expression >= Expression  // Greater than or equal
Expression <= Expression  // Less than or equal
```

### SqlClause Class

Represents a SQL clause with optional comments:

```csharp
public class SqlClause : ISqlClause
{
    public string? Clause { get; set; }    // SQL clause text
    public string? Comment { get; set; }   // Associated comments
}
```

### WithClause Class

Represents a WITH clause (Common Table Expression) with structured query information:

```csharp
public class WithClause : SqlClause, IWithClause
{
    public string TableName { get; set; }              // CTE table name
    public SqlClauses? Sql { get; set; }               // Parsed query clauses
    public IQueryBreakdown? Query { get; set; }        // Full query breakdown with parameters
}
```

The `WithClause` extends `SqlClause` to provide:
- **TableName**: The name identifier for the CTE
- **Sql**: Parsed structure of the CTE query (SELECT, FROM, WHERE, etc.)
- **Query**: Full `IQueryBreakdown` reference providing access to parameters and query generation
- **Clause** (inherited): The raw SQL text of the CTE for round-trip parsing
- **Comment** (inherited): Any SQL comments associated with the CTE

This structure enables QueryBreakdown to manage CTEs with proper parameter handling and nested query support.

### SqlClauses Class

Helper structure for holding parsed SQL clauses:

```csharp
public sealed class SqlClauses
{
    public ISqlExpressionClause? SelectClause { get; set; }
    public ISqlClause? FromClause { get; set; }
    public ISqlExpressionClause? WhereClause { get; set; }
    public ISqlExpressionClause? GroupByClause { get; set; }
    public ISqlExpressionClause? HavingClause { get; set; }
    public ISqlExpressionClause? OrderByClause { get; set; }

    public SqlClauses Copy()  // Creates a copy with same clause references
}
```

### Utility Classes

#### SqlUtils (Partial Class)
Static utility methods split across multiple files in `Utilities/` folder:
- `SqlUtils.cs` - Core SQL utilities
- `SqlUtils.Filters.cs` - Filter generation
- `SqlColumnHelpers.cs` - Column and alias helpers
- `SqlDataTypeHelpers.cs` - Data type checks
- `SqlFilterHelpers.cs` - Filter value helpers
- `SqlGuidHelpers.cs` - GUID-related SQL operations
- `SqlPagingHelpers.cs` - Query pagination
- `SqlSchemaHelpers.cs` - Schema information
- `SqlAggregationHelpers.cs` - Aggregate function helpers

#### Other Utilities
- `ArrayUtils` - Array and CSV conversion utilities
- `GuidUtils` - GUID encoding, validation, and parsing
- `StringUtils` - String hashing and manipulation

## Testing

Comprehensive NUnit tests are available in the `Strata.SqlTools.Tests` folder:

- **QueryBreakdownTests.cs** - 100+ tests covering:
  - Constructor variations
  - Property accessors (SqlClause objects)
  - Parameter management
  - WHERE clause building
  - Expression-based query building (AddSelectExpression, AddWhereExpression)
  - CTE operations
  - Parsing (simple and complex)
  - Merging and cloning
  - SQL injection patterns
  - Whitespace handling
  - Special characters
  - Nested queries
  - Comment preservation
  - Edge cases

- **SnowflakeQueryBreakdownTests.cs** - 80+ tests covering:
  - Snowflake-specific syntax
  - Double colon casting
  - VARIANT/JSON data types
  - FLATTEN function
  - Time Travel
  - QUALIFY clause
  - Colon parameters
  - Expression-based building with Snowflake formatting
  - Indentation differences (4-space vs 5-space)
  - Dual-syntax support (isMicrosoftSql parameter)
  - Real-world scenarios

- **StatementReaderTests.cs** - Tokenization tests for both SQL Server and Snowflake
- **StatementExpressionParserTests.cs** - Expression parsing and tree building
- **CommentTests.cs** - SQL comment extraction and preservation

Run tests:
```bash
dotnet test Strata.SqlTools.Tests
```

## Best Practices

### Security
- Always use parameters for user input to prevent SQL injection
- Use the `Parameters` dictionary for value binding
- Never concatenate user input directly into SQL strings
- Prefer expression-based building over string concatenation

```csharp
// ✅ GOOD - Using parameters
var query = new QueryBreakdown("*", "Users");
query.WhereClause.Clause = "Email = @Email";
query.AddParameter("Email", userInput);

// ✅ BETTER - Using expressions (type-safe)
var email = new GenericColumnExpression(1, "Email", "Email");
query.AddWhereExpression(email == userInput);

// ❌ BAD - SQL injection risk
query.WhereClause.Clause = $"Email = '{userInput}'";
```

### Code Quality

**Expression-Based Building**
- Use `AddSelectExpression` and `AddWhereExpression` for type-safe query building
- Leverage operator overloads (+, -, *, /, ==, !=, >, <, >=, <=) for readable code
- Wrap boolean literals in `BooleanLiteralExpression` for proper dialect support
- Use expressions for complex calculations to ensure proper parenthesization

```csharp
// Type-safe with automatic SQL generation
var revenue = quantity * unitPrice * (1 - discount);
query.AddSelectExpression(revenue, "NetRevenue");

// vs. error-prone string building
query.SelectClause.Clause = "Quantity * UnitPrice * (1 - Discount) AS NetRevenue";
```

**Comment Preservation**
- Access `.Clause` and `.Comment` properties of SqlClause objects separately
- Use comment parameters in expression methods for self-documenting SQL
- Comments are preserved during parsing and reconstruction

```csharp
// Access clause and comment separately
Console.WriteLine(query.SelectClause.Clause);   // SQL text
Console.WriteLine(query.SelectClause.Comment);  // Associated comments

// Add expressions with documentation
query.AddSelectExpression(
    salesAmount * 1.08,
    "TotalWithTax",
    "-- Includes 8% sales tax");
```

### Performance
- Use `includeSetupFinish: false` when generating CTEs to avoid recursion
- Cache parsed queries when executing the same SQL multiple times
- Use `TryParse` instead of `Parse` in production to handle errors gracefully
- Expression trees are evaluated once during SQL generation (no runtime overhead)

### Maintainability
- Use descriptive CTE names that explain their purpose
- Add comments to complex expressions using the comment parameter
- Break large queries into multiple CTEs for readability
- Use the query builder and expressions instead of string concatenation
- Leverage the visitor pattern for dialect-specific customization

### Dialect Handling
- Always specify `isMicrosoftSql` parameter when working with Snowflake
- Use `SnowflakeQueryBreakdown` for Snowflake-specific features
- Test with both dialects if building cross-platform queries
- Expression methods automatically handle dialect differences

```csharp
// Snowflake with explicit dialect control
var snowflakeQuery = new SnowflakeQueryBreakdown();
snowflakeQuery.AddSelectExpression(
    productId,
    null,
    isMicrosoftSql: false);  // Uses Snowflake formatting

// SQL Server uses default
var sqlServerQuery = new QueryBreakdown();
sqlServerQuery.AddSelectExpression(productId);  // Uses T-SQL formatting
```

### Error Handling
```csharp
if (QueryBreakdown.TryParse(sql, out var query, out var error))
{
    // Use query
    string result = query.GetSql();
}
else
{
    // Log error and handle gracefully
    _logger.LogError($"Failed to parse SQL: {error}");
    throw new InvalidOperationException($"Invalid SQL: {error}");
}
```

## Limitations

### Parsing Limitations
- **Comments**: SQL comments are extracted and preserved but may cause issues in some complex scenarios (e.g., comments inside string literals)
- **Dollar-quoted strings**: Snowflake `$$` syntax has limited support
- **PIVOT/UNPIVOT**: Complex pivoting operations may not parse correctly
- **Stored procedures**: Only SELECT statements are supported, not stored procedure definitions
- **Some Snowflake features**: Advanced features like MATCH_RECOGNIZE have limited support

### Expression System Limitations
- **Aggregate functions**: Some complex aggregates may require string-based building
- **Window functions**: Complex OVER clauses should use string-based WHERE/SELECT clauses
- **Type inference**: Expression system doesn't validate SQL types at compile time
- **Subqueries**: Nested subqueries in expressions not fully supported

### Workarounds
For unsupported scenarios, use direct string assignment:
```csharp
// Complex window function - use string assignment
query.SelectClause.Clause = @"
    ProductID,
    ROW_NUMBER() OVER (PARTITION BY Category ORDER BY Price DESC) AS RowNum";

// Simple expressions - use expression API
var price = new GenericColumnExpression(1, "Price", "Price");
query.AddWhereExpression(price > 100);
```

## Design Patterns

The library employs several design patterns for extensibility and maintainability:

### Template Method Pattern

The `CommandVisitor` class uses the Template Method pattern to allow dialect-specific SQL formatting:

```mermaid
graph TD
    A[CommandVisitor.Visit] --> B{Expression Type?}
    B -->|Column| C[FormatIdentifier]
    B -->|Parameter| D[FormatParameterName]
    B -->|Boolean| E[FormatBooleanLiteral]
    B -->|String| F[FormatStringLiteral]

    C --> G[Subclass Override]
    D --> G
    E --> G
    F --> G

    G --> H[SQL String]

    style A fill:#ffe1e1
    style G fill:#e1ffe1
```

**Benefits:**
- 82% code reduction in Snowflake implementation (280 lines → 50 lines)
- Easy to add new SQL dialects
- Centralized common logic
- Override only what differs between dialects

### Visitor Pattern

Expression trees use the Visitor pattern for SQL generation:

```csharp
// Expression tree
var expr = (quantity * unitPrice) * (1 - discount);

// Visitor converts to SQL
var visitor = new CommandVisitor();
string sql = expr.Accept(visitor);  // "((Quantity * UnitPrice) * (1 - Discount))"
```

### Builder Pattern

QueryBreakdown acts as a builder for complex SQL queries:

```csharp
var query = new QueryBreakdown()
    .WithSelect("ID, Name")
    .WithFrom("Users")
    .WithWhere("Active = 1");

// Fluent API for query construction
query.AddSelectExpression(column1);
query.AddWhereExpression(column2 > 100);
```

### Explicit Interface Implementation

Maintains backward compatibility while evolving to SqlClause objects:

```csharp
// New: Direct SqlClause access
query.SelectClause.Clause = "ID, Name";
query.SelectClause.Comment = "-- User columns";

// Old: String interface (still works)
((IQueryBreakdown)query).SelectClause = "ID, Name";
```

## Credits

This library is developed and maintained by the Strata Decision Technology team.

## Contributing

When contributing to this library:
1. Ensure all existing tests pass
2. Add tests for new features
3. Follow existing code style and naming conventions
4. Update this README with new features and examples
5. Document any breaking changes

## Version History

### Current Release
- **Expression System**: Type-safe query building with `AddSelectExpression` and `AddWhereExpression` methods
- **SqlClause Objects**: Refactored to use `SqlClause` class for better comment preservation
- **Template Method Pattern**: Implemented in `CommandVisitor` for 82% code reduction in Snowflake variant
- **Improved Architecture**: Reorganized into focused folders (Utilities, Classes, Enums, Extensions)
- **Comment Preservation**: SQL comments extracted and preserved during parsing and reconstruction
- **Dual Syntax Support**: Snowflake constructors and expression methods support both SQL Server and Snowflake parsing
- **Comprehensive Testing**: 200+ NUnit tests covering expressions, dialects, and edge cases

### Previous Releases
- Full CTE support with ordered list storage
- Comprehensive test coverage for both SQL Server and Snowflake
- Added Snowflake SQL support with dialect-specific features
- Added parameter extraction and management
- Added query merging and composition
- Initial release with basic SELECT parsing and generation

## Migration Guide

### Upgrading to SqlClause Objects

If you were directly assigning to clause properties:

```csharp
// Old approach (still works via explicit interface)
query.SelectClause.Clause = "ID, Name";

// New approach (recommended)
query.SelectClause.Clause = "ID, Name";
query.SelectClause.Comment = "-- Primary columns";

// Or use expressions (preferred)
var id = new GenericColumnExpression(1, "ID", "ID");
var name = new GenericColumnExpression(2, "Name", "Name");
query.AddSelectExpression(id);
query.AddSelectExpression(name);
```

### Adopting Expression-Based Building

For new code, prefer expressions over strings:

```csharp
// String-based (old)
query.SelectClause.Clause = "Price * Quantity AS Revenue";
query.WhereClause.Clause = "Price > 100 AND Category = 'Electronics'";

// Expression-based (new - type-safe)
var price = new GenericColumnExpression(1, "Price", "Price");
var quantity = new GenericColumnExpression(2, "Quantity", "Quantity");
var category = new GenericColumnExpression(3, "Category", "Category");

query.AddSelectExpression(price * quantity, "Revenue");
query.AddWhereExpression(price > 100);
query.AddWhereExpression(category == "Electronics", null, "AND");
```

## Support

For questions or issues:
- Check the test files for additional usage examples
- Review the inline XML documentation
- Refer to the Architecture section for design patterns and component relationships
- Contact the Strata development team

## Folder Structure

```
Strata.SqlTools/
├── Breakdowns/           # Query breakdown implementations
│   └── InsertSqlBreakdown.cs
├── Classes/              # Core data structures
│   ├── SqlClause.cs     # SQL clause with comments
│   ├── SqlClauses.cs    # Container for all clauses
│   ├── Token.cs         # Tokenization result
│   ├── QueryParam.cs
│   ├── SqlFilter.cs
│   └── ...
├── Enums/SQL/           # SQL-related enumerations
│   ├── TokenType.cs     # Lexical token types
│   ├── SqlDataType.cs
│   ├── FilterOperation.cs
│   ├── AggregateFunction.cs
│   └── ...
├── Exceptions/          # Custom exception types
├── Expressions/         # Expression tree components
│   ├── Expression.cs    # Base expression class
│   ├── Literals/        # Literal expressions
│   ├── Functions/       # Function expressions
│   └── Operators/       # Operator expressions
├── Extensions/          # Extension methods
│   └── StringBuilderEx.cs
├── Interfaces/          # Contracts and abstractions
│   ├── IQueryBreakdown.cs
│   ├── IStatementReader.cs
│   └── ...
├── QueryBuilders/       # Query builder implementations
├── Snowflake/          # Snowflake-specific implementations
│   ├── CommandVisitor.cs          # Snowflake SQL generation
│   ├── QueryBreakdown.cs          # Snowflake query representation
│   ├── StatementExpressionParser.cs
│   ├── StatementParser.cs         # Snowflake SQL parsing
│   └── StatementReader.cs         # Snowflake tokenization
├── SqlServer/          # SQL Server-specific implementations
│   ├── CommandVisitor.cs          # T-SQL generation (base)
│   ├── QueryBreakdown.cs          # SQL Server query representation
│   ├── StatementExpressionParser.cs
│   ├── StatementParser.cs         # T-SQL parsing (base)
│   └── StatementReader.cs         # T-SQL tokenization (base)
└── Utilities/          # Helper utilities (partial SqlUtils class)
    ├── ArrayUtils.cs
    ├── GuidUtils.cs
    ├── StringUtils.cs
    ├── SqlUtils.cs                # Core SQL utilities
    ├── SqlUtils.Filters.cs        # Filter generation
    ├── SqlAggregationHelpers.cs   # Aggregate functions
    ├── SqlColumnHelpers.cs        # Column operations
    ├── SqlDataTypeHelpers.cs      # Type checking
    ├── SqlFilterHelpers.cs        # Filter helpers
    ├── SqlGuidHelpers.cs          # GUID operations
    ├── SqlPagingHelpers.cs        # Pagination
    └── SqlSchemaHelpers.cs        # Schema info
```

### Key File Relationships

```mermaid
graph LR
    A[QueryBreakdown] --> B[StatementParser]
    A --> C[CommandVisitor]
    B --> D[SqlClause/SqlClauses]
    C --> E[Expression]
    F[StatementReader] --> G[Token]
    G --> H[TokenType]
    F --> I[StatementExpressionParser]
    I --> E

    style A fill:#ffe1e1
    style D fill:#e1f5ff
    style E fill:#e1ffe1
    style H fill:#ffffcc
```

## License

Copyright © Strata Decision Technology. All rights reserved.