From b7f360f88bec18ddcde528e3e3400fb869de8c09 Mon Sep 17 00:00:00 2001 From: Michael Neves Date: Thu, 26 Jan 2012 13:21:17 +0100 Subject: Added Preboottype in DB {usb, iso} --- application/modules/user/forms/BootmenuEntries.php | 3 - setup/pbs.sql | 11 + setup/pbs.sql~ | 674 +++++++++++++++++++++ 3 files changed, 685 insertions(+), 3 deletions(-) create mode 100644 setup/pbs.sql~ diff --git a/application/modules/user/forms/BootmenuEntries.php b/application/modules/user/forms/BootmenuEntries.php index ae11d84..a2b861a 100644 --- a/application/modules/user/forms/BootmenuEntries.php +++ b/application/modules/user/forms/BootmenuEntries.php @@ -93,9 +93,6 @@ class user_Form_BootmenuEntries extends Zend_Form { if($_POST['kcl']) { $this->addElement('textarea', 'defaultkcl', array( 'filters' => array('StringTrim'), - 'validators' => array( - array('StringLength', false, array(0, 50)), - ), 'required' => false, 'cols' => 50, 'rows' => 5, diff --git a/setup/pbs.sql b/setup/pbs.sql index 09bbed8..886d8a3 100644 --- a/setup/pbs.sql +++ b/setup/pbs.sql @@ -124,6 +124,12 @@ CREATE TABLE IF NOT EXISTS `pbs_hometype` ( 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, @@ -386,6 +392,11 @@ INSERT INTO `pbs_hometype` (`hometypeID`, `name`) VALUES (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'), diff --git a/setup/pbs.sql~ b/setup/pbs.sql~ new file mode 100644 index 0000000..dcb093f --- /dev/null +++ b/setup/pbs.sql~ @@ -0,0 +1,674 @@ +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_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, + `source` varchar(140), + `created` VARCHAR(14) 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, + `description` varchar(140), + `membershipID` int(11), + `groupID` int(11) NOT NULL, + `prebootID` int(11) NOT NULL, + `serialnumber` varchar(30) NOT NULL UNIQUE, + `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), + `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; + +-- 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'); -- cgit v1.2.3-55-g7522