Defining Relationships Between JDBC Tables

In the simplest case, a JDBC object class contains only a single (primary) table. There is no secondary table, and thus no need to define relationships between tables.

If the object class contains more than one table, the relationships between these tables must be clearly defined. The relationships between tables are always defined on the secondary table. The following properties of a secondary table enable you to define these relationships:

• is-single-row-table specifies that an LDAP entry has only one matching row in the table.

• contains-shared-entries specifies that a row in the secondary table is used by more than one row in the primary table.

• filter-join-rule indicates how an entry should be retrieved from the secondary table based on something in the primary table.

The following examples illustrate how the filter join rule is defined, based on the values of the first two properties. These examples assume that the object class has one primary table and one secondary table.

Example 22–1 is-single-row-table:true and contains-shared-entries:true

These are the default values of these properties. In this case, the relationship between the primary and secondary tables is n->1, that is, n rows in the primary table reference one shared row in the secondary table.

In the relational database, a foreign key (FK) is defined in the primary table, and points to a column in the secondary table.

Take, for example, an organization in which several employees can share the same manager. Two relational database tables are defined, with the following structure:

primary table : EMPLOYEE [ID, NAME, FK_MANAGER_ID]
secondary table : MANAGER  [ID, NAME]

The following object class and attributes are defined:

object-class : employee
attr : name (from primary EMPLOYEE.NAME)
attr : manager (from secondary MANAGER.NAME)

The following filter join rule is defined in the secondary table:

ID=\${EMPLOYEE.FK_MANAGER_ID}"

In case of multiple secondary tables, you must configure filter-join-rule on each secondary table. For more information on how to configure filter-join-rule for multiple secondary tables, see the Step 12.

With this configuration, the following behavior occurs for LDAP operations:

• Adding an employee entry. If the manager in the employee entry does not exist in the table, a new row is created. If the manager does exist, an existing row is used.

• Replacing the value of the “manager” attribute in an entry. The value of the row MANAGER.NAME is changed.

• Deleting an employee entry. The row in the secondary table is not deleted because the manager entries are shared.

• Deleting the “manager” attribute from an entry. The row in the secondary table is deleted and the foreign key (EMPLOYEE.FK_MANAGER_ID) is set to NULL.

  If an attribute is deleted, the value of the attribute is set to NULL. But if the attribute is defined as a NOT NULL attribute in the table definition, the deletion is not possible.

Example 22–2 is-single-row-table:true and contains-shared-entries:false

In this case, the relationship between the primary and secondary tables is 1->1 or 1<-1, that is, one row in the primary table is referenced by one row in the secondary table.

In the relational database, the foreign key (FK) might be defined in the primary table, or in the secondary table.

Take, for example, an organization in which the UID of employees is stored in one table, and the surname of employees is stored in a second table. Two relational database tables are defined, with the following structure:

primary table : UID [ID, VALUE, FK_SN_ID]
secondary table : SN [ID, VALUE]

The following object class and attributes are defined:

object-class : employee
attr : uid (from primary UID.VALUE)
attr : sn (from secondary ID.VALUE)

The following filter join rule is defined in the secondary table:

ID=\${UID.FK_SN_ID}

You can use it in the following manner using the dpconf command:

dpconf set-jdbc-table-prop SN filter-join-rule:ID=\${UID.FK_SN_ID}

This configuration could be the other way around, with the foreign key FK_UID_ID stored in the secondary table, and pointing to UID.ID.

Example 22–3 is-single-row-table:false and contains-shared-entries:false

In this case, the relationship between the primary and secondary tables is 1->n, that is, one row in the primary table is referenced by n rows in the secondary table. This example illustrates the case of multi-valued attributes. A multi-valued attribute is represented as a set of rows in the secondary table, with one row per attribute value.

In the relational database, the foreign key is defined in the secondary table, and points to a column in the primary table.

Take, for example, an organization in which an employee can have several telephone numbers. Two relational database tables are defined, with the following structure:

primary table : EMPLOYEE [ID, NAME]
secondary table : PHONE [ID, VALUE, USER_ID]

The following object class and attributes are defined:

object-class : employee
attr : cn (from primary EMPLOYEE.NAME)
attr : telephoneNumber (from secondary PHONE.VALUE)

The following filter join rule is defined in the secondary table:

USER_ID=\${EMPLOYEE.ID}

Example 22–4 is-single-row-table:false and contains-shared-entries:true

This case is currently unsupported in Directory Proxy Server.