1.4 LDAP and SQL Integration
The LDAP JDBC Driver maps LDAP information into an SQL relational table. In general, LDAP class definitions become the table name, LDAP entries become the rows in the table, and LDAP attributes become the columns in the table. LDAP also maintains information about entries other than attributes. This information is mapped to the table in special columns. For more information about these topics, see
1.4.1 Supported SQL Syntaxes
The JDBC driver is read only, that is, it supports only select queries. The driver must implement all the JDBC interfaces for supporting the select operations. Following are the SQL syntaxes that are supported by this driver:
Predicates
The following predicates forms are supported:
Alias
AS
Grouping
ORDER BY
Aggregate
- AVG
- COUNT - COUNT(*) only
- MAX
- MIN
- SUM
Logical Operators
- AND
- OR
- NOT
Join queries
- Inner join
1.4.2 Mapping of LDAP Data to Relational Tables
The object class and attribute definitions in the LDAP schema are taken from their structure as a hierarchical X.500 directory and mapped to a flattened relational database table. Directory concepts such as object class inheritance, naming attributes, and attribute syntax give way to the relational database features of tables and columns. Actual entries, or objects created in the LDAP database, become the rows in the table.
For example, a table for the user object class with four entries and with three attributes (surname, givenName, and title) would look similar to the following diagram.
Figure 1-2 Mapping of LDAP Data to Retional Tables
The following table describes in general the LDAP elements and their SQL counterparts.
Table 1-5 LDAP Elements and their SQL Counterparts
|
SQL |
LDAP |
|---|---|
|
Database |
The selected tree and base domain name represent the database. These are selected when the connection is established with the LDAP server. |
|
Tables |
LDAP classes are represented as database tables. The table name is the same as the class name. Two of the LDAP class names have been given special table names to avoid conflicts with the SQL query keywords. The table for the User class is inetOrgPerson, and the table for group class is group eDirectory. |
|
Columns |
LDAP class attributes represent the table columns. Each attribute represents one or more table columns. If the attribute type has multiple data fields, it will result in more than one column in the database table. For example, the homeDirectory attribute will create the following three columns in the table:
For more information, see Composite Attributes. |
|
Records |
Each LDAP entry represents one or more rows in the database table. If an entry has a multi-valued attribute and multiple values have been assigned to the attribute, the entry can have multiple rows in the table (see Multi-Valued Attributes). |
|
Special Columns |
Special columns are used to present additional information items which are available from the LDAP database but are not LDAP attributes. These items include the entry's context, tree name, and distinguished name. For more information, see Special Columns in Tables. |
1.4.3 Special Columns in Tables
LDAP does not use attributes to keep track of the following information about entries:
- The tree in which the entry resides
- The fully distinguished name of the entry
- The fully distinguished name of the context that contains the entry
- The relative name of the entry
Since these are not attributes, the LDAP JDBC Driver provides column names for this information, and these names can be used just like attribute names in SQL statements. All tables can contain the following columns:
- NDS_FullName
- NDS_Context
- NDS_Name
1.4.4 Composite Attributes
Some attributes use a syntax that contains multiple data fields. For example, the homeDirectory attribute uses the Path syntax. This syntax has three fields: name space, volume, and path. The JDBC driver splits such attributes into multiple columns, one for each data field in the syntax. The name of each column consists of the attribute name followed by an underscore and the field name. The homeDirectory attribute is split into the following columns:
- homeDirectory_NameSpace
- homeDirectory_VolName
- homeDirectory_Path
Column Names and Data. The column names may not be completely applicable to attributes which extend the schema. A syntax can be used to store data other than data specified by the label as long as the data fits the data type. For example, the name space field can contain 4 bytes of data, but LDAP does not verify that the data contains a valid name space value. The volume field contains a distinguished name which LDAP verifies. However, LDAP does not verify that it contains a volume name, only that it contains a distinguished name of an entry in the directory. The path field contains a string which LDAP stores but does not verify. Therefore, the path syntax can be used to store the distinguished name of any entry in the directory with a string value (perhaps a description) and 4-byte value (perhaps a integer level).
SQL Statements. When specifying a composite attribute in an SQL select statement, each column that you want must be included in the statement. For example, to select only one of the columns for the homeDirectory attribute, you would use the following:
select homeDirectory_NameSpace from inetOrgPerson
To select all columns, you would use the following:
select homeDirectory from inetOrgPerson
Notice, you can specify the attribute name for composite attributes when you want all fields.The multiple fields are put in the same column, and the fields are separated with either a dollar ($) sign for similar data or a pound (#) sign for dissimilar data.
Columns for Composite Attributes. The following table lists the possible columns for each syntax that has multiple fields. The NDS/eDirectory column lists the minimum version required for LDAP support for the syntax. The JDBC data types are VARCHAR unless noted.
Table 1-6 Syntax with Multiple Fields
1.4.5 Multi-Valued Attributes
Most attributes are multi-valued, meaning that the attribute can contain more than one value. The order of the values is not guaranteed, and the order can vary from replica to replica. For example, the givenName attribute is multi-valued so that a User entry can have his or her legal given name and multiple nick names, for example, Elizabeth, Liz, and Beth.
When including attributes in a report, you need to know whether the attributes are multi-valued. (Consult the attribute definition in the Developer Kit for this information.) If your report selects multi-valued attributes, you need to select one of the following methods of reporting them:
Multiple Rows
The standard method of reporting multiple values in an SQL report is to put each value in a separate row. If you include two or more multi-valued attributes in a report and select to report each value on a separate row, the size of the table grows by multiples of the number of values. For example, suppose you request a report that includes a user’s telephoneNumber and groupMembership attributes, and each user has 2 telephone numbers and belongs to 3 groups. The table will have six rows for each user. The figure below illustrates such a table.
Figure 1-3 Reporting Multiple Values in an SQL Report
If your report is gathering information for 100 users, instead of a 100 row table your report generates a 600 row table. Depending on available workstation resources and the number of multi-valued attributes you have included in the report, you could run out of memory or hard disk space before the report is completed. For a solution to this problem, see Concatenating Rows.
To determine whether an attribute is multi-valued, see the Developer Kit.
For information on how an eDirectory syntax is translated to an SQL data type, see Data Type Mappings.
Concatenating Rows
Concatenation allows multiple values of an attribute to be written to a single row in a column, with values separated by a specified delimiter. If your report has selected two or more attributes with multiple values, concatenating values into a single row reduces the size of the table (see “Multiple Rows” for information on how a table can increase in size with multi-valued attributes). The disadvantage of concatenation is that the values can get very long. The advantage is that you can create a report that contains many multi-valued attributes without increasing the number of rows in the report.
Multi-valued attributes have two column names to select from when creating a report. The column name that ends with an _S suffix is the name for concatenating values. For example, the column name for the cn attribute, which is multi-valued, is cn. When cn is selected, the driver produces a row for each value. When the cn_s column is selected, the driver concatenates the values into a single row in the column.
The following sections supply the information you need to concatenate values in your reports:
Attributes Eligible for Concatenation
Not all multi-valued attributes can be concatenated. The LDAP JDBC Driver supports concatenation of attributes only if they use one of the following syntaxes:
- Case Exact String
- Case Ignore String
- Class Name
- Distinguished Name
- Facsimile Telephone Number
- Network Address (Address column only)
- Numeric String
- Printable String
- Telephone Number
- Typed Name (ObjectName column only)
Thus, an attribute must meet two conditions for concatenation: be multi-valued and use one of the supported syntaxes. You can use the Developer Kit to verify which attributes support these conditions or use the JDBC catalog function of your SQL tool to query the schema for a list of all columns. The tool returns the column names for all the attributes defined in that directory. Attributes which can be concatenated have a name with an _S suffix.
Separator Characters Used by Concatenation
Value concatenation requires the use of a separator character to indicate the end of one value and the beginning of another. Since attributes can store a variety of data types, a single separator cannot be selected that will be appropriate for all possible values. The LDAP JDBC Driver allows you to set the separator for each report. The default separator is a comma. You can use any single character string as the separator or one of the following escaped values for non-printing characters.
Separator Character Functions
The LDAP JDBC Driver supports two scalar functions for managing the separator character used when concatenating values.
Table 1-8 Scalar Functions
These functions use the standard SQL escape syntax for scalar functions.
1.4.6 Data Type Mappings
Each column of a table must have an associated data type. The allowed data types are defined by the JDBC protocol. By default, the LDAP protocol returns all attribute values as strings. Even values that contain numeric data are converted to their string representation. Since the JDBC driver is primarily intended for report generation, this default behavior is perfectly acceptable. Except for the exceptions noted below, all columns are of the JDBC type VARCHAR or LONGVARCHAR, which are variable length character strings.
Data Type Mappings for Non-Character Strings. The following table lists the data type mappings for syntaxes that do not map to VARCHAR or LONGVARCHAR.
1.4.7 Effective Rights Table
Most of the tables available to the JDBC driver are created from the object class definitions in the schema. The one exception is the effective rights table. This table is not part of the schema and is created by the JDBC driver. The four object columns contain information about the entry to which the trustee has rights. The four trustee columns contain information about the entry who has been granted rights to the entry specified by the object columns.
The table uses the baseDN property to determine where to start reading ACL attributes. The table generates a large report unless restricted by a WHERE clause. It includes the following:
- Rows for all entries below the context specified by the baseDN property
- A row for every entry in the eDirectory tree as a trustee for each ObjectName included in the report
If you have 1,000 entries in the eDirectory tree, each entry will have rows for the 1,000 potential trustees. In addition, if you specify all attributes, the report will have the 1,000 rows for each attribute.
To restrict the size of the report to the information you really want, use a WHERE clause with values for the following columns:
- ObjectName
- ObjectContext
- ObjectClass
- TrusteeName
- TrusteeContext
- TrusteeClass
- Attribute
Do not use ObjectFullname or TrusteeFullname to restrict the size. These two columns, when used in a WHERE clause, noticeably degrade performance.
For a sample SQL command, see Restricted Effective Rights Query.
The following table does not contain columns for the Attribute Inheritance Control or the Entry Inheritance Control rights because these rights are used to calculate effective rights, and once used to calculate rights, the other rights specify what has been granted.
Table 1-11 Effective Rights Table