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
GROUPBY
client,
YEAR(orderDate)
HAVING
sum(amount)>2000

MySQL：

SELECT
ordersenti0_.ClientAScol_0_0_,
YEAR(ordersenti0_.OrderDate)AScol_1_0_,
sum(ordersenti0_.Amount)AScol_2_0_,
count(ordersenti0_.OrderID)AScol_3_0_
FROM
orders ordersenti0_
GROUPBY
ordersenti0_.Client,
YEAR(ordersenti0_.OrderDate)
HAVING
sum(ordersenti0_.Amount)>2000

Oracle：

SELECT
ordersenti0_.ClientAScol_0_0_,
EXTRACT(YEARFROMordersenti0_.OrderDate)AScol_1_0_,
sum(ordersenti0_.Amount)AScol_2_0_,
count(ordersenti0_.OrderID)AScol_3_0_
FROM
system.orders ordersenti0_
GROUPBY
ordersenti0_.Client,
EXTRACT(YEARFROMordersenti0_.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,INTERVAL3DAY)AScol_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(CASEWHENNATION ='CHINA'THENVOLUMEELSE0END) /SUM(VOLUME)ASMKT_SHARE
FROM
(
SELECT
YEAR(O_ORDERDATE)ASO_YEAR,
L_EXTENDEDPRICE * (1- L_DISCOUNT)ASVOLUME,
N2.N_NAMEASNATION
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(CASEWHENNATION ='CHINA'THENVOLUMEELSE0END) /SUM(VOLUME)ASMKT_SHARE
FROM
(
SELECT
EXTRACT(YEARFROMO_ORDERDATE)ASO_YEAR,
L_EXTENDEDPRICE * (1- L_DISCOUNT)ASVOLUME,
N2.N_NAMEASNATION
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:

SELECTCLIENT,YEAR(ORDERDATE),SUM(AMOUNT)
,COUNT(ORDERID)
FROMORDERS
GROUPBYCLIENT,YEAR(ORDERDATE)
HAVINGSUM(AMOUNT) >2000

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

SELECTCLIENT,YEAR(ORDERDATE),SUM(AMOUNT)
,COUNT(ORDERID)
FROMORDERS
GROUPBYCLIENT,YEAR(ORDERDATE)
HAVINGSUM(AMOUNT) >2000

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

SELECTCLIENT,EXTRACT(YEARFROMORDERDATE),SUM(AMOUNT)
,COUNT(ORDERID)
FROMORDERS
GROUPBYCLIENT,EXTRACT(YEARFROMORDERDATE)
HAVINGSUM(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 +INTERVAL3DAY
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.