# sultanal-jrboa/schema_to_ddl architecture

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

- Interactive diagram: https://gitdiagram.com/sultanal-jrboa/schema_to_ddl
- Repository: https://github.com/sultanal-jrboa/schema_to_ddl

## Overview

##### Project Type and Purpose
- This is a web-based tool built (using Flask, as indicated in the README) for generating DDL (Data Definition Language) scripts from schema metadata (provided via Excel files).
- The purpose is to support multiple database systems (PostgreSQL, MySQL, SQL Server, Oracle) by converting user-uploaded schema metadata into executable SQL scripts.

##### File Structure
- Although the file tree you provided shows files like README.md, main.py, and directories for static files and templates, the README further clarifies the presence of key directories such as:
- app.py (the main Flask application, even if your tree shows main.py, treat it as the core application file)
- /templates and /static which support the frontend
- /uploads and /outputs are essential directories for file storage (user uploads and generated DDL outputs) – these may not appear in the file tree you saw but are described inside the README.
- The directories imply a simple MVC-like separation where:
- Controllers (Flask routes in the main app file)
- Views (HTML templates in the templates folder and static assets)
- Data processing (handling file upload, validation, conversion, and DDL generation)

##### Important Architectural Components
- Frontend: The user interface (HTML/CSS, contained in templates, with assets in the static directory).
- Backend: The Flask application that receives uploaded files, processes them, and returns the DDL scripts.
- File Handling: Components handling user uploads (Excel files) and the storage of generated DDL scripts.
- DDL Generation Engine: A module or set of functions responsible for reading the Excel file, validating the schema/data types, and then formatting the DDL for different database types.
- Deployment: Although the system is deployed on Render, the deployment components (environment variables, GitHub integration) aren’t part of the runtime architecture but are important for context.

##### Relationships and Interactions
- The client (browser) interacts with the web-based interface.
- The frontend sends file uploads to the backend via HTTP requests to Flask routes.
- The backend processes the file through internal components:
- It temporarily stores the uploaded file (in the uploads directory)
- Processes the metadata using the DDL generation engine
- Writes the SQL output into the outputs directory.
- Finally, the application allows the user to download the generated script.

##### Architectural Patterns and Key Technologies
- The Model-View-Controller (MVC) pattern is applied implicitly, with clear separation between the presentation (templates, static), business logic (Flask application and processing functions), and the data/file handling.
- Technologies include Python (Flask), standard HTML/CSS for the frontend, and libraries (potentially using openpyxl or pandas for Excel processing as specified in requirements.txt).
- Multi-database logic implies distinct handling paths or configurations for PostgreSQL, MySQL, SQL Server, and Oracle.

## Diagram (Mermaid)

```mermaid
flowchart TD
    Client("Web Browser"):::client

    subgraph "Presentation Layer"
        FE1("HTML Templates"):::frontend
        FE2("Static Assets"):::frontend
    end

    subgraph "Backend Layer"
        BE1("Web Server (Flask App)"):::backend
        BE2("DDL Generation Engine\n(Multi-DB Support)"):::backend
    end

    subgraph "Storage Layer"
        ST1("Uploads Directory"):::storage
        ST2("Outputs Directory"):::storage
    end

    subgraph "Documentation"
        DOC1("README.md"):::documentation
        DOC2("Dependencies (requirements.txt)"):::documentation
    end

    DEP("Deployment (Render)"):::external

    Client -->|"UploadExcel"| BE1
    BE1 -->|"StoreUploadedFile"| ST1
    BE1 -->|"ProcessSchema"| BE2
    BE2 -->|"StoreGeneratedDDL"| ST2
    BE1 -->|"RenderPage"| FE1
    BE1 -->|"LoadAssets"| FE2
    BE1 -->|"ReturnScript"| Client
    DOC1 -->|"configures"| BE1
    DOC2 -->|"dependencies"| BE1
    BE1 -->|"deployedOn"| DEP

    click BE1 "https://github.com/sultanal-jrboa/schema_to_ddl/blob/main/main.py"
    click FE1 "https://github.com/sultanal-jrboa/schema_to_ddl/blob/main/templates/index.html"
    click FE2 "https://github.com/sultanal-jrboa/schema_to_ddl/tree/main/static/"
    click DOC1 "https://github.com/sultanal-jrboa/schema_to_ddl/blob/main/README.md"
    click DOC2 "https://github.com/sultanal-jrboa/schema_to_ddl/blob/main/requirements.txt"

    classDef client fill:#A2D9CE,stroke:#333,stroke-width:2px,color:black;
    classDef frontend fill:#AED6F1,stroke:#333,stroke-width:2px,color:black;
    classDef backend fill:#FAD7A0,stroke:#333,stroke-width:2px,color:black;
    classDef storage fill:#F9E79F,stroke:#333,stroke-width:2px,color:black;
    classDef documentation fill:#D5F5E3,stroke:#333,stroke-width:2px,color:black;
    classDef external fill:#F5B7B1,stroke:#333,stroke-width:2px,color:black;
```
