summaryrefslogtreecommitdiffstats
path: root/modules-available/statistics
diff options
context:
space:
mode:
Diffstat (limited to 'modules-available/statistics')
-rw-r--r--modules-available/statistics/api.inc.php32
-rw-r--r--modules-available/statistics/hooks/config-tgz.inc.php2
-rw-r--r--modules-available/statistics/inc/devicetype.inc.php7
-rw-r--r--modules-available/statistics/inc/hardwareinfo.inc.php5
-rw-r--r--modules-available/statistics/inc/hardwarequery.inc.php130
-rw-r--r--modules-available/statistics/inc/hardwarequerycolumn.inc.php88
-rw-r--r--modules-available/statistics/pages/hints.inc.php22
-rw-r--r--modules-available/statistics/pages/machine.inc.php2
-rw-r--r--modules-available/statistics/pages/projectors.inc.php2
9 files changed, 175 insertions, 115 deletions
diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php
index 431d4cd4..7aa6de34 100644
--- a/modules-available/statistics/api.inc.php
+++ b/modules-available/statistics/api.inc.php
@@ -1,26 +1,12 @@
<?php
if (Request::any('action') === 'test' && isLocalExecution()) {
- $uuid = Request::any('uuid', '', 'string');
- /*
- error_log(HardwareInfo::getKclModifications());
- exit;
- $x = new HardwareQuery(HardwareInfo::PCI_DEVICE);
- //$x->addCompare(false, 'Memory Slot Occupied', '>=', true, 'Memory Slot Count');
- $x->addWhere(true, 'vendor', '=', '8086');
- $x->addGlobalColumn('device');
- $res = $x->query();
- foreach ($res as $row) {
- error_log(json_encode($row));
- }
- exit;
- */
- $data = file_get_contents('/tmp/bla.json');
- Database::exec(
- "UPDATE machine SET data = :data WHERE machineuuid = :uuid",
- ['uuid' => $uuid, 'data' => $data]);
- HardwareParser::parseMachine($uuid, json_decode($data, true));
- echo 'Kweries: ' . Database::getQueryCount();
+ $q = new HardwareQuery(HardwareInfo::RAM_MODULE);
+ $speed = $q->addGlobalColumn('Speed');
+ $q->addLocalColumn('Configured Speed')->addCondition('<', $speed);
+ $speed->addCondition('>', 2666);
+ $q->addMachineWhere('clientip', '>', 5);
+ echo $q->buildQuery(), "\n";
exit;
}
@@ -352,7 +338,7 @@ if ($type[0] === '~') {
$hwid = $hwids[$screen['name']];
} else {
$hwid = (int)Database::insertIgnore('statistic_hw', 'hwid',
- array('hwtype' => DeviceType::SCREEN, 'hwname' => $screen['name']));
+ array('hwtype' => HardwareInfo::SCREEN, 'hwname' => $screen['name']));
$hwids[$screen['name']] = $hwid;
}
// Now add new entries
@@ -398,7 +384,7 @@ if ($type[0] === '~') {
Database::exec("UPDATE machine_x_hw x, statistic_hw h
SET x.disconnecttime = UNIX_TIMESTAMP()
WHERE x.machineuuid = :uuid AND x.hwid = h.hwid AND h.hwtype = :type AND x.disconnecttime = 0",
- array('uuid' => $uuid, 'type' => DeviceType::SCREEN));
+ array('uuid' => $uuid, 'type' => HardwareInfo::SCREEN));
} else {
// Some screens connected, make sure old entries get removed
Database::exec("UPDATE machine_x_hw x, statistic_hw h
@@ -407,7 +393,7 @@ if ($type[0] === '~') {
AND x.machineuuid = :uuid", array(
'pairs' => $keepPair,
'uuid' => $uuid,
- 'type' => DeviceType::SCREEN,
+ 'type' => HardwareInfo::SCREEN,
));
}
}
diff --git a/modules-available/statistics/hooks/config-tgz.inc.php b/modules-available/statistics/hooks/config-tgz.inc.php
index 0a43e115..b732fd7a 100644
--- a/modules-available/statistics/hooks/config-tgz.inc.php
+++ b/modules-available/statistics/hooks/config-tgz.inc.php
@@ -4,7 +4,7 @@ $res = Database::simpleQuery('SELECT h.hwname FROM statistic_hw h'
. " INNER JOIN statistic_hw_prop p ON (h.hwid = p.hwid AND p.prop = :projector)"
. " WHERE h.hwtype = :screen ORDER BY h.hwname ASC", array(
'projector' => 'projector',
- 'screen' => DeviceType::SCREEN,
+ 'screen' => HardwareInfo::SCREEN,
));
if ($res !== false) { // CHeck this in case we're running on old DB during update
diff --git a/modules-available/statistics/inc/devicetype.inc.php b/modules-available/statistics/inc/devicetype.inc.php
deleted file mode 100644
index a01ec310..00000000
--- a/modules-available/statistics/inc/devicetype.inc.php
+++ /dev/null
@@ -1,7 +0,0 @@
-<?php
-
-class DeviceType
-{
- const SCREEN = 'SCREEN';
-
-}
diff --git a/modules-available/statistics/inc/hardwareinfo.inc.php b/modules-available/statistics/inc/hardwareinfo.inc.php
index df33a5f6..6ca070ee 100644
--- a/modules-available/statistics/inc/hardwareinfo.inc.php
+++ b/modules-available/statistics/inc/hardwareinfo.inc.php
@@ -12,6 +12,7 @@ class HardwareInfo
const PCI_DEVICE = 'PCI_DEVICE';
const HDD = 'HDD';
const CPU = 'CPU';
+ const SCREEN = 'SCREEN';
/**
* Get a KCL modification string for the given machine, enabling GVT, PCI passthrough etc.
@@ -44,7 +45,7 @@ class HardwareInfo
return '';
$hw = new HardwareQuery(self::PCI_DEVICE, $best['machineuuid'], true);
// TODO: Get list of enabled pass through groups for this client's location
- $hw->addJoin(true, '@PASSTHROUGH', 'passthrough_group_x_location', 'groupid',
+ $hw->addForeignJoin(true, '@PASSTHROUGH', 'passthrough_group_x_location', 'groupid',
'locationid', $locations);
$hw->addGlobalColumn('vendor');
$hw->addGlobalColumn('device');
@@ -77,7 +78,7 @@ class HardwareInfo
foreach (array_keys($slots) as $slot) {
//error_log('Querying slot ' . $slot);
$hw = new HardwareQuery(self::PCI_DEVICE, $best['machineuuid'], true);
- $hw->addWhere(false, 'slot', 'LIKE', $slot . '.%');
+ $hw->addLocalColumn('slot')->addCondition('LIKE', $slot . '.%');
$hw->addGlobalColumn('vendor');
$hw->addGlobalColumn('device');
foreach ($hw->query() as $row) {
diff --git a/modules-available/statistics/inc/hardwarequery.inc.php b/modules-available/statistics/inc/hardwarequery.inc.php
index 24b27190..00a9bbc9 100644
--- a/modules-available/statistics/inc/hardwarequery.inc.php
+++ b/modules-available/statistics/inc/hardwarequery.inc.php
@@ -10,10 +10,10 @@ class HardwareQuery
private $columns = [];
/**
- * @param string $type type form HardwareInfo
- * @param ?string $uuid
+ * @param string $type hardware type form HardwareInfo
+ * @param ?string $uuid If set, only return data for specific client
*/
- public function __construct(string $type, $uuid = null, $connectedOnly = true)
+ public function __construct(string $type, string $uuid = null, $connectedOnly = true)
{
if ($connectedOnly) {
$this->joins['mxhw_join'] = "INNER JOIN machine_x_hw mxhw ON (mxhw.hwid = shw.hwid AND mxhw.disconnecttime = 0)";
@@ -30,50 +30,33 @@ class HardwareQuery
private function id(): string
{
- return 't' . (++$this->id);
- }
-
- private function fillTableVars(bool $global, &$srcTable, &$table, &$column)
- {
- if ($global) {
- $srcTable = 'shw';
- $table = 'statistic_hw_prop';
- $column = 'hwid';
- } else {
- $srcTable = 'mxhw';
- $table = 'machine_x_hw_prop';
- $column = 'machinehwid';
- }
+ return 'b' . (++$this->id);
}
/**
- * @param bool $global
- * @param string $prop
- * @param string $op
- * @param string|string[] $value
+ * Add join of a virtual column (hw property) to an arbitrary table and column.
+ * @param bool $global Is the virtual column global or local to machine?
+ * @param string $prop Name of property/virtual column
+ * @param string $jTable Table to join on
+ * @param string $jColumn Column to join on
+ * @param string $condColumn optionally, another column from the joined table to match against $condVal
+ * @param string|array $condVal optionally, a literal, or array of literals, to match foreign column against
+ * @return void
*/
- public function addWhere(bool $global, string $prop, string $op, $value)
- {
- if (isset($this->columns[$prop]))
- return;
- $this->fillTableVars($global, $srcTable, $table, $column);
- $tid = $this->id();
- $pid = $this->id();
- $vid = $this->id();
- $valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
- $this->joins[$prop] = "INNER JOIN $table $tid ON ($srcTable.$column = $tid.$column AND
- $tid.prop = :$pid AND $tid.`$valueCol` $op (:$vid))";
- $this->args[$pid] = $prop;
- $this->args[$vid] = $value;
- $this->columns[$prop] = "$tid.`value` AS `$prop`";
- }
-
- public function addJoin(bool $global, string $prop, string $jTable, string $jColumn, string $condColumn = '', $condVal = null)
+ public function addForeignJoin(bool $global, string $prop, string $jTable, string $jColumn, string $condColumn = '', $condVal = null)
{
if (isset($this->columns[$jTable]))
return;
if (!isset($this->columns[$prop])) {
- $this->fillTableVars($global, $srcTable, $table, $column);
+ if ($global) {
+ $srcTable = 'shw';
+ $table = 'statistic_hw_prop';
+ $column = 'hwid';
+ } else {
+ $srcTable = 'mxhw';
+ $table = 'machine_x_hw_prop';
+ $column = 'machinehwid';
+ }
$tid = $this->id();
$pid = $this->id();
$this->joins[$prop] = "INNER JOIN $table $tid ON ($srcTable.$column = $tid.$column
@@ -99,55 +82,33 @@ class HardwareQuery
{
if (isset($this->columns[$column]))
return;
- $valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
$vid = $this->id();
$this->joins['machine'] = 'INNER JOIN machine m USING (machineuuid)';
- $this->where[] = "m.$column $op :$vid";
+ $this->where[] = "m.$column $op (:$vid)";
$this->args[$vid] = $value;
$this->columns[$column] = "m.$column";
}
- public function addCompare(bool $global1, string $prop1, string $op, string $global2, string $prop2)
+ public function addGlobalColumn(string $prop): HardwareQueryColumn
{
- $this->fillTableVars($global1, $srcTable1, $table1, $column1);
- $this->fillTableVars($global2, $srcTable2, $table2, $column2);
- $tid1 = $this->id();
- $pid1 = $this->id();
- $tid2 = $this->id();
- $pid2 = $this->id();
- $valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
- $this->joins[] = "INNER JOIN $table1 $tid1 ON ($srcTable1.$column1 = $tid1.$column1 AND
- $tid1.prop = :$pid1)";
- $this->joins[] = "INNER JOIN $table2 $tid2 ON ($srcTable2.$column2 = $tid2.$column2 AND
- $tid2.prop = :$pid2 AND $tid1.`$valueCol` $op $tid2.`$valueCol`)";
- $this->args[$pid1] = $prop1;
- $this->args[$pid2] = $prop2;
- $this->columns[$prop1] = "$tid1.`value` AS `$prop1`";
- $this->columns[$prop2] = "$tid2.`value` AS `$prop2`";
+ return $this->addColumn(true, $prop);
}
- public function addGlobalColumn(string $prop)
+ public function addLocalColumn(string $prop): HardwareQueryColumn
{
- $this->addColumn(true, $prop);
+ return $this->addColumn(false, $prop);
}
- public function addLocalColumn(string $prop)
+ public function addColumn(bool $global, string $prop, string $alias = null): HardwareQueryColumn
{
- $this->addColumn(false, $prop);
- }
-
- public function addColumn(bool $global, string $prop)
- {
- if (isset($this->columns[$prop]))
- return;
- $this->fillTableVars($global, $srcTable, $table, $column);
- $tid = $this->id();
- $pid = $this->id();
- $this->joins[$prop] = "LEFT JOIN $table $tid ON ($srcTable.$column = $tid.$column AND $tid.prop = :$pid)";
- $this->args[$pid] = $prop;
- $this->columns[$prop] = "$tid.`value` AS `$prop`";
+ return $this->columns[] = new HardwareQueryColumn($global, $prop, $alias);
}
+ /**
+ * Join the machine table and add the given column from it to the SELECT
+ * @param string $column
+ * @return void
+ */
public function addMachineColumn(string $column)
{
if (isset($this->columns[$column]))
@@ -161,9 +122,27 @@ class HardwareQuery
*/
public function query(string $groupBy = '')
{
- $columns = $this->columns;
+ return Database::simpleQuery($this->buildQuery($groupBy), $this->args);
+ }
+
+ /**
+ * Build query string
+ * @param string $groupBy Column to group by
+ */
+ public function buildQuery(string $groupBy = ''): string
+ {
+ $groupConcat = !empty($groupBy) && $groupBy !== 'mxhw.machinehwid';
+ $columns = [];
+ foreach ($this->columns as $column) {
+ if ($column instanceof HardwareQueryColumn) {
+ $column->generate($this->joins, $columns, $this->args, $groupConcat);
+ } else {
+ $columns[] = $column;
+ }
+ }
$columns[] = 'mxhw.machineuuid';
$columns[] = 'shw.hwid';
+ // TODO: Untangle this implicit magic
if (empty($groupBy) || $groupBy === 'mxhw.machinehwid') {
$columns[] = 'mxhw.disconnecttime';
} else {
@@ -173,12 +152,11 @@ class HardwareQuery
$columns[] = 'Count(*) AS group_count';
$groupBy = " GROUP BY $groupBy";
}
- $query = 'SELECT ' . implode(', ', $columns)
+ return 'SELECT ' . implode(', ', $columns)
. ' FROM statistic_hw shw '
. implode(' ', $this->joins)
. ' WHERE ' . implode(' AND ', $this->where)
. $groupBy;
- return Database::simpleQuery($query, $this->args);
}
} \ No newline at end of file
diff --git a/modules-available/statistics/inc/hardwarequerycolumn.inc.php b/modules-available/statistics/inc/hardwarequerycolumn.inc.php
new file mode 100644
index 00000000..d073530b
--- /dev/null
+++ b/modules-available/statistics/inc/hardwarequerycolumn.inc.php
@@ -0,0 +1,88 @@
+<?php
+
+class HardwareQueryColumn
+{
+ /** @var int For unique table names in join */
+ private static $id = 0;
+
+ private $global;
+ private $tableAlias;
+ private $virtualColumnName;
+ private $alias;
+ private $conditions = [];
+ private $params = [];
+ private $classId;
+
+ private static function getId(): string
+ {
+ return 't' . ++self::$id;
+ }
+
+ public function __construct(bool $global, string $column, string $alias = null)
+ {
+ $this->classId = ++self::$id;
+ $this->global = $global;
+ $this->tableAlias = self::getId();
+ $this->virtualColumnName = $column;
+ $this->alias = $alias ?? $column;
+ }
+
+ /**
+ * Add necessary conditions, joins, columns to final SQL arrays. To be called
+ * from HardwareQuery::buildQuery().
+ * @param bool $groupConcat whether to add distinct GROUP_CONCAT to column.
+ */
+ public function generate(array &$joins, array &$columns, array &$params, bool $groupConcat)
+ {
+ if ($this->global) {
+ $srcTable = 'shw';
+ $table = 'statistic_hw_prop';
+ $column = 'hwid';
+ } else {
+ $srcTable = 'mxhw';
+ $table = 'machine_x_hw_prop';
+ $column = 'machinehwid';
+ }
+ $tid = $this->tableAlias;
+ $pid = self::getId();
+ $this->conditions[] = "$srcTable.$column = $tid.$column AND $tid.prop = :$pid";
+ $params[$pid] = $this->virtualColumnName; // value of property column is our virtual column
+ // If we have just one condition, it's the join condition itself. Since we pretend we're just adding
+ // a column to the query, do a left join, so the "column" is NULL if the join doesn't match.
+ // If however any conditions were added to this class via the addCondition() method, do a regular
+ // INNER JOIN, so the result will be empty if the condition doesn't match.
+ $type = count($this->conditions) === 1 ? 'LEFT' : 'INNER';
+ $joins[] = "$type JOIN $table $tid ON (" . implode(' AND ', $this->conditions) . ")";
+ if ($groupConcat) {
+ $columns[] = "Group_Concat(DISTINCT $tid.`value` SEPARATOR ', ') AS `{$this->alias}`";
+ } else {
+ $columns[] = "$tid.`value` AS `{$this->alias}`";
+ }
+ $params += $this->params;
+ }
+
+ /**
+ * @param string $op Operator (<>=, IN, LIKE)
+ * @param string|string[]|HardwareQueryColumn $other value to compare with.
+ * Can be a literal, an array (if opererator is IN), or another Column
+ * @return void
+ */
+ public function addCondition(string $op, $other)
+ {
+ $valueCol = ($op === '<' || $op === '>' || $op === '<=' || $op === '>=') ? 'numeric' : 'value';
+ if ($other instanceof HardwareQueryColumn) {
+ $cond = "{$this->tableAlias}.`$valueCol` $op {$other->tableAlias}.`$valueCol`";
+ // Don't reference a column of a table that hasn't been joined yet
+ if ($this->classId > $other->classId) {
+ $this->conditions[] = $cond;
+ } else {
+ $other->conditions[] = $cond;
+ }
+ } else {
+ $pid = self::getId();
+ $this->conditions[] = "{$this->tableAlias}.`$valueCol` $op (:$pid)";
+ $this->params[$pid] = $other;
+ }
+ }
+
+} \ No newline at end of file
diff --git a/modules-available/statistics/pages/hints.inc.php b/modules-available/statistics/pages/hints.inc.php
index 278c0e26..67c42a18 100644
--- a/modules-available/statistics/pages/hints.inc.php
+++ b/modules-available/statistics/pages/hints.inc.php
@@ -19,6 +19,10 @@ class SubPage
self::showUnusedSpace($locs);
}
+ /**
+ * Machines that have less than 8GB of RAM. Highlight those
+ * that still have free memory slots.
+ */
private static function showMemoryUpgrade(array $locs)
{
$q = new HardwareQuery(HardwareInfo::MAINBOARD);
@@ -30,7 +34,8 @@ class SubPage
$q->addGlobalColumn('Memory Maximum Capacity');
$q->addMachineColumn('clientip');
$q->addMachineColumn('hostname');
- $q->addWhere(false, 'Memory Installed Capacity', '<', 8 * 1024 * 1024 * 1024);
+ $col = $q->addLocalColumn('Memory Installed Capacity');
+ $col->addCondition('<', 8 * 1024 * 1024 * 1024);
$list = [];
foreach ($q->query() as $row) {
if (HardwareParser::convertSize($row['Memory Installed Capacity'], 'M', false)
@@ -47,6 +52,10 @@ class SubPage
Render::addTemplate('hints-ram-upgrade', ['list' => $list]);
}
+ /**
+ * Show machines where RAM modules are running slower
+ * than their design speed.
+ */
private static function showMemorySlow(array $locs)
{
$q = new HardwareQuery(HardwareInfo::RAM_MODULE);
@@ -62,11 +71,16 @@ class SubPage
$q->addLocalColumn('Serial Number');
$q->addMachineColumn('clientip');
$q->addMachineColumn('hostname');
- $q->addCompare(true, 'Speed', '>', false, 'Configured Memory Speed');
+ $col = $q->addGlobalColumn('Speed');
+ $col->addCondition('>', $q->addLocalColumn('Configured Memory Speed'));
$list = $q->query()->fetchAll();
Render::addTemplate('hints-ram-underclocked', ['list' => $list]);
}
+ /**
+ * Show machines that have unpartitioned space available,
+ * and no ID44 or ID45.
+ */
private static function showUnusedSpace(array $locs)
{
$id44 = $id45 = [];
@@ -77,7 +91,7 @@ class SubPage
}
$q->addMachineColumn('clientip');
$q->addMachineColumn('hostname');
- $q->addWhere(false, 'unused', '>', 2000000000); // 2 GB
+ $q->addLocalColumn('unused')->addCondition('>', 2000000000); // 2 GB
$q->addMachineWhere('id44mb', '<', 20000); // 20 GB
foreach ($q->query()->fetchAll() as $row) {
$row['unused_s'] = Util::readableFileSize($row['unused']);
@@ -91,7 +105,7 @@ class SubPage
}
$q->addMachineColumn('clientip');
$q->addMachineColumn('hostname');
- $q->addWhere(false, 'unused', '>', 25000000000); // 25 GB
+ $q->addLocalColumn('unused')->addCondition('>', 25000000000); // 25 GB
$q->addMachineWhere('id44mb', '>', 20000); // 20 GB
$q->addMachineWhere('id45mb', '<', 20000); // 20 GB
foreach ($q->query()->fetchAll() as $row) {
diff --git a/modules-available/statistics/pages/machine.inc.php b/modules-available/statistics/pages/machine.inc.php
index 019b0d8d..6ea0ce28 100644
--- a/modules-available/statistics/pages/machine.inc.php
+++ b/modules-available/statistics/pages/machine.inc.php
@@ -229,7 +229,7 @@ class SubPage
. " LEFT JOIN machine_x_hw_prop p ON (m.machinehwid = p.machinehwid AND p.prop = 'resolution')"
. " LEFT JOIN statistic_hw_prop q ON (m.hwid = q.hwid AND q.prop = 'projector')"
. " WHERE m.machineuuid = :uuid",
- array('screen' => DeviceType::SCREEN, 'uuid' => $uuid));
+ array('screen' => HardwareInfo::SCREEN, 'uuid' => $uuid));
$client['screens'] = array();
$ports = array();
foreach ($res as $row) {
diff --git a/modules-available/statistics/pages/projectors.inc.php b/modules-available/statistics/pages/projectors.inc.php
index 97a21ebd..91abfe9c 100644
--- a/modules-available/statistics/pages/projectors.inc.php
+++ b/modules-available/statistics/pages/projectors.inc.php
@@ -49,7 +49,7 @@ class SubPage
. " INNER JOIN statistic_hw_prop p ON (h.hwid = p.hwid AND p.prop = :projector)"
. " WHERE h.hwtype = :screen ORDER BY h.hwname ASC", array(
'projector' => 'projector',
- 'screen' => DeviceType::SCREEN,
+ 'screen' => HardwareInfo::SCREEN,
));
$data = array(
'projectors' => $res->fetchAll()