Salon DataBase Bug

Backend Development
1

Hi, there is quite a bug with this code, because the second time I am entering the same customer it is adding a whitespace on the front of the string, as a result the appointments table is not being populated with the customer_id, can sb help me to understand the issue? Thanks!

image
image1318×536 39.3 KB

second entry for same customer, boom!

image
image948×477 23 KB

code:

#!/bin/bash
PSQL="psql -X --username=freecodecamp --dbname=salon --tuples-only -c"

echo -e "\n~~~~ Our Salon Services ~~~~"
echo -e "Welcome to Salon Wonderhair!\n"

MAIN_MENU(){
  GET_SERVICES=$($PSQL "SELECT * FROM services")
  
  echo "$GET_SERVICES" | while read ID BAR NAME
  do
    echo "$ID) $NAME" 
  done
  read SERVICE_ID_SELECTED
  SERVICE=$($PSQL "SELECT name FROM services WHERE service_id = $SERVICE_ID_SELECTED")
  # if service not found
  if [[ -z $SERVICE ]]
  then
  # wrong entry
  echo "Sorry, we don't have that kind of service right now, enter another option."
  MAIN_MENU
  else
  # check customer records
  CUS_RECORDS
  fi
}


CUS_RECORDS(){
  echo -e "\nWhat is your phone number"
  read CUSTOMER_PHONE
  # check customer name 
  CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone='$CUSTOMER_PHONE'")
  # if number not fount
  if [[ -z $CUSTOMER_NAME ]]
  then
    echo -e "\nWe don't have a record for that phone number, what's your name?"
    read CUSTOMER_NAME
    # update table customers
    INSERT_CUSTOMERS_RESULT=$($PSQL "INSERT INTO customers(phone, name) VALUES('$CUSTOMER_PHONE', '$CUSTOMER_NAME')")
    MAKE_APPOINTMENT  
  else
    MAKE_APPOINTMENT
  fi
}

MAKE_APPOINTMENT(){
    echo -e "\nOk $CUSTOMER_NAME, what time would you like your$SERVICE?"
    read SERVICE_TIME
    # update table appointments
    CUS_ID=$($PSQL "SELECT customer_id FROM customers WHERE name='$CUSTOMER_NAME'")
    INSERT_APPOINTMENT_RESULTS=$($PSQL "INSERT INTO appointments(customer_id, service_id, time) VALUES($CUS_ID,$SERVICE_ID_SELECTED,'$SERVICE_TIME')")
    echo -e "I have put you down for a$SERVICE at $SERVICE_TIME, $CUSTOMER_NAME."


}

MAIN_MENU
2

well I am not sure how to refactor the code but for some reason after first input into appointments this query in the script returns an empty value for the same customer name imputed before, in psql is working fine, so no clue about it.
CUS_ID=$($PSQL “SELECT customer_id FROM customers WHERE name=‘$CUSTOMER_NAME’”)

#!/bin/bash
PSQL="psql -X --username=freecodecamp --dbname=salon --tuples-only -c"

echo -e "\n~~~~ Our Salon Services ~~~~"


MAIN_MENU(){
  if [[ $1 ]]
  then
    echo -e "\n$1"
  fi
  echo -e "Welcome to Salon Wonderhair!\nWhich service would you like?"
  GET_SERVICES=$($PSQL "SELECT * FROM services ORDER BY service_id")
  
  echo "$GET_SERVICES" | while read ID BAR NAME
  do
    echo -e "$ID) $NAME" 
  done
  read SERVICE_ID_SELECTED
  SERVICE=$($PSQL "SELECT name FROM services WHERE service_id = $SERVICE_ID_SELECTED")
  # if service not found
  if [[ -z $SERVICE ]]
  then
    # wrong entry
    echo "Sorry, we don't have that kind of service right now, enter another option."
    MAIN_MENU
  else
    # check customer records
    CUS_RECORDS
  fi
}


CUS_RECORDS(){
  echo -e "\nWhat is your phone number"
  read CUSTOMER_PHONE
  # check customer name for that phone
  CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone='$CUSTOMER_PHONE'")
  # if number not fount
  if [[ -z $CUSTOMER_NAME ]]
  then
    echo -e "\nWe don't have you in our database, what's your name?"
    read CUSTOMER_NAME
    echo -e "\nHello $CUSTOMER_NAME, let's book an appointment."
    # update table customers
    INSERT_CUSTOMERS_RESULT=$($PSQL "INSERT INTO customers(phone, name) VALUES('$CUSTOMER_PHONE', '$CUSTOMER_NAME')")
    MAKE_APPOINTMENT  
  else
    MAKE_APPOINTMENT
  fi
}

MAKE_APPOINTMENT(){
  echo -e "\nOk $CUSTOMER_NAME, what time would you like your $SERVICE?"
  read SERVICE_TIME
  # update table appointments
  CUS_ID=$($PSQL "SELECT customer_id FROM customers WHERE name='$CUSTOMER_NAME'")
  echo $CUS_ID
  if [[ ! -z $CUS_ID ]]
  then
  INSERT_APPOINTMENT_RESULTS=$($PSQL "INSERT INTO appointments(customer_id, service_id, time) VALUES($CUS_ID,$SERVICE_ID_SELECTED,'$SERVICE_TIME')")
  
  
  echo -e "I have put you down for a $SERVICE at $SERVICE_TIME, $CUSTOMER_NAME."
  fi
}

MAIN_MENU
3

here an update of the code, there was some bug in the control flow , then I updated the query to fetch the customer_id from the phone number instead from the customer name, i tested if the trimmed name=name and was t, nevermind.
now the script is doing the job, but the tests dont pass, even the tests that passed earlier. I suck

#!/bin/bash
PSQL="psql -X --username=freecodecamp --dbname=salon --tuples-only -c"

echo -e "\n~~~~~ My Salon ~~~~~\n"
echo -e "Welcome to My Salon, how can I help you?\n"

MAIN_MENU(){
  if [[ $1 ]]
  then
    echo -e "\n$1"
  fi
  
  GET_SERVICES=$($PSQL "SELECT * FROM services ORDER BY service_id")
  
  echo "$GET_SERVICES" | while read ID BAR NAME
  do
    echo -e "$ID) $NAME" 
  done
  read SERVICE_ID_SELECTED
  SERVICE=$($PSQL "SELECT name FROM services WHERE service_id = $SERVICE_ID_SELECTED")
  # if service not found
  if [[ -z $SERVICE ]]
  then
    # wrong entry
    echo "I could not find that service. What would you like today?"
    MAIN_MENU
  else
    # check customer records
    CUS_RECORDS
  fi
}


CUS_RECORDS(){
  echo -e "What's your phone number?"
  read CUSTOMER_PHONE
  # get customer_id
  CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone='$CUSTOMER_PHONE'")
  # check customer name for that phone
  CUSTOMER_NAME=$($PSQL "SELECT name FROM customers WHERE phone='$CUSTOMER_PHONE'")
  # if number not fount
  if [[ -z $CUSTOMER_NAME ]]
  then
    echo -e "I don't have a record for that phone number, what's your name?"
    read CUSTOMER_NAME
    # update table customers
    INSERT_CUSTOMERS_RESULT=$($PSQL "INSERT INTO customers(phone, name) VALUES('$CUSTOMER_PHONE', '$CUSTOMER_NAME')")
  fi
  # get customer_id
  CUSTOMER_ID=$($PSQL "SELECT customer_id FROM customers WHERE phone='$CUSTOMER_PHONE'")
  MAKE_APPOINTMENT  
}

MAKE_APPOINTMENT(){
  
  echo -e "What time would you like your $SERVICE, $CUSTOMER_NAME?"

  read SERVICE_TIME
  # update table appointments
  INSERT_APPOINTMENT_RESULTS=$($PSQL "INSERT INTO appointments(customer_id, service_id, time) VALUES($CUSTOMER_ID,$SERVICE_ID_SELECTED,'$SERVICE_TIME')")
  echo -e "I have put you down for a $SERVICE at $SERVICE_TIME, $CUSTOMER_NAME."
  
}

MAIN_MENU

tests

image
image1467×208 7.79 KB

image
image1577×828 43.9 KB

image
image1455×548 28.8 KB

image
image1445×797 57.7 KB

image

image
image711×215 9.49 KB

4

Could you share your sql file too please? I will load it all up and check it out for you and see if I can get it passing.

5

sure, I see something strange to me , during the create table appointments, that the column time has double quotes, “time” character varying(20)
is that normal?

--
-- PostgreSQL database dump
--

-- Dumped from database version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)
-- Dumped by pg_dump version 12.9 (Ubuntu 12.9-2.pgdg20.04+1)

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 salon;
--
-- Name: salon; Type: DATABASE; Schema: -; Owner: freecodecamp
--

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


ALTER DATABASE salon OWNER TO freecodecamp;

\connect salon

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: appointments; Type: TABLE; Schema: public; Owner: freecodecamp
--

CREATE TABLE public.appointments (
    appointment_id integer NOT NULL,
    customer_id integer,
    service_id integer,
    "time" character varying(20)
);


ALTER TABLE public.appointments OWNER TO freecodecamp;

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

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


ALTER TABLE public.appointments_appointment_id_seq OWNER TO freecodecamp;

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

ALTER SEQUENCE public.appointments_appointment_id_seq OWNED BY public.appointments.appointment_id;


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

CREATE TABLE public.customers (
    customer_id integer NOT NULL,
    phone character varying(20),
    name character varying(50)
);


ALTER TABLE public.customers OWNER TO freecodecamp;

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

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


ALTER TABLE public.customers_customer_id_seq OWNER TO freecodecamp;

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

ALTER SEQUENCE public.customers_customer_id_seq OWNED BY public.customers.customer_id;


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

CREATE TABLE public.services (
    service_id integer NOT NULL,
    name character varying(50)
);


ALTER TABLE public.services OWNER TO freecodecamp;

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

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


ALTER TABLE public.services_service_id_seq OWNER TO freecodecamp;

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

ALTER SEQUENCE public.services_service_id_seq OWNED BY public.services.service_id;


--
-- Name: appointments appointment_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments ALTER COLUMN appointment_id SET DEFAULT nextval('public.appointments_appointment_id_seq'::regclass);


--
-- Name: customers customer_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers ALTER COLUMN customer_id SET DEFAULT nextval('public.customers_customer_id_seq'::regclass);


--
-- Name: services service_id; Type: DEFAULT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.services ALTER COLUMN service_id SET DEFAULT nextval('public.services_service_id_seq'::regclass);


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

INSERT INTO public.appointments VALUES (148, 156, 5, '15:00');
INSERT INTO public.appointments VALUES (115, 135, 1, '10:30');
INSERT INTO public.appointments VALUES (116, 135, 2, '11:00');
INSERT INTO public.appointments VALUES (141, 156, 1, '12:00');


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

INSERT INTO public.customers VALUES (135, '555-555-5555', 'Fabio');
INSERT INTO public.customers VALUES (156, '111-1111-11', 'Bart');


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

INSERT INTO public.services VALUES (2, 'Colouring');
INSERT INTO public.services VALUES (3, 'Styling');
INSERT INTO public.services VALUES (4, 'Washing and Drying');
INSERT INTO public.services VALUES (1, 'Haircut');
INSERT INTO public.services VALUES (5, 'Trim');
INSERT INTO public.services VALUES (6, 'Perm');


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

SELECT pg_catalog.setval('public.appointments_appointment_id_seq', 166, true);


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

SELECT pg_catalog.setval('public.customers_customer_id_seq', 176, true);


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

SELECT pg_catalog.setval('public.services_service_id_seq', 6, true);


--
-- Name: appointments appointments_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_pkey PRIMARY KEY (appointment_id);


--
-- Name: customers customers_phone_key; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_phone_key UNIQUE (phone);


--
-- Name: customers customers_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.customers
    ADD CONSTRAINT customers_pkey PRIMARY KEY (customer_id);


--
-- Name: services services_pkey; Type: CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.services
    ADD CONSTRAINT services_pkey PRIMARY KEY (service_id);


--
-- Name: appointments appointments_customer_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES public.customers(customer_id);


--
-- Name: appointments appointments_service_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: freecodecamp
--

ALTER TABLE ONLY public.appointments
    ADD CONSTRAINT appointments_service_id_fkey FOREIGN KEY (service_id) REFERENCES public.services(service_id);


--
-- PostgreSQL database dump complete
--
6

Ok, increasing the timeout made all tests pass at the first attempt for me, with your code.

With credit to @sethi, enter this command in your terminal:

sed 's/1000/10000/' -i /home/codeally/project/.freeCodeCamp/test/utils.js

This changes the value in the setTimeout() here in the utils.js file from 1 sec to 10 secs:

setTimeout(() => {
    child.kill();
  }, 1000);

When you’ve run this command, hit ‘Run’ on the tests again and they should hopefully all pass.

7

the sed command for replacing that pattern did not work for some reason, but I am interested to know how it should work
but I got very lazy so I changed that manually from the file, does it count? or is a bad practice?

image
image1425×880 59.9 KB

image
image941×842 59 KB

8

The sed command (and others) were covered the kitty ipsum project. It’s just a simple pattern replace.