Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Query Imported Without Players/Teams #75

Closed
JessePresnell opened this issue Dec 27, 2014 · 7 comments
Closed

Query Imported Without Players/Teams #75

JessePresnell opened this issue Dec 27, 2014 · 7 comments

Comments

@JessePresnell
Copy link

I successfully imported the SQL text dump into a remote postgres database. The schema was successfully setup and the data was imported for all tables except players, meta, and team. Those tables are entirely empty.

The logfile from the import:

********* QUERY **********
SET TIME ZONE 'UTC';
**************************

SET
********* QUERY **********
SET statement_timeout = 0;
**************************

SET
********* QUERY **********
SET lock_timeout = 0;
**************************

********* QUERY **********
SET client_encoding = 'UTF8';
**************************


SET
********* QUERY **********
SET standard_conforming_strings = on;
**************************

SET
********* QUERY **********
SET check_function_bodies = false;
**************************

SET
********* QUERY **********
SET client_min_messages = warning;
**************************

SET
********* QUERY **********
SET search_path = public, pg_catalog;
**************************

SET
********* QUERY **********
CREATE DOMAIN field_offset AS smallint
    CONSTRAINT field_offset_check CHECK (((VALUE >= (-50)) AND (VALUE <= 50)));
**************************

CREATE DOMAIN
********* QUERY **********
CREATE TYPE field_pos AS (
    pos field_offset
);
**************************

CREATE TYPE
********* QUERY **********
CREATE DOMAIN game_clock AS smallint
    CONSTRAINT game_clock_check CHECK (((VALUE >= 0) AND (VALUE <= 900)));
**************************

CREATE DOMAIN
********* QUERY **********
CREATE TYPE game_day AS ENUM (
    'Sunday',
    'Monday',
    'Tuesday',
    'Wednesday',
    'Thursday',
    'Friday',
    'Saturday'
);
**************************

CREATE TYPE
********* QUERY **********
CREATE TYPE game_phase AS ENUM (
    'Pregame',
    'Q1',
    'Q2',
    'Half',
    'Q3',
    'Q4',
    'OT',
    'OT2',
    'Final'
);
**************************

CREATE TYPE
********* QUERY **********
CREATE TYPE game_time AS (
    phase game_phase,
    elapsed game_clock
);
**************************

CREATE TYPE
********* QUERY **********
CREATE DOMAIN gameid AS character varying(10)
    CONSTRAINT gameid_check CHECK ((char_length((VALUE)::text) = 10));
**************************

CREATE DOMAIN
********* QUERY **********
CREATE TYPE player_pos AS ENUM (
    'C',
    'CB',
    'DB',
    'DE',
    'DL',
    'DT',
    'FB',
    'FS',
    'G',
    'ILB',
    'K',
    'LB',
    'LS',
    'MLB',
    'NT',
    'OG',
    'OL',
    'OLB',
    'OT',
    'P',
    'QB',
    'RB',
    'SAF',
    'SS',
    'T',
    'TE',
    'WR',
    'UNK'
);
**************************

CREATE TYPE
********* QUERY **********
CREATE TYPE player_status AS ENUM (
    'Active',
    'InjuredReserve',
    'NonFootballInjury',
    'Suspended',
    'PUP',
    'UnsignedDraftPick',
    'Exempt',
    'Unknown'
);
**************************

CREATE TYPE
********* QUERY **********
CREATE DOMAIN usmallint AS smallint
    CONSTRAINT usmallint_check CHECK ((VALUE >= 0));
**************************

CREATE DOMAIN
********* QUERY **********
CREATE TYPE pos_period AS (
    elapsed usmallint
);
**************************

CREATE TYPE
********* QUERY **********
CREATE TYPE season_phase AS ENUM (
    'Preseason',
    'Regular',
    'Postseason'
);
**************************

CREATE TYPE
********* QUERY **********
CREATE DOMAIN utctime AS timestamp with time zone
    CONSTRAINT utctime_check CHECK ((date_part('timezone'::text, VALUE) = (0)::double precision));
**************************

CREATE DOMAIN
********* QUERY **********
CREATE FUNCTION agg_play_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                INSERT INTO
                    agg_play (gsis_id, drive_id, play_id)
                    VALUES   (NEW.gsis_id, NEW.drive_id, NEW.play_id);
                RETURN NULL;
            END;
        $$;
**************************

********* QUERY **********
CREATE FUNCTION agg_play_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                UPDATE agg_play SET defense_ast = s.defense_ast, defense_ffum = s.defense_ffum, defense_fgblk = s.defense_fgblk, defense_frec = s.defense_frec, defense_frec_tds = s.defense_frec_tds, defense_frec_yds = s.defense_frec_yds, defense_int = s.defense_int, defense_int_tds = s.defense_int_tds, defense_int_yds = s.defense_int_yds, defense_misc_tds = s.defense_misc_tds, defense_misc_yds = s.defense_misc_yds, defense_pass_def = s.defense_pass_def, defense_puntblk = s.defense_puntblk, defense_qbhit = s.defense_qbhit, defense_safe = s.defense_safe, defense_sk = s.defense_sk, defense_sk_yds = s.defense_sk_yds, defense_tkl = s.defense_tkl, defense_tkl_loss = s.defense_tkl_loss, defense_tkl_loss_yds = s.defense_tkl_loss_yds, defense_tkl_primary = s.defense_tkl_primary, defense_xpblk = s.defense_xpblk, fumbles_forced = s.fumbles_forced, fumbles_lost = s.fumbles_lost, fumbles_notforced = s.fumbles_notforced, fumbles_oob = s.fumbles_oob, fumbles_rec = s.fumbles_rec, fumbles_rec_tds = s.fumbles_rec_tds, fumbles_rec_yds = s.fumbles_rec_yds, fumbles_tot = s.fumbles_tot, kicking_all_yds = s.kicking_all_yds, kicking_downed = s.kicking_downed, kicking_fga = s.kicking_fga, kicking_fgb = s.kicking_fgb, kicking_fgm = s.kicking_fgm, kicking_fgm_yds = s.kicking_fgm_yds, kicking_fgmissed = s.kicking_fgmissed, kicking_fgmissed_yds = s.kicking_fgmissed_yds, kicking_i20 = s.kicking_i20, kicking_rec = s.kicking_rec, kicking_rec_tds = s.kicking_rec_tds, kicking_tot = s.kicking_tot, kicking_touchback = s.kicking_touchback, kicking_xpa = s.kicking_xpa, kicking_xpb = s.kicking_xpb, kicking_xpmade = s.kicking_xpmade, kicking_xpmissed = s.kicking_xpmissed, kicking_yds = s.kicking_yds, kickret_fair = s.kickret_fair, kickret_oob = s.kickret_oob, kickret_ret = s.kickret_ret, kickret_tds = s.kickret_tds, kickret_touchback = s.kickret_touchback, kickret_yds = s.kickret_yds, passing_att = s.passing_att, passing_cmp = s.passing_cmp, passing_cmp_air_yds = s.passing_cmp_air_yds, passing_incmp = s.passing_incmp, passing_incmp_air_yds = s.passing_incmp_air_yds, passing_int = s.passing_int, passing_sk = s.passing_sk, passing_sk_yds = s.passing_sk_yds, passing_tds = s.passing_tds, passing_twopta = s.passing_twopta, passing_twoptm = s.passing_twoptm, passing_twoptmissed = s.passing_twoptmissed, passing_yds = s.passing_yds, punting_blk = s.punting_blk, punting_i20 = s.punting_i20, punting_tot = s.punting_tot, punting_touchback = s.punting_touchback, punting_yds = s.punting_yds, puntret_downed = s.puntret_downed, puntret_fair = s.puntret_fair, puntret_oob = s.puntret_oob, puntret_tds = s.puntret_tds, puntret_tot = s.puntret_tot, puntret_touchback = s.puntret_touchback, puntret_yds = s.puntret_yds, receiving_rec = s.receiving_rec, receiving_tar = s.receiving_tar, receiving_tds = s.receiving_tds, receiving_twopta = s.receiving_twopta, receiving_twoptm = s.receiving_twoptm, receiving_twoptmissed = s.receiving_twoptmissed, receiving_yac_yds = s.receiving_yac_yds, receiving_yds = s.receiving_yds, rushing_att = s.rushing_att, rushing_loss = s.rushing_loss, rushing_loss_yds = s.rushing_loss_yds, rushing_tds = s.rushing_tds, rushing_twopta = s.rushing_twopta, rushing_twoptm = s.rushing_twoptm, rushing_twoptmissed = s.rushing_twoptmissed, rushing_yds = s.rushing_yds
                FROM (
                    SELECT COALESCE(SUM(play_player.defense_ast), 0) AS defense_ast, COALESCE(SUM(play_player.defense_ffum), 0) AS defense_ffum, COALESCE(SUM(play_player.defense_fgblk), 0) AS defense_fgblk, COALESCE(SUM(play_player.defense_frec), 0) AS defense_frec, COALESCE(SUM(play_player.defense_frec_tds), 0) AS defense_frec_tds, COALESCE(SUM(play_player.defense_frec_yds), 0) AS defense_frec_yds, COALESCE(SUM(play_player.defense_int), 0) AS defense_int, COALESCE(SUM(play_player.defense_int_tds), 0) AS defense_int_tds, COALESCE(SUM(play_player.defense_int_yds), 0) AS defense_int_yds, COALESCE(SUM(play_player.defense_misc_tds), 0) AS defense_misc_tds, COALESCE(SUM(play_player.defense_misc_yds), 0) AS defense_misc_yds, COALESCE(SUM(play_player.defense_pass_def), 0) AS defense_pass_def, COALESCE(SUM(play_player.defense_puntblk), 0) AS defense_puntblk, COALESCE(SUM(play_player.defense_qbhit), 0) AS defense_qbhit, COALESCE(SUM(play_player.defense_safe), 0) AS defense_safe, COALESCE(SUM(play_player.defense_sk), 0) AS defense_sk, COALESCE(SUM(play_player.defense_sk_yds), 0) AS defense_sk_yds, COALESCE(SUM(play_player.defense_tkl), 0) AS defense_tkl, COALESCE(SUM(play_player.defense_tkl_loss), 0) AS defense_tkl_loss, COALESCE(SUM(play_player.defense_tkl_loss_yds), 0) AS defense_tkl_loss_yds, COALESCE(SUM(play_player.defense_tkl_primary), 0) AS defense_tkl_primary, COALESCE(SUM(play_player.defense_xpblk), 0) AS defense_xpblk, COALESCE(SUM(play_player.fumbles_forced), 0) AS fumbles_forced, COALESCE(SUM(play_player.fumbles_lost), 0) AS fumbles_lost, COALESCE(SUM(play_player.fumbles_notforced), 0) AS fumbles_notforced, COALESCE(SUM(play_player.fumbles_oob), 0) AS fumbles_oob, COALESCE(SUM(play_player.fumbles_rec), 0) AS fumbles_rec, COALESCE(SUM(play_player.fumbles_rec_tds), 0) AS fumbles_rec_tds, COALESCE(SUM(play_player.fumbles_rec_yds), 0) AS fumbles_rec_yds, COALESCE(SUM(play_player.fumbles_tot), 0) AS fumbles_tot, COALESCE(SUM(play_player.kicking_all_yds), 0) AS kicking_all_yds, COALESCE(SUM(play_player.kicking_downed), 0) AS kicking_downed, COALESCE(SUM(play_player.kicking_fga), 0) AS kicking_fga, COALESCE(SUM(play_player.kicking_fgb), 0) AS kicking_fgb, COALESCE(SUM(play_player.kicking_fgm), 0) AS kicking_fgm, COALESCE(SUM(play_player.kicking_fgm_yds), 0) AS kicking_fgm_yds, COALESCE(SUM(play_player.kicking_fgmissed), 0) AS kicking_fgmissed, COALESCE(SUM(play_player.kicking_fgmissed_yds), 0) AS kicking_fgmissed_yds, COALESCE(SUM(play_player.kicking_i20), 0) AS kicking_i20, COALESCE(SUM(play_player.kicking_rec), 0) AS kicking_rec, COALESCE(SUM(play_player.kicking_rec_tds), 0) AS kicking_rec_tds, COALESCE(SUM(play_player.kicking_tot), 0) AS kicking_tot, COALESCE(SUM(play_player.kicking_touchback), 0) AS kicking_touchback, COALESCE(SUM(play_player.kicking_xpa), 0) AS kicking_xpa, COALESCE(SUM(play_player.kicking_xpb), 0) AS kicking_xpb, COALESCE(SUM(play_player.kicking_xpmade), 0) AS kicking_xpmade, COALESCE(SUM(play_player.kicking_xpmissed), 0) AS kicking_xpmissed, COALESCE(SUM(play_player.kicking_yds), 0) AS kicking_yds, COALESCE(SUM(play_player.kickret_fair), 0) AS kickret_fair, COALESCE(SUM(play_player.kickret_oob), 0) AS kickret_oob, COALESCE(SUM(play_player.kickret_ret), 0) AS kickret_ret, COALESCE(SUM(play_player.kickret_tds), 0) AS kickret_tds, COALESCE(SUM(play_player.kickret_touchback), 0) AS kickret_touchback, COALESCE(SUM(play_player.kickret_yds), 0) AS kickret_yds, COALESCE(SUM(play_player.passing_att), 0) AS passing_att, COALESCE(SUM(play_player.passing_cmp), 0) AS passing_cmp, COALESCE(SUM(play_player.passing_cmp_air_yds), 0) AS passing_cmp_air_yds, COALESCE(SUM(play_player.passing_incmp), 0) AS passing_incmp, COALESCE(SUM(play_player.passing_incmp_air_yds), 0) AS passing_incmp_air_yds, COALESCE(SUM(play_player.passing_int), 0) AS passing_int, COALESCE(SUM(play_player.passing_sk), 0) AS passing_sk, COALESCE(SUM(play_player.passing_sk_yds), 0) AS passing_sk_yds, COALESCE(SUM(play_player.passing_tds), 0) AS passing_tds, COALESCE(SUM(play_player.passing_twopta), 0) AS passing_twopta, COALESCE(SUM(play_player.passing_twoptm), 0) AS passing_twoptm, COALESCE(SUM(play_player.passing_twoptmissed), 0) AS passing_twoptmissed, COALESCE(SUM(play_player.passing_yds), 0) AS passing_yds, COALESCE(SUM(play_player.punting_blk), 0) AS punting_blk, COALESCE(SUM(play_player.punting_i20), 0) AS punting_i20, COALESCE(SUM(play_player.punting_tot), 0) AS punting_tot, COALESCE(SUM(play_player.punting_touchback), 0) AS punting_touchback, COALESCE(SUM(play_player.punting_yds), 0) AS punting_yds, COALESCE(SUM(play_player.puntret_downed), 0) AS puntret_downed, COALESCE(SUM(play_player.puntret_fair), 0) AS puntret_fair, COALESCE(SUM(play_player.puntret_oob), 0) AS puntret_oob, COALESCE(SUM(play_player.puntret_tds), 0) AS puntret_tds, COALESCE(SUM(play_player.puntret_tot), 0) AS puntret_tot, COALESCE(SUM(play_player.puntret_touchback), 0) AS puntret_touchback, COALESCE(SUM(play_player.puntret_yds), 0) AS puntret_yds, COALESCE(SUM(play_player.receiving_rec), 0) AS receiving_rec, COALESCE(SUM(play_player.receiving_tar), 0) AS receiving_tar, COALESCE(SUM(play_player.receiving_tds), 0) AS receiving_tds, COALESCE(SUM(play_player.receiving_twopta), 0) AS receiving_twopta, COALESCE(SUM(play_player.receiving_twoptm), 0) AS receiving_twoptm, COALESCE(SUM(play_player.receiving_twoptmissed), 0) AS receiving_twoptmissed, COALESCE(SUM(play_player.receiving_yac_yds), 0) AS receiving_yac_yds, COALESCE(SUM(play_player.receiving_yds), 0) AS receiving_yds, COALESCE(SUM(play_player.rushing_att), 0) AS rushing_att, COALESCE(SUM(play_player.rushing_loss), 0) AS rushing_loss, COALESCE(SUM(play_player.rushing_loss_yds), 0) AS rushing_loss_yds, COALESCE(SUM(play_player.rushing_tds), 0) AS rushing_tds, COALESCE(SUM(play_player.rushing_twopta), 0) AS rushing_twopta, COALESCE(SUM(play_player.rushing_twoptm), 0) AS rushing_twoptm, COALESCE(SUM(play_player.rushing_twoptmissed), 0) AS rushing_twoptmissed, COALESCE(SUM(play_player.rushing_yds), 0) AS rushing_yds
                    FROM play
                    LEFT JOIN play_player
                    ON (play.gsis_id, play.drive_id, play.play_id)
                       = (play_player.gsis_id, play_player.drive_id,
                          play_player.play_id)
                    WHERE (play.gsis_id, play.drive_id, play.play_id)
                          = (NEW.gsis_id, NEW.drive_id, NEW.play_id)
                ) s
                WHERE (agg_play.gsis_id, agg_play.drive_id, agg_play.play_id)
                      = (NEW.gsis_id, NEW.drive_id, NEW.play_id);
                RETURN NULL;
            END;
        $$;
**************************

********* QUERY **********
SET default_tablespace = '';
**************************

SET
********* QUERY **********
SET default_with_oids = false;
**************************

SET
********* QUERY **********
CREATE TABLE agg_play (
    gsis_id gameid NOT NULL,
    drive_id usmallint NOT NULL,
    play_id usmallint NOT NULL,
    defense_ast smallint DEFAULT 0 NOT NULL,
    defense_ffum smallint DEFAULT 0 NOT NULL,
    defense_fgblk smallint DEFAULT 0 NOT NULL,
    defense_frec smallint DEFAULT 0 NOT NULL,
    defense_frec_tds smallint DEFAULT 0 NOT NULL,
    defense_frec_yds smallint DEFAULT 0 NOT NULL,
    defense_int smallint DEFAULT 0 NOT NULL,
    defense_int_tds smallint DEFAULT 0 NOT NULL,
    defense_int_yds smallint DEFAULT 0 NOT NULL,
    defense_misc_tds smallint DEFAULT 0 NOT NULL,
    defense_misc_yds smallint DEFAULT 0 NOT NULL,
    defense_pass_def smallint DEFAULT 0 NOT NULL,
    defense_puntblk smallint DEFAULT 0 NOT NULL,
    defense_qbhit smallint DEFAULT 0 NOT NULL,
    defense_safe smallint DEFAULT 0 NOT NULL,
    defense_sk real DEFAULT 0.0 NOT NULL,
    defense_sk_yds smallint DEFAULT 0 NOT NULL,
    defense_tkl smallint DEFAULT 0 NOT NULL,
    defense_tkl_loss smallint DEFAULT 0 NOT NULL,
    defense_tkl_loss_yds smallint DEFAULT 0 NOT NULL,
    defense_tkl_primary smallint DEFAULT 0 NOT NULL,
    defense_xpblk smallint DEFAULT 0 NOT NULL,
    fumbles_forced smallint DEFAULT 0 NOT NULL,
    fumbles_lost smallint DEFAULT 0 NOT NULL,
    fumbles_notforced smallint DEFAULT 0 NOT NULL,
    fumbles_oob smallint DEFAULT 0 NOT NULL,
    fumbles_rec smallint DEFAULT 0 NOT NULL,
    fumbles_rec_tds smallint DEFAULT 0 NOT NULL,
    fumbles_rec_yds smallint DEFAULT 0 NOT NULL,
    fumbles_tot smallint DEFAULT 0 NOT NULL,
    kicking_all_yds smallint DEFAULT 0 NOT NULL,
    kicking_downed smallint DEFAULT 0 NOT NULL,
    kicking_fga smallint DEFAULT 0 NOT NULL,
    kicking_fgb smallint DEFAULT 0 NOT NULL,
    kicking_fgm smallint DEFAULT 0 NOT NULL,
    kicking_fgm_yds smallint DEFAULT 0 NOT NULL,
    kicking_fgmissed smallint DEFAULT 0 NOT NULL,
    kicking_fgmissed_yds smallint DEFAULT 0 NOT NULL,
    kicking_i20 smallint DEFAULT 0 NOT NULL,
    kicking_rec smallint DEFAULT 0 NOT NULL,
    kicking_rec_tds smallint DEFAULT 0 NOT NULL,
    kicking_tot smallint DEFAULT 0 NOT NULL,
    kicking_touchback smallint DEFAULT 0 NOT NULL,
    kicking_xpa smallint DEFAULT 0 NOT NULL,
    kicking_xpb smallint DEFAULT 0 NOT NULL,
    kicking_xpmade smallint DEFAULT 0 NOT NULL,
    kicking_xpmissed smallint DEFAULT 0 NOT NULL,
    kicking_yds smallint DEFAULT 0 NOT NULL,
    kickret_fair smallint DEFAULT 0 NOT NULL,
    kickret_oob smallint DEFAULT 0 NOT NULL,
    kickret_ret smallint DEFAULT 0 NOT NULL,
    kickret_tds smallint DEFAULT 0 NOT NULL,
    kickret_touchback smallint DEFAULT 0 NOT NULL,
    kickret_yds smallint DEFAULT 0 NOT NULL,
    passing_att smallint DEFAULT 0 NOT NULL,
    passing_cmp smallint DEFAULT 0 NOT NULL,
    passing_cmp_air_yds smallint DEFAULT 0 NOT NULL,
    passing_incmp smallint DEFAULT 0 NOT NULL,
    passing_incmp_air_yds smallint DEFAULT 0 NOT NULL,
    passing_int smallint DEFAULT 0 NOT NULL,
    passing_sk smallint DEFAULT 0 NOT NULL,
    passing_sk_yds smallint DEFAULT 0 NOT NULL,
    passing_tds smallint DEFAULT 0 NOT NULL,
    passing_twopta smallint DEFAULT 0 NOT NULL,
    passing_twoptm smallint DEFAULT 0 NOT NULL,
    passing_twoptmissed smallint DEFAULT 0 NOT NULL,
    passing_yds smallint DEFAULT 0 NOT NULL,
    punting_blk smallint DEFAULT 0 NOT NULL,
    punting_i20 smallint DEFAULT 0 NOT NULL,
    punting_tot smallint DEFAULT 0 NOT NULL,
    punting_touchback smallint DEFAULT 0 NOT NULL,
    punting_yds smallint DEFAULT 0 NOT NULL,
    puntret_downed smallint DEFAULT 0 NOT NULL,
    puntret_fair smallint DEFAULT 0 NOT NULL,
    puntret_oob smallint DEFAULT 0 NOT NULL,
    puntret_tds smallint DEFAULT 0 NOT NULL,
    puntret_tot smallint DEFAULT 0 NOT NULL,
    puntret_touchback smallint DEFAULT 0 NOT NULL,
    puntret_yds smallint DEFAULT 0 NOT NULL,
    receiving_rec smallint DEFAULT 0 NOT NULL,
    receiving_tar smallint DEFAULT 0 NOT NULL,
    receiving_tds smallint DEFAULT 0 NOT NULL,
    receiving_twopta smallint DEFAULT 0 NOT NULL,
    receiving_twoptm smallint DEFAULT 0 NOT NULL,
    receiving_twoptmissed smallint DEFAULT 0 NOT NULL,
    receiving_yac_yds smallint DEFAULT 0 NOT NULL,
    receiving_yds smallint DEFAULT 0 NOT NULL,
    rushing_att smallint DEFAULT 0 NOT NULL,
    rushing_loss smallint DEFAULT 0 NOT NULL,
    rushing_loss_yds smallint DEFAULT 0 NOT NULL,
    rushing_tds smallint DEFAULT 0 NOT NULL,
    rushing_twopta smallint DEFAULT 0 NOT NULL,
    rushing_twoptm smallint DEFAULT 0 NOT NULL,
    rushing_twoptmissed smallint DEFAULT 0 NOT NULL,
    rushing_yds smallint DEFAULT 0 NOT NULL
);
**************************

CREATE TABLE
********* QUERY **********
CREATE TABLE drive (
    gsis_id gameid NOT NULL,
    drive_id usmallint NOT NULL,
    start_field field_pos,
    start_time game_time NOT NULL,
    end_field field_pos,
    end_time game_time NOT NULL,
    pos_team character varying(3) NOT NULL,
    pos_time pos_period,
    first_downs usmallint NOT NULL,
    result text,
    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
);
**************************

CREATE TABLE
********* QUERY **********
CREATE TABLE game (
    gsis_id gameid NOT NULL,
    gamekey character varying(5),
    start_time utctime NOT NULL,
    week usmallint NOT NULL,
    day_of_week game_day NOT NULL,
    season_year usmallint NOT NULL,
    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,
    home_score_q2 usmallint,
    home_score_q3 usmallint,
    home_score_q4 usmallint,
    home_score_q5 usmallint,
    home_turnovers usmallint NOT NULL,
    away_team character varying(3) NOT NULL,
    away_score usmallint NOT NULL,
    away_score_q1 usmallint,
    away_score_q2 usmallint,
    away_score_q3 usmallint,
    away_score_q4 usmallint,
    away_score_q5 usmallint,
    away_turnovers usmallint NOT NULL,
    time_inserted utctime NOT NULL,
    time_updated utctime NOT NULL,
    CONSTRAINT game_season_year_check CHECK ((((season_year)::smallint >= 1960) AND ((season_year)::smallint <= 2100))),
    CONSTRAINT game_week_check CHECK ((((week)::smallint >= 0) AND ((week)::smallint <= 25)))
);
**************************

CREATE TABLE
********* QUERY **********
CREATE TABLE meta (
    version smallint,
    last_roster_download utctime NOT NULL,
    season_type season_phase,
    season_year usmallint,
    week usmallint,
    CONSTRAINT meta_season_year_check CHECK ((((season_year)::smallint >= 1960) AND ((season_year)::smallint <= 2100))),
    CONSTRAINT meta_week_check CHECK ((((week)::smallint >= 0) AND ((week)::smallint <= 25)))
);
**************************

CREATE TABLE
********* QUERY **********
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,
    down smallint,
    yards_to_go smallint,
    description text,
    note text,
    time_inserted utctime NOT NULL,
    time_updated utctime NOT NULL,
    first_down smallint DEFAULT 0 NOT NULL,
    fourth_down_att smallint DEFAULT 0 NOT NULL,
    fourth_down_conv smallint DEFAULT 0 NOT NULL,
    fourth_down_failed smallint DEFAULT 0 NOT NULL,
    passing_first_down smallint DEFAULT 0 NOT NULL,
    penalty smallint DEFAULT 0 NOT NULL,
    penalty_first_down smallint DEFAULT 0 NOT NULL,
    penalty_yds smallint DEFAULT 0 NOT NULL,
    rushing_first_down smallint DEFAULT 0 NOT NULL,
    third_down_att smallint DEFAULT 0 NOT NULL,
    third_down_conv smallint DEFAULT 0 NOT NULL,
    third_down_failed smallint DEFAULT 0 NOT NULL,
    timeout smallint DEFAULT 0 NOT NULL,
    xp_aborted smallint DEFAULT 0 NOT NULL,
    CONSTRAINT play_down_check CHECK (((down >= 1) AND (down <= 4))),
    CONSTRAINT play_yards_to_go_check CHECK (((yards_to_go >= 0) AND (yards_to_go <= 100)))
);
**************************

CREATE TABLE
********* QUERY **********
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,
    defense_ast smallint DEFAULT 0 NOT NULL,
    defense_ffum smallint DEFAULT 0 NOT NULL,
    defense_fgblk smallint DEFAULT 0 NOT NULL,
    defense_frec smallint DEFAULT 0 NOT NULL,
    defense_frec_tds smallint DEFAULT 0 NOT NULL,
    defense_frec_yds smallint DEFAULT 0 NOT NULL,
    defense_int smallint DEFAULT 0 NOT NULL,
    defense_int_tds smallint DEFAULT 0 NOT NULL,
    defense_int_yds smallint DEFAULT 0 NOT NULL,
    defense_misc_tds smallint DEFAULT 0 NOT NULL,
    defense_misc_yds smallint DEFAULT 0 NOT NULL,
    defense_pass_def smallint DEFAULT 0 NOT NULL,
    defense_puntblk smallint DEFAULT 0 NOT NULL,
    defense_qbhit smallint DEFAULT 0 NOT NULL,
    defense_safe smallint DEFAULT 0 NOT NULL,
    defense_sk real DEFAULT 0.0 NOT NULL,
    defense_sk_yds smallint DEFAULT 0 NOT NULL,
    defense_tkl smallint DEFAULT 0 NOT NULL,
    defense_tkl_loss smallint DEFAULT 0 NOT NULL,
    defense_tkl_loss_yds smallint DEFAULT 0 NOT NULL,
    defense_tkl_primary smallint DEFAULT 0 NOT NULL,
    defense_xpblk smallint DEFAULT 0 NOT NULL,
    fumbles_forced smallint DEFAULT 0 NOT NULL,
    fumbles_lost smallint DEFAULT 0 NOT NULL,
    fumbles_notforced smallint DEFAULT 0 NOT NULL,
    fumbles_oob smallint DEFAULT 0 NOT NULL,
    fumbles_rec smallint DEFAULT 0 NOT NULL,
    fumbles_rec_tds smallint DEFAULT 0 NOT NULL,
    fumbles_rec_yds smallint DEFAULT 0 NOT NULL,
    fumbles_tot smallint DEFAULT 0 NOT NULL,
    kicking_all_yds smallint DEFAULT 0 NOT NULL,
    kicking_downed smallint DEFAULT 0 NOT NULL,
    kicking_fga smallint DEFAULT 0 NOT NULL,
    kicking_fgb smallint DEFAULT 0 NOT NULL,
    kicking_fgm smallint DEFAULT 0 NOT NULL,
    kicking_fgm_yds smallint DEFAULT 0 NOT NULL,
    kicking_fgmissed smallint DEFAULT 0 NOT NULL,
    kicking_fgmissed_yds smallint DEFAULT 0 NOT NULL,
    kicking_i20 smallint DEFAULT 0 NOT NULL,
    kicking_rec smallint DEFAULT 0 NOT NULL,
    kicking_rec_tds smallint DEFAULT 0 NOT NULL,
    kicking_tot smallint DEFAULT 0 NOT NULL,
    kicking_touchback smallint DEFAULT 0 NOT NULL,
    kicking_xpa smallint DEFAULT 0 NOT NULL,
    kicking_xpb smallint DEFAULT 0 NOT NULL,
    kicking_xpmade smallint DEFAULT 0 NOT NULL,
    kicking_xpmissed smallint DEFAULT 0 NOT NULL,
    kicking_yds smallint DEFAULT 0 NOT NULL,
    kickret_fair smallint DEFAULT 0 NOT NULL,
    kickret_oob smallint DEFAULT 0 NOT NULL,
    kickret_ret smallint DEFAULT 0 NOT NULL,
    kickret_tds smallint DEFAULT 0 NOT NULL,
    kickret_touchback smallint DEFAULT 0 NOT NULL,
    kickret_yds smallint DEFAULT 0 NOT NULL,
    passing_att smallint DEFAULT 0 NOT NULL,
    passing_cmp smallint DEFAULT 0 NOT NULL,
    passing_cmp_air_yds smallint DEFAULT 0 NOT NULL,
    passing_incmp smallint DEFAULT 0 NOT NULL,
    passing_incmp_air_yds smallint DEFAULT 0 NOT NULL,
    passing_int smallint DEFAULT 0 NOT NULL,
    passing_sk smallint DEFAULT 0 NOT NULL,
    passing_sk_yds smallint DEFAULT 0 NOT NULL,
    passing_tds smallint DEFAULT 0 NOT NULL,
    passing_twopta smallint DEFAULT 0 NOT NULL,
    passing_twoptm smallint DEFAULT 0 NOT NULL,
    passing_twoptmissed smallint DEFAULT 0 NOT NULL,
    passing_yds smallint DEFAULT 0 NOT NULL,
    punting_blk smallint DEFAULT 0 NOT NULL,
    punting_i20 smallint DEFAULT 0 NOT NULL,
    punting_tot smallint DEFAULT 0 NOT NULL,
    punting_touchback smallint DEFAULT 0 NOT NULL,
    punting_yds smallint DEFAULT 0 NOT NULL,
    puntret_downed smallint DEFAULT 0 NOT NULL,
    puntret_fair smallint DEFAULT 0 NOT NULL,
    puntret_oob smallint DEFAULT 0 NOT NULL,
    puntret_tds smallint DEFAULT 0 NOT NULL,
    puntret_tot smallint DEFAULT 0 NOT NULL,
    puntret_touchback smallint DEFAULT 0 NOT NULL,
    puntret_yds smallint DEFAULT 0 NOT NULL,
    receiving_rec smallint DEFAULT 0 NOT NULL,
    receiving_tar smallint DEFAULT 0 NOT NULL,
    receiving_tds smallint DEFAULT 0 NOT NULL,
    receiving_twopta smallint DEFAULT 0 NOT NULL,
    receiving_twoptm smallint DEFAULT 0 NOT NULL,
    receiving_twoptmissed smallint DEFAULT 0 NOT NULL,
    receiving_yac_yds smallint DEFAULT 0 NOT NULL,
    receiving_yds smallint DEFAULT 0 NOT NULL,
    rushing_att smallint DEFAULT 0 NOT NULL,
    rushing_loss smallint DEFAULT 0 NOT NULL,
    rushing_loss_yds smallint DEFAULT 0 NOT NULL,
    rushing_tds smallint DEFAULT 0 NOT NULL,
    rushing_twopta smallint DEFAULT 0 NOT NULL,
    rushing_twoptm smallint DEFAULT 0 NOT NULL,
    rushing_twoptmissed smallint DEFAULT 0 NOT NULL,
    rushing_yds smallint DEFAULT 0 NOT NULL
);
**************************

CREATE TABLE
********* QUERY **********
CREATE TABLE player (
    player_id character varying(10) NOT NULL,
    gsis_name character varying(75),
    full_name character varying(100),
    first_name character varying(100),
    last_name character varying(100),
    team character varying(3) NOT NULL,
    "position" player_pos NOT NULL,
    profile_id integer,
    profile_url character varying(255),
    uniform_number usmallint,
    birthdate character varying(75),
    college character varying(255),
    height usmallint,
    weight usmallint,
    years_pro usmallint,
    status player_status NOT NULL,
    CONSTRAINT player_player_id_check CHECK ((char_length((player_id)::text) = 10))
);
**************************

CREATE TABLE
********* QUERY **********
CREATE TABLE team (
    team_id character varying(3) NOT NULL,
    city character varying(50) NOT NULL,
    name character varying(50) NOT NULL
);
**************************

CREATE TABLE
********* QUERY **********
COPY agg_play (gsis_id, drive_id, play_id, defense_ast, defense_ffum, defense_fgblk, defense_frec, defense_frec_tds, defense_frec_yds, defense_int, defense_int_tds, defense_int_yds, defense_misc_tds, defense_misc_yds, defense_pass_def, defense_puntblk, defense_qbhit, defense_safe, defense_sk, defense_sk_yds, defense_tkl, defense_tkl_loss, defense_tkl_loss_yds, defense_tkl_primary, defense_xpblk, fumbles_forced, fumbles_lost, fumbles_notforced, fumbles_oob, fumbles_rec, fumbles_rec_tds, fumbles_rec_yds, fumbles_tot, kicking_all_yds, kicking_downed, kicking_fga, kicking_fgb, kicking_fgm, kicking_fgm_yds, kicking_fgmissed, kicking_fgmissed_yds, kicking_i20, kicking_rec, kicking_rec_tds, kicking_tot, kicking_touchback, kicking_xpa, kicking_xpb, kicking_xpmade, kicking_xpmissed, kicking_yds, kickret_fair, kickret_oob, kickret_ret, kickret_tds, kickret_touchback, kickret_yds, passing_att, passing_cmp, passing_cmp_air_yds, passing_incmp, passing_incmp_air_yds, passing_int, passing_sk, passing_sk_yds, passing_tds, passing_twopta, passing_twoptm, passing_twoptmissed, passing_yds, punting_blk, punting_i20, punting_tot, punting_touchback, punting_yds, puntret_downed, puntret_fair, puntret_oob, puntret_tds, puntret_tot, puntret_touchback, puntret_yds, receiving_rec, receiving_tar, receiving_tds, receiving_twopta, receiving_twoptm, receiving_twoptmissed, receiving_yac_yds, receiving_yds, rushing_att, rushing_loss, rushing_loss_yds, rushing_tds, rushing_twopta, rushing_twoptm, rushing_twoptmissed, rushing_yds) FROM stdin;
**************************

COPY 333341
********* QUERY **********
COPY drive (gsis_id, drive_id, start_field, start_time, end_field, end_time, pos_team, pos_time, first_downs, result, penalty_yards, yards_gained, play_count, time_inserted, time_updated) FROM stdin;
**************************

COPY 44875
********* QUERY **********
COPY game (gsis_id, gamekey, start_time, week, day_of_week, season_year, season_type, finished, home_team, home_score, home_score_q1, home_score_q2, home_score_q3, home_score_q4, home_score_q5, home_turnovers, away_team, away_score, away_score_q1, away_score_q2, away_score_q3, away_score_q4, away_score_q5, away_turnovers, time_inserted, time_updated) FROM stdin;
**************************

COPY 1980
********* QUERY **********
COPY meta (version, last_roster_download, season_type, season_year, week) FROM stdin;
**************************

COPY 1
********* QUERY **********
COPY play (gsis_id, drive_id, play_id, "time", pos_team, yardline, down, yards_to_go, description, note, time_inserted, time_updated, first_down, fourth_down_att, fourth_down_conv, fourth_down_failed, passing_first_down, penalty, penalty_first_down, penalty_yds, rushing_first_down, third_down_att, third_down_conv, third_down_failed, timeout, xp_aborted) FROM stdin;
**************************

COPY 333341
********* QUERY **********
COPY play_player (gsis_id, drive_id, play_id, player_id, team, defense_ast, defense_ffum, defense_fgblk, defense_frec, defense_frec_tds, defense_frec_yds, defense_int, defense_int_tds, defense_int_yds, defense_misc_tds, defense_misc_yds, defense_pass_def, defense_puntblk, defense_qbhit, defense_safe, defense_sk, defense_sk_yds, defense_tkl, defense_tkl_loss, defense_tkl_loss_yds, defense_tkl_primary, defense_xpblk, fumbles_forced, fumbles_lost, fumbles_notforced, fumbles_oob, fumbles_rec, fumbles_rec_tds, fumbles_rec_yds, fumbles_tot, kicking_all_yds, kicking_downed, kicking_fga, kicking_fgb, kicking_fgm, kicking_fgm_yds, kicking_fgmissed, kicking_fgmissed_yds, kicking_i20, kicking_rec, kicking_rec_tds, kicking_tot, kicking_touchback, kicking_xpa, kicking_xpb, kicking_xpmade, kicking_xpmissed, kicking_yds, kickret_fair, kickret_oob, kickret_ret, kickret_tds, kickret_touchback, kickret_yds, passing_att, passing_cmp, passing_cmp_air_yds, passing_incmp, passing_incmp_air_yds, passing_int, passing_sk, passing_sk_yds, passing_tds, passing_twopta, passing_twoptm, passing_twoptmissed, passing_yds, punting_blk, punting_i20, punting_tot, punting_touchback, punting_yds, puntret_downed, puntret_fair, puntret_oob, puntret_tds, puntret_tot, puntret_touchback, puntret_yds, receiving_rec, receiving_tar, receiving_tds, receiving_twopta, receiving_twoptm, receiving_twoptmissed, receiving_yac_yds, receiving_yds, rushing_att, rushing_loss, rushing_loss_yds, rushing_tds, rushing_twopta, rushing_twoptm, rushing_twoptmissed, rushing_yds) FROM stdin;
**************************

COPY 135679
@BurntSushi
Copy link
Owner

What text dump? What command did you run? What OS are you on?

Basically: could you please provide enough information so that someone else is able to reproduce the issue.

@JessePresnell
Copy link
Author

Sorry, very new to this.

Running Windows 7

Imported your nfldb.sql file into my remote database using psql -h hostname -d databasename -U username -f file.sql -L logfile.log

@BurntSushi
Copy link
Owner

Something is going wrong with your import. Look at my log file when I run the same command as you: https://gist.github.com/5c977e3f5f88524d61f2 --- There's a lot more left to do.

Are there any error messages you might have missed that were emitted on the console?

@JessePresnell
Copy link
Author

No error messages in the console but I'm wondering if it's an upload limitation I'm running into from my web host. I'll build the database locally and see if that fixes it.

@BurntSushi
Copy link
Owner

Possibly. The database is quite big:

nfldb=# select pg_size_pretty(pg_database_size('nfldb'));
 pg_size_pretty 
----------------
 9843 MB
(1 row)

My guess is that a lot of the size comes from indexes.

@JessePresnell
Copy link
Author

It was an upload limit. Works flawlessly locally (and substantially faster than a remote deployment). I can't begin to thank you for sharing your incredible work on this project. Seriously amazing work.

@BurntSushi
Copy link
Owner

@JessePresnell Glad to hear it worked for you. :-)

And yes, a remote database is going to perform very poorly unless you have a low latency connection. (Which is unlikely for a run-of-the-mill shared web host from, say, a cable modem.)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@BurntSushi @JessePresnell