Add GUID to groups

--HG--
branch : trunk

4 files changed, 27 insertions, 6 deletions

diff --git a/sql/characters.sql b/sql/characters.sql
index b0e3e170062..015e77d36a8 100644
--- a/sql/characters.sql
+++ b/sql/characters.sql
@@ -1297,10 +1297,10 @@ DROP TABLE IF EXISTS `group_instance`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `group_instance` (
-  `leaderGuid` int(11) unsigned NOT NULL default '0',
+  `guid` int(11) unsigned NOT NULL default '0',
   `instance` int(11) unsigned NOT NULL default '0',
   `permanent` tinyint(1) unsigned NOT NULL default '0',
-  PRIMARY KEY  (`leaderGuid`,`instance`),
+  PRIMARY KEY  (`guid`,`instance`),
   KEY `instance` (`instance`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 /*!40101 SET character_set_client = @saved_cs_client */;
@@ -1322,12 +1322,11 @@ DROP TABLE IF EXISTS `group_member`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `group_member` (
-  `leaderGuid` int(11) unsigned NOT NULL,
+  `guid` int(11) unsigned NOT NULL,
   `memberGuid` int(11) unsigned NOT NULL,
   `memberFlags` tinyint(2) unsigned NOT NULL,
   `subgroup` smallint(6) unsigned NOT NULL,
-  PRIMARY KEY  (`leaderGuid`,`memberGuid`),
-  INDEX `Idx_memberGuid`(`memberGuid`)
+  PRIMARY KEY  (`memberGuid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Groups';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -1348,6 +1347,7 @@ DROP TABLE IF EXISTS `groups`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `groups` (
+  `guid` int(11) unsigned NOT NULL,
   `leaderGuid` int(11) unsigned NOT NULL,
   `lootMethod` tinyint(4) unsigned NOT NULL,
   `looterGuid` int(11) unsigned NOT NULL,
@@ -1363,7 +1363,7 @@ CREATE TABLE `groups` (
   `groupType` mediumint(8) unsigned NOT NULL,
   `difficulty` tinyint(3) unsigned NOT NULL default '0',
   `raiddifficulty` int(11) UNSIGNED NOT NULL default '0',
-  PRIMARY KEY  (`leaderGuid`)
+  PRIMARY KEY  (`guid`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Groups';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
diff --git a/sql/updates/8136_01_characters_groups.sql b/sql/updates/8136_01_characters_groups.sql
new file mode 100644
index 00000000000..af0f6cb736c
--- /dev/null
+++ b/sql/updates/8136_01_characters_groups.sql
@@ -0,0 +1,5 @@
+-- Create guid column and make it new pk
+ALTER TABLE `groups` ADD `guid` INT(11) NOT NULL FIRST;
+SET @a := 0;
+UPDATE `groups` SET `guid` = @a := @a+1;
+ALTER TABLE `groups` DROP PRIMARY KEY, ADD PRIMARY KEY (`guid`);
diff --git a/sql/updates/8136_02_characters_group_member.sql b/sql/updates/8136_02_characters_group_member.sql
new file mode 100644
index 00000000000..7300e34f2f9
--- /dev/null
+++ b/sql/updates/8136_02_characters_group_member.sql
@@ -0,0 +1,8 @@
+-- Create guid column
+ALTER TABLE `group_member` ADD `guid` INT(11) NOT NULL FIRST;
+-- populate column
+UPDATE `group_member` SET `guid`=(SELECT `groups`.`guid` FROM `groups` WHERE `leaderGuid`=`group_member`.`leaderGuid`);
+-- Remove index that will be same as pk
+ALTER TABLE `group_member` DROP INDEX `Idx_memberGuid` ;
+-- NOTE: if this one fails then u have a problem in your DB. Have a member in 2 groups, so fix it yourself and then re-run this one
+ALTER TABLE `group_member` DROP `leaderGuid`;
diff --git a/sql/updates/8136_03_characters_group_instance.sql b/sql/updates/8136_03_characters_group_instance.sql
new file mode 100644
index 00000000000..78601c14b98
--- /dev/null
+++ b/sql/updates/8136_03_characters_group_instance.sql
@@ -0,0 +1,8 @@
+-- Create guid column
+ALTER TABLE `group_instance` ADD `guid` INT(11) NOT NULL FIRST;
+-- populate column
+UPDATE `group_instance` SET `guid`=(SELECT `groups`.`guid` FROM `groups` WHERE `leaderGuid`=`group_instance`.`leaderGuid`);
+-- Update pk
+ALTER TABLE `group_instance` DROP PRIMARY KEY, ADD PRIMARY KEY (`guid`, `instance`);
+-- Remove unused column leaderguid
+ALTER TABLE `group_instance` DROP `leaderGuid`;