Maintaining RPS Column Mappings

The RPS Column Mappings sheet of the Relational Population Service wizard enables you to map the columns in the relational database tables.

An example of the RPS Column Mappings sheet is shown in the following image.

By default, the RPS Column Mappings sheet displays all tables in the RPS mapping.

The default column name for a junction table column is class-name_oid. When the class of both collections is the same type, the second column in the junction table is created with the name class-name_oid2. You can change these junction column names but you cannot remove or split them.

In the Feature column, the key icon at the left of a feature indicates that it is the _type feature of a subclass of the JADE class mapped to the table of its superclass. (For details, see "Mapping Subclasses to a Class Map", earlier in this chapter.) The value assigned to the column for the subclass is shown in the ID column.

The properties (and methods, if selected) are sorted alphabetically with the properties first, followed by the methods that are also sorted alphabetically.

As columns in the RPS mapping are grouped by table, each alternating table in the RPS mapping has an alternating background color of light yellow or white, making it easier for you to distinguish between the tables when all tables are viewed on the RPS Column Mappings sheet.

If your mapping consists of more than 50 tables whose columns you want to map (for example, your RPS mapping has more than the maximum of 32,000 table columns), the View Selected Table option button is selected by default, to display only the columns in the table whose row is selected in the RPS Column Mappings table. You can then click the View All Tables option button at the upper right of the sheet, to display all tables in the RPS Column Mappings table. If fewer than 50 tables are available for view, the View All Tables option button is selected by default.

An example of the RPS Column Mappings sheet for a single relational database table is shown the following image.

To specify your column mappings

  1. If you want to change the name of a column, click in the required cell of the Column Name and then change the name to the value that you require. The name must be a valid column name in the target relational database and it can be a maximum of 80 characters.

  2. A default type mapping is defined for each JADE property type or column-mapping method return type. The drop-down list box in the Mapping column contains any valid alternative mapping types for this column. To change the mapping type, select the required value in this drop-down list.

    The options available depend on the type of database selected in the Database Type combo box on the Define RPS sheet of this wizard. For example, under SQL Server 2000 and SQL Server 2005, strings with fewer than 255 characters are mapped to VARCHAR and those 255 characters or more are mapped to TEXT. For more details, see "RPS Mapping", in Chapter 2 of the JADE Synchronized Database Service (SDS) Administration Guide.

    If column-mapping methods are included in the RPS mapping, these are displayed in a blue font. Column‑mapping methods that return a String, Binary, or Decimal primitive type have default mapping types defined to accommodate the largest possible values that could be returned.

    You can redefine the column type of a column‑mapping method to a more appropriate size for the data, by performing the following actions.

    1. In the drop-down list box in the Mapping column, select the value that has ? in the length (for example, VARCHAR[?]... or DECIMAL[?,?]...). The RPS Wizard Metrics dialog is then displayed.

    2. Uncheck the Maximum Length check box if you want to specify a length or scale factor for the method column type and then enter the appropriate value in the Length or Scale Factor text box.

      It is your responsibility to ensure that the values returned by the column-mapping method fit into the allocated lengths. An exception is raised in the RPS Datapump application if the lengths are not valid.

    3. Click the OK button to update your method column type value. Alternatively, click the Cancel button to abandon your selection.

  3. If you want to change the column name of a table or the column name of all tables:

    1. If you do not want the prefix or suffix to apply to all tables in the RPS mapping, uncheck the Modify All Tables check box. (A prefix or suffix value applies to all tables by default.)

    2. Ensure that the appropriate Add Prefix, Add Suffix, Remove Prefix, or Remove Suffix value is displayed in the Column Name list box.

    3. Specify the prefix or suffix value in the Value text box (for example, test_) that you want to apply to a table or to all tables in the RPS mapping.

    4. Click the Modify button.

    If the change that is being attempted would result in a duplicate column name, a message box is displayed, informing you that the change cannot be actioned. As no update action takes place, you must change the column name manually.

  4. If you want to change the column identifier generated by the Relational Population Service wizard for a _type column of a subclass of the JADE class mapped to the table of its superclass, enter the value that you require in the ID column.

    Although you can change the identifier of a _type column mapped to a column, you cannot delete it.

    When you specify a string in the ID column, the length value in the associated Mapping column is updated to reflect the size of the longest identifier value. (The ID column does not contain user data but a user-defined identifier that enables you to determine the type of data that is in the record.)

  5. In the Excluded column, check the check box in the cell of the Excluded column if you want to exclude the column from the RPS mapping when used on the RPS node, but retain the column in the schema definition.

    For details about customizing an RPS mapping for a specific site, see "Site-Specific RPS Mapping Customization", in Chapter 2 of the JADE Synchronized Database Service (SDS) Administration Guide.

  6. Each JADE class in a relational database table must have a primary key, to identify individual rows. The JADE object identifier (oid) maps to a relational database primary key attribute, which in Overwrite tables, identifies individual rows.

    Historical tables have oid, _edition, _operation, and _tranid defined as primary keys by default. You can add _timestamp special columns either in addition to or as a substitute for _tranid if it has been selected on the Select Columns for Tables sheet of the Relational Population Service wizard.

    See also "Primary Keys" under "RPS Mapping", in Chapter 2 of the JADE Synchronized Database Service (SDS) Administration Guide.

    You can change a primary key mapping for tables marked as No Deletes. Select a different key in the Key column of an attribute, if required. For details, see "Mapping Classes to Tables", earlier in this chapter.

    If the selected column has multiple duplicated values, insertions, updates, or deletions to the relational database may fail or overwrite data unintentionally. In most cases, you should leave the oid as the primary key.

  7. If you want to map the selected item into multiple columns:

    1. Right-click on the appropriate row and then select the Split Item Into Multiple Columns command from the popup menu that is then displayed. A row is then created below the selected row, with the background color of the Column Name column displayed in bright yellow as an indication that you must specify a name.

    2. In the highlighted Column Name column, specify a valid column name that is unique in the table.

    3. Change or specify values in the Mapping, ID, and Key columns for the selected attribute or method to column map, if required.

  8. To remove a selected column, right-click on that row and then select the Remove Column command from the popup menu that is displayed.