SQLをパースするライブラリ(本当にただのメモ)
最近JAVAでSQLをパースするライブラリを探した。
下のスタックオーバーフローのQAでの状況が、その時の状況に近いのでメモを置いている。
(少し愚痴)
10年くらい前にも、SQLパーサとMDX操作のライブラリが必要となってSQLパーサを、ほとんどスクラッチで書いたこともある。
それは、ひどい出来栄えでは無いと思っているが、今回は、使えるライブラリがあれば、それを使用した方が良いと思い探してみた。
前よりは良い状況で、無くはないが、豊富に見つかる OR 有力なデファクトの様なものがあるという状況ではない。
フレームワークから抜いてくれば良いという考えも浮かぶが、それは、汎用性の点で、フレームワークの構造に特化しすぎているものが多い。
改造するのなら、Antlrで独自実装した方がマシに思える様なものしか見あたらない。
(フレームワーク作者に、内部構造を分かってもらうドキュメントを作るような人はいない様に思えるので、独自に作るのと、トータルコストが変わらないかもしれない。)
上記10年くらい前の状況の時、字句解析後の字句オブジェクト列へのパターンマッチをする為のライブラリも探したが、見当たらなかった。
見つけたのは、方法論として、正規表現文字列に(字句オブジェクト列を)マッピングすれば擬似的に「字句オブジェクト列へのパターンマッチ」を行うライブラリが作れるのではないか ?
という目論見の記事があったと記憶している。
結局その時は、「字句オブジェクト列へのパターンマッチ」の実装を独自に書き、上記パーサや木の操作に使用した。
「字句オブジェクト列へのパターンマッチ」を作成した時の感想を言うと、なにかオートマトンの教科書の説明を実装したようなコードが出来上がり、対称性はあるが、読みやすいのか読みにくいのか判らないコードになった憶えがある。
しかし、なぜ、フレームワークやcommonsのようなライブラリのプロジェクトは、雨後の筍のように生えてくるのに、SQLパーサや、文字でない対象に正規表現風にマッチする用途の様なライブラリは、次々と候補が生まれてこないのだろうか ?
自分が知らないだけの可能性もあるが、下のスタックオーバーフローのQAでも、応えている人が、マッチする答えを持っていないのか、質問とは、ずれた回答ばかりしている。
最後の回答の「org.eclipse.datatools」は、良いのかもしれないが、まだ試していない。
ただ、自分は、今のところJSQLParserの0.9.3を使用するつもり。
ただし、ORACLEのJOINの+表現とかの方言は対応していないとのこと。
(+)OUTER JOINで等式結合では解析が通るが、LIKE結合では通らない。
例)WHERE T1.NUM1(+) LIKE SBSTR(...
自分の気が済むまで、試しながら、汎用性の高いSQLパーサを目指して自作し、使い方のサンプルを、相当レベルで提供できるまで持っていけるならば、それをを公開するのが一番良いのかもしれない。
また、OSSのコードを読めと言われるかもしれないが、フレームワークやライブラリを読む時、結構ガッカリした経験がある。
1例として、所謂2waySQLのフレームワークで、SQLの変数に値をバインドする仕組みに、OGNLを使用していて、INSERT文を実行するのに12msかかるのに対して、SQLの解析・操作部で、144msを要しているのである。
しかも悪いことに、jdbcのbutchExecuteで実行して12msを更に短縮しようという文脈で、バインド値が少しでも変わると、SQLの解析・操作が始まってしまう作りで、それでは、butchExecuteを使わない方が速い or 固定のSQL(これはどんな用途に使うの?)の連続実行のどちらかしか対応しないとなってしまう。
遅いOGNLに関しては、多くの2waySQLフレームワークで使用されているので、絶望的な気持ちになる。
また、OGNLよりも、(10年前に書いた注意深く時間を掛けたわけでもない)自分でスクラッチで書いた汚いパーサの方がずっと速いという不思議な結果がでる ?
世の中には、確実に自分より頭が良い人達が、沢山いるのに、これは、どういう事なのだろうか ?
■SQL parser library for Java - Retrieve the list of table names present in a SQL statement
http://stackoverflow.com/questions/5572793/sql-parser-library-for-java-retrieve-the-list-of-table-names-present-in-a-sql
(質問)
I am looking for a SQL Library that will parse an SQL statement and return some sort of Object representation of the SQL statement.
>SQLライブラリを探しています。それは、SQL文をparseして、SQL文を表現する何かしらのオブジェクトを返すものです。
My main objective is actually to be able to parse the SQL statement and retrieve the list of table names present in the SQL statement
(including subqueries, joins and unions).
>主な目的は、当にSQL文をparseすることや、SQLステートメントに存在する、テーブル名のリストを取得することを可能にすることです。
I am looking for a free library with a license business friendly (e.g. Apache license).
>ビジネスに有効な、ライセンスが無料のライブラリを探しています。(例えば、Apache license)
I am looking for a library and not for an SQL Grammar.
> 特定のSQL文法用ではないライブラリを探しています。
I do not want to build my own parser.
>自分のパーサーを組み立てることを望んている訳ではありません。
The best I could find so far was JSQLParser, and the example they give is actually close to what I am looking for.
>これまで見つけた中で一番のものは、JSQLParserです。それらが提供するサンプル例は、当に、かなり、探していたものに近いものです。
However it fails parsing too many good queries (DB2 Database) and I'm hoping to find a more reliable library.
>しかしながら、正しいクエリー(DB2データベース)に対して失敗することが多すぎます。もっと信頼性の高いライブラリを見つけたいのです。
(回答1)
I doubt you'll find anything prewritten that you can just use.
>あなたが、当に使える様な、既に書かれたものを見つけられるかどうか、疑わしく思っています。
The problem is that ISO/ANSI SQL is a very complicated grammar - something like more than 600 production rules IIRC.
>ISO/ANSI SQLが、とても厄介な文法である、という問題、つまり600を超えるIIRCの生成ルールの様なものがあります。
Terence Parr's ANTLR parser generator (Java, but can generate parsers in any one of a number of target languages)
has several SQL grammars available,
>Terence ParrのANTLRパーサージェネレータ(javaだけど、たくさんの目的言語の任意の1つのパーサーを生成できます)
>には、いくつかの利用可能なSQL文法が存在しています。
including a couple for PL/SQL, one for a SQL Server SELECT statement, one for mySQL, and one for ISO SQL.
>PL/SQLを計算するものを含んでいすし、SQLサーバーのSELECT文用のもの、mySQL用のもの、そしてISOSQL用のものも含んでいます。
No idea how complete/correct/up-to-date they are.
>完全に、それらに、正しく、かつアップデートされたようなものは存在しないのではないでしょうか。
(回答1へのリプライ)
I don't actually need a complete Object representation of the SQL statement, what I really need is to extract the names of the tables present in the SQL statement.
>完全なSQL文の表現オブジェクトが必要なのではありません。本当にほしいものは、SQL文のテーブル名を抽出すことなのです。
So, as long as I can somehow get that from an existing library and as long as it correctly parses most queries, I would be happy.
>だから、できるだけ、現存するライブラリからなんとか取得し、できだけ多くのクエリが、正常に終了する。そうなれば、ハッピーだねと言うことなのです。
Writing my own parser right now is not an optin as I don't have the time for that
>自分のパーサーを今書くことは、時間がないということから同意できません。
(更にリプライ)
your not gonna be writing your own parser.your gonna be using a preexisting one.
>自分でパーサーを書きたくないなら、既存のものを使うしかない。
YACC is another option.
>YACCは、その他の選択しですが。
antlr will let you write a code snippet to a java file every time it encounters a table element in the query.
>Antlrによって、クエリーのtable要素に当たったすべての時に、javaファイルに、コード片を記述できるようになります。
you will use this generated java file to parse the sql, and if you wrote the code snippet and header / footer correctly in antlr, then you will just have a list of tables.
>SQLをパースするための、この生成されたJAVAファイルを使えば良いし、コード片やヘッダー/フッターをAntlrで正しく記述すれば、テープルのリストを得られますよ。
i dont see how you can get a list of table names without a complete representation.
>完全な表現無しに、テーブル名のリストを取得する方法はわかりません。
if your queries are really simplistic, then maybe you can just fake it with regular expressions.
>もし、あなたのクエリーが本当にシンプルなら、正規表現で、やっつけることができるかもしれませんが。
(更にリプライのリプライ)
what I meant was that I don't need a very flexible library.
>私が言いたかったことは、汎用性の高いライブラリがほしいと言うことでは無いのです。
As long as it would understand most of my queries (DB2) and would give me the names of the tables present in the query, I would be happy.
>出来る限り、多くの(DB2の)クエリーを解釈し、クエリーに存在するテーブル名を得る。そしてハッピーになる。
(回答2)
You needn't reinvent the wheel, there is already such a reliable SQL parser library there, (it's commerical, not free),
>車輪の再発明をする必要は無いですよ、既にそのような、信頼性の高いSQLパーサーライブラリがそこにありますよ。(商用で、無料ではありませんが)
and this article shows how to retrieve the list of table names present in the SQL statement (including subqueries, joins and unions)
>この記事は、どのようにしてSQL文(サブクエリとJOINとUNIONを含む)に存在するテープル名のリストを取得するかを、提示することですね。
that is exactly what you are looking for.
>それは、正しく、あなたの探していたものですよ。
http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/get-columns-and-tables-in-sql-script/
This SQL parser library supports Oracle, SQL Server, DB2, MySQL, Teradata and ACCESS.
>このSQLパーサーライブラリは、Oracle, SQL Server, DB2, MySQL, Teradata そして ACCESSをサポートしてますよ。
(回答2へのリプライ)
This does exactly what I am looking for.
>これは、本当に求めていたものです。
However, as I said in my question, I can only consider free libraries (although I'm under the impression that's not going to happen :/) as this is just a prototype.
>が、しかし、質問で言っていた様に、無料のライブラリのみしか考慮対象にできません。(とはいえ、何が起こるかわからないと言う感想ですが)これは、まだほんのプロトタイプにすぎないので。
Nonetheless, thank you! It's good to know that at least there's a good commercial library that does exactly what I need, and maybe later I would be allowed to use it.
>それでも、ありがとう。少なくとも、希望のそのものの良い商用ライブラリがあるということが分かったことはよいことで、後日それを使用させてもらうことになるかもしれません。
(回答3)
Old question, but I think this project contains what you need:
>古い質問だが、このプロジェクトはニーズがあると思う。
Data Tools Project - SQL Development Tools
http://www.eclipse.org/datatools/project_sqldevtools/
Here's the documentation for the SQL Query Parser.
http://www.eclipse.org/datatools/project_sqldevtools/sqltools_doc/SQL%20Query%20Parser%20User%20documentation.htm
>ここにSQLパーサーのドキュメントがあるよ。
Also, here's a small sample program.
>また、ここに小さなサンプルを置きます。
I'm no Java programmer so use with care.
>自分は、Javaプログラマーでないから使う時にきおつけてね。
package org.lala;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.MappedByteBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.util.Iterator;
import java.util.List;
import org.eclipse.datatools.modelbase.sql.query.QuerySelectStatement;
import org.eclipse.datatools.modelbase.sql.query.QueryStatement;
import org.eclipse.datatools.modelbase.sql.query.TableReference;
import org.eclipse.datatools.modelbase.sql.query.ValueExpressionColumn;
import org.eclipse.datatools.modelbase.sql.query.helper.StatementHelper;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParseErrorInfo;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParserException;
import org.eclipse.datatools.sqltools.parsers.sql.SQLParserInternalException;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParseResult;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManager;
import org.eclipse.datatools.sqltools.parsers.sql.query.SQLQueryParserManagerProvider;
public class SQLTest {
private static String readFile(String path) throws IOException {
FileInputStream stream = new FileInputStream(new File(path));
try {
FileChannel fc = stream.getChannel();
MappedByteBuffer bb = fc.map(FileChannel.MapMode.READ_ONLY, 0,
fc.size());
/* Instead of using default, pass in a decoder. */
return Charset.defaultCharset().decode(bb).toString();
} finally {
stream.close();
}
}
/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {
try {
// Create an instance the Parser Manager
// >パーサー管理インスタンスを生成
// SQLQueryParserManagerProvider.getInstance().getParserManager
// returns the best compliant SQLQueryParserManager
// supporting the SQL dialect of the database described by the given
// database product information.
// >SQLQueryParserManagerProvider.getInstance().getParserManagerが、
// >データベース製品情報によって記述されるデータベースのSQL方言を
// >サポートする最適なコンパイラのSQLQueryParserManagerを返します。
// In the code below null is passed for both the database and version
// in which case a generic parser is returned
// >下のコードで、nullは、データベースとバージョンを無視し
// >このケースでは、汎用パーサーを返却します。
SQLQueryParserManager parserManager = SQLQueryParserManagerProvider
.getInstance().getParserManager("DB2 UDB", "v9.1");
// Sample query
String sql = readFile("c:\\test.sql");
// Parse
SQLQueryParseResult parseResult = parserManager.parseQuery(sql);
// Get the Query Model object from the result
QueryStatement resultObject = parseResult.getQueryStatement();
// Get the SQL text
String parsedSQL = resultObject.getSQL();
System.out.println(parsedSQL);
// Here we have the SQL code parsed!
QuerySelectStatement querySelect = (QuerySelectStatement) parseResult
.getSQLStatement();
List columnExprList = StatementHelper
.getEffectiveResultColumns(querySelect);
Iterator columnIt = columnExprList.iterator();
while (columnIt.hasNext()) {
ValueExpressionColumn colExpr = (ValueExpressionColumn) columnIt
.next();
// DataType dataType = colExpr.getDataType();
System.out.println("effective result column: "
+ colExpr.getName());// + " with data type: " +
// dataType.getName());
}
List tableList = StatementHelper.getTablesForStatement(resultObject);
// List tableList = StatementHelper.getTablesForStatement(querySelect);
for (Object obj : tableList) {
TableReference t = (TableReference) obj;
System.out.println(t.getName());
}
} catch (SQLParserException spe) {
// handle the syntax error
System.out.println(spe.getMessage());
@SuppressWarnings("unchecked")
List<SQLParseErrorInfo> syntacticErrors = spe.getErrorInfoList();
Iterator<SQLParseErrorInfo> itr = syntacticErrors.iterator();
while (itr.hasNext()) {
SQLParseErrorInfo errorInfo = (SQLParseErrorInfo) itr.next();
// Example usage of the SQLParseErrorInfo object
// the error message
String errorMessage = errorInfo.getParserErrorMessage();
String expectedText = errorInfo.getExpectedText();
String errorSourceText = errorInfo.getErrorSourceText();
// the line numbers of error
int errorLine = errorInfo.getLineNumberStart();
int errorColumn = errorInfo.getColumnNumberStart();
System.err.println("Error in line " + errorLine + ", column "
+ errorColumn + ": " + expectedText + " "
+ errorMessage + " " + errorSourceText);
}
} catch (SQLParserInternalException spie) {
// handle the exception
System.out.println(spie.getMessage());
}
System.exit(0);
}
}
