Metadata-Version: 2.4
Name: trinofy
Version: 0.1.1
Summary: MySQL → Trino compiler for LLM-generated analytical SQL.
Project-URL: Homepage, https://github.com/ushanzzz/trinofy
Project-URL: Repository, https://github.com/ushanzzz/trinofy
Project-URL: Issues, https://github.com/ushanzzz/trinofy/issues
Author-email: Ushan Balasooriya <ushanbala@outlook.com>
License: MIT License
        
        Copyright (c) 2026 Ushan Balasooriya
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
License-File: LICENSE
Keywords: compiler,llm,mysql,sql,sqlglot,trino
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Compilers
Requires-Python: >=3.10
Requires-Dist: sqlglot>=25.0
Description-Content-Type: text/markdown

<p align="center">
  <strong>MySQL &rarr; Trino SQL compiler for LLM-generated analytical queries</strong>
</p>

<p align="center">
  <a href="#install">Install</a> &bull;
  <a href="#quick-start">Quick Start</a> &bull;
  <a href="#how-it-works">How It Works</a> &bull;
  <a href="#supported-transforms">Transforms</a> &bull;
  <a href="#warning-codes">Warnings</a> &bull;
  <a href="#license">License</a>
</p>

---

**trinofy** is a compiler that rewrites MySQL-flavored SQL into valid [Trino](https://trino.io/) SQL. It is purpose-built for the **LLM-to-SQL pipeline**: when large language models generate analytical queries, they default to MySQL dialect. trinofy sits as a middleware layer, translating that output into Trino-compatible SQL while surfacing any lossy or ambiguous rewrites as structured warnings.

It extends [sqlglot](https://github.com/tobymao/sqlglot) with a multi-phase rule engine that handles the real-world dialect gaps sqlglot doesn't cover.

## Install

```bash
pip install trinofy
```

Requires **Python 3.12+**. The only dependency is `sqlglot`.

## Quick Start

```python
from trinofy import compile_mysql_to_trino

result = compile_mysql_to_trino(
    """
    SELECT
        SUBSTRING_INDEX(name, ',', 1)   AS last_name,
        GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ', ') AS tags,
        UNIX_TIMESTAMP(created_at)       AS epoch
    FROM users
    WHERE created_at >= '2026-01-01 00:00:00'
    """
)

print(result.trino_sql)
```

**Output:**

```sql
SELECT
    array_join(slice(split(name, ','), 1, 1), ',') AS last_name,
    array_join(array_distinct(array_agg(tag ORDER BY tag)), ', ') AS tags,
    to_unixtime(created_at) AS epoch
FROM users
WHERE created_at >= CAST('2026-01-01 00:00:00' AS TIMESTAMP)
```

```python
for w in result.warnings:
    print(f"[{w.code}] {w.message}")
```

```
[length_semantics] MySQL LENGTH() counts bytes, Trino length() counts characters...
```

Pass `pretty=True` for formatted output: `compile_mysql_to_trino(sql, pretty=True)`.

## How It Works

trinofy compiles MySQL SQL to Trino SQL in **three phases**:

```
  MySQL SQL (from LLM)
         |
    ┌────┴────┐
    │ Phase 1 │  Pre-Parse Text Rules
    │         │  Normalize raw SQL before parsing
    └────┬────┘
         |
    ┌────┴────┐
    │ Phase 2 │  AST Transformation Rules
    │         │  Walk the sqlglot AST, rewrite nodes
    └────┬────┘
         |
    ┌────┴────┐
    │ Phase 3 │  Post-Emit Text Rules
    │         │  Patch the generated Trino SQL string
    └────┬────┘
         |
  Trino SQL + Warnings
```

### Phase 1 &mdash; Pre-Parse

Raw text fixes before sqlglot parses the SQL:

| Rule | What it does |
|---|---|
| **C-escape detection** | Flags MySQL escape sequences (`\n`, `\t`, etc.) inside string literals. Warns but does not rewrite to avoid silent data corruption. |
| **Duplicate JOIN collapse** | Fixes LLM typos like `INNER INNER JOIN` &rarr; `INNER JOIN` so sqlglot can parse the query. |

### Phase 2 &mdash; AST Transformations

The core compiler walks every node in the sqlglot AST and applies dialect-specific rewrites:

<details>
<summary><strong>Date &amp; Time</strong></summary>

| MySQL | Trino |
|---|---|
| `WEEKDAY(d)` | `day_of_week(d) - 1` (preserves Mon=0..Sun=6) |
| `DAYNAME(d)` | `date_format(d, '%W')` |
| `MAKEDATE(y, doy)` | `date_add('day', doy - 1, cast(format('%04d-01-01', y) as date))` |
| `TIME_TO_SEC(t)` | `hour(t)*3600 + minute(t)*60 + second(t)` |
| `UNIX_TIMESTAMP()` | `to_unixtime(current_timestamp)` |
| `UNIX_TIMESTAMP(ts)` | `to_unixtime(cast(ts AS TIMESTAMP))` (with warning) |
| `REGEXP_INSTR(s, p)` | `regexp_position(s, p)` |
| `WEEK(d, mode)` | `week(d)` &mdash; warns on non-ISO modes |
| `DATE_FORMAT` / `STR_TO_DATE` | Flags unsupported specifiers (`%D`, `%U`, `%u`, `%V`, `%w`, `%X`) |

</details>

<details>
<summary><strong>String Operations</strong></summary>

| MySQL | Trino |
|---|---|
| `SUBSTRING_INDEX(s, delim, n)` | `array_join(slice(split(s, delim), ...), delim)` &mdash; handles positive, negative, zero, and dynamic counts |
| `FIELD(x, s1, s2, ...)` | `CASE x WHEN s1 THEN 1 WHEN s2 THEN 2 ... ELSE 0 END` |
| `FIND_IN_SET(str, csv)` | `coalesce(array_position(split(csv, ','), str), 0)` |
| `GROUP_CONCAT(col)` | `array_join(array_agg(col), sep)` &mdash; supports `DISTINCT` and `ORDER BY` |
| `TRUNCATE(x, d)` | `sign(x) * floor(abs(x) * power(10, d)) / power(10, d)` |
| `HEX(x)` | `to_hex(cast(x AS varbinary))` for strings; `format('%X', n)` for numeric literals |
| `LENGTH(x)` | Flags byte-vs-character semantics difference |

</details>

<details>
<summary><strong>Casts &amp; Types</strong></summary>

| MySQL | Trino |
|---|---|
| `CAST(x AS UNSIGNED)` | `CAST(x AS BIGINT)` (Trino has no unsigned types) |
| `CAST(x AS BINARY)` | `CAST(x AS VARBINARY)` |
| String vs Timestamp comparison | Auto-wraps string literals in `CAST(... AS TIMESTAMP)` to prevent `TYPE_MISMATCH` errors |

</details>

<details>
<summary><strong>JSON</strong></summary>

| MySQL | Trino |
|---|---|
| `JSON_UNQUOTE(JSON_EXTRACT(col, path))` | `json_extract_scalar(col, path)` |

</details>

<details>
<summary><strong>Miscellaneous</strong></summary>

| MySQL | Trino |
|---|---|
| `@var` session variables | Flagged as unsupported |
| `CROSS JOIN ... ON` | Rewritten to `JOIN ... ON` (INNER JOIN) |

</details>

### Phase 3 &mdash; Post-Emit

After sqlglot generates Trino SQL, text-level patches fix output that the AST layer can't address:

| Rule | What it does |
|---|---|
| **AT TIME ZONE operator** | Rewrites sqlglot's `AT_TIMEZONE(expr, 'zone')` function call into Trino's `(expr AT TIME ZONE 'zone')` operator syntax. Handles nesting iteratively. |

## Warning Codes

Every transform can emit structured `CompileWarning(code, message, snippet)` objects. These tell you when a rewrite may have changed semantics or needs human review.

| Code | Severity | Meaning |
|---|---|---|
| `mysql_c_escape` | Review | String contains MySQL C-style escape (`\n`, `\t`, etc.). Trino doesn't interpret these. |
| `duplicate_join_keyword_collapsed` | Info | Doubled JOIN keyword collapsed (LLM typo). |
| `week_mode_unsupported` | Review | `WEEK(d, mode)` with a non-ISO mode. Trino only supports ISO week numbering. |
| `date_format_specifier_unsupported` | Review | `DATE_FORMAT`/`STR_TO_DATE` uses a format specifier Trino doesn't support. |
| `unix_timestamp_string` | Review | `UNIX_TIMESTAMP(str)` &mdash; string-to-timestamp format may need verification. |
| `hex_polymorphic` | Review | `HEX()` on a non-literal &mdash; type (number vs string) is ambiguous without a catalog. |
| `length_semantics` | Review | `LENGTH()` counts bytes in MySQL but characters in Trino. |
| `substring_index_dynamic_count` | Review | `SUBSTRING_INDEX` with a non-literal count &mdash; verify runtime value. |
| `session_variable_unsupported` | Error | MySQL session variable (`@var`) has no Trino equivalent. |
| `cross_join_with_on_rewritten` | Info | `CROSS JOIN ... ON` rewritten to `INNER JOIN ... ON`. |

## The Gotcha Catalog

trinofy ships with a comprehensive **[catalog.md](trinofy/catalog.md)** documenting 50+ MySQL&mdash;Trino dialect differences across date/time, strings, JSON, comparison, regex, casts, math, booleans, identifiers, and session variables. Each entry records the MySQL syntax, Trino equivalent, and whether sqlglot handles it or trinofy's rule engine is needed.

## Why Not Just Use sqlglot?

[sqlglot](https://github.com/tobymao/sqlglot) is an excellent SQL transpiler and forms the foundation of trinofy. However, it doesn't cover every MySQL&mdash;Trino gap. trinofy adds value where sqlglot falls short:

- **`GROUP_CONCAT` with `ORDER BY` or `DISTINCT`** &mdash; sqlglot emits `LISTAGG` which doesn't support these in Trino. trinofy uses `array_join(array_agg(...))` instead.
- **`SUBSTRING_INDEX`** &mdash; No direct Trino equivalent; requires `split` + `slice` + `array_join`.
- **`WEEKDAY` / `DAYNAME` / `MAKEDATE`** &mdash; Semantic differences in numbering or missing functions.
- **String-to-timestamp comparison casting** &mdash; MySQL auto-casts strings in comparisons; Trino throws `TYPE_MISMATCH`.
- **`AT TIME ZONE` operator** &mdash; sqlglot emits a function call; Trino requires operator syntax.
- **Pre-parse normalization** &mdash; LLM-specific typos like doubled JOIN keywords.
- **Structured warnings** &mdash; Every ambiguous or lossy transform is surfaced, not silently applied.

## Development

```bash
# Clone the repository
git clone https://github.com/yourname/trinofy.git
cd trinofy

# Install in development mode
pip install -e .

# Build distribution
pip install build
python -m build
```

### Project Structure

```
trinofy/
├── __init__.py          # Public API: compile_mysql_to_trino, CompileResult, CompileWarning
├── compile.py           # Core compiler pipeline (parse → transform → generate)
├── warnings.py          # CompileWarning dataclass
├── catalog.md           # MySQL ↔ Trino gotcha reference
└── rules/               # Translation rule engine
    ├── pre_parse.py     # Phase 1: raw SQL text fixes
    ├── datetime.py      # Date/time function rewrites
    ├── string_ops.py    # String function rewrites
    ├── casts.py         # Type cast rewrites
    ├── json_ops.py      # JSON function rewrites
    ├── misc.py          # Session vars, CROSS JOIN fix
    └── text_fallback.py # Phase 3: post-emit SQL patches
```

## License

MIT &mdash; see [LICENSE](LICENSE).

---

<p align="center">
  Built for the <strong>LLM-to-SQL</strong> generation pipeline.<br>
  <em>Because LLMs speak MySQL, but your lakehouse speaks Trino.</em>
</p>
