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.twitter;
017
018import java.util.Date;
019import java.util.List;
020import java.util.Map;
021import java.util.Map.Entry;
022
023import org.apache.commons.lang3.StringUtils;
024import org.apache.log4j.Logger;
025import org.springframework.beans.factory.annotation.Autowired;
026import org.springframework.jdbc.core.JdbcTemplate;
027import org.springframework.transaction.TransactionStatus;
028import org.springframework.transaction.support.TransactionCallback;
029
030import service.tut.pori.users.UserDAO;
031import twitter4j.auth.AccessToken;
032import core.tut.pori.dao.clause.SQLClause.SQLType;
033import core.tut.pori.dao.SQLDAO;
034import core.tut.pori.users.ExternalAccountConnection;
035import core.tut.pori.users.UserIdentity;
036import core.tut.pori.users.ExternalAccountConnection.UserServiceType;
037
038/**
039 * DAO for handling authorization tokens for Twitter User Service.
040 */
041public class TwitterUserDAO extends SQLDAO {
042  private static final Logger LOGGER = Logger.getLogger(TwitterUserDAO.class);
043  /* tables */
044  private static final String TABLE_USERS_TWITTER = DATABASE+".users_twitter";
045  private static final String TABLE_USERS_TWITTER_REQUEST_TOKENS = DATABASE+".users_twitter_request_tokens";
046  /* columns */
047  private static final String COLUMN_ACCESS_TOKEN = "access_token";
048  private static final String COLUMN_ACCESS_TOKEN_SECRET = "access_token_secret";
049  private static final String COLUMN_REDIRECT_URI = "redirect_uri";
050  private static final String COLUMN_REQUEST_TOKEN = "request_token";
051  private static final String COLUMN_REQUEST_TOKEN_SECRET = "request_token_secret";
052  /* sql scripts */
053  private static final String SQL_GET_ACCESS_TOKEN = "SELECT "+COLUMN_ACCESS_TOKEN+", "+COLUMN_ACCESS_TOKEN_SECRET+" FROM "+TABLE_USERS_TWITTER+" WHERE "+COLUMN_USER_ID+"=?";
054  
055  private static final String SQL_INSERT_REQUEST_TOKEN = "INSERT INTO "+TABLE_USERS_TWITTER_REQUEST_TOKENS+" ("+COLUMN_USER_ID+", "+COLUMN_REQUEST_TOKEN+", "+COLUMN_REQUEST_TOKEN_SECRET+", "+COLUMN_REDIRECT_URI+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,NOW())";
056  private static final int[] SQL_INSERT_REQUEST_TOKEN_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt()};
057  
058  private static final String SQL_GET_REQUEST_TOKEN = "SELECT "+COLUMN_USER_ID+", "+COLUMN_REQUEST_TOKEN_SECRET+", "+COLUMN_REDIRECT_URI+", "+COLUMN_ROW_UPDATED+" FROM "+TABLE_USERS_TWITTER_REQUEST_TOKENS+" WHERE "+COLUMN_REQUEST_TOKEN+"=?";
059    
060  private static final String SQL_REMOVE_ACCESS_TOKEN_BY_USER = "DELETE FROM "+TABLE_USERS_TWITTER+" WHERE "+COLUMN_USER_ID+"=?";
061  
062  private static final String SQL_REMOVE_REQUEST_TOKEN = "DELETE FROM "+TABLE_USERS_TWITTER_REQUEST_TOKENS+" WHERE "+COLUMN_REQUEST_TOKEN+"=?";
063  
064  private static final String SQL_REMOVE_REQUEST_TOKEN_BY_USER = "DELETE FROM "+TABLE_USERS_TWITTER_REQUEST_TOKENS+" WHERE "+COLUMN_USER_ID+"=?";
065  
066  private static final int[] SQL_REQUEST_TOKEN_SQL_TYPE = {SQLType.STRING.toInt()};
067  
068  /** tokens generated by twitter should be unique, so the unique column constraint on access token should never match on ON DUPLICATE KEY UPDATE for two different users, the match will be solely done on user id */
069  private static final String SQL_SET_ACCESS_TOKEN = "INSERT INTO "+TABLE_USERS_TWITTER+" ("+COLUMN_USER_ID+","+COLUMN_ACCESS_TOKEN+","+COLUMN_ACCESS_TOKEN_SECRET+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_ACCESS_TOKEN+"=VALUES("+COLUMN_ACCESS_TOKEN+"), "+COLUMN_ACCESS_TOKEN_SECRET+"=VALUES("+COLUMN_ACCESS_TOKEN_SECRET+")";
070  private static final int[] SQL_SET_ACCESS_TOKEN_SQL_TYPES = {SQLType.LONG.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt()};
071  
072  private static final String SQL_SET_REQUEST_TOKEN = "INSERT INTO "+TABLE_USERS_TWITTER_REQUEST_TOKENS+" ("+COLUMN_USER_ID+", "+COLUMN_REQUEST_TOKEN+", "+COLUMN_REQUEST_TOKEN_SECRET+", "+COLUMN_REDIRECT_URI+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_REQUEST_TOKEN+"=VALUES("+COLUMN_REQUEST_TOKEN+"), "+COLUMN_REQUEST_TOKEN_SECRET+"=VALUES("+COLUMN_REQUEST_TOKEN_SECRET+"), "+COLUMN_REDIRECT_URI+"=VALUES("+COLUMN_REDIRECT_URI+")";
073  private static final int[] SQL_SET_REQUEST_TOKEN_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt()};
074  
075  private static final int[] SQL_USER_IDENTITY_SQL_TYPE = {SQLType.LONG.toInt()};
076  
077  @Autowired
078  private UserDAO _userDAO = null;
079  
080  /**
081   * 
082   * @param token
083   */
084  public void setRequestToken(RequestToken token) {
085    Long userId = token.getUserIdValue();
086    if(userId == null){ // do not use on duplicate update for null, so that a new row will always be added
087      getJdbcTemplate().update(SQL_INSERT_REQUEST_TOKEN, new Object[]{userId, token.getToken(), token.getSecret(), token.getRedirectUri()}, SQL_INSERT_REQUEST_TOKEN_SQL_TYPES);
088    }else{
089      getJdbcTemplate().update(SQL_SET_REQUEST_TOKEN, new Object[]{userId, token.getToken(), token.getSecret(), token.getRedirectUri()}, SQL_SET_REQUEST_TOKEN_SQL_TYPES);
090    }
091  }
092
093  /**
094   * This will remove both the access token and the request token associated with the given user (if any)
095   * 
096   * @param userIdentity valid user identity
097   */
098  public void removeTokens(UserIdentity userIdentity) {
099    Object[] ob = {userIdentity.getUserId()};
100    JdbcTemplate t = getJdbcTemplate();
101    if(t.update(SQL_REMOVE_ACCESS_TOKEN_BY_USER, ob, SQL_USER_IDENTITY_SQL_TYPE) < 1){
102      LOGGER.debug("No access tokens removed for user, id: "+ob[0]);
103    }
104    if(t.update(SQL_REMOVE_REQUEST_TOKEN_BY_USER, ob, SQL_USER_IDENTITY_SQL_TYPE) < 1){
105      LOGGER.debug("No request tokens removed for user, id: "+ob[0]);
106    }
107  }
108
109  /**
110   * 
111   * @param token the request token value
112   * @return the fully populated request token or null if not found
113   */
114  public RequestToken getRequestToken(String token) {
115    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(SQL_GET_REQUEST_TOKEN, new Object[]{token}, SQL_REQUEST_TOKEN_SQL_TYPE);
116    if(rows.isEmpty()){
117      LOGGER.debug("The request token was not found.");
118      return null;
119    }
120    RequestToken requestToken = extractRequestToken(rows.get(0));
121    requestToken.setToken(token);
122    return requestToken;
123  }
124  
125  /**
126   * 
127   * @param row
128   * @return the token or null on failure
129   */
130  private RequestToken extractRequestToken(Map<String, Object> row){
131    RequestToken token = new RequestToken();
132    for(Entry<String, Object> e : row.entrySet()){
133      String column = e.getKey();
134      switch(column){
135        case COLUMN_USER_ID:
136          token.setUserId(new UserIdentity((Long) e.getValue()));
137          break;
138        case COLUMN_REQUEST_TOKEN:
139          token.setToken((String) e.getValue());
140          break;
141        case COLUMN_REQUEST_TOKEN_SECRET:
142          token.setSecret((String) e.getValue());
143          break;
144        case COLUMN_REDIRECT_URI:
145          token.setRedirectUri((String) e.getValue());
146          break;
147        case COLUMN_ROW_UPDATED:
148          token.setUpdated((Date) e.getValue());
149          break;
150        default:
151          LOGGER.warn("Ignored unknown column: "+column);
152          break;
153      }
154    }
155    return token;
156  }
157
158  /**
159   * 
160   * @param token
161   */
162  public void removeRequestToken(RequestToken token) {
163    removeRequestToken(token.getToken());
164  }
165  
166  /**
167   * 
168   * @param requestToken
169   */
170  private void removeRequestToken(String requestToken) {
171    if(getJdbcTemplate().update(SQL_REMOVE_REQUEST_TOKEN, new Object[]{requestToken}, SQL_REQUEST_TOKEN_SQL_TYPE) < 1){
172      LOGGER.debug("No token removed, request token: "+requestToken);
173    }
174  }
175
176  /**
177   * 
178   * @param authorizedUser a valid user
179   * @return access token for the given user or null if none was found
180   */
181  public AccessToken getAccessToken(UserIdentity authorizedUser) {
182    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(SQL_GET_ACCESS_TOKEN, new Object[]{authorizedUser.getUserId()}, SQL_USER_IDENTITY_SQL_TYPE);
183    if(rows.isEmpty()){
184      LOGGER.debug("No access token found for user, id: "+authorizedUser.getUserId());
185      return null;
186    }
187    
188    Map<String, Object> row = rows.get(0);
189    return new AccessToken((String)row.get(COLUMN_ACCESS_TOKEN), (String) row.get(COLUMN_ACCESS_TOKEN_SECRET));
190  }
191
192  /**
193   * 
194   * @param twitterUserId
195   * @param token
196   * @param userId
197   * @return true on success
198   */
199  public boolean setAccessToken(final String twitterUserId, final AccessToken token, final UserIdentity userId) {
200    if(!UserIdentity.isValid(userId) || token == null || StringUtils.isBlank(twitterUserId)){
201      LOGGER.debug("Invalid userId or token.");
202      return false;
203    }
204    
205    return getTransactionTemplate().execute(new TransactionCallback<Boolean>() {
206
207      @Override
208      public Boolean doInTransaction(TransactionStatus status) {      
209        JdbcTemplate t = getJdbcTemplate();
210        
211        String oldTwitterUserId = getTwitterUserId(userId);
212        if(oldTwitterUserId == null){
213          LOGGER.debug("Creating a new external account connection for user, id: "+userId.getUserId()+", google user id: "+twitterUserId);
214          _userDAO.insertExternalAccountConnection(new ExternalAccountConnection(twitterUserId, UserServiceType.TWITTER), userId);
215        }else if(!oldTwitterUserId.equals(twitterUserId)){ // do not allow changing the twitter user, this may cause undefined behavior on other parts of the system
216          LOGGER.warn("The given twitter user Id "+twitterUserId+" did not match the old twitter user id: "+oldTwitterUserId);
217          return false;
218        }
219        
220        t.update(SQL_SET_ACCESS_TOKEN, new Object[]{userId.getUserId(),token.getToken(),token.getTokenSecret()}, SQL_SET_ACCESS_TOKEN_SQL_TYPES);
221        
222        return true;
223      }
224    });
225  }
226  
227  /**
228   * 
229   * @param userId non-null, valid userId
230   * @return twitter user id for the given userId, if known by the system, otherwise returns null
231   */
232  public String getTwitterUserId(UserIdentity userId){
233    ExternalAccountConnection connection = _userDAO.getExternalAccountConnection(userId, UserServiceType.TWITTER);
234    return (connection == null ? null : connection.getExternalId());
235  }
236}