CREATE DATABASE `##poolctrl##` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; USE ##poolctrl##; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- PoolCtrl Tabellen CREATE TABLE IF NOT EXISTS `poolctrl_event` ( `eventID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, `action` int(11) NOT NULL DEFAULT '1', `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(), `end` timestamp COLLATE utf8_unicode_ci NOT NULL, `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0), `category` int(11) NOT NULL, `pbs_poolID` int(11) NOT NULL, `pbs_membershipID` int(11) NOT NULL, `pbs_bootosID` int(11) NOT NULL, `repeat` bool NOT NULL DEFAULT false, `immediate` bool NOT NULL DEFAULT false, `note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL, `pbs_bootmenuID` int(11) NOT NULL, `pbs_filterID` int(11) NOT NULL, PRIMARY KEY (`eventID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_eventaction` ( `eventactionID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`eventactionID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` ( `reportID` int(11) NOT NULL AUTO_INCREMENT, `report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success', `eventID` int(11) NOT NULL, `timestamp` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(), PRIMARY KEY (`reportID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_eventcategory` ( `eventcategoryID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`eventcategoryID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; -- PBS2 Tabellen -- TO DO: Tabellen an dieser Stelle nicht per Hand erzeugen sondern von der PBS2-Datenbank kopieren, sodass diese auf dem neusten Stand sind CREATE TABLE IF NOT EXISTS `pbs_filter` ( `filterID` int(11) NOT NULL AUTO_INCREMENT, `membershipID` int(11), `groupID` int(11), `bootmenuID` int(11) NOT NULL, `title` varchar(30) NOT NULL, `description` varchar(140), `created` varchar(14) NOT NULL, `priority` int(11) NOT NULL, PRIMARY KEY (`filterID`), KEY `membershipID` (`membershipID`), KEY `groupID` (`groupID`), KEY `bootmenuID` (`bootmenuID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_bootmenu` ( `bootmenuID` int(11) NOT NULL AUTO_INCREMENT, `membershipID` int(11), `groupID` int(11), `title` varchar(50) NOT NULL, `created` varchar(14) NOT NULL, `defaultbootmenu` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`bootmenuID`), KEY `groupID` (`groupID`), KEY `membershipID` (`membershipID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_person` ( `personID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci, `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `firstname` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `street` varchar(30) COLLATE utf8_unicode_ci, `housenumber` varchar(30) COLLATE utf8_unicode_ci, `city` varchar(30) COLLATE utf8_unicode_ci, `postalcode` varchar(30) COLLATE utf8_unicode_ci, `logindate` varchar(14) COLLATE utf8_unicode_ci, `registerdate` varchar(14) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, `login` varchar(30) COLLATE utf8_unicode_ci, `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `password_salt` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `loginpassword` varchar(512) COLLATE utf8_unicode_ci NOT NULL, `suspend` tinyint(1) NOT NULL, PRIMARY KEY (`personID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `pbs_bootos` ( `bootosID` int(11) NOT NULL AUTO_INCREMENT, `groupID` int(11) NOT NULL, `membershipID` int(11), `title` varchar(30) NOT NULL, `description` varchar(140), `distro` varchar(30), `distroversion` varchar(30), `source` varchar(140), `share` varchar(30), `shortname` varchar(30), `defaultkcl` varchar(255), `created` VARCHAR(14) NOT NULL, `expires` VARCHAR(14), `public` int(11) NOT NULL DEFAULT '-1', PRIMARY KEY (`bootosID`), KEY `groupID` (`groupID`), KEY `membershipID` (`membershipID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_config` ( `configID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) NOT NULL, `description` varchar(140), `groupID` int(11), `membershipID` int(11), `visible` tinyint(1) DEFAULT '0', `created` VARCHAR(14) NOT NULL, `bootosID` int(11) NOT NULL, PRIMARY KEY (`configID`), KEY `groupID` (`groupID`), KEY `membershipID` (`membershipID`), KEY `bootosID` (`bootosID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_group` ( `groupID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, `description` varchar(140) COLLATE utf8_unicode_ci, PRIMARY KEY (`groupID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_groupgroups` ( `parentID` int(11) NOT NULL, `groupID` int(11) NOT NULL, PRIMARY KEY (`parentID`,`groupID`), KEY `parentID` (`parentID`), KEY `groupID` (`groupID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_membership` ( `membershipID` int(11) NOT NULL AUTO_INCREMENT, `groupID` int(11) NOT NULL, `personID` int(11) NOT NULL, `suspend` tinyint(1) NOT NULL, `apikey` varchar(30), PRIMARY KEY (`membershipID`), KEY `groupID` (`groupID`), KEY `personID` (`personID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `pbs_pool` ( `poolID` int(11) NOT NULL AUTO_INCREMENT, `groupID` int(11) NOT NULL, `title` varchar(30) NOT NULL, `description` varchar(140), `location` varchar(30), PRIMARY KEY (`poolID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `pbs_client` ( `clientID` int(11) NOT NULL AUTO_INCREMENT, `macadress` varchar(17) NOT NULL, `hardwarehash` varchar(32), `created` int(15), PRIMARY KEY (`clientID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; ALTER TABLE `pbs_client` ADD `groupID` INT NOT NULL AFTER `clientID` ; ALTER TABLE `pbs_client` ADD UNIQUE (`groupID` ,`macadress`); CREATE TABLE IF NOT EXISTS `pbs_poolentries` ( `poolentriesID` INT NOT NULL AUTO_INCREMENT, `poolID` int(11) NOT NULL, `clientID` int(11) NOT NULL, PRIMARY KEY (`poolentriesID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; -- Constraints ALTER TABLE `pbs_filter` ADD CONSTRAINT `pbs_filter_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`), ADD CONSTRAINT `pbs_filter_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_filter_ibfk_3` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE; ALTER TABLE `pbs_bootmenu` ADD `startcounter` INT NOT NULL AFTER `title`; ALTER TABLE `pbs_bootmenu` CHANGE `startcounter` `startcounter` INT( 11 ) NOT NULL DEFAULT '300'; ALTER TABLE `pbs_config` ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_config_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_config_ibfk_3` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE; ALTER TABLE `pbs_groupgroups` ADD CONSTRAINT `pbs_groupgroups_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_groupgroups_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; ALTER TABLE `pbs_membership` ADD CONSTRAINT `pbs_membership_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_membership_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE; ALTER TABLE `pbs_bootos` ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL; ALTER TABLE `pbs_client` ADD CONSTRAINT `pbs_client_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; ALTER TABLE `pbs_pool` ADD CONSTRAINT `pbs_pool_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; ALTER TABLE `pbs_poolentries` ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE; ALTER TABLE `poolctrl_eventreport` ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`eventID`) REFERENCES `poolctrl_event` (`eventID`) ON DELETE CASCADE; ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_membershipC` FOREIGN KEY (`pbs_membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_bootosidC` FOREIGN KEY (`pbs_bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE, -- ADD CONSTRAINT `poolctrl_event_bootmenuidC` FOREIGN KEY (`pbs_bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE, -- ADD CONSTRAINT `poolctrl_event_filteridC` FOREIGN KEY (`pbs_filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_categoryC` FOREIGN KEY (`category`) REFERENCES `poolctrl_eventcategory` (`eventcategoryID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_actionC` FOREIGN KEY (`action`) REFERENCES `poolctrl_eventaction` (`eventactionID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()), ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);