diff options
Diffstat (limited to 'modules-available/statistics/inc/statisticsfilter.inc.php')
| -rw-r--r-- | modules-available/statistics/inc/statisticsfilter.inc.php | 1064 |
1 files changed, 701 insertions, 363 deletions
diff --git a/modules-available/statistics/inc/statisticsfilter.inc.php b/modules-available/statistics/inc/statisticsfilter.inc.php index 934e01da..5e6448c7 100644 --- a/modules-available/statistics/inc/statisticsfilter.inc.php +++ b/modules-available/statistics/inc/statisticsfilter.inc.php @@ -3,21 +3,118 @@ /* base class with rudimentary SQL generation abilities. * WARNING: argument is escaped, but $column and $operator are passed unfiltered into SQL */ -class StatisticsFilter +abstract class StatisticsFilter { /** - * Delimiter for js_selectize filters + * Legacy delimiter for js_selectize filters - used to redirect old URLs */ - const DELIMITER = '~,~'; + const LEGACY_DELIMITER = '~,~'; - const SIZE_ID44 = array(0, 8, 16, 24, 30, 40, 50, 60, 80, 100, 120, 150, 180, 250, 300, 400, 500, 1000, 2000, 4000); - const SIZE_RAM = array(1, 2, 3, 4, 6, 8, 10, 12, 16, 24, 32, 48, 64, 96, 128, 192, 256, 320, 480, 512, 768, 1024); + const SIZE_PARTITION = [0, 8, 16, 24, 30, 40, 50, 60, 80, 100, 120, 150, 180, 250, 300, 400, 500, 1000, 1500, 2000, 3000, + 4000, 6000, 8000, 10000]; + const SIZE_RAM = [1, 2, 3, 4, 6, 8, 10, 12, 16, 24, 32, 48, 64, 96, 128, 192, 256, 320, 480, 512, 768, 1024, 1536, + 2048]; + private static $keyCounter = 0; + + + /* + * Simple filters that map directly to DB columns + */ + + const OP_ORDINAL = ['=', '!=', '<', '>', '<=', '>=']; + const OP_STRCMP = ['~', '!~', '=', '!=']; + const OP_NOMINAL = ['=', '!=']; + const OP_LOCATIONS = ['~', '=', '!=']; + const OP_FUZZY_ORDINAL = ['=', '!=', '~', '!~', '<', '>', '<=', '>=']; + + /** + * @var StatisticsFilter[] + */ + public static $columns; + + /* + * Class instance stuff + */ + + /** + * @var string|null db-based sort column for this field, null if not sortable + */ public $column; - public $operator; - public $argument; - private static $keyCounter = 0; + /** + * @var string[] valid operators for this filter + */ + public $ops; + /** + * @var string placeholder for input field + */ + public $placeholder; + + public function __construct($column, array $ops, string $placeholder = '') + { + $this->column = $column; + $this->ops = $ops; + $this->placeholder = $placeholder; + } + + public function type(): string + { + return ($this->ops === self::OP_ORDINAL || $this->ops === self::OP_FUZZY_ORDINAL) ? 'int' : 'string'; + } + + /** + * Needed for joins with the hardware tables, to use the HardwareQueryColumn afterwards. + * The HardwareQuery class should probably be extended/rewritten to be more versatile in + * this regard. + */ + public static function addHardwareJoin(array &$args, array &$joins, string $hwtype = null): string + { + $joins['mxhw'] = ' INNER JOIN machine_x_hw mxhw ON (mxhw.disconnecttime = 0 AND mxhw.machineuuid = m.machineuuid)'; + $key = self::getNewKey('foo'); + $shw = self::getNewKey('shw'); + if ($hwtype === null) { + $joins[] = " INNER JOIN statistic_hw $shw ON (mxhw.hwid = {$shw}.hwid)"; + } else { + $joins[] = " INNER JOIN statistic_hw $shw ON (mxhw.hwid = {$shw}.hwid AND {$shw}.hwtype = :$key)"; + $args[$key] = $hwtype; + } + return $shw; + } + + /** + * To be called by DatabaseFilter::whereClause() when building actual query. + * @param string $operator operator to use + * @param string[]|string $argument argument to compare against + * @param string[] $args assoc array to add parametrized version of $argument to + * @param string[] $joins any optional joins can be added to this array + * @return string where clause + */ + public abstract function whereClause(string $operator, $argument, array &$args, array &$joins): string; + + /** + * Called to get an instance of DatabaseFilter that binds the given $op and $argument to this filter. + * @param string[]|string $argument + */ + public function bind(string $op, $argument): DatabaseFilter { return new DatabaseFilter($this, $op, $argument); } + + /** + * Check if given $operator is valid for this filter. Throws error and halts if not. + * @return void + */ + public final function validateOperator(string $operator) + { + if (empty($this->ops)) + return; + if (!in_array($operator, $this->ops)) { + // Yes keep $this in this call, get_class() !== get_class($this) + ErrorHandler::traceError("Invalid op '$operator' for " . get_class($this) . '::' . $this->column); + } + } + + /* + * Static/Helpers + */ public static function findBestValue($array, $value, $up) { @@ -40,478 +137,719 @@ class StatisticsFilter return ($array[$best] + $array[$best - 1]) / 2; } - public static function getNewKey($colname) + public static function getNewKey($colname): string { return $colname . '_' . (self::$keyCounter++); } - public function __construct($column, $operator, $argument = null) - { - $this->column = trim($column); - $this->operator = trim($operator); - $this->argument = is_array($argument) ? $argument : trim($argument); - } - - /* returns a where clause and adds needed operators to the passed array */ - public function whereClause(&$args, &$joins) + /** + * @return DatabaseFilter[] + */ + public static function parseQuery(): array { - $key = StatisticsFilter::getNewKey($this->column); - $addendum = ''; - - /* check if we have to do some parsing*/ - if (self::$columns[$this->column]['type'] === 'date') { - $args[$key] = strtotime($this->argument); - } else { - $args[$key] = $this->argument; - if ($this->operator === '~' || $this->operator === '!~') { - $args[$key] = str_replace(array('=', '_', '%', '*', '?'), array('==', '=_', '=%', '%', '_'), $args[$key]); - $addendum = " ESCAPE '='"; - } + // Get current settings from GET + $ops = Request::get('op', [], 'array'); + $currentValues = ArrayUtil::mergeByKey([ + 'filter' => Request::get('filter', [], 'array'), + 'op' => $ops, + 'argument' => Request::get('arg', [], 'array'), + ]); + if (Request::get('show') === false && empty($ops)) { + $currentValues['lastseen'] = [ + 'filter' => true, + 'op' => '>', + 'argument' => gmdate('Y-m-d', strtotime('-30 day')), + ]; } + $filters = []; + foreach ($currentValues as $filterType => $data) { + if (!$data['filter']) + continue; + $operator = $data['op']; + $argument = $data['argument']; - $op = $this->operator; - if ($this->operator == '~') { - $op = 'LIKE'; - } elseif ($this->operator == '!~') { - $op = 'NOT LIKE'; + if (array_key_exists($filterType, self::$columns)) { + $filters[$filterType] = self::$columns[$filterType]->bind($operator, $argument); + } else { + Message::addError('invalid-filter-key', $filterType); + } } - return 'm.' . $this->column . ' ' . $op . ' :' . $key . $addendum; + return $filters; } - /* parse a query into an array of filters */ - public static function parseQuery($query) + public static function renderFilterBox(string $show, StatisticsFilterSet $filterSet): void { - $operators = ['<=', '>=', '!=', '!~', '=', '~', '<', '>']; - $filters = []; - if (empty($query)) - return $filters; - foreach (explode(self::DELIMITER, $query) as $q) { - $q = trim($q); - if (empty($q)) - continue; - // Special case: User pasted UUID, turn into filter - if (preg_match('/^\w{8}-\w{4}-\w{4}-\w{4}-\w{12}$/', $q)) { - $filters[] = new StatisticsFilter('machineuuid', '=', $q); - continue; - } - // Special case: User pasted IP, turn into filter - if (preg_match('/^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$/', $q)) { - $filters[] = new StatisticsFilter('clientip', '=', $q); - continue; - } - /* find position of first operator */ - $pos = 10000; - $operator = false; - foreach ($operators as $op) { - $newpos = strpos($q, $op); - if ($newpos > -1 && ($newpos < $pos)) { - $pos = $newpos; - $operator = $op; + // Build location list, with permissions + if (Module::isAvailable('locations')) { + self::$columns['location']->filterLocations($filterSet->getAllowedLocations()); + } + // Build column array for rendering + $columns = []; + $showCount = 0; + foreach (self::$columns as $key => $filter) { + $col = [ + 'key' => $key, + 'name' => Dictionary::translateFile('filters', $key), + 'placeholder' => $filter->placeholder, + ]; + $bind = $filterSet->hasFilterKey($key); + if ($filter->type() === 'int') { + $col['input'] = 'number'; + } elseif ($filter->type() === 'string') { + $col['input'] = 'text'; + } elseif ($filter->type() === 'date') { + $col['input'] = 'text'; + $col['inputclass'] = 'is-date'; + } elseif ($filter->type() === 'enum') { + $col['enum'] = true; + /** @var EnumStatisticsFilter $filter */ + $col['values'] = $filter->values; + if ($bind !== null) { + // Current value from GET + foreach ($col['values'] as &$value) { + if ($value['key'] == $bind->argument) { + $value['selected'] = 'selected'; + } + } } } - if ($pos == 10000) { - error_log("couldn't find operator in segment " . $q); - /* TODO */ - continue; + // current value from GET + if ($bind !== null) { + $col['currentvalue'] = $bind->argument; + $col['checked'] = 'checked'; + $showCount++; + } elseif (!isset($col['show']) || !$col['show']) { + $col['collapse'] = 'collapse'; } - $lhs = trim(substr($q, 0, $pos)); - $rhs = trim(substr($q, $pos + strlen($operator))); - - if ($lhs === 'gbram') { - $filters[] = new RamGbStatisticsFilter($operator, $rhs); - } elseif ($lhs === 'runtime') { - $filters[] = new RuntimeStatisticsFilter($operator, $rhs); - } elseif ($lhs === 'state') { - $filters[] = new StateStatisticsFilter($operator, $rhs); - } elseif ($lhs === 'hddgb') { - $filters[] = new Id44StatisticsFilter($operator, $rhs); - } elseif ($lhs === 'location') { - $filters[] = new LocationStatisticsFilter($operator, $rhs); - } elseif ($lhs === 'subnet') { - $filters[] = new SubnetStatisticsFilter($operator, $rhs); - } else { - if (array_key_exists($lhs, self::$columns) && self::$columns[$lhs]['column']) { - $filters[] = new StatisticsFilter($lhs, $operator, $rhs); - } else { - Message::addError('invalid-filter-key', $lhs); + $col['op'] = $filter->ops; + foreach ($col['op'] as &$value) { + $value = ['op' => $value]; + if ($bind !== null && $bind->op === $value['op']) { + $value['selected'] = 'selected'; } } + $columns[$key] = $col; + } + if ($showCount < 2) { + unset($columns['clientip']['collapse']); + } + if ($showCount < 1) { + unset($columns['machineuuid']['collapse']); } + $data = array( + 'show' => $show, + 'columns' => array_values($columns), + $show . 'ButtonClass' => 'active', + ); - return $filters; + Permission::addGlobalTags($data['perms'], null, ['view.summary', 'view.list']); + Render::addTemplate('filterbox', $data); } - /** - * @param \StatisticsFilterSet $filterSet - */ - public static function renderFilterBox($show, $filterSet, $query) + public static function initConstants() { - $data = array( - 'show' => $show, - 'query' => $query, - 'delimiter' => StatisticsFilter::DELIMITER, - 'sortDirection' => $filterSet->getSortDirection(), - 'sortColumn' => $filterSet->getSortColumn(), - 'columns' => json_encode(StatisticsFilter::$columns), - ); + self::$columns = [ + 'clientip' => new IpStatisticsFilter(), + 'hostname' => new SimpleStatisticsFilter('hostname', self::OP_STRCMP, 'pc.fqdn.example.com'), + 'machineuuid' => new SimpleStatisticsFilter('machineuuid', self::OP_STRCMP, '88888888-4444-4444-121212121212'), + 'macaddr' => new MacAddressStatisticsFilter(), + 'firstseen' => new DateStatisticsFilter('firstseen', '2020-10-15 14:00'), + 'lastseen' => new DateStatisticsFilter('lastseen', '2020-10-15 14:00'), + 'lastboot' => new DateStatisticsFilter('lastboot', '2020-10-15 14:00'), + 'runtime' => new RuntimeStatisticsFilter(), + 'realcores' => new SimpleStatisticsFilter('realcores', self::OP_ORDINAL, ''), + 'systemmodel' => new SystemModelStatisticsFilter(), + 'cpumodel' => new SimpleStatisticsFilter('cpumodel', self::OP_STRCMP, 'Pentium Pro 200 MHz'), + 'hddgb' => new PartitionGbStatisticsFilter('id44mb'), + 'persistentgb' => new PartitionGbStatisticsFilter('id45mb'), + 'gbram' => new RamGbStatisticsFilter(), + 'kvmstate' => new EnumStatisticsFilter('kvmstate', ['ENABLED', 'DISABLED', 'UNSUPPORTED']), + 'badsectors' => new SimpleStatisticsFilter('badsectors', self::OP_ORDINAL, ''), + 'currentuser' => new SimpleStatisticsFilter('currentuser', self::OP_STRCMP, 'login'), + 'state' => new StateStatisticsFilter(), + 'live_swapfree' => new SimpleStatisticsFilter('live_swapfree', self::OP_ORDINAL, 'MiB'), + 'live_memfree' => new SimpleStatisticsFilter('live_memfree', self::OP_ORDINAL, 'MiB'), + 'live_tmpfree' => new SimpleStatisticsFilter('live_tmpfree', self::OP_ORDINAL, 'MiB'), + 'live_id45free' => new SimpleNotZeroStatisticsFilter('live_id45free', self::OP_ORDINAL, 'MiB'), + 'standbycrash' => new StandbyCrashStatisticsFilter(), + 'pcidev' => new PciDeviceStatisticsFilter(), + 'nicspeed' => new NicSpeedStatisticsFilter(), + 'hddrpm' => new HddRpmStatisticsFilter(), + //'anydev' => new AnyHardwarePropStatisticsFilter(), + ]; + if (Module::isAvailable('locations')) { + self::$columns['location'] = new LocationStatisticsFilter(); + } + } + +} + +class SimpleStatisticsFilter extends StatisticsFilter +{ - if ($show === 'list') { - $data['listButtonClass'] = 'active'; - $data['statButtonClass'] = ''; + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $addendum = ''; + $key = self::getNewKey($this->column); + $args[$key] = $argument; + + if (is_array($argument)) { + if ($operator[0] === '!') { + $op = 'NOT IN'; + } else { + $op = 'IN'; + } } else { - $data['listButtonClass'] = ''; - $data['statButtonClass'] = 'active'; + if ($operator === '~' || $operator === '!~') { + $args[$key] = str_replace(array('=', '_', '%', '*', '?'), array('==', '=_', '=%', '%', '_'), $args[$key]); + $addendum = " ESCAPE '='"; + } + $op = $operator; + if ($operator === '~') { + $op = 'LIKE'; + } elseif ($operator === '!~') { + $op = 'NOT LIKE'; + } } + return 'm.' . $this->column . ' ' . $op . ' (:' . $key . ') ' . $addendum; + } - $locsFlat = array(); - if (Module::isAvailable('locations')) { - $allowed = $filterSet->getAllowedLocations(); - foreach (Location::getLocations() as $loc) { - $locsFlat['L' . $loc['locationid']] = array( - 'pad' => $loc['locationpad'], - 'name' => $loc['locationname'], - 'disabled' => $allowed !== false && !in_array($loc['locationid'], $allowed), - ); - } +} + +class SimpleNotZeroStatisticsFilter extends SimpleStatisticsFilter +{ + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $str = parent::whereClause($operator, $argument, $args, $joins); + if ((int)$argument !== 0 || $operator !== '=') { + $str = "($str AND {$this->column} != 0)"; } + return $str; + } - Permission::addGlobalTags($data['perms'], null, ['view.summary', 'view.list']); - $data['locations'] = json_encode($locsFlat); - Render::addTemplate('filterbox', $data); +} + +class EnumStatisticsFilter extends SimpleStatisticsFilter +{ + + public $values; + + public function __construct(string $column, array $values, array $ops = self::OP_NOMINAL) + { + parent::__construct($column, $ops, ''); + if (isset($values[0])) { + if (!is_array($values[0])) { + $values = array_map(function($e) { return [ + 'key' => $e, + 'value' => $e, + ]; }, $values); + } + } else { + $values = array_map(function($v, $k) { return [ + 'key' => $k, + 'value' => $v, + ]; }, $values, array_keys($values)); + } + $this->values = $values; } - private static $query = false; + public function type(): string { return 'enum'; } - public static function getQuery() + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { - if (self::$query === false) { - self::$query = Request::any('filters', false, 'string'); - if (self::$query === false) { - self::$query = 'lastseen > ' . gmdate('Y-m-d', strtotime('-30 day')); + if ($this->validateArgument()) { + $keys = ArrayUtil::flattenByKey($this->values, 'key'); + if (is_array($argument)) { + $ok = true; + foreach ($argument as $e) { + if (!in_array($e, $keys)) { + $ok = false; + } + } + } else { + $ok = in_array($argument, $keys); + } + if (!$ok) { + Message::addError('invalid-enum-item', $this->column, $argument); + return '0'; } } - return self::$query; + return parent::whereClause($operator, $argument, $args, $joins); } - /* - * Simple filters that map directly to DB columns - */ + protected function validateArgument(): bool { return true; } - const OP_ORDINAL = ['!=', '<=', '>=', '=', '<', '>']; - const OP_STRCMP = ['!~', '~', '=', '!=']; - const OP_NOMINAL = ['!=', '=']; - public static $columns; +} - /** - * Do this here instead of const since we need to check for available modules while building array. - */ - public static function initConstants() +class StandbyCrashStatisticsFilter extends EnumStatisticsFilter +{ + + public function __construct() { + parent::__construct('standbysem', ['NONE', 'MANY']); + } - self::$columns = [ - 'machineuuid' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'string', - 'column' => true, - ], - 'macaddr' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'string', - 'column' => true, - ], - 'firstseen' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'date', - 'column' => true, - ], - 'lastseen' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'date', - 'column' => true, - ], - 'logintime' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'date', - 'column' => true, - ], - 'realcores' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => true, - ], - 'systemmodel' => [ - 'op' => self::OP_STRCMP, - 'type' => 'string', - 'column' => true, - ], - 'cpumodel' => [ - 'op' => self::OP_STRCMP, - 'type' => 'string', - 'column' => true, - ], - 'hddgb' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => false, - 'map_sort' => 'id44mb' - ], - 'gbram' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'map_sort' => 'mbram', - 'column' => false, - ], - 'kvmstate' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'enum', - 'column' => true, - 'values' => ['ENABLED', 'DISABLED', 'UNSUPPORTED'] - ], - 'badsectors' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => true - ], - 'clientip' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'string', - 'column' => true - ], - 'hostname' => [ - 'op' => self::OP_STRCMP, - 'type' => 'string', - 'column' => true - ], - 'subnet' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'string', - 'column' => false - ], - 'currentuser' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'string', - 'column' => true - ], - 'state' => [ - 'op' => self::OP_NOMINAL, - 'type' => 'enum', - 'column' => true, - 'values' => ['occupied', 'on', 'off', 'idle', 'standby'] - ], - 'live_swapfree' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => true - ], - 'live_memfree' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => true - ], - 'live_tmpfree' => [ - 'op' => self::OP_ORDINAL, - 'type' => 'int', - 'column' => true - ], - ]; - if (Module::isAvailable('locations')) { - self::$columns['location'] = [ - 'op' => self::OP_STRCMP, - 'type' => 'enum', - 'column' => false, - 'values' => array_keys(Location::getLocationsAssoc()), - ]; + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + if ($argument === 'NONE') { + $argument = 0; + } else { // MANY + $argument = 3; + $operator = $operator === '=' ? '>' : '<='; } + return parent::whereClause($operator, $argument, $args, $joins); } + protected function validateArgument(): bool { return false; } + } -class RamGbStatisticsFilter extends StatisticsFilter +class DateStatisticsFilter extends StatisticsFilter { - public function __construct($operator, $argument) - { - parent::__construct('mbram', $operator, $argument); - } - - public function whereClause(&$args, &$joins) - { - $lower = floor(StatisticsFilter::findBestValue(StatisticsFilter::SIZE_RAM, (int)$this->argument, false) * 1024 - 100); - $upper = ceil(StatisticsFilter::findBestValue(StatisticsFilter::SIZE_RAM, (int)$this->argument, true) * 1024 + 100); - if ($this->operator == '=') { - return " mbram BETWEEN $lower AND $upper"; - } elseif ($this->operator == '<') { - return " mbram < $lower"; - } elseif ($this->operator == '<=') { - return " mbram <= $upper"; - } elseif ($this->operator == '>') { - return " mbram > $upper"; - } elseif ($this->operator == '>=') { - return " mbram >= $lower"; - } elseif ($this->operator == '!=') { - return " (mbram < $lower OR mbram > $upper)"; + + public function __construct(string $column, string $placeholder) + { + parent::__construct($column, self::OP_ORDINAL, $placeholder); + } + + public function type(): string { return 'date'; } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $key = self::getNewKey($this->column); + + if (!preg_match('/^(?<date>\d{4}-\d{2}-\d{2})(\s+(?<h>\d{1,2})(:(?<m>\d{2})(:\d+)?)?)?$/', $argument, $out)) { + Message::addError('invalid-date-format', $argument); + return '0'; + } + + if (isset($out['m'])) { + $span = 'minute'; + } elseif (isset($out['h'])) { + $span = 'hour'; + $argument .= ':00'; } else { - error_log("unimplemented operator in RamGbFilter: $this->operator"); + $span = 'day'; + } - return ' 1'; + $args[$key] = strtotime($argument); + if ($operator === '=' || $operator === '!=') { + $key2 = self::getNewKey($this->column); + $args[$key2] = strtotime(' +1 ' . $span, $args[$key]); + return ($operator === '=' ? '' : 'NOT ') . 'm.' . $this->column . " BETWEEN :$key AND :$key2"; } + if ($operator === '>' || $operator === '<=') { + $args[$key] = strtotime('+1 ' . $span . ' -1 second', $args[$key]); + } + + return 'm.' . $this->column . ' ' . $operator . ' :' . $key; } + } class RuntimeStatisticsFilter extends StatisticsFilter { - public function __construct($operator, $argument) + + public function __construct() { - parent::__construct('lastboot', $operator, $argument); + parent::__construct('lastboot', self::OP_ORDINAL); } - public function whereClause(&$args, &$joins) + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { - $upper = time() - (int)$this->argument * 3600; + $upper = time() - (int)$argument * 3600; $lower = $upper - 3600; $common = "state IN ('OCCUPIED', 'IDLE', 'STANDBY') AND"; - if ($this->operator == '=') { - return "$common ({$this->column} BETWEEN $lower AND $upper)"; - } elseif ($this->operator == '<') { - return "$common {$this->column} > $upper"; - } elseif ($this->operator == '<=') { - return "$common {$this->column} > $lower"; - } elseif ($this->operator == '>') { - return "$common {$this->column} < $lower"; - } elseif ($this->operator == '>=') { - return "$common {$this->column} < $upper"; - } elseif ($this->operator == '!=') { - return "$common ({$this->column} < $lower OR {$this->column} > $upper)"; + if ($operator == '<') { // These are inverted (uptime vs lastboot) + return "$common lastboot > $upper"; + } elseif ($operator == '<=') { + return "$common lastboot > $lower"; + } elseif ($operator == '>') { + return "$common lastboot < $lower"; + } elseif ($operator == '>=') { + return "$common lastboot < $upper"; + } elseif ($operator == '=') { + return "$common (lastboot BETWEEN $lower AND $upper)"; + } + // != + return "$common (lastboot NOT BETWEEN $lower AND > $upper)"; + } +} + +abstract class GbToMbRangeStatisticsFilter extends StatisticsFilter +{ + + protected function rangeClause(string $operator, $argument, array $fuzzyVals): string + { + if ($operator === '~' || $operator === '!~') { + $lower = (int)floor(StatisticsFilter::findBestValue($fuzzyVals, (int)$argument, false) * 1024 - 500); + $upper = (int)ceil(StatisticsFilter::findBestValue($fuzzyVals, (int)$argument, true) * 1024 + 100); + $operator = str_replace('~', '=', $operator); } else { - error_log("unimplemented operator in RuntimeFilter: $this->operator"); - return ' 1'; + $lower = round($argument * 1024 - 500); + $upper = round($argument * 1024 + 1023); } + if ($operator === '=') + return " {$this->column} BETWEEN $lower AND $upper"; + if ($operator === '!=') + return " {$this->column} NOT BETWEEN $lower AND $upper"; + if ($operator === '<') + return " {$this->column} < $lower"; + if ($operator === '<=') + return " {$this->column} <= $upper"; + if ($operator === '>') + return " {$this->column} > $upper"; + return " {$this->column} >= $lower"; // >= } + } -class Id44StatisticsFilter extends StatisticsFilter +class RamGbStatisticsFilter extends GbToMbRangeStatisticsFilter { - public function __construct($operator, $argument) + + public function __construct() { - parent::__construct('id44mb', $operator, $argument); + parent::__construct('mbram', self::OP_FUZZY_ORDINAL, 'GiB'); } - public function whereClause(&$args, &$joins) + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { - if ($this->operator === '=' || $this->operator === '!=') { - $lower = floor(StatisticsFilter::findBestValue(StatisticsFilter::SIZE_ID44, $this->argument, false) * 1024 - 100); - $upper = ceil(StatisticsFilter::findBestValue(StatisticsFilter::SIZE_ID44, $this->argument, true) * 1024 + 100); - } else { - $lower = $upper = round($this->argument * 1024); - } - - if ($this->operator === '=') { - return " id44mb BETWEEN $lower AND $upper"; - } elseif ($this->operator === '!=') { - return " id44mb < $lower OR id44mb > $upper"; - } elseif ($this->operator === '<=') { - return " id44mb <= $upper"; - } elseif ($this->operator === '>=') { - return " id44mb >= $lower"; - } elseif ($this->operator === '<') { - return " id44mb < $lower"; - } elseif ($this->operator === '>') { - return " id44mb > $upper"; - } else { - error_log("unimplemented operator in Id44Filter: $this->operator"); + return parent::rangeClause($operator, $argument, self::SIZE_RAM); + } - return ' 1'; - } +} + +class PartitionGbStatisticsFilter extends GbToMbRangeStatisticsFilter +{ + + public function __construct(string $column) + { + parent::__construct($column, self::OP_FUZZY_ORDINAL, 'GiB'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + return parent::rangeClause($operator, $argument, self::SIZE_PARTITION); } } -class StateStatisticsFilter extends StatisticsFilter +class StateStatisticsFilter extends EnumStatisticsFilter { - public function __construct($operator, $argument) + + public function __construct() { - parent::__construct(null, $operator, $argument); + parent::__construct('state', ['on', 'off', 'idle', 'occupied', 'standby']); } - public function whereClause(&$args, &$joins) + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { $map = [ 'on' => ['IDLE', 'OCCUPIED'], 'off' => ['OFFLINE'], 'idle' => ['IDLE'], 'occupied' => ['OCCUPIED'], 'standby' => ['STANDBY'] ]; - $neg = $this->operator == '!=' ? 'NOT ' : ''; - if (array_key_exists($this->argument, $map)) { + $neg = $operator === '!=' ? 'NOT ' : ''; + if (array_key_exists($argument, $map)) { $key = StatisticsFilter::getNewKey($this->column); - $args[$key] = $map[$this->argument]; + $args[$key] = $map[$argument]; return " m.state $neg IN ( :$key ) "; - } else { - Message::addError('invalid-filter-argument', 'state', $this->argument); - return ' 1'; } + Message::addError('invalid-filter-argument', 'state', $argument); + return ' 1'; } } -class LocationStatisticsFilter extends StatisticsFilter +class LocationStatisticsFilter extends EnumStatisticsFilter { - public function __construct($operator, $argument) + + public function __construct() { - parent::__construct('locationid', $operator, $argument); + $locs = []; + foreach (Location::getLocations(-1, 0, true) as $loc) { + $locs[] = [ + 'key' => $loc['locationid'], + 'value' => $loc['locationpad'] . ' ' . $loc['locationname'], + ]; + } + parent::__construct('locationid', $locs, self::OP_LOCATIONS); } - public function whereClause(&$args, &$joins) + public function type(): string { return 'enum'; } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { - $recursive = (substr($this->operator, -1) === '~'); - $this->operator = str_replace('~', '=', $this->operator); + $recursive = (substr($operator, -1) === '~'); + $operator = str_replace('~', '=', $operator); - if (is_array($this->argument)) { - if ($recursive) - Util::traceError('Cannot use ~ operator for location with array'); - } else { - settype($this->argument, 'int'); + if ($recursive && is_array($argument)) { + ErrorHandler::traceError('Cannot use ~ operator for location with array'); } - $neg = $this->operator === '=' ? '' : 'NOT'; - if ($this->argument === 0) { - return "m.locationid IS $neg NULL"; - } else { - $key = StatisticsFilter::getNewKey($this->column); - if ($recursive) { - $args[$key] = array_keys(Location::getRecursiveFlat($this->argument)); - } else { - $args[$key] = $this->argument; + if ($recursive) { + $argument = array_keys(Location::getRecursiveFlat($argument)); + } elseif ($argument == 0) { + return 'locationid IS ' . ($operator === '!=' ? 'NOT' : '') . ' NULL'; + } + return parent::whereClause($operator, $argument, $args, $joins); + } + + public function filterLocations($list) + { + if ($list === false || in_array(0, $list)) + return; + foreach ($this->values as &$loc) { + if (!in_array($loc['key'], $list)) { + $loc['disabled'] = 'disabled'; } - return "m.locationid $neg IN (:$key)"; } } } -class SubnetStatisticsFilter extends StatisticsFilter +class IpStatisticsFilter extends StatisticsFilter { - public function __construct($operator, $argument) + + public function __construct() { - parent::__construct(null, $operator, $argument); + parent::__construct('clientip', self::OP_NOMINAL, '1.2.3.4, 1.2.3.*, 1.2.3/24'); } - public function whereClause(&$args, &$joins) + public function whereClause(string $operator, $argument, array &$args, array &$joins) : string { - $argument = preg_replace('/[^0-9\.:]/', '', $this->argument); - return " clientip LIKE '$argument%'"; + $argument = strtolower(preg_replace('#[^0-9a-f.:/*]#i', '', $argument)); + if (filter_var($argument, FILTER_VALIDATE_IP) !== false) { + // Valid \o/ - do nothing to $argument + } elseif (strpos($argument, '/') !== false) { + // TODO: IPv6 CIDR + $range = IpUtil::parseCidr($argument); + if ($range === null) { + Message::addError('invalid-cidr-notion', $argument); + return '0'; + } + return 'INET_ATON(clientip) BETWEEN ' . $range['start'] . ' AND ' . $range['end']; + } elseif (($num = substr_count($argument, ':')) !== 0 && $num <= 7) { + // TODO: Probably valid IPv6, not yet in DB + } elseif (($num = substr_count($argument, '.')) !== 0 && $num <= 3) { + if (substr($argument, -1) === '.') { + $argument .= '*'; + } elseif ($num < 3) { + $argument .= '.*'; + } + } else { + Message::addError('invalid-ip-address', $argument); + return '0'; + } + $operator = $operator[0] === '!' ? 'NOT LIKE' : 'LIKE'; + return "clientip $operator '" . str_replace('*', '%', $argument) . "'"; } } class IsClientStatisticsFilter extends StatisticsFilter { - public function __construct($argument) + + public function __construct() { - parent::__construct(null, null, $argument); + parent::__construct(null, []); } - public function whereClause(&$args, &$joins) + public function whereClause(string $operator, $argument, array &$args, array &$joins): string { - if ($this->argument) { - $joins[] = ' LEFT JOIN runmode USING (machineuuid)'; + if ($argument) { + $joins[] = ' LEFT JOIN runmode ON (m.machineuuid = runmode.machineuuid)'; return "(runmode.isclient <> 0 OR runmode.isclient IS NULL)"; } - $joins[] = ' INNER JOIN runmode USING (machineuuid)'; + $joins[] = ' INNER JOIN runmode ON (m.machineuuid = runmode.machineuuid)'; return "runmode.isclient = 0"; } } -StatisticsFilter::initConstants();
\ No newline at end of file +class PciDeviceStatisticsFilter extends StatisticsFilter +{ + + public function __construct() + { + parent::__construct(null, ['='], 'vvvv[:dddd][,cccc]'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + // vendor[:device][,class] + if (!preg_match('/^(?<v>[0-9a-f]{4})(?::(?<d>[0-9a-f]{4}))?(?:,(?<c>[0-9a-f]{4}))?$/i', $argument, $out)) { + Message::addError('invalid-pciid', $argument); + return '0'; + } + $vendor = $out['v']; + $device = $out['d'] ?? ''; + $class = $out['c'] ?? ''; + // basic join for hw_x_machine + $shw = StatisticsFilter::addHardwareJoin($args, $joins, HardwareInfo::PCI_DEVICE); + $_ = []; + $c = new HardwareQueryColumn(true, 'vendor'); + $c->addCondition($operator, $vendor); + $c->generate($joins, $_, $args, [], $shw); + if (!empty($device)) { + $c = new HardwareQueryColumn(true, 'device'); + $c->addCondition($operator, $device); + $c->generate($joins, $_, $args, [], $shw); + } + if (!empty($class)) { + $c = new HardwareQueryColumn(true, 'class'); + $c->addCondition($operator, $class); + $c->generate($joins, $_, $args, [], $shw); + } + return '1'; + } + +} + +class NicSpeedStatisticsFilter extends StatisticsFilter +{ + + public function __construct() + { + parent::__construct(null, StatisticsFilter::OP_ORDINAL, 'MBit/s'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $shw = StatisticsFilter::addHardwareJoin($args, $joins, HardwareInfo::MAINBOARD); + $_ = []; + $c = new HardwareQueryColumn(false, 'nic-speed'); + $c->addCondition($operator, $argument); + $c->generate($joins, $_, $args, [], $shw); + return '1'; + } + +} + +class HddRpmStatisticsFilter extends StatisticsFilter +{ + + public function __construct() + { + parent::__construct(null, StatisticsFilter::OP_ORDINAL, '7200'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $shw = StatisticsFilter::addHardwareJoin($args, $joins, HardwareInfo::HDD); + $_ = []; + $c = new HardwareQueryColumn(true, 'rotation_rate'); + $c->addCondition($operator, $argument); + $c->generate($joins, $_, $args, [], $shw); + return '1'; + } + +} + +class SystemModelStatisticsFilter extends StatisticsFilter +{ + + public function __construct() + { + parent::__construct(null, StatisticsFilter::OP_STRCMP, 'PC-365 (IBM)'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $shw = StatisticsFilter::addHardwareJoin($args, $joins, HardwareInfo::DMI_SYSTEM); + $_ = []; + $manufacturer = null; + $model = $argument; + if (preg_match('/^(.*)\((.*)\)\s*$/', $model, $out)) { + $manufacturer = trim($out[2]); + $model = trim($out[1]); + } + $c = new HardwareQueryColumn(true, 'Product Name'); + $c->addCondition($operator, $model); + $c->generate($joins, $_, $args, [], $shw); + if ($manufacturer !== null) { + $c = new HardwareQueryColumn(true, 'Manufacturer'); + $c->addCondition($operator, $manufacturer); + $c->generate($joins, $_, $args, [], $shw); + } + return '1'; + } + +} + +class MacAddressStatisticsFilter extends SimpleStatisticsFilter +{ + public function __construct() + { + parent::__construct('macaddr', self::OP_STRCMP, '11-22-33-44-55-66'); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + // Allow just 12 hex digits, and convert ':' to '-', which we unfortunately settled on for the DB format + if (preg_match('/^([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})([0-9a-f]{2})$/i', + $argument, $out)) { + $argument = $out[1] . '-' . $out[2] . '-' . $out[3] . '-' . $out[4] . '-' . $out[5] . '-' . $out[6]; + } elseif (strpos($argument, ':') !== false) { + $argument = str_replace(':', '-', $argument); + } + return parent::whereClause($operator, $argument, $args, $joins); + } +} + +class AnyHardwarePropStatisticsFilter extends StatisticsFilter +{ + + public function __construct() + { + parent::__construct(null, ['~']); + } + + public function whereClause(string $operator, $argument, array &$args, array &$joins): string + { + $shw = StatisticsFilter::addHardwareJoin($args, $joins); + $val = self::getNewKey('val'); + $key1 = self::getNewKey('hw'); + $joins[] = "LEFT JOIN statistic_hw_prop $key1 ON (`$key1`.`value` LIKE :$val AND `$key1`.hwid = `$shw`.hwid)"; + $key2 = self::getNewKey('hw'); + $joins[] = "LEFT JOIN machine_x_hw_prop $key2 ON (`$key2`.`value` LIKE :$val AND `$key2`.machinehwid = mxhw.machinehwid)"; + $args[$val] = '%' . str_replace(['%', '*'], ['_', '%'], $argument) . '%'; + return "((`$key1`.`value` IS NOT NULL) OR (`$key2`.`value` IS NOT NULL))"; + } + +} + +class DatabaseFilter +{ + /** @var StatisticsFilter + */ + private $inst; + public $op; + public $argument; + + /** + * Called by StatisticsFilter::bind(). + */ + public function __construct(StatisticsFilter $inst, string $op, $argument) + { + $inst->validateOperator($op); + $this->inst = $inst; + $this->op = $op; + $this->argument = $argument; + } + + /** + * Called from StatisticsFilterSet::makeFragments() to build the final query. + */ + public function whereClause(array &$args, array &$joins): string + { + return $this->inst->whereClause($this->op, $this->argument, $args, $joins); + } + + public function isClass(string $what): bool + { + return get_class($this->inst) === $what; + } + + public function getClass(): string + { + return get_class($this->inst); + } + +} + +StatisticsFilter::initConstants(); |