前言
最近遇到个项目,打算做ORACLE应用系统里的PLSQL重构,将一些ORACLE独有的高级语法改写成指定的通用语法,以便能支持在更多的数据库上运行,但该应用系统里的PLSQL代码量实在太大,人工改写或者正则替换都不是那么容易,数据库厂家的自动改写工具也只能提供一种规则的自动转换,不能定制转换规则,所以还是期望借助语法树解析工具来做一些个性化定制的改写。
几年前我就有写过antlr4这个工具怎么用,但是隔了这么多年,这个工具的有了一点点变化。另外,python相关的东西经常要解决一些pip依赖,在没有外网的环境非常不方便,所以本文通过不使用任何在线安装命令的方式来装这个环境,以整理出必须下载的文件。
一、安装PYTHON和JAVA(11或以上)
https://www.python.org/ftp/python/3.13.0/python-3.13.0-amd64.exe
https://download.java.net/java/ga/jdk11/openjdk-11_windows-x64_bin.zip (以后可能会用到jdk,就不单独弄jre了)
python安装很简单,没什么说的。
JAVA只需要解压,然后把解压后的目录配置到环境变量 JAVA_HOME
,再在环境变量 PATH
里加入 %JAVA_HOME%\bin
即可。
用11的原因是后文下载的 antlr-4.13.2-complete.jar
这个文件是用11编译的,实测用17也行,但用8就会报错。
二、创建工作目录
md c:\antlr
pushd c:\antlr
三、下载必要文件
wget https://www.antlr.org/download/antlr-4.13.2-complete.jar
git clone https://github.com/antlr/grammars-v4
#或
wget https://codeload.github.com/antlr/grammars-v4/zip/refs/heads/master
unzip grammars-v4-master.zip
四、生成解析器
1.复制必要文件
pushd C:\antlr
copy C:\antlr\grammars-v4-master\sql\plsql\*.g4 .
copy C:\antlr\grammars-v4-master\sql\plsql\Python3\* .
2.生成
pushd C:\antlr
SET CLASSPATH=.;C:\antlr\antlr-4.13.2-complete.jar;
doskey antlr4vpy3=java org.antlr.v4.Tool -Dlanguage=Python3 -no-listener -visitor $*
antlr4vpy3 PlSqlLexer.g4
antlr4vpy3 PlSqlParser.g4
执行后,会生成这么几个文件
PlSqlLexer.interp
PlSqlLexer.tokens
PlSqlLexer.py
PlSqlParser.interp
PlSqlParser.tokens
PlSqlParser.py
PlSqlParserVisitor.py
五、安装antlr4_python3_runtime
方法一:通过poetry进行源码安装
pip install poetry
pushd C:\antlr\antlr4-dev\runtime\Python3
poetry install
注:poetry本身的离线安装非常麻烦,建议使用方法二
方法二:直接下载发行版本安装
antlr4-python3-runtime · PyPI
pip install antlr4_python3_runtime-4.13.2-py3-none-any.whl
六、测试
注意参数为 -t
或 --tree
,不是 -tree
pygrun PlSql sql_script --tree
输入sql,ctrl+z
,回车
C:\antlr>pygrun PlSql sql_script --tree
SELECT 1 A FROM DUAL;
^Z
(sql_script
(unit_statement
(data_manipulation_language_statements
(select_statement
(select_only_statement
(subquery
(subquery_basic_elements
(query_block SELECT
(selected_list
(select_list_elements
(expression
(logical_expression
(unary_logical_expression
(multiset_expression
(relational_expression
(compound_expression
(concatenation
(model_expression
(unary_expression
(atom
(constant
(numeric 1))))))))))))
(column_alias
(identifier
(id_expression
(regular_id A))))))
(from_clause FROM
(table_ref_list
(table_ref
(table_ref_aux
(table_ref_aux_internal
(dml_table_expression_clause
(tableview_name
(identifier
(id_expression
(regular_id DUAL))))))))))))))))) ; <EOF>)
C:\antlr>
七、JAVA版
上面是基于python语言进行开发做准备,但antlr4其实还支持使用其他开发语言,比如C/CPP/C#/JAVA/GO/JS/SWIFT/DART,下面就简单说了使用JAVA开发需要准备的内容。
1.安装jdk(11或以上)
2.生成解析器
SET CLASSPATH=.;C:\antlr\antlr-4.13.2-complete.jar;
doskey antlr4vjava=java org.antlr.v4.Tool -Dlanguage=Java -no-listener -visitor $*
doskey grun =java org.antlr.v4.gui.TestRig $*
antlr4vjava PlSqlParser.g4
antlr4vjava PlSqlLexer.g4
javac PlSqlLexer.java
javac PlSqlParserVisitor.java
javac PlSqlParserBaseVisitor.java
javac PlSqlParser.java
3.测试生成语法树
注意参数为 -tree
,不是 -t
,也不是 --tree
doskey grun =java org.antlr.v4.gui.TestRig $*
grun PlSql sql_script -tree
输入sql,ctrl+z
,回车
C:\antlr>SET CLASSPATH=.;C:\antlr\antlr-4.13.2-complete.jar;
C:\antlr>doskey grun =java org.antlr.v4.gui.TestRig $*
C:\antlr>grun PlSql sql_script -tree
SELECT 1 A FROM DUAL;
^Z
(sql_script (unit_statement (data_manipulation_language_statements (select_statement (select_only_statement (subquery (subquery_basic_elements (query_block SELECT (selected_list (select_list_elements (expression (logical_expression (unary_logical_expression (multiset_expression (relational_expression (compound_expression (concatenation (model_expression (unary_expression (atom (constant (numeric 1)))))))))))) (column_alias (identifier (id_expression (regular_id A)))))) (from_clause FROM (table_ref_list (table_ref (table_ref_aux (table_ref_aux_internal (dml_table_expression_clause (tableview_name (identifier (id_expression (regular_id DUAL))))))))))))))))) ; <EOF>)
C:\antlr>
4.语法树图形化
grun PlSql sql_script -gui
输入sql,ctrl+z
,回车
八、离线环境需准备的最少文件列表
1.java版
- https://www.antlr.org/download/antlr-4.13.2-complete.jar
- https://download.java.net/java/ga/jdk11/openjdk-11_windows-x64_bin.zip
- https://github.com/antlr/grammars-v4
- https://github.com/antlr/grammars-v4/sql/plsql/PlSqlLexer.g4
- https://github.com/antlr/grammars-v4/sql/plsql/PlSqlParser.g4
- https://github.com/antlr/grammars-v4/sql/plsql/Java/PlSqlLexerBase.java
- https://github.com/antlr/grammars-v4/sql/plsql/Java/PlSqlParserBase.java
2.python版
- https://www.antlr.org/download/antlr-4.13.2-complete.jar
- https://download.java.net/java/ga/jdk11/openjdk-11_windows-x64_bin.zip (或者用手上有的jre)
- https://www.python.org/ftp/python/3.13.0/python-3.13.0-amd64.exe
- https://files.pythonhosted.org/packages/89/03/a851e84fcbb85214dc637b6378121ef9a0dd61b4c65264675d8a5c9b1ae7/antlr4_python3_runtime-4.13.2-py3-none-any.whl
- https://github.com/antlr/grammars-v4
- https://github.com/antlr/grammars-v4/sql/plsql/PlSqlLexer.g4
- https://github.com/antlr/grammars-v4/sql/plsql/PlSqlParser.g4
- https://github.com/antlr/grammars-v4/sql/plsql/Python3/PlSqlLexerBase.py
- https://github.com/antlr/grammars-v4/sql/plsql/Python3/PlSqlParserBase.py
- https://github.com/antlr/grammars-v4/sql/plsql/Python3/transformGrammar.py
至于怎么使用antlr4进行语法转换的开发,下一篇再说
基于语法树的SQL自动改写工具开发系列(2)-使用PYTHON进行简单SQL改写的开发实战