Author: jfthomps
Date: Thu May 10 15:15:13 2012
New Revision: 1336741

URL: http://svn.apache.org/viewvc?rev=1336741&view=rev
Log:
VCL-576
Finalizing for 2.3 release

vcl.sql:
-changed key on serverprofile.name to be unique
-removed vmhost.vmkernalnic and vmhost.vmwaredisk
-changed vmprofile.vmdisk from 'localdisk','networkdisk' to 'dedicated','shared'
-added "all profiles" server profile resource group
-added several missing resource attributes for resource groups
-added serverCheckOut and serverProfileAdmin for admin user

update-vcl.sql:
-added AlterVMDiskValues stored procedure
-added AddManageMapping stored procedure
-changed key on serverprofile.name to be unique
-added call to AlterVMDiskValues
-changed alter table for resourcepriv that added 'manageMapping' to call to 
AddManageMapping
-added all resource group attributes for 'all profiles', 'All VM Computers', 
and 'allVMimages' to the default VCL->admin node if it exists
-added serverCheckOut and serverProfileAdmin for admin user

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=1336741&r1=1336740&r2=1336741&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/update-vcl.sql (original)
+++ incubator/vcl/trunk/mysql/update-vcl.sql Thu May 10 15:15:13 2012
@@ -408,6 +408,75 @@ END$$
 
 -- --------------------------------------------------------
 
+/*
+Procedure   : AlterVMDiskValues
+Description : Changes vmprofile.vmdisk enum values from
+              localdisk,networkdisk to dedicated,shared
+*/
+
+DROP PROCEDURE IF EXISTS `AlterVMDiskValues`$$
+CREATE PROCEDURE `AlterVMDiskValues`()
+BEGIN
+  DECLARE data TEXT;
+  SET data = (SELECT COLUMN_TYPE FROM information_schema.COLUMNS WHERE
+              TABLE_SCHEMA = Database()
+              AND TABLE_NAME = 'vmprofile'
+              AND COLUMN_NAME = 'vmdisk');
+  SET data = SUBSTRING_INDEX(data, "enum(", -1);
+  SET data = SUBSTRING_INDEX(data, ")", 1);
+  IF NOT STRCMP(data, "'localdisk','networkdisk'") THEN
+    ALTER TABLE vmprofile
+    CHANGE vmdisk
+    vmdisk ENUM('localdisk','networkdisk','dedicated','shared') NOT NULL 
DEFAULT 'dedicated';
+
+    UPDATE vmprofile
+    SET vmdisk = 'dedicated'
+    WHERE vmdisk = 'localdisk';
+
+    UPDATE vmprofile
+    SET vmdisk = 'shared'
+    WHERE vmdisk = 'networkdisk';
+
+    ALTER TABLE vmprofile
+    CHANGE vmdisk
+    vmdisk ENUM('dedicated','shared') NOT NULL DEFAULT 'dedicated';
+  END IF;
+END$$
+
+-- --------------------------------------------------------
+
+/*
+Procedure   : AddManageMapping
+Description : adds the manageMapping resource attribute
+              and assigns it everywhere manageGroup is
+              assigned
+*/
+
+DROP PROCEDURE IF EXISTS `AddManageMapping`$$
+CREATE PROCEDURE `AddManageMapping`()
+BEGIN
+  DECLARE data TEXT;
+  SET data = (SELECT COLUMN_TYPE FROM information_schema.COLUMNS WHERE
+              TABLE_SCHEMA = Database()
+              AND TABLE_NAME = 'resourcepriv'
+              AND COLUMN_NAME = 'type');
+  IF NOT LOCATE('manageMapping', data) THEN
+    /* add manageMapping attribute */
+    ALTER TABLE resourcepriv
+    CHANGE `type`
+    `type` 
ENUM('block','cascade','available','administer','manageGroup','manageMapping') 
NOT NULL default 'block';
+
+    /* grant manageMapping everywhere manageGroup is currently granted */
+    INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, type)
+    SELECT resourcegroupid, privnodeid, 'manageMapping'
+    FROM resourcepriv WHERE `type` = 'manageGroup';
+  END IF;
+END$$
+
+/* ============= End of Stored Procedures ===============*/
+
+-- --------------------------------------------------------
+
 --
 --  Table structure for table `affiliation`
 --
@@ -638,7 +707,7 @@ CREATE TABLE IF NOT EXISTS `reservationa
 --
 -- Table structure for table `resourcepriv`
 --
-ALTER TABLE resourcepriv MODIFY COLUMN `type` 
ENUM('block','cascade','available','administer','manageGroup','manageMapping') 
NOT NULL default 'block';
+CALL AddManageMapping();
 
 -- --------------------------------------------------------
 
@@ -660,7 +729,7 @@ CREATE TABLE IF NOT EXISTS `serverprofil
   `monitored` tinyint(1) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `ownerid` (`ownerid`),
-  KEY `name` (`name`),
+  UNIQUE KEY `name` (`name`),
   KEY `admingroupid` (`admingroupid`),
   KEY `logingroupid` (`logingroupid`),
   KEY `imageid` (`imageid`)
@@ -823,6 +892,8 @@ CALL AddColumnIfNotExists('vmprofile', '
 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'");
 
+CALL AlterVMDiskValues()
+
 CALL AddUniqueIndex('vmprofile', 'profilename');
 CALL AddIndexIfNotExists('vmprofile', 'repositoryimagetypeid');
 CALL AddIndexIfNotExists('vmprofile', 'datastoreimagetypeid');
@@ -1013,6 +1084,33 @@ INSERT IGNORE INTO resourcetype (id, nam
 -- --------------------------------------------------------
 
 -- 
+-- Inserts for table `resourcegroup`
+--
+
+INSERT IGNORE INTO resourcegroup (name, ownerusergroupid, resourcetypeid) 
VALUES ('all profiles', 3, 17);
+
+-- --------------------------------------------------------
+
+-- 
+-- Inserts for table `resourcepriv`
+--
+
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'available' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'all profiles' AND resourcegroup.resourcetypeid = 17 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'administer' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'all profiles' AND resourcegroup.resourcetypeid = 17 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageGroup' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'all profiles' AND resourcegroup.resourcetypeid = 17 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageMapping' FROM resourcegroup, privnode 
WHERE resourcegroup.name = 'all profiles' AND resourcegroup.resourcetypeid = 17 
AND privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'available' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'All VM Computers' AND resourcegroup.resourcetypeid = 12 
AND privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'administer' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'All VM Computers' AND resourcegroup.resourcetypeid = 12 
AND privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageGroup' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'All VM Computers' AND resourcegroup.resourcetypeid = 12 
AND privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageMapping' FROM resourcegroup, privnode 
WHERE resourcegroup.name = 'All VM Computers' AND resourcegroup.resourcetypeid 
= 12 AND privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'available' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'allVMimages' AND resourcegroup.resourcetypeid = 13 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'administer' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'allVMimages' AND resourcegroup.resourcetypeid = 13 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageGroup' FROM resourcegroup, privnode WHERE 
resourcegroup.name = 'allVMimages' AND resourcegroup.resourcetypeid = 13 AND 
privnode.name = 'admin' AND privnode.parent = 3;
+INSERT IGNORE INTO resourcepriv (resourcegroupid, privnodeid, `type`) SELECT 
resourcegroup.id, privnode.id, 'manageMapping' FROM resourcegroup, privnode 
WHERE resourcegroup.name = 'allVMimages' AND resourcegroup.resourcetypeid =137 
AND privnode.name = 'admin' AND privnode.parent = 3;
+
+-- --------------------------------------------------------
+
+-- 
 -- Inserts for table `state`
 --
 
@@ -1083,6 +1181,8 @@ INSERT IGNORE INTO userprivtype (id, nam
 -- Inserts for table `userpriv`
 --
 
+INSERT IGNORE userpriv (userid, privnodeid, userprivtypeid) SELECT user.id, 
privnode.id, userprivtype.id FROM user, privnode, userprivtype WHERE 
user.unityid = 'admin' AND user.affiliationid = (SELECT id FROM affiliation 
WHERE name = 'Local') AND privnode.name = 'admin' AND privnode.parent = 3 AND 
userprivtype.name = 'serverCheckOut';
+INSERT IGNORE userpriv (userid, privnodeid, userprivtypeid) SELECT user.id, 
privnode.id, userprivtype.id FROM user, privnode, userprivtype WHERE 
user.unityid = 'admin' AND user.affiliationid = (SELECT id FROM affiliation 
WHERE name = 'Local') AND privnode.name = 'admin' AND privnode.parent = 3 AND 
userprivtype.name = 'serverProfileAdmin';
 INSERT IGNORE userpriv (usergroupid, privnodeid, userprivtypeid) SELECT 
usergroup.id, privnode.id, userprivtype.id FROM usergroup, privnode, 
userprivtype WHERE usergroup.name = 'adminUsers' AND usergroup.affiliationid = 
(SELECT id FROM affiliation WHERE name = 'Local') AND privnode.name = 'admin' 
AND privnode.parent = 3 AND userprivtype.name = 'serverCheckOut';
 INSERT IGNORE userpriv (usergroupid, privnodeid, userprivtypeid) SELECT 
usergroup.id, privnode.id, userprivtype.id FROM usergroup, privnode, 
userprivtype WHERE usergroup.name = 'adminUsers' AND usergroup.affiliationid = 
(SELECT id FROM affiliation WHERE name = 'Local') AND privnode.name = 'admin' 
AND privnode.parent = 3 AND userprivtype.name = 'serverProfileAdmin';
 
@@ -1232,6 +1332,8 @@ DROP PROCEDURE IF EXISTS `AddIndexIfNotE
 DROP PROCEDURE IF EXISTS `AddUniqueIndex`;
 DROP PROCEDURE IF EXISTS `AddConstraintIfNotExists`;
 DROP PROCEDURE IF EXISTS `AddConnectMethodMapIfNotExists`;
+DROP PROCEDURE IF EXISTS `AlterVMDiskValues`;
 DROP PROCEDURE IF EXISTS `AddOrRenameColumn`;
 DROP PROCEDURE IF EXISTS `DropExistingConstraints`;
 DROP PROCEDURE IF EXISTS `DropExistingIndices`;
+DROP PROCEDURE IF EXISTS `AddManageMapping`;

Modified: incubator/vcl/trunk/mysql/vcl.sql
URL: 
http://svn.apache.org/viewvc/incubator/vcl/trunk/mysql/vcl.sql?rev=1336741&r1=1336740&r2=1336741&view=diff
==============================================================================
--- incubator/vcl/trunk/mysql/vcl.sql (original)
+++ incubator/vcl/trunk/mysql/vcl.sql Thu May 10 15:15:13 2012
@@ -914,7 +914,7 @@ CREATE TABLE IF NOT EXISTS `serverprofil
   `monitored` tinyint(1) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
   KEY `ownerid` (`ownerid`),
-  KEY `name` (`name`),
+  UNIQUE KEY `name` (`name`),
   KEY `admingroupid` (`admingroupid`),
   KEY `logingroupid` (`logingroupid`),
   KEY `imageid` (`imageid`)
@@ -1206,8 +1206,6 @@ CREATE TABLE IF NOT EXISTS `vmhost` (
   `computerid` smallint(5) unsigned NOT NULL,
   `vmlimit` tinyint(3) unsigned NOT NULL,
   `vmprofileid` smallint(5) unsigned NOT NULL,
-  `vmkernalnic` varchar(15) default NULL,
-  `vmwaredisk` enum('localdisk','networkdisk') NOT NULL default 'localdisk',
   PRIMARY KEY  (`id`),
   UNIQUE KEY `computerid` (`computerid`),
   KEY `vmprofileid` (`vmprofileid`)
@@ -1233,7 +1231,7 @@ CREATE TABLE IF NOT EXISTS `vmprofile` (
   `virtualswitch1` varchar(80) NOT NULL default 'VMnet2',
   `virtualswitch2` varchar(80) NULL default NULL,
   `virtualswitch3` varchar(80) NULL default NULL,
-  `vmdisk` enum('localdisk','networkdisk') NOT NULL default 'localdisk',
+  `vmdisk` enum('dedicated','shared') NOT NULL default 'dedicated',
   `username` varchar(80) NULL default NULL,
   `password` varchar(256) NULL default NULL,
   `eth0generated` tinyint(1) unsigned NOT NULL default '0',
@@ -1636,7 +1634,8 @@ INSERT INTO `resourcegroup` (`id`, `name
 (5, 'All VM Computers', 3, 12),
 (8, 'newimages', 4, 12),
 (9, 'newvmimages', 4, 12),
-(10, 'allVMimages', 4, 13);
+(10, 'allVMimages', 4, 13),
+(11, 'all profiles', 3, 17);
 
 -- 
 -- Dumping data for table `resourcegroupmembers`
@@ -1675,7 +1674,23 @@ INSERT INTO `resourcepriv` (`id`, `resou
 (11, 4, 4, 'administer'),
 (12, 4, 4, 'manageGroup'),
 (15, 8, 5, 'cascade'),
-(16, 8, 5, 'available');
+(16, 8, 5, 'available'),
+(17, 1, 4, 'manageMapping'),
+(18, 2, 4, 'manageMapping'),
+(19, 3, 4, 'manageMapping'),
+(20, 4, 4, 'manageMapping'),
+(21, 5, 4, 'available'),
+(22, 5, 4, 'administer'),
+(23, 5, 4, 'manageGroup'),
+(24, 5, 4, 'manageMapping'),
+(25, 10, 4, 'available'),
+(26, 10, 4, 'administer'),
+(27, 10, 4, 'manageGroup'),
+(28, 10, 4, 'manageMapping'),
+(29, 11, 4, 'available'),
+(30, 11, 4, 'administer'),
+(31, 11, 4, 'manageGroup'),
+(32, 11, 4, 'manageMapping');
 
 -- 
 -- Dumping data for table `resourcetype`
@@ -1827,6 +1842,8 @@ INSERT INTO `userpriv` (`id`, `userid`, 
 (7, 1, NULL, 3, 5),
 (3, 1, NULL, 3, 6),
 (8, 1, NULL, 3, 7),
+(22, 1, NULL, 3, 8),
+(23, 1, NULL, 3, 9),
 (4, 1, NULL, 3, 10),
 (9, 1, NULL, 3, 11),
 (5, 1, NULL, 3, 12),


Reply via email to