Merge branch 'master' of github.com:antzucaro/xonstatdb

[xonotic/xonstatdb.git] / functions / merge_players.sql

diff --git a/functions/merge_players.sql b/functions/merge_players.sql
index 2cb6a851b7e38ac9c8aeee39069f7ad84974d508..ecb89b30bbe23ce084d275a83070f76193d3ebcd 100644 (file)
--- a/functions/merge_players.sql
+++ b/functions/merge_players.sql
@@ -1,8 +1,11 @@
-create or replace function merge_players(p_winner_player_id players.player_id%TYPE, p_loser_player_id players.player_id%TYPE) RETURNS void as
+create or replace function merge_players(p_winner_player_id players.player_id%TYPE, p_loser_player_id players.player_id%TYPE) RETURNS void as
 $$
 declare
-   rowcount integer;   
+   rowcount integer;
+   r record;
 begin
+   raise notice 'Merging % and %', p_winner_player_id, p_loser_player_id;
+
    -- start with weapon stats
    update player_weapon_stats
    set player_id = p_winner_player_id
@@ -25,24 +28,30 @@ begin
    where player_id = p_loser_player_id
    and not exists (select 1 from xonstat.player_elos where player_id = p_winner_player_id and game_type_cd = pe.game_type_cd);
 
-   -- update the existing player_elos entries that have a higher elo on the loser
-   update xonstat.player_elos pe
-   set elo = newpe.elo
-   from
-      (select lpe.game_type_cd, lpe.elo
-      from xonstat.player_elos wpe, xonstat.player_elos lpe
-      where wpe.game_type_cd = lpe.game_type_cd
-      and wpe.player_id = 67
-      and lpe.player_id = 720
-      and lpe.elo > wpe.elo) newpe
-   where player_id = 67
-   and pe.game_type_cd = newpe.game_type_cd;
+   -- Take the aggregate # of games and the *greatest* Elo 
+   -- value and use that going forward
+   FOR r in (
+     select game_type_cd, sum(games) sum_games, 
+     max(elo) max_elo, max(update_dt) max_update_dt
+     from player_elos
+     where player_id in (p_winner_player_id, p_loser_player_id) 
+     group by game_type_cd
+   )
+   LOOP
+      update xonstat.player_elos
+      set elo = r.max_elo, games = r.sum_games, update_dt = r.max_update_dt
+      where player_id = p_winner_player_id
+      and game_type_cd = r.game_type_cd
+      and games != r.sum_games;
+
+      raise notice 'New % Elo is %.', r.game_type_cd, r.max_elo;
+   END LOOP;
 
    -- then hashkeys (winner takes the loser's hashkey)
    update hashkeys
    set player_id = p_winner_player_id
    where player_id = p_loser_player_id;
-   
+
    get diagnostics rowcount = ROW_COUNT;
    raise notice '% hashkeys copied.', rowcount;
 
@@ -50,4 +59,4 @@ begin
    update players set active_ind = false where player_id = p_loser_player_id;
 end;
 $$
-language plpgsql;
\ No newline at end of file
+language plpgsql;