From 203a8fa7acc12bfc3debf6337362f3fb7900bab9 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Mon, 21 Dec 2015 18:00:38 +0100 Subject: [statistics] Add table definition, bump db version --- install/content.sql | 2 +- install/schema.sql | 359 ++++++++++++++++++++++++++++------------------------ 2 files changed, 193 insertions(+), 168 deletions(-) (limited to 'install') diff --git a/install/content.sql b/install/content.sql index d5c5f2ac..bf54a711 100644 --- a/install/content.sql +++ b/install/content.sql @@ -1,2 +1,2 @@ -INSERT INTO property (name, dateline, value) VALUES ('webif-version', 0, 10); +INSERT INTO property (name, dateline, value) VALUES ('webif-version', 0, 11); diff --git a/install/schema.sql b/install/schema.sql index 2cc68861..c4ca99c5 100644 --- a/install/schema.sql +++ b/install/schema.sql @@ -1,167 +1,192 @@ -SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; -SET time_zone = "+00:00"; - -/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; -/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; -/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; -/*!40101 SET NAMES utf8 */; - - -CREATE TABLE `callback` ( - `taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `dateline` int(10) unsigned NOT NULL, - `cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, - `args` text NOT NULL, - PRIMARY KEY (`taskid`,`cbfunction`), - KEY `dateline` (`dateline`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `cat_setting` ( - `catid` int(10) unsigned NOT NULL, - `sortval` smallint(5) unsigned NOT NULL, - PRIMARY KEY (`catid`), - KEY `sortval` (`sortval`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `clientlog` ( - `logid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `dateline` int(10) unsigned NOT NULL, - `logtypeid` varchar(30) NOT NULL, - `clientip` varchar(40) NOT NULL, - `description` varchar(255) NOT NULL, - `extra` text NOT NULL, - PRIMARY KEY (`logid`), - KEY `dateline` (`dateline`), - KEY `logtypeid` (`logtypeid`,`dateline`), - KEY `clientip` (`clientip`,`dateline`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `configtgz` ( - `configid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) NOT NULL, - `filepath` varchar(255) NOT NULL, - `status` enum('OK','OUTDATED','MISSING') NOT NULL DEFAULT 'MISSING', - PRIMARY KEY (`configid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `configtgz_module` ( - `moduleid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `title` varchar(200) NOT NULL, - `moduletype` varchar(16) NOT NULL, - `filepath` varchar(250) NOT NULL, - `contents` text NOT NULL, - `version` int(10) unsigned NOT NULL DEFAULT '0', - `status` enum('OK','MISSING','OUTDATED') NOT NULL DEFAULT 'MISSING', - PRIMARY KEY (`moduleid`), - KEY `title` (`title`), - KEY `moduletype` (`moduletype`,`title`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `configtgz_x_module` ( - `configid` int(10) unsigned NOT NULL, - `moduleid` int(10) unsigned NOT NULL, - PRIMARY KEY (`configid`,`moduleid`), - KEY `moduleid` (`moduleid`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `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`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `news` ( - `newsid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `dateline` int(10) unsigned NOT NULL, - `title` varchar(200) DEFAULT NULL, - `content` text, - PRIMARY KEY (`newsid`), - KEY `dateline` (`dateline`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `permission` ( - `mask` int(10) unsigned NOT NULL, - `identifier` varchar(32) NOT NULL, - PRIMARY KEY (`mask`), - UNIQUE KEY `identifier` (`identifier`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `property` ( - `name` varchar(50) NOT NULL, - `dateline` int(10) unsigned NOT NULL DEFAULT '0', - `value` text NOT NULL, - PRIMARY KEY (`name`), - KEY `dateline` (`dateline`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `setting` ( - `setting` varchar(28) NOT NULL, - `catid` int(10) unsigned NOT NULL, - `defaultvalue` text NOT NULL, - `permissions` int(10) unsigned NOT NULL, - `validator` varchar(250) NOT NULL DEFAULT '', - PRIMARY KEY (`setting`), - KEY `catid` (`catid`,`setting`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `setting_distro` ( - `distroid` int(10) unsigned NOT NULL, - `setting` varchar(28) NOT NULL, - `value` text NOT NULL, - `displayvalue` text NOT NULL, - PRIMARY KEY (`distroid`,`setting`), - KEY `setting` (`setting`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `setting_global` ( - `setting` varchar(28) NOT NULL, - `value` text NOT NULL, - `displayvalue` text NOT NULL, - PRIMARY KEY (`setting`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `statistic` ( - `logid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `dateline` int(10) unsigned NOT NULL, - `typeid` varchar(30) NOT NULL, - `clientip` varchar(40) NOT NULL, - `username` varchar(255) NOT NULL, - `data` varchar(255) NOT NULL, - PRIMARY KEY (`logid`), - KEY `dateline` (`dateline`), - KEY `logtypeid` (`typeid`,`dateline`), - KEY `clientip` (`clientip`,`dateline`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - -CREATE TABLE `user` ( - `userid` int(10) unsigned NOT NULL AUTO_INCREMENT, - `login` varchar(100) NOT NULL, - `passwd` varchar(150) NOT NULL, - `fullname` varchar(100) DEFAULT NULL, - `phone` varchar(100) DEFAULT NULL, - `email` varchar(100) DEFAULT NULL, - `permissions` int(10) unsigned NOT NULL, - `lasteventid` int(10) unsigned NOT NULL DEFAULT '0', - PRIMARY KEY (`userid`), - UNIQUE KEY `login` (`login`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; - - -ALTER TABLE `configtgz_x_module` - ADD CONSTRAINT `configtgz_x_module_ibfk_1` FOREIGN KEY (`configid`) REFERENCES `configtgz` (`configid`) ON DELETE CASCADE, - ADD CONSTRAINT `configtgz_x_module_ibfk_2` FOREIGN KEY (`moduleid`) REFERENCES `configtgz_module` (`moduleid`); - -ALTER TABLE `setting` - ADD CONSTRAINT `setting_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `cat_setting` (`catid`) ON UPDATE CASCADE; - -ALTER TABLE `setting_distro` - ADD CONSTRAINT `setting_distro_ibfk_1` FOREIGN KEY (`setting`) REFERENCES `setting` (`setting`) ON DELETE CASCADE ON UPDATE CASCADE; - -/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; -/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; -/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; +SET time_zone = "+00:00"; + +CREATE TABLE `callback` ( + `taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `dateline` int(10) unsigned NOT NULL, + `cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, + `args` text NOT NULL, + PRIMARY KEY (`taskid`,`cbfunction`), + KEY `dateline` (`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `cat_setting` ( + `catid` int(10) unsigned NOT NULL, + `sortval` smallint(5) unsigned NOT NULL, + PRIMARY KEY (`catid`), + KEY `sortval` (`sortval`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `clientlog` ( + `logid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `dateline` int(10) unsigned NOT NULL, + `logtypeid` varchar(30) NOT NULL, + `clientip` varchar(40) NOT NULL, + `description` varchar(255) NOT NULL, + `extra` text NOT NULL, + PRIMARY KEY (`logid`), + KEY `dateline` (`dateline`), + KEY `logtypeid` (`logtypeid`,`dateline`), + KEY `clientip` (`clientip`,`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `configtgz` ( + `configid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `title` varchar(200) NOT NULL, + `filepath` varchar(255) NOT NULL, + `status` enum('OK','OUTDATED','MISSING') NOT NULL DEFAULT 'MISSING', + PRIMARY KEY (`configid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `configtgz_module` ( + `moduleid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `title` varchar(200) NOT NULL, + `moduletype` varchar(16) NOT NULL, + `filepath` varchar(250) NOT NULL, + `contents` text NOT NULL, + `version` int(10) unsigned NOT NULL DEFAULT '0', + `status` enum('OK','MISSING','OUTDATED') NOT NULL DEFAULT 'MISSING', + PRIMARY KEY (`moduleid`), + KEY `title` (`title`), + KEY `moduletype` (`moduletype`,`title`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `configtgz_x_module` ( + `configid` int(10) unsigned NOT NULL, + `moduleid` int(10) unsigned NOT NULL, + PRIMARY KEY (`configid`,`moduleid`), + KEY `moduleid` (`moduleid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `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`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `machine` ( + `machineuuid` char(36) CHARACTER SET ascii NOT NULL, + `roomid` int(10) unsigned DEFAULT NULL, + `macaddr` char(17) CHARACTER SET ascii NOT NULL, + `clientip` varchar(45) CHARACTER SET ascii NOT NULL, + `firstseen` int(10) unsigned NOT NULL, + `lastseen` int(10) unsigned NOT NULL, + `logintime` int(10) unsigned NOT NULL, + `position` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, + `lastboot` int(10) unsigned NOT NULL, + `realcores` smallint(5) unsigned NOT NULL, + `mbram` int(10) unsigned NOT NULL, + `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL, + `cpumodel` varchar(120) NOT NULL, + `systemmodel` varchar(120) NOT NULL DEFAULT '', + `id44mb` int(10) unsigned NOT NULL, + `badsectors` int(10) unsigned NOT NULL, + `data` mediumtext NOT NULL, + `hostname` varchar(200) NOT NULL DEFAULT '', + `notes` text, + PRIMARY KEY (`machineuuid`), + KEY `macaddr` (`macaddr`), + KEY `clientip` (`clientip`), + KEY `realcores` (`realcores`), + KEY `mbram` (`mbram`), + KEY `kvmstate` (`kvmstate`), + KEY `id44mb` (`id44mb`), + KEY `roomid` (`roomid`), + KEY `lastseen` (`lastseen`), + KEY `cpumodel` (`cpumodel`), + KEY `systemmodel` (`systemmodel`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `news` ( + `newsid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `dateline` int(10) unsigned NOT NULL, + `title` varchar(200) DEFAULT NULL, + `content` text, + PRIMARY KEY (`newsid`), + KEY `dateline` (`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `permission` ( + `mask` int(10) unsigned NOT NULL, + `identifier` varchar(32) NOT NULL, + PRIMARY KEY (`mask`), + UNIQUE KEY `identifier` (`identifier`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `property` ( + `name` varchar(50) NOT NULL, + `dateline` int(10) unsigned NOT NULL DEFAULT '0', + `value` text NOT NULL, + PRIMARY KEY (`name`), + KEY `dateline` (`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `setting` ( + `setting` varchar(28) NOT NULL, + `catid` int(10) unsigned NOT NULL, + `defaultvalue` text NOT NULL, + `permissions` int(10) unsigned NOT NULL, + `validator` varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (`setting`), + KEY `catid` (`catid`,`setting`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `setting_distro` ( + `distroid` int(10) unsigned NOT NULL, + `setting` varchar(28) NOT NULL, + `value` text NOT NULL, + `displayvalue` text NOT NULL, + PRIMARY KEY (`distroid`,`setting`), + KEY `setting` (`setting`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `setting_global` ( + `setting` varchar(28) NOT NULL, + `value` text NOT NULL, + `displayvalue` text NOT NULL, + PRIMARY KEY (`setting`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `statistic` ( + `logid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `dateline` int(10) unsigned NOT NULL, + `typeid` varchar(30) NOT NULL, + `machineuuid` varchar(36) CHARACTER SET ascii DEFAULT NULL, + `clientip` varchar(40) NOT NULL, + `username` varchar(255) NOT NULL, + `data` varchar(255) NOT NULL, + PRIMARY KEY (`logid`), + KEY `dateline` (`dateline`), + KEY `logtypeid` (`typeid`,`dateline`), + KEY `clientip` (`clientip`,`dateline`), + KEY `machineuuid` (`machineuuid`,`dateline`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE `user` ( + `userid` int(10) unsigned NOT NULL AUTO_INCREMENT, + `login` varchar(100) NOT NULL, + `passwd` varchar(150) NOT NULL, + `fullname` varchar(100) DEFAULT NULL, + `phone` varchar(100) DEFAULT NULL, + `email` varchar(100) DEFAULT NULL, + `permissions` int(10) unsigned NOT NULL, + `lasteventid` int(10) unsigned NOT NULL DEFAULT '0', + PRIMARY KEY (`userid`), + UNIQUE KEY `login` (`login`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + + +ALTER TABLE `configtgz_x_module` + ADD CONSTRAINT `configtgz_x_module_ibfk_1` FOREIGN KEY (`configid`) REFERENCES `configtgz` (`configid`) ON DELETE CASCADE, + ADD CONSTRAINT `configtgz_x_module_ibfk_2` FOREIGN KEY (`moduleid`) REFERENCES `configtgz_module` (`moduleid`); + +ALTER TABLE `setting` + ADD CONSTRAINT `setting_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `cat_setting` (`catid`) ON UPDATE CASCADE; + +ALTER TABLE `setting_distro` + ADD CONSTRAINT `setting_distro_ibfk_1` FOREIGN KEY (`setting`) REFERENCES `setting` (`setting`) ON DELETE CASCADE ON UPDATE CASCADE; -- cgit v1.2.3-55-g7522