575 lines
14 KiB
Markdown
575 lines
14 KiB
Markdown
# Strata.SqlTools.Markdown
|
|
|
|
**SQL Query Visualization with Mermaid Diagrams**
|
|
|
|
---
|
|
|
|
## Overview
|
|
|
|
The `Strata.SqlTools.Markdown` package provides comprehensive visualization tools for SQL queries using Mermaid diagrams. It generates flowcharts, sequence diagrams, entity-relationship diagrams, and specialized visualizations for different SQL dialects.
|
|
|
|
### Supported Dialects
|
|
|
|
- ✅ **SQL Server** - T-SQL query visualization
|
|
- ✅ **PostgreSQL** - PostgreSQL query visualization with parameter analysis
|
|
- ✅ **Snowflake** - Snowflake query visualization
|
|
- ✅ **LINQ to SQL** - LINQ expression tree and execution pipeline visualization
|
|
|
|
---
|
|
|
|
## Installation
|
|
|
|
```bash
|
|
dotnet add package Strata.SqlTools.Markdown
|
|
```
|
|
|
|
**Dependencies:**
|
|
- `Strata.SqlTools` (core)
|
|
- `Strata.SqlTools.SqlServer` (for SQL Server visualizations)
|
|
- `Strata.SqlTools.PostgreSql` (for PostgreSQL visualizations)
|
|
- `Strata.SqlTools.Snowflake` (for Snowflake visualizations)
|
|
- `Strata.SqlTools.LinqToSql` (for LINQ visualizations)
|
|
|
|
---
|
|
|
|
## Quick Start
|
|
|
|
### Basic Query Diagram
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Breakdowns.SqlServer;
|
|
using Strata.SqlTools.Markdown.SqlServer;
|
|
|
|
var breakdown = new QueryBreakdown(@"
|
|
SELECT id, name, email
|
|
FROM users
|
|
WHERE age > @minAge
|
|
ORDER BY name ASC
|
|
");
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Query");
|
|
|
|
Console.WriteLine(diagram);
|
|
```
|
|
|
|
**Output:**
|
|
|
|
````markdown
|
|
### User Query
|
|
|
|
```mermaid
|
|
flowchart TD
|
|
Start([Start]) --> Select[SELECT id, name, email]
|
|
Select --> From[FROM users]
|
|
From --> Where[WHERE age > @minAge]
|
|
Where --> OrderBy[ORDER BY name ASC]
|
|
OrderBy --> End([End])
|
|
```
|
|
````
|
|
|
|
---
|
|
|
|
## SQL Server Visualizations
|
|
|
|
### QueryBreakdownGenerator
|
|
|
|
Generate flowchart diagrams showing query structure:
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Markdown.SqlServer;
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
|
|
// Generate basic flowchart
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "Query Structure");
|
|
|
|
// Generate with CTE
|
|
var breakdown = new QueryBreakdown("*", "cte_result");
|
|
breakdown.AddWithClause("cte_result", subquery);
|
|
string cteDiagram = generator.GenerateMermaidDiagram(breakdown, "CTE Query");
|
|
```
|
|
|
|
### SqlStatementGenerator
|
|
|
|
Generate sequence and ER diagrams:
|
|
|
|
```csharp
|
|
var stmtGenerator = new SqlStatementGenerator();
|
|
|
|
// Sequence diagram showing execution flow
|
|
string sequence = stmtGenerator.GenerateSequenceDiagram(breakdown, "Execution");
|
|
|
|
// Entity-relationship diagram
|
|
var tables = new[] { "users", "orders", "products" };
|
|
string erDiagram = stmtGenerator.GenerateEntityRelationshipDiagram(tables, "Schema");
|
|
```
|
|
|
|
---
|
|
|
|
## PostgreSQL Visualizations
|
|
|
|
### QueryBreakdownGenerator
|
|
|
|
PostgreSQL-specific visualization with parameter tracking:
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Breakdowns.PostgreSql;
|
|
using Strata.SqlTools.Markdown.PostgreSql;
|
|
|
|
var breakdown = new QueryBreakdown(@"
|
|
SELECT u.id, u.name, o.total
|
|
FROM users u
|
|
JOIN orders o ON u.id = o.user_id
|
|
WHERE u.age > $1
|
|
ORDER BY o.total DESC
|
|
");
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Orders");
|
|
```
|
|
|
|
### QueryBreakdownCollectionGenerator
|
|
|
|
Visualize collections of queries:
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Markdown.PostgreSql;
|
|
|
|
var collection = new QueryBreakdownCollection();
|
|
collection.Add(new QueryBreakdown("SELECT * FROM users WHERE age > $1"));
|
|
collection.Add(new QueryBreakdown("SELECT * FROM orders WHERE status = $1"));
|
|
|
|
var collectionGen = new QueryBreakdownCollectionGenerator();
|
|
|
|
// Generate summary with all diagrams
|
|
string summary = collectionGen.GenerateCollectionSummary(collection, "All Queries");
|
|
|
|
// Generate parameter usage diagram
|
|
string paramDiagram = collectionGen.GenerateParameterUsageDiagram(collection);
|
|
|
|
// Generate table reference diagram
|
|
string tableDiagram = collectionGen.GenerateTableReferenceDiagram(collection);
|
|
```
|
|
|
|
**Example Parameter Usage Diagram:**
|
|
|
|
```mermaid
|
|
graph LR
|
|
Q1[Query 1] --> P1[$1]
|
|
Q2[Query 2] --> P1
|
|
Q1 --> P2[$2]
|
|
|
|
style P1 fill:#e1f5ff
|
|
style P2 fill:#e1f5ff
|
|
```
|
|
|
|
---
|
|
|
|
## Snowflake Visualizations
|
|
|
|
### QueryBreakdownGenerator
|
|
|
|
Snowflake-specific query visualization:
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Breakdowns.Snowflake;
|
|
using Strata.SqlTools.Markdown.Snowflake;
|
|
|
|
var breakdown = new QueryBreakdown(@"
|
|
SELECT *
|
|
FROM database.schema.table
|
|
WHERE created_at > :start_date
|
|
LIMIT 100
|
|
");
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "Snowflake Query");
|
|
```
|
|
|
|
---
|
|
|
|
## LINQ to SQL Visualizations
|
|
|
|
### LINQ Method Chain Diagrams
|
|
|
|
Visualize LINQ query method chains:
|
|
|
|
```csharp
|
|
using Strata.SqlTools.Breakdowns.LinqToSql;
|
|
using Strata.SqlTools.Markdown.LinqToSql;
|
|
|
|
var query = context.Users
|
|
.Where(u => u.Age > 21)
|
|
.OrderBy(u => u.Name)
|
|
.Select(u => new { u.Id, u.Name });
|
|
|
|
var breakdown = LinqQueryBreakdown.Analyze(query);
|
|
var generator = new QueryBreakdownGenerator();
|
|
|
|
// Generate method chain diagram
|
|
string methodChain = generator.GenerateMethodChainDiagram(breakdown, "LINQ Flow");
|
|
```
|
|
|
|
**Output:**
|
|
|
|
```mermaid
|
|
flowchart LR
|
|
Start[IQueryable] --> Where[Where]
|
|
Where --> OrderBy[OrderBy]
|
|
OrderBy --> Select[Select]
|
|
Select --> Result[Result]
|
|
```
|
|
|
|
### LINQ Execution Pipeline
|
|
|
|
Visualize how LINQ translates to SQL:
|
|
|
|
```csharp
|
|
var sqlGenerator = new SqlStatementGenerator();
|
|
string pipeline = sqlGenerator.GenerateLinqPipelineDiagram(breakdown, "Execution Pipeline");
|
|
```
|
|
|
|
**Output:**
|
|
|
|
```mermaid
|
|
sequenceDiagram
|
|
participant Client as Client Application
|
|
participant LINQ as LINQ Provider
|
|
participant ET as Expression Tree
|
|
participant SQL as SQL Generator
|
|
participant DB as Database
|
|
|
|
Client->>LINQ: LINQ Query
|
|
activate LINQ
|
|
LINQ->>ET: Where Predicate
|
|
activate ET
|
|
LINQ->>ET: Select Projection
|
|
ET->>SQL: Expression Tree
|
|
deactivate ET
|
|
SQL->>DB: Generate SQL
|
|
activate DB
|
|
DB-->>SQL: Result Set
|
|
deactivate DB
|
|
SQL-->>LINQ: Mapped Objects
|
|
LINQ-->>Client: IEnumerable Result
|
|
deactivate LINQ
|
|
```
|
|
|
|
### Combined Diagrams
|
|
|
|
Show both method chain and SQL structure:
|
|
|
|
```csharp
|
|
string combined = generator.GenerateCombinedDiagram(breakdown, "Full Analysis");
|
|
```
|
|
|
|
---
|
|
|
|
## Advanced Features
|
|
|
|
### Custom Diagram Titles
|
|
|
|
```csharp
|
|
// With title
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "My Custom Title");
|
|
|
|
// Without title
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, null);
|
|
```
|
|
|
|
### Nested CTEs Visualization
|
|
|
|
```csharp
|
|
var mainQuery = new QueryBreakdown("*", "cte2");
|
|
var cte1 = new QueryBreakdown("id, name", "users");
|
|
var cte2 = new QueryBreakdown("*", "cte1");
|
|
|
|
mainQuery.AddWithClause("cte1", cte1);
|
|
mainQuery.AddWithClause("cte2", cte2);
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
string diagram = generator.GenerateMermaidDiagram(mainQuery, "Nested CTEs");
|
|
```
|
|
|
|
### Collection Statistics
|
|
|
|
```csharp
|
|
var collectionGen = new QueryBreakdownCollectionGenerator();
|
|
var collection = new QueryBreakdownCollection();
|
|
// ... add queries ...
|
|
|
|
// Generate statistics table
|
|
string stats = $@"
|
|
## Query Statistics
|
|
|
|
- Total Queries: {collection.Count}
|
|
- Total Selected Columns: {collection.GetTotalSelectedColumns()}
|
|
- Unique Tables: {string.Join(", ", collection.GetUniqueTableReferences())}
|
|
|
|
{collectionGen.GenerateCollectionSummary(collection, "Query Details")}
|
|
";
|
|
```
|
|
|
|
---
|
|
|
|
## Integration with Documentation Tools
|
|
|
|
### Markdown File Generation
|
|
|
|
```csharp
|
|
public class QueryDocumentationGenerator
|
|
{
|
|
public void GenerateDocumentation(string outputPath)
|
|
{
|
|
var sb = new StringBuilder();
|
|
sb.AppendLine("# Database Queries Documentation");
|
|
sb.AppendLine();
|
|
|
|
var queries = GetAllQueries(); // Your query collection
|
|
var generator = new QueryBreakdownGenerator();
|
|
|
|
foreach (var (name, breakdown) in queries)
|
|
{
|
|
sb.AppendLine($"## {name}");
|
|
sb.AppendLine();
|
|
sb.AppendLine($"**SQL:**");
|
|
sb.AppendLine("```sql");
|
|
sb.AppendLine(breakdown.GetSql());
|
|
sb.AppendLine("```");
|
|
sb.AppendLine();
|
|
sb.AppendLine(generator.GenerateMermaidDiagram(breakdown, $"{name} Flow"));
|
|
sb.AppendLine();
|
|
}
|
|
|
|
File.WriteAllText(outputPath, sb.ToString());
|
|
}
|
|
}
|
|
```
|
|
|
|
### GitHub Pages / Wikis
|
|
|
|
The generated Mermaid diagrams work seamlessly with:
|
|
- **GitHub** - Renders Mermaid in README.md and wiki pages
|
|
- **GitLab** - Full Mermaid support in markdown
|
|
- **Azure DevOps** - Mermaid support in wiki
|
|
- **Docusaurus** - With mermaid plugin
|
|
- **MkDocs** - With mermaid2 plugin
|
|
|
|
---
|
|
|
|
## Diagram Customization
|
|
|
|
### Flowchart Styles
|
|
|
|
The generators use standard Mermaid syntax. You can customize by modifying the output:
|
|
|
|
```csharp
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, "Styled Query");
|
|
|
|
// Add custom styling
|
|
diagram = diagram.Replace("```mermaid", @"```mermaid
|
|
%%{init: {'theme':'forest'}}%%");
|
|
|
|
// Or add classDefs
|
|
diagram = diagram.Replace("```", @"
|
|
classDef selectClass fill:#bbf,stroke:#333,stroke-width:2px
|
|
classDef whereClass fill:#fbf,stroke:#333,stroke-width:2px
|
|
```");
|
|
```
|
|
|
|
### Sequence Diagram Themes
|
|
|
|
```csharp
|
|
string sequence = stmtGenerator.GenerateSequenceDiagram(breakdown, "Execution");
|
|
|
|
// Add theme
|
|
sequence = sequence.Replace("sequenceDiagram", @"%%{init: {'theme':'dark'}}%%
|
|
sequenceDiagram");
|
|
```
|
|
|
|
---
|
|
|
|
## Common Use Cases
|
|
|
|
### 1. API Documentation
|
|
|
|
```csharp
|
|
/// <summary>
|
|
/// Gets active users ordered by name.
|
|
/// </summary>
|
|
/// <remarks>
|
|
/// Query Details:
|
|
/// <code>
|
|
/// var generator = new QueryBreakdownGenerator();
|
|
/// var breakdown = new QueryBreakdown("SELECT * FROM users WHERE is_active = 1");
|
|
/// Console.WriteLine(generator.GenerateMermaidDiagram(breakdown, "Active Users"));
|
|
/// </code>
|
|
/// </remarks>
|
|
public async Task<List<User>> GetActiveUsers()
|
|
{
|
|
// Implementation
|
|
}
|
|
```
|
|
|
|
### 2. Code Review Documentation
|
|
|
|
```csharp
|
|
// Generate before/after diagrams for query optimization
|
|
var beforeBreakdown = new QueryBreakdown(originalQuery);
|
|
var afterBreakdown = new QueryBreakdown(optimizedQuery);
|
|
|
|
var generator = new QueryBreakdownGenerator();
|
|
|
|
File.WriteAllText("query-comparison.md", $@"
|
|
# Query Optimization Results
|
|
|
|
## Before
|
|
{generator.GenerateMermaidDiagram(beforeBreakdown, "Original Query")}
|
|
|
|
## After
|
|
{generator.GenerateMermaidDiagram(afterBreakdown, "Optimized Query")}
|
|
|
|
## Improvements
|
|
- Reduced number of JOINs
|
|
- Added index on filtered column
|
|
- Removed SELECT *
|
|
");
|
|
```
|
|
|
|
### 3. Testing Documentation
|
|
|
|
```csharp
|
|
[Test]
|
|
public void ComplexQuery_GeneratesDiagram()
|
|
{
|
|
var breakdown = BuildComplexQuery();
|
|
var generator = new QueryBreakdownGenerator();
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown);
|
|
|
|
// Save diagram for test documentation
|
|
TestContext.WriteLine(diagram);
|
|
|
|
// Assert query properties
|
|
Assert.That(breakdown.WhereClause, Is.Not.Null);
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## Best Practices
|
|
|
|
### 1. Use Descriptive Titles
|
|
|
|
```csharp
|
|
// Good: Descriptive title
|
|
generator.GenerateMermaidDiagram(breakdown, "Active Users by Department");
|
|
|
|
// Avoid: Generic title
|
|
generator.GenerateMermaidDiagram(breakdown, "Query 1");
|
|
```
|
|
|
|
### 2. Generate Diagrams for Complex Queries Only
|
|
|
|
```csharp
|
|
// Generate diagrams for queries with multiple clauses
|
|
if (breakdown.GetClauses().Count() > 3)
|
|
{
|
|
string diagram = generator.GenerateMermaidDiagram(breakdown, queryName);
|
|
SaveDiagram(diagram);
|
|
}
|
|
```
|
|
|
|
### 3. Include SQL Alongside Diagrams
|
|
|
|
```markdown
|
|
## User Query
|
|
|
|
**SQL:**
|
|
```sql
|
|
SELECT id, name, email
|
|
FROM users
|
|
WHERE age > 21
|
|
ORDER BY name
|
|
```
|
|
|
|
**Flow:**
|
|
[Mermaid diagram here]
|
|
```
|
|
|
|
---
|
|
|
|
## Troubleshooting
|
|
|
|
### Diagram Not Rendering
|
|
|
|
**Problem**: Mermaid diagram shows as plain text
|
|
|
|
**Solution**: Ensure your markdown viewer supports Mermaid:
|
|
- GitHub: Native support ✅
|
|
- VS Code: Install "Markdown Preview Mermaid Support" extension
|
|
- Local rendering: Use `mermaid-cli` or online editors
|
|
|
|
### Diagram Too Complex
|
|
|
|
**Problem**: Large queries create cluttered diagrams
|
|
|
|
**Solution**: Break into smaller sections or use collection generator:
|
|
|
|
```csharp
|
|
// Instead of one large diagram, generate multiple focused diagrams
|
|
var generator = new QueryBreakdownGenerator();
|
|
|
|
// Main query flow
|
|
string mainFlow = generator.GenerateMermaidDiagram(mainQuery, "Main Query");
|
|
|
|
// CTE flows separately
|
|
foreach (var cte in mainQuery.WithClauses)
|
|
{
|
|
string cteFlow = generator.GenerateMermaidDiagram(cte.Value, $"CTE: {cte.Key}");
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## API Reference
|
|
|
|
### Generator Classes by Dialect
|
|
|
|
| Namespace | Generator Classes |
|
|
|-----------|------------------|
|
|
| `Strata.SqlTools.Markdown.SqlServer` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |
|
|
| `Strata.SqlTools.Markdown.PostgreSql` | `QueryBreakdownGenerator`, `SqlStatementGenerator`, `QueryBreakdownCollectionGenerator` |
|
|
| `Strata.SqlTools.Markdown.Snowflake` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |
|
|
| `Strata.SqlTools.Markdown.LinqToSql` | `QueryBreakdownGenerator`, `SqlStatementGenerator` |
|
|
|
|
### Common Methods
|
|
|
|
All `QueryBreakdownGenerator` classes provide:
|
|
|
|
```csharp
|
|
string GenerateMermaidDiagram(breakdown, title?) // Main flowchart diagram
|
|
```
|
|
|
|
All `SqlStatementGenerator` classes provide:
|
|
|
|
```csharp
|
|
string GenerateSequenceDiagram(breakdown, title?) // Execution sequence
|
|
string GenerateEntityRelationshipDiagram(tables/breakdown, title?) // ER diagram
|
|
```
|
|
|
|
---
|
|
|
|
## Related Documentation
|
|
|
|
- [SqlUtilities.SqlServer.md](SqlUtilities.SqlServer.md) - SQL Server query breakdown
|
|
- [SqlUtilities.PostgreSql.md](SqlUtilities.PostgreSql.md) - PostgreSQL query breakdown
|
|
- [SqlUtilities.Snowflake.md](SqlUtilities.Snowflake.md) - Snowflake query breakdown
|
|
- [SqlUtilities.LinqToSql.md](SqlUtilities.LinqToSql.md) - LINQ query analysis
|
|
|
|
---
|
|
|
|
**Version**: 1.0.0
|
|
**Last Updated**: February 2026
|
|
**Package**: Strata.SqlTools.Markdown
|