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

ALTER TABLE person_phases ADD phase_type VARCHAR(40);
GO

ALTER TABLE locations ADD city VARCHAR(100);
GO

ALTER TABLE locations ADD state VARCHAR(100);
GO

ALTER TABLE locations ADD area VARCHAR(100);
GO

ALTER TABLE locations ADD country VARCHAR(100);
GO

ALTER TABLE events ADD series_index VARCHAR(40);
GO

ALTER TABLE person_phases ADD subphase_type VARCHAR(40);
GO

ALTER TABLE ice_hockey_action_plays ALTER COLUMN comment VARCHAR(1024);
GO

CREATE TABLE motor_racing_event_stats (
    id INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
    speed_average DECIMAL(6,3),
    speed_units VARCHAR(32),
    margin_of_victory DECIMAL(6,3),
    caution_flags INTEGER,
    caution_flags_laps INTEGER,
    lead_changes INTEGER,
    lead_changes_drivers INTEGER,
    laps_total INTEGER
);
GO

CREATE INDEX IDX_affiliations_3 ON affiliations (affiliation_key, affiliation_type, publisher_id);
GO

DELETE FROM db_info;
GO

INSERT INTO db_info (version) VALUES ('27');
GO