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 will be created automatically during automatic generation in the etc/sql-map-config.d directory for structural LDAP object classes.

This file is divided in three different sections:

• Namespace and associated LSC flat object definition;
• Definition of SQL fields and LDAP attributes mapping;
• Definition of SQL requests to retrieve data.

This file is case sensitive, so be extremely careful!

The namespace string must be the LDAP object class name suffixed by “JDBCService” string . Its first character must be in upper case, and also first character of all words that part of the string. Namespace have to be unique in the org.lsc.persistence.xml package.

For example, consider the inetOrgPerson LDAP object class, then the associated namespace should be “InetOrgPersonJDBCService”:

<sqlMap namespace="InetOrgPersonJDBCService">

You will probably define alias for the flat object LSC want to use. It will avoid to specify the full JAVA class name.

This is define in the typeAlias XML tag. This tag contains two parameters:

• alias defines the LDAP object class;
• type defines the full name of the associated JAVA flat object.

For example, consider the inetOrgPerson LDAP object class, then the associated typeAlias definition should be:

<typeAlias alias="InetOrgPerson"
  type="org.lsc.objects.flat.fInetOrgPerson" />

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 exaclty fit field returned by the SQL query that returns data. There is an other use case which is defined at the end of this document.

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>

SQL requests are defined into a third part of this file. Each SQL request is define through the select XML tag. This tag could contains four parameters:

• id specifies the identifier of this request, called later by Ibatis through the JDBCService defined by the namespace (you have to also check that this JAVA class uses it);
• parameterClass
• resultMap defines the result you want to use to match SQL field, typically the one used to map SQL field and LDAP attributes;
• 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: instead of resultMap, you could specify this parameter which will contain the JAVA object to receive data; be careful that SQL field name should exactly match LDAP attribute name (case sensitive too);

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;
• on 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" resultMap="InetOrgPersonResult"
    parameterClass="java.util.Map">
  SELECT id, lastname, firstname, name, email, password
  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#). Be careful that field have to be defined in the resultMap used by the select.

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>

During the configuration, you may not know exactly which SQL fields to select. So, in that particular case, you have to map all attributes (perhaps 100!) into the resultMap XML tag, each property value must match the corresponding column value.

With this configuration, you can then create a SQL query that retrieve data for a particular identifier (for the example, we use a fictive extendedPerson auxiliary LDAP object class):

<select id="getExtendedPerson" resultClass="ExtendedPerson"
    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>

Don't forget that the field names are case sensitive !