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.sql.ResultSet;
019import java.sql.SQLException;
020import java.util.ArrayList;
021import java.util.Collection;
022import java.util.Date;
023import java.util.HashMap;
024import java.util.HashSet;
025import java.util.List;
026import java.util.Map;
027import java.util.Set;
028
029import org.apache.log4j.Logger;
030import org.springframework.jdbc.core.RowCallbackHandler;
031
032import core.tut.pori.dao.SQLDAO;
033import core.tut.pori.dao.SQLSelectBuilder;
034import core.tut.pori.dao.clause.AndClause;
035import core.tut.pori.dao.clause.SQLClause.SQLType;
036
037/**
038 * Used to associate media objects to photos.
039 * 
040 * Note that even though it is possible to retrieve an instance of this class through DAOHandler, it is not recommended to use this DAO to directly modify media object relations. An attempt to do so may cause undefined behavior.
041 */
042public class AssociationDAO extends SQLDAO {
043  private static final Logger LOGGER = Logger.getLogger(AssociationDAO.class);
044  private static final String TABLE_ASSOCIATIONS = DATABASE+".ca_media_object_associations";
045  /* sql scripts */
046  private static final int[] SQL_ASSOCIATE_SQL_TYPES = new int[]{SQLType.STRING.toInt(), SQLType.STRING.toInt()};
047  private static final String[] SQL_ASSOCIATION_COLUMNS = new String[]{COLUMN_GUID, Definitions.COLUMN_MEDIA_OBJECT_ID};
048  private static final String SQL_DEASSOCIATE = "DELETE FROM "+TABLE_ASSOCIATIONS+" WHERE "+COLUMN_GUID+"=? AND "+Definitions.COLUMN_MEDIA_OBJECT_ID+"=?";
049  private static final int[] SQL_DEASSOCIATE_SQL_TYPES = SQL_ASSOCIATE_SQL_TYPES;
050  private static final String SQL_DEASSOCIATE_BY_GUID = "DELETE FROM "+TABLE_ASSOCIATIONS+" WHERE "+COLUMN_GUID+"=?";
051  private static final String SQL_DEASSOCIASTE_BY_MEDIA_OBJECT_ID = "DELETE FROM "+TABLE_ASSOCIATIONS+" WHERE "+Definitions.COLUMN_MEDIA_OBJECT_ID+"=?";
052  private static final int[] SQL_DEASSOCIATE_BY_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
053
054  /**
055   * 
056   * @param mediaList
057   */
058  public void associate(Collection<? extends Media> mediaList) {
059    if(mediaList == null || mediaList.isEmpty()){
060      LOGGER.debug("Empty media list.");
061      return;
062    }
063    StringBuilder sql = new StringBuilder("INSERT INTO "+TABLE_ASSOCIATIONS+" ("+COLUMN_GUID+","+Definitions.COLUMN_MEDIA_OBJECT_ID+", "+COLUMN_ROW_CREATED+") VALUES ");
064    List<Object> objects = new ArrayList<>();
065    Date updated = new Date();
066    for(Media media : mediaList){ // this is method is generally called when external accounts are synced, and the amount to of photos can be quite high, i.e. it is much faster to parse a new sql string manually than use template
067      MediaObjectList mediaObjects = media.getMediaObjects();
068      String guid = media.getGUID();
069      if(!MediaObjectList.isEmpty(mediaObjects)){
070        for(MediaObject o : mediaObjects.getMediaObjects()){
071          sql.append("(?,?,?),");
072          objects.add(guid);
073          objects.add(o.getMediaObjectId());
074          objects.add(updated);
075        }
076      }
077    }
078    if(objects.isEmpty()){
079      LOGGER.debug("No objects to associate.");
080      return;
081    }
082
083    sql.setLength(sql.length()-1);  // chop the extra ,
084    getJdbcTemplate().update(sql.toString(), objects.toArray()); // this will duplicate previously existing associations, but that's ok for now
085  }
086
087  /**
088   * GUID and/or mediaObjectId must be given.
089   * 
090   * @param guid if null, the associations between the given mediaObjectId and ANY GUID will be removed
091   * @param mediaObjectId if null, the associations between the given GUID and ANY mediaObjectId will be removed
092   */
093  public void deassociate(String guid, String mediaObjectId){
094    // select delete sql by the given parameters, note that it does not matter if both parameters are null, the database does not allow null values so the update will just do nothing on bad (null) values.
095    if(guid == null){
096      getJdbcTemplate().update(SQL_DEASSOCIASTE_BY_MEDIA_OBJECT_ID, new Object[]{mediaObjectId}, SQL_DEASSOCIATE_BY_SQL_TYPES);
097    }else if(mediaObjectId == null){
098      getJdbcTemplate().update(SQL_DEASSOCIATE_BY_GUID, new Object[]{guid}, SQL_DEASSOCIATE_BY_SQL_TYPES);
099    }else{ // mediaObjectId != null && guid != null
100      getJdbcTemplate().update(SQL_DEASSOCIATE, new Object[]{guid, mediaObjectId}, SQL_DEASSOCIATE_SQL_TYPES);
101    }
102  }
103
104  /**
105   * 
106   * @param guids
107   * @return GUID-mediaobject id relation map or null if none found or if guids was null or empty
108   */
109  public Map<String, Set<String>> getAssociationsForGUIDs(Collection<String> guids){
110    if(guids == null || guids.isEmpty()){
111      LOGGER.debug("No GUIDs.");
112      return null;
113    }
114    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_ASSOCIATIONS);
115    sql.addSelectColumns(SQL_ASSOCIATION_COLUMNS);
116    sql.addWhereClause(new AndClause(COLUMN_GUID, guids, SQLType.STRING));
117    return extractMap(sql);
118  }
119
120  /**
121   * helper for extracting guid-void map from the given builder
122   * 
123   * @param builder
124   * @return guid-void map
125   */
126  private Map<String, Set<String>> extractMap(SQLSelectBuilder builder){
127    final Map<String, Set<String>> map = new HashMap<>();
128    getJdbcTemplate().query(builder.toSQLString(), builder.getValues(), builder.getValueTypes(), new RowCallbackHandler() {   
129      @Override
130      public void processRow(ResultSet set) throws SQLException {
131        String guid = set.getString(COLUMN_GUID);
132        Set<String> voids = map.get(guid);
133        if(voids == null){
134          voids = new HashSet<>();
135          map.put(guid, voids);
136        }
137        voids.add(set.getString(Definitions.COLUMN_MEDIA_OBJECT_ID));
138      }
139    });
140
141    return (map.isEmpty() ? null : map);
142  }
143
144  /**
145   * 
146   * @param mediaObjectIds
147   * @return guid-media object map or null if no associations found
148   */
149  public Map<String, Set<String>> getAssociationsForMediaObjectIds(Collection<String> mediaObjectIds){
150    if(mediaObjectIds == null || mediaObjectIds.isEmpty()){
151      LOGGER.debug("No media object ids.");
152      return null;
153    }
154
155    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_ASSOCIATIONS);
156    final List<String> guids = new ArrayList<>();
157    sql.addSelectColumn(COLUMN_GUID);
158    sql.addWhereClause(new AndClause(Definitions.COLUMN_MEDIA_OBJECT_ID, mediaObjectIds, SQLType.STRING));
159    sql.addGroupBy(COLUMN_GUID);
160    getJdbcTemplate().query(sql.toSQLString(), sql.getValues(), sql.getValueTypes(), new RowCallbackHandler() {
161      @Override
162      public void processRow(ResultSet set) throws SQLException {
163        guids.add(set.getString(COLUMN_GUID));
164      }
165    });
166
167    return getAssociationsForGUIDs(guids);
168  }
169}