skip to main content
Corticon Studio: Rule Modeling Guide : Writing Rules to access external data : Enabling Database Access for Rules using root-level Entities : Testing the Rulesheet with Database Access enabled
 

Try Corticon Now
Testing the Rulesheet with Database Access enabled
First, let's update the database we have been using in the EDC tutorial to prepare for the features that we want to demonstrate. The Ruletest we just created, CargoLoad.ert, has the aircraft data we want including the primary key, the tailNumber. It actually extends the tutorial's data with one added row. But it has cargo info we want to keep aside for now.
We'll copy the Ruletest, drop those unwanted inputs, and then update the database.
Note: The procedure for connecting and mapping a Vocabulary to an external database, and setting an Input Ruletest to access that database in Read Only and Read/Update modes is discussed in the topic How data from an EDC Datasource integrates into rule output
To load the aircraft data:
1. In the Rule Project Explorer, copy and paste the CargoLoad.ert file. Name the copy AircraftLoader.ert.
2. Open AircraftLoader.ert.
3. In the Input area, click on FlightPlan, and then press Delete.
4. Select the menu option Ruletest > Testsheet > Database Access > Read/Update.
5. Select the menu command Ruletest > Testsheet > Run Test.
Look at the Aircraft table in the database. You see the updated values and the new row:
To make the test effective, we need to add some heavy cargo to one of the flight plans. Here, we created four SQL query lines to add four new Cargo manifests to one flight:
INSERT INTO Cargo.dbo.Cargo
(manifestNumber,RflightPlanAssoc_flightNumber,
needsRefrigeration,container,volume,weight)
VALUES ('625E',102,null,null,80,50000);
INSERT INTO Cargo.dbo.Cargo
(manifestNumber,RflightPlanAssoc_flightNumber,
needsRefrigeration,container,volume,weight)
VALUES ('625F',102,0,null,100,40000);
INSERT INTO Cargo.dbo.Cargo
(manifestNumber,RflightPlanAssoc_flightNumber,
needsRefrigeration,container,volume,weight)
VALUES ('625G',102,0,null,90,20000);
INSERT INTO Cargo.dbo.Cargo
(manifestNumber,RflightPlanAssoc_flightNumber,
needsRefrigeration,container,volume,weight)
VALUES ('625H',102,1,null,50,50000);
Copy the text in the codeblock and paste it into a new SQL Query in your database, and execute it.

Alternate approach: Using a Ruletest to load a database

You could instead create a Ruletest, CargoLoader, with these values and the associated flightPlan, entering the values as shown, and then running the test in Read/Update mode:
Figure 202. Using a Ruletest to add Cargo rows to the connected external database

Setting up the test

The Cargo table now shows that there are eight items, five of which are assigned to one flight:
Figure 203. Cargo Table from Database
Let's create a new Ruletest that uses the test subject we created earlier, the CargoLoad.ers Rulesheet: CargoLoad Rulesheet. We will create a new Input Ruletest that just takes the FlightPlan entity from the scope, and then enter the flightNumber value 102. When we run the test, the Output is identical to the input and there are no messages. That seemed to do nothing:
Figure 204. Ruletest of FlightPlan Seed Data
Notice that the only data necessary to provide in the Input Ruletest is a FlightPlan.flightNumber value – since this attribute serves as the primary key for the FlightPlan table, Studio has all the “seed data” it needs to retrieve the associated Cargo records from the Cargo database table. In addition to retrieving the load.weight collection, we also needed all Aircraft records from the Aircraft table. But this didn’t happen – no Aircraft records were retrieved, so the rule’s comparison couldn’t be made, so the rule couldn’t fire. We should have expected this since we have already learned that database access for root-level terms is disabled by default.
Let's set the Ruletest to read data from the database and return everything that it finds. Toggle the menu options in the Ruletest > Testsheet menu as shown:
When we run the test again, the output is the same as the input and there are no messages.

Extend to Database

What we want to do now is set the Rulesheet to Extend to Database, and then see how it impacts the test. On the CargoLoad.ers Rulesheet, right-click Aircraft in the Scope area, and then select Extend to Database, as shown:
Save your Rulesheet to ensure that the changes take effect. Now, retest the same Input Ruletest shown in Input Ruletest with Seed Data. The results are as follows:
Figure 205. Results Ruletest Showing a Successful Extend-to-Database Retrieval
These results are much different! Corticon has successfully retrieved all Aircraft records, performed the summation of all the cargo in the given flightplan, and identified an Aircraft record that fails the test. Given this set of sample data, it is the Aircraft with tailNumber N1004 that receives the Violation message.

Returning all instances can be overwhelming

While this rich relational data retrieval is good to see, we are only have four planes and five packages in the flight plan. What if we have 1,000 planes and hundreds of thousands of packages every day? That amount of data would be overwhelming. So what we can do is constrain the return data to just relevant new information by toggling the Ruletest's return option to Return Incoming/New Entity Instances Only, as shown:
The data that returns is drawn only from those entities that were:
*Directly used in the rules.
*Present in the request message.
*Generated by the rules (if any).
Note: This option can be set in Deployment Descriptor file (.cdd), or as a parameter in the 9-parameter version of addDecisionService method in the Server API scripts.
When you run the Ruletest now, the output is unchanged yet we got the Violation message as to which plane cannot be assigned that flight plan.
That result is concise, providing what could be all we really wanted to know in this test.