diff options
Diffstat (limited to 'modules-available/statistics/install.inc.php')
| -rw-r--r-- | modules-available/statistics/install.inc.php | 84 |
1 files changed, 76 insertions, 8 deletions
diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php index 3becce8f..bc8a5c91 100644 --- a/modules-available/statistics/install.inc.php +++ b/modules-available/statistics/install.inc.php @@ -11,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`), @@ -40,8 +40,10 @@ $res[] = 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, @@ -64,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`), @@ -74,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`) "); @@ -248,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) { @@ -299,5 +303,69 @@ if (!tableHasColumn('machine', 'live_id45size')) { $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); |