Changes between Initial Version and Version 1 of AdvancedApp

Timestamp:

04/05/26 20:05:06 (6 days ago)

Author:

221511

Comment:

--

AdvancedApp

@@ +1 @@
+= Advanced Application Development =
+
+== Transactions ==
+
+A database transaction groups multiple operations into a single atomic unit: either all operations succeed and are committed, or all are rolled back if any error occurs. This prevents the database from being left in an inconsistent state.
+
+In the FRRUAS prototype (Python + psycopg2), we implement transactions using a context manager {{{get_transaction()}}} that automatically commits on success and rolls back on exception:
+
+{{{
+@contextmanager
+def get_transaction():
+    p = _get_pool()
+    conn = p.getconn()
+    try:
+        conn.autocommit = False
+        yield conn
+        conn.commit()
+    except Exception:
+        conn.rollback()
+        raise
+    finally:
+        p.putconn(conn)
+}}}
+
+=== Transaction 1: Make a Reservation (conflict check + insert) ===
+
+When a user creates a reservation, two operations must happen atomically: checking for time conflicts and inserting the reservation. Without a transaction, a race condition exists: two users could both pass the conflict check simultaneously, and both insert overlapping reservations.
+
+{{{
+# uc_reserve.py — make_reservation()
+
+with get_transaction() as conn:
+    with conn.cursor() as cur:
+        # Re-check conflicts inside the transaction to prevent
+        # race conditions (another user inserting between our
+        # check and insert)
+        cur.execute("""
+            SELECT COUNT(*) FROM reservations
+            WHERE resource_id = %s
+              AND status IN ('approved', 'pending')
+              AND start_time < %s AND end_time > %s
+        """, (rid, end_dt, start_dt))
+
+        if cur.fetchone()[0] > 0:
+            raise RuntimeError("Conflict: another reservation was just created for this time slot")
+
+        cur.execute("""
+            INSERT INTO reservations
+                (start_time, end_time, status, purpose, created_at, user_id, resource_id)
+            VALUES (%s, %s, 'pending', %s, CURRENT_TIMESTAMP, %s, %s)
+            RETURNING reservation_id, status, created_at
+        """, (start_dt, end_dt, purpose, user_id, rid))
+
+        result = cur.fetchone()
+# Transaction committed automatically on successful exit
+}}}
+
+=== Transaction 2: Approve or Reject a Reservation (conflict re-check + status update) ===
+
+When an administrator approves a reservation, the system must verify no conflicting reservation was approved in the meantime. The conflict check and status update are wrapped in a single transaction to prevent two administrators from simultaneously approving overlapping reservations.
+
+{{{
+# uc_approve.py — _review_reservation()
+
+with get_transaction() as conn:
+    with conn.cursor() as cur:
+        # For approvals, re-check conflicts within the transaction
+        if new_status == "approved":
+            cur.execute("""
+                SELECT COUNT(*) FROM reservations
+                WHERE resource_id = %s
+                  AND reservation_id != %s
+                  AND status = 'approved'
+                  AND start_time < %s AND end_time > %s
+            """, (res["resource_id"], res["reservation_id"],
+                  res["end_time"], res["start_time"]))
+
+            if cur.fetchone()[0] > 0:
+                raise RuntimeError("Conflict: another reservation was approved for this time slot")
+
+        cur.execute("""
+            UPDATE reservations
+            SET status = %s, approved_by = %s
+            WHERE reservation_id = %s AND status = 'pending'
+            RETURNING reservation_id, status
+        """, (new_status, admin_user_id, res["reservation_id"]))
+
+        result = cur.fetchone()
+# Transaction committed automatically
+}}}
+
+=== Transaction 3: Register a New User (email uniqueness check + insert) ===
+
+User registration checks email uniqueness and inserts the new user atomically. Without a transaction, two simultaneous registrations with the same email could both pass the uniqueness check and both insert, violating the UNIQUE constraint.
+
+{{{
+# uc_users.py — register_user()
+
+with get_transaction() as conn:
+    with conn.cursor() as cur:
+        cur.execute("SELECT COUNT(*) FROM users WHERE email = %s", (email,))
+
+        if cur.fetchone()[0] > 0:
+            raise ValueError(f"A user with email '{email}' already exists.")
+
+        cur.execute("""
+            INSERT INTO users (first_name, last_name, email, password, type_id)
+            VALUES (%s, %s, %s, %s, %s)
+            RETURNING user_id, first_name, last_name, email
+        """, (first_name, last_name, email, hashed, type_id))
+
+        result = cur.fetchone()
+# Transaction committed automatically
+}}}
+
+== Database Connection Pooling ==
+
+Connection pooling reuses open database connections instead of creating a new one for every query. Opening a database connection involves TCP handshake, authentication, and session setup, which adds overhead. With pooling, these costs are paid once, and subsequent operations borrow pre-established connections from the pool.
+
+=== Implementation ===
+
+The prototype uses {{{psycopg2.pool.SimpleConnectionPool}}} to maintain a pool of reusable connections:
+
+{{{
+# db.py
+
+from psycopg2 import pool
+
+_pool = None
+
+def _get_pool():
+    global _pool
+    if _pool is None:
+        _pool = pool.SimpleConnectionPool(minconn=2, maxconn=10, **DB_CONFIG)
+    return _pool
+}}}
+
+'''Pool configuration:'''
+
+||'''Parameter'''||'''Value'''||'''Meaning'''||
+||minconn||2||Minimum connections kept open and ready||
+||maxconn||10||Maximum concurrent connections allowed||
+
+The pool is initialized lazily on first use and cleaned up when the application exits via {{{close_pool()}}}.
+
+=== Connection Usage ===
+
+Two context managers provide connections from the pool:
+
+'''Read operations''' use {{{get_connection()}}} with autocommit enabled:
+{{{
+@contextmanager
+def get_connection():
+    p = _get_pool()
+    conn = p.getconn()
+    try:
+        conn.autocommit = True
+        yield conn
+    finally:
+        conn.autocommit = False
+        p.putconn(conn)
+}}}
+
+'''Write operations''' use {{{get_transaction()}}} with explicit commit/rollback:
+{{{
+@contextmanager
+def get_transaction():
+    p = _get_pool()
+    conn = p.getconn()
+    try:
+        conn.autocommit = False
+        yield conn
+        conn.commit()
+    except Exception:
+        conn.rollback()
+        raise
+    finally:
+        p.putconn(conn)
+}}}
+
+In both cases, the connection is automatically returned to the pool when the {{{with}}} block exits, ensuring no connection leaks.
+
+=== Example: Read Operation Using Pool ===
+
+{{{
+# uc_browse.py — fetching resources
+
+with get_connection() as conn:
+    with conn.cursor() as cur:
+        cur.execute("""
+            SELECT r.resource_id, r.name, rt.type_name, l.building, l.room
+            FROM resources r
+            JOIN resource_types rt ON r.type_id = rt.type_id
+            LEFT JOIN locations l ON r.location_id = l.location_id
+            ORDER BY rt.type_name, r.name
+        """)
+        resources = cur.fetchall()
+# Connection returned to pool automatically
+}}}
+
+=== Pool Lifecycle ===
+
+{{{
+# main.py
+
+def main():
+    try:
+        while True:
+            user = login()       # uses pooled connections
+            if user is None:
+                break
+            main_menu(...)       # all operations use pooled connections
+    except KeyboardInterrupt:
+        pass
+    finally:
+        close_pool()             # close all connections on exit
+}}}