def congratulate_sales_rep(row: Order, old_row: Order, logic_row: LogicRow):
if logic_row.ins_upd_dlt == "ins": # logic engine fills parents for insert
sales_rep = row.SalesRep # type : Employee
if sales_rep is None:
logic_row.log("no salesrep for this order")
else:
logic_row.log(f'Hi, {sales_rep.Manager.FirstName}, congratulate {sales_rep.FirstName} on their new order')
def test_run(self):
self.toggle_order_shipped()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_adjusted_product)
print("\nNow delete the Customer...")
delete_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.delete(delete_cust)
session.commit()
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped - 40:
logic_row.log("Product adjusted properly on delete customer")
else:
logic_row.log("Product adjusted improperly on delete customer")
assert False, "Product adjusted improperly on delete customer"
print("\nCustomer deleted - check log")
self.assertTrue(True)
def toggle_order_shipped(self):
""" 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]"))
# 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:
self.fail(logic_row.log("Incorrect adjusted Customer Result - expected 960 difference"))
def transfer_funds(row: TRANSFERFUND, old_row: TRANSFERFUND,
logic_row: LogicRow):
if logic_row.ins_upd_dlt == "ins" or True: # logic engine fills parents for insert
logic_row.log("Transfer from source to target")
fromCustNum = row.FromCustNum
toCustNum = row.ToCustNum
acctNum = row.FromAcct
trans_date = datetime.datetime(2020, 10, 1)
transferAmt = row.TransferAmt
transID = row.TransId
# need to lookup the From Acct to see if it is checking or savings - that way we can reverse the flow
deposit = models.SAVINGSTRANS(TransId=transID,
CustNum=toCustNum,
AcctNum=acctNum,
DepositAmt=transferAmt,
WithdrawlAmt=0,
TransDate=trans_date)
logic_row.insert("Deposit to savings", deposit)
withdrawl = models.CHECKINGTRANS(TransId=transID,
CustNum=fromCustNum,
AcctNum=acctNum,
DepositAmt=0,
WithdrawlAmt=transferAmt,
TransDate=trans_date)
logic_row.insert("Withdraw from CHECKINGTRANS", withdrawl)
def toggle_order_shipped(self):
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
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).options(joinedload(models.Order.SalesRep)).\
one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
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:
self.fail(
logic_row.log(
"ERROR - incorrect adjusted Customer Result (not 960 delta)"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
def test_run(self):
""" test class <> table name """
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.expunge(pre_cust)
print("")
test_order = session.query(models.OrderClass).filter(
models.OrderClass.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]"))
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)
# logic_row.row.Balance = 10 # force failure
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected no difference"
))
print("\nupd_order_required, ran to completion")
def execute(self, logic_row: LogicRow):
"""
Called by logic engine, overriding generic EarlyEvent rule.
Creates allocation row for each recipient until while_calling_allocator returns false
:return:
"""
logic_row.log(f'BEGIN {str(self)}')
provider = logic_row
to_recipients = self.recipients(provider)
for each_recipient in to_recipients:
new_allocation = self.creating_allocation()
new_allocation_logic_row = LogicRow(
row=new_allocation,
old_row=new_allocation,
ins_upd_dlt="ins",
nest_level=provider.nest_level + 1,
a_session=provider.session,
row_sets=provider.row_sets)
new_allocation_logic_row.link(to_parent=provider)
each_recipient_logic_row = LogicRow(row=each_recipient,
old_row=each_recipient,
ins_upd_dlt="*",
nest_level=0,
a_session=provider.session,
row_sets=None)
new_allocation_logic_row.link(to_parent=each_recipient_logic_row)
if self.while_calling_allocator is not None:
allocator = self.while_calling_allocator(
new_allocation_logic_row, provider)
else:
allocator = self.while_calling_allocator_default(
new_allocation_logic_row, provider)
if not allocator:
break
provider.log(f'END {str(self)}')
return self
"""
********* Customer Account Deposit Checking Setup - Deposit $100 to CHECKINGTRANS *********
"""
trans_date = datetime(2020, 10, 1)
deposit = models.CHECKINGTRANS(TransId=1, CustNum=1, AcctNum=1,
DepositAmt=100, WithdrawlAmt=0, TransDate=trans_date)
print("\n\nCustomer Account Deposit Checking Setup - Deposit $100 to CHECKINGTRANS ")
session.add(deposit)
session.commit()
print("")
verify_cust = session.query(models.CUSTOMER).filter(models.CUSTOMER.CustNum == 1).one()
logic_row = LogicRow(row=verify_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None)
if verify_cust.TotalBalance == 100.0:
logic_row.log("Customer Account Deposit Checking Setup OK - balance is 100")
assert True
else:
logic_row.log("Customer Account Deposit Checking Setup OK fails - balance not 100")
assert False
session.expunge(verify_cust)
"""
********* Transfer 10 from checking to savings (main test) *********
"""
transfer = models.TRANSFERFUND(TransId=2, FromCustNum=1, FromAcct=1, ToCustNum=1, ToAcct=1, TransferAmt=10, TransDate=trans_date)
print("\n\nTransfer 10 from checking to savings (main test) ")
session.add(transfer)
session.commit()
print("")
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
else:
logic_row.log("ERROR - incorrect adjusted Customer Result")
print("\n--> probable cause: Order customer update not written")
assert False
if post_cust.OrderCount == pre_cust.OrderCount + 1 and\
post_cust.UnpaidOrderCount == pre_cust.UnpaidOrderCount + 1:
pass
else:
logic_row.log("Error - unexpected OrderCounts")
print("\nadd_order, ran to completion\n\n")
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 *
"""
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 - 1000: # 1016 -> 16 ?? 794
logic_row.log("Correct adjusted Customer Result")
assert True
else:
logic_row.log("ERROR - incorrect adjusted Customer Result")
assert False
print("\nadd_payment, ran to completion\n\n")
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 *
"""
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 - 1000: # 1016 -> 16 ?? 794
logic_row.log("Correct adjusted Customer Result")
assert True
else:
logic_row.log("ERROR - Balance not reduced 1000")
assert False, "Balance not reduced 1000"
print("\nadd_payment, ran to completion\n\n")
def test_run(self):
"""
Illustrate re-use with a number of changes:
1 - reassign the order to a different customer
2 - change an OrderDetail (eg, "I'll buy 1 WidgetSet, not 5 Widgets")
a. A different Product
b. A different Quantity
"""
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=pre_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
logic_row = LogicRow(row=pre_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
pre_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
logic_row = LogicRow(row=pre_order,
old_row=pre_order,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
session.expunge(pre_alfki)
session.expunge(pre_anatr)
session.expunge(pre_order)
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
test_order_details = test_order.OrderDetailList
changed_order_detail = None
for each_order_detail in test_order_details:
if each_order_detail.ProductId == 58: # Escargots de Bourgogne, @ $13.25
each_order_detail.ProductId = 48 # Chocolade, @ $12.75
each_order_detail.Quantity = 10 # 40 x 13.25 => 10 x 12.75
break
pre_amount_total = test_order.AmountTotal
post_amount_total = pre_amount_total -\
Decimal(40.0) * Decimal(13.25) +\
Decimal(10.0) * Decimal(12.75)
test_order.CustomerId = "ANATR"
print("\n" + prt("Reparenting *altered* order - new CustomerId: " +
test_order.CustomerId))
print(
f'order amount {pre_amount_total} projected to be {post_amount_total}'
)
session.commit()
print('')
msg = 'Committed... order.amountTotal ' + \
str(pre_amount_total) + ' -> ' + \
str(post_amount_total)
logic_row = LogicRow(row=test_order,
old_row=pre_order,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log(msg)
print("\n")
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 post_alfki.Balance == 56:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
msg = "ERROR - incorrect adjusted Customer Result, " + "should be 56"
self.fail(logic_row.log(msg))
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 post_anatr.Balance == 557.50:
logic_row.log("Correct non-adjusted Customer Result")
assert True
else:
print("\n*** ERROR***")
msg = "ERROR - incorrect adjusted Customer Result, " + "should be 557.50"
self.fail(logic_row.log(msg))
print("\nupd_order_customer_reuse, ran to completion")
self.assertTrue(True)
"""
def test_run(self):
"""
Test 1 - toggle Due Date, to verify no effect on Customer, OrderDetails
Also test join.
"""
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]"))
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)
# logic_row.row.Balance = 0 # force error (for debug)
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
self.assertTrue(True)
else:
self.fail(
logic_row.log(
"\nIncorrect adjusted Customer Result - Balance changed"))
"""
Test 2 - Invalid SalesRep
"""
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]"))
test_order.EmployeeId = 11 # should provoke error - EmployeeId does not exist
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..."
))
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)
# logic_row.row.Balance = 0 # force error (for debug)
if abs(post_cust.Balance - pre_cust.Balance) == 0:
logic_row.log("Correct non-adjusted Customer Result")
self.assertTrue(True)
else:
self.fail(
logic_row.log(
"\nIncorrect adjusted Customer Result - Balance changed"))
print("\nupd_order_required, ran to completion")
self.assertTrue(True)
def toggle_order_shipped(self):
""" toggle Shipped Date, to trigger balance adjustment """
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_cust)
session.expunge(pre_adjusted_product)
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 == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
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:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 2"))
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped + 40:
logic_row.log("Product adjusted properly on ship order")
else:
self.fail(
logic_row.log("Product adjusted improperly on ship order"))
def handle_all(logic_row: LogicRow):
row = logic_row.row
if logic_row.ins_upd_dlt == "ins" and hasattr(row, "CreatedOn"):
row.CreatedOn = datetime.datetime.now()
logic_row.log("early_row_event_all_classes - handle_all sets 'Created_on"'')
def toggle_order_shipped(self):
"""
toggle Shipped Date, to trigger
* balance adjustment
* cascade to OrderDetails
* and Product adjustment
also test join
"""
# fails on post_cust with TypeError: my_load_listener() got an unexpected keyword argument 'attrs'
# @sqlalchemy.event.listens_for(sqlalchemy.orm.Mapper, 'refresh', named=True)
def my_load_listener(target, context):
print("on load!")
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
print(f'type(Base): {type(Base)}'
) # sqlalchemy.ext.declarative.api.DeclarativeMeta
do_listen = False
if do_listen:
event.listen(
sqlalchemy.ext.declarative.api.
DeclarativeMeta, # models.Customer is fine
'load',
my_load_listener)
# , propagate=True) # TypeError: descriptor '__subclasses__' of 'type' object needs an argument
pre_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
supers = pre_cust.__class__.__bases__
for each_super in supers:
print(f'pre_cust super: {type(each_super)}')
session.expunge(pre_cust)
print("")
test_order = session.query(models.Order).filter(models.Order.Id == 11011).\
join(models.Employee).options(joinedload(models.Order.SalesRep)).\
one()
if test_order.ShippedDate is None or test_order.ShippedDate == "":
test_order.ShippedDate = str(datetime.now())
print(
prt("Shipping order - ShippedDate: ['' -> " +
test_order.ShippedDate + "]"))
else:
test_order.ShippedDate = None
print(prt("Returning order - ShippedDate: [ -> None]"))
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:
self.fail(
logic_row.log(
"ERROR - incorrect adjusted Customer Result (not 960 delta)"
))
if post_cust.Balance == 56:
pass
else:
self.fail(logic_row.log("ERROR - balance should be 56"))
if post_cust.UnpaidOrderCount == 3 and pre_cust.UnpaidOrderCount == 4:
pass
else:
self.fail(logic_row.log("Error - UnpaidOrderCount should be 3"))
def test_run(self):
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
session.expunge(pre_alfki)
session.expunge(pre_anatr)
if pre_alfki.Balance != 1016:
self.fail(
"pre_alfki balance not 1016 (database-gold not copied?), value: "
+ str(pre_alfki.Balance))
print("")
test_order = session.query(models.Order).filter(
models.Order.Id == 11011).one() # type : Order
amount_total = test_order.AmountTotal
if test_order.CustomerId == "ALFKI":
test_order.CustomerId = "ANATR"
else:
test_order.CustomerId = "ALFKI"
print(
prt("Reparenting order - new CustomerId: " +
test_order.CustomerId))
session.commit()
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 adjusted Customer Result")
pass
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
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 adjusted Customer Result")
pass
else:
self.fail(
logic_row.log(
"Incorrect adjusted Customer Result - expected 960 difference"
))
print("\nupd_order_customer, ran to completion")
self.assertTrue(True)
else:
logic_row.log("Product adjusted improperly on ship order")
assert False
toggle_order_shipped()
pre_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
session.expunge(pre_adjusted_product)
print("\ndlt_order, shipped... now delete")
delete_cust = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
session.delete(delete_cust)
session.commit()
post_adjusted_product = session.query(
models.Product).filter(models.Product.Id == 58).one()
logic_row = LogicRow(row=post_adjusted_product,
old_row=pre_adjusted_product,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
if post_adjusted_product.UnitsShipped == pre_adjusted_product.UnitsShipped - 40:
logic_row.log("Product adjusted properly on delete customer")
else:
logic_row.log("Product adjusted improperly on delete customer")
assert False
print("\ndlt_order, shipped... deleted - check log")
a. A different Product
b. A different Quantity
"""
pre_alfki = session.query(
models.Customer).filter(models.Customer.Id == "ALFKI").one()
pre_anatr = session.query(
models.Customer).filter(models.Customer.Id == "ANATR").one()
logic_row = LogicRow(row=pre_alfki,
old_row=pre_alfki,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
logic_row = LogicRow(row=pre_anatr,
old_row=pre_anatr,
ins_upd_dlt="*",
nest_level=0,
a_session=session,
row_sets=None)
logic_row.log("starting")
pre_order = session.query(
models.Order).filter(models.Order.Id == 11011).one() # type : Order
logic_row = LogicRow(row=pre_order,
old_row=pre_order,
ins_upd_dlt="*",
nest_level=0,
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")
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)
