The technologies for SQL migration

The function syntaxes of various types of databases are more or less different from each other. In order to make the SQL query statements of the same functionality be executed successfully in different types of databases, we need to translate these differentiated function syntaxes that are available in each database, and that is SQL migration. This article will explore several technologies for SQL migration and compare them in detail.

ORM technology

The queries written by programmers can be converted to SQLs of different databases with ORM, which equals certain migratability. But ORM is only suitable for simple SQL in OLTP scenario. As for OLAP scenario, it can hardly migrates the complex SQL.

Hibernate and Mybatis are two of the most common ORM technology. Mybatis is also known as semi-automatic ORM, which is mainly because the programmers need to write native SQL by themselves. That makes Mybatis hardly for migration, even with third-party expansion packages like Mybatis-plus and Mybatis-ext, Mybatis’ input method can be improved but its migratability is still far less than Hibernate. While Hibernate can use HQL to automatically generate corresponding SQL based on the database dialect in the configuration file, so it can perform migration for some simple situations (which are directly supported in Hibernate), for example:

HQL：

SELECT
            client,
            YEAR(orderDate),
            sum(amount),
            count(orderId)
FROM
            OrdersEntity
GROUP BY
            client,
            YEAR(orderDate)
HAVING
            sum(amount)>2000

MySQL：

SELECT
            ordersenti0_.Client AS col_0_0_,
            YEAR(ordersenti0_.OrderDate) AS col_1_0_,
            sum(ordersenti0_.Amount) AS col_2_0_,
            count(ordersenti0_.OrderID) AS col_3_0_
FROM
            orders ordersenti0_
GROUP BY
            ordersenti0_.Client,
            YEAR(ordersenti0_.OrderDate)
HAVING
            sum(ordersenti0_.Amount)>2000

Oracle：

SELECT
            ordersenti0_.Client AS col_0_0_,
            EXTRACT(YEAR FROM ordersenti0_.OrderDate) AS col_1_0_,
            sum(ordersenti0_.Amount) AS col_2_0_,
            count(ordersenti0_.OrderID) AS col_3_0_
FROM
            system.orders ordersenti0_
GROUP BY
            ordersenti0_.Client,
            EXTRACT(YEAR FROM ordersenti0_.OrderDate)
HAVING
            sum(ordersenti0_.Amount)>2000

In the SQL generated in HQL, the YEAR(d) function is used to correspond to MySQL and the EXTRACT(YEAR FROM d) function is used to Oracle, which can achieve SQL migration.

However, Hibernate may not perform migration successfully in some more complicated circumstances.

For example, we need to register custom functions according to the current database before generating SQL from HQL when using functions not directly supported in HQL itself, using MySQL as an example:

The key code to register custom functions:

registerFunction("udf_dateadd", new SQLFunctionTemplate(DateType.INSTANCE,"date_add(?1,INTERVAL ?2 DAY)"));

HQL：

SELECT
            udf_dateadd (orderDate,3)
FROM
            OrdersEntity

MySQL：

SELECT
            date_add(ordersenti0_.OrderDate,INTERVAL 3 DAY) AS col_0_0_
FROM
            orders ordersenti0_

But there is no date_add function in Oracle. If the database turns to Oracle, then we need to register other new custom functions based on the function syntax of Oracle, which can not be migrated automatically.

There are also some operations that cannot be described in HQL, such as the subquery in FROM:

SELECT
            orderId,
            m
FROM
            (
            SELECT
                        orderId,
                        MONTH(orderDate) m
            FROM
                        OrdersEntity) t1

In order to solve such a problem, we usually execute native SQL to use SQLQuery interfaces, which will also lose the ability of migration.

SQL conversion tools

With the help of some tools to migrate SQL, we can directly translate the current original SQL (like Oracle SQL) to target SQL (like MySQL SQL). Even in some complex situations like nested subqueries, the conversion can still be achieved.

For example, in https://www.sqlines.com/online:

Select the database type of the original SQL as MySQL, and the SQL to be translated is:

SELECT
            O_YEAR ,
            SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
            (
            SELECT
                        YEAR (O_ORDERDATE) AS O_YEAR,
                        L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
                        N2.N_NAME AS NATION
            FROM
                        PART,
                        SUPPLIER,
                        …

Select the database type of the target SQL as Oracle, and click the “convert” button, then the original SQL is converted to SQL that can be executed by the target database:

SELECT
            O_YEAR ,
            SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
            (
            SELECT
                        EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR,
                        L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
                        N2.N_NAME AS NATION
            FROM
                        PART,
                        SUPPLIER,
                        …

Still, the method has its defects. Such tools are mostly independent applets which only support output of the target SQL on the command line or in the result file, and lack a programmatic interface for integration in various development tools.

esProc SPL

SPL has designed a set of standard SQL query syntax which has many built-in functions (and more are being added) to describe more common operations. And there is a sqltranslate function in SPL, which can translate the standard SQL to SQLs for different databases for the purpose of database migration.

Take this standard SQL for example:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000

Translate it with .sqltranlate("MYSQL") and the result will be:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000

Whereas the returned result of using .sqltranslate("ORACLE") will be:

SELECT CLIENT, EXTRACT(YEAR FROM ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, EXTRACT(YEAR FROM ORDERDATE)
HAVING SUM(AMOUNT) > 2000

As we can see, the standard function is able to correctly select the appropriate functions according to the databases.

In addition, SPL can also handle with those operations not supported in HQL itself like INTERVAL n DAY:

The standard SQL is:

SELECT
            ADDDAYS(ORDERDATE,3)
FROM
            ORDERS

Translate it to MySQL SQL as:

SELECT
            ORDERDATE + INTERVAL 3 DAY
FROM
            ORDERS

Translate it to Oracle SQL as:

SELECT
            ORDERDATE + NUMTODSINTERVAL(3,'DAY')
FROM
            ORDERS

In order to achieve SQL migration, SPL just translates the functions rather than the statements (copied as they are) in standard SQL so that the standard SQL is able to describe more operations. For example, the following subquery will not change and can be executed normally no matter which database SQL it is translated to.

SELECT
            ORDERID,
            M
FROM
            (
            SELECT
                        ORDERID,
                        MONTH(ORDERDATE) M
            FROM
                        ORDERS) T1

SPL can be easily integrated with JAVA to enable migration in applications. For further information, please refer to How to Call an SPL Script in Java.