Source code

001/**
002 * Copyright 2014 Tampere University of Technology, Pori Department
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 service.tut.pori.users;
017
018import java.util.Collection;
019import java.util.EnumSet;
020import java.util.HashMap;
021import java.util.List;
022import java.util.Map;
023import java.util.Map.Entry;
024
025import org.apache.commons.lang3.StringUtils;
026import org.apache.log4j.Logger;
027import org.springframework.jdbc.core.JdbcTemplate;
028import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
029import org.springframework.security.core.GrantedAuthority;
030import org.springframework.transaction.TransactionStatus;
031import org.springframework.transaction.support.TransactionCallback;
032
033import core.tut.pori.dao.clause.AndClause;
034import core.tut.pori.dao.clause.SQLClause.SQLType;
035import core.tut.pori.dao.SQLDAO;
036import core.tut.pori.dao.SQLSelectBuilder;
037import core.tut.pori.users.ExternalAccountConnection;
038import core.tut.pori.users.UserAuthority;
039import core.tut.pori.users.ExternalAccountConnection.UserServiceType;
040import core.tut.pori.users.UserIdentity;
041
042/**
043 * DAO for retrieving user details, and for creating new users. This class can also be used to modify existing users as well as list and modify user's external account connections.
044 *
045 */
046public class UserDAO extends SQLDAO{
047  private static final Logger LOGGER = Logger.getLogger(UserDAO.class);
048  /* tables */
049  private static final String TABLE_USERS = DATABASE+".users";
050  private static final String TABLE_USERS_EXTERNAL_IDS = DATABASE+".users_external_ids";
051  private static final String TABLE_USERS_ROLES = DATABASE+".users_roles";
052  /* columns */
053  private static final String COLUMN_EXTERNAL_ID = "external_id";
054  private static final String COLUMN_PASSWORD_HASH = "password_hash";
055  private static final String COLUMN_ROLE = "role";
056  private static final String COLUMN_USERNAME = "username";
057  private static final String COLUMN_USER_SERVICE_ID = "user_service_id";
058  /* sql strings */
059  private static final String SQL_ADD_USER_ROLE = "INSERT INTO "+TABLE_USERS_ROLES+" ("+COLUMN_USER_ID+", "+COLUMN_ROLE+", "+COLUMN_ROW_CREATED+") VALUES (?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_ROW_UPDATED+"=NOW()";
060  private static final int[] SQL_ADD_USER_ROLE_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt()};
061  
062  private static final String SQL_DELETE_EXTERNAL_ACCOUNT_CONNECTION = "DELETE FROM "+TABLE_USERS_EXTERNAL_IDS+" WHERE "+COLUMN_USER_ID+"=? AND "+COLUMN_USER_SERVICE_ID+"=?";
063  private static final int[] SQL_DELETE_EXTERNAL_ACCOUNT_CONNECTION_TYPES = {SQLType.LONG.toInt(), SQLType.INTEGER.toInt()};
064  
065  private static final String[] SQL_GET_EXTERNAL_ACCOUNT_CONNECTIONS_COLUMNS = {COLUMN_EXTERNAL_ID, COLUMN_USER_SERVICE_ID};
066  
067  private static final String SQL_GET_EXTERNAL_ID = "SELECT "+COLUMN_EXTERNAL_ID+" FROM "+TABLE_USERS_EXTERNAL_IDS+" WHERE "+COLUMN_USER_ID+"=? AND "+COLUMN_USER_SERVICE_ID+"=? LIMIT 1";
068  private static final int[] SQL_GET_EXTERNAL_ID_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.INTEGER.toInt()};
069  
070  private static final String SQL_GET_USER_ID = "SELECT "+COLUMN_COUNT+", "+COLUMN_USER_ID+" FROM "+TABLE_USERS_EXTERNAL_IDS+" WHERE "+COLUMN_EXTERNAL_ID+"=? AND "+COLUMN_USER_SERVICE_ID+"=? LIMIT 1"; // count added to force result
071  private static final int[] SQL_GET_USER_ID_SQL_TYPES = {SQLType.STRING.toInt(), SQLType.INTEGER.toInt()};
072  
073  private static final String SQL_GET_USER_ROLES = "SELECT "+COLUMN_ROLE+" FROM "+TABLE_USERS_ROLES+" WHERE "+COLUMN_USER_ID+"=?";
074  private static final int[] SQL_GET_USER_ROLES_SQL_TYPES = {SQLType.LONG.toInt()};
075  
076  private static final String SQL_INSERT_EXTERNAL_ID = "INSERT INTO "+TABLE_USERS_EXTERNAL_IDS+" ("+COLUMN_USER_ID+", "+COLUMN_EXTERNAL_ID+", "+COLUMN_USER_SERVICE_ID+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW())";
077  private static final int[] SQL_INSERT_EXTERNAL_ID_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt(), SQLType.INTEGER.toInt()};
078  
079  private static final String SQL_REMOVE_EXTERNAL_IDS_BY_USER_ID = "DELETE FROM "+TABLE_USERS_EXTERNAL_IDS+" WHERE "+COLUMN_USER_ID+"=?";
080  private static final String SQL_REMOVE_USER = "DELETE FROM "+TABLE_USERS+" WHERE "+COLUMN_USER_ID+"=?";
081  private static final String SQL_REMOVE_USER_ROLES = "DELETE FROM "+TABLE_USERS_ROLES+" WHERE "+COLUMN_USER_ID+"=?";
082  private static final int[] SQL_REMOVE_USER_SQL_TYPES = {SQLType.LONG.toInt()};
083  
084  private static final String[] SQL_SELECT_COLUMNS_GET_USERS = {COLUMN_USER_ID, COLUMN_USERNAME, COLUMN_PASSWORD_HASH};
085
086  private static final String SQL_SELECT_BY_USERNAME = "SELECT "+COLUMN_COUNT+","+StringUtils.join(SQL_SELECT_COLUMNS_GET_USERS, ',')+" FROM "+TABLE_USERS+" WHERE "+COLUMN_USERNAME+"=? LIMIT 1";
087  private static final int[] SQL_SELECT_BY_USER_NAME_SQL_TYPES = {SQLType.STRING.toInt()};
088  
089  private static final String SQL_SELECT_BY_USER_ID = "SELECT "+COLUMN_COUNT+", "+StringUtils.join(SQL_SELECT_COLUMNS_GET_USERS, ',')+" FROM "+TABLE_USERS+" WHERE "+COLUMN_USER_ID+"=? LIMIT 1";
090  private static final int[] SQL_SELECT_BY_USER_ID_SQL_TYPES = {SQLType.LONG.toInt()};
091  
092  private static final String[] SQL_COLUMNS_ADD_USER = {COLUMN_USERNAME, COLUMN_PASSWORD_HASH, COLUMN_ROW_CREATED};
093  
094  /**
095   * 
096   * @param username
097   * @return the user or null if not found
098   */
099  public UserIdentity getUser(String username) {
100    LOGGER.debug("Searching user by username...");
101    UserIdentity userIdentity = extractUserIdentity(getJdbcTemplate().queryForMap(SQL_SELECT_BY_USERNAME, new Object[]{username}, SQL_SELECT_BY_USER_NAME_SQL_TYPES));
102    resolveRoles(userIdentity);
103    return userIdentity;
104  }
105  
106  /**
107   * 
108   * @param userId
109   * @return the user or null if not found
110   */
111  public UserIdentity getUser(Long userId) {
112    LOGGER.debug("Searching user by user id...");
113    UserIdentity userIdentity = extractUserIdentity(getJdbcTemplate().queryForMap(SQL_SELECT_BY_USER_ID, new Object[]{userId}, SQL_SELECT_BY_USER_ID_SQL_TYPES));
114    resolveRoles(userIdentity);
115    return userIdentity;
116  }
117  
118  /**
119   * Note: this will NOT resolve user roles
120   * 
121   * @param userIds
122   * @return list of matching user identities or null if none
123   */
124  public UserIdentityList getUsers(long[] userIds){
125    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_USERS);
126    sql.addSelectColumns(SQL_SELECT_COLUMNS_GET_USERS);
127    sql.addWhereClause(new AndClause(COLUMN_USER_ID, userIds));
128    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
129    if(rows.isEmpty()){
130      return null;
131    }
132    UserIdentityList list = new UserIdentityList();
133    for(Map<String,Object> row : rows){
134      list.addUserId(extractUserIdentity(row));
135    }
136    if(UserIdentityList.isEmpty(list)){
137      LOGGER.warn("Row count was !=0, but failed to extract any results.");
138      return null;
139    }
140    return list;
141  }
142  
143  /**
144   * Resolve and set user roles for the given user id. This is a helper method.
145   * 
146   * @param userId
147   */
148  private void resolveRoles(UserIdentity userId){
149    if(!UserIdentity.isValid(userId)){
150      LOGGER.debug("Ignored invalid user identity.");
151      return;
152    }
153    
154    for(String role : getJdbcTemplate().queryForList(SQL_GET_USER_ROLES, new Object[]{userId.getUserId()}, SQL_GET_USER_ROLES_SQL_TYPES, String.class)){
155      userId.addAuthority(UserAuthority.getGrantedAuthority(role));
156    }
157  }
158  
159  /**
160   * 
161   * @param connection
162   * @return UserIdentity with the id value set or null if none is found
163   */
164  public UserIdentity getUserId(ExternalAccountConnection connection){
165    Long userId = (Long) getJdbcTemplate().queryForMap(SQL_GET_USER_ID, new Object[]{connection.getExternalId(), connection.getServiceType().toInt()}, SQL_GET_USER_ID_SQL_TYPES).get(COLUMN_USER_ID);
166    if(userId == null){
167      return null;
168    }else{
169      return new UserIdentity(userId);
170    }
171  }
172  
173  /**
174   * 
175   * @param connection
176   * @param userId
177   * @throws IllegalArgumentException
178   */
179  public void insertExternalAccountConnection(final ExternalAccountConnection connection, final UserIdentity userId) throws IllegalArgumentException{
180    getTransactionTemplate().execute(new TransactionCallback<Void>() {
181      @Override
182      public Void doInTransaction(TransactionStatus status) {
183        Long userIdValue = userId.getUserId();
184        String externalId = connection.getExternalId();
185        UserServiceType userServiceType = connection.getServiceType();
186        
187        UserIdentity existing = getUserId(connection);
188        if(existing != null){
189          if(UserIdentity.equals(userId, existing)){
190            LOGGER.debug("Id already connected for the given user.");
191            return null;
192          }else{
193            LOGGER.debug("Cannot connect external id: "+externalId+" of "+UserServiceType.class.toString()+" : "+userServiceType.name()+" for user, id: "+userIdValue+". Already connected with another account, id: "+existing.getUserId());
194            throw new IllegalArgumentException("Failed to connect account, bad external id.");
195          }
196        }
197        getJdbcTemplate().update(SQL_INSERT_EXTERNAL_ID, new Object[]{userIdValue, externalId, userServiceType.toInt()}, SQL_INSERT_EXTERNAL_ID_SQL_TYPES);
198        return null;
199      }
200    });
201  }
202  
203  /**
204   * Remove the user and all external id connections.
205   * 
206   * @param userId
207   * @return true if user of the given id was removed
208   */
209  public boolean removeUser(UserIdentity userId){
210    Object[] ob = new Object[]{userId.getUserId()};
211    JdbcTemplate t = getJdbcTemplate();
212    if(t.update(SQL_REMOVE_USER, ob, SQL_REMOVE_USER_SQL_TYPES) != 1){
213      LOGGER.warn("Nothing was removed.");
214      return false;
215    }
216    if(t.update(SQL_REMOVE_USER_ROLES, ob, SQL_REMOVE_USER_SQL_TYPES) < 1){
217      LOGGER.warn("No roles were removed.");
218    }
219    LOGGER.debug("Removed "+t.update(SQL_REMOVE_EXTERNAL_IDS_BY_USER_ID, ob, SQL_REMOVE_USER_SQL_TYPES)+" external ids for user, id: "+ob[0]);
220    return true;
221  }
222  
223  /**
224   * On success, the passed userId will have the generated userId value set.
225   * 
226   * @param userId must have username, password and role set
227   * @return true on success, generally means invalid username, as generic database errors will throw an exception
228   * @throws IllegalArgumentException on bad userId
229   */
230  public boolean addUser(final UserIdentity userId) throws IllegalArgumentException{
231    final Collection<? extends GrantedAuthority> authorities = userId.getAuthorities();
232    if(authorities == null || authorities.isEmpty()){ // there must be at least one authority
233      throw new IllegalArgumentException("Invalid authorities.");
234    }
235    
236    return getTransactionTemplate().execute(new TransactionCallback<Boolean>() {
237      @Override
238      public Boolean doInTransaction(TransactionStatus status) {
239        String username = userId.getUsername();
240        UserIdentity check = getUser(username);
241        if(check != null){
242          LOGGER.debug("Username already in use: "+username);
243          return false;
244        }
245        
246        SimpleJdbcInsert userInsert = new SimpleJdbcInsert(getJdbcTemplate());
247        userInsert.withTableName(TABLE_USERS);
248        userInsert.setGeneratedKeyName(COLUMN_USER_ID);
249        userInsert.usingColumns(SQL_COLUMNS_ADD_USER);
250        userInsert.withoutTableColumnMetaDataAccess();
251        
252        HashMap<String, Object> parameters = new HashMap<>(SQL_COLUMNS_ADD_USER.length);
253        parameters.put(COLUMN_USERNAME, userId.getUsername());
254        parameters.put(COLUMN_PASSWORD_HASH, userId.getPassword());
255        parameters.put(COLUMN_ROW_CREATED, null);
256        Number key = userInsert.executeAndReturnKey(parameters);
257        if(key == null){
258          LOGGER.error("Failed to add new user.");
259          return false;
260        }
261        
262        userId.setUserId(key.longValue());
263        for(GrantedAuthority authority : authorities){
264          addUserRole(userId, authority);
265        }
266        
267        return true;
268      }
269    });
270  }
271  
272  /**
273   * Helper method for adding a new user role. Duplicate roles are ignored (no-op).
274   * 
275   * Note: this will NOT check for the existence of the given user.
276   * 
277   * @param userId
278   * @param authority
279   */
280  private void addUserRole(UserIdentity userId, GrantedAuthority authority){
281    getJdbcTemplate().update(SQL_ADD_USER_ROLE, new Object[]{userId.getUserId(), authority.getAuthority()}, SQL_ADD_USER_ROLE_SQL_TYPES);
282  }
283
284  /**
285   * extracts the contents of the given row
286   * 
287   * @param row
288   * @return the user or null if null or empty map was passed
289   */
290  private UserIdentity extractUserIdentity(Map<String,Object> row){
291    if(row.isEmpty()){
292      LOGGER.debug("Row contains no columns.");
293      return null;
294    }
295    
296    UserIdentity userId = new UserIdentity();
297    for(Entry<String,Object> entry : row.entrySet()){
298      switch(entry.getKey()){
299        case COLUMN_USER_ID:
300          userId.setUserId((Long) entry.getValue());
301          break;
302        case COLUMN_USERNAME:
303          userId.setUsername((String) entry.getValue());
304          break;
305        case COLUMN_PASSWORD_HASH:
306          userId.setPassword((String) entry.getValue());
307          break;
308        default:        
309          if(checkCountColumn(entry.getKey(), entry.getValue()) < 1){ // this should be count(*)
310            LOGGER.debug("Unknown column name, or no results.");
311            return null;
312          }
313          break;
314      }
315    }
316    return userId;
317  }
318  
319  /**
320   * 
321   * @param serviceTypes optional service type filter
322   * @param userId
323   * @return list of connections or null if none found
324   */
325  public ExternalAccountConnectionList getExternalAccountConnections(EnumSet<UserServiceType> serviceTypes, UserIdentity userId) {
326    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_USERS_EXTERNAL_IDS);
327    sql.addSelectColumns(SQL_GET_EXTERNAL_ACCOUNT_CONNECTIONS_COLUMNS);
328    sql.addWhereClause(new AndClause(COLUMN_USER_ID, userId.getUserId(), SQLType.LONG));
329    if(serviceTypes != null && !serviceTypes.isEmpty()){
330      sql.addWhereClause(new AndClause(COLUMN_USER_SERVICE_ID, UserServiceType.toInt(serviceTypes)));
331    }
332    
333    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
334    if(rows.isEmpty()){
335      return null;
336    }
337    ExternalAccountConnectionList list = new ExternalAccountConnectionList();
338    for(Map<String, Object> row : rows){
339      list.addConnection(extractExternalAccountConnection(row));
340    }
341    
342    if(ExternalAccountConnectionList.isEmpty(list)){
343      LOGGER.warn("Row count was !=0, but failed to extract any results.");
344      return null;
345    }else{
346      return list;
347    }
348  }
349  
350  /**
351   * 
352   * @param userId
353   * @param serviceType
354   * @return the matching external account connection or null if none found
355   */
356  public ExternalAccountConnection getExternalAccountConnection(UserIdentity userId, UserServiceType serviceType){
357    List<String> externalIds = getJdbcTemplate().queryForList(SQL_GET_EXTERNAL_ID, new Object[]{userId.getUserId(), serviceType.toInt()}, SQL_GET_EXTERNAL_ID_SQL_TYPES, String.class);
358    if(externalIds.isEmpty()){
359      LOGGER.debug("No external ids found for user, id: "+userId.getUserId());
360      return null;
361    }
362    return new ExternalAccountConnection(externalIds.get(0), serviceType);
363  }
364  
365  /**
366   * 
367   * @param row
368   * @return the connection or null if extraction was not possible
369   */
370  private ExternalAccountConnection extractExternalAccountConnection(Map<String, Object> row){
371    if(row.isEmpty()){
372      LOGGER.debug("Row contains no columns.");
373      return null;
374    }
375    
376    ExternalAccountConnection connection = new ExternalAccountConnection();
377    for(Entry<String, Object> e : row.entrySet()){
378      switch(e.getKey()){
379        case COLUMN_EXTERNAL_ID:
380          connection.setExternalId((String) e.getValue());
381          break;
382        case COLUMN_USER_SERVICE_ID:
383          connection.setServiceType(UserServiceType.fromInt((Integer)e.getValue()));
384          break;
385        default:
386          if(checkCountColumn(e.getKey(), e.getValue()) < 1){ // this should be count(*)
387            LOGGER.debug("Unknown column name, or no results.");
388            return null;
389          }
390          break;  
391      } // switch
392    } // for
393    return connection;
394  }
395
396  /**
397   * 
398   * @param userServiceType
399   * @param userId
400   * @return true if connection was removed. Note that false means that the connection did not exist, on database error an appropriate exception will be thrown.
401   */
402  public boolean deleteExternalAccountConnection(UserServiceType userServiceType, UserIdentity userId) {
403    return (getJdbcTemplate().update(SQL_DELETE_EXTERNAL_ACCOUNT_CONNECTION, new Object[]{userId.getUserId(), userServiceType.toInt()}, SQL_DELETE_EXTERNAL_ACCOUNT_CONNECTION_TYPES) > 0);
404  }
405}