From 74d2e51da636f8ec981a0b004efb7e1f04b08e08 Mon Sep 17 00:00:00 2001 From: Björn Geiger Date: Fri, 19 Aug 2011 00:45:34 +0200 Subject: verschiedene Datenbankänderungen --- setup/poolctrl.sql | 23 ++++++++++++++++------- 1 file changed, 16 insertions(+), 7 deletions(-) (limited to 'setup/poolctrl.sql') diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql index e1badbd..96db9d7 100644 --- a/setup/poolctrl.sql +++ b/setup/poolctrl.sql @@ -10,6 +10,7 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` ( `start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(), `end` timestamp COLLATE utf8_unicode_ci NULL, `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0), + `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, @@ -18,7 +19,7 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` ( `pbs_filterID` 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, + `running` bool NOT NULL DEFAULT false, PRIMARY KEY (`eventID`), KEY `pbs_poolID` (`pbs_poolID`), KEY `pbs_membershipID` (`pbs_membershipID`), @@ -28,22 +29,29 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` ( KEY `category` (`category`) ) 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; + CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` ( `reportID` int(11) NOT NULL AUTO_INCREMENT, `report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success', + `type` int(11) NOT NULL, `eventID` int(11) NOT NULL, `timestamp` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(), PRIMARY KEY (`reportID`), - KEY `eventID` (`eventID`) + KEY `eventID` (`eventID`), + KEY `type` (`type`) ) 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, +CREATE TABLE IF NOT EXISTS `poolctrl_reporttype` ( + `reporttypeID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE, - PRIMARY KEY (`eventcategoryID`) + PRIMARY KEY (`reporttypeID`) ) 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_bootos` ( @@ -177,7 +185,8 @@ ALTER TABLE `pbs_membership` ADD CONSTRAINT `pbs_membership_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE; ALTER TABLE `poolctrl_eventreport` - ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`eventID`) REFERENCES `poolctrl_event` (`eventID`) ON DELETE CASCADE; + 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_reporttype` (`reporttypeID`) ON DELETE CASCADE; ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE, -- cgit v1.2.3-55-g7522