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.userpref; 017 018import java.sql.Connection; 019import java.sql.PreparedStatement; 020import java.sql.ResultSet; 021import java.sql.ResultSetMetaData; 022import java.sql.SQLException; 023import java.sql.Types; 024import java.util.ArrayList; 025import java.util.Date; 026import java.util.HashMap; 027import java.util.List; 028import java.util.Map; 029import java.util.regex.Pattern; 030 031import org.apache.avalon.framework.configuration.Configurable; 032import org.apache.avalon.framework.configuration.Configuration; 033import org.apache.avalon.framework.configuration.ConfigurationException; 034import org.apache.avalon.framework.logger.AbstractLogEnabled; 035import org.apache.avalon.framework.thread.ThreadSafe; 036import org.apache.commons.lang.StringUtils; 037 038import org.ametys.core.datasource.ConnectionHelper; 039import org.ametys.core.user.UserIdentity; 040import org.ametys.core.userpref.DefaultUserPreferencesStorage; 041import org.ametys.core.userpref.UserPreferencesException; 042import org.ametys.core.userpref.UserPreferencesStorage; 043import org.ametys.runtime.config.Config; 044import org.ametys.runtime.parameter.ParameterHelper; 045import org.ametys.runtime.parameter.ParameterHelper.ParameterType; 046 047/** 048 * This class is a JDBC implementation of {@link UserPreferencesStorage}, 049 * which stores preferences in database, one preference by column.<br> 050 * Currently, it only supports storing in string-typed columns (VARCHAR, TEXT, ...), 051 * but allows all preference types by casting them from/to strings when retrieving/getting them.<br> 052 * This component does not impose a table structure. This is a configuration example:<br> 053 * <pre> 054 * <component role="com.mydomain.test.MyDatabaseUserPreferencesStorage"<br> 055 * class="org.ametys.plugins.core.userpref.JdbcPlainUserPreferencesStorage"><br> 056 * <pool>com.mydomain.test.MyPool</pool><br> 057 * <table>MyUserPreferences</table><br> 058 * <loginColumn>user</loginColumn><br> 059 * <contextColumn>context</contextColumn><br> 060 * <columnPattern>^(mypref_\w+)$</columnPattern><br> 061 * <mappings><br> 062 * <mapping prefId="lastname" column="mypref_lastname"/><br> 063 * <mapping prefId="firstname" column="mypref_firstname"/><br> 064 * <mapping prefId="email" column="mypref_email_address"/><br> 065 * </mappings><br> 066 * </component><br> 067 * </pre><br> 068 * Column names must be configured lowercase, both when setting login and context columns and when setting mapping columns. 069 * <br><br> 070 * This class differs from {@link JdbcXmlUserPreferencesStorage} as it does not implement {@link DefaultUserPreferencesStorage}, 071 * and because the latter imposes the DB table structure and stores the preferences as an XML binary. 072 */ 073public class JdbcPlainUserPreferencesStorage extends AbstractLogEnabled implements UserPreferencesStorage, ThreadSafe, Configurable 074{ 075 /** The id of the data source used. */ 076 protected String _dataSourceId; 077 078 /** The database table in which the preferences are stored. */ 079 protected String _databaseTable; 080 081 /** The login column, cannot be null. */ 082 protected String _loginColumn; 083 084 /** The population id column, cannot be null. */ 085 protected String _populationColumn; 086 087 /** The context column, can be null if the database is not context-dependent. */ 088 protected String _contextColumn; 089 090 /** A pattern to filter the columns which correspond to preferences. */ 091 protected Pattern _columnPattern; 092 093 /** Mapping from preference id to column name. */ 094 protected Map<String, String> _prefIdToColumn; 095 096 /** Mapping from column name to preference id. */ 097 protected Map<String, String> _columnToPrefId; 098 099 @Override 100 public void configure(Configuration configuration) throws ConfigurationException 101 { 102 // Data source id 103 Configuration dataSourceConf = configuration.getChild("datasource", false); 104 if (dataSourceConf == null) 105 { 106 throw new ConfigurationException("The 'datasource' configuration node must be defined.", dataSourceConf); 107 } 108 109 String dataSourceConfParam = dataSourceConf.getValue(); 110 String dataSourceConfType = dataSourceConf.getAttribute("type", "config"); 111 112 if (StringUtils.equals(dataSourceConfType, "config")) 113 { 114 _dataSourceId = Config.getInstance().getValueAsString(dataSourceConfParam); 115 } 116 else // expecting type="id" 117 { 118 _dataSourceId = dataSourceConfParam; 119 } 120 121 // The table configuration is mandatory. 122 _databaseTable = configuration.getChild("table").getValue(); 123 // Default to "login". 124 _loginColumn = configuration.getChild("loginColumn").getValue("login").toLowerCase(); 125 // Default to "population" 126 _populationColumn = configuration.getChild("populationColumn").getValue("population").toLowerCase(); 127 // Default to null (no context column). 128 _contextColumn = configuration.getChild("contextColumn").getValue(null); 129 if (_contextColumn != null) 130 { 131 _contextColumn = _contextColumn.toLowerCase(); 132 } 133 134 // Default to null: all columns except the login column and the context column (if any) are preferences. 135 String regex = configuration.getChild("columnPattern").getValue(null); 136 _columnPattern = StringUtils.isBlank(regex) ? null : Pattern.compile(regex, Pattern.CASE_INSENSITIVE); 137 138 // Configure the preference-column mappings. 139 configureMappings(configuration.getChild("mappings")); 140 } 141 142 /** 143 * Configure the mappings from preference ID to column name. 144 * @param configuration the mapping configuration root. 145 * @throws ConfigurationException if an error occurs. 146 */ 147 public void configureMappings(Configuration configuration) throws ConfigurationException 148 { 149 // Store the mappings in both directions. 150 _prefIdToColumn = new HashMap<>(); 151 _columnToPrefId = new HashMap<>(); 152 153 for (Configuration mappingConf : configuration.getChildren("mapping")) 154 { 155 String prefId = mappingConf.getAttribute("prefId"); 156 String column = mappingConf.getAttribute("column").toLowerCase(); 157 158 _prefIdToColumn.put(prefId, column); 159 _columnToPrefId.put(column, prefId); 160 } 161 } 162 163 @Override 164 public Map<String, String> getUnTypedUserPrefs(UserIdentity user, String storageContext, Map<String, String> contextVars) throws UserPreferencesException 165 { 166 Map<String, String> prefs = new HashMap<>(); 167 168 Connection connection = null; 169 PreparedStatement stmt = null; 170 ResultSet rs = null; 171 172 try 173 { 174 connection = ConnectionHelper.getConnection(_dataSourceId); 175 176 StringBuilder query = new StringBuilder(); 177 query.append("SELECT * FROM ").append(_databaseTable).append(" WHERE ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?"); 178 if (StringUtils.isNotBlank(_contextColumn)) 179 { 180 query.append(" AND ").append(_contextColumn).append(" = ?"); 181 } 182 183 stmt = connection.prepareStatement(query.toString()); 184 185 stmt.setString(1, user.getLogin()); 186 stmt.setString(2, user.getPopulationId()); 187 if (StringUtils.isNotBlank(_contextColumn)) 188 { 189 stmt.setString(2, storageContext); 190 } 191 192 rs = stmt.executeQuery(); 193 194 if (rs.next()) 195 { 196 ResultSetMetaData metaData = rs.getMetaData(); 197 198 int colCount = metaData.getColumnCount(); 199 200 for (int col = 1; col <= colCount; col++) 201 { 202 String name = metaData.getColumnName(col).toLowerCase(); 203 204 if (isColumnValid(name)) 205 { 206 int type = metaData.getColumnType(col); 207 String value = getPreferenceValue(rs, col, type); 208 209 if (value != null) 210 { 211 String prefId = _columnToPrefId.containsKey(name) ? _columnToPrefId.get(name) : name; 212 prefs.put(prefId, value); 213 } 214 } 215 } 216 } 217 218 return prefs; 219 } 220 catch (SQLException e) 221 { 222 String message = "Database error trying to access the preferences of user '" + user + "' in context '" + storageContext + "'."; 223 getLogger().error(message, e); 224 throw new UserPreferencesException(message, e); 225 } 226 finally 227 { 228 ConnectionHelper.cleanup(rs); 229 ConnectionHelper.cleanup(stmt); 230 ConnectionHelper.cleanup(connection); 231 } 232 } 233 234 @Override 235 public void removeUserPreferences(UserIdentity user, String storageContext, Map<String, String> contextVars) throws UserPreferencesException 236 { 237 Connection connection = null; 238 PreparedStatement stmt = null; 239 240 try 241 { 242 connection = ConnectionHelper.getConnection(_dataSourceId); 243 244 StringBuilder query = new StringBuilder(); 245 query.append("DELETE FROM ").append(_databaseTable).append(" WHERE ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?"); 246 if (StringUtils.isNotBlank(_contextColumn)) 247 { 248 query.append(" AND ").append(_contextColumn).append(" = ?"); 249 } 250 251 stmt = connection.prepareStatement(query.toString()); 252 253 stmt.setString(1, user.getLogin()); 254 stmt.setString(2, user.getPopulationId()); 255 if (StringUtils.isNotBlank(_contextColumn)) 256 { 257 stmt.setString(2, storageContext); 258 } 259 260 stmt.executeUpdate(); 261 } 262 catch (SQLException e) 263 { 264 String message = "Database error trying to remove preferences for login '" + user + "' in context '" + storageContext + "'."; 265 getLogger().error(message, e); 266 throw new UserPreferencesException(message, e); 267 } 268 finally 269 { 270 ConnectionHelper.cleanup(stmt); 271 ConnectionHelper.cleanup(connection); 272 } 273 } 274 275 @Override 276 public void setUserPreferences(UserIdentity user, String storageContext, Map<String, String> contextVars, Map<String, String> preferences) throws UserPreferencesException 277 { 278 Connection connection = null; 279 try 280 { 281 connection = ConnectionHelper.getConnection(_dataSourceId); 282 283 // Test if the preferences already exist. 284 if (dataExists(connection, user, storageContext)) 285 { 286 updatePreferences(connection, preferences, user, storageContext); 287 } 288 else 289 { 290 insertPreferences(connection, preferences, user, storageContext); 291 } 292 } 293 catch (SQLException e) 294 { 295 String message = "Database error trying to set the preferences of user '" + user + "' in context '" + storageContext + "'."; 296 getLogger().error(message, e); 297 throw new UserPreferencesException(message, e); 298 } 299 finally 300 { 301 ConnectionHelper.cleanup(connection); 302 } 303 } 304 305 @Override 306 public String getUserPreferenceAsString(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException 307 { 308 Connection connection = null; 309 PreparedStatement statement = null; 310 ResultSet rs = null; 311 String value = null; 312 313 try 314 { 315 String column = _prefIdToColumn.containsKey(id) ? _prefIdToColumn.get(id) : id; 316 317 if (isColumnValid(column)) 318 { 319 StringBuilder query = new StringBuilder(); 320 query.append("SELECT ? FROM ").append(_databaseTable).append(" WHERE ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?"); 321 if (StringUtils.isNotBlank(_contextColumn)) 322 { 323 query.append(" AND ").append(_contextColumn).append(" = ?"); 324 } 325 326 connection = ConnectionHelper.getConnection(_dataSourceId); 327 328 statement = connection.prepareStatement(query.toString()); 329 statement.setString(1, column); 330 statement.setString(2, user.getLogin()); 331 statement.setString(3, user.getPopulationId()); 332 if (StringUtils.isNotBlank(_contextColumn)) 333 { 334 statement.setString(4, storageContext); 335 } 336 337 rs = statement.executeQuery(); 338 339 if (rs.next()) 340 { 341 value = rs.getString(1); 342 } 343 } 344 } 345 catch (SQLException e) 346 { 347 String message = "Database error trying to get the preferences of user '" + user + "' in context '" + storageContext + "'."; 348 getLogger().error(message, e); 349 throw new UserPreferencesException(message, e); 350 } 351 finally 352 { 353 ConnectionHelper.cleanup(rs); 354 ConnectionHelper.cleanup(statement); 355 ConnectionHelper.cleanup(connection); 356 } 357 358 return value; 359 } 360 361 @Override 362 public Long getUserPreferenceAsLong(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException 363 { 364 // TODO Single select 365 Long value = null; 366 367 Map<String, String> values = getUnTypedUserPrefs(user, storageContext, contextVars); 368 if (values.containsKey(id)) 369 { 370 value = (Long) ParameterHelper.castValue(values.get(id), ParameterType.LONG); 371 } 372 373 return value; 374 } 375 376 @Override 377 public Date getUserPreferenceAsDate(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException 378 { 379 // TODO Single select 380 Date value = null; 381 382 Map<String, String> values = getUnTypedUserPrefs(user, storageContext, contextVars); 383 if (values.containsKey(id)) 384 { 385 value = (Date) ParameterHelper.castValue(values.get(id), ParameterType.DATE); 386 } 387 388 return value; 389 } 390 391 @Override 392 public Boolean getUserPreferenceAsBoolean(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException 393 { 394 // TODO Single select 395 Boolean value = null; 396 397 Map<String, String> values = getUnTypedUserPrefs(user, storageContext, contextVars); 398 if (values.containsKey(id)) 399 { 400 value = (Boolean) ParameterHelper.castValue(values.get(id), ParameterType.BOOLEAN); 401 } 402 403 return value; 404 } 405 406 @Override 407 public Double getUserPreferenceAsDouble(UserIdentity user, String storageContext, Map<String, String> contextVars, String id) throws UserPreferencesException 408 { 409 // TODO Single select 410 Double value = null; 411 412 Map<String, String> values = getUnTypedUserPrefs(user, storageContext, contextVars); 413 if (values.containsKey(id)) 414 { 415 value = (Double) ParameterHelper.castValue(values.get(id), ParameterType.DOUBLE); 416 } 417 418 return value; 419 } 420 421 /** 422 * Test if the given column corresponds to a preference value. 423 * @param name the column name. 424 * @return true if the column corresponds to a preference value, false otherwise. 425 */ 426 protected boolean isColumnValid(String name) 427 { 428 // Do not return the login column, the context column (if applicable) 429 // and columns not matching the pattern. 430 return !_loginColumn.equalsIgnoreCase(name) 431 && (_contextColumn == null || !_contextColumn.equalsIgnoreCase(name)) 432 && (_columnPattern == null || _columnPattern.matcher(name).matches()); 433 } 434 435 /** 436 * Get a preference value as a String. 437 * @param rs The result set, must be set on the right record. 438 * @param columnIndex The column index. 439 * @param jdbcType The JDBC type. 440 * @return The preference value as a String, can be null. 441 * @throws SQLException if an error occurs. 442 */ 443 protected String getPreferenceValue(ResultSet rs, int columnIndex, int jdbcType) throws SQLException // $CHECKSTYLE:cyclomaticcomplexity 444 { 445 String value = null; 446 447 // TODO Cast if necessary. 448 switch (jdbcType) 449 { 450 case Types.VARCHAR: 451 case Types.NVARCHAR: 452 case Types.LONGVARCHAR: 453 case Types.CHAR: 454 case Types.NCHAR: 455 case Types.INTEGER: 456 case Types.TINYINT: 457 case Types.SMALLINT: 458 case Types.BIGINT: 459 case Types.NUMERIC: 460 case Types.DECIMAL: 461 case Types.DOUBLE: 462 case Types.FLOAT: 463 case Types.REAL: 464 case Types.DATE: 465 case Types.TIME: 466 case Types.TIMESTAMP: 467 value = rs.getString(columnIndex); 468 break; 469 default: 470 break; 471 } 472 473 return value; 474 } 475 476 /** 477 * Test if a record exists for this user and context. 478 * @param connection The database connection. 479 * @param user The user. 480 * @param storageContext The storage context. 481 * @return true if data exists, false otherwise. 482 * @throws SQLException if an error occurs. 483 */ 484 protected boolean dataExists(Connection connection, UserIdentity user, String storageContext) throws SQLException 485 { 486 PreparedStatement stmt = null; 487 ResultSet rs = null; 488 489 try 490 { 491 StringBuilder query = new StringBuilder(); 492 query.append("SELECT count(*) FROM ").append(_databaseTable).append(" WHERE ").append(_loginColumn).append(" = ? AND ").append(_populationColumn).append(" = ?"); 493 if (StringUtils.isNotBlank(_contextColumn)) 494 { 495 query.append(" AND ").append(_contextColumn).append(" = ?"); 496 } 497 498 stmt = connection.prepareStatement(query.toString()); 499 500 stmt.setString(1, user.getLogin()); 501 stmt.setString(2, user.getPopulationId()); 502 if (StringUtils.isNotBlank(_contextColumn)) 503 { 504 stmt.setString(3, storageContext); 505 } 506 507 rs = stmt.executeQuery(); 508 rs.next(); 509 510 return rs.getInt(1) > 0; 511 } 512 finally 513 { 514 ConnectionHelper.cleanup(rs); 515 ConnectionHelper.cleanup(stmt); 516 } 517 } 518 519 /** 520 * Insert preferences into the database. 521 * @param connection The database connection. 522 * @param preferences The preference values, indexed by preference id. 523 * @param user The user. 524 * @param storageContext The preference storage context. 525 * @throws SQLException if an error occurs. 526 */ 527 protected void insertPreferences(Connection connection, Map<String, String> preferences, UserIdentity user, String storageContext) throws SQLException 528 { 529 PreparedStatement stmt = null; 530 531 try 532 { 533 StringBuilder query = new StringBuilder(); 534 StringBuilder values = new StringBuilder(); 535 query.append("INSERT INTO ").append(_databaseTable).append("(").append(_loginColumn).append(", ").append(_populationColumn); 536 values.append("?, ?"); 537 if (StringUtils.isNotBlank(_contextColumn)) 538 { 539 query.append(", ").append(_contextColumn); 540 values.append(", ?"); 541 } 542 543 List<String> valuesToSet = new ArrayList<>(); 544 545 int validPrefCount = 0; 546 for (String prefId : preferences.keySet()) 547 { 548 String column = _prefIdToColumn.containsKey(prefId) ? _prefIdToColumn.get(prefId) : prefId; 549 550 if (isColumnValid(column)) 551 { 552 valuesToSet.add(preferences.get(prefId)); 553 554 query.append(", ").append(column); 555 values.append(", ?"); 556 557 validPrefCount++; 558 } 559 } 560 561 if (validPrefCount > 0) 562 { 563 query.append(") VALUES (").append(values).append(')'); 564 565 int i = 1; 566 567 stmt = connection.prepareStatement(query.toString()); 568 569 stmt.setString(i++, user.getLogin()); 570 stmt.setString(i++, user.getPopulationId()); 571 if (StringUtils.isNotBlank(_contextColumn)) 572 { 573 stmt.setString(i++, storageContext); 574 } 575 576 for (String value : valuesToSet) 577 { 578 stmt.setString(i, value); 579 i++; 580 } 581 582 stmt.executeUpdate(); 583 } 584 } 585 finally 586 { 587 ConnectionHelper.cleanup(stmt); 588 } 589 } 590 591 /** 592 * Update existing preferences. 593 * @param connection The database connection. 594 * @param preferences The preference values, indexed by preference id. 595 * @param user The user. 596 * @param storageContext The preference storage context. 597 * @throws SQLException if an error occurs. 598 */ 599 protected void updatePreferences(Connection connection, Map<String, String> preferences, UserIdentity user, String storageContext) throws SQLException 600 { 601 PreparedStatement stmt = null; 602 603 try 604 { 605 StringBuilder query = new StringBuilder(); 606 query.append("UPDATE ").append(_databaseTable).append(" SET "); 607 608 List<String> valuesToSet = new ArrayList<>(); 609 610 int validPrefCount = 0; 611 for (String prefId : preferences.keySet()) 612 { 613 String column = _prefIdToColumn.containsKey(prefId) ? _prefIdToColumn.get(prefId) : prefId; 614 615 if (isColumnValid(column)) 616 { 617 valuesToSet.add(preferences.get(prefId)); 618 619 if (validPrefCount > 0) 620 { 621 query.append(", "); 622 } 623 624 query.append(" ").append(column).append(" = ?"); 625 626 validPrefCount++; 627 } 628 } 629 630 query.append(" WHERE ").append(_loginColumn).append(" = ?").append(" AND ").append(_populationColumn).append(" = ?"); 631 if (StringUtils.isNotBlank(_contextColumn)) 632 { 633 query.append(" AND ").append(_contextColumn).append(" = ?"); 634 } 635 636 if (validPrefCount > 0) 637 { 638 stmt = connection.prepareStatement(query.toString()); 639 640 int i = 1; 641 for (String value : valuesToSet) 642 { 643 stmt.setString(i, value); 644 i++; 645 } 646 647 stmt.setString(i++, user.getLogin()); 648 stmt.setString(i++, user.getPopulationId()); 649 if (StringUtils.isNotBlank(_contextColumn)) 650 { 651 stmt.setString(i++, storageContext); 652 } 653 654 stmt.executeUpdate(); 655 } 656 } 657 finally 658 { 659 ConnectionHelper.cleanup(stmt); 660 } 661 } 662 663}