From 3c8f2a7c26ca065ef33e85661d3e9e299b9aeb3f Mon Sep 17 00:00:00 2001 From: Björn Geiger Date: Wed, 29 Jun 2011 17:04:33 +0200 Subject: Eventcategory nun in eigener Tabelle --- setup/poolctrl.sql | 14 ++++++++++---- setup/poolctrl_data.sql | 13 ++++++++++--- 2 files changed, 20 insertions(+), 7 deletions(-) (limited to 'setup') diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql index c861bdf..9be8e3e 100644 --- a/setup/poolctrl.sql +++ b/setup/poolctrl.sql @@ -10,7 +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 NOT NULL, `participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0), - `category` varchar(30) COLLATE utf8_unicode_ci, + `category` int(11) NOT NULL, `pbs_poolID` int(11) NOT NULL, `pbs_membershipID` int(11) NOT NULL, `pbs_bootosID` int(11) NOT NULL, @@ -25,9 +25,14 @@ CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` ( `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`), - FOREIGN KEY (`eventID`) references poolctrl_event(eventID) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5; + 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 @@ -174,5 +179,6 @@ 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_boolisoidC` FOREIGN KEY (`pbs_bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE, + ADD CONSTRAINT `poolctrl_event_categoryC` FOREIGN KEY (`category`) REFERENCES `poolctrl_eventcategory` (`eventcategoryID`) ON DELETE CASCADE, ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()), ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start); diff --git a/setup/poolctrl_data.sql b/setup/poolctrl_data.sql index 9967aee..dd08c97 100644 --- a/setup/poolctrl_data.sql +++ b/setup/poolctrl_data.sql @@ -99,11 +99,18 @@ INSERT INTO `pbs_poolentries` (`poolentriesID`, `poolID`, `clientID`) VALUES (9, 4, 9), (10, 4, 10); +-- Adding eventcategories +INSERT INTO `poolctrl_eventcategory` (`eventcategoryID`, `title`) VALUES +(1, 'Lecture'), +(2, 'Private'), +(3, 'Public'), +(4, 'Maintenance'); + -- Adding events INSERT INTO `poolctrl_event` (`eventID`, `title`, `start`, `end`, `category`, `pbs_poolID`, `pbs_membershipID`, `pbs_bootosID`, `note`) VALUES -(1, 'Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', 'Vorlesung', 1, 1, 1, 'Systeme I Vorlesung'), -(2, 'Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', 'Vorlesung', 1, 1, 1, 'Systeme II Vorlesung'), -(3, 'Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', 'Vorlesung', 1, 1, 1, 'Systeme III Vorlesung'); +(1, 'Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', '1', 1, 1, 1, 'Systeme I Vorlesung'), +(2, 'Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', '1', 1, 1, 1, 'Systeme II Vorlesung'), +(3, 'Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', '1', 1, 1, 1, 'Systeme III Vorlesung'); -- Adding eventreport INSERT INTO `poolctrl_eventreport` (`reportID`, `report`, `eventID`) VALUES -- cgit v1.2.3-55-g7522