From dd02094733fb66da0e7ee3c4dfafc75ef20af3bc Mon Sep 17 00:00:00 2001 From: Simon Date: Mon, 17 Jan 2011 11:57:02 +0100 Subject: beginnendes Datenbankschema --- pbs.sql | 279 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 279 insertions(+) create mode 100644 pbs.sql diff --git a/pbs.sql b/pbs.sql new file mode 100644 index 0000000..58f3def --- /dev/null +++ b/pbs.sql @@ -0,0 +1,279 @@ +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +CREATE TABLE IF NOT EXISTS `pbs_group` ( + `groupID` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `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_right` ( + `rightID` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `description` varchar(140) COLLATE utf8_unicode_ci, + PRIMARY KEY (`rightID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + + +CREATE TABLE IF NOT EXISTS `pbs_role` ( + `roleID` int(11) NOT NULL AUTO_INCREMENT, + `groupID` int(11) NOT NULL, + `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `description` varchar(140) COLLATE utf8_unicode_ci, + PRIMARY KEY (`roleID`), + KEY `groupID` (`groupID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_role` + ADD CONSTRAINT `pbs_role_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_rightroles` ( + `roleID` int(11) NOT NULL, + `rightID` int(11) NOT NULL, + KEY `roleID` (`roleID`), + KEY `rightID` (`rightID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_rightroles` + ADD CONSTRAINT `pbs_rightroles_ibfk_1` FOREIGN KEY (`roleID`) REFERENCES `pbs_role` (`roleID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_rightroles_ibfk_2` FOREIGN KEY (`rightID`) REFERENCES `pbs_right` (`rightID`) ON DELETE CASCADE; + + +CREATE TABLE IF NOT EXISTS `pbs_groupgroups` ( + `parentID` int(11) NOT NULL, + `groupID` int(11) NOT NULL, + KEY `parentID` (`parentID`), + KEY `groupID` (`groupID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +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; + +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, + `login` varchar(30) COLLATE utf8_unicode_ci NOT NULL, + `password` varchar(64) COLLATE utf8_unicode_ci NOT NULL, + PRIMARY KEY (`personID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +CREATE TABLE IF NOT EXISTS `pbs_grouprequest` ( + `grouprequestID` int(11) NOT NULL AUTO_INCREMENT, + `groupID` int(11) NOT NULL, + `personID` int(11) NOT NULL, + `time` varchar(14) NOT NULL, + PRIMARY KEY (`grouprequestID`), + KEY `groupID` (`groupID`), + KEY `personID` (`personID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_grouprequest` + ADD CONSTRAINT `pbs_grouprequest_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_grouprequest_ibfk_2` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE; + +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, + PRIMARY KEY (`membershipID`), + KEY `groupID` (`groupID`), + KEY `roleID` (`roleID`), + KEY `personID` (`personID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +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, + ADD CONSTRAINT `pbs_membership_ibfk_3` FOREIGN KEY (`roleID`) REFERENCES `pbs_role` (`roleID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_config` ( + `configID` int(11) NOT NULL AUTO_INCREMENT, + `membershipID` int(11) NOT NULL, + `shellscript` text NOT NULL, + PRIMARY KEY (`configID`), + KEY `membershipID` (`membershipID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_config` + ADD CONSTRAINT `pbs_config_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_bootos` ( + `bootosID` int(11) NOT NULL AUTO_INCREMENT, + `configID` int(11), + `groupID` int(11) NOT NULL, + `title` varchar(30) NOT NULL, + `description` varchar(140), + `path_init` varchar(140) NOT NULL, + `path_kernel` varchar(140) NOT NULL, + `defaultkcl` varchar(140) NOT NULL, + `created` VARCHAR(14) NOT NULL, + `expires` VARCHAR(14), + `public` int(11) NOT NULL, + PRIMARY KEY (`bootosID`), + KEY `configID` (`configID`), + KEY `groupID` (`groupID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_bootos` + ADD CONSTRAINT `pbs_bootos_ibfk_1` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`), + ADD CONSTRAINT `pbs_bootos_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_bootmenu` ( + `bootmenuID` int(11) NOT NULL AUTO_INCREMENT, + `membershipID` int(11) NOT NULL, + `title` varchar(30) NOT NULL, + `time` varchar(14) NOT NULL, + PRIMARY KEY (`bootmenuID`), + KEY `membershipID` (`membershipID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_bootmenu` + ADD CONSTRAINT `pbs_bootmenu_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_bootmenuentries` ( + `bootosID` int(11) NOT NULL, + `bootmenuID` int(11) NOT NULL, + `title` varchar(30) NOT NULL, + `kcl` varchar(140), + `order` int(11) NOT NULL, + KEY `bootosID` (`bootosID`), + KEY `bootmenuID` (`bootmenuID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_bootmenuentries` + ADD CONSTRAINT `pbs_bootmenuentries_ibfk_1` FOREIGN KEY (`bootmenuID`) REFERENCES `pbs_bootmenu` (`bootmenuID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_bootmenuentries_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_bootiso` ( + `bootisoID` int(11) NOT NULL AUTO_INCREMENT, + `membershipID` int(11), + `groupID` int(11) NOT NULL, + `serialnumber` int(11) NOT NULL, + `created` VARCHAR(14) NOT NULL, + `expires` VARCHAR(14), + `public` int(11) NOT NULL, + PRIMARY KEY (`bootisoID`), + KEY `membershipID` (`membershipID`), + KEY `groupID` (`groupID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_bootiso` + ADD CONSTRAINT `pbs_bootiso_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`), + ADD CONSTRAINT `pbs_bootiso_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_client` ( + `clientID` int(11) NOT NULL AUTO_INCREMENT, + `macadress` varchar(17) NOT NULL, + `hardwarehash` varchar(32), + PRIMARY KEY (`clientID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; + +CREATE TABLE IF NOT EXISTS `pbs_session` ( + `sessionID` int(11) NOT NULL AUTO_INCREMENT, + `clientID` int(11) NOT NULL, + `bootosID` int(11) NOT NULL, + `time` varchar(14) NOT NULL, + `ip` varchar(15) NOT NULL, + `ip6` varchar(45), + PRIMARY KEY (`sessionID`), + KEY `clientID` (`clientID`), + KEY `bootosID` (`bootosID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_session` + ADD CONSTRAINT `pbs_session_ibfk_1` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`), + ADD CONSTRAINT `pbs_session_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`); + +CREATE TABLE IF NOT EXISTS `pbs_pool` ( + `poolID` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(30) NOT NULL, + `description` varchar(140), + `location` varchar(30), + PRIMARY KEY (`poolID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +CREATE TABLE IF NOT EXISTS `pbs_poolentries` ( + `poolID` int(11) NOT NULL, + `clientID` int(11) NOT NULL, + KEY `poolID` (`poolID`), + KEY `clientID` (`clientID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +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; + +CREATE TABLE IF NOT EXISTS `pbs_filter` ( + `filterID` int(11) NOT NULL AUTO_INCREMENT, + `membershipID` int(11), + `groupID` int(11) NOT NULL, + `bootmenuID` int(11) NOT NULL, + `title` varchar(30) NOT NULL, + `description` varchar(140), + `created` varchar(14) NOT NULL, + `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 ; + +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; + +CREATE TABLE IF NOT EXISTS `pbs_membershipfilters` ( + `membershipID` int(11) NOT NULL, + `filterID` int(11) NOT NULL, + KEY `membershipID` (`membershipID`), + KEY `filterID` (`filterID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_membershipfilters` + ADD CONSTRAINT `pbs_membershipfilters_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_membershipfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_poolfilters` ( + `poolID` int(11) NOT NULL, + `filterID` int(11) NOT NULL, + KEY `poolID` (`poolID`), + KEY `filterID` (`filterID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_poolfilters` + ADD CONSTRAINT `pbs_poolfilters_ibfk_1` FOREIGN KEY (`poolID`) REFERENCES `pbs_pool` (`poolID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_poolfilters_ibfk_2` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE; + +CREATE TABLE IF NOT EXISTS `pbs_filtertype` ( + `filtertypeID` int(11) NOT NULL AUTO_INCREMENT, + `filtertypename` varchar(30) NOT NULL, + PRIMARY KEY (`filtertypeID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +CREATE TABLE IF NOT EXISTS `pbs_filterentries` ( + `filterID` int(11) NOT NULL, + `filtertypeID` int(11) NOT NULL, + `filtervalue` varchar(140) NOT NULL, + `filtervalue2` varchar(140) NOT NULL, + KEY `filterID` (`filterID`), + KEY `filtertypeID` (`filtertypeID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; + +ALTER TABLE `pbs_filterentries` + ADD CONSTRAINT `pbs_filterentries_ibfk_1` FOREIGN KEY (`filterID`) REFERENCES `pbs_filter` (`filterID`) ON DELETE CASCADE, + ADD CONSTRAINT `pbs_filterentries_ibfk_2` FOREIGN KEY (`filtertypeID`) REFERENCES `pbs_filtertype` (`filtertypeID`) ON DELETE CASCADE; + + -- cgit v1.2.3-55-g7522