====== 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.

===== Introduction =====

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.

===== Mapping definition =====

==== Retrieving data with SQL queries ====

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:

<code>
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
</code>

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

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

Then, the second one should be:

<code>
<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>
</code>

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#).


==== Attributes mapping ====

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:

<code>
<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>
</code>

===== Finalize configuration =====

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:

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

You may have something like the following:

<code>
<?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>
</code>

Finally, you need to configure //lsc.properties// to use these SQL requests:
<code>
lsc.tasks.MySyncTask.srcService.requestNameForList = getInetOrgPersonList
lsc.tasks.MySyncTask.srcService.requestNameForObject = getInetOrgPerson
</code>