diff options
| -rw-r--r-- | sql/base/characters_database.sql | 2 | ||||
| -rw-r--r-- | sql/old/11.x/characters/25071_2025_08_16/2025_08_13_00_characters.sql | 287 |
2 files changed, 160 insertions, 129 deletions
diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index c93b076181a..e291850dc8b 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -3821,7 +3821,7 @@ INSERT INTO `updates` VALUES ('2025_05_31_00_characters.sql','C240EB5C4008B6AA0514802A18D7DD875680DE82','ARCHIVED','2025-05-31 19:45:56',0), ('2025_06_27_00_characters.sql','35088BA5BA4BD3B7FAAD6FD4FAE38E52A5B71CD8','ARCHIVED','2025-06-27 14:22:08',0), ('2025_07_21_00_characters.sql','056A99B9AA90186E5B3177BF54C86607B6518BE9','ARCHIVED','2025-07-21 22:51:05',0), -('2025_08_13_00_characters.sql','9C2321777E6C34F18799C8967B1CA0C44F17DA18','ARCHIVED','2025-08-12 20:17:45',0), +('2025_08_13_00_characters.sql','0A559553A1DD9FC973AB01C3B1A9284B07CFBB36','ARCHIVED','2025-08-12 20:17:45',0), ('2025_08_16_00_characters.sql','8DF6284B6C7BEDAB599F7E4802FF8BCD80613A92','ARCHIVED','2025-08-16 14:58:22',0); /*!40000 ALTER TABLE `updates` ENABLE KEYS */; UNLOCK TABLES; diff --git a/sql/old/11.x/characters/25071_2025_08_16/2025_08_13_00_characters.sql b/sql/old/11.x/characters/25071_2025_08_16/2025_08_13_00_characters.sql index 0fbb7311b12..35bc8739c89 100644 --- a/sql/old/11.x/characters/25071_2025_08_16/2025_08_13_00_characters.sql +++ b/sql/old/11.x/characters/25071_2025_08_16/2025_08_13_00_characters.sql @@ -1,128 +1,159 @@ -ALTER TABLE `characters` - ADD `bankTabs` tinyint unsigned NOT NULL DEFAULT 0 AFTER `bankSlots`, - DROP `bankBagSlotFlags1`, - DROP `bankBagSlotFlags2`, - DROP `bankBagSlotFlags3`, - DROP `bankBagSlotFlags4`, - DROP `bankBagSlotFlags5`, - DROP `bankBagSlotFlags6`, - DROP `bankBagSlotFlags7`; - -UPDATE `characters` SET `bankTabs`=LEAST(6,GREATEST( - `bankSlots`, - IF((`playerFlags` & 0x20000000)<>0,6,0), -- unlocked void storage - IF((`playerFlagsEx` & 0x1)<>0,4,0), -- unlocked reagent bank - IF(`level`>=80,6,0) -)); - --- --- Table structure for table `character_bank_tab_settings` --- -DROP TABLE IF EXISTS `character_bank_tab_settings`; -CREATE TABLE `character_bank_tab_settings` ( - `characterGuid` bigint unsigned NOT NULL, - `tabId` tinyint unsigned NOT NULL, - `name` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `icon` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `description` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL, - `depositFlags` int DEFAULT '0', - PRIMARY KEY (`characterGuid`,`tabId`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - -INSERT INTO `character_bank_tab_settings` SELECT `guid`,0,'Tab 1',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 1; -INSERT INTO `character_bank_tab_settings` SELECT `guid`,1,'Tab 2',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 2; -INSERT INTO `character_bank_tab_settings` SELECT `guid`,2,'Tab 3',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 3; -INSERT INTO `character_bank_tab_settings` SELECT `guid`,3,'Reagents',NULL,NULL,0x80 FROM `characters` WHERE `bankTabs` >= 4; -INSERT INTO `character_bank_tab_settings` SELECT `guid`,4,'Void Storage 1',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 5; -INSERT INTO `character_bank_tab_settings` SELECT `guid`,5,'Void Storage 2',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 6; - --- helper columns --- character_inventory.bag_slot (inventory slot where the bag is stored) --- character_inventory.new_slot --- character_inventory.slot temporarily changed type to SMALLINT -ALTER TABLE `character_inventory` - ADD `bag_slot` INT, - ADD `new_slot` INT, - MODIFY `slot` SMALLINT UNSIGNED NOT NULL; - -UPDATE `character_inventory` ci1 -LEFT JOIN `character_inventory` ci2 ON ci1.bag = ci2.item -SET ci1.bag_slot = COALESCE(ci2.slot, 0); - --- move items that need moving out of the way -UPDATE `character_inventory` SET `slot`=`slot`+1000 WHERE `bag`=0 AND `slot` BETWEEN 63 AND 97; -- BankItemSlots/BankBagSlots -UPDATE `character_inventory` SET `slot`=`slot`+1000 WHERE `bag`=0 AND `slot` BETWEEN 110 AND 207; -- ReagentSlots - --- adjust BuyBackSlots/ChildEquipmentSlots/EquipableSpellSlots/AccountBankBagSlots -UPDATE `character_inventory` SET `slot`=`slot`-29 WHERE `bag`=0 AND `slot` BETWEEN 98 AND 109; -- BuyBackSlots -UPDATE `character_inventory` SET `slot`=`slot`-127 WHERE `bag`=0 AND `slot` BETWEEN 208 AND 231; -- ChildEquipmentSlots/EquipableSpellSlots/AccountBankBagSlots - -SET @item_guid := 0; -SELECT MAX(guid) INTO @item_guid FROM item_instance; - --- create new bags -INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`) -SELECT cbts.characterGuid, 0, 63 + cbts.`tabId`, (@item_guid := @item_guid + 1) FROM `character_bank_tab_settings` cbts; - -INSERT INTO `item_instance` (`guid`,`itemEntry`,`owner_guid`,`charges`,`flags`,`enchantments`,`playedTime`,`createTime`,`text`,`bonusListIDs`) -SELECT ci.`item`, 242709, ci.`guid`, '', 0x1, '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ', c.totaltime, UNIX_TIMESTAMP(),'','' -FROM `character_inventory` ci -LEFT JOIN `characters` c ON c.`guid` = ci.`guid` -WHERE ci.`bag` = 0 AND ci.`slot` BETWEEN 63 AND 68; - --- calculate new slots for old banked items -UPDATE `character_inventory` ci -LEFT JOIN ( - SELECT item, new_slot - FROM ( - SELECT guid, bag, slot, item, IF(@prev <> guid, @row_num:=0,@row_num:=@row_num+1) AS new_slot, @prev:=guid - FROM character_inventory, - (SELECT @row_num:=0) rn, - (SELECT @prev:='') prev - WHERE (bag_slot = 0 AND slot BETWEEN 1063 AND 1097) OR bag_slot BETWEEN 91 AND 97 - ORDER BY guid, bag_slot, slot - ) `window` -) new_slots ON new_slots.item = ci.item -SET ci.new_slot = new_slots.new_slot; - --- assign new bags and slots -UPDATE `character_inventory` ci -LEFT JOIN `character_inventory` ci2 ON ci2.guid = ci.guid AND ci2.bag = 0 AND ci2.slot = (ci.new_slot DIV 98) + 63 -SET ci.bag = ci2.item, ci.slot = ci.new_slot MOD 98 -WHERE (ci.bag_slot = 0 AND ci.slot BETWEEN 1063 AND 1097) OR ci.bag_slot BETWEEN 91 AND 97; - --- move reagents as-is (bag is the same size) -UPDATE `character_inventory` ci -LEFT JOIN `character_inventory` ci2 ON ci2.guid = ci.guid AND ci2.bag = 0 AND ci2.slot = 66 -SET ci.bag = ci2.item, ci.slot = ci.slot - 1110 -WHERE (ci.bag_slot = 0 AND ci.slot BETWEEN 1110 AND 1207); - --- void storage -ALTER TABLE `character_void_storage` - ADD `itemGuid` BIGINT UNSIGNED, - ADD `bagGuid` BIGINT UNSIGNED; - --- generate guids for void storage -UPDATE `character_void_storage` cvs -SET -cvs.`itemGuid` = (@item_guid := @item_guid + 1), -cvs.`bagGuid` = (SELECT ci.`item` FROM `character_inventory` ci WHERE ci.`guid` = cvs.`playerGuid` AND ci.`bag` = 0 AND ci.`slot` = 67 + (cvs.`slot` DIV 80)); - --- create items from void storage -INSERT INTO `item_instance` (`guid`, `itemEntry`, `owner_guid`, `creatorGuid`, `charges`, `flags`, `enchantments`, `randomBonusListId`, `durability`, `playedTime`, `createTime`, `text`, `context`, `bonusListIDs`) -SELECT cvs.`itemGuid`, cvs.`itemEntry`, cvs.`playerGuid`, cvs.`creatorGuid`, '', 0x1, '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ', cvs.`randomBonusListId`, 999, c.`totaltime`, UNIX_TIMESTAMP(), '', cvs.context, cvs.bonusListIDs FROM `character_void_storage` cvs -LEFT JOIN `characters` c ON c.`guid` = cvs.`playerGuid`; - -INSERT INTO `item_instance_modifiers` (`itemGuid`, `fixedScalingLevel`, `artifactKnowledgeLevel`) -SELECT cvs.`itemGuid`, COALESCE(cvs.`fixedScalingLevel`, 0), COALESCE(cvs.`artifactKnowledgeLevel`, 0) FROM `character_void_storage` cvs -WHERE cvs.`fixedScalingLevel` > 0 OR cvs.`artifactKnowledgeLevel` > 0; - -INSERT INTO `character_inventory` (`guid`, `bag`, `slot`, `item`) -SELECT cvs.`playerGuid`, cvs.`bagGuid`, cvs.`slot` MOD 80, cvs.`itemGuid` FROM `character_void_storage` cvs; - -ALTER TABLE `character_inventory` - DROP `bag_slot`, - DROP `new_slot`, - MODIFY `slot` TINYINT UNSIGNED NOT NULL DEFAULT 0; - -DROP TABLE IF EXISTS `character_void_storage`; +DELIMITER ;; + +CREATE PROCEDURE apply_if_not_exists_2025_08_13_00() +this_proc: BEGIN + + IF NOT EXISTS (SELECT * FROM `information_schema`.`tables` WHERE `table_schema`=SCHEMA() AND `table_name`='character_void_storage') THEN + -- all was good, executed last statement here (DROP TABLE) + LEAVE this_proc; + END IF; + + IF NOT EXISTS (SELECT * FROM `information_schema`.`columns` WHERE `table_schema`=SCHEMA() AND `table_name`='characters' AND `column_name`='bankTabs') THEN + ALTER TABLE `characters` + ADD `bankTabs` tinyint unsigned NOT NULL DEFAULT 0 AFTER `bankSlots`, + DROP `bankBagSlotFlags1`, + DROP `bankBagSlotFlags2`, + DROP `bankBagSlotFlags3`, + DROP `bankBagSlotFlags4`, + DROP `bankBagSlotFlags5`, + DROP `bankBagSlotFlags6`, + DROP `bankBagSlotFlags7`; + END IF; + + UPDATE `characters` SET `bankTabs`=LEAST(6,GREATEST( + `bankSlots`, + IF((`playerFlags` & 0x20000000)<>0,6,0), -- unlocked void storage + IF((`playerFlagsEx` & 0x1)<>0,4,0), -- unlocked reagent bank + IF(`level`>=80,6,0), + 1 + )); + + IF NOT EXISTS (SELECT * FROM `information_schema`.`tables` WHERE `table_schema`=SCHEMA() AND `table_name`='character_bank_tab_settings') THEN + -- + -- Table structure for table `character_bank_tab_settings` + -- + CREATE TABLE `character_bank_tab_settings` ( + `characterGuid` bigint unsigned NOT NULL, + `tabId` tinyint unsigned NOT NULL, + `name` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `icon` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `description` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `depositFlags` int DEFAULT '0', + PRIMARY KEY (`characterGuid`,`tabId`) + ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; + + INSERT INTO `character_bank_tab_settings` SELECT `guid`,0,'Tab 1',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 1; + INSERT INTO `character_bank_tab_settings` SELECT `guid`,1,'Tab 2',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 2; + INSERT INTO `character_bank_tab_settings` SELECT `guid`,2,'Tab 3',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 3; + INSERT INTO `character_bank_tab_settings` SELECT `guid`,3,'Reagents',NULL,NULL,0x80 FROM `characters` WHERE `bankTabs` >= 4; + INSERT INTO `character_bank_tab_settings` SELECT `guid`,4,'Void Storage 1',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 5; + INSERT INTO `character_bank_tab_settings` SELECT `guid`,5,'Void Storage 2',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 6; + END IF; + + SET @item_guid := 0; + SELECT MAX(guid) INTO @item_guid FROM item_instance; + + IF EXISTS (SELECT * FROM `information_schema`.`columns` WHERE `table_schema`=SCHEMA() AND `table_name`='character_inventory' AND `column_name`='bag_slot') THEN + -- rerunning in case of partially failed update + UPDATE `characters` SET `bankTabs` = 1 WHERE `bankTabs` = 0; + + -- create missing tab + INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`) + SELECT c.guid, 0, 63, (@item_guid := @item_guid + 1) FROM `characters` c LEFT JOIN `character_bank_tab_settings` cbts ON c.guid = cbts.characterGuid + WHERE c.`bankTabs` = 1 AND cbts.characterGuid IS NULL; + + INSERT IGNORE INTO `character_bank_tab_settings` SELECT `guid`,0,'Tab 1',NULL,NULL,0 FROM `characters` WHERE `bankTabs` >= 1; + ELSE + -- helper columns + -- character_inventory.bag_slot (inventory slot where the bag is stored) + -- character_inventory.new_slot + -- character_inventory.slot temporarily changed type to SMALLINT + ALTER TABLE `character_inventory` + ADD `bag_slot` INT, + ADD `new_slot` INT, + MODIFY `slot` SMALLINT UNSIGNED NOT NULL; + + UPDATE `character_inventory` ci1 + LEFT JOIN `character_inventory` ci2 ON ci1.bag = ci2.item + SET ci1.bag_slot = COALESCE(ci2.slot, 0); + + -- move items that need moving out of the way + UPDATE `character_inventory` SET `slot`=`slot`+1000 WHERE `bag`=0 AND `slot` BETWEEN 63 AND 97; -- BankItemSlots/BankBagSlots + UPDATE `character_inventory` SET `slot`=`slot`+1000 WHERE `bag`=0 AND `slot` BETWEEN 110 AND 207; -- ReagentSlots + + -- adjust BuyBackSlots/ChildEquipmentSlots/EquipableSpellSlots/AccountBankBagSlots + UPDATE `character_inventory` SET `slot`=`slot`-29 WHERE `bag`=0 AND `slot` BETWEEN 98 AND 109; -- BuyBackSlots + UPDATE `character_inventory` SET `slot`=`slot`-127 WHERE `bag`=0 AND `slot` BETWEEN 208 AND 231; -- ChildEquipmentSlots/EquipableSpellSlots/AccountBankBagSlots + + -- create new bags + INSERT INTO `character_inventory` (`guid`,`bag`,`slot`,`item`) + SELECT cbts.characterGuid, 0, 63 + cbts.`tabId`, (@item_guid := @item_guid + 1) FROM `character_bank_tab_settings` cbts; + + INSERT INTO `item_instance` (`guid`,`itemEntry`,`owner_guid`,`charges`,`flags`,`enchantments`,`playedTime`,`createTime`,`text`,`bonusListIDs`) + SELECT ci.`item`, 242709, ci.`guid`, '', 0x1, '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ', c.totaltime, UNIX_TIMESTAMP(),'','' + FROM `character_inventory` ci + LEFT JOIN `characters` c ON c.`guid` = ci.`guid` + WHERE ci.`bag` = 0 AND ci.`slot` BETWEEN 63 AND 68; + + -- calculate new slots for old banked items + UPDATE `character_inventory` ci + LEFT JOIN ( + SELECT item, new_slot + FROM ( + SELECT guid, bag, slot, item, IF(@prev <> guid, @row_num:=0,@row_num:=@row_num+1) AS new_slot, @prev:=guid + FROM character_inventory, + (SELECT @row_num:=0) rn, + (SELECT @prev:='') prev + WHERE (bag_slot = 0 AND slot BETWEEN 1063 AND 1097) OR bag_slot BETWEEN 91 AND 97 + ORDER BY guid, bag_slot, slot + ) `window` + ) new_slots ON new_slots.item = ci.item + SET ci.new_slot = new_slots.new_slot; + END IF; + + -- assign new bags and slots + UPDATE `character_inventory` ci + LEFT JOIN `character_inventory` ci2 ON ci2.guid = ci.guid AND ci2.bag = 0 AND ci2.slot = (ci.new_slot DIV 98) + 63 + SET ci.bag = ci2.item, ci.slot = ci.new_slot MOD 98 + WHERE (ci.bag_slot = 0 AND ci.slot BETWEEN 1063 AND 1097) OR ci.bag_slot BETWEEN 91 AND 97; + + -- move reagents as-is (bag is the same size) + UPDATE `character_inventory` ci + LEFT JOIN `character_inventory` ci2 ON ci2.guid = ci.guid AND ci2.bag = 0 AND ci2.slot = 66 + SET ci.bag = ci2.item, ci.slot = ci.slot - 1110 + WHERE (ci.bag_slot = 0 AND ci.slot BETWEEN 1110 AND 1207); + + -- void storage + ALTER TABLE `character_void_storage` + ADD `itemGuid` BIGINT UNSIGNED, + ADD `bagGuid` BIGINT UNSIGNED; + + -- generate guids for void storage + UPDATE `character_void_storage` cvs + SET + cvs.`itemGuid` = (@item_guid := @item_guid + 1), + cvs.`bagGuid` = (SELECT ci.`item` FROM `character_inventory` ci WHERE ci.`guid` = cvs.`playerGuid` AND ci.`bag` = 0 AND ci.`slot` = 67 + (cvs.`slot` DIV 80)); + + -- create items from void storage + INSERT INTO `item_instance` (`guid`, `itemEntry`, `owner_guid`, `creatorGuid`, `charges`, `flags`, `enchantments`, `randomBonusListId`, `durability`, `playedTime`, `createTime`, `text`, `context`, `bonusListIDs`) + SELECT cvs.`itemGuid`, cvs.`itemEntry`, cvs.`playerGuid`, cvs.`creatorGuid`, '', 0x1, '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ', cvs.`randomBonusListId`, 999, c.`totaltime`, UNIX_TIMESTAMP(), '', cvs.context, cvs.bonusListIDs FROM `character_void_storage` cvs + LEFT JOIN `characters` c ON c.`guid` = cvs.`playerGuid`; + + INSERT INTO `item_instance_modifiers` (`itemGuid`, `fixedScalingLevel`, `artifactKnowledgeLevel`) + SELECT cvs.`itemGuid`, COALESCE(cvs.`fixedScalingLevel`, 0), COALESCE(cvs.`artifactKnowledgeLevel`, 0) FROM `character_void_storage` cvs + WHERE cvs.`fixedScalingLevel` > 0 OR cvs.`artifactKnowledgeLevel` > 0; + + INSERT INTO `character_inventory` (`guid`, `bag`, `slot`, `item`) + SELECT cvs.`playerGuid`, cvs.`bagGuid`, cvs.`slot` MOD 80, cvs.`itemGuid` FROM `character_void_storage` cvs; + + ALTER TABLE `character_inventory` + DROP `bag_slot`, + DROP `new_slot`, + MODIFY `slot` TINYINT UNSIGNED NOT NULL DEFAULT 0; + + DROP TABLE IF EXISTS `character_void_storage`; +END;; + +DELIMITER ; + +CALL apply_if_not_exists_2025_08_13_00(); |