In this blog post, we will take a look at data model concepts and how it applies to the art of reactive programming (aka. business logic). We will explore a change to our shopping cart and introduce BOGO (buy-one-get-one) pricing. Espresso Logic reactive programming model uses a combination of declarations and JavaScript on entities (tables) and attributes (columns) to build enterprise scale application API?s. The latest release of Espresso Logic?s breakthrough technology is the ability to have multiple RDBMS connections open. This allows a level of API Virtualization and Resource definitions (i.e. custom API endpoints) to create amazing solutions to business requirements. Sometimes the data model may need to altered or a new database, tables, and attributes added to introduce new features supported by the Espresso Logic Reactive Programming model.
Tables , Columns, and Keys
When building a business application or connecting to an existing SQL database, the core concept of tables and columns can be visualized as a spreadsheet of raw data. When designing a business application or extending an existing legacy model the first step is to identify the primary key(s) for each row. The ability to identify a unique row is critical for Espresso Logic to work correctly. WIthout an identified primary key updates and deletes would be difficult if not impossible to perform. The Primary Key is also used in foreign key declarations (e.g. relationships) to join parent to child. Espresso Logic will use these native relationships and user defined declarations to enforce referential integrity (RI) at the server across databases. The attributes are used for column derivations (formula, sums, counts, parent copy) and the entities hold the cross column validations.
Default Values
The next step in the process may be to identify ?default? values that are required or calculated for each column. One of the most common patterns is the created and modified date. The created date default is set to today and is not changed and the modified date is updated each time the record is updated. In business logic terms, this would be a column formula (createdDT is set to return row.createdDT == null? new Date() : row.createdDT) where as the modifiedDT is set to return new Date() no matter what state change occurs). These new rules can be applied on top of an existing database and will be applied at the server regardless (or in addition to) of the database default value settings. Column derivations (formula) can be more complex and include the full range of JavaScript options including if/then/else, or calling other REST resources or Java/JavaScript code.
Relationships
If each table represents a spreadsheet page, then a workbook would be a collection of tables with ?links? that join or lookup related data. The relationship is defined as a foreign key from the child table to the parent table. Relationships can include one or more column attributes. The advantage of the predefined relationship is that it helps the SQL engine optimize joins and validate related data (RI). A relationship is often described by the roles it plays from parent to child. A common example is the state code field in an address collection. The parent table StateCodeTypes has a primary key stateCode and the Address table has a foreign key reference from the stateCode. Espresso Logic uses these relationships to validate data before writing back to disk. This means that you can add new tables and new user defined relationships for lookup and validation without having to change your existing schema. (See Espresso Logic Multi-database support page). The Resource editor and Live Browser both use these relationships to help build new nested documents and display parent/child data and foreign key data lookups.
Derivations (SUMS, COUNT, MIN, MAX)
When you see a spreadsheet with subtotals and totals ? these are formula expressed on a column or row. In our example, we know we can add formula to each single record to create row totals. For column subtotals, we will introduce a new parent table to hold these aggregate values. The common example is the shopping cart where the order total is the parent and the cart details are the individual rows. Our parent may have columns to sum the order line total amount, the discounts applied, and any tax or shipping costs associated with the row. Counts can be applied to the parent (e.g. you have 3 items in your cart). If your existing database schema cannot be easily changed, simply add a new parent table to a new database and create a user defined relationship from the parent to the existing child. New entries into the child can automatically create the parent (manage parent rule) and create these aggregations. In fact, the concept of a multi-hierarchy rollup is very easy to achieve. Weeks can roll up to months, months to quarters, and quarters into years. Business logic can create the same analysis on your entire SQL database that you would consider using a spreadsheet. If a row derivation is needed, the model will need to be altered to add a new column.
Parent Copy
One special rule is the parent copy ? using a relationship to the parent table, a parent attribute value can be copied down into the selected child column. A common example is the shopping cart, we want to copy the current price of the product down to the cart line item so that the price can be used to calculate the line total (price * quantity). If the parent price changes in the future ? it will not impact the child. If you need to propagate a change to all children, use an Event, described below.
Validations
One of the most powerful concepts in Espresso Logic is the validation. This is a special type of rule that can test the content and context of the row being processed and determine if the entire business transaction should be accepted or rejected. This can be used to validate required values, determine if values are within acceptable ranges, or perform external credit checks using the full access of Java and JavaScript libraries. The validation works within the scope of a business logic transaction, that is, it all passes (accept when) or the entire business transaction is rolled back and a user defined validation message is returned.
Events
Espresso Logic is a REST Server and responds to various event requests to retrieve data GET(Request Event, Row Event, Response Event) and insert/update POST/PUT(Early Event, Event, Commit Event) as part of an event process lifecycle. Events use the JavaScript language and have full access to other JavaScript and Java libraries. These events also have full access to the entity model (tables and columns) as well as relationships to parent entities. Using Espresso Logic helper functions, Events can be used to insert data into other entities (createPersistentBean, getBeanByPrimaryKey), fire REST requests (restGET, restPOST), to other services, lookup data from other entities (getRowsByQuery) or resources (getResource). A common pattern is to audit changes and insert the oldRow values into the audit table. Another example is to send an email when the order payment is confirmed and another one when the order is shipped (orderPaid and orderShipped flags are used to trigger these state change events).
Putting it together
In our shopping cart example, we have been asked to add a new pricing policy (Buy one Get one ? BOGO) to our existing application. We are also told that the max limit per order is 4 and the max limit per customer is 10. Further, these rules only apply to selected products during a selected date range. To make it more difficult our DBA told us we cannot change the data model ? only the logic. We will need to create new tables to hold the BOGO products, effective dates, and limits. We will also need a global customer bogo table to count lifetime limits. Our new tables will be joined to our existing shopping cart, product, and the new pricing rules will be applied to our existing formula.
Logic Summary
Our Bogo_product table has effective dates, product ID, and bogo limits for orders and customers, and the discount price. It is up to the rule itself to determine if a product is used in the BOGO calculation and is within the effective date. The count on the CustomerBogo table is used to determine the lifetime limit. These tables can be added to the existing database or a new database. Now the formula to determine the line item price is invoked to lookup the price and then ask if this is a bogo product within the effective range.
var rows = logicContext.getRowsByQuery("bogo_product",
"select * from bogo_product where id <> "+row.productID +' and effStartDT >= getDate() and effEndDate <= getDate());
for (var i = 0; i < rows.length; i++)
log.debug('Found bogo_product:' + rows[i].name);
Validations are used to enforce lifetime customer and client line item purchase amounts. In Espresso Logic, rules are added and invoked based on a dependency tree of columns and entities. This means you do not need to invoke or ?call? rules, rules are invoked when state change occurs (e.g. insert or update). The image below is an example of the Logic Design Studio relationship editor used to join tables between new and existing databases.
Summary
Espresso Logic can connect to one or more of your existing SQL and NoSQL databases and create an instant REST API for tables, views, and stored procedures. Business Logic can be placed on top of existing entities and attributes or new tables can be added to new databases and joined into your legacy system to create new REST API endpoints that provide sophisticated business transaction logic. Data Model concepts like adding new tables or altering tables to add columns to support business rules is an integral part of the design lifecycle of reactive programming.
