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, `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(), `end` timestamp COLLATE utf8_unicode_ci NULL, `participants` int(4) DEFAULT NULL, `note` varchar(140) COLLATE utf8_unicode_ci DEFAULT NULL, `category` int(11) NOT NULL, `pbs_poolID` int(11) NOT NULL, `pbs_membershipID` int(11) NOT NULL, `pbs_bootosID` int(11) DEFAULT NULL, `pbs_configID` int(11) DEFAULT NULL, `pbs_bootmenuID` int(11) DEFAULT NULL, `pbs_filterID` int(11) DEFAULT NULL, `force` bool NOT NULL DEFAULT false, `repeat` bool NOT NULL DEFAULT false, `repeattype` int(11) NULL, `repeatend` int(11) NULL, `repeatdate` timestamp COLLATE utf8_unicode_ci NULL, `repeatings` int(4) NULL, `immediate` bool NOT NULL DEFAULT false, `running` bool NOT NULL DEFAULT false, `runningtype` int(11) DEFAULT NULL, `created` varchar(14) COLLATE utf8_unicode_ci NULL, PRIMARY KEY (`eventID`), KEY `pbs_poolID` (`pbs_poolID`), KEY `pbs_membershipID` (`pbs_membershipID`), KEY `pbs_bootosID` (`pbs_bootosID`), KEY `pbs_configID` (`pbs_configID`), KEY `pbs_bootmenuID` (`pbs_bootmenuID`), KEY `pbs_filterID` (`pbs_filterID`), KEY `category` (`category`), KEY `runningtype` (`runningtype`), KEY `repeattype` (`repeattype`), KEY `repeatend` (`repeatend`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_runningtype` ( `runningtypeID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`runningtypeID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_repeattype` ( `repeattypeID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`repeattypeID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; CREATE TABLE IF NOT EXISTS `poolctrl_repeatend` ( `repeatendID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`repeatendID`) ) 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, `color` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, PRIMARY KEY (`eventcategoryID`) ) 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, `result` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'successful', `errors` text COLLATE utf8_unicode_ci, `type` int(11) NOT NULL, `eventID` int(11) NOT NULL, `created` varchar(14) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`reportID`), KEY `eventID` (`eventID`), KEY `type` (`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; -- Constraints ALTER TABLE `poolctrl_eventreport` ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`eventID`) REFERENCES `poolctrl_event` (`eventID`) ON DELETE CASCADE, ADD CONSTRAINT `pbs_eventreport_typeidC` FOREIGN KEY (`type`) REFERENCES `poolctrl_runningtype` (`runningtypeID`) ON DELETE CASCADE; ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_categoryC` FOREIGN KEY (`category`) REFERENCES `poolctrl_eventcategory` (`eventcategoryID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_repeattypeC` FOREIGN KEY (`repeattype`) REFERENCES `poolctrl_repeattype` (`repeattypeID`) ON DELETE SET NULL, ADD CONSTRAINT `poolctrl_event_runningtypeC` FOREIGN KEY (`runningtype`) REFERENCES `poolctrl_runningtype` (`runningtypeID`) ON DELETE SET NULL, ADD CONSTRAINT `poolctrl_event_repeatendC` FOREIGN KEY (`repeatend`) REFERENCES `poolctrl_repeatend` (`repeatendID`) ON DELETE SET NULL, ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()), ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);