190 lines
7.3 KiB
Markdown
190 lines
7.3 KiB
Markdown
# Strata.SqlTools
|
|
|
|
[](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)
|