Source code

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.contentanalysis;
017
018import java.util.ArrayList;
019import java.util.EnumSet;
020import java.util.Iterator;
021import java.util.List;
022import java.util.Map;
023import java.util.Map.Entry;
024import java.util.Set;
025
026import org.apache.commons.lang3.BooleanUtils;
027import org.apache.commons.lang3.StringUtils;
028import org.apache.log4j.Logger;
029import org.springframework.jdbc.core.JdbcTemplate;
030import org.springframework.transaction.TransactionStatus;
031import org.springframework.transaction.support.TransactionCallback;
032
033import service.tut.pori.contentanalysis.AnalysisBackend.Capability;
034import core.tut.pori.dao.clause.AndClause;
035import core.tut.pori.dao.clause.SQLClause.SQLType;
036import core.tut.pori.dao.SQLDAO;
037import core.tut.pori.dao.SQLSelectBuilder;
038import core.tut.pori.http.parameters.DataGroups;
039
040/**
041 * A DAO for inserting, removing and modifying the analysis back-ends known by the system.
042 *
043 */
044public class BackendDAO extends SQLDAO{
045  private static final Logger LOGGER = Logger.getLogger(BackendDAO.class);
046  /* table names */
047  private static final String TABLE_BACKEND_CAPABILITIES = DATABASE+".ca_backend_capabilities";
048  private static final String TABLE_BACKENDS = DATABASE+".ca_backends";
049  /* columns */
050  private static final String COLUMN_ANALYSIS_URI = "analysis_uri";
051  private static final String COLUMN_CAPABILITY = "capability";
052  private static final String COLUMN_ENABLED = "enabled";
053  private static final String COLUMN_DEFAULT_TASK_DATAGROUPS = "default_task_datagroups";
054  /* sql strings */
055  private static final String SQL_DELETE_BACKEND = "DELETE FROM "+TABLE_BACKENDS+" WHERE "+Definitions.COLUMN_BACKEND_ID+"=?";
056  private static final int[] SQL_DELETE_BACKEND_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
057
058  private static final String SQL_DELETE_CAPABILITIES = "DELETE FROM "+TABLE_BACKEND_CAPABILITIES+" WHERE "+Definitions.COLUMN_BACKEND_ID+"=?";
059  private static final int[] SQL_DELETE_CAPABILITIES_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
060
061  private static final String SQL_INSERT_BACKEND = "INSERT INTO " + TABLE_BACKENDS + " ("+Definitions.COLUMN_BACKEND_ID+", "+Definitions.COLUMN_DESCRIPTION+", "+COLUMN_ENABLED+", "+COLUMN_ANALYSIS_URI+", "+COLUMN_DEFAULT_TASK_DATAGROUPS+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,NOW())";
062  private static final int[] SQL_INSERT_BACKEND_SQL_TYPES = new int[]{SQLType.INTEGER.toInt(),SQLType.STRING.toInt(),SQLType.INTEGER.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt()};
063
064  private static final String SQL_INSERT_CAPABILITIES = "INSERT INTO "+TABLE_BACKEND_CAPABILITIES+" ("+Definitions.COLUMN_BACKEND_ID+","+COLUMN_CAPABILITY+","+COLUMN_ROW_CREATED+") VALUES (?,?,NOW()) ON DUPLICATE KEY UPDATE "+COLUMN_ROW_UPDATED+"=NOW()";
065  private static final int[] SQL_INSERT_CAPABILITIES_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.STRING.toInt()};
066  
067  private static final String SQL_SELECT_BACKENDS = "SELECT "+TABLE_BACKENDS+"."+Definitions.COLUMN_BACKEND_ID+","+TABLE_BACKENDS+"."+COLUMN_ANALYSIS_URI+","+TABLE_BACKENDS+"."+COLUMN_ENABLED+","+TABLE_BACKENDS+"."+Definitions.COLUMN_DESCRIPTION+","+TABLE_BACKENDS+"."+COLUMN_DEFAULT_TASK_DATAGROUPS+
068      " FROM "+TABLE_BACKENDS+" INNER JOIN "+TABLE_BACKEND_CAPABILITIES+" ON "+TABLE_BACKEND_CAPABILITIES+"."+Definitions.COLUMN_BACKEND_ID+"="+TABLE_BACKENDS+"."+Definitions.COLUMN_BACKEND_ID+" WHERE "+TABLE_BACKEND_CAPABILITIES+"."+COLUMN_CAPABILITY+"=? AND "+TABLE_BACKENDS+"."+COLUMN_ENABLED+"="+BooleanUtils.toInteger(true);
069  private static final int[] SQL_SELECT_BACKENDS_SQL_TYPES = new int[]{SQLType.STRING.toInt()};
070  
071  private static final String SQL_UPDATE_BACKENDS = "UPDATE "+TABLE_BACKENDS+" SET "+COLUMN_ANALYSIS_URI+"=?,"+COLUMN_ENABLED+"=?,"+Definitions.COLUMN_DESCRIPTION+"=?,"+COLUMN_DEFAULT_TASK_DATAGROUPS+"=? WHERE "+Definitions.COLUMN_BACKEND_ID+"=?";
072  private static final int[] SQL_UPDATE_BACKENDS_SQL_TYPES = new int[]{SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt(),SQLType.STRING.toInt()};
073  
074  private static final String[] TABLE_BACKEND_CAPABILITIES_ALL_COLUMNS = new String[]{Definitions.COLUMN_BACKEND_ID,COLUMN_CAPABILITY};
075  private static final String[] TABLE_BACKENDS_ALL_COLUMNS = new String[]{Definitions.COLUMN_BACKEND_ID,COLUMN_ANALYSIS_URI,COLUMN_ENABLED,Definitions.COLUMN_DESCRIPTION,COLUMN_DEFAULT_TASK_DATAGROUPS};
076  
077  private static final String SQL_GET_ENABLED_BACKENDS = "SELECT "+StringUtils.join(TABLE_BACKENDS_ALL_COLUMNS, ',')+" FROM "+TABLE_BACKENDS+" WHERE "+COLUMN_ENABLED+"="+BooleanUtils.toInteger(true);
078  
079  private static final String SQL_SELECT_BACKEND_BY_BACKEND_ID = "SELECT "+StringUtils.join(TABLE_BACKENDS_ALL_COLUMNS, ',')+" FROM "+TABLE_BACKENDS+" WHERE "+Definitions.COLUMN_BACKEND_ID+"=? LIMIT 1";
080  private static final int[] SQL_SELECT_BACKEND_BY_BACKEND_ID_TYPES = new int[]{SQLType.INTEGER.toInt()};
081  
082
083  /**
084   * 
085   * @param end
086   * 
087   */
088  public void createBackend(final AnalysisBackend end){
089    getTransactionTemplate().execute(new TransactionCallback<Void>() {
090
091      @Override
092      public Void doInTransaction(TransactionStatus status) {
093        JdbcTemplate t = getJdbcTemplate();
094        t.update(SQL_INSERT_BACKEND, new Object[]{end.getBackendId(),end.getDescription(),BooleanUtils.toInteger(end.isEnabled()),end.getAnalysisUri(),end.getDefaultTaskDataGroups().toDataGroupString()}, SQL_INSERT_BACKEND_SQL_TYPES);
095        
096        addCapabilities(t.queryForObject(SQL_SELECT_LAST_INSERT_ID, Integer.class), end.getCapabilities());
097        return null;
098      }
099    });
100  }
101  
102  /**
103   * add the list of capabilities for the given backend
104   * 
105   * @param backendId
106   * @param capabilities
107   */
108  private void addCapabilities(Integer backendId, EnumSet<Capability> capabilities){
109    if(capabilities == null){
110      LOGGER.debug("No capabilities.");
111      return;
112    }
113    JdbcTemplate t = getJdbcTemplate();
114    Object[] ob = new Object[]{backendId,null};
115    for(Iterator<Capability> iter = capabilities.iterator();iter.hasNext();){
116      ob[1] = iter.next().toInt();
117      t.update(SQL_INSERT_CAPABILITIES, ob, SQL_INSERT_CAPABILITIES_SQL_TYPES);
118    }
119  }
120  
121  /**
122   * updates the details for the given backend
123   * 
124   * @param end
125   * 
126   */
127  public void updateBackend(AnalysisBackend end){
128    JdbcTemplate t = getJdbcTemplate();
129    Integer backendId = end.getBackendId();
130    t.update(SQL_UPDATE_BACKENDS, new Object[]{end.getAnalysisUri(),BooleanUtils.toInteger(end.isEnabled()),end.getDescription(),end.getDefaultTaskDataGroups().toDataGroupString(),backendId}, SQL_UPDATE_BACKENDS_SQL_TYPES);
131    
132    removeCapabilities(end.getBackendId()); // remove all previously set capabilities
133    
134    addCapabilities(backendId, end.getCapabilities());
135  }
136  
137  /**
138   * 
139   * @param backendId
140   */
141  private void removeCapabilities(Integer backendId){
142    getJdbcTemplate().update(SQL_DELETE_CAPABILITIES, new Object[]{backendId}, SQL_DELETE_CAPABILITIES_SQL_TYPES);
143  }
144
145  /**
146   * 
147   * @param backendId
148   * 
149   */
150  public void removeBackend(Integer backendId){
151    getJdbcTemplate().update(SQL_DELETE_BACKEND, new Object[]{backendId}, SQL_DELETE_BACKEND_SQL_TYPES);
152    removeCapabilities(backendId);
153  }
154
155  /**
156   * convenience method for retrieving all known backends
157   * @return list of backends
158   */
159  public List<AnalysisBackend> getBackends(){
160    return getBackends((List<Integer>)null);
161  }
162
163  /**
164   * Only enabled backends will be returned.
165   * 
166   * @param capability
167   * @return the list of backends with the given capability or null if none available
168   */
169  public List<AnalysisBackend> getBackends(Capability capability){
170    List<AnalysisBackend> ends = extractBackends(getJdbcTemplate().queryForList(SQL_SELECT_BACKENDS, new Object[]{capability.toInt()}, SQL_SELECT_BACKENDS_SQL_TYPES));
171    setCapabilities(ends);
172    return ends;
173  }
174  
175  /**
176   * 
177   * @param capabilities
178   * @return list of back-ends matching ANY of the given capabilities or null if none was found
179   */
180  public List<AnalysisBackend> getBackends(Set<Capability> capabilities){
181    if(capabilities == null || capabilities.isEmpty()){
182      LOGGER.debug("Empty capability list.");
183      return null;
184    }
185    List<AnalysisBackend> ends = new ArrayList<>();
186    for(Capability c : capabilities){ // loop all, we could also replace this with SQL join
187      List<AnalysisBackend> tempEnds = getBackends(c);
188      if(tempEnds != null){
189        ends.addAll(tempEnds);
190      }else{
191        LOGGER.debug("No back-ends with capability: "+c.name());
192      }
193    }
194    return (ends.isEmpty() ? null : ends);
195  }
196
197  /**
198   * 
199   * @param rows
200   * @return list of backends from the row map or null if none
201   */
202  private List<AnalysisBackend> extractBackends(List<Map<String,Object>> rows){
203    if(rows.isEmpty()){
204      LOGGER.debug("No backends.");
205      return null;
206    }
207    List<AnalysisBackend> ends = new ArrayList<>(rows.size());
208    for(Iterator<Map<String,Object>> rowIter = rows.iterator();rowIter.hasNext();){
209      AnalysisBackend end = new AnalysisBackend();
210      for(Entry<String,Object> rowEntry : rowIter.next().entrySet()){
211        switch(rowEntry.getKey()){
212          case Definitions.COLUMN_BACKEND_ID:
213            Integer id = (Integer) rowEntry.getValue();
214            end.setBackendId(id);
215            break;
216          case COLUMN_ANALYSIS_URI:
217            end.setAnalysisUri((String) rowEntry.getValue());
218            break;
219          case COLUMN_ENABLED:
220            end.setEnabled(BooleanUtils.toBoolean((Integer) rowEntry.getValue()));
221            break;
222          case Definitions.COLUMN_DESCRIPTION:
223            end.setDescription((String) rowEntry.getValue());
224            break;
225          case COLUMN_DEFAULT_TASK_DATAGROUPS:
226            DataGroups dg = new DataGroups();
227            dg.initialize((String)rowEntry.getValue());
228            end.setDefaultTaskDataGroups(dg);
229            break;
230          default:
231            LOGGER.warn("Ignored unknown column: "+rowEntry.getKey());
232            break;
233        }
234      }
235      ends.add(end);
236    }
237    return ends;
238  }
239  
240  /**
241   * helper method for retrieving (and setting) the capabilities for the given list of backend
242   * 
243   * @param ends
244   */
245  private void setCapabilities(List<AnalysisBackend> ends){
246    if(ends == null){
247      LOGGER.debug("No backends in the list.");
248      return;
249    }
250    
251    Integer[] ids = new Integer[ends.size()];
252    int index = 0;
253    for(Iterator<AnalysisBackend> iter = ends.iterator();iter.hasNext();){  // get ids
254      ids[index++] = iter.next().getBackendId();
255    }
256  
257    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_BACKEND_CAPABILITIES);
258    sql.addSelectColumns(TABLE_BACKEND_CAPABILITIES_ALL_COLUMNS);
259    sql.addWhereClause(new AndClause(Definitions.COLUMN_BACKEND_ID, ids, SQLType.INTEGER));
260    
261    List<Map<String,Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(), sql.getValues(), sql.getValueTypes());
262    for(Iterator<Map<String,Object>> rowIter = rows.iterator();rowIter.hasNext();){
263      Integer backendId = null;
264      Capability capability = null;
265      for(Entry<String,Object> rowEntry : rowIter.next().entrySet()){ // go through the returned rows
266        switch(rowEntry.getKey()){
267          case Definitions.COLUMN_BACKEND_ID:
268            backendId = (Integer) rowEntry.getValue();
269            break;
270          case COLUMN_CAPABILITY:
271            capability = Capability.fromInt((Integer) rowEntry.getValue());
272            break;
273          default:
274            LOGGER.warn("Ignored unknown column: "+rowEntry.getKey());
275            break;
276        }
277      } // for row
278      
279      for(Iterator<AnalysisBackend> bIter = ends.iterator();bIter.hasNext();){  // find the owner of this capability
280        AnalysisBackend end = bIter.next();
281        if(end.getBackendId().equals(backendId)){
282          end.addCapability(capability);
283          break;
284        }
285      } // for backends
286    } // for rows
287  }
288
289  /**
290   * 
291   * @param backendIds if != null, only the requested backends will be returned
292   * @return list of backends or null if none available. 
293   */
294  public List<AnalysisBackend> getBackends(List<Integer> backendIds){
295    SQLSelectBuilder sqlBuilder = new SQLSelectBuilder(TABLE_BACKENDS);
296    sqlBuilder.addSelectColumns(TABLE_BACKENDS_ALL_COLUMNS);
297
298    if(backendIds != null){
299      sqlBuilder.addWhereClause(new AndClause(Definitions.COLUMN_BACKEND_ID, backendIds.toArray(), SQLType.INTEGER));
300    }
301    
302    List<AnalysisBackend> ends = extractBackends(getJdbcTemplate().queryForList(sqlBuilder.toSQLString(), sqlBuilder.getValues(), sqlBuilder.getValueTypes()));
303    setCapabilities(ends);
304    return ends;
305  }
306  
307  /**
308   * 
309   * @return list of back-ends or null if none was found
310   */
311  public List<AnalysisBackend> getEnabledBackends(){
312    return extractBackends(getJdbcTemplate().queryForList(SQL_GET_ENABLED_BACKENDS));
313  }
314  
315  /**
316   * 
317   * @param backendId
318   * @return the back-end with the given is or null if not found
319   */
320  public AnalysisBackend getBackend(Integer backendId){
321    List<Map<String,Object>> rows = getJdbcTemplate().queryForList(SQL_SELECT_BACKEND_BY_BACKEND_ID, new Object[]{backendId}, SQL_SELECT_BACKEND_BY_BACKEND_ID_TYPES);
322    if(rows.isEmpty()){
323      return null;
324    }else{
325      List<AnalysisBackend> backends = extractBackends(rows);
326      setCapabilities(backends);
327      return backends.get(0);
328    }
329  }
330}