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.
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:
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.
You can create a new database using your current approaches, then connect to Espresso.
Alternatively, you can use the pre-created schema described below.
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.
You can define Multiple Databases
, which enables you to combine these in Resources, build rules between databases, and access them in the Live Browser.
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.
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:
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.
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.
You can manage your schema using the tools you are currently familiar with.
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.
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:
- Reload your Schema
- Verify you Project
- Review Issues
These steps are shown in the sub-sections below.
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).
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.
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));
We suggest the following.
Explicit Foreign Key Definitions
Foreign Keys should be defined via Validations, including a Validation Name.
to see Oracle-specific information.
Microsoft SQL Server
to see Microsoft SQL Server specific information.