How weaidb/cracksql works
Project type and purpose
• CrackSQL is a full-stack, hybrid SQL-dialect translation service.
• Purpose: translate SQL queries between PostgreSQL, MySQL, and Oracle using a combination of rule-based parsing (ANTLR + SQLGlot) and LLM/embedding models. It exposes:
• A Python API & CLI (translate.py, init_knowledge_base.py)
• A Flask REST backend (backend/app.py)
• A Vue3/Vite web interface (webui/)
File structure
• Top-level “backend” directory:
• api/ – Flask route definitions and routers
• services/ – business-logic implementations mirrored under api/services
• config/ – configuration files (YAML, logging, database, Gunicorn)
• preprocessor/ – ANTLR-based SQL parsers and tree mappers
• llm_model/ – LLM and embedding model management & service wrappers
• vector_store/ – Chroma-based vector storage for knowledge retrieval
• translator/ & rewrite.py – orchestrator module combining rule/LLM pipelines
• models.py, db_config.py – ORM models and database connectors
• “data” directory: ANTLR grammars (.g4), processed JSON specs, seed docs, pretrained embedding models
• “webui” directory: Vue.js single-page application with components, API clients, routing, state management
README for insights
• Describes three translation modes (rule‐only, LLM‐direct, rule+LLM)
• Lists supported entry points (Python, CLI, web)
• Mentions external dependencies: OpenAI/HuggingFace APIs, local embedding, relational database, vector store
a. Main components
• Client tier: Web UI (Vue SPA), CLI, Python API
• API tier: Flask app (Gunicorn/WSGI)
• Business logic: Translator orchestrator, Preprocessor (ANTLR), Rule engine (SQLGlot), LLM/Embedding services
• Data tier: Relational DB (SQLAlchemy), Vector store (Chroma), File system (grammar & spec JSON)
• External services: OpenAI/HuggingFace endpoints for LLM & embeddings
b. Relationships & interactions
1. User → Web UI or CLI
2. → HTTP/REST → Flask API endpoints (/translate, /initkb, /rewrite…)
3. Translator module invokes Preprocessor to parse SQL AST
4. Optionally query Vector Store for knowledge retrieval
5. Call local or cloud LLM/Embedding service
6. Apply rule engine and compose final SQL
7. Persist logs/metadata to Relational DB
8. Return translated SQL → UI/CLI
c. Architectural patterns
• Layered architecture (Presentation → API → Service → Data)
• Adapter pattern for plugging in different LLM/embedding backends
• Pipeline pattern in translator: parse → retrieve → model → post-process
d. Key technologies/frameworks
• Python: Flask, SQLAlchemy, ANTLR4, SQLGlot, Chroma
• LLMs: OpenAI API or HuggingFace models
• Frontend: Vue3, Vite, Element-Plus