From 0d4a3b029637e546b17028f2974d919c92f1fd79 Mon Sep 17 00:00:00 2001 From: Björn Geiger Date: Thu, 4 Aug 2011 16:32:41 +0200 Subject: Group und Person nicht mehr in der lokalen Datenbank --- setup/poolctrl.sql | 63 +----------------------------------------------------- 1 file changed, 1 insertion(+), 62 deletions(-) (limited to 'setup/poolctrl.sql') 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; -- cgit v1.2.3-55-g7522