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.2 Reference.
For information about how to create and configure JDBC data views, see the following procedures.
You cannot use DSCC to perform this task. Use the command line, as described in this procedure.
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:
The name of the relational database, for example, payrolldb.
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.
The JDBC driver class, for example org.hsqldb.jdbcDriver.
The path to the JDBC driver, for example file:///path/to/hsqldb/lib/hsqldb.jar.
The driver-url property is multi-valued. Hence, driver-url can support multiple JAR files for the JDBC driver to ensure connectivity to the JDBC source on different platforms.
Create a JDBC data source pool.
$ dpconf create-jdbc-data-source-pool -h host -p port pool-name |
Attach the JDBC data source to the JDBC data source pool.
$ dpconf attach-jdbc-data-source -h host -p port pool-name source-name |
Create a JDBC data view.
$ dpconf create-jdbc-data-view -h host -p port view-name pool-name suffix-DN |
(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 |
You cannot use DSCC to perform this task. Use the command line, as described in this procedure.
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 |
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 ... ] |
When you configure a JDBC data view, you must also configure the following objects:
JDBC object class. Maps one or more JDBC tables to an LDAP object class.
JDBC table. Defined for each relational database table.
JDBC attribute. Defines an LDAP attribute from a specified column in a JDBC table.
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.
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.
(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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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}" |
This case is currently unsupported in Directory Proxy Server.