IN2090-ukesoppgaver: Uke 11
Programmering med SQL
Du velger selv om du vil løse oppgavene med Python (3) eller Java. Oppgavene denne uken uvider koden skrevet som del av denne ukens videoer, og koden er tilgjengelig fra timeplanen i enkle filer, eller kan lastes ned som en Zip-fil som inneholder SQL-scriptene, Python-koden, Java-koden og Javas avhengigheter.
Merk: Koden kobler seg til din personlige database på IFI, så i
likhet med tilkobling til databasene via psql
må man enten
fjerninnlogge til en IFI-maskin, være på IFIs WIFI eller sitte på en
IFI-maskin for å kunne kjøre koden.
Om du fjerninnlogger kan koden lastes ned i bash
slik:
wget https://www.uio.no/studier/emner/matnat/ifi/IN2090/h21/undervisningsmateriale/webshop.zip
unzip webshop.zip
cd webshop
For å få dataene inn i din personlige database, kjør de to
SQL-scriptene webshop.sql
og data.sql
som
ligger i Zip-filen. Disse kan kjøres ved å logge inn i din personlige
database fra mappen med SQL-filene, og kjøre følgende fra
psql
:
\i webshop.sql
\i data.sql
Python
Dersom du velger Python må programmene du skriver kjøres med Python 3. Dette gjøres på følgende måte:
python3 user_frontend.py
For Python kan det være (om du får en feilmelding om at
psycopg2
ikke finnes) du også må installere biblioteket
psycopg2
, som gjøres ved å kjøre:
pip3 install --user psycopg2-binary
Java
Dersom du velger å løse oppgaven med Java kan det være (om du får en
ClassDefNotFoundError
) at du må ha med filen
postgresql.jar
når du kjører programmet. Filen ligger i
webshop/java
-mappen i Zip-filen som man kan laste ned over.
Du kjører så Java programmet (etter vanlig kompilering med
javac
) slik i Linux/Mac:
java -cp ".:postgresql.jar" UserFrontend
og slik i Windows:
java -cp ".;postgresql.jar" UserFrontend
Oppgave 1 – Forbedret søk
I denne oppgaven skal du forbedre søket etter produkter. Brukeren skal kunne sortere resultatene, og skal kunne velge om sorteringen skal skje på pris eller på produktnavn, i tillegg skal brukeren kunne velge om sorteringen skal skje fra minst til størst eller størst til minst. Til slutt skal brukeren kunne velge en begrensning på hvor mange produkter som skal vises. Merk at om brukeren ikke oppgir noe på spørsmålene, skal ingen sortering/begrensning skje. Et søk skal da kunne se slik ut:
-- USER FRONTEND --
Please choose an option:
1. Register
2. Login
3. Exit
Option: 2
-- LOGIN --
Username: perh
Password: per123
Welcome Per Hansen
-- SEARCH --
Search:
Category: games
How should the results be sorted?
1. by price
2. by name
Sorting: 1
Sort according to:
1. Ascending order
2. Descending order
Ordering: 1
Limit: 2
-- RESULTS --
=== Realm of Battle Skill ===
Product ID: 13
Price: 19.95
Category: games
Description: An MMORPG
=== Star Fights 3 ===
Product ID: 2
Price: 15.39
Category: games
Description: A space simulator
Tips: Merk at man må nå holde styr på hvilke “placeholdere” man har
lagt til i spørringen og ikke (avhengig av brukerens input). I Python
kan det derfor være lurt å bruke navngitte “placeholders” som beskrevet
i ukens videoer. For Java blir dette litt mer komplisert, siden Javas
JDBC ikke støtter navngitte parametre. Her kan man derimot bruke en
teller (f.eks. int count=0
) og så øke denne for hver
“placeholder” man substituerer inne en verdi for. F.eks.~som i koden
her:
String q = "SELECT * FROM ws.products WHERE name LIKE ?";
if (min_price != null)
+= " AND price <= ?";
q if (max_price != null)
+= " AND price >= ?";
q += ";";
q
= connection.prepareStatement(q);
PrepareStatement stmt .setString(1, name);
stmtint count = 2;
if (min_price != null)
.setFloat(count++, min_price);
stmtif (max_price != null)
.setFloat(count++, max_price); stmt
Oppgave 2 – Bestilling av produkter
I denne oppgaven skal du implementere
order_products(conn, username)
(Python) eller
orderProducts(Connection connection, String username)
(Java). Denne metoden blir kalt etter at en bruker har søkt etter
produkter. Denne metoden skal spørre brukeren om hvilket produkt
brukeren ønsker å bestille basert på pid
(produktets ID),
og deretter hvor mange av dette produktet som skal bestilles. Så skal
metoden kjøre en INSERT
-kommando som setter bestillingen
inn i ws.orders
-tabellen.
Merk at man vet brukernavnet til brukeren som skal bestille
produketet, men i ws.orders
skal man legge in brukerens
uid
.
En bestilling kan se slik ut (og er en forsettelse av søket vist
over), hvor brukeren bestiller 5 eksemplarer av spillet med
pid
lik 2, altså Star Fights 3
:
[...]
-- RESULTS --
=== Realm of Battle Skill ===
Product ID: 13
Price: 19.95
Category: games
Description: An MMORPG
=== Star Fights 3 ===
Product ID: 2
Price: 15.39
Category: games
Description: A space simulator
Order (Product ID): 2
How many: 5
Product(s) ordered.
Oppgave 3 – Regninger
I denne og neste oppgave skal vi jobbe med et nytt program,
Administrator
, som er tiltenkt de ansatte i Webshop. Så
last ned følgende Zip-fil
på samme måte som beskrevet over.
Filen inneholder i likhet med forrige et skelett-program. I denne
oppgaven skal du implementere funksjonen make_bills(conn)
(for Python) eller metoden makeBills(Connection)
(for Java)
som genererer regninger for brukere. Funksjonen skal spørre brukeren av
programmet om et brukernavn. Dersom brukeren oppgir et brukernavn skal
programmet så generere en regning for denne brukeren på følgende
format:
Name: <navn>
Address: <adresse>
Total due: <total>
hvor <navn>
er brukerens navn,
<adresse>
er brukerens adresse, og
<total>
er mengden penger brukeren skylder, altså
summen av alle produktene som brukeren har bestilt men ennå ikke betalt
for (husk at en bestilling kan inneholde flere av samme produkt via
num
-kolonnen).
F.eks.~kan output da se slik ut:
-- ADMINISTRATOR --
Please choose an option:
1. Create bills
2. Insert new product
3. Exit
Option: 1
-- BILLS --
Username: test
---Bill---
Name: Tester
Address: Teststreet 1
Total due: 25.0
Dersom brukeren ikke oppgir et brukernavn skal programmet generere en
regning for alle brukere i databasen. F.eks. kan output da se slik ut
(merk ingen input for Username:
):
-- ADMINISTRATOR --
Please choose an option:
1. Create bills
2. Insert new product
3. Exit
Option: 1
-- BILLS --
Username:
---Bill---
Name: Carl Smith
Address: Streetroad 34, 1234 Townplace
Total due: 404.88
---Bill---
Name: Mary Sagan
Address: Placestreet 12B, 4356 Nicetown
Total due: 259.91
---Bill---
Name: Tester
Address: Teststreet 1
Total due: 25.0
Oppgave 4 – Sette inn nye produkter
I denne oppgaven skal du implementere funksjonen
insert_product(conn)
(for Python) eller metoden
insertProducts(Connection)
(for Java) som lar brukeren
sette inn nye produkter i Webshopens produktkatalog (som da er
ws.products
-tabellen). Brukeren skal bli spurt om navn og
pris på produktet, navnet på kategorien produktet tilhører, samt en
mulig beskrivelse.
Programmet skal så sette dette produktet inn i
ws.products
-tabellen. En kjøring kan da se slik ut:
-- ADMINISTRATOR --
Please choose an option:
1. Create bills
2. Insert new product
3. Exit
Option: 2
-- INSERT NEW PRODUCT --
Product name: Juice
Price: 2.3
Category: food
Description: Fresh orange juice
New product Juice inserted.
Etter dette skal det finnes et nytt tuppel i tabellen
ws.products
med name
lik Juice
,
price
lik 2.3
og description
lik
Fresh orange juice
.
Merk at pid
-kolonnen i ws.products
er satt
til SERIAL
, så verdien for denne genereres automatisk. Merk
også at brukeren oppgir navnet på kategorien, men i
ws.products
-tabellen skal vi ha cid
, så man må
altså finne cid
-verdien basert på kategori-navnet fra
brukeren gjennom en SELECT
-spørring (husk at dette kan
gjøres ved å bruke SELECT
i en
INSERT
-spørring).