jackz / stats-v3-migration.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', duraton_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, 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) ); 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, drop map, drop gamemode, drop difficulty, drop finale_time, drop server_tags, drop DamageDealt, 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 mediumint unsigned default 0 not null, change SurvivorFFTakenCount damage_taken_friendly_count int null, change SurvivorFFDamage damage_dealt_friendly int null, change SurvivorFFTakenDamage damage_taken_friendly int null, change MedkitsUsed used_kit_self tinyint(10) unsigned default 0 not null comment 'heal self', change FirstAidShared used_kit_other tinyint(10) unsigned default 0 not null comment 'healed teammate', change PillsUsed used_pills smallint(10) unsigned default 0 not null, change MolotovsUsed used_molotov smallint(10) unsigned default 0 not null, add times_hanging smallint unsigned default 0 null after times_revive_other, 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_revive_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, add kills_tank smallint unsigned null after kills_all_specials, change smoker_kills kills_smoker smallint(10) unsigned not null, change jockey_kills kills_jockey smallint(10) unsigned not null, add kills_witch smallint unsigned null after kills_tank, change hunter_kills kills_hunter smallint(10) unsigned not null, add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch, add kills_pipebomb smallint unsigned null after kills_fire, change spitter_kills kills_spitter smallint(10) unsigned not null, change charger_kills kills_charger smallint(10) unsigned not null, add kills_minigun smallint unsigned null after kills_pipebomb, 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, add longest_shot_distance float null, 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); # TODO: game_id to new id # TODO: minutes_idle, minutes_played to map to seconds_ update stats_sessions u join stats_games g on g.uuid = u.campaignID SET u.game_id=g.id, u.seconds_alive=duration*60, u.seconds_idle=u.minutes_idle*60; alter table stats_sessions drop duration, drop date_start, drop date_end, drop minutes_idle, add constraint stats_sessions_stats_games_id_fk foreign key (game_id) references stats_games (id) on update cascade on delete cascade; select * from stats_games; select * from stats_sessions;