Setting up the tutorial

Step 1: Installing Microsoft SQL Server Express

Microsoft SQL Server Express and Tools is a version of SQL Server that is free to use and distribute. Follow these steps to download, install, and configure SQL Server Express 2014 software:

1. Download the appropriate installer. On the webpage https://www.microsoft.com/en-us/download/details.aspx?id=42299, click Download.
2. On the Choose the download that you want page, select:ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
3. Click Next.
4. In your downloads location, unzip its contents.
5. In the SQL Server Express folder, double-click SETUP.
6. In the SQL Server Installation Center wizard that opens, click New SQL Server stand-alone installation or add features to an existing installation.
7. Perform a typical installation, accepting the license agreement and retaining the default settings in each screen, and clicking Next until you reach the Database Engine Configuration panel.
8. IMPORTANT: On the Database Engine Configuration panel’s Server Configuration tab:
   1. Select Mixed Mode (SQL Server and Windows authentication)
   2. In the Enter Password and Confirm Password fields, enter sqlserver2014password. This password is for a default administrator user named sa.
      
      
9. Click Next on the remaining panels, and then click Close to exit the wizard.

You’ve now installed SQL Server Express and its tools. The SQL Server Express database engine starts up automatically.

Step 2: Configuring Microsoft SQL Server Express

Corticon wants to connect to a database through TCP/IP on a designated port. We’ll configure SQL Server Express for that in these steps:

1. Choose Start > Microsoft SQL Server 2014 > SQL Server 2014 Configuration Manager.
2. Expand SQL Server Network Configuration in the left pane and select Protocols for SQLEXPRESS.

   

3. Right-click TCP/IP in the right pane and select Properties.
4. In the TCP/IP Properties window, click the IP Addresses tab and scroll to the bottom.
5. In the IP All section’s TCP Port field, enter 1433, click Apply, and then OK:

   

6. Click OK in the Warning message box.
7. Right-click TCP/IP and click Enable. Click OK to any warning messages.

   

8. Restart SQL Server Express by selecting SQL Server Services in the left pane, right-clicking SQL Server (SQLEXPRESS) on` the right, and then choosing Restart.

   

Step 3: Creating a database

Next, you will create a database named Transportation. Later, you will generate a schema for the database from a Corticon Vocabulary.

Follow these steps to create the database:

1. Launch SQL Server Management Studio by selecting Start > All Programs > Microsoft SQL Server 2014 > SQL Server 2014 Management Studio.
2. In the Connect to Server window, select SQL Server Authentication in the Authentication setting, enter sa in the Login field and sqlserver2014password in the Password field, and click Connect. >fig>
3. Right-click Databases and select New Database.

   

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

   

Your new database named Transportation should now be created. To verify this, expand Databases. You should be able to see a new database folder named Transportation. If you expand Transportation and then expand its Tables subfolder, you will see some default system and file tables, but no user-defined tables.

Step 4: Opening a sample rule project

Next, open the sample rule project Training which contains the Cargo Vocabulary that we will use. As part of this tutorial, you will map this Vocabulary to a database in SQL Server. You will create Rulesheets and Ruletests that read and write to the database.

Follow these steps to open the sample project:

1. Choose the Start menu command Progress > Corticon Studio.
2. In Studio, select the menu command Help > Samples.
3. Choose Training:

   

4. Click Open, and then select Training and click OK.
5. Expand the Training project, and you see two folders.
6. Expand the Intro folder so you can access the Cargo.ecore located there.

Success! Your environment is now ready to use!

You will use the Cargo Vocabulary to generate a database schema. You will create Rulesheets, and then create Ruletests to do some testing. By running the Ruletests, you can verify that the rules are able to access the database.

Step 5: Adding the EDC Datasource to the Vocabulary

We need to declare that we want to use an EDC Datasource.

In the Studio, open Cargo.ecore under Intro in the Training rule project.

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

Step 6: Mapping the Vocabulary to the database

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 to be stored in a 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 now looks like this:

Step 7: Establishing the connection to the database

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:

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

Step 8: Generating the Vocabulary to the database schema

We have a connection, we have defined which entities we want to persist in the database as tables, and which attribute in each entity will be assigned as the primary key. We are ready to generate the database schema.

1. On the EDC tab, click SCHEMA Create/Update
   
   
2. Click Yes in the Schema Update Warning dialog box:
   
   
3. When the database schema is created successfully, you see the following message:
   
   
4. Verify that a table is created in the database for each entity in the Vocabulary. In SQL Server Management Studio, right-click Transportation and select Refresh.

   
   
   

5. Expand Transportation > Tables. You see the following tables:
   
   

Step 9: Populating the database with sample data

The last step is to populate some sample data so that when you define rules that read from the Transportation database, there are sample records that the rules can process.

Follow these steps to populate sample data:

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

   

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

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

3. Click Execute.

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

Success! Your environment is now ready to use!