CONTENT1. Purpose2. Metainformation used3. Typical back-sql operation4. Several important common techniques (referrals, multiclassing)1. PurposePrimary purpose of this backend is to PRESENT information stored in some RDBMSas an LDAP subtree without any programming (some SQL and maybe storedprocedures can't be considered programming, anyway ;).That is, for example, when you (some ISP) have accountinformation you use in RDBMS, and want to use modern solutions that expect suchinformation in LDAP (to authenticate users, make email lookups etc.).Or you want to syncronize or distribute information between differentsites/applications that use RDBMSes and/or LDAP. Or whatever else...It is NOT designed as general-purpose backend that uses RDBMS instead ofBerkeleyDB (as standard back-ldbm does), though it can be used as suchwith several limitations. You can take a look athttp://www.openldap.org/faq/index.cgi?file=378 (OpenLDAP FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional databases)to find out more on this point.The idea (detailed below) is to use some metainformation to translateLDAP queries to SQL queries, leaving relational schema untouched, so thatold applications can continue using it without any modifications. This allowsSQL and LDAP applications to interoperate without replication, and exchangedata as needed.Back-sql is designed to be tunable to virtually any relational schema withouthaving to change source (through that metainformation mentioned).Also, it uses ODBC to connect to RDBMSes, and is highly configurable forSQL 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.2. Metainformation used***Almost everything mentioned later is illustrated in example, which is locatedin backsql/rdbms_depend directory, and contains scripts for generating sampledatabase for Oracle,MS SQL Server and mySQL.***First thing that one must arrange for himself is what set of LDAP objectclasses can present your RDBMS information.The easiest way is to create objectclass for each entity you hadin ER-diagram when designing your relational schema.Any relational schema, no matter how normalized it is, was designed aftersome model of your applications domain (for instance, accounts, services etc.in ISP), and is used in terms of its entities, not just tables of normalizedschema.It means that for every attribute of every such instance there is an effectiveSQL query that loads it's values.Also you might want your objectclasses to conform to some of standard schemaslike inetOrgPerson etc..Nevertheless, when you think it out, we must define a way to translate LDAPoperation requests to (series of) SQL queries. Let us deal with SEARCH operation.Example:Lets suppose that we store information about persons working in our organization in two tables:PERSONS   		PHONES----------           -------------id integer		id integerfirst_name varchar      pers_id integer references persons(id)last_name varchar       phonemiddle_name varchar...(PHONES contains telephone numbers associated with persons). A person can haveseveral numbers, then PHONES contains several records with corresponding pers_id, or no numbers (and no records in PHONES with such pers_id). LDAPobjectclass to present such information could look like this:person-------MUST cnMAY telephoneNumberMAY firstNameMAY 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 request with filter 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 '123%'So, if we had information about what tables contain values for each attribute, how to join this tables and arrange these values, we could tryto automatically generate such statements, and translate search filtersto SQL WHERE clauses.To store such information, we add three more tables to our schema, so that and fill it with data (see samples):ldap_oc_mappings (some columns are not listed for clarity)---------------id=1name="person"keytbl="persons"keycol="id"This table defines mapping between objectclass (its name held in "name" column),and table that holds 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 identifiedby persons.id column (that we will call primary key for this entity).keytbl and keycol thus contain "persons" (name of the table), and "id" (nameof the column).ldap_attr_mappings (some columns are not listed for clarity)-----------id=1oc_id=1name="cn"sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"from_tbls="persons"join_where=NULL************id=<n>oc_map_id=1name="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 thatload their values. Note that, unlike LDAP schema, these are not *attribute types*- attribute "cn" for "person" objectclass can well have it's values in differenttable than "cn" for other objectclass, so attribute mappings depend onobjectclass mappings (unlike attribute types in LDAP schema, which areindifferent to objectclasses). Thus, we have oc_map_id column with link tooc_mappings table. Now we cut the SQL query that loads values for given attribute into 3 parts.First goes into sel_expr column - this is the expression we had betweenSELECT and FROM keywords, which defines WHAT to load.Next is table list - text between FROM and WHERE keywords. It may containaliases for convenience (see exapmles).The last is part of where clause, which (if exists at all) express thecondition for joining the table containing values wich table containingprimary key (foreign key equality and such). If values are in the same tablewith 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 queriesthat load attribute values by id of entry (for this we could store SQL queryas a whole), but to construct queries that load id's of objects thatcorrespond to given search filter (or at least part of it).See below for examples.ldap_entries------------id=1dn=<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 hasrecursive 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 forprimary key, and keyval stores value of it, thus defining exact tuplecorresponding to LDAP entry with this DN.Note that such design (see exact SQL table creation query) implies oneimportant constraint - the key must be integer. But all that I know aboutwell-designed schemas makes me think that it s not very narrow ;)If anyone needs support for different types for keys - he may want to writea patch, and submit it to OpenLDAP ITS, then I'll include it.Also, several people complained that they don't really need very structuredtree, and they don't want to update one more table every time they add ordelete instance in relational schema. Those can use a view instead of realtable, something like this:Robin Elfrink wrote:> About using a view for ldap_entries...>> This is what I came up with this morning:>> CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval) AS>         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;>3. Typical back-sql operationHaving metainformation loaded, back-sql uses these tables to determine a setof primary keys of candidates (depending on search scope and filter). It triesto do it for each objectclass registered in ldap_objclasses.Exapmle:for our query with filter (telephoneNumber=123*) we would get following query generated (which loads candidate IDs)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 '123%')(for ONELEVEL search)or "... AND dn=?" (for BASE search)or "... AND dn LIKE '%?'" (for SUBTREE)Then, for each candidate, we load attributes requested using per-attribute querieslikeSELECT phones.phone AS telephoneNumber FROM persons,phones WHERE persons.id=? AND phones.pers_id=persons.idThen, we use test_filter() from frontend API to test entry for fullLDAP search filter match (since we cannot effectively make sense of SYNTAXof corresponding LDAP schema attribute, we translate the filter into most relaxedSQL condition to filter candidates), and send it to user.ADD,DELETE,MODIFY operations also performed on per-attribute metainformation(add_proc etc.). In those fields one can specify an SQL statement or stored procedurecall which can add, or delete given value of given attribute, using given entrykeyval (see examples -- mostly ORACLE and MSSQL - since there're no stored procs in mySQL).We just add more columns to oc_m,appings and attr_mappings, holding statementsto execute (like create_proc, add_proc, del_proc etc.), and flags governingorder of parameters passed to those statements.Please see samples to find out what are the parameters passed, and otherinformation on this matter - they are self-explanatory for those familiarwith concept expressed above.4. Several common techniques (referrals, multiclassing etc.)First of all, lets remember that among other major differences to completeLDAP data model, the concept above does not directly support such thingsas multiple objectclasses for entry, and referrals.Fortunately, they are easy to adopt in this scheme. Back-sql suggests two moretables being added to schema - ldap_entry_objectclasses(entry_id, oc_name), and ldap_referrals (entry_id,url).First contains any number of objectclass names that corresponding entrieswill be found by, in addition to that mentioned in mapping. Back-sqlautomatically adds attribute mapping for "objectclass" attribute to each objectclassmapping, that loads values from this table. So, you may, for instance, havemapping for inetOrgPerson, and use it for queries for "person" objectclass...Second table contains any number of referrals associated with given entry.Back-sql automatically adds attribute mapping for "ref" attribute to eachobjectclass mapping, that loads values from this table.So, if you add objectclass "referral" to this entry, and make one or moretuples in ldap_referrals for this entry (they will be seen as values of "ref"attribute), you will have slapd return referral, as described in AdministratorsGuide.