Add the aggregate table to the build.

[xonotic/xonstatdb.git] / build / build_full.sql

diff --git a/build/build_full.sql b/build/build_full.sql
index 43ab506b126fb38c9c08f99f48e18a811ff7a9e8..f5b9f8c7b71e2b5cee1c8aeb86ea35b0ef5530bf 100755 (executable)
--- a/build/build_full.sql
+++ b/build/build_full.sql
@@ -1,36 +1,72 @@
 -- drop tables first in reverse order
+drop table if exists player_agg_stats_mv;
+drop table if exists merged_servers;
+drop table if exists player_medals;
+drop table if exists active_maps_mv;
+drop table if exists active_servers_mv;
+drop table if exists active_players_mv;
+drop table if exists summary_stats_mv;
+drop table if exists player_groups;
+drop table if exists player_game_anticheats;
+drop table if exists team_game_stats;
+drop table if exists summary_stats cascade;
+drop table if exists player_map_captimes;
+drop table if exists player_ladder_ranks;
+drop table if exists cd_ladder cascade;
+drop table if exists player_ranks_history cascade;
+drop table if exists player_ranks cascade;
+drop table if exists player_elos cascade;
+drop table if exists player_nicks cascade;
 drop table if exists db_version cascade;
 drop table if exists hashkeys cascade;
 drop table if exists player_weapon_stats cascade;
 drop table if exists achievements cascade;
 drop table if exists cd_achievement cascade;
-drop table if exists game_mutators cascade;
+--drop table if exists game_mutators cascade;
 drop table if exists player_game_stats cascade;
 drop table if exists games cascade;
-drop table if exists map_game_types cascade;
+--drop table if exists map_game_types cascade;
 drop table if exists maps cascade;
 drop table if exists servers cascade;
 drop table if exists cd_game_type cascade;
 drop table if exists cd_weapon cascade;
-drop table if exists cd_mutator cascade;
+--drop table if exists cd_mutator cascade;
 drop table if exists players cascade;
 
 -- table definitions
 \i tables/players.tab
-\i tables/cd_mutator.tab
+--\i tables/cd_mutator.tab
 \i tables/cd_game_type.tab
 \i tables/cd_weapon.tab
 \i tables/servers.tab
 \i tables/maps.tab
-\i tables/map_game_types.tab
+--\i tables/map_game_types.tab
 \i tables/games.tab
 \i tables/player_game_stats.tab
-\i tables/game_mutators.tab
+--\i tables/game_mutators.tab
 \i tables/cd_achievement.tab
 \i tables/achievements.tab
 \i tables/player_weapon_stats.tab
 \i tables/hashkeys.tab
 \i tables/db_version.tab
+\i tables/player_nicks.tab
+\i tables/player_elos.tab
+\i tables/player_ranks.tab
+\i tables/player_ranks_history.tab
+\i tables/cd_ladder.tab
+\i tables/player_ladder_ranks.tab
+\i tables/player_map_captimes.tab
+\i tables/summary_stats.tab
+\i tables/team_game_stats.tab
+\i tables/player_game_anticheats.tab
+\i tables/player_groups.tab
+\i tables/summary_stats_mv.tab
+\i tables/active_players_mv.tab
+\i tables/active_servers_mv.tab
+\i tables/active_maps_mv.tab
+\i tables/player_medals.tab
+\i tables/merged_servers.tab
+\i tables/player_agg_stats_mv.tab
 
 begin;
 
@@ -48,7 +84,7 @@ values('dm', 'Deathmatch');
 insert into cd_game_type(game_type_cd, descr)
 values('dom', 'Domination');
 insert into cd_game_type(game_type_cd, descr)
-values('freezetag', 'Freezetag');
+values('ft', 'Freezetag');
 insert into cd_game_type(game_type_cd, descr)
 values('ka', 'Keepaway');
 insert into cd_game_type(game_type_cd, descr)
@@ -56,7 +92,7 @@ values('kh', 'Keyhunt');
 insert into cd_game_type(game_type_cd, descr)
 values('lms', 'Last Man Standing');
 insert into cd_game_type(game_type_cd, descr)
-values('nexball', 'Nexball');
+values('nb', 'Nexball');
 insert into cd_game_type(game_type_cd, descr)
 values('ons', 'Onslaught');
 insert into cd_game_type(game_type_cd, descr)
@@ -67,6 +103,8 @@ insert into cd_game_type(game_type_cd, descr)
 values('rune', 'Runematch');
 insert into cd_game_type(game_type_cd, descr)
 values('tdm', 'Team Deathmatch');
+insert into cd_game_type(game_type_cd, descr)
+values('duel', 'Duel');
 
 -- weapons
 insert into cd_weapon(weapon_cd, descr) values('laser', 'Laser');
@@ -87,6 +125,13 @@ insert into cd_weapon(weapon_cd, descr) values('tuba', '@!#%''n Tuba');
 insert into cd_weapon(weapon_cd, descr) values('hlac', 'Heavy Laser Assault Cannon');
 insert into cd_weapon(weapon_cd, descr) values('hook', 'Grappling Hook');
 insert into cd_weapon(weapon_cd, descr) values('porto', 'Port-O-Launch');
+insert into cd_weapon(weapon_cd, descr) values('blaster', 'Blaster');
+insert into cd_weapon(weapon_cd, descr) values('devastator', 'Devastator');
+insert into cd_weapon(weapon_cd, descr) values('machinegun', 'Machine Gun');
+insert into cd_weapon(weapon_cd, descr) values('mortar', 'Mortar');
+insert into cd_weapon(weapon_cd, descr) values('vaporizer', 'Vaporizer');
+insert into cd_weapon(weapon_cd, descr) values('vortex', 'Vortex');
+insert into cd_weapon(weapon_cd, descr) values('arc', 'Arc');
 
 -- achievements
 insert into cd_achievement(achievement_cd, descr, active_ind)
@@ -131,36 +176,36 @@ insert into cd_achievement(achievement_cd, descr, active_ind)
 values(20,'Be the highest scorer in a game.', 'Y');
 
 -- mutators
-insert into cd_mutator(mutator_cd, name)
-values(1, 'g_dodging');
-insert into cd_mutator(mutator_cd, name)
-values(2, 'g_minstagib');
-insert into cd_mutator(mutator_cd, name)
-values(3, 'g_nix');
-insert into cd_mutator(mutator_cd, name)
-values(4, 'g_rocket_flying');
-insert into cd_mutator(mutator_cd, name)
-values(5, 'g_weaponarena');
-insert into cd_mutator(mutator_cd, name)
-values(6, 'g_start_weapon_laser');
-insert into cd_mutator(mutator_cd, name)
-values(7, 'sv_gravity');
-insert into cd_mutator(mutator_cd, name)
-values(8, 'g_cloaked');
-insert into cd_mutator(mutator_cd, name)
-values(9, 'g_grappling_hook');
-insert into cd_mutator(mutator_cd, name)
-values(10, 'g_midair');
-insert into cd_mutator(mutator_cd, name)
-values(11, 'g_vampire');
-insert into cd_mutator(mutator_cd, name)
-values(12, 'g_pinata');
-insert into cd_mutator(mutator_cd, name)
-values(13, 'g_weapon_stay');
-insert into cd_mutator(mutator_cd, name)
-values(14, 'g_bloodloss');
-insert into cd_mutator(mutator_cd, name)
-values(15, 'g_jetpack');
+--insert into cd_mutator(mutator_cd, name)
+--values(1, 'g_dodging');
+--insert into cd_mutator(mutator_cd, name)
+--values(2, 'g_minstagib');
+--insert into cd_mutator(mutator_cd, name)
+--values(3, 'g_nix');
+--insert into cd_mutator(mutator_cd, name)
+--values(4, 'g_rocket_flying');
+--insert into cd_mutator(mutator_cd, name)
+--values(5, 'g_weaponarena');
+--insert into cd_mutator(mutator_cd, name)
+--values(6, 'g_start_weapon_laser');
+--insert into cd_mutator(mutator_cd, name)
+--values(7, 'sv_gravity');
+--insert into cd_mutator(mutator_cd, name)
+--values(8, 'g_cloaked');
+--insert into cd_mutator(mutator_cd, name)
+--values(9, 'g_grappling_hook');
+--insert into cd_mutator(mutator_cd, name)
+--values(10, 'g_midair');
+--insert into cd_mutator(mutator_cd, name)
+--values(11, 'g_vampire');
+--insert into cd_mutator(mutator_cd, name)
+--values(12, 'g_pinata');
+--insert into cd_mutator(mutator_cd, name)
+--values(13, 'g_weapon_stay');
+--insert into cd_mutator(mutator_cd, name)
+--values(14, 'g_bloodloss');
+--insert into cd_mutator(mutator_cd, name)
+--values(15, 'g_jetpack');
 
 -- bots and untracked players have special records in player
 insert into players (nick) values ('Bot');
@@ -170,6 +215,7 @@ insert into players (nick) values ('Untracked Player');
 \i triggers/games_ins_trg.sql
 \i triggers/player_game_stats_ins_trg.sql
 \i triggers/player_weapon_stats_ins_trg.sql
+\i triggers/team_game_stats_ins_trg.sql
 
 -- version tracking
 insert into db_version(version, descr) values('1.0.0', 'Initial build');