summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--install.php84
-rw-r--r--modules-available/permissionmanager/install.inc.php30
-rw-r--r--modules-available/statistics/api.inc.php29
-rw-r--r--modules-available/statistics/inc/parser.inc.php2
-rw-r--r--modules-available/statistics/install.inc.php39
-rw-r--r--modules-available/syslog/install.inc.php31
6 files changed, 126 insertions, 89 deletions
diff --git a/install.php b/install.php
index 88eb82c2..c471f4b6 100644
--- a/install.php
+++ b/install.php
@@ -50,7 +50,6 @@ define('UPDATE_FAILED', 'UPDATE_FAILED'); // Fatal error occured, retry will not
function tableHasColumn($table, $column)
{
$table = preg_replace('/\W/', '', $table);
- $column = preg_replace('/\W/', '', $column);
$res = Database::simpleQuery("DESCRIBE `$table`", array(), true);
if ($res !== false) {
while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
@@ -61,6 +60,32 @@ function tableHasColumn($table, $column)
return false;
}
+function tableHasIndex($table, $index)
+{
+ $table = preg_replace('/\W/', '', $table);
+ if (!is_array($index)) {
+ $index = [$index];
+ }
+ $res = Database::simpleQuery("SHOW INDEX FROM `$table`", array(), true);
+ if ($res !== false) {
+ $matches = [];
+ while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
+ $i = $row['Seq_in_index'] - 1;
+ if (isset($index[$i]) && $index[$i] === $row['Column_name']) {
+ if (!isset($matches[$row['Key_name']])) {
+ $matches[$row['Key_name']] = 0;
+ }
+ $matches[$row['Key_name']]++;
+ }
+ }
+ }
+ foreach ($matches as $m) {
+ if ($m === count($index))
+ return true;
+ }
+ return false;
+}
+
function tableDropColumn($table, $column)
{
$table = preg_replace('/\W/', '', $table);
@@ -91,13 +116,13 @@ function tableRename($old, $new) {
/**
- * Get all constraints from given table+column to another table+column.
+ * Get all constraint details for given combo.
*
* @param string $table source table, being constrained
* @param string $column source column
* @param string $refTable referenced table, dictating the constraints
* @param string $refColumn referenced column
- * @return false|string[] list of constraints matching the request, false on error
+ * @return false|string[] false == doesn't exist, assoc array otherwise
*/
function tableGetConstraints($table, $column, $refTable, $refColumn)
{
@@ -110,21 +135,12 @@ function tableGetConstraints($table, $column, $refTable, $refColumn)
define('CONFIG_SQL_DB', $db);
}
}
- $res = Database::simpleQuery('SELECT `CONSTRAINT_NAME` FROM information_schema.KEY_COLUMN_USAGE'
- . ' WHERE `TABLE_SCHEMA` = :db AND `TABLE_NAME` = :table AND `COLUMN_NAME` = :column'
- . ' AND `REFERENCED_TABLE_NAME` = :refTable AND `REFERENCED_COLUMN_NAME` = :refColumn',
+ return Database::queryFirst('SELECT b.CONSTRAINT_NAME, b.UPDATE_RULE, b.DELETE_RULE
+ FROM information_schema.KEY_COLUMN_USAGE a
+ INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS b USING (CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME)
+ WHERE a.TABLE_SCHEMA = :db AND a.TABLE_NAME = :table AND a.COLUMN_NAME = :column
+ AND a.REFERENCED_TABLE_NAME = :refTable AND a.REFERENCED_COLUMN_NAME = :refColumn',
compact('db', 'table', 'column', 'refTable', 'refColumn'));
- if ($res === false)
- return false;
- return $res->fetchAll(PDO::FETCH_COLUMN, 0);
-}
-
-/**
- * Because I'm stupid and can't type properly.
- */
-function tableGetContraints($table, $column, $refTable, $refColumn)
-{
- return tableGetConstraints($table, $column, $refTable, $refColumn);
}
/**
@@ -138,28 +154,42 @@ function tableGetContraints($table, $column, $refTable, $refColumn)
* @param string $actions "ON xxx ON yyy" string
* @return string UPDATE_* result code
*/
-function tableAddConstraint($table, $column, $refTable, $refColumn, $actions)
+function tableAddConstraint($table, $column, $refTable, $refColumn, $actions, $ignoreError = false)
{
$test = tableExists($refTable) && tableHasColumn($refTable, $refColumn);
if ($test === false) {
// Most likely, destination table does not exist yet or isn't up to date
return UPDATE_RETRY;
}
- $test = tableGetConstraints($table, $column, $refTable, $refColumn);
- if ($test === false) {
- // Should never happen!?
- finalResponse(UPDATE_FAILED, 'DB: Cannot query constraints: ' . Database::lastError());
+ // TODO: Refactor function, make this two args
+ $update = 'RESTRICT';
+ $delete = 'RESTRICT';
+ if (preg_match('/on\s+update\s+(RESTRICT|SET\s+NULL|CASCADE)/ims', $actions, $out)) {
+ $update = preg_replace('/\s+/ms', ' ', strtoupper($out[1]));
}
- if (!empty($test)) {
- // Already exists
- return UPDATE_NOOP;
+ if (preg_match('/on\s+delete\s+(RESTRICT|SET\s+NULL|CASCADE)/ims', $actions, $out)) {
+ $delete = preg_replace('/\s+/ms', ' ', strtoupper($out[1]));
+ }
+ $test = tableGetConstraints($table, $column, $refTable, $refColumn);
+ if ($test !== false) {
+ // Exists, check if same
+ if ($test['UPDATE_RULE'] === $update && $test['DELETE_RULE'] === $delete) {
+ // Yep, nothing more to do here
+ return UPDATE_NOOP;
+ }
+ // Kill the old one
+ $ret = tableDeleteConstraint($table, $test['CONSTRAINT_NAME']);
}
// Need to create
$ret = Database::exec("ALTER TABLE `$table` ADD CONSTRAINT FOREIGN KEY (`$column`)
REFERENCES `$refTable` (`$refColumn`)
- $actions");
+ ON DELETE $delete ON UPDATE $update");
if ($ret === false) {
- finalResponse(UPDATE_FAILED, 'DB: Cannot add constraint: ' . Database::lastError());
+ if ($ignoreError) {
+ return UPDATE_FAILED;
+ } else {
+ finalResponse(UPDATE_FAILED, 'DB: Cannot add constraint: ' . Database::lastError());
+ }
}
return UPDATE_DONE;
}
diff --git a/modules-available/permissionmanager/install.inc.php b/modules-available/permissionmanager/install.inc.php
index 7f9c80ef..d0f8cdeb 100644
--- a/modules-available/permissionmanager/install.inc.php
+++ b/modules-available/permissionmanager/install.inc.php
@@ -51,50 +51,40 @@ if (tableHasColumn('role_x_location', 'id')) {
if (!tableExists('user') || !tableExists('location')) {
finalResponse(UPDATE_RETRY, 'Cannot add constraint yet. Please retry.');
} else {
- $c = tableGetContraints('role_x_user', 'userid', 'user', 'userid');
- if ($c === false)
- finalResponse(UPDATE_FAILED, 'Cannot get constraints of user table: ' . Database::lastError());
- if (empty($c)) {
+ $c = tableGetConstraints('role_x_user', 'userid', 'user', 'userid');
+ if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (userid) REFERENCES user (userid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add userid constraint referencing user table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
- $c = tableGetContraints('role_x_user', 'roleid', 'role', 'roleid');
- if ($c === false)
- finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError());
- if (empty($c)) {
+ $c = tableGetConstraints('role_x_user', 'roleid', 'role', 'roleid');
+ if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_user ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
- $c = tableGetContraints('role_x_location', 'roleid', 'role', 'roleid');
- if ($c === false)
- finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError());
- if (empty($c)) {
+ $c = tableGetConstraints('role_x_location', 'roleid', 'role', 'roleid');
+ if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
- $c = tableGetContraints('role_x_location', 'locationid', 'location', 'locationid');
- if ($c === false)
- finalResponse(UPDATE_FAILED, 'Cannot get constraints of location table: ' . Database::lastError());
- if (empty($c)) {
+ $c = tableGetConstraints('role_x_location', 'locationid', 'location', 'locationid');
+ if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_location ADD FOREIGN KEY (locationid) REFERENCES location (locationid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add locationid constraint referencing location table: ' . Database::lastError());
$res[] = UPDATE_DONE;
}
- $c = tableGetContraints('role_x_permission', 'roleid', 'role', 'roleid');
- if ($c === false)
- finalResponse(UPDATE_FAILED, 'Cannot get constraints of role table: ' . Database::lastError());
- if (empty($c)) {
+ $c = tableGetConstraints('role_x_permission', 'roleid', 'role', 'roleid');
+ if ($c === false) {
$alter = Database::exec('ALTER TABLE role_x_permission ADD FOREIGN KEY (roleid) REFERENCES role (roleid) ON DELETE CASCADE ON UPDATE CASCADE');
if ($alter === false)
finalResponse(UPDATE_FAILED, 'Cannot add roleid constraint referencing role table: ' . Database::lastError());
diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php
index d4b8f346..19ae3cb6 100644
--- a/modules-available/statistics/api.inc.php
+++ b/modules-available/statistics/api.inc.php
@@ -14,23 +14,20 @@ if (substr($ip, 0, 7) === '::ffff:') $ip = substr($ip, 7);
if ($type{0} === '~') {
// UUID is mandatory
$uuid = Request::post('uuid', '', 'string');
- if (strlen($uuid) !== 36) die("Invalid UUID.\n");
- $macaddr = Request::post('macaddr', '', 'string');
- if (!empty($macaddr) && substr($uuid, 0, 16) === '000000000000001-') {
- // Override uuid if the mac is known and unique
- $res = Database::simpleQuery('SELECT machineuuid FROM machine WHERE macaddr = :macaddr AND machineuuid <> :uuid', compact('macaddr', 'uuid'));
- $override = false;
- while ($row = $res->fetch(PDO::FETCH_ASSOC)) {
- if ($override !== false) {
- $override = false;
- break;
- }
- $override = $row['machineuuid'];
- }
- if ($override !== false) {
- $uuid = $override;
+ $macaddr = Request::post('macaddr', false, 'string');
+ if ($macaddr !== false) {
+ $macaddr = strtolower(str_replace(':', '-', $macaddr));
+ if (strlen($macaddr) !== 17 || $macaddr{2} !== '-') {
+ $macaddr = false;
}
}
+ if ($macaddr !== false && $uuid{8} !== '-' && substr($uuid, 0, 16) === '000000000000001-') {
+ $uuid = 'baad1d00-9491-4716-b98b-' . str_replace('-', '', $macaddr);
+ }
+ if (strlen($uuid) !== 36 || !preg_match('/^[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}$/i', $uuid)) {
+ die("Invalid UUID.\n");
+ }
+ $uuid = strtoupper($uuid);
// External mode of operation?
$mode = Request::post('mode', false, 'string');
$NOW = time();
@@ -45,7 +42,7 @@ if ($type{0} === '~') {
if ($mode === false && $type === '~poweron') {
// Poweron & hw stats
$uptime = Request::post('uptime', 0, 'integer');
- if (strlen($macaddr) > 17) die("Invalid MAC.\n");
+ if ($macaddr === false) die("No/Invalid MAC address.\n");
if ($uptime < 0 || $uptime > 4000000) die("Implausible uptime.\n");
$realcores = Request::post('realcores', 0, 'integer');
if ($realcores < 0 || $realcores > 512) $realcores = 0;
diff --git a/modules-available/statistics/inc/parser.inc.php b/modules-available/statistics/inc/parser.inc.php
index 0d39079d..8ac3fcf3 100644
--- a/modules-available/statistics/inc/parser.inc.php
+++ b/modules-available/statistics/inc/parser.inc.php
@@ -90,7 +90,7 @@ class Parser {
}
}
if (empty($row['ramslotcount'])) {
- $row['ramslotcount'] = count($row['ramslot']);
+ $row['ramslotcount'] = isset($row['ramslot']) ? count($row['ramslot']) : 0;
}
}
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php
index 84e038a4..a9e8fb7b 100644
--- a/modules-available/statistics/install.inc.php
+++ b/modules-available/statistics/install.inc.php
@@ -196,32 +196,15 @@ if ($addTrigger) {
if (Module::isAvailable('locations')) {
if (tableExists('subnet')) {
AutoLocation::rebuildAll();
- } else {
- finalResponse(UPDATE_RETRY, 'Locations module not installed yet, retry later');
}
}
$res[] = UPDATE_DONE;
}
-if ($machineHwCreate === UPDATE_DONE) {
- $ret = Database::exec('ALTER TABLE `machine_x_hw`
- ADD CONSTRAINT `machine_x_hw_ibfk_1` FOREIGN KEY (`hwid`) REFERENCES `statistic_hw` (`hwid`) ON DELETE CASCADE,
- ADD CONSTRAINT `machine_x_hw_ibfk_2` FOREIGN KEY (`machineuuid`) REFERENCES `machine` (`machineuuid`) ON DELETE CASCADE');
- if ($ret === false) {
- finalResponse(UPDATE_FAILED, 'Adding constraints to machine_x_hw failed: ' . Database::lastError());
- }
- $ret = Database::exec('ALTER TABLE `machine_x_hw_prop`
- ADD CONSTRAINT `machine_x_hw_prop_ibfk_1` FOREIGN KEY (`machinehwid`) REFERENCES `machine_x_hw` (`machinehwid`) ON DELETE CASCADE');
- if ($ret === false) {
- finalResponse(UPDATE_FAILED, 'Adding constraint to machine_x_hw_prop failed: ' . Database::lastError());
- }
- $ret = Database::exec('ALTER TABLE `statistic_hw_prop`
- ADD CONSTRAINT `statistic_hw_prop_ibfk_1` FOREIGN KEY (`hwid`) REFERENCES `statistic_hw` (`hwid`) ON DELETE CASCADE');
- if ($ret === false) {
- finalResponse(UPDATE_FAILED, 'Adding constraint to statistic_hw_prop failed: ' . Database::lastError());
- }
- $res[] = UPDATE_DONE;
-}
+$res[] = tableAddConstraint('machine_x_hw', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE');
+$res[] = tableAddConstraint('machine_x_hw', 'machineuuid', 'machine', 'machineuuid', 'ON DELETE CASCADE ON UPDATE CASCADE');
+$res[] = tableAddConstraint('machine_x_hw_prop', 'machinehwid', 'machine_x_hw', 'machinehwid', 'ON DELETE CASCADE');
+$res[] = tableAddConstraint('statistic_hw_prop', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE');
// 2017-11-27: Add state column
if (!tableHasColumn('machine', 'state')) {
@@ -251,5 +234,19 @@ if (!tableHasColumn('machine', 'live_tmpsize')) {
$res[] = UPDATE_DONE;
}
+// 2019-02-20: Convert bogus UUIDs
+$res2 = Database::simpleQuery("SELECT machineuuid, macaddr FROM machine WHERE machineuuid LIKE '000000000000001-%'");
+while ($row = $res2->fetch(PDO::FETCH_ASSOC)) {
+ $new = strtoupper('baad1d00-9491-4716-b98b-' . preg_replace('/[^0-9a-f]/i', '', $row['macaddr']));
+ error_log('Replacing ' . $row['machineuuid'] . ' with ' . $new);
+ if (strlen($new) === 36) {
+ if (Database::exec("UPDATE machine SET machineuuid = :new WHERE machineuuid = :old",
+ ['old' => $row['machineuuid'], 'new' => $new]) === false) {
+ error_log('Result: ' . Database::lastError());
+ Database::exec("DELETE FROM machine WHERE machineuuid = :old", ['old' => $row['machineuuid']]);
+ }
+ }
+}
+
// Create response
responseFromArray($res);
diff --git a/modules-available/syslog/install.inc.php b/modules-available/syslog/install.inc.php
index 406a6cc6..9d79c811 100644
--- a/modules-available/syslog/install.inc.php
+++ b/modules-available/syslog/install.inc.php
@@ -14,7 +14,7 @@ $res[] = $tc = tableCreate('clientlog', "
KEY `dateline` (`dateline`),
KEY `logtypeid` (`logtypeid`,`dateline`),
KEY `clientip` (`clientip`,`dateline`),
- KEY `machineuuid` (`machineuuid`,`logid`)
+ KEY `machineuuidnew` (`machineuuid`,`logid`)
");
// Update path
@@ -30,9 +30,32 @@ if (!tableHasColumn('clientlog', 'machineuuid')) {
}
// 2017-11-03: Create proper index for query in statistics module
-if ($tc !== UPDATE_DONE) {
- Database::exec("ALTER TABLE `openslx`.`clientlog` DROP INDEX `machineuuid` ,
- ADD INDEX `machineuuid` ( `machineuuid` , `logid` )");
+if (tableHasIndex('clientlog', ['machineuuid'])) {
+ $r = Database::exec("ALTER TABLE `openslx`.`clientlog` DROP INDEX `machineuuid`");
+ $res[] = $r === false ? UPDATE_FAILED : UPDATE_DONE;
+}
+if (!tableHasIndex('clientlog', ['machineuuid', 'logid'])) {
+ $r = Database::exec("ALTER TABLE `openslx`.`clientlog`
+ ADD INDEX `machineuuid` ( `machineuuid` , `logid` )");
+ $res[] = $r === false ? UPDATE_FAILED : UPDATE_DONE;
+}
+
+// 2019-02-20: Add constraint for machineuuid
+if (tableExists('machine')) {
+ $rr = tableAddConstraint('clientlog', 'machineuuid', 'machine', 'machineuuid',
+ 'ON DELETE SET NULL ON UPDATE CASCADE', true);
+ if ($rr === UPDATE_FAILED) {
+ // The table might still be populated with orphaned rows
+ $dups = Database::queryColumnArray("SELECT DISTINCT l.machineuuid FROM clientlog l LEFT JOIN machine m USING (machineuuid) WHERE m.machineuuid IS NULL");
+ if (!empty($dups)) {
+ Database::exec("UPDATE clientlog SET machineuuid = NULL WHERE machineuuid IN (:dups)", ['dups' => $dups]);
+ $rr = tableAddConstraint('clientlog', 'machineuuid', 'machine', 'machineuuid',
+ 'ON DELETE SET NULL ON UPDATE CASCADE');
+ }
+ }
+ $res[] = $rr;
+} elseif (Module::get('statistics') !== false) {
+ $res[] = UPDATE_RETRY;
}
// Create response for browser
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2024-05-08 19:58:07 +0200