Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered.
Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools?
Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches.
For example：
Suppose SQL PIVOT function is as follows :
SELECT *
FROM table2222 PIVOT
(
SUM(sales) AS ss1,
SUM(cogs) AS sc
FOR (yr, qtr)
IN (
(2001, 'Q1'),
(2001, 'Q2'),
(2001, 'Q3'),
(2001, 'Q4')
)
) tmp
;
Using ZGLanguage conversion rules, execute the conversion to obtain the result :
SELECT *
FROM
(
select ###,###,###
SUM(case when yr=2001 and qtr='Q1' then sales else null end ) AS "2001_Q1_ss1",
SUM(case when yr=2001 and qtr='Q2' then sales else null end ) AS "2001_Q2_ss1",
SUM(case when yr=2001 and qtr='Q3' then sales else null end ) AS "2001_Q3_ss1",
SUM(case when yr=2001 and qtr='Q4' then sales else null end ) AS "2001_Q4_ss1",
SUM(case when yr=2001 and qtr='Q1' then cogs else null end ) AS "2001_Q1_sc",
SUM(case when yr=2001 and qtr='Q2' then cogs else null end ) AS "2001_Q2_sc",
SUM(case when yr=2001 and qtr='Q3' then cogs else null end ) AS "2001_Q3_sc",
SUM(case when yr=2001 and qtr='Q4' then cogs else null end ) AS "2001_Q4_sc"
from table2222
where (yr, qtr) IN
(
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4')
)
group by ###,###,###
) tmp
;
The conversion rules is as follows :
__DEF_FUZZY__ Y
__DEF_DEBUG__ N
__DEF_CASE_SENSITIVE__ N
__DEF_LINE_COMMENT__ --
__DEF_LINES_COMMENT__ /* */
__DEF_STR__ __IF_KW__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_
__DEF_PATH__ __FROM_PIVOT_2_1__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__ __//__ sum ....
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
: x2 @ | )
------------------------------------------------------------------
1 : frm @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __\b__
: y1 @ | __COLS_4_FOR__
: y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
1 : for2 @ | where
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __VALUE_4_IN__
e : dh7 @ | ,
1 : y6 @ | )
: x2 @ | )
__DEF_PATH__ __FROM_PIVOT_2_2__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
e : dh1 @ | ,
1 : for2 @ %__IF_KW__ | for
: y1 @ | __COLS_4_FOR__
: in2 @ | in
: y5 @ | (
N : y3 @ | __COLS_VALUES__
e : dh7 @ | ,
1 : y6 @ | )
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
------------------------------------------------------------------
1 : frm @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ | as
: colas @ | __NAME__
* : y3 @ | __COLS_VALUES__
e : y3 @ | ,
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
__DEF_PATH__ __FROM_PIVOT_2_3__
1 : frm @ %__IF_KW__ | from
: tab @ | __TABLE_NAME__
: ssl @ + __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ %__IF_KW__ CAN_SKIP | as
: colas @ | __NAME__
: cw @ | __CASE_WHEN__
: as2 @ | as
: y2 @ | __VALUE_2_COL__
e : y3 @ | ,
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x2 @ | )
--------------------------------------------------------------
1 : frm @ | from
: x1 @ | (
: x1 @ STRING | select ###,###,###
N : fun @ | __NAME__
: fs @ | (
: cw @ | __CASE_WHEN__
: col1 @ | __NAME__
: col1 @ STRING | else null end
: fe @ | )
: as1 @ | as
: y2 @ | __VALUE_2_COL__
: colas @ \ __NAME__
: colas @ \ "
e : y3 @ | ,
1 : pvt @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
1 : where @ | where
: y11 @ | __COLS_4_FOR__
: in21 @ | in
: y51 @ | (
N : y31 @ | __VALUE_4_IN__
e : dh71 @ | ,
1 : y61 @ | )
: x1 @ STRING | group by ###,###,###
: x2 @ | )
__DEF_SUB_PATH__ __VALUE_2_COL__
N : x1 @ | __INT__
+ : x2 @ | '
: x3 @ | __ANY__
: x4 @ | '
------------------------------------------------------------------
1 : x1 @ | "
: x3 @ | "
N : x1 @ \ __INT__
: x3 @ \ __ANY__
: x1 @ \ _
: x3 @ \ _
__DEF_SUB_PATH__ __CASE_WHEN__
N : x1 @ | __NAME__
: x2 @ | =
: x3 @ | __INT__
: x4 @ + __STRING__
e : x5 @ | and
------------------------------------------------------------------
1 : x1 @ STRING | case when
N : x1 @ | __NAME__
: x2 @ | =
: x3 @ | __INT__
: x4 @ | __STRING__
e : x5 @ | and
1 : x1 @ | then
__DEF_SUB_PATH__ __COLS_VALUES__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | )
: y1 @ | (
N : y2 @ | __INT__
: y3 @ + __STRING__
e : y4 @ | ,
1 : y5 @ | )
----------------------------------------------------------------------
N : x2 @ | __NAME__
: x2 @ / =
: y2 @ / __INT__
: y3 @ / __STRING__
e : x2 @ | and
1 : x2 @ | as
N : y2 @ | __INT__
: y3 @ | __STRING__
__DEF_SUB_PATH__ __COLS_4_FOR__
1 : x1 @ | (
N : x2 @ | __NAME__
e : x3 @ | ,
1 : x4 @ | )
__DEF_SUB_PATH__ __VALUE_4_IN__
1 : x1 @ | (
N : x2 @ | __INT__
: x3 @ + __STRING__
e : x4 @ | ,
1 : x5 @ | )
__DEF_SUB_PATH__ __TABLE_NAME__
1 : srctab @ | __NAME__
+ : schema @ | __NAME__
: pp @ | .
: srctab2 @ | __NAME__
__DEF_SUB_PATH__ __SUB_SELECT__
1 : x1 @ | __SUB__
__DEF_PATH__ __SUB__
1 : x1 @ | (
N : x2 @ | __ALL_STR__
: x3 @ + __SUB__
1 : x4 @ | )
__DEF_STR__ __ALL_STR__
<1,20000>
[1,20000]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789`~!@#$%^&*-_+={}[]\|:;'"<,>.?/
__DEF_STR__ __NAME__
<1,100>
[1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz_??
[0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_??
[NO] create insert update delete truncate drop merge table select inner left join on from where group order partition by having union all with as set between and or like in is not null case when then pivot lateral view
__DEF_STR__ __FLOAT__
<1,100>
[1,50]0123456789
[1,1].
[1,50]0123456789
__DEF_STR__ __INT__
<1,100>
[1,100]0123456789
__DEF_SUB_PATH__ __STRING__
1 : x1 | '
: x2 | __ANY__
: x3 | '
Detailed explanation of conversion rules :
The conversion rules of 'PIVOT' function mentioned above are quite complex, it cannot be converted all at once, needs to be completed through 3 conversions :
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r pivot_unpivot.code -o 1_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 1_mid_result.zgl -o 2_mid_result.zgl
ZGLanguage -e PIVOT_UNPIVOT_SQL_REPLACE.syn -r 2_mid_result.zgl -o result.zgl
The first conversion, trigger the rule 'FROM_PIVOT_2_1' to converts source code.
To complete :
(A) One-to-one mapping between value "(yr, qtr)" and enumeration values "Q1,Q2,Q3,Q4"
(B) Add 'where' structure(Converted from 'FOR' structure)
The result were obtained as follows :
SELECT *
FROM table2222 PIVOT
(
SUM ( sales ) AS ss1 ,
SUM ( cogs ) AS sc
FOR (yr, qtr)
IN (
(yr, qtr) (2001, 'Q1') ,
(yr, qtr) (2001, 'Q2') ,
(yr, qtr) (2001, 'Q3') ,
(yr, qtr) (2001, 'Q4') )
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
) tmp
;
The 2nd conversion, trigger the rule 'FROM_PIVOT_2_2' to convert the above conversion result again.
To complete :
(A) Cartesian product of "SUM" columns and "qtr" columns
(B) Extract enumeration values to prepare for generating new field aliases
And the result is as follows :
SELECT *
FROM table2222 PIVOT
(
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
SUM(sales) AS ss1 yr = 2001 and qtr = 'Q4' as 2001 'Q4' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q1' as 2001 'Q1' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q2' as 2001 'Q2' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q3' as 2001 'Q3' ,
SUM(cogs) AS sc yr = 2001 and qtr = 'Q4' as 2001 'Q4'
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
) tmp
;
The 3rd conversion, trigger the rule 'FROM_PIVOT_2_3' to convert the result of 'FROM_PIVOT_2_2' again.
To complete :
(A) Perform operations such as adding, shifting, and merging on the field content starting with SUM to form a syntactically correct field code
(B) Remove key word 'PIVOT', move table name above 'where' statement
(C) Concatenate to create new field names
(D) Newly added parts to be manually supplemented: select ###,###,### group by ###,###,###
Obtain the finally result :
SELECT *
FROM
(
select ###,###,###
SUM(case when yr=2001 and qtr='Q1' then sales else null end) AS "2001_Q1_ss1",
SUM(case when yr=2001 and qtr='Q2' then sales else null end) AS "2001_Q2_ss1",
SUM(case when yr=2001 and qtr='Q3' then sales else null end) AS "2001_Q3_ss1",
SUM(case when yr=2001 and qtr='Q4' then sales else null end) AS "2001_Q4_ss1",
SUM(case when yr=2001 and qtr='Q1' then cogs else null end) AS "2001_Q1_sc",
SUM(case when yr=2001 and qtr='Q2' then cogs else null end) AS "2001_Q2_sc",
SUM(case when yr=2001 and qtr='Q3' then cogs else null end) AS "2001_Q3_sc",
SUM(case when yr=2001 and qtr='Q4' then cogs else null end) AS "2001_Q4_sc"
from table2222
where (yr, qtr)
IN (
(2001, 'Q1') ,
(2001, 'Q2') ,
(2001, 'Q3') ,
(2001, 'Q4') )
group by ###,###,###
) tmp
;
Description of "###,###,###" :
- It cannot obtains the fully available SQL code through syntax conversion, some code sections still require manual supplementation
- The parts that require manual supplementation have been clearly marked with "###,###,###"
- Most of the conversion work has been completed through tool, greatly reduces the workload of manual participation, avoids the risk of errors caused by manual modification.
Top comments (0)