Author: jfthomps
Date: Thu Apr 28 19:00:12 2011
New Revision: 1097573

URL: http://svn.apache.org/viewvc?rev=1097573&view=rev
Log:
VCL-463
VCL-30

added definitions for these tables:
-connectmethod
-connectmethodmap
-serverprofile
-serverrequest

added data for these tables:
-connectmethod
-connectmethodmap

added constraints for these tables:
-connectmethodmap
-serverprofile
-serverrequest

Modified:
    incubator/vcl/trunk/mysql/update-vcl.sql
    incubator/vcl/trunk/mysql/vcl.sql

Modified: incubator/vcl/trunk/mysql/update-vcl.sql
URL: 
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/update-vcl.sql?rev=1097573&r1=1097572&r2=1097573&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-vcl.sql (original)
+++ incubator/vcl/trunk/mysql/update-vcl.sql Thu Apr 28 19:00:12 2011
@@ -244,6 +244,39 @@ EXECUTE nextimageid_noimage;
 -- --------------------------------------------------------
 
 --
+-- Table structure for table `connectmethod`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethod` (
+  `id` tinyint(3) unsigned NOT NULL auto_increment,
+  `name` varchar(80) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  `port` smallint(5) unsigned NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `connectmethodmap`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethodmap` (
+  `connectmethodid` tinyint(3) unsigned NOT NULL,
+  `OStypeid` tinyint(3) unsigned default NULL,
+  `OSid` tinyint(3) unsigned default NULL,
+  `imageid` smallint(5) unsigned default NULL,
+  `disabled` tinyint(1) unsigned NOT NULL default '0',
+  `autoprovisioned` tinyint(1) unsigned default NULL,
+  KEY `connectmethodid` (`connectmethodid`),
+  KEY `OStypeid` (`OStypeid`),
+  KEY `OSid` (`OSid`),
+  KEY `imageid` (`imageid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
 -- Table structure for table `loginlog`
 --
  
@@ -311,6 +344,50 @@ CREATE TABLE IF NOT EXISTS `provisioning
 -- --------------------------------------------------------
 
 --
+-- Table structure for table `serverprofile`
+--
+
+CREATE TABLE IF NOT EXISTS `serverprofile` (
+  `id` smallint(5) unsigned NOT NULL auto_increment,
+  `name` varchar(255) NOT NULL,
+  `description` text NOT NULL,
+  `imageid` smallint(5) unsigned NOT NULL,
+  `ownerid` mediumint(8) unsigned NOT NULL,
+  `ending` enum('specified','indefinite') NOT NULL default 'specified',
+  `fixedIP` varchar(15) default NULL,
+  `fixedMAC` varchar(17) default NULL,
+  `admingroupid` smallint(5) unsigned default NULL,
+  `logingroupid` smallint(5) unsigned default NULL,
+  `monitored` tinyint(1) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`id`),
+  KEY `ownerid` (`ownerid`),
+  KEY `name` (`name`),
+  KEY `admingroupid` (`admingroupid`),
+  KEY `logingroupid` (`logingroupid`),
+  KEY `imageid` (`imageid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `serverrequest`
+--
+
+CREATE TABLE IF NOT EXISTS `serverrequest` (
+  `id` mediumint(8) unsigned NOT NULL auto_increment,
+  `requestid` mediumint(8) unsigned NOT NULL,
+  `fixedIP` varchar(15) default NULL,
+  `fixedMAC` varchar(17) default NULL,
+  `admingroupid` smallint(5) unsigned default NULL,
+  `logingroupid` smallint(5) unsigned default NULL,
+  `monitored` tinyint(1) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY `requestid` (`requestid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
 -- Table structure for table `sitemaintenance`
 --
 
@@ -437,6 +514,28 @@ UPDATE `computer` SET `imagerevisionid` 
 
 -- --------------------------------------------------------
 
+--
+-- Inserts for table `connectmethod`
+--
+
+INSERT IGNORE INTO `connectmethod` (`id`, `name`, `description`, `port`) 
VALUES (1, 'ssh', 'ssh on port 22', 22);
+INSERT IGNORE INTO `connectmethod` (`id`, `name`, `description`, `port`) 
VALUES (2, 'RDP', 'Remote Desktop', 3389);
+
+-- --------------------------------------------------------
+
+--
+-- Inserts for table `connectmethodmap`
+--
+
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (1, 2, NULL, NULL, 0, 1);
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (1, 3, NULL, NULL, 0, 1);
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (2, 1, NULL, NULL, 0, 1);
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (1, 2, NULL, NULL, 0, NULL);
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (1, 3, NULL, NULL, 0, NULL);
+INSERT IGNORE INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES (2, 1, NULL, NULL, 0, NULL);
+
+-- --------------------------------------------------------
+
 -- 
 -- Inserts for table `module`
 -- 
@@ -532,6 +631,17 @@ CALL AddConstraintIfNotExists('computer'
 -- --------------------------------------------------------
 
 --
+-- Constraints for table `connectmethodmap`
+--
+
+CALL AddConstraintIfNotExists('connectmethodmap', 'connectmethodid', 
'connectmethod', 'id');
+CALL AddConstraintIfNotExists('connectmethodmap', 'OStypeid', 'OStype', 'id');
+CALL AddConstraintIfNotExists('connectmethodmap', 'OSid', 'OS', 'id');
+CALL AddConstraintIfNotExists('connectmethodmap', 'imageid', 'image', 'id');
+
+-- --------------------------------------------------------
+
+--
 -- Constraints for table `provisioningOSinstalltype`
 --
  
@@ -541,6 +651,25 @@ CALL AddConstraintIfNotExists('provision
 -- --------------------------------------------------------
 
 --
+-- Constraints for table `serverprofile`
+--
+
+CALL AddConstraintIfNotExists('serverprofile', 'ownerid', 'user', 'id');
+CALL AddConstraintIfNotExists('serverprofile', 'admingroupid', 'usergroup', 
'id');
+CALL AddConstraintIfNotExists('serverprofile', 'logingroupid', 'usergroup', 
'id');
+CALL AddConstraintIfNotExists('serverprofile', 'imageid', 'image', 'id');
+
+-- --------------------------------------------------------
+
+--
+-- Constraints for table `serverrequest`
+--
+
+CALL AddConstraintIfNotExists('serverrequest', 'requestid', 'request', 'id');
+
+-- --------------------------------------------------------
+
+--
 -- Constraints for table `vmhost`
 --
  

Modified: incubator/vcl/trunk/mysql/vcl.sql
URL: 
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=1097573&r1=1097572&r2=1097573&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/vcl.sql (original)
+++ incubator/vcl/trunk/mysql/vcl.sql Thu Apr 28 19:00:12 2011
@@ -289,6 +289,39 @@ CREATE TABLE IF NOT EXISTS `computerload
 
 -- --------------------------------------------------------
 
+--
+-- Table structure for table `connectmethod`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethod` (
+  `id` tinyint(3) unsigned NOT NULL auto_increment,
+  `name` varchar(80) NOT NULL,
+  `description` varchar(255) NOT NULL,
+  `port` smallint(5) unsigned NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `connectmethodmap`
+--
+
+CREATE TABLE IF NOT EXISTS `connectmethodmap` (
+  `connectmethodid` tinyint(3) unsigned NOT NULL,
+  `OStypeid` tinyint(3) unsigned default NULL,
+  `OSid` tinyint(3) unsigned default NULL,
+  `imageid` smallint(5) unsigned default NULL,
+  `disabled` tinyint(1) unsigned NOT NULL default '0',
+  `autoprovisioned` tinyint(1) unsigned default NULL,
+  KEY `connectmethodid` (`connectmethodid`),
+  KEY `OStypeid` (`OStypeid`),
+  KEY `OSid` (`OSid`),
+  KEY `imageid` (`imageid`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
 -- 
 -- Table structure for table `continuations`
 -- 
@@ -823,6 +856,50 @@ CREATE TABLE IF NOT EXISTS `scheduletime
 
 -- --------------------------------------------------------
 
+--
+-- Table structure for table `serverprofile`
+--
+
+CREATE TABLE IF NOT EXISTS `serverprofile` (
+  `id` smallint(5) unsigned NOT NULL auto_increment,
+  `name` varchar(255) NOT NULL,
+  `description` text NOT NULL,
+  `imageid` smallint(5) unsigned NOT NULL,
+  `ownerid` mediumint(8) unsigned NOT NULL,
+  `ending` enum('specified','indefinite') NOT NULL default 'specified',
+  `fixedIP` varchar(15) default NULL,
+  `fixedMAC` varchar(17) default NULL,
+  `admingroupid` smallint(5) unsigned default NULL,
+  `logingroupid` smallint(5) unsigned default NULL,
+  `monitored` tinyint(1) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`id`),
+  KEY `ownerid` (`ownerid`),
+  KEY `name` (`name`),
+  KEY `admingroupid` (`admingroupid`),
+  KEY `logingroupid` (`logingroupid`),
+  KEY `imageid` (`imageid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
+--
+-- Table structure for table `serverrequest`
+--
+
+CREATE TABLE IF NOT EXISTS `serverrequest` (
+  `id` mediumint(8) unsigned NOT NULL auto_increment,
+  `requestid` mediumint(8) unsigned NOT NULL,
+  `fixedIP` varchar(15) default NULL,
+  `fixedMAC` varchar(17) default NULL,
+  `admingroupid` smallint(5) unsigned default NULL,
+  `logingroupid` smallint(5) unsigned default NULL,
+  `monitored` tinyint(1) unsigned NOT NULL default '0',
+  PRIMARY KEY  (`id`),
+  UNIQUE KEY `requestid` (`requestid`)
+) ENGINE=InnoDB  DEFAULT CHARSET=latin1;
+
+-- --------------------------------------------------------
+
 -- 
 -- Table structure for table `shibauth`
 --
@@ -1250,6 +1327,26 @@ INSERT INTO `computerloadstate` (`id`, `
 (53, 'deleted', 'deleted', NULL),
 (54, 'begin', 'beginning to process reservation', 0);
 
+--
+-- Dumping data for table `connectmethod`
+--
+
+INSERT INTO `connectmethod` (`id`, `name`, `description`, `port`) VALUES
+(1, 'ssh', 'ssh on port 22', 22),
+(2, 'RDP', 'Remote Desktop', 3389);
+
+--
+-- Dumping data for table `connectmethodmap`
+--
+
+INSERT INTO `connectmethodmap` (`connectmethodid`, `OStypeid`, `OSid`, 
`imageid`, `disabled`, `autoprovisioned`) VALUES
+(1, 2, NULL, NULL, 0, 1),
+(1, 3, NULL, NULL, 0, 1),
+(2, 1, NULL, NULL, 0, 1),
+(1, 2, NULL, NULL, 0, NULL),
+(1, 3, NULL, NULL, 0, NULL),
+(2, 1, NULL, NULL, 0, NULL);
+
 -- 
 -- Dumping data for table `documentation`
 -- 
@@ -1726,6 +1823,15 @@ ALTER TABLE `computer`
 ALTER TABLE `computerloadlog`
   ADD CONSTRAINT `computerloadlog_ibfk_1` FOREIGN KEY (`reservationid`) 
REFERENCES `reservation` (`id`) ON DELETE CASCADE;
 
+--
+-- Constraints for table `connectmethodmap`
+--
+ALTER TABLE `connectmethodmap`
+  ADD CONSTRAINT `connectmethodmap_ibfk_1` FOREIGN KEY (`connectmethodid`) 
REFERENCES `connectmethod` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  ADD CONSTRAINT `connectmethodmap_ibfk_2` FOREIGN KEY (`OStypeid`) REFERENCES 
`OStype` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  ADD CONSTRAINT `connectmethodmap_ibfk_3` FOREIGN KEY (`OSid`) REFERENCES 
`OS` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  ADD CONSTRAINT `connectmethodmap_ibfk_4` FOREIGN KEY (`imageid`) REFERENCES 
`image` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
+
 -- 
 -- Constraints for table `continuations`
 -- 
@@ -1858,6 +1964,21 @@ ALTER TABLE `resourcepriv`
 ALTER TABLE `schedule`
   ADD CONSTRAINT `schedule_ibfk_1` FOREIGN KEY (`ownerid`) REFERENCES `user` 
(`id`) ON UPDATE CASCADE;
 
+--
+-- Constraints for table `serverprofile`
+--
+ALTER TABLE `serverprofile`
+  ADD CONSTRAINT `serverprofile_ibfk_1` FOREIGN KEY (`ownerid`) REFERENCES 
`user` (`id`),
+  ADD CONSTRAINT `serverprofile_ibfk_2` FOREIGN KEY (`admingroupid`) 
REFERENCES `usergroup` (`id`),
+  ADD CONSTRAINT `serverprofile_ibfk_3` FOREIGN KEY (`logingroupid`) 
REFERENCES `usergroup` (`id`),
+  ADD CONSTRAINT `serverprofile_ibfk_4` FOREIGN KEY (`imageid`) REFERENCES 
`image` (`id`);
+
+--
+-- Constraints for table `serverrequest`
+--
+ALTER TABLE `serverrequest`
+  ADD CONSTRAINT `serverrequest_ibfk_1` FOREIGN KEY (`requestid`) REFERENCES 
`request` (`id`) ON DELETE CASCADE;
+
 -- 
 -- Constraints for table `user`
 -- 


Reply via email to