Index: db.py =================================================================== --- db.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) +++ db.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) @@ -1,6 +1,4 @@ import datetime import psycopg2 -from psycopg2 import pool -from contextlib import contextmanager DB_CONFIG = { @@ -13,60 +11,7 @@ } -# -- 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(): - """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 --------------------------------------------------------------- + return psycopg2.connect(**DB_CONFIG) Index: main.py =================================================================== --- main.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) +++ main.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) @@ -4,5 +4,5 @@ import bcrypt -from db import get_connection, close_pool, pick_from_list +from db import get_connection, pick_from_list from uc_browse import browse_resources from uc_reserve import make_reservation @@ -95,6 +95,4 @@ except KeyboardInterrupt: print("\n\n Goodbye.") - finally: - close_pool() Index: uc_approve.py =================================================================== --- uc_approve.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) +++ uc_approve.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) @@ -1,3 +1,3 @@ -from db import get_connection, get_transaction, pick_from_list, print_table +from db import get_connection, pick_from_list, print_table @@ -120,35 +120,18 @@ new_status = "approved" if action == 0 else "rejected" + conn = get_connection() try: - 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 + 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() if result: print(f"\n Reservation #{result[0]} has been {result[1]}.") @@ -156,4 +139,6 @@ print("\n Reservation was already processed by another administrator.") except Exception as e: - # Transaction rolled back automatically + conn.rollback() print(f"\n Error: {e}") + finally: + conn.close() Index: uc_reserve.py =================================================================== --- uc_reserve.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) +++ uc_reserve.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) @@ -1,5 +1,5 @@ import datetime -from db import get_connection, get_transaction, pick_from_list, print_table, input_date, input_time +from db import get_connection, pick_from_list, print_table, input_date, input_time @@ -96,34 +96,19 @@ return - # Step 7: INSERT (transaction: re-check conflicts + insert atomically) + # Step 7: INSERT + conn = get_connection() try: - 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 + 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() print(f"\n Reservation created successfully!") print(f" Reservation ID: {result[0]}") @@ -132,7 +117,9 @@ return except Exception as e: - # Transaction rolled back automatically + conn.rollback() print(f"\n Error creating reservation: {e}") return + finally: + conn.close() Index: uc_users.py =================================================================== --- uc_users.py (revision 527c0976508c7d2fad14e9180bcf1a211d5f18af) +++ uc_users.py (revision 95cf703d2ef8f7abf8a3684935be560cdf73467f) @@ -3,5 +3,5 @@ import bcrypt -from db import get_connection, get_transaction, pick_from_list +from db import get_connection, pick_from_list @@ -40,5 +40,14 @@ return - # Step 3: Enter password + # 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 password = getpass.getpass(" Initial password: ").strip() if not password: @@ -50,28 +59,22 @@ return - # Step 4: Hash password + # Step 5: Hash and insert hashed = bcrypt.hashpw(password.encode(), bcrypt.gensalt()).decode() - # 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). + conn = get_connection() try: - 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.") + 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() - 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 + # Step 6: Confirmation print(f"\n User created successfully!") print(f" User ID: {result[0]}") @@ -79,6 +82,7 @@ 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()