New skills table columns

[WafflesMcDuff]

skill_id, label, parent, level_1_name, level_1_description, [... level_5_name, level_5_description], status, date_created, date_modified

status = alpha, beta, active, retired

[WafflesMcDuff]

This query will let us convert the current ecc_skills_allskills and ecc_skills_groups tables into the new, simpler, ecc_skills table

SELECT g.primaryskill_id AS skill_id, g.NAME, g.psychic, g.status, SUBSTRING_INDEX(L1.skill_index, '_', 1) AS skill_index, NULL AS parents,
L1.label AS level_1_name, L1.description AS level_1_description,
L2.label AS level_2_name, L2.description AS level_2_description,
L3.label AS level_3_name, L3.description AS level_3_description,
L4.label AS level_4_name, L4.description AS level_4_description,
L5.label AS level_5_name, L5.description AS level_5_description
FROM ecc_skills_groups g
JOIN ecc_skills_allskills L1 ON ((L1.parent = g.primaryskill_id) AND (L1.level = 1))
JOIN ecc_skills_allskills L2 ON ((L2.parent = g.primaryskill_id) AND (L2.level = 2))
JOIN ecc_skills_allskills L3 ON ((L3.parent = g.primaryskill_id) AND (L3.level = 3))
JOIN ecc_skills_allskills L4 ON ((L4.parent = g.primaryskill_id) AND (L4.level = 4))
JOIN ecc_skills_allskills L5 ON ((L5.parent = g.primaryskill_id) AND (L5.level = 5))

UNION

SELECT g.primaryskill_id AS skill_id, g.NAME, g.psychic, g.status, SUBSTRING_INDEX(L1.skill_index, '_', 1) AS skill_index, g.parents,
L1.label AS level_1_name, L1.description AS level_1_description,
L2.label AS level_2_name, L2.description AS level_2_description,
L3.label AS level_3_name, L3.description AS level_3_description,
L4.label AS level_4_name, L4.description AS level_4_description,
L5.label AS level_5_name, L5.description AS level_5_description
FROM ecc_skills_groups g
JOIN ecc_skills_allskills L1 ON ((L1.parent = g.primaryskill_id) AND (L1.level = 6))
JOIN ecc_skills_allskills L2 ON ((L2.parent = g.primaryskill_id) AND (L2.level = 7))
JOIN ecc_skills_allskills L3 ON ((L3.parent = g.primaryskill_id) AND (L3.level = 8))
JOIN ecc_skills_allskills L4 ON ((L4.parent = g.primaryskill_id) AND (L4.level = 9))
JOIN ecc_skills_allskills L5 ON ((L5.parent = g.primaryskill_id) AND (L5.level = 10))

[WafflesMcDuff]

This query will let us convert all the current characters skills:

set session sql_mode='';
SELECT skill_id, charId, max(LEVEL) AS level FROM (SELECT a.parent as skill_id, s.charID AS charID, a.level as level FROM ecc_char_skills s
JOIN ecc_skills_allskills a ON (s.skill_id = a.skill_id)) m GROUP BY charID, skill_id ORDER BY charID