Sun Directory Server Enterprise Edition 7.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 Directory Server Enterprise Edition 7.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 \
    [-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.)

    You must finish db-url with a / for MySQL, DB2, and Derby databases and with a : for Oracle database.

    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.

    The driver-url property is multivalued. Hence, driver-url can support multiple JAR files for the JDBC driver to ensure connectivity to the JDBC source on different platforms.

    If a 3rd party JDBC driver, which is other than the JDBC driver provided by the DB Vendor, is used to connect to the RDBMS back-end, set the db-vendor property. For more information about the db-vendor property, see db-vendor(5dpconf)

  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-attr-date-format                       :  yyyy-MM-dd
    jdbc-attr-time-format                       :  hh:mm:ss
    jdbc-attr-timestamp-format                  :  yyyy-MM-dd hh:mm:ss
    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 ... ]
  3. (Optional) In addition to the ISO format, you can set JDBC attributes of type date, time, and timestamp attributes in all of the following formats as well.

    Following lists the components that constitutes date and time:


    Letter      Date or Time Component   Examples
    G           Era designator           AD
    y           Year                     1996; 96
    M           Month in year            July; Jul; 07
    w           Week in year             27
    W           Week in month            2
    D           Day in year              189
    d           Day in month             10
    F           Day of week in month     2
    E           Day in week              Tuesday; Tue
    a           Am/pm marker             PM
    H           Hour in day (0-23)       0
    k           Hour in day (1-24)       24
    K           Hour in am/pm (0-11)     0
    h           Hour in am/pm (1-12)     12
    m           Minute in hour           30
    s           Second in minute         55
    S           Millisecond              978
    z           Time zone                Pacific Standard Time; PST; GMT-08:00
    Z           Time zone                -0800

    The following examples show how date and time patterns are interpreted in the U.S. locale. The given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.


    Date and Time Pattern                Result
    "yyyy.MM.dd G 'at' HH:mm:ss z"       2001.07.04 AD at 12:08:56 PDT
    "EEE, MMM d, ''yy"                   Wed, Jul 4, '01
    "h:mm a"                             12:08 PM
    "hh 'o''clock' a, zzzz"              12 o'clock PM, Pacific Daylight Time
    "K:mm a, z"                          0:08 PM, PDT
    "yyyyy.MMMMM.dd GGG hh:mm aaa"       02001.July.04 AD 12:08 PM
    "EEE, d MMM yyyy HH:mm:ss Z"         Wed, 4 Jul 2001 12:08:56 -0700
    "yyMMddHHmmssZ"                      010704120856-0700
    "yyyy-MM-dd'T'HH:mm:ss.SSSZ"         2001-07-04T12:08:56.235-0700

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.


    Note –

    If your relational database is not case sensitive, use ldap-syntax with the default value, that is, cis. ldap-syntax:ces is not supported with the case insensitive databases.


  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. DN pattern describes which attributes are to be used to construct DN of the entry. For example, when you specify DN pattern as uid then the DN of the entry is constructed using attribute uid and view base of the data view. For example, uid=bjensen,ou=people,dc=example,dc=com. The DN pattern can constitute multiple attributes. In that case, attributes should be separated by , (comma). For example, if DN pattern is specified as uid,country, DN of the entry returned by the data view is uid=bjensen,country=America,ou=people,dc=example,dc=com.

    When data source contains duplicate entries and the duplicate entries are not required, set perform-distinct-select to true using the following command:


    % dpconf set-jdbc-object-class-prop view-name objectclass perform-distinct-select:true

    All the subtree components defined in the DN pattern of JDBC object class should have a JDBC object class defined for them. For example, if there is a DN pattern uid,ou in a JDBC object class, there should be a JDBC object class definition with a DN pattern ou. This is necessary for Directory Proxy Server to construct a properly structured DIT. Otherwise, the subtree with values like ou=xxx,base-DN would not be returned in the search results.

  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 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:



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.