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)
