Exercise 2: Building the Self-Referencing Employees Example — Possible Solution
====================================================================

THE TEST
------------------------------
    cat = Catalog(tmp)
    cat.create_table('employees', [('id','INTEGER'),('name','TEXT'),('manager_id','INTEGER')],
                      foreign_keys=[ForeignKey('manager_id', 'employees', 'id')])

    # Part 1: the normal path rejects a self-reference to a not-yet-inserted id
    try:
        cat.insert('employees', [1, 'CEO', 1])
        self_ref_worked = True
    except ForeignKeyViolation:
        self_ref_worked = False

    # Part 2: insert-then-validate-then-rollback DOES allow it
    loc_ceo = insert_allow_self_reference(cat, 'employees', [1, 'CEO', 1])
    insert_allow_self_reference(cat, 'employees', [2, 'VP', 1])   # reports to the real CEO row
    try:
        insert_allow_self_reference(cat, 'employees', [3, 'Ghost', 999])
        ghost_worked = True
    except ForeignKeyViolation:
        ghost_worked = False

    _, emp_table = cat.tables['employees']
    print(list(emp_table.scan()))

RESULT
------------------------------
    a row referencing its OWN not-yet-inserted id: REJECTED
    the SAME self-referencing row, via insert-then-check-then-rollback-if-invalid: inserted at (0, 0)
    employees after CEO (self-ref), VP (valid), and Ghost (invalid, rolled back): [[1, 'CEO', 1], [2, 'VP', 1]]

self_ref_worked is False under the normal insert() path -- confirming
the chapter's own finding directly. Under insert_allow_self_reference(),
the CEO row succeeds, the VP row (a genuinely ordinary FK, referencing
an already-existing different row) succeeds, and the Ghost row
(referencing manager 999, who doesn't exist at all) is correctly
rejected -- ghost_worked is False, and Ghost never appears in the
final scan.

WHY THE NORMAL PATH CAN NEVER SUCCEED HERE
------------------------------
cat.insert()'s own check runs BEFORE table.insert() is ever called.
At the moment [1, 'CEO', 1]'s own manager_id=1 is checked against
employees.id, no row with id=1 exists yet -- the row being inserted
IS the one that would satisfy the check, and it doesn't exist until
after the check has already failed. This is true regardless of which
row number it is or what table it's in: it's a structural property of
checking before writing, not a quirk of this specific example.

WHY insert_allow_self_reference() SUCCEEDS
------------------------------
It physically writes the row FIRST (via HeapTable.insert() through a
TransactionalHeapFile, which writes immediately to the real heap
file -- there's no deferred/staged write the way Chapter 6's MVCC
had). By the time the foreign key check runs, [1, 'CEO', 1] already
exists as a real row a scan or an index search could find, so
_value_exists('employees', 'id', 1) correctly returns True. For the
Ghost row, the same physical-write-first step happens, but the check
still correctly fails (no row with id=999 exists, self-referencing or
otherwise), and txn.rollback() -- Chapter 4's own real undo-log-based
rollback, completely unmodified -- restores the page to what it was
before Ghost was ever written, leaving no trace.

WHY THIS WORKS AS AN ANSWER
------------------------------
Reproducing both halves of the chapter's own finding -- confirming the
normal path's rejection is real and structural, and confirming the
transactional workaround genuinely resolves it while still correctly
rejecting a truly invalid row -- demonstrates that reusing Chapter 4's
transaction machinery here isn't just a clever trick that happens to
work, but a principled application of "insert first, validate second,
undo on failure" to a problem check-before-insert genuinely cannot
solve on its own.