From 765bcb53aa178808c2eec5502aa5387ba6e1bf68 Mon Sep 17 00:00:00 2001 From: Simon Date: Tue, 19 Apr 2011 15:14:17 +0200 Subject: Aufgeräumt und verschoben --- setup/pbs-newdata.sql | 183 ++++++++++++++ setup/pbs.sql | 647 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 830 insertions(+) create mode 100644 setup/pbs-newdata.sql create mode 100644 setup/pbs.sql (limited to 'setup') diff --git a/setup/pbs-newdata.sql b/setup/pbs-newdata.sql new file mode 100644 index 0000000..0c1fffb --- /dev/null +++ b/setup/pbs-newdata.sql @@ -0,0 +1,183 @@ +USE pbs; +SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; + +-- Adding person test//test +INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`) VALUES +(1, 'Herr', 'Test 1', 'Test 1', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a'); +-- Adding person test2//test +INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`) VALUES +(2, 'Herr', 'Test 2', 'Test 2', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test2', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a'); +-- Adding person test3//test +INSERT INTO `pbs_person` (`personID`, `title`, `name`, `firstname`, `street`, `housenumber`, `city`, `postalcode`, `logindate`, `registerdate`, `email`, `login`, `password`, `password_salt`) VALUES +(3, 'Herr', 'Test 3', 'Test 3', 'Teststr,', '5', 'Testburg', '1337', NULL, '1299612370', 'test3', NULL, '4207acba08cadccc397e2302a55b339a', 'f21ee663b17bcefc6868694dffda602a'); + +-- Adding group +INSERT INTO `pbs`.`pbs_group` (`groupID` ,`title` ,`description`)VALUES +(2, 'Group 2', 'Second Group'), +(3, 'Group 3', 'Third Group'), +(4, 'Group 4', 'Fourth Group'), +(5, 'Group 5', 'Fifth Group'), +(6, 'Group 6', 'Sixth Group'), +(7, 'Group 7', 'Seventh Group'), +(8, 'Group 8', 'Eight Group'), +(9, 'Group 9', 'Nineth Group'), +(10, 'Group 10', 'Tenth Group'); + +INSERT INTO `pbs`.`pbs_groupgroups` (`parentID`, `groupID`) VALUES +(1, 2), +(1, 3), +(2, 4), +(2, 5), +(1, 8), +(3, 6), +(3, 7), +(3, 9), +(3, 10); + +-- Adding role +INSERT INTO `pbs`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES (7, '2', 'Role 1-Group2', NULL, 0); +INSERT INTO `pbs`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES (8, '2', 'Role 2-Group2', NULL, 0); +INSERT INTO `pbs`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES (9, '2', 'Role 3-Group2', NULL, 0); + +INSERT INTO `pbs_rightroles` (`roleID`, `rightID`) VALUES +(7, 4), +(7, 3), +(8, 1), +(9, 2); + +-- Adding memberships +INSERT INTO `pbs`.`pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (1, '1', '1', '1', 'apikey1'); +INSERT INTO `pbs`.`pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (2, '1', '2', '2', 'apikey2'); +INSERT INTO `pbs`.`pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (3, '2', '3', '1', 'apikey3'); +INSERT INTO `pbs`.`pbs_membership` (`membershipID`, `groupID`, `roleID`, `personID`, `apikey`) VALUES (4, '2', '4', '3', 'apikey4'); + +-- Adding clients +INSERT INTO `pbs_client` (`clientID`, `groupID`,`macadress`, `hardwarehash`) VALUES +(1, 1, '00:00:00:00:00:10', 'ea9b82d9de911bc2d3cd23f53a6cab48'), +(2, 1, '00:00:00:00:10:00', '1e2b1599710fbbef0dc789e8cfe12455'), +(3, 1, '00:00:00:10:10:00', '8f6209ca3d6b35e223a11c249d1b69fc'), +(4, 1, '00:00:10:00:00:00', 'e17ab09f3586464f19629e2e8b1e9a9d'), +(5, 1, '00:10:00:00:00:00', '9bf70279d283b85440c2031c19bb6812'), +(6, 1, '10:00:00:00:00:00', 'ad3bce4464a6267441ec144744439c7e'), +(7, 1, '00:55:00:55:00:55', 'e8d7e80d79f224771b7a3a0af4e02748'), +(8, 1, '66:00:66:00:66:00', 'ded66ce272f384e9e386c1b57ded3e4d'), +(9, 1, '00:ff:ff:ff:ff:ff', '695610ee509c060b1fca9c8011529af4'), +(10, 1, '00:22:00:22:00:22', 'a3562c8cad2a4fa4fc11656025dc911b'), +(11, 2, 'af:54:07:87:63:44', '98413218152196816519841365419816'), +(12, 2, '87:21:74:52:96:20', '98741298132516132169813516981616'), +(13, 2, '14:47:58:47:36:48', '32168132068132068513216053516513'), +(14, 1, '64:46:85:A1:89:23', '9684216842068420616841asd6516984'); + +-- Adding config +INSERT INTO `pbs_config` (`configID`, `title`, `groupID`, `membershipID`, `visible`, `created`) VALUES +(1, 'Config 1', 1, null, '1', '1299693677'), +(2, 'Config 2', 1, null, '0', '1299693690'), +(3, 'Config 3', 1, null, '1', '1299693690'), +(4, 'Config 4', null, 1, '1', '1299693690'), +(5, 'Config 5', null, 1, '1', '1299693690'); + +-- Adding bootosuser +INSERT INTO `pbs_bootosuser` (`bootosuserID`, `configID`, `login`, `password`, `homepath`, `hometypeID`) VALUES +(1, 1, 'login1', 'password1', null, 0), +(2, 1, 'login2', 'password2', null, 1), +(3, 1, 'login3', 'password3', '/home/bla/blub', 2), +(4, 4, 'login4', 'password4', null, 0), +(5, 4, 'login5', 'password5', null, 1); + +-- Adding hometype +INSERT INTO `pbs_hometype` (`hometypeID`, `name`) VALUES +(0, 'standard'), +(1, 'usbstick'), +(2, 'custom path'), +(3, 'extern'), +(4, 'dropbox'); + +-- Adding bootos +INSERT INTO `pbs_bootos` (`bootosID`, `path_config`, `groupID`, `membershipID`, `title`, `description`, `path_init`, `path_kernel`, `defaultkcl`, `created`, `expires`, `public`, `source`, `distro`, `distroversion`, `shortname`, `share`) VALUES +(1, 'configpath 1', 1, 1, 'BootOs 1', 'Description 1', 'initpath 1', 'kernelpath 1', 'default kcl 1', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(2, 'configpath 2', 1, 1, 'BootOs 2', 'Description 2', 'initpath 2', 'kernelpath 2', 'default kcl 2', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(3, 'configpath 3', 1, 1, 'BootOs 3', 'Description 3', 'initpath 3', 'kernelpath 3', 'default kcl 3', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(4, 'configpath 4', 1, 1, 'BootOs 4', 'Description 4', 'initpath 4', 'kernelpath 4', 'default kcl 4', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(5, 'configpath 5', 1, 1, 'BootOs 5', 'Description 5', 'initpath 5', 'kernelpath 5', 'default kcl 5', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(6, 'configpath 6', 1, 1, 'BootOs 6', 'Description 6', 'initpath 6', 'kernelpath 6', 'default kcl 6', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(7, 'configpath 7', 1, 1, 'BootOs 7', 'Description 7', 'initpath 7', 'kernelpath 7', 'default kcl 7', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(8, 'configpath 8', 1, 1, 'BootOs 8', 'Description 8', 'initpath 8', 'kernelpath 8', 'default kcl 8', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(9, 'configpath 9', 1, 1, 'BootOs 9', 'Description 9', 'initpath 9', 'kernelpath 9', 'default kcl 9', '1299693782', '2012-03-09', 0, null, null, null, null, null), +(10, 'configpath 10', 1, 1, 'BootOs 10', 'Description 10', 'initpath 10', 'kernelpath 10', 'default kcl 10', '1299693782', '2012-03-09', 0, null, null, null, null, null); + + +-- Adding preboot +INSERT INTO `pbs_preboot` (`prebootID`, `title`, `membershipID`, `groupID`, `path_preboot`) VALUES +(1, 'Preboot 1', 1, 1, 'path 1'), +(2, 'Preboot 2', 1, 1, 'path 2'), +(3, 'Preboot 3', 1, 1, 'path 3'); + +-- Adding bootiso +INSERT INTO `pbs_bootiso` (`bootisoID`, `title`, `membershipID`, `groupID`, `prebootID`, `serialnumber`, `created`, `expires`, `public`) VALUES +(1, 'BootIso 1', 1, 1, '1', '10-20-30-40-50', '123', '2012-03-09', 0), +(2, 'BootIso 2', 1, 1, '2', '11-20-30-40-50', '456', '2012-03-09', 0), +(3, 'BootIso 3', 1, 1, '3', '12-20-30-40-50', '789', '2012-03-09', 0); + +-- Adding Pools +INSERT INTO `pbs_pool` (`poolID`, `groupID`, `title`, `description`, `location`) VALUES +(1, 1, 'Pool 1', 'Description 1', 'Keller'), +(2, 1, 'Pool 2', 'Description 2', 'EG'), +(3, 1, 'Pool 3', 'Description 3', '1 OG'), +(4, 1, 'Pool 4', 'Description 4', '2 OG'); + +-- Adding Bootmenus +INSERT INTO `pbs_bootmenu` (`bootmenuID`, `membershipID`, `groupID`, `title`, `created`,`defaultbootmenu`) VALUES +(1, NULL, 1, 'Bootmenu 1', '1299694422', '0'), +(2, NULL, 1, 'Bootmenu 2', '1299694433', '0'), +(3, NULL, 1, 'Bootmenu 3', '1299694441', '0'), +(4, NULL, 1, 'Bootmenu 4', '1299694454', '1'), +(5, NULL, 1, 'Bootmenu 5', '1299694461', '0'); + +-- Adding BootOs to BootMenus +INSERT INTO `pbs_bootmenuentries` (`bootmenuentriesID`, `bootosID`, `bootmenuID`, `title`, `kcl`, `kclappend`, `configID`, `order`) VALUES +(1, 5, 1, '123', '1', 'abc', 1, 0), +(2, 2, 1, 'menu2', '0', '123456', 1, 1), +(3, 6, 1, 'Mein OS 6', '1', 'appending', 1, 2), +(4, 10, 2, 'Das OS 10', '1', 'append', 1, 0), +(5, 9, 5, 'Bootos 9', '1', 'a', 1, 0), +(6, 1, 4, 'booting 1', '0', 'aphabeta', 1, 0), +(7, 6, 3, 'blablabla', '0', '11', 1, 0); + +-- Adding client to pools (poolentries) +INSERT INTO `pbs_poolentries` (`poolentriesID`, `poolID`, `clientID`) VALUES +(1, 1, 1), +(2, 1, 2), +(3, 1, 3), +(4, 1, 4), +(5, 2, 5), +(6, 2, 6), +(7, 3, 7), +(8, 3, 8), +(9, 4, 9), +(10, 4, 10); + +-- Adding Sessions +INSERT INTO `pbs_session` (`sessionID`, `alphasessionID`,`clientID`, `bootmenuentryID`,`bootosID`, `bootisoID`, `time`, `ip`, `ip6`) VALUES +(1, 'a', 1, NULL, NULL, 1, '1299684000', '132.230.5.6', ''), +(2, 'b', 9, NULL, NULL, 2, '1299684000', '132.230.10.2', ''), +(3, 'c', 10, NULL, NULL, 1, '1299648000', '132.20.20.2', ''), +(4, 'd', 7, NULL, NULL, 3, '1299676800', '84.23.56.86', ''), +(5, 'e', 4, NULL, NULL, 3, '1299674340', '70.81.94.222', ''), +(6, 'f', 5, NULL, NULL, 3, '1299683100', '210.84.65.2', ''); + +-- Adding Filter +INSERT INTO `pbs_filter` (`filterID`, `membershipID`, `groupID`, `bootmenuID`, `title`, `description`, `created`, `priority`) VALUES +(1, NULL, 1, 1, 'Filter 1', 'Innerhalb der Uni', '1300022113', 1), +(2, NULL, 1, 2, 'Filter 2', 'Ausserhalb der Uni', '1302000113', 1), +(3, NULL, 1, 3, 'Filter 3', 'Montags-Freitag \r\nvon 8 bis 16 Uhr \r\nInnerhalb der Uni', '1302022113', 10); + +-- Adding Filterentries +INSERT INTO `pbs_filterentries` (`filterentriesID`, `filterID`, `filtertypeID`, `filtervalue`, `filtervalue2`) VALUES +(1, 1, 6, '1', NULL), +(2, 1, 1, '132.230.001.001', '132.230.255.255'), +(3, 2, 6, '1', NULL), +(4, 2, 1, '000.000.000.000', '132.229.255.255'), +(5, 2, 1, '132.231.001.001', '255.255.255.255'), +(6, 3, 6, '1', NULL), +(7, 3, 7, '08:00', '16:00'), +(8, 3, 10, '1', '5'); diff --git a/setup/pbs.sql b/setup/pbs.sql new file mode 100644 index 0000000..ebcdbef --- /dev/null +++ b/setup/pbs.sql @@ -0,0 +1,647 @@ +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, + `suspended` 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, + `suspended` 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_config` ( + `configID` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(30) NOT NULL, + `groupID` int(11), + `membershipID` int(11), + `visible` tinyint(1) DEFAULT '0', + `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_hometype` ( + `hometypeID` int(11) NOT NULL AUTO_INCREMENT, + `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_bootosuser` ( + `bootosuserID` int(11) NOT NULL AUTO_INCREMENT, + `configID` int(11) NOT NULL, + `login` varchar(30) NOT NULL, + `password` varchar(30) 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_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), + `path_init` varchar(140), + `path_kernel` varchar(140), + `path_config` varchar(140), + `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_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 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, + `membershipID` int(11), + `groupID` int(11) NOT NULL, + `path_preboot` varchar(140) 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` 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), + 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), + `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'); + +-- 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`.`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`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'geo', 'Edit own group', NULL); +INSERT INTO `pbs`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'ge', 'Edit other group', NULL); +INSERT INTO `pbs`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'glk', 'Link groups', NULL); +INSERT INTO `pbs`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'grog', 'Overview of own group requests', NULL); +INSERT INTO `pbs`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'grotg', 'Overview of other group requests', NULL); +INSERT INTO `pbs`.`pbs_right` (`rightID`, `rightcategoryID`, `shortcut`, `title`, `description`) VALUES (NULL, '2', 'gsmgo', 'Show members of own group', NULL); + +-- Adding Super-User +INSERT INTO `pbs`.`pbs_group` (`groupID` ,`title` ,`description`)VALUES +(1, 'OpenSLX', 'This is the OpenSLX-Group'); +INSERT INTO `pbs`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES + (1, '1', 'SuperAdmin', 'The SuperAdmin', 0); +INSERT INTO `pbs`.`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`.`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`.`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`.`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`.`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`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES +(4, 1, 'Admin', 'This role is the defautl Admin', 1); +INSERT INTO `pbs`.`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`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES +(5, 1, 'Meta-Admin', 'This role can edit all metadata from entrys', 1); +INSERT INTO `pbs`.`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`.`pbs_role` (`roleID`, `groupID`, `title`, `description`, `inheritance`) VALUES +(6, 1, 'User', 'The default user', 1); +INSERT INTO `pbs`.`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); -- cgit v1.2.3-55-g7522 From 813d54a40e3908c12218f33e8b81085d890f1a40 Mon Sep 17 00:00:00 2001 From: Simon Date: Wed, 20 Apr 2011 10:17:21 +0200 Subject: Umbennennung der Beispieldaten von BootIso zu BootMedium --- setup/pbs-newdata.sql | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'setup') diff --git a/setup/pbs-newdata.sql b/setup/pbs-newdata.sql index 0c1fffb..18c197a 100644 --- a/setup/pbs-newdata.sql +++ b/setup/pbs-newdata.sql @@ -114,9 +114,9 @@ INSERT INTO `pbs_preboot` (`prebootID`, `title`, `membershipID`, `groupID`, `pat -- Adding bootiso INSERT INTO `pbs_bootiso` (`bootisoID`, `title`, `membershipID`, `groupID`, `prebootID`, `serialnumber`, `created`, `expires`, `public`) VALUES -(1, 'BootIso 1', 1, 1, '1', '10-20-30-40-50', '123', '2012-03-09', 0), -(2, 'BootIso 2', 1, 1, '2', '11-20-30-40-50', '456', '2012-03-09', 0), -(3, 'BootIso 3', 1, 1, '3', '12-20-30-40-50', '789', '2012-03-09', 0); +(1, 'BootMedium 1', 1, 1, '1', '10-20-30-40-50', '123', '2012-03-09', 0), +(2, 'BootMedium 2', 1, 1, '2', '11-20-30-40-50', '456', '2012-03-09', 0), +(3, 'BootMedium 3', 1, 1, '3', '12-20-30-40-50', '789', '2012-03-09', 0); -- Adding Pools INSERT INTO `pbs_pool` (`poolID`, `groupID`, `title`, `description`, `location`) VALUES -- cgit v1.2.3-55-g7522 From 624cbcae5c9b5bcedf03c77460b07eb20fbb2366 Mon Sep 17 00:00:00 2001 From: Simon Date: Wed, 20 Apr 2011 13:08:33 +0200 Subject: Automatischer Start nach x Sekunden hinzugefügt --- application/controllers/ErrorController.php | 4 +- application/models/BootMenu.php | 10 ++++ application/models/BootMenuMapper.php | 26 ++++++++-- .../modules/fbgui/controllers/IndexController.php | 1 + .../modules/fbgui/views/scripts/index/index.phtml | 55 +++++++++++++++++++++- .../user/controllers/BootmenuController.php | 2 +- application/modules/user/forms/Bootmenu.php | 8 ++++ .../user/views/scripts/bootmenu/index.phtml | 3 +- setup/pbs.sql | 2 + 9 files changed, 101 insertions(+), 10 deletions(-) (limited to 'setup') diff --git a/application/controllers/ErrorController.php b/application/controllers/ErrorController.php index efee141..abf3ec1 100644 --- a/application/controllers/ErrorController.php +++ b/application/controllers/ErrorController.php @@ -26,13 +26,13 @@ class ErrorController extends Zend_Controller_Action $this->getResponse()->setHttpResponseCode(404); $this->view->message = 'Page not found'; $this->_redirect('/user/'); - #print_a($errors); + # print_a($errors); break; default: // application error $this->getResponse()->setHttpResponseCode(500); $this->view->message = 'Application error - Please contact the admin'; - #print_a($errors); + # print_a($errors); break; } diff --git a/application/models/BootMenu.php b/application/models/BootMenu.php index 82e771a..dae3776 100644 --- a/application/models/BootMenu.php +++ b/application/models/BootMenu.php @@ -16,6 +16,7 @@ class Application_Model_BootMenu protected $_membershipID; protected $_groupID; protected $_title; + protected $_startcounter; protected $_created; protected $_defaultbootmenu; @@ -96,6 +97,15 @@ class Application_Model_BootMenu { return $this->_created; } + public function setStartcounter($_startcounter) + { + $this->_startcounter = $_startcounter; + return $this; + } + public function getStartcounter() + { + return $this->_startcounter; + } public function setCreated($_created) { $this->_created = $_created; diff --git a/application/models/BootMenuMapper.php b/application/models/BootMenuMapper.php index 8b39a5a..6ba72bd 100644 --- a/application/models/BootMenuMapper.php +++ b/application/models/BootMenuMapper.php @@ -84,7 +84,13 @@ class Application_Model_BootMenuMapper public function save(Application_Model_BootMenu $botmenu) { - $data = array('bootmenuID'=> $botmenu->getID() ,'membershipID'=> $botmenu->getMembershipID(), 'groupID'=> $botmenu->getGroupID() ,'title'=> $botmenu->getTitle() ,'created'=> $botmenu->getCreated(), 'defaultbootmenu' => $botmenu->getDefaultbootmenu()); + $data = array('bootmenuID'=> $botmenu->getID() , + 'membershipID'=> $botmenu->getMembershipID(), + 'groupID'=> $botmenu->getGroupID() , + 'title'=> $botmenu->getTitle() , + 'startcounter'=> $botmenu->getStartcounter() , + 'created'=> $botmenu->getCreated(), + 'defaultbootmenu' => $botmenu->getDefaultbootmenu()); if (null === ($id = $botmenu->getID()) ) { unset($data['bootmenuID']); @@ -113,10 +119,22 @@ class Application_Model_BootMenuMapper if($botmenu == null){ $botmenu = new Application_Model_BootMenu(); - $botmenu->setID($row->bootmenuID)->setMembershipID($row->membershipID)->setGroupID($row->groupID)->setTitle($row->title)->setCreated($row->created)->setDefaultbootmenu($row->defaultbootmenu); + $botmenu->setID($row->bootmenuID) + ->setMembershipID($row->membershipID) + ->setGroupID($row->groupID) + ->setTitle($row->title) + ->setStartcounter($row->startcounter) + ->setCreated($row->created) + ->setDefaultbootmenu($row->defaultbootmenu); return $botmenu; }else{ - $botmenu->setID($row->bootmenuID)->setMembershipID($row->membershipID)->setGroupID($row->groupID)->setTitle($row->title)->setCreated($row->created)->setDefaultbootmenu($row->defaultbootmenu); + $botmenu->setID($row->bootmenuID) + ->setMembershipID($row->membershipID) + ->setGroupID($row->groupID) + ->setTitle($row->title) + ->setStartcounter($row->startcounter) + ->setCreated($row->created) + ->setDefaultbootmenu($row->defaultbootmenu); } @@ -129,7 +147,7 @@ class Application_Model_BootMenuMapper foreach ($resultSet as $row) { $entry = new Application_Model_BootMenu(); - $entry->setID($row->bootmenuID)->setMembershipID($row->membershipID)->setGroupID($row->groupID)->setTitle($row->title)->setCreated($row->created)->setDefault($row->default); + $entry->setID($row->bootmenuID)->setMembershipID($row->membershipID)->setGroupID($row->groupID)->setTitle($row->title)->setStartcounter($row->startcounter)->setCreated($row->created)->setDefault($row->default); $entries[] = $entry; } diff --git a/application/modules/fbgui/controllers/IndexController.php b/application/modules/fbgui/controllers/IndexController.php index 17d9337..150f1c5 100644 --- a/application/modules/fbgui/controllers/IndexController.php +++ b/application/modules/fbgui/controllers/IndexController.php @@ -117,6 +117,7 @@ class Fbgui_IndexController extends Zend_Controller_Action $bootmenuMapper = new Application_Model_BootMenuMapper(); $bm = $bootmenuMapper->find($bootmenuID); $this->view->title = $bm->getTitle(); + $this->view->startcounter = $bm->getStartcounter(); $bootmenuentriesMapper = new Application_Model_BootMenuEntriesMapper(); $res = $bootmenuentriesMapper->findBy(array('bootmenuID' => $bootmenuID),false); diff --git a/application/modules/fbgui/views/scripts/index/index.phtml b/application/modules/fbgui/views/scripts/index/index.phtml index 2454d45..8a5f0e6 100644 --- a/application/modules/fbgui/views/scripts/index/index.phtml +++ b/application/modules/fbgui/views/scripts/index/index.phtml @@ -26,6 +26,9 @@ }); $(function() { + $(document).click(function(){ + abortStartcount = true; + }) $(document).keyup(function (event) { if (event.keyCode == 38) { // up @@ -41,9 +44,29 @@ // Down $('#startbutton').click(); } + abortStartcount = true; }); }); + decreaseDown(); }); + function decreaseDown(){ + var act = parseInt($('.startcounter').html()); + if(act == 0){ + start(); + return; + } + var next = act-1; + $('.startcounter').html(next); + if(next == 0){ + start(); + } + if(next > 0 && abortStartcount == false){ + setTimeout('decreaseDown()',1000); + } + if(abortStartcount == true){ + $('.startcounter').parent().fadeOut(); + } + } function start(){ if($('#selectedBootOs').val() != ''){ if($('#selectedBootOs').val() == 'login'){ @@ -79,6 +102,7 @@

title;?>

+ entries) > 0): ?> +
Starting in startcounter; ?> seconds
+
@@ -149,7 +200,7 @@
- +