273 lines
12 KiB
Python
273 lines
12 KiB
Python
|
"""
|
||
|
|
Player Processor - Handles dim_players and fact_match_players
|
||
|
|
|
||
|
|
Responsibilities:
|
||
|
|
- Process player dimension table (UPSERT to avoid duplicates)
|
||
|
|
- Merge fight/fight_t/fight_ct data
|
||
|
|
- Process VIP+ advanced statistics
|
||
|
|
- Handle all player match statistics tables
|
||
|
|
"""
|
||
|
|
|
||
|
|
import sqlite3
|
||
|
|
import json
|
||
|
|
import logging
|
||
|
|
from typing import Any, Dict
|
||
|
|
|
||
|
|
logger = logging.getLogger(__name__)
|
||
|
|
|
||
|
|
|
||
|
|
def safe_int(val):
|
||
|
|
"""Safely convert value to integer"""
|
||
|
|
try:
|
||
|
|
return int(float(val)) if val is not None else 0
|
||
|
|
except:
|
||
|
|
return 0
|
||
|
|
|
||
|
|
|
||
|
|
def safe_float(val):
|
||
|
|
"""Safely convert value to float"""
|
||
|
|
try:
|
||
|
|
return float(val) if val is not None else 0.0
|
||
|
|
except:
|
||
|
|
return 0.0
|
||
|
|
|
||
|
|
|
||
|
|
def safe_text(val):
|
||
|
|
"""Safely convert value to text"""
|
||
|
|
return "" if val is None else str(val)
|
||
|
|
|
||
|
|
|
||
|
|
class PlayerProcessor:
|
||
|
|
@staticmethod
|
||
|
|
def process(match_data, conn: sqlite3.Connection) -> bool:
|
||
|
|
"""
|
||
|
|
Process all player-related data
|
||
|
|
|
||
|
|
Args:
|
||
|
|
match_data: MatchData object containing parsed JSON
|
||
|
|
conn: L2 database connection
|
||
|
|
|
||
|
|
Returns:
|
||
|
|
bool: True if successful
|
||
|
|
"""
|
||
|
|
try:
|
||
|
|
cursor = conn.cursor()
|
||
|
|
|
||
|
|
# Process dim_players (UPSERT) - using dynamic column building
|
||
|
|
for steam_id, meta in match_data.player_meta.items():
|
||
|
|
# Define columns (must match schema exactly)
|
||
|
|
player_columns = [
|
||
|
|
'steam_id_64', 'uid', 'username', 'avatar_url', 'domain', 'created_at', 'updated_at',
|
||
|
|
'last_seen_match_id', 'uuid', 'email', 'area', 'mobile', 'user_domain',
|
||
|
|
'username_audit_status', 'accid', 'team_id', 'trumpet_count', 'profile_nickname',
|
||
|
|
'profile_avatar_audit_status', 'profile_rgb_avatar_url', 'profile_photo_url',
|
||
|
|
'profile_gender', 'profile_birthday', 'profile_country_id', 'profile_region_id',
|
||
|
|
'profile_city_id', 'profile_language', 'profile_recommend_url', 'profile_group_id',
|
||
|
|
'profile_reg_source', 'status_status', 'status_expire', 'status_cancellation_status',
|
||
|
|
'status_new_user', 'status_login_banned_time', 'status_anticheat_type',
|
||
|
|
'status_flag_status1', 'status_anticheat_status', 'status_flag_honor',
|
||
|
|
'status_privacy_policy_status', 'status_csgo_frozen_exptime', 'platformexp_level',
|
||
|
|
'platformexp_exp', 'steam_account', 'steam_trade_url', 'steam_rent_id',
|
||
|
|
'trusted_credit', 'trusted_credit_level', 'trusted_score', 'trusted_status',
|
||
|
|
'trusted_credit_status', 'certify_id_type', 'certify_status', 'certify_age',
|
||
|
|
'certify_real_name', 'certify_uid_list', 'certify_audit_status', 'certify_gender',
|
||
|
|
'identity_type', 'identity_extras', 'identity_status', 'identity_slogan',
|
||
|
|
'identity_list', 'identity_slogan_ext', 'identity_live_url', 'identity_live_type',
|
||
|
|
'plus_is_plus', 'user_info_raw'
|
||
|
|
]
|
||
|
|
|
||
|
|
player_values = [
|
||
|
|
steam_id, meta['uid'], meta['username'], meta['avatar_url'], meta['domain'],
|
||
|
|
meta['created_at'], meta['updated_at'], match_data.match_id, meta['uuid'],
|
||
|
|
meta['email'], meta['area'], meta['mobile'], meta['user_domain'],
|
||
|
|
meta['username_audit_status'], meta['accid'], meta['team_id'],
|
||
|
|
meta['trumpet_count'], meta['profile_nickname'],
|
||
|
|
meta['profile_avatar_audit_status'], meta['profile_rgb_avatar_url'],
|
||
|
|
meta['profile_photo_url'], meta['profile_gender'], meta['profile_birthday'],
|
||
|
|
meta['profile_country_id'], meta['profile_region_id'], meta['profile_city_id'],
|
||
|
|
meta['profile_language'], meta['profile_recommend_url'], meta['profile_group_id'],
|
||
|
|
meta['profile_reg_source'], meta['status_status'], meta['status_expire'],
|
||
|
|
meta['status_cancellation_status'], meta['status_new_user'],
|
||
|
|
meta['status_login_banned_time'], meta['status_anticheat_type'],
|
||
|
|
meta['status_flag_status1'], meta['status_anticheat_status'],
|
||
|
|
meta['status_flag_honor'], meta['status_privacy_policy_status'],
|
||
|
|
meta['status_csgo_frozen_exptime'], meta['platformexp_level'],
|
||
|
|
meta['platformexp_exp'], meta['steam_account'], meta['steam_trade_url'],
|
||
|
|
meta['steam_rent_id'], meta['trusted_credit'], meta['trusted_credit_level'],
|
||
|
|
meta['trusted_score'], meta['trusted_status'], meta['trusted_credit_status'],
|
||
|
|
meta['certify_id_type'], meta['certify_status'], meta['certify_age'],
|
||
|
|
meta['certify_real_name'], meta['certify_uid_list'],
|
||
|
|
meta['certify_audit_status'], meta['certify_gender'], meta['identity_type'],
|
||
|
|
meta['identity_extras'], meta['identity_status'], meta['identity_slogan'],
|
||
|
|
meta['identity_list'], meta['identity_slogan_ext'], meta['identity_live_url'],
|
||
|
|
meta['identity_live_type'], meta['plus_is_plus'], meta['user_info_raw']
|
||
|
|
]
|
||
|
|
|
||
|
|
# Build SQL dynamically
|
||
|
|
placeholders = ','.join(['?' for _ in player_columns])
|
||
|
|
columns_sql = ','.join(player_columns)
|
||
|
|
sql = f"INSERT OR REPLACE INTO dim_players ({columns_sql}) VALUES ({placeholders})"
|
||
|
|
|
||
|
|
cursor.execute(sql, player_values)
|
||
|
|
|
||
|
|
# Process fact_match_players
|
||
|
|
for steam_id, stats in match_data.players.items():
|
||
|
|
player_stats_row = _build_player_stats_tuple(match_data.match_id, stats)
|
||
|
|
cursor.execute(_get_fact_match_players_insert_sql(), player_stats_row)
|
||
|
|
|
||
|
|
# Process fact_match_players_t
|
||
|
|
for steam_id, stats in match_data.players_t.items():
|
||
|
|
player_stats_row = _build_player_stats_tuple(match_data.match_id, stats)
|
||
|
|
cursor.execute(_get_fact_match_players_insert_sql('fact_match_players_t'), player_stats_row)
|
||
|
|
|
||
|
|
# Process fact_match_players_ct
|
||
|
|
for steam_id, stats in match_data.players_ct.items():
|
||
|
|
player_stats_row = _build_player_stats_tuple(match_data.match_id, stats)
|
||
|
|
cursor.execute(_get_fact_match_players_insert_sql('fact_match_players_ct'), player_stats_row)
|
||
|
|
|
||
|
|
logger.debug(f"Processed {len(match_data.players)} players for match {match_data.match_id}")
|
||
|
|
return True
|
||
|
|
|
||
|
|
except Exception as e:
|
||
|
|
logger.error(f"Error processing players for match {match_data.match_id}: {e}")
|
||
|
|
import traceback
|
||
|
|
traceback.print_exc()
|
||
|
|
return False
|
||
|
|
|
||
|
|
|
||
|
|
def _build_player_stats_tuple(match_id, stats):
|
||
|
|
"""Build tuple for player stats insertion"""
|
||
|
|
return (
|
||
|
|
match_id,
|
||
|
|
stats.steam_id_64,
|
||
|
|
stats.team_id,
|
||
|
|
stats.kills,
|
||
|
|
stats.deaths,
|
||
|
|
stats.assists,
|
||
|
|
stats.headshot_count,
|
||
|
|
stats.kd_ratio,
|
||
|
|
stats.adr,
|
||
|
|
stats.rating,
|
||
|
|
stats.rating2,
|
||
|
|
stats.rating3,
|
||
|
|
stats.rws,
|
||
|
|
stats.mvp_count,
|
||
|
|
stats.elo_change,
|
||
|
|
stats.origin_elo,
|
||
|
|
stats.rank_score,
|
||
|
|
stats.is_win,
|
||
|
|
stats.kast,
|
||
|
|
stats.entry_kills,
|
||
|
|
stats.entry_deaths,
|
||
|
|
stats.awp_kills,
|
||
|
|
stats.clutch_1v1,
|
||
|
|
stats.clutch_1v2,
|
||
|
|
stats.clutch_1v3,
|
||
|
|
stats.clutch_1v4,
|
||
|
|
stats.clutch_1v5,
|
||
|
|
stats.flash_assists,
|
||
|
|
stats.flash_duration,
|
||
|
|
stats.jump_count,
|
||
|
|
stats.util_flash_usage,
|
||
|
|
stats.util_smoke_usage,
|
||
|
|
stats.util_molotov_usage,
|
||
|
|
stats.util_he_usage,
|
||
|
|
stats.util_decoy_usage,
|
||
|
|
stats.damage_total,
|
||
|
|
stats.damage_received,
|
||
|
|
stats.damage_receive,
|
||
|
|
stats.damage_stats,
|
||
|
|
stats.assisted_kill,
|
||
|
|
stats.awp_kill,
|
||
|
|
stats.awp_kill_ct,
|
||
|
|
stats.awp_kill_t,
|
||
|
|
stats.benefit_kill,
|
||
|
|
stats.day,
|
||
|
|
stats.defused_bomb,
|
||
|
|
stats.end_1v1,
|
||
|
|
stats.end_1v2,
|
||
|
|
stats.end_1v3,
|
||
|
|
stats.end_1v4,
|
||
|
|
stats.end_1v5,
|
||
|
|
stats.explode_bomb,
|
||
|
|
stats.first_death,
|
||
|
|
stats.fd_ct,
|
||
|
|
stats.fd_t,
|
||
|
|
stats.first_kill,
|
||
|
|
stats.flash_enemy,
|
||
|
|
stats.flash_team,
|
||
|
|
stats.flash_team_time,
|
||
|
|
stats.flash_time,
|
||
|
|
stats.game_mode,
|
||
|
|
stats.group_id,
|
||
|
|
stats.hold_total,
|
||
|
|
stats.id,
|
||
|
|
stats.is_highlight,
|
||
|
|
stats.is_most_1v2,
|
||
|
|
stats.is_most_assist,
|
||
|
|
stats.is_most_awp,
|
||
|
|
stats.is_most_end,
|
||
|
|
stats.is_most_first_kill,
|
||
|
|
stats.is_most_headshot,
|
||
|
|
stats.is_most_jump,
|
||
|
|
stats.is_svp,
|
||
|
|
stats.is_tie,
|
||
|
|
stats.kill_1,
|
||
|
|
stats.kill_2,
|
||
|
|
stats.kill_3,
|
||
|
|
stats.kill_4,
|
||
|
|
stats.kill_5,
|
||
|
|
stats.many_assists_cnt1,
|
||
|
|
stats.many_assists_cnt2,
|
||
|
|
stats.many_assists_cnt3,
|
||
|
|
stats.many_assists_cnt4,
|
||
|
|
stats.many_assists_cnt5,
|
||
|
|
stats.map,
|
||
|
|
stats.match_code,
|
||
|
|
stats.match_mode,
|
||
|
|
stats.match_team_id,
|
||
|
|
stats.match_time,
|
||
|
|
stats.per_headshot,
|
||
|
|
stats.perfect_kill,
|
||
|
|
stats.planted_bomb,
|
||
|
|
stats.revenge_kill,
|
||
|
|
stats.round_total,
|
||
|
|
stats.season,
|
||
|
|
stats.team_kill,
|
||
|
|
stats.throw_harm,
|
||
|
|
stats.throw_harm_enemy,
|
||
|
|
stats.uid,
|
||
|
|
stats.year,
|
||
|
|
stats.sts_raw,
|
||
|
|
stats.level_info_raw
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def _get_fact_match_players_insert_sql(table='fact_match_players'):
|
||
|
|
"""Get INSERT SQL for player stats table - dynamically generated"""
|
||
|
|
# Define columns explicitly to ensure exact match with schema
|
||
|
|
columns = [
|
||
|
|
'match_id', 'steam_id_64', 'team_id', 'kills', 'deaths', 'assists', 'headshot_count',
|
||
|
|
'kd_ratio', 'adr', 'rating', 'rating2', 'rating3', 'rws', 'mvp_count', 'elo_change',
|
||
|
|
'origin_elo', 'rank_score', 'is_win', 'kast', 'entry_kills', 'entry_deaths', 'awp_kills',
|
||
|
|
'clutch_1v1', 'clutch_1v2', 'clutch_1v3', 'clutch_1v4', 'clutch_1v5',
|
||
|
|
'flash_assists', 'flash_duration', 'jump_count', 'util_flash_usage',
|
||
|
|
'util_smoke_usage', 'util_molotov_usage', 'util_he_usage', 'util_decoy_usage',
|
||
|
|
'damage_total', 'damage_received', 'damage_receive', 'damage_stats',
|
||
|
|
'assisted_kill', 'awp_kill', 'awp_kill_ct', 'awp_kill_t', 'benefit_kill',
|
||
|
|
'day', 'defused_bomb', 'end_1v1', 'end_1v2', 'end_1v3', 'end_1v4', 'end_1v5',
|
||
|
|
'explode_bomb', 'first_death', 'fd_ct', 'fd_t', 'first_kill', 'flash_enemy',
|
||
|
|
'flash_team', 'flash_team_time', 'flash_time', 'game_mode', 'group_id',
|
||
|
|
'hold_total', 'id', 'is_highlight', 'is_most_1v2', 'is_most_assist',
|
||
|
|
'is_most_awp', 'is_most_end', 'is_most_first_kill', 'is_most_headshot',
|
||
|
|
'is_most_jump', 'is_svp', 'is_tie', 'kill_1', 'kill_2', 'kill_3', 'kill_4', 'kill_5',
|
||
|
|
'many_assists_cnt1', 'many_assists_cnt2', 'many_assists_cnt3',
|
||
|
|
'many_assists_cnt4', 'many_assists_cnt5', 'map', 'match_code', 'match_mode',
|
||
|
|
'match_team_id', 'match_time', 'per_headshot', 'perfect_kill', 'planted_bomb',
|
||
|
|
'revenge_kill', 'round_total', 'season', 'team_kill', 'throw_harm',
|
||
|
|
'throw_harm_enemy', 'uid', 'year', 'sts_raw', 'level_info_raw'
|
||
|
|
]
|
||
|
|
placeholders = ','.join(['?' for _ in columns])
|
||
|
|
columns_sql = ','.join(columns)
|
||
|
|
return f'INSERT OR REPLACE INTO {table} ({columns_sql}) VALUES ({placeholders})'
|