-
Notifications
You must be signed in to change notification settings - Fork 19
/
4_pokestop_update.up.sql
46 lines (37 loc) · 2.72 KB
/
4_pokestop_update.up.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
ALTER TABLE `pokestop`
ADD COLUMN quest_expiry int unsigned NULL,
ADD COLUMN alternative_quest_expiry int unsigned NULL,
ADD COLUMN description text;
ALTER TABLE `pokestop` ADD INDEX `ix_quest_expiry` (`quest_expiry`);
ALTER TABLE `pokestop` ADD INDEX `ix_alternative_quest_expiry` (`alternative_quest_expiry`);
/* Update quest calculated fields */
ALTER TABLE `pokestop`
DROP COLUMN `quest_reward_type`,
DROP COLUMN `quest_item_id` ,
DROP COLUMN `quest_reward_amount`,
DROP COLUMN `quest_pokemon_id`,
DROP COLUMN `alternative_quest_pokemon_id`,
DROP COLUMN `alternative_quest_reward_type`,
DROP COLUMN `alternative_quest_item_id`,
DROP COLUMN `alternative_quest_reward_amount`;
ALTER TABLE `pokestop`
ADD COLUMN `quest_reward_type` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`quest_rewards`,_utf8mb4'$[*].type'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `quest_item_id` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`quest_rewards`,_utf8mb4'$[*].info.item_id'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `quest_reward_amount` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`quest_rewards`,_utf8mb4'$[*].info.amount'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `quest_pokemon_id` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`quest_rewards`,_utf8mb4'$[*].info.pokemon_id'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `alternative_quest_pokemon_id` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`alternative_quest_rewards`,_utf8mb4'$[*].info.pokemon_id'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `alternative_quest_reward_type` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`alternative_quest_rewards`,_utf8mb4'$[*].type'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `alternative_quest_item_id` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`alternative_quest_rewards`,_utf8mb4'$[*].info.item_id'),_utf8mb4'$[0]')) STORED,
ADD COLUMN `alternative_quest_reward_amount` smallint unsigned GENERATED ALWAYS AS (json_extract(json_extract(`alternative_quest_rewards`,_utf8mb4'$[*].info.amount'),_utf8mb4'$[0]')) STORED;
/* Add back indexes? */
# KEY `ix_quest_reward_type` (`quest_reward_type`),
# KEY `ix_quest_item_id` (`quest_item_id`),
# KEY `ix_quest_pokemon_id` (`quest_pokemon_id`),
# KEY `ix_alternative_quest_alternative_quest_pokemon_id` (`alternative_quest_pokemon_id`),
# KEY `ix_alternative_quest_reward_type` (`alternative_quest_reward_type`),
# KEY `ix_alternative_quest_item_id` (`alternative_quest_item_id`)
/* Description for gym */
ALTER TABLE `gym`
ADD COLUMN description text;
/* incident expiry time index */
ALTER TABLE `incident` ADD INDEX `ix_expiration` (`expiration`);