4.5. Superset Mapping Options

Contents

4.5.1. Introduction

4.5.2. Specifying Joins, ID Columns, and Concurrency Columns

4.5.3. Specifying a SQL Statement or Procedure Name

4.5.4. The Superset Options Form

4.5.1. Introduction

This section explains how to specify mapping options for a persistent classes with a SUPERSET mapping type. A persistent class with a SUPERSET mapping contains attributes that represent a result set derived from multiple tables. Superset mappings can be used to create "view classes" that hide the underlying data model, or to map a class inheritance tree to the database using a Vertical mapping approach. VBSF fully supports performing insertions, updates, and deletions on objects with this type of mapping, while transparently updating and maintaining all foreign key columns that join the tables.

Each attribute of a SUPERSET class must be associated with both a table and a column using the The Attribute Mapping Form. In addition one of the following two mapping options must be specified:

1. The table joins to be performed, the ID columns of all tables, and optionally any concurrency columns.
2. An actual SQL statement or stored procedure name to be executed.

4.5.2. Specifying Joins, ID Columns, and Concurrency Columns

Table joins and information on the ID and concurrency columns of all tables being joined is specified using the Superset Options Form described later in this section.

A table join is simply a specification containing two table names and the column names on those tables to be joined. A join is written in the format:

table1.column1 = table2.column2

This means that column1 of table1 is joined with column2 of table2. The number of table joins that must be specified is always one less than the number of tables to be joined. So for example if two tables are being joined, only one join must be specified. If three tables are joined, then two joins must be specified, and so on.

For example, in the vcontactdemo2 sample application the ThinContact class is a SUPERSET class containing attributes from two tables: CONTACT and CATEGORY. Below is the structure of both tables:

The ThinContact class is defined as shown below:

In this case we need to specify the following join:

CONTACT.CategoryID = CATEGORY.CategoryID

In addition to specifying the table joins, you must also specify the column names and the ID generation methods of the ID columns of all tables being joined. The only exception is for the ID column directly mapped to the object ID of the SUPERSET class, in which case the required information for that ID column is supplied in the object ID attribute mapping form and in the ID generation form of the SUPERSET class. In the example above, we must supply ID column information for the CATEGORY table using the Superset Options Form, but not for the CONTACT table because information about the CONTACT table ID column (ContactID) is supplied in the ID generation form of the ThinContact class and in the attribute mapping form of the id attribute.

If the any of the tables being joined contain concurrency columns, you must also specify the concurrency column for each table. Again, the exception is for the concurrency column of the table containing the column mapped to the object ID of the SUPERSET class, in which case the required information for that concurrency column is supplied in the Filter and Concurrency Columns form of the SUPERSET class.

Section 4.5.4. below illustrates how the above join and the CATEGORY table ID column information is specified in the VBSF mapping tool.

Mapping an Inheritance Tree using Vertical Mapping

SUPERSET mappings can also be used to map a class inheritance tree to the database using a Vertical mapping approach. In this case each class in the inheritance tree is mapped to a different table. For example, the person demo sample application maps the Person inheritance tree described in section 2.3. Mapping a Class Inheritance Tree to the database as shown below:

The Person class is specified as abstract in its Main Class form. The Person class is also specified as the superclass of the Employee and Customer classes in the Main Class form of each of its subclasses. All three classes are defined with a SUPERSET mapping in their Main Class form, and for each attribute a table and column is specified.

The following join is specified in the Superset Options form of the Employee class:

EMPLOYEE.PersonID = PERSON.PersonID

with the following ID column information:

Table: EMPLOYEE, ID Column: ID, Type: LONG, Column Type: COUNTER (for Access), ID Method: AUTO.

And the join below is specified in the Superset Options form of the Customer class:

CUSTOMER.PersonID = PERSON.PersonID

with the following ID column information:

Table: CUSTOMER, ID Column: ID, Type: LONG, Column Type: COUNTER (for Access), ID Method: AUTO.

For details see the person.schema file of the sample application.

4.5.3. Specifying a SQL Statement or Procedure Name

You can also specify your own SQL statement or stored procedure to execute to retrieve objects of a SUPERSET class. This option can be used when specifying mappings and joins is not sufficient. The SQL statement or stored procedure must return a result set matching the attribute structure of the persistent object, including the object ID. If you specify a SQL statement or a stored procedure in the Superset Options Form you lose the ability update the persistent class in the database and to query the persistent class with the OQuery class. If you would like to specify your own SQL statement or stored procedure and still be able to update the class in the database and to perform queries, specify the joins and ID columns instead, and define the SQL or stored procedure as a Named SQL Command.

4.5.4. The Superset Options Form

The Superset Options form is displayed when the "Superset Options" sub-header of a persistent class is clicked in the tree, but only if the corresponding class has been defined with a SUPERSET mapping type. The form is divided into four tabbed panels: Joins, ID Columns, Concurrency Columns, and SQL Command. You must either specify joins, id columns, and concurrency columns, or a SQL command, but not both. The SQL Command panel contains a checkbox labeled Specify SQL command that controls which of the two options is active. When this checkbox is not checked, all controls in the first three panels are enabled so you can enter joins, id column, and concurrency column information, and all controls in the SQL command panel are disabled. When this checkbox is checked, all controls in the SQL Command panel are enabled so you can enter a SQL command, and all controls in the other three panels are disabled.

Joins Panel

The Joins panel is used to specify table joins. This information is required to define a SUPERSET class. The actual join specified in the form below corresponds to the ThinContact join discussed in section 4.5.2. Specifying Joins, ID Columns, and Concurrency Columns. The table and column names of the join are entered into, or selected from the drop down list of,  the six combo boxes or text fields on the top half of the panel. The list area on the bottom half of the panel displays all joins previously entered via the combo boxes or text fields.

To add a new join, enter, or select from the combo box drop down list, the table and column name for each side of the join, and click the Add button. This moves the tables and column names from the combo boxes to the list area. VBSF is case sensitive when it comes to all table and column names, so when referring to the same table or column name in multiple forms the same case must be used. It is important to note that VBSF requires that primary key column names in the superclass table must have the same name as the foreign key columns in the subclass table. 

If generating a DDL script, you must also specify for each column its data type, and whether the column is a primary key (or part of a primary key) or indexed. If you leave the column data types blank and generate a DDL script, the script generator will display an error message.

To remove an existing join, select it in the list area and click Remove.

To edit an existing join, select it from the list area and click Edit. The table names, column names, and column data types will then be displayed in the combo boxes for editing. Once editing is complete click the Update button and the list area will be updated to reflect the modifications. If you do not click Update all changes to that join will be discarded when you leave the form.

ID Columns Panel

The ID Columns panel is used to specify specify the column names and the ID generation methods of the ID columns of all tables being joined, with the exception of the base table containing the ID column mapped to the object ID of the SUPERSET class. These tables are referred to as secondary tables. This information is required to define a SUPERSET class. The actual ID column specified in the form below corresponds to the ThinContact example discussed in section 4.5.2. Specifying Joins, ID Columns, and Concurrency Columns. Note that the only ID column specified is for the CATEGORY table. The ID column information for the CONTACT table is not specified in this form, but is instead supplied in the ID generation form of the ThinContact class and in the attribute mapping form of the id attribute.

The ID column information is entered into, or selected from the drop down list of, the seven combo boxes or text fields on the top half of the panel. The list area on the bottom half of the panel displays all ID columns previously entered via combo boxes or text fields.

To add a new ID column, enter the required information into the combo boxes and text fields on the top half of the panel and click the Add button. This moves the table and column name from the combo boxes to the list area.

To remove an existing ID column, select it in the list area and click Remove.

To edit an existing ID column, select it from the list area and click Edit. All ID column information will then be displayed in the combo boxes and text fields on the top half of the panel for editing. Once editing is complete click the Update button and the list area will be updated to reflect the modifications. If you do not click Update all changes will be discarded when you leave the form.

For each ID column the following information must be specified:

Table. Enter or select from the combo box drop down list the name of the secondary table whose ID column information is being entered. Table names are case sensitive, therefore all references to the same table name throughout the schema must use the same capitalization.

ID Column. Enter or select from the combo box drop down list the name of the ID column. Column names are case sensitive, therefore all references to the same column name throughout the schema must use the same capitalization.

Type. Select the corresponding attribute type of the ID column. Attribute types are defined in section 4.2.1. Attribute Types.

Column type. If generating a DDL script from the schema, enter the column data type. For floating point types you may directly specify the precision.   If you leave this field blank and generate a DDL script, the script generator will obtain the default column data type for the current Type from the DB Type Mappings panel of the configuration dialog box. If that value is also blank, the script generator will display an error message.

ID Method. Select from the combo box drop down list the ID generation method to be used to generate values for the ID column. The following table summarizes the possible ID generation methods. For additional information on these methods refer to section 4.3. ID Generation Methods.

Note: The ID OBJECT generation method is not supported for ID columns specified in this form.

If the AUTO or TRIGGER methods are selected, no further information needs to be entered and the remaining two text fields remain disabled.

If the SEQUENCE method is selected, the text field below the ID Method combo box will be enabled and its label will prompt you for the name of the sequence.

If the STORED PROCEDURE method is selected, the text field below the ID Method combo box will be enabled and its label will prompt you for the name of the stored procedure. You may enter the name of the procedure or its full call syntax. The last text field will also be enabled to allow you to enter a custom parameter marker. You may leave it blank to use the standard ODBC/JDBC question mark (?) as parameter marker.

If the FUNCTION method is selected, the text field below the ID Method combo box will be enabled and its label will prompt you for the name of the database function. You must enter the function's full call syntax.

If the ID TABLE method is selected, the text field below the ID Method combo box will be enabled and its label will prompt you for the name of the ID table. The last text field will also be enabled and its label will prompt you for the name of the ID table column.

The EXTERNAL method is used if the the ID column and the foreign key join column of the secondary table is the same. In this case there is no need to have a dedicated primary key column in the secondary table. The vpersondemo sample application demonstrates this feature. For example, in the Customer class SUPERSET options form the PersonID column of the Customer table is both the ID column (i.e. primary key) and the join column (i.e. foreign key) that joins the Customer table with the Person table. Its value is obtained automatically from the ID column of the Person table whenever a new Customer is inserted in the database. A more complex scenario involving composite foreign keys in a secondary table is demonstrated in the EMail class of the vcontactdemo3 sample application.

Concurrency Columns Panel

The Concurrency Columns panel is used to specify the names of any concurrency columns in the tables being joined (with the exception of the concurrency column of the table containing the column mapped to the object ID of the SUPERSET class, which is defined in the 'Filter and Concurrency Columns' form of the class that holds the object ID). This is an optional form. Concurrency columns are not required in order to define a SUPERSET class.

The actual concurrency column specified in the form below corresponds to the ThinContact example discussed in section 4.5.2. Specifying Joins, ID Columns, and Concurrency Columns. Note that the only concurrency column specified is for the CATEGORY table. The concurrency column information for the CONTACT table is not specified in this form, but is instead supplied in the Filter and Concurrency Columns form of the ThinContact class.

The concurrency column information is entered into, or selected from the drop down list of, the four combo boxes or text fields on the top half of the panel. The list area on the bottom half of the panel displays all concurrency columns previously entered via combo boxes or text fields.

To add a new concurrency column, enter the required information into the combo boxes and text fields on the top half of the panel and click the Add button. This moves the table and column name from the combo boxes to the list area.

To remove an existing concurrency column, select it in the list area and click Remove.

To edit an existing concurrency column, select it from the list area and click Edit. All concurrency column information will then be displayed in the combo boxes and text fields on the top half of the panel for editing. Once editing is complete click the Update button and the list area will be updated to reflect the modifications. If you do not click Update all changes will be discarded when you leave the form.

For each concurrency column the following information must be specified:

Table. Enter or select from the combo box drop down list the name of the table where the concurrency column is located. Table names are case sensitive, therefore all references to the same table name throughout the schema must use the same capitalization.

Column. Enter or select from the combo box drop down list the name of the concurrency column. Column names are case sensitive, therefore all references to the same column name throughout the schema must use the same capitalization.

Type. Select the corresponding attribute type of the concurrency column. Attribute types are defined in section 4.2.1. Attribute Types.

Column type. If generating a DDL script from the schema, enter the column data type. For string and numeric attributes you may directly specify the size and precision.  If you leave this field blank and generate a DDL script, the script generator will obtain the default column data type for the current Type from the DB Type Mappings panel of the configuration dialog box. If that value is also blank, the script generator will display an error message.

Nullable. This setting is only used when generating a DDL script from the schema. Check this option if the column is nullable.

Updated by VBSF. Select this option if the concurrency column is to be maintained by VBSF. In this case, the Type should be INTEGER or LONG.

Updated by Database. Select this option if the concurrency column is to be maintained by the database. In this case, the Type will probably be OBJECT.

Command Panel

This panel allows you to specify a custom SQL command or store procedure. This panel is disabled by default. To enable it check the Specify SQL command checkbox. When this panel is enabled, the Joins, ID Columns, and Concurrency Columns panels are automatically disabled. Once enabled, select from the Type combo box whether the command is a SQL statement or a stored procedure name. Finally enter the SQL statement or name of stored procedure in the text area. If the command is a stored procedure, enter just the name of the procedure (i.e. do not specify the full call syntax). Only SQL statements and stored procedures that do not accept or return any parameters can be specified in this form. You can specify SQL statements and procedures that accept or return parameters using Named SQL Commands.

Next Section 

Return to Table of Contents