---

   slapd-sql    ( 5 )

---

METAINFORMATION USED

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.