Docs‎ > ‎Logic Designer‎ > ‎

Database

A project has multiple database locations, one of which is current.  

Your database can be in the cloud, or on-premise.  For on-premise database, see the section on connectivity, below.

Espresso Logic update processing relies on database services for locking and transaction management.  Retrieval processing leverages the database engine's services for query optimization (index selection, join strategy).


Connecting to your Database

Espresso Logic needs access to your database, whether it be stored in the cloud or within your firewall.  


Database Types

We are always adding new databases, so please contact us for a current list, or to suggest new requirements.  As of this writing, we support:




Existing Database


You begin by connecting, which creates a project (API), as shown here.  Firewalls require accommodations.  See here for more information.

Connectivity can be trying.  Please contact support for help.  See here for suggestions on Microsoft SQL Server.

You can also start with a new database, as described in the following sections.

New Database

You can create a new database using your current approaches, then connect to Espresso. 

Alternatively, you can use the pre-created schema described below.

Pre-created Schema

When you create your account, Espresso also creates an empty Schema you can use for your own project.  The database credentials are provided in your registration email.  In addition, Espresso creates a default project that points to this database.

This enables you to get started right away.  For example, you can use phpMyAdmin to create your tables and relationships, then use Espresso to create your API, Logic and Security, and use them in conjunction with the Live Browser.








Multiple Databases

You can define Multiple Databases, which enables you to combine these in Resources, build rules between databases, and access them in the Live Browser.


Mongo DB

Most of the sections below (schema, database administration etc) do not apply to a NoSQL database such as MongoDB.  You can find more information on Mongo here.


Schema as Data Model

Espresso does not duplicate the information from your database catalogs to determine the table names, column names, and foreign key names.  All of these are obtained from the database catalog, so it is not necessary to maintain multiple copies of the same information.

Use your existing tools to manage your schema.  More information is described below.

You do, however, need to understand that Espresso Logic references to database objects (tables, columns, foreign key names) may break if you rename / remove database objects.

Foreign Key-based Relationships

Espresso requires references to foreign key relationships to express Logic (sum/count, parent references etc), retrieval joins, and Live Browser support for Master/Detail, Lookups, and Automatic Joins.  A strong understanding of these Foreign Keys is therefore critical - see here for background, examples and terminology..  

The underlying concept is a one-to-many relationship between 2 tables in a relational database.  Relationships are discovered by Foreign Keys defined in your database.  The Sample Database has examples of multiple different kinds of relationships.

We use the following familiar terminology:
  • Parent - Within a Relationship, the table containing the Primary Key on the "one" side.  For example, Purchaseorder is a parent to Lineitem.

  • Child - Within a Relationship, the table containing the Foreign Key on the "many" side.  For example, Purchaseorder is a child to Customer.  PUT/POST JSON provides mechanisms to associate a child with its parents.

  • Parent Role - the name by which the Child refers to the Parent

  • Child Role - the name by which the Parent refers to the Children

Determining Role Names

Relational database catalogs do not specify role names, so we must determine them as described here.  In most cases, these defaults mean you don't need to worry about this; however, multiple relationships between tables and databases without Foreign Key names require special consideration.

By default:
  • Parent Role are defaulted to the name of the Foreign Key.  If this is not present, the Parent Table Name is used, as shown below
    CONSTRAINT product FOREIGN KEY (product_name) REFERENCES products (name) ON UPDATE CASCADE,

  • Child Role Names are defaulted to the name of the Child Table concatenated with "List".  So, for example, Customers could reference (perhaps in a sum rule) OrdersList.

Naming Alert

Role Names must not conflict with attribute names.
The defaults above don't work when there are multiple relationships between the same two tables, such as in the Sample (Department has worksFor and onLoan Employees).  You can use the Logic Designer to specify your Role Names.  Or, you can encode your role names into your foreign key validation names like this:

ParentRoleName__ChildRoleName

for example:

 CONSTRAINT reportsTo__reportingEmployees FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT onLoanTo__onLoanEmployees FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE




Virtual Foreign Keys

Many database schemas do not provide Foreign Key definitions.  Espresso therefore provides Virtual Foreign Keys, which enable critical pieces of functionality such as API Definition, rules, Live Browser, etc.

Note that the related tables can be in different databases.



Database Administration

Espresso does not replicate your schema (model) in the Repository.  You can freely make changes to your schema using your existing tools and procedures, but please review the topics below.

The signup process creates a test database for the Espresso Logic Demo project; your registration email provides credentials.

Use Any Tool

You can manage your schema using the tools you are currently familiar with.


phpMyAdmin

Your Service Creation mail provides a link to a phpMyAdmin, shown below.  Here, we have used phpMyAdmin to rename Customer to Customers, intentionally invalidating the Repository as described in the following sections.

You can configure Espresso to access your database, as follows:



Schema Evolution and Caching

When you first create an active connection with the Connect Wizard or manually in the Databases tab, the database meta-data is read.  Depending on the size of the database and the latency to the database server, this may take significant time.  Once the meta-data has been read, it is cached in the Espresso administration database and shared between nodes.  

Normally, this is exactly what you want.  However, as the schema evolves, Espresso needs to be told to scan the database meta-data again.  Whenever the schema has changed, press the Reload Schema button to flush the cache and reload the schema.


Verify

The Object Model built using Schema Discovery is not a code generation process.  It always matches your current schema (subject to Schema caching)

Changes to your schema can make Rule Base objects (e.g., Resources) invalid, due to dangling references.   Use the procedure below to verify your project:
  1. Reload your Schema
  2. Verify you Project
  3. Review Issues
These steps are shown in the sub-sections below.

Reload Schema



Verify Project

Use the Projects screen, and click the Verify button in the lower right.

This will identify resources that refer to objects that no longer exist (e.g., they were deleted or renamed), and log them as Issues (described in next section).



Issues

If errors are detected, the Problems Navigation will be highlighted as shown below.  You can click it to see / address the errors.



Virtual Foreign Keys

Foreign Keys are useful for defining multi-table resources, Object Model accessors, rules, and the Live Browser.   Yet, many schemas do not define these.

Espresso therefore provides a mechanism to define Foreign Keys in Espresso, instead of the schema.  The related tables can be in the same database, or different databases.  See the following section.


Relationship Editor

This editor under the database tab allows additional relationships to be created between parent and child tables.  This is useful when creating Resources that require 'joins' between tables.  The Live Browser will use the existing relationships to create navigation, tab panels, parent pick choices using these definitions.  

Note: the system will reload the schema, so give it a moment.



Synchronizing data with Logic

To maintain high performance, the Logic Engine presumes that existing data matches your logic.  For example, in Logic Demo, we assume that the Customer balance is correct, so we can adjust it on purchaseorder changes and avoid expensive aggregate queries.

If you alter the logic, you will therefore need to bring your current into conformance.  For example, here are the (MySql) queries used for Logic Demo:

update lineitem item set item.product_price = (select price from product p where p.product_number = item.product_number);

update lineitem item set item.amount = item.qty_ordered * item.product_price;

update purchaseorder po set po.amount_total = 
(select sum(item.amount) from lineitem item where (item.order_number = po.order_number));

update customer cust set cust.balance = 
(select sum(po.amount_total) from purchaseorder po where po.customer_name = cust.name and po.paid = false);


For the following rules:

If you are using SQL/Server, a similar script would be:

update [dbo].[Customers] set Balance = 
  (select sum(orders.AmountTotal) from [dbo].[Orders] orders
          where (orders.CustomerID = [dbo].[Customers].CustomerID));

Best Practices

We suggest the following.

Explicit Foreign Key Definitions

Foreign Keys should be defined via Validations, including a Validation Name.


Database-specific Information

Oracle

Click here to see Oracle-specific information.

Microsoft SQL Server

Click here to see Microsoft SQL Server specific information.