001/*
002 *  Copyright 2019 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.odfweb.cart;
017
018import java.sql.Connection;
019import java.sql.PreparedStatement;
020import java.sql.ResultSet;
021import java.sql.SQLException;
022import java.time.ZonedDateTime;
023import java.util.ArrayList;
024import java.util.Collection;
025import java.util.HashMap;
026import java.util.List;
027import java.util.Map;
028
029import org.apache.avalon.framework.component.Component;
030import org.apache.avalon.framework.configuration.Configurable;
031import org.apache.avalon.framework.configuration.Configuration;
032import org.apache.avalon.framework.configuration.ConfigurationException;
033import org.apache.commons.lang3.StringUtils;
034import org.apache.commons.lang3.Strings;
035
036import org.ametys.core.datasource.ConnectionHelper;
037import org.ametys.core.user.UserIdentity;
038import org.ametys.core.userpref.UserPreferencesException;
039import org.ametys.core.userpref.UserPreferencesStorage;
040import org.ametys.runtime.config.Config;
041import org.ametys.runtime.plugin.component.AbstractLogEnabled;
042
043
044/**
045 * Specific storage for odf cart user preferences
046 */
047public class ODFCartUserPreferencesStorage extends AbstractLogEnabled implements UserPreferencesStorage, Configurable, Component
048{
049    /** The Avalon Role */
050    public static final String ROLE = ODFCartUserPreferencesStorage.class.getName();
051    
052    /** The id of the data source used. */
053    protected String _dataSourceId;
054    
055    /** The login column, cannot be null. */
056    protected String _loginColumn;
057    
058    /** The population id column, cannot be null. */
059    protected String _populationColumn;
060    
061    /** The context column, can be null if the database is not context-dependent. */
062    protected String _contextColumn;
063    
064    /** The content id column */
065    protected String _contentIdColumn;
066    
067    /** Mapping from preference id to table name. */
068    protected Map<String, String> _prefIdToTable;
069
070    @Override
071    public void configure(Configuration configuration) throws ConfigurationException
072    {
073        // Data source id
074        Configuration dataSourceConf = configuration.getChild("datasource", false);
075        if (dataSourceConf == null)
076        {
077            throw new ConfigurationException("The 'datasource' configuration node must be defined.", dataSourceConf);
078        }
079        
080        String dataSourceConfParam = dataSourceConf.getValue();
081        String dataSourceConfType = dataSourceConf.getAttribute("type", "config");
082        
083        if (Strings.CS.equals(dataSourceConfType, "config"))
084        {
085            _dataSourceId = Config.getInstance().getValue(dataSourceConfParam);
086        }
087        else // expecting type="id"
088        {
089            _dataSourceId = dataSourceConfParam;
090        }
091        
092        // Default to "contentId".
093        _contentIdColumn = configuration.getChild("content-id").getValue("contentId");
094        // Default to "login".
095        _loginColumn = configuration.getChild("loginColumn").getValue("login").toLowerCase();
096        // Default to "population"
097        _populationColumn = configuration.getChild("populationColumn").getValue("population").toLowerCase();
098        // Default to 'context' (no context column).
099        _contextColumn = configuration.getChild("contextColumn").getValue("context");
100        
101        // Configure the preference-table mappings.
102        configureMappings(configuration.getChild("mappings"));
103    }
104    
105    /**
106     * Configure the mappings from preference ID to table name.
107     * @param configuration the mapping configuration root.
108     * @throws ConfigurationException if an error occurs.
109     */
110    public void configureMappings(Configuration configuration) throws ConfigurationException
111    {
112        _prefIdToTable = new HashMap<>();
113        
114        for (Configuration mappingConf : configuration.getChildren("mapping"))
115        {
116            String prefId = mappingConf.getAttribute("prefId");
117            String table = mappingConf.getAttribute("table");
118            
119            _prefIdToTable.put(prefId, table);
120        }
121    }
122    
123    public Map<UserIdentity, Map<String, String>> getAllUnTypedUserPrefs(String storageContext, Map<String, String> contextVars) throws UserPreferencesException
124    {
125        Map<UserIdentity, Map<String, String>> allUserPrefs = new HashMap<>();
126        
127        for (String id : _prefIdToTable.keySet())
128        {
129            Map<UserIdentity, List<String>> preferences = _getUserPreferences(null, storageContext, id);
130            for (UserIdentity user : preferences.keySet())
131            {
132                Map<String, String> userPrefs = allUserPrefs.computeIfAbsent(user, __ -> new HashMap<>());
133                userPrefs.put(id, StringUtils.join(preferences.get(user), ","));
134            }
135        }
136        
137        return allUserPrefs;
138    }
139    
140    private Map<UserIdentity, List<String>> _getUserPreferences(UserIdentity user, String storageContext, String id) throws UserPreferencesException
141    {
142        Map<UserIdentity, List<String>> userResults = new HashMap<>();
143
144        Connection connection = null;
145        PreparedStatement statement = null;
146        ResultSet rs = null;
147        String table = _prefIdToTable.get(id);
148        
149        try
150        {
151            StringBuilder query = new StringBuilder();
152            query.append("SELECT ").append(" * FROM ").append(table).append(" WHERE ").append(_contextColumn).append(" = ?");
153            
154            if (user != null)
155            {
156                query.append(" AND ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?");
157            }
158            
159            connection = ConnectionHelper.getConnection(_dataSourceId);
160            
161            statement = connection.prepareStatement(query.toString());
162            
163            int index = 1;
164            statement.setString(index, storageContext);
165            index++;
166            
167            if (user != null)
168            {
169                statement.setString(index, user.getLogin());
170                index++;
171                statement.setString(index, user.getPopulationId());
172            }
173            
174            rs = statement.executeQuery();
175            
176            while (rs.next())
177            {
178                UserIdentity u = _getUserIdentity(rs);
179                List<String> results = userResults.computeIfAbsent(u, __ -> new ArrayList<>());
180                results.add(rs.getString(_contentIdColumn));
181            }
182        }
183        catch (SQLException e)
184        {
185            String message = user == null
186                ? "Database error trying to get ODF cart all user' preferences in context '" + storageContext + "'."
187                : "Database error trying to get ODF cart preferences of user '" + user + "' in context '" + storageContext + "'.";
188            getLogger().error(message, e);
189            throw new UserPreferencesException(message, e);
190        }
191        finally
192        {
193            ConnectionHelper.cleanup(rs);
194            ConnectionHelper.cleanup(statement);
195            ConnectionHelper.cleanup(connection);
196        }
197        
198        return userResults;
199    }
200    
201    private UserIdentity _getUserIdentity(ResultSet rs) throws SQLException
202    {
203        String login = rs.getString(_loginColumn);
204        String population = rs.getString(_populationColumn);
205        return new UserIdentity(login, population);
206    }
207    
208    @Override
209    public Map<String, String> getUnTypedUserPrefs(UserIdentity user, String storageContext, Map<String, String> contextVars) throws UserPreferencesException
210    {
211        Map<String, String> prefs = new HashMap<>();
212        
213        for (String id : _prefIdToTable.keySet())
214        {
215            prefs.put(id, getUserPreferenceAsString(user, storageContext, contextVars, id));
216        }
217        
218        return prefs;
219    }
220    
221    /**
222     * Remove the stored user preferences for a login in a given context.
223     * @param prefIds the id of user preferences to remove
224     * @param user the user.
225     * @param storageContext the preferences storage context.
226     * @param contextVars the context variables.
227     * @throws UserPreferencesException if an error occurred
228     */
229    public void removeUserPreferences(Collection<String> prefIds, UserIdentity user, String storageContext, Map<String, String> contextVars) throws UserPreferencesException
230    {
231        Connection connection = null;
232        try
233        {
234            connection = ConnectionHelper.getConnection(_dataSourceId);
235            for (String id : prefIds)
236            {
237                _removeUserPreferencesFromTable(connection, user, storageContext, _prefIdToTable.get(id));
238            }
239        }
240        catch (SQLException e)
241        {
242            String message = "Database error trying to remove ODF cart preferences for user '" + user + "' in context '" + storageContext + "'.";
243            getLogger().error(message, e);
244            throw new UserPreferencesException(message, e);
245        }
246        finally
247        {
248            ConnectionHelper.cleanup(connection);
249        }
250    }
251    
252    @Override
253    public void removeUserPreferences(UserIdentity user, String storageContext, Map<String, String> contextVars) throws UserPreferencesException
254    {
255        Connection connection = null;
256        try
257        {
258            connection = ConnectionHelper.getConnection(_dataSourceId);
259            for (String id : _prefIdToTable.keySet())
260            {
261                _removeUserPreferencesFromTable(connection, user, storageContext, _prefIdToTable.get(id));
262            }
263        }
264        catch (SQLException e)
265        {
266            String message = "Database error trying to remove ODF cart preferences for user '" + user + "' in context '" + storageContext + "'.";
267            getLogger().error(message, e);
268            throw new UserPreferencesException(message, e);
269        }
270        finally
271        {
272            ConnectionHelper.cleanup(connection);
273        }
274    }
275    
276    private void _removeUserPreferencesFromTable(Connection connection, UserIdentity user, String storageContext, String table) throws SQLException
277    {
278        PreparedStatement stmt = null;
279        
280        try
281        {
282            StringBuilder query = new StringBuilder();
283            query.append("DELETE FROM ").append(table).append(" WHERE ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?");
284            if (storageContext != null)
285            {
286                query.append(" AND ").append(_contextColumn).append(" = ?");
287            }
288            
289            stmt = connection.prepareStatement(query.toString());
290            stmt.setString(1, user.getLogin());
291            stmt.setString(2, user.getPopulationId());
292            if (storageContext != null)
293            {
294                stmt.setString(3, storageContext);
295            }
296            
297            stmt.executeUpdate();
298        }
299        finally
300        {
301            ConnectionHelper.cleanup(stmt);
302        }
303        
304    }
305
306    /**
307     * Remove content from all user preferences
308     * @param contentId the content id to remove
309     * @throws UserPreferencesException if failed to remove user preferences
310     */
311    public void removeContentFromUserPreferences(String contentId) throws UserPreferencesException
312    {
313        Connection connection = null;
314        PreparedStatement stmt = null;
315        
316        try
317        {
318            connection = ConnectionHelper.getConnection(_dataSourceId);
319            
320            StringBuilder query = new StringBuilder();
321            query.append("DELETE FROM ").append(_prefIdToTable.get(ODFCartManager.CART_USER_PREF_CONTENT_IDS)).append(" WHERE ").append(_contentIdColumn).append(" like ?");
322            
323            stmt = connection.prepareStatement(query.toString());
324            
325            stmt.setString(1, contentId + "%");
326            stmt.executeUpdate();
327            
328            ConnectionHelper.cleanup(stmt);
329            
330            query = new StringBuilder();
331            query.append("DELETE FROM ").append(_prefIdToTable.get(ODFCartManager.SUBSCRIPTION_USER_PREF_CONTENT_IDS)).append(" WHERE ").append(_contentIdColumn).append(" like ?");
332            
333            stmt = connection.prepareStatement(query.toString());
334            
335            stmt.setString(1, contentId + "%");
336            stmt.executeUpdate();
337            
338        }
339        catch (SQLException e)
340        {
341            String message = "Database error trying to remove ODF content of id '" + contentId + "' from all user's preferences";
342            getLogger().error(message, e);
343            throw new UserPreferencesException(message, e);
344        }
345        finally
346        {
347            ConnectionHelper.cleanup(stmt);
348            ConnectionHelper.cleanup(connection);
349        }
350    }
351
352    @Override
353    public void setUserPreferences(UserIdentity user, String storageContext, Map<String, String> contextVars, Map<String, String> preferences) throws UserPreferencesException
354    {
355        removeUserPreferences(preferences.keySet(), user, storageContext, contextVars);
356        
357        Connection connection = null;
358        try
359        {
360            connection = ConnectionHelper.getConnection(_dataSourceId);
361            _insertPreferences(connection, preferences, user, storageContext);
362        }
363        catch (SQLException e)
364        {
365            String message = "Database error trying to set ODF cart preferences of user '" + user + "' in context '" + storageContext + "'.";
366            getLogger().error(message, e);
367            throw new UserPreferencesException(message, e);
368        }
369        finally
370        {
371            ConnectionHelper.cleanup(connection);
372        }
373    }
374    
375    private void _insertPreferences(Connection connection, Map<String, String> preferences, UserIdentity user, String storageContext) throws SQLException
376    {
377        for (String id : preferences.keySet())
378        {
379            String table = _prefIdToTable.get(id);
380            
381            String[] contentIdsTab = StringUtils.split(preferences.get(id), ",");
382            
383            if (contentIdsTab != null && contentIdsTab.length > 0)
384            {
385                PreparedStatement stmt = null;
386                try
387                {
388                    StringBuilder query = new StringBuilder();
389                    query.append("INSERT INTO ").append(table).append("(").append(_loginColumn).append(", ").append(_populationColumn);
390                    query.append(", ").append(_contextColumn);
391                    query.append(", ").append(_contentIdColumn);
392    
393                    StringBuilder values = new StringBuilder();
394                    for (int i = 0; i < contentIdsTab.length; i++)
395                    {
396                        if (i != 0)
397                        {
398                            values.append(",");
399                        }
400                        values.append("(?, ?, ?, ?)");
401                    }
402                    
403                    query.append(") VALUES ").append(values);
404                    
405                    int i = 1;
406                    stmt = connection.prepareStatement(query.toString());
407                    
408                    for (String idContent : contentIdsTab)
409                    {
410                        stmt.setString(i++, user.getLogin());
411                        stmt.setString(i++, user.getPopulationId());
412                        stmt.setString(i++, storageContext);
413                        stmt.setString(i++, idContent);
414                    }
415                    
416                    stmt.executeUpdate();
417                }
418                finally
419                {
420                    ConnectionHelper.cleanup(stmt);
421                }
422            }
423        }
424    }
425
426    @Override
427    public String getUserPreferenceAsString(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException
428    {
429        if (user == null)
430        {
431            return null;
432        }
433        
434        Map<UserIdentity, List<String>> userPreferences = _getUserPreferences(user, storageContext, id);
435        List<String> values = userPreferences.getOrDefault(user, null);
436        return values != null ? StringUtils.join(values, ",") : null;
437    }
438    
439    @Override
440    public Long getUserPreferenceAsLong(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException
441    {
442        return null;
443    }
444
445    @Override
446    public ZonedDateTime getUserPreferenceAsDate(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException
447    {
448        return null;
449    }
450
451    @Override
452    public Boolean getUserPreferenceAsBoolean(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException
453    {
454        return null;
455    }
456
457    @Override
458    public Double getUserPreferenceAsDouble(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException
459    {
460        return null;
461    }
462
463}