path: root/setup/pbs.sql

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 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_rightcategory` (
  `rightcategoryID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`rightcategoryID`)
) 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,
  `rightcategoryID` int(11) NOT NULL,
  `shortcut` varchar(10) COLLATE utf8_unicode_ci NOT NULL UNIQUE,
  `title` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`rightID`),
  KEY `rightcategoryID` (`rightcategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_right`
  ADD CONSTRAINT `pbs_right_ibfk_1` FOREIGN KEY (`rightcategoryID`) REFERENCES `pbs_rightcategory` (`rightcategoryID`) ON DELETE CASCADE;

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 DEFAULT NULL,
  `inheritance` tinyint(1) NOT NULL,
  PRIMARY KEY (`roleID`),
  KEY `groupID` (`groupID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

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,
  `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_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,
  `suspend` tinyint(1) NOT NULL,
  `apikey` varchar(30),
  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_hometype` (
  `hometypeID` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`hometypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `pbs_preboottype` (
  `preboottypeID` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  PRIMARY KEY (`preboottypeID`)
) 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,
  `membershipID` int(11),
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `distro` varchar(30),
  `distroversion` varchar(30),
  `source` varchar(140),
  `share` varchar(30),
  `shortname` varchar(30),
  `defaultkcl` varchar(255),
  `created` VARCHAR(14) NOT NULL,
  `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 ;
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;

 CREATE TABLE IF NOT EXISTS `pbs_config` (
  `configID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `description` varchar(140),
  `groupID` int(11),
  `membershipID` int(11),
  `visible` tinyint(1) DEFAULT '0',
  `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 ;

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; 
  
  
 CREATE TABLE IF NOT EXISTS `pbs_bootosuser` (
  `bootosuserID` int(11) NOT NULL AUTO_INCREMENT,
  `configID` int(11) NOT NULL,
  `login` varchar(30) NOT NULL,
  `password` varchar(512) NOT NULL,
  `homepath` varchar(140),
  `hometypeID` int(11),
  PRIMARY KEY (`bootosuserID`),
  KEY `configID` (`configID`),
  KEY `hometypeID` (`hometypeID`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_bootosuser`
  ADD CONSTRAINT `pbs_bootosuser_ibfk_1` FOREIGN KEY (`configID`) REFERENCES `pbs_config` (`configID`) ON DELETE CASCADE; 
  
CREATE TABLE IF NOT EXISTS `pbs_bootmenu` (
  `bootmenuID` int(11) NOT NULL AUTO_INCREMENT,
  `membershipID` int(11),
  `groupID` int(11),
  `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 ;
ALTER TABLE `pbs_bootmenu`  ADD `startcounter` INT NOT NULL AFTER `title`;
ALTER TABLE `pbs_bootmenu` CHANGE `startcounter` `startcounter` INT( 11 ) NOT NULL DEFAULT '300';

ALTER TABLE `pbs_bootmenu`
  ADD CONSTRAINT `pbs_bootmenu_ibfk_1` FOREIGN KEY (`membershipID`) REFERENCES `pbs_membership` (`membershipID`) ON DELETE SET NULL,
  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` tinyint(1) NOT NULL,
  `kclappend` varchar(255),
  `configID` int(11),
  `order` int(11) NOT NULL,
  PRIMARY KEY (`bootmenuentriesID`),
  KEY `bootosID` (`bootosID`),
  KEY `bootmenuID` (`bootmenuID`),
  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;

CREATE TABLE IF NOT EXISTS `pbs_preboot` (
  `prebootID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,  
  `description` varchar(140),
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `preboottypeID` int(11) NOT NULL,
  `serialnumber` varchar(30) NOT NULL,
  `source` varchar(140),
  `created` VARCHAR(14) NOT NULL,
  PRIMARY KEY (`prebootID`),
  UNIQUE KEY `preboottypeID` (`preboottypeID`,`serialnumber`),
  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,
  ADD CONSTRAINT `pbs_preboot_ibfk_3` FOREIGN KEY (`preboottypeID`) REFERENCES `pbs_preboottype` (`preboottypeID`);


CREATE TABLE IF NOT EXISTS `pbs_bootiso` (
  `bootisoID` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,  
  `description` varchar(140),
  `membershipID` int(11),
  `groupID` int(11) NOT NULL,
  `prebootID` int(11) NOT NULL,    
  `preboottypeID` int(11) NOT NULL,
  `serialnumber` varchar(30) NOT NULL,
  `created` VARCHAR(14) NOT NULL,
  `expires` VARCHAR(14),
  `public` int(11) NOT NULL,
  PRIMARY KEY (`bootisoID`),
  UNIQUE KEY `preboottypeID` (`preboottypeID`,`serialnumber`),
  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 (`preboottypeID`) REFERENCES `pbs_preboottype` (`preboottypeID`);

CREATE TABLE IF NOT EXISTS `pbs_client` (
  `clientID` int(11) NOT NULL AUTO_INCREMENT,
  `macadress` varchar(17) NOT NULL,
  `hardwarehash` varchar(32),
  `created` int(15),
  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`);
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 `personID` INT NULL AFTER `membershipID` ;

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,
  ADD CONSTRAINT `pbs_session_ibfk_5` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) 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),
  `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_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;

CREATE TABLE IF NOT EXISTS `pbs_passwordrecovery` (
  `personID` int(11) NOT NULL,
  `recoveryID` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `pbs_passwordrecovery`
  ADD CONSTRAINT `pbs_passwordrecovery_ibfk_1` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`) ON DELETE CASCADE;

 
CREATE TABLE IF NOT EXISTS `pbs_certificate` (
  `certificateID` int(11) NOT NULL AUTO_INCREMENT,
  `personID` int(11),
  `serial` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `issuedate` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  `expiredate` varchar(14) COLLATE utf8_unicode_ci NOT NULL,
  `issuerdn` varchar(1024) COLLATE utf8_unicode_ci NOT NULL,
  `guestcert` tinyint(1) NOT NULL,
  `revoked` tinyint(1) NOT NULL,
  PRIMARY KEY (`certificateID`),
  KEY `personID` (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `pbs_certificates`
  ADD CONSTRAINT `pbs_certificates_ibfk_1` FOREIGN KEY (`personID`) REFERENCES `pbs_person` (`personID`); 
  
  
-- Insert Filtertypes
INSERT INTO `pbs_filtertype` (`filtertypeID`, `filtertypename`) VALUES
(1, 'IP'),
(2, 'MAC'),
(3, 'PoolID'),
(4, 'BootIso'),
(5, 'Membership'),
(6, 'All Members'),
(7, 'Time'),
(8, 'Client'),
(9, 'Hardwarehash'),
(10, 'Weekday'),
(11, 'Date');

-- Adding hometype
INSERT INTO `pbs_hometype` (`hometypeID`, `name`) VALUES
(0, 'standard'),
(1, 'usbstick'),
(2, 'custom path');

-- Adding preboottype
INSERT INTO `pbs_preboottype` (`preboottypeID`, `name`) VALUES
(0, 'usb'),
(1, 'iso');

-- Insert rightcategorys
INSERT INTO `pbs_rightcategory` (`rightcategoryID`, `title`) VALUES
(1, 'Person'),
(2, 'Group'),
(3, 'Role'),
(4, 'BootMedia'),
(5, 'Preboot'),
(6, 'Config'),
(7, 'BootOs'),
(8, 'BootMenu'),
(9, 'Client'),
(10, 'Pool'),
(11, 'Filter'),
(12, 'Session');

-- Insert rights
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES 
	-- Person
	-- Personenübersicht anzeigen
(NULL, '1', 'pso', 'Show overview of persons', NULL),
	-- Eigene Personendetails ändern
(NULL, '1', 'peod', 'Edit own profiledetails', NULL),
	-- Eigene Personendetails anzeigen
(NULL, '1', 'psod', 'XXX - Show own profiledetails', NULL),
	-- Eigene Identität vom System löschen
(NULL, '1', 'pdo', 'Delete own account', NULL),
	-- Person löschen
(NULL, '1', 'pd', 'Delete account of other', NULL),
	-- Personendetails anderer anzeigen
(NULL, '1', 'psood', 'Show other profiledetails', NULL),
	-- Personendetails anderer ändern
(NULL, '1', 'peoa', 'Edit account from other', NULL),
	-- Person sperren
(NULL, '1', 'psa', 'Suspend account', NULL),


	-- Gruppen
	-- Gruppenübersicht anzeigen
(NULL, '2', 'gso', 'Show overview of groups', NULL),
	-- Gruppendetails der eigenen Gruppe anzeigen
(NULL, '2', 'gsdo', 'Show details of own group', NULL),
	-- Gruppendetails anderer Gruppen anzeigen
(NULL, '2', 'gsdog', 'Show details of other group', NULL),
	-- Mitglieder einer Gruppe Anzeigen
(NULL, '2', 'gsmg', 'Show members of other group', NULL),
	-- Gruppe erstellen
(NULL, '2', 'gc', 'Create group', NULL),
	-- Gruppe löschen	
(NULL, '2', 'gd', 'Delete group', NULL),
	-- Gruppe mit allen Untergruppen löschen
(NULL, '2', 'gdlwc', 'Delete group with all child groups', NULL),
	-- Gruppenanfragen stellen
(NULL, '2', 'grm', 'XXX - Request membership', NULL),
	-- Gruppenanfragen akzeptieren
(NULL, '2', 'gam', 'Accept membership request', NULL),
	-- Gruppenanfragen ablehnen	
(NULL, '2', 'gdm', 'Decline membership request', NULL),
	-- Gruppe verlassen	
(NULL, '2', 'gl', 'Leave Group', NULL),
	-- Mitgliedschaften aus eigenen Gruppen entfernen
(NULL, '2', 'gdmo', 'Delete membership in own group', NULL),
	-- Mitgliedschaften aus anderen Gruppen entfernen
(NULL, '2', 'gdmog', 'Delete membership in other group', NULL),
	-- Gruppenmitgliedschaft sperren
(NULL, '2', 'gsm', 'Suspend membership', NULL),

	-- Rollen
	-- Zeige Übersicht
(NULL, '3', 'ro', 'Show overview of roles', NULL),
	-- Neue Rollen anlegen
(NULL, '3', 'ra', 'Add role', NULL),
	-- Rollen anzeigen
(NULL, '3', 'rdd', 'Display details of role', NULL),
	-- Rollen löschen
(NULL, '3', 'rd', 'Delete role', NULL),
	-- Rollen editieren
(NULL, '3', 're', 'Edit role', NULL),
	-- Rollen von Personen ändern
(NULL, '3', 'rcr', 'Change role of membership', NULL),
	-- Recht zu Rolle hinzufügen
(NULL, '3', 'rar', 'Add right to role', NULL),
	-- Recht von Rolle löschen
(NULL, '3', 'rrr', 'Remove right of role', NULL),
	-- Rolle vererbbar machen
(NULL, '3', 'rir', 'Inherit role', NULL),

	-- BootMedien
	-- BootMedien Admin-Übersicht anzeigen
(NULL, '4', 'bai', 'Show admin interface of BootMedia', NULL),
	-- BootMedien User-Übersicht anzeigen
(NULL, '4', 'bui', 'Show user interface of BootMedia', NULL),
	-- BootMedien anlegen
(NULL, '4', 'bc', 'Create new BootMedia', NULL),
	-- BootMedien editieren
(NULL, '4', 'be', 'Edit BootMedia', NULL),
	-- BootMedien löschen
(NULL, '4', 'bd', 'Delete BootMedia', NULL),
	-- BootMedien herunterladen
(NULL, '4', 'bdld', 'Download BootMedia', NULL),
	-- BootMedien-Metadata (Description, Title) ändern
(NULL, '4', 'bem', 'Edit metadata (Description, Title) of a BootMedia', NULL),

	-- Preboot
	-- Preboot übersicht anzeigen
(NULL, '5', 'pro', 'Show overview of preboots', NULL),
	-- Preboot anlegen
(NULL, '5', 'prc', 'Create preboot', NULL),
	-- Preboot editieren
(NULL, '5', 'pre', 'Edit preboot', NULL),
	-- Preboot löschen
(NULL, '5', 'prd', 'Delete preboot', NULL),
	-- Preboot updaten
(NULL, '5', 'pru', 'Update preboot', NULL),
	-- Preboot-Metadata (Description, Title) ändern
(NULL, '5', 'prem', 'Edit metadata (Description, Title) of a preboot', NULL),

	-- Config
	-- Config Admin-Übersicht anzeigen
(NULL, '6', 'csai', 'Show admin overview of config', NULL),
	-- Config User-Übersicht anzeigen
(NULL, '6', 'csui', 'Show user overview of config', NULL),
	-- Config anlegen
(NULL, '6', 'cc', 'Create config', NULL),
	-- Config editieren
(NULL, '6', 'ce', 'Edit config', NULL),
	-- Config löschen
(NULL, '6', 'cd', 'Delete config', NULL),
	-- Eigene Config anlegen
(NULL, '6', 'cco', 'Create own config', NULL),
	-- Eigene Config editieren
(NULL, '6', 'ceo', 'Edit own config', NULL),
	-- Eigene Config löschen
(NULL, '6', 'cdo', 'Delete own config', NULL),
	-- Config updaten
(NULL, '6', 'cu', 'Update config', NULL),
	-- Config-Metadata (Description, Title) ändern
(NULL, '6', 'cem', 'Edit metadata (Description, Title) of config', NULL),

	-- BootOs
	-- BootOs anlegen
(NULL, '7', 'boc', 'Create BootOs', NULL),
	-- BootOS Admin-Übersicht anzeigen
(NULL, '7', 'boai', 'Show admin interface of BootOs', NULL),
	-- BootOs User-Übersicht anzeigen
(NULL, '7', 'boui', 'Show user interface of BootOs', NULL),
	-- BootOs editieren
(NULL, '7', 'boe', 'Edit BootOs', NULL),
	-- BootOs löschen
(NULL, '7', 'bod', 'Delete BootOs', NULL),
	-- BootOs updaten
(NULL, '7', 'bou', 'Update BootOs', NULL),
	-- BootOs-Metadata (Description, Title) ändern
(NULL, '7', 'boem', 'Edit metadata (Description, Title) of BootOs', NULL),

	-- BootMenu
	-- BootMenu Admin-Übersicht anzeigen
(NULL, '8', 'booai', 'Show admin interface of BootMenu', NULL),
	-- User-BootMenu anzeigen
(NULL, '8', 'booui', 'Show user interface of BootMenu', NULL),
	-- Defautl Bootmenu setzen
(NULL, '8', 'boodbm', 'Set default BootMenu', NULL),
	-- BootMenu anlegen
(NULL, '8', 'booc', 'Create BootMenu', NULL),
	-- BootMenu löschen
(NULL, '8', 'bood', 'Delete BootMenu', NULL),
	-- Bootmenu editieren
(NULL, '8', 'booe', 'Edit BootMenu', NULL),
	-- BootmenuEintrag löschen
(NULL, '8', 'boode', 'Delete entry of BootMenu', NULL),
	-- BootmenuEintrag hinzufügen
(NULL, '8', 'booae', 'Add entry to a BootMenu', NULL),
	-- BootmenuEintrag editieren
(NULL, '8', 'booee', 'Edit an entry of a BootMenu', NULL),
	-- Eigener BootmenuEintrag löschen
(NULL, '8', 'boodeo', 'Delete own entry of BootMenu', NULL),
	-- Eigener BootmenuEintrag hinzufügen
(NULL, '8', 'booaeo', 'Add own entry to a BootMenu', NULL),
	-- Eigener BootmenuEintrag editieren
(NULL, '8', 'booeeo', 'Edit  own entry of a BootMenu', NULL),
	-- BootmenuEintrag-Metadata (Description, Title) ändern
(NULL, '8', 'booeem', 'Edit metadata (Description, Title) of an entry of a BootMenu', NULL),

	-- Client
	-- Client Übersicht ansehen
(NULL, '9', 'clo', 'Show overview of Clients', NULL),
	-- Clients manuell anlegen
(NULL, '9', 'cla', 'Add new Clients', NULL),
	-- Clients bearbeiten
(NULL, '9', 'cle', 'Edit Clients', NULL),
	-- Clients löschen
(NULL, '9', 'cld', 'Delete Clients', NULL),

	-- Pool
	-- Pool Übersicht anzeigen
(NULL, '10', 'poo', 'Show overview of Pool', NULL),
	-- Pools anlegen
(NULL, '10', 'poc', 'Create new Pool', NULL),
	-- Pools bearbeiten
(NULL, '10', 'poe', 'Edit Pool', NULL),
	-- Pools löschen
(NULL, '10', 'pod', 'Delete Pool', NULL),
	-- Clients aus Pool entfernen
(NULL, '10', 'pouc', 'Unlink client to pool', NULL),
	-- Clients zu Pool hinzufügen
(NULL, '10', 'polc', 'Link client to pool', NULL),
	-- Übersicht an nicht zugewiesene Clients anzeigen
(NULL, '10', 'posuc', 'Show unlinked Clients', NULL),

	-- Filter
	-- Filter Übersicht ansehen
(NULL, '11', 'fo', 'Show overview of Filter', NULL),
	-- Filter anlegen
(NULL, '11', 'fa', 'Create new Filter', NULL),
	-- Filter bearbeiten
(NULL, '11', 'fe', 'Edit Filter', NULL),
	-- Filterpriorität ändern
(NULL, '11', 'fefp', 'Edit Filterpriority', NULL),
	-- Filter löschen
(NULL, '11', 'fd', 'Delete Filter', NULL),
	-- FilterEinträge hinzufügen
(NULL, '11', 'ffa', 'Add additional Filterentry to a Filter', NULL),
	-- FilterEinträge editieren
(NULL, '11', 'ffe', 'Edit Filterentry of a Filter', NULL),
	-- FilterEinträge löschen
(NULL, '11', 'ffd', 'Delete Filterentry of a Filter', NULL),

	-- Session
	-- Session Übersicht ansehen
(NULL, '12', 'so', 'Show overview of Session', NULL);

INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'geo', 'Edit own group', NULL);
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'ge', 'Edit other group', NULL);
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'glk', 'Link groups', NULL);
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'grog', 'Overview of own group requests', NULL);
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'grotg', 'Overview of other group requests', NULL);
INSERT INTO `pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'gsmgo', 'Show members of own group', NULL);

-- Adding Super-User
INSERT INTO `pbs_group` (`groupID` ,`title` ,`description`)VALUES 
(1, 'OpenSLX', 'This is the OpenSLX-Group');
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
	(1, '1', 'SuperAdmin', 'The SuperAdmin', 0);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(1, 1),(1, 2),(1, 3),(1, 4),(1, 5),(1, 6),(1, 7),(1, 8),(1, 9),(1, 10),(1, 11),(1, 12),(1, 13),(1, 14),(1, 15),(1, 16),(1, 17),(1, 18),(1, 19),(1, 20),(1, 21),(1, 22),(1, 23),(1, 24),(1, 25),(1, 26),(1, 27),(1, 28),(1, 29),(1, 30),(1, 31),(1, 32),(1, 33),(1, 34),(1, 35),(1, 36),(1, 37),(1, 38),(1, 39),(1, 40),(1, 41),(1, 42),(1, 43),(1, 44),(1, 45),(1, 46),(1, 47),(1, 48),(1, 49),(1, 50),(1, 51),(1, 52),(1, 53),(1, 54),(1, 55),(1, 56),(1, 57),(1, 58),(1, 59),(1, 60),(1, 61),(1, 62),(1, 63),(1, 64),(1, 65),(1, 66),(1, 67),(1, 68),(1, 69),(1, 70),(1, 71),(1, 72),(1, 73),(1, 74),(1, 75),(1, 76),(1, 77),(1, 78),(1, 79),(1, 80),(1, 81),(1, 82),(1, 83),(1, 84),(1, 85),(1, 86),(1, 87),(1, 88),(1, 89),(1, 90),(1, 91),(1, 92),(1, 93),(1, 94),(1, 95),(1, 96),(1, 97),(1, 98),(1, 99),(1, 100);

-- Adding Community-Admin
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
(2, 1, 'Community-Admin', 'This role can observe and edit all group, person and role specific details', 1);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(2, 1),(2, 2),(2, 3),(2, 4),(2, 5),(2, 6),(2, 7),(2, 8),(2, 9),(2, 10),(2, 11),(2, 12),(2, 13),(2, 14),(2, 15),(2, 16),(2, 17),(2, 18),(2, 19),(2, 20),(2, 21),(2, 22),(2, 23),(2, 24),(2, 25),(2, 26),(2, 27),(2, 28),(2, 29),(2, 30),(2, 31),(2, 33),(2, 37),(2, 46),(2, 50),(2, 51),(2, 52),(2, 57),(2, 63),(2, 70),(2, 71),(2, 72),(2, 73),(2, 95),(2, 96),(2, 97),(2, 98),(2, 99),(2, 100);

-- Adding Monitor-Admin
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
(3, 1, 'Monitor-Admin', 'This role can monitor all details of the pbs-server', 1);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(3, 1),(3, 9),(3, 10),(3, 11),(3, 12),(3, 16),(3, 23),(3, 25),(3, 32),(3, 39),(3, 45),(3, 50),(3, 51),(3, 52),(3, 56),(3, 62),(3, 71),(3, 72),(3, 73),(3, 75),(3, 79),(3, 86),(3, 94);

-- Adding Default-Admin
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
(4, 1, 'Admin', 'This role is the defautl Admin', 1);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(4, 2),(4, 3),(4, 4),(4, 10),(4, 12),(4, 13),(4, 16),(4, 17),(4, 18),(4, 19),(4, 20),(4, 22),(4, 23),(4, 24),(4, 25),(4, 26),(4, 28),(4, 29),(4, 30),(4, 31),(4, 32),(4, 34),(4, 35),(4, 36),(4, 37),(4, 39),(4, 40),(4, 41),(4, 42),(4, 43),(4, 45),(4, 47),(4, 48),(4, 49),(4, 50),(4, 51),(4, 52),(4, 53),(4, 55),(4, 56),(4, 58),(4, 59),(4, 60),(4, 62),(4, 64),(4, 65),(4, 66),(4, 67),(4, 68),(4, 69),(4, 70),(4, 71),(4, 72),(4, 73),(4, 75),(4, 76),(4, 77),(4, 78),(4, 79),(4, 80),(4, 81),(4, 82),(4, 83),(4, 84),(4, 85),(4, 86),(4, 87),(4, 88),(4, 89),(4, 90),(4, 91),(4, 92),(4, 93),(4, 94);

-- Adding Meta-Admin
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
(5, 1, 'Meta-Admin', 'This role can edit all metadata from entrys', 1);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(5, 1),(5, 2),(5, 3),(5, 4),(5, 32),(5, 38),(5, 39),(5, 44),(5, 45),(5, 50),(5, 51),(5, 52),(5, 54),(5, 55),(5, 61),(5, 62),(5, 71),(5, 72),(5, 73),(5, 74),(5, 75),(5, 86),(5, 89);

-- Adding User
INSERT INTO `pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES 
(6, 1, 'User', 'The default user', 1);
INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES
(6, 2),(6, 3),(6, 4),(6, 16),(6, 19),(6, 33),(6, 37),(6, 46),(6, 50),(6, 51),(6, 52),(6, 57),(6, 63),(6, 70),(6, 71),(6, 72);

-- Adding a Person for SuperAdmin
INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`, `loginpassword`) VALUES
(1, 'Herr', 'Super', 'Admin', 'Street', '1337', 'Teshouse', '1337', NULL, '1299612370', 'test', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a', '$6$Do9tGnw0$9ndoxsmcpNV.9mFTBRB7u2RbWekbSfjnUfPKXrCUEpXrZXoqnOesXITGl.RDy0cuaYB1Ouob6WtNWQqU/M/4U.');

-- Get him a Membership in MainGroup for activate Requests
INSERT INTO `pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (NULL, '1', '1', '1', 'apikey1');