slapd-sql.5

OpenLdap是LDAP的开源项目

.LP
The easiest way is to create an objectClass for each entity you had in
ER-diagram when designing your relational schema.
Any relational schema, no matter how normalized it is, was designed
after some model of your application's domain (for instance, accounts,
services etc. in ISP), and is used in terms of its entities, not just
tables of normalized schema.
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 the standard
schemas like inetOrgPerson etc.
.LP
Nevertheless, when you think it out, we must define a way to translate
LDAP operation requests to (a series of) SQL queries.
Let us deal with the SEARCH operation.
.LP
Example:
Let's suppose that we store information about persons working in our 
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
  middle_name varchar
  ...
.fi
.LP
(PHONES contains telephone numbers associated with persons).
A person can have several numbers, then PHONES contains several
records with corresponding pers_id, or no numbers (and no records in
PHONES with such pers_id).
An LDAP objectclass to present such information could look like this:
.LP
.nf
  person
  -------
  MUST cn
  MAY telephoneNumber $ firstName $ lastName
  ...
.fi
.LP
To fetch all values for cn attribute given person ID, we construct the
query:
.LP
.nf
  SELECT CONCAT(persons.first_name,' ',persons.last_name)
      AS cn FROM persons WHERE persons.id=?
.fi
.LP
for telephoneNumber we can use:
.LP
.nf
  SELECT phones.phone AS telephoneNumber FROM persons,phones
      WHERE persons.id=phones.pers_id AND persons.id=?
.fi
.LP
If we wanted to service LDAP requests with filters like
(telephoneNumber=123*), we would construct something like:
.LP
.nf
  SELECT ... FROM persons,phones
      WHERE persons.id=phones.pers_id
          AND persons.id=?
          AND phones.phone like '%1%2%3%'
.fi
.LP
(note how the telephoneNumber match is expanded in multiple wildcards
to account for interspersed ininfluential chars like spaces, dashes
and so; this occurs by design because telephoneNumber is defined after 
a specially recognized syntax).
So, if we had information about what tables contain values for each
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
and fill it with data (see samples):
.LP
.nf
  ldap_oc_mappings (some columns are not listed for clarity)
  ---------------
  id=1
  name="person"
  keytbl="persons"
  keycol="id"
.fi
.LP
This table defines a mapping between objectclass (its name held in the
"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 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
.nf
  ldap_attr_mappings (some columns are not listed for clarity)
  -----------
  id=1
  oc_map_id=1
  name="cn"
  sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
  from_tbls="persons"
  join_where=NULL
  ************
  id=<n>
  oc_map_id=1
  name="telephoneNumber"
  sel_expr="phones.phone"
  from_tbls="persons,phones"
  join_where="phones.pers_id=persons.id"
.fi
.LP
This table defines mappings between LDAP attributes and SQL queries
that load their values.
Note that, unlike LDAP schema, these are not
.B attribute types
- 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 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 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 a given search filter (or at least part of
it).
See below for examples.
.LP
.nf
  ldap_entries
  ------------
  id=1
  dn=<dn you choose>
  oc_map_id=...
  parent=<parent record id>
  keyval=<value of primary key>
.fi
.LP
This table defines mappings between DNs of entries in your LDAP tree,
and values of primary keys for corresponding relational data.
It has recursive structure (parent column references id column of the
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 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 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 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
  CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
      AS
          SELECT 0, UPPER('o=MyCompany,c=NL'),
              3, 0, 'baseObject' FROM unixusers WHERE userid='root'
      UNION
          SELECT (1000000000+userid),
              UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
              1, 0, userid FROM unixusers
      UNION
          SELECT (2000000000+groupnummer),
              UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
              2, 0, groupnummer FROM groups;
.fi

.LP
If your RDBMS does not support
.B unions
in views, only one objectClass can be mapped in
.BR ldap_entries ,
and the baseObject cannot be created; in this case, see the 
.B baseObject
directive for a possible workaround.

.LP
.SH Typical SQL backend operation
Having metainformation 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.
.LP
Example:
for our query with filter (telephoneNumber=123*) we would get the following 
query generated (which loads candidate IDs)
.LP
.nf
  SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
         ldap_entries.dn AS dn
    FROM ldap_entries,persons,phones
   WHERE persons.id=ldap_entries.keyval
     AND ldap_entries.objclass=?
     AND ldap_entries.parent=?
     AND phones.pers_id=persons.id
     AND (phones.phone LIKE '%1%2%3%')
.fi
.LP
(for ONELEVEL search)
or "... AND dn=?" (for BASE search)
or "... AND dn LIKE '%?'" (for SUBTREE)
.LP
Then, for each candidate, we load the requested attributes using
per-attribute queries like
.LP
.nf
  SELECT phones.phone AS telephoneNumber
    FROM persons,phones
   WHERE persons.id=? AND phones.pers_id=persons.id
.fi
.LP
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 the most relaxed SQL condition to filter candidates), and send it to
the user.
.LP
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 values of a given attribute, using
the given entry keyval (see examples -- mostly PostgreSQL, ORACLE and MSSQL 
- since as of this writing there are no stored procs in MySQL).
.LP
We just add more columns to ldap_oc_mappings and ldap_attr_mappings, holding
statements to execute (like create_proc, add_proc, del_proc etc.), and
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 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.
Fortunately, they are easy to adopt in this scheme.
The SQL backend suggests one more table being 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.
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
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
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 (brain-dead?) filter
of the form
\fB(!(hasSubordinates=TRUE))\fP
will give no results instead of returning all the leaf entries, because
it actually expands into \fB... AND NOT (1=1)\fP.
If you need to find all the leaf entries, please use
\fB(hasSubordinates=FALSE)\fP
instead.
.LP
A directoryString value of the form "__First___Last_"
(where underscores should be replaced by spaces) 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 
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 
allows caching of LDAP search requests (queries) in a local database.
See 
.BR slapo-pcache (5)
for details.
.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
ETCDIR/slapd.conf
default slapd configuration file
.SH SEE ALSO
.BR slapd.conf (5),
.BR slapd (8).