Setting up the tutorial

Step 1: Installing Microsoft SQL Server 2019 Developer and SQL Server Management Studio (SSMS)

Microsoft SQL Server Developer is a version of SQL Server that is free to use and distribute.

Follow these steps to download, install, and configure SQL Server 2019 Developer and the recommended SQL Server Management Studio (SSMS) software:

1. Browse to https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2019 .
2. Download the free EXE edition installer.
3. Launch the downloaded package, SQL2019-SSEI-Eval.
4. Typically, accept all the default options and locations. Proceed through all the installer panels.
5. On the last panel, choose Install SSMS, to open its browser page. There, click Download SSMS, and then click Download SQL Server Management Studio. Run SSMS-Setup-ENU.exe, its installer.
6. When completed, launch SQL Server Management Studio 20. Click Install.
7. Launch C:\SQL2019\Evaluation_ENU\SETUP.EXE
   1. Choose Installation.
   2. Choose New SQL Server standalone installation...
   3. Proceed through the panels.
      1. On the Product Key panel, specify the free edition Developer.
      2. On the Feature Selection panel, choose Database Engine Services, and then click Next.
      3. On the Instance Configuration panel, choose Default instance.
      4. On the Database Engine Configuration panel:
         1. Select Mixed Mode (SQL Server authentication and Windows authentication)
         2. In the Enter Password and Confirm Password fields, enter

            sqlserver2019

            This password is for a default administrator user named sa as will be used in the sample's connections.
      5. Click Next on the remaining panels, and then click Close to exit the wizard.

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

Step 2: Configuring Microsoft SQL Server Developer

Corticon wants to connect to a database through TCP/IP on a designated port. Configure SQL Server for that in these steps:

1. Choose Start > SQL Server 2019 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 then select Enabled:
   
   In the TCP/IP Properties window, click the IP Addresses tab and scroll to the bottom:
   
   
4. In the IPAll section’s TCP Port field, enter 1433, click Apply, and then click OK.
5. Restart SQL Server Express by selecting SQL Server Services in the left pane, right-clicking SQL Server (MSSQLSERVER) on the right, and then choosing Restart< as shown:
   
   

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 starting SQL Server Management Studio 20.
2. In the Connect to Server window, select SQL Server Authentication in the Authentication setting, enter sa in the Login field, and your password in the Password field. This sample assigned sqlserver2019. Click Connect.
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 is 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: sqlserver2019
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!