Creating Associations for JDBC Base Views Automatically¶

The administration tool can create automatically the associations between JDBC base views of the same data source. These associations will mirror the foreign key constraints of the tables/views in the source database.

To do this, follow these steps:

1. Select the JDBC base views for which you want to create its associations:

   With Design Studio: press the key Ctrl and click on each view. Then, on the bottom-left corner, click More > Discover associations.

   With the Administration Tool: right-click one of the views and click Discover associations. To select several views, press Ctrl and click each view. You can also click the first view, press Shift and click the last view you want to select.

2. The Tool will display a dialog like the one below, with the associations that will be created. In this dialog you can:

   • Change the name of the new association.

   • If you do not want to create one of the associations, clear the check box next to its name.

3. Click Ok. The Tool will create the associations.

Discover associations dialog¶

You can also discover the associations of a single JDBC base view by opening the view and clicking Associations. The tool will display a panel with the associations of the view. In this panel, click Discover.

The list of new associations of this dialog is based on the foreign key constraints the tables/views had in the database, at the time the base views were created. In the VQL of a base view, each parameter REFERENCES of the statement CREATE WRAPPER JDBC of the base view represents one of these constraints. If the foreign keys of the tables/views in the database changed since you created the base view, do a Source refresh of the base view to update this information.

To create an association involving a derived view or a non-JDBC base view, follow the steps of the section below.

Creating an Association Manually¶

To create a new association, click Association on the File > New menu or right-click the Server Explorer and click Association on the New menu.

The Tool will open the “View association” dialog and it will add the view that is currently selected in the Server Explorer. To add the other view of the association, click the Model tab and drag the view from the Server Explorer to this dialog.

The “View association” dialog has three tabs:

1. Model (see Creating a new association (“Model” tab)): tab where you have to drag the two views that will form the association and establish the mappings between the fields of the view. These views can belong to different databases.

   To link the field of a view with a field of another view, drag one field to the other. You will see a line between these fields. In views with many fields, you can right-click on the view and click Find to search the field you look for.

   To associate two fields, they must have the same type or compatible. For example, you can associate a float field with a double, an int with a long, etc.

   If you want to delete a view from the union, click on (in the top-right side of the view).

   The label on the top-left of each view – by default “(*)” – indicates the cardinality on that side of the association.

2. Output (see Creating a new association (“Output” tab)): in this tab you can do the following:

   1. Change the name of the association.

   2. Select/clear the check box Referential constraint. If selected, it indicates that this association represents a foreign key constraint. The section Referential Integrity in Associations explains in more detail the implications of selecting this check box.

   Note

   Only the associations marked as referential constraint are reported by the JDBC and the ODBC drivers as foreign keys.

   Note

   Virtual DataPort cannot enforce referential integrity of the data. The sources have to guarantee it.

   3. An association links two views and each view is one endpoint of the association. For each endpoint, do this:

      1. In the list next to the name of the view, select the multiplicity on that side of the association. The section Multiplicity of Associations below explains what the multiplicity of an association is.

      2. If “Referential constraint” is selected, in the next box, select Dependent if this side of the association represents a foreign key constraint. Otherwise, select Principal.

      3. Enter the role name of the end point and optionally, a description.

      4. Optionally, set a Role precondition. You can only do this on an end point when the other end point has a multiplicity of 0..1 or *. See more about this in the section Role Preconditions.

   4. In the “Model” tab you can only define mappings like “field X = field Y”. In this tab, you can define mappings that are more complex. To do this, click on to add a new mapping. The Tool will display a dialog where you specify the condition of the mapping. Each side of the condition must refer to the fields of only one of the views.

      To edit an existing mapping, click on it.

3. Metadata: tab that allows you to define the folder where the new association will be stored and provide a description for the association.

Creating a new association (“Model” tab)¶

Creating a new association (“Output” tab)¶