-.TH SLAPD-SQL 5 "30 April 2002" "OpenLDAP LDVERSION"
+.TH SLAPD-SQL 5 "RELEASEDATE" "OpenLDAP LDVERSION"
.\" $OpenLDAP$
.SH NAME
slapd-sql \- SQL backend to slapd
.SH SYNOPSIS
ETCDIR/slapd.conf
-.SH PURPOSE
-The primary purpose of this backend (8) to
+.SH DESCRIPTION
+The primary purpose of this
.BR slapd (8)
-is to PRESENT information stored in some RDBMS as an LDAP subtree
+backend is to PRESENT information stored in some RDBMS as an LDAP subtree
without any programming (some SQL and maybe stored procedures can't be
considered programming, anyway ;).
.LP
That is, for example, when you (some ISP) have account information you
-use in RDBMS, and want to use modern solutions that expect such
+use in an RDBMS, and want to use modern solutions that expect such
information in LDAP (to authenticate users, make email lookups etc.).
Or you want to synchronize or distribute information between different
sites/applications that use RDBMSes and/or LDAP.
Or whatever else...
.LP
-It is NOT designed as general-purpose backend that uses RDBMS instead
-of BerkeleyDB (as the standard LDBM backend does), though it can be
+It is NOT designed as a general-purpose backend that uses RDBMS instead
+of BerkeleyDB (as the standard BDB backend does), though it can be
used as such with several limitations.
You can take a look at
.B http://www.openldap.org/faq/index.cgi?file=378
for SQL dialects RDBMSes may use, so it may be used for integration
and distribution of data on different RDBMSes, OSes, hosts etc., in
other words, in highly heterogeneous environment.
+.LP
+This backend is experimental.
+.SH CONFIGURATION
+These
+.B slapd.conf
+options apply to the SQL backend database.
+That is, they must follow a "database sql" line and come before any
+subsequent "backend" or "database" lines.
+Other database options are described in the
+.BR slapd.conf (5)
+manual page.
+.TP
+.B dbname <datasource name>
+The name of the ODBC datasource to use.
+.LP
+.B dbhost <hostname>
+.br
+.B dbuser <username>
+.br
+.B dbpasswd <password>
+.RS
+These three options are generally unneeded, because this information is already
+taken from the datasource.
+Use them if you need to override datasource settings.
+Also, several RDBMS' drivers tend to require explicit passing of user/password,
+even if those are given in datasource (Note:
+.B dbhost
+is currently ignored).
+.RE
+.TP
+.B subtree_cond <SQL expression>
+Specifies a where-clause template used to form a subtree search condition
+(dn=".*<dn>").
+It may differ from one SQL dialect to another (see samples).
+.TP
+.B children_cond <SQL expression>
+Specifies a where-clause template used to form a children search condition
+(dn="\.+,<dn>").
+It may differ from one SQL dialect to another (see samples).
+.TP
+.B oc_query <SQL expression>
+The default is
+.B "SELECT id, name, keytbl, keycol, create_proc, delete_proc, expect_return FROM ldap_oc_mappings"
+.TP
+.B at_query <SQL expression>
+The default is
+.B "SELECT name, sel_expr, from_tbls, join_where, add_proc, delete_proc, param_order, expect_return FROM ldap_attr_mappings WHERE oc_map_id=?"
+.TP
+.B insentry_query <SQL expression>
+The default is
+.B "INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES (?, ?, ?, ?)"
+.TP
+.B delentry_query <SQL expression>
+The default is
+.B "DELETE FROM ldap_entries WHERE id=?"
+
+These four options specify SQL query templates for loading schema mapping
+metainformation,
+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.
+Note that the parameter number and order must not be changed.
+.TP
+.B upper_func <SQL function name>
+Specifies the name of a function that converts a given value to uppercase.
+This is used for CIS matching when the RDBMS is case sensitive.
+.TP
+.B upper_needs_cast { yes | no }
+Set this directive to
+.B yes
+if
+.B upper_func
+needs an explicit cast when applied to literal strings. The form
+.B cast (<arg> as varchar(<max DN length>))
+is used, where
+.B <max DN length>
+is builtin.
+This is
+.B experimental
+and may change in future releases.
+.TP
+.B concat_pattern <pattern>
+This statement defines the
+.B pattern
+to be used to concatenate strings. The
+.B pattern
+MUST contain two question marks, '?', that will be replaced
+by the two strings that must be concatenated. The default value is
+.BR "CONCAT(?,?)";
+a form that is known to be highly portable is
+.BR "?||?",
+but an explicit cast may be required when operating on literal strings:
+.BR "cast(?||? as varchar(<length>))".
+On some RDBMSes the form
+.B "?+?"
+is known to work.
+Carefully check the documentation of your RDBMS or stay with the examples
+for supported ones.
+This is
+.B experimental
+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
+.B experimental
+and may change in future releases.
+.TP
+.B has_ldapinfo_dn_ru { yes | no }
+Explicitly inform the backend whether the SQL schema has dn_ru column
+(dn in reverse uppercased form) or not.
+Overrides automatic check (required by PostgreSQL/unixODBC).
+This is
+.B experimental
+and may change in future releases.
+
+.TP
+.B fail_if_no_mapping { yes | no }
+When set to
+.B yes
+it forces write operations to fail if no appropriate mapping between LDAP
+attributes and SQL data is available. The default behavior is to ignore
+those changes that cannot be mapped correctly.
+This is
+.B experimental
+and may change in future releases.
+
.SH METAINFORMATION USED
.LP
Almost everything mentioned later is illustrated in examples located
in the
-.B slapd/back-sql/rdbms_depend/
+.B servers/slapd/back-sql/rdbms_depend/
directory in the OpenLDAP source tree, and contains scripts for
-generating sample database for Oracle,MS SQL Server and mySQL.
+generating sample database for Oracle, MS SQL Server, mySQL and more
+(including PostgreSQL and IBM db2).
.LP
-First thing that one must arrange for himself is what set of LDAP
+The first thing that one must arrange is what set of LDAP
object classes can present your RDBMS information.
.LP
The easiest way is to create an objectclass for each entity you had in
It means that for every attribute of every such instance there is an
effective SQL query that loads its values.
.LP
-Also you might want your object classes to conform to some of standard
+Also you might want your object classes to conform to some of the standard
schemas like inetOrgPerson etc.
.LP
Nevertheless, when you think it out, we must define a way to translate
-LDAP operation requests to (series of) SQL queries.
+LDAP operation requests to (a series of) SQL queries.
Let us deal with the SEARCH operation.
.LP
Example:
organization in two tables:
.LP
.nf
- PERSONS PHONES
- ---------- -------------
- id integer id integer
- first_name varchar pers_id integer references persons(id)
- last_name varchar phone
+ PERSONS PHONES
+ ---------- -------------
+ id integer id integer
+ first_name varchar pers_id integer references persons(id)
+ last_name varchar phone
middle_name varchar
...
.fi
person
-------
MUST cn
- MAY telephoneNumber
- MAY firstName
- MAY lastName
+ MAY telephoneNumber $ firstName $ lastName
...
.fi
.LP
.LP
.nf
SELECT phones.phone AS telephoneNumber FROM persons,phones
- WHERE persons.id=phones.pers.id AND persons.id=?
+ WHERE persons.id=phones.pers_id AND persons.id=?
.fi
.LP
If we wanted to service LDAP requests with filters like
.LP
.nf
SELECT ... FROM persons,phones
- WHERE persons.id=phones.pers.id
+ WHERE persons.id=phones.pers_id
AND persons.id=?
AND phones.phone like '123%'
.fi
.LP
So, if we had information about what tables contain values for each
-attribute, how to join this tables and arrange these values, we could
+attribute, how to join these tables and arrange these values, we could
try to automatically generate such statements, and translate search
filters to SQL WHERE clauses.
.LP
-To store such information, we add three more tables to our schema, so
-that and fill it with data (see samples):
+To store such information, we add three more tables to our schema
+and fill it with data (see samples):
.LP
.nf
ldap_oc_mappings (some columns are not listed for clarity)
.fi
.LP
This table defines a mapping between objectclass (its name held in the
-"name" column), and a table that holds primary key for corresponding
+"name" column), and a table that holds the primary key for corresponding
entities.
For instance, in our example, the person entity, which we are trying
to present as "person" objectclass, resides in two tables (persons and
-phones), and is identified by persons.id column (that we will call
-primary key for this entity).
+phones), and is identified by the persons.id column (that we will call
+the primary key for this entity).
Keytbl and keycol thus contain "persons" (name of the table), and "id"
(name of the column).
.LP
ldap_attr_mappings (some columns are not listed for clarity)
-----------
id=1
- oc_id=1
+ oc_map_id=1
name="cn"
sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
from_tbls="persons"
that load their values.
Note that, unlike LDAP schema, these are not
.B attribute types
-- attribute "cn" for "person" objectclass can well
-have its values in different table than "cn" for other objectclass,
+- the attribute "cn" for "person" objectclass can
+have its values in different tables than "cn" for some other objectclass,
so attribute mappings depend on objectclass mappings (unlike attribute
types in LDAP schema, which are indifferent to objectclasses).
Thus, we have oc_map_id column with link to oc_mappings table.
.LP
-Now we cut the SQL query that loads values for given attribute into 3 parts.
+Now we cut the SQL query that loads values for a given attribute into 3 parts.
First goes into sel_expr column - this is the expression we had
between SELECT and FROM keywords, which defines WHAT to load.
Next is table list - text between FROM and WHERE keywords.
-It may contain aliases for convenience (see exapmles).
-The last is part of where clause, which (if exists at all) express the
-condition for joining the table containing values wich table
-containing primary key (foreign key equality and such).
-If values are in the same table with primary key, then this column is
+It may contain aliases for convenience (see examples).
+The last is part of the where clause, which (if it exists at all) expresses the
+condition for joining the table containing values with the table
+containing the primary key (foreign key equality and such).
+If values are in the same table as the primary key, then this column is
left NULL (as for cn attribute above).
.LP
Having this information in parts, we are able to not only construct
queries that load attribute values by id of entry (for this we could
store SQL query as a whole), but to construct queries that load id's
-of objects that correspond to given search filter (or at least part of
+of objects that correspond to a given search filter (or at least part of
it).
See below for examples.
.LP
same table), which allows you to add any tree structure(s) to your
flat relational data.
Having id of objectclass mapping, we can determine table and column
-for primary key, and keyval stores value of it, thus defining exact
-tuple corresponding to LDAP entry with this DN.
+for primary key, and keyval stores value of it, thus defining the exact
+tuple corresponding to the LDAP entry with this DN.
.LP
Note that such design (see exact SQL table creation query) implies one
-important constraint - the key must be integer.
-But all that I know about well-designed schemas makes me think that it
-s not very narrow ;) If anyone needs support for different types for
+important constraint - the key must be an integer.
+But all that I know about well-designed schemas makes me think that it's
+not very narrow ;) If anyone needs support for different types for
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
-structured tree, and they don't want to update one more table every
-time they add or delete instance in relational schema.
-Those can use a view instead of real table for ldap_entries, something
+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
like this (by Robin Elfrink):
.LP
.nf
It tries to do it for each objectclass registered in ldap_objclasses.
.LP
Example:
-for our query with filter (telephoneNumber=123*) we would get following
+for our query with filter (telephoneNumber=123*) we would get the following
query generated (which loads candidate IDs)
.LP
.nf
or "... AND dn=?" (for BASE search)
or "... AND dn LIKE '%?'" (for SUBTREE)
.LP
-Then, for each candidate, we load attributes requested using
+Then, for each candidate, we load the requested attributes using
per-attribute queries like
.LP
.nf
WHERE persons.id=? AND phones.pers_id=persons.id
.fi
.LP
-Then, we use test_filter() from frontend API to test entry for full
+Then, we use test_filter() from the frontend API to test the entry for a full
LDAP search filter match (since we cannot effectively make sense of
SYNTAX of corresponding LDAP schema attribute, we translate the filter
-into most relaxed SQL condition to filter candidates), and send it to
-user.
+into the most relaxed SQL condition to filter candidates), and send it to
+the user.
.LP
-ADD, DELETE, MODIFY operations also performed on per-attribute
+ADD, DELETE, MODIFY and MODRDN operations are also performed on per-attribute
metainformation (add_proc etc.).
In those fields one can specify an SQL statement or stored procedure
-call which can add, or delete given value of given attribute, using
-given entry keyval (see examples -- mostly ORACLE and MSSQL - since
+call which can add, or delete given values of a given attribute, using
+the given entry keyval (see examples -- mostly ORACLE and MSSQL - since
there're no stored procs in mySQL).
.LP
We just add more columns to oc_mappings and attr_mappings, holding
statements to execute (like create_proc, add_proc, del_proc etc.), and
-flags governing order of parameters passed to those statements.
+flags governing the order of parameters passed to those statements.
Please see samples to find out what are the parameters passed, and other
information on this matter - they are self-explanatory for those familiar
with concept expressed above.
.LP
-.SH common techniques (referrals, multiclassing etc.)
-First of all, lets remember that among other major differences to
+.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 for entry, and referrals.
+such things 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 schema -
+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).
.LP
-First contains any number of objectclass names that corresponding
+The first contains any number of objectclass names that corresponding
entries will be found by, in addition to that mentioned in
mapping.
-The SQL backend automatically adds attribute mapping for "objectclass"
-attribute to each objectclass mapping, that loads values from this table.
-So, you may, for instance, have mapping for inetOrgPerson, and use it
+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
-Second table contains any number of referrals associated with given entry.
+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.
+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 referral, as
-described in Administrators Guide.
+values of "ref" attribute), you will have slapd return a referral, as
+described in the Administrators Guide.
+.LP
+.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 hasSubordintes 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
+of the form
+\fB(!(hasSubordinates=TRUE))\fP
+will give no results instead of returning all the leaf entries.
+If you need to find all the leaf entries, please use
+\fB(hasSubordinates=FALSE)\fP
+instead.
+.LP
+\fBAttribute\fP and \fBObjectClass\fP inheritance is honored neither
+in filters nor in search results.
+So you may search for \fB(givenName=Mitya)\fP in the examples,
+but not for \fB(name=Mitya)\fP.
.LP
.SH EXAMPLES
There are example SQL modules in the slapd/back-sql/rdbms_depend/
-direcetory in the OpenLDAP source tree.
+directory in the OpenLDAP source tree.
.SH FILES
+.TP
ETCDIR/slapd.conf
+default slapd configuration file
.SH SEE ALSO
.BR slapd.conf (5),
.BR slapd (8).
projects / openldap / blobdiff