following script is doing the job but it is taking ages, I think because of the nested ifs inside the loop. the issue is that the tasks are not being validated after pushing the run button
see code below
#! /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.
#truncate tables from database
echo $($PSQL "TRUNCATE teams, games")
#insert data into table teams
#looping over file to
cat games.csv | while IFS="," read YEAR ROUND WINNER OPPONENT WINNER_GOALS OPPONENT_GOALS
do
if [[ $YEAR != "year" ]]
then
#get team_id from winner
TEAM_ID1=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER'")
#if not exist then insert its name into teams
if [[ -z $TEAM_ID1 ]]
then
INSERT_WINNER_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$WINNER')")
#check what was inserted
if [[ $INSERT_WINNER_RESULT == "INSERT 0 1" ]]
then
echo inserted into teams, $WINNER
fi
fi
#get team_id from opponent
TEAM_ID2=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT'")
#if not exist then insert its name into teams
if [[ -z $TEAM_ID2 ]]
then
INSERT_OPPONENT_RESULT=$($PSQL "INSERT INTO teams(name) VALUES('$OPPONENT')")
#check what was inserted
if [[ $INSERT_OPPONENT_RESULT == "INSERT 0 1" ]]
then
echo inserted into teams, $OPPONENT
fi
fi
#get new winner and opponent ids
WINNER_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$WINNER' ")
OPPONENT_ID=$($PSQL "SELECT team_id FROM teams WHERE name='$OPPONENT' ")
if [[ -n $WINNER_ID || -n $OPPONENT_ID ]]
then
#insert data into table games
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)")
if [[ $INSERT_GAMES_RESULT == "INSERT 0 1" ]]
then
echo inserted into games, $WINNER $OPPONENT
fi
fi
fi
done
