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 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 setup_db(): """ copy db/database-gold.db over db/database.db""" print("\n" + prt("restoring database-gold\n")) basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) print("\n********************************\n" " IMPORTANT - create database.db from database-gold.db in " + basedir + "/nw/db/\n" + " - from -- " + prt("") + "\n********************************") nw_loc = os.path.join(basedir, "db/database.db") nw_source = os.path.join(basedir, "db/database-gold.db") copyfile(src=nw_source, dst=nw_loc)
def copy_db_from_gold(): """ copy db/database-gold.db over db/database.db""" print("\n" + prt("restoring database-gold\n")) basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) print("\n********************************\n" " IMPORTANT - create database.db from database-gold.db in " + basedir + "/payment_allocation/db/\n" + " - from -- " + prt("") + "\n********************************") db_loc = os.path.join(basedir, "db/database.db") db_source = os.path.join(basedir, "db/database-gold.db") copyfile(src=db_source, dst=db_loc)
def test_run(self): """ Test State Transition Logic - raise over 20% should fail due to credit limit exceeded (catch exception to verify) """ bad_employee_raise = session.query(models.Employee).filter(models.Employee.Id == 1).one() bad_employee_raise.Salary = bad_employee_raise.Salary * Decimal('1.1') did_fail_as_expected = False try: session.commit() except: session.rollback() did_fail_as_expected = True if not did_fail_as_expected: self.fail("too-small should have failed constraint, but succeeded") else: print("\n" + prt("puny raise failed constraint as expected.")) print("\nupd_employee_salary, ran to completion") self.assertTrue(True)
def update_referenced_parent_attributes(self, dependencies: list): """ Used by Formulas and constraints log their dependence on parent attributes This sets RuleBank.TableRules[mapped_class].referring_children dependencies is a list But, can't do this now, because meta_contains_role_name = False So, do it on the fly in logic_row (which is an ugh) """ meta_contains_role_name = False if meta_contains_role_name is False: return else: meta_data = rule_bank_withdraw.get_meta_data() child_meta = meta_data.tables[self.table] parent_role_name = dependencies[1] foreign_keys = child_meta.foreign_keys for each_foreign_key in foreign_keys: # eg, OrderDetail has OrderHeader, Product each_parent_class_name = each_foreign_key.name each_parent_role_name = each_foreign_key.key if parent_role_name == each_parent_role_name: # eg, OrderHeader rule_bank = RuleBank() if each_parent_class_name not in rule_bank.orm_objects: self._tables[rule_bank] = TableRules() table_rules = self._tables[rule_bank] if table_rules.referring_children is None: table_rules.referring_children = {} if parent_role_name not in table_rules.referring_children: table_rules.referring_children[parent_role_name] = [] table_rules.referring_children.append(dependencies[2]) engine_logger.debug( prt("child parent dependency: " + dependencies[1])) break
def setup_db(): """ copy db/database-gold.sqlite3 over db/database.sqlite3""" import os from shutil import copyfile from logic_bank.util import prt print("\n" + prt("restoring database-gold\n")) basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) print("\n********************************\n" " IMPORTANT - create database.sqlite3 from database-gold.sqlite3 in " + basedir + "/payment_allocation/db/\n" + " - from -- " + prt("") + "\n********************************") db_loc = os.path.join(basedir, "database.sqlite3") db_source = os.path.join(basedir, "database-gold.sqlite3") copyfile(src=db_source, dst=db_loc)
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 copy_gold_over_db(): """ copy db/database-gold.db over db/database.db""" basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) print( "\n********************************\n" " IMPORTANT - copy_gold_over_db: copy db/database.db from db/database-gold.db in " + basedir + "/nw/db/\n" + " - from -- " + prt("") + " at: " + str(datetime.now()) + "\n********************************") nw_loc = os.path.join(basedir, "db/database.db") nw_source = os.path.join(basedir, "db/database-gold.db") copyfile(src=nw_source, dst=nw_loc)
def setup(session: session): """ setup legacy logic only implements 5 basic check-credit rules other Use Cases not implemented only tested for tests/add_order tests will fail that verify counts :param session: :return: """ # target, modifier, function print("\n" + prt("legacy setup - register listeners")) event.listen(session, "before_flush", before_flush)
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"))
do_engine_logging = False # TODO move to config file, reconsider level engine_logger = logging.getLogger('engine_logger') # for internals if do_engine_logging: engine_logger.setLevel(logging.DEBUG) handler = logging.StreamHandler(sys.stdout) handler.setLevel(logging.DEBUG) formatter = logging.Formatter( '%(message)s - %(asctime)s - %(name)s - %(levelname)s') handler.setFormatter(formatter) engine_logger.addHandler(handler) basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) banking_loc = basedir + "/db/database.db" banking_source = basedir + "/db/database-gold.db" conn_string = "sqlite:///" + banking_loc engine = sqlalchemy.create_engine(conn_string, pool_pre_ping=True, echo=False) # sqlalchemy sqls... session_maker = sqlalchemy.orm.sessionmaker() session_maker.configure(bind=engine) session = session_maker() rule_list = None db = None LogicBank.activate(session=session, activator=activate_basic_rules) print("\n" + prt("session created, listeners registered\n"))
print("nw_before_flush flushing New! --> " + str(each_instance)) obj_class = each_instance.__tablename__ if obj_class == "OrderDetail": order_detail_flush_delete(each_instance, a_session) elif obj_class == "Order": order_flush_delete(each_instance, a_session) print("nw_before_flush EXIT") """ Initialization 1 - Connect 2 - Register listeners (either hand-coded ones above, or the logic-engine listeners). """ print("\n" + prt("nw/logic/__init__.py BEGIN - setup logging, connect to db, register listeners\n")) # Initialize Logging import logging import sys logic_logger = logging.getLogger('logic_logger') # for debugging user logic logic_logger.setLevel(logging.DEBUG) handler = logging.StreamHandler(sys.stdout) handler.setLevel(logging.DEBUG) formatter = logging.Formatter('%(message)s - %(asctime)s - %(name)s - %(levelname)s') handler.setFormatter(formatter) logic_logger.addHandler(handler) do_engine_logging = False engine_logger = logging.getLogger('engine_logger') # for internals
Quantity=20000, UnitPrice=18, Discount=0) bad_order.OrderDetailList.append(bad_item2) did_fail_as_expected = False try: session.commit() except: session.rollback() did_fail_as_expected = True if not did_fail_as_expected: raise Exception("huge order expected to fail, but succeeded") else: print("\n" + prt( "huge order failed credit check as expected. Now trying valid order, 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)
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}') insp = inspect(test_order) 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)
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) 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)) insp = inspect(test_order) 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 non-adjusted Customer Result")
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 deposit_rule(self, a_rule: 'AbstractRule'): if a_rule.table not in self.orm_objects: self.orm_objects[a_rule.table] = TableRules() table_rules = self.orm_objects[a_rule.table] table_rules.rules.append(a_rule) engine_logger.debug(prt(str(a_rule)))
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)
from nw.logic import session # opens db, activates logic listener <-- """ toggle Due Date, to verify no effect on Customer, OrderDetails """ """ 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.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")
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 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"))
from sqlalchemy.orm import session from logic_bank.logic_bank import LogicBank from logic_bank.rule_bank import rule_bank_withdraw # FIXME design why required to avoid circular imports?? # from logic_bank.rule_bank import logic_bank_setup from nw.logic.rules_bank import declare_logic import nw.logic.legacy.setup as legacy_setup from logic_bank.util import prt """ Initialization 1 - Connect 2 - Register listeners (either hand-coded ones above, or the logic-engine listeners). """ print("\n" + prt("BEGIN - setup logging, connect to db, register listeners")) # Initialize Logging import logging import sys logic_logger = logging.getLogger('logic_logger') # for debugging user logic logic_logger.setLevel(logging.DEBUG) handler = logging.StreamHandler(sys.stdout) handler.setLevel(logging.DEBUG) formatter = logging.Formatter( '%(message)s - %(asctime)s - %(name)s - %(levelname)s') handler.setFormatter(formatter) logic_logger.addHandler(handler) do_engine_logging = False
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)