Like 0
Fork 0

Last active 2 months ago

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 14 insertions, 3 deletions

stats-v3-migration.sql

@@ -82,7 +82,11 @@ alter table stats_sessions
82 82 add seconds_alive int unsigned not null,
83 83 add seconds_idle int unsigned not null,
84 84 add seconds_dead int unsigned default 0,
85 - add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead);
85 + add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
86 + add damage_taken_fall float,
87 + add times_shove mediumint,
88 + add times_jumped mediumint,
89 + add bullets_fired mediumint;
86 90 # get new game id, update seconds_* (not perfect but whatever)
87 91 update stats_sessions u
88 92 join stats_games g on g.uuid = u.campaignID
@@ -127,7 +131,9 @@ alter table stats_users
127 131 change damage_to_tank damage_dealt_tank int unsigned default 0 null,
128 132 drop column damage_as_tank,
129 133 change damage_witch damage_dealt_witch int unsigned default 0 not null,
130 - change packs_used used_ammo_packs int unsigned default 0 not null,
134 + drop packs_used,
135 + add used_ammopack_fire mediumint unsigned default 0 not null,
136 + add used_ammopack_explosive mediumint unsigned default 0 not null,
131 137 change ff_kills kills_friendly int unsigned default 0 not null comment 'teammates killed',
132 138 change damage_molotov damage_dealt_fire int unsigned default 0 not null comment 'gascan/molotov',
133 139 change kills_molotov kills_fire int unsigned default 0 not null comment 'gascan/molotov',
@@ -151,7 +157,12 @@ alter table stats_users
151 157 add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
152 158 change throws_puke used_bile mediumint unsigned default 0 not null comment 'throws',
153 159 change throws_molotov used_molotov mediumint unsigned default 0 not null comment 'throws',
154 - change throws_pipe used_pipebomb mediumint unsigned default 0 not null comment 'throws';
160 + change throws_pipe used_pipebomb mediumint unsigned default 0 not null comment 'throws',
161 + add damage_taken_fall float,
162 + add times_shove mediumint,
163 + add times_jumped mediumint,
164 + add bullets_fired mediumint;
165 +
155 166
156 167 update stats_users
157 168 set seconds_alive = minutes_played * 60, seconds_idle = minutes_idle * 60;

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 34 insertions, 33 deletions

stats-v3-migration.sql

@@ -34,12 +34,13 @@ alter table stats_sessions
34 34 change zombie_kills kills_common int unsigned default 0 not null,
35 35 change melee_kills kills_melee smallint(10) unsigned default 0 not null,
36 36 change SurvivorDamage damage_dealt int unsigned default 0 not null,
37 - change SurvivorFFCount damage_dealt_friendly_count mediumint unsigned default 0 not null,
38 - change SurvivorFFTakenCount damage_taken_friendly_count int null,
39 - change SurvivorFFDamage damage_dealt_friendly int null,
40 - change SurvivorFFTakenDamage damage_taken_friendly int null,
41 - change MedkitsUsed used_kit_self tinyint(10) unsigned default 0 not null comment 'heal self',
42 - change FirstAidShared used_kit_other tinyint(10) unsigned default 0 not null comment 'healed teammate',
37 + change SurvivorFFCount damage_dealt_friendly_count int unsigned default 0 not null,
38 + change SurvivorFFTakenCount damage_taken_friendly_count int unsigned null,
39 + change SurvivorFFDamage damage_dealt_friendly int unsigned null,
40 + change SurvivorFFTakenDamage damage_taken_friendly int unsigned null,
41 + change MedkitsUsed used_kit_self smallint(10) unsigned default 0 not null comment 'heal self',
42 + change FirstAidShared used_kit_other smallint(10) unsigned default 0 not null comment 'healed teammate',
43 + add used_kit smallint(11) unsigned as (used_kit_self + used_kit_other) comment 'any usage',
43 44 change PillsUsed used_pills smallint(10) unsigned default 0 not null,
44 45 change MolotovsUsed used_molotov smallint(10) unsigned default 0 not null,
45 46 change PipebombsUsed used_pipebomb smallint(10) unsigned default 0 not null,
@@ -100,23 +101,23 @@ alter table stats_sessions
100 101 references stats_games (id)
101 102 on update cascade on delete cascade;
102 103 alter table stats_users
103 - change survivor_deaths deaths int(11) unsigned default 0 not null,
104 + change survivor_deaths deaths mediumint(11) unsigned default 0 not null,
104 105 drop infected_deaths,
105 - change survivor_damage_rec damage_taken bigint(11) unsigned default 0 not null,
106 - change survivor_damage_give damage_dealt bigint(11) unsigned default 0 not null,
106 + change survivor_damage_rec damage_taken int(11) unsigned default 0 not null,
107 + change survivor_damage_give damage_dealt int(11) unsigned default 0 not null,
107 108 add damage_dealt_friendly_count mediumint unsigned default 0 not null,
108 109 add damage_taken_friendly_count mediumint unsigned default 0 null,
109 110 drop infected_damage_rec,
110 111 drop infected_damage_give,
111 - add pickups_bile int(11) unsigned default 0 not null after pickups_molotov,
112 - change pickups_pipe_bomb pickups_pipebomb int(11) unsigned default 0 not null,
113 - add pickups_adrenaline int null after pickups_pills,
114 - change survivor_incaps times_incapped int(11) unsigned default 0 not null,
115 - change pickups_pain_pills pickups_pills int(11) unsigned default 0 not null after pickups_pipebomb,
116 - add times_hanging int(11) unsigned default 0 not null comment 'ledge grabs' after times_incapped,
117 - change revived_others times_revived_other int(11) unsigned default 0 not null,
112 + add pickups_bile mediumint(11) unsigned default 0 not null after pickups_molotov,
113 + change pickups_pipe_bomb pickups_pipebomb mediumint(11) unsigned default 0 not null,
114 + add pickups_adrenaline mediumint unsigned null after pickups_pills,
115 + change survivor_incaps times_incapped mediumint(11) unsigned default 0 not null,
116 + change pickups_pain_pills pickups_pills mediumint(11) unsigned default 0 not null after pickups_pipebomb,
117 + add times_hanging mediumint(11) unsigned default 0 not null comment 'ledge grabs' after times_incapped,
118 + change revived_others times_revived_other mediumint(11) unsigned default 0 not null,
118 119 change melee_kills kills_melee int(11) unsigned default 0 not null comment 'kils with melee',
119 - change tanks_killed kills_tank int unsigned default 0 not null,
120 + change tanks_killed kills_tank int(11) unsigned default 0 not null,
120 121 change tanks_killed_solo kills_tank_solo int unsigned default 0 not null,
121 122 change tanks_killed_melee kills_tank_melee int unsigned default 0 not null comment 'with melee',
122 123 change survivor_ff damage_dealt_friendly int unsigned default 0 not null,
@@ -131,26 +132,26 @@ alter table stats_users
131 132 change damage_molotov damage_dealt_fire int unsigned default 0 not null comment 'gascan/molotov',
132 133 change kills_molotov kills_fire int unsigned default 0 not null comment 'gascan/molotov',
133 134 change kills_pipe kills_pipebomb int unsigned default 0 not null,
134 - change clowns_honked honks smallint unsigned default 0 not null comment 'clown honks',
135 - change boomer_mellos times_boomed_teammates int default 0 null comment 'popped boomer and got someone boomed',
136 - change boomer_mellos_self times_boomed_self smallint default 0 null comment 'popped boomer and got self boomed',
137 - add times_boomed smallint default 0 null after times_boomed_self,
138 - change heal_self used_kit_self int(11) unsigned default 0 not null comment 'healed self',
139 - change heal_others used_kit_other int(11) unsigned default 0 not null comment 'healed teammate',
140 - add used_kit int(11) as (used_kit_self + used_kit_other) comment 'any usage',
141 - change defibs_used used_defib int(11) unsigned default 0 not null after used_kit_other,
142 - change pills_used used_pills int(11) unsigned default 0 not null,
143 - change adrenaline_used used_adrenaline int(11) unsigned default 0 not null after used_pills,
144 - change revived times_revived int(11) unsigned default 0 not null comment 'self got incapped',
145 - change cleared_pinned times_cleared_pinned int unsigned default 0 not null comment 'helped survivor that was pinned',
146 - add rocks_dodged int unsigned default 0 not null after rocks_hitby,
135 + change clowns_honked honks int unsigned default 0 not null comment 'clown honks',
136 + change boomer_mellos times_boomed_teammates mediumint unsigned default 0 null comment 'popped boomer and got someone boomed',
137 + change boomer_mellos_self times_boomed_self mediumint unsigned default 0 null comment 'popped boomer and got self boomed',
138 + add times_boomed mediumint unsigned default 0 null after times_boomed_self,
139 + change heal_self used_kit_self mediumint unsigned default 0 not null comment 'healed self',
140 + change heal_others used_kit_other mediumint unsigned default 0 not null comment 'healed teammate',
141 + add used_kit int as (used_kit_self + used_kit_other) comment 'any usage',
142 + change defibs_used used_defib mediumint unsigned default 0 not null after used_kit_other,
143 + change pills_used used_pills mediumint unsigned default 0 not null,
144 + change adrenaline_used used_adrenaline mediumint unsigned default 0 not null after used_pills,
145 + change revived times_revived mediumint unsigned default 0 not null comment 'self got incapped',
146 + change cleared_pinned times_cleared_pinned mediumint unsigned default 0 not null comment 'helped survivor that was pinned',
147 + add rocks_dodged mediumint unsigned default 0 not null after rocks_hitby,
147 148 add seconds_alive int unsigned not null,
148 149 add seconds_idle int unsigned not null,
149 150 add seconds_dead int unsigned default 0,
150 151 add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
151 - change throws_puke used_bile int unsigned default 0 not null comment 'throws',
152 - change throws_molotov used_molotov int unsigned default 0 not null comment 'throws',
153 - change throws_pipe used_pipebomb int unsigned default 0 not null comment 'throws';
152 + change throws_puke used_bile mediumint unsigned default 0 not null comment 'throws',
153 + change throws_molotov used_molotov mediumint unsigned default 0 not null comment 'throws',
154 + change throws_pipe used_pipebomb mediumint unsigned default 0 not null comment 'throws';
154 155
155 156 update stats_users
156 157 set seconds_alive = minutes_played * 60, seconds_idle = minutes_idle * 60;

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 1 insertion, 1 deletion

stats-v3-migration.sql

@@ -6,7 +6,7 @@ create table stats_games
6 6 date_start bigint not null comment 'unix timestamp',
7 7 date_start_finale int null comment 'unix timestamp of finale',
8 8 date_end bigint null comment 'unix timestamp',
9 - duraton_game bigint as (`date_end` - `date_start`) comment 'seconds of game',
9 + duration_game bigint as (`date_end` - `date_start`) comment 'seconds of game',
10 10 duration_finale bigint as (`date_end` - `date_start_finale`) comment 'seconds of finale',
11 11 map_id varchar(64) not null comment 'map id of last chapter',
12 12 gamemode varchar(64) not null,

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 6 insertions, 3 deletions

stats-v3-migration.sql

@@ -75,7 +75,7 @@ alter table stats_sessions
75 75 add times_hanging smallint unsigned default 0 null after times_revived_other,
76 76 add kills_tank smallint unsigned null after kills_all_specials,
77 77 add kills_witch smallint unsigned null after kills_tank,
78 - add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch,
78 + add kills_fire smallint unsigned null comment 'gascan/molotov' after kills_witch,
79 79 add kills_pipebomb smallint unsigned null after kills_fire,
80 80 add kills_minigun smallint unsigned null after kills_pipebomb,
81 81 add seconds_alive int unsigned not null,
@@ -92,6 +92,9 @@ alter table stats_sessions
92 92 drop date_start,
93 93 drop date_end,
94 94 drop minutes_idle,
95 + drop index stats_games_campaignID_index,
96 + drop key campaignID,
97 + drop column campaignID,
95 98 add constraint stats_sessions_stats_games_id_fk
96 99 foreign key (game_id)
97 100 references stats_games (id)
@@ -114,7 +117,7 @@ alter table stats_users
114 117 change revived_others times_revived_other int(11) unsigned default 0 not null,
115 118 change melee_kills kills_melee int(11) unsigned default 0 not null comment 'kils with melee',
116 119 change tanks_killed kills_tank int unsigned default 0 not null,
117 - change tanks_killed_solo kils_tank_solo int unsigned default 0 not null,
120 + change tanks_killed_solo kills_tank_solo int unsigned default 0 not null,
118 121 change tanks_killed_melee kills_tank_melee int unsigned default 0 not null comment 'with melee',
119 122 change survivor_ff damage_dealt_friendly int unsigned default 0 not null,
120 123 change survivor_ff_rec damage_taken_friendly int default 0 null comment 'ff recv',
@@ -131,7 +134,7 @@ alter table stats_users
131 134 change clowns_honked honks smallint unsigned default 0 not null comment 'clown honks',
132 135 change boomer_mellos times_boomed_teammates int default 0 null comment 'popped boomer and got someone boomed',
133 136 change boomer_mellos_self times_boomed_self smallint default 0 null comment 'popped boomer and got self boomed',
134 - add times_boomed int null after times_boomed_self,
137 + add times_boomed smallint default 0 null after times_boomed_self,
135 138 change heal_self used_kit_self int(11) unsigned default 0 not null comment 'healed self',
136 139 change heal_others used_kit_other int(11) unsigned default 0 not null comment 'healed teammate',
137 140 add used_kit int(11) as (used_kit_self + used_kit_other) comment 'any usage',

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 13 insertions, 5 deletions

stats-v3-migration.sql

@@ -47,7 +47,7 @@ alter table stats_sessions
47 47 change AdrenalinesUsed used_adrenaline smallint(10) unsigned default 0 not null,
48 48 change DefibrillatorsUsed used_defib smallint(10) unsigned default 0 not null,
49 49 change DamageTaken damage_taken int unsigned default 0 not null,
50 - change ReviveOtherCount times_revive_other smallint(10) unsigned default 0 not null,
50 + change ReviveOtherCount times_revived_other smallint(10) unsigned default 0 not null,
51 51 change Incaps times_incapped smallint(10) unsigned default 0 not null,
52 52 change Deaths deaths tinyint(10) unsigned default 0 not null,
53 53 change boomer_kills kills_boomer smallint(10) unsigned not null,
@@ -72,7 +72,7 @@ alter table stats_sessions
72 72 change CarAlarmsActivated caralarms_activated tinyint unsigned default 0 not null,
73 73 # new metrics
74 74 add longest_shot_distance float null,
75 - add times_hanging smallint unsigned default 0 null after times_revive_other,
75 + add times_hanging smallint unsigned default 0 null after times_revived_other,
76 76 add kills_tank smallint unsigned null after kills_all_specials,
77 77 add kills_witch smallint unsigned null after kills_tank,
78 78 add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch,
@@ -101,6 +101,8 @@ alter table stats_users
101 101 drop infected_deaths,
102 102 change survivor_damage_rec damage_taken bigint(11) unsigned default 0 not null,
103 103 change survivor_damage_give damage_dealt bigint(11) unsigned default 0 not null,
104 + add damage_dealt_friendly_count mediumint unsigned default 0 not null,
105 + add damage_taken_friendly_count mediumint unsigned default 0 null,
104 106 drop infected_damage_rec,
105 107 drop infected_damage_give,
106 108 add pickups_bile int(11) unsigned default 0 not null after pickups_molotov,
@@ -109,7 +111,7 @@ alter table stats_users
109 111 change survivor_incaps times_incapped int(11) unsigned default 0 not null,
110 112 change pickups_pain_pills pickups_pills int(11) unsigned default 0 not null after pickups_pipebomb,
111 113 add times_hanging int(11) unsigned default 0 not null comment 'ledge grabs' after times_incapped,
112 - change revived_others times_revive_other int(11) unsigned default 0 not null,
114 + change revived_others times_revived_other int(11) unsigned default 0 not null,
113 115 change melee_kills kills_melee int(11) unsigned default 0 not null comment 'kils with melee',
114 116 change tanks_killed kills_tank int unsigned default 0 not null,
115 117 change tanks_killed_solo kils_tank_solo int unsigned default 0 not null,
@@ -127,7 +129,7 @@ alter table stats_users
127 129 change kills_molotov kills_fire int unsigned default 0 not null comment 'gascan/molotov',
128 130 change kills_pipe kills_pipebomb int unsigned default 0 not null,
129 131 change clowns_honked honks smallint unsigned default 0 not null comment 'clown honks',
130 - change boomer_mellos times_boomed_teammate int default 0 null comment 'popped boomer and got someone boomed',
132 + change boomer_mellos times_boomed_teammates int default 0 null comment 'popped boomer and got someone boomed',
131 133 change boomer_mellos_self times_boomed_self smallint default 0 null comment 'popped boomer and got self boomed',
132 134 add times_boomed int null after times_boomed_self,
133 135 change heal_self used_kit_self int(11) unsigned default 0 not null comment 'healed self',
@@ -137,10 +139,16 @@ alter table stats_users
137 139 change pills_used used_pills int(11) unsigned default 0 not null,
138 140 change adrenaline_used used_adrenaline int(11) unsigned default 0 not null after used_pills,
139 141 change revived times_revived int(11) unsigned default 0 not null comment 'self got incapped',
142 + change cleared_pinned times_cleared_pinned int unsigned default 0 not null comment 'helped survivor that was pinned',
143 + add rocks_dodged int unsigned default 0 not null after rocks_hitby,
140 144 add seconds_alive int unsigned not null,
141 145 add seconds_idle int unsigned not null,
142 146 add seconds_dead int unsigned default 0,
143 - add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead);
147 + add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead),
148 + change throws_puke used_bile int unsigned default 0 not null comment 'throws',
149 + change throws_molotov used_molotov int unsigned default 0 not null comment 'throws',
150 + change throws_pipe used_pipebomb int unsigned default 0 not null comment 'throws';
151 +
144 152 update stats_users
145 153 set seconds_alive = minutes_played * 60, seconds_idle = minutes_idle * 60;
146 154 alter table stats_users

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 3 insertions, 2 deletions

stats-v1-migration.sql renamed to stats-v3-migration.sql

@@ -97,8 +97,8 @@ alter table stats_sessions
97 97 references stats_games (id)
98 98 on update cascade on delete cascade;
99 99 alter table stats_users
100 - change survivor_deaths deaths_as_survivor int(11) unsigned default 0 not null,
101 - change infected_deaths deaths_as_infected int(11) unsigned default 0 not null,
100 + change survivor_deaths deaths int(11) unsigned default 0 not null,
101 + drop infected_deaths,
102 102 change survivor_damage_rec damage_taken bigint(11) unsigned default 0 not null,
103 103 change survivor_damage_give damage_dealt bigint(11) unsigned default 0 not null,
104 104 drop infected_damage_rec,
@@ -132,6 +132,7 @@ alter table stats_users
132 132 add times_boomed int null after times_boomed_self,
133 133 change heal_self used_kit_self int(11) unsigned default 0 not null comment 'healed self',
134 134 change heal_others used_kit_other int(11) unsigned default 0 not null comment 'healed teammate',
135 + add used_kit int(11) as (used_kit_self + used_kit_other) comment 'any usage',
135 136 change defibs_used used_defib int(11) unsigned default 0 not null after used_kit_other,
136 137 change pills_used used_pills int(11) unsigned default 0 not null,
137 138 change adrenaline_used used_adrenaline int(11) unsigned default 0 not null after used_pills,

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 61 insertions, 12 deletions

stats-v1-migration.sql

@@ -10,7 +10,7 @@ create table stats_games
10 10 duration_finale bigint as (`date_end` - `date_start_finale`) comment 'seconds of finale',
11 11 map_id varchar(64) not null comment 'map id of last chapter',
12 12 gamemode varchar(64) not null,
13 - difficulty tinyint null,
13 + difficulty tinyint null comment '0=easy, 1=normal, 2=advanced, 3=expert',
14 14 server_tags varchar(255) not null comment 'comma separated list of tags',
15 15 stat_version tinyint unsigned not null comment 'version of metrics',
16 16 index stats_games_gamemode_index (gamemode),
@@ -23,12 +23,14 @@ FROM stats_sessions group by campaignID
23 23 order by id asc;
24 24 alter table stats_sessions
25 25 add game_id int unsigned not null after id,
26 + # moved to games
26 27 drop map,
27 28 drop gamemode,
28 29 drop difficulty,
29 30 drop finale_time,
30 31 drop server_tags,
31 32 drop DamageDealt,
33 + # renamed columns for consistency
32 34 change zombie_kills kills_common int unsigned default 0 not null,
33 35 change melee_kills kills_melee smallint(10) unsigned default 0 not null,
34 36 change SurvivorDamage damage_dealt int unsigned default 0 not null,
@@ -40,7 +42,6 @@ alter table stats_sessions
40 42 change FirstAidShared used_kit_other tinyint(10) unsigned default 0 not null comment 'healed teammate',
41 43 change PillsUsed used_pills smallint(10) unsigned default 0 not null,
42 44 change MolotovsUsed used_molotov smallint(10) unsigned default 0 not null,
43 - add times_hanging smallint unsigned default 0 null after times_revive_other,
44 45 change PipebombsUsed used_pipebomb smallint(10) unsigned default 0 not null,
45 46 change BoomerBilesUsed used_bile smallint(10) unsigned default 0 not null,
46 47 change AdrenalinesUsed used_adrenaline smallint(10) unsigned default 0 not null,
@@ -50,16 +51,11 @@ alter table stats_sessions
50 51 change Incaps times_incapped smallint(10) unsigned default 0 not null,
51 52 change Deaths deaths tinyint(10) unsigned default 0 not null,
52 53 change boomer_kills kills_boomer smallint(10) unsigned not null,
53 - add kills_tank smallint unsigned null after kills_all_specials,
54 54 change smoker_kills kills_smoker smallint(10) unsigned not null,
55 55 change jockey_kills kills_jockey smallint(10) unsigned not null,
56 - add kills_witch smallint unsigned null after kills_tank,
57 56 change hunter_kills kills_hunter smallint(10) unsigned not null,
58 - add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch,
59 - add kills_pipebomb smallint unsigned null after kills_fire,
60 57 change spitter_kills kills_spitter smallint(10) unsigned not null,
61 58 change charger_kills kills_charger smallint(10) unsigned not null,
62 - add kills_minigun smallint unsigned null after kills_pipebomb,
63 59 change SpecialInfectedKills kills_all_specials int unsigned as (`kills_boomer` + `kills_smoker` + `kills_jockey` + `kills_hunter` + `kills_spitter` + `kills_charger`),
64 60 modify honks smallint unsigned default 0 null comment 'clowns honked',
65 61 change WitchesCrowned witches_crowned smallint(8) unsigned default 0 not null,
@@ -74,17 +70,23 @@ alter table stats_sessions
74 70 change DamageToTank damage_dealt_tank mediumint unsigned default 0 not null comment 'dmg to tank',
75 71 change DamageToWitch damage_dealt_witch mediumint unsigned default 0 not null comment 'dmg to witch',
76 72 change CarAlarmsActivated caralarms_activated tinyint unsigned default 0 not null,
73 + # new metrics
77 74 add longest_shot_distance float null,
75 + add times_hanging smallint unsigned default 0 null after times_revive_other,
76 + add kills_tank smallint unsigned null after kills_all_specials,
77 + add kills_witch smallint unsigned null after kills_tank,
78 + add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch,
79 + add kills_pipebomb smallint unsigned null after kills_fire,
80 + add kills_minigun smallint unsigned null after kills_pipebomb,
78 81 add seconds_alive int unsigned not null,
79 82 add seconds_idle int unsigned not null,
80 83 add seconds_dead int unsigned default 0,
81 84 add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead);
82 -
83 - # TODO: game_id to new id
84 - # TODO: minutes_idle, minutes_played to map to seconds_
85 + # get new game id, update seconds_* (not perfect but whatever)
85 86 update stats_sessions u
86 87 join stats_games g on g.uuid = u.campaignID
87 88 SET u.game_id=g.id, u.seconds_alive=duration*60, u.seconds_idle=u.minutes_idle*60;
89 + # drop rest of columns and add fk
88 90 alter table stats_sessions
89 91 drop duration,
90 92 drop date_start,
@@ -94,5 +96,52 @@ alter table stats_sessions
94 96 foreign key (game_id)
95 97 references stats_games (id)
96 98 on update cascade on delete cascade;
97 - select * from stats_games;
98 - select * from stats_sessions;
99 + alter table stats_users
100 + change survivor_deaths deaths_as_survivor int(11) unsigned default 0 not null,
101 + change infected_deaths deaths_as_infected int(11) unsigned default 0 not null,
102 + change survivor_damage_rec damage_taken bigint(11) unsigned default 0 not null,
103 + change survivor_damage_give damage_dealt bigint(11) unsigned default 0 not null,
104 + drop infected_damage_rec,
105 + drop infected_damage_give,
106 + add pickups_bile int(11) unsigned default 0 not null after pickups_molotov,
107 + change pickups_pipe_bomb pickups_pipebomb int(11) unsigned default 0 not null,
108 + add pickups_adrenaline int null after pickups_pills,
109 + change survivor_incaps times_incapped int(11) unsigned default 0 not null,
110 + change pickups_pain_pills pickups_pills int(11) unsigned default 0 not null after pickups_pipebomb,
111 + add times_hanging int(11) unsigned default 0 not null comment 'ledge grabs' after times_incapped,
112 + change revived_others times_revive_other int(11) unsigned default 0 not null,
113 + change melee_kills kills_melee int(11) unsigned default 0 not null comment 'kils with melee',
114 + change tanks_killed kills_tank int unsigned default 0 not null,
115 + change tanks_killed_solo kils_tank_solo int unsigned default 0 not null,
116 + change tanks_killed_melee kills_tank_melee int unsigned default 0 not null comment 'with melee',
117 + change survivor_ff damage_dealt_friendly int unsigned default 0 not null,
118 + change survivor_ff_rec damage_taken_friendly int default 0 null comment 'ff recv',
119 + change common_kills kills_common int unsigned default 0 null,
120 + change common_headshots kills_common_headshots int unsigned default 0 not null,
121 + change damage_to_tank damage_dealt_tank int unsigned default 0 null,
122 + drop column damage_as_tank,
123 + change damage_witch damage_dealt_witch int unsigned default 0 not null,
124 + change packs_used used_ammo_packs int unsigned default 0 not null,
125 + change ff_kills kills_friendly int unsigned default 0 not null comment 'teammates killed',
126 + change damage_molotov damage_dealt_fire int unsigned default 0 not null comment 'gascan/molotov',
127 + change kills_molotov kills_fire int unsigned default 0 not null comment 'gascan/molotov',
128 + change kills_pipe kills_pipebomb int unsigned default 0 not null,
129 + change clowns_honked honks smallint unsigned default 0 not null comment 'clown honks',
130 + change boomer_mellos times_boomed_teammate int default 0 null comment 'popped boomer and got someone boomed',
131 + change boomer_mellos_self times_boomed_self smallint default 0 null comment 'popped boomer and got self boomed',
132 + add times_boomed int null after times_boomed_self,
133 + change heal_self used_kit_self int(11) unsigned default 0 not null comment 'healed self',
134 + change heal_others used_kit_other int(11) unsigned default 0 not null comment 'healed teammate',
135 + change defibs_used used_defib int(11) unsigned default 0 not null after used_kit_other,
136 + change pills_used used_pills int(11) unsigned default 0 not null,
137 + change adrenaline_used used_adrenaline int(11) unsigned default 0 not null after used_pills,
138 + change revived times_revived int(11) unsigned default 0 not null comment 'self got incapped',
139 + add seconds_alive int unsigned not null,
140 + add seconds_idle int unsigned not null,
141 + add seconds_dead int unsigned default 0,
142 + add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead);
143 + update stats_users
144 + set seconds_alive = minutes_played * 60, seconds_idle = minutes_idle * 60;
145 + alter table stats_users
146 + drop minutes_played,
147 + drop minutes_idle;

jackz's Avatar jackz revised this gist 2 months ago. Go to revision

1 file changed, 98 insertions

stats-v1-migration.sql(file created)

@@ -0,0 +1,98 @@
1 + rename table stats_games to stats_sessions;
2 + create table stats_games
3 + (
4 + id int unsigned auto_increment primary key,
5 + uuid uuid default uuid() not null comment 'legacy campaignID' unique,
6 + date_start bigint not null comment 'unix timestamp',
7 + date_start_finale int null comment 'unix timestamp of finale',
8 + date_end bigint null comment 'unix timestamp',
9 + duraton_game bigint as (`date_end` - `date_start`) comment 'seconds of game',
10 + duration_finale bigint as (`date_end` - `date_start_finale`) comment 'seconds of finale',
11 + map_id varchar(64) not null comment 'map id of last chapter',
12 + gamemode varchar(64) not null,
13 + difficulty tinyint null,
14 + server_tags varchar(255) not null comment 'comma separated list of tags',
15 + stat_version tinyint unsigned not null comment 'version of metrics',
16 + index stats_games_gamemode_index (gamemode),
17 + index stats_games_map_id_index (map_id)
18 + );
19 + insert into stats_games (uuid, date_start, date_start_finale, date_end, map_id, gamemode, difficulty, server_tags, stat_version)
20 + # add stats_games data from session data
21 + SELECT campaignID, date_start, date_end-finale_time, date_end, map, gamemode, difficulty, server_tags, 0
22 + FROM stats_sessions group by campaignID
23 + order by id asc;
24 + alter table stats_sessions
25 + add game_id int unsigned not null after id,
26 + drop map,
27 + drop gamemode,
28 + drop difficulty,
29 + drop finale_time,
30 + drop server_tags,
31 + drop DamageDealt,
32 + change zombie_kills kills_common int unsigned default 0 not null,
33 + change melee_kills kills_melee smallint(10) unsigned default 0 not null,
34 + change SurvivorDamage damage_dealt int unsigned default 0 not null,
35 + change SurvivorFFCount damage_dealt_friendly_count mediumint unsigned default 0 not null,
36 + change SurvivorFFTakenCount damage_taken_friendly_count int null,
37 + change SurvivorFFDamage damage_dealt_friendly int null,
38 + change SurvivorFFTakenDamage damage_taken_friendly int null,
39 + change MedkitsUsed used_kit_self tinyint(10) unsigned default 0 not null comment 'heal self',
40 + change FirstAidShared used_kit_other tinyint(10) unsigned default 0 not null comment 'healed teammate',
41 + change PillsUsed used_pills smallint(10) unsigned default 0 not null,
42 + change MolotovsUsed used_molotov smallint(10) unsigned default 0 not null,
43 + add times_hanging smallint unsigned default 0 null after times_revive_other,
44 + change PipebombsUsed used_pipebomb smallint(10) unsigned default 0 not null,
45 + change BoomerBilesUsed used_bile smallint(10) unsigned default 0 not null,
46 + change AdrenalinesUsed used_adrenaline smallint(10) unsigned default 0 not null,
47 + change DefibrillatorsUsed used_defib smallint(10) unsigned default 0 not null,
48 + change DamageTaken damage_taken int unsigned default 0 not null,
49 + change ReviveOtherCount times_revive_other smallint(10) unsigned default 0 not null,
50 + change Incaps times_incapped smallint(10) unsigned default 0 not null,
51 + change Deaths deaths tinyint(10) unsigned default 0 not null,
52 + change boomer_kills kills_boomer smallint(10) unsigned not null,
53 + add kills_tank smallint unsigned null after kills_all_specials,
54 + change smoker_kills kills_smoker smallint(10) unsigned not null,
55 + change jockey_kills kills_jockey smallint(10) unsigned not null,
56 + add kills_witch smallint unsigned null after kills_tank,
57 + change hunter_kills kills_hunter smallint(10) unsigned not null,
58 + add kills_fire smallint unsigned null comment 'gasca/molotov' after kills_witch,
59 + add kills_pipebomb smallint unsigned null after kills_fire,
60 + change spitter_kills kills_spitter smallint(10) unsigned not null,
61 + change charger_kills kills_charger smallint(10) unsigned not null,
62 + add kills_minigun smallint unsigned null after kills_pipebomb,
63 + change SpecialInfectedKills kills_all_specials int unsigned as (`kills_boomer` + `kills_smoker` + `kills_jockey` + `kills_hunter` + `kills_spitter` + `kills_charger`),
64 + modify honks smallint unsigned default 0 null comment 'clowns honked',
65 + change WitchesCrowned witches_crowned smallint(8) unsigned default 0 not null,
66 + change SmokersSelfCleared smokers_selfcleared smallint(8) unsigned default 0 not null,
67 + change RocksHitBy rocks_hitby smallint(8) unsigned default 0 not null,
68 + change RocksDodged rocks_dodged smallint(8) unsigned default 0 not null,
69 + change HuntersDeadstopped hunters_deadstopped smallint(8) unsigned default 0 not null,
70 + change TimesPinned times_pinned smallint(8) unsigned default 0 not null,
71 + change ClearedPinned times_cleared_pinned smallint(8) unsigned default 0 null comment 'helped pinned teammate',
72 + change BoomedTeammates times_boomed_teammates smallint unsigned default 0 not null,
73 + change TimesBoomed times_boomed smallint(8) unsigned default 0 not null,
74 + change DamageToTank damage_dealt_tank mediumint unsigned default 0 not null comment 'dmg to tank',
75 + change DamageToWitch damage_dealt_witch mediumint unsigned default 0 not null comment 'dmg to witch',
76 + change CarAlarmsActivated caralarms_activated tinyint unsigned default 0 not null,
77 + add longest_shot_distance float null,
78 + add seconds_alive int unsigned not null,
79 + add seconds_idle int unsigned not null,
80 + add seconds_dead int unsigned default 0,
81 + add seconds_total int unsigned as (seconds_alive + seconds_idle + seconds_dead);
82 +
83 + # TODO: game_id to new id
84 + # TODO: minutes_idle, minutes_played to map to seconds_
85 + update stats_sessions u
86 + join stats_games g on g.uuid = u.campaignID
87 + SET u.game_id=g.id, u.seconds_alive=duration*60, u.seconds_idle=u.minutes_idle*60;
88 + alter table stats_sessions
89 + drop duration,
90 + drop date_start,
91 + drop date_end,
92 + drop minutes_idle,
93 + add constraint stats_sessions_stats_games_id_fk
94 + foreign key (game_id)
95 + references stats_games (id)
96 + on update cascade on delete cascade;
97 + select * from stats_games;
98 + select * from stats_sessions;
Newer Older

Powered by Opengist ⋅ Load: 123ms⋅

English