Unix/Linux Go Back    


OpenDarwin 7.2.1 - man page for slapd-sql (opendarwin section 5)

Linux & Unix Commands - Search Man Pages
Man Page or Keyword Search:   man
Select Man Page Set:       apropos Keyword Search (sections above)


SLAPD-SQL(5)									     SLAPD-SQL(5)

NAME
       slapd-sql - SQL backend to slapd

SYNOPSIS
       /etc/openldap/slapd.conf

DESCRIPTION
       The  primary  purpose  of  this	slapd(8) backend is to PRESENT information stored in some
       RDBMS as an LDAP subtree without any programming (some SQL  and	maybe  stored  procedures
       can't be considered programming, anyway ;).

       That  is,  for  example, when you (some ISP) have account information you use in an RDBMS,
       and want to use modern solutions that expect such 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...

       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 used as such with several limitations.
       You can take a look  at	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.

       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.

       The  SQL backend is designed to be tunable to virtually any relational schema without hav-
       ing to change source (through that metainformation mentioned).  Also, it uses ODBC to con-
       nect 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.

       This backend is experimental.

CONFIGURATION
       These  slapd.conf  options apply to the SQL backend database.  That is, they must follow a
       "database sql" line and come before any subsequent "backend" or "database"  lines.   Other
       database options are described in the slapd.conf(5) manual page.

       dbname <datasource name>
	      The name of the ODBC datasource to use.

       dbhost <hostname>
       dbuser <username>
       dbpasswd <password>
	      These  three  options  are  generally unneeded, because this information is already
	      taken from the datasource.  Use them if you need to override  datasource	settings.
	      Also,  several  RDBMS'  drivers  tend to require explicit passing of user/password,
	      even if those are given in datasource (Note: dbhost is currently ignored).

       subtree_cond <SQL expression>
	      Specifies  a  where-clause  template  used  to  form  a  subtree	search	condition
	      (dn=".*<dn>").  It may differ from one SQL dialect to another (see samples).

       children_cond <SQL expression>
	      Specifies  a  where-clause  template  used  to  form  a  children  search condition
	      (dn=".+,<dn>").  It may differ from one SQL dialect to another (see samples).

       oc_query <SQL expression>
	      The  default  is	SELECT	id,  name,  keytbl,  keycol,  create_proc,   delete_proc,
	      expect_return FROM ldap_oc_mappings

       at_query <SQL expression>
	      The default is SELECT name, sel_expr, from_tbls, join_where, add_proc, delete_proc,
	      param_order, expect_return FROM ldap_attr_mappings WHERE oc_map_id=?

       insentry_query <SQL expression>
	      The default is INSERT INTO ldap_entries (dn, oc_map_id, parent, keyval) VALUES  (?,
	      ?, ?, ?)

       delentry_query <SQL expression>
	      The default is DELETE FROM ldap_entries WHERE id=?

	      These  four  options specify SQL query templates for loading schema mapping metain-
	      formation, adding and deleting entries to ldap_entries, etc.  All  these	and  sub-
	      tree_cond should have the given default values.  For the current value it is recom-
	      mended 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.

       upper_func <SQL function name>
	      Specifies the name of a function that converts a given value to uppercase.  This is
	      used for CIS matching when the RDBMS is case sensitive.

       upper_needs_cast { yes | no }
	      Set this directive to yes if upper_func needs an explicit cast when applied to lit-
	      eral  strings.   The  form  cast (<arg> as varchar(<max DN length>)) is used, where
	      <max DN length> is  builtin.   This  is  experimental  and  may  change  in  future
	      releases.

       concat_pattern <pattern>
	      This  statement defines the pattern to be used to concatenate strings.  The pattern
	      MUST contain two question marks, '?', that will be replaced by the two strings that
	      must be concatenated.  The default value is CONCAT(?,?); a form that is known to be
	      highly portable is ?||?, but an explicit cast may be  required  when  operating  on
	      literal strings: cast(?||? as varchar(<length>)).  On some RDBMSes the form ?+?  is
	      known to work.  Carefully check the documentation of your RDBMS or  stay	with  the
	      examples	for  supported	ones.	This  is  experimental	and  may change in future
	      releases.

       strcast_func <SQL function name>
	      Specifies the name of a function that converts a given value to a string for appro-
	      priate  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 experimental and may change in future releases.

       has_ldapinfo_dn_ru { yes | no }
	      Explicitly  inform  the  backend	whether  the  SQL  schema has dn_ru column (dn in
	      reverse uppercased form) or not.	Overrides  automatic  check  (required	by  Post-
	      greSQL/unixODBC).  This is experimental and may change in future releases.

       fail_if_no_mapping { yes | no }
	      When  set  to  yes  it  forces  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 correctly.  This is experimental and may
	      change in future releases.

METAINFORMATION USED
       Almost  everything  mentioned  later  is  illustrated   in   examples   located	 in   the
       servers/slapd/back-sql/rdbms_depend/  directory	in the OpenLDAP source tree, and contains
       scripts for generating sample database for Oracle, MS SQL Server, mySQL and more  (includ-
       ing PostgreSQL and IBM db2).

       The  first thing that one must arrange is what set of LDAP object classes can present your
       RDBMS information.

       The easiest way is to create an objectclass for each entity you	had  in  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,  ser-
       vices  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.

       Also  you  might  want your object classes to conform to some of the standard schemas like
       inetOrgPerson etc.

       Nevertheless, when you think it out, we must define a  way  to  translate  LDAP	operation
       requests to (a series of) SQL queries.  Let us deal with the SEARCH operation.

       Example: Let's suppose that we store information about persons working in our organization
       in two tables:

	 PERSONS	      PHONES
	 ----------	      -------------
	 id integer	      id integer
	 first_name varchar   pers_id integer references persons(id)
	 last_name varchar    phone
	 middle_name varchar
	 ...

       (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  infor-
       mation could look like this:

	 person
	 -------
	 MUST cn
	 MAY telephoneNumber $ firstName $ 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 requests with filters 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
       these  tables and arrange these values, we could try to automatically generate such state-
       ments, and translate search filters to SQL WHERE clauses.

       To store such information, we add three more tables to our schema and fill  it  with  data
       (see samples):

	 ldap_oc_mappings (some columns are not listed for clarity)
	 ---------------
	 id=1
	 name="person"
	 keytbl="persons"
	 keycol="id"

       This table defines a mapping between objectclass (its name held in the "name" column), and
       a table that holds the primary key for corresponding entities.  For instance, in our exam-
       ple, the person entity, which we are trying to present as "person" objectclass, resides in
       two tables (persons and phones), and is identified by the persons.id column (that we  will
       call  the primary key for this entity).	Keytbl and keycol thus contain "persons" (name of
       the table), and "id" (name of the column).

	 ldap_attr_mappings (some columns are not listed for clarity)
	 -----------
	 id=1
	 oc_map_id=1
	 name="cn"
	 sel_expr="CONCAT(persons.first_name,' ',persons.last_name)"
	 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"

       This table defines mappings between LDAP attributes and SQL queries that load  their  val-
       ues.   Note  that,  unlike LDAP schema, these are not attribute types - the attribute "cn"
       for "person" objectclass can have its values in different tables than "cn" for some  other
       objectclass,  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.

       Now we cut the SQL query that loads values for a given attribute into 3 parts.  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.
       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 contain-
       ing 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 left NULL (as for cn
       attribute above).

       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 of objects that correspond to a given search  filter  (or
       at least part of it).  See below for examples.

	 ldap_entries
	 ------------
	 id=1
	 dn=<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 pri-
       mary 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  for primary key, and keyval stores value of it, thus defining the exact tuple cor-
       responding to the LDAP entry with this DN.

       Note that such design (see exact SQL table creation  query)  implies  one  important  con-
       straint	-  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  keys - he may want to write a patch, and submit it to OpenLDAP ITS, then I'll include
       it.

       Also, several people complained that they don't really need  very  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 like this (by Robin Elfrink):

	 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;

Typical SQL backend operation
       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.

       Example: for our query with filter (telephoneNumber=123*) we would get the 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 the requested attributes using per-attribute queries
       like

	 SELECT phones.phone AS telephoneNumber
	   FROM persons,phones
	  WHERE persons.id=? AND phones.pers_id=persons.id

       Then, we use test_filter() from the frontend API to test the entry for a full LDAP  search
       filter  match  (since  we  cannot  effectively  make sense of SYNTAX of corresponding LDAP
       schema attribute, we translate the filter into the most relaxed SQL  condition  to  filter
       candidates), and send it to the user.

       ADD,  DELETE,  MODIFY  operations  are  also  performed	on  per-attribute metainformation
       (add_proc etc.).  In those fields one can specify an SQL  statement  or	stored	procedure
       call  which  can  add,  or delete given values of a given attribute, using the given entry
       keyval (see examples -- mostly ORACLE and MSSQL	-  since  there're  no	stored	procs  in
       mySQL).

       We  just  add more columns to oc_mappings and attr_mappings, holding statements to execute
       (like create_proc, add_proc, del_proc etc.), and flags governing the order  of  parameters
       passed  to  those  statements.	Please	see  samples  to find out what are the parameters
       passed, and other information on this matter - they are self-explanatory for those  famil-
       iar with concept expressed above.

common techniques (referrals, multiclassing etc.)
       First  of all, let's remember that among other major differences to the complete LDAP data
       model, the concept above does not directly support such things as  multiple  objectclasses
       per  entry,  and  referrals.  Fortunately, they are easy to adopt in this scheme.  The SQL
       backend	suggests  two  more  tables  being  added  to  the  schema  -  ldap_entry_object-
       classes(entry_id,oc_name), and ldap_referrals(entry_id,url).

       The  first  contains  any  number  of objectclass names that corresponding entries will be
       found by, in addition to that mentioned in mapping.  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 for queries for "person" objectclass...

       The  second table contains any number of referrals associated with a given entry.  The SQL
       backend automatically adds attribute mapping for "ref" attribute to each objectclass  map-
       ping  that  loads  values  from this table.  So, if you add objectclass "referral" to this
       entry, and make one or more tuples in ldap_referrals for this entry (they will be seen  as
       values  of  "ref"  attribute),  you will have slapd return a referral, as described in the
       Administrators Guide.

EXAMPLES
       There are example SQL modules in the slapd/back-sql/rdbms_depend/ directory in the  OpenL-
       DAP source tree.

FILES
       /etc/openldap/slapd.conf
	      default slapd configuration file

SEE ALSO
       slapd.conf(5), slapd(8).

OpenLDAP 2.1.X				   RELEASEDATE				     SLAPD-SQL(5)
Unix & Linux Commands & Man Pages : ©2000 - 2018 Unix and Linux Forums


All times are GMT -4. The time now is 08:56 AM.