Creating a new database

Let’s start by creating a new database and tables that can be mapped to the Cargo.ecore Vocabulary.

In SQL Server Management Studio, right-click Databases and select New Database.

In the New Database window, enter Scenario2 as the Database name and click OK.

Next, let’s create tables in the database. We will later map these tables and their columns to entities and attributes in the Cargo.ecore Vocabulary.

In SQL Server Management Studio, select File > New > Query with Current Connection.

CREATE TABLE Scenario2.dbo.Plane
(planeType varchar(50),
maxCargoVolume decimal,
maxCargoWeight decimal,
tailNumber varchar(50) NOT NULL PRIMARY KEY);
                                
CREATE TABLE Scenario2.dbo.FlightPlan
(flightNumber int NOT NULL PRIMARY KEY, 
tailNumber varchar(50) FOREIGN KEY REFERENCES Scenario2.dbo.Plane(tailNumber));
                            
CREATE TABLE Scenario2.dbo.Shipment
(container varchar(50), 
manifestNumber varchar(50) NOT NULL PRIMARY KEY,
volume decimal,
weight decimal,
flightNumber int FOREIGN KEY REFERENCES Scenario2.dbo.FlightPlan(flightNumber));
                            

Click Execute.

You see a message indicating that the command executed successfully.

Expand Databases > Scenario2 > Tables in the Object Explorer view on the left. You see its three tables—dbo.FlightPlan, dbo.Plane, and dbo.Shipment:

Adding the EDC Datasource to the Vocabulary

As in Scenario 1, we need to declare that we want to use an EDC Datasource.

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

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

Mapping the Vocabulary to the database

The next step is to map the Vocabulary to the database. This step consists of six substeps:

• Defining database connection properties
• Setting up entities for mapping
• Importing database metadata
• Manually mapping differently-named entity with tables
• Manually mapping differently-named attributes with columns
• Validating mappings

Defining database connection properties

On the EDC tab, specify the following database connection properties:

• Database Server: Microsoft SQL Server
• Database URL: jdbc:progress:sqlserver://localhost:1433;databaseName=Scenario2
• Username: sa
• Password: sqlserver2014password

Click CONNECTION Test. You see a message indicating that connection was successfully established.

Setting up entities for mapping

As you did in Scenario 1, configure each entity to persist to the database by setting its Datastore Persistent property to Yes. Configure the Entity Identity property for each entity as follows:

• For Aircraft, choose tailNumber:
• For Cargo, choose manifestNumber as its entity identity.
• For FlightPlan, choose flightNumber as its entity identity.

The icons of the entities change to include a database icon, indicating that they are now configured to be stored in a database together with their attributes and associations.

Importing database metadata

After you have configured the entities for database persistence and primary key, you can import the database’s metadata. Doing this automatically maps matching entity and table names, and if an entity has been mapped to a table, it also automatically maps matching attribute and column names. Any differently-named entities and attributes will have to be mapped manually.

To import database metadata, click the EDC tab METADATA Import.

This opens the Import Database Metadata dialog box. In this dialog box, select Choose tables for database metadata import and click Next.

Next, you choose which tables to import. Click Deselect All and then scroll through the list of tables to locate dbo.FlightPlan, dbo.Plane, and dbo.Shipment. Select these tables and click Finish.

If all entity and attribute names have corresponding elements in the database with matching names, you would see a message indicating that the database metadata has been imported successfully.

However, if there are any differently-named entities (as in our example), the entity and its attributes will not get mapped automatically. You will see the following message:

The Problems view shows which elements were not mapped automatically:

Mapping differently-named entities

Looking at the Problems from bottom to top, you see that no matching database table for two of the entities could be found in the imported metadata. A lot of the other problems derive from those problems, so we will map the mismatched entities manually.

Let’s start with the Aircraft entity. Select the Aircraft entity and click on the Table Name drop-down. In the list of Scenario2 tables, select Scenario2.dbo.Plane:

Similarly, click Cargo and then click its Table Name drop-down. Select Scenario2.dbo.Shipment:

Before we correct the one remaining problem, let’s take a closer look at the way that Vocabulary “decorations” provide information:

• All the entities and attributes have a database symbol in their upper right.
• The two entities we manually mapped have a black stripe in their database symbol.
• The attribute aircraftType has an alert marker in its lower left.

Save the Vocabulary file so that the problems are re-evaluated.

Mapping differently-named attributes

All attributes that have a corresponding column with the same name get mapped automatically. However, any attribute that does not have a corresponding column with the same name will not get mapped.

Click aircraftType and then click on the Column Name property. In the list of choices, select planeType:

All elements in the Vocabulary are now mapped to the appropriate database element. Save the Vocabulary to show that the Problems view has no entries. Let’s verify this by validating the mappings.

Validating mappings

To validate mappings, click the EDC tab MAPPING Validate:

A message indicates that no mapping errors were found:

Testing

Let’s verify that Corticon rules that use the Cargo.ecore Vocabulary are able to access the Scenario2 database.

Open AddRecord.ers in the Scenario2 folder. The Rulesheet assigns a value to Cargo.container based on the value of Cargo.weight received in input:

Open AddRecord.ert in the Scenario2 folder. The Ruletest has one Cargo entity instance in input:

For a Ruletest to perform read/write operations on the database, it must be configured to do so. Verify that the Ruletest’s database access setting is set to Read/Update by selecting Ruletest > Testsheet > EDC Database Access. There is a checkmark next to Read/Update.

Run the Ruletest. You see the following output:

The value of the container attribute has changed, indicating that the entity instance has been processed by the AddRecord.ers Rulesheet. Let’s verify that a record corresponding to the Output has been added to the Shipment table (since Cargo is mapped to Shipment). In SQL Server Management Studio, right-click dbo.Shipment and select Select Top 1000 Rows:

You see one record in the Results that corresponds to the Cargo entity instance in the Ruletest’s output:

This illustrates how differently named entity/table elements (Cargo vs Shipment) work when mapped.

Let’s verify that differently named attributes/columns work just as seamlessly too. Open Aircraft_addRecord.ers in the Scenario2 folder:

The Rulesheet assigns a value to Aircraft.maxCargoWeight based on the value of Aircraft.aircraftType received in input. As you may remember, the Aircraft entity is mapped to the Plane table, while the aircraftType attribute is mapped to a planeType column in the database.

Open Aircraft_addRecord.ert in the Scenario2 folder:

The Ruletest contains one Aircraft entity instance in input. The aircraftType is set to 747.

Let’s ensure that the Ruletest is configured to perform read/write operations on the database. Select Ruletest > Testsheet > EDC Database Access. There is a checkmark next to Read/Update.

Run the Ruletest. You see the following output:

As you can see, the value of the maxCargoWeight attribute has changed. Let’s verify that a new record has been added in the Plane table.

In SQL Server Management Studio, right-click dbo.Plane and select Select Top 1000 Rows:

You see one record in the results area corresponding to the Ruletest’s output:

The aircraftType attribute is represented through the planeType column in the dbo.Plane table. The values of other columns are also correctly populated based on the Ruletest’s output.

You have now successfully mapped a Vocabulary to a database in two ways—by generating a database schema from an existing Vocabulary and by mapping an existing Vocabulary to an existing database schema.