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.contentstorage;
017
018import java.util.ArrayList;
019import java.util.Collection;
020import java.util.LinkedList;
021import java.util.List;
022import java.util.Map;
023import java.util.Map.Entry;
024
025import org.apache.log4j.Logger;
026
027import service.tut.pori.contentstorage.TwitterPhotoStorage.TwitterEntry;
028import core.tut.pori.dao.clause.AndClause;
029import core.tut.pori.dao.clause.SQLClause.SQLType;
030import core.tut.pori.dao.SQLDAO;
031import core.tut.pori.dao.SQLDeleteBuilder;
032import core.tut.pori.dao.SQLSelectBuilder;
033import core.tut.pori.users.UserIdentity;
034
035/**
036 * DAO for saving and retrieving Twitter content entries.
037 *
038 */
039public class TwitterDAO extends SQLDAO {
040  private static final Logger LOGGER = Logger.getLogger(TwitterDAO.class);
041  /* tables */
042  private static final String TABLE_ENTRIES = DATABASE+".ca_twitter_entries";
043  /* columns */
044  private static final String COLUMN_ENTITY_ID = "entity_id";
045  private static final String COLUMN_ENTITY_URL = "entity_url";
046  private static final String COLUMN_SCREEN_NAME = "screen_name";
047  /* sql scripts */
048  private static final String SQL_CREATE_ENTRY = "INSERT INTO "+TABLE_ENTRIES+" ("+COLUMN_GUID+", "+COLUMN_ENTITY_ID+", "+COLUMN_ENTITY_URL+", "+COLUMN_SCREEN_NAME+", "+COLUMN_USER_ID+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,NOW())";
049  private static final int[] SQL_CREATE_ENTRY_SQL_TYPES = new int[]{SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.LONG.toInt()};
050  
051  private static final String[] SQL_SELECT_COLUMNS = {COLUMN_GUID, COLUMN_USER_ID, COLUMN_ENTITY_ID, COLUMN_ENTITY_URL, COLUMN_SCREEN_NAME};
052  
053  private static final String SQL_GET_URL = "SELECT "+COLUMN_COUNT+", "+COLUMN_ENTITY_URL+" FROM "+TABLE_ENTRIES+" WHERE "+COLUMN_GUID+"=?"; // add count to force result
054  private static final int[] SQL_GET_URL_SQL_TYPES = {SQLType.STRING.toInt()};
055
056  /**
057   * 
058   * @param entityIds optional list of entity ids used as a filter
059   * @param userId
060   * @return list of entries or null if none was found
061   */
062  public List<TwitterEntry> getEntriesByEntityId(List<String> entityIds, UserIdentity userId) {
063    if(!UserIdentity.isValid(userId)){
064      LOGGER.warn("Invalid userId.");
065      return null;
066    }
067    
068    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_ENTRIES);
069    sql.addSelectColumns(SQL_SELECT_COLUMNS);
070    sql.addWhereClause(new AndClause(COLUMN_USER_ID, userId.getUserId(), SQLType.LONG));
071    if(entityIds != null && !entityIds.isEmpty()){
072      sql.addWhereClause(new AndClause(COLUMN_ENTITY_ID, entityIds, SQLType.STRING));
073    }
074    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
075    
076    if(rows.isEmpty()){
077      LOGGER.debug("No results.");
078      return null;
079    }
080    
081    List<TwitterEntry> entries = new LinkedList<>();
082    for(Map<String, Object> row : rows){
083      entries.add(extractEntry(row));
084    }
085    
086    return entries;
087  }
088
089  /**
090   * 
091   * @param row
092   * @return the entry extracted from the given row map
093   */
094  private TwitterEntry extractEntry(Map<String, Object> row) {
095    TwitterEntry te = new TwitterEntry();
096    for(Entry<String, Object> e : row.entrySet()){
097      String column = e.getKey();
098      switch(column){
099        case COLUMN_GUID:
100          te.setGUID((String) e.getValue());
101          break;
102        case COLUMN_USER_ID:
103          te.setUserId(new UserIdentity((Long) e.getValue()));
104          break;
105        case COLUMN_ENTITY_ID:
106          te.setEntityId((String)e.getValue());
107          break;
108        case COLUMN_ENTITY_URL:
109          te.setEntityUrl((String) e.getValue());
110          break;
111        case COLUMN_SCREEN_NAME:
112          te.setScreenName((String) e.getValue());
113          break;
114        default:
115          LOGGER.warn("Ignored unknown column: "+column);
116          break;
117      }
118    }
119    return te;
120  }
121
122  /**
123   * 
124   * @param screenNames optional screen name filter, if null or empty, the filter is ignored
125   * @param userId
126   * @return list of entries or null if none was found
127   */
128  public List<TwitterEntry> getEntriesByScreenName(Collection<String> screenNames, UserIdentity userId) {
129    if(!UserIdentity.isValid(userId)){
130      LOGGER.warn("Invalid userId.");
131      return null;
132    }
133  
134    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_ENTRIES);
135    sql.addSelectColumns(SQL_SELECT_COLUMNS);
136    sql.addWhereClause(new AndClause(COLUMN_USER_ID, userId.getUserId(), SQLType.LONG));
137    if(screenNames != null && !screenNames.isEmpty()){
138      LOGGER.debug("Adding screen name filter...");
139      sql.addWhereClause(new AndClause(COLUMN_SCREEN_NAME, screenNames, SQLType.STRING));
140    }
141    
142    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
143    if(rows.isEmpty()){
144      LOGGER.debug("No results.");
145      return null;
146    }
147    
148    List<TwitterEntry> entries = new ArrayList<>(rows.size());
149    for(Map<String, Object> row : rows){
150      entries.add(extractEntry(row));
151    }
152    return entries;
153  }
154
155  /**
156   * 
157   * @param guid
158   * @return static URL for the given GUID or null if not found
159   */
160  public String getUrl(String guid) {
161    Map<String, Object> rows = getJdbcTemplate().queryForMap(SQL_GET_URL, new Object[]{guid}, SQL_GET_URL_SQL_TYPES);
162    return (String) rows.get(COLUMN_ENTITY_URL);
163  }
164
165  /**
166   * 
167   * @param guids
168   */
169  public void removeEntries(Collection<String> guids) {
170    if(guids != null && !guids.isEmpty()){
171      SQLDeleteBuilder sql = new SQLDeleteBuilder(TABLE_ENTRIES);
172      sql.addWhereClause(new AndClause(COLUMN_GUID, guids, SQLType.STRING));
173      LOGGER.debug("Removed entries count: "+getJdbcTemplate().update(sql.toSQLString(), sql.getValues(), sql.getValueTypes()));
174    }else{
175      LOGGER.debug("Empty GUID list.");
176    }
177  }
178
179  /**
180   * 
181   * @param entries
182   */
183  public void createEntries(Collection<TwitterEntry> entries) {
184    if(entries == null || entries.isEmpty()){
185      LOGGER.debug("Ignored empty entry list.");
186      return;
187    }
188    Object[] ob = new Object[5];
189    for(TwitterEntry e : entries){
190      ob[0] = e.getGUID();
191      ob[1] = e.getEntityId();
192      ob[2] = e.getEntityUrl();
193      ob[3] = e.getScreenName();
194      ob[4] = e.getUserId().getUserId();
195      getJdbcTemplate().update(SQL_CREATE_ENTRY, ob, SQL_CREATE_ENTRY_SQL_TYPES);
196    }
197  }
198}