jSsp4Qjf

1DROP TABLE IF EXISTS guild_duplicates;
2CREATE TABLE guild_duplicates (
3    guild_id INT(10) DEFAULT 0,
4    copied_guild_id INT(10) NOT NULL,
5    PRIMARY KEY (`copied_guild_id`)
6);
7
8-- Tworze spejalna tabele, gdzie bede trzymal id gildii, ktore sa zduplowane
9-- guild_id to wlasciwe, 1. id gildii
10-- copied_guild_id to id kopii gildii, ktora bede upnieta do tej 1.
11
12-- Insertuje do tabeli tylko takie id, ktore sa duplikatami (czyli id maja wyzsze od najnizszego - to bedzie nasza wlasciwa gildia)
13INSERT IGNORE INTO guild_duplicates
14SELECT n1.guildid, n2.guildid FROM guild n1, guild n2 WHERE n2.guildid > n1.guildid AND n1.name = n2.name;
15
16-- Tutaj przerzucam memberow gildi, ktore sa duplikatami, do wlasciwej gildii
17DROP PROCEDURE IF EXISTS UpdateGuildMembers;
18DELIMITER //
19CREATE PROCEDURE UpdateGuildMembers()
20BEGIN
21	DECLARE guild_id_ INT;
22	DECLARE copied_guild_id_ INT;
23	DECLARE currentCount INT;
24	DECLARE entryCount INT;
25	
26	DECLARE cur1 CURSOR FOR SELECT guild_id, copied_guild_id FROM guild_duplicates ORDER BY copied_guild_id_;
27	
28	SET currentCount = 1;
29
30	SELECT COUNT(*) INTO entryCount FROM guild_duplicates;
31	
32	OPEN cur1;
33	WHILE currentCount <= entryCount DO
34		FETCH cur1 INTO guild_id_, copied_guild_id_;
35		UPDATE `guild_member` SET `guildid`=`guild_id_` WHERE `guildid`=`copied_guild_id_`;
36		SET currentCount = currentCount + 1;
37	END WHILE;
38END //
39DELIMITER ;
40
41CALL UpdateGuildMembers();
42
43-- Usuwam teraz zduplikowane gildie (members maja juz wlasciwe guildid)
44DELETE FROM `guild` WHERE `guildid` IN (SELECT copied_guild_id FROM guild_duplicates);
45
46DROP TABLE guild_duplicates;
47
48-- // Guild Ranks
49
50-- Usuwam z tabeli guild_rank nie istniejace gildie
51DELETE FROM `guild_rank` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
52
53-- Tutaj dodaje 2 podstawowe ranki dla gildi, majacych tylko 1 rank
54DROP PROCEDURE IF EXISTS AddMissingRank;
55DELIMITER //
56CREATE PROCEDURE AddMissingRank()
57BEGIN
58	DECLARE guild_id_ INT;
59	DECLARE currentCount INT;
60	DECLARE entryCount INT;
61	
62	DECLARE cur1 CURSOR FOR SELECT guildid FROM guild_rank WHERE guildid IN (SELECT guildid FROM guild) AND guildid IN (SELECT guildid FROM guild_rank GROUP BY guildid HAVING COUNT(rid) = 1) ORDER BY guildid;
63	
64	SET currentCount = 1;
65
66	SELECT COUNT(*) INTO entryCount FROM guild_rank WHERE guildid IN (SELECT guildid FROM guild) AND guildid IN (SELECT guildid FROM guild_rank GROUP BY guildid HAVING COUNT(rid) = 1) ORDER BY guildid;
67	
68	OPEN cur1;
69	WHILE currentCount <= entryCount DO
70		FETCH cur1 INTO guild_id_;
71		DELETE FROM `guild_rank` WHERE `guildid`=guild_id_;
72		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,1,'Guild Master',913919,0);
73		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,2,'Member',67,0);
74		SET currentCount = currentCount + 1;
75	END WHILE;
76END //
77DELIMITER ;
78
79CALL AddMissingRank();
80
81-- Tutaj dodaje 2 podstawowe ranki dla gildi, ktore nie maja wpisow w guild_rank
82DROP PROCEDURE IF EXISTS CreateGuildRanks;
83DELIMITER //
84CREATE PROCEDURE CreateGuildRanks()
85BEGIN
86	DECLARE guild_id_ INT;
87	DECLARE currentCount INT;
88	DECLARE entryCount INT;
89	
90	DECLARE cur1 CURSOR FOR SELECT guildid FROM guild WHERE guildid NOT IN (SELECT guildid FROM guild_rank) ORDER BY guildid;
91	
92	SET currentCount = 1;
93
94	SELECT COUNT(*) INTO entryCount FROM guild WHERE guildid NOT IN (SELECT guildid FROM guild_rank) ORDER BY guildid;
95	
96	OPEN cur1;
97	WHILE currentCount <= entryCount DO
98		FETCH cur1 INTO guild_id_;
99		DELETE FROM `guild_rank` WHERE `guildid`=guild_id_;
100		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,1,'Guild Master',913919,0);
101		INSERT IGNORE INTO `guild_rank` VALUES (guild_id_,2,'Member',67,0);
102		SET currentCount = currentCount + 1;
103	END WHILE;
104END //
105DELIMITER ;
106
107CALL CreateGuildRanks();
108
109-- Guild Members
110
111-- Tutaj ustawiam tylko jednej osobie guild mastera, a innym, którzy go mieli, ustawiam rank niżej
112-- Dodatkowo ustawiam na rank niżej dla osob, którzy mają nieistniejace ranki
113DROP PROCEDURE IF EXISTS UpdateGuildMemberRanks;
114DELIMITER //
115CREATE PROCEDURE UpdateGuildMemberRanks()
116BEGIN
117	DECLARE guild_id_ INT;
118	DECLARE guild_leader_ INT;
119	DECLARE currentCount INT;
120	DECLARE entryCount INT;
121	
122	DECLARE cur1 CURSOR FOR SELECT guildid, leaderguid FROM guild ORDER BY guildid;
123	
124	SET currentCount = 1;
125
126	SELECT COUNT(*) INTO entryCount FROM guild ORDER BY guildid;
127	
128	OPEN cur1;
129	WHILE currentCount <= entryCount DO
130		FETCH cur1 INTO guild_id_, guild_leader_;
131		UPDATE `guild_member` SET `rank`=0 WHERE `guildid`=guild_id_ AND `guid`=guild_leader_;
132		UPDATE `guild_member` SET `rank`=1 WHERE `guildid`=guild_id_ AND `guid` != guild_leader_ AND `rank`=0;
133		UPDATE `guild_member` SET `rank`=1 WHERE `guildid`=guild_id_ AND `guid` != guild_leader_ AND `rank` NOT IN (SELECT `rid`-1 FROM `guild_rank` WHERE `guildid`=guild_id_);
134		SET currentCount = currentCount + 1;
135	END WHILE;
136END //
137DELIMITER ;
138
139CALL UpdateGuildMemberRanks();
140
141-- Usuwam pozostałe tabele z nieistniejących gildi (nie maja w bazie gauil bankow ani itemow)
142DELETE FROM `guild_announce_cooldown` WHERE `guild_id` NOT IN (SELECT `guildid` FROM `guild`);
143DELETE FROM `guild_bank_eventlog` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
144DELETE FROM `guild_bank_item` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
145DELETE FROM `guild_bank_right` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
146DELETE FROM `guild_bank_tab` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
147DELETE FROM `guild_eventlog` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);