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) || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1 || (group.getIdentity() != null && group.getIdentity().getId().toLowerCase().indexOf(pattern.toLowerCase()) != -1))
518            {
519                currentOffset--;
520                //totalCount++;
521            }
522        }
523
524        int currentCount = count;
525        while ((count == -1 || currentCount > 0) && iterator.hasNext())
526        {
527            Group group = (Group) iterator.next();
528
529            if (StringUtils.isEmpty(pattern) || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1 || (group.getIdentity() != null && group.getIdentity().getId().toLowerCase().indexOf(pattern.toLowerCase()) != -1))
530            {
531                groups.add(group);
532                currentCount--;
533                //totalCount++;
534            }
535        }
536
537        /*while (iterator.hasNext())
538        {
539            Group group = (Group) iterator.next();
540            
541            if (StringUtils.isEmpty(pattern) || group.getLabel().toLowerCase().indexOf(pattern.toLowerCase()) != -1)
542            {
543                totalCount++;
544            }
545        }*/
546        
547        // TODO return totalCount
548        return groups;
549    }
550
551    @Override
552    public ModifiableGroup add(String name) throws InvalidModificationException
553    {
554        Connection connection = null; 
555        PreparedStatement statement = null;
556        ResultSet rs = null;
557
558        String id = null;
559
560        try
561        {
562            connection = getSQLConnection();
563            String dbType = ConnectionHelper.getDatabaseType(connection);
564            
565            if (ConnectionHelper.DATABASE_ORACLE.equals(dbType))
566            {
567                statement = connection.prepareStatement("SELECT seq_" + _groupsListTableName + ".nextval FROM dual");
568                rs = statement.executeQuery();
569                if (rs.next())
570                {
571                    id = rs.getString(1);
572                }
573                ConnectionHelper.cleanup(rs);
574                ConnectionHelper.cleanup(statement);
575
576                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " (" + __GROUPS_LIST_COLUMN_ID + ", " + __GROUPS_LIST_COLUMN_LABEL + ") VALUES(?, ?)");
577                statement.setString(1, id);
578                statement.setString(2, name);
579            }
580            else
581            {
582                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " (" + __GROUPS_LIST_COLUMN_LABEL + ") VALUES (?)");
583                statement.setString(1, name);
584            }
585
586            statement.executeUpdate();
587
588            ConnectionHelper.cleanup(statement);
589
590            //FIXME Write query working with all database
591            if (ConnectionHelper.DATABASE_MYSQL.equals(dbType))
592            {
593                statement = connection.prepareStatement("SELECT " + __GROUPS_LIST_COLUMN_ID + " FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " WHERE " + __GROUPS_LIST_COLUMN_ID + " = last_insert_id()");    
594                rs = statement.executeQuery();
595                if (rs.next())
596                {
597                    id = rs.getString(__GROUPS_LIST_COLUMN_ID);
598                }
599                else
600                {
601                    if (connection.getAutoCommit())
602                    {
603                        throw new InvalidModificationException("Cannot retrieve inserted group. Group was created but listeners not called : base may be inconsistant");
604                    }
605                    else
606                    {
607                        connection.rollback();
608                        throw new InvalidModificationException("Cannot retrieve inserted group. Rolling back");
609                    }
610                }
611            }
612            else if (ConnectionHelper.DATABASE_DERBY.equals(dbType))
613            {
614                statement = connection.prepareStatement("VALUES IDENTITY_VAL_LOCAL ()");
615                rs = statement.executeQuery();
616                if (rs.next())
617                {
618                    id = rs.getString(1);
619                }
620            }
621            else if (ConnectionHelper.DATABASE_HSQLDB.equals(dbType))
622            {
623                statement = connection.prepareStatement("CALL IDENTITY ()");
624                rs = statement.executeQuery();
625                if (rs.next())
626                {
627                    id = rs.getString(1);
628                }
629            }
630            else if (ConnectionHelper.DATABASE_POSTGRES.equals(dbType))
631            {
632                statement = connection.prepareStatement("SELECT lastval()");
633                rs = statement.executeQuery();
634                if (rs.next())
635                {
636                    id = rs.getString(1);
637                }
638            }
639
640            if (id != null)
641            {
642                Map<String, Object> eventParams = new HashMap<>();
643                eventParams.put(ObservationConstants.ARGS_GROUP, new GroupIdentity(id, getId()));
644                _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_ADDED, _currentUserProvider.getUser(), eventParams));
645            }
646        }
647        catch (SQLException ex)
648        {
649            throw new RuntimeException(ex);
650        }
651        finally
652        {
653            ConnectionHelper.cleanup(rs);       
654            ConnectionHelper.cleanup(statement);       
655            ConnectionHelper.cleanup(connection);       
656        }
657
658        return new JdbcGroup(new GroupIdentity(id, getId()), name, this);
659    }
660
661    @Override
662    public void update(ModifiableGroup userGroup) throws InvalidModificationException
663    {
664        Connection connection = null;
665        PreparedStatement statement = null;
666
667        if (getLogger().isDebugEnabled())
668        {
669            getLogger().debug("Updating group " + GroupIdentity.groupIdentityToString(userGroup.getIdentity()) + " with " + userGroup.getUsers().size() + " user(s)");
670        }
671        
672        try
673        {
674            
675            connection = getSQLConnection();
676            String dbType = ConnectionHelper.getDatabaseType(connection);
677            
678            // Start transaction.
679            connection.setAutoCommit(false);
680
681            statement = connection.prepareStatement("UPDATE " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " SET " + __GROUPS_LIST_COLUMN_LABEL + "=? WHERE " + __GROUPS_LIST_COLUMN_ID + " = ?");
682            statement.setString(1, userGroup.getLabel());
683            statement.setInt(2, Integer.parseInt(userGroup.getIdentity().getId()));
684
685            if (statement.executeUpdate() == 0)
686            {
687                throw new InvalidModificationException("No group with id '" + userGroup.getIdentity().getId() + "' may be removed");
688            }
689            ConnectionHelper.cleanup(statement);
690
691            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " = ?");
692            statement.setInt(1, Integer.parseInt(userGroup.getIdentity().getId()));
693
694            statement.executeUpdate();
695            ConnectionHelper.cleanup(statement);
696
697            if (!userGroup.getUsers().isEmpty())
698            {
699                // Tests if the connection supports batch updates.
700                boolean supportsBatch = connection.getMetaData().supportsBatchUpdates();
701
702                statement = connection.prepareStatement("INSERT INTO " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " (" + __GROUPS_COMPOSITION_COLUMN_GROUPID + ", " + __GROUPS_COMPOSITION_COLUMN_LOGIN + ", " + __GROUPS_COMPOSITION_COLUMN_POPULATIONID + ") VALUES (?, ?, ?)");
703                
704                for (UserIdentity identity : userGroup.getUsers())
705                {
706                    String login = identity.getLogin();
707                    String populationId = identity.getPopulationId();
708                    statement.setInt(1, Integer.parseInt(userGroup.getIdentity().getId()));
709                    statement.setString(2, login);
710                    statement.setString(3, populationId);
711                    
712                    // If batch updates are supported, add to the batch, else execute directly.
713                    if (supportsBatch)
714                    {
715                        statement.addBatch();
716                    }
717                    else
718                    {
719                        statement.executeUpdate();
720                    }
721                }
722                
723                // If the insert queries were queued in a batch, execute it.
724                if (supportsBatch)
725                {
726                    statement.executeBatch();
727                }
728            }
729
730            ConnectionHelper.cleanup(statement);
731
732            // Commit transaction.
733            connection.commit();
734            
735            // Clear global cache
736            _getCacheGroupsByUser().invalidateAll();
737
738            Map<String, Object> eventParams = new HashMap<>();
739            eventParams.put(ObservationConstants.ARGS_GROUP, userGroup.getIdentity());
740            _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_UPDATED, _currentUserProvider.getUser(), eventParams));
741        }
742        catch (NumberFormatException ex)
743        {
744            throw new InvalidModificationException("No group with id '" + userGroup.getIdentity().getId() + "' may be removed", ex);
745        }
746        catch (SQLException ex)
747        {
748            throw new RuntimeException(ex);
749        }
750        finally
751        {
752            ConnectionHelper.cleanup(statement);
753            ConnectionHelper.cleanup(connection);
754        }
755        
756        if (getLogger().isDebugEnabled())
757        {
758            getLogger().debug("Updated group " + GroupIdentity.groupIdentityToString(userGroup.getIdentity()) + " with " + userGroup.getUsers().size() + " user(s)");
759        }
760    }
761
762    @Override
763    public void remove(String groupID) throws InvalidModificationException
764    {
765        Connection connection = null;
766        PreparedStatement statement = null;
767
768        try
769        {
770            connection = getSQLConnection();
771            String dbType = ConnectionHelper.getDatabaseType(connection);
772            
773            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsListTableName) + " WHERE " + __GROUPS_LIST_COLUMN_ID + " = ?");
774            statement.setInt(1, Integer.parseInt(groupID));
775
776            if (statement.executeUpdate() == 0)
777            {
778                throw new InvalidModificationException("No group with id '" + groupID + "' may be removed");
779            }
780            ConnectionHelper.cleanup(statement);       
781
782            statement = connection.prepareStatement("DELETE FROM " + getSQLDatabaseTypeExtensionPoint().languageEscapeTableName(dbType, _groupsCompositionTableName) + " WHERE " + __GROUPS_COMPOSITION_COLUMN_GROUPID + " = ?");
783            statement.setInt(1, Integer.parseInt(groupID));
784
785            statement.executeUpdate();
786            
787            // Clear global cache
788            _getCacheGroupsByUser().invalidateAll();
789
790            Map<String, Object> eventParams = new HashMap<>();
791            eventParams.put(ObservationConstants.ARGS_GROUP, new GroupIdentity(groupID, getId()));
792            _observationManager.notify(new Event(ObservationConstants.EVENT_GROUP_DELETED, _currentUserProvider.getUser(), eventParams));
793        }
794        catch (NumberFormatException ex)
795        {
796            throw new InvalidModificationException("No group with id '" + groupID + "' may be removed, the ID must be a number.", ex);
797        }
798        catch (SQLException ex)
799        {
800            throw new RuntimeException(ex);
801        }
802        finally
803        {
804            ConnectionHelper.cleanup(statement);       
805            ConnectionHelper.cleanup(connection);       
806        }
807    }
808    
809    private static final class JdbcGroup implements ModifiableGroup
810    {
811        private Set<UserIdentity> _users;
812        private GroupIdentity _identity;
813        private String _groupLabel;
814        
815        @ExcludeFromSizeCalculation
816        private GroupDirectory _groupDirectory;
817        
818        JdbcGroup(GroupIdentity identity, String label, GroupDirectory groupDirectory)
819        {
820            _identity = identity;
821            _groupLabel = label;
822            _groupDirectory = groupDirectory;
823            _users = new HashSet<>();
824        }
825        
826        @Override
827        public GroupIdentity getIdentity()
828        {
829            return _identity;
830        }
831
832        @Override
833        public String getLabel()
834        {
835            return _groupLabel;
836        }
837
838        @Override
839        public GroupDirectory getGroupDirectory()
840        {
841            return _groupDirectory;
842        }
843        
844        @Override
845        public void setLabel(String label)
846        {
847            _groupLabel = label;
848        }
849
850        @Override
851        public void addUser(UserIdentity user)
852        {
853            _users.add(user);
854        }
855
856        @Override
857        public void removeUser(UserIdentity user)
858        {
859            _users.remove(user);
860        }
861        
862        @Override
863        public void removeUsers()
864        {
865            _users.clear();
866        }
867        
868        @Override
869        public Set<UserIdentity> getUsers()
870        {
871            return _users;
872        }
873        
874        @Override
875        public String toString()
876        {
877            StringBuffer sb = new StringBuffer("UserGroup[");
878            sb.append(_identity);
879            sb.append(" (");
880            sb.append(_groupLabel);
881            sb.append(") => ");
882            sb.append(_users.toString());
883            sb.append("]");
884            return sb.toString();
885        }    
886        
887        @Override
888        public boolean equals(Object another)
889        {
890            if (another == null || !(another instanceof JdbcGroup))
891            {
892                return false;
893            }
894            
895            JdbcGroup otherGroup = (JdbcGroup) another;
896            
897            return _identity != null && _identity.equals(otherGroup.getIdentity());
898        }
899        
900        @Override
901        public int hashCode()
902        {
903            return _identity.hashCode();
904        }
905    }
906}