Source code

001/**
002 * Copyright 2015 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.contentanalysis;
017
018import java.util.ArrayList;
019import java.util.Collection;
020import java.util.EnumSet;
021import java.util.HashMap;
022import java.util.Iterator;
023import java.util.List;
024import java.util.Map;
025import java.util.Map.Entry;
026
027import org.apache.log4j.Logger;
028import org.springframework.beans.factory.annotation.Autowired;
029import org.springframework.jdbc.core.JdbcTemplate;
030
031import service.tut.pori.contentanalysis.AsyncTask.TaskStatus;
032import core.tut.pori.dao.SQLSelectBuilder;
033import core.tut.pori.dao.SQLSelectBuilder.OrderDirection;
034import core.tut.pori.dao.clause.AndClause;
035import core.tut.pori.dao.clause.SQLClause.SQLType;
036import core.tut.pori.http.parameters.DataGroups;
037import core.tut.pori.http.parameters.Limits;
038import core.tut.pori.utils.MediaUrlValidator.MediaType;
039
040/**
041 * Abstract base class for tasks that use media content.
042 * 
043 */
044public abstract class MediaTaskDAO extends TaskDAO {
045  private static final Logger LOGGER = Logger.getLogger(MediaTaskDAO.class);
046  /* tables */
047  private static final String TABLE_TASK_GUIDS = DATABASE +".ca_tasks_guids";
048  private static final String TABLE_TASK_GUIDS_STATUS = DATABASE +".ca_tasks_guids_status";
049  private static final String TABLE_TASK_MEDIA_OBJECTS = DATABASE +".ca_tasks_media_objects";
050  /* columns */
051  private static final String COLUMN_GUID_TYPE = "type";
052  /* sql scripts */
053  private static final String[] SQL_GET_MEDIA_STATUS_SELECT_COLUMNS = {Definitions.COLUMN_BACKEND_ID, COLUMN_GUID, Definitions.COLUMN_STATUS, COLUMN_MESSAGE};
054
055  private static final int[] SQL_DELETE_GUID_SQL_TYPES = {SQLType.STRING.toInt()};
056  private static final String SQL_DELETE_TASK_GUIDS = "DELETE FROM "+TABLE_TASK_GUIDS+" WHERE "+COLUMN_GUID+"=?";
057  private static final String SQL_DELETE_TASK_GUIDS_STATUS = "DELETE FROM "+TABLE_TASK_GUIDS_STATUS+" WHERE "+COLUMN_GUID+"=?";
058  private static final String SQL_DELETE_TASK_MEDIA_OBJECTS = "DELETE FROM "+TABLE_TASK_MEDIA_OBJECTS+" WHERE "+COLUMN_GUID+"=?";
059
060  private static final String SQL_INSERT_TASK_GUID = "INSERT INTO "+TABLE_TASK_GUIDS+" ("+COLUMN_TASK_ID+", "+COLUMN_GUID+", "+COLUMN_GUID_TYPE+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW())";
061  private static final int[] SQL_INSERT_TASK_GUID_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt(), SQLType.INTEGER.toInt()};
062
063  private static final String SQL_INSERT_TASK_MEDIA_OBJECTS = "INSERT INTO "+TABLE_TASK_MEDIA_OBJECTS+" ("+COLUMN_TASK_ID+", "+COLUMN_GUID+", "+Definitions.COLUMN_MEDIA_OBJECT_ID+","+COLUMN_ROW_CREATED+") VALUES (?,?,?,NOW())";
064  private static final int[] SQL_INSERT_TASK_MEDIA_OBJECTS_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt()};
065
066  private static final String SQL_UPDATE_MEDIA_STATUS = "INSERT INTO "+TABLE_TASK_GUIDS_STATUS+" ("+Definitions.COLUMN_BACKEND_ID+", "+COLUMN_GUID+", "+COLUMN_TASK_ID+", "+Definitions.COLUMN_STATUS+", "+COLUMN_MESSAGE+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?, NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_TASK_ID+"=VALUES("+COLUMN_TASK_ID+"), "+Definitions.COLUMN_STATUS+"=VALUES("+Definitions.COLUMN_STATUS+"), "+COLUMN_MESSAGE+"=VALUES("+COLUMN_MESSAGE+"), "+COLUMN_ROW_UPDATED+"=NOW()";
067  private static final int[] SQL_UPDATE_MEDIA_STATUS_TYPES = {SQLType.INTEGER.toInt(), SQLType.STRING.toInt(), SQLType.LONG.toInt(), SQLType.INTEGER.toInt(), SQLType.STRING.toInt()};
068
069  @Autowired
070  private BackendDAO _backendDAO = null;
071  @Autowired
072  private MediaObjectDAO _mediaObjectDAO = null;
073  
074  /**
075   * the type of GUID in tasks GUIDs table
076   */
077  protected enum GUIDType{
078    /** GUID describes a basic media item */
079    MEDIA(0),
080    /** GUID describes a reference media item */
081    REFERENCE_MEDIA(1),
082    /** GUID describes media item similar with a reference item */
083    SIMILAR_MEDIA(2),
084    /** GUID describes media item dissimilar with a reference item */
085    DISSIMILAR_MEDIA(3),
086    /** GUID describes a deleted media item */
087    DELETED_MEDIA(4);
088
089    private int _value;
090
091    /**
092     * 
093     * @param value
094     */
095    private GUIDType(int value){
096      _value = value;
097    }
098
099    /**
100     * 
101     * @return type as a string
102     */
103    public int toInt(){
104      return _value;
105    }
106
107    /**
108     * 
109     * @param value
110     * @return value converted to type
111     * @throws IllegalArgumentException on bad value
112     */
113    public static GUIDType fromInt(int value) throws IllegalArgumentException {
114      for(GUIDType t : GUIDType.values()){
115        if(t._value == value){
116          return t;
117        }
118      }
119      throw new IllegalArgumentException("Bad "+GUIDType.class.toString()+" : "+value);
120    }
121  } // enum GUIDType
122
123  /**
124   * 
125   * @param limits 
126   * @param taskId
127   * @param type
128   * @return list of GUIDs for the given task, which are of the given type or null if none was found
129   */
130  protected List<String> getTaskGUIDs(Limits limits, Long taskId, GUIDType type) {
131    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_TASK_GUIDS);
132    sql.addSelectColumn(COLUMN_GUID);
133    sql.setLimits(limits);
134    sql.addWhereClause(new AndClause(COLUMN_TASK_ID, taskId, SQLType.LONG));
135    sql.addWhereClause(new AndClause(COLUMN_GUID_TYPE, type.toInt(), SQLType.INTEGER));
136    
137    List<String> guids = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes(), String.class);
138    if(guids.isEmpty()){
139      LOGGER.warn("No types found for task, id: "+taskId);
140      return null;
141    }
142    return guids;
143  } 
144  
145  /**
146   * This will also set photo statuses, if any are present. Note that even through status elements can appear in any photo list,
147   * creating two different lists with identical GUIDs, and conflicting status lists may create undefined behavior.
148   * 
149   * @param media
150   * @param taskId
151   * @param type 
152   */
153  protected <T extends Media> void insertTaskGUIDs(Collection<T> media, Long taskId, GUIDType type){
154    if(media == null || media.isEmpty()){
155      LOGGER.debug("No media for task, id: "+taskId);
156    }else{
157      JdbcTemplate t = getJdbcTemplate();
158      Object[] values = new Object[]{taskId,null,type.toInt()};
159      for(T m : media){
160        values[1] = m.getGUID();
161        t.update(SQL_INSERT_TASK_GUID, values, SQL_INSERT_TASK_GUID_TYPES);
162      }
163      updateMediaStatus(media, taskId);
164    }
165  }
166
167  /**
168   * 
169   * @param guid
170   * @param taskId
171   * @param mediaObjects list of media objects to add
172   */
173  protected void insertTaskMediaObjects(String guid, Long taskId, MediaObjectList mediaObjects){
174    if(MediaObjectList.isEmpty(mediaObjects)){
175      LOGGER.debug("Ignored empty media object list for GUID: "+guid);
176      return;
177    }
178    if(guid == null){
179      LOGGER.debug("Adding task media object without GUID.");
180    }
181    Object[] ob = {taskId, guid, null};
182    for(MediaObject vo : mediaObjects.getMediaObjects()){
183      ob[2] = vo.getMediaObjectId();
184      getJdbcTemplate().update(SQL_INSERT_TASK_MEDIA_OBJECTS, ob, SQL_INSERT_TASK_MEDIA_OBJECTS_TYPES);
185    }
186  }
187
188  /**
189   * 
190   * @param media list of media items
191   * @param taskId optional taskId. This should be (if given) the id of the most recent task.
192   */
193  public <T extends Media> void updateMediaStatus(Collection<T> media, Long taskId){
194    if(media == null || media.isEmpty()){
195      LOGGER.debug("No media given.");
196      return;
197    }
198
199    JdbcTemplate t = getJdbcTemplate();
200    Object[] values = new Object[]{null,null,taskId,null,null};
201    for(Media m : media){
202      values[1] = m.getGUID();
203      BackendStatusList statuses = m.getBackendStatus();
204      if(BackendStatusList.isEmpty(statuses)){
205        LOGGER.debug("No statuses for photo, guid: "+values[1]);
206      }else{
207        for(Iterator<BackendStatus> sIter = statuses.getBackendStatuses().iterator(); sIter.hasNext();){
208          BackendStatus s = sIter.next();
209          values[0] = s.getBackendId();
210          values[3] = s.getStatus().toInt();
211          values[4] = s.getMessage();
212          t.update(SQL_UPDATE_MEDIA_STATUS, values, SQL_UPDATE_MEDIA_STATUS_TYPES);
213        } // for
214      }
215    } // for
216  }
217
218  /**
219   * Removes the list of GUIDs from all tasks
220   * 
221   * @param guids
222   */
223  public void remove(Collection<String> guids){
224    if(guids == null || guids.isEmpty()){
225      LOGGER.debug("Ignored empty guids list.");
226      return;
227    }
228    JdbcTemplate t = getJdbcTemplate();
229    Object[] ob = new Object[1];
230    for(Iterator<String> iter = guids.iterator(); iter.hasNext();){
231      ob[0] = iter.next();
232      t.update(SQL_DELETE_TASK_GUIDS, ob, SQL_DELETE_GUID_SQL_TYPES);
233      t.update(SQL_DELETE_TASK_GUIDS_STATUS, ob, SQL_DELETE_GUID_SQL_TYPES);
234      t.update(SQL_DELETE_TASK_MEDIA_OBJECTS, ob, SQL_DELETE_GUID_SQL_TYPES);
235    }
236  }
237
238  @Override
239  public abstract AbstractTaskDetails getTask(Integer backendId, DataGroups dataGroups, Limits limits, Long taskId) throws IllegalArgumentException;
240
241  /**
242   * 
243   * @param dataGroups
244   * @param limits
245   * @param media
246   * @param mediaTypes media object media types to retrieve
247   * @param taskId
248   */
249  protected <T extends Media> void setMediaObjects(DataGroups dataGroups, Limits limits, Collection<T> media, EnumSet<MediaType> mediaTypes, Long taskId){
250    Map<String, List<String>> GUIDMediaObjectIdMap = getMediaObjectIds(limits, taskId);
251    if(GUIDMediaObjectIdMap == null){
252      return;
253    }
254
255    for(T m : media){
256      List<String> voids = GUIDMediaObjectIdMap.get(m.getGUID());
257      if(voids != null){
258        m.setMediaObjects(_mediaObjectDAO.getMediaObjects(dataGroups, null, mediaTypes, null, voids, null));
259      }
260    }
261  }
262
263  /**
264   * 
265   * @param limits
266   * @param taskId
267   * @return GUID-void map of media object ids or null if none were found for the given task
268   */
269  protected Map<String, List<String>> getMediaObjectIds(Limits limits, Long taskId){
270    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_TASK_MEDIA_OBJECTS);
271    sql.addSelectColumns(new String[]{COLUMN_GUID, Definitions.COLUMN_MEDIA_OBJECT_ID});
272    sql.addOrderBy(Definitions.COLUMN_MEDIA_OBJECT_ID, OrderDirection.ASCENDING);
273    sql.setLimits(limits);
274    sql.addWhereClause(new AndClause(COLUMN_TASK_ID, taskId, SQLType.LONG));
275    
276    List<Map<String,Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(Definitions.ELEMENT_MEDIA_OBJECTLIST), sql.getValues(), sql.getValueTypes());
277    if(rows.isEmpty()){
278      LOGGER.debug("No media objects for task, id: "+taskId);
279      return null;
280    }
281    HashMap<String, List<String>> map = new HashMap<>(rows.size());
282    for(Map<String, Object> row : rows){
283      String guid = (String) row.get(COLUMN_GUID);
284      List<String> voids = map.get(guid);
285      if(voids == null){
286        map.put(guid, (voids = new ArrayList<>()));
287      }
288      voids.add((String) row.get(Definitions.COLUMN_MEDIA_OBJECT_ID));
289    }
290    return map;
291  }
292
293  /**
294   * retrieves the media status information for the list of media-items if available
295   * 
296   * @param mediaList the list to which the status information is to be set. If previous information exists, it is overridden.
297   * @return the passed list
298   */
299  public <T extends Media> Collection<T> getMediaStatus(Collection<T> mediaList){
300    if(mediaList == null || mediaList.isEmpty()){
301      LOGGER.debug("Empty media list.");
302    }else{
303      SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_TASK_GUIDS_STATUS);
304      
305      List<String> guids = new ArrayList<>(mediaList.size());
306      for (Media media : mediaList) {
307        guids.add(media.getGUID());
308      }     
309      sql.addSelectColumns(SQL_GET_MEDIA_STATUS_SELECT_COLUMNS);
310      sql.addWhereClause(new AndClause(COLUMN_GUID, guids.toArray(), SQLType.STRING));
311      List<Map<String,Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(Definitions.ELEMENT_BACKEND_STATUS_LIST), sql.getValues(), sql.getValueTypes());
312      if(rows.isEmpty()){
313        LOGGER.debug("No status for the given media items.");
314        return mediaList;
315      }
316
317      for(Iterator<Map<String, Object>> rIter = rows.iterator(); rIter.hasNext();){
318        Map<String,Object> row = rIter.next();
319        BackendStatus status = extractBackendStatus(row);
320        if(status == null){
321          LOGGER.debug("Ignored status for non-existent backend.");
322          continue;
323        }
324        String guid = (String) row.get(COLUMN_GUID);
325        for(Iterator<? extends Media> pIter = mediaList.iterator(); pIter.hasNext();){
326          Media p = pIter.next();
327          if(guid.equals(p.getGUID())){
328            p.addackendStatus(status);
329          }
330        } // for mediaList
331      } // for rows
332    } // else
333    return mediaList;
334  }
335  
336  /**
337   * extract new BackendStatus from the given row
338   * 
339   * @param row
340   * @return status extracted from the given row map
341   */
342  private BackendStatus extractBackendStatus(Map<String, Object> row){
343    BackendStatus s = new BackendStatus();
344    for(Entry<String, Object> e : row.entrySet()){
345      switch (e.getKey()) {
346        case Definitions.COLUMN_BACKEND_ID:
347          AnalysisBackend end = _backendDAO.getBackend((Integer)e.getValue());
348          if(end == null){
349            LOGGER.warn("Detected non-existent backend.");
350            return null;
351          }
352          s.setBackend(end);
353          break;
354        case Definitions.COLUMN_STATUS:
355          s.setStatus(TaskStatus.fromInt((Integer)e.getValue()));
356          break;
357        case COLUMN_MESSAGE:
358          s.setMessage((String) e.getValue());
359          break;
360        case COLUMN_GUID:   // valid column, but not handled by extractor
361        case COLUMN_TASK_ID:  // valid column, but not handled by extractor
362          break;
363        default:
364          throw new IllegalArgumentException("Unhandeled column: "+e.getKey());
365      }
366    } // for
367    return s;
368  }
369
370  /**
371   * @return the backendDAO
372   */
373  protected BackendDAO getBackendDAO() {
374    return _backendDAO;
375  }
376
377  /**
378   * @return the mediaObjectDAO
379   */
380  protected MediaObjectDAO getMediaObjectDAO() {
381    return _mediaObjectDAO;
382  }
383}