Graph database-ball! Exploring the Game with the graph capabilities of LadybugDB, DuckDB and PostgreSQL

sudo apt-get update -y
sudo apt-get install -y git build-essential pkg-config libicu-dev bison flex libreadline-dev liburing-dev zlib1g-dev liblz4-dev hyperfine
sudo useradd postgres
sudo mkdir /postgres

git clone https://github.com/postgres/postgres.git
cd postgres
git checkout master

./configure --prefix /postgres --with-liburing --with-lz4
make -j8
sudo make install

sudo chown -R postgres:postgres /postgres/

sudo -u postgres /postgres/bin/initdb -D /postgres/data
sudo -u postgres /postgres/bin/pg_ctl -D /postgres/data -l /postgres/logfile start
sudo -u postgres /postgres/bin/createdb graph_baseball

echo "max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5041kB
huge_pages = off
jit = off
wal_compression = lz4
io_method = io_uring
min_wal_size = 1GB
max_wal_size = 4GB" | sudo tee -a /postgres/data/postgresql.conf

sudo -u postgres /postgres/bin/pg_ctl -D /postgres/data -l /postgres/logfile restart

curl https://install.duckdb.org | DUCKDB_VERSION=1.4.4 sh
export PATH="$HOME/.duckdb/cli/1.4.4":$PATH
duckdb graph_baseball.duckdb -c "INSTALL duckpgq FROM community;"

curl -s https://install.ladybugdb.com | bash

sudo apt-get update -y && sudo apt-get install -y unzip
curl -O https://www.retrosheet.org/downloads/biodata.zip
unzip -q "biodata.zip" -d "biodata"
rm "biodata.zip"

for year in {2010..2025}; do
    url="https://www.retrosheet.org/downloads/${year}/${year}csvs.zip"
    curl -O "$url"
    if [ $? -eq 0 ]; then
        mkdir -p "${year}csvs"
        unzip -q "${year}csvs.zip" -d "${year}csvs"
        rm "${year}csvs.zip"
    else
        echo "Failed to download ${year}csvs.zip"
    fi
done

sed -i 's/\r//' $(find . -name "*.csv")

mkdir -p parsed
find . -name "*.csv" | while read -r f; do
   t="parsed/${f##*/}"
   awk -F, -v f="$f" 'BEGIN{OFS=","}
   FNR==1{delete c;for(i=1;i<=NF;i++)if(tolower($i)~/date|last_|first_|debut_/)c[i];
       if(f~/plays.csv/){for(i=1;i<=NF;i++){if($i=="gid")g=i;if($i=="pn")p=i}printf "event_id,";print;next}
       if(f~/fielding|pitching|batting|teamstats/){x=$1;$1=$2;$2=x}
       if(f~/relatives.csv/){x=$2;$2=$3;$3=x}
       if(f~/allplayers.csv/){x=$6;for(i=6;i>2;i--)$i=$(i-1);$2=x}
       if(f~/gameinfo.csv/){x=$4;for(i=4;i>2;i--)$i=$(i-1);$2=x}
       if(f~/coaches0.csv/){y=$2;tm=$3;$2=tm;$3=y}
       print;next}
   {if(f~/plays.csv/)printf "%s_%s,",$g,$p;
       if(f~/fielding|pitching|batting|teamstats/){x=$1;$1=$2;$2=x}
       if(f~/relatives.csv/){x=$2;$2=$3;$3=x}
       if(f~/allplayers.csv/){x=$6;for(i=6;i>2;i--)$i=$(i-1);$2=x}
       if(f~/gameinfo.csv/){x=$4;for(i=4;i>2;i--)$i=$(i-1);$2=x}
       if(f~/coaches0.csv/){y=$2;tm=$3;$2=tm;$3=y}
       for(i in c){
           if(length($i)<8)$i="";
           if(substr($i,5,2)=="00")$i=substr($i,1,4)"01"substr($i,7,2);
           if(substr($i,7,2)=="00")$i=substr($i,1,6)"01";
           if($i~/^(1[89]|20)[0-9]{6}$/)$i=substr($i,1,4)"-"substr($i,5,2)"-"substr($i,7,2)}print}' "$f" > "$t"
done

// Nodes
CREATE NODE TABLE IF NOT EXISTS Player(id STRING PRIMARY KEY, lastname STRING, usename STRING, fullname STRING, birthdate DATE, birthcity STRING, birthstate STRING, birthcountry STRING, deathdate DATE, deathcity STRING, deathstate STRING, deathcountry STRING, cemetery STRING, cem_city STRING, cem_state STRING, cem_ctry STRING, cem_note STRING, birthname STRING, altname STRING, debut_p DATE, last_p DATE, debut_c DATE, last_c DATE, debut_m DATE, last_m DATE, debut_u DATE, last_u DATE, bats STRING, throws STRING, height DOUBLE, weight DOUBLE, HOF STRING);

CREATE NODE TABLE IF NOT EXISTS Umpire(id STRING PRIMARY KEY, lastname STRING, firstname STRING, first_g DATE, last_g DATE);

CREATE NODE TABLE IF NOT EXISTS Team(team STRING PRIMARY KEY, city STRING, nickname STRING, first_g DATE, last_g DATE);

CREATE NODE TABLE IF NOT EXISTS Ballpark(site STRING PRIMARY KEY, name STRING, city STRING, state STRING, first_g DATE, last_g DATE);

CREATE NODE TABLE IF NOT EXISTS Game(gid STRING PRIMARY KEY, site STRING, visteam STRING, hometeam STRING, date DATE, number INT64, starttime STRING, daynight STRING, innings INT64, tiebreaker INT64, usedh BOOLEAN, htbf STRING, timeofgame INT64, attendance INT64, fieldcond STRING, precip STRING, sky STRING, temp INT64, winddir STRING, windspeed INT64, oscorer STRING, forfeit STRING, suspend INT64, umphome STRING, ump1b STRING, ump2b STRING, ump3b STRING, umplf STRING, umprf STRING, wp STRING, lp STRING, save STRING, gametype STRING, vruns INT64, hruns INT64, wteam STRING, lteam STRING, line STRING, batteries STRING, lineups STRING, box STRING, pbp STRING, season INT64);

CREATE NODE TABLE IF NOT EXISTS Event(event_id STRING PRIMARY KEY, gid STRING, event STRING, inning INT64, top_bot INT64, vis_home INT64, site STRING, batteam STRING, pitteam STRING, score_v INT64, score_h INT64, batter STRING, pitcher STRING, lp INT64, bat_f INT64, bathand STRING, pithand STRING, balls INT64, strikes INT64, `count` INT64, pitches STRING, nump INT64, pa INT64, ab INT64, `single` INT64, double INT64, triple INT64, hr INT64, sh INT64, sf INT64, hbp INT64, walk INT64, k INT64, xi INT64, roe INT64, fc INT64, othout INT64, noout INT64, oth INT64, bip INT64, bunt INT64, ground INT64, fly INT64, line INT64, iw INT64, gdp INT64, othdp INT64, tp INT64, fle INT64, wp INT64, pb INT64, bk INT64, oa INT64, di INT64, sb2 INT64, sb3 INT64, sbh INT64, cs2 INT64, cs3 INT64, csh INT64, pko1 INT64, pko2 INT64, pko3 INT64, k_safe INT64, e1 INT64, e2 INT64, e3 INT64, e4 INT64, e5 INT64, e6 INT64, e7 INT64, e8 INT64, e9 INT64, outs_pre INT64, outs_post INT64, br1_pre STRING, br2_pre STRING, br3_pre STRING, br1_post STRING, br2_post STRING, br3_post STRING, lob_id1 STRING, lob_id2 STRING, lob_id3 STRING, pr1_pre STRING, pr2_pre STRING, pr3_pre STRING, pr1_post STRING, pr2_post STRING, pr3_post STRING, run_b STRING, run1 STRING, run2 STRING, run3 STRING, prun_b STRING, prun1 STRING, prun2 STRING, prun3 STRING, ur_b INT64, ur1 INT64, ur2 INT64, ur3 INT64, rbi_b INT64, rbi1 INT64, rbi2 INT64, rbi3 INT64, runs INT64, rbi INT64, er INT64, tur INT64, l1 STRING, l2 STRING, l3 STRING, l4 STRING, l5 STRING, l6 STRING, l7 STRING, l8 STRING, l9 STRING, lf1 INT64, lf2 INT64, lf3 INT64, lf4 INT64, lf5 INT64, lf6 INT64, lf7 INT64, lf8 INT64, lf9 INT64, f2 STRING, f3 STRING, f4 STRING, f5 STRING, f6 STRING, f7 STRING, f8 STRING, f9 STRING, po0 INT64, po1 INT64, po2 INT64, po3 INT64, po4 INT64, po5 INT64, po6 INT64, po7 INT64, po8 INT64, po9 INT64, a1 INT64, a2 INT64, a3 INT64, a4 INT64, a5 INT64, a6 INT64, a7 INT64, a8 INT64, a9 INT64, fseq INT64, batout1 INT64, batout2 INT64, batout3 INT64, brout_b INT64, brout1 INT64, brout2 INT64, brout3 INT64, firstf INT64, loc STRING, hittype STRING, dpopp INT64, pivot INT64, pn INT64, umphome STRING, ump1b STRING, ump2b STRING, ump3b STRING, umplf STRING, umprf STRING, date DATE, gametype STRING, pbp STRING);

// Edges
CREATE REL TABLE IF NOT EXISTS PLAYED_FOR_TEAM(FROM Player TO Team, last STRING, first STRING, bat STRING, throw STRING, g INT64, g_p INT64, g_sp INT64, g_rp INT64, g_c INT64, g_1b INT64, g_2b INT64, g_3b INT64, g_ss INT64, g_lf INT64, g_cf INT64, g_rf INT64, g_of INT64, g_dh INT64, g_ph INT64, g_pr INT64, first_g DATE, last_g DATE);

CREATE REL TABLE IF NOT EXISTS COACHED_FOR_TEAM(FROM Player TO Team, year INT64, role STRING, first_g DATE, last_g DATE);

CREATE REL TABLE IF NOT EXISTS PLAYED_IN_GAME(FROM Team TO Game, inn1 INT64, inn2 INT64, inn3 INT64, inn4 INT64, inn5 INT64, inn6 INT64, inn7 INT64, inn8 INT64, inn9 INT64, inn10 INT64, inn11 INT64, inn12 INT64, inn13 INT64, inn14 INT64, inn15 INT64, inn16 INT64, inn17 INT64, inn18 INT64, inn19 STRING, inn20 STRING, inn21 STRING, inn22 STRING, inn23 STRING, inn24 STRING, inn25 STRING, inn26 STRING, inn27 STRING, inn28 STRING, lob INT64, mgr STRING, stattype STRING, b_pa INT64, b_ab INT64, b_r INT64, b_h INT64, b_d INT64, b_t INT64, b_hr INT64, b_rbi INT64, b_sh INT64, b_sf INT64, b_hbp INT64, b_w INT64, b_iw INT64, b_k INT64, b_sb INT64, b_cs INT64, b_gdp INT64, b_xi INT64, b_roe INT64, p_ipouts INT64, p_noout INT64, p_bfp INT64, p_h INT64, p_d INT64, p_t INT64, p_hr INT64, p_r INT64, p_er INT64, p_w INT64, p_iw INT64, p_k INT64, p_hbp INT64, p_wp INT64, p_bk INT64, p_sh INT64, p_sf INT64, p_sb INT64, p_cs INT64, p_pb INT64, d_po INT64, d_a INT64, d_e INT64, d_dp INT64, d_tp INT64, d_pb INT64, d_wp INT64, d_sb INT64, d_cs INT64, start_l1 STRING, start_l2 STRING, start_l3 STRING, start_l4 STRING, start_l5 STRING, start_l6 STRING, start_l7 STRING, start_l8 STRING, start_l9 STRING, start_f1 STRING, start_f2 STRING, start_f3 STRING, start_f4 STRING, start_f5 STRING, start_f6 STRING, start_f7 STRING, start_f8 STRING, start_f9 STRING, start_f10 STRING, date DATE, number INT64, site STRING, vishome STRING, opp STRING, win INT64, loss INT64, tie INT64, gametype STRING, box STRING, pbp STRING);

CREATE REL TABLE IF NOT EXISTS BATTED_IN_GAME(FROM Player TO Game, team STRING, b_lp INT64, b_seq INT64, stattype STRING, b_pa INT64, b_ab INT64, b_r INT64, b_h INT64, b_d INT64, b_t INT64, b_hr INT64, b_rbi INT64, b_sh INT64, b_sf INT64, b_hbp INT64, b_w INT64, b_iw INT64, b_k INT64, b_sb INT64, b_cs INT64, b_gdp INT64, b_xi INT64, b_roe INT64, dh INT64, ph INT64, pr INT64, date DATE, number INT64, site STRING, vishome STRING, opp STRING, win INT64, loss INT64, tie INT64, gametype STRING, box STRING, pbp STRING);

CREATE REL TABLE IF NOT EXISTS FIELDED_IN_GAME(FROM Player TO Game, team STRING, d_seq INT64, d_pos INT64, stattype STRING, d_ifouts INT64, d_po INT64, d_a INT64, d_e INT64, d_dp INT64, d_tp INT64, d_pb INT64, d_wp INT64, d_sb INT64, d_cs INT64, d_gs INT64, date DATE, number INT64, site STRING, vishome STRING, opp STRING, win INT64, loss INT64, tie INT64, gametype STRING, box STRING, pbp STRING);

CREATE REL TABLE IF NOT EXISTS PITCHED_IN_GAME(FROM Player TO Game, team STRING, p_seq INT64, stattype STRING, p_ipouts INT64, p_noout INT64, p_bfp INT64, p_h INT64, p_d INT64, p_t INT64, p_hr INT64, p_r INT64, p_er INT64, p_w INT64, p_iw INT64, p_k INT64, p_hbp INT64, p_wp INT64, p_bk INT64, p_sh INT64, p_sf INT64, p_sb INT64, p_cs INT64, p_pb INT64, wp INT64, lp INT64, save INT64, p_gs INT64, p_gf INT64, p_cg INT64, date DATE, number INT64, site STRING, vishome STRING, opp STRING, win INT64, loss INT64, tie INT64, gametype STRING, box STRING, pbp STRING);

CREATE REL TABLE IF NOT EXISTS UMPIRED_IN_GAME(FROM Umpire TO Game, position STRING);

CREATE REL TABLE IF NOT EXISTS OCCURRED_IN_GAME (FROM Event TO Game, event STRING, date DATE);

CREATE REL TABLE IF NOT EXISTS PITCHED_IN_EVENT (FROM Player TO Event, pa INT64, pb INT64, pithand STRING, nump INT64, balls INT64, strikes INT64, count INT64, pitches STRING, wp INT64, bk INT64, runs INT64, er INT64, tur INT64, hr INT64, k INT64, walk INT64, iw INT64, hbp INT64, bip INT64, bunt INT64, ground INT64, fly INT64, line INT64, bathand STRING, risp BOOLEAN);

CREATE REL TABLE IF NOT EXISTS BATTED_IN_EVENT (FROM Player TO Event, pa INT64, ab INT64, bat_f INT64, bathand STRING, lineup_pos INT64, `single` INT64, double INT64, triple INT64, hr INT64, k INT64, walk INT64, iw INT64, hbp INT64, sh INT64, sf INT64, xi INT64, roe INT64, fc INT64, gdp INT64, tp INT64, bip INT64, bunt INT64, ground INT64, fly INT64, line INT64, rbi INT64, outs_pre INT64, risp BOOLEAN, k_safe INT64, fseq STRING);

CREATE REL TABLE IF NOT EXISTS ADVANCED_IN_EVENT (FROM Player TO Event, start_base INT64, end_base INT64, is_out INT64, rbi_credited INT64);

CREATE REL TABLE IF NOT EXISTS FIELDED_IN_EVENT (FROM Player TO Event, position INT64, putouts INT64, assists INT64, errors INT64);

CREATE REL TABLE IF NOT EXISTS PLAYED_IN_BALLPARK(FROM Game TO Ballpark);

CREATE REL TABLE IF NOT EXISTS CONNECTED_TO (FROM Event TO Event, inning INT64, top_bot INT64, from_event_string STRING, to_event_string STRING, runs_scored INT64, outs_recorded INT64, has_risp BOOLEAN);

CREATE REL TABLE IF NOT EXISTS RELATED_TO(FROM Player TO Player, relation STRING);

lbug graph_baseball.lbug < ladybugdb/schema.cypher

// Nodes
COPY Player FROM 'parsed/biofile0.csv' (header=true, ignore_errors=false);

COPY Umpire FROM 'parsed/umpires0.csv' (header=true, ignore_errors=false);

COPY Team FROM 'parsed/teams0.csv' (header=true, ignore_errors=false);

COPY Ballpark FROM 'parsed/ballparks0.csv' (header=true, ignore_errors=false);

COPY Game FROM 'parsed/*gameinfo.csv' (header=true, ignore_errors=false);

COPY Event FROM 'parsed/*plays.csv' (header=true, ignore_errors=false);

// Edges
COPY PLAYED_FOR_TEAM FROM 'parsed/*allplayers.csv' (header=true, ignore_errors=false);

COPY COACHED_FOR_TEAM FROM 'parsed/coaches0.csv' (header=true, ignore_errors=false);

COPY PLAYED_IN_GAME FROM 'parsed/*teamstats.csv' (header=true, ignore_errors=false);

COPY BATTED_IN_GAME FROM 'parsed/*batting.csv' (header=true, ignore_errors=false);

COPY FIELDED_IN_GAME FROM 'parsed/*fielding.csv' (header=true, ignore_errors=false);

COPY PITCHED_IN_GAME FROM 'parsed/*pitching.csv' (header=true, ignore_errors=false);

COPY RELATED_TO FROM 'parsed/relatives.csv' (header=true, ignore_errors=false);

COPY OCCURRED_IN_GAME FROM (
    MATCH (e:Event)
    WHERE e.event_id IS NOT NULL AND e.gid IS NOT NULL
    RETURN e.event_id, e.gid, e.event, e.date
);

COPY UMPIRED_IN_GAME FROM (
    MATCH (g:Game)
    UNWIND [
        {id: g.umphome, pos: 'home'},
        {id: g.ump1b,   pos: '1b'},
        {id: g.ump2b,   pos: '2b'},
        {id: g.ump3b,   pos: '3b'},
        {id: g.umplf,   pos: 'lf'},
        {id: g.umprf,   pos: 'rf'}
    ] AS ump
    WITH g, ump
    WHERE ump.id <> '(none)' AND ump.id <> ''
    MATCH (u:Umpire {id: ump.id})
    RETURN u.id, g.gid, ump.pos
);

COPY PITCHED_IN_EVENT FROM (
    MATCH (e:Event)
    WHERE e.pitcher IS NOT NULL AND e.event_id IS NOT NULL
    RETURN
    e.pitcher, e.event_id, e.pa, e.pb, e.pithand, e.nump, e.balls, e.strikes,
    e.`count`, e.pitches, e.wp, e.bk, e.runs, e.er, e.tur, e.hr, e.k, e.walk,
    e.iw, e.hbp, e.bip, e.bunt, e.ground, e.fly, e.line, e.bathand,
    (e.br2_pre IS NOT NULL OR e.br3_pre IS NOT NULL)
);

COPY BATTED_IN_EVENT FROM (
    MATCH (e:Event)
    WHERE e.batter IS NOT NULL AND e.event_id IS NOT NULL
    WITH e, CASE e.batter
        WHEN e.l1 THEN 1 WHEN e.l2 THEN 2 WHEN e.l3 THEN 3
        WHEN e.l4 THEN 4 WHEN e.l5 THEN 5 WHEN e.l6 THEN 6
        WHEN e.l7 THEN 7 WHEN e.l8 THEN 8 WHEN e.l9 THEN 9
        ELSE 0
    END AS lineup_pos
    RETURN
    e.batter, e.event_id, e.pa, e.ab, e.bat_f, e.bathand, lineup_pos,
    e.`single`, e.`double`, e.triple, e.hr, e.k, e.walk, e.iw, e.hbp, e.sh,
    e.sf, e.xi, e.roe, e.fc, e.gdp, e.tp, e.bip, e.bunt, e.ground, e.fly,
    e.line, e.rbi, e.outs_pre, (e.br2_pre IS NOT NULL OR e.br3_pre IS NOT NULL), e.k_safe, e.fseq
);

COPY ADVANCED_IN_EVENT FROM (
    MATCH (e:Event)
    WITH e,
        list_creation(e.batter, e.br1_pre, e.br2_pre, e.br3_pre) AS pre,
        list_creation(e.br1_post, e.br2_post, e.br3_post) AS post,
        list_creation(e.run_b, e.run1, e.run2, e.run3) AS runs
    UNWIND pre as b
    WITH e, b, pre, post, runs
    WHERE b IS NOT NULL
    WITH e, b, list_position(pre, b) - 1 AS start_base,
        CASE
            WHEN list_has(runs, b) THEN 4
            WHEN list_has(post, b) THEN list_position(post, b)
            ELSE 0
        END AS end_base
    RETURN b, e.event_id, start_base, end_base, (CASE WHEN end_base = 0 THEN 1 ELSE 0 END), (CASE WHEN end_base = 4 AND e.rbi > 0 THEN 1 ELSE 0 END)
);

COPY FIELDED_IN_EVENT FROM (
    MATCH (e:Event)
    UNWIND [
        {id: e.pitcher, pos: 1, po: e.po1, a: e.a1, err: e.e1},
        {id: e.f2,      pos: 2, po: e.po2, a: e.a2, err: e.e2},
        {id: e.f3,      pos: 3, po: e.po3, a: e.a3, err: e.e3},
        {id: e.f4,      pos: 4, po: e.po4, a: e.a4, err: e.e4},
        {id: e.f5,      pos: 5, po: e.po5, a: e.a5, err: e.e5},
        {id: e.f6,      pos: 6, po: e.po6, a: e.a6, err: e.e6},
        {id: e.f7,      pos: 7, po: e.po7, a: e.a7, err: e.e7},
        {id: e.f8,      pos: 8, po: e.po8, a: e.a8, err: e.e8},
        {id: e.f9,      pos: 9, po: e.po9, a: e.a9, err: e.e9}
    ] AS fielder
    WITH e, fielder
    WHERE fielder.po > 0 OR fielder.a > 0 OR fielder.err > 0
    RETURN fielder.id, e.event_id, fielder.pos, fielder.po, fielder.a, fielder.err
);

COPY PLAYED_IN_BALLPARK FROM (
    MATCH (g:Game)
    RETURN g.gid, g.site
);

COPY CONNECTED_TO FROM (
    MATCH (e1:Event), (e2:Event)
    WHERE e1.gid = e2.gid
        AND e1.inning = e2.inning
        AND e1.top_bot = e2.top_bot
        AND e2.pn = e1.pn + 1
    RETURN
        e1.event_id, e2.event_id, e1.inning, e1.top_bot, e1.event, e2.event, e1.runs,
        (e1.outs_post - e1.outs_pre),
        (e1.br2_pre IS NOT NULL OR e1.br3_pre IS NOT NULL)
);

lbug graph_baseball.lbug < ladybugdb/copy.cypher

echo "EXPORT DATABASE './ladybugdb/export' (format='csv', header=true);" | lbug graph_baseball.lbug

-- Entities Tables
CREATE TABLE player (id VARCHAR PRIMARY KEY, lastname VARCHAR, usename VARCHAR, fullname VARCHAR, birthdate DATE, birthcity VARCHAR, birthstate VARCHAR, birthcountry VARCHAR, deathdate DATE, deathcity VARCHAR, deathstate VARCHAR, deathcountry VARCHAR, cemetery VARCHAR, cem_city VARCHAR, cem_state VARCHAR, cem_ctry VARCHAR, cem_note VARCHAR, birthname VARCHAR, altname VARCHAR, debut_p DATE, last_p DATE, debut_c DATE, last_c DATE, debut_m DATE, last_m DATE, debut_u DATE, last_u DATE, bats VARCHAR, throws VARCHAR, height DOUBLE, weight DOUBLE, HOF VARCHAR);

CREATE TABLE umpire( id VARCHAR PRIMARY KEY, lastname VARCHAR, firstname VARCHAR, first_g DATE, last_g DATE);

CREATE TABLE team (team VARCHAR PRIMARY KEY, city VARCHAR, nickname VARCHAR, first_g DATE, last_g DATE);

CREATE TABLE ballpark ("site" VARCHAR PRIMARY KEY, "name" VARCHAR, city VARCHAR, state VARCHAR, first_g DATE, last_g DATE);

CREATE TABLE game (gid VARCHAR PRIMARY KEY, "site" VARCHAR, visteam VARCHAR, hometeam VARCHAR, date DATE, number BIGINT, starttime VARCHAR, daynight VARCHAR, innings BIGINT, tiebreaker BIGINT, usedh BOOLEAN, htbf VARCHAR, timeofgame BIGINT, attendance BIGINT, fieldcond VARCHAR, precip VARCHAR, sky VARCHAR, "temp" BIGINT, winddir VARCHAR, windspeed BIGINT, oscorer VARCHAR, forfeit VARCHAR, suspend BIGINT, umphome VARCHAR, ump1b VARCHAR, ump2b VARCHAR, ump3b VARCHAR, umplf VARCHAR, umprf VARCHAR, wp VARCHAR, lp VARCHAR, save VARCHAR, gametype VARCHAR, vruns BIGINT, hruns BIGINT, wteam VARCHAR, lteam VARCHAR, line VARCHAR, batteries VARCHAR, lineups VARCHAR, box VARCHAR, pbp VARCHAR, season BIGINT);

CREATE TABLE "event" (event_id VARCHAR PRIMARY KEY, gid VARCHAR, "event" VARCHAR, inning BIGINT, top_bot BIGINT, vis_home BIGINT, site VARCHAR, batteam VARCHAR, pitteam VARCHAR, score_v BIGINT, score_h BIGINT, batter VARCHAR, pitcher VARCHAR, lp BIGINT, bat_f BIGINT, bathand VARCHAR, pithand VARCHAR, balls BIGINT, strikes BIGINT, count BIGINT, pitches VARCHAR, nump BIGINT, pa BIGINT, ab BIGINT, single BIGINT, "double" BIGINT, triple BIGINT, hr BIGINT, sh BIGINT, sf BIGINT, hbp BIGINT, walk BIGINT, k BIGINT, xi BIGINT, roe BIGINT, fc BIGINT, othout BIGINT, noout BIGINT, oth BIGINT, bip BIGINT, bunt BIGINT, ground BIGINT, fly BIGINT, line BIGINT, iw BIGINT, gdp BIGINT, othdp BIGINT, tp BIGINT, fle BIGINT, wp BIGINT, pb BIGINT, bk BIGINT, oa BIGINT, di BIGINT, sb2 BIGINT, sb3 BIGINT, sbh BIGINT, cs2 BIGINT, cs3 BIGINT, csh BIGINT, pko1 BIGINT, pko2 BIGINT, pko3 BIGINT, k_safe BIGINT, e1 BIGINT, e2 BIGINT, e3 BIGINT, e4 BIGINT, e5 BIGINT, e6 BIGINT, e7 BIGINT, e8 BIGINT, e9 BIGINT, outs_pre BIGINT, outs_post BIGINT, br1_pre VARCHAR, br2_pre VARCHAR, br3_pre VARCHAR, br1_post VARCHAR, br2_post VARCHAR, br3_post VARCHAR, lob_id1 VARCHAR, lob_id2 VARCHAR, lob_id3 VARCHAR, pr1_pre VARCHAR, pr2_pre VARCHAR, pr3_pre VARCHAR, pr1_post VARCHAR, pr2_post VARCHAR, pr3_post VARCHAR, run_b VARCHAR, run1 VARCHAR, run2 VARCHAR, run3 VARCHAR, prun_b VARCHAR, prun1 VARCHAR, prun2 VARCHAR, prun3 VARCHAR, ur_b BIGINT, ur1 BIGINT, ur2 BIGINT, ur3 BIGINT, rbi_b BIGINT, rbi1 BIGINT, rbi2 BIGINT, rbi3 BIGINT, runs BIGINT, rbi BIGINT, er BIGINT, tur BIGINT, l1 VARCHAR, l2 VARCHAR, l3 VARCHAR, l4 VARCHAR, l5 VARCHAR, l6 VARCHAR, l7 VARCHAR, l8 VARCHAR, l9 VARCHAR, lf1 BIGINT, lf2 BIGINT, lf3 BIGINT, lf4 BIGINT, lf5 BIGINT, lf6 BIGINT, lf7 BIGINT, lf8 BIGINT, lf9 BIGINT, f2 VARCHAR, f3 VARCHAR, f4 VARCHAR, f5 VARCHAR, f6 VARCHAR, f7 VARCHAR, f8 VARCHAR, f9 VARCHAR, po0 BIGINT, po1 BIGINT, po2 BIGINT, po3 BIGINT, po4 BIGINT, po5 BIGINT, po6 BIGINT, po7 BIGINT, po8 BIGINT, po9 BIGINT, a1 BIGINT, a2 BIGINT, a3 BIGINT, a4 BIGINT, a5 BIGINT, a6 BIGINT, a7 BIGINT, a8 BIGINT, a9 BIGINT, fseq BIGINT, batout1 BIGINT, batout2 BIGINT, batout3 BIGINT, brout_b BIGINT, brout1 BIGINT, brout2 BIGINT, brout3 BIGINT, firstf BIGINT, loc VARCHAR, hittype VARCHAR, dpopp BIGINT, "pivot" BIGINT, pn BIGINT, umphome VARCHAR, ump1b VARCHAR, ump2b VARCHAR, ump3b VARCHAR, umplf VARCHAR, umprf VARCHAR, date DATE, gametype VARCHAR, pbp VARCHAR);

-- Many-to-Many Tables
CREATE TABLE players_teams (id VARCHAR, team VARCHAR, "last" VARCHAR, "first" VARCHAR, bat VARCHAR, throw VARCHAR, g BIGINT, g_p BIGINT, g_sp BIGINT, g_rp BIGINT, g_c BIGINT, g_1b BIGINT, g_2b BIGINT, g_3b BIGINT, g_ss BIGINT, g_lf BIGINT, g_cf BIGINT, g_rf BIGINT, g_of BIGINT, g_dh BIGINT, g_ph BIGINT, g_pr BIGINT, first_g DATE, last_g DATE);

CREATE TABLE coaches_teams (id VARCHAR, team VARCHAR, "year" BIGINT, "role" VARCHAR, first_g DATE, last_g DATE, PRIMARY KEY(id, first_g));

CREATE TABLE teams_games (team VARCHAR, gid VARCHAR, inn1 BIGINT, inn2 BIGINT, inn3 BIGINT, inn4 BIGINT, inn5 BIGINT, inn6 BIGINT, inn7 BIGINT, inn8 BIGINT, inn9 BIGINT, inn10 BIGINT, inn11 BIGINT, inn12 BIGINT, inn13 BIGINT, inn14 BIGINT, inn15 BIGINT, inn16 BIGINT, inn17 BIGINT, inn18 BIGINT, inn19 VARCHAR, inn20 VARCHAR, inn21 VARCHAR, inn22 VARCHAR, inn23 VARCHAR, inn24 VARCHAR, inn25 VARCHAR, inn26 VARCHAR, inn27 VARCHAR, inn28 VARCHAR, lob BIGINT, mgr VARCHAR, stattype VARCHAR, b_pa BIGINT, b_ab BIGINT, b_r BIGINT, b_h BIGINT, b_d BIGINT, b_t BIGINT, b_hr BIGINT, b_rbi BIGINT, b_sh BIGINT, b_sf BIGINT, b_hbp BIGINT, b_w BIGINT, b_iw BIGINT, b_k BIGINT, b_sb BIGINT, b_cs BIGINT, b_gdp BIGINT, b_xi BIGINT, b_roe BIGINT, p_ipouts BIGINT, p_noout BIGINT, p_bfp BIGINT, p_h BIGINT, p_d BIGINT, p_t BIGINT, p_hr BIGINT, p_r BIGINT, p_er BIGINT, p_w BIGINT, p_iw BIGINT, p_k BIGINT, p_hbp BIGINT, p_wp BIGINT, p_bk BIGINT, p_sh BIGINT, p_sf BIGINT, p_sb BIGINT, p_cs BIGINT, p_pb BIGINT, d_po BIGINT, d_a BIGINT, d_e BIGINT, d_dp BIGINT, d_tp BIGINT, d_pb BIGINT, d_wp BIGINT, d_sb BIGINT, d_cs BIGINT, start_l1 VARCHAR, start_l2 VARCHAR, start_l3 VARCHAR, start_l4 VARCHAR, start_l5 VARCHAR, start_l6 VARCHAR, start_l7 VARCHAR, start_l8 VARCHAR, start_l9 VARCHAR, start_f1 VARCHAR, start_f2 VARCHAR, start_f3 VARCHAR, start_f4 VARCHAR, start_f5 VARCHAR, start_f6 VARCHAR, start_f7 VARCHAR, start_f8 VARCHAR, start_f9 VARCHAR, start_f10 VARCHAR, date DATE, number BIGINT, site VARCHAR, vishome VARCHAR, opp VARCHAR, win BIGINT, loss BIGINT, tie BIGINT, gametype VARCHAR, box VARCHAR, pbp VARCHAR);

CREATE TABLE batting_games (id VARCHAR, gid VARCHAR, team VARCHAR, b_lp BIGINT, b_seq BIGINT, stattype VARCHAR, b_pa BIGINT, b_ab BIGINT, b_r BIGINT, b_h BIGINT, b_d BIGINT, b_t BIGINT, b_hr BIGINT, b_rbi BIGINT, b_sh BIGINT, b_sf BIGINT, b_hbp BIGINT, b_w BIGINT, b_iw BIGINT, b_k BIGINT, b_sb BIGINT, b_cs BIGINT, b_gdp BIGINT, b_xi BIGINT, b_roe BIGINT, dh BIGINT, ph BIGINT, pr BIGINT, date DATE, number BIGINT, site VARCHAR, vishome VARCHAR, opp VARCHAR, win BIGINT, loss BIGINT, tie BIGINT, gametype VARCHAR, box VARCHAR, pbp VARCHAR);

CREATE TABLE fielding_games (id VARCHAR, gid VARCHAR, team VARCHAR, d_seq BIGINT, d_pos BIGINT, stattype VARCHAR, d_ifouts BIGINT, d_po BIGINT, d_a BIGINT, d_e BIGINT, d_dp BIGINT, d_tp BIGINT, d_pb BIGINT, d_wp BIGINT, d_sb BIGINT, d_cs BIGINT, d_gs BIGINT, date DATE, number BIGINT, site VARCHAR, vishome VARCHAR, opp VARCHAR, win BIGINT, loss BIGINT, tie BIGINT, gametype VARCHAR, box VARCHAR, pbp VARCHAR);

CREATE TABLE pitching_games (id VARCHAR, gid VARCHAR, team VARCHAR, p_seq BIGINT, stattype VARCHAR, p_ipouts BIGINT, p_noout BIGINT, p_bfp BIGINT, p_h BIGINT, p_d BIGINT, p_t BIGINT, p_hr BIGINT, p_r BIGINT, p_er BIGINT, p_w BIGINT, p_iw BIGINT, p_k BIGINT, p_hbp BIGINT, p_wp BIGINT, p_bk BIGINT, p_sh BIGINT, p_sf BIGINT, p_sb BIGINT, p_cs BIGINT, p_pb BIGINT, wp BIGINT, lp BIGINT, save BIGINT, p_gs BIGINT, p_gf BIGINT, p_cg BIGINT, date DATE, number BIGINT, site VARCHAR, vishome VARCHAR, opp VARCHAR, win BIGINT, loss BIGINT, tie BIGINT, gametype VARCHAR, box VARCHAR, pbp VARCHAR);

CREATE TABLE umpires_games (id VARCHAR, gid VARCHAR, position VARCHAR);

CREATE TABLE events_games (event_id VARCHAR, gid VARCHAR, "event" VARCHAR, "date" DATE);

CREATE TABLE pitching_events (id VARCHAR, event_id VARCHAR, pa BIGINT, pb BIGINT, pithand VARCHAR, nump BIGINT, balls BIGINT, strikes BIGINT, count BIGINT, pitches VARCHAR, wp BIGINT, bk BIGINT, runs BIGINT, er BIGINT, tur BIGINT, hr BIGINT, k BIGINT, walk BIGINT, iw BIGINT, hbp BIGINT, bip BIGINT, bunt BIGINT, ground BIGINT, fly BIGINT, line BIGINT, bathand VARCHAR, risp BOOLEAN);

CREATE TABLE batting_events (id VARCHAR, event_id VARCHAR, pa BIGINT, ab BIGINT, bat_f BIGINT, bathand VARCHAR, lineup_pos BIGINT, single BIGINT, double BIGINT, triple BIGINT, hr BIGINT, k BIGINT, walk BIGINT, iw BIGINT, hbp BIGINT, sh BIGINT, sf BIGINT, xi BIGINT, roe BIGINT, fc BIGINT, gdp BIGINT, tp BIGINT, bip BIGINT, bunt BIGINT, ground BIGINT, fly BIGINT, line BIGINT, rbi BIGINT, outs_pre BIGINT, risp BOOLEAN, k_safe BIGINT, fseq VARCHAR);

CREATE TABLE advancing_events (id VARCHAR, event_id VARCHAR, start_base BIGINT, end_base BIGINT, is_out BIGINT, rbi_credited BIGINT);

CREATE TABLE fielding_events (id VARCHAR, event_id VARCHAR, position BIGINT, putouts BIGINT, assists BIGINT, errors BIGINT);

CREATE TABLE connected_events (event_id1 VARCHAR, event_id2 VARCHAR, inning BIGINT, top_bot BIGINT, from_event_string VARCHAR, to_event_string VARCHAR, runs_scored BIGINT, outs_recorded BIGINT, has_risp BOOLEAN);

CREATE TABLE players_relationships (id1 VARCHAR, id2 VARCHAR, relation VARCHAR);