Using a Visual Modeling Tool to Generate the DDL

Visual modeling tools are a must for integrating UML with an implementation language. Chapter 10 will show that the iterative and incremental approach to building software relies on quick and accurate forward and reverse engineering. This approach to modeling and code generation is called round-trip engineering.

The quality of the SQL DDL to generate the table structures is just as critical as the quality of the Java code to implement the classes. As before, we use Rational Rose. With the enterprise version, Rose contains an add-in product called the Data Modeler. Again, I show this in the book because I find most project teams using some form of visual modeling not only to generate code but also to generate DDL. Visual modeling isn't a necessary part of the application, but it sure beats hand-editing the DDL.

When using Rose, we need to intervene manually in the area of transient attributes because although Rose supports transient classes, it does not support transient attributes. (Rose does have a transient attri bute, but its usage pertains to Java and serialization). During DDL generation, each class attribute is given a corresponding column in the target relational table. Any attributes that are needed only at runtime and that we don't want persisted will require manual removal from the schema for the corresponding table. Figure 9-10 shows Rational Rose and the current class diagram for Remulak Productions.

DDL generation in Rose is very easy. Let's look at an example that generates DDL for the classes in the Remulak class diagram. We begin by defining the target database in Rose through the component view package located in the tree view on the left-hand side of the screen (see Figure 9-10).

1.	Right-click on Component View in the tree view.
2.	Select Data Modeler in the context menu.
3.	Select New in the next context menu.
4.	Select Database in the next context menu.

This sequence of commands will create a component in the component view that resembles a disk drive. Double-click on this component, and a dialog box will appear (see Figure 9-11). This dialog allows you to select the type of database to which the specified component refers. This will be the component that we tie to the schema created in the next step (see Figure 9-12).

Next we must create an empty schema package to be the recipient of the relational entities. Keep in mind that this process creates only relational entities—fodder for an entity relationship diagram if you will. The step after this will be to forward-generate DDL from the schema we create here. So to create the schema, do the following:

1.	Double-click on Logical View in the tree view.
2.	Double-click on Increment1 in the tree view.
3.	Right-click on Business Services.
4.	Select the Data Modeler context menu item.
5.	Select Transform To Data Model…

This sequence will take you through a dialog to create a schema and the tables based on the classes found in the class diagram (see Figure 9-10).

Notice the Target Database drop-down list box in Figure 9-12. This is where a reference is made to the database component that was created in the previous step (see Figure 9-11). This level of abstraction for relating database components to schemas is quite powerful and reusable. For instance, The DB_0 component created in Figure 9-11 can be referenced by more than one schema. Why would we want more than one schema? There are two reasons: (1) Because each schema can refer to a unique table space (a physical container of sorts for tables), two different schemas are needed, but each one can refer to the same database component. (2) Perhaps some of your classes will be physically housed in a SQL Server database and others in an Oracle database. Remulak would then need two database components—DB_0 and DB_1—one referring to Microsoft SQL Server, the other to Oracle. This would allow each schema to refer to the appropriate database component.

Once the dialog in Figure 9-12 has been completed, there will be a new schema package containing another package that goes by the name of the schema assigned in the dialog outlined in Figure 9-12 (the default schema name is S_0). If you open up the S_0 schema (or whatever name you give it), several table names will be displayed. If you double-click on one—say, T_Customer—and select the Columns tab, a Table Specification dialog box will appear as in Figure 9-13.

Using the dialog outlined in Figure 9-13, you can make any necessary changes to the table definitions. To further reinforce the understanding of the relationship between the database component created in Figure 9-11 and the schema created in Figure 9-12, all of the column types displayed under the “Type” column in Figure 9-13 will be constrained to those supported by the database component specified when the schema was created. Additional dialogs support further definition of the columnar information.

Double-clicking on a column name—say, customerNumber—shows a dialog box like the one in Figure 9-14. It is here that items such as length and precision are specified for an individual attribute.

Now it's time to generate the DDL scripts for the schema. We can do this from two different locations: the schema package or the database component package. Here are the steps involved:

1.	Right-click on the schema package created in Figure 9-12.
2.	Select the Data Modeler context menu item.
3.	Select the Forward Engineer… context menu item.
4.	Select Next in the ensuing dialogs until a dialog appears as outlined in Figure 9-15. Figure 9-15. Rose DDL script dialog box

Supplying a DDL file name and selecting Next will create the DDL for all the tables in the schema. Figure 9-16 is a partial listing of the DDL. The entire DDL for the project can be found in Appendix D.