CREATE DATABASE `pbs` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
USE pbs;
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,
PRIMARY KEY (`roleID`,`rightID`),
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,
PRIMARY KEY (`parentID`,`groupID`),
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 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,
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,
`title` varchar(30) NOT NULL,
`groupID` int(11) NOT NULL,
`membershipID` int(11),
`shellscript` text NOT NULL,
`created` VARCHAR(14) NOT NULL,
PRIMARY KEY (`configID`),
KEY `groupID` (`groupID`),
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,
ADD CONSTRAINT `pbs_config_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) 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,
`membershipID` int(11),
`title` varchar(30) NOT NULL,
`description` varchar(140),
`path_init` varchar(140) NOT NULL,
`path_kernel` varchar(140) NOT NULL,
`defaultkcl` varchar(255) NOT NULL,
`created` VARCHAR(14) NOT NULL,
`expires` VARCHAR(14),
`public` int(11) NOT NULL,
PRIMARY KEY (`bootosID`),
KEY `configID` (`configID`),
KEY `groupID` (`groupID`),
KEY `membershipID` (`membershipID`),
KEY `defaultkcl` (`defaultkcl`)
) 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`) ON DELETE SET NULL,
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;
CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
`bootmenuID` int(11) NOT NULL AUTO_INCREMENT,
`membershipID` int(11),
`groupID` int(11) NOT NULL,
`title` varchar(30) NOT NULL,
`created` varchar(14) NOT NULL,
PRIMARY KEY (`bootmenuID`),
KEY `groupID` (`groupID`),
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,
ADD CONSTRAINT `pbs_bootmenu_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_bootmenuentries` (
`bootmenuentriesID` int(11) NOT NULL AUTO_INCREMENT,
`bootosID` int(11) NOT NULL,
`bootmenuID` int(11) NOT NULL,
`title` varchar(30) NOT NULL,
`kcl` varchar(255),
`kclappend` varchar(255),
`configID` int(11),
`order` int(11) NOT NULL,
PRIMARY KEY (`bootmenuentriesID`),
KEY `bootosID` (`bootosID`),
KEY `bootmenuID` (`bootmenuID`),
KEY `kcl` (`kcl`),
KEY `configID` (`configID`)
) 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,
ADD CONSTRAINT `pbs_bootmenuentries_ibfk_3` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`) ON DELETE SET NULL,
ADD CONSTRAINT `pbs_bootmenuentries_ibfk_4` FOREIGN KEY (`kcl`) REFERENCES `pbs_bootos` (`defaultkcl`) ON DELETE CASCADE,
ADD CONSTRAINT `pbs_bootmenuentries_ibfk_5` FOREIGN KEY (`kcl`) REFERENCES `pbs_bootos` (`defaultkcl`) ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_preboot` (
`prebootID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(30) NOT NULL,
`membershipID` int(11),
`groupID` int(11) NOT NULL,
`path_preboot` varchar(30) NOT NULL,
PRIMARY KEY (`prebootID`),
KEY `membershipID` (`membershipID`),
KEY `groupID` (`groupID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `pbs_preboot`
ADD CONSTRAINT `pbs_preboot_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
ADD CONSTRAINT `pbs_preboot_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
`bootisoID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(30) NOT NULL,
`membershipID` int(11),
`groupID` int(11) NOT NULL,
`prebootID` 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`) ON DELETE SET NULL,
ADD CONSTRAINT `pbs_bootiso_ibfk_2` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE,
ADD CONSTRAINT `pbs_bootiso_ibfk_3` FOREIGN KEY (`prebootID`) REFERENCES `pbs_preboot` (`prebootID`) 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;
ALTER TABLE `pbs_client` ADD `groupID` INT NOT NULL AFTER `clientID` ;
ALTER TABLE `pbs_client` ADD UNIQUE (`macadress`);
ALTER TABLE `pbs_client`
ADD CONSTRAINT `pbs_client_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_session` (
`sessionID` int(11) NOT NULL AUTO_INCREMENT,
`clientID` int(11),
`bootosID` int(11),
`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 `bootisoID` INT AFTER `bootosID`;
ALTER TABLE `pbs_session` ADD `alphasessionID` VARCHAR( 16 ) NOT NULL AFTER `sessionID`, ADD UNIQUE (`alphasessionID`) ;
ALTER TABLE `pbs_session` ADD `bootmenuentryID` INT AFTER `clientID` ;
ALTER TABLE `pbs_session` ADD `membershipID` INT NULL AFTER `bootisoID` ;
ALTER TABLE `pbs_session`
ADD CONSTRAINT `pbs_session_ibfk_1` FOREIGN KEY (`clientID`) REFERENCES `pbs_client` (`clientID`) ON DELETE CASCADE,
ADD CONSTRAINT `pbs_session_ibfk_2` FOREIGN KEY (`bootosID`) REFERENCES `pbs_bootos` (`bootosID`) ON DELETE SET NULL,
ADD CONSTRAINT `pbs_session_ibfk_3` FOREIGN KEY (`bootisoID`) REFERENCES `pbs_bootiso` (`bootisoID`) ON DELETE SET NULL,
ADD CONSTRAINT `pbs_session_ibfk_4` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE CASCADE;
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 ;
ALTER TABLE `pbs_pool` ADD `groupID` INT NOT NULL AFTER `poolID`;
ALTER TABLE `pbs_pool`
ADD CONSTRAINT `pbs_pool_ibfk_1` FOREIGN KEY (`groupID`) REFERENCES `pbs_group` (`groupID`) ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS `pbs_poolentries` (
`poolID` int(11) NOT NULL,
`clientID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `pbs_poolentries` ADD `poolentriesID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
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,
PRIMARY KEY (`membershipID`,`filterID`),
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,
PRIMARY KEY (`poolID`,`filterID`),
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
ALTER TABLE `pbs_filterentries` CHANGE `filtervalue2` `filtervalue2` VARCHAR( 140 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;
ALTER TABLE `pbs_filterentries` ADD `filterentriesID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
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;
-- Insert Filtertypes
INSERT INTO `pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES
(1, 'IP'),
(2, 'MAC'),
(3, 'PoolID'),
(4, 'BootIso'),
(5, 'Membership'),
(6, 'Group'),
(7, 'Time'),
(8, 'Client'),
(9, 'Hardwarehash'),
(10, 'Weekday'),
(11, 'Date');
