SQL から参照しているテーブルを抽出 - FoundationDB SQL Parser

FoundationDB SQL Parser を使って SQL から参照しているテーブル（from 句で使われているテーブル）を抽出する方法をご紹介します。

FoundationDB SQL Parser 1.4.0

これを応用すれば SQL から CRUD 図を生成するような処理も比較的容易に実装できると思います。

今回使用したソースは http://github.com/fits/try_samples/tree/master/blog/20140920/

(1) SQL のパース

まずは、SQL をパースしてその結果を出力してみます。

パースは SQLParser オブジェクトの parseStatement メソッドへ SQL 文字列を渡すだけです。

また、parseStatement メソッドの戻り値である CursorNode オブジェクトの treePrint メソッドを呼び出せばパース結果を整形して出力してくれます。

sql_parse.groovy

@Grab('com.foundationdb:fdb-sql-parser:1.4.0')
import com.foundationdb.sql.parser.SQLParser

def parser = new SQLParser()
// SQL のパース
def node = parser.parseStatement(new File(args[0]).text)
// パース結果の出力
node.treePrint()

実行結果は下記の通りです。

実行結果

> groovy sql_parse.groovy sample1.sql

com.foundationdb.sql.parser.CursorNode@46271dd6
name: null
updateMode: UNSPECIFIED
statementType: SELECT
resultSet:  
    com.foundationdb.sql.parser.SelectNode@11bb571c
    isDistinct: false
    resultColumns:
        com.foundationdb.sql.parser.ResultColumnList@7c51f34b
        ・・・
    fromList:
        com.foundationdb.sql.parser.FromList@2a225dd7
        ・・・
        leftResultSet:
            com.foundationdb.sql.parser.FromBaseTable@125290e5
            tableName: orders
            ・・・
            correlation Name: od
            null
        rightResultSet:
            com.foundationdb.sql.parser.FromBaseTable@6fa34d52
            tableName: order_items
            ・・・
            correlation Name: oi
            null
        joinClause:
            com.foundationdb.sql.parser.BinaryRelationalOperatorNode@57576994
            operator: =
            ・・・
        [1]:
        com.foundationdb.sql.parser.FromBaseTable@1205bd62
        tableName: users
        ・・・
        correlation Name: us
        us
    whereClause: 
        com.foundationdb.sql.parser.AndNode@7ef27d7f
        operator: and
        methodName: and
        type: null
        leftOperand: 
            com.foundationdb.sql.parser.BinaryRelationalOperatorNode@490caf5f
            operator: >
            ・・・
        rightOperand: 
            com.foundationdb.sql.parser.BinaryRelationalOperatorNode@31920ade
            operator: =
            ・・・

上記で使用した SQL は下記の通り。

sample1.sql

select
    *
from
    orders od
    join order_items oi on
        od.order_id = oi.order_id
    ,
    users us
where
    od.ordered_date > ? and
    od.user_id = us.user_id

(2) テーブルの抽出

それでは、SQL 内で参照しているテーブルを抽出してみます。

テーブルの抽出には com.foundationdb.sql.parser.Visitor インターフェースを使うのが簡単です。（いわゆる Visitor パターン）

具体的には、下記処理を実装した Visitor インターフェースの実装オブジェクトを用意し、パース結果（CursorNode）の accept メソッドへ渡します。

(a) visit メソッドの引数が FromBaseTable オブジェクトだった場合にテーブル名を取得して保持する
(b) visit メソッドの戻り値は引数をそのまま返す

skipChildren メソッド等の Visitor インターフェースのその他メソッドは、全ノードを処理するように false を返すようにしておきます。

テーブル名（スキーマ名付き）を文字列で取得するには FromBaseTable オブジェクトの getOrigTableName メソッドで取得した TableName オブジェクトの getFullTableName メソッドを使います。

ここで、テーブル名取得系のメソッドにはいくつかバリエーションがありますが、主なものは下記の通りです。

クラス	メソッド	内容	戻り値の型
FromBaseTable	getOrigTableName	テーブル名	TableName
FromBaseTable	getTableName	テーブルの別名（別名が無ければテーブル名）	TableName
FromBaseTable	getCorrelationName	テーブルの別名（別名が無ければ null）	String
TableName	getFullTableName	スキーマ名付きテーブル名	String
TableName	getTableName	スキーマ名なしテーブル名	String

また、下記サンプルでは、テーブル名の重複を排除しテーブル名でソートするように TreeSet へテーブル名を登録するようにしています。

listup_tables.groovy

@Grab('com.foundationdb:fdb-sql-parser:1.4.0')
import com.foundationdb.sql.parser.FromBaseTable
import com.foundationdb.sql.parser.SQLParser
import com.foundationdb.sql.parser.Visitor

def tables = [] as TreeSet

// FromBaseTable （参照しているテーブル）を処理するための Visitor 実装
def tableSelector = [
    visit: { node -> 
        if (node instanceof FromBaseTable) {
            // テーブル名（スキーマ名付き）を取得して tables へ追加
            tables << node.origTableName.fullTableName
        }
        node
    },
    skipChildren: { node -> false },
    stopTraversal: { -> false },
    visitChildrenFirst: { node -> false}
] as Visitor

def parser = new SQLParser()

def node = parser.parseStatement(new File(args[0]).text)
// テーブル名の抽出
node.accept(tableSelector)
// テーブル名の出力
tables.each {
    println it
}

「(1) SQL のパース」で使用した sample1.sql からテーブル名を抽出すると下記のような結果になりました。

実行結果1

> groovy listup_tables.groovy sample1.sql

order_items
orders
users

更に、下記のような SQL からテーブル名を抽出してみます。

sample2.sql

select
    us.user_id,
    us.name,
    (select count(*) from REFUNDS rf where rf.user_id = us.user_id) as refund_count1,
    (select count(*) from SCH1.REFUNDS rf where rf.user_id = us.user_id) as refund_count2
from
    Users us
where
    us.user_id in (
        select
            ou.user_id
        from (
            select
                od.user_id,
                count(*) ct
            from
                orders od
                join order_items oi on
                    od.item_id = oi.item_id
            where
                oi.item_id in (
                    select item_id from special_items
                ) and
                od.ordered_date > ?
            group by
                od.user_id
        ) ou
        where
            ou.ct >= 5
    )

結果は下記の通り、テーブル名は全て小文字になりましたが正常に抽出されています。

getFullTableName でテーブル名を取得しているので、スキーマ名を付けたテーブルと付けなかったテーブルは別途出力されています。

実行結果2

> groovy listup_tables.groovy sample2.sql

order_items
orders
refunds
sch1.refunds
special_items
users