External user database synchronization: duplicated user

[andrepra]

Hi,

I wrote some classes to perform the synchronization with an external user database following the LDAP examples. I wrote MyDatabasePersonExportSource to generate the export and the PersonServiceImpl to override the method getDefaultProperties() for user created on demand at login.
I use this classes from version 1.4 of Alfresco without problems. Recently I upgraded to Alfresco 3.0 (community final). I start get some problems related to some duplicated users.
I start debug the source to understand what happen and I see that at the end of the method  generateExport of the ExportSource implementation there's a check for the existance of the user.

if (personService.personExists(uid)) {
‍‍‍

The method  personExists of the PersonServiceImpl call the method getPersonOrNull of the PersonDaoImpl that executes this query

SQLQuery query = getSession().createSQLQuery("SELECT {n.*} FROM alf_node n JOIN alf_node_properties p ON n.id = p.node_id JOIN alf_child_assoc c on c.child_node_id = n.id WHERE c.qname_localname = :userName1 AND p.qname_id = :qnameId AND p.string_value = :userName2 and n.node_deleted = :False");
                query.addEntity("n", NodeImpl.class);
                query.setParameter("qnameId", qNameId);
                query.setParameter("userName1", searchUserName);
                query.setParameter("userName2", searchUserName);
                query.setParameter("False", Boolean.FALSE);
                return query.list();
‍‍‍‍‍‍‍‍‍

where qNameId = 98 and searchUserName is the userid to check.
Debugging I saw that the results of this query is always empty. So the check return always false.

I tried to execute the query directly on database with an administration console and return 1 record. Very strange.

I found the problem on these configurations:
Alfresco 3 stable
Database: MySQL 5.0.21 and 5.0.49
JDBC: jdbc connector 5.0.3
O.S.: Windows and Linux

My previous post: http://forums.alfresco.com/en/viewtopic.php?f=9&t=17941

Thanks
Andrea

[andrepra]

Looking better I found that I was looking a previous version of the code. The real query is

        SELECT
           {n.*}
        FROM
           alf_node n
           JOIN alf_node_properties p ON n.id = p.node_id
           JOIN alf_child_assoc c on c.child_node_id = n.id
         JOIN alf_store s on s.id = n.store_id
        WHERE
           c.qname_localname = :userName1 AND
           p.qname_id = :qnameId AND
           p.string_value = :userName2 AND
           n.node_deleted = :False AND
      s.protocol = :storeProtocol AND
      s.identifier = :storeIdentifier
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

where storeProtocol = 'workspace' and storeIdentifier = 'SpacesStore'
This time I'm sure of the query because i've enabled the show sql hibernate parameter.
The problem is the same: Alfresco don't find any record while running query from a console return 1 record.

Andrea

[andrepra]

There's no error. Simply the query return no record when executed from Alfresco while return one record when excuted from database console.
The implementation of the class ExportSource create an xml file with the users to import in Alfresco. For each user the routine check if already present in Alfresco and, if present, retrieve the node id. With the node id the import procedure execute update otherwise create.
Returning no record the method existsPerson return false also if the user is already in Alfresco. So the node id is never retrieved.

Andrea