def toggle_order_shipped(): """ toggle Shipped Date, to trigger balance adjustment """ """ also test join. session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all() """ pre_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one() session.expunge(pre_cust) print("") test_order = session.query(models.Order).filter(models.Order.Id == 11011).join(models.Employee).one() if test_order.ShippedDate is None or test_order.ShippedDate == "": # with restored db, cust[ALFKI] has bal 960 & 3 unpaid orders, Order[11011) is 960, unshipped test_order.ShippedDate = str(datetime.now()) print(prt("Shipping order - ShippedDate: ['' -> " + test_order.ShippedDate + "]" + " for customer balance: " + str(pre_cust.Balance) + ", with UnpaidOrderCount: " + str(pre_cust.UnpaidOrderCount))) else: test_order.ShippedDate = None print(prt("Returning order - ShippedDate: [ -> None]")) insp = inspect(test_order) # session.commit() print("") post_cust = session.query(models.Customer).filter(models.Customer.Id == "ALFKI").one() logic_row = LogicRow(row=post_cust, old_row=pre_cust, ins_upd_dlt="*", nest_level=0, a_session=session, row_sets=None) if abs(post_cust.Balance - pre_cust.Balance) == 960: logic_row.log("Correct adjusted Customer Result") assert True else: row_prt(post_cust, "\nERROR - incorrect adjusted Customer Result") print("\n--> probable cause: Order customer update not written") row_prt(pre_cust, "\npre_alfki") assert False
def 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 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._tables: 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
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)
""" 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)
def deposit_rule(self, a_rule: 'AbstractRule'): if a_rule.table not in self._tables: self._tables[a_rule.table] = TableRules() table_rules = self._tables[a_rule.table] table_rules.rules.append(a_rule) engine_logger.debug(prt(str(a_rule)))
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/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)
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")
basedir = os.path.abspath(os.path.dirname(__file__)) basedir = os.path.dirname(basedir) print("\n****************\n" " IMPORTANT - create banking.db from banking-gold.db in " + basedir + "/db/" + "\n****************") banking_loc = basedir + "/db/banking.db" banking_source = basedir + "/db/banking-gold.db" copyfile(src=banking_source, dst=banking_loc) import banking as banking conn_string = banking.conn_string # "mysql://*****:*****@127.0.0.1:3309/banking" conn_string = "sqlite:///" + banking_loc engine = sqlalchemy.create_engine(conn_string, pool_pre_ping=True, echo=True) # sqlalchemy sqls... session_maker = sqlalchemy.orm.sessionmaker() session_maker.configure(bind=engine) session = session_maker() by_rules = True # True => use rules, False => use hand code (for comparison) rule_list = None db = None if by_rules: rule_bank_setup.setup(session, engine) activate_basic_rules() rule_bank_setup.validate(session, engine) # checks for cycles, etc print("\n" + prt("session created, listeners registered\n"))
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)