[lsc-users] Issues syncing ApacheDS Ldap with MySQl DB

jijo.cs10 at iitp.ac.in jijo.cs10 at iitp.ac.in
Fri Oct 17 14:03:00 CEST 2014


Hi,

I am facing some lsc issues.Really want some help.
I am using Apache Directory Studio with ApacheDS LDAP Server v2.0 and
MySQL DB using MYSQL WorkBench v6.1. I am using lsc to sync Groups from
ldap to mysql.


the AD structure is :

BaseDN
------ou=Groups
      -------ou=Group1
             ----------member = uid=User1
             ----------member = uid=User2

      -------ou=Group2
             ----------member = uid=User3

------ou=Users
      -------uid=User1
      -------uid=User2
      -------uid=User3

Each Group has some 'member' attributes ( because Apache LDAP Server do
not allow 'memberOf' in inetOrgPerson!!) which have uid of some users in
ou=Users.
In SQLDB i have Groups,Users and user_group table which references other 2
tables(to give membership info)
the issues are the following :

1) I am able to sync ou=Users seperately in users table of DB and
ou=Groups seperately in groups table in DB. but i need to sync groups, and
through groups i need to fill users table and users_groups table. how can
i do that.?

2) basically my issue is that i will fetch group attributes including
multivalued members attr from ou=Groups... but then how do i recursively
fetch member attributes from ou=Users and then fill the users table in DB?

In lsc.xml :

<tasks>

//Comment : This tak works fine alone to fill Users table in DB
	<task>
      <name>SyncUsersADtoSQL</name>
      <bean>org.lsc.beans.SimpleBean</bean>
      <ldapSourceService>
        <name>ldap-syncuserstoSQL-service</name>
        <connection reference="ldap-conn" />
        <baseDn>ou=Users,dc=xxxxx,dc=com</baseDn>
        <pivotAttributes>
          <string>mail</string>
        </pivotAttributes>
        <fetchedAttributes>
          <string>cn</string>
          <string>sn</string>
		  <string>mail</string>
		  <string>mobile</string>
		  <string>uid</string>
          <string>userPassword</string>
        </fetchedAttributes>
        <getAllFilter>(&amp;(objectClass=inetOrgPerson)(mail=*))</getAllFilter>
        <getOneFilter>(&amp;(objectClass=inetOrgPerson)(mail={mail}))</getOneFilter>
        <cleanFilter>(&amp;(objectClass=inetOrgPerson)(mail={mail}))</cleanFilter>
      </ldapSourceService>
      <databaseDestinationService>
        <name>mysql-syncuserstoSQL-service</name>
        <connection reference="mysql-conn" />
       	<requestNameForList>Person.getPersonList</requestNameForList>
       	<requestNameForObject>Person.getPerson</requestNameForObject>
       	<requestsNameForInsert><string>Person.insertPerson</string></requestsNameForInsert>
       	<requestsNameForUpdate><string>Person.updatePerson</string></requestsNameForUpdate>
       	<requestsNameForDelete><string>Person.deletePerson</string></requestsNameForDelete>
      </databaseDestinationService>
      <propertiesBasedSyncOptions>
        <mainIdentifier>srcBean.getMainIdentifier()</mainIdentifier>
        <defaultDelimiter>;</defaultDelimiter>
        <defaultPolicy>FORCE</defaultPolicy>
        <conditions>
			<create>true</create>
			<update>true</update>
			<delete>true</delete>
        	<changeId>false</changeId>
        </conditions>
      </propertiesBasedSyncOptions>
    </task>


//Comment : this also works if only to fill Groups table in DB with pivot
= member(multivalued)

	<task>
      <name>SyncGroupsADtoSQL</name>
      <bean>org.lsc.beans.SimpleBean</bean>
      <ldapSourceService>
        <name>ldap-syncgroupstoSQL-service</name>
        <connection reference="ldap-conn" />
        <baseDn>ou=Groups,dc=samsung,dc=com</baseDn>
        <pivotAttributes>
          <string>member</string>
        </pivotAttributes>
        <fetchedAttributes>
          <string>cn</string>
		  <string>member</string>
        </fetchedAttributes>
        <getAllFilter>(&amp;(objectClass=groupOfNames))</getAllFilter>
        <getOneFilter>(&amp;(objectClass=groupOfNames)(member={member}))</getOneFilter>
        <cleanFilter>(&amp;(objectClass=groupOfNames)(member={member}))</cleanFilter>
      </ldapSourceService>
      <databaseDestinationService>
        <name>mysql-syncgroupstoSQL-service</name>
        <connection reference="mysql-conn" />
       	<requestNameForList>Group.getGroupList</requestNameForList>
       	<requestNameForObject>Group.getGroup</requestNameForObject>
       	<requestsNameForInsert><string>Group.insertGroup</string></requestsNameForInsert>
       	<requestsNameForUpdate><string>Group.updateGroup</string></requestsNameForUpdate>
       	<requestsNameForDelete><string>Group.deleteGroup</string></requestsNameForDelete>
      </databaseDestinationService>
      <propertiesBasedSyncOptions>
        <mainIdentifier>"member="+srcBean.getDatasetById("member")</mainIdentifier>
        <defaultDelimiter>;</defaultDelimiter>
        <defaultPolicy>FORCE</defaultPolicy>
        <conditions>
			<create>true</create>
			<update>true</update>
			<delete>true</delete>
        	<changeId>false</changeId>
        </conditions>
      </propertiesBasedSyncOptions>
    </task>

  </tasks>



///////////////////////////////////////////////////////////////////////

How do i modify this so that i can just run Group sync task and
resursively also sync Users (members) in it????


In sql-map-config.xml:

<sqlMapConfig>

  <!-- The settings element has a number of configuration switches and
properties
       that configure the SQL Maps engine. Most are performance tweaks or
resource
       management.  They are pretty self explanatory. The defaults are
shown here.
       There can be only one settins element.  -->

  <settings
    lazyLoadingEnabled="true"
    cacheModelsEnabled="true"
    enhancementEnabled="true"
	useStatementNamespaces="true"
    maxRequests="512"
    maxSessions="128"
    maxTransactions="32"
    />

  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
     <property value="com.mysql.jdbc.Driver" name="JDBC.Driver"/>
	 <property value="jdbc:mysql://localhost:3306/adsync"
name="JDBC.ConnectionURL"/>
	 <property value="root" name="JDBC.Username"/>
	 <property value="root" name="JDBC.Password"/>
	 <property value="15" name="Pool.MaximumActiveConnections"/>
	 <property value="15" name="Pool.MaximumIdleConnections"/>
	 <property value="1000" name="Pool.MaximumWait"/>
    </dataSource>
  </transactionManager>

  <!-- Finally, the SQL Maps themselves must be registred with the
configuration file.
       Below are some examples of some SQL Map reference elements.  Notice
that an
       SQL Map can  be located in the classpath (resource) or at a URL
(e.g. file://
       or http://).  There can be any number of sqlMap elements. -->
  <sqlMap url="file://${lsc.config}/sql-map-config.d/GroupOfNames.xml"/>
  <sqlMap url="file://${lsc.config}/sql-map-config.d/InetOrgPerson.xml"/>

</sqlMapConfig>


Also give me an idea how to run the SQl statements in <SqlMap> in
GroupofNames.xml for insert , update and delete of Groups and through
groups, users......


here are the DB tables :

CREATE TABLE `groups` (
  `groupId` bigint(20) NOT NULL AUTO_INCREMENT,
  `groupName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`groupId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `users` (
  `userId` bigint(20) NOT NULL AUTO_INCREMENT,
  `commonName` varchar(255) DEFAULT NULL,
  `lastName` varchar(255) DEFAULT NULL,
  `emailId` varchar(255) NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userId`,`emailId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `user_group` (
  `user_id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`,`group_id`),
  KEY `FK_b690ka5rykxik9ab7m7cvi66j` (`group_id`),
  CONSTRAINT `FK_7ouurayfsmsi3jk4rfug32bgu` FOREIGN KEY (`user_id`)
REFERENCES `users` (`userId`),
  CONSTRAINT `FK_b690ka5rykxik9ab7m7cvi66j` FOREIGN KEY (`group_id`)
REFERENCES `groups` (`groupId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


regards,
thanks















More information about the lsc-users mailing list