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 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 audit_by_event(row: Employee, old_row: Employee, logic_row: LogicRow): tedious = False # tedious code to repeat for every audited class if tedious: # see instead the following rule extension - nw_copy_row if logic_row.are_attributes_changed([Employee.Salary, Employee.Title]): copy_to_logic_row = logic_row.new_logic_row(EmployeeAudit) copy_to_logic_row.link(to_parent=logic_row) copy_to_logic_row.set_same_named_attributes(logic_row) copy_to_logic_row.insert(reason="Manual Copy " + copy_to_logic_row.name) # triggers rules...
def toggle_order_shipped(): """ 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).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]")) 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: logic_row.log(post_cust, "ERROR - incorrect adjusted Customer Result") assert False if post_cust.Balance == 0: pass else: logic_row.log("ERROR - balance should be 0") assert False if post_cust.UnpaidOrderCount == 2 and pre_cust.UnpaidOrderCount == 3: pass else: logic_row.log("Error - UnpaidOrderCount should be 2") assert False
def execute(self): """ Create allocation row for each recipient until while_calling_allocator returns false :return: """ self.from_provider_row.log("Allocate " + self.from_provider_row.name) for each_recipient in self.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=self.from_provider_row.nest_level + 1, a_session=self.from_provider_row.session, row_sets=self.from_provider_row.row_sets) new_allocation_logic_row.link(to_parent=self.from_provider_row) each_recipient_logic_row = LogicRow(row=each_recipient, old_row=each_recipient, ins_upd_dlt="*", nest_level=0, a_session=self.from_provider_row.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, self.from_provider_row) else: allocator = self.while_calling_allocator_default(new_allocation_logic_row, self.from_provider_row) if not allocator: break return self
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 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 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 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
session.expunge(pre_cust) """ ********* 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()
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 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")
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)
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): 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)
def temp_debug(a_session, bug_explore, row_cache): """ do not delete - see description in nw/tests/upd_order_reuse """ for each_instance in a_session.dirty: table_name = each_instance.__tablename__ if table_name.startswith("OrderDetail"): bug_explore[0] = each_instance else: bug_explore[1] = each_instance order_detail_first = False # true triggers defer if order_detail_first: each_instance = bug_explore[0] old_row = get_old_row(each_instance) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_cache) logic_row.update(reason="client") each_instance = bug_explore[1] old_row = get_old_row(each_instance) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_cache) logic_row.update(reason="client") else: each_instance = bug_explore[1] old_row = get_old_row(each_instance) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_cache) logic_row.update(reason="client") each_instance = bug_explore[0] old_row = get_old_row(each_instance) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_cache) logic_row.update(reason="client")
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)
def before_flush(a_session: session, a_flush_context, an_instances): """ Logic Execution processes LogicRows: row and old_row Note old_row is critical for: * user logic (did the value change? by how much?) * performance / pruning (skip rules iff no dependent values change) * performance / optimization (1 row adjustments, not expensive select sum/count) """ """ Logic Phase """ logic_bank.logic_logger.info(f'Logic Phase:\t\tROW LOGIC(session={str(hex(id(a_session)))}) (sqlalchemy before_flush)\t\t\t') row_sets = RowSets() # type : RowSet client_inserts = [] for each_instance in a_session.dirty: row_sets.add_submitted(each_instance) for each_instance in a_session.new: row_sets.add_submitted(each_instance) """ inserts first... SQLAlchemy queues these on a_session.new (but *not* updates!) so, process the client changes, so that triggered inserts (eg. audit) aren't run twice """ client_inserts.append(each_instance) bug_explore = None # None to disable, [None, None] to enable if bug_explore is not None: # temp hack - order rows to explore bug (upd_order_reuse) temp_debug(a_session, bug_explore, row_sets) else: for each_instance in a_session.dirty: old_row = get_old_row(each_instance, a_session) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.update(reason="client") for each_instance in client_inserts: # a_session.new: logic_row = LogicRow(row=each_instance, old_row=None, ins_upd_dlt="ins", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.insert(reason="client") # if len(a_session.deleted) > 0: # print("deleting") for each_instance in a_session.deleted: logic_row = LogicRow(row=each_instance, old_row=None, ins_upd_dlt="dlt", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.delete(reason="client") """ Commit Logic Phase """ logic_bank.logic_logger.info(f'Logic Phase:\t\tCOMMIT(session={str(hex(id(a_session)))}) \t\t\t\t\t\t\t\t\t\t') processed_rows = dict.copy(row_sets.processed_rows) # set in LogicRow ctor for each_logic_row_key in processed_rows: each_logic_row = processed_rows[each_logic_row_key] logic_bank.engine_logger.debug("visit: " + each_logic_row.__str__()) commit_row_events = rule_bank_withdraw.rules_of_class(each_logic_row, CommitRowEvent) for each_row_event in commit_row_events: each_logic_row.log("Commit Event") each_row_event.execute(each_logic_row) """ Proceed with sqlalchemy flush processing """ logic_bank.logic_logger.info(f'Logic Phase:\t\tFLUSH(session={str(hex(id(a_session)))}) (sqlalchemy flush processing) \t')
def before_flush(a_session: session, a_flush_context, an_instances): """ Logic Execution processes LogicRows: row and old_row Note old_row is critical for: * user logic (did the value change? by how much?) * performance / pruning (skip rules iff no dependent values change) * performance / optimization (1 row adjustments, not expensive select sum/count) """ """ Logic Phase """ logic_bank.logic_logger.debug("Logic Phase:\t\tROW LOGIC (sqlalchemy before_flush)\t\t\t") # print("\n***************** sqlalchemy calls logic_bank\n") row_sets = RowSets() # type : RowSet for each_instance in a_session.dirty: row_sets.add_submitted(each_instance) bug_explore = None # None to disable, [None, None] to enable if bug_explore is not None: # temp hack - order rows to explore bug (upd_order_reuse) temp_debug(a_session, bug_explore, row_sets) else: for each_instance in a_session.dirty: table_name = each_instance.__tablename__ old_row = get_old_row(each_instance) logic_row = LogicRow(row=each_instance, old_row=old_row, ins_upd_dlt="upd", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.update(reason="client") for each_instance in a_session.new: table_name = each_instance.__tablename__ logic_row = LogicRow(row=each_instance, old_row=None, ins_upd_dlt="ins", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.insert(reason="client") for each_instance in a_session.deleted: table_name = each_instance.__tablename__ logic_row = LogicRow(row=each_instance, old_row=None, ins_upd_dlt="dlt", nest_level=0, a_session=a_session, row_sets=row_sets) logic_row.delete(reason="client") """ Commit Logic Phase """ logic_bank.logic_logger.debug("Logic Phase:\t\tCOMMIT \t\t\t\t\t\t\t\t\t") processed_rows = dict.copy(row_sets.processed_rows) for each_logic_row_key in processed_rows: each_logic_row = processed_rows[each_logic_row_key] logic_bank.engine_logger.debug("visit: " + each_logic_row.__str__()) commit_row_events = rule_bank_withdraw.rules_of_class(each_logic_row, CommitRowEvent) for each_row_event in commit_row_events: each_logic_row.log("Commit Event") each_row_event.execute(each_logic_row) """ Proceed with sqlalchemy flush processing """ logic_bank.logic_logger.debug("Logic Phase:\t\tFLUSH (sqlalchemy flush processing \t")
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")
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")
""" 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,