Source code

001/*
002 *  Copyright 2016 Anyware Services
003 *
004 *  Licensed under the Apache License, Version 2.0 (the "License");
005 *  you may not use this file except in compliance with the License.
006 *  You may obtain a copy of the License at
007 *
008 *      http://www.apache.org/licenses/LICENSE-2.0
009 *
010 *  Unless required by applicable law or agreed to in writing, software
011 *  distributed under the License is distributed on an "AS IS" BASIS,
012 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 *  See the License for the specific language governing permissions and
014 *  limitations under the License.
015 */
016package org.ametys.plugins.core.impl.group.directory.jdbc;
017
018import java.sql.Connection;
019import java.sql.PreparedStatement;
020import java.sql.ResultSet;
021import java.sql.SQLException;
022import java.sql.Statement;
023import java.util.ArrayList;
024import java.util.Arrays;
025import java.util.Collection;
026import java.util.Collections;
027import java.util.HashMap;
028import java.util.HashSet;
029import java.util.Iterator;
030import java.util.LinkedHashSet;
031import java.util.List;
032import java.util.Map;
033import java.util.Set;
034
035import org.apache.avalon.framework.activity.Disposable;
036import org.apache.avalon.framework.service.ServiceException;
037import org.apache.avalon.framework.service.ServiceManager;
038import org.apache.avalon.framework.service.Serviceable;
039import org.apache.commons.lang3.ArrayUtils;
040import org.apache.commons.lang3.StringUtils;
041import org.apache.excalibur.source.SourceResolver;
042
043import org.ametys.core.ObservationConstants;
044import org.ametys.core.cache.AbstractCacheManager;
045import org.ametys.core.cache.Cache;
046import org.ametys.core.datasource.ConnectionHelper;
047import org.ametys.core.datasource.dbtype.SQLDatabaseTypeExtensionPoint;
048import org.ametys.core.group.Group;
049import org.ametys.core.group.GroupIdentity;
050import org.ametys.core.group.InvalidModificationException;
051import org.ametys.core.group.ModifiableGroup;
052import org.ametys.core.group.directory.GroupDirectory;
053import org.ametys.core.group.directory.GroupDirectoryModel;
054import org.ametys.core.group.directory.ModifiableGroupDirectory;
055import org.ametys.core.migration.MigrationExtensionPoint;
056import org.ametys.core.migration.storage.VersionStorageExtensionPoint;
057import org.ametys.core.migration.storage.impl.SqlVersionStorage;
058import org.ametys.core.observation.Event;
059import org.ametys.core.observation.ObservationManager;
060import org.ametys.core.script.SQLScriptHelper;
061import org.ametys.core.user.CurrentUserProvider;
062import org.ametys.core.user.UserIdentity;
063import org.ametys.core.util.Cacheable;
064import org.ametys.core.util.SizeUtils.ExcludeFromSizeCalculation;
065import org.ametys.runtime.i18n.I18nizableText;
066import org.ametys.runtime.i18n.I18nizableTextParameter;
067import org.ametys.runtime.plugin.component.AbstractLogEnabled;
068
069/**
070 * Standard implementation of {@link GroupDirectory} from the core database.
071 */
072public class JdbcGroupDirectory extends AbstractLogEnabled implements ModifiableGroupDirectory, Serviceable, Cacheable, Disposable
073{
074    /** Name of the parameter holding the datasource id */
075    private static final String __DATASOURCE_PARAM_NAME = "runtime.groups.jdbc.datasource";
076    /** Name of the parameter holding the SQL table name for storing the groups */
077    private static final String __GROUPS_LIST_TABLE_PARAM_NAME = "runtime.groups.jdbc.list.table";
078    /** Name of the parameter holding the SQL table name for storing the composition (i.e. users) of the groups */
079    private static final String __GROUPS_COMPOSITION_TABLE_PARAM_NAME = "runtime.groups.jdbc.composition.table";
080    
081    private static final String __GROUPS_LIST_COLUMN_ID = "Id";
082    private static final String __GROUPS_LIST_COLUMN_LABEL = "Label";
083    private static final String __GROUPS_COMPOSITION_COLUMN_GROUPID = "Group_Id";
084    private static final String __GROUPS_COMPOSITION_COLUMN_LOGIN = "Login";
085    private static final String __GROUPS_COMPOSITION_COLUMN_POPULATIONID = "UserPopulation_Id";
086    
087    private static final String __JDBC_GROUPDIRECTORY_GROUPS_BY_USER_CACHE_NAME_PREFIX = JdbcGroupDirectory.class.getName() + "$groups.by.user$";
088    
089    /** The observation manager */
090    protected ObservationManager _observationManager;
091    /** The current user provider */
092    protected CurrentUserProvider _currentUserProvider;
093    /** The cocoon source resolver */
094    protected SourceResolver _sourceResolver;
095    /** The cache manager */
096    protected AbstractCacheManager _cacheManager;
097    
098    /** The identifier of data source */
099    protected String _dataSourceId;
100    /** The name of the SQL table storing the groups */
101    protected String _groupsListTableName;
102    /** The name of the SQL table storing the composition (i.e. users) of the groups*/
103    protected String _groupsCompositionTableName;
104    
105    /** Avalon manager */
106    protected ServiceManager _manager;
107    
108    /** The id */
109    protected String _id;
110    /** The label */
111    protected I18nizableText _label;
112    /** The id of the {@link GroupDirectoryModel} */
113    private String _groupDirectoryModelId;
114    /** The map of the values of the parameters */
115    private Map<String, Object> _paramValues;
116    private boolean _lazyInitialized;
117    
118    // Cannot use _id as two GroupDirectories with same id can co-exist during a short amount of time (during GroupDirectoryDAO#_read)
119    private final String _uniqueCacheSuffix = org.ametys.core.util.StringUtils.generateKey();
120    
121    private MigrationExtensionPoint _migrationEP;
122    
123    private SqlVersionStorage _sqlVersionStorage;
124    private SQLDatabaseTypeExtensionPoint _sqlDatabaseTypeExtensionPoint;
125    
126    @Override
127    public void service(ServiceManager manager) throws ServiceException
128    {
129        _manager = manager;
130        _observationManager = (ObservationManager) manager.lookup(ObservationManager.ROLE);
131        _currentUserProvider = (CurrentUserProvider) manager.lookup(CurrentUserProvider.ROLE);
132        _sourceResolver = (SourceResolver) manager.lookup(SourceResolver.ROLE);
133        _cacheManager = (AbstractCacheManager) manager.lookup(AbstractCacheManager.ROLE);
134        _migrationEP = (MigrationExtensionPoint) manager.lookup(MigrationExtensionPoint.ROLE);
135        VersionStorageExtensionPoint versionStorageEP = (VersionStorageExtensionPoint) manager.lookup(VersionStorageExtensionPoint.ROLE);
136        _sqlVersionStorage = (SqlVersionStorage) versionStorageEP.getExtension("sql");
137    }
138    
139    private SQLDatabaseTypeExtensionPoint getSQLDatabaseTypeExtensionPoint()
140    {
141        if (_sqlDatabaseTypeExtensionPoint == null)
142        {
143            try
144            {
145                _sqlDatabaseTypeExtensionPoint = (SQLDatabaseTypeExtensionPoint) _manager.lookup(SQLDatabaseTypeExtensionPoint.ROLE);
146            }
147            catch (ServiceException e)
148            {
149                throw new RuntimeException(e);
150            }
151        }
152        return _sqlDatabaseTypeExtensionPoint;
153    }
154    
155    @Override
156    public String getId()
157    {
158        return _id;
159    }
160    
161    @Override
162    public I18nizableText getLabel()
163    {
164        return _label;
165    }
166    
167    @Override
168    public void setId(String id)
169    {
170        _id = id;
171    }
172    
173    @Override
174    public void setLabel(I18nizableText label)
175    {
176        _label = label;
177    }
178    
179    @Override
180    public String getGroupDirectoryModelId ()
181    {
182        return _groupDirectoryModelId;
183    }
184    
185    @Override
186    public Map<String, Object> getParameterValues()
187    {
188        return _paramValues;
189    }
190    
191    @Override
192    public void dispose()
193    {
194        removeCaches();
195    }
196    
197    @Override
198    public Collection<SingleCacheConfiguration> getManagedCaches()
199    {
200        return Arrays.asList(
201                SingleCacheConfiguration.of(
202                        __JDBC_GROUPDIRECTORY_GROUPS_BY_USER_CACHE_NAME_PREFIX + _uniqueCacheSuffix, 
203                        _buildI18n("PLUGINS_CORE_GROUPS_JDBC_CACHE_GROUPS_BY_USER_LABEL"), 
204                        _buildI18n("PLUGINS_CORE_GROUPS_JDBC_CACHE_GROUPS_BY_USER_DESC"))
205        );
206    }
207    
208    private I18nizableText _buildI18n(String i18Key)
209    {
210        String catalogue = "plugin.core-impl";
211        I18nizableText groupDirectoryId = new I18nizableText(getId());
212        Map<String, I18nizableTextParameter> params = Map.of("id", groupDirectoryId);
213        return new I18nizableText(catalogue, i18Key, params);
214    }
215    
216    private Cache<UserIdentity, Set<String>> _getCacheGroupsByUser()
217    {
218        return getCacheManager().get(__JDBC_GROUPDIRECTORY_GROUPS_BY_USER_CACHE_NAME_PREFIX + _uniqueCacheSuffix);
219    }
220    
221    @Override
222    public AbstractCacheManager getCacheManager()
223    {
224        return _cacheManager;
225    }
226    
227    @Override
228    public void init(String groupDirectoryModelId, Map<String, Object> paramValues)
229    {
230        _groupDirectoryModelId = groupDirectoryModelId;
231        _paramValues = paramValues;
232        
233        _groupsListTableName = (String) paramValues.get(__GROUPS_LIST_TABLE_PARAM_NAME);
234        _groupsCompositionTableName = (String) paramValues.get(__GROUPS_COMPOSITION_TABLE_PARAM_NAME);
235        _dataSourceId = (String) paramValues.get(__DATASOURCE_PARAM_NAME);
236        
237        createCaches();
238    }
239    
240    /**
241     * Get the connection to the database 
242     * @return the SQL connection
243     */
244    @SuppressWarnings("unchecked")
245    protected Connection getSQLConnection()
246    {
247        Connection connection = ConnectionHelper.getConnection(_dataSourceId);
248        
249        if (!_lazyInitialized)
250        {
251            try
252            {
253                String componentId = "group-directory.jdbc";
254                String versionId = "org.ametys.plugins.core.user.directory.Jdbc.upgrade_" + _groupsListTableName;
255                SQLScriptHelper.createTableIfNotExists(_dataSourceId, _groupsListTableName, "plugin:core://scripts/%s/jdbc_groups.template.sql", _sourceResolver, 
256                        (Map) ArrayUtils.toMap(new String[][] {{"%TABLENAME%", _groupsListTableName}, {"%TABLENAME_COMPOSITION%", _groupsCompositionTableName}}), componentId, versionId, _migrationEP, _sqlVersionStorage);
257            }
258            catch (Exception e)
259            {
260                getLogger().error("The tables requires by the " + this.getClass().getName() + " could not be created. A degraded behavior will occur", e);
261            }
262            
263            _lazyInitialized = true;
264        }
265        
266        return connection;
267    }
268    
269    @Override
270    public ModifiableGroup getGroup(String groupID)
271    {
272        JdbcGroup group = null;
273
274        Connection connection = null;
275        PreparedStatement stmt = null;
276        ResultSet rs = null;
277
278        try
279        {
280            connection = getSQLConnection();
281            String dbType = ConnectionHelper.getDatabaseType(connection);
282            
283            String sql = "SELECT " + __GROUPS_LIST_COLUMN_LABEL + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " WHERE " + __GROUPS_LIST_COLUMN_ID + " = ?";
284            stmt = connection.prepareStatement(sql);
285            stmt.setInt(1, Integer.parseInt(groupID));
286
287            if (getLogger().isDebugEnabled())
288            {
289                getLogger().debug(sql);
290            }
291            rs = stmt.executeQuery();
292
293            // Iterate over all the groups
294            if (rs.next())
295            {
296                String label = rs.getString(__GROUPS_LIST_COLUMN_LABEL);
297                group = new JdbcGroup(new GroupIdentity(groupID, getId()), label, this);
298
299                _fillGroup(group, connection);
300            }
301        }
302        catch (NumberFormatException e)
303        {
304            getLogger().error("Group ID must be an integer.", e);
305            return null;
306        }
307        catch (SQLException e)
308        {
309            getLogger().error("Error communication with database", e);
310            return null;
311        }
312        finally
313        {
314            ConnectionHelper.cleanup(rs);       
315            ConnectionHelper.cleanup(stmt);       
316            ConnectionHelper.cleanup(connection);       
317        }
318
319        // Return the found group or null
320        return group;
321    }
322
323    @Override
324    public Set<Group> getGroups()
325    {
326        Set<Group> groups = new LinkedHashSet<>();
327
328        Connection connection = null;
329        Statement stmt = null;
330        ResultSet rs = null;
331        
332        try
333        {
334            connection = getSQLConnection();
335            String dbType = ConnectionHelper.getDatabaseType(connection);
336            
337            stmt = connection.createStatement();
338            String sql = _createGetGroupsClause(dbType);
339
340            if (getLogger().isDebugEnabled())
341            {
342                getLogger().debug(sql);
343            }
344            rs = stmt.executeQuery(sql);
345
346            // Iterate over all the groups
347            while (rs.next())
348            {
349                String groupID = rs.getString(__GROUPS_LIST_COLUMN_ID);
350                String label = rs.getString(__GROUPS_LIST_COLUMN_LABEL);
351                JdbcGroup group = new JdbcGroup(new GroupIdentity(groupID, getId()), label, this);
352
353                _fillGroup(group, connection);
354
355                // Add current group
356                groups.add(group);
357            }
358        }
359        catch (SQLException e)
360        {
361            getLogger().error("Error communication with database", e);
362            return Collections.emptySet();
363        }
364        finally
365        {
366            ConnectionHelper.cleanup(rs);       
367            ConnectionHelper.cleanup(stmt);       
368            ConnectionHelper.cleanup(connection);       
369        }
370
371        return groups;
372    }
373    
374    /**
375     * Get the sql clause that gets all groups
376     * @param dbType The connection type
377     * @return A non null sql clause (e.g. "select ... from ... where ...")
378     */
379    protected String _createGetGroupsClause(String dbType)
380    {
381        return "SELECT " + __GROUPS_LIST_COLUMN_ID + ", " + __GROUPS_LIST_COLUMN_LABEL + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " ORDER BY " + __GROUPS_LIST_COLUMN_LABEL;
382    }
383    
384    /**
385     * Fill users set in a group.
386     * 
387     * @param group The group to fill.
388     * @param connection The SQL connection.
389     * @throws SQLException If a problem occurs.
390     */
391    protected void _fillGroup(JdbcGroup group, Connection connection) throws SQLException
392    {
393        PreparedStatement stmt = null;
394        ResultSet rs = null;
395
396        try
397        {
398            String dbType = ConnectionHelper.getDatabaseType(connection);
399            String sql = "SELECT " + __GROUPS_COMPOSITION_COLUMN_LOGIN + ", " + __GROUPS_COMPOSITION_COLUMN_POPULATIONID + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " = ?";
400
401            stmt = connection.prepareStatement(sql);
402
403            stmt.setInt(1, Integer.parseInt(group.getIdentity().getId()));
404
405            if (getLogger().isDebugEnabled())
406            {
407                getLogger().debug(sql);
408            }
409            rs = stmt.executeQuery();
410
411            // Iterate over all the users from current group 
412            while (rs.next())
413            {
414                UserIdentity identity = new UserIdentity(rs.getString(__GROUPS_COMPOSITION_COLUMN_LOGIN), rs.getString(__GROUPS_COMPOSITION_COLUMN_POPULATIONID));
415                group.addUser(identity);
416            }
417        }
418        finally
419        {
420            ConnectionHelper.cleanup(rs);
421            ConnectionHelper.cleanup(stmt);
422        }
423    }
424
425    @Override
426    public Set<String> getUserGroups(UserIdentity userIdentity)
427    {
428        if (isCachingEnabled() && _getCacheGroupsByUser().hasKey(userIdentity))
429        {
430            Set<String> userGroups = _getCacheGroupsByUser().get(userIdentity);
431            // Cache hit, return the results. 
432            return userGroups;
433        }
434        
435        Set<String> userGroups = _executeSqlForUserGroups(userIdentity);
436        
437        // Cache the results.
438        if (isCachingEnabled())
439        {
440            _getCacheGroupsByUser().put(userIdentity, userGroups);
441        }
442        
443        return userGroups;
444    }
445    
446    private Set<String> _executeSqlForUserGroups(UserIdentity userIdentity)
447    {
448        String login = userIdentity.getLogin();
449        String populationId = userIdentity.getPopulationId();
450        
451        Set<String> groups = new HashSet<>();
452        if (login == null)
453        {
454            return groups;
455        }
456        
457        Connection connection = null;
458        PreparedStatement stmt = null;
459        ResultSet rs = null;
460
461        try
462        {
463            connection = getSQLConnection();
464            String dbType = ConnectionHelper.getDatabaseType(connection);
465            
466            String sql = "SELECT " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_LOGIN + " = ? AND " + __GROUPS_COMPOSITION_COLUMN_POPULATIONID + " = ?";
467            stmt = connection.prepareStatement(sql);
468            stmt.setString(1, login);
469            stmt.setString(2, populationId);
470
471            if (getLogger().isDebugEnabled())
472            {
473                getLogger().debug(sql);
474            }
475            rs = stmt.executeQuery();
476
477            // Iterate over all the groups
478            while (rs.next())
479            {
480                String groupID = rs.getString(__GROUPS_COMPOSITION_COLUMN_GROUPID);
481
482                // Add the current group
483                groups.add(groupID);
484            }
485        }
486        catch (SQLException e)
487        {
488            getLogger().error("Error communication with database", e);
489            return Collections.emptySet();
490        }
491        finally
492        {
493            ConnectionHelper.cleanup(rs);       
494            ConnectionHelper.cleanup(stmt);       
495            ConnectionHelper.cleanup(connection);       
496        }
497
498        // Return the groups, potentially empty
499        return groups;
500    }
501
502    @Override
503    public List<Group> getGroups(int count, int offset, Map parameters)
504    {
505        List<Group> groups = new ArrayList<>();
506        
507        String pattern = (String) parameters.get("pattern");
508
509        Iterator iterator = getGroups().iterator();
510
511        //int totalCount = 0;
512        int currentOffset = offset;
513
514        while (currentOffset > 0 && iterator.hasNext())
515        {
516            Group group = (Group) iterator.next();
517            if (StringUtils.isEmpty(pattern)
518                || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1
519                || group.getIdentity() != null && group.getIdentity().getId().toLowerCase().indexOf(pattern.toLowerCase()) != -1)
520            {
521                currentOffset--;
522                //totalCount++;
523            }
524        }
525
526        int currentCount = count;
527        while ((count == -1 || currentCount > 0) && iterator.hasNext())
528        {
529            Group group = (Group) iterator.next();
530
531            if (StringUtils.isEmpty(pattern)
532                || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1
533                || group.getIdentity() != null && group.getIdentity().getId().toLowerCase().indexOf(pattern.toLowerCase()) != -1)
534            {
535                groups.add(group);
536                currentCount--;
537                //totalCount++;
538            }
539        }
540
541        /*while (iterator.hasNext())
542        {
543            Group group = (Group) iterator.next();
544            
545            if (StringUtils.isEmpty(pattern) || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1)
546            {
547                totalCount++;
548            }
549        }*/
550        
551        // TODO return totalCount
552        return groups;
553    }
554
555    @Override
556    public ModifiableGroup add(String name) throws InvalidModificationException
557    {
558        Connection connection = null; 
559        PreparedStatement statement = null;
560        ResultSet rs = null;
561
562        String id = null;
563
564        try
565        {
566            connection = getSQLConnection();
567            String dbType = ConnectionHelper.getDatabaseType(connection);
568            
569            if (ConnectionHelper.DATABASE_ORACLE.equals(dbType))
570            {
571                statement = connection.prepareStatement("SELECT seq_" + _groupsListTableName + ".nextval FROM dual");
572                rs = statement.executeQuery();
573                if (rs.next())
574                {
575                    id = rs.getString(1);
576                }
577                ConnectionHelper.cleanup(rs);
578                ConnectionHelper.cleanup(statement);
579
580                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " (" + __GROUPS_LIST_COLUMN_ID + ", " + __GROUPS_LIST_COLUMN_LABEL + ") VALUES(?, ?)");
581                statement.setString(1, id);
582                statement.setString(2, name);
583            }
584            else
585            {
586                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " (" + __GROUPS_LIST_COLUMN_LABEL + ") VALUES (?)");
587                statement.setString(1, name);
588            }
589
590            statement.executeUpdate();
591
592            ConnectionHelper.cleanup(statement);
593
594            //FIXME Write query working with all database
595            if (ConnectionHelper.DATABASE_MYSQL.equals(dbType))
596            {
597                statement = connection.prepareStatement("SELECT " + __GROUPS_LIST_COLUMN_ID + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " WHERE " + __GROUPS_LIST_COLUMN_ID + " = last_insert_id()");    
598                rs = statement.executeQuery();
599                if (rs.next())
600                {
601                    id = rs.getString(__GROUPS_LIST_COLUMN_ID);
602                }
603                else
604                {
605                    if (connection.getAutoCommit())
606                    {
607                        throw new InvalidModificationException("Cannot retrieve inserted group. Group was created but listeners not called : base may be inconsistant");
608                    }
609                    else
610                    {
611                        connection.rollback();
612                        throw new InvalidModificationException("Cannot retrieve inserted group. Rolling back");
613                    }
614                }
615            }
616            else if (ConnectionHelper.DATABASE_DERBY.equals(dbType))
617            {
618                statement = connection.prepareStatement("VALUES IDENTITY_VAL_LOCAL ()");
619                rs = statement.executeQuery();
620                if (rs.next())
621                {
622                    id = rs.getString(1);
623                }
624            }
625            else if (ConnectionHelper.DATABASE_HSQLDB.equals(dbType))
626            {
627                statement = connection.prepareStatement("CALL IDENTITY ()");
628                rs = statement.executeQuery();
629                if (rs.next())
630                {
631                    id = rs.getString(1);
632                }
633            }
634            else if (ConnectionHelper.DATABASE_POSTGRES.equals(dbType))
635            {
636                statement = connection.prepareStatement("SELECT lastval()");
637                rs = statement.executeQuery();
638                if (rs.next())
639                {
640                    id = rs.getString(1);
641                }
642            }
643
644            if (id != null)
645            {
646                Map<String, Object> eventParams = new HashMap<>();
647                eventParams.put(ObservationConstants.ARGS_GROUP, new GroupIdentity(id, getId()));
648                _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_ADDED, _currentUserProvider.getUser(), eventParams));
649            }
650        }
651        catch (SQLException ex)
652        {
653            throw new RuntimeException(ex);
654        }
655        finally
656        {
657            ConnectionHelper.cleanup(rs);       
658            ConnectionHelper.cleanup(statement);       
659            ConnectionHelper.cleanup(connection);       
660        }
661
662        return new JdbcGroup(new GroupIdentity(id, getId()), name, this);
663    }
664
665    @Override
666    public void update(ModifiableGroup userGroup) throws InvalidModificationException
667    {
668        Connection connection = null;
669        PreparedStatement statement = null;
670
671        if (getLogger().isDebugEnabled())
672        {
673            getLogger().debug("Updating group " + GroupIdentity.groupIdentityToString(userGroup.getIdentity()) + " with " + userGroup.getUsers().size() + " user(s)");
674        }
675        
676        try
677        {
678            
679            connection = getSQLConnection();
680            String dbType = ConnectionHelper.getDatabaseType(connection);
681            
682            // Start transaction.
683            connection.setAutoCommit(false);
684
685            statement = connection.prepareStatement("UPDATE " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " SET " + __GROUPS_LIST_COLUMN_LABEL + "=? WHERE " + __GROUPS_LIST_COLUMN_ID + " = ?");
686            statement.setString(1, userGroup.getLabel());
687            statement.setInt(2, Integer.parseInt(userGroup.getIdentity().getId()));
688
689            if (statement.executeUpdate() == 0)
690            {
691                throw new InvalidModificationException("No group with id '" + userGroup.getIdentity().getId() + "' may be removed");
692            }
693            ConnectionHelper.cleanup(statement);
694
695            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " = ?");
696            statement.setInt(1, Integer.parseInt(userGroup.getIdentity().getId()));
697
698            statement.executeUpdate();
699            ConnectionHelper.cleanup(statement);
700
701            if (!userGroup.getUsers().isEmpty())
702            {
703                // Tests if the connection supports batch updates.
704                boolean supportsBatch = connection.getMetaData().supportsBatchUpdates();
705
706                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " (" + __GROUPS_COMPOSITION_COLUMN_GROUPID + ", " + __GROUPS_COMPOSITION_COLUMN_LOGIN + ", " + __GROUPS_COMPOSITION_COLUMN_POPULATIONID + ") VALUES (?, ?, ?)");
707                
708                for (UserIdentity identity : userGroup.getUsers())
709                {
710                    String login = identity.getLogin();
711                    String populationId = identity.getPopulationId();
712                    statement.setInt(1, Integer.parseInt(userGroup.getIdentity().getId()));
713                    statement.setString(2, login);
714                    statement.setString(3, populationId);
715                    
716                    // If batch updates are supported, add to the batch, else execute directly.
717                    if (supportsBatch)
718                    {
719                        statement.addBatch();
720                    }
721                    else
722                    {
723                        statement.executeUpdate();
724                    }
725                }
726                
727                // If the insert queries were queued in a batch, execute it.
728                if (supportsBatch)
729                {
730                    statement.executeBatch();
731                }
732            }
733
734            ConnectionHelper.cleanup(statement);
735
736            // Commit transaction.
737            connection.commit();
738            
739            // Clear global cache
740            _getCacheGroupsByUser().invalidateAll();
741
742            Map<String, Object> eventParams = new HashMap<>();
743            eventParams.put(ObservationConstants.ARGS_GROUP, userGroup.getIdentity());
744            _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_UPDATED, _currentUserProvider.getUser(), eventParams));
745        }
746        catch (NumberFormatException ex)
747        {
748            throw new InvalidModificationException("No group with id '" + userGroup.getIdentity().getId() + "' may be removed", ex);
749        }
750        catch (SQLException ex)
751        {
752            throw new RuntimeException(ex);
753        }
754        finally
755        {
756            ConnectionHelper.cleanup(statement);
757            ConnectionHelper.cleanup(connection);
758        }
759        
760        if (getLogger().isDebugEnabled())
761        {
762            getLogger().debug("Updated group " + GroupIdentity.groupIdentityToString(userGroup.getIdentity()) + " with " + userGroup.getUsers().size() + " user(s)");
763        }
764    }
765
766    @Override
767    public void remove(String groupID) throws InvalidModificationException
768    {
769        Connection connection = null;
770        PreparedStatement statement = null;
771
772        try
773        {
774            connection = getSQLConnection();
775            String dbType = ConnectionHelper.getDatabaseType(connection);
776            
777            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " WHERE " + __GROUPS_LIST_COLUMN_ID + " = ?");
778            statement.setInt(1, Integer.parseInt(groupID));
779
780            if (statement.executeUpdate() == 0)
781            {
782                throw new InvalidModificationException("No group with id '" + groupID + "' may be removed");
783            }
784            ConnectionHelper.cleanup(statement);       
785
786            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " = ?");
787            statement.setInt(1, Integer.parseInt(groupID));
788
789            statement.executeUpdate();
790            
791            // Clear global cache
792            _getCacheGroupsByUser().invalidateAll();
793
794            Map<String, Object> eventParams = new HashMap<>();
795            eventParams.put(ObservationConstants.ARGS_GROUP, new GroupIdentity(groupID, getId()));
796            _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_DELETED, _currentUserProvider.getUser(), eventParams));
797        }
798        catch (NumberFormatException ex)
799        {
800            throw new InvalidModificationException("No group with id '" + groupID + "' may be removed, the ID must be a number.", ex);
801        }
802        catch (SQLException ex)
803        {
804            throw new RuntimeException(ex);
805        }
806        finally
807        {
808            ConnectionHelper.cleanup(statement);       
809            ConnectionHelper.cleanup(connection);       
810        }
811    }
812    
813    private static final class JdbcGroup implements ModifiableGroup
814    {
815        private Set<UserIdentity> _users;
816        private GroupIdentity _identity;
817        private String _groupLabel;
818        
819        @ExcludeFromSizeCalculation
820        private GroupDirectory _groupDirectory;
821        
822        JdbcGroup(GroupIdentity identity, String label, GroupDirectory groupDirectory)
823        {
824            _identity = identity;
825            _groupLabel = label;
826            _groupDirectory = groupDirectory;
827            _users = new HashSet<>();
828        }
829        
830        @Override
831        public GroupIdentity getIdentity()
832        {
833            return _identity;
834        }
835
836        @Override
837        public String getLabel()
838        {
839            return _groupLabel;
840        }
841
842        @Override
843        public GroupDirectory getGroupDirectory()
844        {
845            return _groupDirectory;
846        }
847        
848        @Override
849        public void setLabel(String label)
850        {
851            _groupLabel = label;
852        }
853
854        @Override
855        public void addUser(UserIdentity user)
856        {
857            _users.add(user);
858        }
859
860        @Override
861        public void removeUser(UserIdentity user)
862        {
863            _users.remove(user);
864        }
865        
866        @Override
867        public void removeUsers()
868        {
869            _users.clear();
870        }
871        
872        @Override
873        public Set<UserIdentity> getUsers()
874        {
875            return _users;
876        }
877        
878        @Override
879        public String toString()
880        {
881            StringBuffer sb = new StringBuffer("UserGroup[");
882            sb.append(_identity);
883            sb.append(" (");
884            sb.append(_groupLabel);
885            sb.append(") => ");
886            sb.append(_users.toString());
887            sb.append("]");
888            return sb.toString();
889        }    
890        
891        @Override
892        public boolean equals(Object another)
893        {
894            if (another == null || !(another instanceof JdbcGroup))
895            {
896                return false;
897            }
898            
899            JdbcGroup otherGroup = (JdbcGroup) another;
900            
901            return _identity != null && _identity.equals(otherGroup.getIdentity());
902        }
903        
904        @Override
905        public int hashCode()
906        {
907            return _identity.hashCode();
908        }
909    }
910}