/*
 *  
 * Script to update the sportsdb schema between version 26 and 27.
 */

ALTER TABLE person_phases ADD COLUMN phase_type VARCHAR(40);
ALTER TABLE person_phases ADD COLUMN subphase_type VARCHAR(40);

ALTER TABLE events ADD COLUMN series_index VARCHAR(40);

ALTER TABLE locations ADD COLUMN city VARCHAR(100);
ALTER TABLE locations ADD COLUMN state VARCHAR(100);
ALTER TABLE locations ADD COLUMN area VARCHAR(100);
ALTER TABLE locations ADD COLUMN country VARCHAR(100);

ALTER TABLE ice_hockey_action_plays MODIFY comment VARCHAR(1024);

CREATE TABLE motor_racing_event_stats (
    id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    speed_average DECIMAL(6,3),
    speed_units VARCHAR(40),
    margin_of_victory DECIMAL(6,3),
    caution_flags INTEGER,
    caution_flags_laps INTEGER,
    lead_changes INTEGER,
    lead_changes_drivers INTEGER,
    laps_total INTEGER
);

ALTER TABLE affiliations ADD INDEX IDX_affiliations_3 (affiliation_key, affiliation_type, publisher_id);

DELETE FROM db_info;
INSERT INTO db_info (version) VALUES ('27');