You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@vcl.apache.org by jf...@apache.org on 2014/09/30 20:09:01 UTC
svn commit: r1628507 - in /vcl/trunk/mysql: update-vcl.sql vcl.sql
Author: jfthomps
Date: Tue Sep 30 18:09:01 2014
New Revision: 1628507
URL: http://svn.apache.org/r1628507
Log:
VCL-174 - NAT - support for sites that have small IP address ranges
VCL-706 - Allow a Connect Method to Specify a Port Range or Port List
VCL-170 - option to power off blades after reservation - new reload module
VCL-636 - Allow vCenter folder to be specified in VM profile
VCL-758 - VCL timings - make various timings to be variables and edited by admin
update-vcl.sql:
-modified DropExistingConstraints, DropExistingIndices, AddUniqueIndex, Add2ColUniqueIndexIfNotExist, and Add3ColUniqueIndexIfNotExist: updated length of several CHAR fields in stored procedures; there were some problems with names getting truncated
-added populateConnectMethodPort - populates connectmethodport, drops connectmethod.port and connectmethod.protocol
-changed ALTER TABLE for computer.predictivemoduleid to AddColumnIfNotExists
-added connectmethodport table
-added DropColumnIfExists for imagemeta.usergroupid
-added DropColumnIfExists for managementnode.predictivemoduleid
-added nathost table
-added natlog table
-added natmap table
-added natport table
-added DropColumnIfExists for vmhost.vmkernalnic
-added DropColumnIfExists for vmhost.vmwaredisk
-added AddColumnIfNotExists for vmprofile.folderpath
-added 'INSERT IGNORE INTO nathost' to populate nathost table with entries from managementnode table
-added INSERT IGNORE's for connectmethodport
-added INSERT IGNORE's for 'Site Configuration...' entries for usergroupprivtype table
vcl.sql:
-moved managementnode.predictivemoduleid to end of table
-removed connectmethod.port and connectmethod.protocol
-added connectmethodport table
-added nathost table
-added natlog table
-added natmap table
-added natport table
-removed port values from INSERTs for connectmethod table
-added INSERTs for 'Site Configuration...' entries for usergroupprivtype table
Modified:
vcl/trunk/mysql/update-vcl.sql
vcl/trunk/mysql/vcl.sql
Modified: vcl/trunk/mysql/update-vcl.sql
URL: http://svn.apache.org/viewvc/vcl/trunk/mysql/update-vcl.sql?rev=1628507&r1=1628506&r2=1628507&view=diff
==============================================================================
--- vcl/trunk/mysql/update-vcl.sql (original)
+++ vcl/trunk/mysql/update-vcl.sql Tue Sep 30 18:09:01 2014
@@ -137,8 +137,8 @@ CREATE PROCEDURE `DropExistingConstraint
)
BEGIN
DECLARE done INT DEFAULT 0;
- DECLARE existing_constraint_name CHAR(16);
- DECLARE database_name CHAR(16);
+ DECLARE existing_constraint_name CHAR(40);
+ DECLARE database_name CHAR(25);
DECLARE select_existing_constraint_names CURSOR FOR
SELECT CONSTRAINT_NAME, TABLE_SCHEMA FROM information_schema.KEY_COLUMN_USAGE WHERE
@@ -177,8 +177,8 @@ CREATE PROCEDURE `DropExistingIndices`(
)
BEGIN
DECLARE done INT DEFAULT 0;
- DECLARE existing_index_name CHAR(16);
- DECLARE database_name CHAR(16);
+ DECLARE existing_index_name CHAR(40);
+ DECLARE database_name CHAR(25);
DECLARE select_existing_index_names CURSOR FOR
SELECT INDEX_NAME, TABLE_SCHEMA FROM information_schema.STATISTICS WHERE
@@ -247,7 +247,7 @@ CREATE PROCEDURE `AddUniqueIndex`(
)
BEGIN
DECLARE done INT DEFAULT 0;
- DECLARE nonunique_index_name CHAR(16);
+ DECLARE nonunique_index_name CHAR(40);
DECLARE select_nonunique_index_names CURSOR FOR
SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE
@@ -491,7 +491,7 @@ CREATE PROCEDURE `Add2ColUniqueIndexIfNo
)
BEGIN
DECLARE done INT DEFAULT 0;
- DECLARE nonunique_index_name CHAR(16);
+ DECLARE nonunique_index_name CHAR(40);
DECLARE select_index_names CURSOR FOR
SELECT i1.INDEX_NAME FROM information_schema.STATISTICS i1
@@ -562,7 +562,7 @@ CREATE PROCEDURE `Add3ColUniqueIndexIfNo
)
BEGIN
DECLARE done INT DEFAULT 0;
- DECLARE nonunique_index_name CHAR(16);
+ DECLARE nonunique_index_name CHAR(40);
DECLARE select_index_names CURSOR FOR
SELECT
@@ -645,6 +645,46 @@ BEGIN
END IF;
END$$
+-- --------------------------------------------------------
+
+/*
+Procedure : populateConnectMethodPort
+Description : Populates connectmethodport table from connectmethod table if it is empty
+*/
+
+DROP PROCEDURE IF EXISTS `moveConnectMethodPortProtocol`$$
+CREATE PROCEDURE `moveConnectMethodPortProtocol`(
+)
+BEGIN
+ IF EXISTS (
+ SELECT id FROM connectmethod WHERE connecttext LIKE '#connectport#'
+ )
+ THEN
+ UPDATE connectmethod SET connecttext = REPLACE(connecttext , '#connectport#', CONCAT('#Port-', protocol, '-', port, '#'));
+ END IF;
+ IF NOT EXISTS (
+ SELECT id FROM connectmethodport
+ )
+ THEN
+ IF EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND COLUMN_NAME='port'
+ AND TABLE_NAME='connectmethod'
+ ) AND EXISTS (
+ SELECT * FROM information_schema.COLUMNS WHERE
+ TABLE_SCHEMA=Database()
+ AND COLUMN_NAME='protocol'
+ AND TABLE_NAME='connectmethod'
+ )
+ THEN
+ INSERT INTO connectmethodport (connectmethodid, port, protocol) SELECT id, port, protocol FROM connectmethod;
+ CALL DropColumnIfExists('connectmethod', 'port');
+ CALL DropColumnIfExists('connectmethod', 'protocol');
+ END IF;
+ END IF;
+END$$
+
/* ============= End of Stored Procedures ===============*/
-- --------------------------------------------------------
@@ -721,7 +761,7 @@ EXECUTE nextimageid_noimage;
-- change RAM to mediumint
ALTER TABLE `computer` CHANGE `RAM` `RAM` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `computer` CHANGE `location` `location` VARCHAR(255) NULL DEFAULT NULL;
-ALTER TABLE `computer` ADD `predictivemoduleid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '8';
+CALL AddColumnIfNotExists('computer', 'predictivemoduleid', "SMALLINT(5) UNSIGNED NOT NULL DEFAULT '8'");
-- set datedeleted for deleted computers
UPDATE computer SET datedeleted = NOW() WHERE deleted = 1 AND datedeleted = '0000-00-00 00:00:00';
@@ -745,6 +785,7 @@ CREATE TABLE IF NOT EXISTS `connectmetho
UNIQUE KEY `name` (`name`,`description`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
-- --------------------------------------------------------
--
@@ -767,6 +808,23 @@ CREATE TABLE IF NOT EXISTS `connectmetho
-- --------------------------------------------------------
--
+-- Table structure for table `connectmethodport`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethodport` (
+ `id` tinyint(3) unsigned NOT NULL auto_increment,
+ `connectmethodid` tinyint(3) unsigned NOT NULL,
+ `port` mediumint(8) unsigned NOT NULL,
+ `protocol` enum('TCP','UDP') NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `connectmethodid` (`connectmethodid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CALL moveConnectMethodPortProtocol;
+
+-- --------------------------------------------------------
+
+--
-- Table structure for table 'connectlog'
--
@@ -799,6 +857,14 @@ CALL AddIndexIfNotExists('image', 'image
-- --------------------------------------------------------
--
+-- Table structure for table `imagemeta`
+--
+
+CALL DropColumnIfExists('imagemeta', 'usergroupid');
+
+-- --------------------------------------------------------
+
+--
-- Table structure for table `imagerevision`
--
@@ -851,6 +917,7 @@ CALL AddColumnIfNotExists('managementnod
CALL AddColumnIfNotExists('managementnode', 'sharedMailBox', "varchar(128) default NULL");
CALL AddColumnIfNotExists('managementnode', 'NOT_STANDALONE', "varchar(128) default NULL");
CALL AddColumnIfNotExists('managementnode', 'availablenetworks', "text NOT NULL");
+CALL DropColumnIfExists('managementnode', 'predictivemoduleid');
-- --------------------------------------------------------
@@ -863,6 +930,69 @@ CALL AddUniqueIndex('module', 'name');
-- --------------------------------------------------------
--
+-- Table structure for table `nathost`
+--
+
+CREATE TABLE IF NOT EXISTS `nathost` (
+ `id` smallint(5) unsigned NOT NULL auto_increment,
+ `resourceid` mediumint(8) unsigned NOT NULL,
+ `natIP` varchar(15) NOT NULL,
+ `deleted` tinyint(1) unsigned NOT NULL default '0',
+ `datedeleted` DATETIME DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `resourceid` (`resourceid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natlog`
+--
+
+CREATE TABLE IF NOT EXISTS `natlog` (
+ `logid` int(11) NOT NULL,
+ `connectmethodportid` int(11) NOT NULL,
+ `nathostid` int(11) NOT NULL,
+ `natIP` int(11) NOT NULL,
+ `computerid` int(11) NOT NULL,
+ `publicport` int(11) NOT NULL,
+ `privateport` int(11) NOT NULL,
+ `protocol` int(11) NOT NULL,
+ KEY `logid` (`logid`),
+ KEY `connectmethodportid` (`connectmethodportid`),
+ KEY `nathostid` (`nathostid`),
+ KEY `computerid` (`computerid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natmap`
+--
+
+CREATE TABLE IF NOT EXISTS `natmap` (
+ `computerid` smallint(5) unsigned NOT NULL,
+ `nathostid` smallint(5) unsigned NOT NULL,
+ UNIQUE KEY `computerid` (`computerid`,`nathostid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natport`
+--
+
+CREATE TABLE IF NOT EXISTS `natport` (
+ `reservationid` mediumint(8) unsigned NOT NULL,
+ `publicport` smallint(5) unsigned NOT NULL,
+ `connectmethodportid` tinyint(3) unsigned NOT NULL,
+ KEY `reservationid` (`reservationid`),
+ KEY `connectmethodportid` (`connectmethodportid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
-- Table structure change for table `OS`
--
@@ -1112,6 +1242,8 @@ CREATE TABLE IF NOT EXISTS `variable` (
ALTER TABLE `vmhost` CHANGE `vmprofileid` `vmprofileid` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1';
CALL AddIndexIfNotExists('vmhost', 'vmprofileid');
+CALL DropColumnIfExists('vmhost', 'vmkernalnic');
+CALL DropColumnIfExists('vmhost', 'vmwaredisk');
-- --------------------------------------------------------
@@ -1132,6 +1264,7 @@ CALL AddColumnIfNotExists('vmprofile', '
CALL AddColumnIfNotExists('vmprofile', 'rsapub', "text NULL default NULL AFTER `virtualswitch3`");
CALL AddColumnIfNotExists('vmprofile', 'rsakey', "varchar(256) NULL default NULL AFTER `rsapub`");
CALL AddColumnIfNotExists('vmprofile', 'encryptedpasswd', "text NULL default NULL AFTER `rsakey`");
+CALL AddColumnIfNotExists('vmprofile', 'folderpath', "varchar(256) default NULL AFTER resourcepath");
CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth0_generated', 'eth0generated', "tinyint(1) unsigned NOT NULL default '0'");
CALL AddOrRenameColumn('vmprofile', 'vmware_mac_eth1_generated', 'eth1generated', "tinyint(1) unsigned NOT NULL default '0'");
@@ -1238,6 +1371,14 @@ INSERT IGNORE INTO `module` (`name`, `pr
-- --------------------------------------------------------
+--
+-- Inserts for table `nathost`
+--
+
+INSERT IGNORE INTO nathost (resourceid, natIP) SELECT resource.id, managementnode.IPaddress FROM resource, managementnode WHERE resource.resourcetypeid = 16 AND resource.subid = managementnode.id;
+
+-- --------------------------------------------------------
+
--
-- Inserts for table `OStype`
--
@@ -1307,12 +1448,23 @@ INSERT IGNORE provisioningOSinstalltype
-- Inserts for table `connectmethod`
--
-INSERT IGNORE INTO `connectmethod` (`name`, `description`, `port`, `connecttext`, `servicename`, `startupscript`) VALUES
-('ssh', 'ssh on port 22', 22, 'You will need to have an X server running on your local computer and use an ssh client to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote Desktop Connection to connect t
o this computer. You must use an ssh client.</strong>', 'ext_sshd', '/etc/init.d/ext_sshd');
-INSERT IGNORE INTO `connectmethod` (`name`, `description`, `port`, `connecttext`, `servicename`, `startupscript`) VALUES
-('RDP', 'Remote Desktop', 3389, 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connection program.<b
r><br>\r\n', 'TermService', NULL);
-INSERT IGNORE INTO `connectmethod` (`name`, `description`, `port`, `connecttext`, `servicename`, `startupscript`) VALUES
-('iRAPP RDP', 'Remote Desktop for OS X', 3389, 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connec
tion program.<br><br>\r\n', NULL, NULL);
+INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`, `servicename`, `startupscript`) VALUES
+('ssh', 'ssh on port 22', 'You will need to have an X server running on your local computer and use an ssh client to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote Desktop Connection to connect to th
is computer. You must use an ssh client.</strong>', 'ext_sshd', '/etc/init.d/ext_sshd');
+INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`, `servicename`, `startupscript`) VALUES
+('RDP', 'Remote Desktop', 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connection program.<br><br>
\r\n', 'TermService', NULL);
+INSERT IGNORE INTO `connectmethod` (`name`, `description`, `connecttext`, `servicename`, `startupscript`) VALUES
+('iRAPP RDP', 'Remote Desktop for OS X', 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connection p
rogram.<br><br>\r\n', NULL, NULL);
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `connectmethodport`
+--
+
+INSERT IGNORE INTO `connectmethodport` (`connectmethodid`, `port`, `protocol`) VALUES
+((SELECT id FROM connectmethod WHERE name = 'ssh'), 22, 'TCP'),
+((SELECT id FROM connectmethod WHERE name = 'RDP'), 3389, 'TCP'),
+((SELECT id FROM connectmethod WHERE name = 'iRAPP RDP'), 3389, 'TCP');
-- --------------------------------------------------------
@@ -1412,7 +1564,10 @@ INSERT IGNORE INTO `usergroupprivtype` (
(12, 'View Statistics by Affiliation', 'Grants the ability to see statistics for affiliations that do not match the affiliation of the logged in user.'),
(13, 'Manage Block Allocations (affiliation only)', 'Grants the ability to create, accept, and reject block allocations owned by users matching your affiliation.'),
(14, 'Manage Federated User Groups (global)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.'),
-(15, 'Manage Federated User Groups (affiliation only)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.');
+(15, 'Manage Federated User Groups (affiliation only)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.'),
+(16, 'Site Configuration (global)', 'Grants the ability to view the Site Configuration part of the site to manage site settings.'),
+(17, 'Site Configuration (affiliation only)', 'Grants the ability to view the Site Configuration part of the site to manage site settings specific to the user''s own affiliation.');
+
UPDATE `usergroupprivtype` SET `name` = 'Manage Block Allocations (global)', `help` = 'Grants the ability to create, accept, and reject block allocations for any affiliation.' WHERE name = 'Manage Block Allocations';
Modified: vcl/trunk/mysql/vcl.sql
URL: http://svn.apache.org/viewvc/vcl/trunk/mysql/vcl.sql?rev=1628507&r1=1628506&r2=1628507&view=diff
==============================================================================
--- vcl/trunk/mysql/vcl.sql (original)
+++ vcl/trunk/mysql/vcl.sql Tue Sep 30 18:09:01 2014
@@ -213,7 +213,6 @@ CREATE TABLE IF NOT EXISTS `computer` (
`privateIPaddress` varchar(15) default NULL,
`eth0macaddress` varchar(17) default NULL,
`eth1macaddress` varchar(17) default NULL,
- `predictivemoduleid` smallint(5) unsigned NOT NULL default '1',
`type` enum('blade','lab','virtualmachine') NOT NULL default 'blade',
`provisioningid` smallint(5) unsigned NOT NULL,
`drivetype` varchar(4) NOT NULL default 'hda',
@@ -230,6 +229,7 @@ CREATE TABLE IF NOT EXISTS `computer` (
`hostpub` mediumtext,
`vmhostid` smallint(5) unsigned default NULL,
`vmtypeid` tinyint(3) unsigned default NULL,
+ `predictivemoduleid` smallint(5) unsigned NOT NULL default '1',
PRIMARY KEY (`id`),
UNIQUE KEY `hostname` (`hostname`, `datedeleted`),
UNIQUE KEY `eth1macaddress` (`eth1macaddress`, `datedeleted`),
@@ -328,8 +328,6 @@ CREATE TABLE IF NOT EXISTS `connectmetho
`id` tinyint(3) unsigned NOT NULL auto_increment,
`name` varchar(80) NOT NULL,
`description` varchar(255) NOT NULL,
- `protocol` varchar(32) NOT NULL,
- `port` smallint(5) unsigned NOT NULL,
`connecttext` text NOT NULL,
`servicename` varchar(32) NOT NULL,
`startupscript` varchar(256) DEFAULT NULL,
@@ -358,6 +356,21 @@ CREATE TABLE IF NOT EXISTS `connectmetho
-- --------------------------------------------------------
+--
+-- Table structure for table `connectmethodport`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethodport` (
+ `id` tinyint(3) unsigned NOT NULL auto_increment,
+ `connectmethodid` tinyint(3) unsigned NOT NULL,
+ `port` mediumint(8) unsigned NOT NULL,
+ `protocol` enum('TCP','UDP') NOT NULL,
+ PRIMARY KEY (`id`),
+ KEY `connectmethodid` (`connectmethodid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
--
-- Table structure for table `continuations`
--
@@ -618,6 +631,69 @@ CREATE TABLE IF NOT EXISTS `module` (
-- --------------------------------------------------------
+--
+-- Table structure for table `nathost`
+--
+
+CREATE TABLE IF NOT EXISTS `nathost` (
+ `id` smallint(5) unsigned NOT NULL auto_increment,
+ `resourceid` mediumint(8) unsigned NOT NULL,
+ `natIP` varchar(15) NOT NULL,
+ `deleted` tinyint(1) unsigned NOT NULL default '0',
+ `datedeleted` DATETIME DEFAULT NULL,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `resourceid` (`resourceid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natlog`
+--
+
+CREATE TABLE IF NOT EXISTS `natlog` (
+ `logid` int(11) NOT NULL,
+ `connectmethodportid` int(11) NOT NULL,
+ `nathostid` int(11) NOT NULL,
+ `natIP` int(11) NOT NULL,
+ `computerid` int(11) NOT NULL,
+ `publicport` int(11) NOT NULL,
+ `privateport` int(11) NOT NULL,
+ `protocol` int(11) NOT NULL,
+ KEY `logid` (`logid`),
+ KEY `connectmethodportid` (`connectmethodportid`),
+ KEY `nathostid` (`nathostid`),
+ KEY `computerid` (`computerid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natmap`
+--
+
+CREATE TABLE IF NOT EXISTS `natmap` (
+ `computerid` smallint(5) unsigned NOT NULL,
+ `nathostid` smallint(5) unsigned NOT NULL,
+ UNIQUE KEY `computerid` (`computerid`,`nathostid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `natport`
+--
+
+CREATE TABLE IF NOT EXISTS `natport` (
+ `reservationid` mediumint(8) unsigned NOT NULL,
+ `publicport` smallint(5) unsigned NOT NULL,
+ `connectmethodportid` tinyint(3) unsigned NOT NULL,
+ KEY `reservationid` (`reservationid`),
+ KEY `connectmethodportid` (`connectmethodportid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
--
-- Table structure for table `OS`
--
@@ -1455,10 +1531,10 @@ INSERT INTO `computerloadstate` (`id`, `
-- Dumping data for table `connectmethod`
--
-INSERT INTO `connectmethod` (`id`, `name`, `description`, `port`, `connecttext`, `servicename`, `startupscript`) VALUES
-(1, 'ssh', 'ssh on port 22', 22, 'You will need to have an X server running on your local computer and use an ssh client to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote Desktop Connection to connec
t to this computer. You must use an ssh client.</strong>', 'ext_sshd', '/etc/init.d/ext_sshd'),
-(2, 'RDP', 'Remote Desktop', 3389, 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connection program
.<br><br>\r\n', 'TermService', NULL),
-(3, 'iRAPP RDP', 'Remote Desktop for OS X', 3389, 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Con
nection program.<br><br>\r\n', NULL, NULL);
+INSERT INTO `connectmethod` (`id`, `name`, `description`, `connecttext`, `servicename`, `startupscript`) VALUES
+(1, 'ssh', 'ssh on port 22', 'You will need to have an X server running on your local computer and use an ssh client to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<strong><big>NOTE:</big> You cannot use the Windows Remote Desktop Connection to connect to
this computer. You must use an ssh client.</strong>', 'ext_sshd', '/etc/init.d/ext_sshd'),
+(2, 'RDP', 'Remote Desktop', 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connection program.<br><
br>\r\n', 'TermService', NULL),
+(3, 'iRAPP RDP', 'Remote Desktop for OS X', 'You will need to use a Remote Desktop program to connect to the system. If you did not click on the <b>Connect!</b> button from the computer you will be using to access the VCL system, you will need to return to the <strong>Current Reservations</strong> page and click the <strong>Connect!</strong> button from a web browser running on the same computer from which you will be connecting to the VCL system. Otherwise, you may be denied access to the remote computer.<br><br>\r\n\r\nUse the following information when you are ready to connect:<br>\r\n<UL>\r\n<LI><b>Remote Computer</b>: #connectIP#</LI>\r\n<LI><b>User ID</b>: #userid#</LI>\r\n<LI><b>Password</b>: #password#<br></LI>\r\n</UL>\r\n<b>NOTE</b>: The given password is for <i>this reservation only</i>. You will be given a different password for any other reservations.<br>\r\n<br>\r\nFor automatic connection, you can download an RDP file that can be opened by the Remote Desktop Connectio
n program.<br><br>\r\n', NULL, NULL);
--
@@ -1883,7 +1959,9 @@ INSERT INTO `usergroupprivtype` (`id`, `
(12, 'View Statistics by Affiliation', 'Grants the ability to see statistics for affiliations that do not match the affiliation of the logged in user.'),
(13, 'Manage Block Allocations (affiliation only)', 'Grants the ability to create, accept, and reject block allocations owned by users matching your affiliation.'),
(14, 'Manage Federated User Groups (global)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.'),
-(15, 'Manage Federated User Groups (affiliation only)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.');
+(15, 'Manage Federated User Groups (affiliation only)', 'Grants the ability to control attributes of user groups that are created through federated systems such as LDAP and Shibboleth. Does not grant control of user group membership.'),
+(16, 'Site Configuration (global)', 'Grants the ability to view the Site Configuration part of the site to manage site settings.'),
+(17, 'Site Configuration (affiliation only)', 'Grants the ability to view the Site Configuration part of the site to manage site settings specific to the user''s own affiliation.');
--
-- Dumping data for table `userpriv`