diff options
Diffstat (limited to 'modules-available/statistics/install.inc.php')
-rw-r--r-- | modules-available/statistics/install.inc.php | 112 |
1 files changed, 91 insertions, 21 deletions
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php index e8788224..bc8a5c91 100644 --- a/modules-available/statistics/install.inc.php +++ b/modules-available/statistics/install.inc.php @@ -1,8 +1,5 @@ <?php -// locationid trigger -$addTrigger = false; - $res = array(); // The main statistic table used for log entries @@ -14,7 +11,7 @@ $res[] = tableCreate('statistic', " `clientip` varchar(40) NOT NULL, `machineuuid` char(36) CHARACTER SET ascii DEFAULT NULL, `username` varchar(30) NOT NULL, - `data` varchar(255) NOT NULL, + `data` BLOB NOT NULL, PRIMARY KEY (`logid`), KEY `dateline` (`dateline`), KEY `logtypeid` (`typeid`,`dateline`), @@ -24,7 +21,7 @@ $res[] = tableCreate('statistic', " // Main table containing all known clients -$res[] = $machineCreate = tableCreate('machine', " +$res[] = tableCreate('machine', " `machineuuid` char(36) CHARACTER SET ascii NOT 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)', @@ -43,8 +40,10 @@ $res[] = $machineCreate = tableCreate('machine', " `cpumodel` varchar(120) NOT NULL, `systemmodel` varchar(120) NOT NULL DEFAULT '', `id44mb` int(10) unsigned NOT NULL, + `id45mb` int(10) unsigned NOT NULL DEFAULT 0, `badsectors` int(10) unsigned NOT NULL, - `data` mediumtext NOT NULL, + `data` mediumblob NOT NULL, + `dataparsetime` int(10) unsigned NOT NULL DEFAULT 0, `hostname` varchar(200) NOT NULL DEFAULT '', `currentsession` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `currentuser` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, @@ -67,7 +66,7 @@ $res[] = $machineHwCreate = tableCreate('machine_x_hw', " `machinehwid` int(10) unsigned NOT NULL AUTO_INCREMENT, `hwid` int(10) unsigned NOT NULL, `machineuuid` char(36) CHARACTER SET ascii NOT NULL, - `devpath` char(50) CHARACTER SET ascii NOT NULL, + `devpath` char(32) CHARACTER SET ascii NOT NULL, `disconnecttime` int(10) unsigned NOT NULL COMMENT 'time the device was not connected to the pc anymore for the first time, 0 if it is connected', PRIMARY KEY (`machinehwid`), UNIQUE KEY `hwid` (`hwid`,`machineuuid`,`devpath`), @@ -77,23 +76,25 @@ $res[] = $machineHwCreate = tableCreate('machine_x_hw', " $res[] = tableCreate('machine_x_hw_prop', " `machinehwid` int(10) unsigned NOT NULL, - `prop` char(16) CHARACTER SET ascii NOT NULL, + `prop` varchar(64) CHARACTER SET ascii NOT NULL, `value` varchar(500) NOT NULL, + `numeric` bigint(20) DEFAULT NULL, PRIMARY KEY (`machinehwid`,`prop`) "); $res[] = tableCreate('statistic_hw', " `hwid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `hwtype` char(11) CHARACTER SET ascii NOT NULL, - `hwname` varchar(200) NOT NULL, + `hwtype` char(16) CHARACTER SET ascii NOT NULL, + `hwname` char(32) CHARACTER SET ascii NOT NULL, PRIMARY KEY (`hwid`), UNIQUE KEY `hwtype` (`hwtype`,`hwname`) "); $res[] = tableCreate('statistic_hw_prop', " `hwid` int(10) unsigned NOT NULL, - `prop` char(16) CHARACTER SET ascii NOT NULL, + `prop` varchar(64) CHARACTER SET ascii NOT NULL, `value` varchar(500) NOT NULL, + `numeric` bigint(20) DEFAULT NULL, PRIMARY KEY (`hwid`,`prop`) "); @@ -117,11 +118,6 @@ $res[] = tableCreate('setting_machine', ' KEY `setting` (`setting`) '); -// need trigger? -if ($machineCreate === UPDATE_DONE) { - $addTrigger = true; -} - // // This was added/changed later -- keep update path // @@ -177,7 +173,6 @@ if (!tableHasColumn('machine', 'subnetlocationid')) { 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')) { @@ -189,10 +184,10 @@ if (!tableHasColumn('machine', 'fixedlocationid')) { // 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) { + +$checkTrigger = Database::queryFirst("show triggers where `Trigger` = 'set_automatic_locationid'"); +if ($checkTrigger === false) { $ret = Database::exec(" CREATE TRIGGER set_automatic_locationid BEFORE UPDATE ON machine FOR EACH ROW @@ -257,7 +252,7 @@ if (!tableHasColumn('machine', 'live_tmpsize')) { // 2019-02-20: Convert bogus UUIDs $res2 = Database::simpleQuery("SELECT machineuuid, macaddr FROM machine WHERE machineuuid LIKE '00000000000000_-%'"); -while ($row = $res2->fetch(PDO::FETCH_ASSOC)) { +foreach ($res2 as $row) { $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) { @@ -297,5 +292,80 @@ if (!tableHasColumn('machine', 'currentrunmode')) { $res[] = UPDATE_DONE; } +// 2019-01-25: Add memory/temp stats column +if (!tableHasColumn('machine', 'live_id45size')) { + $ret = Database::exec("ALTER TABLE `machine` + ADD COLUMN `live_id45size` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_tmpfree`, + ADD COLUMN `live_id45free` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_id45size`"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Adding mem-stat columns to machine table failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} + +// 2021-08-19 Enhanced machine property indexing +if (stripos(tableColumnType('statistic_hw_prop', 'prop'), 'varchar(64)') === false) { + Database::exec("DELETE FROM statistic_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'"); + $ret = Database::exec("ALTER TABLE statistic_hw_prop + MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL, + ADD `numeric` bigint(20) DEFAULT NULL"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Changing prop of statistic_hw_prop failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} +if (stripos(tableColumnType('machine_x_hw_prop', 'prop'), 'varchar(64)') === false) { + Database::exec("DELETE FROM machine_x_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'"); + $ret = Database::exec("ALTER TABLE machine_x_hw_prop + MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL, + ADD `numeric` bigint(20) DEFAULT NULL"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Changing prop of machine_x_hw_prop failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} +if (stripos(tableColumnType('statistic_hw', 'hwname'), 'char(32)') === false) { + Database::exec("DELETE FROM statistic_hw WHERE hwname NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'"); + $ret = Database::exec("ALTER TABLE statistic_hw MODIFY `hwname` char(32) CHARACTER SET ascii NOT NULL, + MODIFY `hwtype` char(16) CHARACTER SET ascii NOT NULL"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Changing hwname/hwtype of statistic_hw failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} +if (stripos(tableColumnType('machine_x_hw', 'devpath'), 'char(32)') === false) { + Database::exec("DELETE FROM machine_x_hw WHERE devpath NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'"); + $ret = Database::exec("ALTER TABLE machine_x_hw MODIFY `devpath` char(32) CHARACTER SET ascii NOT NULL"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Changing devpath of machine_x_hw failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} +if (!tableHasColumn('machine', 'dataparsetime')) { + $ret = Database::exec("ALTER TABLE `machine` + ADD COLUMN `dataparsetime` int(10) unsigned NOT NULL DEFAULT '0' AFTER `data`"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Adding dateparsetime column to machine table failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} +if (!tableHasColumn('machine', 'id45mb')) { + $ret = Database::exec("ALTER TABLE `machine` + ADD COLUMN `id45mb` int(10) unsigned NOT NULL DEFAULT 0 AFTER `id44mb`"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Adding id45mb column to machine table failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} + +// 2022-11-22 Change data column of statistic table from varchar(255) to blob +if (stripos(tableColumnType('statistic', 'data'), 'blob') === false) { + $ret = Database::exec("ALTER TABLE `statistic` MODIFY COLUMN `data` BLOB NOT NULL"); + if ($ret === false) { + finalResponse(UPDATE_FAILED, 'Changing statistic.data to blob failed: ' . Database::lastError()); + } + $res[] = UPDATE_DONE; +} + // Create response responseFromArray($res); |