Generating a Physical Database Schema

Physical Schemas and Database Drivers

To create a physical database schema from your model, you can either create a DDL (data definition language) script, or connect directly to a database server via an ODBC driver. The fundamental issues with both methods are the same, although there are additional considerations when using an ODBC connection. This chapter will first explain how to create a DDL script using basic driver settings, and then describe driver setup options, and finally explain how to use ODBC connections.

DDL for different database platforms generally looks quite similar. All platforms provide syntax for creating tables and constraints, and some allow for stored and procedures and triggers. However, the differences in DDL syntax by platform are pronounced enough that DDL written for one database product will generally not run unmodified on another platform. Thus, VEA must be able to translate models into platform specific DDL.

VEA database drivers translate data models into platform specific DDL the same way that printer drivers translate between word processors and various printer control languages.

Creating a Basic DDL Script

Physical schemas, whether created via ODBC connections or DDL scripts, can only be generated from a database model diagram. It doesn't matter whether you create the diagram directly, or build a project from source models. For the following example you need a simple database model diagram that looks like Figure 11-1. To save space, the graphic shows only the database model diagram and not a full screen shot.

Figure 11-1: Database model diagram for testing physical schema generation.

If you build the database model diagram directly or use an ER source diagram, refer to Table 11-1 for a list of the entities, attributes and keys that you should include in your model. In this table, the primary key of each entity is double underlined, unique attributes are single underlined , and mandatory non-key attributes are shown in boldface. Also, make sure to add a relationship from the Patient entity (child) to the Country entity (parent), and make the Country_code attribute optional in the Patient entity.

An ORM source model with the correct facts and constraints will automatically generate a database model diagram like the one in Figure 11-1. If you choose to create an ORM model, the first part of section 16.1 in this book lists the facts and constraints that you would need to include in the ORM source model. Regardless of how you created the database model diagram, save it to a file called Patient_ch11.vsd.

To generate a DDL script:

1. Set the Visio default driver to match your target database. To set the default driver:
   1. Choose Database > Options > Drivers from the main menu. Your screen should look like Figure 11-2.

      Figure 11-2: Selecting the default driver.

   2. Highlight Microsoft SQL Server if it is not already selected and click the OK button. The Setup button and the other tabs will be described later in the chapter.

   Caution

   In the VEA beta used to for this book, the drivers for INFORMIX Online/SE Sever and Sybase Adaptive Server appear in the Database Drivers window, but are non functional. These drivers will neither generate nor reverse engineer a physical schema. Check with Microsoft to see if these drivers have been changed in the production release.

2. Invoke the Generate Wizard by choosing Database > Generate from the main menu. Your screen should now look like Figure 11-3.

   Figure 11-3: Generate Wizard page one.

   By default, your DDL script will be generated to a file with the following path and file name: .DDL. If you wish to change the path or name of the DDL file, you can use the File name text box and the Browse button shown in Figure 11-3. If you were to select the Generate new database box, VEA would try to establish an ODBC connection with a database server. For this example, accept the default choices and click on the Next button.

3. The Installed Visio Drivers list box on page two of the generate wizard (Figure 11-4), allows you to choose the VEA database driver for generating your DDL.

   Figure 11-4: Generate Wizard, page two.

   The list box will default to the driver you chose in step one. The Setup button will be explained later in the chapter. At the bottom of the page, supply a name for the database that you will generate. Do not select the Extract Server Information box. Click the Next button.

   Note

   Because you can choose a Visio database driver from page two of the Generate Wizard (Figure 11-4), you are not technically not required to set the correct default driver (step one of this example) prior to generating DDL. However, setting the default driver ahead of time makes it much easier to assign the proper physical data types to objects in your model. The default database driver setting is persistent, so you only have to set it once.

4. Page three of the wizard (Figure 11-5) shows a graphical representation of the tables that will be created when the schema is generated. If the table list isn't correct, you can cancel out of the wizard. Click the Next button.

   Figure 11-5: Generate Wizard page three.

5. The fourth and final page of the generate wizard (Figure 11-6) reports the results of VEA's physical validation of your model. During this validation, VEA checks to ensure that your model does not violate any platform specific rules. For instance, Oracle only allows 30 character object names, while MS SQL Server allows much longer names . The rules that VEA enforces are based on the diver you chose in step three.

   Figure 11-6: Generate Wizard page four.

   Click the Finish button to start the actual DDL generation.

   Hint

   The physical validation page of the Generate Wizard is the most likely place for errors to occur during DDL generation. Platform specific rules, such as object name length, reserved words, and characters , etc, are not enforced during the modeling phase prior to DDL generation. It is thus possible for a model with no conceptual or logical errors to fail physical validation. Figure 11-7 shows a Generate Wizard page for a model that has failed physical validation.

   The errors that occur during physical validation are not listed on the Generate Wizard page. Instead the errors are listed in the Output window (Figure 11-8). Warnings, which are recommendations but not fatal errors, are listed in a text box on the Generation Wizard page.

   Figure 11-8: Output window with physical validation errors.

   In most cases when physical validation fails there are no warnings, only errors. Thus, it is common to see the message "There are no warnings" in the text box when validation fails. The absence of warnings does not mean the absence of errors. When physical validation fails, always check the Output window.

   Figure 11-7: Generate Wizard, failed physical validation.

6. The next screen allows you to specify physical parameters that are specific to your chosen database server. This window is not part of the Generate Wizard, and is different depending on the database driver you chose in step three. If you are generating MS SQL Server DDL, as in this example, the screen will look like Figure 11-9.

   Figure 11-9: MS SQL Server Create Database window.

   The analogous screen for Oracle (Figure 11-10) is more complex because of the additional physical parameters that apply to Oracle databases. You will not see the Oracle window if you are following along with this example. Figure 11-10 is included here for information only.

   Figure 11-10: Oracle Server physical parameters.

   If you fill out the screen, the result of your input will be generated into your DDL file using the proper syntax. If you leave the screen blank, your DDL will not contain these parameters. Leave the screen blank and click the Close button

7. You will be asked if you wish to view the generated DDL, as in Figure 11-11.

   Figure 11-11: View DDL prompt.

   Click the Yes button to invoke the VEA code editor and automatically load the DDL file, as shown in Figure 11-12.

Figure 11-12: Generated DDL script.

Database Driver Options

The preceding example showed how to create a DDL script, but ignored database driver options. The options fall into two categories, driver specific options, and options that apply to all drivers.

Driver Specific Options

Discussion of the Setup button on the Database Drivers window was deferred in step one of the DDL script generation example (Figure 11-2). Re-Invoke the Database Drivers window by choosing Database > Options > Drivers from the main menu. Your screen should once again look like Figure 11-2. Click the Setup button to invoke the VEA driver setup dialog box. The first tab is labeled ODBC Drivers , and the second is labeled Preferred Settings . The ODBC Drivers pane is discussed in section 11.5. For now, click the Preferred Settings tab to make your screen look like Figure 11-13.

Figure 11-13: VEA driver setup, Preferred Settings pane.

The choices available on the Preferred Settings pane are specific to the driver being configured, in this case MS SQL Server. The top portion of the pane is devoted to resolving data type mapping ambiguity. Portable data types are mapped to a single physical data type for each supported database product. MS SQL Server has three physical data types that deal with integers: tinyint, smallint, and int. Each portable data type must map to just one physical type, which can be specified on this pane.

In the list box on the bottom of the Preferred Settings pane, choose the version of MS SQL Server for generating DDL or making an ODBC connection. In versions subsequent to 6.0, primary and foreign keys are generated with SQL statements, which is why the radio buttons at the bottom of the pane are disabled. In version 6.0, primary and foreign keys can optionally be defined by stored procedures instead of SQL statements.

If you have set MS SQL server as your default driver, you will not see the Oracle Server Setup dialog box (Figure 11-14), but it is shown here for informational purposes.

Figure 11-14: Oracle Setup, Preferred Settings pane.

The radio buttons along the top of the pane specify which version of Oracle to use when generating DDL or making an ODBC connection. The text box in the middle of the pane is for specifying the default array size for the Oracle VARRAY collection type, an object relational data type.

The "Create uppercase " checkbox forces all column names and table names to full upper case. Many Oracle developers prefer uppercase object names because of case sensitivity issues that arise otherwise .

The final checkbox causes VEA to create an Oracle sequence for any single column primary key that is an Auto Counter portable data type. In addition to the sequence object, VEA will write an INSERT trigger for the column to automatically populate the primary key with the next value in the sequence.

Use this feature carefully . The sequence that is created will always be called

_SEQ, and the trigger will be called _TRIG. If your table name is 30 characters long (the Oracle maximum), the last four letters of the table name will be replaced with the characters "_SEQ." Thus, if your database has more than one table where the first 26 characters of the table name are the same and you declare the keys of both tables to be the portable data type Auto Counter, you will generate a name collision with the two sequence objects. A similar problem can arise with the trigger.

Once you have filled out the Preferred Settings for your driver, click the OK button to be returned to the Database Drivers window (see Figure 11-2).

Options that Apply to All Drivers

The options discussed in this section apply to all database drivers. Click on the Default Mapping tab to open the next configuration pane, as shown in Figure 11-15.

Figure 11-15: Setting Default Mapping for portable data types.

In this pane you can make minor customizations to your database driver.

The Category list box in Figure 11-15 contains the major kinds of portable data types which are Text, Numeric, Raw Data, Temporal, Logical , and Other . The Type text box contains further subdivisions of the selected major category. As Figure 11-15 shows, the portable data type of the major category Text can be Fixed Length , Variable Length , or Large Length .

The Size text box allows you to specify the default character set for portable data types that belong to the Text category. For portable that data types that belong to the Numeric category, use the Size box to indicate magnitude (e.g., "small" or "large"). In the Length text box, you set the default data length that should be used for the chosen Category / Type combination.

In Figure 11-15, the default length for a Text, Fixed Length portable data type will be 10 characters. The Scale text box is disabled, because scale only applies to certain numeric portable data types. These adjustments only affect the default length of a portable data type.

The final list box in Figure 11-15 is labeled Default category type for column creation . This list box tells VEA what portable data type to assign to ORM objects and logical columns for which data types have not been specifically declared.

Click on the Driver Misc tab to invoke the next configuration pane, shown in Figure 11-16. This pane is confusing, because it appears that all the options it are applicable to all drivers, when in fact one of the options is driver specific.

Figure 11-16: Setting Miscellaneous preferences.

The first checkbox on the Driver Misc pane controls whether VEA will put comments about each database object into the generated DDL script file. These comments can be derived from user entered notes only, or they can include notes that VEA automatically generates during database project builds. The comments inserted into the script will not execute, because they will be marked as comments /* like this * /. If you want "bare bones" DDL, deselect this box.

Choose among the three radio buttons in the middle of the pane to control VEA's generation of SQL COMMENT ON statements, which actually store comments in the server data dictionary. Generating SQL statements to store comments in the database server dictionary is not the same as simply generating comments into the DDL script. Not all database engines support the COMMENT ON feature. In the generated script, VEA will truncate any comments that exceed the server's maximum comment length.

Generating Schemas Via an ODBC Connection

As an alternative to generating a script, VEA can establish a connection with your database server and generate the physical schema directly. VEA makes use of ODBC drivers to establish the connection. The ODBC drivers are different from the VEA database drivers that are discussed in the beginning of this chapter. VEA database drivers are an integral part of the VEA product. ODBC drivers are supplied by various vendors (including Microsoft) and are not actually part of the VEA product, although some of the more common ODBC drivers are distributed with VEA. Figure 11-17 shows how VEA connects to a database server.

Figure 11-17: VEA connects to the database.

The VEA database driver translates the Database Model Diagram into platform specific DDL. The VEA database driver attaches to an ODBC DSN (data source name ) that is associated with a particular ODBC driver and points to a particular database instance. Through the DSN, the VEA database driver issues DDL to the ODBC driver, which in turn passes that DDL to the database for execution.

You must define a DSN that uses the appropriate ODBC driver and points to the proper physical database instance. DSNs are defined through the ODBC Data Source Administrator , which is an administrative tool of the Windows operating system.

The VEA Generate Wizard gives you two choices when generating a physical schema to a physical database. You can have VEA create a new database, or you can have VEA connect to an existing database.

Creating A New Database Using the Generate Wizard

1. Invoke the Generate Wizard by choosing Database > Generate from the main menu. Select the Generate new database checkbox. A lightining bolt symbol will be added to the Generate Wizard (Figure 11-18) to confirm that you have chosen to generate your database via a direct connection to the server. By default, the other two checkboxes on the page are selected. The box labeled Generate a text file of the DDL script tells VEA to create a text file of the DDL in addition to generating the database directly. The box labeled Store current database image in model tells VEA to store a copy of the physical schema image once the generation is complete. Storage of this image is important for later updates and synchronizations. Click the Next button.

   Figure 11-18: Database generation, page one.

2. On the next page of the Generate Wizard (Figure 11-19), make sure that the Installed Visio Driver is set to MS SQL Server Retain the default radio button choice Create database .

   Figure 11-19: Database generation, page two.

3. Click the New button next to the Data source name text box. This action invokes the Windows ODBC Data Source Administrator from within VEA. You will work through two wizards before returning to VEA. Use the first wizard to choose the type of DSN and the associated ODBC driver. Use the second wizard to actually create the DSN based on the specific features of the ODBC driver you have chosen.
4. On the first page of the Create New Data Source wizard (Figure 11-20), choose User Data Source (the middle radio button) and click Next .

   Figure 11-20: Create New Data Source, page one.

5. The second page of the wizard (Figure 11-21) presents a list of all the ODBC drivers installed on your system. Scroll to the SQL Server ODBC driver, select it, and click the Next button.

   Figure 11-21: Create New Data Source, page two.

6. The third page of the Create New Data Source Wizard (image not shown to save space) merely confirms your choice of ODBC driver. Clicking on the Finish button invokes the second wizard, where you actually create the DSN and supply options based on the ODBC driver you have chosen.
7. On the first page of the database specific ODBC Wizard (Figure 11-22), supply a name for your DSN and choose the server to which you wish to connect. The Description field is optional. Click the Next button.

   Figure 11-22: Driver specific ODBC Wizard, page one.

8. The second page of the wizard (Figure 11-23) allows you to configure logon options for your database. Choose the appropriate authentication method for your environment, and click the Next button.

   Figure 11-23: Driver Specific ODBC Wizard, page two.

9. Accept the default choices on page three of the wizard (Figure 11-24), and click Next .

   Figure 11-24: Driver Specific ODBC Wizard, page three.

10. Accept the default choices for page four of the wizard (Figure 11-25) and click Finish .

    Figure 11-25: Driver Specific ODBC Wizard, page four.

    Hint

    The " Connect to SQL Server " checkbox is selected by default. If the check-box is selected and the Server that you specified on the first page of the wizard (Figure 11-22) is unreachable, you will receive an ODBC connection failure message when you click on the Next button. If you wish to configure a DSN for a temporarily unavailable server, deselect the checkbox.

11. Test your DSN by clicking the Test Data Source button (Figure 11-26). When the test completes successfully, click OK .

    Figure 11-26: DSN test screen.

12. After testing your DSN, you will be returned page two of VEA's Generate Wizard (Figure 11-27), which is exactly where you left the program when you invoked the ODBC Data Source Administrator in step three of this example. VEA will automatically fill the Data source name text box with the name of the DSN you just created. You must manually type in the name that you want to use for your new database in the Database name textbox. Click the Next button.

    Figure 11-27: Database generation, page two after creating a new DSN.

    Note

    If you already have an existing DSN that points to the correct instance of SQL Server, you do not have to perform steps 4 “12 of this example. Instead, type the DSN name into the Data Source Name box (Figure 11-27), and type the desired name of your new database into the Database name. Once you generate the schema, the database that you typed into the text box will become the default database for the DSN that you used.

13. You will be prompted to provide logon information (Figure 11-28), even if you chose Windows authentication in the setup of your DSN (Figure 11-23). If your are using Windows authentication, leave the fields empty and click on OK . If you set up your DSN with SQL server authentication, you must provide a user name and password.

    Figure 11-28: Connection prompt.

    The next steps are almost exactly like the final steps in generating a DDL script, and will refer to those screen images. The difference is that model objects (tables, keys, etc) will be generated directly into the database server.

14. After connecting to the database server, you will be shown a graphical list of the tables that are to be created. This screen should be the same as Figure 11-5. Click Next to physically validate your model.
15. Upon successful validation of your model (see Figure 11-6), click the Finish button.
16. The next screen will be the MS SQL Server Create Database window (Figure 11-9). Click Close to continue.
17. VEA will generate your database schema directly into the server. The Output window (Figure 11-29) shows the results of this generation.

    Figure 11-29: Output Pane after database generation.

18. If you close to generate a DDL script, you will be prompted to view the scirpt (see Figure 11-11).

Connecting to an Existing Database Using the Generate Wizard

Instead of creating a new database from within VEA, can generate your schema into an existing database. To generate into an existing databse:

1. Create a DSN for an existing database using the Windows administrative tools.

   Note

   The database you use does not have to be empty, but make sure that none of the existing database objects (i.e., tables, constraints, views, triggers, etc) share a name with the objects in the model you intend to generate. For instance, if you are going to generate the Patient_Ch_11.vsd model into an existing database, that database must not contain a table named Patient .

2. Invoke the Generate Wizard and make the same choices on the first page as you did in the previous examples (see Figure 11-18). Click the Next button.
3. On page two of the Generate Wizard (Figure 11-30), choose the radio button labeled Database already exists . The Data source name field, Setup button, and Database name field will all be disabled. Click the Next button.

   Figure 11-30: Generating to an Existing Database, page two.

4. In the text box labeled Data Sources (Figure 11-31), the generate wizard shows a list of DSNs that are defined on your machine.

   Figure 11-31: Generating into an existing database, page three.

   Highlight the correct DSN and click the Next button.

5. From this point forward, the generation process is the same as steps 13 through 18 of the previous example.

The ODBC Driver Setup Pane

As shown in Figure 11-17, models ard translated by VEA database drivers, which connect to databases via a DSN. Each DSN is associated with an ODBC driver. For database generation to function properly, the ODBC driver of a DSN must be designed for the same target database engine as the selected VEA driver.

Ideally, the list of available DSNs in Figure 11-31 should be limited based upon the ODBC driver associated with each DSN. For instance, if you are using the VEA MS SQL Server database driver, then the list in Figure 11-31 should only include DSNs that use the SQL server ODBC driver. Similarly, if they are using the VEA Oracle Server driver, the list of DSNs should only include those DSNs that use the Oracle ODBC drive .

By default all DSNs are included in the list of available data sources, regardless of the associated ODBC driver. To change this default behavior:

1. Choose Database > Options > Drivers from the main menu. Your screen should look like Figure 11-2. Highlight the Microsoft SQL Server driver and click the Setup button.
2. Select the ODBC Drivers tab (Figure 11-32) from the new window. Depending on the number of ODBC drivers installed on your computer, your list may be longer or shorter than the one shown in the figure. Scroll to the bottom of the list and select the SQL Server entry.

   Figure 11-32: VEA Driver setup ODBC Drivers pane.

3. Click OK to return to the Database Drivers window (see Figure 11-2), and then click OK again to return to the main window.

The VEA MS SQL Server driver is now associated with the SQL Server ODBC driver. From now on, the list of available DSNs will be limited to only those DSNs using the SQL Server ODBC driver. You can repeat this procedure for each VEA driver that you use, though it is not necessary for the proper functioning of the tool.