From 7f11aa00673b0f77523db44969699c54289ace5b Mon Sep 17 00:00:00 2001
From: pacien
Date: Sun, 25 Jul 2021 21:53:59 +0200
Subject: app: working web prototype

---
 app/app.py          | 17 ++++++++++
 app/app_account.py  | 87 ++++++++++++++++++++++++++++++++++++++++++++++++
 app/app_database.py | 43 ++++++++++++++++++++++++
 app/app_sessions.py | 48 +++++++++++++++++++++++++++
 app/app_wallet.py   | 95 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 readme.md           | 24 ++++++++++++++
 sql/queries.sql     |  9 ++++-
 7 files changed, 322 insertions(+), 1 deletion(-)
 create mode 100644 app/app.py
 create mode 100644 app/app_account.py
 create mode 100644 app/app_database.py
 create mode 100644 app/app_sessions.py
 create mode 100644 app/app_wallet.py

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)
+
+
+@router.post('/wallet/transfer')
+def wallet_transfer(
+    session: UserSession=Depends(UserSession.authenticated),
+    recipient: str=Form(...),
+    amount: Decimal=Form(...),
+):
+    if amount <= 0:
+        return 'error: Invalid transaction amount.'
+
+    try:
+        with db_transaction(ISOLATION_LEVEL_SERIALIZABLE) as tx:
+            recipient_user = tx.fetch_account_username(username=recipient)
+            if recipient_user is None:
+                raise LookupError('Could not find recipient')
+
+            tx.transfer(
+                from_user_id=session.get_user_id(),
+                to_user_id=recipient_user.id,
+                amount=amount,
+                fee=amount * Decimal(0.10))
+
+        return 'Your business is appreciated.'
+
+    except LookupError as exception:
+        return 'error: ' + str(exception)
+
+    except IntegrityError as exception:
+        if isinstance(exception.__cause__, CheckViolation):
+            return 'error: Insufficient funds.'
+        else:
+            raise exception
+
+
+@router.post('/wallet/deposit')
+def wallet_deposit(
+    session: UserSession=Depends(UserSession.authenticated),
+    amount: Decimal=Form(...),
+):
+    if amount <= 0:
+        return 'error: Invalid transaction amount.'
+
+    with db_transaction(ISOLATION_LEVEL_SERIALIZABLE) as tx:
+        tx.deposit(user_id=session.get_user_id(), amount=amount)
+
+    return 'Your business is appreciated.'
+
+
+@router.post('/wallet/withdraw')
+def wallet_withdraw(
+    session: UserSession=Depends(UserSession.authenticated),
+    amount: Decimal=Form(...),
+):
+    if amount <= 0:
+        return 'error: Invalid transaction amount.'
+
+    try:
+        with db_transaction(ISOLATION_LEVEL_SERIALIZABLE) as tx:
+            tx.withdraw(user_id=session.get_user_id(), amount=amount)
+
+        return 'Annnnnd... It\'s gone.'
+
+    except IntegrityError as exception:
+        if isinstance(exception.__cause__, CheckViolation):
+            return 'error: Insufficient funds.'
+        else:
+            raise exception
diff --git a/readme.md b/readme.md
index 7500744..b5844f2 100644
--- a/readme.md
+++ b/readme.md
@@ -115,6 +115,13 @@ of annotated raw SQL queries, exposing them as callable Python functions.
 * `./sql/`
   * `tables.sql`: database initialisation statements
   * `queries.sql`: annotated SQL queries (from which the DAO is derived)
+
+* `./app/`
+  * `app_database.py`: database connection pool and transaction helper
+  * `app_sessions.py`: (signed cookies) session data management helpers
+  * `app_{account,wallet}.py`: page-specific request handlers
+  * `app.py`: FastAPI web application entry point
+
 * `./flake.nix`: project runtime and development environment description
 
 ### Security considerations
@@ -196,6 +203,23 @@ before being created and initialised again:
 dropdb app
 ```
 
+### Local application server
+
+The server application can be started in development mode with:
+
+```sh
+uvicorn \
+  --reload-dir app \
+  --reload-dir templates \
+  --reload \
+  --app-dir app \
+  app:main
+```
+
+This server will listen to incoming requests to a locally bound port. It will
+automatically reload itself when its files are edited, and display logs about
+type checking and runtime errors.
+
 
 ## Copyright and licensing
 
diff --git a/sql/queries.sql b/sql/queries.sql
index 066781f..3cc8342 100644
--- a/sql/queries.sql
+++ b/sql/queries.sql
@@ -3,10 +3,17 @@
 -- Licence: EUPL-1.2
 
 
--- Fetch a user account by its username.
+-- Fetch a user account by its ID.
 --
 -- :name fetch_account
 -- :result :one-or-none
+select * from accounts where id = :user_id;
+
+
+-- Fetch a user account by its username.
+--
+-- :name fetch_account_username
+-- :result :one-or-none
 select * from accounts where username = :username;
 
 
-- 
cgit v1.2.3