1 | <?php |
||
2 | /** |
||
3 | * QueryBuilderParser.php |
||
4 | * |
||
5 | * -Description- |
||
6 | * |
||
7 | * This program is free software: you can redistribute it and/or modify |
||
8 | * it under the terms of the GNU General Public License as published by |
||
9 | * the Free Software Foundation, either version 3 of the License, or |
||
10 | * (at your option) any later version. |
||
11 | * |
||
12 | * This program is distributed in the hope that it will be useful, |
||
13 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
||
14 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.See the |
||
15 | * GNU General Public License for more details. |
||
16 | * |
||
17 | * You should have received a copy of the GNU General Public License |
||
18 | * along with this program. If not, see <https://www.gnu.org/licenses/>. |
||
19 | * |
||
20 | * @link https://www.librenms.org |
||
21 | * |
||
22 | * @copyright 2018 Tony Murray |
||
23 | * @author Tony Murray <[email protected]> |
||
24 | */ |
||
25 | |||
26 | namespace LibreNMS\Alerting; |
||
27 | |||
28 | use Illuminate\Support\Str; |
||
29 | use LibreNMS\Config; |
||
30 | use LibreNMS\DB\Schema; |
||
31 | |||
32 | class QueryBuilderParser implements \JsonSerializable |
||
33 | { |
||
34 | protected static $legacy_operators = [ |
||
35 | '=' => 'equal', |
||
36 | '!=' => 'not_equal', |
||
37 | '~' => 'regex', |
||
38 | '!~' => 'not_regex', |
||
39 | '<' => 'less', |
||
40 | '>' => 'greater', |
||
41 | '<=' => 'less_or_equal', |
||
42 | '>=' => 'greater_or_equal', |
||
43 | ]; |
||
44 | protected static $operators = [ |
||
45 | 'equal' => '=', |
||
46 | 'not_equal' => '!=', |
||
47 | 'less' => '<', |
||
48 | 'less_or_equal' => '<=', |
||
49 | 'greater' => '>', |
||
50 | 'greater_or_equal' => '>=', |
||
51 | 'between' => 'BETWEEN', |
||
52 | 'not_between' => 'NOT BETWEEN', |
||
53 | 'begins_with' => 'LIKE', |
||
54 | 'not_begins_with' => 'NOT LIKE', |
||
55 | 'contains' => 'LIKE', |
||
56 | 'not_contains' => 'NOT LIKE', |
||
57 | 'ends_with' => 'LIKE', |
||
58 | 'not_ends_with' => 'NOT LIKE', |
||
59 | 'is_empty' => '=', |
||
60 | 'is_not_empty' => '!=', |
||
61 | 'is_null' => 'IS NULL', |
||
62 | 'is_not_null' => 'IS NOT NULL', |
||
63 | 'regex' => 'REGEXP', |
||
64 | 'not_regex' => 'NOT REGEXP', |
||
65 | 'in' => 'IN', |
||
66 | 'not_in' => 'NOT IN', |
||
67 | ]; |
||
68 | |||
69 | protected static $values = [ |
||
70 | 'between' => '? AND ?', |
||
71 | 'not_between' => '? AND ?', |
||
72 | 'begins_with' => "'?%'", |
||
73 | 'not_begins_with' => "'?%'", |
||
74 | 'contains' => "'%?%'", |
||
75 | 'not_contains' => "'%?%'", |
||
76 | 'ends_with' => "'%?'", |
||
77 | 'not_ends_with' => "'%?'", |
||
78 | 'is_null' => '', |
||
79 | 'is_not_null' => '', |
||
80 | 'is_empty' => "''", |
||
81 | 'is_not_empty' => "''", |
||
82 | ]; |
||
83 | |||
84 | protected $builder; |
||
85 | protected $schema; |
||
86 | private $tables; |
||
87 | |||
88 | private function __construct(array $builder) |
||
89 | { |
||
90 | $this->builder = $builder; |
||
91 | $this->schema = new Schema(); |
||
92 | } |
||
93 | |||
94 | /** |
||
95 | * Get all tables used by this rule |
||
96 | * |
||
97 | * @return array |
||
98 | */ |
||
99 | public function getTables() |
||
100 | { |
||
101 | if (! isset($this->tables)) { |
||
102 | $this->tables = $this->findTablesRecursive($this->builder); |
||
103 | } |
||
104 | |||
105 | return $this->tables; |
||
106 | } |
||
107 | |||
108 | /** |
||
109 | * Recursively find tables (including expanding macros) in the given rules |
||
110 | * |
||
111 | * @param array $rules |
||
112 | * @return array List of tables found in rules |
||
113 | */ |
||
114 | protected function findTablesRecursive($rules) |
||
115 | { |
||
116 | $tables = []; |
||
117 | |||
118 | foreach ($rules['rules'] as $rule) { |
||
119 | if (array_key_exists('rules', $rule)) { |
||
120 | $tables = array_merge($this->findTablesRecursive($rule), $tables); |
||
121 | } elseif (Str::contains($rule['field'], '.')) { |
||
122 | [$table, $column] = explode('.', $rule['field']); |
||
123 | |||
124 | if ($table == 'macros') { |
||
125 | $tables = array_merge($this->expandMacro($rule['field'], true), $tables); |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
126 | } else { |
||
127 | $tables[] = $table; |
||
128 | } |
||
129 | } |
||
130 | } |
||
131 | |||
132 | // resolve glue tables (remove duplicates) |
||
133 | foreach (array_keys(array_flip($tables)) as $table) { |
||
134 | $rp = $this->schema->findRelationshipPath($table); |
||
135 | if ($rp) { |
||
136 | $tables = array_merge($rp, $tables); |
||
137 | } |
||
138 | } |
||
139 | |||
140 | // remove duplicates |
||
141 | return array_keys(array_flip($tables)); |
||
142 | } |
||
143 | |||
144 | /** |
||
145 | * Initialize this from json generated by jQuery QueryBuilder |
||
146 | * |
||
147 | * @param string|array $json |
||
148 | * @return static |
||
149 | */ |
||
150 | public static function fromJson($json) |
||
151 | { |
||
152 | if (! is_array($json)) { |
||
153 | $json = json_decode($json, true) ?: []; |
||
154 | } |
||
155 | |||
156 | return new static($json); |
||
157 | } |
||
158 | |||
159 | /** |
||
160 | * Initialize this from a legacy LibreNMS rule |
||
161 | * |
||
162 | * @param string $query |
||
163 | * @return static |
||
164 | */ |
||
165 | public static function fromOld($query) |
||
166 | { |
||
167 | $condition = null; |
||
168 | $rules = []; |
||
169 | $filter = new QueryBuilderFilter(); |
||
170 | |||
171 | $split = array_chunk(preg_split('/(&&|\|\|)/', $query, -1, PREG_SPLIT_DELIM_CAPTURE), 2); |
||
172 | |||
173 | foreach ($split as $chunk) { |
||
174 | if (count($chunk) < 2 && empty($chunk[0])) { |
||
175 | continue; // likely the ending && or || |
||
176 | } |
||
177 | |||
178 | @[$rule_text, $rule_operator] = $chunk; |
||
179 | if (! isset($condition)) { |
||
180 | // only allow one condition. Since old rules had no grouping, this should hold logically |
||
181 | $condition = ($rule_operator == '||' ? 'OR' : 'AND'); |
||
182 | } |
||
183 | |||
184 | @[$field, $op, $value] = preg_split('/ *([!=<>~]{1,2}) */', trim($rule_text), 2, PREG_SPLIT_DELIM_CAPTURE); |
||
185 | $field = ltrim($field, '%'); |
||
186 | |||
187 | // for rules missing values just use '= 1' |
||
188 | $operator = isset(self::$legacy_operators[$op]) ? self::$legacy_operators[$op] : 'equal'; |
||
189 | if (is_null($value)) { |
||
190 | $value = '1'; |
||
191 | } else { |
||
192 | // value is a field, mark it with backticks |
||
193 | if (Str::startsWith($value, '%')) { |
||
194 | $value = '`' . ltrim($value, '%') . '`'; |
||
195 | } else { |
||
196 | // but if it has quotes just remove the % |
||
197 | $value = ltrim(trim($value, '"'), '%'); |
||
198 | } |
||
199 | |||
200 | // replace regex placeholder, don't think we can safely convert to like operators |
||
201 | if ($operator == 'regex' || $operator == 'not_regex') { |
||
202 | $value = str_replace('@', '.*', $value); |
||
203 | } |
||
204 | } |
||
205 | |||
206 | $filter_item = $filter->getFilter($field); |
||
207 | |||
208 | $type = $filter_item['type']; |
||
209 | $input = isset($filter_item['input']) ? $filter_item['input'] : 'text'; |
||
210 | |||
211 | $rules[] = [ |
||
212 | 'id' => $field, |
||
213 | 'field' => $field, |
||
214 | 'type' => $type, |
||
215 | 'input' => $input, |
||
216 | 'operator' => $operator, |
||
217 | 'value' => $value, |
||
218 | ]; |
||
219 | } |
||
220 | |||
221 | $builder = [ |
||
222 | 'condition' => $condition, |
||
223 | 'rules' => $rules, |
||
224 | 'valid' => true, |
||
225 | ]; |
||
226 | |||
227 | return new static($builder); |
||
228 | } |
||
229 | |||
230 | /** |
||
231 | * Get the SQL for this rule, ready to execute with device_id supplied as the parameter |
||
232 | * If $expand is false, this will return a more readable representation of the rule, but not executable. |
||
233 | * |
||
234 | * @param bool $expand |
||
235 | * @return null|string The rule or null if this is invalid. |
||
236 | */ |
||
237 | public function toSql($expand = true) |
||
238 | { |
||
239 | if (empty($this->builder) || ! array_key_exists('condition', $this->builder)) { |
||
240 | return null; |
||
241 | } |
||
242 | |||
243 | $sql = ''; |
||
244 | $wrap = false; |
||
245 | |||
246 | if ($expand) { |
||
247 | $sql = 'SELECT * FROM ' . implode(',', $this->getTables()); |
||
248 | $sql .= ' WHERE (' . implode(' AND ', $this->generateGlue()) . ') AND '; |
||
249 | |||
250 | // only wrap in ( ) if the condition is OR and there is more than one rule |
||
251 | $wrap = $this->builder['condition'] == 'OR' && count($this->builder['rules']) > 1; |
||
252 | } |
||
253 | |||
254 | return $sql . $this->parseGroup($this->builder, $expand, $wrap); |
||
255 | } |
||
256 | |||
257 | /** |
||
258 | * Parse a rule group |
||
259 | * |
||
260 | * @param array $rule |
||
261 | * @param bool $expand Expand macros? |
||
262 | * @param bool $wrap Wrap in parenthesis |
||
263 | * @return string |
||
264 | */ |
||
265 | private function parseGroup($rule, $expand = false, $wrap = true) |
||
266 | { |
||
267 | $group_rules = []; |
||
268 | |||
269 | foreach ($rule['rules'] as $group_rule) { |
||
270 | if (array_key_exists('condition', $group_rule)) { |
||
271 | $group_rules[] = $this->parseGroup($group_rule, $expand); |
||
272 | } else { |
||
273 | $group_rules[] = $this->parseRule($group_rule, $expand); |
||
274 | } |
||
275 | } |
||
276 | |||
277 | $sql = implode(" {$rule['condition']} ", $group_rules); |
||
278 | |||
279 | if ($wrap) { |
||
280 | return "($sql)"; |
||
281 | } else { |
||
282 | return "$sql"; |
||
283 | } |
||
284 | } |
||
285 | |||
286 | /** |
||
287 | * Parse a rule |
||
288 | * |
||
289 | * @param array $rule |
||
290 | * @param bool $expand Expand macros? |
||
291 | * @return string |
||
292 | */ |
||
293 | protected function parseRule($rule, $expand = false) |
||
294 | { |
||
295 | $field = $rule['field']; |
||
296 | $builder_op = $rule['operator']; |
||
297 | $op = self::$operators[$builder_op]; |
||
298 | $value = $rule['value']; |
||
299 | |||
300 | if (is_string($value) && Str::startsWith($value, '`') && Str::endsWith($value, '`')) { |
||
301 | // pass through value such as field |
||
302 | $value = trim($value, '`'); |
||
303 | if ($expand) { |
||
304 | $value = $this->expandMacro($value); |
||
305 | } |
||
306 | } elseif (isset(self::$values[$builder_op])) { |
||
307 | // wrap values as needed (is null values don't contain ? so '' is returned) |
||
308 | $values = (array) $value; |
||
309 | $value = preg_replace_callback('/\?/', function ($matches) use (&$values) { |
||
310 | return array_shift($values); |
||
311 | }, self::$values[$builder_op]); |
||
312 | } elseif (! is_numeric($value)) { |
||
313 | // wrap quotes around non-numeric values |
||
314 | $value = "\"$value\""; |
||
315 | } |
||
316 | |||
317 | if ($expand) { |
||
318 | $field = $this->expandMacro($field); |
||
319 | } |
||
320 | |||
321 | return trim("$field $op $value"); |
||
322 | } |
||
323 | |||
324 | /** |
||
325 | * Expand macro to sql |
||
326 | * |
||
327 | * @param string $subject |
||
328 | * @param bool $tables_only Used when finding tables in query returns an array instead of sql string |
||
329 | * @param int $depth_limit |
||
330 | * @return string|array |
||
331 | */ |
||
332 | protected function expandMacro($subject, $tables_only = false, $depth_limit = 20) |
||
333 | { |
||
334 | if (! Str::contains($subject, 'macros.')) { |
||
335 | return $subject; |
||
336 | } |
||
337 | |||
338 | $macros = Config::get('alert.macros.rule'); |
||
339 | |||
340 | $count = 0; |
||
341 | while ($count++ < $depth_limit && Str::contains($subject, 'macros.')) { |
||
342 | $subject = preg_replace_callback('/%?macros.([^ =()]+)/', function ($matches) use ($macros) { |
||
343 | $name = $matches[1]; |
||
344 | if (isset($macros[$name])) { |
||
345 | return $macros[$name]; |
||
346 | } else { |
||
347 | return $matches[0]; // this isn't a macro, don't replace |
||
348 | } |
||
349 | }, $subject); |
||
350 | } |
||
351 | |||
352 | if ($tables_only) { |
||
353 | preg_match_all('/%([^%.]+)\./', $subject, $matches); |
||
354 | |||
355 | return array_unique($matches[1]); |
||
356 | } |
||
357 | |||
358 | // clean leading % |
||
359 | $subject = preg_replace('/%([^%.]+)\./', '$1.', $subject); |
||
360 | |||
361 | // wrap entire macro result in parenthesis if needed |
||
362 | if (! (Str::startsWith($subject, '(') && Str::endsWith($subject, ')'))) { |
||
363 | $subject = "($subject)"; |
||
364 | } |
||
365 | |||
366 | return $subject; |
||
367 | } |
||
368 | |||
369 | /** |
||
370 | * Generate glue and first part of sql query for this rule |
||
371 | * |
||
372 | * @param string $target the name of the table to target, for alerting, this should be devices |
||
373 | * @return array |
||
374 | */ |
||
375 | protected function generateGlue($target = 'devices') |
||
376 | { |
||
377 | $tables = $this->getTables(); // get all tables in query |
||
378 | |||
379 | // always add the anchor to the target table |
||
380 | $anchor = $target . '.' . $this->schema->getPrimaryKey($target) . ' = ?'; |
||
381 | $glue = [$anchor]; |
||
382 | |||
383 | foreach ($tables as $table) { |
||
384 | $path = $this->schema->findRelationshipPath($table, $target); |
||
385 | if ($path) { |
||
386 | foreach (array_pairs($path) as $pair) { |
||
387 | [$left, $right] = $pair; |
||
388 | $glue[] = $this->getGlue($left, $right); |
||
389 | } |
||
390 | } |
||
391 | } |
||
392 | |||
393 | // remove duplicates |
||
394 | return array_unique($glue); |
||
395 | } |
||
396 | |||
397 | /** |
||
398 | * Get glue sql between tables. Resolve fields to use. |
||
399 | * |
||
400 | * @param string $parent |
||
401 | * @param string $child |
||
402 | * @return string |
||
403 | */ |
||
404 | public function getGlue($parent, $child) |
||
405 | { |
||
406 | // first check to see if there is a single shared column name ending with _id |
||
407 | $shared_keys = array_filter(array_intersect( |
||
408 | $this->schema->getColumns($parent), |
||
409 | $this->schema->getColumns($child) |
||
410 | ), function ($table) { |
||
411 | return Str::endsWith($table, '_id'); |
||
412 | }); |
||
413 | |||
414 | if (count($shared_keys) === 1) { |
||
415 | $shared_key = reset($shared_keys); |
||
416 | |||
417 | return "$parent.$shared_key = $child.$shared_key"; |
||
418 | } |
||
419 | |||
420 | $parent_key = $this->schema->getPrimaryKey($parent); |
||
421 | $flipped = empty($parent_key); |
||
422 | if ($flipped) { |
||
423 | // if the "parent" table doesn't have a primary key, flip them |
||
424 | [$parent, $child] = [$child, $parent]; |
||
425 | $parent_key = $this->schema->getPrimaryKey($parent); |
||
426 | } |
||
427 | $child_key = $parent_key; // assume the column names match |
||
428 | |||
429 | if (! $this->schema->columnExists($child, $child_key)) { |
||
430 | // if they don't match, guess the column name from the parent |
||
431 | if (Str::endsWith($parent, 'xes')) { |
||
432 | $child_key = substr($parent, 0, -2) . '_id'; |
||
433 | } else { |
||
434 | $child_key = preg_replace('/s$/', '_id', $parent); |
||
435 | } |
||
436 | |||
437 | if (! $this->schema->columnExists($child, $child_key)) { |
||
438 | $child_key = $this->schema->getPrimaryKey($child); |
||
439 | \Log::warning("QueryBuilderParser: Warning, guessing glue from $child.$child_key to $parent.$parent_key"); |
||
440 | } |
||
441 | } |
||
442 | |||
443 | if ($flipped) { |
||
444 | return "$child.$child_key = $parent.$parent_key"; |
||
445 | } |
||
446 | |||
447 | return "$parent.$parent_key = $child.$child_key"; |
||
448 | } |
||
449 | |||
450 | /** |
||
451 | * Get an array of this rule ready for jQuery QueryBuilder |
||
452 | * |
||
453 | * @return array |
||
454 | */ |
||
455 | public function toArray() |
||
456 | { |
||
457 | return $this->builder; |
||
458 | } |
||
459 | |||
460 | /** |
||
461 | * Specify data which should be serialized to JSON |
||
462 | * |
||
463 | * @link https://php.net/manual/en/jsonserializable.jsonserialize.php |
||
464 | * |
||
465 | * @return mixed data which can be serialized by <b>json_encode</b>, |
||
466 | * which is a value of any type other than a resource. |
||
467 | * |
||
468 | * @since 5.4.0 |
||
469 | */ |
||
470 | public function jsonSerialize() |
||
471 | { |
||
472 | return $this->builder; |
||
473 | } |
||
474 | } |
||
475 |