World Cup Database - Build a World Cup Database

yasminenoubissie76 · April 19, 2023, 11:47am

Tell us what’s happening:
Describe your issue in detail here.

Hi, I am currently completing the worldcup database project, I have run the scripts several times but I can only seem to pass all of the tests except for test 9 and 10.

I have tried deleting the component 3 times.

My script also works perfectly, the output is just like the one expected, the database is correctly completed, I have in 24 rows for the teams table and 32 rows for the games table. Also, I have to run the script several times before the other tests pass.

I think the problem is from the insert_data.sh

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 TABLE teams, games;")
echo $($PSQL "SELECT currval('games_game_id_seq') FROM games;")
echo $($PSQL "SELECT setval('games_game_id_seq', 1, false);")
echo $($PSQL "SELECT currval('teams_team_id_seq') FROM teams;")
echo $($PSQL "SELECT setval('teams_team_id_seq', 1, false);")
# Insert data into teams table

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  WINNER_ID=$($PSQL "SELECT team_id from teams WHERE name='$WINNER' ")
  OPPONENT_ID=$($PSQL "SELECT team_id from teams WHERE name='$OPPONENT' ")
if [[ $WINNER != 'winner' && $OPPONENT != 'opponent' ]]
then
  if [[ -z $WINNER_ID ]]
  then
   INSERT_W=$($PSQL "INSERT INTO teams(name, qualified) VALUES('$WINNER', 'yes')")
   if [[ $INSERT_W == "INSERT 0 1" ]]
   then
    echo Inserted $WINNER
   fi
  elif [[ -z $OPPONENT_ID ]]
  then
    INSERT_O=$($PSQL "INSERT INTO teams(name, qualified) VALUES('$OPPONENT', 'yes')")
    if [[ $INSERT_O == "INSERT 0 1" ]]
   then
    echo Inserted $OPPONENT
   fi
  fi
fi
done

# Insert data into games table

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
# get team ids
  WINNER_ID=$($PSQL "SELECT team_id from teams WHERE name='$WINNER' ")
  OPPONENT_ID=$($PSQL "SELECT team_id from teams WHERE name='$OPPONENT' ")

  if [[ $YEAR != "year" && $WINNER != 'winner' && $OPPONENT != 'opponent' ]]
  then 
    # get infos
    G_YEAR=$($PSQL "SELECT year FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_ROUND=$($PSQL "SELECT round FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_WINNER=$($PSQL "SELECT winner_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_OPPONENT=$($PSQL "SELECT opponent_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_WINNER_GOALS=$($PSQL "SELECT winner_goals FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_OPPONENT_GOALS=$($PSQL "SELECT opponent_goals FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")

    # if not found
    if [[ -z $G_YEAR && -z $G_ROUND && -z $G_WINNER && -z $G_OPPONENT && -z $G_WINNER_GOALS && -z $G_OPPONENT_GOALS ]] 
    then 
      # insert infos
   echo $($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
   fi
    
done

echo THERE ARE $($PSQL "SELECT COUNT(*) FROM teams;") rows
echo THERE ARE $($PSQL "SELECT COUNT(*) FROM games;") rows

Your browser information:

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

Challenge: World Cup Database - Build a World Cup Database

Link to the challenge:

sanity · April 19, 2023, 2:20pm

Could you share the dump of the db as well?

yasminenoubissie76 · April 20, 2023, 12:30pm

Yes sure, but I am having trouble connecting to the db, both db actually, the postgres db and the worldcup db. So I’ll be deleting the component and restarting the project before sending the db dump

yasminenoubissie76 · April 20, 2023, 1:39pm

Hi, I have tried deleting and restarting the component multiple time sbut nothing changes. I still have the error code.

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: Connection refused
        Is the server running locally and accepting connections on that socket?

I tried this solution : Unable to connect to postgres - #3 by moT01
several times but nothing works

I need help please, I haven’t used any of the databases lately because of my issue with the Worldcup project tests and switched to the ’ Kitty Ipsum Translator’ project and now that I came back, it just wont work

Mathhew · April 20, 2023, 1:56pm

Above these tasks, there is described that the script has a limited amount of time to run?
If so you need to make it to insert data faster.

yasminenoubissie76 · April 20, 2023, 1:57pm

OKay, got it, thank you I will try that once I manage to connect to postgres database. Do you have a solution concerning this problem ?

igorgetmeabrain · April 20, 2023, 1:57pm

There are connection issues with the Relational Database courses at the moment I believe. The error you’re getting has nothing to do with any issues with your code.

yasminenoubissie76 · April 20, 2023, 1:58pm

Oh okay, so I should wait then. Thank you very much, I will do that and if I encounter any issues after all these solutions, I will be coming back, if you don’t mind.

igorgetmeabrain · April 20, 2023, 2:01pm

Yes, it’s possible that your script is running too slow anyway (as @Mathhew suggests), but let us know if it’s still not working once the connection issues have been resolved.

Coder122 · April 20, 2023, 8:07pm

I got the same issue as you around the same time there’s probably a server issue with Free Code Camp

YasserRa-fat · April 20, 2023, 8:25pm

The issue is in here within this part of the code, when you TRUNCATE the table the SERIAL datatype increments over the previous id’s that were deleted…
So the id’s aren’t provided correctly.
To make serial count from id 0 after deleting the table

You should add after truncate RESTART IDENTITY

it should look something like this

tkhuu79 · April 21, 2023, 1:41am

Please explain how changing the TRUNCATE line help.
I got the same error in bike-shop database lesson that does not have the TRUNCATE line.

YasserRa-fat · April 21, 2023, 3:17am

Whenever you delete a column of SERIAL datatype in your database The datatype of SERIAL needs to be told to restart so it can start to counting from index 0.
If you don’t do that it will keep counting from where it was before deleting the column

tkhuu79 · April 21, 2023, 2:05pm

So I am not trying to press the point. How does the TRUNCATE line relate to server fail to connect?

yasminenoubissie76 · April 21, 2023, 2:40pm

While I was coding I noticed that issue, that is why I used “SELECT currval()” and “SELECT setval()”, but this method seems shorter, it might be useful to make my script shorter and faster

YasserRa-fat · April 22, 2023, 1:23am

It doesn’t relate to a server problem it relates to the two unchecked tasks that the post was originally made for

YasserRa-fat · April 22, 2023, 1:25am

The reason why it doesn’t run the task correctly is that you indexed them at 1 while SERIAL indexes from 0

yasminenoubissie76 · April 28, 2023, 3:44pm

I can finally access the postgres database, I recreated worldcup in addition of it’s tables and their rows. Now, I pasted my code again, setting the TRUNCATE line to the line you gave me, nothing has changed. And I can’t figure out a way to shorten my script.

Here is my code

#! /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 TABLE teams, games RESTART IDENTITY;")
# Insert data into teams table

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
  WINNER_ID=$($PSQL "SELECT team_id from teams WHERE name='$WINNER' ")
  OPPONENT_ID=$($PSQL "SELECT team_id from teams WHERE name='$OPPONENT' ")
if [[ $WINNER != 'winner' && $OPPONENT != 'opponent' ]]
then
  if [[ -z $WINNER_ID ]]
  then
   INSERT_W=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
   if [[ $INSERT_W == "INSERT 0 1" ]]
   then
    echo Inserted $WINNER
   fi
  elif [[ -z $OPPONENT_ID ]]
  then
    INSERT_O=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
    if [[ $INSERT_O == "INSERT 0 1" ]]
   then
    echo Inserted $OPPONENT
   fi
  fi
fi
done

# Insert data into games table

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
# get team ids
  WINNER_ID=$($PSQL "SELECT team_id from teams WHERE name='$WINNER' ")
  OPPONENT_ID=$($PSQL "SELECT team_id from teams WHERE name='$OPPONENT' ")

  if [[ $YEAR != "year" && $WINNER != 'winner' && $OPPONENT != 'opponent' ]]
  then 
    # get infos
    G_YEAR=$($PSQL "SELECT year FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_ROUND=$($PSQL "SELECT round FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_WINNER=$($PSQL "SELECT winner_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_OPPONENT=$($PSQL "SELECT opponent_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_WINNER_GOALS=$($PSQL "SELECT winner_goals FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_OPPONENT_GOALS=$($PSQL "SELECT opponent_goals FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")

    # if not found
    if [[ -z $G_YEAR && -z $G_ROUND && -z $G_WINNER && -z $G_OPPONENT && -z $G_WINNER_GOALS && -z $G_OPPONENT_GOALS ]] 
    then 
      # insert infos
   echo $($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
   fi
    
done

yasminenoubissie76 · April 28, 2023, 4:19pm

Could it be related to the fact that I use cat games.csv twice ? Because everything seems fin when I delete one of the two blocks, every test except the last 3 check green but when I leave both, they all turn gray again

By the way, I shortened the 2nd part of the code :

cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
# get team ids
  WINNER_ID=$($PSQL "SELECT team_id from teams WHERE name='$WINNER' ")
  OPPONENT_ID=$($PSQL "SELECT team_id from teams WHERE name='$OPPONENT' ")

  if [[ $YEAR != "year" && $WINNER != 'winner' && $OPPONENT != 'opponent' ]]
  then 
    # get infos
    G_WINNER=$($PSQL "SELECT winner_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    G_OPPONENT=$($PSQL "SELECT opponent_id FROM games WHERE (winner_id=$WINNER_ID AND opponent_id=$OPPONENT_ID )")
    
    # if not found
    if [[ -z $G_WINNER && -z $G_OPPONENT ]] 
    then 
      # insert infos
   echo $($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
   fi
    
done

yasminenoubissie76 · April 28, 2023, 6:19pm

Hey, so spent some time trying to figure out how to make the code shorter and it made me pass all the tests at the end. And so using cat games.csv twice wasn’t the problem, this method also helped shorten the code

echo $($PSQL "TRUNCATE TABLE teams, games RESTART IDENTITY;")

Tests weren’t passing within the 20 seconds, so I reduced the number of times I used a query ($PSQL ) so it would be faster

Thanks to all the people who helped by the way, at the end, all their advices were very useful