# brynjmsdlnn/addstoredprocedure architecture

> Architecture diagram of the GitHub repository brynjmsdlnn/addstoredprocedure, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-03-12). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/brynjmsdlnn/addstoredprocedure
- Repository: https://github.com/brynjmsdlnn/addstoredprocedure

## Overview

##### What this project is (type + purpose)
This is **not an application**; it’s a **NuGet Package Manager Console (PMC) helper package** for Visual Studio / NuGet (classic `packages.config` workflow) that installs a PowerShell command: **`Add-StoredProcedure`**.

Purpose:
- Scaffold **timestamped** `.sql` stored procedure files into a target “Infrastructure” project.
- Auto-detect the Infrastructure project by presence of a `Migrations` folder.
- Automatically **edits the target `.csproj`** to include each generated `.sql` as an **`EmbeddedResource`** (grouped by schema/table, with a comment header).
- Runs inside Visual Studio’s PMC with support for `-WhatIf`, author detection, BOM-safe UTF-8 output, etc.

##### What the file tree implies about architecture
Top-level:
- `AddStoredProcedure.nuspec`: NuGet packaging manifest; defines package metadata and what goes into `lib/` and `tools/`.
- `tools/`
- `Add-StoredProcedure.ps1`: the actual command implementation.
- `init.ps1`: NuGet “install-time” script that loads/registers the command into the PMC session.
- `lib/net48/_._` and `lib/net481/_._`: compatibility shims so NuGet sees the package as compatible with those frameworks (there is no compiled assembly here).
- No `src/`, no compiled code, no tests—this is **script-only** shipped as a NuGet package.

Key architectural point: **NuGet/Visual Studio PMC execution model** is the runtime. The “host” is Visual Studio + NuGet Package Manager Console.

##### What the README adds (runtime behavior & interactions)
- Input: `Add-StoredProcedure -Name -Schema -Table [-Author] [-Anon] [-WhatIf]`
- Discovery: “Infrastructure project” detection by scanning solution projects for `Migrations` folder
- Output artifacts:
- File created at: `Database\StoredProcedures\<schema>\<table>\<timestamp>_<name>.sql`
- `.csproj` updated: `<EmbeddedResource Include="...sql" />` plus grouping comment
- Environment integrations:
- Author detection chain: GitHub Actions → Azure DevOps → Git config → solution name fallback
- Uses Visual Studio DTE/PMC context (implied by common PMC scripting patterns and the troubleshooting note about `$DTE`)

##### NuGet Package: AddStoredProcedure
- Contains: `tools/init.ps1`, `tools/Add-StoredProcedure.ps1`, `lib/*/_._`, `.nuspec`

##### Host Environment
- **Visual Studio**
- **NuGet Package Manager Console (PowerShell runspace)**

##### Command Module
- `Add-StoredProcedure` PowerShell function/script (core logic)

##### Target Solution/Projects
- **Infrastructure project** (auto-detected by presence of `Migrations/`)
- **Other projects** (only relevant for discovery step)

##### Files / Artifacts in Infrastructure Project
- `Database/StoredProcedures/.../*.sql`
- `*.csproj` file (MSBuild XML) being modified

##### External/Environment Metadata Sources (for author detection)
- GitHub Actions env vars
- Azure DevOps env vars
- Local Git config
- Solution name fallback

- Developer → Visual Studio PMC: `Install-Package AddStoredProcedure ...`
- NuGet → executes `tools/init.ps1`
- `init.ps1` → dot-sources / registers `Add-StoredProcedure.ps1` into the PMC session

- Developer → PMC: runs `Add-StoredProcedure -Name ...`
- `Add-StoredProcedure.ps1` → queries Visual Studio solution/projects context (DTE/PMC variables)
- `Add-StoredProcedure.ps1` → scans projects → finds project containing `Migrations/` (Infrastructure)
- `Add-StoredProcedure.ps1` → reads env vars / git config → resolves `Author`
- `Add-StoredProcedure.ps1` → writes `.sql` file to target folder path
- `Add-StoredProcedure.ps1` → edits `.csproj` to add `<EmbeddedResource Include="...">`
- (Conditional branch) If `-WhatIf`: actions are simulated; no file system / csproj writes

##### Architectural patterns / principles
- **Tooling automation** pattern: “scripted scaffolding” integrated via NuGet `tools/` scripts.
- **Convention-over-configuration**:
- Infrastructure project inferred by `Migrations/` presence
- Output path conventions by schema/table
- Timestamp naming convention for ordering
- **Idempotent-ish / safe operations**: `-WhatIf`, BOM-safe UTF-8 output, comment-grouping in `.csproj`
- **Host-coupled runtime**: depends on Visual Studio PMC / DTE context (not a cross-platform CLI).

##### Relevant technologies explicitly (labels)
- PowerShell (PMC)
- Visual Studio + NuGet (packages.config-era tooling semantics)
- MSBuild `.csproj` editing
- .NET Framework compatibility (`net481` target requirement, though no assemblies are shipped)

##### Runtime/Execution view (what happens when the command is run)

- Parameter parsing/validation (`-Name`, `-Schema`, `-Table`, `-Author`, `-Anon`, `-WhatIf`)
- Infrastructure project discovery (scan solution projects for `Migrations/`)
- Path computation (schema/table + timestamp prefix)
- SQL template renderer (header with author/date/object)
- Encoding writer (UTF-8, BOM-safe)
- `.csproj` updater (inserts comment group + `EmbeddedResource Include=...`)
- Dry-run executor (`-WhatIf` branch)

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% =========================
    %% Distribution / Packaging
    %% =========================
    Dev["Developer"]:::actor

    subgraph "Visual Studio Host Environment"
        VS["Visual Studio IDE"]:::host
        PMC["NuGet Package Manager Console (PowerShell runspace)"]:::host
        DTE["VS Automation Context ($DTE / Solution Projects)"]:::host
        VS --> PMC
        PMC --> DTE
    end

    subgraph "NuGet Package: AddStoredProcedure (nupkg)"
        Nuspec["AddStoredProcedure.nuspec\n(package manifest)"]:::pkg
        Readme["README.md\n(usage & runtime behavior)"]:::doc
        subgraph "tools/"
            Init["init.ps1\n(NuGet install hook)"]:::script
            Cmd["Add-StoredProcedure.ps1\n(PMC command implementation)"]:::script
        end
        subgraph "lib/"
            Shim48["net48/_._\n(compatibility shim)"]:::shim
            Shim481["net481/_._\n(compatibility shim)"]:::shim
        end
        Nuspec --> Init
        Nuspec --> Cmd
        Nuspec --> Shim48
        Nuspec --> Shim481
        Readme --> Cmd
    end

    %% Install-time flow (dashed)
    Dev -.->|"Install-Package AddStoredProcedure"| PMC
    PMC -.->|"NuGet install pipeline runs"| Init
    Init -.->|"dot-source/register command"| Cmd

    %% =========================
    %% Runtime / Execution
    %% =========================
    Dev -->|"Run Add-StoredProcedure -Name -Schema -Table (-WhatIf)"| PMC
    PMC -->|"invokes"| Cmd
    Cmd -->|"queries"| DTE

    subgraph "Add-StoredProcedure.ps1 - Internal Steps"
        Params["Parameter parsing & validation\n(-Name,-Schema,-Table,-Author,-Anon,-WhatIf)"]:::logic
        Discover["Infrastructure project discovery\n(scan projects for 'Migrations/' folder)"]:::logic
        Author["Author resolution\n(GHA env → ADO env → git config → solution name)"]:::logic
        Pathing["Path & name computation\nDatabase/StoredProcedures/<schema>/<table>/<timestamp>_<name>.sql"]:::logic
        Render["SQL template renderer\n(header: author/date/object)"]:::logic
        WriteSql["Write .sql file\n(UTF-8, BOM-safe)"]:::io
        UpdateProj["Edit .csproj (MSBuild XML)\nadd EmbeddedResource + grouping comment"]:::io
        WhatIf["-WhatIf mode\n(simulate; no writes)"]:::branch

        Params --> Discover --> Author --> Pathing --> Render
        Render --> WriteSql
        Render --> UpdateProj
        Params --> WhatIf
        WhatIf -.->|"skips filesystem/csproj writes"| WriteSql
        WhatIf -.->|"skips filesystem/csproj writes"| UpdateProj
    end

    Cmd --> Params

    subgraph "Target Solution"
        Projects["Solution Projects"]:::host
        Infra["Infrastructure project\n(contains 'Migrations/' folder)"]:::target
        Others["Other projects\n(ignored after discovery)"]:::target
        Projects --> Infra
        Projects --> Others
    end

    DTE -->|"enumerate"| Projects
    Discover -->|"selects"| Infra

    subgraph "Infrastructure Project Artifacts"
        Migrations["Migrations/\n(convention signal)"]:::folder
        FS["File System\nDatabase/StoredProcedures/..."]:::folder
        SqlFile[("Timestamped .sql stored procedure file")]:::artifact
        Csproj[("*.csproj (MSBuild project file)")]:::artifact
        Migrations --> Infra
        Infra --> FS
        FS --> SqlFile
        Infra --> Csproj
    end

    WriteSql -->|"creates/updates"| SqlFile
    UpdateProj -->|"inserts EmbeddedResource Include='...sql'"| Csproj

    %% External metadata sources (author detection)
    subgraph "External Metadata Sources (Author Detection Inputs)"
        GHA["GitHub Actions env vars"]:::external
        ADO["Azure DevOps env vars"]:::external
        GIT["Local git config"]:::external
        SOL["Solution name fallback"]:::external
    end

    GHA -->|"author hint"| Author
    ADO -->|"author hint"| Author
    GIT -->|"author hint"| Author
    SOL -->|"author hint"| Author

    %% =========================
    %% Click Events (from mapping)
    %% =========================
    click Nuspec "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/AddStoredProcedure.nuspec"
    click Readme "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/README.md"
    click Init "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/tools/init.ps1"
    click Cmd "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/tools/Add-StoredProcedure.ps1"
    click Shim48 "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/lib/net48/_._"
    click Shim481 "https://github.com/brynjmsdlnn/addstoredprocedure/blob/master/lib/net481/_._"

    %% =========================
    %% Styles
    %% =========================
    classDef actor fill:#ffffff,stroke:#111827,stroke-width:2px,color:#111827
    classDef host fill:#dbeafe,stroke:#1d4ed8,stroke-width:2px,color:#0b1b3a
    classDef pkg fill:#ede9fe,stroke:#6d28d9,stroke-width:2px,color:#1f123a
    classDef doc fill:#f5f3ff,stroke:#7c3aed,stroke-width:2px,color:#2a145a
    classDef script fill:#dcfce7,stroke:#16a34a,stroke-width:2px,color:#052e16
    classDef shim fill:#e5e7eb,stroke:#6b7280,stroke-width:2px,color:#111827
    classDef logic fill:#bbf7d0,stroke:#15803d,stroke-width:2px,color:#052e16
    classDef io fill:#fed7aa,stroke:#ea580c,stroke-width:2px,color:#3b1a00
    classDef artifact fill:#ffedd5,stroke:#c2410c,stroke-width:2px,color:#3b1a00
    classDef external fill:#e5e7eb,stroke:#4b5563,stroke-width:2px,color:#111827
    classDef target fill:#cffafe,stroke:#0891b2,stroke-width:2px,color:#083344
    classDef folder fill:#fde68a,stroke:#b45309,stroke-width:2px,color:#3b2500
    classDef branch fill:#fee2e2,stroke:#dc2626,stroke-width:2px,color:#3a0a0a
```
