Core/Players: Update profession rank handling with new split skill values for each expansion

1 files changed, 135 insertions, 0 deletions

diff --git a/sql/updates/characters/master/2018_12_02_00_characters.sql b/sql/updates/characters/master/2018_12_02_00_characters.sql
new file mode 100644
index 00000000000..6c95b00778d
--- /dev/null
+++ b/sql/updates/characters/master/2018_12_02_00_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`;