# flyingfeather/dea-sql architecture

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

- Interactive diagram: https://gitdiagram.com/flyingfeather/dea-sql
- Repository: https://github.com/flyingfeather/dea-sql

## Overview

##### Project type and purpose
- This is an academic/research codebase implementing DEA-SQL: a workflow-oriented, LLM-based Text-to-SQL system. It reads Spider dataset files, applies a five-stage decomposition and few-shot retrieval pipeline, queries an LLM (OpenAI/Azure), post-processes and corrects generated SQL, then evaluates against gold queries.

##### High-level architecture implied by file tree and README
- Orchestrator: main.py drives the pipeline, accepting CLI args via argsparser.py.
- Common utilities: common/ holds shared config (common/config/static_config.py), LLM wrappers (common/wrapper.py), CSV logging, enumeration helpers, etc.
- Data ingestion & preprocessing: data_preprocess.py, filter_characters.py, get_ner.py.
- Few-shot retrieval: fewshot/ with methods to build and index embeddings, dispatcher in fewshot/processor.py.
- Prompt construction: prompt/ modules assemble decomposition-aware prompts (get_prompt.py, get_sql_prompt_for_fewshot.py, questions_label_prompt.py).
- LLM invocation: llm/chat.py and llm/chatgpt.py wrap API calls, embedding.py handles embedding lookups.
- SQL generation & correction: gen_sql.py produces raw SQL, correct_sql.py applies heuristic fixes.
- Evaluation: evaluation/ contains both project-native and external Spider test suite evaluation scripts.
- Supporting scripts: single_eval.py for standalone evaluation; nltk_downloader.py to fetch linguistic assets; hardness_eval.py to analyze query difficulty.
- Outputs: outputs/ stores prediction files per dataset, organized by baseline.
- External dependencies: Spider dataset files under data/spider; Azure/OpenAI endpoints.

##### Main components & interactions
a. Data Layer
- Input: JSON dataset + table schemas (data/spider/…)
- Output: processed examples fed to few-shot and prompt modules
b. Preprocessing Layer
- data_preprocess.py / filter_characters.py / get_ner.py
- Normalizes questions, tokenizes, extracts named entities
c. Few-Shot Retriever
- Index training examples (fewshot/index_train_*)
- Compute/query embeddings via llm/embedding.py
- Return top-k examples based on mode (random, ques_tim, masked_ques_sim)
d. Prompt Builder
- Takes decomposed steps (from decomposition logic in argsparser.py or prompt modules)
- Inserts few-shot examples, schema info, question into a templated prompt
e. LLM Executor
- llm/chat.py / llm/chatgpt.py wrap HTTP calls to OpenAI / Azure
- Handles rate limits, retries, streaming if enabled
f. Post-processing & Correction
- gen_sql.py parses LLM raw output into SQL AST or text
- correct_sql.py applies static fixes (e.g., keyword normalization)
g. Evaluation
- Native: single_eval.py compares predictions to gold sets
- Standard: evaluation/test-suite-sql-eval uses established Spider evaluator

##### Architectural patterns and principles
- Modular pipeline: Each stage (preprocess → retrieve → prompt → infer → postprocess → evaluate) is a separate Python module/script.
- Wrapper pattern: common/wrapper.py and llm/* abstract external LLM API details.
- Configuration centralization: static parameters (API keys, endpoints) live in common/config/static_config.py.
- CLI-driven: main.py + argsparser.py implement a clear, reproducible command-line interface.
- Separation of concerns: evaluation modules isolated from core inference logic.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Data Layer
    subgraph "Data Layer"
        direction TB
        DataJSON["Spider Dataset JSON"]:::datastore
        SchemaJSON["Table Schema JSON"]:::datastore
    end

    %% Preprocessing Layer
    subgraph "Preprocessing" 
        direction TB
        DataPre["data_preprocess.py"]:::script
        FilterChar["filter_characters.py"]:::script
        GetNER["get_ner.py"]:::script
    end

    %% Few-Shot Retriever
    subgraph "Few-Shot Retriever"
        direction TB
        Processor["fewshot/processor.py"]:::script
        AutoSel["fewshot/auto_selection.py"]:::script
        IndexV1["Index Merge V1"]:::datastore
        IndexV5["Index Merge V5"]:::datastore
    end

    %% Prompt Builder
    subgraph "Prompt Builder"
        direction TB
        GetPrompt["prompt/get_prompt.py"]:::script
        GetSQLPrompt["prompt/get_sql_prompt_for_fewshot.py"]:::script
        QLabel["prompt/questions_label_prompt.py"]:::script
    end

    %% LLM Invocation
    subgraph "LLM Invocation"
        direction TB
        Chat["llm/chat.py"]:::script
        ChatGPT["llm/chatgpt.py"]:::script
        Embedding["llm/embedding.py"]:::script
        LLMService["OpenAI/Azure LLM Service"]:::external
    end

    %% Post-Processing & Correction
    subgraph "Post-Processing"
        direction TB
        GenSQL["gen_sql.py"]:::script
        CorrectSQL["correct_sql.py"]:::script
    end

    %% Evaluation
    subgraph "Evaluation"
        direction TB
        SingleEval["single_eval.py"]:::script
        NativeEval["evaluation/src/evaluation.py"]:::script
        StdEval["evaluation/test-suite-sql-eval"]:::datastore
    end

    %% Orchestrator
    subgraph "Orchestrator"
        direction TB
        Main["main.py"]:::script
        ArgsParser["argsparser.py"]:::script
    end

    %% Common Utilities
    subgraph "Common Utilities"
        direction TB
        StaticConfig["common/config/static_config.py"]:::script
        Wrapper["common/wrapper.py"]:::script
        CsvSample["common/csv_to_sample.py"]:::script
        LogCSV["common/log_to_csv.py"]:::script
        EnumSQL["common/enumeration/sql.py"]:::script
    end

    %% Supporting & Analysis Scripts
    subgraph "Supporting Scripts"
        direction TB
        NLTK["nltk_downloader.py"]:::script
        Hardness["hardness_eval.py"]:::script
    end

    %% Outputs
    subgraph "Outputs"
        direction TB
        OutDEA["outputs/spider/dea-sql.txt"]:::output
        OutBase["outputs/spider/baselines-sql/"]:::output
    end

    %% Flows
    DataJSON -->|raw JSON| DataPre
    SchemaJSON -->|schema info| DataPre
    DataPre -->|clean questions| FilterChar
    FilterChar --> GetNER
    GetNER --> Processor
    Processor --> AutoSel
    AutoSel -->|top-k examples| GetPrompt
    GetPrompt --> GetSQLPrompt
    GetSQLPrompt --> QLabel
    QLabel --> Chat
    Embedding --> Processor
    Chat --> ChatGPT
    ChatGPT -->|LLM request| LLMService
    LLMService -->|response| ChatGPT
    ChatGPT --> GenSQL
    GenSQL --> CorrectSQL
    CorrectSQL --> SingleEval
    CorrectSQL --> NativeEval
    NativeEval --> StdEval
    CorrectSQL --> OutDEA
    NativeEval --> OutBase
    Main --> ArgsParser
    Main --> StaticConfig
    Main --> Wrapper
    Main --> CsvSample
    Main --> LogCSV
    Main --> EnumSQL
    Main --> DataPre
    Main --> Processor
    Main --> GetPrompt
    Main --> Chat
    Main --> GenSQL
    Main --> SingleEval
    NLTK --> Main
    Hardness --> Main

    %% Click Events
    click Main "https://github.com/flyingfeather/dea-sql/blob/main/main.py"
    click ArgsParser "https://github.com/flyingfeather/dea-sql/blob/main/argsparser.py"
    click StaticConfig "https://github.com/flyingfeather/dea-sql/blob/main/common/config/static_config.py"
    click Wrapper "https://github.com/flyingfeather/dea-sql/blob/main/common/wrapper.py"
    click CsvSample "https://github.com/flyingfeather/dea-sql/blob/main/common/csv_to_sample.py"
    click LogCSV "https://github.com/flyingfeather/dea-sql/blob/main/common/log_to_csv.py"
    click EnumSQL "https://github.com/flyingfeather/dea-sql/blob/main/common/enumeration/sql.py"
    click DataPre "https://github.com/flyingfeather/dea-sql/blob/main/data_preprocess.py"
    click FilterChar "https://github.com/flyingfeather/dea-sql/blob/main/filter_characters.py"
    click GetNER "https://github.com/flyingfeather/dea-sql/blob/main/get_ner.py"
    click Processor "https://github.com/flyingfeather/dea-sql/blob/main/fewshot/processor.py"
    click AutoSel "https://github.com/flyingfeather/dea-sql/blob/main/fewshot/auto_selection.py"
    click IndexV1 "https://github.com/flyingfeather/dea-sql/tree/main/fewshot/index_train_merge_v1/"
    click IndexV5 "https://github.com/flyingfeather/dea-sql/tree/main/fewshot/index_train_merge_v5/"
    click GetPrompt "https://github.com/flyingfeather/dea-sql/blob/main/prompt/get_prompt.py"
    click GetSQLPrompt "https://github.com/flyingfeather/dea-sql/blob/main/prompt/get_sql_prompt_for_fewshot.py"
    click QLabel "https://github.com/flyingfeather/dea-sql/blob/main/prompt/questions_label_prompt.py"
    click Chat "https://github.com/flyingfeather/dea-sql/blob/main/llm/chat.py"
    click ChatGPT "https://github.com/flyingfeather/dea-sql/blob/main/llm/chatgpt.py"
    click Embedding "https://github.com/flyingfeather/dea-sql/blob/main/llm/embedding.py"
    click GenSQL "https://github.com/flyingfeather/dea-sql/blob/main/gen_sql.py"
    click CorrectSQL "https://github.com/flyingfeather/dea-sql/blob/main/correct_sql.py"
    click NativeEval "https://github.com/flyingfeather/dea-sql/blob/main/evaluation/src/evaluation.py"
    click StdEval "https://github.com/flyingfeather/dea-sql/tree/main/evaluation/test-suite-sql-eval/"
    click SingleEval "https://github.com/flyingfeather/dea-sql/blob/main/single_eval.py"
    click NLTK "https://github.com/flyingfeather/dea-sql/blob/main/nltk_downloader.py"
    click Hardness "https://github.com/flyingfeather/dea-sql/blob/main/hardness_eval.py"

    %% Styles
    classDef script fill:#D0E8FF,stroke:#0366D6,stroke-width:1px;
    classDef datastore fill:#FFF5BA,stroke:#D4A017,stroke-width:1px;
    classDef external fill:#E8E8E8,stroke:#666666,stroke-width:1px,stroke-dasharray: 5 5;
    classDef output fill:#DFFFE0,stroke:#28A745,stroke-width:1px;
```
