クエリービルダーのような機能を作るために、生のSQL文を解析して連想配列にできないか調べたところ、PHPでそのものズバリなライブラリがありました。
インストール
composer require greenlion/php-sql-parser
php -S localhost:8888 index.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をパースさせてみます。
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はこんな感じです。
{
"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と変換できることが確認できました。