• 29
• Server Generation
• What is Server Generation?
• Generating DDL.
• Pre-Generation Quality Control
• Generation of DDL Scripts
• Selections Tab
• Objects Tab
• Options Tab
• Generation and its Output
• Post Generation Activities
• Reconcile Utilities.
• Reconciliation Report Output
• Reconciliation DDL Output
• Reverse Engineer Utilities
• Reverse Engineering DDL Objects
• Options Tab
• Objects Tab
• Reverse Engineering Forms
• Reverse Engineering Reports
• Post Reverse Engineering
• Summary.

29

Server Generation

What is Server Generation?

Server Generation is the second half of application development. The server generator will provide two important elements in an Oracle7 application. First, it provides for the generation of the PL/SQL code for implementation of triggers, procedures, functions, and packages embedded on the server side. Second, it also provides for the generation of the SQL code necessary to create tablespaces, tables, views, snapshots, indexes, roles, and users.

Server Generation consists of three distinct tools that can be used throughout the life-cycle of an application system. These tools include the DDL Generator, the Reconciliation Utilities, and the Reverse Engineering Utilities. The first two are used to create the data definition language (DDL) necessary to create new and update existing database objects that have been added or changed in the repository. The latter will become vital in ensuring that the repository contains references to any server related objects that were not generated from Designer/2000, or that have been changed on-line from their original repository definition.

Generating DDL.

The DDL Generation utility is to be used when system design has been completed, or when new objects have been added to the application system. The generator is intended for Oracle7 DDL generation, but can also generate some Oracle V6 and ANSI compatible DDL scripts. See the Designer/2000 documentation, on-line help, and release notes for specific generator capabilities and restrictions as these may change with newer versions of the DDL generator. The DDL generation tool can be executed from any of the following Designer/2000 applications:

• Data Diagrammer
  
  
• Module Data Diagrammer
  
  
• Module Structure Diagrammer
  
  
• Module Logic Navigator
  
  
• Repository Object Navigator
  
  

Pre-Generation Quality Control

It is recommended that a quality check be performed on server objects in the repository prior to generation by running some or all of the quality control reports available for the repository. These reports provide information such as object validity, incomplete definitions, and create status checks. Also, ensure that the database being generated for has a name and version defined in the repository.

Generation of DDL Scripts

To generate DDL script files based on repository definitions, select the Utilities | Generate SQL DDL menu option. Figure 29.1 shows the Generate DDL dialog box that will be displayed when started. Notice the three tab selectors used in the dialogue box.

Figure 29.1. Generate DDL dialog box.

Selections Tab

In the selections tab, a list box is available to choose the database in which the objects to create will be located. There are also selection option checkboxes to turn on and off automatic trigger and index selection. These are very helpful when generating DDL to ensure that everything defined in the repository is accounted for. Finally, there is a second list box, which a default database can be specified. Any objects selected that have not been associated with a database name will be generated for the default database identified. To save the selection options for future use, select the Save Options button. If option changes are not saved, a prompt will appear prior to exiting the Generate DDL dialogue box.

Objects Tab

The objects tab is where the designer actually picks objects that DDL scripts are to be generated for. Figure 29.2 shows an example of the objects tab in use.

Figure 29.2. Objects selection tab for DDL generation.

The objects tab provides a list of object types in the Type list box, found in the upper left of this tab. Selecting an object type will cause the DDL generator to retrieve all defined objects of that type for the database being generated. These objects will then be listed, by name, in the Name list box, which is found in the upper right of this tab. Objects can be moved to the Objects list box, found at the bottom of this tab, by highlighting the object and selecting the down arrow button or all objects listed by selecting the double down arrow button found in the objects selection tab. An object can also be removed from the Objects list box by highlighting the object and selecting the up arrow button.

Options Tab

The options tab provides the user with the capabilities of specifying how objects are to be generated. An example of the Options tab in use can be seen in Figure 29.3.

Figure 29.3. Options selection tab for DDL generation.

The first option group is a radio selection in which package specifications, bodies, or both can be selected for generation. Some IS departments may wish that package specification and package body DDL scripts be generated into separate files. It also provides for the black box capabilities of packages by allowing for the creation of the package specifications to be done prior to package body completion. This will help decrease the amount of time other developers have to spend waiting for database packages to be created for use in their development efforts.

Generation output options are defined in the second option group. There is a checkbox to turn on and off file overwriting. There is also an edit box where the filename prefix for each of the DDL generation scripts is defined. Each script file has this filename, and has different extensions based on the type of object it generates. It is good practice to use a file naming scheme that will identify the application system and release or generation number so that system creation history can be maintained and configuration management can be better controlled. For example, CS1129 could be used for the November 29th generation of the Customer Service application. See the operating system documentation for restrictions on file naming conventions.

CAUTION: If the Overwrite files option is checked, any previous DDL scripts with that filename prefix will be lost if that object type is generated! The SQL*Plus driver script is always created and thus overwritten!

The last options group provides for an array of generation options not already defined. If a new system is being created, the database creation command option should be checked. The distributed capability is only generated for systems that have the distributed option loaded. Both table level constraint creation and column level check constraints creation can be turned on or off. The column comments places any comments on a column's repository definition into the database's data dictionary. The Oracle 7.1 syntax option tells the generator to create its DDL scripts in Oracle 7.1 format. Finally, the comment syntax is the syntax used by the generator to comment the DDL script files.

Generation and its Output

Once a database and object(s) have been selected, DDL generation can begin. To start the generation process, simply select the Generate button found at the bottom of the Generate DDL dialogue box. Generation time will depend on the number of objects selected and the size of the repository. When generation has completed, a second dialogue box will be displayed showing the files that have been created. At this point, any of the files can be viewed or executed by selecting either the Browse or the Execute buttons. Figure 29.4 shows the results of DDL generation.

Figure 29.4. DDL generation output dialog box.

Post Generation Activities

It is best not to run the generated DDL scripts immediately. First, edit the .SQL master execution script and ensure that objects are generated in the correct order. Also, check for validity of the statements generated. Ensure that all objects that were intended to be created have scripts available. Furthermore, there will be a QUIT command at the end of the .SQL script. Removing this command will allow the executor to verify the results of the DDL execution. Table 29.1 below shows the object types and their generated output filename extensions.

Table 29.1. DDL Generator output file extensions.

OBJECT

GENERATOR EXTENSION

Once the generated DDL scripts have been validated, then the scripts should be turned over to the database administrator (DBA.) The DBA can execute them using the master execution file the generator created or by calling them individually from the SQL*Plus command prompt.

Reconcile Utilities.

Only in a perfect world will the repository and the database stay the same at all times. Very active databases will be changing all the time, if for no other reason than database tuning. For this, Designer/2000 provides two reconciliation utilities. A cross reference report and an alter database DDL generator. Both reconciliation utilities can be started by selecting the Utilities-->Reconcile menu option from any of the following Designer/2000 applications:

• Data Diagrammer
  
  
• Module Data Diagrammer
  
  
• Module Structure Diagrammer
  
  
• Module Logic Navigator
  
  
• Repository Object Navigator
  
  

Once started, the Reconcile Repository Object Definitions dialogue box will be displayed as in Figure 29.5. Notice in the options group, at the bottom of the dialogue box, there is a radio button group. Here is where either the cross reference report or the alter database DDL generator are selected.

Figure 29.5. Reconcile Repository Object Definitions dialog box.

The database list box will identify the Oracle database that the objects are to be reconciled in. The database must be predefined in the repository. The object owner identifies the user on the physical database that owns the objects being reconciled.

The remote user, password, and connect string fields are only needed if the objects are located on a remote server. The connect string format will be determined by the SQL*Net version.

The first two selection options determine whether a table's associated trigger and index objects are selected automatically in the object's tab. The reconcile constraints option determines whether the declarative table level constraints will be looked at during reconciliation. The reconcile private procedures option will allow the utility to also reconcile procedures and functions declared as private in a package. The ignore create status is used to turn off checking of the repository create flag on an object. Finally, the include unassigned objects option will attempt to reconcile repository objects that are not assigned to a database to the database being reconciled against.

CAUTION: If the ignore create status option is not selected and the create status is false on the object, the reconciliation utility will ignore it even if it exists on the database.

Reconciliation Report Output

The reconciliation report is probably the most helpful of the two utilities. It provides a wide variety of information to the user. Since the inconsistencies may exist as problems, either in the repository or the data dictionary, it is best to look at the report and decide which is correct. The report will output to a preview screen. From here it can be printed. The output also is saved in a file called CDRK55.LIS, which will remain for reference if upon exit, the delete output dialogue is answered NO.

The report will list some administrative information at the top. These items include the following:

• Server Generator version number.
  
  
• Application name
  
  
• User names
  
  
• Object types
  
  

The body of the report will have four columns of the following titles:

• Database Object Name/Property
  
  
• Repository value
  
  
• Data Dictionary value
  
  
• Error
  
  

An example the possible reported errors identify would be a column in the repository that was defined as NOT NULL, but in the data dictionary it is actually NULL. The Error column would identify this error. The report also identifies sizing differences. These will more commonly be different due to database tuning. At the end of each objects listing, there is an error count total for the object.

Reconciliation DDL Output

The reconciliation DDL will be generated to the file CDRK55.SQL. While the DDL will give a starting point for methods in correcting database discrepancies, it is very likely that it will fail upon execution. Some of the reasons for failure are:

• Adding a NOT NULL column.
  
  
• Modifying a column's data type when data exist in the column.
  
  
• Modifying sizes below current data sizes.
  
  

CAUTION: It is more likely that the repository has not been kept up to date and thus, the DDL generated would do more harm than good.

Reverse Engineer Utilities

In many situations, older applications may not have been developed using a CASE product. Server objects and client applications may also be created or modified on the fly by the DBA, or by other developers when quick development is essential to success. Reverse engineering utilities will become an important tool used to create references for legacy systems or keep the current Designer/2000 repository application definitions up to date with changes. There are three utilities available for reverse-engineering DDL objects, FORMS, and REPORTS.

Reverse Engineering DDL Objects

The most common occurrence for DDL or server objects to be changed without repository updates is from database tuning. Another possible cause is the quick fix syndrome that plagues any system without strict management controls. To start the reverse-engineering utility for DDL or server objects, select the Utilities-->Reverse Engineer DDL menu option from one of the following Designer/2000 applications:

• Data Diagrammer
  
  
• Module Data Diagrammer
  
  
• Module Structure Diagrammer
  
  
• Module Logic Navigator
  
  
• Repository Object Navigator
  
  

The Reverse Engineer Database Objects dialogue box will be displayed at startup. There are two tab screens for this utility, Options and Objects, as shown in Figure 29.6

Figure 29.6. Reverse Engineer Database Objects dialog box.

Options Tab

The options tab first provides a drop-down list of known database names. This will correspond to the database that objects are being reverse engineered from. The object owner identifies the database user that the object belongs too. This will probably not be the same as the repository owner.

The remote username, password, and connect string fields are only needed if the object exists on a remote server. The connect string must be in SQL*Net format. As in the DDL Generator, there are checkboxes to turn on or off automatic trigger and index selection in the objects tab. There are also checkboxes that will enable reverse engineering of declarative table level constraints and package, procedure, and function data usage. If reverse engineering is being done on objects not defined in the repository, it is best to select all of these options. If the object is already defined in the repository, the object will need to be dropped and then reverse engineered in. The utility will not overwrite existing objects.

Objects Tab

The objects tab is used in the same manor as the objects tab found in the generate DDL. The up and down arrow buttons move selected objects to and from object's list box, and the double down arrow button moves all objects in the name list box to the object's list box.

Reverse Engineering Forms

There is also a tool for reverse engineering client side objects. The first of these are forms. To start the forms reverse-engineering utility, select the Utilities-->Reverse Engineer Form menu option from one of the following Designer/2000 applications:

• Data Diagrammer
  
  
• Module Data Diagrammer
  
  
• Module Structure Diagrammer
  
  
• Module Logic Navigator
  
  
• Repository Object Navigator
  
  

When this utility is started, the Reverse Engineer Form dialogue box will be displayed as in Figure 29.7.

Figure 29.7. Reverse Engineer Form dialog box.

This utility is more like the Form Generator than the DDL Generator. Under the product-flavor list box, the only of the following type of form that can be reverse engineered is Oracle Forms version 4.5. Since future releases of the Forms Generator utility will be able to generate Visual Basic forms, it is likely that Visual Basic forms will be included in reverse engineering.

The location list box will default to File, but can be changed to Database if the form is stored in the database. The destination list box is an editable list. Either a currently-defined module can be selected, or a new object name can be typed in.

The Use-Preferences group determines where module preferences are defined. The Other Module item is used when the new module is to use the same preferences identified in another module already defined in the repository. The module short name is specified in the list box. If Named Set is used, the specified named set of preferences are used.

The files group at the bottom has a name field and a list box. The browse button can be selected to navigate directly through the directory structure to the form .fmb file, or the path and file name can be typed in. Upon entering the file name, the files internal module name will appear in the list box. Multiple form modules can be put into the list box, but only one can be reverse engineered at a time.

Reverse Engineering Reports

The second of the reverse-engineering utilities for client-side objects is for reports. To start the reports reverse-engineering utility, select the Utilities-->Reverse Engineer Report menu option from one of the following Designer/2000 applications:

• Data Diagrammer
  
  
• Module Data Diagrammer
  
  
• Module Structure Diagrammer
  
  
• Module Logic Navigator
  
  
• Repository Object Navigator
  
  

When this utility is started, the Reverse Engineer Report dialogue box will be displayed as in Figure 29.8.

Figure 29.8. Reverse Engineer Report dialog box.

This utility is also like its counterpart, the Report Generator, rather than the DDL Generator. Under the product-flavor list box, one of the following types of reports can be reverse engineered:

• Oracle Reports version 2.5
  
  
• SQL*ReportWriter 1.1
  
  
• SQL*Plus Reports
  
  
• SQL*Reports (RPT)
  
  
• Oracle Pre-compiler (PRO*C)
  
  

The location list box will default to File, but can be changed to Database if the form is stored in the database. The destination list box is an editable list. Either a currently defined module can be selected, or a new object name can be typed in.

The Use-Preferences group determines where module preferences are defined. The Other Module item is used when the new module is to use the same preferences identified in another module already defined in the repository. The module short name is specified in the list box. If Named Set is used, the specified named set of preferences are used.

The files group at the bottom has a name field and a list box. The browse button can be selected to navigate directly through the directory structure to the report .rdf file, or the path and file name can be typed in. Upon entering the file name, the files internal module name will appear in the list box. Multiple report modules can be put into the list box, but only one can be reverse engineered at a time.

Post Reverse Engineering

After completion of the reverse-engineering process, the objects will need to be modified using the Repository Object Navigator. Identification of which entities and functions the tables and modules are fulfilling will link the new physical definitions back to the system model. There will most likely be some incomplete information in complex modules that may need to be updated.

Summary.

The tools in the Server Generation suite can be divided into two distinct categories. The first category is those that generate output to update the database. This category includes two tools: the Generate DDL utility and the Reconcile-Alter Database utility. Both of these produce DDL scripts that can be run against the database. The second category is those that update the repository tables. This category includes the Reverse Engineer DDL, the Reverse Engineer Forms, and the Reverse Engineer Reports utilities. All of these utilities look at external objects and automatically update the repository. The Reconcile-Cross Reference report utility can be considered to span both categories to a some extent. Its only output is a report, but the information provided on the report can lead to changes being made in either the repository or the database.

With Oracle's commitment to becoming an open systems solution provider, the server generation tools will most likely expand in capabilities in the future. Already, the Client Application generators are venturing into the generation of Visual Basic applications. It could be assumed that a reverse engineering tool will follow to support existing systems. Other possibilities could include capabilities to generate more ANSI compliant DDL scripts that can be used with products like SQL Server and Sybase.