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.

Introduction

The XML persistence map makes correspondance between fields found in SQL tables and LSC Bean attributes. It should be be placed in the /etc/lsc/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

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

• 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 retrieves 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 need the identifier to 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 need the identifier to 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	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>

Tip

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

Important

The #xxx# field must be in lowercase.

Writing data with SQL queries

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

Tip

For each type, you can provide more thant one SQL query, each one will be exectuted by LSC.

Insert query

<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 query

<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 query

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

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 attribute to be mapped into;

• 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>

Finalize configuration

LSC uses IBatis to perform SQL/Bean mapping. So, once the XML persistence map has been created, this has to be declared in IBatis through the /etc/lsc/sql-map-config.xml.

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>

Note

You need to configure lsc.xml to use these SQL requests, see source database service or destination database service documentation.