diff options
| -rw-r--r-- | sql/base/characters_database.sql | 2 | ||||
| -rw-r--r-- | sql/updates/characters/master/2021_10_02_00_characters.sql | 46 |
2 files changed, 39 insertions, 9 deletions
diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql index 005b09ff3e9..e1c4d6162cd 100644 --- a/sql/base/characters_database.sql +++ b/sql/base/characters_database.sql @@ -3721,7 +3721,7 @@ INSERT INTO `updates` VALUES ('2021_07_04_00_characters.sql','E0E7AD664DDB553E96B457DD9ED8976665E94007','ARCHIVED','2021-07-04 22:23:24',0), ('2021_08_11_00_characters.sql','2137A52A45B045104B97D39626CE3C0214625B17','ARCHIVED','2021-08-11 21:48:57',0), ('2021_08_18_00_characters.sql','5BA1326EE8EC907CDE82E6E8BCB38EA2E661F10A','ARCHIVED','2021-08-18 15:14:17',0), -('2021_10_02_00_characters.sql','C6831D3ED03F6BD390A5E5E403FA402D6DC0E95D','RELEASED','2021-10-02 21:21:37',0), +('2021_10_02_00_characters.sql','31CEACE4E9A4BE58A659A2BDE4A7C51D2DB8AC41','RELEASED','2021-10-02 21:21:37',0), ('2021_10_02_01_characters.sql','F97B956F3B5F909294CA399F75B5795A07C4D8EC','RELEASED','2021-10-02 21:47:38',0); /*!40000 ALTER TABLE `updates` ENABLE KEYS */; UNLOCK TABLES; diff --git a/sql/updates/characters/master/2021_10_02_00_characters.sql b/sql/updates/characters/master/2021_10_02_00_characters.sql index e6e6285b72f..b23080ce0b0 100644 --- a/sql/updates/characters/master/2021_10_02_00_characters.sql +++ b/sql/updates/characters/master/2021_10_02_00_characters.sql @@ -1,12 +1,42 @@ -ALTER TABLE `characters` - ADD `createTime` bigint(20) NOT NULL DEFAULT '0' AFTER `online`, - ADD `createMode` tinyint(4) NOT NULL DEFAULT '0' AFTER `createTime`; +-- THIS SQL UPDATE HAS BEEN EDITED TO FIX MYSQL 5.7 COMPATIBILITY +-- ADD COLUMN IF NOT EXISTS +SET @dbname = DATABASE(); +SET @preparedStatement = (SELECT IF( + ( + SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS + WHERE + (table_name = 'characters') + AND (table_schema = @dbname) + AND (column_name = 'createTime') + ) > 0, + "SELECT 'Skipped adding column, already exists.'", + CONCAT("ALTER TABLE characters ADD `createTime` bigint(20) NOT NULL DEFAULT '0' AFTER `online`, ADD `createMode` tinyint(4) NOT NULL DEFAULT '0' AFTER `createTime`") +)); +PREPARE alterIfNotExists FROM @preparedStatement; +EXECUTE alterIfNotExists; +DEALLOCATE PREPARE alterIfNotExists; + +-- EDITED NOTE: updates safe to reapply unconditionally -- AT_LOGIN_FIRST: Characters that have never logged in have accurate creation timestamp in `logout_time` UPDATE `characters` SET `createTime`=`logout_time` WHERE (`at_login` & 0x20) != 0; -- attempt to find lowest criteria timestamp for characters that have logged in already -UPDATE `characters` SET `createTime`=(SELECT COALESCE(MIN(candidates.`date`), UNIX_TIMESTAMP()) FROM ( -SELECT MIN(cap.`date`) `date` FROM `character_achievement_progress` cap WHERE cap.`guid`=`characters`.`guid` -UNION -SELECT MIN(ca.`date`) `date` FROM `character_achievement` ca WHERE ca.`guid`=`characters`.`guid` -) candidates) WHERE (`at_login` & 0x20) = 0; +UPDATE `characters` c + INNER JOIN + ( + SELECT cap.`guid` `guid`, COALESCE(MIN(cap.`date`), UNIX_TIMESTAMP()) `date` + FROM `character_achievement_progress` cap + GROUP BY cap.`guid` + ) criteria ON criteria.`guid` = c.`guid` + INNER JOIN + ( + SELECT ca.`guid` `guid`, COALESCE(MIN(ca.`date`), UNIX_TIMESTAMP()) `date` + FROM `character_achievement` ca + GROUP BY ca.`guid` + ) achievement ON achievement.`guid` = c.`guid` +SET + `createTime` = IF(criteria.`date` < achievement.`date`, + criteria.`date`, + achievement.`date`) +WHERE + (`at_login` & 0x20) = 0; |