Changes between Version 5 and Version 6 of P8

Timestamp:

05/18/26 21:17:23 (8 days ago)

Author:

211171

Comment:

--

P8

@@ -255 +255 @@
 The attendance table has UNIQUE (guest_id, event_id). A guest may need to be reassigned to a different table later. Using ON CONFLICT DO UPDATE handles both the initial seat assignment and any re-assignment atomically, avoiding race conditions.
 
+=== Flask Implementation ===
+{{{
+from sqlalchemy.exc import SQLAlchemyError
+from datetime import date
+
+def accept_rsvp_and_assign_seat(guest_id, event_id, table_number, role='Guest'):
+try:
+db.session.begin()
+
+    # STEP 1: Find RSVP record
+    rsvp = db.session.query(EventRSVP).filter_by(
+        guest_id=guest_id,
+        event_id=event_id
+    ).first()
+
+    # RSVP must exist
+    if not rsvp:
+        raise ValueError('RSVP record not found')
+
+    # STEP 2: Update RSVP status
+    rsvp.status = 'accepted'
+    rsvp.response_date = date.today()
+
+    # STEP 3: Check if attendance already exists
+    attendance = db.session.query(Attendance).filter_by(
+        guest_id=guest_id,
+        event_id=event_id
+    ).first()
+
+    # STEP 4: UPSERT logic
+    if attendance:
+        attendance.status = 'attending'
+        attendance.table_number = table_number
+        attendance.role = role
+    else:
+        attendance = Attendance(
+            status='attending',
+            table_number=table_number,
+            role=role,
+            guest_id=guest_id,
+            event_id=event_id
+        )
+        db.session.add(attendance)
+
+    db.session.commit()
+
+    return {
+        'status': 'success',
+        'guest_id': guest_id,
+        'event_id': event_id,
+        'table_number': table_number
+    }
+
+except (ValueError, SQLAlchemyError) as e:
+    db.session.rollback()
+
+    return {
+        'status': 'error',
+        'reason': str(e)
+    }, 409
+
+}}}
+
+=== Explanation ===
+This transaction guarantees that RSVP acceptance and attendance assignment happen together atomically. If attendance creation fails after the RSVP update, the rollback restores the RSVP to its previous state, preventing inconsistencies.
+
 == Transaction 4 – Wedding Cancellation with Status Cascade ==
 === Business Scenario ===
@@ -310 +376 @@
 A concurrent RSVP or attendance insert could occur between steps, causing inconsistent data tied to a cancelled wedding.
 
+=== Flask Implementation ===
+{{{
+from sqlalchemy.exc import SQLAlchemyError
+
+def cancel_wedding(wedding_id):
+try:
+db.session.begin()
+
+    # STEP 1: Find wedding
+    wedding = db.session.query(Wedding).filter_by(
+        wedding_id=wedding_id
+    ).first()
+
+    if not wedding:
+        raise ValueError('Wedding not found')
+
+    # STEP 2: Update wedding status
+    wedding.notes = 'Wedding cancelled'
+
+    # STEP 3: Cancel venue bookings
+    db.session.query(VenueBooking).filter_by(
+        wedding_id=wedding_id
+    ).update({
+        'status': 'cancelled'
+    })
+
+    # STEP 4: Cancel photographer bookings
+    db.session.query(PhotographerBooking).filter_by(
+        wedding_id=wedding_id
+    ).update({
+        'status': 'cancelled'
+    })
+
+    # STEP 5: Cancel band bookings
+    db.session.query(BandBooking).filter_by(
+        wedding_id=wedding_id
+    ).update({
+        'status': 'cancelled'
+    })
+
+    # STEP 6: Cancel registrar bookings
+    db.session.query(RegistrarBooking).filter_by(
+        wedding_id=wedding_id
+    ).update({
+        'status': 'cancelled'
+    })
+
+    # STEP 7: Cancel events
+    db.session.query(Event).filter_by(
+        wedding_id=wedding_id
+    ).update({
+        'status': 'cancelled'
+    })
+
+    # STEP 8: Find all wedding events
+    events = db.session.query(Event.event_id).filter_by(
+        wedding_id=wedding_id
+    ).all()
+
+    event_ids = [e.event_id for e in events]
+
+    # STEP 9: Mark attendance absent
+    if event_ids:
+        db.session.query(Attendance).filter(
+            Attendance.event_id.in_(event_ids)
+        ).update({
+            'status': 'absent'
+        }, synchronize_session=False)
+
+    db.session.commit()
+
+    return {
+        'status': 'success',
+        'wedding_id': wedding_id
+    }
+
+except (ValueError, SQLAlchemyError) as e:
+    db.session.rollback()
+
+    return {
+        'status': 'error',
+        'reason': str(e)
+    }, 500
+
+}}}
+
+=== Explanation ===
+This transaction performs a full cancellation cascade across all dependent tables. Every update occurs inside one transaction so the database can never contain partially cancelled wedding data.
+
+
 == Transaction 5 – Budget Validation with Row Locking ==
 === Business Scenario ===
@@ -342 +498 @@
 }}}
 
-=== Why FOR UPDATE matters ===
-Prevents TOCTOU race conditions by locking the wedding row during validation.
+=== Flask Implementation ===
+{{{
+from sqlalchemy.exc import SQLAlchemyError
+
+def validate_wedding_budget(wedding_id):
+try:
+db.session.begin()
+
+    # STEP 1: Lock wedding row
+    wedding = db.session.query(Wedding)\
+        .filter_by(wedding_id=wedding_id)\
+        .with_for_update()\
+        .first()
+
+    if not wedding:
+        raise ValueError('Wedding not found')
+
+    budget = wedding.budget
+
+    # STEP 2: Calculate venue costs
+    venue_total = db.session.query(
+        db.func.coalesce(db.func.sum(VenueBooking.price), 0)
+    ).filter_by(
+        wedding_id=wedding_id
+    ).scalar()
+
+    # STEP 3: Calculate photographer costs
+    photographer_total = db.session.query(
+        db.func.coalesce(db.func.sum(PhotographerBooking.price), 0)
+    ).filter_by(
+        wedding_id=wedding_id
+    ).scalar()
+
+    # STEP 4: Calculate band costs
+    band_total = db.session.query(
+        db.func.coalesce(db.func.sum(BandBooking.price), 0)
+    ).filter_by(
+        wedding_id=wedding_id
+    ).scalar()
+
+    # STEP 5: Calculate registrar costs
+    registrar_total = db.session.query(
+        db.func.coalesce(db.func.sum(RegistrarBooking.price), 0)
+    ).filter_by(
+        wedding_id=wedding_id
+    ).scalar()
+
+    # STEP 6: Compute total cost
+    total_cost = (
+        venue_total +
+        photographer_total +
+        band_total +
+        registrar_total
+    )
+
+    # STEP 7: Validate budget
+    if total_cost > budget:
+        raise ValueError(
+            f'Budget exceeded. Total: {total_cost}, Budget: {budget}'
+        )
+
+    # STEP 8: Finalise wedding
+    wedding.notes = 'Budget validated – wedding finalised'
+
+    db.session.commit()
+
+    return {
+        'status': 'success',
+        'wedding_id': wedding_id,
+        'budget': float(budget),
+        'total_cost': float(total_cost)
+    }
+
+except (ValueError, SQLAlchemyError) as e:
+    db.session.rollback()
+
+    return {
+        'status': 'error',
+        'reason': str(e)
+    }, 409
+
+}}}
+
+=== Explanation ===
+The with_for_update() row lock prevents concurrent transactions from modifying the wedding budget or related financial data during validation. This avoids TOCTOU (Time Of Check To Time Of Use) race conditions.
 
 == Savepoints – Partial Rollback ==
@@ -390 +629 @@
 === Why needed ===
 Pooling reuses DB connections instead of creating new ones per request.
+
+=== Flask Implementation ===
+{{{
+from sqlalchemy.exc import SQLAlchemyError
+
+def partial_booking_example(wedding_id, venue_cfg, photo_cfg, band_cfg):
+try:
+db.session.begin()
+
+    # STEP 1: Insert venue booking
+    venue = VenueBooking(
+        **venue_cfg,
+        wedding_id=wedding_id
+    )
+    db.session.add(venue)
+
+    # STEP 2: Insert photographer booking
+    photographer = PhotographerBooking(
+        **photo_cfg,
+        wedding_id=wedding_id
+    )
+    db.session.add(photographer)
+
+    # STEP 3: Create savepoint
+    savepoint = db.session.begin_nested()
+
+    try:
+        # STEP 4: Attempt risky band booking
+        band = BandBooking(
+            **band_cfg,
+            wedding_id=wedding_id
+        )
+
+        db.session.add(band)
+        db.session.flush()
+
+    except SQLAlchemyError:
+        # STEP 5: Roll back only to savepoint
+        savepoint.rollback()
+
+    # STEP 6: Continue transaction
+    db.session.commit()
+
+    return {
+        'status': 'partial_success'
+    }
+
+except SQLAlchemyError as e:
+    db.session.rollback()
+
+    return {
+        'status': 'error',
+        'reason': str(e)
+    }, 500
+
+}}}
+
+=== Explanation ===
+begin_nested() creates a database savepoint. If the band booking fails, only the operations after the savepoint are rolled back while earlier successful inserts remain intact.
 
 === Affected endpoints ===