Introduction

SQL itself should be a standard language. Since different implementors result in different behavior and supported features, unixODBC has been used in back-sql to provide a neutral interface to the RDBMSes. Unfortunately, this was not enough; as a consequence some work might be required to have a working back-sql.

Supported RDBMSes

How to Use It

• set up the RDBMS appropriately;
• set up the mapping meta-data appropriately;
• set up unixODBC (or any other library providing ODBC services) appropriately.

Useful Links

• Setting up LDAP with back-sql: uses Postgres and MySQL
• OpenLDAP-POSTGRESQL HOWTO: uses Postgres

Meta-Data

• ldap_oc_mappings: how rows of some tables are mapped to entries with a given structuralObjectClass;
• ldap_attr_mappings: how attributeTypes of an objectClass are resolved from RDBMS data;
• ldap_entries: what's the DN of an entry, and how the entry relates to its objectClass mapping and to its parent DN;
• ldap_entry_objclasses: what auxiliary objectClasses an entry has;
• ldap_referrals: what entries must be treated as referrals.

NOTE: the objectClass names used in ldap_oc_mappings and the attributeType names used in ldap_attr_mappings MUST have been already loaded in slapd's schema by loading the appropriate schema files. Example: if your meta-data defines an objectClass inetOrgPerson with an attributeType sn, you need to load core.schema for sn and inetorgperson.schema for inetOrgPerson.

NOTE: the ldap_referrals table is no longer used in OpenLDAP 2.3; referrals should be defined as user data, i.e. the URI should be stored somewhere in the database and a referral objectClass should be defined in the ldap_oc_mappings table. See the example data for pgsql as a guideline.

Test Data

• testdb_create.sql to create the sample tables;
• testdb_data.sql to fill the tables with sample data;
• backsql_create.sql to create the tables for the sample meta-data;
• testdb_metadata.sql to fill the tables with sample meta-data.

In $SRC/servers/slapd/back-sql/rdbms_depend/$SUPPORTED_RDBMS there is also a sample slapd.conf file set up for use with that RDBMS (see for instance the one for Postgres).

Multiple objectClass definitions are allowed for an entry; however, only one can be "structural" in a back-sql sense. This means that each entry must refer to only one row of ldap_oc_mappings. This parallels with the fact that each LDAP entry has only one structuralObjectClass, which will correspond to that objectClass. Auxiliary objectClasses can be added on a per-entry basis by means of the ldap_entry_objclasses.

Multiple attributeType definitions are allowed for an entry; that is, multiple ldap_attr_mappings rows can refer to the same ldap_oc_mappings row with the same name; the resulting attribute values are honored for multivalued attributes in search filters, in search results, in compare AVAs. However, only rules according to the first instance of that attributeType are followed in add, modify and delete operations. This limitation, under certain circumstances, may be removed in the future.

New Features

Filter equality matches on attributeType with DN syntax can be honored by back-sql if those attributes are defined appropriately. Take the steps:

• use the attribute name in the sel_expr field; e.g. documentAuthor.dn instead of ldap_entries.dn;
• alias the ldap_entries table with the attribute name in the from_tbls field; e.g. ldap_entries AS documentAuthor;
• use the attribute name in the join_where field as required, when there is need to reference the table it comes from, instead of ldap_entries; e.g. documentAuthor.keyval=persons.id AND documentAuthor.oc_map_id=1 AND ... instead of ldap_entries.keyval=persons.id AND ldap_entries.oc_map_id=1 AND ...;

Testing

By default back-sql tests are disabled; they must be explicitly enabled, because they require some user interaction in setting up the RDBMS and feeding it with the appropriate data. To run the tests:

• set up the desired RDBMS;
• define the environment variable SLAPD_USE_SQL=<rdbms>, where <rdbms> is the type of RDBMS in use. This is necessary because the slapd.conf may require specific tweakings. Currently, only mysql, ibmdb2 and pgsql are supported;
• populate the RDBMS with the data available in servers/slapd/back-sql/rdbms_depend/<rdbms>/;
• to enable write tests, define the environment variable SLAPD_USE_SQLWRITE=yes; only ibmdb2 and pgsql currently support them. Note that, in some cases, you may need to manually restore the contents of the database using the files in servers/slapd/back-sql/rdbms_depend/<rdbms>/;
• to run all tests, execute

          cd tests
          make sql
  

• to run a specific test only, execute

          cd tests
          ./run sql-test<name>
  

  where <name> is the name of the test, or its leading portion, if unique. Tests are usually named using three digits and a descriptive string; to run the test, the digits suffice.

Please report bugs using the ITS.
ando@sys-net.it

IBM db2/udb

MySQL

PostgreSQL

Formerly supported RDBMSes (not tested during development, but known to work at some time):

 mssql
 oracle
 timesten

Help in supporting other RDBMS is welcome
Need back-sql support for free?
New Item
New Item
New Item