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),
