XML persistence map

To use LSC synchronisation with databases you have to define how LSC will retrieve data from the source database or how it will write them to destination 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 LSC Bean attributes. It should be be placed in the etc/sql-map-config.d directory.

This file is divided in different sections:

• Definition of SQL fields and LSC Bean attributes mapping
• Definition of SQL requests to retrieve data
• Definition of SQL requests to write 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: 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, three 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>destination>pivotAttrs in the 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>destination>pivotAttrs in the configuration file
• one for retrieving data corresponding to an identifier for clean phase, you have to use the identifier match the value specified by the property lsc>tasks>TASKNAME>destination>pivotAttrs in the 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 as uid
  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 = #uid#
</select>

And for the clean phase it should be:

<select id="getInetOrgPersonClean" resultClass="java.util.HashMap"
        parameterClass="java.util.Map">
  SELECT id as uid
  FROM users
  WHERE id = #uid#
</select>

You need SQL queries to write data if you configure a destination database service.

You can write 3 types of queries:

• Insert queries, to add data
• Update queries, to modify existing data
• Delete queries, to delete data

        <insert id="insertInetOrgPerson" parameterClass="java.util.Map">
                INSERT INTO inetorgperson
                        ( uid, sn, givenname, cn, mail, address, telephonenumber)
                        VALUES ( #uid#, #sn#, #givenname#, #cn#, #mail#, #address#, #telephonenumber# )
        </insert>

        <update id="updateInetOrgPerson" parameterClass="java.util.Map">
                UPDATE inetorgperson
                        SET uid = #uid#, sn = #sn# , givenname = #givenname#, cn = #cn#, address = #address#, telephonenumber = #telephonenumber#
                        WHERE mail = #mail#
        </update>

        <delete id="deleteInetOrgPerson" parameterClass="java.util.Map">
                DELETE FROM inetorgperson
                        WHERE mail = #mail#
        </delete>

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