WorldCup/insert_data.sh at main · dai0413/WorldCup · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
#! /bin/bash

if [[ $1 == "test" ]]
then
  PSQL="psql --username=postgres --dbname=worldcuptest -t --no-align -c"
else
  PSQL="psql --username=freecodecamp --dbname=worldcup -t --no-align -c"
fi

# Do not change code above this line. Use the PSQL variable above to query your database.
echo $($PSQL "TRUNCATE teams, games")

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  if [[ $YEAR != 'year' ]]
  then

  # winner_id
  WINNER_ID=$($PSQL "
    SELECT team_id FROM teams
    WHERE name='$WINNER'
  ")

  if [[ -z $WINNER_ID ]]
  then
    INSERT_TEAM_RESULT=$($PSQL "
      INSERT INTO teams(name) VALUES('$WINNER')")

    WINNER_ID=$($PSQL "
      SELECT team_id FROM teams
      WHERE name='$WINNER'
    ")
  fi

  # opponent_id
  OPPONENT_ID=$($PSQL "
    SELECT team_id FROM teams
    WHERE name='$OPPONENT'
  ")

  if [[ -z $OPPONENT_ID ]]
  then
    INSERT_TEAM_RESULT=$($PSQL "
      INSERT INTO teams(name) VALUES('$OPPONENT')")

    OPPONENT_ID=$($PSQL "
      SELECT team_id FROM teams
      WHERE name='$OPPONENT'
    ")
  fi

  INSERT_GAMES_RESULT=$($PSQL "
    INSERT INTO games(
      year, round, winner_id, opponent_id, winner_goals, opponent_goals
    )
    VALUES(
      $YEAR, '$ROUND', $WINNER_ID, $OPPONENT_ID, $WINNER_GOALS, $OPPONENT_GOALS
    )
  ")
  fi
done