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