# Simultaneous processes using ACID xactions to coordinate.

import sqlite3
import time

conn = sqlite3.connect("stooges.sqlite", timeout=20)

cust_num = input("What cust number? (or 'done') ")

while cust_num != "done":
    cn = int(cust_num)

    # A boolean flag to track whether our commit worked.
    written = False
    while not written:

        # Begin a transaction. If our commit (below) fails, and we have to
        # rollback, this is the database state we'll be rolling back to.
        conn.execute("begin transaction")

        result = conn.execute(
            """
            select custid, name, num from custs where custid=?
            """, (cn,)).fetchone()
        print(f"{result[1]} has bought {result[2]} items so far.")
        time.sleep(5)
        curr = result[2]
        newamt = curr + 10
        print(f"Let's order 10 more for {result[1]}!")
        print(f"and that will give him {newamt}!")

        try:
            conn.execute(
                """
                update custs set num = ? where custid=?
                """, (newamt, cn,))

            # Attempt to commit our update. If our database state is dirty,
            # though, and someone else has the table locked, this will throw
            # an exception.
            conn.commit()

            # Yay! Our commit worked. Continue on our merry way.
            written = True

        except Exception as e:

            # Whoops! Take evasive action. 
            print("Commit failed, roll back.")
            conn.rollback()

        else:
            result = conn.execute(
                """
                select custid, name, num from custs where custid=?
                """, (cn,)).fetchone()
            print(f"{result[1]} now has {result[2]} items on order!\n")

    cust_num = input("What cust number? (or 'done') ")

conn.close()