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.EnumSet;
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.URLContentStorage.URLEntry;
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;
034import core.tut.pori.utils.MediaUrlValidator.MediaType;
035
036/**
037 * DAO used for storing and retrieving URL content.
038 */
039public class URLContentDAO extends SQLDAO {
040  private static final Logger LOGGER = Logger.getLogger(URLContentDAO.class);
041  /* tables */
042  private static final String TABLE_URLS = DATABASE+".ca_url_storage_entries";
043  /* columns */
044  private static final String COLUMN_MEDIA_TYPE = "media_type";
045  private static final String COLUMN_URL = "url";
046  /* sql scripts */
047  private static final String[] COLUMNS_ENTRY = {COLUMN_USER_ID, COLUMN_MEDIA_TYPE, COLUMN_URL, COLUMN_GUID};
048  
049  private static final String SQL_INSERT_URL = "INSERT INTO "+TABLE_URLS+" ("+COLUMN_GUID+", "+COLUMN_MEDIA_TYPE+", "+COLUMN_URL+", "+COLUMN_USER_ID+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,NOW())";
050  private static final int[] SQL_INSERT_URL_SQL_TYPES = {SQLType.STRING.toInt(), SQLType.INTEGER.toInt(), SQLType.STRING.toInt(), SQLType.LONG.toInt()};
051  
052  private static final String SQL_GET_URL = "SELECT "+COLUMN_COUNT+","+COLUMN_URL+" FROM "+TABLE_URLS+" WHERE "+COLUMN_GUID+"=?"; // add count to force result
053  private static final int[] SQL_GET_URL_SQL_TYPES = {SQLType.STRING.toInt()};
054  
055  /**
056   * 
057   * @param guids optional GUID filter
058   * @param mediaTypes optional media type filter
059   * @param urls optional url filter
060   * @param userId
061   * @return list of entries or null if none available
062   */
063  public List<URLEntry> getEntries(Collection<String> guids, EnumSet<MediaType> mediaTypes, Collection<String> urls, UserIdentity userId){
064    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_URLS);
065    sql.addSelectColumns(COLUMNS_ENTRY);
066    sql.addWhereClause(new AndClause(COLUMN_USER_ID, userId.getUserId(), SQLType.LONG));
067    
068    if(guids != null && !guids.isEmpty()){
069      LOGGER.debug("Adding GUID filter...");
070      sql.addWhereClause(new AndClause(COLUMN_GUID, guids, SQLType.STRING));
071    }
072    
073    if(urls != null && !urls.isEmpty()){
074      LOGGER.debug("Adding URL filter...");
075      sql.addWhereClause(new AndClause(COLUMN_URL, urls, SQLType.STRING));
076    }
077    
078    if(mediaTypes != null && !mediaTypes.isEmpty()){
079      LOGGER.debug("Adding MediaType filter...");
080      sql.addWhereClause(new AndClause(COLUMN_MEDIA_TYPE, MediaType.toInt(mediaTypes)));
081    }
082    
083    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
084    int count = rows.size();
085    if(count < 1){
086      LOGGER.debug("No known entries.");
087      return null;
088    }
089    
090    List<URLEntry> entries = new ArrayList<>();
091    for(Map<String, Object> row : rows){
092      entries.add(extractEntry(row));
093    }
094    
095    return entries;
096  }
097  
098  /**
099   * 
100   * @param row
101   * @return URL entry extracted from the given database row
102   */
103  private URLEntry extractEntry(Map<String, Object> row){
104    URLEntry ue = new URLEntry();
105    for(Entry<String, Object> e : row.entrySet()){
106      String column = e.getKey();
107      switch(column){
108        case COLUMN_GUID:
109          ue.setGUID((String) e.getValue());
110          break;
111        case COLUMN_USER_ID:
112          ue.setUserId(new UserIdentity((Long) e.getValue()));
113          break;
114        case COLUMN_MEDIA_TYPE:
115          ue.setMediaType(MediaType.fromInt((int) e.getValue()));
116          break;
117        case COLUMN_URL:
118          ue.setUrl((String) e.getValue());
119          break;
120        default:
121          LOGGER.warn("Ignored unknown column: "+column);
122          break;
123      }
124    }
125    return ue;
126  }
127  
128  /**
129   * @param entry
130   */
131  public void addEntry(URLEntry entry){
132    getJdbcTemplate().update(SQL_INSERT_URL, new Object[]{entry.getGUID(), entry.getMediaType().toInt(), entry.getUrl(), entry.getUserId().getUserId()}, SQL_INSERT_URL_SQL_TYPES);
133  }
134  
135  /**
136   * 
137   * @param guid
138   * @return the URL or null if not found
139   */
140  public String getUrl(String guid){
141    return (String) getJdbcTemplate().queryForMap(SQL_GET_URL, new Object[]{guid}, SQL_GET_URL_SQL_TYPES).get(COLUMN_URL);
142  }
143
144  /**
145   * 
146   * @param guids
147   */
148  public void removeEntries(Collection<String> guids) {
149    if(guids == null || guids.isEmpty()){
150      LOGGER.debug("Ignored empty GUID list.");
151      return;
152    }
153    
154    SQLDeleteBuilder sql = new SQLDeleteBuilder(TABLE_URLS);
155    sql.addWhereClause(new AndClause(COLUMN_GUID, guids, SQLType.STRING));
156    LOGGER.debug("Urls removed: "+sql.execute(getJdbcTemplate()));
157  }
158}