summaryrefslogtreecommitdiffstats
path: root/setup
diff options
context:
space:
mode:
Diffstat (limited to 'setup')
-rw-r--r--setup/poolctrl.sql121
-rw-r--r--setup/poolctrl_data.sql26
2 files changed, 124 insertions, 23 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index c00db75..bb40f8a 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -8,18 +8,24 @@ CREATE TABLE IF NOT EXISTS `poolctrl_event` (
`eventID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
`start` timestamp COLLATE utf8_unicode_ci NOT NULL DEFAULT CURRENT_TIMESTAMP(),
- `end` timestamp COLLATE utf8_unicode_ci NOT NULL,
+ `end` timestamp COLLATE utf8_unicode_ci NULL,
`participants` int(4) NOT NULL DEFAULT 50 CHECK (participants > 0),
`category` int(11) NOT NULL,
`pbs_poolID` int(11) NOT NULL,
`pbs_membershipID` int(11) NOT NULL,
`pbs_bootosID` int(11) NOT NULL,
+ `pbs_bootmenuID` int(11) NOT NULL,
+ `pbs_filterID` 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,
- `pbs_bootmenuID` int(11) NOT NULL,
- `pbs_filterID` int(11) NOT NULL,
- PRIMARY KEY (`eventID`)
+ PRIMARY KEY (`eventID`),
+ KEY `pbs_poolID` (`pbs_poolID`),
+ KEY `pbs_membershipID` (`pbs_membershipID`),
+ KEY `pbs_bootosID` (`pbs_bootosID`),
+ KEY `pbs_bootmenuID` (`pbs_bootmenuID`),
+ KEY `pbs_filterID` (`pbs_filterID`),
+ KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` (
@@ -27,7 +33,8 @@ 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`)
+ PRIMARY KEY (`reportID`),
+ KEY `eventID` (`eventID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
CREATE TABLE IF NOT EXISTS `poolctrl_eventcategory` (
@@ -49,8 +56,10 @@ CREATE TABLE IF NOT EXISTS `pbs_filter` (
`description` varchar(140),
`created` varchar(14) NOT NULL,
`priority` int(11) NOT NULL,
- PRIMARY KEY (`filterID`)
- KEY `bootmenuID` (`bootmenuID`)
+ PRIMARY KEY (`filterID`),
+ KEY `bootmenuID` (`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_bootmenu` (
@@ -60,7 +69,10 @@ CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
`title` varchar(50) NOT NULL,
`created` varchar(14) NOT NULL,
`defaultbootmenu` tinyint(1) NOT NULL DEFAULT '0',
- PRIMARY KEY (`bootmenuID`)
+ `startcounter` INT( 11 ) NOT NULL DEFAULT '300',
+ 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_bootos` (
@@ -78,7 +90,9 @@ CREATE TABLE IF NOT EXISTS `pbs_bootos` (
`created` VARCHAR(14) NOT NULL,
`expires` VARCHAR(14),
`public` int(11) NOT NULL DEFAULT '-1',
- PRIMARY KEY (`bootosID`)
+ 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` (
@@ -91,7 +105,9 @@ CREATE TABLE IF NOT EXISTS `pbs_config` (
`created` VARCHAR(14) NOT NULL,
`bootosID` int(11) NOT NULL,
PRIMARY KEY (`configID`),
- KEY `bootosID` (`bootosID`)
+ KEY `bootosID` (`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_pool` (
@@ -100,43 +116,101 @@ CREATE TABLE IF NOT EXISTS `pbs_pool` (
`title` varchar(30) NOT NULL,
`description` varchar(140),
`location` varchar(30),
- PRIMARY KEY (`poolID`)
+ PRIMARY KEY (`poolID`),
+ KEY `groupID` (`groupID`)
) 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,
+ `groupID` int(11) NOT NULL,
`macadress` varchar(17) NOT NULL,
`hardwarehash` varchar(32),
`created` int(15),
- PRIMARY KEY (`clientID`)
+ PRIMARY KEY (`clientID`),
+ KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-ALTER TABLE `pbs_client` ADD `groupID` INT NOT NULL AFTER `clientID` ;
CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
`poolentriesID` INT NOT NULL AUTO_INCREMENT,
`poolID` int(11) NOT NULL,
`clientID` int(11) NOT NULL,
- PRIMARY KEY (`poolentriesID`)
+ PRIMARY KEY (`poolentriesID`),
+ KEY `poolID` (`poolID`),
+ KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-CREATE TABLE IF NOT EXISTS `pbs_passwordrecovery` (
- `personID` int(11) NOT NULL,
- `recoveryID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
+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_person` (
+ `personID` int(11) NOT NULL AUTO_INCREMENT,
+ `title` varchar(30) COLLATE utf8_unicode_ci,
+ `name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
+ `firstname` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
+ `street` varchar(30) COLLATE utf8_unicode_ci,
+ `housenumber` varchar(30) COLLATE utf8_unicode_ci,
+ `city` varchar(30) COLLATE utf8_unicode_ci,
+ `postalcode` varchar(30) COLLATE utf8_unicode_ci,
+ `logindate` varchar(14) COLLATE utf8_unicode_ci,
+ `registerdate` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
+ `email` varchar(30) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
+ `login` varchar(30) COLLATE utf8_unicode_ci,
+ `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
+ `password_salt` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
+ `loginpassword` varchar(512) COLLATE utf8_unicode_ci NOT NULL,
+ `suspend` tinyint(1) NOT NULL,
PRIMARY KEY (`personID`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+) 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,
+ `roleID` 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 ;
-- Constraints
ALTER TABLE `pbs_filter`
- ADD CONSTRAINT `pbs_filter_ibfk` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE;
+ 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_bootmenu`
+ ADD CONSTRAINT `pbs_bootmenu_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
+ ADD CONSTRAINT `pbs_bootmenu_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) 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_config`
- ADD CONSTRAINT `pbs_config_ibfk` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE;
+ 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_client`
+ ADD CONSTRAINT `pbs_client_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
+
+ALTER TABLE `pbs_pool`
+ ADD CONSTRAINT `pbs_pool_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;
+ ADD CONSTRAINT `pbs_poolentries_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `pbs_poolentries_ibfk_2` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`) 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 `poolctrl_eventreport`
ADD CONSTRAINT `pbs_eventreport_eventidC` FOREIGN KEY (`eventID`) REFERENCES `poolctrl_event` (`eventID`) ON DELETE CASCADE;
@@ -144,6 +218,7 @@ 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_bootosidC` FOREIGN KEY (`pbs_bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE,
+ ADD CONSTRAINT `poolctrl_event_membershipidC` FOREIGN KEY (`pbs_membershipID`) REFERENCES `pbs_membership` (`membershipID`) 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,
diff --git a/setup/poolctrl_data.sql b/setup/poolctrl_data.sql
index e3d9b4c..6a7be68 100644
--- a/setup/poolctrl_data.sql
+++ b/setup/poolctrl_data.sql
@@ -1,6 +1,32 @@
USE ##poolctrl##;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+-- Adding person
+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.'),
+(2, 'Herr', 'Test 2', 'Test 2', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test2', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.'),
+(3, 'Herr', 'Test 3', 'Test 3', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test3', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');
+
+-- 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 memberships
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '2', '1', '1', 'apikey2');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '3', '1', '1', 'apikey3');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '4', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '5', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '6', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '7', '1', '1', 'apikey4');
+INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `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'),