/* * * Script to update the sportsdb schema between version 23 and 24. */ ALTER TABLE baseball_action_pitches MODIFY comment VARCHAR(2048); ALTER TABLE ice_hockey_event_states ADD COLUMN record_type VARCHAR(40); ALTER TABLE ice_hockey_event_states ADD COLUMN power_play_team_id INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN power_play_player_advantage INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN score_team INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN score_team_opposing INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN score_team_home INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN score_team_away INTEGER; ALTER TABLE ice_hockey_event_states ADD COLUMN action_key VARCHAR(100); ALTER TABLE ice_hockey_event_states MODIFY sequence_number VARCHAR(100); ALTER TABLE ice_hockey_action_plays ADD COLUMN penalty_time_remaining VARCHAR(40); ALTER TABLE ice_hockey_action_plays ADD COLUMN location VARCHAR(40); ALTER TABLE ice_hockey_action_plays ADD COLUMN zone VARCHAR(40); ALTER TABLE ice_hockey_action_participants ADD COLUMN team_id INTEGER; ALTER TABLE ice_hockey_action_participants ADD COLUMN goals_cumulative INTEGER; ALTER TABLE ice_hockey_action_participants ADD COLUMN assists_cumulative INTEGER; ALTER TABLE penalty_stats ADD COLUMN value INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN goals INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots_missed INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots_blocked INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots_power_play INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots_short_handed INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN shots_even_strength INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN player_count INTEGER; ALTER TABLE ice_hockey_offensive_stats ADD COLUMN player_count_opposing INTEGER; ALTER TABLE ice_hockey_defensive_stats ADD COLUMN player_count INTEGER; ALTER TABLE ice_hockey_defensive_stats ADD COLUMN player_count_opposing INTEGER; ALTER TABLE ice_hockey_defensive_stats ADD COLUMN goaltender_losses_overtime INTEGER; CREATE TABLE ice_hockey_faceoff_stats ( id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, player_count INTEGER, player_count_opposing INTEGER, faceoff_wins INTEGER, faceoff_losses INTEGER, faceoff_win_percentage DECIMAL(5,2), faceoffs_power_play_wins INTEGER, faceoffs_power_play_losses INTEGER, faceoffs_power_play_win_percentage DECIMAL(5,2), faceoffs_short_handed_wins INTEGER, faceoffs_short_handed_losses INTEGER, faceoffs_short_handed_win_percentage DECIMAL(5,2), faceoffs_even_strength_wins INTEGER, faceoffs_even_strength_losses INTEGER, faceoffs_even_strength_win_percentage DECIMAL(5,2), faceoffs_offensive_zone_wins INTEGER, faceoffs_offensive_zone_losses INTEGER, faceoffs_offensive_zone_win_percentage DECIMAL(5,2), faceoffs_defensive_zone_wins INTEGER, faceoffs_defensive_zone_losses INTEGER, faceoffs_defensive_zone_win_percentage DECIMAL(5,2), faceoffs_neutral_zone_wins INTEGER, faceoffs_neutral_zone_losses INTEGER, faceoffs_neutral_zone_win_percentage DECIMAL(5,2) ); CREATE TABLE ice_hockey_time_on_ice_stats ( id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, player_count INTEGER, player_count_opposing INTEGER, shifts INTEGER, time_total VARCHAR(40), time_power_play VARCHAR(40), time_short_handed VARCHAR(40), time_even_strength VARCHAR(40), time_empty_net VARCHAR(40), time_power_play_empty_net VARCHAR(40), time_short_handed_empty_net VARCHAR(40), time_even_strength_empty_net VARCHAR(40), time_average_per_shift VARCHAR(40) ); ALTER TABLE ice_hockey_action_plays DROP COLUMN location_x; ALTER TABLE ice_hockey_action_plays DROP COLUMN location_y; ALTER TABLE ice_hockey_action_plays DROP COLUMN location_zone; ALTER TABLE ice_hockey_action_plays DROP COLUMN empty_net; ALTER TABLE ice_hockey_action_plays DROP COLUMN goal_awarded; ALTER TABLE ice_hockey_action_participants DROP COLUMN goals_to_date; ALTER TABLE ice_hockey_action_participants DROP COLUMN assists_to_date; ALTER TABLE ice_hockey_action_participants DROP COLUMN points_to_date; ALTER TABLE persons ADD COLUMN final_resting_location_id INTEGER; ALTER TABLE events ADD COLUMN start_date_time_local DATETIME; ALTER TABLE american_football_action_plays ADD COLUMN touchdown_type VARCHAR(100); ALTER TABLE american_football_action_plays ADD COLUMN team_id INTEGER; /* Drop NOT NULL requirement for two position id fields */ ALTER TABLE soccer_action_substitutions MODIFY person_original_position_id INTEGER NULL; ALTER TABLE soccer_action_substitutions MODIFY person_replacing_position_id INTEGER NULL; ALTER TABLE american_football_defensive_stats ADD COLUMN first_downs_against_total INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN first_downs_against_rushing INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN first_downs_against_passing INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN first_downs_against_penalty INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_third_down_against INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_third_down_against_attempts INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_third_down_against_percentage NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_fourth_down_against INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_fourth_down_against_attempts INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN conversions_fourth_down_against_percentage NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN two_point_conversions_against INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN two_point_conversions_against_attempts INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN offensive_plays_against_touchdown INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN offensive_plays_against_average_yards_per_game NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN rushes_against_attempts INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN rushes_against_yards INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN rushing_against_average_yards_per_game NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN rushes_against_touchdowns INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN rushes_against_average_yards_per NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN rushes_against_longest INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN receptions_against_total INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN receptions_against_yards INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN receptions_against_touchdowns INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN receptions_against_average_yards_per NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN receptions_against_longest INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_yards_net INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_yards_gross INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_attempts INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_completions INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_percentage NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_average_yards_per_game NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_average_yards_per NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_touchdowns INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_touchdowns_percentage NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_longest INTEGER; ALTER TABLE american_football_defensive_stats ADD COLUMN passes_against_rating NUMERIC(5,2); ALTER TABLE american_football_defensive_stats ADD COLUMN interceptions_percentage NUMERIC(5,2); ALTER TABLE american_football_scoring_stats ADD COLUMN safeties_against_opponent INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN punts_against_blocked INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts_1_to_19 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_made_1_to_19 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts_20_to_29 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_made_20_to_29 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts_30_to_39 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_made_30_to_39 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts_40_to_49 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_made_40_to_49 INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts_50_plus INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_made_50_plus INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN field_goals_against_attempts INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN extra_points_against_attempts INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN tackles INTEGER; ALTER TABLE american_football_special_teams_stats ADD COLUMN tackles_assists INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_own_touchdowns INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_opposing_touchdowns INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_committed_defense INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_committed_special_teams INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_committed_other INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_lost_defense INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_lost_special_teams INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_lost_other INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_forced_defense INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_defense INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_special_teams INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_other INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_yards_defense INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_yards_special_teams INTEGER; ALTER TABLE american_football_fumbles_stats ADD COLUMN fumbles_recovered_yards_other INTEGER; ALTER TABLE american_football_offensive_stats ADD COLUMN tackles INTEGER; ALTER TABLE american_football_offensive_stats ADD COLUMN tackles_assists INTEGER; ALTER TABLE events ADD COLUMN broadcast_listing VARCHAR(255); ALTER TABLE ice_hockey_event_states ADD CONSTRAINT FK_hockey_event_states_power_play_team_id_teams_id FOREIGN KEY (power_play_team_id) REFERENCES teams (id); ALTER TABLE ice_hockey_action_participants ADD CONSTRAINT FK_ice_hockey_action_participants_team_id_teams_id FOREIGN KEY (team_id) REFERENCES teams (id); ALTER TABLE american_football_action_plays ADD CONSTRAINT FK_american_football_action_plays_team_id_teams_id FOREIGN KEY (team_id) REFERENCES teams (id); ALTER TABLE persons ADD CONSTRAINT FK_persons_final_resting_location_id_locations_id FOREIGN KEY (final_resting_location_id) REFERENCES locations (id); ALTER TABLE outcome_totals ADD COLUMN losses_overtime INTEGER; DELETE FROM db_info; INSERT INTO db_info (version) VALUES ('24');