[statistics/locations/..] Cache calculated locationid of machine in machine table

We now have three columns for the locationid:
- subnetlocationid
  Updated whenever the clientip of a client changes on bootup, or when
  locations/subnets are edited in the locations module.
- fixedlocationid
  Takes the role of the old locationid field - set when a machine is
  placed in a room via roomplanner, set to NULL otherwise.
- locationid
  Set to fixedlocationid if it's not NULL, to subnetlocationid otherwise.
  The field is updated by a BEFORE UPDATE trigger.

4 files changed, 69 insertions, 13 deletions

diff --git a/modules-available/statistics/api.inc.php b/modules-available/statistics/api.inc.php
index 5fc57194..2ac6e782 100644
--- a/modules-available/statistics/api.inc.php
+++ b/modules-available/statistics/api.inc.php
@@ -32,7 +32,7 @@ if ($type{0} === '~') {
 		}
 	}
 	$NOW = time();
-	$old = Database::queryFirst('SELECT logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
+	$old = Database::queryFirst('SELECT clientip, logintime, lastseen FROM machine WHERE machineuuid = :uuid', array('uuid' => $uuid));
 	if ($old !== false) {
 		settype($old['logintime'], 'integer');
 		settype($old['lastseen'], 'integer');
@@ -127,6 +127,12 @@ if ($type{0} === '~') {
 			'data'       => $data,
 			'hostname'   => $hostname,
 		));
+
+		if (($old === false || $old['clientip'] !== $ip) && Module::isAvailable('locations')) {
+			// New, or ip changed (dynamic pool?), update subnetlicationid
+			Location::updateMapIpToLocation($uuid, $ip);
+		}
+
 		// Write statistics data
 
 	} else if ($type === '~runstate') {
@@ -134,6 +140,10 @@ if ($type{0} === '~') {
 		$sessionLength = 0;
 		$used = Request::post('used', 0, 'integer');
 		if ($old === false) die("Unknown machine.\n");
+		if ($old['clientip'] !== $ip) {
+			EventLog::warning("[runstate] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip)");
+			die("Address changed.\n");
+		}
 		// Figure out what's happening
 		if ($used === 0) {
 			// Is not in use
@@ -173,12 +183,18 @@ if ($type{0} === '~') {
 			));
 		}
 	} elseif ($type === '~poweroff') {
-		if ($old !== false && $old['logintime'] !== 0) {
+		if ($old === false) die("Unknown machine.\n");
+		if ($old['clientip'] !== $ip) {
+			EventLog::warning("[poweroff] IP address of client $uuid seems to have changed ({$old['clientip']} -> $ip)");
+			die("Address changed.\n");
+		}
+		if ($old['logintime'] !== 0) {
 			$sessionLength = $old['lastseen'] - $old['logintime'];
 			if ($sessionLength > 0 && $sessionLength < 86400*2) {
-				Database::exec('INSERT INTO statistic (dateline, typeid, clientip, username, data)'
-					. " VALUES (:start, '~session-length', :clientip, '', :length)", array(
+				Database::exec('INSERT INTO statistic (dateline, typeid, machineuuid, clientip, username, data)'
+					. " VALUES (:start, '~session-length', :uuid, :clientip, '', :length)", array(
 					'start'     => $old['logintime'],
+					'uuid'     => $uuid,
 					'clientip'  => $ip,
 					'length'    => $sessionLength
 				));
diff --git a/modules-available/statistics/inc/filter.inc.php b/modules-available/statistics/inc/filter.inc.php
index 03dba6c3..6af6eed1 100644
--- a/modules-available/statistics/inc/filter.inc.php
+++ b/modules-available/statistics/inc/filter.inc.php
@@ -214,13 +214,11 @@ class LocationFilter extends Filter
 	{
 		settype($this->argument, 'int');
 		if ($this->argument === 0) {
-			$joins[] = 'LEFT JOIN subnet s ON (INET_ATON(machine.clientip) BETWEEN s.startaddr AND s.endaddr)';
-			return 'machine.locationid IS NULL AND s.locationid IS NULL';
+			$neg = $this->operator === '=' ? '' : 'NOT';
+			return "machine.locationid IS $neg NULL";
 		} else {
-			$joins[] = ' LEFT JOIN subnet ON (INET_ATON(clientip) BETWEEN startaddr AND endaddr AND machine.locationid IS NULL) ';
 			$args['lid'] = $this->argument;
-			$neg = $this->operator == '=' ? '' : 'NOT';
-			return "$neg ((subnet.locationid = :lid) OR (machine.locationid = :lid))";
+			return "machine.locationid {$this->operator} :lid";
 		}
 	}
 }
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php
index be07274c..0729d676 100644
--- a/modules-available/statistics/install.inc.php
+++ b/modules-available/statistics/install.inc.php
@@ -1,5 +1,8 @@
 <?php
 
+// locationid trigger
+$addTrigger = false;
+
 $res = array();
 
 // The main statistic table used for log entries
@@ -23,7 +26,9 @@ $res[] = tableCreate('statistic', "
 
 $res[] = tableCreate('machine', "
   `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
-  `locationid` int(11) DEFAULT NULL,
+  `fixedlocationid` int(11) DEFAULT NULL           COMMENT 'Manually set location (e.g. roomplanner)',
+  `subnetlocationid` int(11) DEFAULT NULL          COMMENT 'Automatically determined location (e.g. from subnet match),
+  `locationid` int(11) DEFAULT NULL                COMMENT 'Will be automatically set to fixedlocationid if not null, subnetlocationid otherwise',
   `macaddr` char(17) CHARACTER SET ascii NOT NULL,
   `clientip` varchar(45) CHARACTER SET ascii NOT NULL,
   `firstseen` int(10) unsigned NOT NULL,
@@ -66,6 +71,10 @@ $res[] = tableCreate('pciid', "
 	PRIMARY KEY (`category`,`id`)
 ");
 
+if (in_array(UPDATE_DONE, $res)) {
+	$addTrigger = true;
+}
+
 //
 // This was added/changed later -- keep update path
 //
@@ -112,6 +121,42 @@ if ($ret === false) {
 	finalResponse(UPDATE_FAILED, 'Expanding position column failed: ' . Database::lastError());
 }
 
+// 2016-12-06:
+// Add subnetlocationid - contains automatically determined location (by subnet)
+if (!tableHasColumn('machine', 'subnetlocationid')) {
+	$ret = Database::exec('ALTER TABLE machine'
+		. ' ADD COLUMN `subnetlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding subnetlocationid to machine failed: ' . Database::lastError());
+	}
+	$res[] = UPDATE_DONE;
+	$addTrigger = true;
+}
+// And fixedlocationid - manually set location, currently used by roomplanner
+if (!tableHasColumn('machine', 'fixedlocationid')) {
+	$ret = Database::exec('ALTER TABLE machine'
+		. ' ADD COLUMN `fixedlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding fixedlocationid to machine failed: ' . Database::lastError());
+	}
+	// Now copy over the values from locationid, since this was used before
+	Database::exec("UPDATE machine SET fixedlocationid = locationid");
+	$res[] = UPDATE_DONE;
+	$addTrigger = true;
+}
+// If any of these was added, create the trigger
+if ($addTrigger) {
+	$ret = Database::exec(" 
+	CREATE TRIGGER set_automatic_locationid
+		BEFORE UPDATE ON machine FOR EACH ROW
+	BEGIN
+		SET NEW.locationid = If(NEW.fixedlocationid IS NULL, NEW.subnetlocationid, NEW.fixedlocationid);
+	END");
+	if ($ret === false) {
+		finalResponse(UPDATE_FAILED, 'Adding locationid trigger to machine failed: ' . Database::lastError());
+	}
+}
+
 // Create response
 
 if (in_array(UPDATE_DONE, $res)) {
diff --git a/modules-available/statistics/page.inc.php b/modules-available/statistics/page.inc.php
index cc8acacc..6a9acd14 100644
--- a/modules-available/statistics/page.inc.php
+++ b/modules-available/statistics/page.inc.php
@@ -714,9 +714,6 @@ class Page_Statistics extends Page
 		unset($client['data']);
 		// Get locations
 		if (Module::isAvailable('locations')) {
-			if (is_null($client['locationid'])) {
-				$client['locationid'] = Location::getFromIp($client['clientip']);
-			}
 			$locs = Location::getLocationsAssoc();
 			$next = (int)$client['locationid'];
 			$output = array();