adjusted Bootiso in pbs.sql and pbs_newdata.sql

3 files changed, 27 insertions, 9 deletions

diff --git a/setup/pbs-newdata.sql b/setup/pbs-newdata.sql
index 86b0993..e110b43 100644
--- a/setup/pbs-newdata.sql
+++ b/setup/pbs-newdata.sql
@@ -90,10 +90,10 @@ INSERT INTO `pbs_preboot` (`prebootID`, `title`, `membershipID`, `groupID`, `cre
 (3, 'Preboot 3', 1, 1,  '1299694422', 'source1', 'Description 3', 0, '12-20-30-40-50');
 
 -- Adding bootiso
-INSERT INTO `pbs_bootiso` (`bootisoID`, `title`, `membershipID`, `groupID`, `prebootID`, `serialnumber`, `created`, `expires`, `public`, `description`) VALUES
-(1, 'BootMedium 1', 1, 1, '1', '10-20-30-40-50', '123', '2012-03-09', 0, 'Description 1'),
-(2, 'BootMedium 2', 1, 1, '2', '11-20-30-40-50', '456', '2012-03-09', 0, 'Description 2'),
-(3, 'BootMedium 3', 1, 1, '3', '12-20-30-40-50', '789', '2012-03-09', 0, 'Description 3');
+INSERT INTO `pbs_bootiso` (`bootisoID`, `title`, `membershipID`, `groupID`, `typeID`, `serialnumber`, `created`, `expires`, `public`, `description`) VALUES
+(1, 'BootMedium 1', 1, 1, 0, '10-20-30-40-50', '123', '2012-03-09', 0, 'Description 1'),
+(2, 'BootMedium 2', 1, 1, 0, '11-20-30-40-50', '456', '2012-03-09', 0, 'Description 2'),
+(3, 'BootMedium 3', 1, 1, 0, '12-20-30-40-50', '789', '2012-03-09', 0, 'Description 3');
 
 -- Adding Pools
 INSERT INTO `pbs_pool` (`poolID`, `groupID`, `title`, `description`, `location`) VALUES
diff --git a/setup/pbs.sql b/setup/pbs.sql
index 886d8a3..6497ba2 100644
--- a/setup/pbs.sql
+++ b/setup/pbs.sql
@@ -233,16 +233,20 @@ CREATE TABLE IF NOT EXISTS `pbs_preboot` (
   `description` varchar(140),
   `membershipID` int(11),
   `groupID` int(11) NOT NULL,
+  `typeID` int(11) NOT NULL,
+  `serialnumber` varchar(30) NOT NULL,
   `source` varchar(140),
   `created` VARCHAR(14) NOT NULL,
   PRIMARY KEY (`prebootID`),
+  UNIQUE KEY `typeID` (`typeID`,`serialnumber`),
   KEY `membershipID` (`membershipID`),
   KEY `groupID` (`groupID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
 ALTER TABLE `pbs_preboot`
   ADD CONSTRAINT `pbs_preboot_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
-  ADD CONSTRAINT `pbs_preboot_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
+  ADD CONSTRAINT `pbs_preboot_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
+  ADD CONSTRAINT `pbs_preboot_ibfk_3` FOREIGN KEY (`typeID`) REFERENCES `pbs_preboottype` (`preboottypeID`);
 
 
 CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
@@ -252,11 +256,13 @@ CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
   `membershipID` int(11),
   `groupID` int(11) NOT NULL,
   `prebootID` int(11) NOT NULL,    
-  `serialnumber` varchar(30) NOT NULL UNIQUE,
+  `typeID` int(11) NOT NULL,
+  `serialnumber` varchar(30) NOT NULL,
   `created` VARCHAR(14) NOT NULL,
   `expires` VARCHAR(14),
   `public` int(11) NOT NULL,
   PRIMARY KEY (`bootisoID`),
+  UNIQUE KEY `typeID` (`typeID`,`serialnumber`),
   KEY `membershipID` (`membershipID`),
   KEY `groupID` (`groupID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -265,7 +271,7 @@ CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
 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_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
-  ADD CONSTRAINT `pbs_bootiso_ibfk_3` FOREIGN KEY (`prebootID`) REFERENCES `pbs_preboot` (`prebootID`) ON DELETE CASCADE;
+  ADD CONSTRAINT `pbs_bootiso_ibfk_3` FOREIGN KEY (`typeID`) REFERENCES `pbs_preboottype` (`preboottypeID`);
 
 CREATE TABLE IF NOT EXISTS `pbs_client` (
   `clientID` int(11) NOT NULL AUTO_INCREMENT,
diff --git a/setup/pbs.sql~ b/setup/pbs.sql~
index dcb093f..fa90857 100644
--- a/setup/pbs.sql~
+++ b/setup/pbs.sql~
@@ -124,6 +124,12 @@ CREATE TABLE IF NOT EXISTS `pbs_hometype` (
   PRIMARY KEY (`hometypeID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
 
+CREATE TABLE IF NOT EXISTS `pbs_preboottype` (
+  `preboottypeID` int(11) NOT NULL,
+  `name` varchar(30) NOT NULL,
+  PRIMARY KEY (`preboottypeID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+
 CREATE TABLE IF NOT EXISTS `pbs_bootos` (
   `bootosID` int(11) NOT NULL AUTO_INCREMENT,
   `groupID` int(11) NOT NULL,
@@ -227,16 +233,20 @@ CREATE TABLE IF NOT EXISTS `pbs_preboot` (
   `description` varchar(140),
   `membershipID` int(11),
   `groupID` int(11) NOT NULL,
+  `typeID` int(11) NOT NULL,
+  `serialnumber` varchar(30) NOT NULL,
   `source` varchar(140),
   `created` VARCHAR(14) NOT NULL,
   PRIMARY KEY (`prebootID`),
+  UNIQUE KEY `typeID` (`typeID`,`serialnumber`),
   KEY `membershipID` (`membershipID`),
   KEY `groupID` (`groupID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
 ALTER TABLE `pbs_preboot`
   ADD CONSTRAINT `pbs_preboot_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
-  ADD CONSTRAINT `pbs_preboot_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
+  ADD CONSTRAINT `pbs_preboot_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
+  ADD CONSTRAINT `pbs_preboot_ibfk_3` FOREIGN KEY (`typeID`) REFERENCES `pbs_preboottype` (`preboottypeID`);
 
 
 CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
@@ -246,11 +256,13 @@ CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
   `membershipID` int(11),
   `groupID` int(11) NOT NULL,
   `prebootID` int(11) NOT NULL,    
-  `serialnumber` varchar(30) NOT NULL UNIQUE,
+  `typeID` int(11) NOT NULL,
+  `serialnumber` varchar(30) NOT NULL,
   `created` VARCHAR(14) NOT NULL,
   `expires` VARCHAR(14),
   `public` int(11) NOT NULL,
   PRIMARY KEY (`bootisoID`),
+  UNIQUE KEY `typeID` (`typeID`,`serialnumber`),
   KEY `membershipID` (`membershipID`),
   KEY `groupID` (`groupID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;