u65dKX3R

1alter table profile_bit rename to profile_bit_backup;
2alter table profile_bit_backup rename CONSTRAINT profile_bit_pkey to profile_bit_backup_pkey;
3CREATE TABLE public.profile_bit (
4    profile_id character varying NOT NULL,
5    bit_name character varying NOT NULL,
6    revision integer NOT NULL,
7    update_datetime timestamp without time zone,
8    data jsonb NOT NULL
9)
10PARTITION BY LIST (bit_name);
11ALTER TABLE public.profile_bit
12    ADD CONSTRAINT profile_bit_pkey PRIMARY KEY (profile_id, bit_name);
13
14
15create table profile_bit_actionsbit partition of profile_bit FOR VALUES IN ('ActionsBit');
16create table profile_bit_armybit partition of profile_bit FOR VALUES IN ('ArmyBit');
17create table profile_bit_attributebit partition of profile_bit FOR VALUES IN ('AttributeBit');
18create table profile_bit_bankbit partition of profile_bit FOR VALUES IN ('BankBit');
19create table profile_bit_battlepassbit partition of profile_bit FOR VALUES IN ('BattlepassBit');
20create table profile_bit_castleminebit partition of profile_bit FOR VALUES IN ('CastleMineBit');
21create table profile_bit_casualbit partition of profile_bit FOR VALUES IN ('CasualBit');
22create table profile_bit_chatbit partition of profile_bit FOR VALUES IN ('ChatBit');
23create table profile_bit_chestbit partition of profile_bit FOR VALUES IN ('ChestBit');
24create table profile_bit_disciplesbit partition of profile_bit FOR VALUES IN ('DisciplesBit');
25create table profile_bit_dungeonbit partition of profile_bit FOR VALUES IN ('DungeonBit');
26create table profile_bit_eventshopbit partition of profile_bit FOR VALUES IN ('EventShopBit');
27create table profile_bit_friendresourcebit partition of profile_bit FOR VALUES IN ('FriendResourceBit');
28create table profile_bit_friendsbit partition of profile_bit FOR VALUES IN ('FriendsBit');
29create table profile_bit_goldminebit partition of profile_bit FOR VALUES IN ('GoldMineBit');
30create table profile_bit_guildshopbit partition of profile_bit FOR VALUES IN ('GuildShopBit');
31create table profile_bit_leaguechestbalancebit partition of profile_bit FOR VALUES IN ('LeagueChestBalanceBit');
32create table profile_bit_medalsbit partition of profile_bit FOR VALUES IN ('MedalsBit');
33create table profile_bit_minieventbit partition of profile_bit FOR VALUES IN ('MiniEventBit');
34create table profile_bit_namebit partition of profile_bit FOR VALUES IN ('NameBit');
35create table profile_bit_newquestbit partition of profile_bit FOR VALUES IN ('NewQuestBit');
36create table profile_bit_notificationsbit partition of profile_bit FOR VALUES IN ('NotificationsBit');
37create table profile_bit_paramsbit partition of profile_bit FOR VALUES IN ('ParamsBit');
38create table profile_bit_paymentsinfobit partition of profile_bit FOR VALUES IN ('PaymentsInfoBit');
39create table profile_bit_pethuntbit partition of profile_bit FOR VALUES IN ('PetHuntBit');
40create table profile_bit_pitbit partition of profile_bit FOR VALUES IN ('PitBit');
41create table profile_bit_questbit partition of profile_bit FOR VALUES IN ('QuestBit');
42create table profile_bit_rankbit partition of profile_bit FOR VALUES IN ('RankBit');
43create table profile_bit_resourcebit partition of profile_bit FOR VALUES IN ('ResourceBit');
44create table profile_bit_resourcerewardprogressbit partition of profile_bit FOR VALUES IN ('ResourceRewardProgressBit');
45create table profile_bit__revision partition of profile_bit FOR VALUES IN ('_revision');
46create table profile_bit_roulettebit partition of profile_bit FOR VALUES IN ('RouletteBit');
47create table profile_bit_shopbit partition of profile_bit FOR VALUES IN ('ShopBit');
48create table profile_bit_simplemedalsbit partition of profile_bit FOR VALUES IN ('SimpleMedalsBit');
49create table profile_bit_statisticbit partition of profile_bit FOR VALUES IN ('StatisticBit');
50create table profile_bit_survivalbit partition of profile_bit FOR VALUES IN ('SurvivalBit');
51create table profile_bit_temporaryboostsbit partition of profile_bit FOR VALUES IN ('TemporaryBoostsBit');
52create table profile_bit_tutorialbit partition of profile_bit FOR VALUES IN ('TutorialBit');
53create table profile_bit_vipbit partition of profile_bit FOR VALUES IN ('VipBit');
54create table profile_bit_default partition of profile_bit DEFAULT;
55insert into profile_bit select * from profile_bit_backup;
56
57drop materialized view if exists country_rating_view;
58CREATE MATERIALIZED VIEW public.country_rating_view AS
59SELECT pvp_player.profile_id,
60pvp_player.league,
61pvp_player.max_league,
62pvp_player.fame,
63profile_bit.data AS name_bit_data,
64(profile_bit.data ->> 'Country'::text) AS country,
65guild.icon,
66guild.id
67FROM ((((public.pvp_player
68 JOIN public.player_profile ON (((player_profile.profile_id)::text = (pvp_player.profile_id)::text)))
69 JOIN public.profile_bit ON ((((profile_bit.bit_name)::text = 'NameBit'::text) AND ((profile_bit.profile_id)::text = (pvp_player.profile_id)::text))))
70 LEFT JOIN public.guild_member ON (((guild_member.profile_id)::text = (pvp_player.profile_id)::text)))
71 LEFT JOIN public.guild ON ((guild_member.guild_id = guild.id)))
72WHERE ((player_profile.is_cheater = false) AND (player_profile.is_qa_account = false) AND (player_profile.use_in_pvp = true) AND (player_profile.pvp_active = true) AND (player_profile.update_datetime >= (now() - '14 days'::interval)) AND (pvp_player.league <= 28))
73ORDER BY pvp_player.league, pvp_player.fame DESC
74WITH NO DATA;
75CREATE INDEX country_rating_view_country_league_fame_idx ON public.country_rating_view USING btree (country, league, fame DESC);
76CREATE UNIQUE INDEX country_rating_view_profile_id_idx ON public.country_rating_view USING btree (profile_id);
77refresh materialized view country_rating_view;
78drop materialized view if exists platform_rating_view;
79CREATE MATERIALIZED VIEW public.platform_rating_view AS
80 SELECT pvp_player.profile_id,
81    pvp_player.league,
82    pvp_player.max_league,
83    pvp_player.fame,
84    profile_bit.data AS name_bit_data,
85    player_social_info.platform,
86    guild.icon,
87    guild.id
88   FROM (((((public.pvp_player
89     JOIN public.player_profile ON (((player_profile.profile_id)::text = (pvp_player.profile_id)::text)))
90     JOIN public.profile_bit ON ((((profile_bit.bit_name)::text = 'NameBit'::text) AND ((profile_bit.profile_id)::text = (pvp_player.profile_id)::text))))
91     LEFT JOIN public.guild_member ON (((guild_member.profile_id)::text = (pvp_player.profile_id)::text)))
92     LEFT JOIN public.guild ON ((guild_member.guild_id = guild.id)))
93     JOIN public.player_social_info ON (((player_social_info.profile_id)::text = (pvp_player.profile_id)::text)))
94  WHERE ((player_profile.is_cheater = false) AND (player_profile.is_qa_account = false) AND (player_profile.use_in_pvp = true) AND (player_profile.pvp_active = true) AND (player_profile.update_datetime >= (now() - '14 days'::interval)) AND (pvp_player.league <= 28))
95  ORDER BY pvp_player.league, pvp_player.fame DESC
96  WITH NO DATA;
97ALTER TABLE public.platform_rating_view OWNER TO blitz;
98CREATE INDEX platform_rating_view_country_league_fame_idx ON public.platform_rating_view USING btree (platform, league, fame DESC);
99CREATE UNIQUE INDEX platform_rating_view_profile_id_idx ON public.platform_rating_view USING btree (profile_id, platform);
100refresh materialized view platform_rating_view;
101refresh materialized view global_rating_view;