Commits: 13
Implement user registration and password hashing
Also create Token class for type safety.
index 2f6297e..cde3810 100644
--- a/flake.nix
+++ b/flake.nix
@@ -37,6 +37,7 @@
= venv.requirements = ''
= fastapi[standard]
= python-lsp-server[all]
+ passlib[bcrypt]
= '';
= };
=index 9a65d97..a254331 100644
--- a/main.py
+++ b/main.py
@@ -10,6 +10,7 @@ from pydantic import BaseModel
=from typing import Optional, Annotated
=import sqlite3
=import logging
+from passlib.context import CryptContext
=
=app = FastAPI(
= title="JaaS",
@@ -18,6 +19,7 @@ app = FastAPI(
=
=logger = logging.getLogger("uvicorn.error")
=security = OAuth2PasswordBearer(tokenUrl="token")
+password_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
=
=
=class Joke(BaseModel):
@@ -35,6 +37,11 @@ class Joker(BaseModel):
= name: str
=
=
+class Token(BaseModel):
+ access_token: str
+ token_type: str = "bearer"
+
+
=async def get_current_user(token: Annotated[str, Depends(security)]):
= """Provide the current user if logged in, otherwise err.
=
@@ -66,7 +73,7 @@ async def get_current_user(token: Annotated[str, Depends(security)]):
=
=
=@app.post("/token")
-async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]):
+async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]) -> Token:
= """Get a token based on username and password."""
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
@@ -74,13 +81,14 @@ async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]):
= joker = cursor.execute("""
= Select
= joker.id,
- joker.name
+ joker.name,
+ joker.password
= from
= joker
= where
- joker.name = :username and joker.password = :password
+ joker.name = :username
= ;
- """, {"username": form.username, "password": form.password}).fetchone()
+ """, {"username": form.username}).fetchone()
=
= if joker is None:
= raise HTTPException(
@@ -88,11 +96,44 @@ async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]):
= detail="Incorrect username or password"
= )
=
- token = {"access_token": joker["name"], "token_type": "bearer"}
+ if not password_context.verify(form.password, joker["password"]):
+ raise HTTPException(
+ status_code=400,
+ detail="Incorrect username or password"
+ )
+
=
- return token
+ # FIXME: Use JWT instead of username
+ return Token(access_token=joker["name"])
=
=
+# TODO: Validation: password and name length, not blank, etc.
+class JokerRegistration(BaseModel):
+ name: str
+ password: str
+
+
+@app.post("/jokers/")
+def register_joker(registration: JokerRegistration) -> Joker:
+ # TODO: Hash passwords, write to DB, respond with a new user?
+ password = password_context.hash(registration.password)
+ with sqlite3.connect("jokes.db") as db:
+ db.row_factory = sqlite3.Row
+ cursor = db.cursor()
+ joker = cursor.execute("""
+ Insert into joker (
+ name,
+ password
+ ) values (
+ :name,
+ :password
+ )
+ returning id, name
+ ;
+ """, {"name": registration.name, "password": password}).fetchone()
+
+ return Joker(**joker)
+
=@app.get("/me")
=def get_current_joker(user: Annotated[Joker, Depends(get_current_user)]) -> Joker:
= """Give the user information about them. Requires authentication."""Use nixpkgs LSP instead of PIP
The one from PIP was terribly slow and sometimes hanged my Emacs. So far native seems to work better.
index cde3810..31c37fb 100644
--- a/flake.nix
+++ b/flake.nix
@@ -29,6 +29,9 @@
= # https://devenv.sh/reference/options/
= packages = [
= pkgs.litecli
+ pkgs.python313Packages.python-lsp-server
+ pkgs.python313Packages.python-lsp-ruff
+ pkgs.python313Packages.pylsp-rope
= ];
=
= languages.python = {
@@ -36,7 +39,6 @@
= venv.enable = true;
= venv.requirements = ''
= fastapi[standard]
- python-lsp-server[all]
= passlib[bcrypt]
= '';
= };Implement JWT based authentication
index 31c37fb..8be30e7 100644
--- a/flake.nix
+++ b/flake.nix
@@ -39,6 +39,7 @@
= venv.enable = true;
= venv.requirements = ''
= fastapi[standard]
+ pyjwt
= passlib[bcrypt]
= '';
= };index a254331..7a1f934 100644
--- a/main.py
+++ b/main.py
@@ -4,6 +4,8 @@ A sample REST API service.
=You can use it to teach concepts of REST.
="""
=
+import jwt
+from datetime import datetime, timedelta, timezone
=from fastapi import FastAPI, HTTPException, Response, Depends
=from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm
=from pydantic import BaseModel
@@ -12,6 +14,10 @@ import sqlite3
=import logging
=from passlib.context import CryptContext
=
+JWT_SECRET_KEY = "036ec75175141fceea455d8c68d87a5d97e1abcef24b8569468e191701219ede"
+JWT_ALGORITHM="HS256"
+JWT_TTL_MINUTES=30
+
=app = FastAPI(
= title="JaaS",
= summary="Joke as a service"
@@ -48,6 +54,10 @@ async def get_current_user(token: Annotated[str, Depends(security)]):
= It's a dependency that can be used on endpoints that require
= authentication.
= """
+
+ token_data = jwt.decode(token, JWT_SECRET_KEY, algorithms=[JWT_ALGORITHM])
+ username = token_data.get("sub")
+
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
@@ -60,7 +70,7 @@ async def get_current_user(token: Annotated[str, Depends(security)]):
= where
= joker.name = :username
= ;
- """, {"username": token}).fetchone()
+ """, {"username": username}).fetchone()
=
= if joker is None:
= raise HTTPException(
@@ -102,9 +112,13 @@ async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]) ->
= detail="Incorrect username or password"
= )
=
-
- # FIXME: Use JWT instead of username
- return Token(access_token=joker["name"])
+ ttl = timedelta(minutes=JWT_TTL_MINUTES)
+ exp = datetime.now(timezone.utc) + ttl
+ encoded = jwt.encode({
+ "exp": exp,
+ "sub": joker["name"]
+ }, JWT_SECRET_KEY, algorithm=JWT_ALGORITHM)
+ return Token(access_token=encoded)
=
=
=# TODO: Validation: password and name length, not blank, etc.
@@ -134,6 +148,7 @@ def register_joker(registration: JokerRegistration) -> Joker:
=
= return Joker(**joker)
=
+
=@app.get("/me")
=def get_current_joker(user: Annotated[Joker, Depends(get_current_user)]) -> Joker:
= """Give the user information about them. Requires authentication."""Explicitly handle duplicate username on POST /users/
Also give appropriate, 201 status code.
index 7a1f934..ffb0e31 100644
--- a/main.py
+++ b/main.py
@@ -127,26 +127,33 @@ class JokerRegistration(BaseModel):
= password: str
=
=
-@app.post("/jokers/")
+@app.post("/jokers/", status_code=201)
=def register_joker(registration: JokerRegistration) -> Joker:
= # TODO: Hash passwords, write to DB, respond with a new user?
= password = password_context.hash(registration.password)
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- joker = cursor.execute("""
- Insert into joker (
- name,
- password
- ) values (
- :name,
- :password
- )
- returning id, name
- ;
- """, {"name": registration.name, "password": password}).fetchone()
+ try:
+ joker = cursor.execute("""
+ Insert into joker (
+ name,
+ password
+ ) values (
+ :name,
+ :password
+ )
+ returning id, name
+ ;
+ """, {"name": registration.name, "password": password}).fetchone()
=
- return Joker(**joker)
+ return Joker(**joker)
+
+ except sqlite3.IntegrityError as error:
+ if error.args[0] == "UNIQUE constraint failed: joker.name":
+ raise HTTPException(status_code=409, detail=f"Name already taken: {registration.name}")
+ else:
+ raise error
=
=
=@app.get("/me")Require authentication to POST /jokes/
Also don't require useless id of a new joke. It was never really taken into account anyway, but needed to be there for validation. In the Joke resource returned from the API on the other hand id is no longer optional.
index ffb0e31..b2c2886 100644
--- a/main.py
+++ b/main.py
@@ -31,7 +31,7 @@ password_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
=class Joke(BaseModel):
= """A joke."""
=
- id: Optional[int]
+ id: int
= text: str
= author: str
=
@@ -228,29 +228,23 @@ def get_joke(id: int) -> Joke:
= return Joke(**joke)
=
=
+class JokeSubmission(BaseModel):
+ """A new joke to be stored.
+
+ The author is implicit (the endpoint requires authentication).
+
+ The id will be assigned by a database.
+ """
+
+ text: str
+
+
=@app.post("/jokes", status_code=201)
-def new_joke(joke: Joke) -> Joke:
+def new_joke(joke: JokeSubmission, joker: Annotated[Joker, Depends(get_current_user)]) -> Joke:
= """Write a new joke."""
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- author = cursor.execute("""
- Select
- id
- from
- joker
- where
- name = ?
- ;
- """, (joke.author,)).fetchone()
-
- if author is None:
- logger.debug(f"Specified author not found {joke.author}")
- raise HTTPException(
- status_code=422,
- detail=f"The specified joker is not known: {joke.author}"
- )
-
= row = cursor.execute("""
= Insert into joke (
= text,
@@ -259,7 +253,7 @@ def new_joke(joke: Joke) -> Joke:
= values (:text, :author)
= returning id
= ;
- """, {"text": joke.text, "author": author["id"]}).fetchone()
+ """, {"text": joke.text, "author": joker.id}).fetchone()
=
= return Response(
= headers={Make the Joke response contain joker id
as well as name. It's useful, since there is no way to find joker by name only.
index b2c2886..7227d2c 100644
--- a/main.py
+++ b/main.py
@@ -33,7 +33,8 @@ class Joke(BaseModel):
=
= id: int
= text: str
- author: str
+ author_id: int
+ author_name: str
=
=
=class Joker(BaseModel):
@@ -194,7 +195,8 @@ def list_jokes() -> list[Joke]:
= Select
= joke.id,
= joke.text,
- joker.name as author
+ joker.id as author_id,
+ joker.name as author_name
= from
= joke
= join joker on joke.author = joker.id
@@ -213,7 +215,8 @@ def get_joke(id: int) -> Joke:
= Select
= joke.id,
= joke.text,
- joker.name as author
+ joker.id as author_id,
+ joker.name as author_name
= from
= joke
= join joker on joke.author = joker.id
@@ -240,7 +243,7 @@ class JokeSubmission(BaseModel):
=
=
=@app.post("/jokes", status_code=201)
-def new_joke(joke: JokeSubmission, joker: Annotated[Joker, Depends(get_current_user)]) -> Joke:
+def new_joke(joke: JokeSubmission, joker: Annotated[Joker, Depends(get_current_user)]) -> Response:
= """Write a new joke."""
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.RowFormat code with ruff
index 8be30e7..3847bf1 100644
--- a/flake.nix
+++ b/flake.nix
@@ -29,6 +29,7 @@
= # https://devenv.sh/reference/options/
= packages = [
= pkgs.litecli
+ pkgs.ruff
= pkgs.python313Packages.python-lsp-server
= pkgs.python313Packages.python-lsp-ruff
= pkgs.python313Packages.pylsp-ropeindex 7227d2c..139326e 100644
--- a/main.py
+++ b/main.py
@@ -4,24 +4,23 @@ A sample REST API service.
=You can use it to teach concepts of REST.
="""
=
-import jwt
+import logging
+import sqlite3
=from datetime import datetime, timedelta, timezone
+from typing import Optional, Annotated
+
+import jwt
=from fastapi import FastAPI, HTTPException, Response, Depends
=from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm
-from pydantic import BaseModel
-from typing import Optional, Annotated
-import sqlite3
-import logging
=from passlib.context import CryptContext
+from pydantic import BaseModel
+
=
=JWT_SECRET_KEY = "036ec75175141fceea455d8c68d87a5d97e1abcef24b8569468e191701219ede"
-JWT_ALGORITHM="HS256"
-JWT_TTL_MINUTES=30
+JWT_ALGORITHM = "HS256"
+JWT_TTL_MINUTES = 30
=
-app = FastAPI(
- title="JaaS",
- summary="Joke as a service"
-)
+app = FastAPI(title="JaaS", summary="Joke as a service")
=
=logger = logging.getLogger("uvicorn.error")
=security = OAuth2PasswordBearer(tokenUrl="token")
@@ -62,16 +61,19 @@ async def get_current_user(token: Annotated[str, Depends(security)]):
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- joker = cursor.execute("""
- Select
- joker.id,
- joker.name
- from
- joker
- where
- joker.name = :username
- ;
- """, {"username": username}).fetchone()
+ joker = cursor.execute(
+ """
+ Select
+ joker.id,
+ joker.name
+ from
+ joker
+ where
+ joker.name = :username
+ ;
+ """,
+ {"username": username},
+ ).fetchone()
=
= if joker is None:
= raise HTTPException(
@@ -89,36 +91,36 @@ async def authenticate(form: Annotated[OAuth2PasswordRequestForm, Depends()]) ->
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- joker = cursor.execute("""
- Select
- joker.id,
- joker.name,
- joker.password
- from
- joker
- where
- joker.name = :username
- ;
- """, {"username": form.username}).fetchone()
+ joker = cursor.execute(
+ """
+ Select
+ joker.id,
+ joker.name,
+ joker.password
+ from
+ joker
+ where
+ joker.name = :username
+ ;
+ """,
+ {"username": form.username},
+ ).fetchone()
=
= if joker is None:
= raise HTTPException(
- status_code=400,
- detail="Incorrect username or password"
+ status_code=400, detail="Incorrect username or password"
= )
=
= if not password_context.verify(form.password, joker["password"]):
= raise HTTPException(
- status_code=400,
- detail="Incorrect username or password"
+ status_code=400, detail="Incorrect username or password"
= )
=
= ttl = timedelta(minutes=JWT_TTL_MINUTES)
= exp = datetime.now(timezone.utc) + ttl
- encoded = jwt.encode({
- "exp": exp,
- "sub": joker["name"]
- }, JWT_SECRET_KEY, algorithm=JWT_ALGORITHM)
+ encoded = jwt.encode(
+ {"exp": exp, "sub": joker["name"]}, JWT_SECRET_KEY, algorithm=JWT_ALGORITHM
+ )
= return Token(access_token=encoded)
=
=
@@ -136,23 +138,28 @@ def register_joker(registration: JokerRegistration) -> Joker:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
= try:
- joker = cursor.execute("""
- Insert into joker (
- name,
- password
- ) values (
- :name,
- :password
- )
- returning id, name
- ;
- """, {"name": registration.name, "password": password}).fetchone()
+ joker = cursor.execute(
+ """
+ Insert into joker (
+ name,
+ password
+ ) values (
+ :name,
+ :password
+ )
+ returning id, name
+ ;
+ """,
+ {"name": registration.name, "password": password},
+ ).fetchone()
=
= return Joker(**joker)
=
= except sqlite3.IntegrityError as error:
= if error.args[0] == "UNIQUE constraint failed: joker.name":
- raise HTTPException(status_code=409, detail=f"Name already taken: {registration.name}")
+ raise HTTPException(
+ status_code=409, detail=f"Name already taken: {registration.name}"
+ )
= else:
= raise error
=
@@ -211,19 +218,22 @@ def get_joke(id: int) -> Joke:
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- joke = cursor.execute("""
- Select
- joke.id,
- joke.text,
- joker.id as author_id,
- joker.name as author_name
- from
- joke
- join joker on joke.author = joker.id
- where
- joke.id = :id
- ;
- """, {"id": id}).fetchone()
+ joke = cursor.execute(
+ """
+ Select
+ joke.id,
+ joke.text,
+ joker.id as author_id,
+ joker.name as author_name
+ from
+ joke
+ join joker on joke.author = joker.id
+ where
+ joke.id = :id
+ ;
+ """,
+ {"id": id},
+ ).fetchone()
=
= if joke is None:
= raise HTTPException(status_code=404)
@@ -243,24 +253,24 @@ class JokeSubmission(BaseModel):
=
=
=@app.post("/jokes", status_code=201)
-def new_joke(joke: JokeSubmission, joker: Annotated[Joker, Depends(get_current_user)]) -> Response:
+def new_joke(
+ joke: JokeSubmission, joker: Annotated[Joker, Depends(get_current_user)]
+) -> Response:
= """Write a new joke."""
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
= cursor = db.cursor()
- row = cursor.execute("""
- Insert into joke (
- text,
- author
- )
- values (:text, :author)
- returning id
- ;
- """, {"text": joke.text, "author": joker.id}).fetchone()
+ row = cursor.execute(
+ """
+ Insert into joke (
+ text,
+ author
+ )
+ values (:text, :author)
+ returning id
+ ;
+ """,
+ {"text": joke.text, "author": joker.id},
+ ).fetchone()
=
- return Response(
- headers={
- "location": f"/jokes/{row["id"]}"
- },
- status_code=201
- )
+ return Response(headers={"location": f"/jokes/{row['id']}"}, status_code=201)Extract requirements.txt from flake.nix
index 3847bf1..25fe85f 100644
--- a/flake.nix
+++ b/flake.nix
@@ -38,11 +38,7 @@
= languages.python = {
= enable = true;
= venv.enable = true;
- venv.requirements = ''
- fastapi[standard]
- pyjwt
- passlib[bcrypt]
- '';
+ venv.requirements = ./requirements.txt;
= };
=
= processes = {new file mode 100644
index 0000000..f411225
--- /dev/null
+++ b/requirements.txt
@@ -0,0 +1,3 @@
+fastapi[standard]
+pyjwt
+passlib[bcrypt]Implement laughing at jokes
Laughs are like likes. You put a laugh on a joke. It's idempotent - subsequent laughs do not change anything.
index 3f5e347..2e9316a 100644
--- a/init.sql
+++ b/init.sql
@@ -1,3 +1,5 @@
+Pragma foreign_keys = on;
+
=Create table if not exists "joker" (
= id integer primary key autoincrement,
= name text not null unique,
@@ -8,7 +10,9 @@ Create table if not exists "joker" (
=Create table if not exists "joke" (
= id integer primary key autoincrement,
= text text not null,
- author integer not null references "joker.id"
+ author integer not null,
+
+ foreign key (author) references joker (id)
=);
=
=
@@ -23,4 +27,14 @@ Insert into joke ("author", "text") values
=(1, "Why did the scarecrow get promoted? He was outstanding in his field."),
=(2, "I used to be a baker, but I couldn’t make enough dough."),
=(2, "What’s a snake’s favorite subject in school? Hiss‑tory."),
-(3, "What's big? Your nose.");
+(3, "Whats big? Your nose.");
+
+
+Create table if not exists "laugh" (
+ joke integer not null,
+ joker integer not null,
+
+ primary key (joke, joker),
+ foreign key (joke) references joke (id),
+ foreign key (joker) references joker (id)
+);index 139326e..a181801 100644
--- a/main.py
+++ b/main.py
@@ -197,6 +197,7 @@ def list_jokes() -> list[Joke]:
= """List all jokes."""
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
+ db.execute("Pragma foreign_keys = on")
= cursor = db.cursor()
= cursor.execute("""
= Select
@@ -240,6 +241,46 @@ def get_joke(id: int) -> Joke:
=
= return Joke(**joke)
=
+@app.put("/jokes/{id}/laugh", status_code=201)
+def laugh(
+ id: int,
+ joker: Annotated[Joker, Depends(get_current_user)]
+) -> Response:
+ """Add a laugh to a funny joke."""
+ with sqlite3.connect("jokes.db") as db:
+ db.row_factory = sqlite3.Row
+ db.execute("Pragma foreign_keys = on")
+ cursor = db.cursor()
+
+ try:
+ joke = cursor.execute(
+ """
+ Insert into laugh (
+ joke,
+ joker
+ ) values (
+ :joke,
+ :joker
+ )
+ ;
+ """,
+ {"joke": id, "joker": joker.id},
+ ).fetchone()
+
+ return Response(status_code=201)
+
+ except sqlite3.IntegrityError as error:
+ if error.args[0] == "UNIQUE constraint failed: laugh.joke, laugh.joker":
+ raise HTTPException(
+ status_code=200, detail=f"You've already laughed at this joke"
+ )
+ elif error.args[0] == "FOREIGN KEY constraint failed":
+ raise HTTPException(
+ status_code=404, detail=f"Probably no such joke. Or maybe you don't exist. Who can tell?"
+ )
+ else:
+ raise error
+
=
=class JokeSubmission(BaseModel):
= """A new joke to be stored.Implement unlaughing
index a181801..277bf45 100644
--- a/main.py
+++ b/main.py
@@ -282,6 +282,32 @@ def laugh(
= raise error
=
=
+@app.delete("/jokes/{id}/laugh", status_code=204)
+def unlaugh(
+ id: int,
+ joker: Annotated[Joker, Depends(get_current_user)]
+) -> Response:
+ """Let it be known that the joke is not funny anymore."""
+ with sqlite3.connect("jokes.db") as db:
+ db.row_factory = sqlite3.Row
+ db.execute("Pragma foreign_keys = on")
+ cursor = db.cursor()
+
+ # TODO: Handle a 404 type of situation
+ joke = cursor.execute(
+ """
+ Delete from laugh
+ where
+ joke = :joke and
+ joker = :joker
+ ;
+ """,
+ {"joke": id, "joker": joker.id},
+ ).fetchone()
+
+ return Response(status_code=204)
+
+
=class JokeSubmission(BaseModel):
= """A new joke to be stored.
=A Joke will show laughs count
index 277bf45..ea67f2f 100644
--- a/main.py
+++ b/main.py
@@ -34,6 +34,7 @@ class Joke(BaseModel):
= text: str
= author_id: int
= author_name: str
+ laughs: int
=
=
=class Joker(BaseModel):
@@ -180,7 +181,9 @@ def get_random_joke() -> Joke:
= Select
= joke.id,
= joke.text,
- joker.name as author
+ joker.id as author_id,
+ joker.name as author_name,
+ (select count(*) from laugh where laugh.joke = joke.id) as laughs
= from
= joke
= join joker on joke.author = joker.id
@@ -204,7 +207,8 @@ def list_jokes() -> list[Joke]:
= joke.id,
= joke.text,
= joker.id as author_id,
- joker.name as author_name
+ joker.name as author_name,
+ (select count(*) from laugh where laugh.joke = joke.id) as laughs
= from
= joke
= join joker on joke.author = joker.id
@@ -225,7 +229,8 @@ def get_joke(id: int) -> Joke:
= joke.id,
= joke.text,
= joker.id as author_id,
- joker.name as author_name
+ joker.name as author_name,
+ (select count(*) from laugh where laugh.joke = joke.id) as laughs
= from
= joke
= join joker on joke.author = joker.idLet jokers see what they laugh at
index ea67f2f..cc542d7 100644
--- a/main.py
+++ b/main.py
@@ -170,6 +170,33 @@ def get_current_joker(user: Annotated[Joker, Depends(get_current_user)]) -> Joke
= """Give the user information about them. Requires authentication."""
= return user
=
+@app.get("/me/laughs")
+def get_laughs(user: Annotated[Joker, Depends(get_current_user)]) -> list[Joke]:
+ """List of jokes user laughed at."""
+ """List all jokes."""
+ with sqlite3.connect("jokes.db") as db:
+ db.row_factory = sqlite3.Row
+ db.execute("Pragma foreign_keys = on")
+ cursor = db.cursor()
+ cursor.execute(
+ """
+ Select
+ joke.id,
+ joke.text,
+ joker.id as author_id,
+ joker.name as author_name,
+ (select count(*) from laugh where laugh.joke = joke.id) as laughs
+ from
+ joke
+ join joker on joke.author = joker.id
+ join laugh on joke.id = laugh.joke
+ where
+ laugh.joker = :joker
+ ;
+ """,
+ {"joker": user.id}
+ )
+ return [Joke(**row) for row in cursor.fetchall()]
=
=@app.get("/")
=def get_random_joke() -> Joke:Let users sort jokes via query parameters
index cc542d7..272b8c3 100644
--- a/main.py
+++ b/main.py
@@ -223,24 +223,38 @@ def get_random_joke() -> Joke:
=
=
=@app.get("/jokes")
-def list_jokes() -> list[Joke]:
+def list_jokes(sort: str = "laughs", descending: bool = True) -> list[Joke]:
= """List all jokes."""
+ logger.info(f"{sort} {descending}")
+
+ sortable_columns = ["laughs", "author_name", "text"]
+ if sort not in sortable_columns:
+ raise HTTPException(
+ status_code=400,
+ detail=f"Invalid sort parameter: '{sort}'. Acceptable values are {sortable_columns}"
+ )
+
= with sqlite3.connect("jokes.db") as db:
= db.row_factory = sqlite3.Row
+ db.set_trace_callback(logger.info)
= db.execute("Pragma foreign_keys = on")
= cursor = db.cursor()
- cursor.execute("""
- Select
- joke.id,
- joke.text,
- joker.id as author_id,
- joker.name as author_name,
- (select count(*) from laugh where laugh.joke = joke.id) as laughs
- from
- joke
- join joker on joke.author = joker.id
- ;
- """)
+ direction = "desc" if descending else "asc"
+ cursor.execute(
+ f"""
+ Select
+ joke.id,
+ joke.text,
+ joker.id as author_id,
+ joker.name as author_name,
+ (select count(*) from laugh where laugh.joke = joke.id) as laughs
+ from
+ joke
+ join joker on joke.author = joker.id
+ order by {sort} {direction}
+ ;
+ """
+ )
= return [Joke(**row) for row in cursor.fetchall()]
=
=