slapd-sql.5 25.2 KB
Newer Older
1
.TH SLAPD-SQL 5 "RELEASEDATE" "OpenLDAP LDVERSION"
2
3
4
5
6
.\" $OpenLDAP$
.SH NAME
slapd-sql \- SQL backend to slapd
.SH SYNOPSIS
ETCDIR/slapd.conf
7
.SH DESCRIPTION
Kurt Zeilenga's avatar
Kurt Zeilenga committed
8
The primary purpose of this
9
.BR slapd (8)
Kurt Zeilenga's avatar
Kurt Zeilenga committed
10
backend is to PRESENT information stored in some RDBMS as an LDAP subtree
11
12
13
14
without any programming (some SQL and maybe stored procedures can't be
considered programming, anyway ;).
.LP
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
17
18
19
20
information in LDAP (to authenticate users, make email lookups etc.).
Or you want to synchronize or distribute information between different
sites/applications that use RDBMSes and/or LDAP.
Or whatever else...
.LP
21
22
It is NOT designed as a general-purpose backend that uses RDBMS instead
of BerkeleyDB (as the standard BDB backend does), though it can be
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
used as such with several limitations.
You can take a look at
.B http://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.
.LP
The idea (detailed below) is to use some metainformation to translate
LDAP queries to SQL queries, leaving relational schema untouched, so
that old applications can continue using it without any
modifications.
This allows SQL and LDAP applications to inter-operate without
replication, and exchange data as needed.
.LP
The SQL backend is designed to be tunable to virtually any relational
schema without having to change source (through that metainformation
mentioned).
Also, it uses ODBC to connect to RDBMSes, and is highly configurable
for SQL 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.
43
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
44
This backend is \fIexperimental\fP.
45
46
47
.SH CONFIGURATION
These
.B slapd.conf
Kurt Zeilenga's avatar
Kurt Zeilenga committed
48
49
options apply to the SQL backend database, which means that 
they must follow a "database sql" line and come before any
50
subsequent "backend" or "database" lines.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
51
52
Other database options not specific to this backend are described 
in the
53
54
.BR slapd.conf (5)
manual page.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
55
56
.SH DATA SOURCE CONFIGURATION

57
58
59
.TP
.B dbname <datasource name>
The name of the ODBC datasource to use.
60
.LP
61
.B dbhost <hostname>
62
.br
63
.B dbpasswd <password>
Kurt Zeilenga's avatar
Kurt Zeilenga committed
64
65
.br
.B dbuser <username>
66
.RS
Kurt Zeilenga's avatar
Kurt Zeilenga committed
67
68
69
70
71
The three above options are generally unneeded, because this information
is taken from the datasource specified by the
.B dbname
directive.
They allow to override datasource settings.
72
Also, several RDBMS' drivers tend to require explicit passing of user/password,
73
74
75
even if those are given in datasource (Note:
.B dbhost
is currently ignored).
76
.RE
Kurt Zeilenga's avatar
Kurt Zeilenga committed
77
78
79
.SH SCOPING CONFIGURATION
These options specify SQL query templates for scoping searches.

80
81
.TP
.B subtree_cond <SQL expression>
82
Specifies a where-clause template used to form a subtree search condition
Kurt Zeilenga's avatar
Kurt Zeilenga committed
83
(dn="(.+,)?<dn>$").
84
It may differ from one SQL dialect to another (see samples).
Kurt Zeilenga's avatar
Kurt Zeilenga committed
85
86
87
88
89
90
By default, it is constructed based on the knowledge about
how to normalize DN values (e.g.
\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%',?)"\fP);
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.

91
92
93
.TP
.B children_cond <SQL expression>
Specifies a where-clause template used to form a children search condition
Kurt Zeilenga's avatar
Kurt Zeilenga committed
94
(dn=".+,<dn>$").
95
It may differ from one SQL dialect to another (see samples).
Kurt Zeilenga's avatar
Kurt Zeilenga committed
96
97
98
99
100
101
102
By default, it is constructed based on the knowledge about
how to normalize DN values (e.g.
\fB"<upper_func>(ldap_entries.dn) LIKE CONCAT('%,',?)"\fP);
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.

.TP
103
.B use_subtree_shortcut { YES | no }
Kurt Zeilenga's avatar
Kurt Zeilenga committed
104
105
106
107
Do not use the subtree condition when the searchBase is the database
suffix, and the scope is subtree; rather collect all entries.

.RE
Kurt Zeilenga's avatar
Kurt Zeilenga committed
108
.SH STATEMENT CONFIGURATION
Kurt Zeilenga's avatar
Kurt Zeilenga committed
109
110
111
112
113
114
115
These options specify SQL query templates for loading schema mapping
metainformation, adding and deleting entries to ldap_entries, etc.
All these and subtree_cond should have the given default values.
For the current value it is recommended to look at the sources,
or in the log output when slapd starts with "-d 5" or greater.
Note that the parameter number and order must not be changed.

116
117
.TP
.B oc_query <SQL expression>
Kurt Zeilenga's avatar
Kurt Zeilenga committed
118
119
The query that is used to collect the objectClass mapping data
from table \fIldap_oc_mappings\fP; see "METAINFORMATION USED" for details.
120
The default is
Kurt Zeilenga's avatar
Kurt Zeilenga committed
121
122
123
\fB"SELECT id, name, keytbl, keycol, create_proc, delete_proc, expect_return
FROM ldap_oc_mappings"\fP.

124
125
.TP
.B at_query <SQL expression>
Kurt Zeilenga's avatar
Kurt Zeilenga committed
126
127
The query that is used to collect the attributeType mapping data
from table \fIldap_attr_mappings\fP; see "METAINFORMATION USED" for details.
128
The default is
Kurt Zeilenga's avatar
Kurt Zeilenga committed
129
130
131
\fB"SELECT name, sel_expr, from_tbls, join_where, add_proc, delete_proc,
param_order, expect_return FROM ldap_attr_mappings WHERE oc_map_id=?"\fP.

132
.TP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
133
134
135
.B id_query <SQL expression>
The query that is used to map a DN to an entry
in table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
136
The default is
Kurt Zeilenga's avatar
Kurt Zeilenga committed
137
138
139
140
141
142
143
\fB"SELECT id,keyval,oc_map_id,dn FROM ldap_entries WHERE <DN match expr>"\fP,
where \fB<DN match expr>\fP is constructed based on the knowledge about
how to normalize DN values (e.g. \fB"dn=?"\fP if no means to uppercase
strings are available; typically, \fB"<upper_func>(dn)=?"\fP is used);
see \fBupper_func\fP, \fBupper_needs_cast\fP, \fBconcat_pattern\fP
and \fBstrcast_func\fP in "HELPER CONFIGURATION" for details.

144
.TP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
145
146
147
.B insentry_stmt <SQL expression>
The statement that is used to insert a new entry
in table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
148
The default is
Kurt Zeilenga's avatar
Kurt Zeilenga committed
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
\fB"INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES
(?, ?, ?, ?)"\fP.

.TP
.B delentry_stmt <SQL expression>
The statement that is used to delete an existing entry
from table \fIldap_entries\fP; see "METAINFORMATION USED" for details.
The default is
\fB"DELETE FROM ldap_entries WHERE id=?"\fP.

.TP
.B delobjclasses_stmt <SQL expression>
The statement that is used to delete an existing entry's ID
from table \fIldap_objclasses\fP; see "METAINFORMATION USED" for details.
The default is
Kurt Zeilenga's avatar
Kurt Zeilenga committed
164
\fB"DELETE FROM ldap_entry_objclasses WHERE entry_id=?"\fP.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

.RE
.SH HELPER CONFIGURATION
These statements are used to modify the default behavior of the backend
according to issues of the dialect of the RDBMS.
The first options essentially refer to string and DN normalization
when building filters.
LDAP normalization is more than upper- (or lower-)casing everything;
however, as a reasonable trade-off, for case-sensitive RDBMSes the backend
can be instructed to uppercase strings and DNs by providing
the \fBupper_func\fP directive.
Some RDBMSes, to use functions on arbitrary data types, e.g. string
constants, requires a cast, which is triggered
by the \fBupper_needs_cast\fP directive.
If required, a string cast function can be provided as well,
by using the \fBstrcast_func\fP directive.
Finally, a custom string concatenation pattern may be required;
it is provided by the \fBconcat_pattern\fP directive.
183
184
185
186

.TP
.B upper_func <SQL function name>
Specifies the name of a function that converts a given value to uppercase.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
187
188
189
190
191
This is used for case insensitive matching when the RDBMS is case sensitive.
It may differ from one SQL dialect to another (e.g. \fBUCASE\fP, \fBUPPER\fP
or whatever; see samples).  By default, none is used, i.e. strings are not
uppercased, so matches may be case sensitive.

192
.TP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
193
.B upper_needs_cast { NO | yes }
194
195
196
197
Set this directive to 
.B yes
if 
.B upper_func
Kurt Zeilenga's avatar
Kurt Zeilenga committed
198
199
needs an explicit cast when applied to literal strings.
A cast in the form
200
.B CAST (<arg> AS VARCHAR(<max DN length>))
201
202
is used, where
.B <max DN length>
Kurt Zeilenga's avatar
Kurt Zeilenga committed
203
204
205
206
207
208
209
is builtin in back-sql; see macro
.B BACKSQL_MAX_DN_LEN
(currently 255; note that slapd's builtin limit, in macro
.BR SLAP_LDAPDN_MAXLEN ,
is set to 8192).
This is \fIexperimental\fP and may change in future releases.

210
.TP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
211
212
213
214
215
216
217
.B strcast_func <SQL function name>
Specifies the name of a function that converts a given value to a string
for appropriate ordering.  This is used in "SELECT DISTINCT" statements
for strongly typed RDBMSes with little implicit casting (like PostgreSQL),
when a literal string is specified.
This is \fIexperimental\fP and may change in future releases.

Kurt Zeilenga's avatar
Kurt Zeilenga committed
218
.TP
219
220
221
.B concat_pattern <pattern>
This statement defines the
.B pattern 
Kurt Zeilenga's avatar
Kurt Zeilenga committed
222
that is used to concatenate strings.  The
223
224
225
226
.B pattern
MUST contain two question marks, '?', that will be replaced 
by the two strings that must be concatenated.  The default value is
.BR "CONCAT(?,?)";
227
a form that is known to be highly portable (IBM db2, PostgreSQL) is 
228
229
.BR "?||?",
but an explicit cast may be required when operating on literal strings:
230
231
.BR "CAST(?||? AS VARCHAR(<length>))".
On some RDBMSes (IBM db2, MSSQL) the form
232
.B "?+?"
Kurt Zeilenga's avatar
Kurt Zeilenga committed
233
is known to work as well.
234
235
Carefully check the documentation of your RDBMS or stay with the examples
for supported ones.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
236
237
This is \fIexperimental\fP and may change in future releases.

Kurt Zeilenga's avatar
Kurt Zeilenga committed
238
239
240
241
242
243
244
245
246
247
248
.TP
.B aliasing_keyword <string>
Define the aliasing keyword.  Some RDBMSes use the word "\fIAS\fP"
(the default), others don't use any.

.TP
.B aliasing_quote <string>
Define the quoting char of the aliasing keyword.  Some RDBMSes 
don't require any (the default), others may require single 
or double quotes.

249
.TP
250
.B has_ldapinfo_dn_ru { NO | yes }
Kurt Zeilenga's avatar
Kurt Zeilenga committed
251
252
Explicitly inform the backend whether the dn_ru column
(DN in reverse uppercased form) is present in table \fIldap_entries\fP.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
253
Overrides automatic check (this is required, for instance,
Kurt Zeilenga's avatar
Kurt Zeilenga committed
254
255
by PostgreSQL/unixODBC).
This is \fIexperimental\fP and may change in future releases.
256
257

.TP
258
.B fail_if_no_mapping { NO | yes }
259
260
When set to
.B yes
Kurt Zeilenga's avatar
Kurt Zeilenga committed
261
262
263
it forces \fIattribute\fP write operations to fail if no appropriate
mapping between LDAP attributes and SQL data is available.
The default behavior is to ignore those changes that cannot be mapped.
264
265
266
267
268
269
270
271
It has no impact on objectClass mapping, i.e. if the
.I structuralObjectClass
of an entry cannot be mapped to SQL by looking up its name 
in ldap_oc_mappings, an 
.I add
operation will fail regardless of the
.B fail_if_no_mapping
switch; see section "METAINFORMATION USED" for details.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
272
This is \fIexperimental\fP and may change in future releases.
273

274
275
276
277
278
279
280
281
282
283
.TP
.B allow_orphans { NO | yes }
When set to 
.B yes
orphaned entries (i.e. without the parent entry in the database)
can be added.  This option should be used with care, possibly 
in conjunction with some special rule on the RDBMS side that
dynamically creates the missing parent.

.TP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
284
.B baseObject [ <filename> ]
285
286
287
Instructs the database to create and manage an in-memory baseObject
entry instead of looking for one in the RDBMS.
If the (optional) 
Kurt Zeilenga's avatar
Kurt Zeilenga committed
288
289
.B <filename>
argument is given, the entry is read from that file in
290
.BR LDIF (5)
Kurt Zeilenga's avatar
Kurt Zeilenga committed
291
292
293
format; otherwise, an entry with objectClass \fBextensibleObject\fP
is created based on the contents of the RDN of the \fIbaseObject\fP.
This is particularly useful when \fIldap_entries\fP
294
295
296
297
298
information is stored in a view rather than in a table, and 
.B union
is not supported for views, so that the view can only specify
one rule to compute the entry structure for one objectClass.
This topic is discussed further in section "METAINFORMATION USED".
Kurt Zeilenga's avatar
Kurt Zeilenga committed
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
This is \fIexperimental\fP and may change in future releases.

.TP
.B create_needs_select { NO | yes }
Instructs the database whether or not entry creation
in table \fIldap_entries\fP needs a subsequent select to collect 
the automatically assigned ID, instead of being returned 
by a stored procedure.

.LP
.B fetch_attrs <attrlist>
.br
.B fetch_all_attrs { NO | yes }
.RS
The first statement allows to provide a list of attributes that
must always be fetched in addition to those requested by any specific
operation, because they are required for the proper usage of the
backend.  For instance, all attributes used in ACLs should be listed
here.  The second statement is a shortcut to require all attributes 
to be always loaded.  Note that the dynamically generated attributes,
e.g. \fIhasSubordinates\fP, \fIentryDN\fP and other implementation
dependent attributes are \fBNOT\fP generated at this point, for
consistency with the rest of slapd.  This may change in the future.
.RE

Kurt Zeilenga's avatar
Kurt Zeilenga committed
324
325
326
327
328
329
330
.TP
.B check_schema { YES | no }
Instructs the database to check schema adherence of entries after
modifications, and structural objectClass chain when entries are built.
By default it is set to 
.BR yes .

Kurt Zeilenga's avatar
Kurt Zeilenga committed
331
332
333
334
335
336
337
.TP
.B sqllayer <name> [...]
Loads the layer \fB<name>\fP onto a stack of helpers that are used 
to map DNs from LDAP to SQL representation and vice-versa.
Subsequent args are passed to the layer configuration routine.
This is \fIhighly experimental\fP and should be used with extreme care.
The API of the layers is not frozen yet, so it is unpublished.
338

Pierangelo Masarati's avatar
Pierangelo Masarati committed
339
.SH METAINFORMATION USED
340
341
342
.LP
Almost everything mentioned later is illustrated in examples located
in the
343
.B servers/slapd/back-sql/rdbms_depend/
344
directory in the OpenLDAP source tree, and contains scripts for
345
346
generating sample database for Oracle, MS SQL Server, mySQL and more
(including PostgreSQL and IBM db2).
347
.LP
348
The first thing that one must arrange is what set of LDAP
349
350
object classes can present your RDBMS information.
.LP
351
The easiest way is to create an objectClass for each entity you had in
352
353
354
355
356
357
358
359
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.
.LP
360
Also you might want your object classes to conform to some of the standard
361
362
363
schemas like inetOrgPerson etc.
.LP
Nevertheless, when you think it out, we must define a way to translate
364
LDAP operation requests to (a series of) SQL queries.
365
366
367
368
369
370
371
Let us deal with the SEARCH operation.
.LP
Example:
Let's suppose that we store information about persons working in our 
organization in two tables:
.LP
.nf
372
373
374
375
376
  PERSONS              PHONES
  ----------           -------------
  id integer           id integer
  first_name varchar   pers_id integer references persons(id)
  last_name varchar    phone
377
378
379
380
381
382
383
384
385
386
387
388
389
390
  middle_name varchar
  ...
.fi
.LP
(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:
.LP
.nf
  person
  -------
  MUST cn
391
  MAY telephoneNumber $ firstName $ lastName
392
393
394
395
396
397
398
  ...
.fi
.LP
To fetch all values for cn attribute given person ID, we construct the
query:
.LP
.nf
Pierangelo Masarati's avatar
Pierangelo Masarati committed
399
  SELECT CONCAT(persons.first_name,' ',persons.last_name)
400
401
402
403
404
405
406
      AS cn FROM persons WHERE persons.id=?
.fi
.LP
for telephoneNumber we can use:
.LP
.nf
  SELECT phones.phone AS telephoneNumber FROM persons,phones
Kurt Zeilenga's avatar
Kurt Zeilenga committed
407
      WHERE persons.id=phones.pers_id AND persons.id=?
408
409
410
411
412
413
414
.fi
.LP
If we wanted to service LDAP requests with filters like
(telephoneNumber=123*), we would construct something like:
.LP
.nf
  SELECT ... FROM persons,phones
Kurt Zeilenga's avatar
Kurt Zeilenga committed
415
416
417
      WHERE persons.id=phones.pers_id
          AND persons.id=?
          AND phones.phone like '%1%2%3%'
418
419
.fi
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
420
421
422
423
(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).
424
So, if we had information about what tables contain values for each
425
attribute, how to join these tables and arrange these values, we could
426
427
428
try to automatically generate such statements, and translate search
filters to SQL WHERE clauses.
.LP
429
430
To store such information, we add three more tables to our schema
and fill it with data (see samples):
431
432
433
434
435
436
437
438
439
440
441
.LP
.nf
  ldap_oc_mappings (some columns are not listed for clarity)
  ---------------
  id=1
  name="person"
  keytbl="persons"
  keycol="id"
.fi
.LP
This table defines a mapping between objectclass (its name held in the
442
"name" column), and a table that holds the primary key for corresponding
443
444
445
entities.
For instance, in our example, the person entity, which we are trying
to present as "person" objectclass, resides in two tables (persons and
446
447
phones), and is identified by the persons.id column (that we will call
the primary key for this entity).
448
449
450
451
452
453
454
Keytbl and keycol thus contain "persons" (name of the table), and "id"
(name of the column).
.LP
.nf
  ldap_attr_mappings (some columns are not listed for clarity)
  -----------
  id=1
455
  oc_map_id=1
456
  name="cn"
Pierangelo Masarati's avatar
Pierangelo Masarati committed
457
  sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
  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"
.fi
.LP
This table defines mappings between LDAP attributes and SQL queries
that load their values.
Note that, unlike LDAP schema, these are not
.B attribute types
473
474
- the attribute "cn" for "person" objectclass can
have its values in different tables than "cn" for some other objectclass,
475
476
477
478
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.
.LP
479
Now we cut the SQL query that loads values for a given attribute into 3 parts.
480
481
482
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.
483
484
485
486
487
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
488
489
490
491
492
left NULL (as for cn attribute above).
.LP
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
493
of objects that correspond to a given search filter (or at least part of
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
it).
See below for examples.
.LP
.nf
  ldap_entries
  ------------
  id=1
  dn=<dn you choose>
  oc_map_id=...
  parent=<parent record id>
  keyval=<value of primary key>
.fi
.LP
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
513
514
for primary key, and keyval stores value of it, thus defining the exact
tuple corresponding to the LDAP entry with this DN.
515
516
.LP
Note that such design (see exact SQL table creation query) implies one
517
518
519
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
520
521
522
523
keys - he may want to write a patch, and submit it to OpenLDAP ITS,
then I'll include it.
.LP
Also, several people complained that they don't really need very
524
525
526
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
527
528
529
530
like this (by Robin Elfrink):
.LP
.nf
  CREATE VIEW ldap_entries (id, dn, oc_map_id, parent, keyval)
531
532
      AS
          SELECT 0, UPPER('o=MyCompany,c=NL'),
Kurt Zeilenga's avatar
Kurt Zeilenga committed
533
534
              3, 0, 'baseObject' FROM unixusers WHERE userid='root'
      UNION
535
          SELECT (1000000000+userid),
Kurt Zeilenga's avatar
Kurt Zeilenga committed
536
537
538
              UPPER(CONCAT(CONCAT('cn=',gecos),',o=MyCompany,c=NL')),
              1, 0, userid FROM unixusers
      UNION
539
          SELECT (2000000000+groupnummer),
Kurt Zeilenga's avatar
Kurt Zeilenga committed
540
541
              UPPER(CONCAT(CONCAT('cn=',groupnaam),',o=MyCompany,c=NL')),
              2, 0, groupnummer FROM groups;
542
.fi
543
544
545
546
547
548
549
550
551
552

.LP
If your RDBMS does not support
.B unions
in views, only one objectClass can be mapped in
.BR ldap_entries ,
and the baseObject cannot be created; in this case, see the 
.B baseObject
directive for a possible workaround.

553
.LP
Pierangelo Masarati's avatar
Pierangelo Masarati committed
554
.SH Typical SQL backend operation
555
556
557
558
559
560
Having metainformation loaded, the SQL backend uses these tables to
determine a set of primary keys of candidates (depending on search
scope and filter).
It tries to do it for each objectclass registered in ldap_objclasses.
.LP
Example:
561
for our query with filter (telephoneNumber=123*) we would get the following 
562
563
564
query generated (which loads candidate IDs)
.LP
.nf
Pierangelo Masarati's avatar
Pierangelo Masarati committed
565
  SELECT ldap_entries.id,persons.id, 'person' AS objectClass,
566
567
568
569
570
571
         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
Kurt Zeilenga's avatar
Kurt Zeilenga committed
572
     AND (phones.phone LIKE '%1%2%3%')
573
574
575
576
.fi
.LP
(for ONELEVEL search)
or "... AND dn=?" (for BASE search)
Pierangelo Masarati's avatar
Pierangelo Masarati committed
577
or "... AND dn LIKE '%?'" (for SUBTREE)
578
.LP
579
Then, for each candidate, we load the requested attributes using
580
581
582
583
584
585
586
587
per-attribute queries like
.LP
.nf
  SELECT phones.phone AS telephoneNumber
    FROM persons,phones
   WHERE persons.id=? AND phones.pers_id=persons.id
.fi
.LP
588
Then, we use test_filter() from the frontend API to test the entry for a full
589
590
LDAP search filter match (since we cannot effectively make sense of
SYNTAX of corresponding LDAP schema attribute, we translate the filter
591
592
into the most relaxed SQL condition to filter candidates), and send it to
the user.
593
.LP
594
ADD, DELETE, MODIFY and MODRDN operations are also performed on per-attribute
595
596
metainformation (add_proc etc.).
In those fields one can specify an SQL statement or stored procedure
597
call which can add, or delete given values of a given attribute, using
Kurt Zeilenga's avatar
Kurt Zeilenga committed
598
599
the given entry keyval (see examples -- mostly PostgreSQL, ORACLE and MSSQL 
- since as of this writing there are no stored procs in MySQL).
600
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
601
We just add more columns to ldap_oc_mappings and ldap_attr_mappings, holding
602
statements to execute (like create_proc, add_proc, del_proc etc.), and
603
flags governing the order of parameters passed to those statements.
604
605
Please see samples to find out what are the parameters passed, and other
information on this matter - they are self-explanatory for those familiar
Kurt Zeilenga's avatar
Kurt Zeilenga committed
606
with the concepts expressed above.
607
.LP
608
.SH Common techniques (referrals, multiclassing etc.)
609
First of all, let's remember that among other major differences to the
610
complete LDAP data model, the concept above does not directly support
611
such things as multiple objectclasses per entry, and referrals.
612
Fortunately, they are easy to adopt in this scheme.
Kurt Zeilenga's avatar
Kurt Zeilenga committed
613
614
The SQL backend suggests one more table being added to the schema:
ldap_entry_objectclasses(entry_id,oc_name).
615
.LP
616
The first contains any number of objectclass names that corresponding
617
618
entries will be found by, in addition to that mentioned in
mapping.
619
620
621
The SQL backend automatically adds attribute mapping for the "objectclass"
attribute to each objectclass mapping that loads values from this table.
So, you may, for instance, have a mapping for inetOrgPerson, and use it
622
623
for queries for "person" objectclass...
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
624
625
626
627
628
629
630
631
632
633
634
635
636
Referrals used to be implemented in a loose manner by adding an extra
table that allowed any entry to host a "ref" attribute, along with
a "referral" extra objectClass in table ldap_entry_objclasses.
In the current implementation, referrals are treated like any other
user-defined schema, since "referral" is a structural objectclass.
The suggested practice is to define a "referral" entry in ldap_oc_mappings,
holding a naming attribute, e.g. "ou" or "cn", a "ref" attribute,
containing the url; in case multiple referrals per entry are needed,
a separate table for urls can be created, where urls are mapped
to the respective entries.
The use of the naming attribute usually requires to add 
an "extensibleObject" value to ldap_entry_objclasses.

637
.LP
638
639
640
641
642
.SH Caveats
As previously stated, this backend should not be considered
a replacement of other data storage backends, but rather a gateway
to existing RDBMS storages that need to be published in LDAP form.
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
643
The \fBhasSubordintes\fP operational attribute is honored by back-sql
644
in search results and in compare operations; it is partially honored
Kurt Zeilenga's avatar
Kurt Zeilenga committed
645
also in filtering.  Owing to design limitations, a (brain-dead?) filter
646
647
of the form
\fB(!(hasSubordinates=TRUE))\fP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
648
649
will give no results instead of returning all the leaf entries, because
it actually expands into \fB... AND NOT (1=1)\fP.
650
651
652
653
If you need to find all the leaf entries, please use
\fB(hasSubordinates=FALSE)\fP
instead.
.LP
Kurt Zeilenga's avatar
Kurt Zeilenga committed
654
655
656
657
658
659
660
A directoryString value of the form "__First___Last_"
(where underscores should be replaced by spaces) corresponds
to its prettified counterpart "First_Last"; this is not currently
honored by back-sql if non-prettified data is written via RDBMS;
when non-prettified data is written thru back-sql, the prettified 
values are actually used instead.
.LP
661
662
663
664
665
666
.SH PROXY CACHE OVERLAY
The proxy cache overlay 
allows caching of LDAP search requests (queries) in a local database.
See 
.BR slapo-pcache (5)
for details.
667
668
.SH EXAMPLES
There are example SQL modules in the slapd/back-sql/rdbms_depend/
669
directory in the OpenLDAP source tree.
670
671
672
673
674
675
676
677
.SH ACCESS CONTROL
The 
.B sql
backend honors access control semantics as indicated in
.BR slapd.access (5)
(including the 
.B disclose
access privilege when enabled at compile time).
Pierangelo Masarati's avatar
Pierangelo Masarati committed
678
.SH FILES
Kurt Zeilenga's avatar
Kurt Zeilenga committed
679

680
.TP
Pierangelo Masarati's avatar
Pierangelo Masarati committed
681
ETCDIR/slapd.conf
682
default slapd configuration file
683
684
685
.SH SEE ALSO
.BR slapd.conf (5),
.BR slapd (8).