
-- ----------------------------------------------------------------
-- ------ convert utf8 DB to utf8mb4 ------------------------------
-- ----------------------------------------------------------------

-- see: https://github.com/Asqatasun/Asqatasun/issues/123
--
-- /!\ This the **engine** part of the SQL update
-- /!\ do not forget to run the **webapp** part,
--     located in web-app/tgol-resources/src/main/resources/sql-update

-- 1. Change the character set and collation properties of the database
-- -----------------------------------------------------------------
ALTER DATABASE `asqatasun`
    CHARACTER SET = utf8mb4
    COLLATE       = utf8mb4_general_ci;

-- 2. Change the character set, collation properties
--    and maximum length of each VARCHAR or TEXT column **which is an index**
-- ----------------------------------------------------------------

ALTER TABLE `THEME`                 CHANGE `Cd_Theme`               `Cd_Theme`              varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `REFERENCE`             CHANGE `Cd_Reference`           `Cd_Reference`          varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `CRITERION`             CHANGE `Cd_Criterion`           `Cd_Criterion`          varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `TEST`                  CHANGE `Cd_Test`                `Cd_Test`               varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PROCESS_RESULT`        CHANGE `Definite_Value`         `Definite_Value`        varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PROCESS_REMARK`        CHANGE `Issue`                  `Issue`                 varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PRE_PROCESS_RESULT`    CHANGE `Pre_Process_Key`        `Pre_Process_Key`       varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PARAMETER_FAMILY`      CHANGE `Cd_Parameter_Family`    `Cd_Parameter_Family`   varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PARAMETER_ELEMENT`     CHANGE `Cd_Parameter_Element`   `Cd_Parameter_Element`  varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

ALTER TABLE `PARAMETER` DROP KEY `Unique_Param_Element_Type_Param_value`;
ALTER TABLE `PARAMETER` ADD UNIQUE KEY `Unique_Param_Element_Type_Param_value` (`Parameter_Value`(191), `Id_Parameter_Element`);

-- 3. Change the character set and collation properties of each tables
-- ----------------------------------------------------------------
ALTER TABLE `AUDIT`                        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `WEB_RESOURCE`                 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `CONTENT`                      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `CONTENT_RELATIONSHIP`         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `SCOPE`                        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `DECISION_LEVEL`               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `LEVEL`                        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `THEME`                        CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `REFERENCE`                    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `CRITERION`                    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `TEST`                         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `AUDIT_TEST`                   CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `EVIDENCE`                     CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PROCESS_RESULT`               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PROCESS_REMARK`               CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `EVIDENCE_ELEMENT`             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `NOMENCLATURE`                 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `NOMENCLATURE_ELEMENT`         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `STANDARD_MESSAGE`             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PRE_PROCESS_RESULT`           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- ALTER TABLE `SNAPSHOT`                  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PARAMETER_FAMILY`             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PARAMETER_ELEMENT`            CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PARAMETER`                    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `AUDIT_PARAMETER`              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `WEB_RESOURCE_STATISTICS`      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `THEME_STATISTICS`             CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `CRITERION_STATISTICS`         CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `TEST_STATISTICS`              CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `REVINFO`                      CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `PROCESS_RESULT_AUD`           CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;


-- 4. Repair and optimize all tables
-- ----------------------------------------------------------------

    --  with cli:
    --      mysqlcheck -u root -p --auto-repair --optimize --all-databases

-- If you don't perform those repairing and optimizing, you may run into some weird bugs
-- where UPDATE statements don't  have any effect, even though no errors were thrown.
--    source:  http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

-- Optimize all tables
OPTIMIZE TABLE `AUDIT`;
OPTIMIZE TABLE `WEB_RESOURCE`;
OPTIMIZE TABLE `CONTENT`;
OPTIMIZE TABLE `CONTENT_RELATIONSHIP`;
OPTIMIZE TABLE `SCOPE`;
OPTIMIZE TABLE `DECISION_LEVEL`;
OPTIMIZE TABLE `LEVEL`;
OPTIMIZE TABLE `THEME`;
OPTIMIZE TABLE `REFERENCE`;
OPTIMIZE TABLE `CRITERION`;
OPTIMIZE TABLE `TEST`;
OPTIMIZE TABLE `AUDIT_TEST`;
OPTIMIZE TABLE `EVIDENCE`;
OPTIMIZE TABLE `PROCESS_RESULT`;
OPTIMIZE TABLE `PROCESS_REMARK`;
OPTIMIZE TABLE `EVIDENCE_ELEMENT`;
OPTIMIZE TABLE `NOMENCLATURE`;
OPTIMIZE TABLE `NOMENCLATURE_ELEMENT`;
OPTIMIZE TABLE `STANDARD_MESSAGE`;
OPTIMIZE TABLE `PRE_PROCESS_RESULT`;
-- OPTIMIZE TABLE `SNAPSHOT`;
OPTIMIZE TABLE `PARAMETER_FAMILY`;
OPTIMIZE TABLE `PARAMETER_ELEMENT`;
OPTIMIZE TABLE `PARAMETER`;
OPTIMIZE TABLE `AUDIT_PARAMETER`;
OPTIMIZE TABLE `WEB_RESOURCE_STATISTICS`;
OPTIMIZE TABLE `THEME_STATISTICS`;
OPTIMIZE TABLE `CRITERION_STATISTICS`;
OPTIMIZE TABLE `TEST_STATISTICS`;
OPTIMIZE TABLE `REVINFO`;
OPTIMIZE TABLE `PROCESS_RESULT_AUD`;
