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 _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()))
