Almost everything mentioned later is illustrated in examples
located in the servers/slapd/back-sql/rdbms_depend/ directory in
the OpenLDAP source tree, and contains scripts for generating
sample database for Oracle, MS SQL Server, mySQL and more
(including PostgreSQL and IBM db2).
The first thing that one must arrange is what set of LDAP object
classes can present your RDBMS information.
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.
Also you might want your object classes to conform to some of the
standard schemas like inetOrgPerson etc.
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.
Example: Let's suppose that we store information about persons
working in our organization in two tables:
PERSONS PHONES
---------- -------------
id integer id integer
first_name varchar pers_id integer references persons(id)
last_name varchar phone
middle_name varchar
...
(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:
person
-------
MUST cn
MAY telephoneNumber $ firstName $ lastName
...
To fetch all values for cn attribute given person ID, we
construct the query:
SELECT CONCAT(persons.first_name,' ',persons.last_name)
AS cn FROM persons WHERE persons.id=?
for telephoneNumber we can use:
SELECT phones.phone AS telephoneNumber FROM persons,phones
WHERE persons.id=phones.pers_id AND persons.id=?
If we wanted to service LDAP requests with filters like
(telephoneNumber=123*), we would construct something like:
SELECT ... FROM persons,phones
WHERE persons.id=phones.pers_id
AND persons.id=?
AND phones.phone like '%1%2%3%'
(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.
To store such information, we add three more tables to our schema
and fill it with data (see samples):
ldap_oc_mappings (some columns are not listed for clarity)
---------------
id=1
name="person"
keytbl="persons"
keycol="id"
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).
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"
This table defines mappings between LDAP attributes and SQL
queries that load their values. Note that, unlike LDAP schema,
these are not 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.
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).
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.
ldap_entries
------------
id=1
dn=<dn you choose>
oc_map_id=...
parent=<parent record id>
keyval=<value of primary key>
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.
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.
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 like this (by Robin Elfrink):
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=',groupname),',o=MyCompany,c=NL')),
2, 0, groupnummer FROM groups;
If your RDBMS does not support unions in views, only one
objectClass can be mapped in ldap_entries, and the baseObject
cannot be created; in this case, see the baseObject directive for
a possible workaround.
