How to parse sql and modify ast, and then generate sql?

[melin]

There are some needs to modify sql at work. example:
replace the table name in sql.

[kaby76]

There are several ways to parse and modify the sql.

But first, you will need a SQL grammar. Go to https://github.com/antlr/grammars-v4/tree/master/sql and pick one of the grammars.

Then, choose one or the other:

• Write a parser and listener to rewrite the parse tree/token stream. This method uses the Antlr tool to generate a parser and base class listener, which you can then modify and extend. Some examples are here:
  • https://stackoverflow.com/questions/69851567/how-to-create-a-refactor-tool/69853739#69853739
  • https://stackoverflow.com/questions/66713257/net-library-to-parse-modify-reconstruct-sql-statement/66715593#66715593
  • https://stackoverflow.com/a/58875448/4779853
  • https://github.com/mike-lischke/java2typescript
• Use a third-party application.
  • With the Trash toolkit to parse, edit, and print out the modified code using command-line tools. Trash uses XPath (version 2) expressions to identify where in the parse tree you want to modify. trdelete deletes nodes in the tree. trinsert inserts strings in the parse tree. trtext prints the parse tree(s) out. To see the parse tree so you can write XPath expressions to identify what you want to manipulate, use trtree. Note, Trash only does single edits, not complete edits of the tree nodes.
  • Use a template engine
    • Jinja. Example: Grammarinator -- In this code, you can think of each macro as a "visitor" for a particular node, and the code within as template output.
    • Convert the parse tree to XML and use XSLT.

[kaby76]

Update: I see a recent comment in this thread. I'm updating my comment because I think it is important to stress that Antlr is not a very good refactoring system. Antlr is mainly just a parser generator, and one of the best available. But, using Antlr Visitors and Listeners is akin to using assembly language for tree processing. There are better ways.

I'm including a concrete example of how to rename a table name across many files of MySQL using the mysql grammar and Trash. This example shows how to do the renaming with three commands in Bash using pipe to pass a parse tree between programs. The command is a succinct 106 characters: trparse ../examples/*.sql | trquery replace ' //tableName//ID[text() = "t1"]' "'t1_'" | trsponge -o xxx -c. There is a bit of set-up involved because one needs to generate a program to use the tools because Antlr grammars often include target-specific code.

2/04-07:16:05 ~/issues/g4-3791/sql/mysql/Positive-Technologies
$ trgen -t CSharp
C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies
CSharp  MySqlParser.g4 success 0.0797421
CSharp  MySqlLexer.g4 success 0.0221446
Rendering template file from CSharp/Other.csproj to Generated-CSharp/Other.csproj
Rendering template file from CSharp/st.build.ps1 to Generated-CSharp/st.build.ps1
Rendering template file from CSharp/st.build.sh to Generated-CSharp/st.build.sh
Rendering template file from CSharp/st.clean.ps1 to Generated-CSharp/st.clean.ps1
Rendering template file from CSharp/st.clean.sh to Generated-CSharp/st.clean.sh
Rendering template file from CSharp/st.Encodings.cs to Generated-CSharp/st.Encodings.cs
Rendering template file from CSharp/st.ErrorListener.cs to Generated-CSharp/st.ErrorListener.cs
Rendering template file from CSharp/st.makefile to Generated-CSharp/st.makefile
Rendering template file from CSharp/st.perf.sh to Generated-CSharp/st.perf.sh
Rendering template file from CSharp/st.run.ps1 to Generated-CSharp/st.run.ps1
Rendering template file from CSharp/st.run.sh to Generated-CSharp/st.run.sh
Rendering template file from CSharp/st.test-cover.sh to Generated-CSharp/st.test-cover.sh
Rendering template file from CSharp/st.Test.cs to Generated-CSharp/st.Test.cs
Rendering template file from CSharp/st.test.ps1 to Generated-CSharp/st.test.ps1
Rendering template file from CSharp/st.test.sh to Generated-CSharp/st.test.sh
Rendering template file from CSharp/Test.csproj.st to Generated-CSharp/Test.csproj.st
Copying source file from C:/msys64/home/Kenne/issues/g4-3791/sql/mysql/Positive-Technologies/README.md to Generated-CSharp/README.md
Copying source file from C:/msys64/home/Kenne/issues/g4-3791/sql/mysql/Positive-Technologies/MySqlParser.g4 to Generated-CSharp/MySqlParser.g4
Copying source file from C:/msys64/home/Kenne/issues/g4-3791/sql/mysql/Positive-Technologies/MySqlLexer.g4 to Generated-CSharp/MySqlLexer.g4
Copying source file from C:/msys64/home/Kenne/issues/g4-3791/sql/mysql/Positive-Technologies/desc.xml to Generated-CSharp/desc.xml
12/04-07:16:21 ~/issues/g4-3791/sql/mysql/Positive-Technologies
$ cd Generated-CSharp/
12/04-07:16:29 ~/issues/g4-3791/sql/mysql/Positive-Technologies/Generated-CSharp
$ make
bash build.sh
  Determining projects to restore...
  Restored C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\Test.csproj (in 586 ms).
MSBuild version 17.8.3+195e7f5a3 for .NET
  Determining projects to restore...
  All projects are up-to-date for restore.
C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\obj\Debug\net7.0\MySqlParser.cs(34881,65): warn
ing CS0108: 'MySqlParser.JsonOnEmptyContext.EMPTY()' hides inherited member 'ParserRuleContext.EMPTY'. Use the new keyword if hiding
 was intended. [C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\Test.csproj]
C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\obj\Debug\net7.0\MySqlParser.cs(71644,65): warn
ing CS0108: 'MySqlParser.KeywordsCanBeIdContext.EMPTY()' hides inherited member 'ParserRuleContext.EMPTY'. Use the new keyword if hi
ding was intended. [C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\Test.csproj]
  Test -> C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\bin\Debug\net7.0\Test.dll

Build succeeded.

C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\obj\Debug\net7.0\MySqlParser.cs(34881,65): warn
ing CS0108: 'MySqlParser.JsonOnEmptyContext.EMPTY()' hides inherited member 'ParserRuleContext.EMPTY'. Use the new keyword if hiding
 was intended. [C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\Test.csproj]
C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\obj\Debug\net7.0\MySqlParser.cs(71644,65): warn
ing CS0108: 'MySqlParser.KeywordsCanBeIdContext.EMPTY()' hides inherited member 'ParserRuleContext.EMPTY'. Use the new keyword if hi
ding was intended. [C:\msys64\home\Kenne\issues\g4-3791\sql\mysql\Positive-Technologies\Generated-CSharp\Test.csproj]
    2 Warning(s)
    0 Error(s)

Time Elapsed 00:00:20.92
12/04-07:16:53 ~/issues/g4-3791/sql/mysql/Positive-Technologies/Generated-CSharp
$ trparse ../examples/*.sql | trquery replace ' //tableName//ID[text() = "t1"]' "'t1_'" | trsponge -o xxx -c
CSharp 0 ../examples/admin.sql success 0.0932976
CSharp 0 ../examples/analyze.sql success 0.0293592
CSharp 0 ../examples/bitrix_queries_cut.sql success 17.3012507
CSharp 0 ../examples/case_sensitive_sql.sql success 0.1614342
CSharp 0 ../examples/ddl_alter.sql success 0.3345038
CSharp 0 ../examples/ddl_create.sql success 1.5174618
CSharp 0 ../examples/ddl_drop.sql success 0.0507935
CSharp 0 ../examples/ddl_flush.sql success 0.0173371
CSharp 0 ../examples/dml_delete.sql success 0.0892516
CSharp 0 ../examples/dml_insert.sql success 0.259436
CSharp 0 ../examples/dml_replace.sql success 0.0729177
CSharp 0 ../examples/dml_select.sql success 1.9158696
CSharp 0 ../examples/dml_table.sql success 0.0095061
CSharp 0 ../examples/dml_test_arithmetic_expression.sql success 0.0005621
CSharp 0 ../examples/dml_union.sql success 0.2038682
CSharp 0 ../examples/dml_update.sql success 0.1434043
CSharp 0 ../examples/dml_with.sql success 0.0821737
CSharp 0 ../examples/expressions.sql success 0.0396489
CSharp 0 ../examples/ext_tests.sql success 0.7920502
CSharp 0 ../examples/grant.sql success 0.0652138
CSharp 0 ../examples/kill.sql success 0.0166815
CSharp 0 ../examples/mysql_spec_comment.sql success 0.0478595
CSharp 0 ../examples/optimize.sql success 0.003708
CSharp 0 ../examples/smoke_tests.sql success 0.1341548
Writing to xxx/admin.sql
Writing to xxx/analyze.sql
Writing to xxx/bitrix_queries_cut.sql
Writing to xxx/case_sensitive_sql.sql
Writing to xxx/ddl_alter.sql
Writing to xxx/ddl_create.sql
Writing to xxx/ddl_drop.sql
Writing to xxx/ddl_flush.sql
Writing to xxx/dml_delete.sql
Writing to xxx/dml_insert.sql
Writing to xxx/dml_replace.sql
Writing to xxx/dml_select.sql
Writing to xxx/dml_table.sql
Writing to xxx/dml_test_arithmetic_expression.sql
Writing to xxx/dml_union.sql
Writing to xxx/dml_update.sql
Writing to xxx/dml_with.sql
Writing to xxx/expressions.sql
Writing to xxx/ext_tests.sql
Writing to xxx/grant.sql
Writing to xxx/kill.sql
Writing to xxx/mysql_spec_comment.sql
Writing to xxx/optimize.sql
Writing to xxx/smoke_tests.sql
12/04-07:17:31 ~/issues/g4-3791/sql/mysql/Positive-Technologies/Generated-CSharp
$ grep t1 xxx/*.sql
xxx/analyze.sql:ANALYZE TABLE t1_;
xxx/analyze.sql:ANALYZE TABLE t1_ UPDATE HISTOGRAM ON c1, c2;
xxx/case_sensitive_sql.sql:DeleTE t1_.*, t2 from t1_ join t3 on t1.col1 = t3.col1 jOin t2 oN t2.col2 = t3.col2 where t3.co4 is NoT nULl;
xxx/ddl_alter.sql:alter table t1_ stats_auto_recalc=default stats_sample_pages=50;
xxx/ddl_alter.sql:alter table t1_ stats_auto_recalc=default, stats_sample_pages=50;
xxx/ddl_alter.sql:alter table t1_ stats_auto_recalc=default, stats_sample_pages=default;
xxx/ddl_alter.sql:alter table t1_ modify column c1 enum('abc','cba','aaa') character set 'utf8' collate 'utf8_unicode_ci' not null default 'abc';
xxx/ddl_alter.sql:      as select * from  (t1_ as tt1, t2 as tt2) inner join t1_ on t1.col1 = tt1.col1;
xxx/ddl_create.sql:create table new_t  (like t1_);
xxx/ddl_create.sql:  dt1 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
xxx/ddl_create.sql:  dt10 DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
xxx/ddl_create.sql:  dt11 DATETIME DEFAULT '2038-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP
xxx/ddl_create.sql:truncate table t1_;
xxx/ddl_create.sql:create database `ymsun_test1` charset gb18030 collate gb18030_bin;
xxx/ddl_create.sql:on completion preserve do begin insert into test.t1 values (33), (111);select * from test.t1; end; -- //
xxx/ddl_create.sql:create definer = 'ivan'@'%' event testevent1 on schedule every 1 hour ends '2016-11-05 23:59:00'
xxx/ddl_create.sql:create index index1 on t1_(col1) comment 'test index' comment 'some test' using btree;
xxx/ddl_create.sql:create index index8 on t1_(col1) comment 'test index' using btree;
xxx/ddl_create.sql:create trigger trg_my1 before delete on test.t1 for each row begin insert into log_table values ("delete row from test.t1"); insert into t4 values (old.col1, old.col1 + 5, old.col1 + 7); end; -- //-- delimiter ;
xxx/ddl_create.sql:create trigger trg_my1 before delete on test.t1 for each row begin insert into log_table values ("delete row from test.t1"); insert into t4 values (old.col1, old.col1 + 5, old.col1 + 7); end; -- //-- delimiter ;
xxx/ddl_create.sql:     as select * from  (t1_ as tt1, t2 as tt2) inner join t1_ on t1.col1 = tt1.col1;
xxx/ddl_create.sql:       WHERE NOT EXISTS (select 1 from inventory.order_config t1 where t1.order_id = orderID and t1.attribute = 'first_attr') OR
xxx/ddl_create.sql:lock tables t1_ read;
xxx/ddl_create.sql:lock table t1_ read local wait 100;
xxx/ddl_drop.sql:drop temporary table if exists temp_t1;
xxx/ddl_drop.sql:drop table antlr_all_tokens, antlr_function_tokens, antlr_keyword_tokens, antlr_tokens, childtable, guns, log_table, new_t, parenttable, ship_class, ships, ships_guns, t1_, t2, t3, t4, tab1;
xxx/ddl_drop.sql:drop index index1 on t1_;
xxx/ddl_drop.sql:drop event if exists testevent1;
xxx/ddl_drop.sql:drop index index1 on t1_ algorithm=default;
xxx/dml_delete.sql:delete from t1_ where col1 = true and (col2 - col3 <= (select count(*) from t2) or maincol/2 > 100.2);
xxx/dml_delete.sql:delete ignore t1_.*, alias_t2 from t1_ inner join t3 on t1.col1 = t3.somecol and t1.col2 > t3.col_for_compare left join t2 as alias_t2 on t1.col1 <= alias_t2.col1 and alias_t2.col_onecol + t3.col_for_compare <> t1.sum_col
xxx/dml_delete.sql:where alias_t2.not_null_col is not null and t1.primary_key_column >= 100;
xxx/dml_delete.sql:DELETE FROM t1_, t2 USING t1_ INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
xxx/dml_delete.sql:DELETE t1_, t2 FROM t1_ INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
xxx/dml_delete.sql:DELETE t1_ FROM t1_ LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
xxx/dml_delete.sql:DELETE a1, a2 FROM t1_ AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;
xxx/dml_delete.sql:DELETE FROM a1, a2 USING t1_ AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;
xxx/dml_delete.sql:DELETE FROM t1_ alias_t1 WHERE alias_t1.col1 > 0;
xxx/dml_delete.sql:DELETE FROM t1_ as alias_t1 WHERE alias_t1.col1 > 0;
xxx/dml_insert.sql:insert into t1_ select * from t2;
xxx/dml_insert.sql:insert ignore into t1_(col1, col2, col3) values ('abc', 0, .12), ('adfasdf',23432, -.12);
xxx/dml_insert.sql:     VALUES ((SELECT a FROM t1_ WHERE b='Chip'), 'shoulder'),
xxx/dml_insert.sql:     ((SELECT a FROM t1_ WHERE b='Chip'), 'old block'),
xxx/dml_insert.sql:     ((SELECT a FROM t1_ WHERE b='John'), 'toilet'),
xxx/dml_insert.sql:     ((SELECT a FROM t1_ WHERE b='John'), 'long,silver'),
xxx/dml_insert.sql:     ((SELECT a FROM t1_ WHERE b='John'), 'li''l');
xxx/dml_insert.sql:INSERT INTO t1_ (a,b,c) VALUES (1,2,3),(4,5,6) AS new ON DUPLICATE KEY UPDATE c = new.a+new.b;
xxx/dml_replace.sql:replace into t1_ values (default, 1, '2', abs(-10 * col1) + sqrt(col2/col3));
xxx/dml_select.sql:select * from t1_ for update;
xxx/dml_select.sql:select * from t1_ lock in share mode;
xxx/dml_select.sql:select t1e2 as e1 from t;
xxx/dml_select.sql:select * from t1_ limit @varcount;
xxx/dml_select.sql:select * from t1_ limit @varoffset, @varcount;
xxx/dml_select.sql:select * from t1_ limit @varcount offset @varoffset;
xxx/dml_select.sql:select col1 from t1_ union select * from (select 1 as col2) as newt;
xxx/dml_select.sql:select col1 from t1_ union (select * from (select 1 as col2) as newt);
xxx/dml_select.sql:select * from (((((((select col1 from t1_) as ttt))))));
xxx/dml_select.sql:select * from t1_ inner join (t1_ as tt1, t2 as tt2) on t1.col1 = tt1.col1;
xxx/dml_select.sql:select * from  (t1_ as tt1, t2 as tt2) inner join t1_ on t1.col1 = tt1.col1;
xxx/dml_select.sql:select * from  t1_ as tt1, t2 as tt2 inner join t1_ on true;
xxx/dml_select.sql:SELECT * FROM test_table_1 t1 LEFT JOIN test_table_2 t2 JOIN test_table_3 t3 ON t3.id=t2.id ON t1.id=t2.id;
xxx/dml_select.sql:select col1 from t1_ inner join t2 on (t1.col1 = t2.col2);
xxx/dml_select.sql:SELECT 1 AS col1, t1.Id FROM t1_ GROUP BY col1;
xxx/dml_select.sql:SELECT COALESCE(LAG(last_eq.end_variation) OVER (PARTITION BY eq.account_id, eq.execution_name_id, eq.currency ORDER BY eq.start_date), 0) AS start_variation FROM t1_;
xxx/dml_select.sql:SELECT * FROM t1_ USE INDEX (PRIMARY) ORDER BY a;
xxx/dml_select.sql:SELECT * FROM t1_ FORCE INDEX (PRIMARY) ORDER BY a;
xxx/dml_update.sql:update ignore t1_ set `column_name` = default, `one-more-column` = (to_seconds(now()) mod 33);
xxx/dml_update.sql:update t1_, t2, t3 inner join t4 using (col_name1, col_name2)
xxx/dml_update.sql:set t1.value_col = t3.new_value_col, t4.`some-col*` = `t2`.`***` * 2
xxx/dml_update.sql:where  t1.pk = t2.fk_t1_pk and t2.id = t4.fk_id_entity;
xxx/dml_update.sql:UPDATE t1_ SET col1 = col1 + 1;
xxx/dml_update.sql:UPDATE t1_ SET col1 = col1 + 1, col2 = col1;
xxx/dml_update.sql:update Table1 t1 join Table2 t2 on t1.ID=t2.t1ID join Table3 t3 on t2.ID=t3.t2ID set t1.Value=12345 where t3.ID=54321;
xxx/ext_tests.sql:create table t1_(col1 binary(20));
xxx/ext_tests.sql:select 0 in (20 = any (select col1 from t1_)) is not null is not unknown as t;
xxx/ext_tests.sql:select 0 in (20 = any (select col1 from t1_)) is not unknown as t;
xxx/ext_tests.sql:select 20 = any (select col1 from t1_) is not unknown as t;
xxx/ext_tests.sql:select 20 = any (select col1 from t1_) as t;
xxx/ext_tests.sql:-- select sqrt(20.5) not in (sqrt(20.5) not in (select col1 from t1), 1 in (1, 2, 3, 4)) as c;
xxx/optimize.sql:OPTIMIZE TABLE t1_;
xxx/optimize.sql:OPTIMIZE TABLE t1_, t2;
xxx/optimize.sql:OPTIMIZE TABLES t1_;
xxx/optimize.sql:OPTIMIZE TABLES t1_, t2;
xxx/optimize.sql:optimize local table t1_;
xxx/smoke_tests.sql:create table t1_(col1 binary(20));
xxx/smoke_tests.sql:select 0 in (20 = any (select col1 from t1_)) is not null is not unknown as t;
xxx/smoke_tests.sql:select 0 in (20 = any (select col1 from t1_)) is not unknown as t;
xxx/smoke_tests.sql:select 20 = any (select col1 from t1_) is not unknown as t;
xxx/smoke_tests.sql:select 20 = any (select col1 from t1_) as t;
xxx/smoke_tests.sql:-- select sqrt(20.5) not in (sqrt(20.5) not in (select col1 from t1), 1 in (1, 2, 3, 4)) as c;
12/04-07:17:40 ~/issues/g4-3791/sql/mysql/Positive-Technologies/Generated-CSharp
$

[Fnck]

it's easy to modify the table name in antlr ParserTree, while you are using the ParserBaseListener or SqlParser which generated by antlr.
I will post a method to generate the SQL with ParserTree here with java:

private static String getParserTree(ParseTree tree) {
        StringBuilder sb = new StringBuilder();
        if (tree.getChildCount() == 0) {
            sb.append(tree.getText());
            sb.append(" ");
        } else {
            
            for (int i=0;i<tree.getChildCount();i++) {
                sb.append(getParserTree(tree.getChild(i)));
            }
            
        }
        return sb.toString();
    }