def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS league (
league_id TEXT,
name TEXT
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO city(name, state)
SELECT city, state
FROM park_codes
GROUP BY city, state;
''')
def feed_table():
columns = [
"game_number", "score", "line_score", "at_bats", "hits", "doubles",
"triples", "homeruns", "rbi", "sacrifice_hits", "sacrifice_flies",
"hit_by_pitch", "walks", "intentional_walks", "strikeouts",
"stolen_bases", "caught_stealing", "grounded_into_double",
"first_catcher_interference", "left_on_base", "pitchers_used",
"individual_earned_runs", "team_earned_runs", "wild_pitches", "balks",
"putouts", "assists", "errors", "passed_balls", "double_plays",
"triple_plays"
]
db.run_command(f'''
INSERT OR IGNORE INTO team_stat(game_id, team_id, {','.join([f'{c}' for c in columns])})
SELECT game_id, team_id, {','.join([f'{c}' for c in columns])}
FROM (
SELECT
game_id, v_name team_id, {','.join([f'v_{c} {c}' for c in columns])}, date
FROM game_log
UNION
SELECT
game_id, h_name team_id, {','.join([f'h_{c} {c}' for c in columns])}, date
FROM game_log
ORDER BY date
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO team_appearence(appearence_id, team_id)
SELECT
p.appearance_id,
CASE
WHEN p.person_id IN
(
v_manager_id,
v_starting_pitcher_id,
v_player_1_id,
v_player_2_id,
v_player_3_id,
v_player_4_id,
v_player_5_id,
v_player_6_id,
v_player_7_id,
v_player_8_id,
v_player_9_id
)
THEN g.v_name
ELSE g.h_name
END AS team_id
FROM person_appearance p
INNER JOIN game_log g ON g.game_id == p.game_id
WHERE appearance_type_id NOT IN ('UHP', 'U1B', 'U2B', 'U3B', 'ULF', 'URF', 'AWP', 'ALP', 'ASP', 'AWB')
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS person (
person_id TEXT PRIMARY KEY,
first TEXT,
last TEXT
)
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS city (
city_id INTEGER PRIMARY KEY,
name TEXT,
state TEXT
)
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS team_appearence (
appearence_id INTEGER PRIMARY KEY,
team_id TEXT,
FOREIGN KEY(team_id) REFERENCES team(team_id)
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO league(league_id, name)
VALUES
('NL', 'National League'),
('AL', 'American League'),
('FL', 'Federal League'),
('PL', "Players' League"),
('UA', 'Union Association'),
('AA', 'American Association')
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS person_appearance (
appearance_id INTEGER PRIMARY KEY,
game_id TEXT,
person_id TEXT,
appearance_type_id TEXT,
FOREIGN KEY(game_id) REFERENCES game(game_id),
FOREIGN KEY(person_id) REFERENCES person(person_id)
)
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS team (
team_id TEXT PRIMARY KEY,
league_id TEXT,
city_id INTEGER,
nickname TEXT,
french_id TEXT,
seq INTEGER,
FOREIGN KEY(league_id) REFERENCES league(league_id),
FOREIGN KEY(city_id) REFERENCES city(city_id)
)
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS park (
park_id TEXT PRIMARY KEY,
city_id INTEGER,
league_id TEXT,
name TEXT,
aka TEXT,
notes TEXT,
FOREIGN KEY(city_id) REFERENCES city(city_id),
FOREIGN KEY(league_id) REFERENCES league(league_id)
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO park(
park_id,
city_id,
league_id,
name,
aka,
notes
)
SELECT p.park_id, c.city_id, l.league_id, p.name, p.aka, p.notes
FROM park_codes p
LEFT JOIN city c ON p.city == c.name
LEFT JOIN league l ON p.league == l.league_id
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS game (
game_id TEXT PRIMARY KEY,
park_id TEXT,
date DATE,
number_of_game VARCHAR(1),
length_outs INTEGER,
day BOOLEAN,
completion TEXT,
forefeit VARCHAR(1),
protest VARCHAR(1),
attendance INTEGER,
length_minutes INTEGER,
additional_info TEXT,
acquisition_info VARCHAR(1),
FOREIGN KEY(park_id) REFERENCES park(park_id)
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO team(
team_id,
league_id,
city_id,
nickname,
french_id,
seq
)
SELECT
t.team_id,
t.league,
c.city_id,
t.nickname,
t.franch_id,
t.seq
FROM team_codes t
LEFT JOIN city c ON c.name == t.city
''')
def create_table():
db.run_command('''
CREATE TABLE IF NOT EXISTS team_stat (
game_id TEXT PRIMARY KEY,
team_id TEXT,
game_number INTEGER,
score INTEGER,
line_score TEXT,
at_bats INTEGER,
hits INTEGER,
doubles INTEGER,
triples INTEGER,
homeruns INTEGER,
rbi INTEGER,
sacrifice_hits INTEGER,
sacrifice_flies INTEGER,
hit_by_pitch INTEGER,
walks INTEGER,
intentional_walks INTEGER,
strikeouts INTEGER,
stolen_bases INTEGER,
caught_stealing INTEGER,
grounded_into_double INTEGER,
first_catcher_interference INTEGER,
left_on_base INTEGER,
pitchers_used INTEGER,
individual_earned_runs INTEGER,
team_earned_runs INTEGER,
wild_pitches INTEGER,
balks INTEGER,
putouts INTEGER,
assists INTEGER,
errors INTEGER,
passed_balls INTEGER,
double_plays INTEGER,
triple_plays INTEGER,
FOREIGN KEY(team_id) REFERENCES team(team_id)
)
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO game
SELECT
game_id,
park_id,
date,
number_of_game,
length_outs,
CASE
WHEN day_night=='D'
THEN TRUE
WHEN day_night=='N'
THEN FALSE
ELSE NULL END AS day,
completion,
forefeit,
protest,
attendance,
length_minutes,
additional_info,
acquisition_info
FROM game_log
''')
def feed_table():
# yes, copied from solution...
c2 = """
INSERT OR IGNORE INTO person_appearance (
game_id,
person_id,
appearance_type_id
)
SELECT
game_id,
hp_umpire_id,
"UHP"
FROM game_log
WHERE hp_umpire_id IS NOT NULL
UNION
SELECT
game_id,
[1b_umpire_id],
"U1B"
FROM game_log
WHERE "1b_umpire_id" IS NOT NULL
UNION
SELECT
game_id,
[2b_umpire_id],
"U2B"
FROM game_log
WHERE [2b_umpire_id] IS NOT NULL
UNION
SELECT
game_id,
[3b_umpire_id],
"U3B"
FROM game_log
WHERE [3b_umpire_id] IS NOT NULL
UNION
SELECT
game_id,
lf_umpire_id,
"ULF"
FROM game_log
WHERE lf_umpire_id IS NOT NULL
UNION
SELECT
game_id,
rf_umpire_id,
"URF"
FROM game_log
WHERE rf_umpire_id IS NOT NULL
UNION
SELECT
game_id,
v_manager_id,
"MM"
FROM game_log
WHERE v_manager_id IS NOT NULL
UNION
SELECT
game_id,
h_manager_id,
"MM"
FROM game_log
WHERE h_manager_id IS NOT NULL
UNION
SELECT
game_id,
winning_pitcher_id,
"AWP"
FROM game_log
WHERE winning_pitcher_id IS NOT NULL
UNION
SELECT
game_id,
losing_pitcher_id,
"ALP"
FROM game_log
WHERE losing_pitcher_id IS NOT NULL
UNION
SELECT
game_id,
saving_pitcher_id,
"ASP"
FROM game_log
WHERE saving_pitcher_id IS NOT NULL
UNION
SELECT
game_id,
winning_rbi_batter_id,
"AWB"
FROM game_log
WHERE winning_rbi_batter_id IS NOT NULL
UNION
SELECT
game_id,
v_starting_pitcher_id,
"PSP"
FROM game_log
WHERE v_starting_pitcher_id IS NOT NULL
UNION
SELECT
game_id,
h_starting_pitcher_id,
"PSP"
FROM game_log
WHERE h_starting_pitcher_id IS NOT NULL;
"""
template = """
INSERT OR IGNORE INTO person_appearance (
game_id,
person_id,
appearance_type_id
)
SELECT
game_id,
{hv}_player_{num}_id,
"O{num}"
FROM game_log
WHERE {hv}_player_{num}_id IS NOT NULL
UNION
SELECT
game_id,
{hv}_player_{num}_id,
"D" || CAST({hv}_player_{num}_def_pos AS INT)
FROM game_log
WHERE {hv}_player_{num}_id IS NOT NULL;
"""
db.run_command(c2)
for hv in ["h", "v"]:
for num in range(1, 10):
query_vars = {"hv": hv, "num": num}
db.run_command(template.format(**query_vars))
def drop_table():
db.run_command('''
DROP TABLE IF EXISTS person
''')
def feed_table():
db.run_command('''
INSERT OR IGNORE INTO person
SELECT id, first, last
FROM person_codes
''')
def drop_table():
db.run_command('''
DROP TABLE IF EXISTS league
''')
def drop_table():
db.run_command('''
DROP TABLE IF EXISTS team
''')
def drop_table():
db.run_command('''
DROP TABLE IF EXISTS person_appearance
''')
def drop_table():
db.run_command('''
DROP TABLE IF EXISTS team_appearence
''')