From bb45b9e1e83caaee9d0d29daec37c345bee47f26 Mon Sep 17 00:00:00 2001
From: Björn Geiger
Date: Thu, 10 Mar 2011 14:40:05 +0100
Subject: zu allen verknüpfungstabellen zusammengesetzten primary key
 hinzugefügt, Zend benötigt primary key

---
 pbs.sql | 23 ++++++++++-------------
 1 file changed, 10 insertions(+), 13 deletions(-)

(limited to 'pbs.sql')

diff --git a/pbs.sql b/pbs.sql
index f404b0a..5cf0c5a 100644
--- a/pbs.sql
+++ b/pbs.sql
@@ -33,6 +33,7 @@ ALTER TABLE `pbs_role`
 CREATE TABLE IF NOT EXISTS `pbs_rightroles` (
   `roleID` int(11) NOT NULL,
   `rightID` int(11) NOT NULL,
+  PRIMARY KEY (`roleID`,`rightID`),
   KEY `roleID` (`roleID`),
   KEY `rightID` (`rightID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -45,6 +46,7 @@ ALTER TABLE `pbs_rightroles`
 CREATE TABLE IF NOT EXISTS `pbs_groupgroups` (
   `parentID` int(11) NOT NULL,
   `groupID` int(11) NOT NULL,
+  PRIMARY KEY (`parentID`,`groupID`),
   KEY `parentID` (`parentID`),
   KEY `groupID` (`groupID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -140,7 +142,7 @@ CREATE TABLE IF NOT EXISTS `pbs_bootos` (
 ALTER TABLE `pbs_bootos`
   ADD CONSTRAINT `pbs_bootos_ibfk_1` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`),
   ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
-  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`);
+  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL;
 
 CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
   `bootmenuID` int(11) NOT NULL AUTO_INCREMENT,
@@ -192,7 +194,7 @@ CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
 ALTER TABLE `pbs_bootiso`
-  ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`),
+  ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
   ADD CONSTRAINT `pbs_bootiso_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
 
 CREATE TABLE IF NOT EXISTS `pbs_client` (
@@ -204,8 +206,8 @@ CREATE TABLE IF NOT EXISTS `pbs_client` (
 
 CREATE TABLE IF NOT EXISTS `pbs_session` (
   `sessionID` int(11) NOT NULL AUTO_INCREMENT,
-  `clientID` int(11),
-  `bootosID` int(11),
+  `clientID` int(11) NOT NULL,
+  `bootosID` int(11) NOT NULL,
   `time` varchar(14) NOT NULL,
   `ip` varchar(15) NOT NULL,
   `ip6` varchar(45),
@@ -231,6 +233,7 @@ CREATE TABLE IF NOT EXISTS `pbs_pool` (
 CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
   `poolID` int(11) NOT NULL,
   `clientID` int(11) NOT NULL,
+  PRIMARY KEY (`poolID`,`clientID`),
   KEY `poolID` (`poolID`),
   KEY `clientID` (`clientID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -263,6 +266,7 @@ ALTER TABLE `pbs_filter`
 CREATE TABLE IF NOT EXISTS `pbs_membershipfilters` (
   `membershipID` int(11) NOT NULL,
   `filterID` int(11) NOT NULL,
+  PRIMARY KEY (`membershipID`,`filterID`),
   KEY `membershipID` (`membershipID`),
   KEY `filterID` (`filterID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -274,6 +278,7 @@ ALTER TABLE `pbs_membershipfilters`
 CREATE TABLE IF NOT EXISTS `pbs_poolfilters` (
   `poolID` int(11) NOT NULL,
   `filterID` int(11) NOT NULL,
+  PRIMARY KEY (`poolID`,`filterID`),
   KEY `poolID` (`poolID`),
   KEY `filterID` (`filterID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -293,6 +298,7 @@ CREATE TABLE IF NOT EXISTS `pbs_filterentries` (
   `filtertypeID` int(11) NOT NULL,
   `filtervalue` varchar(140) NOT NULL,
   `filtervalue2` varchar(140) NOT NULL,
+  PRIMARY KEY (`filterID`,`filtertypeID`),
   KEY `filterID` (`filterID`),
   KEY `filtertypeID` (`filtertypeID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -303,14 +309,5 @@ ALTER TABLE `pbs_filterentries`
   ADD CONSTRAINT `pbs_filterentries_ibfk_2` FOREIGN KEY (`filtertypeID`) REFERENCES `pbs_filtertype` (`filtertypeID`) ON DELETE CASCADE;
 
 
--- Insert Filtertypes
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'IP');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'MAC');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'PoolID');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'BootIso');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Membership');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Group');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Time');
-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Client');
 
 
-- 
cgit v1.2.3-55-g7522


From 959b12e883b32ff5771cac3fb52c10dc0d018066 Mon Sep 17 00:00:00 2001
From: Björn Geiger
Date: Thu, 10 Mar 2011 14:46:40 +0100
Subject: ausversehen gelöschtes wiederhergestellt

---
 pbs.sql | 19 +++++++++++++------
 1 file changed, 13 insertions(+), 6 deletions(-)

(limited to 'pbs.sql')

diff --git a/pbs.sql b/pbs.sql
index 5cf0c5a..319e46a 100644
--- a/pbs.sql
+++ b/pbs.sql
@@ -142,7 +142,7 @@ CREATE TABLE IF NOT EXISTS `pbs_bootos` (
 ALTER TABLE `pbs_bootos`
   ADD CONSTRAINT `pbs_bootos_ibfk_1` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`),
   ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
-  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL;
+  ADD CONSTRAINT `pbs_bootos_ibfk_3` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`);
 
 CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
   `bootmenuID` int(11) NOT NULL AUTO_INCREMENT,
@@ -194,7 +194,7 @@ CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
 ALTER TABLE `pbs_bootiso`
-  ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
+  ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`),
   ADD CONSTRAINT `pbs_bootiso_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
 
 CREATE TABLE IF NOT EXISTS `pbs_client` (
@@ -206,8 +206,8 @@ CREATE TABLE IF NOT EXISTS `pbs_client` (
 
 CREATE TABLE IF NOT EXISTS `pbs_session` (
   `sessionID` int(11) NOT NULL AUTO_INCREMENT,
-  `clientID` int(11) NOT NULL,
-  `bootosID` int(11) NOT NULL,
+  `clientID` int(11),
+  `bootosID` int(11),
   `time` varchar(14) NOT NULL,
   `ip` varchar(15) NOT NULL,
   `ip6` varchar(45),
@@ -308,6 +308,13 @@ ALTER TABLE `pbs_filterentries`
   ADD CONSTRAINT `pbs_filterentries_ibfk_1` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE,
   ADD CONSTRAINT `pbs_filterentries_ibfk_2` FOREIGN KEY (`filtertypeID`) REFERENCES `pbs_filtertype` (`filtertypeID`) ON DELETE CASCADE;
 
-
-
+-- Insert Filtertypes
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'IP');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'MAC');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'PoolID');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'BootIso');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Membership');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Group');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Time');
+-INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Client');
 
-- 
cgit v1.2.3-55-g7522


From 4a9b2301390f34f161e17830e4f0cd1df207831c Mon Sep 17 00:00:00 2001
From: Björn Geiger
Date: Thu, 10 Mar 2011 14:47:55 +0100
Subject: ausversehen gelöschtes wiederhergestellt

---
 pbs.sql | 16 ++++++++--------
 1 file changed, 8 insertions(+), 8 deletions(-)

(limited to 'pbs.sql')

diff --git a/pbs.sql b/pbs.sql
index 319e46a..f762115 100644
--- a/pbs.sql
+++ b/pbs.sql
@@ -309,12 +309,12 @@ ALTER TABLE `pbs_filterentries`
   ADD CONSTRAINT `pbs_filterentries_ibfk_2` FOREIGN KEY (`filtertypeID`) REFERENCES `pbs_filtertype` (`filtertypeID`) ON DELETE CASCADE;
 
 -- Insert Filtertypes
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'IP');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'MAC');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'PoolID');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'BootIso');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Membership');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Group');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Time');
--INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Client');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'IP');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'MAC');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'PoolID');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'BootIso');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Membership');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Group');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Time');
+INSERT INTO `pbs`.`pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES (NULL, 'Client');
 
-- 
cgit v1.2.3-55-g7522

