【PHP】SQL文を構文解析してJSONに変換する

クエリービルダーのような機能を作るために、生のSQL文を解析して連想配列にできないか調べたところ、PHPでそのものズバリなライブラリがありました。

[PHP-SQL-Parser](https://github.com/greenlion/PHP-SQL-Parser)

---

# インストール

```
composer require greenlion/php-sql-parser
php -S localhost:8888 index.php
```

# 検証

```php:index.php
<?php
require_once dirname(__FILE__) . '/vendor/autoload.php';

use PHPSQLParser\PHPSQLParser;
use PHPSQLParser\PHPSQLCreator;

$sql  = '';
$json = '';
if(!empty($_POST)){
    if(isset($_POST['sql'])){
         // SQL⇒連想配列⇒JSON
        $sql    = $_POST['sql'];
        $parser = new PHPSQLParser($sql, true);
        $parsed = $parser->parsed;
        $json   = json_encode($parsed, JSON_PRETTY_PRINT);

    }elseif(isset($_POST['json'])){
         // JSON⇒連想配列⇒SQL
        $json    = $_POST['json'];
        $creator = new PHPSQLCreator();
        $array   = json_decode($json, true);
        $sql     = $creator->create($array);
    }
}
?>
<html>
    <body>
        <h2>SQL</h2>
        <form action="/" method="post">
            <textarea rows="20" cols="100" name="sql"><?= $sql ?></textarea>
            <input type="submit" value="JSONに変換"/>
        </form>
        <hr>
        <h2>JSON</h2>
        <form action="/" method="post">
            <textarea rows="20" cols="100" name="json"><?= $json ?></textarea>
            <input type="submit" value="SQLに変換"/>
        </form>
    </body>
</html>
```

次のようなSQLをパースさせてみます。

```sql
SELECT A.col1
      ,B.col2
      ,B.SUM(col3)
  FROM tab1 A
 INNER JOIN tab2 B
    ON A.col1 = B.col2
 WHERE A.col1 = 'hoge'
GROUP BY A.col1, B.col2
ORDER BY 2 DESC
```

得られるJSONはこんな感じです。

```json
{
    "SELECT": [
        {
            "expr_type": "colref",
            "alias": false,
            "base_expr": "A.col1",
            "no_quotes": {
                "delim": ".",
                "parts": [
                    "A",
                    "col1"
                ]
            },
            "sub_tree": false,
            "delim": ",",
            "position": 7
        },
        {
            "expr_type": "colref",
            "alias": false,
            "base_expr": "B.col2",
            "no_quotes": {
                "delim": ".",
                "parts": [
                    "B",
                    "col2"
                ]
            },
            "sub_tree": false,
            "delim": ",",
            "position": 22
        },
        {
            "expr_type": "function",
            "alias": false,
            "base_expr": "B.SUM",
            "sub_tree": [
                {
                    "expr_type": "colref",
                    "base_expr": "col3",
                    "no_quotes": {
                        "delim": false,
                        "parts": [
                            "col3"
                        ]
                    },
                    "sub_tree": false,
                    "position": 43
                }
            ],
            "delim": false,
            "position": 37
        }
    ],
    "FROM": [
        {
            "expr_type": "table",
            "table": "tab1",
            "no_quotes": {
                "delim": false,
                "parts": [
                    "tab1"
                ]
            },
            "alias": {
                "as": false,
                "name": "A",
                "no_quotes": {
                    "delim": false,
                    "parts": [
                        "A"
                    ]
                },
                "base_expr": "A",
                "position": 62
            },
            "hints": false,
            "join_type": "JOIN",
            "ref_type": false,
            "ref_clause": false,
            "base_expr": "tab1 A",
            "sub_tree": false,
            "position": 57
        },
        {
            "expr_type": "table",
            "table": "tab2",
            "no_quotes": {
                "delim": false,
                "parts": [
                    "tab2"
                ]
            },
            "alias": {
                "as": false,
                "name": "B",
                "no_quotes": {
                    "delim": false,
                    "parts": [
                        "B"
                    ]
                },
                "base_expr": "B",
                "position": 82
            },
            "hints": false,
            "join_type": "JOIN",
            "ref_type": "ON",
            "ref_clause": [
                {
                    "expr_type": "colref",
                    "base_expr": "A.col1",
                    "no_quotes": {
                        "delim": ".",
                        "parts": [
                            "A",
                            "col1"
                        ]
                    },
                    "sub_tree": false,
                    "position": 92
                },
                {
                    "expr_type": "operator",
                    "base_expr": "=",
                    "sub_tree": false,
                    "position": 99
                },
                {
                    "expr_type": "colref",
                    "base_expr": "B.col2",
                    "no_quotes": {
                        "delim": ".",
                        "parts": [
                            "B",
                            "col2"
                        ]
                    },
                    "sub_tree": false,
                    "position": 101
                }
            ],
            "base_expr": "tab2 B\r\n    ON A.col1 = B.col2",
            "sub_tree": false,
            "position": 77
        }
    ],
    "WHERE": [
        {
            "expr_type": "colref",
            "base_expr": "A.col1",
            "no_quotes": {
                "delim": ".",
                "parts": [
                    "A",
                    "col1"
                ]
            },
            "sub_tree": false,
            "position": 116
        },
        {
            "expr_type": "operator",
            "base_expr": "=",
            "sub_tree": false,
            "position": 123
        },
        {
            "expr_type": "const",
            "base_expr": "'hoge'",
            "sub_tree": false,
            "position": 125
        }
    ],
    "GROUP": [
        {
            "expr_type": "colref",
            "base_expr": "A.col1",
            "no_quotes": {
                "delim": ".",
                "parts": [
                    "A",
                    "col1"
                ]
            },
            "sub_tree": false,
            "position": 142
        },
        {
            "expr_type": "colref",
            "base_expr": "B.col2",
            "no_quotes": {
                "delim": ".",
                "parts": [
                    "B",
                    "col2"
                ]
            },
            "sub_tree": false,
            "position": 150
        }
    ],
    "ORDER": [
        {
            "expr_type": "pos",
            "base_expr": "2",
            "direction": "DESC",
            "position": 167
        }
    ]
}
```

このJSONからSQL文に再変換もできます。
SQL⇔連想配列⇔JSONと変換できることが確認できました。