Index: db.py =================================================================== --- db.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) +++ db.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) @@ -1,4 +1,6 @@ import datetime import psycopg2 +from psycopg2 import pool +from contextlib import contextmanager DB_CONFIG = { @@ -11,7 +13,60 @@ } +# -- Connection Pool ---------------------------------------------------------- +# Reuses database connections instead of opening a new one per query. +# SimpleConnectionPool maintains between minconn and maxconn connections. +_pool = None + + +def _get_pool(): + global _pool + if _pool is None: + _pool = pool.SimpleConnectionPool(minconn=2, maxconn=10, **DB_CONFIG) + return _pool + + +@contextmanager def get_connection(): - return psycopg2.connect(**DB_CONFIG) + """Get a read-only connection from the pool. + Autocommit is enabled so SELECTs don't need explicit commit. + Connection is returned to the pool on exit.""" + p = _get_pool() + conn = p.getconn() + try: + conn.autocommit = True + yield conn + finally: + conn.autocommit = False + p.putconn(conn) + + +@contextmanager +def get_transaction(): + """Get a connection with explicit transaction management. + Commits on successful exit, rolls back on exception. + Connection is returned to the pool in both cases.""" + p = _get_pool() + conn = p.getconn() + try: + conn.autocommit = False + yield conn + conn.commit() + except Exception: + conn.rollback() + raise + finally: + p.putconn(conn) + + +def close_pool(): + """Close all connections in the pool.""" + global _pool + if _pool is not None: + _pool.closeall() + _pool = None + + +# -- UI Helpers --------------------------------------------------------------- Index: main.py =================================================================== --- main.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) +++ main.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) @@ -4,5 +4,5 @@ import bcrypt -from db import get_connection, pick_from_list +from db import get_connection, close_pool, pick_from_list from uc_browse import browse_resources from uc_reserve import make_reservation @@ -95,4 +95,6 @@ except KeyboardInterrupt: print("\n\n Goodbye.") + finally: + close_pool() Index: uc_approve.py =================================================================== --- uc_approve.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) +++ uc_approve.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) @@ -1,3 +1,3 @@ -from db import get_connection, pick_from_list, print_table +from db import get_connection, get_transaction, pick_from_list, print_table @@ -120,18 +120,35 @@ new_status = "approved" if action == 0 else "rejected" - conn = get_connection() try: - with conn.cursor() as cur: - 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() - conn.commit() + with get_transaction() as conn: + with conn.cursor() as cur: + # For approvals, re-check conflicts within the transaction + # to prevent approving overlapping reservations + 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 if result: print(f"\n Reservation #{result[0]} has been {result[1]}.") @@ -139,6 +156,4 @@ print("\n Reservation was already processed by another administrator.") except Exception as e: - conn.rollback() + # Transaction rolled back automatically print(f"\n Error: {e}") - finally: - conn.close() Index: uc_reserve.py =================================================================== --- uc_reserve.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) +++ uc_reserve.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) @@ -1,5 +1,5 @@ import datetime -from db import get_connection, pick_from_list, print_table, input_date, input_time +from db import get_connection, get_transaction, pick_from_list, print_table, input_date, input_time @@ -96,19 +96,34 @@ return - # Step 7: INSERT - conn = get_connection() + # Step 7: INSERT (transaction: re-check conflicts + insert atomically) try: - with conn.cursor() as cur: - 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() - conn.commit() + 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 print(f"\n Reservation created successfully!") print(f" Reservation ID: {result[0]}") @@ -117,9 +132,7 @@ return except Exception as e: - conn.rollback() + # Transaction rolled back automatically print(f"\n Error creating reservation: {e}") return - finally: - conn.close() Index: uc_users.py =================================================================== --- uc_users.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) +++ uc_users.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) @@ -3,5 +3,5 @@ import bcrypt -from db import get_connection, pick_from_list +from db import get_connection, get_transaction, pick_from_list @@ -40,14 +40,5 @@ return - # Step 3: Check email uniqueness - with get_connection() as conn: - with conn.cursor() as cur: - cur.execute("SELECT COUNT(*) FROM users WHERE email = %s", (email,)) - exists = cur.fetchone()[0] - if exists > 0: - print(f"\n ERROR: A user with email '{email}' already exists.") - return - - # Step 4: Enter password + # Step 3: Enter password password = getpass.getpass(" Initial password: ").strip() if not password: @@ -59,22 +50,28 @@ return - # Step 5: Hash and insert + # Step 4: Hash password hashed = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode() - conn = get_connection() + # Step 5: Transaction — check email uniqueness + insert atomically + # This prevents a race condition where two users register with the + # same email simultaneously (one check passes, both insert). try: - with conn.cursor() as cur: - 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() - conn.commit() + 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.") - # Step 6: Confirmation + 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 — Step 6: Confirmation print(f"\n User created successfully!") print(f" User ID: {result[0]}") @@ -82,7 +79,6 @@ print(f" Email: {result[3]}") print(f" Role: {role_name}") + except ValueError as e: + print(f"\n ERROR: {e}") except Exception as e: - conn.rollback() print(f"\n Error creating user: {e}") - finally: - conn.close()