Source code

001/**
002 * Copyright 2016 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.apilta.sensors;
017
018import java.util.ArrayList;
019import java.util.Collection;
020import java.util.HashMap;
021import java.util.List;
022import java.util.Map;
023import java.util.Map.Entry;
024import java.util.Set;
025import java.util.UUID;
026
027import org.apache.commons.lang3.ArrayUtils;
028import org.apache.log4j.Logger;
029import org.springframework.beans.factory.annotation.Autowired;
030import org.springframework.jdbc.core.JdbcTemplate;
031import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
032import org.springframework.transaction.TransactionStatus;
033import org.springframework.transaction.support.TransactionCallback;
034
035import service.tut.pori.apilta.sensors.datatypes.Measurement;
036import service.tut.pori.apilta.sensors.datatypes.MeasurementList;
037import core.tut.pori.dao.SQLDAO;
038import core.tut.pori.dao.SQLDeleteBuilder;
039import core.tut.pori.dao.SQLSelectBuilder;
040import core.tut.pori.dao.SQLSelectBuilder.OrderDirection;
041import core.tut.pori.dao.clause.AndClause;
042import core.tut.pori.dao.clause.AndCompareClause;
043import core.tut.pori.dao.clause.AndCompareClause.CompareType;
044import core.tut.pori.dao.clause.AndSubClause;
045import core.tut.pori.dao.clause.JoinClause;
046import core.tut.pori.dao.clause.OrSubClause;
047import core.tut.pori.dao.clause.SQLClause.SQLType;
048import core.tut.pori.http.parameters.DataGroups;
049import core.tut.pori.http.parameters.DateIntervalParameter.Interval;
050import core.tut.pori.http.parameters.Limits;
051
052/**
053 * dao for accessing sensor data
054 * 
055 */
056public class SensorsDAO extends SQLDAO {
057  private static final Logger LOGGER = Logger.getLogger(SensorsDAO.class);
058  /* tables */
059  private static final String TABLE_MEASUREMENTS = DATABASE+".measurements";
060  private static final String TABLE_MEASUREMENTS_FILES = DATABASE+".measurements_files";
061  private static final String TABLE_MEASUREMENTS_TASKS = DATABASE+".measurements_tasks";
062  /* columns */
063  private static final String COLUMN_BACKEND_ID = "backend_id";
064  private static final String COLUMN_MEASUREMENT_ID = "measurement_id";
065  private static final String COLUMN_TABLE_MEASUREMENTS_ROW_CREATED = TABLE_MEASUREMENTS+"."+COLUMN_ROW_CREATED;
066  private static final String COLUMN_TASK_ID_WITH_TABLE_NAME = TABLE_MEASUREMENTS_TASKS+"."+Definitions.COLUMN_TASK_ID;
067  /* sql */
068  private static final String SQL_BACKEND_HAS_GUID = "SELECT "+COLUMN_COUNT+" FROM "+TABLE_MEASUREMENTS_FILES+" WHERE "+COLUMN_BACKEND_ID+"=? AND "+COLUMN_GUID+"=? LIMIT 1";
069  private static final int[] SQL_BACKEND_HAS_GUID_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt()};
070  private static final String[] SQL_COLUMNS_GET_MEASUREMENTS = {TABLE_MEASUREMENTS+"."+COLUMN_MEASUREMENT_ID, TABLE_MEASUREMENTS+"."+COLUMN_BACKEND_ID}; // also used in the where filters of getMeasurements(), check before making modifications
071  private static final String[] SQL_COLUMNS_INSERT_MEASUREMENT = {COLUMN_BACKEND_ID, COLUMN_MEASUREMENT_ID, COLUMN_ROW_CREATED};
072  private static final String SQL_DELETE_FILES = "DELETE FROM "+TABLE_MEASUREMENTS_FILES+" WHERE "+COLUMN_BACKEND_ID+"=?";
073  private static final String SQL_DELETE_MEASUREMENTS = "DELETE FROM "+TABLE_MEASUREMENTS+" WHERE "+COLUMN_BACKEND_ID+"=?";
074  private static final String SQL_GET_BACKEND_ID = "SELECT "+COLUMN_BACKEND_ID+", "+COLUMN_COUNT+" FROM "+TABLE_MEASUREMENTS_FILES+" WHERE "+COLUMN_GUID+"=? LIMIT 1";
075  private static final int[] SQL_GET_BACKEND_ID_SQL_TYPES = {SQLType.STRING.toInt()};
076  private static final String SQL_GET_FILE_GUIDS = "SELECT "+COLUMN_GUID+" FROM "+TABLE_MEASUREMENTS_FILES+" WHERE "+COLUMN_BACKEND_ID+"=?";
077  private static final String SQL_GET_MEASUREMENT_IDS = "SELECT "+COLUMN_MEASUREMENT_ID+" FROM "+TABLE_MEASUREMENTS+" WHERE "+COLUMN_BACKEND_ID+"=?";
078  private static final String SQL_INSERT_FILE = "INSERT INTO "+TABLE_MEASUREMENTS_FILES+" ("+COLUMN_BACKEND_ID+", "+COLUMN_GUID+", "+COLUMN_ROW_CREATED+") VALUES (?,?,NOW())";
079  private static final int[] SQL_INSERT_FILE_SQL_TYPES = {SQLType.LONG.toInt(), SQLType.STRING.toInt()};
080  private static final String SQL_INSERT_MEASUREMENT_TASK = "INSERT INTO "+TABLE_MEASUREMENTS_TASKS+" ("+COLUMN_MEASUREMENT_ID+", "+Definitions.COLUMN_TASK_ID+", "+COLUMN_ROW_CREATED+") VALUES (?,?,NOW())";
081  private static final int[] SQL_INSERT_MEASUREMENT_TASK_SQL_TYPES =  {SQLType.STRING.toInt(), SQLType.STRING.toInt()};
082  private static final JoinClause SQL_JOIN_TASKS = new JoinClause("INNER JOIN "+TABLE_MEASUREMENTS_TASKS+" ON "+TABLE_MEASUREMENTS+"."+COLUMN_MEASUREMENT_ID+"="+TABLE_MEASUREMENTS_TASKS+"."+COLUMN_MEASUREMENT_ID);
083  private static final int[] SQL_TYPE_BACKEND_ID = {SQLType.LONG.toInt()};
084  @Autowired
085  private DataPointDAO _dataPointDAO = null;
086  
087  /**
088   * 
089   * @param backendIdFilter optional filter
090   * @param createdFilter optional filter, this is targeted to the data points (if requested)
091   * @param dataGroups
092   * @param limits
093   * @param measurementIdFilter optional filter
094   * @param taskIds
095   * @return list of measurements or null if none was found
096   */
097  public MeasurementList getMeasurements(long[] backendIdFilter, Set<Interval> createdFilter, DataGroups dataGroups, Limits limits, List<String> measurementIdFilter, List<String> taskIds) {
098    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_MEASUREMENTS);
099    sql.addSelectColumns(SQL_COLUMNS_GET_MEASUREMENTS);
100    sql.addOrderBy(COLUMN_TABLE_MEASUREMENTS_ROW_CREATED, OrderDirection.DESCENDING);
101    sql.setLimits(limits);
102    
103    sql.addWhereClause(new AndClause(COLUMN_TASK_ID_WITH_TABLE_NAME, taskIds, SQLType.STRING));
104    sql.addJoin(SQL_JOIN_TASKS);
105    
106    if(!ArrayUtils.isEmpty(backendIdFilter)){
107      LOGGER.debug("Using back end id filter...");
108      sql.addWhereClause(new AndClause(SQL_COLUMNS_GET_MEASUREMENTS[1], backendIdFilter));
109    }
110    
111    if(measurementIdFilter != null && !measurementIdFilter.isEmpty()){
112      LOGGER.debug("Using measurement id filter...");
113      sql.addWhereClause(new AndClause(SQL_COLUMNS_GET_MEASUREMENTS[0], measurementIdFilter, SQLType.STRING));    
114    }
115    
116    if(createdFilter != null && !createdFilter.isEmpty()){
117      LOGGER.debug("Using created filter...");
118      AndSubClause asc = new AndSubClause();
119      for(Interval interval : createdFilter){
120        asc.addWhereClause(new OrSubClause(new AndCompareClause[]{new AndCompareClause(COLUMN_TABLE_MEASUREMENTS_ROW_CREATED, interval.getStart(), CompareType.GREATER_OR_EQUAL, SQLType.TIMESTAMP), new AndCompareClause(COLUMN_TABLE_MEASUREMENTS_ROW_CREATED, interval.getEnd(), CompareType.LESS_OR_EQUAL, SQLType.TIMESTAMP)}));
121      }
122      sql.addWhereClause(asc);
123    }
124    
125    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(service.tut.pori.apilta.sensors.datatypes.Definitions.ELEMENT_MEASUREMENT_LIST), sql.getValues(), sql.getValueTypes());
126    if(rows.isEmpty()){
127      LOGGER.debug("No measurements found.");
128      return null;
129    }
130    
131    boolean getDataPoints = (DataGroups.hasDataGroup(DataGroups.DATA_GROUP_ALL, dataGroups) || DataGroups.hasDataGroup(Definitions.DATA_GROUP_DATA_POINTS, dataGroups));
132    ArrayList<Measurement> measurements = new ArrayList<>(rows.size());
133    for(Map<String, Object> row : rows){
134      measurements.add(extractMeasurement(row, createdFilter, getDataPoints, limits));
135    }
136    
137    MeasurementList list = new MeasurementList();
138    list.setMeasurements(measurements);
139    return list;
140  }
141  
142  /**
143   * 
144   * @param row
145   * @param createdFilter 
146   * @param getDataPoints if true, data points for the measuremnt will be retrieved
147   * @param limits 
148   * @return measurement extracted from the given row
149   */
150  private Measurement extractMeasurement(Map<String, Object> row, Set<Interval> createdFilter, boolean getDataPoints, Limits limits) {
151    Measurement m = new Measurement();
152    String measurementId = null;
153    for(Entry<String, Object> entry : row.entrySet()){
154      switch(entry.getKey()){
155        case COLUMN_MEASUREMENT_ID:
156          measurementId = (String) entry.getValue();
157          m.setMeasurementId(measurementId);
158          break;
159        case COLUMN_BACKEND_ID:
160          m.setBackendId((Long) entry.getValue());
161          break;
162        default:
163          if(checkCountColumn(entry.getKey(), entry.getValue()) < 1){ // this should be count(*)
164            LOGGER.warn("Unknown column name, or no results, column: "+entry.getKey());
165            return null;
166          }
167          break;
168      }
169    }
170    if(getDataPoints){
171      m.setDataPoints(_dataPointDAO.getDataPoints(createdFilter, limits, measurementId));
172    }
173    return m;
174  }
175  
176  /**
177   * 
178   * Add measurements, the generated measurement identifiers are set to the passed objects
179   * 
180   * @param measurements the measurements to add
181   * @param taskIds the tasks which are to be associated with the measurements
182   */
183  public void addMeasurements(Collection<Measurement> measurements, Collection<String> taskIds) {
184    getTransactionTemplate().execute(new TransactionCallback<Void>() {
185
186      @Override
187      public Void doInTransaction(TransactionStatus status) {
188        JdbcTemplate t = getJdbcTemplate();
189        SimpleJdbcInsert sql = new SimpleJdbcInsert(t);
190        sql.withTableName(TABLE_MEASUREMENTS);
191        sql.withoutTableColumnMetaDataAccess();
192        sql.usingColumns(SQL_COLUMNS_INSERT_MEASUREMENT);
193        
194        HashMap<String, Object> params = new HashMap<>(SQL_COLUMNS_INSERT_MEASUREMENT.length);
195        params.put(COLUMN_ROW_CREATED, null);
196        Object[] ob = new Object[2];
197        for(Measurement m : measurements){
198          params.put(COLUMN_BACKEND_ID, m.getBackendId());
199          String measurementId = UUID.randomUUID().toString();
200          params.put(COLUMN_MEASUREMENT_ID, measurementId);
201          sql.execute(params);
202          
203          ob[0] = measurementId;
204          for(String taskId : taskIds) {
205            ob[1] = taskId;
206            t.update(SQL_INSERT_MEASUREMENT_TASK, ob, SQL_INSERT_MEASUREMENT_TASK_SQL_TYPES);
207          }
208          
209          m.setMeasurementId(measurementId);
210          _dataPointDAO.createDataPoints(m.getDataPoints(), measurementId);
211        }
212        return null;
213      }
214    });
215  }
216  
217  /**
218   * delete all measurements taken by the back end
219   * 
220   * @param backendId
221   */
222  public void deleteMeasurements(Long backendId) {
223    getTransactionTemplate().execute(new TransactionCallback<Void>() {
224
225      @Override
226      public Void doInTransaction(TransactionStatus status) {
227        JdbcTemplate t = getJdbcTemplate();
228        Object[] ob = {backendId};
229        List<Long> measurementIds = t.queryForList(SQL_GET_MEASUREMENT_IDS, ob, SQL_TYPE_BACKEND_ID, Long.class);
230        if(measurementIds.isEmpty()){
231          LOGGER.debug("No measurements for back end, id: "+backendId);
232          return null;
233        }
234        
235        _dataPointDAO.deleteDataPoints(measurementIds);
236        
237        t.update(SQL_DELETE_MEASUREMENTS, ob, SQL_TYPE_BACKEND_ID);
238        
239        SQLDeleteBuilder sql = new SQLDeleteBuilder(TABLE_MEASUREMENTS_TASKS);
240        sql.addWhereClause(new AndClause(COLUMN_MEASUREMENT_ID, measurementIds, SQLType.LONG));
241        sql.execute(getJdbcTemplate());
242        return null;
243      }
244    });
245  }
246  
247  /**
248   * 
249   * @param backendId
250   * @param guid
251   */
252  protected void addFile(Long backendId, String guid) {
253    getJdbcTemplate().update(SQL_INSERT_FILE, new Object[]{backendId, guid}, SQL_INSERT_FILE_SQL_TYPES);
254  }
255  
256  /**
257   * 
258   * @param guid
259   * @return back end identifier associated with the given file GUID or null if not found
260   */
261  protected Long getFileBackendId(String guid) {
262    return (Long) getJdbcTemplate().queryForMap(SQL_GET_BACKEND_ID, new Object[]{guid}, SQL_GET_BACKEND_ID_SQL_TYPES).get(COLUMN_BACKEND_ID);
263  }
264  
265  /**
266   * 
267   * @param backendId
268   * @return list of file GUIDs associated with the given back end id or null if none was found
269   */
270  protected List<String> getFileGUIDs(Long backendId) {
271    List<String> guids = getJdbcTemplate().queryForList(SQL_GET_FILE_GUIDS, new Object[]{backendId}, SQL_GET_BACKEND_ID_SQL_TYPES, String.class);
272    return (guids.isEmpty() ? null : guids);
273  }
274  
275  /**
276   * 
277   * @param backendId
278   * @param guid
279   * @return true if the given GUID is associated with the given back end
280   */
281  protected boolean backendHasGUID(Long backendId, String guid) {
282    return (getJdbcTemplate().queryForObject(SQL_BACKEND_HAS_GUID, new Object[]{backendId, guid}, SQL_BACKEND_HAS_GUID_SQL_TYPES, Integer.class) > 0);
283  }
284  
285  /**
286   * delete all file GUID associations for the given back end id
287   * 
288   * @param backendId
289   */
290  protected void deleteFiles(Long backendId) {
291    getJdbcTemplate().update(SQL_DELETE_FILES, new Object[]{backendId}, SQL_GET_BACKEND_ID_SQL_TYPES);
292  }
293}