# arulkn/mssql_sp_browser architecture

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

- Interactive diagram: https://gitdiagram.com/arulkn/mssql_sp_browser
- Repository: https://github.com/arulkn/mssql_sp_browser

## Overview

##### Project Type and Purpose
- This is a Python Flask web application created to browse, analyze, and manage stored procedures in a SQL Server database. It offers a user-friendly interface for filtering and highlighting keywords in stored procedure definitions, with an added functionality to track completed procedures via a CSV file.

##### Analyzing the File Structure
- The top-level files include key modules such as app.py, routes.py, database.py, and config.py. These files represent the main application logic and configuration.
- The presence of db_config.json indicates external configuration for connecting to the SQL Server.
- The static and templates directories house the frontend assets (CSS, JavaScript) and HTML templates, respectively.
- A completed_procedures.csv file is used to track which stored procedures have been marked as completed.
- Additional helper modules like utils.py suggest utility functions that support the main application tasks.

a. Main Components:
- Web Client (Browser): This is where users interact with the application. The browser loads the HTML from the templates directory, styled by CSS (static/css) and empowered by JavaScript (static/js).
- Flask Application (Backend):
- app.py: Acts as the entry point.
- routes.py: Manages URL routing and request handling.
- config.py: Contains configuration details for the web server.
- Database Access Layer:
- database.py: Contains database connection logic using PyODBC to interact with the SQL Server.
- db_config.json: Provides the necessary credentials and server configuration.
- Data Persistence:
- completed_procedures.csv: Stores information about which stored procedures have been marked as completed.
- Utility Services:
- utils.py: Helper functions and possibly additional processing tasks.

b. Component Interactions:
- The User interacts with the Web Client (UI).
- UI requests are sent to the Flask Application, where routes (in routes.py) route the requests to the proper handlers in app.py.
- The Flask Application makes calls to the Database Access Layer (database.py) to fetch stored procedures and their definitions from the SQL Server.
- The application also reads from and writes to the completed_procedures.csv file to track user actions.
- JavaScript (in static/js/script.js) enhances the UI dynamically (e.g., highlighting keywords, handling client-side actions).

c. Architectural Patterns & Principles:
- Separation of Concerns: Clear separation between the UI (templates and static assets), application logic (app.py, routes.py, utils.py), and data access (database.py).
- MVC-like Approach: Even if loosely coupled, the routes act as controllers, the templates as views, and Python modules simulating the model layer (interaction with the SQL Server and CSV persistence).
- External Configuration: db_config.json allows the database connection details to be managed outside of the main code.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Frontend Layer
    subgraph "Frontend Layer"
        UI["Web Client (Browser)"]:::client
        subgraph "Static Assets"
            T["Templates (index.html)"]:::clientUI
            CSS["CSS (styles.css)"]:::clientUI
            JS["JS (script.js)"]:::clientUI
        end
    end

    %% Backend Layer
    subgraph "Backend Layer"
        A["Entry Point (app.py)"]:::backend
        R["URL Routing (routes.py)"]:::backend
        Cfg["Configuration (config.py)"]:::backend
    end

    %% Utility Services
    U["Utility Services (utils.py)"]:::util

    %% Data Layer
    subgraph "Data Layer"
        DB["Database Connection (database.py)"]:::database
        DBC["DB Config (db_config.json)"]:::database
        CSV["Completion Tracker (completed_procedures.csv)"]:::datastore
    end

    %% Connections: User Interface to Backend
    UI -->|"HTTP_request"| R
    %% Backend internal communication
    R -->|"calls_handler"| A
    A -->|"invokes"| U
    A -->|"queries"| DB
    DB -->|"uses"| DBC
    A -->|"reads/writes"| CSV
    %% Backend serving static assets
    A -->|"serves"| T
    A -->|"serves"| CSS
    A -->|"serves"| JS

    %% Click Events
    click T "https://github.com/arulkn/mssql_sp_browser/blob/main/templates/index.html"
    click CSS "https://github.com/arulkn/mssql_sp_browser/blob/main/static/css/styles.css"
    click JS "https://github.com/arulkn/mssql_sp_browser/blob/main/static/js/script.js"
    click A "https://github.com/arulkn/mssql_sp_browser/blob/main/app.py"
    click R "https://github.com/arulkn/mssql_sp_browser/blob/main/routes.py"
    click Cfg "https://github.com/arulkn/mssql_sp_browser/blob/main/config.py"
    click DB "https://github.com/arulkn/mssql_sp_browser/blob/main/database.py"
    click DBC "https://github.com/arulkn/mssql_sp_browser/blob/main/db_config.json"
    click CSV "https://github.com/arulkn/mssql_sp_browser/blob/main/completed_procedures.csv"
    click U "https://github.com/arulkn/mssql_sp_browser/blob/main/utils.py"

    %% Styles
    classDef client fill:#ccf2ff,stroke:#333,stroke-width:2px;
    classDef clientUI fill:#d1e7dd,stroke:#333,stroke-width:2px;
    classDef backend fill:#ffcccc,stroke:#900,stroke-width:2px;
    classDef util fill:#e6ccff,stroke:#600,stroke-width:2px;
    classDef database fill:#ccffcc,stroke:#060,stroke-width:2px;
    classDef datastore fill:#ffe6cc,stroke:#f80,stroke-width:2px;
```
