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 2011/04/28 21:00:12 UTC
svn commit: r1097573 - in /incubator/vcl/trunk/mysql: update-vcl.sql vcl.sql
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`
--