fcc certification - number_guess - relational database

number_guess.sh

#!/bin/bash

psql=(psql --username=freecodecamp --dbname=number_guess -t --no-align -X)

#echo $("${psql[@]}" "\d")

get_username="Enter your username:"
welcome_msg="Welcome, %s! It looks like this is your first time here."
welcome_back_msg="Welcome back, %s! You have played %d games, and your best game took %d guesses."
game_prompt_start="Guess the secret number between 1 and 1000:"
game_prompt_not_int="That is not an integer, guess again:"
game_prompt_lower="It's lower than that, guess again:"
game_prompt_higher="It's higher than that, guess again:"
game_prompt_invalid_input="That is not an integer, guess again:"
game_prompt_correct_guess="You guessed it in %d tries. The secret number was %d. Nice job!"
select_sql="select games_played, best_guess from games where username = :'username';"
upsert_sql=$(cat << SQL
insert into games
  (username, games_played, best_guess)
  values
  (:'username', :games_played, :best_guess)
  on conflict (username) do
  update set
    username = EXCLUDED.username,
    games_played = EXCLUDED.games_played,
    best_guess = EXCLUDED.best_guess
;
SQL
)

declare -i secret
secret=$(( (RANDOM % 1000) + 1 ))
#echo $secret

### start

printf "${get_username}\n"
read -r username
if [[ "${#username}" -eq 22 ]]; then
  echo "nope. shouldn't be 22."
  exit 1
fi

declare -i best_guess

select_result=$("${psql[@]}" -v username="$username" <<SQL
${select_sql}
SQL
)
IFS='|' read -r games_played best_guess <<< $select_result

if [[ -n $select_result ]]; then
  printf "${welcome_back_msg}\n" "$username" "$games_played" "$best_guess"
else
  best_guess="99999"
  printf "${welcome_msg}\n" "$username"
fi

user_guess=""
printf "${game_prompt_start}\n"

declare -i guess_count=0

while true; do
  read -r user_guess
  (( ++guess_count ))
  if [[ ! "$user_guess" =~ ^[0-9]+$ ]]; then
    printf "${game_prompt_not_int}\n"
    continue
  fi
  if (( user_guess == secret )); then
    printf "${game_prompt_correct_guess}\n" "$guess_count" "$secret"
    break
  fi
  if (( user_guess < secret )); then
    printf "${game_prompt_higher}\n"
  elif (( user_guess > secret )); then
    printf "${game_prompt_lower}\n"
  fi
done


(( ++games_played ))
if (( guess_count < best_guess )); then
  best_guess="$guess_count"
fi


"${psql[@]}" -v username="$username" -v games_played="$games_played" -v best_guess="$best_guess" <<< ${upsert_sql} > /dev/null

###

number_guess.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)
-- Dumped by pg_dump version 12.22 (Ubuntu 12.22-0ubuntu0.20.04.4)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP DATABASE number_guess;
--
-- Name: number_guess; Type: DATABASE; Schema: -; Owner: freecodecamp
--

CREATE DATABASE number_guess WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C.UTF-8' LC_CTYPE = 'C.UTF-8';


ALTER DATABASE number_guess OWNER TO freecodecamp;

\connect number_guess

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: games; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.games (
    id integer NOT NULL,
    username character varying(22) NOT NULL,
    games_played integer,
    best_guess integer
);


ALTER TABLE public.games OWNER TO freecodecamp;

--
-- Name: games_id_seq; Type: SEQUENCE; Schema: public; Owner: freecodecamp
--

CREATE SEQUENCE public.games_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.games_id_seq OWNER TO freecodecamp;

--
-- Name: games_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: freecodecamp
--

ALTER SEQUENCE public.games_id_seq OWNED BY public.games.id;


--
-- Name: games id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games ALTER COLUMN id SET DEFAULT nextval('public.games_id_seq'::regclass);


--
-- Data for Name: games; Type: TABLE DATA; Schema: public; Owner: freecodecamp
--

INSERT INTO public.games VALUES (20, 'aaa', 1, 12);
INSERT INTO public.games VALUES (23, 'user_1782992413376', 2, 610);
INSERT INTO public.games VALUES (21, 'user_1782992413377', 5, 110);
INSERT INTO public.games VALUES (30, 'user_1782992428777', 2, 292);
INSERT INTO public.games VALUES (28, 'user_1782992428778', 5, 247);


--
-- Name: games_id_seq; Type: SEQUENCE SET; Schema: public; Owner: freecodecamp
--

SELECT pg_catalog.setval('public.games_id_seq', 34, true);


--
-- Name: games games_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_pkey PRIMARY KEY (id);


--
-- Name: games games_username_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.games
    ADD CONSTRAINT games_username_key UNIQUE (username);


--
-- PostgreSQL database dump complete
--