Open-source tool: Simple example of syntax conversion for batch SQL code: 'ORACLE START WITH CONNECT' syntax conversion

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 ; 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 ; 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 @ | selec