summaryrefslogtreecommitdiffstats
path: root/setup
diff options
context:
space:
mode:
authorSebastian Wagner2011-06-29 22:45:27 +0200
committerSebastian Wagner2011-06-29 22:45:27 +0200
commitbba95dd2bd9d39a7ba04949b351f93cff3a0f29f (patch)
tree28b82b7035ab63f6a60ee0bace73f67b29b419e7 /setup
parentminor (diff)
downloadpoolctrl-bba95dd2bd9d39a7ba04949b351f93cff3a0f29f.tar.gz
poolctrl-bba95dd2bd9d39a7ba04949b351f93cff3a0f29f.tar.xz
poolctrl-bba95dd2bd9d39a7ba04949b351f93cff3a0f29f.zip
databasechanges
Diffstat (limited to 'setup')
-rw-r--r--setup/poolctrl.sql41
-rw-r--r--setup/poolctrl_data.sql22
2 files changed, 58 insertions, 5 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index 9be8e3e..c3604d2 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -17,6 +17,8 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`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;
@@ -38,6 +40,33 @@ CREATE TABLE IF NOT EXISTS `poolctrl_eventcategory` (
-- 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,
@@ -146,6 +175,14 @@ CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
) 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,
@@ -178,7 +215,9 @@ ALTER TABLE `poolctrl_eventreport`
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_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_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 a4339eb..34f2b88 100644
--- a/setup/poolctrl_data.sql
+++ b/setup/poolctrl_data.sql
@@ -99,6 +99,20 @@ INSERT INTO `pbs_poolentries` (`poolentriesID`, `poolID`, `clientID`) VALUES
(9, 4, 9),
(10, 4, 10);
+-- Adding Bootmenus
+INSERT INTO `pbs_bootmenu` (`bootmenuID`, `membershipID`, `groupID`, `title`, `created`,`defaultbootmenu`) VALUES
+(1, NULL, 1, 'Bootmenu 1', '1299694422', '0'),
+(2, NULL, 1, 'Bootmenu 2', '1299694433', '0'),
+(3, NULL, 1, 'Bootmenu 3', '1299694441', '0'),
+(4, NULL, 1, 'Bootmenu 4', '1299694454', '1'),
+(5, NULL, 1, 'Bootmenu 5', '1299694461', '0');
+
+-- Adding Filter
+INSERT INTO `pbs_filter` (`filterID`, `membershipID`, `groupID`, `bootmenuID`, `title`, `description`, `created`, `priority`) VALUES
+(1, NULL, 1, 1, 'Filter 1', 'Innerhalb der Uni', '1300022113', 1),
+(2, NULL, 1, 2, 'Filter 2', 'Ausserhalb der Uni', '1302000113', 1),
+(3, NULL, 1, 3, 'Filter 3', 'Montags-Freitag \r\nvon 8 bis 16 Uhr \r\nInnerhalb der Uni', '1302022113', 10);
+
-- Adding eventcategories
INSERT INTO `poolctrl_eventcategory` (`eventcategoryID`, `title`) VALUES
(1, 'Lecture'),
@@ -107,10 +121,10 @@ INSERT INTO `poolctrl_eventcategory` (`eventcategoryID`, `title`) VALUES
(4, 'Public');
-- 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', '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');
+INSERT INTO `poolctrl_event` (`eventID`, `title`, `start`, `end`, `category`, `pbs_poolID`, `pbs_membershipID`, `pbs_bootosID`, `note`, `pbs_bootmenuID`, `pbs_filterID`) VALUES
+(1, 'Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', '1', 1, 1, 1, 'Systeme I Vorlesung', 1, 1),
+(2, 'Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', '1', 1, 1, 1, 'Systeme II Vorlesung', 1, 1),
+(3, 'Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', '1', 1, 1, 1, 'Systeme III Vorlesung', 1, 1);
-- Adding eventreport
INSERT INTO `poolctrl_eventreport` (`reportID`, `report`, `eventID`) VALUES