Fix UUID handling; refactor constraints for machineuuid

5 files changed, 69 insertions, 62 deletions

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