{"owner":"tobymao","repo":"sqlglot","hasSkills":true,"hasMcp":false,"mcpConfig":null,"found":["AGENTS.md"],"skills":{"AGENTS.md":"# Contributing to [SQLGlot](https://github.com/tobymao/sqlglot/blob/main/README.md)\n## About SQLGlot\n\nSQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine written in pure Python. It supports 31+ SQL dialects and can transpile between them while preserving semantics. The codebase is performance-critical despite being pure Python, with an optional mypyc-compiled C extension for speed improvements (`sqlglotc/`).\n\n## Development Commands\nBefore contributing read CONTRIBUTING.md\n\n### Installation\n```bash\n# Basic installation\nmake install\n\n# Development installation (Python only, no C extension)\nmake install-dev\n\n# Development installation with mypyc C extension\nmake install-devc\n\n# Install pre-commit hooks\nmake install-pre-commit\n\n# With uv (faster):\nUV=1 make install-dev\n```\n\n### Testing\n```bash\n# Run all tests (pure Python, hides .so files during run)\nmake test\n\n# Run all tests with mypyc C extension (builds extension first)\nmake testc\n\n# Run only unit tests (skip integration tests, pure Python)\nmake unit\n\n# Run only unit tests with C extension\nmake unitc\n\n# Run specific test file\npython -m unittest tests.test_expressions\n\n# Run specific test class\npython -m unittest tests.test_expressions.TestExpressions\n\n# Run specific test method\npython -m unittest tests.test_expressions.TestExpressions.test_alias\n```\n\n### Linting & Type Checking\n```bash\n# Run linter and formatter only\nmake style\n\n# Run full checks (style + pure Python tests + C extension tests)\nmake check\n```\n\n### Benchmarks\n```bash\n# Run parsing benchmark\nmake bench\n\n# Run optimization benchmark\nmake bench-optimize\n```\n\n## Architecture Overview\n\nSQLGlot follows a classic compiler architecture with three main phases:\n\n### 1. Tokenizer (`tokens.py`)\n- Converts SQL strings into a sequence of tokens (lexical analysis)\n- Pure Python implementation in `tokens.py`; core logic in `tokenizer_core.py` (mypyc-compiled when using `[c]` extra)\n- Maps lexemes to `TokenType` enum values via `KEYWORDS` and `SINGLE_TOKENS` dictionaries\n- Dialects can override tokenizer behavior by customizing these mappings\n\n### 2. Parser (`parser.py`)\n- Converts tokens into an Abstract Syntax Tree (AST)\n- Uses recursive descent parsing approach\n- Parsing methods follow `_parse_*` naming convention (e.g., `_parse_create()`, `_parse_select()`)\n- Token matching methods: `_match()`, `_match_set()`, `_match_text_seq()`, `_match_texts()`\n- Helper methods for common patterns: `_parse_csv()`, `_parse_wrapped()`, `_parse_wrapped_csv()`\n- Maintains index/cursor with `_advance()` and `_retreat()` methods\n- Falls back to `exp.Command` for unparseable SQL (preserves original text)\n\n### 3. Generator (`generator.py`)\n- Converts AST back to SQL strings\n- Traverses AST recursively, generating SQL for each expression node\n- Two ways to customize generation:\n  - `TRANSFORMS` dictionary for single-line generations\n  - `<expr_name>_sql()` methods for complex generations\n- Helper methods: `expressions()`, `func()`, `rename_func()`\n- Use `sep()` and `seg()` for proper whitespace/newline handling in pretty-printed output\n\n### 4. Expressions (`expressions.py`)\n- Defines all AST node types as Python classes inheriting from `Expression`\n- Each expression represents a semantic SQL concept (e.g., `Select`, `Join`, `Column`)\n- Expressions can be traversed using `.find()`, `.find_all()`, `.walk()`, `.transform()`\n- Building SQL programmatically: use helper functions like `select()`, `from_()`, `where()`, etc.\n\n### 5. Dialects (`dialects/`)\n- 34 dialect implementations in `dialects/<dialect>.py`\n- Each dialect subclasses base `Dialect` and can override Tokenizer, Parser, and Generator\n- Base \"sqlglot\" dialect acts as a superset to minimize duplication\n- Dialect customization via:\n  - Feature flags (e.g., `SUPPORTS_IMPLICIT_UNNEST`)\n  - Token sets (e.g., `RESERVED_TOKENS`)\n  - `token -> Callable` mappings (e.g., `FUNCTIONS`, `STATEMENTS`)\n  - `Expression -> str` mappings in Generator\n\n### 6. Optimizer (`optimizer/`)\n- Canonicalizes and optimizes queries while preserving semantics\n- Applies sequential optimization rules (order matters!)\n- Key rules:\n  - `qualify`: Normalizes identifiers and qualifies all tables/columns (most important rule)\n  - `annotate_types`: Infers data types throughout the AST\n  - `pushdown_predicates`, `pushdown_projections`: Optimization rewrites\n  - `simplify`: Simplifies boolean expressions and arithmetic\n- Rules depend on schema information for best results\n- Optimizer performs logical optimization only (not physical/performance)\n\n### 7. Schema (`schema.py`)\n- Represents database structure (tables, columns, types)\n- Used by optimizer and lineage analysis\n- `MappingSchema` takes nested dict: `{\"table\": {\"col\": \"type\"}}`\n\n### 8. Lineage (`lineage.py`)\n- Traces column-level lineage through queries\n- Requires target query, upstream queries, and root table schemas\n- Builds linked list of `Node` objects representing data flow\n- Can visualize with `node.to_html()`\n\n## Key Concepts\n\n### The \"sqlglot\" Dialect\n- Base dialect that accommodates common syntax across all dialects\n- All other dialects extend this base\n- When adding multi-dialect features, prefer adding to base dialect to avoid duplication\n- Only add dialect-specific features to individual dialect classes\n\n### AST-First Approach\n- SQLGlot preserves _semantics_ not syntax\n- Parse SQL → AST (semantic representation) → Generate SQL in target dialect\n- This enables accurate cross-dialect transpilation\n- Comments are preserved on best-effort basis\n- See `posts/ast_primer.md` for detailed AST tutorial\n\n### Testing Philosophy\n- Comprehensive test suite in `tests/` directory\n- Dialect-specific tests in `tests/dialects/`\n- Tests are critical - \"robust test suite\" is a core feature\n- Use `tests/fixtures/` for test data\n- `tests/helpers.py` contains test utilities\n\n### Parser/Generator Symmetry\n- Parser: `token -> Callable` mappings (builds AST from tokens)\n- Generator: `Expression -> str` mappings (builds SQL from AST)\n- Customization follows similar patterns in both\n\n### Type Annotations\n- Type inference is crucial for some transpilations (e.g., `+` can mean addition or concatenation)\n- Optimizer's `annotate_types` rule propagates type information through AST\n- Requires schema information to work effectively\n\n## Common Usage Patterns\n\n### Reading SQL\n```python\nimport sqlglot\nexpression = sqlglot.parse_one(\"SELECT * FROM table\", dialect=\"spark\")\n```\n\n### Validate Function Expression\n```python\nimport sqlglot\ntree = sqlglot.parse_one(\"SELECT NULLIF(1, 2)\", dialect=\"snowflake\")\nif \"Anonymous\" in repr(tree):\n    print(\"Function expression exists\")\nelse:\n    print(\"Function expression does not exist\")\n```\n\n### Writing SQL\n```python\nexpression.sql(dialect=\"duckdb\", pretty=True)\n```\n\n### Building SQL Programmatically\n```python\nfrom sqlglot import select, condition\nselect(\"*\").from_(\"y\").where(condition(\"x=1\").and_(\"y=1\")).sql()\n```\n\n### Traversing AST\n```python\nfrom sqlglot import parse_one, exp\ntree = parse_one(\"SELECT a, b + 1 AS c FROM d\", dialect=\"dialect\")\nfor column in tree.find_all(exp.Column):\n    print(column.alias_or_name)\n```\n\n### Transforming AST\n```python\ndef transformer(node):\n    if isinstance(node, exp.Column) and node.name == \"a\":\n        return parse_one(\"FUN(a)\", dialect=\"dialect\")\n    return node\n\ntransformed = tree.transform(transformer)\n```\n\n## Development Guidelines\n\n- Follow [Conventional Commits](https://www.conventionalcommits.org/) for PR titles\n- If you are an agent, you must add your model [CLAUDE, CODEX, etc...] to the end of PR titles and commit messages.\n- Keep PRs minimal in scope - one well-defined change per PR\n- Add tests for non-trivial changes\n- Update docstrings if APIs change\n- Run `make check` before submitting\n- Use comments for complex logic only\n- Don't change license or license files in setup.py, there's nothing wrong with it.\n\n## Important Files\n\n- `posts/ast_primer.md`: Detailed AST tutorial\n- `posts/onboarding.md`: Architecture deep-dive (HIGHLY RECOMMENDED)\n- `.pre-commit-config.yaml`: Pre-commit hooks (ruff, ruff-format, mypy)\n- `pyproject.toml`: Project metadata and build config\n- `Makefile`: All development commands\n\n## Performance Considerations\n\n- Pure Python implementation with optional mypyc-compiled C extension (`sqlglotc/`)\n- Install with `pip install \"sqlglot[c]\"` for C extension speed boost\n- The `[c]` extra compiles core modules (`expression_core`, `tokenizer_core`, `parser_core`, etc.) via mypyc\n- Performance is a key feature despite Python implementation\n- Benchmarks compare against other SQL parsers - see `benchmarks/`\n- Avoid use of typing.Protocol, prefer Union Type and Duck Typing\n- Never suggest Pratt parsing as an optimization for the parser. The recursive descent approach is intentional and preferred.\n\n---\n\n## SQLGlot Coding Rules\n\nThe following patterns are based on PR review feedback. Follow these to minimize review iterations.\n\n### 1. Use Automatic Naming Convention for Generator Methods\n\n**Don't do this (module-level function with TRANSFORMS):**\n```python\ndef _my_func_sql(self: MyDialect.Generator, expression: exp.MyFunc) -> str:\n    ...\n\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: _my_func_sql,\n    }\n```\n\n**Don't do this (method with TRANSFORMS):**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: lambda self, e: self._my_func_sql(e),\n    }\n\n    def _my_func_sql(self, expression):\n        ...\n```\n\n**Do this (auto-discovered method):**\n```python\nclass Generator:\n    # No TRANSFORMS entry needed - automatic discovery by name\n\n    def myfunc_sql(self, expression: exp.MyFunc) -> str:\n        ...\n```\n\nGenerator methods named `<lowercase_expr_name>_sql` are automatically discovered.\n\nImportant: Only use TRANSFORMS for simple one-liners like `rename_func(\"OTHER_NAME\")` or lambdas or functions with multiple entry points. For any single entry point function, always use an auto-discovered method inside the Generator class.\n\nSQLGlot automatically applies transformations based on the structure of the name, but when this fails, you must rename the function.  This is only when the SQL name is not covered by auto mapping:\n\n**Do this:**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.ArrayLength: rename_func(\"LENGTH\"),\n    }\n```\n\n**Don't do this:**\n```python\nexp.ArrayLength: lambda self, e: self.func(\"LENGTH\", e.this),\n```\n\n### 2. Use Existing Expression Classes, Not Anonymous\n\n**Don't do this:**\n```python\nfrom_base64 = exp.Anonymous(this=\"FROM_BASE64\", expressions=[input_expr])\n```\n\n**Do this:**\n```python\nfrom_base64 = exp.FromBase64(this=input_expr)\n```\n\nAlways check if an expression class exists in `expressions.py` before using `exp.Anonymous`. Anonymous should only be used for functions that don't have a dedicated class. Search for the function name in expressions.py first.\n\n### 3. SQL Generation: Choose the Right Approach\n\nUse the appropriate method based on complexity. From simplest to most complex:\n\n#### Level 1: Generator Helper Methods\nFor generating function calls in generator methods, use `self.func()`:\n```python\ndef myfunc_sql(self, expression):\n    # Don't: return self.sql(exp.Func(this=\"MY_FUNC\", expressions=[expression.this]))\n    # Do:\n    return self.func(\"MY_FUNC\", expression.this)\n```\n\n#### Level 2: Expression Builders\nFor building expressions, use helper functions instead of direct class construction:\n\n| Helper | Instead of | Benefits |\n|--------|-----------|----------|\n| `exp.func(\"name\", *args)` | `exp.Anonymous(...)` | Finds proper Func class |\n| `exp.array(e1, e2, ...)` | `exp.Array(expressions=[...])` | Parses automatically |\n| `exp.and_(e1, e2, ...)` | `exp.And(this=..., expression=...)` | Handles nesting |\n| `exp.or_(e1, e2, ...)` | `exp.Or(this=..., expression=...)` | Handles nesting |\n| `exp.case().when(cond, val).else_(default)` | `exp.Case(ifs=[...])` | Fluent interface |\n| `exp.cast(expr, \"TYPE\")` | `exp.Cast(this=..., to=...)` | Builds DataType |\n| `exp.column(\"col\", \"table\")` | `exp.Column(...)` | Handles identifiers |\n| `exp.null()` | `exp.Null()` | Simple factory |\n\nAlso use expression operators for cleaner code:\n```python\n# Arithmetic: exp.column(\"x\") + 1  instead of  exp.Add(this=..., expression=...)\n# Indexing:   arr[index]           instead of  exp.Bracket(this=arr, expressions=[index])\n# Comparison: arg.is_(exp.Null())  instead of  exp.Is(this=arg, expression=exp.Null())\n```\n\n#### Level 3: SQL Templates\nWhen expressions become complex, use templates with `exp.maybe_parse()` and `exp.replace_placeholders()`:\n\n```python\n# Define template with :placeholder syntax\nMY_TEMPLATE: exp.Expression = exp.maybe_parse(\n    \"CASE WHEN :arg IS NULL THEN NULL ELSE :result END\"\n)\n\n# In generator method\ndef myfunc_sql(self, expression):\n    result = exp.replace_placeholders(\n        self.MY_TEMPLATE.copy(),\n        arg=expression.this,\n        result=some_expression,\n    )\n    return self.sql(result)\n```\n\n#### Avoid: F-strings with SQL Fragments\nYou should rarely, if ever, build SQL with f-strings - it breaks quoting, escaping, and dialect handling:\n```python\n# NEVER do this:\ndef my_func_sql(self, expression):\n    return f\"CAST({self.sql(expression.this)} AS TIME)\"\n\n# Do this instead:\ndef my_func_sql(self, expression):\n    return self.sql(exp.cast(expression.this, \"TIME\"))\n```\n\n### 4. Type Checking: `is_string` vs `is_type()`\n\nThese serve **different purposes**:\n\n**`is_type()`** - Semantic type check:\n```python\n# Returns True if expression's type is text (columns, function results, etc.)\n# Requires annotate_types() to populate type info\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    ...\n```\n\n**`is_string`** - Syntactic check for string literals:\n```python\n# Returns True only for literal strings like 'hello'\n# Works without type annotation\nif arg.is_string:\n    value = arg.name  # Extract the string value\n```\n\n\n**When to use each:**\n\n| Use Case | Method |\n|----------|--------|\n| Check if node is a string literal to extract its value | `is_string` |\n| Check if node is a literal vs column/expression | `is_string` |\n| Check semantic type (works for columns, functions) | `is_type()` |\n| Cover both literals and typed expressions | `is_string or is_type()` |\n\n**Combined pattern (from `length_sql`):**\n```python\n# Fast check for string literals (no annotation needed)\nif arg.is_string:\n    return self.func(\"LENGTH\", arg)\n\n# For non-literals, get type info if needed\nif not arg.type:\n    arg = annotate_types(arg, dialect=self.dialect)\n\n# Then check semantic type\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    return self.func(\"LENGTH\", arg)\n```\n\n**Don't do direct type comparisons:**\n```python\n# Bad\nif input_expr.type and input_expr.type.this in exp.DataType.TEXT_TYPES:\n\n# Good\nif input_expr.is_type(*exp.DataType.TEXT_TYPES):\n```\n\n### 5. Use `to_py()` for Literal Value Extraction\n\n**Don't do this:**\n```python\nif isinstance(arg, exp.Literal):\n    value = int(arg.this.strip(\"'\"))\n```\n\n**Do this:**\n```python\nif isinstance(arg, exp.Literal) and arg.is_number:\n    value = int(arg.to_py())\n```\n\n### 6. Avoid Compile-Time NULL Checks\n\nDon't check for `exp.Null()` or literal NULL values in Python during transpilation. NULL handling should happen at query time in the generated SQL using `IS NULL` checks.\n\n**Don't do this:**\n```python\ndef myfunc_sql(self, expression):\n    # Bad: checking for literal NULL at transpile time\n    if any(isinstance(arg, exp.Null) for arg in expression.expressions):\n        return self.sql(exp.Null())\n```\n\n**Do this:**\n```python\n# Good: generate SQL that handles NULL at query time\nTEMPLATE = exp.maybe_parse(\"CASE WHEN :arg IS NULL THEN NULL ELSE ... END\")\n```\n\nCompile-time checks only handle literal `NULL` values in the SQL text, not NULL values that come from columns, parameters, or expressions at runtime. Generate SQL with `IS NULL` checks to handle all cases.\n\n### 7. Type Annotations in Tests\n\nWhen transpilation depends on `is_type()` checks, tests need `annotate_types()`:\n\n```python\nfrom sqlglot.optimizer import annotate_types\n\n# Without annotation - is_type() returns False for literals\nexpr = self.validate_identity(\"SELECT BASE64_ENCODE('Hello World')\")\n\n# With annotation - types are inferred, is_type() works\nannotated = annotate_types(expr, dialect=\"snowflake\")\nself.assertEqual(annotated.sql(\"duckdb\"), \"SELECT TO_BASE64(ENCODE('Hello World'))\")\n```\n\n### 8. Use `find_ancestor` with Scope Boundaries\n\nWhen searching for ancestors, include scope boundaries to avoid crossing into parent queries:\n\n```python\n# Stop at Select to stay within current query scope\nancestor = expression.find_ancestor(exp.Where, exp.Having, exp.Select)\nif ancestor and not isinstance(ancestor, exp.Select):\n    # Found restricted context within current scope\n    ...\n```\n\n### 9. Use @unsupported_args for unsupported arguments\n\nWhen arguments are not supported do this:\n\n```python\n        @unsupported_args(\"ins_cost\", \"del_cost\", \"sub_cost\")\n        def levenshtein_sql(self, expression: exp.Levenshtein) -> str:\n```\n\n### 10. Keep Code Minimal\n\n- Remove unused imports, variables, and dead code\n- Don't add comments for obvious code\n- Don't add docstrings unless the function is complex or public API\n- Prefer inline expressions over intermediate variables when readable\n- Don't add backwards-compatibility shims for removed code\n\n### 11. Test Patterns\n\n- Add tests to the appropriate dialect test file (e.g., `tests/dialects/test_snowflake.py`)\n- Use `self.validate_all()` for cross-dialect tests\n- Use `self.validate_identity()` for round-trip tests\n- Don't add tests for functionality that already has coverage\n\n### 12. Ensure Test Validity\n\n- Make sure all tests added to tests/dialects/*.py actually run in the relevant databases, such as snowflake or duckdb\n"},"files":{"AGENTS.md":"# Contributing to [SQLGlot](https://github.com/tobymao/sqlglot/blob/main/README.md)\n## About SQLGlot\n\nSQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine written in pure Python. It supports 31+ SQL dialects and can transpile between them while preserving semantics. The codebase is performance-critical despite being pure Python, with an optional mypyc-compiled C extension for speed improvements (`sqlglotc/`).\n\n## Development Commands\nBefore contributing read CONTRIBUTING.md\n\n### Installation\n```bash\n# Basic installation\nmake install\n\n# Development installation (Python only, no C extension)\nmake install-dev\n\n# Development installation with mypyc C extension\nmake install-devc\n\n# Install pre-commit hooks\nmake install-pre-commit\n\n# With uv (faster):\nUV=1 make install-dev\n```\n\n### Testing\n```bash\n# Run all tests (pure Python, hides .so files during run)\nmake test\n\n# Run all tests with mypyc C extension (builds extension first)\nmake testc\n\n# Run only unit tests (skip integration tests, pure Python)\nmake unit\n\n# Run only unit tests with C extension\nmake unitc\n\n# Run specific test file\npython -m unittest tests.test_expressions\n\n# Run specific test class\npython -m unittest tests.test_expressions.TestExpressions\n\n# Run specific test method\npython -m unittest tests.test_expressions.TestExpressions.test_alias\n```\n\n### Linting & Type Checking\n```bash\n# Run linter and formatter only\nmake style\n\n# Run full checks (style + pure Python tests + C extension tests)\nmake check\n```\n\n### Benchmarks\n```bash\n# Run parsing benchmark\nmake bench\n\n# Run optimization benchmark\nmake bench-optimize\n```\n\n## Architecture Overview\n\nSQLGlot follows a classic compiler architecture with three main phases:\n\n### 1. Tokenizer (`tokens.py`)\n- Converts SQL strings into a sequence of tokens (lexical analysis)\n- Pure Python implementation in `tokens.py`; core logic in `tokenizer_core.py` (mypyc-compiled when using `[c]` extra)\n- Maps lexemes to `TokenType` enum values via `KEYWORDS` and `SINGLE_TOKENS` dictionaries\n- Dialects can override tokenizer behavior by customizing these mappings\n\n### 2. Parser (`parser.py`)\n- Converts tokens into an Abstract Syntax Tree (AST)\n- Uses recursive descent parsing approach\n- Parsing methods follow `_parse_*` naming convention (e.g., `_parse_create()`, `_parse_select()`)\n- Token matching methods: `_match()`, `_match_set()`, `_match_text_seq()`, `_match_texts()`\n- Helper methods for common patterns: `_parse_csv()`, `_parse_wrapped()`, `_parse_wrapped_csv()`\n- Maintains index/cursor with `_advance()` and `_retreat()` methods\n- Falls back to `exp.Command` for unparseable SQL (preserves original text)\n\n### 3. Generator (`generator.py`)\n- Converts AST back to SQL strings\n- Traverses AST recursively, generating SQL for each expression node\n- Two ways to customize generation:\n  - `TRANSFORMS` dictionary for single-line generations\n  - `<expr_name>_sql()` methods for complex generations\n- Helper methods: `expressions()`, `func()`, `rename_func()`\n- Use `sep()` and `seg()` for proper whitespace/newline handling in pretty-printed output\n\n### 4. Expressions (`expressions.py`)\n- Defines all AST node types as Python classes inheriting from `Expression`\n- Each expression represents a semantic SQL concept (e.g., `Select`, `Join`, `Column`)\n- Expressions can be traversed using `.find()`, `.find_all()`, `.walk()`, `.transform()`\n- Building SQL programmatically: use helper functions like `select()`, `from_()`, `where()`, etc.\n\n### 5. Dialects (`dialects/`)\n- 34 dialect implementations in `dialects/<dialect>.py`\n- Each dialect subclasses base `Dialect` and can override Tokenizer, Parser, and Generator\n- Base \"sqlglot\" dialect acts as a superset to minimize duplication\n- Dialect customization via:\n  - Feature flags (e.g., `SUPPORTS_IMPLICIT_UNNEST`)\n  - Token sets (e.g., `RESERVED_TOKENS`)\n  - `token -> Callable` mappings (e.g., `FUNCTIONS`, `STATEMENTS`)\n  - `Expression -> str` mappings in Generator\n\n### 6. Optimizer (`optimizer/`)\n- Canonicalizes and optimizes queries while preserving semantics\n- Applies sequential optimization rules (order matters!)\n- Key rules:\n  - `qualify`: Normalizes identifiers and qualifies all tables/columns (most important rule)\n  - `annotate_types`: Infers data types throughout the AST\n  - `pushdown_predicates`, `pushdown_projections`: Optimization rewrites\n  - `simplify`: Simplifies boolean expressions and arithmetic\n- Rules depend on schema information for best results\n- Optimizer performs logical optimization only (not physical/performance)\n\n### 7. Schema (`schema.py`)\n- Represents database structure (tables, columns, types)\n- Used by optimizer and lineage analysis\n- `MappingSchema` takes nested dict: `{\"table\": {\"col\": \"type\"}}`\n\n### 8. Lineage (`lineage.py`)\n- Traces column-level lineage through queries\n- Requires target query, upstream queries, and root table schemas\n- Builds linked list of `Node` objects representing data flow\n- Can visualize with `node.to_html()`\n\n## Key Concepts\n\n### The \"sqlglot\" Dialect\n- Base dialect that accommodates common syntax across all dialects\n- All other dialects extend this base\n- When adding multi-dialect features, prefer adding to base dialect to avoid duplication\n- Only add dialect-specific features to individual dialect classes\n\n### AST-First Approach\n- SQLGlot preserves _semantics_ not syntax\n- Parse SQL → AST (semantic representation) → Generate SQL in target dialect\n- This enables accurate cross-dialect transpilation\n- Comments are preserved on best-effort basis\n- See `posts/ast_primer.md` for detailed AST tutorial\n\n### Testing Philosophy\n- Comprehensive test suite in `tests/` directory\n- Dialect-specific tests in `tests/dialects/`\n- Tests are critical - \"robust test suite\" is a core feature\n- Use `tests/fixtures/` for test data\n- `tests/helpers.py` contains test utilities\n\n### Parser/Generator Symmetry\n- Parser: `token -> Callable` mappings (builds AST from tokens)\n- Generator: `Expression -> str` mappings (builds SQL from AST)\n- Customization follows similar patterns in both\n\n### Type Annotations\n- Type inference is crucial for some transpilations (e.g., `+` can mean addition or concatenation)\n- Optimizer's `annotate_types` rule propagates type information through AST\n- Requires schema information to work effectively\n\n## Common Usage Patterns\n\n### Reading SQL\n```python\nimport sqlglot\nexpression = sqlglot.parse_one(\"SELECT * FROM table\", dialect=\"spark\")\n```\n\n### Validate Function Expression\n```python\nimport sqlglot\ntree = sqlglot.parse_one(\"SELECT NULLIF(1, 2)\", dialect=\"snowflake\")\nif \"Anonymous\" in repr(tree):\n    print(\"Function expression exists\")\nelse:\n    print(\"Function expression does not exist\")\n```\n\n### Writing SQL\n```python\nexpression.sql(dialect=\"duckdb\", pretty=True)\n```\n\n### Building SQL Programmatically\n```python\nfrom sqlglot import select, condition\nselect(\"*\").from_(\"y\").where(condition(\"x=1\").and_(\"y=1\")).sql()\n```\n\n### Traversing AST\n```python\nfrom sqlglot import parse_one, exp\ntree = parse_one(\"SELECT a, b + 1 AS c FROM d\", dialect=\"dialect\")\nfor column in tree.find_all(exp.Column):\n    print(column.alias_or_name)\n```\n\n### Transforming AST\n```python\ndef transformer(node):\n    if isinstance(node, exp.Column) and node.name == \"a\":\n        return parse_one(\"FUN(a)\", dialect=\"dialect\")\n    return node\n\ntransformed = tree.transform(transformer)\n```\n\n## Development Guidelines\n\n- Follow [Conventional Commits](https://www.conventionalcommits.org/) for PR titles\n- If you are an agent, you must add your model [CLAUDE, CODEX, etc...] to the end of PR titles and commit messages.\n- Keep PRs minimal in scope - one well-defined change per PR\n- Add tests for non-trivial changes\n- Update docstrings if APIs change\n- Run `make check` before submitting\n- Use comments for complex logic only\n- Don't change license or license files in setup.py, there's nothing wrong with it.\n\n## Important Files\n\n- `posts/ast_primer.md`: Detailed AST tutorial\n- `posts/onboarding.md`: Architecture deep-dive (HIGHLY RECOMMENDED)\n- `.pre-commit-config.yaml`: Pre-commit hooks (ruff, ruff-format, mypy)\n- `pyproject.toml`: Project metadata and build config\n- `Makefile`: All development commands\n\n## Performance Considerations\n\n- Pure Python implementation with optional mypyc-compiled C extension (`sqlglotc/`)\n- Install with `pip install \"sqlglot[c]\"` for C extension speed boost\n- The `[c]` extra compiles core modules (`expression_core`, `tokenizer_core`, `parser_core`, etc.) via mypyc\n- Performance is a key feature despite Python implementation\n- Benchmarks compare against other SQL parsers - see `benchmarks/`\n- Avoid use of typing.Protocol, prefer Union Type and Duck Typing\n- Never suggest Pratt parsing as an optimization for the parser. The recursive descent approach is intentional and preferred.\n\n---\n\n## SQLGlot Coding Rules\n\nThe following patterns are based on PR review feedback. Follow these to minimize review iterations.\n\n### 1. Use Automatic Naming Convention for Generator Methods\n\n**Don't do this (module-level function with TRANSFORMS):**\n```python\ndef _my_func_sql(self: MyDialect.Generator, expression: exp.MyFunc) -> str:\n    ...\n\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: _my_func_sql,\n    }\n```\n\n**Don't do this (method with TRANSFORMS):**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: lambda self, e: self._my_func_sql(e),\n    }\n\n    def _my_func_sql(self, expression):\n        ...\n```\n\n**Do this (auto-discovered method):**\n```python\nclass Generator:\n    # No TRANSFORMS entry needed - automatic discovery by name\n\n    def myfunc_sql(self, expression: exp.MyFunc) -> str:\n        ...\n```\n\nGenerator methods named `<lowercase_expr_name>_sql` are automatically discovered.\n\nImportant: Only use TRANSFORMS for simple one-liners like `rename_func(\"OTHER_NAME\")` or lambdas or functions with multiple entry points. For any single entry point function, always use an auto-discovered method inside the Generator class.\n\nSQLGlot automatically applies transformations based on the structure of the name, but when this fails, you must rename the function.  This is only when the SQL name is not covered by auto mapping:\n\n**Do this:**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.ArrayLength: rename_func(\"LENGTH\"),\n    }\n```\n\n**Don't do this:**\n```python\nexp.ArrayLength: lambda self, e: self.func(\"LENGTH\", e.this),\n```\n\n### 2. Use Existing Expression Classes, Not Anonymous\n\n**Don't do this:**\n```python\nfrom_base64 = exp.Anonymous(this=\"FROM_BASE64\", expressions=[input_expr])\n```\n\n**Do this:**\n```python\nfrom_base64 = exp.FromBase64(this=input_expr)\n```\n\nAlways check if an expression class exists in `expressions.py` before using `exp.Anonymous`. Anonymous should only be used for functions that don't have a dedicated class. Search for the function name in expressions.py first.\n\n### 3. SQL Generation: Choose the Right Approach\n\nUse the appropriate method based on complexity. From simplest to most complex:\n\n#### Level 1: Generator Helper Methods\nFor generating function calls in generator methods, use `self.func()`:\n```python\ndef myfunc_sql(self, expression):\n    # Don't: return self.sql(exp.Func(this=\"MY_FUNC\", expressions=[expression.this]))\n    # Do:\n    return self.func(\"MY_FUNC\", expression.this)\n```\n\n#### Level 2: Expression Builders\nFor building expressions, use helper functions instead of direct class construction:\n\n| Helper | Instead of | Benefits |\n|--------|-----------|----------|\n| `exp.func(\"name\", *args)` | `exp.Anonymous(...)` | Finds proper Func class |\n| `exp.array(e1, e2, ...)` | `exp.Array(expressions=[...])` | Parses automatically |\n| `exp.and_(e1, e2, ...)` | `exp.And(this=..., expression=...)` | Handles nesting |\n| `exp.or_(e1, e2, ...)` | `exp.Or(this=..., expression=...)` | Handles nesting |\n| `exp.case().when(cond, val).else_(default)` | `exp.Case(ifs=[...])` | Fluent interface |\n| `exp.cast(expr, \"TYPE\")` | `exp.Cast(this=..., to=...)` | Builds DataType |\n| `exp.column(\"col\", \"table\")` | `exp.Column(...)` | Handles identifiers |\n| `exp.null()` | `exp.Null()` | Simple factory |\n\nAlso use expression operators for cleaner code:\n```python\n# Arithmetic: exp.column(\"x\") + 1  instead of  exp.Add(this=..., expression=...)\n# Indexing:   arr[index]           instead of  exp.Bracket(this=arr, expressions=[index])\n# Comparison: arg.is_(exp.Null())  instead of  exp.Is(this=arg, expression=exp.Null())\n```\n\n#### Level 3: SQL Templates\nWhen expressions become complex, use templates with `exp.maybe_parse()` and `exp.replace_placeholders()`:\n\n```python\n# Define template with :placeholder syntax\nMY_TEMPLATE: exp.Expression = exp.maybe_parse(\n    \"CASE WHEN :arg IS NULL THEN NULL ELSE :result END\"\n)\n\n# In generator method\ndef myfunc_sql(self, expression):\n    result = exp.replace_placeholders(\n        self.MY_TEMPLATE.copy(),\n        arg=expression.this,\n        result=some_expression,\n    )\n    return self.sql(result)\n```\n\n#### Avoid: F-strings with SQL Fragments\nYou should rarely, if ever, build SQL with f-strings - it breaks quoting, escaping, and dialect handling:\n```python\n# NEVER do this:\ndef my_func_sql(self, expression):\n    return f\"CAST({self.sql(expression.this)} AS TIME)\"\n\n# Do this instead:\ndef my_func_sql(self, expression):\n    return self.sql(exp.cast(expression.this, \"TIME\"))\n```\n\n### 4. Type Checking: `is_string` vs `is_type()`\n\nThese serve **different purposes**:\n\n**`is_type()`** - Semantic type check:\n```python\n# Returns True if expression's type is text (columns, function results, etc.)\n# Requires annotate_types() to populate type info\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    ...\n```\n\n**`is_string`** - Syntactic check for string literals:\n```python\n# Returns True only for literal strings like 'hello'\n# Works without type annotation\nif arg.is_string:\n    value = arg.name  # Extract the string value\n```\n\n\n**When to use each:**\n\n| Use Case | Method |\n|----------|--------|\n| Check if node is a string literal to extract its value | `is_string` |\n| Check if node is a literal vs column/expression | `is_string` |\n| Check semantic type (works for columns, functions) | `is_type()` |\n| Cover both literals and typed expressions | `is_string or is_type()` |\n\n**Combined pattern (from `length_sql`):**\n```python\n# Fast check for string literals (no annotation needed)\nif arg.is_string:\n    return self.func(\"LENGTH\", arg)\n\n# For non-literals, get type info if needed\nif not arg.type:\n    arg = annotate_types(arg, dialect=self.dialect)\n\n# Then check semantic type\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    return self.func(\"LENGTH\", arg)\n```\n\n**Don't do direct type comparisons:**\n```python\n# Bad\nif input_expr.type and input_expr.type.this in exp.DataType.TEXT_TYPES:\n\n# Good\nif input_expr.is_type(*exp.DataType.TEXT_TYPES):\n```\n\n### 5. Use `to_py()` for Literal Value Extraction\n\n**Don't do this:**\n```python\nif isinstance(arg, exp.Literal):\n    value = int(arg.this.strip(\"'\"))\n```\n\n**Do this:**\n```python\nif isinstance(arg, exp.Literal) and arg.is_number:\n    value = int(arg.to_py())\n```\n\n### 6. Avoid Compile-Time NULL Checks\n\nDon't check for `exp.Null()` or literal NULL values in Python during transpilation. NULL handling should happen at query time in the generated SQL using `IS NULL` checks.\n\n**Don't do this:**\n```python\ndef myfunc_sql(self, expression):\n    # Bad: checking for literal NULL at transpile time\n    if any(isinstance(arg, exp.Null) for arg in expression.expressions):\n        return self.sql(exp.Null())\n```\n\n**Do this:**\n```python\n# Good: generate SQL that handles NULL at query time\nTEMPLATE = exp.maybe_parse(\"CASE WHEN :arg IS NULL THEN NULL ELSE ... END\")\n```\n\nCompile-time checks only handle literal `NULL` values in the SQL text, not NULL values that come from columns, parameters, or expressions at runtime. Generate SQL with `IS NULL` checks to handle all cases.\n\n### 7. Type Annotations in Tests\n\nWhen transpilation depends on `is_type()` checks, tests need `annotate_types()`:\n\n```python\nfrom sqlglot.optimizer import annotate_types\n\n# Without annotation - is_type() returns False for literals\nexpr = self.validate_identity(\"SELECT BASE64_ENCODE('Hello World')\")\n\n# With annotation - types are inferred, is_type() works\nannotated = annotate_types(expr, dialect=\"snowflake\")\nself.assertEqual(annotated.sql(\"duckdb\"), \"SELECT TO_BASE64(ENCODE('Hello World'))\")\n```\n\n### 8. Use `find_ancestor` with Scope Boundaries\n\nWhen searching for ancestors, include scope boundaries to avoid crossing into parent queries:\n\n```python\n# Stop at Select to stay within current query scope\nancestor = expression.find_ancestor(exp.Where, exp.Having, exp.Select)\nif ancestor and not isinstance(ancestor, exp.Select):\n    # Found restricted context within current scope\n    ...\n```\n\n### 9. Use @unsupported_args for unsupported arguments\n\nWhen arguments are not supported do this:\n\n```python\n        @unsupported_args(\"ins_cost\", \"del_cost\", \"sub_cost\")\n        def levenshtein_sql(self, expression: exp.Levenshtein) -> str:\n```\n\n### 10. Keep Code Minimal\n\n- Remove unused imports, variables, and dead code\n- Don't add comments for obvious code\n- Don't add docstrings unless the function is complex or public API\n- Prefer inline expressions over intermediate variables when readable\n- Don't add backwards-compatibility shims for removed code\n\n### 11. Test Patterns\n\n- Add tests to the appropriate dialect test file (e.g., `tests/dialects/test_snowflake.py`)\n- Use `self.validate_all()` for cross-dialect tests\n- Use `self.validate_identity()` for round-trip tests\n- Don't add tests for functionality that already has coverage\n\n### 12. Ensure Test Validity\n\n- Make sure all tests added to tests/dialects/*.py actually run in the relevant databases, such as snowflake or duckdb\n"},"items":[{"name":"AGENTS.md","path":"AGENTS.md","title":"AGENTS.md","content":"# Contributing to [SQLGlot](https://github.com/tobymao/sqlglot/blob/main/README.md)\n## About SQLGlot\n\nSQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine written in pure Python. It supports 31+ SQL dialects and can transpile between them while preserving semantics. The codebase is performance-critical despite being pure Python, with an optional mypyc-compiled C extension for speed improvements (`sqlglotc/`).\n\n## Development Commands\nBefore contributing read CONTRIBUTING.md\n\n### Installation\n```bash\n# Basic installation\nmake install\n\n# Development installation (Python only, no C extension)\nmake install-dev\n\n# Development installation with mypyc C extension\nmake install-devc\n\n# Install pre-commit hooks\nmake install-pre-commit\n\n# With uv (faster):\nUV=1 make install-dev\n```\n\n### Testing\n```bash\n# Run all tests (pure Python, hides .so files during run)\nmake test\n\n# Run all tests with mypyc C extension (builds extension first)\nmake testc\n\n# Run only unit tests (skip integration tests, pure Python)\nmake unit\n\n# Run only unit tests with C extension\nmake unitc\n\n# Run specific test file\npython -m unittest tests.test_expressions\n\n# Run specific test class\npython -m unittest tests.test_expressions.TestExpressions\n\n# Run specific test method\npython -m unittest tests.test_expressions.TestExpressions.test_alias\n```\n\n### Linting & Type Checking\n```bash\n# Run linter and formatter only\nmake style\n\n# Run full checks (style + pure Python tests + C extension tests)\nmake check\n```\n\n### Benchmarks\n```bash\n# Run parsing benchmark\nmake bench\n\n# Run optimization benchmark\nmake bench-optimize\n```\n\n## Architecture Overview\n\nSQLGlot follows a classic compiler architecture with three main phases:\n\n### 1. Tokenizer (`tokens.py`)\n- Converts SQL strings into a sequence of tokens (lexical analysis)\n- Pure Python implementation in `tokens.py`; core logic in `tokenizer_core.py` (mypyc-compiled when using `[c]` extra)\n- Maps lexemes to `TokenType` enum values via `KEYWORDS` and `SINGLE_TOKENS` dictionaries\n- Dialects can override tokenizer behavior by customizing these mappings\n\n### 2. Parser (`parser.py`)\n- Converts tokens into an Abstract Syntax Tree (AST)\n- Uses recursive descent parsing approach\n- Parsing methods follow `_parse_*` naming convention (e.g., `_parse_create()`, `_parse_select()`)\n- Token matching methods: `_match()`, `_match_set()`, `_match_text_seq()`, `_match_texts()`\n- Helper methods for common patterns: `_parse_csv()`, `_parse_wrapped()`, `_parse_wrapped_csv()`\n- Maintains index/cursor with `_advance()` and `_retreat()` methods\n- Falls back to `exp.Command` for unparseable SQL (preserves original text)\n\n### 3. Generator (`generator.py`)\n- Converts AST back to SQL strings\n- Traverses AST recursively, generating SQL for each expression node\n- Two ways to customize generation:\n  - `TRANSFORMS` dictionary for single-line generations\n  - `<expr_name>_sql()` methods for complex generations\n- Helper methods: `expressions()`, `func()`, `rename_func()`\n- Use `sep()` and `seg()` for proper whitespace/newline handling in pretty-printed output\n\n### 4. Expressions (`expressions.py`)\n- Defines all AST node types as Python classes inheriting from `Expression`\n- Each expression represents a semantic SQL concept (e.g., `Select`, `Join`, `Column`)\n- Expressions can be traversed using `.find()`, `.find_all()`, `.walk()`, `.transform()`\n- Building SQL programmatically: use helper functions like `select()`, `from_()`, `where()`, etc.\n\n### 5. Dialects (`dialects/`)\n- 34 dialect implementations in `dialects/<dialect>.py`\n- Each dialect subclasses base `Dialect` and can override Tokenizer, Parser, and Generator\n- Base \"sqlglot\" dialect acts as a superset to minimize duplication\n- Dialect customization via:\n  - Feature flags (e.g., `SUPPORTS_IMPLICIT_UNNEST`)\n  - Token sets (e.g., `RESERVED_TOKENS`)\n  - `token -> Callable` mappings (e.g., `FUNCTIONS`, `STATEMENTS`)\n  - `Expression -> str` mappings in Generator\n\n### 6. Optimizer (`optimizer/`)\n- Canonicalizes and optimizes queries while preserving semantics\n- Applies sequential optimization rules (order matters!)\n- Key rules:\n  - `qualify`: Normalizes identifiers and qualifies all tables/columns (most important rule)\n  - `annotate_types`: Infers data types throughout the AST\n  - `pushdown_predicates`, `pushdown_projections`: Optimization rewrites\n  - `simplify`: Simplifies boolean expressions and arithmetic\n- Rules depend on schema information for best results\n- Optimizer performs logical optimization only (not physical/performance)\n\n### 7. Schema (`schema.py`)\n- Represents database structure (tables, columns, types)\n- Used by optimizer and lineage analysis\n- `MappingSchema` takes nested dict: `{\"table\": {\"col\": \"type\"}}`\n\n### 8. Lineage (`lineage.py`)\n- Traces column-level lineage through queries\n- Requires target query, upstream queries, and root table schemas\n- Builds linked list of `Node` objects representing data flow\n- Can visualize with `node.to_html()`\n\n## Key Concepts\n\n### The \"sqlglot\" Dialect\n- Base dialect that accommodates common syntax across all dialects\n- All other dialects extend this base\n- When adding multi-dialect features, prefer adding to base dialect to avoid duplication\n- Only add dialect-specific features to individual dialect classes\n\n### AST-First Approach\n- SQLGlot preserves _semantics_ not syntax\n- Parse SQL → AST (semantic representation) → Generate SQL in target dialect\n- This enables accurate cross-dialect transpilation\n- Comments are preserved on best-effort basis\n- See `posts/ast_primer.md` for detailed AST tutorial\n\n### Testing Philosophy\n- Comprehensive test suite in `tests/` directory\n- Dialect-specific tests in `tests/dialects/`\n- Tests are critical - \"robust test suite\" is a core feature\n- Use `tests/fixtures/` for test data\n- `tests/helpers.py` contains test utilities\n\n### Parser/Generator Symmetry\n- Parser: `token -> Callable` mappings (builds AST from tokens)\n- Generator: `Expression -> str` mappings (builds SQL from AST)\n- Customization follows similar patterns in both\n\n### Type Annotations\n- Type inference is crucial for some transpilations (e.g., `+` can mean addition or concatenation)\n- Optimizer's `annotate_types` rule propagates type information through AST\n- Requires schema information to work effectively\n\n## Common Usage Patterns\n\n### Reading SQL\n```python\nimport sqlglot\nexpression = sqlglot.parse_one(\"SELECT * FROM table\", dialect=\"spark\")\n```\n\n### Validate Function Expression\n```python\nimport sqlglot\ntree = sqlglot.parse_one(\"SELECT NULLIF(1, 2)\", dialect=\"snowflake\")\nif \"Anonymous\" in repr(tree):\n    print(\"Function expression exists\")\nelse:\n    print(\"Function expression does not exist\")\n```\n\n### Writing SQL\n```python\nexpression.sql(dialect=\"duckdb\", pretty=True)\n```\n\n### Building SQL Programmatically\n```python\nfrom sqlglot import select, condition\nselect(\"*\").from_(\"y\").where(condition(\"x=1\").and_(\"y=1\")).sql()\n```\n\n### Traversing AST\n```python\nfrom sqlglot import parse_one, exp\ntree = parse_one(\"SELECT a, b + 1 AS c FROM d\", dialect=\"dialect\")\nfor column in tree.find_all(exp.Column):\n    print(column.alias_or_name)\n```\n\n### Transforming AST\n```python\ndef transformer(node):\n    if isinstance(node, exp.Column) and node.name == \"a\":\n        return parse_one(\"FUN(a)\", dialect=\"dialect\")\n    return node\n\ntransformed = tree.transform(transformer)\n```\n\n## Development Guidelines\n\n- Follow [Conventional Commits](https://www.conventionalcommits.org/) for PR titles\n- If you are an agent, you must add your model [CLAUDE, CODEX, etc...] to the end of PR titles and commit messages.\n- Keep PRs minimal in scope - one well-defined change per PR\n- Add tests for non-trivial changes\n- Update docstrings if APIs change\n- Run `make check` before submitting\n- Use comments for complex logic only\n- Don't change license or license files in setup.py, there's nothing wrong with it.\n\n## Important Files\n\n- `posts/ast_primer.md`: Detailed AST tutorial\n- `posts/onboarding.md`: Architecture deep-dive (HIGHLY RECOMMENDED)\n- `.pre-commit-config.yaml`: Pre-commit hooks (ruff, ruff-format, mypy)\n- `pyproject.toml`: Project metadata and build config\n- `Makefile`: All development commands\n\n## Performance Considerations\n\n- Pure Python implementation with optional mypyc-compiled C extension (`sqlglotc/`)\n- Install with `pip install \"sqlglot[c]\"` for C extension speed boost\n- The `[c]` extra compiles core modules (`expression_core`, `tokenizer_core`, `parser_core`, etc.) via mypyc\n- Performance is a key feature despite Python implementation\n- Benchmarks compare against other SQL parsers - see `benchmarks/`\n- Avoid use of typing.Protocol, prefer Union Type and Duck Typing\n- Never suggest Pratt parsing as an optimization for the parser. The recursive descent approach is intentional and preferred.\n\n---\n\n## SQLGlot Coding Rules\n\nThe following patterns are based on PR review feedback. Follow these to minimize review iterations.\n\n### 1. Use Automatic Naming Convention for Generator Methods\n\n**Don't do this (module-level function with TRANSFORMS):**\n```python\ndef _my_func_sql(self: MyDialect.Generator, expression: exp.MyFunc) -> str:\n    ...\n\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: _my_func_sql,\n    }\n```\n\n**Don't do this (method with TRANSFORMS):**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.MyFunc: lambda self, e: self._my_func_sql(e),\n    }\n\n    def _my_func_sql(self, expression):\n        ...\n```\n\n**Do this (auto-discovered method):**\n```python\nclass Generator:\n    # No TRANSFORMS entry needed - automatic discovery by name\n\n    def myfunc_sql(self, expression: exp.MyFunc) -> str:\n        ...\n```\n\nGenerator methods named `<lowercase_expr_name>_sql` are automatically discovered.\n\nImportant: Only use TRANSFORMS for simple one-liners like `rename_func(\"OTHER_NAME\")` or lambdas or functions with multiple entry points. For any single entry point function, always use an auto-discovered method inside the Generator class.\n\nSQLGlot automatically applies transformations based on the structure of the name, but when this fails, you must rename the function.  This is only when the SQL name is not covered by auto mapping:\n\n**Do this:**\n```python\nclass Generator:\n    TRANSFORMS = {\n        exp.ArrayLength: rename_func(\"LENGTH\"),\n    }\n```\n\n**Don't do this:**\n```python\nexp.ArrayLength: lambda self, e: self.func(\"LENGTH\", e.this),\n```\n\n### 2. Use Existing Expression Classes, Not Anonymous\n\n**Don't do this:**\n```python\nfrom_base64 = exp.Anonymous(this=\"FROM_BASE64\", expressions=[input_expr])\n```\n\n**Do this:**\n```python\nfrom_base64 = exp.FromBase64(this=input_expr)\n```\n\nAlways check if an expression class exists in `expressions.py` before using `exp.Anonymous`. Anonymous should only be used for functions that don't have a dedicated class. Search for the function name in expressions.py first.\n\n### 3. SQL Generation: Choose the Right Approach\n\nUse the appropriate method based on complexity. From simplest to most complex:\n\n#### Level 1: Generator Helper Methods\nFor generating function calls in generator methods, use `self.func()`:\n```python\ndef myfunc_sql(self, expression):\n    # Don't: return self.sql(exp.Func(this=\"MY_FUNC\", expressions=[expression.this]))\n    # Do:\n    return self.func(\"MY_FUNC\", expression.this)\n```\n\n#### Level 2: Expression Builders\nFor building expressions, use helper functions instead of direct class construction:\n\n| Helper | Instead of | Benefits |\n|--------|-----------|----------|\n| `exp.func(\"name\", *args)` | `exp.Anonymous(...)` | Finds proper Func class |\n| `exp.array(e1, e2, ...)` | `exp.Array(expressions=[...])` | Parses automatically |\n| `exp.and_(e1, e2, ...)` | `exp.And(this=..., expression=...)` | Handles nesting |\n| `exp.or_(e1, e2, ...)` | `exp.Or(this=..., expression=...)` | Handles nesting |\n| `exp.case().when(cond, val).else_(default)` | `exp.Case(ifs=[...])` | Fluent interface |\n| `exp.cast(expr, \"TYPE\")` | `exp.Cast(this=..., to=...)` | Builds DataType |\n| `exp.column(\"col\", \"table\")` | `exp.Column(...)` | Handles identifiers |\n| `exp.null()` | `exp.Null()` | Simple factory |\n\nAlso use expression operators for cleaner code:\n```python\n# Arithmetic: exp.column(\"x\") + 1  instead of  exp.Add(this=..., expression=...)\n# Indexing:   arr[index]           instead of  exp.Bracket(this=arr, expressions=[index])\n# Comparison: arg.is_(exp.Null())  instead of  exp.Is(this=arg, expression=exp.Null())\n```\n\n#### Level 3: SQL Templates\nWhen expressions become complex, use templates with `exp.maybe_parse()` and `exp.replace_placeholders()`:\n\n```python\n# Define template with :placeholder syntax\nMY_TEMPLATE: exp.Expression = exp.maybe_parse(\n    \"CASE WHEN :arg IS NULL THEN NULL ELSE :result END\"\n)\n\n# In generator method\ndef myfunc_sql(self, expression):\n    result = exp.replace_placeholders(\n        self.MY_TEMPLATE.copy(),\n        arg=expression.this,\n        result=some_expression,\n    )\n    return self.sql(result)\n```\n\n#### Avoid: F-strings with SQL Fragments\nYou should rarely, if ever, build SQL with f-strings - it breaks quoting, escaping, and dialect handling:\n```python\n# NEVER do this:\ndef my_func_sql(self, expression):\n    return f\"CAST({self.sql(expression.this)} AS TIME)\"\n\n# Do this instead:\ndef my_func_sql(self, expression):\n    return self.sql(exp.cast(expression.this, \"TIME\"))\n```\n\n### 4. Type Checking: `is_string` vs `is_type()`\n\nThese serve **different purposes**:\n\n**`is_type()`** - Semantic type check:\n```python\n# Returns True if expression's type is text (columns, function results, etc.)\n# Requires annotate_types() to populate type info\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    ...\n```\n\n**`is_string`** - Syntactic check for string literals:\n```python\n# Returns True only for literal strings like 'hello'\n# Works without type annotation\nif arg.is_string:\n    value = arg.name  # Extract the string value\n```\n\n\n**When to use each:**\n\n| Use Case | Method |\n|----------|--------|\n| Check if node is a string literal to extract its value | `is_string` |\n| Check if node is a literal vs column/expression | `is_string` |\n| Check semantic type (works for columns, functions) | `is_type()` |\n| Cover both literals and typed expressions | `is_string or is_type()` |\n\n**Combined pattern (from `length_sql`):**\n```python\n# Fast check for string literals (no annotation needed)\nif arg.is_string:\n    return self.func(\"LENGTH\", arg)\n\n# For non-literals, get type info if needed\nif not arg.type:\n    arg = annotate_types(arg, dialect=self.dialect)\n\n# Then check semantic type\nif arg.is_type(*exp.DataType.TEXT_TYPES):\n    return self.func(\"LENGTH\", arg)\n```\n\n**Don't do direct type comparisons:**\n```python\n# Bad\nif input_expr.type and input_expr.type.this in exp.DataType.TEXT_TYPES:\n\n# Good\nif input_expr.is_type(*exp.DataType.TEXT_TYPES):\n```\n\n### 5. Use `to_py()` for Literal Value Extraction\n\n**Don't do this:**\n```python\nif isinstance(arg, exp.Literal):\n    value = int(arg.this.strip(\"'\"))\n```\n\n**Do this:**\n```python\nif isinstance(arg, exp.Literal) and arg.is_number:\n    value = int(arg.to_py())\n```\n\n### 6. Avoid Compile-Time NULL Checks\n\nDon't check for `exp.Null()` or literal NULL values in Python during transpilation. NULL handling should happen at query time in the generated SQL using `IS NULL` checks.\n\n**Don't do this:**\n```python\ndef myfunc_sql(self, expression):\n    # Bad: checking for literal NULL at transpile time\n    if any(isinstance(arg, exp.Null) for arg in expression.expressions):\n        return self.sql(exp.Null())\n```\n\n**Do this:**\n```python\n# Good: generate SQL that handles NULL at query time\nTEMPLATE = exp.maybe_parse(\"CASE WHEN :arg IS NULL THEN NULL ELSE ... END\")\n```\n\nCompile-time checks only handle literal `NULL` values in the SQL text, not NULL values that come from columns, parameters, or expressions at runtime. Generate SQL with `IS NULL` checks to handle all cases.\n\n### 7. Type Annotations in Tests\n\nWhen transpilation depends on `is_type()` checks, tests need `annotate_types()`:\n\n```python\nfrom sqlglot.optimizer import annotate_types\n\n# Without annotation - is_type() returns False for literals\nexpr = self.validate_identity(\"SELECT BASE64_ENCODE('Hello World')\")\n\n# With annotation - types are inferred, is_type() works\nannotated = annotate_types(expr, dialect=\"snowflake\")\nself.assertEqual(annotated.sql(\"duckdb\"), \"SELECT TO_BASE64(ENCODE('Hello World'))\")\n```\n\n### 8. Use `find_ancestor` with Scope Boundaries\n\nWhen searching for ancestors, include scope boundaries to avoid crossing into parent queries:\n\n```python\n# Stop at Select to stay within current query scope\nancestor = expression.find_ancestor(exp.Where, exp.Having, exp.Select)\nif ancestor and not isinstance(ancestor, exp.Select):\n    # Found restricted context within current scope\n    ...\n```\n\n### 9. Use @unsupported_args for unsupported arguments\n\nWhen arguments are not supported do this:\n\n```python\n        @unsupported_args(\"ins_cost\", \"del_cost\", \"sub_cost\")\n        def levenshtein_sql(self, expression: exp.Levenshtein) -> str:\n```\n\n### 10. Keep Code Minimal\n\n- Remove unused imports, variables, and dead code\n- Don't add comments for obvious code\n- Don't add docstrings unless the function is complex or public API\n- Prefer inline expressions over intermediate variables when readable\n- Don't add backwards-compatibility shims for removed code\n\n### 11. Test Patterns\n\n- Add tests to the appropriate dialect test file (e.g., `tests/dialects/test_snowflake.py`)\n- Use `self.validate_all()` for cross-dialect tests\n- Use `self.validate_identity()` for round-trip tests\n- Don't add tests for functionality that already has coverage\n\n### 12. Ensure Test Validity\n\n- Make sure all tests added to tests/dialects/*.py actually run in the relevant databases, such as snowflake or duckdb\n","category":"root","tokens":4437}]}