|
1
|
|
|
<?php |
|
2
|
|
|
namespace Intersvyaz\SqlParser; |
|
3
|
|
|
|
|
4
|
|
|
class Parser |
|
5
|
|
|
{ |
|
6
|
|
|
|
|
7
|
|
|
/** |
|
8
|
|
|
* @var string Текст sql запроса, который надо преобразовать |
|
9
|
|
|
*/ |
|
10
|
|
|
private $sql; |
|
11
|
|
|
/** |
|
12
|
|
|
* @var array параметры, влияющие на парсинг sql запроса |
|
13
|
|
|
*/ |
|
14
|
|
|
private $params; |
|
15
|
|
|
/** |
|
16
|
|
|
* @var array "упрощённый" список параметров, для кеширования |
|
17
|
|
|
*/ |
|
18
|
|
|
private $simplifiedParams; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* @param string $sql |
|
22
|
|
|
* @param array $params |
|
23
|
|
|
*/ |
|
24
|
19 |
|
public function __construct($sql, $params = []) |
|
25
|
|
|
{ |
|
26
|
19 |
|
if (substr($sql, -4) === '.sql') { |
|
27
|
|
|
$this->sql = file_get_contents($sql); |
|
28
|
|
|
} else { |
|
29
|
19 |
|
$this->sql = $sql; |
|
30
|
|
|
} |
|
31
|
|
|
|
|
32
|
19 |
|
$this->params = $params; |
|
33
|
19 |
|
$this->parseSql(); |
|
34
|
19 |
|
} |
|
35
|
|
|
|
|
36
|
|
|
/** |
|
37
|
|
|
* @return string готовый sql запрос |
|
38
|
|
|
*/ |
|
39
|
12 |
|
public function getSql() |
|
40
|
|
|
{ |
|
41
|
12 |
|
return $this->sql; |
|
42
|
|
|
} |
|
43
|
|
|
|
|
44
|
|
|
/** |
|
45
|
|
|
* @inheritdoc |
|
46
|
|
|
*/ |
|
47
|
12 |
|
public function __toString() |
|
48
|
|
|
{ |
|
49
|
12 |
|
return $this->getSql(); |
|
50
|
|
|
} |
|
51
|
|
|
|
|
52
|
|
|
/** |
|
53
|
|
|
* @return array "упрощённый" список параметров |
|
54
|
|
|
*/ |
|
55
|
7 |
|
public function getSimplifiedParams() |
|
56
|
|
|
{ |
|
57
|
7 |
|
if (!isset($this->simplifiedParams)) { |
|
58
|
7 |
|
$this->simplifiedParams = $this->simplifyParams($this->params); |
|
59
|
7 |
|
} |
|
60
|
|
|
|
|
61
|
7 |
|
return $this->simplifiedParams; |
|
62
|
|
|
} |
|
63
|
|
|
|
|
64
|
|
|
/** |
|
65
|
|
|
* Конвертирует параметры запроса из расширенного формата в параметры пригодные для \yii\db\Command::bindValues. |
|
66
|
|
|
* @param array $params Параметры построения запроса. |
|
67
|
|
|
* @return array |
|
68
|
|
|
*/ |
|
69
|
7 |
|
private function simplifyParams($params) |
|
70
|
|
|
{ |
|
71
|
7 |
|
if (empty($params)) { |
|
72
|
1 |
|
return $params; |
|
73
|
|
|
} |
|
74
|
|
|
|
|
75
|
6 |
|
$newParams = []; |
|
76
|
6 |
|
foreach ($params as $key => $value) { |
|
77
|
6 |
|
$key = ':' . ltrim($key, ':'); |
|
78
|
6 |
|
if (is_array($value)) { |
|
79
|
5 |
|
if (isset($value[0]) && is_array($value[0])) { |
|
80
|
1 |
|
foreach ($value[0] as $valKey => $valVal) { |
|
81
|
1 |
|
$newParams[$key . '_' . $valKey] = $valVal; |
|
82
|
1 |
|
} |
|
83
|
5 |
|
} elseif (!isset($value['bind']) || $value['bind'] === true) { |
|
84
|
2 |
|
if (isset($value[0]) && isset($value[1])) { |
|
85
|
1 |
|
$newParams[$key] = [$value[0], $value[1]]; |
|
86
|
2 |
|
} elseif (isset($value[0])) { |
|
87
|
1 |
|
$newParams[$key] = $value[0]; |
|
88
|
1 |
|
} |
|
89
|
2 |
|
} |
|
90
|
5 |
|
} else { |
|
91
|
1 |
|
$newParams[$key] = $value; |
|
92
|
|
|
} |
|
93
|
6 |
|
} |
|
94
|
|
|
|
|
95
|
6 |
|
return $newParams; |
|
96
|
|
|
} |
|
97
|
|
|
|
|
98
|
|
|
/** |
|
99
|
|
|
* Функция разбора и подготовки текста sql запроса. |
|
100
|
|
|
*/ |
|
101
|
19 |
|
private function parseSql() |
|
102
|
|
|
{ |
|
103
|
|
|
// Разбор многострочных комментариев |
|
104
|
19 |
|
if (preg_match_all('#/\*(\w+)(.+?)\*/#s', $this->sql, $matches)) { |
|
105
|
9 |
|
$count = count($matches[0]); |
|
106
|
9 |
|
for ($i = 0; $i < $count; $i++) { |
|
107
|
9 |
|
$this->replaceComment($matches[0][$i], $matches[2][$i], $matches[1][$i]); |
|
108
|
9 |
|
} |
|
109
|
9 |
|
} |
|
110
|
|
|
|
|
111
|
|
|
// Многоитерационный разбор однострчных комментариев |
|
112
|
19 |
|
while (true) { |
|
113
|
19 |
|
if (preg_match_all('#--\*(\w+)(.+)#', $this->sql, $matches)) { |
|
114
|
10 |
|
$count = count($matches[0]); |
|
115
|
10 |
|
for ($i = 0; $i < $count; $i++) { |
|
116
|
10 |
|
$this->replaceComment($matches[0][$i], $matches[2][$i], $matches[1][$i]); |
|
117
|
10 |
|
} |
|
118
|
10 |
|
} else { |
|
119
|
19 |
|
break; |
|
120
|
|
|
} |
|
121
|
10 |
|
} |
|
122
|
|
|
|
|
123
|
19 |
|
$this->sql = preg_replace("/\n+/", "\n", trim($this->sql)); |
|
124
|
19 |
|
} |
|
125
|
|
|
|
|
126
|
|
|
/** |
|
127
|
|
|
* Заменяем коментарий в запросе на соответствующе преобразованный блок или удаляем. |
|
128
|
|
|
* @param string $comment Заменямый комментарий. |
|
129
|
|
|
* @param string $queryInComment Текст внутри комментария. |
|
130
|
|
|
* @param string $paramName Имя параметра. |
|
131
|
|
|
*/ |
|
132
|
10 |
|
private function replaceComment($comment, $queryInComment, $paramName) |
|
133
|
|
|
{ |
|
134
|
10 |
|
$param = $this->getParam($paramName); |
|
135
|
10 |
|
if ($param) { |
|
136
|
9 |
|
$paramName = $param[0]; |
|
137
|
9 |
|
$paramValue = $param[1]; |
|
138
|
9 |
|
if (is_array($paramValue)) { |
|
139
|
3 |
|
$value = isset($paramValue[0]) ? $paramValue[0] : null; |
|
140
|
3 |
|
$bind = isset($paramValue['bind']) ? $paramValue['bind'] : true; |
|
141
|
3 |
|
} else { |
|
142
|
7 |
|
$value = $paramValue; |
|
143
|
7 |
|
$bind = true; |
|
144
|
|
|
} |
|
145
|
|
|
|
|
146
|
9 |
|
if ($bind === true && is_array($value)) { |
|
147
|
1 |
|
$valArr = []; |
|
148
|
1 |
|
foreach (array_keys($value) as $keyVal) { |
|
149
|
1 |
|
$valArr[] = ':' . $paramName . '_' . $keyVal; |
|
150
|
1 |
|
} |
|
151
|
1 |
|
$replacement = implode(',', $valArr); |
|
152
|
1 |
|
$queryInComment = preg_replace('/:@' . preg_quote($paramName) . '/', $replacement, $queryInComment); |
|
153
|
9 |
|
} elseif ($bind === 'text') { |
|
154
|
1 |
|
$queryInComment = preg_replace('/' . preg_quote($paramName) . '/', $value, $queryInComment); |
|
155
|
1 |
|
} |
|
156
|
9 |
|
} else { |
|
157
|
9 |
|
$queryInComment = ''; |
|
158
|
|
|
} |
|
159
|
|
|
|
|
160
|
10 |
|
$this->sql = str_replace($comment, $queryInComment, $this->sql); |
|
161
|
10 |
|
} |
|
162
|
|
|
|
|
163
|
|
|
/** |
|
164
|
|
|
* Ищет параметр в массиве $this->params |
|
165
|
|
|
* @param string $name имя параметра |
|
166
|
|
|
* @return array|bool массив ['имя_параметра_без_ведущего_двоеточия', 'значение_параметра'] или ложь если параметра нет |
|
167
|
|
|
*/ |
|
168
|
10 |
|
private function getParam($name) |
|
169
|
|
|
{ |
|
170
|
10 |
|
$name = ltrim($name, ':'); |
|
171
|
|
|
|
|
172
|
10 |
|
if (array_key_exists($name, $this->params)) { |
|
173
|
8 |
|
return [$name, $this->params[$name]]; |
|
174
|
9 |
|
} elseif (array_key_exists(':' . $name, $this->params)) { |
|
175
|
1 |
|
return [$name, $this->params[':' . $name]]; |
|
176
|
|
|
} |
|
177
|
|
|
|
|
178
|
9 |
|
return false; |
|
179
|
|
|
} |
|
180
|
|
|
} |
|
181
|
|
|
|
intersvyaz /
yii2-sqlparser
