sqlparseによるSQLのフォーマット

SQLをローカルでフォーマットするツールを探していてsqlparseというツールを見つけたので試してみました。

https://github.com/andialbrecht/sqlparse

# インストール

sqlparseはPython製でpipを使ってインストールすることができます。

```bash
$ pip install sqlparse
```

# ウェブ上で使用する

sqlparseはSQLFormatというオンラインサービスで使用されており、ブラウザやAPIでSQLを渡してフォーマットすることができます。

[SQLFormat - Online SQL Formatter](http://sqlformat.org/)

# コマンドラインで使用する

sqlparseをインストールすると使用可能になるsqlformatというコマンドに
ファイルか標準入力でSQLを入力するとフォーマットされたSQLが出力されます。

```-r``` で改行・インデント、```-k upper``` でキーワードを大文字にすることができます。その他のオプションについては```-h``` オプションで確認することができます。

```bash
$ SQL='select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";'
$ echo "$SQL" | sqlformat -r -k upper -
SELECT t1.c1 A,
       t2.c2 B
FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.c1 = "HOGE";
```

# ライブラリとして使用する

sqlparseはライブラリとして使用することもできます。

## フォーマット

sqlparse.formatによりsqlformatコマンドと同様にフォーマットを行うことができます。

```pycon
>>> import sqlparse
>>> sql = 'select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";'
>>> print sqlparse.format(sql, reindent=True, keyword_case='upper')
SELECT t1.c1 A,
       t2.c2 B
FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t1.c1 = "HOGE";

```

## パース

sqlparse.parseでパース結果を取得することができます。sqlparse.parseはSQL文を表すStatementのタプルを返します。

```pycon
>>> parsed = sqlparse.parse(sql)
>>> parsed
(<Statement 'select...' at 0x1077c6160>,)
```

sqlparse.parseはの入力には次のように複数のSQL文を含めることもできます。この場合、タプルの要素が複数になります。

```pycon
>>> sqlparse.parse("select 1; select 2;")
(<Statement 'select...' at 0x1077c6958>,
 <Statement 'select...' at 0x1077c6848>)
```

Statement.tokensによってトークンのリストを取得することができます。

```pycon
>>> stmt = parsed[0]
>>> for t in stmt.tokens:
...     print type(t), t
...     
<class 'sqlparse.sql.Token'> select
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.IdentifierList'> t1.c1 A, t2.c2 B
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Token'> from
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Identifier'> t1
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Token'> join
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Identifier'> t2
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Token'> on
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Comparison'> t1.id = t2.id
<class 'sqlparse.sql.Token'>  
<class 'sqlparse.sql.Where'> where t1.c1 = "HOGE";
```

# フォーマット方法のカスタマイズ

SQLのフォーマットの仕方には色々とスタイルがあります。例えば、SELECT句の列名はsqlformatの出力として得られるように行末にカンマをつけて並べられることもあれば、行の先頭にカンマをつけて並べられることもあります。

formatメソッドのオプションである程度フォーマット方法を変更することはできますが、カンマの位置などオプションだけでは対応できないものもあります。

sqlparseがフォーマット方法を制御しているフィルタを変更しSELECT句の列名をカンマを先頭にして並べるよう変更してみます。

まず、sqlparse.formatの処理の流れを理解するため実装をみると以下のようになっています。

```py:__init__.py
def format(sql, **options):
    """Format *sql* according to *options*.
    Available options are documented in :ref:`formatting`.
    In addition to the formatting options this function accepts the
    keyword "encoding" which determines the encoding of the statement.
    :returns: The formatted SQL statement as string.
    """
    encoding = options.pop('encoding', None)
    stack = engine.FilterStack()
    options = formatter.validate_options(options)
    stack = formatter.build_filter_stack(stack, options)
    stack.postprocess.append(filters.SerializerUnicode())
    return ''.join(stack.run(sql, encoding))
```

```stack = engine.FilterStack()```でパーサーを作成、```stack.run(sql, encoding)```で与えられたSQLのパースを実行しています。

FilterStackにはフォーマット方法を制御するフィルタを追加することができます。sqlparse.formatでは```formatter.validate_options```、```formatter.build_filter_stack```でオプションの値に基づいてセットしています。

```stack.postprocess.append(filters.SerializerUnicode())``` はトークンのリストを文字列に変換するためのフィルタです。

今回は簡単のためオプションを取らない例を考えます。手始めに入力をそのまま出力してみます。

```pycon
>>> from sqlparse import engine
>>> stack = engine.FilterStack()
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select t1.c1 A, t2.c2 B from t1 join t2 on t1.id = t2.id where t1.c1 = "HOGE";
```

```sqlparse.filters.ReindentFilter``` を追加すると結果がインデントされるようになります。
```ReindentFilter``` などのSQLを処理するフィルタを使用する場合には、```stack.enable_grouping()``` でトークンのグルーピングを有効にしておく必要があります。

```pycon
>>> from sqlparse.filters import ReindentFilter
>>> stack = engine.FilterStack()
>>> stack.enable_grouping()
>>> stack.stmtprocess.append(ReindentFilter())
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select t1.c1 A,
       t2.c2 B
from t1
join t2 on t1.id = t2.id
where t1.c1 = "HOGE";
```

カンマを先頭にもってくるよう動作を変えるために、```ReindentFilter``` の```_process_identifierlist``` をオーバーライドしたクラスを作成します。

```py
from sqlparse.sql import Function                                                  
                                                                                   
class MyReindentFilter(ReindentFilter):                                            
    def _process_identifierlist(self, tlist):                                      
        identifiers = list(tlist.get_identifiers())                                
        if len(identifiers) > 1 and not tlist.within(Function):                    
                                                                                   
            first = identifiers[0]                                                 
            self.indent += 1                                                       
            tlist.insert_before(first, self.nl())                                  
            self.offset -= 1                                                       
            tlist.insert_after(first, self.nl())                                   
                                                                                   
            for token in identifiers[1:len(identifiers)-1]:                        
                prev = tlist.token_prev(tlist.token_index(token), False)           
                if prev and prev.is_whitespace():                                  
                    prev.value = ''                                                
                tlist.insert_after(token, self.nl())                               
                                                                                   
            last = identifiers[-1]                                                 
            prev = tlist.token_prev(tlist.token_index(last), False)                
            if prev and prev.is_whitespace():                                      
                prev.value = ''                                                    
            self.offset += 1                                                       
            self.indent -= 1                                                       
                                                                                   
        self._process_default(tlist) 
```

```ReindentFilter``` の代わりに```MyReindentFilter``` を使用するとカンマが先頭に来るようフォーマットされていることがわかります。

```pycon
>>> stack = engine.FilterStack()
>>> stack.enable_grouping()
>>> stack.stmtprocess.append(MyReindentFilter())
>>> stack.postprocess.append(filters.SerializerUnicode())
>>> print stack.run(sql).next()
select
  t1.c1 A
 ,t2.c2 B
from t1
join t2 on t1.id = t2.id
where t1.c1 = "HOGE"
  and t2.c2 = 1;
```

サブクエリでも次のようにインデントの深さに合わせて正しく列名を並べられています。

```pycon
>>> print stack.run('select a, b, c FROM (select a, b, c FROM t1) t2;').next()
select
  a
 ,b
 ,c
FROM
  (select
     a
    ,b
    ,c
   FROM t1) t2;
```

```ReindentFilter``` の他のメソッドや他のフィルタによって、WHERE句のインデント方法やキーワードの大文字・小文字などについても制御することが可能です。