How rehanali1092/nl-2-sql-compiler works
This repository is a small, pipeline-driven compiler that translates natural-language database requests into SQL, executes them against SQLite, and returns results. The architecture is layered like a classic compiler: input normalization, parsing, semantic validation, optimization, SQL generation, execution, and history/logging.
1. Entry point / orchestration
- main.py is the command-line front end.
- It takes a user query, drives it through the compiler stages, and presents either generated SQL or execution results.
- This is the main control boundary between the user and the compiler pipeline.
2. Lexical analysis
- lexer/tokenizer.py converts free-form text into tokens.
- lexer/synonyms.py supports normalization of natural-language variants into canonical terms, which is important because users may express the same intent in different wording.
- This stage is where the project begins mapping “natural language” into a structured query language domain.
3. Parsing / AST construction
- parser/grammar.py defines the supported query grammar.
- parser/parse_tree.py holds the parse tree / AST representation used by later phases.
- The parser is the main structural boundary: it turns token sequences into a query object with explicit clauses like SELECT, WHERE, ORDER BY, INSERT, UPDATE, and DELETE.
4. Semantic analysis
- semantic/analyzer.py validates the parsed query against schema knowledge.
- semantic/symbol_table.py provides the schema-aware symbol lookup used for checking table and column references.
- schema.json is the key external schema source, so semantic validation depends on predefined database structure rather than discovering schema dynamically.
5. Optimization
- optimizer/optimizer.py performs lightweight query simplification.
- From the README and file naming, this is mainly aimed at removing redundant conditions and simplifying simple AND-based filters.
- This stage sits after semantic validation, so it assumes the query is already structurally and schema-valid.
6. SQL generation
- codegen/sql_generator.py translates the optimized internal representation into concrete SQL strings.
- This is the compiler’s code generation boundary: everything before it is internal representation, and everything after it is executable SQL.
7. Execution layer
- executor/ is the runtime boundary that sends generated SQL to SQLite and handles results.
- The primary database artifact is student_data.db, which is the target datastore for generated statements.
- This layer is separate from SQL generation, so the system cleanly distinguishes “produce SQL” from “run SQL.”
8. History / persistence of past queries
- history/history_manager.py and query_history.db suggest a lightweight persistence layer for recording query history.
- This is not part of the compilation pipeline itself, but it is an important side subsystem for traceability and user session continuity.
9. Error handling
- utils/errors.py defines compiler/runtime error types.
- utils/error_reporter.py centralizes how failures are surfaced to the user.
- This gives the pipeline a consistent failure path for lexical, parse, semantic, optimization, and execution errors.
10. Validation and test boundary
- tests/test_integration.py indicates end-to-end coverage of the full NL → SQL → SQLite flow.
- Because the system is a compiler pipeline, integration tests are especially important: they validate that all stages agree on the same internal contract.
11. Data flow summary
- User text enters via main.py.
- It is tokenized, parsed into a query structure, semantically checked against schema.json / symbol table, optionally optimized, converted to SQL, then executed on student_data.db.
- Results and/or failures are reported back through the CLI, while query history can be persisted separately in query_history.db.
12. Key architectural boundary
- The most important boundary in the repository is between natural-language interpretation and database execution.
- Everything before SQL generation is compiler logic; everything after that is runtime/database interaction.
- For an architecture diagram, these should be drawn as distinct layers with schema.json feeding semantic analysis and SQLite databases feeding execution/history.
Main entry points: main.py CLI · analyzer.py Analyzer · tokenizer.py Tokenizer · executor Executor
Components 17
Compiler pipeline
Natural-language query processing stages from input normalization through SQL generation.
- CLI · Command-line orchestration for the full NL-to-SQL flow.
main.py - Tokenizer · Splits free-form input into tokens.
lexer/tokenizer.py - Synonyms · Canonicalizes natural-language variants.
lexer/synonyms.py - Grammar · Defines supported query structure and clauses.
parser/grammar.py - Parse tree · Internal query representation passed between compiler stages.
parser/parse_tree.py - Analyzer · Validates queries against schema-aware rules.
semantic/analyzer.py - Symbols · Resolves table and column names during semantic analysis.
semantic/symbol_table.py - Optimizer · Simplifies redundant conditions before SQL generation.
optimizer/optimizer.py - SQL generator · Converts the optimized internal query into SQL text.
codegen/sql_generator.py
Runtime and data
Database execution, persistence, and external data artifacts.
- Executor · Runs generated SQL against SQLite and returns results.
executor - Student DB · Primary target database for generated statements.
student_data.db - History · Records past queries for traceability and reuse.
history/history_manager.py - Query log · Persistent store for query history.
query_history.db
Support
Errors, validation, and integration coverage that cut across the pipeline.
- Schema · External schema source used by semantic validation.
schema.json - Errors · Shared compiler and runtime exception definitions.
utils/errors.py - Reporter · Formats and surfaces failures to the user.
utils/error_reporter.py - Integration tests · End-to-end coverage of the NL-to-SQL pipeline.
tests/test_integration.py
Connections 20
- CLI → Tokenizer: input · User text enters the compiler pipeline.
- Tokenizer → Synonyms: normalize · Tokens are normalized through synonym mapping.
- Synonyms → Grammar: prepare · Normalized language is interpreted by the grammar.
- Grammar → Parse tree: build · Grammar rules shape the parse tree / AST.
- Parse tree → Analyzer: validate · Parsed queries are checked for semantic correctness.
- Symbols → Analyzer: resolve · Schema-aware symbol lookup supports semantic checks.
- Schema → Symbols: load schema · Schema definitions feed symbol resolution.
- Analyzer → Optimizer: simplify · Validated queries proceed to optimization.
- Optimizer → SQL generator: emit sql · Optimized internal form is translated to SQL.
- SQL generator → Executor: execute · Generated SQL is handed to the runtime layer.
- Executor → Student DB: query db · SQL is executed against the SQLite database.
- History → Query log: persist · Query history is stored in SQLite.
- CLI → History: record · The CLI updates query history after processing.
- Errors → Reporter: report · Error types are surfaced through the reporting layer.
- Tokenizer → Errors: fail · Lexical failures use shared compiler errors.
- Analyzer → Errors: fail · Semantic validation errors use shared compiler errors.
- Executor → Reporter: surface · Runtime/database failures are reported to the user.
- CLI → Reporter: output · CLI presents generated SQL, results, or failures.
- Integration tests → CLI: exercise · End-to-end tests drive the CLI and full pipeline.
- Integration tests → Student DB: verify · Tests validate behavior against the database.