diff options
Diffstat (limited to 'sql/updates/characters')
3 files changed, 175 insertions, 0 deletions
diff --git a/sql/updates/characters/master/2018_12_09_00_characters.sql b/sql/updates/characters/master/2018_12_09_00_characters.sql new file mode 100644 index 00000000000..8a2dc041fe3 --- /dev/null +++ b/sql/updates/characters/master/2018_12_09_00_characters.sql @@ -0,0 +1,8 @@ +TRUNCATE `character_pvp_talent`; +ALTER TABLE `character_pvp_talent` + DROP PRIMARY KEY, + CHANGE `talentId` `talentId0` int(10) unsigned NOT NULL AFTER `guid`, + ADD `talentId1` int(10) unsigned NOT NULL AFTER `talentId0`, + ADD `talentId2` int(10) unsigned NOT NULL AFTER `talentId1`, + ADD `talentId3` int(10) unsigned NOT NULL AFTER `talentId2`, + ADD PRIMARY KEY(`guid`,`talentGroup`); diff --git a/sql/updates/characters/master/2018_12_09_01_characters.sql b/sql/updates/characters/master/2018_12_09_01_characters.sql new file mode 100644 index 00000000000..6ccb47f47e3 --- /dev/null +++ b/sql/updates/characters/master/2018_12_09_01_characters.sql @@ -0,0 +1,32 @@ +DROP TABLE IF EXISTS `total_honor_at_honor_level`; +CREATE TABLE `total_honor_at_honor_level` ( + `HonorLevel` int(10) UNSIGNED NOT NULL, + `Prestige0` int(10) UNSIGNED NOT NULL, + `Prestige1` int(10) UNSIGNED NOT NULL, + PRIMARY KEY (`HonorLevel`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `total_honor_at_honor_level` VALUES +(0,0,0),(1,350,800),(2,700,1600),(3,1050,2400),(4,1400,3200), +(5,1750,4000),(6,2100,4800),(7,2450,5600),(8,2800,6400),(9,3150,7200), +(10,3500,8000),(11,3900,8850),(12,4300,9700),(13,4700,10550),(14,5100,11400), +(15,5500,12250),(16,5900,13100),(17,6300,13950),(18,6700,14800),(19,7100,15650), +(20,7500,16500),(21,7950,17400),(22,8400,18300),(23,8850,19200),(24,9300,20100), +(25,9750,21000),(26,10200,21900),(27,10650,22800),(28,11100,23700),(29,11550,24600), +(30,12000,25500),(31,12500,26450),(32,13000,27400),(33,13500,28350),(34,14000,29300), +(35,14500,30250),(36,15000,31200),(37,15500,32150),(38,16000,33100),(39,16500,34050), +(40,17000,35000),(41,17550,36000),(42,18100,37000),(43,18650,38000),(44,19200,39000), +(45,19750,40000),(46,20300,41000),(47,20850,42000),(48,21400,43000),(49,21950,44000); + +-- first compensate for prestige levels above first +UPDATE `characters` SET `honor`=`honor`+44000*(`prestigeLevel`-1) WHERE `prestigeLevel`>0; +-- compensate for honor levels in prestige for characters above first prestige +UPDATE `characters` SET `honor`=`honor`+(SELECT th.`Prestige1` FROM `total_honor_at_honor_level` th WHERE th.`HonorLevel`=(`characters`.`honorLevel`-1)) WHERE `prestigeLevel`>0; +-- compensate for honor levels in first prestige level +UPDATE `characters` SET `honor`=`honor`+(SELECT th.`Prestige0` FROM `total_honor_at_honor_level` th WHERE th.`HonorLevel`=(`characters`.`honorLevel`-1)) WHERE `prestigeLevel`=0; + +-- reset honor levels, will be recalculated from refunded honor at first login (and grant achievements) +UPDATE `characters` SET `honorLevel`=1; + +ALTER TABLE `characters` DROP `prestigeLevel`; +DROP TABLE IF EXISTS `total_honor_at_honor_level`; diff --git a/sql/updates/characters/master/2018_12_09_02_characters.sql b/sql/updates/characters/master/2018_12_09_02_characters.sql new file mode 100644 index 00000000000..6c95b00778d --- /dev/null +++ b/sql/updates/characters/master/2018_12_09_02_characters.sql @@ -0,0 +1,135 @@ +-- +-- Table structure for table `profession_skill_migration_data` +-- +DROP TABLE IF EXISTS `profession_skill_migration_data`; +CREATE TABLE `profession_skill_migration_data` ( + `SkillID` int(10) unsigned, + `ParentSkillLineID` int(10) unsigned, + `MaxValue` int(10) unsigned, + `NewMaxValue` int(10) unsigned, + `SpellID_A` int(10) unsigned, + `SpellID_H` int(10) unsigned, + PRIMARY KEY (`SkillID`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- +-- Dumping data for table `profession_skill_migration_data` +-- +INSERT INTO `profession_skill_migration_data` VALUES +(2437,164,900,150,264448,265803), +(2454,164,800,100,264446,264446), +(2472,164,700,100,264444,264444), +(2473,164,600,75,264442,264442), +(2474,164,525,75,264440,264440), +(2475,164,450,75,264438,264438), +(2476,164,375,75,264436,264436), +(2477,164,300,300,264434,264434), +(2478,171,900,150,264255,265787), +(2479,171,800,100,264250,264250), +(2480,171,700,100,264247,264247), +(2481,171,600,75,264245,264245), +(2482,171,525,75,264243,264243), +(2483,171,450,75,264220,264220), +(2484,171,375,75,264213,264213), +(2485,171,300,300,264211,264211), +(2486,333,900,150,264473,265805), +(2487,333,800,100,264471,264471), +(2488,333,700,100,264469,264469), +(2489,333,600,75,264467,264467), +(2491,333,525,75,264464,264464), +(2492,333,450,75,264462,264462), +(2493,333,375,75,264460,264460), +(2494,333,300,300,264455,264455), +(2499,202,900,150,264492,265807), +(2500,202,800,100,264490,264490), +(2501,202,700,100,264487,264487), +(2502,202,600,75,264485,264485), +(2503,202,525,75,264483,264483), +(2504,202,450,75,264481,264481), +(2505,202,375,75,264479,264479), +(2506,202,300,300,264475,264475), +(2507,773,900,150,264508,265809), +(2508,773,800,100,264506,264506), +(2509,773,700,100,264504,264504), +(2510,773,600,75,264502,264502), +(2511,773,525,75,264500,264500), +(2512,773,450,75,264498,264498), +(2513,773,375,75,264496,264496), +(2514,773,300,300,264494,264494), +(2517,755,900,150,264548,265811), +(2518,755,800,100,264546,264546), +(2519,755,700,100,264544,264544), +(2520,755,600,75,264542,264542), +(2521,755,525,75,264539,264539), +(2522,755,450,75,264537,264537), +(2523,755,375,75,264534,264534), +(2524,755,300,300,264532,264532), +(2525,165,900,150,264592,265813), +(2526,165,800,100,264590,264590), +(2527,165,700,100,264588,264588), +(2528,165,600,75,264585,264585), +(2529,165,525,75,264583,264583), +(2530,165,450,75,264581,264581), +(2531,165,375,75,264579,264579), +(2532,165,300,300,264577,264577), +(2533,197,900,150,264630,265815), +(2534,197,800,100,264628,264628), +(2535,197,700,100,264626,264626), +(2536,197,600,75,264624,264624), +(2537,197,525,75,264622,264622), +(2538,197,450,75,264620,264620), +(2539,197,375,75,264618,264618), +(2540,197,300,300,264616,264616), +(2541,185,825,150,264646,265817), +(2542,185,750,100,264644,264644), +(2543,185,700,100,264642,264642), +(2544,185,600,75,264640,264640), +(2545,185,525,75,264638,264638), +(2546,185,450,75,264636,264636), +(2547,185,375,75,264634,264634), +(2548,185,300,300,264632,264632), +(2549,182,900,150,265831,265835), +(2550,182,800,100,265834,265834), +(2551,182,700,100,265829,265829), +(2552,182,600,75,265827,265827), +(2553,182,525,75,265825,265825), +(2554,182,450,75,265823,265823), +(2555,182,375,75,265821,265821), +(2556,182,300,300,265819,265819), +(2557,393,900,150,265869,265871), +(2558,393,800,100,265867,265867), +(2559,393,700,100,265865,265865), +(2560,393,600,75,265863,265863), +(2561,393,525,75,265861,265861), +(2562,393,450,75,265859,265859), +(2563,393,375,75,265857,265857), +(2564,393,300,300,265855,265855), +(2565,186,900,150,265851,265853), +(2566,186,800,100,265849,265849), +(2567,186,700,100,265847,265847), +(2568,186,600,75,265845,265845), +(2569,186,525,75,265843,265843), +(2570,186,450,75,265841,265841), +(2571,186,375,75,265839,265839), +(2572,186,300,300,265837,265837), +(2585,356,825,150,271675,271677), +(2586,356,750,100,271672,271672), +(2587,356,700,100,271664,271664), +(2588,356,600,75,271662,271662), +(2589,356,525,75,271660,271660), +(2590,356,450,75,271658,271658), +(2591,356,375,75,271656,271656), +(2592,356,300,300,271616,271616); + +INSERT IGNORE INTO `character_spell` +SELECT cs.`guid`, IF(c.`race` IN (1,3,4,7,11,22,25,29,30,34), psmd.`SpellID_A`, psmd.`SpellID_H`), 1, 0 +FROM `profession_skill_migration_data` psmd +INNER JOIN `character_skills` cs ON psmd.`ParentSkillLineID` = cs.`skill` AND psmd.`MaxValue` <= cs.`max` +INNER JOIN `characters` c ON cs.`guid` = c.`guid`; + +INSERT IGNORE INTO `character_skills` +SELECT cs.`guid`, psmd.`SkillID`, CASE WHEN psmd.`MaxValue` < cs.`value` THEN psmd.`NewMaxValue` WHEN psmd.`MaxValue` - cs.`value` < psmd.`NewMaxValue` THEN psmd.`NewMaxValue` + cs.`value` - psmd.`MaxValue` ELSE 1 END, psmd.`NewMaxValue` +FROM `profession_skill_migration_data` psmd +INNER JOIN `character_skills` cs ON psmd.`ParentSkillLineID` = cs.`skill` AND psmd.`MaxValue` <= cs.`max`; + +DROP TABLE IF EXISTS `profession_skill_migration_data`; |