1 .TH SLAPD-SQL 5 "30 April 2002" "OpenLDAP LDVERSION"
4 slapd-sql \- SQL backend to slapd
8 The primary purpose of this backend (8) to
10 is to PRESENT information stored in some RDBMS as an LDAP subtree
11 without any programming (some SQL and maybe stored procedures can't be
12 considered programming, anyway ;).
14 That is, for example, when you (some ISP) have account information you
15 use in RDBMS, and want to use modern solutions that expect such
16 information in LDAP (to authenticate users, make email lookups etc.).
17 Or you want to synchronize or distribute information between different
18 sites/applications that use RDBMSes and/or LDAP.
21 It is NOT designed as general-purpose backend that uses RDBMS instead
22 of BerkeleyDB (as the standard LDBM backend does), though it can be
23 used as such with several limitations.
24 You can take a look at
25 .B http://www.openldap.org/faq/index.cgi?file=378
26 (OpenLDAP FAQ-O-Matic/General LDAP FAQ/Directories vs. conventional
27 databases) to find out more on this point.
29 The idea (detailed below) is to use some metainformation to translate
30 LDAP queries to SQL queries, leaving relational schema untouched, so
31 that old applications can continue using it without any
33 This allows SQL and LDAP applications to inter-operate without
34 replication, and exchange data as needed.
36 The SQL backend is designed to be tunable to virtually any relational
37 schema without having to change source (through that metainformation
39 Also, it uses ODBC to connect to RDBMSes, and is highly configurable
40 for SQL dialects RDBMSes may use, so it may be used for integration
41 and distribution of data on different RDBMSes, OSes, hosts etc., in
42 other words, in highly heterogeneous environment.
43 .SH METAINFORMATION USED
45 Almost everything mentioned later is illustrated in examples located
47 .B slapd/back-sql/rdbms_depend/
48 directory in the OpenLDAP source tree, and contains scripts for
49 generating sample database for Oracle,MS SQL Server and mySQL.
51 First thing that one must arrange for himself is what set of LDAP
52 object classes can present your RDBMS information.
54 The easiest way is to create an objectclass for each entity you had in
55 ER-diagram when designing your relational schema.
56 Any relational schema, no matter how normalized it is, was designed
57 after some model of your application's domain (for instance, accounts,
58 services etc. in ISP), and is used in terms of its entities, not just
59 tables of normalized schema.
60 It means that for every attribute of every such instance there is an
61 effective SQL query that loads its values.
63 Also you might want your object classes to conform to some of standard
64 schemas like inetOrgPerson etc.
66 Nevertheless, when you think it out, we must define a way to translate
67 LDAP operation requests to (series of) SQL queries.
68 Let us deal with the SEARCH operation.
71 Let's suppose that we store information about persons working in our
72 organization in two tables:
76 ---------- -------------
78 first_name varchar pers_id integer references persons(id)
79 last_name varchar phone
84 (PHONES contains telephone numbers associated with persons).
85 A person can have several numbers, then PHONES contains several
86 records with corresponding pers_id, or no numbers (and no records in
87 PHONES with such pers_id).
88 An LDAP objectclass to present such information could look like this:
100 To fetch all values for cn attribute given person ID, we construct the
104 SELECT CONCAT(persons.first_name,' ',persons.last_name)
105 AS cn FROM persons WHERE persons.id=?
108 for telephoneNumber we can use:
111 SELECT phones.phone AS telephoneNumber FROM persons,phones
112 WHERE persons.id=phones.pers.id AND persons.id=?
115 If we wanted to service LDAP requests with filters like
116 (telephoneNumber=123*), we would construct something like:
119 SELECT ... FROM persons,phones
120 WHERE persons.id=phones.pers.id
122 AND phones.phone like '123%'
125 So, if we had information about what tables contain values for each
126 attribute, how to join this tables and arrange these values, we could
127 try to automatically generate such statements, and translate search
128 filters to SQL WHERE clauses.
130 To store such information, we add three more tables to our schema, so
131 that and fill it with data (see samples):
134 ldap_oc_mappings (some columns are not listed for clarity)
142 This table defines a mapping between objectclass (its name held in the
143 "name" column), and a table that holds primary key for corresponding
145 For instance, in our example, the person entity, which we are trying
146 to present as "person" objectclass, resides in two tables (persons and
147 phones), and is identified by persons.id column (that we will call
148 primary key for this entity).
149 Keytbl and keycol thus contain "persons" (name of the table), and "id"
150 (name of the column).
153 ldap_attr_mappings (some columns are not listed for clarity)
158 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
164 name="telephoneNumber"
165 sel_expr="phones.phone"
166 from_tbls="persons,phones"
167 join_where="phones.pers_id=persons.id"
170 This table defines mappings between LDAP attributes and SQL queries
171 that load their values.
172 Note that, unlike LDAP schema, these are not
174 - attribute "cn" for "person" objectclass can well
175 have its values in different table than "cn" for other objectclass,
176 so attribute mappings depend on objectclass mappings (unlike attribute
177 types in LDAP schema, which are indifferent to objectclasses).
178 Thus, we have oc_map_id column with link to oc_mappings table.
180 Now we cut the SQL query that loads values for given attribute into 3 parts.
181 First goes into sel_expr column - this is the expression we had
182 between SELECT and FROM keywords, which defines WHAT to load.
183 Next is table list - text between FROM and WHERE keywords.
184 It may contain aliases for convenience (see exapmles).
185 The last is part of where clause, which (if exists at all) express the
186 condition for joining the table containing values wich table
187 containing primary key (foreign key equality and such).
188 If values are in the same table with primary key, then this column is
189 left NULL (as for cn attribute above).
191 Having this information in parts, we are able to not only construct
192 queries that load attribute values by id of entry (for this we could
193 store SQL query as a whole), but to construct queries that load id's
194 of objects that correspond to given search filter (or at least part of
196 See below for examples.
204 parent=<parent record id>
205 keyval=<value of primary key>
208 This table defines mappings between DNs of entries in your LDAP tree,
209 and values of primary keys for corresponding relational data.
210 It has recursive structure (parent column references id column of the
211 same table), which allows you to add any tree structure(s) to your
212 flat relational data.
213 Having id of objectclass mapping, we can determine table and column
214 for primary key, and keyval stores value of it, thus defining exact
215 tuple corresponding to LDAP entry with this DN.
217 Note that such design (see exact SQL table creation query) implies one
218 important constraint - the key must be integer.
219 But all that I know about well-designed schemas makes me think that it
220 s not very narrow ;) If anyone needs support for different types for
221 keys - he may want to write a patch, and submit it to OpenLDAP ITS,
222 then I'll include it.
224 Also, several people complained that they don't really need very
225 structured tree, and they don't want to update one more table every
226 time they add or delete instance in relational schema.
227 Those can use a view instead of real table for ldap_entries, something
228 like this (by Robin Elfrink):
231 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
232 AS SELECT (1000000000+userid),
233 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
234 1, 0, userid FROM unixusers UNION
235 SELECT (2000000000+groupnummer),
236 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
237 2, 0, groupnummer FROM groups;
240 .SH Typical SQL backend operation
241 Having metainformation loaded, the SQL backend uses these tables to
242 determine a set of primary keys of candidates (depending on search
244 It tries to do it for each objectclass registered in ldap_objclasses.
247 for our query with filter (telephoneNumber=123*) we would get following
248 query generated (which loads candidate IDs)
251 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
252 ldap_entries.dn AS dn
253 FROM ldap_entries,persons,phones
254 WHERE persons.id=ldap_entries.keyval
255 AND ldap_entries.objclass=?
256 AND ldap_entries.parent=?
257 AND phones.pers_id=persons.id
258 AND (phones.phone LIKE '123%')
261 (for ONELEVEL search)
262 or "... AND dn=?" (for BASE search)
263 or "... AND dn LIKE '%?'" (for SUBTREE)
265 Then, for each candidate, we load attributes requested using
266 per-attribute queries like
269 SELECT phones.phone AS telephoneNumber
271 WHERE persons.id=? AND phones.pers_id=persons.id
274 Then, we use test_filter() from frontend API to test entry for full
275 LDAP search filter match (since we cannot effectively make sense of
276 SYNTAX of corresponding LDAP schema attribute, we translate the filter
277 into most relaxed SQL condition to filter candidates), and send it to
280 ADD, DELETE, MODIFY operations also performed on per-attribute
281 metainformation (add_proc etc.).
282 In those fields one can specify an SQL statement or stored procedure
283 call which can add, or delete given value of given attribute, using
284 given entry keyval (see examples -- mostly ORACLE and MSSQL - since
285 there're no stored procs in mySQL).
287 We just add more columns to oc_mappings and attr_mappings, holding
288 statements to execute (like create_proc, add_proc, del_proc etc.), and
289 flags governing order of parameters passed to those statements.
290 Please see samples to find out what are the parameters passed, and other
291 information on this matter - they are self-explanatory for those familiar
292 with concept expressed above.
294 .SH common techniques (referrals, multiclassing etc.)
295 First of all, lets remember that among other major differences to
296 complete LDAP data model, the concept above does not directly support
297 such things as multiple objectclasses for entry, and referrals.
298 Fortunately, they are easy to adopt in this scheme.
299 The SQL backend suggests two more tables being added to schema -
300 ldap_entry_objectclasses(entry_id,oc_name), and
301 ldap_referrals(entry_id,url).
303 First contains any number of objectclass names that corresponding
304 entries will be found by, in addition to that mentioned in
306 The SQL backend automatically adds attribute mapping for "objectclass"
307 attribute to each objectclass mapping, that loads values from this table.
308 So, you may, for instance, have mapping for inetOrgPerson, and use it
309 for queries for "person" objectclass...
311 Second table contains any number of referrals associated with given entry.
312 The SQL backend automatically adds attribute mapping for "ref" attribute
313 to each objectclass mapping, that loads values from this table.
314 So, if you add objectclass "referral" to this entry, and make one or
315 more tuples in ldap_referrals for this entry (they will be seen as
316 values of "ref" attribute), you will have slapd return referral, as
317 described in Administrators Guide.
320 There are example SQL modules in the slapd/back-sql/rdbms_depend/
321 direcetory in the OpenLDAP source tree.
projects / openldap / blob