<commit_before>-- DB update 2021_03_29_00 -> 2021_03_29_01
DROP PROCEDURE IF EXISTS `updateDb`;
DELIMITER //
CREATE PROCEDURE updateDb ()
proc:BEGIN DECLARE OK VARCHAR(100) DEFAULT 'FALSE';
SELECT COUNT(*) INTO @COLEXISTS
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'version_db_world' AND COLUMN_NAME = '2021_03_29_00';
IF @COLEXISTS = 0 THEN LEAVE proc; END IF;
START TRANSACTION;
ALTER TABLE version_db_world CHANGE COLUMN 2021_03_29_00 2021_03_29_01 bit;
SELECT sql_rev INTO OK FROM version_db_world WHERE sql_rev = '1605128047204479800'; IF OK <> 'FALSE' THEN LEAVE proc; END IF;
--
-- START UPDATING QUERIES
--

INSERT INTO `version_db_world` (`sql_rev`) VALUES ('1605128047204479800');

-- SUMMARY: We edit the old table, then we create the new table, then we transfer the rows from the old table to the new table, then we remove no longer used columns from the old table, then we add foreign keys, then we add the strings, then update the .reload command

-- Add new column ID
ALTER TABLE `access_requirement`
ADD `id` tinyint unsigned NOT NULL COMMENT 'The dungeon template ID' AUTO_INCREMENT UNIQUE FIRST;

-- Set new primary key
ALTER TABLE `access_requirement`
ADD PRIMARY KEY `id` (`id`),
DROP INDEX `PRIMARY`,
DROP INDEX `id`;

-- New table
CREATE TABLE `dungeon_access_requirements` (
  `dungeon_access_id` tinyint unsigned NOT NULL COMMENT 'ID from dungeon_access_template',
  `requirement_type` tinyint unsigned NOT NULL COMMENT '0 = achiev, 1 = quest, 2 = item',
  `requirement_id` mediumint unsigned NOT NULL COMMENT 'Achiev/quest/item ID',
  `requirement_note` varchar(255) COLLATE 'utf8_general_ci' NULL COMMENT 'Optional msg shown ingame to player if he cannot enter. You can add extra info',
  `faction` tinyint unsigned NOT NULL DEFAULT 2 COMMENT '0 = Alliance, 1 = Horde, 2 = Both factions',
  `priority` tinyint unsigned NULL COMMENT 'Priority order for the requirement, sorted by type. 0 is the highest priority',
  `leader_only` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '0 = check the requirement for the player trying to enter, 1 = check the requirement for the party leader',
  `comment` varchar(255) COLLATE 'utf8_general_ci' NULL,
  PRIMARY KEY (`dungeon_access_id`, `requirement_type`, `requirement_id`)
) COMMENT='Add (multiple) requirements before being able to enter a dungeon/raid' ENGINE='MyISAM' COLLATE 'utf8_general_ci';

-- Transfer from old table to new table:
-- ------------------- ITEMS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`, `requirement_note`, `faction`)
    SELECT `id`,
            2 AS requirement_type,
            `item`,
            NULL,
            0
    FROM `access_requirement`
    WHERE `item` > 0 AND `item2` = 0
    UNION
    -- flamewrought key alliance
    SELECT `id`,
            2 AS requirement_type,
            `item2`,
            NULL,
            0 AS faction
    FROM `access_requirement`
    WHERE `item2` > 0
    UNION
    -- flamewrought key horde
    SELECT `id`,
            2 AS requirement_type,
            `item`,
            NULL,
            1 AS faction
    FROM `access_requirement`
    WHERE `item2` > 0
;

-- ------------------ ACHIEVS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`)
    SELECT `id`,
            0 AS requirement_type,
            `completed_achievement`
    FROM `access_requirement`
    WHERE `completed_achievement` > 0
;

-- ------------------ QUESTS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`, `faction`, `requirement_note`, `leader_only`)
    -- Alliance quests only
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_A`,
            0 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_A` > 0 AND `quest_done_A` != `quest_done_H`
    UNION
    -- Horde quests only
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_H`,
            1 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_H` > 0 AND `quest_done_A` != `quest_done_H`
    UNION
    -- Both factions
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_H`,
            2 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_H` > 0 AND `quest_done_A` = `quest_done_H`
;

-- REORDER PRIMARY KEY
CREATE TEMPORARY TABLE IF NOT EXISTS `temp` select * from `dungeon_access_requirements` ORDER BY dungeon_access_id, faction;
TRUNCATE `dungeon_access_requirements`;
ALTER TABLE `dungeon_access_requirements`; 
INSERT INTO `dungeon_access_requirements` SELECT * FROM `temp`;

-- Verify result with this
-- SELECT `id`, `quest_failed_text` FROM `access_requirement` WHERE `quest_failed_text` IS NOT NULL;


-- Remove columns + rename table
ALTER TABLE `access_requirement`
DROP `item`,
DROP `item2`,
DROP `quest_done_A`,
DROP `quest_done_H`,
DROP `completed_achievement`,
DROP `quest_failed_text`,
RENAME TO `dungeon_access_template`,
COMMENT='Dungeon/raid access template and single requirements';

-- Rename columns
ALTER TABLE `dungeon_access_template`
CHANGE `mapId` `map_id` mediumint unsigned NOT NULL COMMENT 'Map ID from instance_template' AFTER `id`,
CHANGE `difficulty` `difficulty` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '5 man: 0 = normal, 1 = heroic, 2 = epic (not implemented) | 10 man: 0 = normal, 2 = heroic | 25 man: 1 = normal, 3 = heroic' AFTER `map_id`,
CHANGE `level_min` `min_level` tinyint unsigned NULL AFTER `difficulty`,
CHANGE `level_max` `max_level` tinyint unsigned NULL AFTER `min_level`,
CHANGE `item_level` `min_avg_item_level` smallint unsigned NULL COMMENT 'Min average ilvl required to enter' AFTER `max_level`,
CHANGE `comment` `comment` varchar(255) COLLATE 'utf8_general_ci' NULL COMMENT 'Dungeon Name 5/10/25/40 man - Normal/Heroic' AFTER `min_avg_item_level`;

-- Add KEY CONSTRAINTS
ALTER TABLE `dungeon_access_template` ADD CONSTRAINT `FK_dungeon_access_template__instance_template` FOREIGN KEY (`map_id`) REFERENCES `instance_template` (`map`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dungeon_access_requirements` ADD CONSTRAINT `FK_dungeon_access_requirements__dungeon_access_template` FOREIGN KEY (`dungeon_access_id`) REFERENCES `dungeon_access_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


-- Add the acore_strings
DELETE FROM `acore_string` WHERE `entry` IN (882,883,884,885,886,887,888);
INSERT INTO `acore_string` (`entry`, `content_default`) VALUES 
(882, 'To enter, you must complete the following quest(s):'),
(883, 'To enter, you must complete the following achievement(s):'),<commit_msg>refactor(DB/SQL): Update 2021_03_29_01.sql to MYSQL standards (#5144)<commit_after>-- DB update 2021_03_29_00 -> 2021_03_29_01
DROP PROCEDURE IF EXISTS `updateDb`;
DELIMITER //
CREATE PROCEDURE updateDb ()
proc:BEGIN DECLARE OK VARCHAR(100) DEFAULT 'FALSE';
SELECT COUNT(*) INTO @COLEXISTS
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'version_db_world' AND COLUMN_NAME = '2021_03_29_00';
IF @COLEXISTS = 0 THEN LEAVE proc; END IF;
START TRANSACTION;
ALTER TABLE version_db_world CHANGE COLUMN 2021_03_29_00 2021_03_29_01 bit;
SELECT sql_rev INTO OK FROM version_db_world WHERE sql_rev = '1605128047204479800'; IF OK <> 'FALSE' THEN LEAVE proc; END IF;
--
-- START UPDATING QUERIES
--

INSERT INTO `version_db_world` (`sql_rev`) VALUES ('1605128047204479800');

-- SUMMARY: We edit the old table, then we create the new table, then we transfer the rows from the old table to the new table, then we remove no longer used columns from the old table, then we add foreign keys, then we add the strings, then update the .reload command

-- Add new column ID
ALTER TABLE `access_requirement`
ADD `id` tinyint unsigned NOT NULL COMMENT 'The dungeon template ID' AUTO_INCREMENT UNIQUE FIRST;

-- Set new primary key
ALTER TABLE `access_requirement`
ADD PRIMARY KEY `id` (`id`),
DROP INDEX `PRIMARY`,
DROP INDEX `id`;

-- New table
CREATE TABLE `dungeon_access_requirements` (
  `dungeon_access_id` TINYINT unsigned NOT NULL COMMENT 'ID from dungeon_access_template',
  `requirement_type` TINYINT unsigned NOT NULL COMMENT '0 = achiev, 1 = quest, 2 = item',
  `requirement_id` MEDIUMINT unsigned NOT NULL COMMENT 'Achiev/quest/item ID',
  `requirement_note` varchar(255) COLLATE 'utf8mb4_general_ci' NULL COMMENT 'Optional msg shown ingame to player if he cannot enter. You can add extra info',
  `faction` TINYINT unsigned NOT NULL DEFAULT 2 COMMENT '0 = Alliance, 1 = Horde, 2 = Both factions',
  `priority` TINYINT unsigned NULL COMMENT 'Priority order for the requirement, sorted by type. 0 is the highest priority',
  `leader_only` TINYINT NOT NULL DEFAULT 0 COMMENT '0 = check the requirement for the player trying to enter, 1 = check the requirement for the party leader',
  `comment` varchar(255) COLLATE 'utf8mb4_general_ci' NULL,
  PRIMARY KEY (`dungeon_access_id`, `requirement_type`, `requirement_id`)
) COMMENT='Add (multiple) requirements before being able to enter a dungeon/raid' ENGINE='MyISAM' COLLATE 'utf8mb4_general_ci';

-- Transfer from old table to new table:
-- ------------------- ITEMS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`, `requirement_note`, `faction`)
    SELECT `id`,
            2 AS requirement_type,
            `item`,
            NULL,
            0
    FROM `access_requirement`
    WHERE `item` > 0 AND `item2` = 0
    UNION
    -- flamewrought key alliance
    SELECT `id`,
            2 AS requirement_type,
            `item2`,
            NULL,
            0 AS faction
    FROM `access_requirement`
    WHERE `item2` > 0
    UNION
    -- flamewrought key horde
    SELECT `id`,
            2 AS requirement_type,
            `item`,
            NULL,
            1 AS faction
    FROM `access_requirement`
    WHERE `item2` > 0
;

-- ------------------ ACHIEVS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`)
    SELECT `id`,
            0 AS requirement_type,
            `completed_achievement`
    FROM `access_requirement`
    WHERE `completed_achievement` > 0
;

-- ------------------ QUESTS

INSERT INTO `dungeon_access_requirements` (`dungeon_access_id`, `requirement_type`, `requirement_id`, `faction`, `requirement_note`, `leader_only`)
    -- Alliance quests only
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_A`,
            0 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_A` > 0 AND `quest_done_A` != `quest_done_H`
    UNION
    -- Horde quests only
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_H`,
            1 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_H` > 0 AND `quest_done_A` != `quest_done_H`
    UNION
    -- Both factions
    SELECT `id`,
            1 AS requirement_type,
            `quest_done_H`,
            2 AS faction,
			quest_failed_text,
			1 as leader_only
    FROM `access_requirement`
    WHERE `quest_done_H` > 0 AND `quest_done_A` = `quest_done_H`
;

-- REORDER PRIMARY KEY
CREATE TEMPORARY TABLE IF NOT EXISTS `temp` select * from `dungeon_access_requirements` ORDER BY dungeon_access_id, faction;
TRUNCATE `dungeon_access_requirements`;
ALTER TABLE `dungeon_access_requirements`; 
INSERT INTO `dungeon_access_requirements` SELECT * FROM `temp`;

-- Verify result with this
-- SELECT `id`, `quest_failed_text` FROM `access_requirement` WHERE `quest_failed_text` IS NOT NULL;


-- Remove columns + rename table
ALTER TABLE `access_requirement`
DROP `item`,
DROP `item2`,
DROP `quest_done_A`,
DROP `quest_done_H`,
DROP `completed_achievement`,
DROP `quest_failed_text`,
RENAME TO `dungeon_access_template`,
COMMENT='Dungeon/raid access template and single requirements';

-- Rename columns
ALTER TABLE `dungeon_access_template`
CHANGE `mapId` `map_id` MEDIUMINT unsigned NOT NULL COMMENT 'Map ID from instance_template' AFTER `id`,
CHANGE `difficulty` `difficulty` TINYINT unsigned NOT NULL DEFAULT 0 COMMENT '5 man: 0 = normal, 1 = heroic, 2 = epic (not implemented) | 10 man: 0 = normal, 2 = heroic | 25 man: 1 = normal, 3 = heroic' AFTER `map_id`,
CHANGE `level_min` `min_level` TINYINT unsigned NULL AFTER `difficulty`,
CHANGE `level_max` `max_level` TINYINT unsigned NULL AFTER `min_level`,
CHANGE `item_level` `min_avg_item_level` SMALLINT unsigned NULL COMMENT 'Min average ilvl required to enter' AFTER `max_level`,
CHANGE `comment` `comment` varchar(255) COLLATE 'utf8mb4_general_ci' NULL COMMENT 'Dungeon Name 5/10/25/40 man - Normal/Heroic' AFTER `min_avg_item_level`;

-- Add KEY CONSTRAINTS
ALTER TABLE `dungeon_access_template` ADD CONSTRAINT `FK_dungeon_access_template__instance_template` FOREIGN KEY (`map_id`) REFERENCES `instance_template` (`map`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `dungeon_access_requirements` ADD CONSTRAINT `FK_dungeon_access_requirements__dungeon_access_template` FOREIGN KEY (`dungeon_access_id`) REFERENCES `dungeon_access_template` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;


-- Add the acore_strings
DELETE FROM `acore_string` WHERE `entry` IN (882,883,884,885,886,887,888);
INSERT INTO `acore_string` (`entry`, `content_default`) VALUES 
(882, 'To enter, you must complete the following quest(s):'),
(883, 'To enter, you must complete the following achievement(s):'),
{'fraction_non_alphanumeric': 0.0975251523830506, 'fraction_numerical': 0.029595358742748035, 'mean_word_length': 3.986451849139509, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 21, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3006574', 'n_tokens_mistral': 4805, 'n_tokens_neox': 4339, 'n_words': 1443}