(OpenLDAP FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional
databases) to find out more on this point.
.LP
-The idea (detailed below) is to use some metainformation to translate
+The idea (detailed below) is to use some meta-information to translate
LDAP queries to SQL queries, leaving relational schema untouched, so
that old applications can continue using it without any
modifications.
replication, and exchange data as needed.
.LP
The SQL backend is designed to be tunable to virtually any relational
-schema without having to change source (through that metainformation
+schema without having to change source (through that meta-information
mentioned).
Also, it uses ODBC to connect to RDBMSes, and is highly configurable
for SQL dialects RDBMSes may use, so it may be used for integration
It may differ from one SQL dialect to another (see samples).
By default, it is constructed based on the knowledge about
how to normalize DN values (e.g.
-\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"\fP).
+\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"\fP);
+see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
+and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
.TP
.B children_cond <SQL expression>
It may differ from one SQL dialect to another (see samples).
By default, it is constructed based on the knowledge about
how to normalize DN values (e.g.
-\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"\fP).
+\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"\fP);
+see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
+and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
.TP
-.B use_subtree_shortcut { NO | yes }
+.B use_subtree_shortcut { YES | no }
Do not use the subtree condition when the searchBase is the database
suffix, and the scope is subtree; rather collect all entries.
+
.RE
-.SH STAMEMENT CONFIGURATION
+.SH STATEMENT CONFIGURATION
These options specify SQL query templates for loading schema mapping
-metainformation, adding and deleting entries to ldap_entries, etc.
+meta-information, adding and deleting entries to ldap_entries, etc.
All these and subtree_cond should have the given default values.
For the current value it is recommended to look at the sources,
or in the log output when slapd starts with "-d 5" or greater.
\fB"SELECT id,keyval,oc_map_id,dn FROM ldap_entries WHERE <DN match expr>"\fP,
where \fB<DN match expr>\fP is constructed based on the knowledge about
how to normalize DN values (e.g. \fB"dn=?"\fP if no means to uppercase
-strings are available; typically, \fB"<upper_func>(dn)=?"\fP
-is used).
+strings are available; typically, \fB"<upper_func>(dn)=?"\fP is used);
+see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
+and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.
.TP
.B insentry_stmt <SQL expression>
The statement that is used to delete an existing entry's ID
from table \fIldap_objclasses\fP; see "METAINFORMATION USED" for details.
The default is
-\fB""DELETE FROM ldap_entry_objclasses WHERE entry_id=?"\fP.
-
-.TP
-.B delreferrals_stmt <SQL expression>
-The statement that is used to delete an existing entry's ID
-from table \fIldap_referrals\fP; see "METAINFORMATION USED" for details.
-The default is
-\fB""DELETE FROM ldap_referrals WHERE entry_id=?"\fP.
+\fB"DELETE FROM ldap_entry_objclasses WHERE entry_id=?"\fP.
.RE
.SH HELPER CONFIGURATION
+These statements are used to modify the default behavior of the backend
+according to issues of the dialect of the RDBMS.
+The first options essentially refer to string and DN normalization
+when building filters.
+LDAP normalization is more than upper- (or lower-)casing everything;
+however, as a reasonable trade-off, for case-sensitive RDBMSes the backend
+can be instructed to uppercase strings and DNs by providing
+the \fBupper_func\fP directive.
+Some RDBMSes, to use functions on arbitrary data types, e.g. string
+constants, requires a cast, which is triggered
+by the \fBupper_needs_cast\fP directive.
+If required, a string cast function can be provided as well,
+by using the \fBstrcast_func\fP directive.
+Finally, a custom string concatenation pattern may be required;
+it is provided by the \fBconcat_pattern\fP directive.
.TP
.B upper_func <SQL function name>
is set to 8192).
This is \fIexperimental\fP and may change in future releases.
+.TP
+.B strcast_func <SQL function name>
+Specifies the name of a function that converts a given value to a string
+for appropriate ordering. This is used in "SELECT DISTINCT" statements
+for strongly typed RDBMSes with little implicit casting (like PostgreSQL),
+when a literal string is specified.
+This is \fIexperimental\fP and may change in future releases.
+
.TP
.B concat_pattern <pattern>
This statement defines the
This is \fIexperimental\fP and may change in future releases.
.TP
-.B strcast_func <SQL function name>
-Specifies the name of a function that converts a given value to a string
-for appropriate ordering. This is used in "SELECT DISTINCT" statements
-for strongly typed RDBMSes with little implicit casting (like PostgreSQL),
-when a literal string is specified.
-This is \fIexperimental\fP and may change in future releases.
+.B aliasing_keyword <string>
+Define the aliasing keyword. Some RDBMSes use the word "\fIAS\fP"
+(the default), others don't use any.
+
+.TP
+.B aliasing_quote <string>
+Define the quoting char of the aliasing keyword. Some RDBMSes
+don't require any (the default), others may require single
+or double quotes.
.TP
.B has_ldapinfo_dn_ru { NO | yes }
Explicitly inform the backend whether the dn_ru column
(DN in reverse uppercased form) is present in table \fIldap_entries\fP.
-Overrides automatic check (this is required, ofr instance,
+Overrides automatic check (this is required, for instance,
by PostgreSQL/unixODBC).
This is \fIexperimental\fP and may change in future releases.
dynamically creates the missing parent.
.TP
-.B baseObject [filename]
+.B baseObject [ <filename> ]
Instructs the database to create and manage an in-memory baseObject
entry instead of looking for one in the RDBMS.
If the (optional)
-.B filename
+.B <filename>
argument is given, the entry is read from that file in
.BR LDIF (5)
-form; otherwise, an entry with objectClass \fBextensibleObject\fP
+format; otherwise, an entry with objectClass \fBextensibleObject\fP
is created based on the contents of the RDN of the \fIbaseObject\fP.
This is particularly useful when \fIldap_entries\fP
information is stored in a view rather than in a table, and
.TP
.B create_needs_select { NO | yes }
-Instructs the database whether entry creation in table \fIldap_entries\fP
-needs a subsequent select to collect the automatically assigned ID,
-instead of being returned by a stored procedure.
+Instructs the database whether or not entry creation
+in table \fIldap_entries\fP needs a subsequent select to collect
+the automatically assigned ID, instead of being returned
+by a stored procedure.
+
+.LP
+.B fetch_attrs <attrlist>
+.br
+.B fetch_all_attrs { NO | yes }
+.RS
+The first statement allows to provide a list of attributes that
+must always be fetched in addition to those requested by any specific
+operation, because they are required for the proper usage of the
+backend. For instance, all attributes used in ACLs should be listed
+here. The second statement is a shortcut to require all attributes
+to be always loaded. Note that the dynamically generated attributes,
+e.g. \fIhasSubordinates\fP, \fIentryDN\fP and other implementation
+dependent attributes are \fBNOT\fP generated at this point, for
+consistency with the rest of slapd. This may change in the future.
+.RE
+
+.TP
+.B check_schema { YES | no }
+Instructs the database to check schema adherence of entries after
+modifications, and structural objectClass chain when entries are built.
+By default it is set to
+.BR yes .
.TP
.B sqllayer <name> [...]
keys - he may want to write a patch, and submit it to OpenLDAP ITS,
then I'll include it.
.LP
-Also, several people complained that they don't really need very
+Also, several users complained that they don't really need very
structured trees, and they don't want to update one more table every
time they add or delete an instance in the relational schema.
Those people can use a view instead of a real table for ldap_entries, something
directive for a possible workaround.
.LP
-.SH Typical SQL backend operation
-Having metainformation loaded, the SQL backend uses these tables to
+.SH TYPICAL SQL BACKEND OPERATION
+Having meta-information loaded, the SQL backend uses these tables to
determine a set of primary keys of candidates (depending on search
scope and filter).
It tries to do it for each objectclass registered in ldap_objclasses.
the user.
.LP
ADD, DELETE, MODIFY and MODRDN operations are also performed on per-attribute
-metainformation (add_proc etc.).
+meta-information (add_proc etc.).
In those fields one can specify an SQL statement or stored procedure
call which can add, or delete given values of a given attribute, using
the given entry keyval (see examples -- mostly PostgreSQL, ORACLE and MSSQL
information on this matter - they are self-explanatory for those familiar
with the concepts expressed above.
.LP
-.SH Common techniques (referrals, multiclassing etc.)
-First of all, let's remember that among other major differences to the
-complete LDAP data model, the concept above does not directly support
-such things as multiple objectclasses per entry, and referrals.
+.SH COMMON TECHNIQUES
+First of all, let's recall that among other major differences to the
+complete LDAP data model, the above illustrated concept does not directly
+support such features as multiple objectclasses per entry, and referrals.
Fortunately, they are easy to adopt in this scheme.
-The SQL backend suggests two more tables being added to the schema -
-ldap_entry_objectclasses(entry_id,oc_name), and
-ldap_referrals(entry_id,url).
+The SQL backend requires that one more table is added to the schema:
+ldap_entry_objectclasses(entry_id,oc_name).
.LP
-The first contains any number of objectclass names that corresponding
-entries will be found by, in addition to that mentioned in
-mapping.
+That table contains any number of objectclass names that corresponding
+entries will possess, in addition to that mentioned in mapping.
The SQL backend automatically adds attribute mapping for the "objectclass"
attribute to each objectclass mapping that loads values from this table.
So, you may, for instance, have a mapping for inetOrgPerson, and use it
for queries for "person" objectclass...
.LP
-The second table contains any number of referrals associated with a given entry.
-The SQL backend automatically adds attribute mapping for "ref" attribute
-to each objectclass mapping that loads values from this table.
-So, if you add objectclass "referral" to this entry, and make one or
-more tuples in ldap_referrals for this entry (they will be seen as
-values of "ref" attribute), you will have slapd return a referral, as
-described in the Administrators Guide.
+Referrals used to be implemented in a loose manner by adding an extra
+table that allowed any entry to host a "ref" attribute, along with
+a "referral" extra objectClass in table ldap_entry_objclasses.
+In the current implementation, referrals are treated like any other
+user-defined schema, since "referral" is a structural objectclass.
+The suggested practice is to define a "referral" entry in ldap_oc_mappings,
+holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute,
+containing the url; in case multiple referrals per entry are needed,
+a separate table for urls can be created, where urls are mapped
+to the respective entries.
+The use of the naming attribute usually requires to add
+an "extensibleObject" value to ldap_entry_objclasses.
+
.LP
-.SH Caveats
+.SH CAVEATS
As previously stated, this backend should not be considered
a replacement of other data storage backends, but rather a gateway
to existing RDBMS storages that need to be published in LDAP form.
.LP
The \fBhasSubordintes\fP operational attribute is honored by back-sql
in search results and in compare operations; it is partially honored
-also in filtering. Owing to design limitations, a (braindead?) filter
+also in filtering. Owing to design limitations, a (brain-dead?) filter
of the form
\fB(!(hasSubordinates=TRUE))\fP
will give no results instead of returning all the leaf entries, because
instead.
.LP
A directoryString value of the form "__First___Last_"
-(where underscores should be replaced by spaces) corresponds
+(where underscores mean spaces, ASCII 0x20 char) corresponds
to its prettified counterpart "First_Last"; this is not currently
honored by back-sql if non-prettified data is written via RDBMS;
-when non-prettified data is written thru back-sql, the prettified
+when non-prettified data is written through back-sql, the prettified
values are actually used instead.
+
+.LP
+.SH BUGS
+When the
+.B ldap_entry_objclasses
+table is empty, filters on the
+.B objectClass
+attribute erroneously result in no candidates.
+A workaround consists in adding at least one row to that table,
+no matter if valid or not.
+
.LP
.SH PROXY CACHE OVERLAY
The proxy cache overlay
.SH EXAMPLES
There are example SQL modules in the slapd/back-sql/rdbms_depend/
directory in the OpenLDAP source tree.
+.SH ACCESS CONTROL
+The
+.B sql
+backend honors access control semantics as indicated in
+.BR slapd.access (5)
+(including the
+.B disclose
+access privilege when enabled at compile time).
.SH FILES
.TP
projects / openldap / blobdiff