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 ‘ORACLE START WITH CONNECT’ syntax code（ start_with_connect.sql ）：
SELECT *
FROM tree
START WITH id = 1
CONNECT BY NOCYCLE PRIOR id = parentid
;
Enter fullscreen mode Exit fullscreen mode
By configuring the conversion rules, the above code can be directly converted into the following code（convert to “with recursive” syntax）：
with recursive wr_tree as
(
SELECT id, parentid, 1 as level from tree where id = 1
union
SELECT tree.id, tree.parentid, level + 1 from tree, wr_tree where tree.parentid = wr_tree.id
)
SELECT * from wr_tree order by id
;
Enter fullscreen mode Exit fullscreen mode
Conversion rule (STATR_WITH_CONNECT_SQL_REPLACE.syn) 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__ __START_WITH_CONNECT__
1 : sel @ %__IF_KW__ | select
: cc @ | *
: frm @ | from
: srctab @ | __NAME__
: sta @ %__IF_KW__ | start
: wth @ %__IF_KW__ | with
: swp @ | __NAME__
: dy1 @ | =
: int @ | __INT__
: str @ + __STRING__
: cnn @ %__IF_KW__ | connect
: by @ %__IF_KW__ | by
: ncy @ %__IF_KW__ CAN_SKIP | nocycle
: prr1 @ %__IF_KW__ CAN_SKIP | prior
: col1 @ | __NAME__
: dy @ | =
: col2 @ | __NAME__
: end @ | ;
-----------------------------------------------------------------------
1 : sel @ | with
: sel @ | recursive
: sel @ | wr_
: srctab @ \ __NAME__
: sel @ STRING | as
: sel @ | __\n__
: sel @ | (
: sel @ | __\n__
: sel @ | select
: col1 @ / __NAME__
: col2 @ \ ,
: col2 @ / __NAME__
: sel @ STRING \ , 1 as level from
: srctab @ / __NAME__
: sta @ / where
: swp @ / __NAME__
: dy1 @ / =
: int @ / __INT__
: str @ / __STRING__
: sel @ | __\n__
: sel @ | union
: sel @ | __\n__
: sel @ | select
: srctab @ / __NAME__
: srctab @ \ .
: col1 @ \ __NAME__
: col2 @ \ ,
: srctab @ / __NAME__
: srctab @ \ .
: col2 @ \ __NAME__
: sel @ STRING \ , level + 1 from
: srctab @ / __NAME__
: srctab @ \ ,
: sel @ / wr_
: srctab @ \ __NAME__
: sta @ / where
: srctab @ / __NAME__
: srctab @ \ .
: col2 @ \ __NAME__
: dy1 @ / =
: sel @ / wr_
: srctab @ \ __NAME__
: srctab @ \ .
: col1 @ \ __NAME__
: sel @ | __\n__
: sel @ STRING | )
: sel @ | __\n__
: sel @ | select
: sel @ | *
: sel @ STRING | from
: sel @ | wr_
: srctab @ \ __NAME__
: sel @ / order
: sel @ / by
: col1 @ / __NAME__
: end @ | ;
__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__ __INT__
<1,100>
[1,100]0123456789
__DEF_SUB_PATH__ __STRING__
1 : x1 | '
: x2 | __ANY__
: x3 | '
Enter fullscreen mode Exit fullscreen mode
Execution command ：
ZGLanguage -e STATR_WITH_CONNECT_SQL_REPLACE.syn -r start_with_connect.sql -o result.sql