Create a database

Let’s create a database named Scenario1.

On the Start menu, choose SQL Server Management Studio 20.

In the Connect Object Explorer window:

• Select SQL Server Authentication in the Authentication drop-down.
• Enter sa in the Username field.
• Enter sqlserver2019 in the Password field.
• Click Connect.

  
  
  

• Right-click Databases and select New Database.

  
  
  

• Enter Scenario1 in the Database name field and click OK.

  
  
  

The database Scenario1 gets created and is displayed in the Databases folder.

Add the EDC Datasource to the Vocabulary

First, we need to declare that we want to use an EDC Datasource.

In the Studio, open Cargo.ecore under Scenario1 in the Connecting_EDC rule project.

Choose the Vocabulary menu command Add Datasource > Add EDC Datasource.

Set up entities for mapping

The next step is to set up the Vocabulary’s entities for mapping. To do this, you configure each entity to persist to the database and choose a primary key for each entity.

EDC gives you a number of options for assigning primary keys. For SQL Server, you could choose an Identity strategy. For Oracle, you could choose a Sequence strategy. A more common option is to assign an attribute as the primary key for the entity. This makes sense if the attribute identifies the entity. In this tutorial, you will use this technique.

Select the Aircraft entity in the Vocabulary. In the Properties editor on the right, click the Datastore Persistent drop-down, and then select Yes.

On the Datasource pulldown, choose EDC. The icon of the Aircraft entity changes to include a database icon, indicating that the Aircraft entity is now configured for EDC and will be stored in the database together with its attributes and associations.

Let’s assign tailNumber as the primary key for Aircraft.

Select the Entity Identity drop-down and select tailNumber.

The tailNumber attribute in the Aircraft entity will now be mapped as its primary key, so it moves up to the top of the list of attributes in the entity, and is marked with an asterisk.

Configure the other two entities—Cargo and FlightPlan.

• For Cargo, choose manifestNumber as the Entity Identity.
• For FlightPlan, choose flightNumber as its Entity Identity.

Your Vocabulary will look like this:

Define database connection properties in the Vocabulary

The next step is to define the database connection properties in the Vocabulary. This enables Corticon to connect to the database and generate the schema from the Vocabulary.

First, choose File > Save to store what we have done so far.

Now, we’ll define database connection properties:

• Select the Cargo root node in the Vocabulary tree, and then click the EDC tab.
• Specify the following database properties:
  • Database Server: Microsoft SQL Server 2014
  • Database URL: jdbc:progress:sqlserver://localhost:1433;databaseName=Scenario1
  • Username: sa
  • Password: sqlserver2014password
  • Finally, let’s test if the Vocabulary is able to connect to the database. Click CONNECTION Test. You see a message indicating that the connection was successful.

  • 

Create a database schema from the Vocabulary

Now that the Vocabulary is connected to the database, you can create a database schema from it. To do this, click Create/Update on the EDC tab.

In the Schema Update Warning dialog box, click Yes.

Corticon Studio takes a few seconds to create the database schema; after which you get a message stating that the procedure completed successfully.

Let’s verify that a table is created in the database for each entity in the Vocabulary. In SQL Server Management Studio, expand Databases > Scenario1 > Tables folder. You see the tables:

Set up test data

Now that the Vocabulary and database are connected and mapped, let’s do some testing. Although this is typically performed by the rule modeler, doing it as part of this tutorial lets you verify that the rules are able to access the database. Let’s populate the database with some sample data and then run the Ruletests.

Populating sample data

Follow these steps to populate sample data:

• In SQL Server Management Studio, select File > New > Query with Current Connection. A new SQL Script file opens in a text editor.

  
  
  

• Copy and paste these lines of code into the text editor:

  INSERT INTO Scenario1.DBO.Aircraft (aircraftType, maxCargoVolume, maxCargoWeight, tailNumber) VALUES ('747', 7500, 150000, 'N111A'); INSERT INTO Scenario1.DBO.FlightPlan (RaircraftAssoc_tailNumber, flightNumber) VALUES ('N111A', 111); INSERT INTO Scenario1.DBO.Cargo (RflightPlanAssoc_flightNumber, container, manifestNumber, volume, weight) VALUES (111, 'STANDARD', '625A', 3000, 100000); INSERT INTO Scenario1.DBO.Cargo (RflightPlanAssoc_flightNumber, container, manifestNumber, volume, weight) VALUES (111, 'HEAVY', '625B', 5000, 150000);

• Click Execute.

  
  
  

You see messages indicating that rows have been added to the tables.

Run the Ruletest

Open the Rulesheet Sc1_read.ers in the Scenario1 subfolder in the sample Rule Project. The rules in this Rulesheet check if the total cargo weight assigned to a FlightPlan is greater than the maximum cargo weight of the aircraft assigned to the same FlightPlan.

In the previous step, you populated the Scenario1 database with data about Cargo, FlightPlan, and Aircraft. Running the Ruletest will verify that the rule can read those records from the database.

Open the Ruletest named Sc1_read.ert and verify that its database access setting is set to Read Only. Select Ruletest > Testsheet > Database Access and verify that the Read Only option has a checkmark next to it.

Run the test. You see the following results:

As you can see, based on the flightNumber in input, Corticon retrieved the FlightPlan record and the records of all the instances associated with it, using the record’s foreign keys. This data was then processed by the rule and the result displayed in the Ruletest’s Output pane.

That completes our look at Scenario1. Let’s cleanup to prepare for Scenario2:

Choose File > Save All, and then choose File > Close All

Collapse the Scenario1 folder, and then expand the Scenario2 folder