summaryrefslogtreecommitdiffstats
path: root/setup
diff options
context:
space:
mode:
authorBjörn Geiger2011-06-27 12:14:17 +0200
committerBjörn Geiger2011-06-27 12:14:17 +0200
commit2befc9baa7ce01c49fb09302d120d738bce822f9 (patch)
treed34c3099eba71480a4043a19ba3cf2ed796cec21 /setup
parentkleine layout änderung (diff)
downloadpoolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.tar.gz
poolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.tar.xz
poolctrl-2befc9baa7ce01c49fb09302d120d738bce822f9.zip
Datenbank angepasst
Diffstat (limited to 'setup')
-rw-r--r--setup/poolctrl.sql116
-rw-r--r--setup/poolctrl_data.sql82
2 files changed, 171 insertions, 27 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index f565baf..7581830 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -12,8 +12,8 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0),
`category` varchar(30) COLLATE utf8_unicode_ci,
`pbs_poolID` int(11) NOT NULL,
- `pbs_personID` int(11) NOT NULL,
- `pbs_bootisoID` int(11) NOT NULL,
+ `pbs_membershipID` int(11) NOT NULL,
+ `pbs_bootosID` 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,
@@ -52,21 +52,67 @@ CREATE TABLE IF NOT EXISTS `pbs_person` (
PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
- `bootisoID` int(11) NOT NULL AUTO_INCREMENT,
- `title` varchar(30) NOT NULL,
- `description` varchar(140),
- `membershipID` int(11),
+CREATE TABLE IF NOT EXISTS `pbs_bootos` (
+ `bootosID` int(11) NOT NULL AUTO_INCREMENT,
`groupID` int(11) NOT NULL,
- `prebootID` int(11) NOT NULL,
- `serialnumber` varchar(30) NOT NULL UNIQUE,
+ `membershipID` int(11),
+ `title` varchar(30) NOT NULL,
+ `description` varchar(140),
+ `distro` varchar(30),
+ `distroversion` varchar(30),
+ `source` varchar(140),
+ `share` varchar(30),
+ `shortname` varchar(30),
+ `defaultkcl` varchar(255),
`created` VARCHAR(14) NOT NULL,
`expires` VARCHAR(14),
- `public` int(11) NOT NULL,
- PRIMARY KEY (`bootisoID`),
+ `public` int(11) NOT NULL DEFAULT '-1',
+ PRIMARY KEY (`bootosID`),
+ KEY `groupID` (`groupID`),
+ KEY `membershipID` (`membershipID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
+
+CREATE TABLE IF NOT EXISTS `pbs_config` (
+ `configID` int(11) NOT NULL AUTO_INCREMENT,
+ `title` varchar(30) NOT NULL,
+ `description` varchar(140),
+ `groupID` int(11),
+ `membershipID` int(11),
+ `visible` tinyint(1) DEFAULT '0',
+ `created` VARCHAR(14) NOT NULL,
+ `bootosID` int(11) NOT NULL,
+ PRIMARY KEY (`configID`),
+ KEY `groupID` (`groupID`),
KEY `membershipID` (`membershipID`),
+ KEY `bootosID` (`bootosID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
+
+CREATE TABLE IF NOT EXISTS `pbs_group` (
+ `groupID` int(11) NOT NULL AUTO_INCREMENT,
+ `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
+ `description` varchar(140) COLLATE utf8_unicode_ci,
+ PRIMARY KEY (`groupID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
+
+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;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
+
+CREATE TABLE IF NOT EXISTS `pbs_membership` (
+ `membershipID` int(11) NOT NULL AUTO_INCREMENT,
+ `groupID` int(11) NOT NULL,
+ `personID` int(11) NOT NULL,
+ `suspend` tinyint(1) NOT NULL,
+ `apikey` varchar(30),
+ PRIMARY KEY (`membershipID`),
+ KEY `groupID` (`groupID`),
+ KEY `roleID` (`roleID`),
+ KEY `personID` (`personID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `pbs_pool` (
`poolID` int(11) NOT NULL AUTO_INCREMENT,
@@ -76,6 +122,16 @@ CREATE TABLE IF NOT EXISTS `pbs_pool` (
PRIMARY KEY (`poolID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+CREATE TABLE IF NOT EXISTS `pbs_client` (
+ `clientID` int(11) NOT NULL AUTO_INCREMENT,
+ `macadress` varchar(17) NOT NULL,
+ `hardwarehash` varchar(32),
+ `created` int(15),
+ PRIMARY KEY (`clientID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
+ALTER TABLE `pbs_client` ADD `groupID` INT NOT NULL AFTER `clientID` ;
+ALTER TABLE `pbs_client` ADD UNIQUE (`groupID` ,`macadress`);
+
CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
`poolentriesID` INT NOT NULL AUTO_INCREMENT,
`poolID` int(11) NOT NULL,
@@ -84,11 +140,35 @@ CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-- Constraints
+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,
+ ADD CONSTRAINT `pbs_config_ibfk_3` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE;
+
+ALTER TABLE `pbs_groupgroups`
+ ADD CONSTRAINT `pbs_groupgroups_ibfk_1` FOREIGN KEY (`parentID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `pbs_groupgroups_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
+
+ALTER TABLE `pbs_membership`
+ ADD CONSTRAINT `pbs_membership_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `pbs_membership_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE;
+
+ALTER TABLE `pbs_bootos`
+ 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;
+
+ALTER TABLE `pbs_client`
+ ADD CONSTRAINT `pbs_client_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
+
+ALTER TABLE `pbs_poolentries`
+ ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE;
-ALTER TABLE `pbs_poolentries` ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE;
+ALTER TABLE `pbs_eventreport`
+ ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`evenID`) REFERENCES `pbs_event` (`eventID`) ON DELETE CASCADE;
-ALTER TABLE `poolctrl_event` ADD CONSTRAINT `poolctrl_event_poolidC` FOREIGN KEY (`pbs_poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
- ADD CONSTRAINT `poolctrl_event_personidC` FOREIGN KEY (`pbs_personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE,
- ADD CONSTRAINT `poolctrl_event_boolisoidC` FOREIGN KEY (`pbs_bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE CASCADE,
- ADD CONSTRAINT `poolctrl_event_startC` CHECK (start > CURRENT_TIMESTAMP()),
- ADD CONSTRAINT `poolctrl_event_endC` CHECK (end > start);
+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_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 e0c84d5..1691953 100644
--- a/setup/poolctrl_data.sql
+++ b/setup/poolctrl_data.sql
@@ -1,6 +1,10 @@
USE ##poolctrl##;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+-- Adding person test
+INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
+(1, 'Herr', 'Super', 'Admin', 'Street', '1337', 'Teshouse', '1337', NULL, '1299612370', 'test', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
+
-- Adding person test2
INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
(2, 'Herr', 'Test 2', 'Test 2', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test2', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
@@ -9,11 +13,71 @@ INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `h
INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
(3, 'Herr', 'Test 3', 'Test 3', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test3', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
--- 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');
+-- Adding group
+INSERT INTO `pbs_group` (`groupID` ,`title` ,`description`)VALUES
+(1, 'OpenSLX', 'This is the OpenSLX-Group'),
+(2, 'Germany', 'Deutschland'),
+(3, 'France', 'France'),
+(4, 'DFN', 'Deutsches Forschungsnetz'),
+(5, 'Institutionen', 'Fifth Group'),
+(6, 'Baden-Würtemberg', 'Sixth Group'),
+(7, 'Bayern', 'Seventh Group'),
+(8, 'Uni-Freiburg', 'Eight Group');
+
+-- Adding groupgroups
+INSERT INTO `pbs_groupgroups` (`parentID`, `groupID`) VALUES
+(1, 2),
+(1, 3),
+(2, 4),
+(2, 5),
+(5, 6),
+(5, 7),
+(6, 8);
+
+-- Adding memberships
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '1', '1', '1', 'apikey1');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '2', '1', '1', 'apikey2');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '3', '1', '1', 'apikey3');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '4', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '5', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '6', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '7', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `personID`, `apikey`) VALUES (NULL, '8', '1', '1', 'apikey4');
+
+-- Adding clients
+INSERT INTO `pbs_client` (`clientID`, `groupID`,`macadress`, `hardwarehash`) VALUES
+(1, 1, 'a5:9a:0f:94:2a:b0', 'ea9b82d9de911bc2d3cd23f53a6cab48'),
+(2, 1, '91:91:f1:e2:99:aa', '1e2b1599710fbbef0dc789e8cfe12455'),
+(3, 1, '6e:5c:82:78:f2:39', '8f6209ca3d6b35e223a11c249d1b69fc'),
+(4, 1, '67:75:e9:f2:5f:8e', 'e17ab09f3586464f19629e2e8b1e9a9d'),
+(5, 1, '63:51:7e:22:aa:72', '9bf70279d283b85440c2031c19bb6812'),
+(6, 1, '68:9e:fe:47:95:c5', 'ad3bce4464a6267441ec144744439c7e'),
+(7, 1, '6e:1c:2e:01:77:33', 'e8d7e80d79f224771b7a3a0af4e02748'),
+(8, 1, 'd1:91:20:43:2f:dd', 'ded66ce272f384e9e386c1b57ded3e4d'),
+(9, 1, '1b:0f:a5:82:47:16', '695610ee509c060b1fca9c8011529af4'),
+(10, 1, '56:8e:7b:03:5f:98', 'a3562c8cad2a4fa4fc11656025dc911b'),
+(11, 2, 'af:54:07:87:63:44', '98413218152196816519841365419816'),
+(12, 2, '87:21:74:52:96:20', '98741298132516132169813516981616'),
+(13, 2, '14:47:58:47:36:48', '32168132068132068513216053516513'),
+(14, 1, '64:46:85:A1:89:23', '9684216842068420616841asd6516984');
+
+-- Adding bootos
+INSERT INTO `pbs_bootos` (`bootosID`, `groupID`, `membershipID`, `title`, `description`, `defaultkcl`, `created`, `expires`, `public`, `source`, `distro`, `distroversion`, `shortname`, `share`) VALUES
+(1, 1, 1, 'BootOs 1', 'Description 1', 'default kcl 1', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(2, 1, 1, 'BootOs 2', 'Description 2', 'default kcl 2', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(3, 1, 1, 'BootOs 3', 'Description 3', 'default kcl 3', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(4, 1, 1, 'BootOs 4', 'Description 4', 'default kcl 4', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(5, 1, 1, 'BootOs 5', 'Description 5', 'default kcl 5', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(6, 1, 1, 'BootOs 6', 'Description 6', 'default kcl 6', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(7, 1, 1, 'BootOs 7', 'Description 7', 'default kcl 7', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(8, 1, 1, 'BootOs 8', 'Description 8', 'default kcl 8', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(9, 1, 1, 'BootOs 9', 'Description 9', 'default kcl 9', '1299693782', '2012-03-09', 0, null, null, null, null, null),
+(10, 1, 1, 'BootOs 10', 'Description 10', 'default kcl 10', '1299693782', '2012-03-09', 0, null, null, null, null, null);
+
+-- Adding config
+INSERT INTO `pbs_config` (`configID`, `title`, `description`, `groupID`, `membershipID`, `visible`, `created`, `bootosID`) VALUES
+(1, 'Default Config', 'Desc1', 1, null, '1', '1299693677', '1'),
+(2, 'Config 2', 'Desc2' ,null, 1, '1', '1299693690', '1');
-- Adding pools
INSERT INTO `pbs_pool` (`poolID`, `title`, `description`, `location`) VALUES
@@ -36,10 +100,10 @@ INSERT INTO `pbs_poolentries` (`poolentriesID`, `poolID`, `clientID`) VALUES
(10, 4, 10);
-- Adding events
-INSERT INTO `poolctrl_event` (`title`, `start`, `end`, `category`, `pbs_poolID`, `pbs_personID`, `pbs_bootisoID`, `note`) VALUES
-('Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', 'Vorlesung', 1, 2, 1, 'Systeme I Vorlesung'),
-('Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', 'Vorlesung', 1, 2, 1, 'Systeme II Vorlesung'),
-('Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', 'Vorlesung', 1, 2, 1, 'Systeme III Vorlesung');
+INSERT INTO `poolctrl_event` (`title`, `start`, `end`, `category`, `pbs_poolID`, `pbs_membershipID`, `pbs_bootosID`, `note`) VALUES
+('Systeme I', '2011-06-24 12:10:00', '2011-06-24 14:00:00', 'Vorlesung', 1, 1, 1, 'Systeme I Vorlesung'),
+('Systeme II', '2011-06-24 14:10:00', '2011-06-24 16:00:00', 'Vorlesung', 1, 1, 1, 'Systeme II Vorlesung'),
+('Systeme III', '2011-06-24 16:10:00', '2011-06-24 18:00:00', 'Vorlesung', 1, 1, 1, 'Systeme III Vorlesung');
-- Adding eventreport
INSERT INTO `poolctrl_eventreport` (`report`, `eventID`) VALUES