summaryrefslogtreecommitdiffstats
path: root/modules-available/statistics/install.inc.php
diff options
context:
space:
mode:
authorSimon Rettberg2016-12-06 16:21:56 +0100
committerSimon Rettberg2016-12-06 16:21:56 +0100
commit8fa66377a1c97d42d83d91ef0821f37f1b885617 (patch)
tree6386a99f2947194b89eac27127fe1805b25423d9 /modules-available/statistics/install.inc.php
parent[statistics] Check if module 'syslog' is active before showing client log (diff)
downloadslx-admin-8fa66377a1c97d42d83d91ef0821f37f1b885617.tar.gz
slx-admin-8fa66377a1c97d42d83d91ef0821f37f1b885617.tar.xz
slx-admin-8fa66377a1c97d42d83d91ef0821f37f1b885617.zip
[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.
Diffstat (limited to 'modules-available/statistics/install.inc.php')
-rw-r--r--modules-available/statistics/install.inc.php47
1 files changed, 46 insertions, 1 deletions
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)) {