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 (select country,state,yr,qtr,sales,cogs from table111)
PIVOT
(
SUM(sales) AS ss1,
SUM(cogs) AS sc
FOR qtr IN
(
'Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3,
'Q4' AS Quarter4
)
) tmp
;
Using the ZGLanguage conversion rule, execute the conversion to obtain the result :
SELECT *
FROM
(
select ###,###,###
SUM (case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
SUM (case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
SUM (case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
SUM (case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
SUM (case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
SUM (case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
SUM (case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
SUM (case when qtr='Q4' then cogs else null end) AS Quarter4_sc
from (select country,state,yr,qtr,sales,cogs from table111)
where qtr IN('Q1','Q2','Q3','Q4')
group by ###,###,###
) tmp
;
The conversion rule 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_
[NO] XXX
__DEF_PATH__ __FROM_PIVOT_1_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 : for @ %__IF_KW__ | for
: col2 @ | __NAME__
: in @ | in
: x3 @ | (
N : val1 @ | __INT__
: val2 @ + __STRING__
: as2 @ CAN_SKIP | as
: coln @ | __NAME__
e : dh @ | ,
1 : x4 @ | )
: 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 : for @ | for
: col2 @ | __NAME__
: in @ | in
: x3 @ | (
N : val1 @ | __\b__
: val2 @ | __\b__
: col2 @ | __NAME__
: col2 @ | =
: val1 @ | __INT__
: val2 @ | __STRING__
: as2 @ | as
: coln @ | __NAME__
e : dh @ | ,
1 : x4 @ | )
: x2 @ | )
__DEF_PATH__ __FROM_PIVOT_1_2__
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 : for @ %__IF_KW__ | for
: col2 @ | __NAME__
: in @ | in
: x3 @ | (
N : col22 @ | __NAME__
: col23 @ | =
: val1 @ | __INT__
: val2 @ + __STRING__
: as2 @ CAN_SKIP | as
: coln @ | __NAME__
e : dh @ | ,
1 : x4 @ | )
: x2 @ | )
--------------------------------------------------------------------
1 : frm @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
: pvt @ | pivot
: x1 @ | (
N : fun @ | __NAME__
: fs @ | (
: col1 @ | __NAME__
: fe @ | )
: as1 @ | as
: colas @ | __NAME__
* : col22 @ | __NAME__
: col23 @ | =
: val1 @ | __INT__
: val2 @ | __STRING__
: as2 @ | as
: coln @ | __NAME__
e : coln @ | ,
1 : for @ | where
: col2 @ | __NAME__
: in @ | in
: x3 @ | (
N : val1 @ | __INT__
: val2 @ | __STRING__
e : dh @ | ,
1 : x4 @ | )
1 : x2 @ | )
__DEF_PATH__ __FROM_PIVOT_1_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__
: col22 @ | __NAME__
: col23 @ | =
: val1 @ | __INT__
: val2 @ + __STRING__
: as2 @ %__IF_KW__ CAN_SKIP | as
: coln @ | __NAME__
e : dh @ | ,
1 : for @ | where
: col2 @ | __NAME__
: in @ | in
: x3 @ | (
N : val3 @ | __INT__
: val4 @ + __STRING__
e : dh1 @ | ,
1 : x4 @ | )
: x2 @ | )
--------------------------------------------------------------------
1 : frm @ STRING | from
: pvt @ STRING | (select ###,###,###
N : fun @ | __NAME__
: fs @ / (
: col22 @ STRING \ case when
: col22 @ / __NAME__
: col23 @ / =
: val1 @ / __INT__
: val2 @ / __STRING__
: col1 @ / then
: col1 @ / __NAME__
: col1 @ STRING / else null end
: fe @ \ )
: as1 @ | as
: coln @ | __NAME__
: coln @ \ _
: colas @ \ __NAME__
e : dh @ | ,
1 : pvt @ | from
: tab @ | __TABLE_NAME__
: ssl @ | __SUB_SELECT__
1 : for @ | where
: col2 @ / __NAME__
: in @ / in
: x3 @ \ (
N : val3 @ \ __INT__
: val4 @ \ __STRING__
e : dh1 @ \ ,
1 : x4 @ \ )
: x4 @ STRING | group by ###,###,###
: x2 @ | )
__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_1_1' to convert the source code, complete the one-to-one mapping between the value "qtr" and the enumeration values "Q1, Q2, Q3, Q4", and result were obtained as follows:
SELECT *
FROM (select country,state,yr,qtr,sales,cogs from table111)
PIVOT
(
SUM(sales ) AS ss1 ,
SUM(cogs) AS sc
FOR qtr IN
(
qtr = 'Q1' AS Quarter1 ,
qtr = 'Q2' AS Quarter2 ,
qtr = 'Q3' AS Quarter3 ,
qtr = 'Q4' AS Quarter4
)
) tmp
;
The 2nd conversion, trigger the rule 'FROM_PIVOT_1_2' to convert the above conversion result again.
To complete :
(A) Cartesian product of "SUM" columns and "qtr" columns
(B) Convert "FOR" structure to "where" structure
And result were obtained as follows:
SELECT *
FROM (select country,state,yr,qtr,sales,cogs from table111) PIVOT
(
SUM(sales) AS ss1 qtr = 'Q1' AS Quarter1 ,
SUM(sales) AS ss1 qtr = 'Q2' AS Quarter2 ,
SUM(sales) AS ss1 qtr = 'Q3' AS Quarter3 ,
SUM(sales) AS ss1 qtr = 'Q4' AS Quarter4 ,
SUM(cogs) AS sc qtr = 'Q1' AS Quarter1 ,
SUM(cogs) AS sc qtr = 'Q2' AS Quarter2 ,
SUM(cogs) AS sc qtr = 'Q3' AS Quarter3 ,
SUM(cogs) AS sc qtr = 'Q4' AS Quarter4
where qtr IN
(
'Q1' ,
'Q2' ,
'Q3' ,
'Q4'
)
) tmp
;
The 3rd conversion, trigger the rule 'FROM_PIVOT_1_3' to convert the above conversion result 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) Delete key word 'PIVOT', move the subquery above the 'where' statement.
(C) Newly added parts to be manually supplemented: select ###,###,### group by ###,###,###
Obtain the finally result:
SELECT *
FROM
(
select ###,###,###
SUM(case when qtr='Q1' then sales else null end) AS Quarter1_ss1,
SUM(case when qtr='Q2' then sales else null end) AS Quarter2_ss1,
SUM(case when qtr='Q3' then sales else null end) AS Quarter3_ss1,
SUM(case when qtr='Q4' then sales else null end) AS Quarter4_ss1,
SUM(case when qtr='Q1' then cogs else null end) AS Quarter1_sc,
SUM(case when qtr='Q2' then cogs else null end) AS Quarter2_sc,
SUM(case when qtr='Q3' then cogs else null end) AS Quarter3_sc,
SUM(case when qtr='Q4' then cogs else null end) AS Quarter4_sc
from (select country,state,yr,qtr,sales,cogs from table111)
where qtr IN('Q1','Q2','Q3','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)