# Strata.SqlTools

[![build](https://github.com/stratadecision/sql-utilities/actions/workflows/build.yaml/badge.svg)](https://github.com/stratadecision/sql-utilities/actions/workflows/build.yaml)

## General Information
This library provides SQL utilities for parsing, analyzing, and manipulating SQL queries programmatically. It includes:

### Core Features
* **QueryBreakdown**: Deep parsing of SELECT statements into component parts (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
* **Expression Trees**: Type-safe expression building with operator overloading
* **WITH Clause Support**: Common Table Expressions (CTEs) parsing and generation
* **Comment Preservation**: Round-trip parsing that preserves SQL comments

### SQL Dialect Support
* **SQL Server**: Full T-SQL dialect support with bracket identifiers and @parameters
* **PostgreSQL**: PostgreSQL syntax with $n positional and :named parameters
* **Snowflake**: Snowflake SQL dialect with :parameters and uppercase identifiers
* **LINQ to SQL**: Expression tree analysis for IQueryable queries

### Visualization
* **Mermaid Diagrams**: Generate flowcharts, sequence diagrams, and ER diagrams
* **Query Structure Visualization**: Visual representation of query clauses and flow
* **LINQ Method Chain Diagrams**: Visualize LINQ query execution pipelines
* **Collection Analysis**: Batch query analysis with parameter usage reports

### Integration
* **Entity Framework Core**: Persist and query QueryBreakdown objects with EF Core

## Credits
This library is developed and maintained by Strata Decision Technology

## Documentation

Comprehensive documentation is available in the [docs](docs/) folder:

### Core Documentation
- **[Architecture Review](docs/ARCHITECTURE_REVIEW.md)** - Design patterns, class hierarchies, and extensibility guide
- **[API Documentation](docs/SqlUtilities.Core.md)** - Complete API reference with examples
- **[NuGet Packaging](docs/NUGET_PACKAGING.md)** - Build and publishing guidelines

### Dialect-Specific Guides
- **[SQL Server Guide](docs/SqlUtilities.SqlServer.md)** - T-SQL specific features
- **[PostgreSQL Guide](docs/SqlUtilities.PostgreSql.md)** - PostgreSQL syntax and parameter support
- **[Snowflake Guide](docs/SqlUtilities.Snowflake.md)** - Snowflake SQL specific features
- **[LINQ to SQL Guide](docs/SqlUtilities.LinqToSql.md)** - LINQ query analysis and expression trees

### Integration & Visualization
- **[Markdown Visualization](docs/SqlUtilities.Markdown.md)** - Mermaid diagram generation guide
- **[EFCore Integration](docs/EFCore_Integration_Guide.md)** - Entity Framework Core patterns
- **[WITH Clause Implementation](docs/WITHCLAUSE_NEXT_STEPS.md)** - CTE feature details

See the [documentation index](docs/README.md) for a complete list.

## Branching and Versioning

| branch     | version format        | example         |
| ---------- | --------------------- | --------------- |
| main       | #.#.#                 | 1.2.3           |
| feature/\* | #.#+1.0-featureName.# | 1.3.0-newfeat.1 |
| fix/\*     | #.#.#+1-fixName.#     | 1.2.4-bug.1     |

See our [confluence page](https://confluence.sdt.local/display/DOP/Branching+and+Versioning) for more information

## Usage

### Build and Test

Build the solution:
```bash
dotnet build Strata.SqlTools.sln
```

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

### TestContainer Integration Tests

The `testContainers` folder contains real-world integration tests for PostgreSQL and SQL Server using Docker containers. These tests are **not included in the main solution** to keep CI/CD builds fast and avoid Docker dependencies in the build pipeline.

**Requirements:**
- Docker Desktop installed and running
- Tests take 1-2 minutes to run (container startup time)

**Run TestContainer Tests:**

```bash
# Run SQL Server integration tests (18 tests)
dotnet test testContainers/Strata.SqlTools.SqlServer.TestContainers

# Run PostgreSQL integration tests (17 tests)
dotnet test testContainers/Strata.SqlTools.PostgreSql.TestContainers

# Run all TestContainer tests (35 tests)
dotnet test testContainers/Strata.SqlTools.SqlServer.TestContainers
dotnet test testContainers/Strata.SqlTools.PostgreSql.TestContainers
```

**Note:** These integration tests are excluded from the main solution to support Docker-less build environments. They remain fully functional for local development and can be run independently as shown above.

#### Core & Dialect Packages
* **Strata.SqlTools** - Core SQL parsing and expression library
* **Strata.SqlTools.SqlServer** - SQL Server (T-SQL) specific implementations
* **Strata.SqlTools.PostgreSql** - PostgreSQL specific implementations with parameter analysis
* **Strata.SqlTools.Snowflake** - Snowflake SQL specific implementations
* **Strata.SqlTools.LinqToSql** - LINQ to SQL query analysis and expression tree parsing

#### Integration & Visualization Packages
* **Strata.SqlTools.Markdown** - Mermaid diagram generation for all SQL dialects
* **Strata.SqlTools.EFCore** - Entity Framework Core integration for QueryBreakdown persistence
* **Strata.SqlTools.Rules** - Rule engine for SQL query validation and analysis

### NuGet Packages

This solution produces the following NuGet packages:
* **Strata.SqlTools** - Core SQL parsing and expression library
* **Strata.SqlTools.SqlServer** - SQL Server (T-SQL) specific implementations
* **Strata.SqlTools.Snowflake** - Snowflake SQL specific implementations

### Package Features

**Package Metadata:**
- Symbol packages (snupkg) for debugging support
- Source Link enabled for debugging into package source
- XML documentation included
- MIT License
- README included in package

**Code Quality:**
- .NET 9.0 target framework
- Nullable reference types enabled
- .NET Analyzers and code style enforcement
- Full XML documentation on public APIs
- EditorConfig for consistent code style

### Creating NuGet Packages

Build and create packages:
```bash
dotnet pack Strata.SqlTools.sln -c Release
```

Packages will be output to the `bin/Release` folders of each project.

Create a specific package:
```bash
dotnet pack src/Strata.SqlTools/Strata.SqlTools.csproj -c Release -o ./nupkg
```

### Publishing to NuGet

Validate package before publishing:
```bash
dotnet tool install -g dotnet-validate
dotnet validate package nupkg/Strata.SqlTools.1.0.0.nupkg
```

Publish to NuGet.org:
```bash
dotnet nuget push nupkg/Strata.SqlTools.1.0.0.nupkg --api-key YOUR_API_KEY --source https://api.nuget.org/v3/index.json
```

### Publishing Checklist

Before publishing to NuGet.org:
- [ ] Verify all public APIs have XML documentation
- [ ] Run full test suite and ensure 100% pass rate
- [ ] Update version number according to SemVer
- [ ] Update PackageReleaseNotes with changes
- [ ] Test package installation in a clean project
- [ ] Validate package contents using `dotnet validate`
- [ ] Push symbols to symbol server for debugging support

## Development

### Prerequisites
- .NET 9.0 SDK or later
- Visual Studio 2022 or VS Code with C# extension

### Code Quality Tools
- **Analyzers**: Enabled for all projects
- **Code Coverage**: Run tests with coverage using your preferred tool
- **SonarQube**: Static analysis issues are tracked

### API Guidelines
- All public APIs must have XML documentation
- Follow [.NET API Design Guidelines](https://learn.microsoft.com/en-us/dotnet/standard/design-guidelines/)
- Maintain backward compatibility within major versions (SemVer)