summaryrefslogtreecommitdiffstats
path: root/setup/poolctrl.sql
diff options
context:
space:
mode:
authorBjörn Geiger2011-08-04 16:32:41 +0200
committerBjörn Geiger2011-08-04 16:32:41 +0200
commit0d4a3b029637e546b17028f2974d919c92f1fd79 (patch)
treed3f9e194c91224c73cfaec414a5ed9733cbefacb /setup/poolctrl.sql
parentPassword Reovery hinzugefügt (diff)
downloadpoolctrl-0d4a3b029637e546b17028f2974d919c92f1fd79.tar.gz
poolctrl-0d4a3b029637e546b17028f2974d919c92f1fd79.tar.xz
poolctrl-0d4a3b029637e546b17028f2974d919c92f1fd79.zip
Group und Person nicht mehr in der lokalen Datenbank
Diffstat (limited to 'setup/poolctrl.sql')
-rw-r--r--setup/poolctrl.sql63
1 files changed, 1 insertions, 62 deletions
diff --git a/setup/poolctrl.sql b/setup/poolctrl.sql
index a2dfeef..2f987f3 100644
--- a/setup/poolctrl.sql
+++ b/setup/poolctrl.sql
@@ -29,7 +29,6 @@ CREATE TABLE IF NOT EXISTS `poolctrl_eventaction` (
PRIMARY KEY (`eventactionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;
-
CREATE TABLE IF NOT EXISTS `poolctrl_eventreport` (
`reportID` int(11) NOT NULL AUTO_INCREMENT,
`report` varchar(140) COLLATE utf8_unicode_ci DEFAULT 'success',
@@ -59,7 +58,6 @@ CREATE TABLE IF NOT EXISTS `pbs_filter` (
`priority` int(11) NOT NULL,
PRIMARY KEY (`filterID`),
KEY `membershipID` (`membershipID`),
- KEY `groupID` (`groupID`),
KEY `bootmenuID` (`bootmenuID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
@@ -70,31 +68,9 @@ 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`),
- KEY `groupID` (`groupID`),
KEY `membershipID` (`membershipID`)
) 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_bootos` (
`bootosID` int(11) NOT NULL AUTO_INCREMENT,
`groupID` int(11) NOT NULL,
@@ -111,7 +87,6 @@ CREATE TABLE IF NOT EXISTS `pbs_bootos` (
`expires` VARCHAR(14),
`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 ;
@@ -125,35 +100,18 @@ CREATE TABLE IF NOT EXISTS `pbs_config` (
`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 ;
-
CREATE TABLE IF NOT EXISTS `pbs_membership` (
`membershipID` int(11) NOT NULL AUTO_INCREMENT,
`groupID` int(11) NOT NULL,
`personID` int(11) NOT NULL,
+ `roleID` int(11) NOT NULL,
`suspend` tinyint(1) NOT NULL,
`apikey` varchar(30),
PRIMARY KEY (`membershipID`),
- KEY `groupID` (`groupID`),
- KEY `personID` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `pbs_pool` (
@@ -173,7 +131,6 @@ CREATE TABLE IF NOT EXISTS `pbs_client` (
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,
@@ -191,7 +148,6 @@ CREATE TABLE IF NOT EXISTS `pbs_passwordrecovery` (
-- Constraints
ALTER TABLE `pbs_filter`
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`;
@@ -199,33 +155,16 @@ ALTER TABLE `pbs_bootmenu` CHANGE `startcounter` `startcounter` INT( 11 ) NOT NU
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_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;
-ALTER TABLE `pbs_passwordrecovery`
- ADD CONSTRAINT `pbs_passwordrecovery_ibfk_1` 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;