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.Iterator;
021import java.util.List;
022import java.util.Map;
023import java.util.Map.Entry;
024
025import org.apache.log4j.Logger;
026import org.springframework.jdbc.core.JdbcTemplate;
027
028import service.tut.pori.contentstorage.PicasaCloudStorage.PicasaEntry;
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;
033
034/**
035 * DAO for saving and retrieving Picasa content entries.
036 */
037public class PicasaDAO extends SQLDAO{
038  private static final Logger LOGGER = Logger.getLogger(PicasaDAO.class);
039  /* tables */
040  private static final String TABLE_PICASA_ENTRIES = DATABASE+".ca_picasa_entries";
041  /* columns */
042  private static final String COLUMN_ALBUM_ID = "album_id";
043  private static final String COLUMN_GOOGLE_USER_ID = "google_user_id";
044  private static final String COLUMN_PHOTO_ID = "photo_id";
045  private static final String COLUMN_STATIC_URL = "static_url";
046  /* sql scripts */
047  private static final String SQL_CREATE_ENTRY = "INSERT INTO "+TABLE_PICASA_ENTRIES+" ("+COLUMN_GUID+", "+COLUMN_ALBUM_ID+", "+COLUMN_GOOGLE_USER_ID+", "+COLUMN_PHOTO_ID+", "+COLUMN_STATIC_URL+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,NOW())";
048  private static final int[] SQL_CREATE_ENTRY_SQL_TYPES = new int[]{SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt(), SQLType.STRING.toInt()};
049
050  private static final String SQL_GET_ENTRY_BY_GUID = "SELECT "+COLUMN_COUNT+", "+COLUMN_GUID+", "+COLUMN_ALBUM_ID+", "+COLUMN_PHOTO_ID+", "+COLUMN_GOOGLE_USER_ID+", "+COLUMN_STATIC_URL+" FROM "+TABLE_PICASA_ENTRIES+" WHERE "+COLUMN_GUID+"=?";
051  private static final int[] SQL_GET_ENTRY_BY_GUID_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
052
053  private static final String SQL_GET_ENTRY_BY_GOOGLE_ID = "SELECT "+COLUMN_GUID+", "+COLUMN_ALBUM_ID+", "+COLUMN_PHOTO_ID+", "+COLUMN_GOOGLE_USER_ID+" FROM "+TABLE_PICASA_ENTRIES+" WHERE "+COLUMN_GOOGLE_USER_ID+"=?";
054  private static final int[] SQL_GET_ENTRY_BY_GOOGLE_ID_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
055
056  private static final String SQL_UPDATE_ENTRY = "UPDATE "+TABLE_PICASA_ENTRIES+" SET "+COLUMN_ALBUM_ID+"=?, "+COLUMN_PHOTO_ID+"=?, "+COLUMN_GOOGLE_USER_ID+"=?, "+COLUMN_STATIC_URL+"=?, "+COLUMN_ROW_UPDATED+"=NOW() WHERE "+COLUMN_GUID+"=?";
057  private static final int[] SQL_UPDATE_ENTRY_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt()};
058
059
060  /**
061   * 
062   * @param guid
063   * @return the entry or null if none
064   */
065  public PicasaEntry getEntry(String guid){
066    List<Map<String,Object>> rows = getJdbcTemplate().queryForList(SQL_GET_ENTRY_BY_GUID, new Object[]{guid}, SQL_GET_ENTRY_BY_GUID_SQL_TYPES);
067    if(rows.isEmpty()){
068      return null;
069    }else{
070      return extractEntry(rows.get(0));
071    }
072  }
073
074  /**
075   * 
076   * @param googleUserId
077   * @return list of entries or null if none
078   */
079  public List<PicasaEntry> getEntries(String googleUserId){
080    List<Map<String,Object>> rows = getJdbcTemplate().queryForList(SQL_GET_ENTRY_BY_GOOGLE_ID, new Object[]{googleUserId}, SQL_GET_ENTRY_BY_GOOGLE_ID_SQL_TYPES);
081    if(rows.isEmpty()){
082      return null;
083    }else{
084      List<PicasaEntry> entries = new ArrayList<>(rows.size());
085      for(Iterator<Map<String, Object>> rowIter = rows.iterator(); rowIter.hasNext();){
086        PicasaEntry e = extractEntry(rowIter.next());
087        if(e == null){
088          LOGGER.warn("Failed to extract entry for Google user, id: "+googleUserId);
089        }else{
090          entries.add(e);
091        }
092      }
093      if(entries.isEmpty()){
094        LOGGER.warn("Could not get any entries for Google user, id: "+googleUserId);
095        return null;
096      }else{
097        return entries;
098      }
099    }
100  }
101
102  /**
103   * 
104   * @param row
105   * @return the entry extracted from the given row map
106   */
107  private PicasaEntry extractEntry(Map<String, Object> row){
108    PicasaEntry pe = new PicasaEntry();
109    for(Entry<String, Object> e : row.entrySet()){
110      String columnName = e.getKey();
111      switch(columnName){
112        case COLUMN_ALBUM_ID:
113          pe.setAlbumId((String) e.getValue());
114          break;
115        case COLUMN_GUID:
116          pe.setGUID((String) e.getValue());
117          break;
118        case COLUMN_GOOGLE_USER_ID:
119          pe.setGoogleUserId((String) e.getValue());
120          break;
121        case COLUMN_PHOTO_ID:
122          pe.setPhotoId((String) e.getValue());
123          break;
124        case COLUMN_STATIC_URL:
125          pe.setStaticUrl((String) e.getValue());
126          break;
127        default:
128          if(checkCountColumn(columnName, e.getValue()) < 1){
129            LOGGER.debug("Unknown column name, or no results.");
130            return null;
131          }
132          break;
133      } // switch
134    }
135    return pe;
136  }
137
138  /**
139   * 
140   * @param entries
141   */
142  public void createEntries(Collection<PicasaEntry> entries) {
143    JdbcTemplate t = getJdbcTemplate();
144    Object[] ob = new Object[5];
145    for(PicasaEntry e : entries){
146      ob[0] = e.getGUID();
147      ob[1] = e.getAlbumId();
148      ob[2] = e.getGoogleUserId();
149      ob[3] = e.getPhotoId();
150      ob[4] = e.getStaticUrl();
151      t.update(SQL_CREATE_ENTRY, ob, SQL_CREATE_ENTRY_SQL_TYPES);
152    }
153  }
154
155  /**
156   * This method will not abort on failure, all updated will be performed that are possible.
157   * 
158   * @param entries
159   * @return true on success, false on failure or partial failure
160   */
161  public boolean updateEntries(List<PicasaEntry> entries) {
162    JdbcTemplate t = getJdbcTemplate();
163    Object[] ob = new Object[5];
164    boolean retval = true;
165    for(PicasaEntry e : entries){
166      ob[0] = e.getAlbumId();
167      ob[1] = e.getPhotoId();
168      ob[2] = e.getGoogleUserId();
169      ob[3] = e.getStaticUrl();
170      ob[4] = e.getGUID();
171      if(t.update(SQL_UPDATE_ENTRY, ob, SQL_UPDATE_ENTRY_SQL_TYPES) != 1){
172        LOGGER.warn("Did not update entry, GUID: "+ob[3]);
173        retval = false;
174      }
175    }
176    return retval;
177  }
178
179  /**
180   * 
181   * @param entry
182   * @return true on success
183   */
184  public boolean updateEntry(PicasaEntry entry){
185    if(getJdbcTemplate().update(SQL_UPDATE_ENTRY, new Object[]{entry.getAlbumId(), entry.getPhotoId(), entry.getGoogleUserId(), entry.getStaticUrl(), entry.getGUID()}, SQL_UPDATE_ENTRY_SQL_TYPES) != 1){
186      LOGGER.warn("Did not update entry, GUID: "+entry.getGUID());
187      return false;
188    }else{
189      return true;
190    }
191  }
192
193  /**
194   * Removes the entries
195   * 
196   * @param guids
197   */
198  public void removeEntries(List<String> guids) {
199    if(guids != null && !guids.isEmpty()){
200      SQLDeleteBuilder sql = new SQLDeleteBuilder(TABLE_PICASA_ENTRIES);
201      sql.addWhereClause(new AndClause(COLUMN_GUID, guids, SQLType.STRING));
202      LOGGER.debug("Entries removed: "+getJdbcTemplate().update(sql.toSQLString(), sql.getValues(), sql.getValueTypes()));
203    }else{
204      LOGGER.debug("Ignored empty GUID list.");
205    }
206  }
207}