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, `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` varchar(30) COLLATE utf8_unicode_ci, `pbs_poolID` int(11) NOT NULL, `pbs_personID` int(11) NOT NULL, `pbs_bootisoID` 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, PRIMARY KEY (`eventID`) ) 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, PRIMARY KEY (`reportID`), FOREIGN KEY (`eventID`) references poolctrl_event(eventID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5; -- 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_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_bootiso` ( `bootisoID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) NOT NULL, `description` varchar(140), `membershipID` int(11), `groupID` int(11) NOT NULL, `prebootID` int(11) NOT NULL, `serialnumber` varchar(30) NOT NULL UNIQUE, `created` VARCHAR(14) NOT NULL, `expires` VARCHAR(14), `public` int(11) NOT NULL, PRIMARY KEY (`bootisoID`), KEY `membershipID` (`membershipID`), KEY `groupID` (`groupID`) ) 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, `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_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_poolentries` ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) 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_personidC` FOREIGN KEY (`pbs_personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_boolisoidC` FOREIGN KEY (`pbs_bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()), ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);