From 659d76d6363394c799dc1cc281371fc71ddb0110 Mon Sep 17 00:00:00 2001 From: Shauren Date: Thu, 26 Sep 2024 19:33:41 +0200 Subject: DB: Remove unsigned floats and recreate views with utf8mb4 in auth database (cherry picked from commit b18ba61140cbb2c26eb5d006826eb61097f44726) (cherry picked from commit be04c17916aa35173ed328eb27cb5f246bc8b863) # Conflicts: # sql/base/auth_database.sql # sql/updates/auth/cata_classic/2024_09_26_00_auth.sql --- sql/updates/auth/cata_classic/2024_09_26_00_auth.sql | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) create mode 100644 sql/updates/auth/cata_classic/2024_09_26_00_auth.sql (limited to 'sql/updates/auth') diff --git a/sql/updates/auth/cata_classic/2024_09_26_00_auth.sql b/sql/updates/auth/cata_classic/2024_09_26_00_auth.sql new file mode 100644 index 00000000000..a5c75e51c38 --- /dev/null +++ b/sql/updates/auth/cata_classic/2024_09_26_00_auth.sql @@ -0,0 +1,20 @@ + +ALTER TABLE `realmlist` MODIFY `population` float NOT NULL DEFAULT '0'; + +SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'; -- views do not have explicit charset/collation options but record whatever the connection settings were as their own (visible in mysqldump) + +DROP VIEW IF EXISTS `vw_log_history`; +DROP VIEW IF EXISTS `vw_rbac`; + +CREATE SQL SECURITY INVOKER VIEW `vw_log_history` AS (SELECT FROM_UNIXTIME(MIN(`logs`.`time`)) AS `First Logged` ,FROM_UNIXTIME(MAX(`logs`.`time`)) AS `Last Logged` ,COUNT(*) AS `Occurrences` ,`realmlist`.`name` AS `Realm` ,`logs`.`type` ,`logs`.`level` ,`logs`.`string` FROM `logs` LEFT JOIN realmlist ON `logs`.`realm` = `realmlist`.`id` GROUP BY `logs`.`string`, `logs`.`type`, `logs`.`realm`); + +CREATE SQL SECURITY INVOKER VIEW `vw_rbac` AS +( + SELECT `t1`.`linkedId` AS `Permission ID`, + `t1`.`id` AS `Permission Group`, + IFNULL(`t2`.`secId`, 'linked') AS `Security Level`, + `t3`.`name` AS `Permission` + FROM `rbac_linked_permissions` `t1` + LEFT JOIN `rbac_default_permissions` `t2` ON `t1`.`id` = `t2`.`permissionId` + LEFT JOIN `rbac_permissions` `t3` ON `t1`.`linkedId` = `t3`.`id` +); -- cgit v1.2.3