<?php
$res = array();
$res[] = tableCreate('eventlog', "
`logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dateline` int(10) unsigned NOT NULL,
`logtypeid` varchar(30) NOT NULL,
`description` varchar(255) NOT NULL,
`extra` TEXT NOT NULL,
PRIMARY KEY (`logid`),
KEY `dateline` (`dateline`),
KEY `logtypeid` (`logtypeid`,`dateline`)
");
$res[] = tableCreate('notification_rule', '
`ruleid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`description` text,
`type` varchar(40) CHARACTER SET ascii NOT NULL,
`datafilter` blob NOT NULL,
`subject` varchar(200) NOT NULL,
`message` text NOT NULL,
`predefid` int(10) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`ruleid`),
KEY `type` (`type`),
UNIQUE KEY `predefid` (`predefid`)
');
$res[] = tableCreate('notification_backend', '
`transportid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`description` text,
`data` blob,
PRIMARY KEY (`transportid`),
KEY (`title`)
');
$res[] = tableCreate('notification_rule_x_transport', '
`ruleid` int(10) UNSIGNED NOT NULL,
`transportid` int(10) UNSIGNED NOT NULL,
PRIMARY KEY (`ruleid`, `transportid`),
KEY (`transportid`)
');
$res[] = tableCreate('notification_sample', "
`sampleid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`dateline` int(10) UNSIGNED NOT NULL,
`extended` tinyint(1) NOT NULL DEFAULT '0',
`type` varchar(40) CHARACTER SET ascii NOT NULL,
`data` mediumblob,
KEY (`type`, `dateline`),
KEY (`extended`),
PRIMARY KEY (`sampleid`)
");
// Update path
if (!tableHasColumn('eventlog', 'extra')) {
if (Database::exec("ALTER TABLE `eventlog` ADD `extra` TEXT NOT NULL") === false) {
finalResponse(UPDATE_FAILED, 'Could not add extra to eventlog: ' . Database::lastError());
}
$res[] = UPDATE_DONE;
}
// 2021-06-15: Add constraints to filter/backend stuff
$res[] = tableAddConstraint('notification_rule_x_transport', 'ruleid',
'notification_rule', 'ruleid', 'ON UPDATE CASCADE ON DELETE CASCADE');
$res[] = tableAddConstraint('notification_rule_x_transport', 'transportid',
'notification_backend', 'transportid', 'ON UPDATE CASCADE ON DELETE CASCADE');
// 2022-07-20: Add flag to see if notification_sample has been extended
if (!tableHasColumn('notification_sample', 'extended')) {
if (Database::exec("ALTER TABLE notification_sample
ADD COLUMN `extended` tinyint(1) NOT NULL DEFAULT '0',
ADD KEY (`extended`),
ADD COLUMN `sampleid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`sampleid`)") === false) {
finalResponse(UPDATE_FAILED, 'Could not add extended flag to notification_sample: ' . Database::lastError());
}
$res[] = UPDATE_DONE;
}
// 2023-08-03: Add a few example filters
if (!tableHasColumn('notification_rule', 'predefid')) {
if (Database::exec("ALTER TABLE notification_rule
ADD COLUMN `predefid` int(10) UNSIGNED NULL DEFAULT NULL,
ADD UNIQUE KEY `predefid` (`predefid`)") === false) {
finalResponse(UPDATE_FAILED, 'Could not add predefid to notification_rule: ' . Database::lastError());
}
$res[] = UPDATE_DONE;
}
$q = "INSERT IGNORE INTO `notification_rule`
(predefid, title, `description`, type, datafilter, subject, message)
VALUES
(1,'Session start','Benachrichtigung über jede gestartete Session','.vmchooser-session','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"currentuser\",\"op\":\"*\",\"index\":2},{\"path\":\"sessionName\",\"op\":\"*\",\"index\":3}]}','Sitzungsstart','Client %0% - User %2% startet Veranstaltung %3%'),
(2,'PowerON: bwlp-Default','Kein spezieller Betriebsmodus','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"currentrunmode\",\"op\":\"=\",\"arg\":\"\",\"index\":1},{\"path\":\"locationid\",\"op\":\"*\",\"index\":2}]}','PowerON-Event','Client %0% in Raum %2L% startet im Modus \'Standard\''),
(3,'Serverlog','Jegliche neue Einträge im Server-Log','#serverlog','{\"list\":[{\"path\":\"type\",\"op\":\"*\",\"index\":0},{\"path\":\"message\",\"op\":\"*\",\"index\":1},{\"path\":\"details\",\"op\":\"*\",\"index\":2}]}','','[%0%] - %1% - %2%'),
(4,'PowerON: Exammode','Rechner startet im Klausurmodus','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"currentrunmode\",\"op\":\"=\",\"arg\":\"exams\",\"index\":1}]}','','Client %0% startet im Modus \'Prüfung\''),
(5,'PowerON: Remoteaccess','Rechner startet im Fernzugriffsmodus','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"currentrunmode\",\"op\":\"=\",\"arg\":\"remoteaccess\",\"index\":1}]}','','Client %0% startet im Modus \'Fernzugriff\''),
(6,'NIC: Slow Uplink','Rechner ist mit weniger als 1GBit/s mit dem Switch verbunden','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"nic_speed\",\"op\":\"<\",\"arg\":\"1000\",\"index\":1}]}','','Client %0% hat einen Uplink von %1% MBit/s'),
(7,'First boot of new Client','Neuer Client wurde gestartet, der dem Server bisher nicht bekannt war','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"hostname\",\"op\":\"*\",\"index\":1},{\"path\":\"oldlastseen\",\"op\":\"=\",\"arg\":\"0\",\"index\":2}]}','','Neuer Client %0% (%1%) bootet bwLehrpool'),
(8,'Rechner mit Platte, ohne ID44','Besagter Rechner hat zwar eine HDD/SSD verbaut, aber keine ID44-Partition','~poweron','{\"list\":[{\"path\":\"clientip\",\"op\":\"*\",\"index\":0},{\"path\":\"hdd_size\",\"op\":\">\",\"arg\":\"0\",\"index\":1},{\"path\":\"id44mb\",\"op\":\"=\",\"arg\":\"0\",\"index\":2},{\"path\":\"hostname\",\"op\":\"*\",\"index\":3}]}','Hallo','%3% (%0%) hat HDD %1b%, keine ID44 Partition'),
(9,'Lahmes Netzwerk, keine ID45','Rechner ist mit weniger als 1GBit/s mit dem Switch verbunden, und besitzt keine ID45-Partition','~poweron','{\"list\":[{\"path\":\"id45mb\",\"op\":\"=\",\"arg\":\"0\",\"index\":0},{\"path\":\"nic_speed\",\"op\":\"<\",\"arg\":\"1000\",\"index\":1},{\"path\":\"hostname\",\"op\":\"*\",\"index\":2},{\"path\":\"nic_speed\",\"op\":\">\",\"arg\":\"0\",\"index\":3},{\"path\":\"clientip\",\"op\":\"*\",\"index\":4}]}','Lahm','%2% (%4%) hat %1%MBit und keine ID45'),
(10,'Drehende Platte','Rechner hat eine Rotierende HDD und keine SSD.','~poweron','{\"list\":[{\"path\":\"hdd_rpm\",\"op\":\">\",\"arg\":\"0\",\"index\":0},{\"path\":\"clientip\",\"op\":\"*\",\"index\":1},{\"path\":\"hostname\",\"op\":\"*\",\"index\":2}]}','Da dreht was im Rechner...','Rechner %2% (%1%) hat drehende Platte (%0% RPM)')";
Database::exec($q);
// Create response for browser
if (in_array(UPDATE_DONE, $res)) {
finalResponse(UPDATE_DONE, 'Tables created successfully');
}
finalResponse(UPDATE_NOOP, 'Everything already up to date');
