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