First, you need to update the database in the EDC tutorial to prepare for the features that will be demonstrated. The Ruletest, CargoLoad.ert, has the aircraft data including the primary key. Copy the Ruletest, drop those unwanted inputs, and then update the database column tailNumber. That edit actually extends the tutorial's data with one added row that is cargo info we want in this topic.

Note: The procedure for connecting and mapping a Vocabulary to an external database, and setting an Input Ruletest to access that database in Read OnlyRead/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 Project Explorer, copy and paste the CargoLoad.ert file. Name the copy AircraftLoader.ert.
  2. Open AircraftLoader.ert.
  3. In the Input area, click 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, you need to add some heavy cargo to one of the flight plans. Here are 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 then execute it.

Alternative 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 1. 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 2. Cargo Table from Database
Now, create a new Ruletest that uses the test subject we created earlier, the CargoLoad.ers Rulesheet: CargoLoad Rulesheet. You will create a new Input Ruletest that just takes the FlightPlan entity from the scope, and then enter the flightNumber value 102. When you run the test, the output is identical to the input, and there are no messages. That test seemed to do nothing:
Figure 3. 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 no Aircraft records were retrieved, therefore the rule’s comparison couldn’t be made, so the rule couldn’t fire. This behavior was expected because that database access for root-level terms is disabled by default.

Now 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 you run the test again, the output is the same as the input and there are no messages.

Extend to Database

Now you will set the Rulesheet to Extend to Database, and then see how it affects 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 4. Results Ruletest showing a successful Extend-to-Database retrieval

These results are much different! Corticon successfully retrieved all Aircraft records, performed the summation of all the cargo in the given flight plan, 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, there are only four planes and five packages in the flight plan. What if there are 1,000 planes and hundreds of thousands of packages every day? That amount of data would be overwhelming. So what you 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 taken 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 a 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. You see a Violation message as to which plane cannot be assigned that flight plan.



That result is concise, providing the information you wanted from this test.