To convert each SQL expression in source model, PostgreSQL Migrator relies on transqlate. This page describes how transqlate implements dialect conversion.

transqlate implements an universal SQL parser and converts to PostgreSQL dialect by rewriting an AST.

Lexing

First step of SQL analysis is lexing the code in tokens like literal values, identifiers, keywords and punctuations. transqlate attaches whitespaces to each token. A token has white spaces before and after the meaningful string. Comments are attached to the following token. Comment right after the token, until first new line, is attached to the previous token.

-- #1 comment attached to SELECT
SELECT -- #2 comment attached to SELECT
  -- #3 comment attached to TRUNC
  TRUNC(d) AS col, /* #4 comment attached to comma
                    */

Thus, when renaming or moving TRUNC, comment #3 will move with.

transqlate preserves char by char how you write a token. For example, integer base and identifier case are preserved.

SELECT x'ff' as COLUMN

There is no dialect. Only lexing parameter like identifier normalization: Oracle uppers identifier case while PostgreSQL lowers them. Lexing is the foundation to effective transpilation. Unlike regular expression, a good lexer preserves comments, style and avoids false-matches.

Parsing

transqlate parser builds an AST streamlined for transpilation. The parser is universal: it parses any dialects and accepts mixed dialect. This allows to handle iterative migration of code.

transqlate parser handles SQL expression as well as DDL and procedural structures.

While parsing, transqlate disambiguates keywords and identifiers. MySQL accepts strings literal as identifiers. This may leads to very ambiguous syntax. transqlate has some heuristics to disambiguate dialect. e.g.: MySQL has DECLARE statement while standard SQL uses DECLARE for a sub-block:

BEGIN
  DECLARE v INT; -- MySQL in-body declaration
  DECLARE
    v INT; -- Standard sub-block.b
  BEGIN
    -- ... sub-block
  END;
END;

In such case, transqlate dares to rely on presence of newline to disambiguate.

Since SQL is a very complex language with an infinity of language structures, transqlate parser knows its limits and tries to overcome them. transqlate has a strong control structure analysis but on unknown syntax, it falls back to searching next semi-colon. This produces generic statements.

Unlike specific statement, a generic statement is a plain list of token ended by semi-colon, without semantic. This prevents semantic rewriting. This, PostgreSQL Migrator warns you when transqlate falled back to generic statement. The transqlate: unparsed statement annotation invites you to carefully review the code for idiomatism.

Splitting and analysis

Oracle packages holds a lot of SQL code. transqlate knows how to parse CREATE PACKAGE DDL. PostgreSQL Migrator walk the full AST of CREATE PACKAGE statement to extract variable declaration, routines declaration and definition, and init section.

Even if transqlate missed conversion, having an effective split of package body is a must have feature. PostgreSQL Migrator also leverages transqlate AST to analyze idomiatisms like planner hints.

Rewriting

Once SQL semantic is formalized in the AST, a set of conversion rules is applied.

Two kinds of rules exist: node rules and token rules. Node rule matches an AST node by grammar semantic: function call, select query, etc. and searches idiomatism in them. Token rule applies casing and styling conversion.

A rule may fail to rewrite idiomatism to PostgreSQL dialect: the grammar is ambiguous, a feature is partially implemented or a bug occured. Such conversion errors are raised as an annotation.

In transqlate, a dialect is actually a set of rules targeting a set of idiomatism. e.g. Oracle dialect will search for join mark ((+)), START WITH/CONNECT BY and other Oracle idiomatisms.

Command line

You may want to transpile a file or debug transpiler. Use transqlate CLI for this. Beware than transqlate CLI does not read pg_migrate.toml and does not apply renaming from PostgreSQL Migrator conversion. You must specify -dialect and -preserve-case option manually.