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.
46 options apply to the SQL backend database.
47 That is, they must follow a "database sql" line and come before any
48 subsequent "backend" or "database" lines.
49 Other database options are described in the
53 .B dbname <datasource name>
54 The name of the ODBC datasource to use.
60 .B dbpasswd <password>
62 These three options are generally unneeded, because this information is already
63 taken from the datasource.
64 Use them if you need to override datasource settings.
65 Also, several RDBMS' drivers tend to require explicit passing of user/password,
66 even if those are given in datasource.
69 .B subtree_cond <SQL expression>
70 Specifies a where-clause template used to form a subtree search condition.
71 It may differ from one SQL dialect to another (see samples).
73 .B oc_query <SQL expression>
75 .B "SELECT id, name, keytbl, keycol, create_proc, delete_proc, expect_return FROM ldap_oc_mappings"
77 .B at_query <SQL expression>
79 .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=?"
81 .B insentry_query <SQL expression>
83 .B "INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES (?, ?, ?, ?)"
85 .B delentry_query <SQL expression>
87 .B "DELETE FROM ldap_entries WHERE id=?"
89 These four options specify SQL query templates for loading schema mapping
91 adding and deleting entries to ldap_entries, etc.
92 All these and subtree_cond should have the given default values.
93 For the current value it is recommended to look at the sources,
94 or in the log output when slapd starts with "-d 5" or greater.
96 .B upper_func <SQL function name>
97 Specifies the name of a function that converts a given value to uppercase.
98 This is used for CIS matching when the RDBMS is case sensitive.
100 .B strcast_func <SQL function name>
101 Specifies the name of a function that converts a given value to a string
102 for appropriate ordering. This is used when selecting distinct data.
104 .B has_ldapinfo_dn_ru { yes | no }
105 Explicitly inform the backend whether the SQL schema has dn_ru or not.
106 Overrides automatic check (required by PostgreSQL).
108 .SH METAINFORMATION USED
110 Almost everything mentioned later is illustrated in examples located
112 .B slapd/back-sql/rdbms_depend/
113 directory in the OpenLDAP source tree, and contains scripts for
114 generating sample database for Oracle, MS SQL Server, mySQL and more
115 (including PostgreSQL and IBM db2).
117 The first thing that one must arrange is what set of LDAP
118 object classes can present your RDBMS information.
120 The easiest way is to create an objectclass for each entity you had in
121 ER-diagram when designing your relational schema.
122 Any relational schema, no matter how normalized it is, was designed
123 after some model of your application's domain (for instance, accounts,
124 services etc. in ISP), and is used in terms of its entities, not just
125 tables of normalized schema.
126 It means that for every attribute of every such instance there is an
127 effective SQL query that loads its values.
129 Also you might want your object classes to conform to some of the standard
130 schemas like inetOrgPerson etc.
132 Nevertheless, when you think it out, we must define a way to translate
133 LDAP operation requests to (a series of) SQL queries.
134 Let us deal with the SEARCH operation.
137 Let's suppose that we store information about persons working in our
138 organization in two tables:
142 ---------- -------------
143 id integer id integer
144 first_name varchar pers_id integer references persons(id)
145 last_name varchar phone
150 (PHONES contains telephone numbers associated with persons).
151 A person can have several numbers, then PHONES contains several
152 records with corresponding pers_id, or no numbers (and no records in
153 PHONES with such pers_id).
154 An LDAP objectclass to present such information could look like this:
160 MAY telephoneNumber $ firstName $ lastName
164 To fetch all values for cn attribute given person ID, we construct the
168 SELECT CONCAT(persons.first_name,' ',persons.last_name)
169 AS cn FROM persons WHERE persons.id=?
172 for telephoneNumber we can use:
175 SELECT phones.phone AS telephoneNumber FROM persons,phones
176 WHERE persons.id=phones.pers_id AND persons.id=?
179 If we wanted to service LDAP requests with filters like
180 (telephoneNumber=123*), we would construct something like:
183 SELECT ... FROM persons,phones
184 WHERE persons.id=phones.pers_id
186 AND phones.phone like '123%'
189 So, if we had information about what tables contain values for each
190 attribute, how to join these tables and arrange these values, we could
191 try to automatically generate such statements, and translate search
192 filters to SQL WHERE clauses.
194 To store such information, we add three more tables to our schema
195 and fill it with data (see samples):
198 ldap_oc_mappings (some columns are not listed for clarity)
206 This table defines a mapping between objectclass (its name held in the
207 "name" column), and a table that holds the primary key for corresponding
209 For instance, in our example, the person entity, which we are trying
210 to present as "person" objectclass, resides in two tables (persons and
211 phones), and is identified by the persons.id column (that we will call
212 the primary key for this entity).
213 Keytbl and keycol thus contain "persons" (name of the table), and "id"
214 (name of the column).
217 ldap_attr_mappings (some columns are not listed for clarity)
222 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
228 name="telephoneNumber"
229 sel_expr="phones.phone"
230 from_tbls="persons,phones"
231 join_where="phones.pers_id=persons.id"
234 This table defines mappings between LDAP attributes and SQL queries
235 that load their values.
236 Note that, unlike LDAP schema, these are not
238 - the attribute "cn" for "person" objectclass can
239 have its values in different tables than "cn" for some other objectclass,
240 so attribute mappings depend on objectclass mappings (unlike attribute
241 types in LDAP schema, which are indifferent to objectclasses).
242 Thus, we have oc_map_id column with link to oc_mappings table.
244 Now we cut the SQL query that loads values for a given attribute into 3 parts.
245 First goes into sel_expr column - this is the expression we had
246 between SELECT and FROM keywords, which defines WHAT to load.
247 Next is table list - text between FROM and WHERE keywords.
248 It may contain aliases for convenience (see examples).
249 The last is part of the where clause, which (if it exists at all) expresses the
250 condition for joining the table containing values with the table
251 containing the primary key (foreign key equality and such).
252 If values are in the same table as the primary key, then this column is
253 left NULL (as for cn attribute above).
255 Having this information in parts, we are able to not only construct
256 queries that load attribute values by id of entry (for this we could
257 store SQL query as a whole), but to construct queries that load id's
258 of objects that correspond to a given search filter (or at least part of
260 See below for examples.
268 parent=<parent record id>
269 keyval=<value of primary key>
272 This table defines mappings between DNs of entries in your LDAP tree,
273 and values of primary keys for corresponding relational data.
274 It has recursive structure (parent column references id column of the
275 same table), which allows you to add any tree structure(s) to your
276 flat relational data.
277 Having id of objectclass mapping, we can determine table and column
278 for primary key, and keyval stores value of it, thus defining the exact
279 tuple corresponding to the LDAP entry with this DN.
281 Note that such design (see exact SQL table creation query) implies one
282 important constraint - the key must be an integer.
283 But all that I know about well-designed schemas makes me think that it's
284 not very narrow ;) If anyone needs support for different types for
285 keys - he may want to write a patch, and submit it to OpenLDAP ITS,
286 then I'll include it.
288 Also, several people complained that they don't really need very
289 structured trees, and they don't want to update one more table every
290 time they add or delete an instance in the relational schema.
291 Those people can use a view instead of a real table for ldap_entries, something
292 like this (by Robin Elfrink):
295 CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
296 AS SELECT (1000000000+userid),
297 UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
298 1, 0, userid FROM unixusers UNION
299 SELECT (2000000000+groupnummer),
300 UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
301 2, 0, groupnummer FROM groups;
304 .SH Typical SQL backend operation
305 Having metainformation loaded, the SQL backend uses these tables to
306 determine a set of primary keys of candidates (depending on search
308 It tries to do it for each objectclass registered in ldap_objclasses.
311 for our query with filter (telephoneNumber=123*) we would get the following
312 query generated (which loads candidate IDs)
315 SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
316 ldap_entries.dn AS dn
317 FROM ldap_entries,persons,phones
318 WHERE persons.id=ldap_entries.keyval
319 AND ldap_entries.objclass=?
320 AND ldap_entries.parent=?
321 AND phones.pers_id=persons.id
322 AND (phones.phone LIKE '123%')
325 (for ONELEVEL search)
326 or "... AND dn=?" (for BASE search)
327 or "... AND dn LIKE '%?'" (for SUBTREE)
329 Then, for each candidate, we load the requested attributes using
330 per-attribute queries like
333 SELECT phones.phone AS telephoneNumber
335 WHERE persons.id=? AND phones.pers_id=persons.id
338 Then, we use test_filter() from the frontend API to test the entry for a full
339 LDAP search filter match (since we cannot effectively make sense of
340 SYNTAX of corresponding LDAP schema attribute, we translate the filter
341 into the most relaxed SQL condition to filter candidates), and send it to
344 ADD, DELETE, MODIFY operations are also performed on per-attribute
345 metainformation (add_proc etc.).
346 In those fields one can specify an SQL statement or stored procedure
347 call which can add, or delete given values of a given attribute, using
348 the given entry keyval (see examples -- mostly ORACLE and MSSQL - since
349 there're no stored procs in mySQL).
351 We just add more columns to oc_mappings and attr_mappings, holding
352 statements to execute (like create_proc, add_proc, del_proc etc.), and
353 flags governing the order of parameters passed to those statements.
354 Please see samples to find out what are the parameters passed, and other
355 information on this matter - they are self-explanatory for those familiar
356 with concept expressed above.
358 .SH common techniques (referrals, multiclassing etc.)
359 First of all, lets remember that among other major differences to the
360 complete LDAP data model, the concept above does not directly support
361 such things as multiple objectclasses per entry, and referrals.
362 Fortunately, they are easy to adopt in this scheme.
363 The SQL backend suggests two more tables being added to the schema -
364 ldap_entry_objectclasses(entry_id,oc_name), and
365 ldap_referrals(entry_id,url).
367 The first contains any number of objectclass names that corresponding
368 entries will be found by, in addition to that mentioned in
370 The SQL backend automatically adds attribute mapping for the "objectclass"
371 attribute to each objectclass mapping that loads values from this table.
372 So, you may, for instance, have a mapping for inetOrgPerson, and use it
373 for queries for "person" objectclass...
375 The second table contains any number of referrals associated with a given entry.
376 The SQL backend automatically adds attribute mapping for "ref" attribute
377 to each objectclass mapping that loads values from this table.
378 So, if you add objectclass "referral" to this entry, and make one or
379 more tuples in ldap_referrals for this entry (they will be seen as
380 values of "ref" attribute), you will have slapd return a referral, as
381 described in the Administrators Guide.
384 There are example SQL modules in the slapd/back-sql/rdbms_depend/
385 directory in the OpenLDAP source tree.
389 default slapd configuration file
projects / openldap / blob