Sun Java System Directory Server Enterprise Edition 6.0 Administration Guide

Creating and Configuring JDBC Data Views

A JDBC data view enables you to make a relational database accessible to LDAP client applications. For information about how JDBC data views work, see JDBC Data Views in Sun Java System Directory Server Enterprise Edition 6.0 Reference.

For information about how to create and configure JDBC data views, see the following procedures.

ProcedureTo Create a JDBC Data View

You cannot use DSCC to perform this task. Use the command line, as described in this procedure.

  1. Create a JDBC data source for the relational database.


    $ dpconf create-jdbc-data-source -h host -p port -b db-name -B db-url -J driver-url \
     -S driver-class source-name
    

    Currently, only one JDBC data source is supported for each JDBC data view. In other words, you cannot load balance across JDBC data sources. To access multiple JDBC data sources, you can create a data view for each data source, and join them together with a join data view.

    The following properties must be set when you create a JDBC data source:

    db-name

    The name of the relational database, for example, payrolldb.

    db-url

    The URL to the database, in the form jdbc:vendor:driver://dbhost:dbport.

    The db-url is not a complete JDBC database URL, because it does not contain the database name. (The database name is specified by the db-name property.)

    driver-class

    The JDBC driver class, for example org.hsqldb.jdbcDriver.

    driver-url

    The path to the JDBC driver, for example file:///path/to/hsqldb/lib/hsqldb.jar.

  2. Create a JDBC data source pool.


    $ dpconf create-jdbc-data-source-pool -h host -p port pool-name
    
  3. Attach the JDBC data source to the JDBC data source pool.


    $ dpconf attach-jdbc-data-source -h host -p port pool-name source-name
    
  4. Create a JDBC data view.


    $ dpconf create-jdbc-data-view -h host -p port view-name pool-name suffix-DN
    
  5. (Optional) View the list of JDBC data views to check that your data view has been created successfully.


    $ dpconf list-jdbc-data-views -h host -p port
    

ProcedureTo Configure a JDBC Data View

You cannot use DSCC to perform this task. Use the command line, as described in this procedure.

  1. View the properties of a JDBC data view.


    $ dpconf get-jdbc-data-view-prop -h host -p port view-name
    

    The default properties of a JDBC data view are as follows:


    alternate-search-base-dn                    :  -
    attr-name-mappings                          :  none
    base-dn                                     :  o=sql1
    contains-shared-entries                     :  -
    description                                 :  -
    distribution-algorithm                      :  -
    dn-join-rule                                :  -
    dn-mapping-attrs                            :  none
    dn-mapping-source-base-dn                   :  none
    excluded-subtrees                           :  -
    filter-join-rule                            :  -
    is-enabled                                  :  true
    is-read-only                                :  false
    is-routable                                 :  true
    jdbc-data-source-pool                       :  pool-name
    lexicographic-attrs                         :  all
    lexicographic-lower-bound                   :  none
    lexicographic-upper-bound                   :  none
    non-viewable-attr                           :  -
    non-writable-attr                           :  -
    numeric-attrs                               :  all
    numeric-default-data-view                   :  false
    numeric-lower-bound                         :  none
    numeric-upper-bound                         :  none
    pattern-matching-base-object-search-filter  :  all
    pattern-matching-dn-regular-expression      :  all
    pattern-matching-one-level-search-filter    :  all
    pattern-matching-subtree-search-filter      :  all
    process-bind                                :  -
    replication-role                            :  master
    viewable-attr                               :  all except non-viewable-attr
    writable-attr                               :  all except non-writable-attr
  2. Change one or more of the properties that are listed in Step 1.


    $ dpconf set-jdbc-data-view-prop -h host -p port view-name property:value \
     [property:value ... ]

ProcedureTo Configure JDBC Tables, Attributes, and Object Classes

When you configure a JDBC data view, you must also configure the following objects:

  1. Create a JDBC table for each table in the relational database.


    % dpconf create-jdbc-table jdbc-table-name db-table
    

    The name of the db-table is case sensitive. Make sure that you use the identical case that is used in the relational database, otherwise operations that target that table might fail.

  2. Create a JDBC attribute for each column in each relational database table.


    % dpconf add-jdbc-attr table-name attr-name sql-column
    

    Creating a JDBC attribute maps the table column to an LDAP attribute.

  3. (Optional) If the column in the relational database is case sensitive, change the LDAP syntax of the JDBC attribute.


    % dpconf set-jdbc-attr-prop table-name attr-name ldap-syntax:ces

    The value of ldap-syntax is cis by default. This implies that the jdbc-attr is case insensitive. Change the value to ces if your relational database is case sensitive.

    Certain relational databases, such as Oracle and DB2, are case sensitive by default. LDAP is case insensitive by default. When Directory Proxy Server detects that a column of the relational database table is case sensitive, an ldapsearch query with the corresponding attribute in the filter is translated into a SQL query using the function UPPER.

    For example, the query ldapsearch -b "dc=mysuffix" "(attr=abc)" is translated into the following SQL query:


    SELECT * FROM mytable WHERE (UPPER(attr)='ABC')

    By default, this type of query is not indexed. Queries of this nature can therefore have a substantial performance impact.

    You can alleviate the performance impact in two ways:

    • By setting the ldap-syntax property of the jdbc-attr to ces.

    • By creating an index with the function UPPER for each jdbc-attr that might be used in an LDAP filter.

  4. Create a JDBC object class for the LDAP relational database table.


    % dpconf create-jdbc-object-class view-name objectclass primary-table \
      [secondary-table... ] DN-pattern
    

    Creating a JDBC object class essentially specifies an LDAP object class with which these tables will be associated. The JDBC object class also specifies the primary table and the secondary tables, if they exist.

    When you create a JDBC object class, you specify a DN pattern. The DN pattern shows how the DN of the entry will be constructed.

  5. If a secondary table exists, define the join rule between the primary table and the secondary table.


    % dpconf set-jdbc-table-prop secondary-table-name filter-join-rule:join-rule
    

    A join rule is defined on the secondary table and determines how data from that table is linked to data from the primary table. How you define the relationships between the primary and secondary tables of an object class is important. For more information, see Defining Relationships Between JDBC Tables.

  6. Specify the super class for the JDBC object class.


    % dpconf set-jdbc-object-class-prop view-name objectclass super-class:value
    

    The super class indicates the LDAP object class from which the JDBC object class inherits.

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:

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 24–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}"

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



Example 24–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}"

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 24–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 24–4 is-single-row-table:false and contains-shared-entries:true

This case is currently unsupported in Directory Proxy Server.