summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--setup/poolctrl.sql56
-rw-r--r--setup/poolctrl_data.sql27
2 files changed, 2 insertions, 81 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index 897229a..4cd4c2a 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -123,70 +123,18 @@ CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
KEY `clientID` (`clientID`)
) 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_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 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_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;
+ ADD CONSTRAINT `pbs_bootos_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL;
ALTER TABLE `pbs_config`
ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
- 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;
+ ADD CONSTRAINT `pbs_config_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) 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_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,
ADD CONSTRAINT `pbs_eventreport_typeidC` FOREIGN KEY (`type`) REFERENCES `poolctrl_eventtype` (`eventtypeID`) ON DELETE CASCADE;
diff --git a/setup/poolctrl_data.sql b/setup/poolctrl_data.sql
index 57df655..6adbc6f 100644
--- a/setup/poolctrl_data.sql
+++ b/setup/poolctrl_data.sql
@@ -1,33 +1,6 @@
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, '1', '1', '1', 'apikey1');
-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', 'apikey5');
-INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '6', '1', '1', 'apikey6');
-INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '7', '1', '1', 'apikey7');
-INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '8', '1', '1', 'apikey8');
-
-- Adding clients
INSERT INTO `pbs_client` (`clientID`, `groupID`,`macadress`, `hardwarehash`, `ip`, `ip6`) VALUES
(1, 1, 'a5:9a:0f:94:2a:b0', 'ea9b82d9de911bc2d3cd23f53a6cab48', '132.230.5.6', ''),