# ruckbreasoning/resdsql architecture

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

- Interactive diagram: https://gitdiagram.com/ruckbreasoning/resdsql
- Repository: https://github.com/ruckbreasoning/resdsql

## Overview

##### Project type and purpose
- RESDSQL is a research‐oriented, two‐stage Text-to-SQL parsing pipeline. It is not a web service or monolithic app, but rather a collection of data preprocessing modules, model training/inference code, SQL conversion utilities, and evaluation scripts. Its goal is to convert a natural-language question into an executable SQL query by (a) linking schema elements then (b) parsing a “skeleton” NatSQL representation before final SQL conversion.

##### File structure
- Top‐level entry points:
- run.py, text2sql.py, evaluate_text2sql_ckpts.py, schema_item_classifier.py, text2sql_data_generator.py, preprocessing.py, original_text2sql_dataset.py, nltk_downloader.py.
- NatSQL submodule:
- JSON datasets (`NatSQLv1_6/*.json`), converter script (`natsql2sql/`), helper scripts (check_and_preprocess.sh, natsql2sql.sh).
- utils/: core utilities for dataset loading, tokenization, model metrics, decoding, etc.
- scripts/: orchestrated shell pipelines for train/ inference/ robustness evaluation across Spider, Dr.Spider, CSpider.
- config.py and requirements.txt declare dependencies (PyTorch, Huggingface Transformers, SpaCy, NLTK).

##### README for additional insights
- Confirms two‐stage design:
- Stage 1: Cross‐encoder schema‐linker (RoBERTa / XLM‐RoBERTa) to predict schema item relevance.
- Stage 2: Skeleton parser (T5 / mT5) to generate NatSQL.
- Post‐processing: NatSQL→SQL conversion via custom parser.
- Uses external datasets/test suites (Spider, Spider-DK, Spider-Syn, Spider-Realistic, Dr.Spider, CSpider) pulled in under third_party/.

1. Data & Config Layer
– Raw datasets (.json, .pkl), database files, tables.json.
– config.py, requirements.txt, environment (conda).
2. Preprocessing
– preprocessing.py, original_text2sql_dataset.py, pattern_generation.py, setence_split.py, table_transform.py, NatSQL/check_and_preprocess.sh.
– Output: cleaned queries + schema representations.
3. Stage 1 – Schema Linking
– schema_item_classifier.py
– Bridge content encoder, classifier_model, classifier_metric (utils/)
– Output: NLQ→{linked_table, linked_column} pairs.
4. Stage 2 – Skeleton Parsing
– text2sql_data_generator.py + text2natsql scripts under scripts/train
– text2sql.py / run.py (loads links + NLQ + schema, fine‐tunes or inference with T5/mT5)
– Output: NatSQL statement.
5. NatSQL → SQL Conversion
– NatSQL/natsql2sql/natsql2sql.py + preprocessing modules under natsql2sql/preprocess
– utils for token/string matching.
– Output: valid SQL.
6. Evaluation & Metrics
– evaluate_text2sql_ckpts.py, scripts/evaluate_robustness, third_party/test_suite
– utils/spider_metric, utils/classifier_metric.
7. Orchestration Scripts
– scripts/train/*
– scripts/inference/*
– scripts/evaluate_robustness/*

- Raw Data → Preprocessing → Prepared Data
- Prepared Data → Schema Linking → Link Annotations
- (Prepared Data + Link Annotations) → Skeleton Parser → NatSQL
- NatSQL → NatSQL Converter → Final SQL
- Final SQL + Ground-truth DB → Evaluation scripts → Metrics
- Orchestration layer invokes each stage in sequence, passing file paths and models.

c. Architectural patterns & design principles:
- Two‐stage modular pipeline (separation of concerns).
- Data‐driven: JSON files as contract between stages.
- Reusable utilities under utils/.
- Script‐based orchestration for reproducibility.

d. Key technologies & frameworks:
- PyTorch / Huggingface Transformers (T5, mT5, RoBERTa).
- SpaCy, NLTK for tokenization / sentence splitting.
- Shell scripts for workflow orchestration.
- JSON/PKL for intermediate data exchange.
- External test suites (Spider, Dr.Spider, CSpider).

##### Elements
- Clear labels for each component (use module/script names).
- Blue for data stores (raw JSON, tables.json, DB files)
- Green for ML components (schema linker, skeleton parser)
- Orange for converters/preprocessors
- Purple for orchestration scripts

##### Final advice
- Be detailed: show sub‐modules (e.g., utils/spider_metric) only if they play a distinct role.
- Core two‐stage pattern: Schema Linking → Skeleton Parsing → Conversion → Evaluation.
- Avoid clutter by folding lower‐level helper scripts into “Preprocessing” or “Utilities” groups.
- Important config points (e.g., choice of model scale: base/large/3B) where relevant.

## Diagram (Mermaid)

```mermaid
flowchart TB
    %% Data & Config Layer
    subgraph "Data & Config Layer"
        direction TB
        Config_py["config.py"]:::config
        Requirements["requirements.txt"]:::config
        NatSQL_Config["NatSQL/config.py"]:::config
        NatSQL_Req["NatSQL/requirements.txt"]:::config
        JSON_Datasets(("NatSQLv1_6/*.json")):::data
        PKL_Dataset(("NatSQL/data/20k.pkl")):::data
        Tables1(("NatSQL/NatSQLv1_6/tables.json")):::data
        Tables2(("NatSQL/NatSQLv1_6/tables_for_natsql.json")):::data
    end

    %% Preprocessing
    subgraph "Preprocessing" 
        direction TB
        Preproc_py["preprocessing.py"]:::preproc
        OrigDataset["original_text2sql_dataset.py"]:::preproc
        CheckPreproc["NatSQL/check_and_preprocess.sh"]:::preproc
        SentenceSplit["NatSQL/setence_split.py"]:::preproc
        PatternGen["NatSQL/pattern_generation.py"]:::preproc
        TableTransform["NatSQL/table_transform.py"]:::preproc
        Punkt["NatSQL/punkt.py"]:::preproc
    end

    %% Stage 1 - Schema Linking
    subgraph "Stage 1 - Schema Linking"
        direction TB
        SchemaClassifier["schema_item_classifier.py"]:::ml
        BridgeEncoder["utils/bridge_content_encoder.py"]:::ml
        ClassifierModel["utils/classifier_model.py"]:::ml
        ClassifierLoss["utils/classifier_loss.py"]:::ml
        ClassifierEval["utils/classifier_metric/evaluator.py"]:::ml
    end

    %% Stage 2 - Skeleton Parsing
    subgraph "Stage 2 - Skeleton Parsing"
        direction TB
        DataGen["text2sql_data_generator.py"]:::ml
        Text2SQL_py["text2sql.py"]:::ml
        Run_py["run.py"]:::ml
        TrainScripts["scripts/train/text2natsql/*"]:::script
        CSPTrain["scripts/train/cspider_text2natsql/*"]:::script
        SchemaTrain["scripts/train/text2sql/*"]:::script
        CSPSchemaTrain["scripts/train/cspider_text2sql/*"]:::script
    end

    %% NatSQL to SQL Conversion
    subgraph "NatSQL → SQL Conversion"
        direction TB
        Conv_SH["NatSQL/natsql2sql.sh"]:::preproc
        Conv_py["NatSQL/natsql2sql/natsql2sql.py"]:::preproc
        Parser_py["NatSQL/natsql2sql/natsql_parser.py"]:::preproc
        PreprocessConv["NatSQL/natsql2sql/preprocess/*.py"]:::preproc
        ProcessSQL["NatSQL/natsql2sql/process_sql.py"]:::preproc
        ConvUtils["NatSQL/natsql2sql/utils.py"]:::preproc
        Conv_SH2["NatSQL/natsql2sql_without_values.sh"]:::preproc
    end

    %% Evaluation & Metrics
    subgraph "Evaluation & Metrics"
        direction TB
        Eval_ckpts["evaluate_text2sql_ckpts.py"]:::script
        EvalRobust["scripts/evaluate_robustness/*"]:::script
        SpiderEval["utils/spider_metric/evaluator.py"]:::ml
        SpiderEM["utils/spider_metric/spider_exact_match.py"]:::ml
        SpiderSuite["utils/spider_metric/spider_test_suite.py"]:::external
        ClassifierEval2["utils/classifier_metric/evaluator.py"]:::ml
    end

    %% Orchestration Scripts
    subgraph "Orchestration Scripts"
        direction TB
        TrainAll["scripts/train/*"]:::script
        InferScripts["scripts/inference/*"]:::script
        EvalScripts["scripts/evaluate_robustness/*"]:::script
    end

    %% External Datasets
    SpiderData(("Third_party test suites")):::external

    %% Flow Arrows
    JSON_Datasets -->|“raw data”| Preproc_py
    Tables1 --> Preproc_py
    Tables2 --> Preproc_py
    PKL_Dataset --> Preproc_py
    Config_py --> Preproc_py
    Requirements --> Preproc_py

    Preproc_py --> OrigDataset
    OrigDataset --> SchemaClassifier
    SchemaClassifier --> BridgeEncoder
    BridgeEncoder --> ClassifierModel
    ClassifierModel --> ClassifierEval
    ClassifierEval --> DataGen
    DataGen --> Text2SQL_py
    Text2SQL_py --> Run_py
    Run_py --> Conv_py
    Conv_py --> Parser_py
    Parser_py --> ProcessSQL
    ProcessSQL --> Eval_ckpts
    Eval_ckpts --> ClassifierEval2
    Eval_ckpts --> SpiderEval
    SpiderEval --> SpiderEM
    SpiderEM --> SpiderSuite
    EvalRobust --> Eval_ckpts

    TrainAll --> SchemaClassifier
    TrainAll --> Text2SQL_py
    InferScripts --> Run_py
    EvalScripts --> Eval_ckpts

    SpiderData --> EvalRobust

    %% Click Events
    click Config_py "https://github.com/ruckbreasoning/resdsql/blob/main/config.py"
    click Requirements "https://github.com/ruckbreasoning/resdsql/blob/main/requirements.txt"
    click NatSQL_Config "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/config.py"
    click NatSQL_Req "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/requirements.txt"
    click JSON_Datasets "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/NatSQLv1_6/*.json"
    click PKL_Dataset "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/data/20k.pkl"
    click Tables1 "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/NatSQLv1_6/tables.json"
    click Tables2 "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/NatSQLv1_6/tables_for_natsql.json"
    click Preproc_py "https://github.com/ruckbreasoning/resdsql/blob/main/preprocessing.py"
    click OrigDataset "https://github.com/ruckbreasoning/resdsql/blob/main/original_text2sql_dataset.py"
    click CheckPreproc "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/check_and_preprocess.sh"
    click SentenceSplit "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/setence_split.py"
    click PatternGen "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/pattern_generation.py"
    click TableTransform "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/table_transform.py"
    click Punkt "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/punkt.py"
    click SchemaClassifier "https://github.com/ruckbreasoning/resdsql/blob/main/schema_item_classifier.py"
    click BridgeEncoder "https://github.com/ruckbreasoning/resdsql/blob/main/utils/bridge_content_encoder.py"
    click ClassifierModel "https://github.com/ruckbreasoning/resdsql/blob/main/utils/classifier_model.py"
    click ClassifierLoss "https://github.com/ruckbreasoning/resdsql/blob/main/utils/classifier_loss.py"
    click ClassifierEval "https://github.com/ruckbreasoning/resdsql/blob/main/utils/classifier_metric/evaluator.py"
    click DataGen "https://github.com/ruckbreasoning/resdsql/blob/main/text2sql_data_generator.py"
    click Text2SQL_py "https://github.com/ruckbreasoning/resdsql/blob/main/text2sql.py"
    click Run_py "https://github.com/ruckbreasoning/resdsql/blob/main/run.py"
    click TrainScripts "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/train/text2natsql/*"
    click CSPTrain "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/train/cspider_text2natsql/*"
    click SchemaTrain "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/train/text2sql/*"
    click CSPSchemaTrain "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/train/cspider_text2sql/*"
    click Conv_SH "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql.sh"
    click Conv_py "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql/natsql2sql.py"
    click Parser_py "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql/natsql_parser.py"
    click PreprocessConv "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql/preprocess/*.py"
    click ProcessSQL "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql/process_sql.py"
    click ConvUtils "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql/utils.py"
    click Conv_SH2 "https://github.com/ruckbreasoning/resdsql/blob/main/NatSQL/natsql2sql_without_values.sh"
    click Eval_ckpts "https://github.com/ruckbreasoning/resdsql/blob/main/evaluate_text2sql_ckpts.py"
    click EvalRobust "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/evaluate_robustness/*"
    click SpiderEval "https://github.com/ruckbreasoning/resdsql/blob/main/utils/spider_metric/evaluator.py"
    click SpiderEM "https://github.com/ruckbreasoning/resdsql/blob/main/utils/spider_metric/spider_exact_match.py"
    click SpiderSuite "https://github.com/ruckbreasoning/resdsql/blob/main/utils/spider_metric/spider_test_suite.py"
    click ClassifierEval2 "https://github.com/ruckbreasoning/resdsql/blob/main/utils/classifier_metric/evaluator.py"
    click TrainAll "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/train/*"
    click InferScripts "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/inference/*"
    click EvalScripts "https://github.com/ruckbreasoning/resdsql/tree/main/scripts/evaluate_robustness/*"

    %% Styles
    classDef data fill:#cce5ff,stroke:#004085,color:#004085
    classDef config fill:#e2e3e5,stroke:#6c757d,color:#383d41
    classDef preproc fill:#ffe5b4,stroke:#d48700,color:#d48700
    classDef ml fill:#d4edda,stroke:#155724,color:#155724
    classDef script fill:#f8d7da,stroke:#721c24,color:#721c24
    classDef external fill:#e2e3e5,stroke:#6c757d,color:#383d41
```
