PmghZb17

1-- USE .saveall TO SAVE PLAYERS BEFORE RUNNING. SERVER NEEDS TO BE ONLINE
2
3-- custom table, clear old data
4DROP TABLE IF EXISTS `custom_item_add_table`;
5CREATE TABLE `custom_item_add_table` (
6`guid` INT(10) UNSIGNED NOT NULL
7)
8ENGINE=InnoDB;
9-- fill online players
10INSERT INTO `custom_item_add_table` (`guid`) SELECT guid FROM `characters` WHERE online <> 0;
11
12-- RUN WHEN SERVER IS OFFLINE
13
14-- Test that table exists
15SELECT 1 FROM `custom_item_add_table` LIMIT 1;
16
17-- Used to determine item guids to send through mail. Inventory can be full!
18SET @MAXGUID := (SELECT IFNULL(max(guid), 0) from `item_instance`);
19
20-- Make item_instance table auto increment easy
21ALTER TABLE `item_instance`
22CHANGE COLUMN `guid` `guid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
23
24-- Insert gift items to DB | MAKE SURE THIS IS CORRECT! (Durability, count, flags ..)
25INSERT INTO `item_instance` (`itemEntry`, `owner_guid`, `creatorGuid`, `giftCreatorGuid`, `count`, `duration`, `charges`, `flags`, `enchantments`, `randomPropertyId`, `durability`, `playedTime`, `text`)
26SELECT 25, guid, 0, 0, 1, 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 0 0 0 0 ', 0, 0, 0, '' FROM `custom_item_add_table`;
27
28-- Restore item_instance table default value
29ALTER TABLE `item_instance`
30CHANGE COLUMN `guid` `guid` INT(10) UNSIGNED NOT NULL DEFAULT '0' FIRST;
31
32-- Used to determine the mails for mail_items
33SET @MAXID := (SELECT IFNULL(max(id), 0) from `mail`);
34
35-- Make mail table auto increment easy
36ALTER TABLE `mail`
37CHANGE COLUMN `id` `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Identifier' FIRST;
38
39-- MAKE SURE THIS IS OK
40INSERT INTO `mail` (`messageType`, `stationery`, `mailTemplateId`, `sender`, `receiver`, `subject`, `body`, `has_items`, `expire_time`, `deliver_time`, `money`, `cod`, `checked`)
41SELECT 0, 41, 0, 0, guid, 'Test', 'Test', 1, 0, 0, 0, 0, 0 FROM `custom_item_add_table`;
42
43-- Restore mail table default value
44ALTER TABLE `mail`
45CHANGE COLUMN `id` `id` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Identifier' FIRST;
46
47-- Insert item to mail relations
48INSERT INTO `mail_items` (`mail_id`, `item_guid`, `receiver`)
49SELECT mail.id, item_instance.guid, mail.receiver FROM item_instance, mail WHERE mail.id > @MAXID and item_instance.guid > @MAXGUID and mail.receiver = item_instance.owner_guid;