def _drop_stat_indexes(c): from nfldb.types import _play_categories, _player_categories for cat in _player_categories.values(): c.execute('DROP INDEX play_player_in_%s' % cat) for cat in _play_categories.values(): c.execute('DROP INDEX play_in_%s' % cat)
def _create_stat_indexes(c): from nfldb.types import _play_categories, _player_categories for cat in _player_categories.values(): c.execute("CREATE INDEX play_player_in_%s ON play_player (%s ASC)" % (cat, cat)) for cat in _play_categories.values(): c.execute("CREATE INDEX play_in_%s ON play (%s ASC)" % (cat, cat))
def _create_stat_indexes(c): from nfldb.types import _play_categories, _player_categories for cat in _player_categories.values(): c.execute('CREATE INDEX play_player_in_%s ON play_player (%s ASC)' % (cat, cat)) for cat in _play_categories.values(): c.execute('CREATE INDEX play_in_%s ON play (%s ASC)' % (cat, cat))
def _migrate_2(c): from nfldb.types import Enums, _play_categories, _player_categories # Create some types and common constraints. c.execute(''' CREATE DOMAIN gameid AS character varying (10) CHECK (char_length(VALUE) = 10) ''') c.execute(''' CREATE DOMAIN usmallint AS smallint CHECK (VALUE >= 0) ''') c.execute(''' CREATE DOMAIN game_clock AS smallint CHECK (VALUE >= 0 AND VALUE <= 900) ''') c.execute(''' CREATE DOMAIN field_offset AS smallint CHECK (VALUE >= -50 AND VALUE <= 50) ''') c.execute(''' CREATE TYPE game_phase AS ENUM %s ''' % _mogrify(c, Enums.game_phase)) c.execute(''' CREATE TYPE season_phase AS ENUM %s ''' % _mogrify(c, Enums.season_phase)) c.execute(''' CREATE TYPE game_day AS ENUM %s ''' % _mogrify(c, Enums.game_day)) c.execute(''' CREATE TYPE player_pos AS ENUM %s ''' % _mogrify(c, Enums.player_pos)) c.execute(''' CREATE TYPE player_status AS ENUM %s ''' % _mogrify(c, Enums.player_status)) c.execute(''' CREATE TYPE game_time AS ( phase game_phase, elapsed game_clock ) ''') c.execute(''' CREATE TYPE pos_period AS ( elapsed usmallint ) ''') c.execute(''' CREATE TYPE field_pos AS ( pos field_offset ) ''') # Now that some types have been made, add current state to meta table. c.execute(''' ALTER TABLE meta ADD season_type season_phase NULL, ADD season_year usmallint NULL CHECK (season_year >= 1960 AND season_year <= 2100), ADD week usmallint NULL CHECK (week >= 1 AND week <= 25) ''') # Create the team table and populate it. c.execute(''' CREATE TABLE team ( team_id character varying (3) NOT NULL, city character varying (50) NOT NULL, name character varying (50) NOT NULL, PRIMARY KEY (team_id) ) ''') c.execute(''' INSERT INTO team (team_id, city, name) VALUES %s ''' % (', '.join(_mogrify(c, team[0:3]) for team in nfldb.team.teams1))) c.execute(''' CREATE TABLE player ( player_id character varying (10) NOT NULL CHECK (char_length(player_id) = 10), gsis_name character varying (75) NULL, full_name character varying (100) NULL, first_name character varying (100) NULL, last_name character varying (100) NULL, team character varying (3) NOT NULL, position player_pos NOT NULL, profile_id integer NULL, profile_url character varying (255) NULL, uniform_number usmallint NULL, birthdate character varying (75) NULL, college character varying (255) NULL, height character varying (100) NULL, weight character varying (100) NULL, years_pro usmallint NULL, status player_status NOT NULL, PRIMARY KEY (player_id), FOREIGN KEY (team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') c.execute(''' CREATE TABLE game ( gsis_id gameid NOT NULL, gamekey character varying (5) NULL, start_time utctime NOT NULL, week usmallint NOT NULL CHECK (week >= 1 AND week <= 25), day_of_week game_day NOT NULL, season_year usmallint NOT NULL CHECK (season_year >= 1960 AND season_year <= 2100), season_type season_phase NOT NULL, finished boolean NOT NULL, home_team character varying (3) NOT NULL, home_score usmallint NOT NULL, home_score_q1 usmallint NULL, home_score_q2 usmallint NULL, home_score_q3 usmallint NULL, home_score_q4 usmallint NULL, home_score_q5 usmallint NULL, home_turnovers usmallint NOT NULL, away_team character varying (3) NOT NULL, away_score usmallint NOT NULL, away_score_q1 usmallint NULL, away_score_q2 usmallint NULL, away_score_q3 usmallint NULL, away_score_q4 usmallint NULL, away_score_q5 usmallint NULL, away_turnovers usmallint NOT NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, PRIMARY KEY (gsis_id), FOREIGN KEY (home_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (away_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') c.execute(''' CREATE TABLE drive ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, start_field field_pos NULL, start_time game_time NOT NULL, end_field field_pos NULL, end_time game_time NOT NULL, pos_team character varying (3) NOT NULL, pos_time pos_period NULL, first_downs usmallint NOT NULL, result text NULL, penalty_yards smallint NOT NULL, yards_gained smallint NOT NULL, play_count usmallint NOT NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, PRIMARY KEY (gsis_id, drive_id), FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (pos_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') # I've taken the approach of using a sparse table to represent # sparse play statistic data. See issue #2: # https://github.com/BurntSushi/nfldb/issues/2 c.execute(''' CREATE TABLE play ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, play_id usmallint NOT NULL, time game_time NOT NULL, pos_team character varying (3) NOT NULL, yardline field_pos NULL, down smallint NULL CHECK (down >= 1 AND down <= 4), yards_to_go smallint NULL CHECK (yards_to_go >= 0 AND yards_to_go <= 100), description text NULL, note text NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, %s, PRIMARY KEY (gsis_id, drive_id, play_id), FOREIGN KEY (gsis_id, drive_id) REFERENCES drive (gsis_id, drive_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (pos_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''' % ', '.join([cat._sql_field for cat in _play_categories.values()])) c.execute(''' CREATE TABLE play_player ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, play_id usmallint NOT NULL, player_id character varying (10) NOT NULL, team character varying (3) NOT NULL, %s, PRIMARY KEY (gsis_id, drive_id, play_id, player_id), FOREIGN KEY (gsis_id, drive_id, play_id) REFERENCES play (gsis_id, drive_id, play_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id, drive_id) REFERENCES drive (gsis_id, drive_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (player_id) REFERENCES player (player_id) ON DELETE RESTRICT, FOREIGN KEY (team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''' % ', '.join(cat._sql_field for cat in _player_categories.values()))
def _migrate_2(c): from nfldb.types import Enums, _play_categories, _player_categories # Create some types and common constraints. c.execute(''' CREATE DOMAIN gameid AS character varying (10) CHECK (char_length(VALUE) = 10) ''') c.execute(''' CREATE DOMAIN usmallint AS smallint CHECK (VALUE >= 0) ''') c.execute(''' CREATE DOMAIN game_clock AS smallint CHECK (VALUE >= 0 AND VALUE <= 900) ''') c.execute(''' CREATE DOMAIN field_offset AS smallint CHECK (VALUE >= -50 AND VALUE <= 50) ''') c.execute(''' CREATE TYPE game_phase AS ENUM %s ''' % _mogrify(c, Enums.game_phase)) c.execute(''' CREATE TYPE season_phase AS ENUM %s ''' % _mogrify(c, Enums.season_phase)) c.execute(''' CREATE TYPE game_day AS ENUM %s ''' % _mogrify(c, Enums.game_day)) c.execute(''' CREATE TYPE player_pos AS ENUM %s ''' % _mogrify(c, Enums.player_pos)) c.execute(''' CREATE TYPE player_status AS ENUM %s ''' % _mogrify(c, Enums.player_status)) c.execute(''' CREATE TYPE game_time AS ( phase game_phase, elapsed game_clock ) ''') c.execute(''' CREATE TYPE pos_period AS ( elapsed usmallint ) ''') c.execute(''' CREATE TYPE field_pos AS ( pos field_offset ) ''') # Now that some types have been made, add current state to meta table. c.execute(''' ALTER TABLE meta ADD season_type season_phase NULL, ADD season_year usmallint NULL CHECK (season_year >= 1960 AND season_year <= 2100), ADD week usmallint NULL CHECK (week >= 1 AND week <= 25) ''') # Create the team table and populate it. c.execute(''' CREATE TABLE team ( team_id character varying (3) NOT NULL, city character varying (50) NOT NULL, name character varying (50) NOT NULL, PRIMARY KEY (team_id) ) ''') c.execute(''' INSERT INTO team (team_id, city, name) VALUES %s ''' % (', '.join(_mogrify(c, team[0:3]) for team in nfldb.team.teams))) c.execute(''' CREATE TABLE player ( player_id character varying (10) NOT NULL CHECK (char_length(player_id) = 10), gsis_name character varying (75) NULL, full_name character varying (100) NULL, first_name character varying (100) NULL, last_name character varying (100) NULL, team character varying (3) NOT NULL, position player_pos NOT NULL, profile_id integer NULL, profile_url character varying (255) NULL, uniform_number usmallint NULL, birthdate character varying (75) NULL, college character varying (255) NULL, height character varying (100) NULL, weight character varying (100) NULL, years_pro usmallint NULL, status player_status NOT NULL, PRIMARY KEY (player_id), FOREIGN KEY (team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') c.execute(''' CREATE TABLE game ( gsis_id gameid NOT NULL, gamekey character varying (5) NULL, start_time utctime NOT NULL, week usmallint NOT NULL CHECK (week >= 1 AND week <= 25), day_of_week game_day NOT NULL, season_year usmallint NOT NULL CHECK (season_year >= 1960 AND season_year <= 2100), season_type season_phase NOT NULL, finished boolean NOT NULL, home_team character varying (3) NOT NULL, home_score usmallint NOT NULL, home_score_q1 usmallint NULL, home_score_q2 usmallint NULL, home_score_q3 usmallint NULL, home_score_q4 usmallint NULL, home_score_q5 usmallint NULL, home_turnovers usmallint NOT NULL, away_team character varying (3) NOT NULL, away_score usmallint NOT NULL, away_score_q1 usmallint NULL, away_score_q2 usmallint NULL, away_score_q3 usmallint NULL, away_score_q4 usmallint NULL, away_score_q5 usmallint NULL, away_turnovers usmallint NOT NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, PRIMARY KEY (gsis_id), FOREIGN KEY (home_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (away_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') c.execute(''' CREATE TABLE drive ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, start_field field_pos NULL, start_time game_time NOT NULL, end_field field_pos NULL, end_time game_time NOT NULL, pos_team character varying (3) NOT NULL, pos_time pos_period NULL, first_downs usmallint NOT NULL, result text NULL, penalty_yards smallint NOT NULL, yards_gained smallint NOT NULL, play_count usmallint NOT NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, PRIMARY KEY (gsis_id, drive_id), FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (pos_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''') # I've taken the approach of using a sparse table to represent # sparse play statistic data. See issue #2: # https://github.com/BurntSushi/nfldb/issues/2 c.execute(''' CREATE TABLE play ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, play_id usmallint NOT NULL, time game_time NOT NULL, pos_team character varying (3) NOT NULL, yardline field_pos NULL, down smallint NULL CHECK (down >= 1 AND down <= 4), yards_to_go smallint NULL CHECK (yards_to_go >= 0 AND yards_to_go <= 100), description text NULL, note text NULL, time_inserted utctime NOT NULL, time_updated utctime NOT NULL, %s, PRIMARY KEY (gsis_id, drive_id, play_id), FOREIGN KEY (gsis_id, drive_id) REFERENCES drive (gsis_id, drive_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (pos_team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''' % ', '.join([cat._sql_field for cat in _play_categories.values()])) c.execute(''' CREATE TABLE play_player ( gsis_id gameid NOT NULL, drive_id usmallint NOT NULL, play_id usmallint NOT NULL, player_id character varying (10) NOT NULL, team character varying (3) NOT NULL, %s, PRIMARY KEY (gsis_id, drive_id, play_id, player_id), FOREIGN KEY (gsis_id, drive_id, play_id) REFERENCES play (gsis_id, drive_id, play_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id, drive_id) REFERENCES drive (gsis_id, drive_id) ON DELETE CASCADE, FOREIGN KEY (gsis_id) REFERENCES game (gsis_id) ON DELETE CASCADE, FOREIGN KEY (player_id) REFERENCES player (player_id) ON DELETE RESTRICT, FOREIGN KEY (team) REFERENCES team (team_id) ON DELETE RESTRICT ON UPDATE CASCADE ) ''' % ', '.join(cat._sql_field for cat in _player_categories.values()))