From c8413a7f27cdb0de2f93d2437decdbc1628cb69e Mon Sep 17 00:00:00 2001 From: leak Date: Thu, 28 Apr 2011 22:46:40 +0200 Subject: Core/ObjectMgr: Refactor sCreatureDataAddonStorage --- .../world/2011_04_28_02_world_creature_addon.sql | 51 ++++++++++++++++++++++ 1 file changed, 51 insertions(+) create mode 100644 sql/updates/world/2011_04_28_02_world_creature_addon.sql (limited to 'sql') diff --git a/sql/updates/world/2011_04_28_02_world_creature_addon.sql b/sql/updates/world/2011_04_28_02_world_creature_addon.sql new file mode 100644 index 00000000000..5084aaf8b60 --- /dev/null +++ b/sql/updates/world/2011_04_28_02_world_creature_addon.sql @@ -0,0 +1,51 @@ +CREATE TABLE `temp_auras` ( + `spell` MEDIUMINT(8) UNSIGNED NOT NULL +) ENGINE=MYISAM DEFAULT CHARSET=utf8; + +DELIMITER %% + +CREATE FUNCTION `ConvertAuras`(`auras` VARCHAR(1024)) +RETURNS VARCHAR(1024) CHARSET utf8 +DETERMINISTIC +BEGIN + DECLARE tmp VARCHAR(1024); + DECLARE curr VARCHAR(10); + DECLARE k INT; + DECLARE pos INT; + DECLARE startp INT; + + SET @k = 0; + SET @tmp = ''; + SET @startp = 1; + SET @pos = LOCATE(' ', auras); + + DELETE FROM temp_auras; + + WHILE @pos > 0 DO + IF @k = 0 THEN + SET @curr = SUBSTR(auras, @startp, @pos - @startp); + + IF NOT EXISTS(SELECT spell FROM temp_auras WHERE spell = @curr) THEN + SET @tmp = CONCAT(@tmp, @curr, ' '); + INSERT INTO temp_auras VALUES(@curr); + END IF; + END IF; + + SET @k = 1-@k; + SET @startp = @pos+1; + SET @pos = LOCATE(' ', auras, @startp); + END WHILE; + + SET @tmp = RTRIM(@tmp); + RETURN @tmp; +END%% + +DELIMITER ; + +UPDATE `creature_addon` SET `auras` = REPLACE(`auras`, ' ', ' '); +UPDATE `creature_addon` SET `auras` = TRIM(`auras`); +UPDATE `creature_addon` SET `auras` = NULL WHERE `auras` = ''; +UPDATE `creature_addon` SET `auras` = ConvertAuras(`auras`) WHERE `auras` IS NOT NULL; + +DROP FUNCTION `ConvertAuras`; +DROP TABLE `temp_auras`; \ No newline at end of file -- cgit v1.2.3