Total Complexity | 61 |
Total Lines | 430 |
Duplicated Lines | 0 % |
Changes | 0 |
Complex classes like QueryBuilderParser often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use QueryBuilderParser, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
31 | class QueryBuilderParser implements \JsonSerializable |
||
32 | { |
||
33 | private 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 | private 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 | private 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 | private $builder; |
||
82 | private $schema; |
||
83 | |||
84 | private function __construct(array $builder) |
||
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 | private 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 | list($rule_text, $rule_operator) = $chunk; |
||
171 | if (!isset($condition)) { |
||
172 | // only allow one condition. Since old rules had no grouping, this should hold logically |
||
173 | $condition = ($rule_operator == '||' ? 'OR' : 'AND'); |
||
174 | } |
||
175 | |||
176 | list($field, $op, $value) = preg_split('/ *([!=<>~]{1,2}) */', trim($rule_text), 2, PREG_SPLIT_DELIM_CAPTURE); |
||
177 | $field = ltrim($field, '%'); |
||
178 | |||
179 | // for rules missing values just use '= 1' |
||
180 | $operator = isset(self::$legacy_operators[$op]) ? self::$legacy_operators[$op] : 'equal'; |
||
181 | if (is_null($value)) { |
||
182 | $value = '1'; |
||
183 | } else { |
||
184 | $value = trim($value, '"'); |
||
185 | |||
186 | // value is a field, mark it with backticks |
||
187 | if (starts_with($value, '%')) { |
||
188 | $value = '`' . ltrim($value, '%') . '`'; |
||
189 | } |
||
190 | |||
191 | // replace regex placeholder, don't think we can safely convert to like operators |
||
192 | if ($operator == 'regex' || $operator == 'not_regex') { |
||
193 | $value = str_replace('@', '.*', $value); |
||
194 | } |
||
195 | } |
||
196 | |||
197 | $filter_item = $filter->getFilter($field); |
||
198 | |||
199 | $type = $filter_item['type']; |
||
200 | $input = isset($filter_item['input']) ? $filter_item['input'] : 'text'; |
||
201 | |||
202 | $rules[] = [ |
||
203 | 'id' => $field, |
||
204 | 'field' => $field, |
||
205 | 'type' => $type, |
||
206 | 'input' => $input, |
||
207 | 'operator' => $operator, |
||
208 | 'value' => $value, |
||
209 | ]; |
||
210 | } |
||
211 | |||
212 | $builder = [ |
||
213 | 'condition' => $condition, |
||
214 | 'rules' => $rules, |
||
215 | 'valid' => true, |
||
216 | ]; |
||
217 | |||
218 | return new static($builder); |
||
219 | } |
||
220 | |||
221 | /** |
||
222 | * Get the SQL for this rule, ready to execute with device_id supplied as the parameter |
||
223 | * If $expand is false, this will return a more readable representation of the rule, but not executable. |
||
224 | * |
||
225 | * @param bool $expand |
||
226 | * @return null|string The rule or null if this is invalid. |
||
227 | */ |
||
228 | public function toSql($expand = true) |
||
229 | { |
||
230 | if (empty($this->builder) || !array_key_exists('condition', $this->builder)) { |
||
231 | return null; |
||
232 | } |
||
233 | |||
234 | $sql = ''; |
||
235 | $wrap = false; |
||
236 | |||
237 | if ($expand) { |
||
238 | $sql = 'SELECT * FROM ' .implode(',', $this->getTables()); |
||
239 | $sql .= ' WHERE ' . $this->generateGlue() . ' AND '; |
||
240 | |||
241 | // only wrap in ( ) if the condition is OR and there is more than one rule |
||
242 | $wrap = $this->builder['condition'] == 'OR' && count($this->builder['rules']) > 1; |
||
243 | } |
||
244 | |||
245 | return $sql . $this->parseGroup($this->builder, $expand, $wrap); |
||
246 | } |
||
247 | |||
248 | /** |
||
249 | * Parse a rule group |
||
250 | * |
||
251 | * @param $rule |
||
252 | * @param bool $expand Expand macros? |
||
253 | * @param bool $wrap Wrap in parenthesis |
||
254 | * @return string |
||
255 | */ |
||
256 | private function parseGroup($rule, $expand = false, $wrap = true) |
||
257 | { |
||
258 | $group_rules = []; |
||
259 | |||
260 | foreach ($rule['rules'] as $group_rule) { |
||
261 | if (array_key_exists('condition', $group_rule)) { |
||
262 | $group_rules[] = $this->parseGroup($group_rule, $expand); |
||
263 | } else { |
||
264 | $group_rules[] = $this->parseRule($group_rule, $expand); |
||
265 | } |
||
266 | } |
||
267 | |||
268 | $sql = implode(" {$rule['condition']} ", $group_rules); |
||
269 | |||
270 | if ($wrap) { |
||
271 | return "($sql)"; |
||
272 | } else { |
||
273 | return "$sql"; |
||
274 | } |
||
275 | } |
||
276 | |||
277 | /** |
||
278 | * Parse a rule |
||
279 | * |
||
280 | * @param $rule |
||
281 | * @param bool $expand Expand macros? |
||
282 | * @return string |
||
283 | */ |
||
284 | private function parseRule($rule, $expand = false) |
||
285 | { |
||
286 | $field = $rule['field']; |
||
287 | $builder_op = $rule['operator']; |
||
288 | $op = self::$operators[$builder_op]; |
||
289 | $value = $rule['value']; |
||
290 | |||
291 | if (is_string($value) && starts_with($value, '`') && ends_with($value, '`')) { |
||
292 | // pass through value such as field |
||
293 | $value = trim($value, '`'); |
||
294 | if ($expand) { |
||
295 | $value = $this->expandMacro($value); |
||
296 | } |
||
297 | } elseif (isset(self::$values[$builder_op])) { |
||
298 | // wrap values as needed (is null values don't contain ? so '' is returned) |
||
299 | $values = (array) $value; |
||
300 | $value = preg_replace_callback('/\?/', function ($matches) use (&$values) { |
||
301 | return array_shift($values); |
||
302 | }, self::$values[$builder_op]); |
||
303 | } elseif (!is_numeric($value)) { |
||
304 | // wrap quotes around non-numeric values |
||
305 | $value = "\"$value\""; |
||
306 | } |
||
307 | |||
308 | if ($expand) { |
||
309 | $field = $this->expandMacro($field); |
||
310 | } |
||
311 | |||
312 | return trim("$field $op $value"); |
||
313 | } |
||
314 | |||
315 | /** |
||
316 | * Expand macro to sql |
||
317 | * |
||
318 | * @param $subject |
||
319 | * @param bool $tables_only Used when finding tables in query returns an array instead of sql string |
||
320 | * @param int $depth_limit |
||
321 | * @return string|array |
||
322 | */ |
||
323 | private function expandMacro($subject, $tables_only = false, $depth_limit = 20) |
||
324 | { |
||
325 | if (!str_contains($subject, 'macros.')) { |
||
326 | return $subject; |
||
327 | } |
||
328 | |||
329 | $macros = Config::get('alert.macros.rule'); |
||
330 | |||
331 | $count = 0; |
||
332 | while ($count++ < $depth_limit && str_contains($subject, 'macros.')) { |
||
333 | $subject = preg_replace_callback('/%?macros.([^ =()]+)/', function ($matches) use ($macros) { |
||
334 | $name = $matches[1]; |
||
335 | if (isset($macros[$name])) { |
||
336 | return $macros[$name]; |
||
337 | } else { |
||
338 | return $matches[0]; // this isn't a macro, don't replace |
||
339 | } |
||
340 | }, $subject); |
||
341 | } |
||
342 | |||
343 | if ($tables_only) { |
||
344 | preg_match_all('/%([^%.]+)\./', $subject, $matches); |
||
345 | return array_unique($matches[1]); |
||
346 | } |
||
347 | |||
348 | // clean leading % |
||
349 | $subject = preg_replace('/%([^%.]+)\./', '$1.', $subject); |
||
350 | |||
351 | // wrap entire macro result in parenthesis if needed |
||
352 | if (!(starts_with($subject, '(') && ends_with($subject, ')'))) { |
||
353 | $subject = "($subject)"; |
||
354 | } |
||
355 | |||
356 | return $subject; |
||
357 | } |
||
358 | |||
359 | |||
360 | /** |
||
361 | * Generate glue and first part of sql query for this rule |
||
362 | * |
||
363 | * @param string $target the name of the table to target, for alerting, this should be devices |
||
364 | * @return string |
||
365 | */ |
||
366 | private function generateGlue($target = 'devices') |
||
367 | { |
||
368 | $tables = $this->getTables(); // get all tables in query |
||
369 | |||
370 | // always add the anchor to the target table |
||
371 | $anchor = $target . '.' . $this->schema->getPrimaryKey($target) . ' = ?'; |
||
372 | $glue = [$anchor]; |
||
373 | |||
374 | foreach ($tables as $table) { |
||
375 | $path = $this->schema->findRelationshipPath($table, $target); |
||
376 | if ($path) { |
||
377 | foreach (array_pairs($path) as $pair) { |
||
378 | list($left, $right) = $pair; |
||
379 | $glue[] = $this->getGlue($left, $right); |
||
380 | } |
||
381 | } |
||
382 | } |
||
383 | |||
384 | // remove duplicates |
||
385 | $glue = array_unique($glue); |
||
386 | |||
387 | return '(' . implode(' AND ', $glue) . ')'; |
||
388 | } |
||
389 | |||
390 | /** |
||
391 | * Get glue sql between tables. Resolve fields to use. |
||
392 | * |
||
393 | * @param string $parent |
||
394 | * @param string $child |
||
395 | * @return string |
||
396 | */ |
||
397 | public function getGlue($parent, $child) |
||
398 | { |
||
399 | // first check to see if there is a single shared column name ending with _id |
||
400 | $shared_keys = array_filter(array_intersect( |
||
401 | $this->schema->getColumns($parent), |
||
402 | $this->schema->getColumns($child) |
||
403 | ), function ($table) { |
||
404 | return ends_with($table, '_id'); |
||
405 | }); |
||
406 | |||
407 | if (count($shared_keys) === 1) { |
||
408 | $shared_key = reset($shared_keys); |
||
409 | return "$parent.$shared_key = $child.$shared_key"; |
||
410 | } |
||
411 | |||
412 | $parent_key = $this->schema->getPrimaryKey($parent); |
||
413 | $flipped = empty($parent_key); |
||
414 | if ($flipped) { |
||
415 | // if the "parent" table doesn't have a primary key, flip them |
||
416 | list($parent, $child) = [$child, $parent]; |
||
417 | $parent_key = $this->schema->getPrimaryKey($parent); |
||
418 | } |
||
419 | $child_key = $parent_key; // assume the column names match |
||
420 | |||
421 | if (!$this->schema->columnExists($child, $child_key)) { |
||
422 | // if they don't match, guess the column name from the parent |
||
423 | if (ends_with($parent, 'xes')) { |
||
424 | $child_key = substr($parent, 0, -2) . '_id'; |
||
425 | } else { |
||
426 | $child_key = preg_replace('/s$/', '_id', $parent); |
||
427 | } |
||
428 | |||
429 | if (!$this->schema->columnExists($child, $child_key)) { |
||
430 | echo"FIXME: Could not make glue from $child to $parent\n"; |
||
431 | } |
||
432 | } |
||
433 | |||
434 | if ($flipped) { |
||
435 | return "$child.$child_key = $parent.$parent_key"; |
||
436 | } |
||
437 | |||
438 | return "$parent.$parent_key = $child.$child_key"; |
||
439 | } |
||
440 | |||
441 | /** |
||
442 | * Get an array of this rule ready for jQuery QueryBuilder |
||
443 | * |
||
444 | * @return array |
||
445 | */ |
||
446 | public function toArray() |
||
447 | { |
||
448 | return $this->builder; |
||
449 | } |
||
450 | |||
451 | /** |
||
452 | * Specify data which should be serialized to JSON |
||
453 | * @link http://php.net/manual/en/jsonserializable.jsonserialize.php |
||
454 | * @return mixed data which can be serialized by <b>json_encode</b>, |
||
455 | * which is a value of any type other than a resource. |
||
456 | * @since 5.4.0 |
||
457 | */ |
||
458 | public function jsonSerialize() |
||
461 | } |
||
462 | } |
||
463 |