1 .TH SLAPD-SQL 5 "RELEASEDATE" "OpenLDAP LDVERSION"
4 slapd-sql \- SQL backend to slapd
8 The primary purpose of this
10 backend 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 an 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 a general-purpose backend that uses RDBMS instead
22 of BerkeleyDB (as the standard BDB 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.
44 This backend is experimental.
48 options apply to the SQL backend database.
49 That is, they must follow a "database sql" line and come before any
50 subsequent "backend" or "database" lines.
51 Other database options are described in the
55 .B dbname <datasource name>
56 The name of the ODBC datasource to use.
62 .B dbpasswd <password>
64 These three options are generally unneeded, because this information is already
65 taken from the datasource.
66 Use them if you need to override datasource settings.
67 Also, several RDBMS' drivers tend to require explicit passing of user/password,
68 even if those are given in datasource (Note:
70 is currently ignored).
73 .B subtree_cond <SQL expression>
74 Specifies a where-clause template used to form a subtree search condition
76 It may differ from one SQL dialect to another (see samples).
78 .B children_cond <SQL expression>
79 Specifies a where-clause template used to form a children search condition
81 It may differ from one SQL dialect to another (see samples).
83 .B oc_query <SQL expression>
85 .B "SELECT id, name, keytbl, keycol, create_proc, delete_proc, expect_return FROM ldap_oc_mappings"
87 .B at_query <SQL expression>
89 .B "SELECT name, sel_expr, from_tbls, join_where, add_proc, delete_proc, param_order, expect_return FROM ldap_attr_mappings WHERE oc_map_id=?"
91 .B insentry_query <SQL expression>
93 .B "INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES (?, ?, ?, ?)"
95 .B delentry_query <SQL expression>
97 .B "DELETE FROM ldap_entries WHERE id=?"
99 These four options specify SQL query templates for loading schema mapping
100 metainformation, adding and deleting entries to ldap_entries, etc.
101 All these and subtree_cond should have the given default values.
102 For the current value it is recommended to look at the sources,
103 or in the log output when slapd starts with "-d 5" or greater.
104 Note that the parameter number and order must not be changed.
106 .B upper_func <SQL function name>
107 Specifies the name of a function that converts a given value to uppercase.
108 This is used for CIS matching when the RDBMS is case sensitive.
110 .B upper_needs_cast { yes | no }
111 Set this directive to
115 needs an explicit cast when applied to literal strings. The form
116 .B CAST (<arg> AS VARCHAR(<max DN length>))
122 and may change in future releases.
124 .B concat_pattern <pattern>
125 This statement defines the
127 to be used to concatenate strings. The
129 MUST contain two question marks, '?', that will be replaced
130 by the two strings that must be concatenated. The default value is
132 a form that is known to be highly portable (IBM db2, PostgreSQL) is
134 but an explicit cast may be required when operating on literal strings:
135 .BR "CAST(?||? AS VARCHAR(<length>))".
136 On some RDBMSes (IBM db2, MSSQL) the form
139 Carefully check the documentation of your RDBMS or stay with the examples
143 and may change in future releases.
145 .B strcast_func <SQL function name>
146 Specifies the name of a function that converts a given value to a string
147 for appropriate ordering. This is used in "SELECT DISTINCT" statements
148 for strongly typed RDBMSes with little implicit casting (like PostgreSQL),
149 when a literal string is specified.
152 and may change in future releases.
154 .B has_ldapinfo_dn_ru { yes | no }
155 Explicitly inform the backend whether the SQL schema has dn_ru column
156 (dn in reverse uppercased form) or not.
157 Overrides automatic check (required by PostgreSQL/unixODBC).
160 and may change in future releases.
163 .B fail_if_no_mapping { yes | no }
168 write operations to fail if no appropriate mapping between LDAP attributes
169 and SQL data is available.
170 The default behavior is to ignore those changes that cannot be mapped
172 It has no impact on objectClass mapping, i.e. if the
173 .I structuralObjectClass
174 of an entry cannot be mapped to SQL by looking up its name
175 in ldap_oc_mappings, an
177 operation will fail regardless of the
178 .B fail_if_no_mapping
179 switch; see section "METAINFORMATION USED" for details.
182 and may change in future releases.
184 .SH METAINFORMATION USED
186 Almost everything mentioned later is illustrated in examples located
188 .B servers/slapd/back-sql/rdbms_depend/
189 directory in the OpenLDAP source tree, and contains scripts for
190 generating sample database for Oracle, MS SQL Server, mySQL and more
191 (including PostgreSQL and IBM db2).
193 The first thing that one must arrange is what set of LDAP
194 object classes can present your RDBMS information.
196 The easiest way is to create an objectClass for each entity you had in
197 ER-diagram when designing your relational schema.
198 Any relational schema, no matter how normalized it is, was designed
199 after some model of your application's domain (for instance, accounts,
200 services etc. in ISP), and is used in terms of its entities, not just
201 tables of normalized schema.
202 It means that for every attribute of every such instance there is an
203 effective SQL query that loads its values.
205 Also you might want your object classes to conform to some of the standard
206 schemas like inetOrgPerson etc.
208 Nevertheless, when you think it out, we must define a way to translate
209 LDAP operation requests to (a series of) SQL queries.
210 Let us deal with the SEARCH operation.
213 Let's suppose that we store information about persons working in our
214 organization in two tables:
218 ---------- -------------
219 id integer id integer
220 first_name varchar pers_id integer references persons(id)
221 last_name varchar phone
226 (PHONES contains telephone numbers associated with persons).
227 A person can have several numbers, then PHONES contains several
228 records with corresponding pers_id, or no numbers (and no records in
229 PHONES with such pers_id).
230 An LDAP objectclass to present such information could look like this:
236 MAY telephoneNumber $ firstName $ lastName
240 To fetch all values for cn attribute given person ID, we construct the
244 SELECT CONCAT(persons.first_name,' ',persons.last_name)
245 AS cn FROM persons WHERE persons.id=?
248 for telephoneNumber we can use:
251 SELECT phones.phone AS telephoneNumber FROM persons,phones
252 WHERE persons.id=phones.pers_id AND persons.id=?
255 If we wanted to service LDAP requests with filters like
256 (telephoneNumber=123*), we would construct something like:
259 SELECT ... FROM persons,phones
260 WHERE persons.id=phones.pers_id
262 AND phones.phone like '123%'
265 So, if we had information about what tables contain values for each
266 attribute, how to join these tables and arrange these values, we could
267 try to automatically generate such statements, and translate search
268 filters to SQL WHERE clauses.
270 To store such information, we add three more tables to our schema
271 and fill it with data (see samples):
274 ldap_oc_mappings (some columns are not listed for clarity)
282 This table defines a mapping between objectclass (its name held in the
283 "name" column), and a table that holds the primary key for corresponding
285 For instance, in our example, the person entity, which we are trying
286 to present as "person" objectclass, resides in two tables (persons and
287 phones), and is identified by the persons.id column (that we will call
288 the primary key for this entity).
289 Keytbl and keycol thus contain "persons" (name of the table), and "id"
290 (name of the column).
293 ldap_attr_mappings (some columns are not listed for clarity)
298 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
304 name="telephoneNumber"
305 sel_expr="phones.phone"
306 from_tbls="persons,phones"
307 join_where="phones.pers_id=persons.id"
310 This table defines mappings between LDAP attributes and SQL queries
311 that load their values.
312 Note that, unlike LDAP schema, these are not
314 - the attribute "cn" for "person" objectclass can
315 have its values in different tables than "cn" for some other objectclass,
316 so attribute mappings depend on objectclass mappings (unlike attribute
317 types in LDAP schema, which are indifferent to objectclasses).
318 Thus, we have oc_map_id column with link to oc_mappings table.
320 Now we cut the SQL query that loads values for a given attribute into 3 parts.
321 First goes into sel_expr column - this is the expression we had
322 between SELECT and FROM keywords, which defines WHAT to load.
323 Next is table list - text between FROM and WHERE keywords.
324 It may contain aliases for convenience (see examples).
325 The last is part of the where clause, which (if it exists at all) expresses the
326 condition for joining the table containing values with the table
327 containing the primary key (foreign key equality and such).
328 If values are in the same table as the primary key, then this column is
329 left NULL (as for cn attribute above).
331 Having this information in parts, we are able to not only construct
332 queries that load attribute values by id of entry (for this we could
333 store SQL query as a whole), but to construct queries that load id's
334 of objects that correspond to a given search filter (or at least part of
336 See below for examples.
344 parent=<parent record id>
345 keyval=<value of primary key>
348 This table defines mappings between DNs of entries in your LDAP tree,
349 and values of primary keys for corresponding relational data.
350 It has recursive structure (parent column references id column of the
351 same table), which allows you to add any tree structure(s) to your
352 flat relational data.
353 Having id of objectclass mapping, we can determine table and column
354 for primary key, and keyval stores value of it, thus defining the exact
355 tuple corresponding to the LDAP entry with this DN.
357 Note that such design (see exact SQL table creation query) implies one
358 important constraint - the key must be an integer.
359 But all that I know about well-designed schemas makes me think that it's
360 not very narrow ;) If anyone needs support for different types for
361 keys - he may want to write a patch, and submit it to OpenLDAP ITS,
362 then I'll include it.
364 Also, several people complained that they don't really need very
365 structured trees, and they don't want to update one more table every
366 time they add or delete an instance in the relational schema.
367 Those people can use a view instead of a real table for ldap_entries, something
368 like this (by Robin Elfrink):
371 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
372 AS SELECT (1000000000+userid),
373 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
374 1, 0, userid FROM unixusers UNION
375 SELECT (2000000000+groupnummer),
376 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
377 2, 0, groupnummer FROM groups;
380 .SH Typical SQL backend operation
381 Having metainformation loaded, the SQL backend uses these tables to
382 determine a set of primary keys of candidates (depending on search
384 It tries to do it for each objectclass registered in ldap_objclasses.
387 for our query with filter (telephoneNumber=123*) we would get the following
388 query generated (which loads candidate IDs)
391 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
392 ldap_entries.dn AS dn
393 FROM ldap_entries,persons,phones
394 WHERE persons.id=ldap_entries.keyval
395 AND ldap_entries.objclass=?
396 AND ldap_entries.parent=?
397 AND phones.pers_id=persons.id
398 AND (phones.phone LIKE '123%')
401 (for ONELEVEL search)
402 or "... AND dn=?" (for BASE search)
403 or "... AND dn LIKE '%?'" (for SUBTREE)
405 Then, for each candidate, we load the requested attributes using
406 per-attribute queries like
409 SELECT phones.phone AS telephoneNumber
411 WHERE persons.id=? AND phones.pers_id=persons.id
414 Then, we use test_filter() from the frontend API to test the entry for a full
415 LDAP search filter match (since we cannot effectively make sense of
416 SYNTAX of corresponding LDAP schema attribute, we translate the filter
417 into the most relaxed SQL condition to filter candidates), and send it to
420 ADD, DELETE, MODIFY and MODRDN operations are also performed on per-attribute
421 metainformation (add_proc etc.).
422 In those fields one can specify an SQL statement or stored procedure
423 call which can add, or delete given values of a given attribute, using
424 the given entry keyval (see examples -- mostly ORACLE and MSSQL - since
425 there're no stored procs in mySQL).
427 We just add more columns to oc_mappings and attr_mappings, holding
428 statements to execute (like create_proc, add_proc, del_proc etc.), and
429 flags governing the order of parameters passed to those statements.
430 Please see samples to find out what are the parameters passed, and other
431 information on this matter - they are self-explanatory for those familiar
432 with concept expressed above.
434 .SH Common techniques (referrals, multiclassing etc.)
435 First of all, let's remember that among other major differences to the
436 complete LDAP data model, the concept above does not directly support
437 such things as multiple objectclasses per entry, and referrals.
438 Fortunately, they are easy to adopt in this scheme.
439 The SQL backend suggests two more tables being added to the schema -
440 ldap_entry_objectclasses(entry_id,oc_name), and
441 ldap_referrals(entry_id,url).
443 The first contains any number of objectclass names that corresponding
444 entries will be found by, in addition to that mentioned in
446 The SQL backend automatically adds attribute mapping for the "objectclass"
447 attribute to each objectclass mapping that loads values from this table.
448 So, you may, for instance, have a mapping for inetOrgPerson, and use it
449 for queries for "person" objectclass...
451 The second table contains any number of referrals associated with a given entry.
452 The SQL backend automatically adds attribute mapping for "ref" attribute
453 to each objectclass mapping that loads values from this table.
454 So, if you add objectclass "referral" to this entry, and make one or
455 more tuples in ldap_referrals for this entry (they will be seen as
456 values of "ref" attribute), you will have slapd return a referral, as
457 described in the Administrators Guide.
460 As previously stated, this backend should not be considered
461 a replacement of other data storage backends, but rather a gateway
462 to existing RDBMS storages that need to be published in LDAP form.
464 The hasSubordintes operational attribute is honored by back-sql
465 in search results and in compare operations; it is partially honored
466 also in filtering. Owing to design limitations, a (braindead) filter
468 \fB(!(hasSubordinates=TRUE))\fP
469 will give no results instead of returning all the leaf entries.
470 If you need to find all the leaf entries, please use
471 \fB(hasSubordinates=FALSE)\fP
475 There are example SQL modules in the slapd/back-sql/rdbms_depend/
476 directory in the OpenLDAP source tree.
480 default slapd configuration file
projects / openldap / blob