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)
