Build a World Cup Database - Build a World Cup Database

[dhorowitz001]

Tell us what’s happening:

Is there supposed to be a teams.csv file to use as an argument in this step: When you run your insert_data.sh script, it should add each unique team to the teams table. There should be 24 rows.

The tutorial opened with 2 .sh files, expected_output.txt and games.csv.

Where do I get the info for the teams table?

Your code so far

Your browser information:

User Agent is: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/26.2 Safari/605.1.15 Ddg/26.2

Challenge Information:

Build a World Cup Database - Build a World Cup Database

freeCodeCamp.org

[dhorowitz001]

I figured it out, extracted the team names (countries) from the games.csv.

[dhorowitz001]

Tell us what’s happening:

I’ve run insert_data.sh and have a teams table with 24 rows and a games table with 32 rows, but am failing the test step that checks the game table. Here’s my game table: what am I doing wrong?

---------±-----±--------------±----------±------------±-------------±---------------
1 | 2018 | Final | 11 | 8 | 4 | 2
2 | 2018 | Third Place | 3 | 10 | 2 | 0
3 | 2018 | Semi-Final | 8 | 10 | 2 | 1
4 | 2018 | Semi-Final | 11 | 3 | 1 | 0
5 | 2018 | Quarter-Final | 8 | 19 | 3 | 2
6 | 2018 | Quarter-Final | 10 | 21 | 2 | 0
7 | 2018 | Quarter-Final | 3 | 4 | 2 | 1
8 | 2018 | Quarter-Final | 11 | 24 | 2 | 0
9 | 2018 | Eighth-Final | 10 | 6 | 2 | 1
10 | 2018 | Eighth-Final | 21 | 22 | 1 | 0
11 | 2018 | Eighth-Final | 3 | 14 | 3 | 2
12 | 2018 | Eighth-Final | 4 | 15 | 2 | 0
13 | 2018 | Eighth-Final | 8 | 9 | 2 | 1
14 | 2018 | Eighth-Final | 19 | 20 | 2 | 1
15 | 2018 | Eighth-Final | 24 | 18 | 2 | 1
16 | 2018 | Eighth-Final | 11 | 2 | 4 | 3
17 | 2014 | Final | 12 | 2 | 1 | 0
18 | 2014 | Third Place | 16 | 4 | 3 | 0
19 | 2014 | Semi-Final | 2 | 16 | 1 | 0
20 | 2014 | Semi-Final | 12 | 4 | 7 | 1
21 | 2014 | Quarter-Final | 16 | 7 | 1 | 0
22 | 2014 | Quarter-Final | 2 | 3 | 1 | 0
23 | 2014 | Quarter-Final | 4 | 6 | 2 | 1
24 | 2014 | Quarter-Final | 12 | 11 | 1 | 0
25 | 2014 | Eighth-Final | 4 | 5 | 2 | 1
26 | 2014 | Eighth-Final | 6 | 24 | 2 | 0
27 | 2014 | Eighth-Final | 11 | 17 | 2 | 0
28 | 2014 | Eighth-Final | 12 | 1 | 2 | 1
29 | 2014 | Eighth-Final | 16 | 15 | 2 | 1
30 | 2014 | Eighth-Final | 7 | 13 | 2 | 1
31 | 2014 | Eighth-Final | 2 | 22 | 1 | 0
32 | 2014 | Eighth-Final | 3 | 23 | 2 | 1
(32 rows)

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.

DB_USER="freecodecamp"
DB_NAME="worldcup"
GAMES_CSV_FILE="games.csv"
TEAMS_TABLE_NAME="teams"
TEMP_TABLE_NAME="temp_games"

# Step 1: Insert unique team names into the teams table
awk -F, 'NR > 1 {print $3; print $4}' "$GAMES_CSV_FILE" | sort -u | while read -r team_name; do
    $PSQL "INSERT INTO $TEAMS_TABLE_NAME (name) VALUES ('$team_name') ON CONFLICT (name) DO NOTHING;"
done

# Check if the insertion was successful
if [ $? -eq 0 ]; then
    echo "Teams imported successfully."
else
    echo "Error importing teams."
    exit 1
fi

# Step 2: Execute all SQL commands with a temporary table
{
echo "CREATE TEMP TABLE $TEMP_TABLE_NAME ("
echo "    year INT,"
echo "    round TEXT,"
echo "    winner TEXT,"
echo "    opponent TEXT,"
echo "    winner_goals INT,"
echo "    opponent_goals INT"
echo ");"

echo "\COPY $TEMP_TABLE_NAME FROM '$GAMES_CSV_FILE' WITH (FORMAT csv, HEADER);"

echo "INSERT INTO games (year, round, winner_id, opponent_id, winner_goals, opponent_goals)"
echo "SELECT"
echo "    tg.year,"
echo "    tg.round,"
echo "    t1.team_id AS winner_id,"
echo "    t2.team_id AS opponent_id,"
echo "    tg.winner_goals,"
echo "    tg.opponent_goals"
echo "FROM"
echo "    $TEMP_TABLE_NAME tg"
echo "JOIN"
echo "    $TEAMS_TABLE_NAME t1 ON tg.winner = t1.name"
echo "JOIN"
echo "    $TEAMS_TABLE_NAME t2 ON tg.opponent = t2.name"
echo "ON CONFLICT (year, round, winner_id, opponent_id) DO NOTHING;"
} | psql --username=freecodecamp --dbname=worldcup -t --no-align

# Check if the games were imported successfully
if [ $? -eq 0 ]; then
    echo "Games imported successfully."
else
    echo "Error importing games."
fi

Your browser information:

User Agent is: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/26.2 Safari/605.1.15 Ddg/26.2

Challenge Information:

Build a World Cup Database - Build a World Cup Database

freeCodeCamp.org

[Teller]

Hi @dhorowitz001

I went ahead and combined your posts for you. In the future, just reply to the original thread to add further updates.

What are the requirements for the failing test?

Happy coding

[dhorowitz001]

This is the failing test:

When you run your insert_data.sh script, it should insert a row for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct ID’s from the teams table (you cannot hard-code the values).

I’ve got 32 completely filled out rows, I didn’t hard-code the country ids, and I’m pretty sure all the values are correct as my queries pass all the tests. Any guidance on how I’m not meeting the requirements would be appreciated!

[hbar1st]

you’ll need to provide us with a link to a repo containing your code and a dump of the db so we can take a look, thanks.

[dhorowitz001]

Thanks for working with me on this. Here’s the repo:

https://github.com/dhorowitz001/WorldCup.git

[hbar1st]

This is a guess but, i noticed your code is overly complicated. The test may be timing out if your script takes longer than expected to run.

Instead of reading the csv file multiple times to get the data, you could have just read it line by line (one time only) and inserted the data one by one as you read each game.

I don’t honestly know if that may be the issue you’re hitting but your code is unique enough that it may be.

[dhorowitz001]

I took another pass at it with a different approach, and now, even though selecting all from teams and games shows them properly populated, and queries.sh runs successfully, I’m failing the final 3 tests:

When you run your insert_data.sh script, it should add each unique team to the teams table. There should be 24 rows

• When you run your insert_data.sh script, it should insert a row for each line in the games.csv file (other than the top line of the file). There should be 32 rows. Each row should have every column filled in with the appropriate info. Make sure to add the correct ID’s from the teams table (you cannot hard-code the values)

• You should correctly complete the queries in the queries.sh file. Fill in each empty echo command to get the output of what is suggested with the command above it. Only use a single line like the first query. The output should match what is in the expected_output.txtfile exactly, take note of the number of decimal places in some of the query results

I’ve pushed the updated version to GitHub if you’d be willing to take another look. Thanks.

[hbar1st]

would you explain why you need a temporary table at all?

(You could just read one line in at a time, and create the teams if needed, then create the game row accordingly?)

[dhorowitz001]

Moving in the wrong direction. I took your suggestion, and now create both teams and games on each line read of the csv. Both tables are created properly with the correct number of rows, and my queries.sh runs fine and passes a diff test with the expected output. But now I’m not passing ANY of the tests, it doesn’t even see the database. So frustrating! What can I try next? By the way, I went with the temporary table at first because I read that it was more efficient at scale, but since we’re dealing with small data, it’s not so important I guess.

[hbar1st]

try clicking the Reset button and recreating the db

[dhorowitz001]

I went through the reset/recreate cycle several times – once the tests ran with the same final 3 failing, most of the time they all fail despite the database and tables being created and populated correctly, and queries.sh/diff with expected_output.txt being successful.

[hbar1st]

one of the differences between how i did this code and yours is that my shell program ran silently (no extra output).
Can you try doing that? (for eg. i didn’t have an exit code)

edit: i would begin by removing the code at the end of your script that confirms the insert was done or not.

[dhorowitz001]

Got rid of the code at the end confirming the inserts, and added —quiet to the PSQL variable so when the inserts happen there’s no console message. Still failing all tests despite presence of database and tables, and queries.sh/diff still demonstrates they’re populated correctly. Aargh!

[hbar1st]

i retested my solution with the lab and it still works, so I think the lab test just doesn’t like something about how you’re writing this script. (something may not be interacting well here).

So other than the fact that my script doesn’t print anything out to screen (the script you have on github still prints out INSERT outputs), the other difference that may be relevant is how I read the input csv file. I use this:
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS

So maybe try using a pipe instead of redirection.

[dhorowitz001]

Pushed the changes to github, including your version of the cat games.csv line. Still failing everything. What next?

[hbar1st]

i still see the redirection in your script in github. Can you confirm you pushed?

the line I’m seeing is this one:
done < <(tail -n +2 "$GAMES_CSV_FILE") # Skip the header line

[dhorowitz001]

Yes I pushed, the redirection I changed was on the line reading the CSV file (like you said ). I think the way the read block tests for a year value effectively skips the header row, so I could drop that from the line you referenced in your last reply. I’ve done that, and everything performs correctly, tables are populated right, queries diff fine with the output.txt file, and I’m still failing. I’m satisfied that the program is working as intended, but I won’t be able to take the certification exam if this project doesn’t pass the tests, so I really do appreciate you sticking with me to figure it out.

[hbar1st]

your script still prints out a bunch of output. Can you silence it?