summaryrefslogtreecommitdiffstats
path: root/pbs.sql
diff options
context:
space:
mode:
Diffstat (limited to 'pbs.sql')
-rw-r--r--pbs.sql279
1 files changed, 279 insertions, 0 deletions
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;
+
+