XML persistence map

To use LSC synchronisation from database to LDAP directory, you have to define how LSC will retrieve data from the source database. This will be done by specifying Ibatis persistence map in XML.

The XML persistence map makes correspondance between fields found in SQL tables and LDAP attributes. It should be be placed in the etc/sql-map-config.d directory.

This file is divided in two different sections:

• Definition of SQL fields and LDAP attributes mapping;
• Definition of SQL requests to retrieve data.

SQL requests are defined in a second part of this file. Each SQL request is defined through the select XML tag. This tag could contain four parameters:

• id specifies the identifier of this request, called later by LSC after configuration in lsc.properties
• parameterClass
• parameterClass: if a resultMap attribute is specified, then you have to also specify this parameter that will contains:
  • java.util.HashMap for a query that retrieves a identifier list;
  • java.util.map for a query that retrieve data corresponding to an identifer;
• resultClass: this will contain the Java object to receive data: this should be java.util.HashMap
• resultMap: instead of resultClass, you could specify this parameter that specifies the ID of a resultMap you want to use to match SQL field, typically the one used to map SQL field and LDAP attributes. See Attributes mapping below.

With select, two basic requests can be declared:

• one for retrieving all identifiers, it is recommended that the identifier match the value specified by the property lsc.tasks.TASKNAME.dstService.pivotAttrs in the lsc.properties configuration file;
• one for retrieving data corresponding to an identifier, you have to use the identifier match the value specified by the property lsc.tasks.TASKNAME.dstService.pivotAttrs in the lsc.properties configuration file;

For example, suppose a SQL table named “users” like the following:

ID    LASTNAME  FIRSTNAME  NAME         EMAIL                  PASSWORD
----  --------  ---------  -----------  ---------------------  --------
jdoe  Doe       John       John Doe     jdoe@lsc-project.org   jdoe123
test  Test      sample     sample Test  stest@lsc-project.org  stestabc

Then, the first SQL request to retrieve all identifiers should be:

<select id="getInetOrgPersonList" resultClass="java.util.HashMap">
  SELECT id
  FROM users
</select>

Then, the second one should be:

<select id="getInetOrgPerson" resultClass="java.util.HashMap"
        parameterClass="java.util.Map">
  SELECT id as uid,
         lastname as sn,
         firstname as givenName,
         name as cn,
         email as mail,
         password as userPassword
  FROM users
  WHERE id = #id#
</select>

Note that the #id# is the SQL field returned by the first SQL query. So, if the first SQL query selects two field (SELECT name, email), then you can use these two field to retrieve a user (#name# and #email#).

This step is optional. If you don't want to use the syntax SELECT field AS attribute …, use this. Otherwise, skip this section.

The mapping is declared through the resultMap XML tag. This tag takes two parameters:

• id is defined by the LDAP object class suffixed by the “Result” string, the first character must be in upper case;
• class contains the JAVA object class, the first character must be in upper case.

Mapping is done by the result XML tag found in resultMap. This tag takes two parameters:

• property defines the LDAP attribute name;
• column defines the SQL field name.

The mapping definition should exactly fit fields returned by the SQL query that returns data.

For example, consider the inetOrgPerson LDAP object class, then the associated resultMap definition could be something like the following:

<resultMap id="InetOrgPersonResult" class="InetOrgPerson">
  <result property="sn" column="lastname"/>
  <result property="cn" column="name"/>
  <result property="givenName" column="firstname"/>
  <result property="userPassword" column="password"/>
  <result property="uid" column="id"/>
  <result property="mail" column="email"/>
</resultMap>

LSC use Ibatis to perform SQL/LDAP mapping. So, once the XML persistence map has been created, this has to be declared in Ibatis through the sql-map-config.xml file found in /etc too.

No need to understand, just add a line like the following:

<sqlMap url="file://${lsc.config}/sql-map-config.d/InetOrgPerson.xml"/>

You may have something like the following:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
<sqlMapConfig>

  <properties resource="database.properties"/>
  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="${driver}"/>
      <property name="JDBC.ConnectionURL" value="${url}"/>
      <property name="JDBC.Username" value="${username}"/>
      <property name="JDBC.Password" value="${password}"/>
      <property name="Pool.MaximumActiveConnections" value="15"/>
      <property name="Pool.MaximumIdleConnections" value="15"/>
      <property name="Pool.MaximumWait" value="1000"/>
    </dataSource>
  </transactionManager>

  <sqlMap url="file://${lsc.config}/sql-map-config.d/InetOrgPerson.xml"/>

</sqlMapConfig>

Finally, you need to configure lsc.properties to use these SQL requests:

lsc.tasks.MySyncTask.srcService.requestNameForList = getInetOrgPersonList
lsc.tasks.MySyncTask.srcService.requestNameForObject = getInetOrgPerson