Build a World Cup Database - Build a World Cup Database

Backend Development
1

Tell us what’s happening:

Cant pass the insert_data.sh test however i have tested my script and have managed to insert the data via the script

Your code so far

#! /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 RESTART IDENTITY CASCADE;")


# Populate teams table

cat $1 | while IFS="," read  YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  if [[ $WINNER != winner ]]
  then
    WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'; ")

    if [[ -z $WINNER_ID ]]
    then
      INSERT_WINNER_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER'); ")
      echo "$INSERT_WINNER_RESULT Winner $WINNER Inserted"
    fi
  fi

  if [[ $OPPONENT != opponent ]]
  then
    OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'; ")
    if [[ -z $OPPONENT_ID ]]
    then
      INSERT_OPPONENT_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT'); ")
      echo "$INSERT_OPPONENT_RESULT Opponent $OPPONENT Inserted"
    fi
  fi

# Populate games table

  if [[ ($YEAR != year) || ($ROUND != round) || ($WINNER_GOALS != winner_goals) || ($OPPONENT_GOALS != opponent_goals) ]]
  then
      GAME_WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER';")
      GAME_OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT';")
      INSERT_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $GAME_WINNER_ID, $GAME_OPPONENT_ID); ")
      echo "$INSERT_OPPONENT_RESULT Fixture $WINNER vs $OPPONENT Inserted"
  fi

done

Your browser information:

User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/143.0.0.0 Safari/537.36

Challenge Information:

Build a World Cup Database - Build a World Cup Database

2

why do you need this?

(I also suggest removing all echo statements. I do not think your script should output anything at all)

Edit:

also you do not need to reselect the ids again here:

  if [[ ($YEAR != year) || ($ROUND != round) || ($WINNER_GOALS != winner_goals) || ($OPPONENT_GOALS != opponent_goals) ]]
  then
      GAME_WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER';")
      GAME_OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT';")
      INSERT_GAME_RESULT=$($PSQL "INSERT INTO games(year, round, winner_goals, opponent_goals, winner_id, opponent_id) VALUES($YEAR, '$ROUND', $WINNER_GOALS, $OPPONENT_GOALS, $GAME_WINNER_ID, $GAME_OPPONENT_ID); ")
      echo "$INSERT_OPPONENT_RESULT Fixture $WINNER vs $OPPONENT Inserted"
  fi

You already got them when you tried to select them earlier, so just use them. (if they don’t exist, you should update the ids when you do the team insert statements, this saves having to go back into the db and slowing down the script which may make the test timeout)

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.