def order_detail_flush_delete(a_row, a_session: session):
order = a_row.OrderHeader
old_order = ObjectView(
row2dict(order)) # hmm... key ShippedDate vs. "ShippedDate"
order.AmountTotal -= a_row.Amount
order_update(order, old_order, a_session)
row_prt(order,
"order_detail_flush_delete adjusted to: " + str(order.AmountTotal))
def customer_flush_dirty(a_row, a_session: session):
"""
Called from listeners.py on before_flush
"""
old_row = get_old_row(a_row)
row_prt(a_row, "\ncustomer_flush_dirty")
customer_update(a_row, old_row, a_session)
def order_flush_delete(a_row, a_session: session): # FIXME
"""
Called from module init on before_flush
"""
is_unshipped = (a_row.ShippedDate is None) or (a_row.ShippedDate == "")
if is_unshipped:
delta = a_row.AmountTotal
customer = a_row.Customer
customer.Balance -= delta # attach, update not req'd
row_prt(customer, "order_upd adjusted Customer per delete")
row_prt(a_row, "order_flush_delete - default values supplied")
def toggle_order_shipped():
""" toggle Shipped Date, to trigger balance adjustment """
""" also test join.
session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
"""
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).join(models.Employee).one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
# with restored db, cust[ALFKI] has bal 960 & 3 unpaid orders, Order[11011) is 960, unshipped
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]" + " for customer balance: " +
str(pre_cust.Balance) + ", with UnpaidOrderCount: " +
str(pre_cust.UnpaidOrderCount)))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
insp = inspect(test_order)
# session.commit()
print("")
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 960:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
row_prt(post_cust, "\nERROR - incorrect adjusted Customer Result")
print("\n--> probable cause: Order customer update not written")
row_prt(pre_cust, "\npre_alfki")
assert False
def order_update(a_row, an_old_row, a_session):
"""
called either by order_flush_dirty, *or* by order_detail_code. to adjust order
see order_detail_code.order_detail_flush_new
"""
row_prt(a_row, "\norder_flush_dirty")
if a_row.ShippedDate != an_old_row.ShippedDate:
is_unshipped = (a_row.ShippedDate is None) or (a_row.ShippedDate == "")
delta = -a_row.AmountTotal # assume not changed!!
if is_unshipped:
delta = a_row.AmountTotal
customer = a_row.Customer
customer.Balance += delta # attach, update not req'd
row_prt(customer, "order_upd adjusted per shipped change")
if a_row.CustomerId != an_old_row.CustomerId:
is_unshipped = (a_row.ShippedDate is None) or (a_row.ShippedDate == "")
if is_unshipped:
delta = a_row.AmountTotal
customer = a_row.Customer
customer.Balance += delta # attach, update not req'd
row_prt(customer, "order_upd adjusted Customer per re-assignment")
old_customer = a_session.query(models.Customer). \
filter(models.Customer.Id == an_old_row.CustomerId).one()
# old_customer = ObjectView(row2dict(customer))
old_customer.Balance -= delta
a_session.add(old_customer)
customer_update(old_customer, old_customer, a_session)
row_prt(customer,
"order_upd adjusted Customer, per AmountTotal change")
if a_row.AmountTotal != an_old_row.AmountTotal:
# nice try: customer = a_row.Customer -- fails, since this is *adding* order
customer = a_session.query(models.Customer). \
filter(models.Customer.Id == a_row.CustomerId).one()
old_customer = ObjectView(row2dict(customer))
delta = a_row.AmountTotal - an_old_row.AmountTotal
customer.Balance += delta
# a_session.add(customer)
customer_update(customer, old_customer, a_session)
row_prt(customer,
"order_upd adjusted Customer, per AmountTotal change")
def order_detail_flush_new(a_row: models.OrderDetail, a_session: session):
"""
OrderDetail before_flush, new rows
compute amount, adjust Order.AmountTotal
.. which adjusts Customer.balance)
"""
# no "old" in inserts... old_row = get_old_row(a_row)
row_prt(a_row, "\norder_detail_flush_new") # readable log: curr/old values
# nice try.. product = row.Product
product = a_session.query(models.Product).\
filter(models.Product.Id == a_row.ProductId).one()
a_row.UnitPrice = product.UnitPrice
a_row.Amount = a_row.Quantity * a_row.UnitPrice
order = a_row.OrderHeader
"""
2 issues make this a little more complicated than expected:
1. can't just alter AmountTotal - does not trigger Order's before_flush
2. can't just call Order's before_flush - old values not available
"""
old_order = ObjectView(row2dict(order))
order.AmountTotal += a_row.Amount
order_update(order, old_order, a_session)
row_prt(order,
"order_detail_flush_new adjusted to: " + str(order.AmountTotal))
def order_detail_flush_dirty(a_row: models.OrderDetail, a_session: session):
old_row = get_old_row(a_row) # type: models.OrderDetail
if a_row.OrderId == old_row.OrderId:
if a_row.ProductId != old_row.ProductId:
product = a_session.query(models.Product). \
filter(models.Product.Id == a_row.ProductId).one()
a_row.UnitPrice = product.UnitPrice
a_row.Amount = a_row.UnitPrice * a_row.Quantity
if a_row.Amount != old_row.Amount:
order = a_row.OrderHeader
order.AmountTotal += a_row.Amount - old_row.Amount
old_order = ObjectView(row2dict(order))
order_update(order, old_order, a_session)
row_prt(
order, "order_detail_flush_dirty adjusted to: " +
str(order.AmountTotal))
else: # moved item to different order
order = a_row.OrderHeader # reduce the old one
order.AmountTotal -= old_row.Amount
old_order = ObjectView(row2dict(order))
order_update(order, old_order, a_session)
row_prt(
order,
"order_detail_flush_dirty adjusted to: " + str(order.AmountTotal))
if a_row.ProductId != old_row.ProductId:
product = a_session.query(models.Product). \
filter(models.Product.Id == old_row.ProductId).one()
a_row.UnitPrice = product.UnitPrice
a_row.Amount = a_row.UnitPrice * a_row.Quantity
order = a_session.query(models.Order). \
filter(models.Order.Id == a_row.OrderId).one()
old_order = ObjectView(row2dict(order))
order.AmountTotal += a_row.Amount
order_update(order, old_order, a_session)
row_prt(
order,
"order_detail_flush_dirty adjusted to: " + str(order.AmountTotal))
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).join(models.Employee).one()
if test_order.RequiredDate is None or test_order.RequiredDate == "":
test_order.RequiredDate = str(datetime.now())
print(prt("Shipping order - RequiredDate: ['' -> " + test_order.RequiredDate + "]"))
else:
test_order.RequiredDate = None
print(prt("Returning order - RequiredDate: [ -> None]"))
insp = inspect(test_order)
session.commit()
print("")
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=pre_cust, old_row=post_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
row_prt(post_cust, "\nERROR - incorrect adjusted Customer Result")
print("\n--> probable cause: Order customer update not written")
row_prt(pre_cust, "\npre_alfki")
assert False
print("\nupd_order_shipped, ran to completion")
UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item1)
new_item2 = models.OrderDetail(ProductId=2,
Amount=0,
Quantity=2,
UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item2)
session.commit()
post_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
print("\nadd_order, update completed\n\n")
row_prt(new_order, "\nnew Order Result") # $18 + $38 = $56
if new_order.AmountTotal != 56:
print("==> ERROR - unexpected AmountTotal: " + str(new_order.AmountTotal) +
"... expected 56")
row_prt(new_item1, "\nnew Order Detail 1 Result") # 1 Chai @ $18
row_prt(new_item2, "\nnew Order Detail 2 Result") # 2 Chang @ $19 = $38
logic_row = LogicRow(row=post_cust,
old_row=pre_cust,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_cust.Balance == pre_cust.Balance + 56:
logic_row.log("Correct adjusted Customer Result")
assert True
print("")
post_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
logic_row = LogicRow(row=post_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_alfki.Balance - pre_alfki.Balance) == 960:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
row_prt(post_alfki, "\nERROR - incorrect adjusted Customer Result")
print("\n--> probable cause: Order customer update not written")
row_prt(pre_alfki, "\npre_alfki")
assert False
post_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=post_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if abs(post_anatr.Balance - pre_anatr.Balance) == 960:
logic_row.log("Correct non-adjusted Customer Result")
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
cust_alfki = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
new_payment = models.Payment(Amount=1000)
cust_alfki.PaymentList.append(new_payment)
session.add(new_payment)
session.commit()
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
print("\nadd_payment, update completed\n\n")
row_prt(new_payment, "\nnew Payment Result") #
if new_payment.Amount != Decimal(1000):
print ("==> ERROR - unexpected new_payment.Amount: " + str(new_payment.Amount) +
"... expected 1000")
else:
print()
"""
(10653 owes nothing)
orderId OrderDate AmountTotal AmountPaid AmountOwed ==> Allocated
10692 2013-10-03 878 778 100 100
10702 2013-10-03 330 0 330 330
10835 2014-01-15 851 0 851 570
10952 2014-03-16 491.20 0 491.20 *
11011 2014-04-09 960 0 960 *
"""
def customer_commit_dirty(a_row, a_session: session):
old_row = get_old_row(a_row)
row_prt(a_row, "order_commit_dirty")
def order_detail_commit_dirty(a_row, a_session: session):
old_row = get_old_row(a_row)
row_prt(a_row, "\norder_detail_commit_dirty")
def order_flush_new(a_row, a_session: session):
"""
Called from module init on before_flush
"""
a_row.ShippedDate = "" # default value
row_prt(a_row, "order_flush_new - default values supplied")
def test_run(self):
pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
"""
Test 1 - should fail due to credit limit exceeded
"""
bad_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=20000, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException as ce:
print("Expected constraint: " + str(ce))
session.rollback()
did_fail_as_expected = True
except:
self.fail("Unexpected Exception Type")
if not did_fail_as_expected:
self.fail("huge order expected to fail, but succeeded")
else:
print("\n" + prt("huge order failed credit check as expected. Now trying non-commissioned order, should also fail..."))
"""
Test 2 - should fail due to not-commissioned
"""
bad_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=2, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException:
session.rollback()
did_fail_as_expected = True
except:
print("Unexpected Exception Type")
if not did_fail_as_expected:
self.fail("order for non-commissioned expected to fail, but succeeded")
else:
print("\n" + prt("non-commissioned order failed constraint as expected. Now trying invalid customer, should fail..."))
"""
Test 3 - should fail due to invalid customer
"""
bad_order = models.Order(AmountTotal=0, CustomerId="XX INVALID CUSTOMER", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(bad_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
bad_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item1)
bad_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
bad_order.OrderDetailList.append(bad_item2)
did_fail_as_expected = False
try:
session.commit()
except ConstraintException as ce:
session.rollback()
did_fail_as_expected = True
print(str(ce))
except:
session.rollback()
did_fail_as_expected = True
e = sys.exc_info()[0]
print(e)
if not did_fail_as_expected:
self.fail("order for invalid customer expected to fail, but succeeded")
else:
print("\n" + prt("invalid customer failed as expected. Now trying valid order, should succeed..."))
"""
Test 4 - should succeed
"""
new_order = models.Order(AmountTotal=0, CustomerId="ALFKI", ShipCity="Richmond",
EmployeeId=6, Freight=1)
session.add(new_order)
# OrderDetails - https://docs.sqlalchemy.org/en/13/orm/backref.html
new_item1 = models.OrderDetail(ProductId=1, Amount=0,
Quantity=1, UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item1)
new_item2 = models.OrderDetail(ProductId=2, Amount=0,
Quantity=2, UnitPrice=18,
Discount=0)
new_order.OrderDetailList.append(new_item2)
session.commit()
post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one()
print("\nadd_order, update completed - analyzing results..\n\n")
row_prt(new_order, session, "\nnew Order Result") # $18 + $38 = $56
if new_order.AmountTotal != 56:
self.fail(row_prt(new_order, "Unexpected AmountTotal: " + str(new_order.AmountTotal) +
"... expected 56"))
row_prt(new_item1, session, "\nnew Order Detail 1 Result") # 1 Chai @ $18
row_prt(new_item2, session, "\nnew Order Detail 2 Result") # 2 Chang @ $19 = $38
logic_row = LogicRow(row=post_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if post_cust.Balance == pre_cust.Balance + 56:
logic_row.log("Correct adjusted Customer Result")
assert True
else:
self.fail(logic_row.log("ERROR - incorrect adjusted Customer Result"))
if post_cust.OrderCount == pre_cust.OrderCount + 1 and\
post_cust.UnpaidOrderCount == pre_cust.UnpaidOrderCount + 1:
pass
else:
self.fail(logic_row.log("Error - unexpected OrderCounts - did not increase by 1"))
from sqlalchemy.sql import func
qry = session.query(models.Order.CustomerId,
func.sum(models.Order.AmountTotal).label('sql_balance'))\
.filter(models.Order.CustomerId == "ALFKI", models.Order.ShippedDate == None)
qry = qry.group_by(models.Order.CustomerId).one()
if qry.sql_balance == post_cust.Balance:
logic_row.log("Derived balance matches sql `select sum' result: " + str(post_cust.Balance))
else:
self.fail(logic_row.log("ERROR - computed balance does not match sql result"))
print("\nadd_order, ran to completion\n\n")
self.assertTrue(True)
