FATx2hwb

1/*
2  $Id: sp_mysql_sportsdb_delete_event_data_by_id.sql 2872 2011-04-08 21:48:13Z ian $
3  
4  WARNING:
5  This code removes data from a SportsDB database. It has been reviewed and tested in an attempt
6  to ensure that it does not harm the integrity of a SportsDB database or remove data that it
7  should not, but it may not function as anticipated. This code comes with absolutely no
8  guarantees. It is your responsibility to check this code to ensure that you are satisfied that
9  it will work as expected. XML Team Inc. takes no responsibility for any unexpected data loss
10  or data corruption that may occur as a result of the execution of this code on your database.  
11  
12  This is a stored procedure to clear out the events table and all dependent table records by event_id.
13*/
14
15delimiter $$
16
17DROP PROCEDURE IF EXISTS sportsdb_delete_event_data_by_id$$
18
19CREATE PROCEDURE sportsdb_delete_event_data_by_id
20(IN in_event_id INTEGER)
21BEGIN
22  main: BEGIN
23
24  DECLARE event_id_test INTEGER;
25
26  DECLARE v_no_more_rows BOOLEAN;
27  DECLARE v_count INT;
28
29  DECLARE v_stat_id INTEGER;
30  DECLARE v_repository_type VARCHAR(120);
31  DECLARE v_repository_id INTEGER;
32  DECLARE v_period_id INTEGER;
33
34  /* A cursor to look up stats related to periods tied to an event. */
35  DECLARE cur_period_stats CURSOR FOR
36    SELECT stats.id, stats.stat_repository_type, stats.stat_repository_id FROM stats
37    JOIN participants_events ON (participants_events.event_id = in_event_id)
38    JOIN periods ON (periods.participant_event_id = participants_events.id)
39    WHERE stats.stat_coverage_type = 'periods'
40    AND stats.stat_coverage_id = periods.id;
41        
42  /* A cursor to look up stats related to an event. */
43  DECLARE cur_event_stats CURSOR FOR
44    SELECT stats.id, stats.stat_repository_type, stats.stat_repository_id FROM stats
45    WHERE stats.stat_coverage_type = 'events'
46    AND stats.stat_coverage_id = in_event_id;
47
48  /* A cursor to look up periods tied to an event. */
49  DECLARE cur_periods CURSOR FOR
50    SELECT id FROM periods
51    WHERE periods.participant_event_id IN (
52      SELECT participants_events.id FROM participants_events
53      WHERE participants_events.event_id = in_event_id  
54    );
55
56  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_rows = TRUE;
57
58  SELECT id INTO event_id_test FROM events
59  WHERE events.id = in_event_id;
60  
61  /* Leave if no event_id found to match the one passed in */
62  IF event_id_test IS NULL THEN
63    LEAVE main;
64  END IF;
65  
66  /*
67    Stats-table-related clearing SQL
68  */
69
70  /* 
71    Delete stats tied to periods and events using cursors and
72    dynamic queries aimed at the stats table and the
73    table each stats record points to. 
74
75    Doing so via individual queries per sports stats table
76    is brittle (new tables will be missed) and requires
77    repeated expensive joins to the stats table and
78    per-sport stats tables that can be very large also. 
79  */
80
81  OPEN cur_period_stats;
82  period_stats_loop: LOOP
83    FETCH cur_period_stats INTO v_stat_id, v_repository_type, v_repository_id;
84    
85    IF v_no_more_rows THEN
86      SET v_no_more_rows = FALSE;
87      CLOSE cur_period_stats;
88      LEAVE period_stats_loop;
89    END IF;
90
91    SET @s = CONCAT('DELETE FROM ', v_repository_type, ' WHERE id = ', v_repository_id);
92    PREPARE stmt FROM @s;
93    EXECUTE stmt;
94    DEALLOCATE PREPARE stmt;
95    
96    SET @s = CONCAT('DELETE FROM stats where id = ', v_stat_id);
97    PREPARE stmt FROM @s;
98    EXECUTE stmt;
99    DEALLOCATE PREPARE stmt;
100  END LOOP period_stats_loop;
101
102  /* Stats tied to events */
103
104  OPEN cur_event_stats;
105  event_stats_loop: LOOP
106    FETCH cur_event_stats INTO v_stat_id, v_repository_type, v_repository_id;
107    
108    IF v_no_more_rows THEN
109      SET v_no_more_rows = FALSE;
110      CLOSE cur_event_stats;
111      LEAVE event_stats_loop;
112    END IF;
113
114    SET @s = CONCAT('DELETE FROM ', v_repository_type, ' WHERE id = ', v_repository_id);
115    PREPARE stmt FROM @s;
116    EXECUTE stmt;
117    DEALLOCATE PREPARE stmt;
118    
119    SET @s = CONCAT('DELETE FROM stats where id = ', v_stat_id);
120    PREPARE stmt FROM @s;
121    EXECUTE stmt;
122    DEALLOCATE PREPARE stmt;
123  END LOOP event_stats_loop;
124  
125
126  /*
127    American Football event-related clearing SQL
128  */
129
130  SELECT count(*) INTO v_count 
131  FROM american_football_event_states 
132  WHERE event_id =  in_event_id; 
133
134  IF v_count > 0 THEN
135    DELETE FROM american_football_action_participants
136    WHERE american_football_action_participants.american_football_action_play_id IN (
137      SELECT american_football_action_plays.id FROM american_football_action_plays
138      JOIN american_football_event_states ON (
139           american_football_event_states.id =  american_football_action_plays.american_football_event_state_id)
140       JOIN events ON american_football_event_states.event_id = in_event_id  
141     );
142
143    DELETE FROM american_football_action_plays
144    WHERE american_football_action_plays.american_football_event_state_id IN (
145      SELECT american_football_event_states.id  FROM american_football_event_states
146      WHERE american_football_event_states.event_id = in_event_id  
147    );
148
149    DELETE FROM american_football_event_states
150    WHERE american_football_event_states.event_id = in_event_id;
151  END IF;
152
153  /*
154    Baseball event-related clearing SQL
155  */
156
157  SELECT count(*) INTO v_count 
158  FROM baseball_event_states 
159  WHERE event_id = in_event_id; 
160
161  IF v_count > 0 THEN
162    DELETE FROM baseball_action_contact_details
163    WHERE baseball_action_contact_details.baseball_action_pitch_id IN (
164      SELECT baseball_action_pitches.id FROM baseball_action_pitches
165      JOIN baseball_action_plays ON (baseball_action_plays.id=baseball_action_pitches.baseball_action_play_id)
166      JOIN baseball_event_states ON baseball_event_states.id =  baseball_action_plays.baseball_event_state_id
167      WHERE baseball_event_states.event_id = in_event_id
168    );
169
170    DELETE FROM baseball_action_pitches
171    WHERE baseball_action_pitches.baseball_action_play_id IN (
172      SELECT baseball_action_plays.id FROM baseball_action_plays
173      JOIN baseball_event_states ON baseball_event_states.id =  baseball_action_plays.baseball_event_state_id
174      WHERE baseball_event_states.event_id = in_event_id
175    );
176
177    DELETE FROM baseball_action_plays
178    WHERE baseball_action_plays.baseball_event_state_id IN (
179      SELECT baseball_event_states.id  FROM baseball_event_states
180      WHERE baseball_event_states.event_id = in_event_id
181    );
182
183    DELETE FROM baseball_action_substitutions
184    WHERE baseball_action_substitutions.baseball_event_state_id = (
185      SELECT baseball_event_states.id  FROM baseball_event_states
186      WHERE baseball_event_states.event_id = in_event_id
187    );
188
189    DELETE FROM baseball_event_states
190    WHERE baseball_event_states.event_id = in_event_id;
191  END IF;
192
193  /*
194    Basketball event-related clearing SQL
195  */
196
197  SELECT count(*) INTO v_count 
198  FROM basketball_event_states 
199  WHERE event_id = in_event_id; 
200
201  IF v_count > 0 THEN
202    DELETE FROM basketball_event_states
203    WHERE basketball_event_states.event_id = in_event_id;
204  END IF;
205
206  /*
207    Ice Hockey event-related clearing SQL
208  */
209
210  SELECT count(*) INTO v_count 
211  FROM ice_hockey_event_states 
212  WHERE event_id = in_event_id; 
213
214  IF v_count > 0 THEN
215    DELETE FROM ice_hockey_action_participants
216    WHERE ice_hockey_action_participants.ice_hockey_action_play_id IN (
217      SELECT ice_hockey_action_plays.id FROM ice_hockey_action_plays
218      JOIN ice_hockey_event_states ON ice_hockey_event_states.id =  ice_hockey_action_plays.ice_hockey_event_state_id
219      WHERE ice_hockey_event_states.event_id = in_event_id  
220    );
221
222    DELETE FROM ice_hockey_action_plays
223    WHERE ice_hockey_action_plays.ice_hockey_event_state_id IN (
224      SELECT ice_hockey_event_states.id  FROM ice_hockey_event_states
225      WHERE ice_hockey_event_states.event_id = in_event_id  
226    );
227
228    DELETE FROM ice_hockey_event_states
229    WHERE ice_hockey_event_states.event_id = in_event_id;
230  END IF;
231
232  SELECT count(*) INTO v_count 
233  FROM tennis_event_states 
234  WHERE event_id = in_event_id; 
235
236  IF v_count > 0 THEN
237    DELETE FROM tennis_event_states
238    WHERE tennis_event_states.event_id = in_event_id;
239  END IF;
240  
241  /*
242    General event state tables
243  */
244
245  SELECT count(*) INTO v_count 
246  FROM event_states 
247  WHERE event_id = in_event_id; 
248
249  IF v_count > 0 THEN
250    DELETE FROM event_action_fouls
251    WHERE event_action_fouls.event_state_id IN (
252      SELECT event_states.id  FROM event_states
253      WHERE event_states.event_id = in_event_id  
254    );
255
256    DELETE FROM event_action_penalties
257    WHERE event_action_penalties.event_state_id IN (
258      SELECT event_states.id FROM event_states
259      WHERE event_states.event_id = in_event_id  
260    );
261
262    DELETE FROM event_action_substitutions
263    WHERE event_action_substitutions.event_state_id IN (
264      SELECT event_states.id FROM event_states
265      WHERE event_states.event_id = in_event_id  
266    );
267
268    DELETE FROM event_action_participants
269    WHERE event_action_participants.event_action_play_id IN (
270      SELECT event_action_plays.id FROM event_action_plays
271      JOIN event_states ON event_states.id =  event_action_plays.event_state_id
272      WHERE event_states.event_id = in_event_id  
273    );
274
275    DELETE FROM event_action_plays
276    WHERE event_action_plays.event_state_id IN (
277      SELECT event_states.id FROM event_states
278      WHERE event_states.event_id = in_event_id  
279    );
280
281    DELETE FROM event_states
282    WHERE event_states.event_id = in_event_id;
283  END IF;
284  
285  
286  /*
287    Common event-related clearing SQL
288  */
289
290  DELETE FROM wagering_moneylines
291  WHERE event_id = in_event_id;
292
293  DELETE FROM wagering_odds_lines
294  WHERE event_id = in_event_id;
295
296  DELETE FROM wagering_runlines
297  WHERE event_id = in_event_id;
298
299  DELETE FROM wagering_straight_spread_lines
300  WHERE event_id = in_event_id;
301
302  DELETE FROM wagering_total_score_lines
303  WHERE event_id = in_event_id;
304
305  DELETE FROM weather_conditions
306  WHERE event_id = in_event_id;
307
308  /* Delete period data using a cursor to speed things up (by 50% in test) */
309  OPEN cur_periods;
310  periods_loop: LOOP
311    FETCH cur_periods INTO v_period_id;
312
313    IF v_no_more_rows THEN
314      SET v_no_more_rows = FALSE;
315      CLOSE cur_periods;
316      LEAVE periods_loop;
317    END IF;
318    
319    DELETE FROM sub_periods
320    WHERE sub_periods.period_id = v_period_id;
321
322    DELETE FROM periods
323    WHERE id = v_period_id;
324  END LOOP periods_loop;  
325
326  DELETE FROM participants_events
327  WHERE participants_events.event_id = in_event_id;
328
329  DELETE FROM affiliations_events
330  WHERE affiliations_events.event_id = in_event_id;
331
332  DELETE FROM events_sub_seasons
333  WHERE events_sub_seasons.event_id = in_event_id;
334
335  DELETE FROM document_fixtures_events
336  WHERE document_fixtures_events.event_id = in_event_id;
337
338  DELETE FROM events_documents
339  WHERE events_documents.event_id = in_event_id;
340
341  DELETE FROM events_sub_seasons
342  WHERE events_sub_seasons.event_id = in_event_id;
343
344  DELETE FROM person_event_metadata
345  WHERE person_event_metadata.event_id = in_event_id;
346
347  DELETE FROM events
348  WHERE events.id = in_event_id;
349
350  END main;
351END;$$
352
353delimiter ;