Working with Customizable Design Rules and Transformation Scripts

Purpose

This tutorial shows you how to work with the predefined design rules and add your own design rules as well as transformation scripts

Time to Complete

Approximately 25 minutes

Overview

Oracle SQL Developer Data Modeler supports logical and physical data modeling for Oracle, and Microsoft SQL Server and IBM DB2.  This addition to the Oracle SQL Developer family of tools provides forward and reverse engineering of database structures for all who work with graphical data models.  There are various diagramming options, you can create Domains and use a set of Design Rules to help ensure your models comply with a set of standards. Oracle SQL Developer Data Modeler also provides a read-only reporting repository for reviewing the design.  Save all designs to the repository and then use Oracle SQL Developer to run the reports provided.

In this tutorial you apply a predefined design rule, review and use a custom design rule, create a rule set and use a transformation script to apply changes throughout your model.

Prerequisites

Before starting this tutorial, you should:

1 .

Install Oracle Database 11g with Sample Schema.

2 .

Unzip the Oracle SQL Developer Data Modeler software to your system.

3 .

Download and unzip the files.zip file into a working directory to use during this tutorial.

Using a Predefined Design Rule

In this section, you run a design rule that is included with SQL Developer Data Modeler. Perform the following steps:

1 .

Open Oracle SQL Developer Data Modeler from the icon on your desktop.

If you performed the previous tutorial, and did not exit Oracle SQL Developer Data Modeler, select File > Close to close the model you previously worked on. Then click Close without Save.

 

2 .

You want to open an existing model. Select File.

Show Screenshot for Step

 

3 .

Select Open.

Show Screenshot for Step

 

4 .

Select the OE_Sample.dmd file in your files directory.

Show Screenshot for Step

 

5 .

Click OK.

Show Screenshot for Step

 

6 .

The Relational Model is displayed.

Show Screenshot for Step

 

7 .

You want to run one of the Design Rules that is supplied with the product. Select Tools > Design Rules > Design Rules.

Show Screenshot for Step

 

8 .

Expand the Logical > Entity rule.

Show Screenshot for Step

 

9 .

Notice all the predefined design rules for Entities.

Show Screenshot for Step

 

10 .

In this case, you want to run all the rules for the Logical Model. Select Logical and click Apply Selected.

Show Screenshot for Step

 

11 .

There are some warnings and errors. In this case, some of the errors indicate that there is an attribute without a datatype. Double-click the Error: MARTIAL_STATUS: Attributes without a datatype.

Show Screenshot for Step

 

12 .

To defined the datatype, click the unknown button.

Show Screenshot for Step

 

13 .

For Logical Type, click the unknown button.

Show Screenshot for Step

 

14 .

For Logical Type, select VARCHAR and enter 12 for Size, then click OK.

Show Screenshot for Step

 

15 .

Click OK again.

Show Screenshot for Step

 

16 .

Click OK once more.

Show Screenshot for Step

 

17 .

Click Apply Selected to run the design rules again

Show Screenshot for Step

 

18 .

Notice that the MARITAL_STATUS attribute no longer has an error. In the next section, you will review a custom rule. Click Close.

Show Screenshot for Step

 

Reviewing and Applying a Custom Design Rule

In this section, you review a custom design rule and apply it to show how it can be used.. Perform the following steps:

1 .

Select Tools > Design Rules > Custom Rules.

Show Screenshot for Step

 

2 .

There is one custom rule already defined for you. You review what it does.

Show Screenshot for Step


3 .

A design rule can be based on different objects. In this case, the design rule will be based on a table.

Show Screenshot for Step

 

4 .

A custom design rule can use a variety of process engines. SQL Developer Data Modeler provides a few example custom design rules that use the default Mozilla Rhino engine, which runs Javascript. You can use other engines, for example, you can the JRuby engine and specify a jruby script that executes when the design rule is applied.

Show Screenshot for Step

 

5 .

A design rule can generate a Warning or Error. Note that if you select Warning, the warning icon will appear next to the design rule, if Error is selected, the error icon will be used.

Show Screenshot for Step

 

6 .

You can specify which library the script is coming from. In this case, my first library is selected. Note that if you do not specify a library, you need to invoke the method and pass the value into the method.

Show Screenshot for Step

 

7 .

Below is the javascript code that will execute when the rule is applied. In this case, it is checking if there is a Comment in the RDBMS specified, if not, then a Problem type error message will be generated. If there is not Comment for the table specified, then an Error type error message will be generated.

Show Screenshot for Step

 

8 .

Click Apply to see the errors that are generated.

Show Screenshot for Step

 

9 .

Notice that there are a number of error type messages displayed. Double-click the error for the OE.ORDER_ITEMS table.

Show Screenshot for Step

 

10 .

Select the Comments attribute.

Show Screenshot for Step

 

11 .

Enter a comment in the Comments field and click OK.

Show Screenshot for Step

 

12 .

From the Custom Design Rules page, click Apply again.

Show Screenshot for Step

 

13 .

Notice that the error has turned to a Problem because there are comments defined but there are no comments in the RDBMS defined. Double-click the Problem for OE.ORDER_ITEMS to add an RDBMS comment.

Show Screenshot for Step

 

14 .

Select the Comments in RDBMS attribute.

Show Screenshot for Step

 

15 .

Enter a comment in the Comments in RDBMS field and click OK.

Show Screenshot for Step

 

16 .

Click Apply one more time.

Show Screenshot for Step

 

17 .

Notice that the Problem is no longer in the list. In the next section, you examine how to create a Rule Set. Click Close.

Show Screenshot for Step

 

Adding a Rule Set

In this section, you learn how to create a rule set which applies just the design rules you want to run together. Perform the following steps:

1 .

Select Tools > Design Rules > Design Rules.

Show Screenshot for Step

 

2 .

Expand Relational > Table. Notice that the custom design rule you just reviewed is in the list.

Show Screenshot for Step

 

3 .

You want to create a rule set that will only apply some of the rules. Click the Rule Sets tab.

Show Screenshot for Step

 

4 .

Click the '+' to create a new Rule Set.

Show Screenshot for Step

 

5 .

Select the Rule Set and select the Properties icon.

Show Screenshot for Step

 

6 .

A list of the rules appears. Scroll down to the Column object rules.

Show Screenshot for Step

 

7 .

Select the rule Identify columns without datatypes and click the Add Rule (right arrow) button to move it over to the list of selected rules.

Show Screenshot for Step

 

8 .

Scroll down to the Table object rules.

Show Screenshot for Step

 

9 .

Select the following rules and click the Add Rule (right arrow) button to select the rules.

  • Complex rule - check comments demo
  • Identify tables without columns
  • Identify tables without primary key

Show Screenshot for Step

 

10 .

Click OK.

Show Screenshot for Step

 

11 .

Click Save to save your rule set.

Show Screenshot for Step

 

12 .

Select your Rule Set and click Apply Selected.

Show Screenshot for Step

 

13 .

All the design rules in the rule set are evaluated and you see the errors that were found. In the next section, you will examine transformation scripts and add a table template that contains some columns that you will apply to all the existing tables in your relational design. Click Close.

Show Screenshot for Step

 

Using a Transformation Script

In this section, you examine the predefined transformation scripts and add a table template table that contains some columns that you apply to all the existing tables in your relational design. Perform the following steps:

1 .

You want to examine the transformation scripts that are currently available. Select Tools > Design Rules > Transformations.

Show Screenshot for Step

 

2 .

The transformation scripts are listed. Because the JRuby engine is not installed, you may receive a warning message. Click OK.

Show Screenshot for Step

 

3 .

If you click one of the scripts, you see the javascript that is executed when the transformation is applied. In this case, an abbreviation can be added to a column in a table.

Show Screenshot for Step

 

4 .

In the Table template example, a table can be created that is a template. The template can be applied to all the tables in your model. Notice that the table must be called table_template. You will apply this transformation script. Click Close.

Show Screenshot for Step

 

5 .

The first task you need to perform is to create a table called table_template. Select the New table icon in the toolbar.

Show Screenshot for Step

 

6 .

Click in the white space of the relational design. Enter table_template for Name and click the Columns attribute.

Show Screenshot for Step

 

7 .

Click the '+' button to create a new column.

Show Screenshot for Step

 

8 .

Select the '+' again to create another column.

Show Screenshot for Step

 

9 .

Click OK to create the table and the columns.

Show Screenshot for Step

 

10 .

Notice the table_template table with the 2 columns. You want to apply these new columns to your existing tables.

Show Screenshot for Step

 

11 .

In the navigator, right-click the Relational Model and select Apply Custom Transformation Scripts.

Show Screenshot for Step

 

12 .

Select the Table template script and click the Add (right arrow) button.

Show Screenshot for Step

 

13 .

Click Apply.

Show Screenshot for Step

 

14 .

The transformation script has been applied. Click OK.

Show Screenshot for Step

 

15 .

Click Close.

Show Screenshot for Step

 

16 .

Notice that each of your tables now contain the two columns from the table_template table. Select the Pointer in the Toolbar.

Show Screenshot for Step

 

17 .

Notice that the columns do not have a data type specified. You can change the table_template table definition and then apply those changes using the same script. Double-click the table_template table.

Show Screenshot for Step

 

18 .

Select the Columns attribute.

Show Screenshot for Step

 

19 .

For Column_1, change the name to UPDATED_DATE, select the Logical datatype, and select Date for Type. Then select Column_2 from the list.

Show Screenshot for Step

 

20 .

For Column_2, change the name to CREATED_BY, select Logical for Datatype, select VARCHAR for Type and enter 25 for Size. Then click OK.

Show Screenshot for Step

 

21 .

Notice that the columns now have a datatype specified in the table_template table.

Show Screenshot for Step

 

22 .

To apply the transformation script again, right-click the Relational Model and select Apply Custom Transformation Scripts.

Show Screenshot for Step

 

23 .

Select the Table template script and click the Add (right arrow) button.

Show Screenshot for Step

 

24 .

Click Apply.

Show Screenshot for Step

 

25 .

Click OK.

Show Screenshot for Step

 

26 .

Click Close.

Show Screenshot for Step

 

27 .

Notice that the changes you made to the table_template are now applied to the other tables.

Show Screenshot for Step

 

Summary

In this tutorial, you have learned how to:

Resources


Hardware and Software Engineered to Work Together Copyright © 2011, Oracle and/or its affiliates. All rights reserved