Hi,We just have found a solution, so we share it. Below an example of a sync of some groups in LDAP from Oracle DB. We have a *..* association table named *assoc_group_user* between a *user* table and a *group* table.
In lsc.xml, a standard task :
<task>
<name>TaskStructuresGroups</name>
<bean>org.lsc.beans.SimpleBean</bean>
<databaseSourceService>
<name>ora-source-service-groupe-structures</name>
<connection reference="ora-source-conn" />
<requestNameForList>*getGroupStructuresList*</requestNameForList>
<requestNameForObject>*getGroupStructures*</requestNameForObject>
<requestNameForClean>*checkGroupStructuresForClean*</requestNameForClean>
</databaseSourceService>
<ldapDestinationService>
<name>groupe-structures-dst-service</name>
<connection reference="ldap-dst-conn" />
<baseDn>ou=structures,ou=groups,dc=univ-jfc,dc=fr</baseDn>
<pivotAttributes>
<string>cn</string>
</pivotAttributes>
<fetchedAttributes>
<string>cn</string>
<string>description</string>
<string>member</string>
<string>objectClass</string>
</fetchedAttributes>
<getAllFilter><![CDATA[(objectClass=*)]]></getAllFilter>
<getOneFilter><![CDATA[(&(objectClass=*)(cn={cn}))]]></getOneFilter>
</ldapDestinationService>
<propertiesBasedSyncOptions>
<mainIdentifier>"cn=" + srcBean.getDatasetFirstValueById("cn") +
",ou=structures,ou=groups,dc=univ-jfc,dc=fr"</mainIdentifier>
<defaultDelimiter>;</defaultDelimiter>
<defaultPolicy>FORCE</defaultPolicy>
<conditions>
<create>true</create>
<update>true</update>
<delete>false</delete>
<changeId>false</changeId>
</conditions>
</propertiesBasedSyncOptions>
</task>
In sql-map-config.xml, don't forget a reference to the iBatis mapping
defined in the .d directory :
<sqlMapConfig>
[...]
<sqlMap url="file://${lsc.config}/*sql-map-config.d/group-structures.xml*"/>
</sqlMapConfig>
In sql-map-config.d/group-structures.xml, here custom resultMap,
referencing an SQL view and a custom Java class
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd";> <sqlMap namespace="InetOrgPerson"> <!-- columnIndices may are superfluous. But it is untested without --> <resultMap id="*mapGroupStructures*" class="java.util.HashMap"> <result property="cn" column="cn" columnIndex="1" /> <result property="description" column="description" columnIndex="2" /> <result property="member" column="member" columnIndex="3"*typeHandler="fr.jfc.misc.CLOBToStringTypeHandlerCallback"* /> <result property="objectClass" column ="objectClass" columnIndex="4" /> </resultMap> <select id="*getGroupStructures*" resultClass="java.util.HashMap" parameterClass="java.util.Map"*resultMap="mapGroupStructures"*> SELECT * FROM*DB.V_LDAP_GROUPS* WHERE "cn"=#cn# </select> <select id="*checkGroupStructuresForClean*" resultClass="java.util.HashMap" parameterClass="java.util.Map"> SELECT "cn" FROM*DB.V_LDAP_GROUPS* WHERE "cn"=#cn# </select> <select id="*getGroupStructuresList*" resultClass="java.util.HashMap"> SELECT "cn" FROM*DB.V_LDAP_GROUPS* </select> </sqlMap>In view V_LDAP_GROUPS (all *.sql applied to the database, in the reverse order)
CREATE OR REPLACE FORCE VIEW "DB"."V_LDAP_GROUPS" ("cn", "description", "member",
"objectClass") AS
SELECT
g.id as "cn",
g.desc as "description",
*custom_string_agg*(CONCAT(CONCAT('uid=',u.login),',ou=people,dc=univ-jfc,dc=fr')) as
"member",
'groupOfURLs;supannEntite' as "objectClass"
FROM GROUPS g
LEFT JOIN ASSOC_GROUP_USERS a ON g.id = a.groupid
INNER JOIN USERS u ON a.userid = u.id
GROUP BY g.id, d.desc;
The custom function is defined by and returns a CLOB string (not a too
short VARCHAR(4000) ) :
create or replace FUNCTION*custom_string_agg* (p_input VARCHAR2)
RETURN*CLOB*
PARALLEL_ENABLE AGGREGATE USING*t_string_agg*;
The function uses a custom type definition :
create or replace TYPE*T_STRING_AGG* AS OBJECT
(
g_string CLOB,
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
)
Then custom type declaration needs a custom body :
create or replace TYPE BODY*T_STRING_AGG* AS
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER AS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER AS
BEGIN
SELF.g_string := self.g_string || ';' || value;
RETURN ODCIConst.Success;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT CLOB,
flags IN NUMBER)
RETURN NUMBER AS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ';'), ';');
RETURN ODCIConst.Success;
END ODCIAggregateTerminate;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER AS
BEGIN
SELF.g_string := SELF.g_string || ';' || ctx2.g_string;
RETURN ODCIConst.Success;
END ODCIAggregateMerge;
END;
Then the piece a code is the custom Java class
fr.jfc.misc.CLOBToStringTypeHandlerCallback
package fr.jfc.misc;
import java.sql.Clob;
import java.sql.SQLException;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
public class CLOBToStringTypeHandlerCallback implements TypeHandlerCallback {
@Override
public Object getResult(ResultGetter getter) throws SQLException {
Clob value = getter.getClob();
String value2;
if ( value.length() < Integer.MAX_VALUE ) {
value2 = value.getSubString(1, (int) value.length());
} else {
value2 = "CLOB too long ( > Integer.MAX_VALUE )";
}
//throw new SQLException("getResult() ==> " + value2); //
Stupid debug method :)
return value2;
}
@Override
public void setParameter(ParameterSetter setter, Object parameter)
throws SQLException {
throw new SQLException("unimplemented");
}
@Override
public Object valueOf(String s) {
throw new RuntimeException("unimplemented");
}
}
You need this in the classpath to compile this code :
/usr/lib/lsc/ibatis-sqlmap-2.3.4.726.jar
Google find the ibatis-sqlmap-2.3.4.726-source.jar easily for you if you
want to read the TypeHandlerCallback abstract class and attached javadoc.
You need to export this as JAR (Eclipse is your friend) and put it in the classpath of LSC, so could be in : /usr/lib/lsc/
For convinience, the JAR is attached, source included.Then you are : you could have "member" concatenated strings up to 2 gigabytes. This will take stupid amount of ressources on the DB server, but it should be OK for reasonnable groups of persons in a university for example.
custom_agg_function is derived from https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:229614022562 no clue about licence, but seems at least free to share.
CLOBToStringTypeHandlerCallback, and XML config are from us and it is licenced under WTFPL <http://www.wtfpl.net/>.
We may try to make a documentation page about that. @LSC guys : let us know how to capitalize efficiently this :) Thanks all folks, Ludovic Pouzenc On 01/06/2015 17:42, Ludovic Pouzenc wrote:
Hi, I try to use LSC to sync data from Oracle 10i DB to OpenLDAP 2.4.I try to sync groups. I try to not use "automagic" management of member attributes by the LDAP itself because it causes stupid tragic problems with syncrepl replication system.I manage to get a SQL query that returns entries like : cn objectClass member .... MyGroup1 groupOfNames;top uid=lpouzenc,ou=poeple,dc=univ-jfc,dc=fr;uid=otherguy,ou=poeple,dc=univ-jfc,dc=frI just put ojdbc6.jar in the lib path of LSC and it goes happily. I use Oracle Database 11g Release 2 (11.2.0.4) JDBC Drivers and associated javadoc.In SQL code, I use a user func for concatenating strings because LIST_AGG() is Oracle >= 11 and WM_CONCAT() is unsupported, limited with VARCHAR2(4000) and enforces "," separator.The "member" column out of my custom function use CLOB type. I have LSC errors on every insert or update. LDIF dumped in the logs are like :juin 01 17:23:34 - ERROR - Error while adding entry cn=SG,ou=structures,ou=groups,dc=univ-jfc,dc=fr in directory :javax.naming.directory.InvalidAttributeValueException: Malformed 'member' attribute value; remaining name 'cn=SG,ou=structures,ou=groups' juin 01 17:23:34 - ERROR - Error while synchronizing ID cn=SG,ou=structures,ou=groups,dc=univ-jfc,dc=fr: java.lang.Exception: Technical problem while applying modifications to the destination # Mon Jun 01 17:23:34 CEST 2015 dn: cn=SG,ou=structures,ou=groups,dc=univ-jfc,dc=fr changetype: add member:*oracle.sql.CLOB@1f8db72d* supannCodeEntite: 1494 cn: SG description: SECRETARIAT GENERAL objectClass: groupOfNames objectClass: supannEntite supannCodeEntiteParent: 2It seems LSC just use toString() (or no conv at all) on this particular objdc type and this could not return a valid dn list.I've tried some javascript, but I'm unsuccessful : <dataset> <name>member</name> <policy>FORCE</policy> <forceValues> <string>js:srcBean.getDatasetFirstValueById("member").getSubString(1,10)</string> </forceValues> </dataset>This complains that getSubString() method is not defined. This method is defined in oracle.sql.CLOB but not in generic JDBC types.I know that, with the same lib in a java project that this is okay : CLOB field1 = new CLOB(null); field1.getSubString(1, 10);but this is not : (Datum is a super type of CLOB. Any other more generic type do the job):Datum field2 = new CLOB(null); field2.getSubString(1, 10);I'm afraid that I can't call specific oracle.sql.* methods from the Javascript code.Here are my questions : * Could I use any CLOB with LSC ? * Is there an alternative to big strings concat's on multi-valued attributes when data came from RDBMS ?Everything else is very great in LSC, many thanks to all folks that are involved.Cheers, -- Ludovic Pouzenc - Administrateur Système CUFR J.-F. Champollion, Site d'Albi Tél: 05.63.48.64.08, ext. 2221# Unix is user-friendly; it's just picky about who its friends are.
-- Ludovic Pouzenc - Administrateur Système CUFR J.-F. Champollion, Site d'Albi Tél: 05.63.48.64.08, ext. 2221# Unix is user-friendly; it's just picky about who its friends are.
lsc-clob-adapter.jar
Description: application/java-archive
_______________________________________________________________ Ldap Synchronization Connector (LSC) - http://lsc-project.org lsc-users mailing list [email protected] http://lists.lsc-project.org/listinfo/lsc-users
