Jackz
ee4680d11b
All checks were successful
/ Sourcemod v1.11.x (push) Successful in 44s
/ Sourcemod v1.12.x (push) Successful in 45s
/ Sourcemod v1.13.x (push) Successful in 47s
/ Create release for tags (push) Has been skipped
/ Deploy release (push) Has been skipped
/ Build Website (push) Successful in 1m0s
/ deploy (push) Has been skipped
179 lines
12 KiB
SQL
179 lines
12 KiB
SQL
rename table stats_games to stats_sessions;
|
|
create table stats_games
|
|
(
|
|
id int unsigned auto_increment primary key,
|
|
uuid uuid default uuid() not null comment 'legacy campaignID' unique,
|
|
date_start bigint not null comment 'unix timestamp',
|
|
date_start_finale int null comment 'unix timestamp of finale',
|
|
date_end bigint null comment 'unix timestamp',
|
|
duration_game bigint as (`date_end` - `date_start`) comment 'seconds of game',
|
|
duration_finale bigint as (`date_end` - `date_start_finale`) comment 'seconds of finale',
|
|
map_id varchar(64) not null comment 'map id of last chapter',
|
|
gamemode varchar(64) not null,
|
|
difficulty tinyint null comment '0=easy, 1=normal, 2=advanced, 3=expert',
|
|
server_tags varchar(255) not null comment 'comma separated list of tags',
|
|
stat_version tinyint unsigned not null comment 'version of metrics',
|
|
index stats_games_gamemode_index (gamemode),
|
|
index stats_games_map_id_index (map_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
|
insert into stats_games (uuid, date_start, date_start_finale, date_end, map_id, gamemode, difficulty, server_tags, stat_version)
|
|
# add stats_games data from session data
|
|
SELECT campaignID, date_start, date_end-finale_time, date_end, map, gamemode, difficulty, server_tags, 0
|
|
FROM stats_sessions group by campaignID
|
|
order by id asc;
|
|
alter table stats_sessions
|
|
add game_id int unsigned not null after id,
|
|
# moved to games
|
|
drop map,
|
|
drop gamemode,
|
|
drop difficulty,
|
|
drop finale_time,
|
|
drop server_tags,
|
|
drop DamageDealt,
|
|
# renamed columns for consistency
|
|
change zombie_kills kills_common int unsigned default 0 not null,
|
|
change melee_kills kills_melee smallint(10) unsigned default 0 not null,
|
|
change SurvivorDamage damage_dealt int unsigned default 0 not null,
|
|
change SurvivorFFCount damage_dealt_friendly_count int unsigned default 0 not null,
|
|
change SurvivorFFTakenCount damage_taken_friendly_count int unsigned null,
|
|
change SurvivorFFDamage damage_dealt_friendly int unsigned null,
|
|
change SurvivorFFTakenDamage damage_taken_friendly int unsigned null,
|
|
change MedkitsUsed used_kit_self smallint(10) unsigned default 0 not null comment 'heal self',
|
|
change FirstAidShared used_kit_other smallint(10) unsigned default 0 not null comment 'healed teammate',
|
|
add used_kit smallint(11) unsigned as (used_kit_self + used_kit_other) comment 'any usage',
|
|
change PillsUsed used_pills smallint(10) unsigned default 0 not null,
|
|
change MolotovsUsed used_molotov smallint(10) unsigned default 0 not null,
|
|
change PipebombsUsed used_pipebomb smallint(10) unsigned default 0 not null,
|
|
change BoomerBilesUsed used_bile smallint(10) unsigned default 0 not null,
|
|
change AdrenalinesUsed used_adrenaline smallint(10) unsigned default 0 not null,
|
|
change DefibrillatorsUsed used_defib smallint(10) unsigned default 0 not null,
|
|
change DamageTaken damage_taken int unsigned default 0 not null,
|
|
change ReviveOtherCount times_revived_other smallint(10) unsigned default 0 not null,
|
|
change Incaps times_incapped smallint(10) unsigned default 0 not null,
|
|
change Deaths deaths tinyint(10) unsigned default 0 not null,
|
|
change boomer_kills kills_boomer smallint(10) unsigned not null,
|
|
change smoker_kills kills_smoker smallint(10) unsigned not null,
|
|
change jockey_kills kills_jockey smallint(10) unsigned not null,
|
|
change hunter_kills kills_hunter smallint(10) unsigned not null,
|
|
change spitter_kills kills_spitter smallint(10) unsigned not null,
|
|
change charger_kills kills_charger smallint(10) unsigned not null,
|
|
change SpecialInfectedKills kills_all_specials int unsigned as (`kills_boomer` + `kills_smoker` + `kills_jockey` + `kills_hunter` + `kills_spitter` + `kills_charger`),
|
|
modify honks smallint unsigned default 0 null comment 'clowns honked',
|
|
change WitchesCrowned witches_crowned smallint(8) unsigned default 0 not null,
|
|
change SmokersSelfCleared smokers_selfcleared smallint(8) unsigned default 0 not null,
|
|
change RocksHitBy rocks_hitby smallint(8) unsigned default 0 not null,
|
|
change RocksDodged rocks_dodged smallint(8) unsigned default 0 not null,
|
|
change HuntersDeadstopped hunters_deadstopped smallint(8) unsigned default 0 not null,
|
|
change TimesPinned times_pinned smallint(8) unsigned default 0 not null,
|
|
change ClearedPinned times_cleared_pinned smallint(8) unsigned default 0 null comment 'helped pinned teammate',
|
|
change BoomedTeammates times_boomed_teammates smallint unsigned default 0 not null,
|
|
change TimesBoomed times_boomed smallint(8) unsigned default 0 not null,
|
|
change DamageToTank damage_dealt_tank mediumint unsigned default 0 not null comment 'dmg to tank',
|
|
change DamageToWitch damage_dealt_witch mediumint unsigned default 0 not null comment 'dmg to witch',
|
|
change CarAlarmsActivated caralarms_activated tinyint unsigned default 0 not null,
|
|
# new metrics
|
|
add longest_shot_distance float null,
|
|
add times_hanging smallint unsigned default 0 null after times_revived_other,
|
|
add kills_tank smallint unsigned null after kills_all_specials,
|
|
add kills_witch smallint unsigned null after kills_tank,
|
|
add kills_fire smallint unsigned null comment 'gascan/molotov' after kills_witch,
|
|
add kills_pipebomb smallint unsigned null after kills_fire,
|
|
add kills_minigun smallint unsigned null after kills_pipebomb,
|
|
add seconds_alive int unsigned not null,
|
|
add seconds_idle int unsigned not null,
|
|
add seconds_dead int unsigned default 0,
|
|
add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
|
|
add damage_taken_fall float,
|
|
add times_shove mediumint,
|
|
add times_jumped mediumint,
|
|
add bullets_fired mediumint,
|
|
add unique index stats_sessions_game_id_steamid_uindex
|
|
(game_id, steamid)
|
|
comment 'ensure no duplicate sessions';
|
|
# get new game id, update seconds_* (not perfect but whatever), mark as in finale
|
|
update stats_sessions s
|
|
join stats_games g on g.uuid = s.campaignID
|
|
SET s.game_id=g.id, s.seconds_alive=duration*60, s.seconds_idle=s.minutes_idle*60, s.flags=4;
|
|
# drop rest of columns and add fk
|
|
alter table stats_sessions
|
|
drop duration,
|
|
drop date_start,
|
|
drop date_end,
|
|
drop minutes_idle,
|
|
drop index stats_games_campaignID_index,
|
|
drop key campaignID,
|
|
drop column campaignID,
|
|
add constraint stats_sessions_stats_games_id_fk
|
|
foreign key (game_id)
|
|
references stats_games (id)
|
|
on update cascade on delete cascade;
|
|
alter table stats_users
|
|
change survivor_deaths deaths mediumint(11) unsigned default 0 not null,
|
|
drop infected_deaths,
|
|
change survivor_damage_rec damage_taken int(11) unsigned default 0 not null,
|
|
change survivor_damage_give damage_dealt int(11) unsigned default 0 not null,
|
|
add damage_dealt_friendly_count mediumint unsigned default 0 not null,
|
|
add damage_taken_friendly_count mediumint unsigned default 0 null,
|
|
drop infected_damage_rec,
|
|
drop infected_damage_give,
|
|
add pickups_bile mediumint(11) unsigned default 0 not null after pickups_molotov,
|
|
change pickups_pipe_bomb pickups_pipebomb mediumint(11) unsigned default 0 not null,
|
|
add pickups_adrenaline mediumint unsigned default 0 not null after pickups_pills,
|
|
change survivor_incaps times_incapped mediumint(11) unsigned default 0 not null,
|
|
change pickups_pain_pills pickups_pills mediumint(11) unsigned default 0 not null after pickups_pipebomb,
|
|
add times_hanging mediumint(11) unsigned default 0 not null comment 'ledge grabs' after times_incapped,
|
|
change revived_others times_revived_other mediumint(11) unsigned default 0 not null,
|
|
change melee_kills kills_melee int(11) unsigned default 0 not null comment 'kils with melee',
|
|
change tanks_killed kills_tank int(11) unsigned default 0 not null,
|
|
change tanks_killed_solo kills_tank_solo int unsigned default 0 not null,
|
|
change tanks_killed_melee kills_tank_melee int unsigned default 0 not null comment 'with melee',
|
|
change survivor_ff damage_dealt_friendly int unsigned default 0 not null,
|
|
change survivor_ff_rec damage_taken_friendly int default 0 null comment 'ff recv',
|
|
change common_kills kills_common int unsigned default 0 null,
|
|
change common_headshots kills_common_headshots int unsigned default 0 not null,
|
|
change damage_to_tank damage_dealt_tank int unsigned default 0 not null,
|
|
drop column damage_as_tank,
|
|
change damage_witch damage_dealt_witch int unsigned default 0 not null,
|
|
drop packs_used,
|
|
add used_ammopack_fire mediumint unsigned default 0 not null,
|
|
add used_ammopack_explosive mediumint unsigned default 0 not null,
|
|
change ff_kills kills_friendly int unsigned default 0 not null comment 'teammates killed',
|
|
change damage_molotov damage_dealt_fire int unsigned default 0 not null comment 'gascan/molotov',
|
|
change kills_molotov kills_fire int unsigned default 0 not null comment 'gascan/molotov',
|
|
change kills_pipe kills_pipebomb int unsigned default 0 not null,
|
|
change clowns_honked honks int unsigned default 0 not null comment 'clown honks',
|
|
change boomer_mellos times_boomed_teammates mediumint unsigned default 0 not null comment 'popped boomer and got someone boomed',
|
|
change boomer_mellos_self times_boomed_self mediumint unsigned default 0 not null comment 'popped boomer and got self boomed',
|
|
add times_boomed mediumint unsigned default 0 not null after times_boomed_self,
|
|
change heal_self used_kit_self mediumint unsigned default 0 not null comment 'healed self',
|
|
change heal_others used_kit_other mediumint unsigned default 0 not null comment 'healed teammate',
|
|
add used_kit int as (used_kit_self + used_kit_other) comment 'any usage',
|
|
change defibs_used used_defib mediumint unsigned default 0 not null after used_kit_other,
|
|
change pills_used used_pills mediumint unsigned default 0 not null,
|
|
change adrenaline_used used_adrenaline mediumint unsigned default 0 not null after used_pills,
|
|
change revived times_revived mediumint unsigned default 0 not null comment 'self got incapped',
|
|
change cleared_pinned times_cleared_pinned mediumint unsigned default 0 not null comment 'helped survivor that was pinned',
|
|
add rocks_dodged mediumint unsigned default 0 not null after rocks_hitby,
|
|
add seconds_alive int unsigned default 0 not null,
|
|
add seconds_idle int unsigned default 0 not null,
|
|
add seconds_dead int unsigned default 0 not null,
|
|
add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
|
|
change throws_puke used_bile mediumint unsigned default 0 not null comment 'throws',
|
|
change throws_molotov used_molotov mediumint unsigned default 0 not null comment 'throws',
|
|
change throws_pipe used_pipebomb mediumint unsigned default 0 not null comment 'throws',
|
|
add damage_taken_fall float default 0 not null,
|
|
add times_shove mediumint default 0 not null,
|
|
add times_jumped mediumint default 0 not null,
|
|
add bullets_fired mediumint default 0 not null,
|
|
add times_incapped_fire mediumint unsigned default 0 not null,
|
|
add times_incapped_acid mediumint unsigned default 0 not null,
|
|
add times_incapped_zombie mediumint unsigned default 0 not null,
|
|
add times_incapped_special mediumint unsigned default 0 not null,
|
|
add times_incapped_tank mediumint unsigned default 0 not null,
|
|
add times_incapped_witch mediumint unsigned default 0 not null;
|
|
|
|
update stats_users
|
|
set seconds_alive = minutes_played * 60, seconds_idle = minutes_idle * 60;
|
|
alter table stats_users
|
|
drop minutes_played,
|
|
drop minutes_idle;
|