-
Notifications
You must be signed in to change notification settings - Fork 53
/
Rel21_07_015__item_loot_template_cleanup.sql
160 lines (136 loc) · 7.58 KB
/
Rel21_07_015__item_loot_template_cleanup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
-- ----------------------------------------
-- Added to prevent timeout's while loading
-- ----------------------------------------
SET GLOBAL net_read_timeout=30;
SET GLOBAL net_write_timeout=60;
SET GLOBAL net_buffer_length=1000000;
SET GLOBAL max_allowed_packet=1000000000;
SET GLOBAL connect_timeout=10000000;
-- --------------------------------------------------------------------------------
-- This is an attempt to create a full transactional MaNGOS update (v1.3)
-- --------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `update_mangos`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_mangos`()
BEGIN
DECLARE bRollback BOOL DEFAULT FALSE ;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `bRollback` = TRUE;
-- Current Values (TODO - must be a better way to do this)
SET @cCurVersion := (SELECT `version` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurStructure := (SELECT structure FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurContent := (SELECT content FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
-- Expected Values
SET @cOldVersion = '21';
SET @cOldStructure = '07';
SET @cOldContent = '014';
-- New Values
SET @cNewVersion = '21';
SET @cNewStructure = '07';
SET @cNewContent = '015';
-- DESCRIPTION IS 30 Characters MAX
SET @cNewDescription = '_item_loot_template_cleanup';
-- COMMENT is 150 Characters MAX
SET @cNewComment = '_item_loot_template_cleanup';
-- Evaluate all settings
SET @cCurResult := (SELECT description FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cOldResult := (SELECT description FROM db_version WHERE `version`=@cOldVersion AND `structure`=@cOldStructure AND `content`=@cOldContent);
SET @cNewResult := (SELECT description FROM db_version WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent);
IF (@cCurResult = @cOldResult) THEN -- Does the current version match the expected version
-- APPLY UPDATE
START TRANSACTION;
-- UPDATE THE DB VERSION
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
INSERT INTO `db_version` VALUES (@cNewVersion, @cNewStructure, @cNewContent, @cNewDescription, @cNewComment);
SET @cNewResult := (SELECT description FROM db_version WHERE `version`=@cNewVersion AND `structure`=@cNewStructure AND `content`=@cNewContent);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- -- PLACE UPDATE SQL BELOW -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- The following items now contain proper loot.
-- Quartermaster Zigris Footlocker, Followup Combat Assignment, Bloated Oily Blackmouth, Bloated Rockscale Cod, Bloated Mightfish, Tier 5 Druid Test Gear Box 2.
DELETE FROM `item_loot_template` WHERE entry IN (13247,32544,21243,21164,21162,21131);
INSERT INTO `item_loot_template` (`entry`, `item`, `ChanceOrQuestChance`, `groupid`, `mincountOrRef`, `maxcount`, `condition_id`) VALUES
(21131,20943,10,1,1,1,0),
(21131,20944,10,1,1,1,0),
(21131,20945,10,1,1,1,0),
(21131,20947,10,1,1,1,0),
(21131,20948,10,1,1,1,0),
(21131,21165,10,1,1,1,0),
(21131,21166,10,1,1,1,0),
(21131,21167,10,1,1,1,0),
(21131,21245,10,1,1,1,0),
(21131,21751,10,1,1,1,0),
(21162,774,9,0,1,3,0),
(21162,818,40,0,1,3,0),
(21162,1206,11,0,1,2,0),
(21162,1210,40,0,1,2,0),
(21164,1529,43,1,1,2,0),
(21164,1705,14,1,1,3,0),
(21164,3864,28,1,1,3,0),
(21164,5009,1,0,1,1,0),
(21164,7909,5,1,1,1,0),
(21164,11986,1.7,0,1,1,0),
(21164,12009,1,0,1,1,0),
(21164,12029,0.3,0,1,1,0),
(21243,7553,4,0,1,1,0),
(21243,7909,48,0,1,3,0),
(21243,7910,30,0,1,3,0),
(32544,30216,100,0,1,1,0),
(32544,30217,100,0,1,1,0),
(32544,30219,100,0,1,1,0),
(32544,30220,100,0,1,1,0),
(32544,30222,100,0,1,1,0),
(32544,30223,100,0,1,1,0),
(32544,30228,100,0,1,1,0),
(32544,30229,100,0,1,1,0),
(32544,30230,100,0,1,1,0),
(32544,30231,100,0,1,1,0),
(32544,30232,100,0,1,1,0),
(32544,30233,100,0,1,1,0),
(32544,30234,100,0,1,1,0),
(32544,30235,100,0,1,1,0),
(13247,16252,50,0,1,1,0),
(13247,13252,50,0,1,1,0);
-- Removed in patch 1.5
DELETE FROM `item_template` WHERE entry IN (18636,6715,10595);
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- -- PLACE UPDATE SQL ABOVE -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
-- If we get here ok, commit the changes
IF bRollback = TRUE THEN
ROLLBACK;
SHOW ERRORS;
SELECT '* UPDATE FAILED *' AS `===== Status =====`,@cCurResult AS `===== DB is on Version: =====`;
ELSE
COMMIT;
SELECT '* UPDATE COMPLETE *' AS `===== Status =====`,@cNewResult AS `===== DB is now on Version =====`;
END IF;
ELSE -- Current version is not the expected version
IF (@cCurResult = @cNewResult) THEN -- Does the current version match the new version
SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cCurResult AS `===== DB is already on Version =====`;
ELSE -- Current version is not one related to this update
IF(@cCurResult IS NULL) THEN -- Something has gone wrong
SELECT '* UPDATE FAILED *' AS `===== Status =====`,'Unable to locate DB Version Information' AS `============= Error Message =============`;
ELSE
IF(@cOldResult IS NULL) THEN -- Something has gone wrong
SET @cCurVersion := (SELECT `version` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurStructure := (SELECT `STRUCTURE` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurContent := (SELECT `Content` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult);
SET @cOldResult = CONCAT('Rel',@cOldVersion, '_', @cOldStructure, '_', @cOldContent, ' - ','IS NOT APPLIED');
SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`;
ELSE
SET @cCurVersion := (SELECT `version` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurStructure := (SELECT `STRUCTURE` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurContent := (SELECT `Content` FROM db_version ORDER BY `version` DESC, STRUCTURE DESC, CONTENT DESC LIMIT 0,1);
SET @cCurOutput = CONCAT(@cCurVersion, '_', @cCurStructure, '_', @cCurContent, ' - ',@cCurResult);
SELECT '* UPDATE SKIPPED *' AS `===== Status =====`,@cOldResult AS `=== Expected ===`,@cCurOutput AS `===== Found Version =====`;
END IF;
END IF;
END IF;
END IF;
END $$
DELIMITER ;
-- Execute the procedure
CALL update_mangos();
-- Drop the procedure
DROP PROCEDURE IF EXISTS `update_mangos`;