7 files changed, 322 insertions, 1 deletions

diff --git a/app/app.py b/app/app.py
new file mode 100644
index 0000000..3bf0337
--- /dev/null
+++ b/app/app.py
@@ -0,0 +1,17 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from fastapi import FastAPI, status
+import app_sessions
+import app_account
+import app_wallet
+
+main = FastAPI()
+
+# Add sessions based on signed cookies.
+main.add_middleware(app_sessions.SessionManager)
+
+# Register our request handlers
+main.include_router(app_account.router)
+main.include_router(app_wallet.router)
diff --git a/app/app_account.py b/app/app_account.py
new file mode 100644
index 0000000..3f4869d
--- /dev/null
+++ b/app/app_account.py
@@ -0,0 +1,87 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from fastapi import APIRouter, Depends, Request, Form, status
+
+from passlib.context import CryptContext
+import re
+
+from embrace.exceptions import IntegrityError
+from psycopg2.errors import UniqueViolation
+
+from app_sessions import UserSession
+from app_database import db_transaction
+
+
+# Password hashing context.
+# Handles proper salting and migration automatically.
+password_ctx = CryptContext(schemes=['bcrypt'], deprecated='auto')
+
+username_pattern = re.compile(r'^[a-zA-Z0-9-_]{4,16}$')
+
+router = APIRouter()
+
+
+@router.get('/')
+def homepage(
+    session: UserSession=Depends(UserSession),
+):
+    if session.is_logged_in():
+        return 'Welcome!'
+
+    return 'Homepage here.'
+
+
+@router.post('/account/register')
+def account_register(
+    session: UserSession=Depends(UserSession),
+    username: str=Form(...),
+    password: str=Form(...),
+):
+    try:
+        if username_pattern.match(username) is None:
+            return 'error: Invalid username format.'
+
+        if not 4 <= len(password) <= 32:
+            return 'error: Invalid password length.'
+
+        hash = password_ctx.hash(password)
+        with db_transaction() as tx:
+            user = tx.create_account(username=username, password_hash=hash)
+
+        session.login(user.id)
+        return 'Account succesfully created. Welcome!'
+
+    except IntegrityError as exception:
+        if isinstance(exception.__cause__, UniqueViolation):
+            return 'error: This username is already taken.'
+        else:
+            raise exception
+
+
+@router.post('/account/login')
+def session_login(
+    session: UserSession=Depends(UserSession),
+    username: str=Form(...),
+    password: str=Form(...),
+):
+    with db_transaction() as tx:
+        user = tx.fetch_account_username(username=username)
+
+    if user is not None and password_ctx.verify(password, user.password_hash):
+        session.login(user.id)
+        return 'Welcome back!'
+    else:
+        return 'error: Invalid credentials.'
+
+
+@router.post('/account/logout')
+def session_logout(
+    session: UserSession=Depends(UserSession),
+):
+    if session.is_logged_in():
+        session.logout()
+        return 'You have been successfully logged out.'
+
+    return 'Nothing to do'
diff --git a/app/app_database.py b/app/app_database.py
new file mode 100644
index 0000000..77f092a
--- /dev/null
+++ b/app/app_database.py
@@ -0,0 +1,43 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from os import environ
+from functools import partial
+from contextlib import contextmanager
+
+import psycopg2
+from psycopg2.extras import NamedTupleCursor
+from psycopg2.extensions import ISOLATION_LEVEL_READ_COMMITTED
+
+import embrace
+from embrace import pool
+
+
+# Connection to the PostgreSQL database server.
+# Using a cursor which exposes result rows as named tuples for convenience.
+# TODO: switch to psycopg3 and use asynchronous queries in handlers.
+db_connect = partial(
+    psycopg2.connect,
+    environ['DATABASE_URL'],
+    cursor_factory=NamedTupleCursor)
+
+db_pool = pool.ConnectionPool(db_connect, limit=4)
+
+
+# Turn our annotated SQL queries into Python functions.
+queries = embrace.module('./sql/', auto_reload=True)
+
+
+@contextmanager
+def db_transaction(isolation_level=ISOLATION_LEVEL_READ_COMMITTED):
+    """
+    Get a connection from the connection pool and begin a transaction with the
+    given isolation level. The transaction is automatically rolled back if an
+    uncaught exception escapes the current context. Otherwise, it is
+    automatically committed when finishing with no error.
+    """
+    with db_pool.connect() as conn:
+        conn.set_isolation_level(isolation_level)
+        with queries.transaction(conn) as tx:
+            yield tx
diff --git a/app/app_sessions.py b/app/app_sessions.py
new file mode 100644
index 0000000..89521fb
--- /dev/null
+++ b/app/app_sessions.py
@@ -0,0 +1,48 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from os import environ
+from functools import partial
+
+from fastapi import Request, HTTPException, status
+from starlette.middleware.sessions import SessionMiddleware
+
+
+# Use a signed-cookie session manager.
+# The default SameSite policy offers some protection against CSRF attacks.
+cookie_key = environ['COOKIE_SECRET_KEY']
+SessionManager = partial(SessionMiddleware, secret_key=cookie_key)
+
+
+class UserSession:
+    """
+    Session decorator for managing user login sessions.
+    """
+
+    def __init__(self, request: Request):
+        self._session = request.session
+
+    def is_logged_in(self) -> bool:
+        return 'user_id' in self._session
+
+    def get_user_id(self) -> int:
+        return self._session['user_id']
+
+    def login(self, user_id: int):
+        self._session['user_id'] = user_id
+
+    def logout(self):
+        self._session.pop('user_id', None)
+
+    @classmethod
+    def authenticated(cls, request: Request) -> 'UserSession':
+        """
+        Returns the authenticated user session or raises an HTTP Exception,
+        dropping the request if the user is not logged in.
+        """
+        session = cls(request)
+        if not session.is_logged_in():
+            raise HTTPException(status_code=status.HTTP_401_UNAUTHORIZED)
+
+        return session
diff --git a/app/app_wallet.py b/app/app_wallet.py
new file mode 100644
index 0000000..9dffbd5
--- /dev/null
+++ b/app/app_wallet.py
@@ -0,0 +1,95 @@
+# UGE / L2 / Intro to relational databases / Python project prototype
+# Author: Pacien TRAN-GIRARD
+# Licence: EUPL-1.2
+
+from decimal import Decimal
+
+from fastapi import APIRouter, Form, Depends, status
+
+from embrace.exceptions import IntegrityError
+from psycopg2.errors import CheckViolation
+from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE
+
+from app_sessions import UserSession
+from app_database import db_transaction
+
+
+router = APIRouter()
+
+
+# TODO: add paging for the transaction history
+@router.get('/wallet')
+def wallet(
+    session: UserSession=Depends(UserSession.authenticated),
+):
+    with db_transaction() as tx:
+        history = tx.fetch_transactions(user_id=session.get_user_id())
+        return list(history)
+
+