Source code

001/**
002 * Copyright 2018 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.shock;
017
018import java.util.Date;
019import java.util.LinkedList;
020import java.util.List;
021import java.util.Map;
022import java.util.Map.Entry;
023import java.util.UUID;
024
025import org.apache.commons.lang3.ArrayUtils;
026import org.apache.log4j.Logger;
027import org.springframework.transaction.TransactionStatus;
028import org.springframework.transaction.support.TransactionCallback;
029
030import core.tut.pori.dao.SQLDAO;
031import core.tut.pori.dao.SQLSelectBuilder;
032import core.tut.pori.dao.SQLSelectBuilder.OrderDirection;
033import core.tut.pori.dao.clause.AndClause;
034import core.tut.pori.dao.clause.AndCompareClause;
035import core.tut.pori.dao.clause.AndCompareClause.CompareType;
036import core.tut.pori.dao.clause.AndSubClause;
037import core.tut.pori.dao.clause.JoinClause;
038import core.tut.pori.dao.clause.OrClause;
039import core.tut.pori.dao.clause.OrSubClause;
040import core.tut.pori.dao.clause.SQLClause.SQLType;
041import core.tut.pori.dao.clause.WhereClause;
042import core.tut.pori.http.parameters.DataGroups;
043import core.tut.pori.http.parameters.DateIntervalParameter;
044import core.tut.pori.http.parameters.DateIntervalParameter.Interval;
045import core.tut.pori.http.parameters.Limits;
046import core.tut.pori.users.UserIdentity;
047import service.tut.pori.apilta.shock.datatypes.AccelerometerData;
048import service.tut.pori.apilta.shock.datatypes.LocationData;
049import service.tut.pori.apilta.shock.datatypes.LocationLimits;
050import service.tut.pori.apilta.shock.datatypes.LocationLimits.LatLng;
051import service.tut.pori.apilta.shock.datatypes.ShockMeasurement;
052import service.tut.pori.apilta.shock.datatypes.ShockMeasurementList;
053import service.tut.pori.tasks.datatypes.Task.Visibility;
054
055/**
056 * 
057 *
058 */
059public class ShockDAO extends SQLDAO {
060  private static final Logger LOGGER = Logger.getLogger(ShockDAO.class);
061  /* tables */
062  private static final String TABLE_SHOCK_ACCELEROMETER_DATA = DATABASE+".shock_accelerometer_data";
063  private static final String TABLE_SHOCK_LOCATION_DATA = DATABASE+".shock_location_data";
064  private static final String TABLE_SHOCK_MEASUREMENTS = DATABASE+".shock_measurements";
065  /* columns */
066  private static final String COLUMN_DATA_VISIBILITY = "data_visibility";
067  private static final String COLUMN_LATITUDE = "latitude";
068  private static final String COLUMN_LONGITUDE = "longitude";
069  private static final String COLUMN_LEVEL = "level";
070  private static final String COLUMN_HEADING = "heading";
071  private static final String COLUMN_SPEED = "speed";
072  private static final String COLUMN_MEASUREMENT_ID = "measurement_id";
073  private static final String COLUMN_TIMESTAMP = "timestamp";
074  private static final String COLUMN_MEASUREMENTS_LEVEL = TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_LEVEL;
075  private static final String COLUMN_MEASUREMENTS_TIMESTAMP = TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_TIMESTAMP;
076  private static final String COLUMN_SYSTEMATIC_ERROR = "systematic_error";
077  private static final String COLUMN_X_ACCELERATION = "x_acc";
078  private static final String COLUMN_Y_ACCELERATION = "y_acc";
079  private static final String COLUMN_Z_ACCELERATION = "z_acc";
080  
081  private static final String[] COLUMNS_GET_MEASUREMENTS = {TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_MEASUREMENT_ID, TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_DATA_VISIBILITY, TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_USER_ID, COLUMN_MEASUREMENTS_TIMESTAMP, COLUMN_MEASUREMENTS_LEVEL};
082  /* sql strings */
083  private static final String SQL_INSERT_MEASUREMENT = "INSERT INTO "+TABLE_SHOCK_MEASUREMENTS+" ("+COLUMN_MEASUREMENT_ID+", "+COLUMN_LEVEL+", "+COLUMN_DATA_VISIBILITY+", "+COLUMN_USER_ID+", "+COLUMN_TIMESTAMP+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,NOW())";
084  private static final int[] SQL_INSERT_MEASUREMENT_VALUE_TYPES = {SQLType.STRING.toInt(), SQLType.INTEGER.toInt(), SQLType.INTEGER.toInt(), SQLType.LONG.toInt(), SQLType.TIMESTAMP.toInt()};
085  
086  private static final String SQL_INSERT_ACCELEROMETER_DATA = "INSERT INTO "+TABLE_SHOCK_ACCELEROMETER_DATA+" ("+COLUMN_MEASUREMENT_ID+", "+COLUMN_X_ACCELERATION+", "+COLUMN_Y_ACCELERATION+", "+COLUMN_Z_ACCELERATION+", "+COLUMN_SYSTEMATIC_ERROR+", "+COLUMN_TIMESTAMP+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,?,NOW())";
087  private static final int[] SQL_INSERT_ACCELEROMETER_DATA_VALUE_TYPES = {SQLType.STRING.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.TIMESTAMP.toInt()};
088  
089  private static final String SQL_INSERT_LOCATION_DATA = "INSERT INTO "+TABLE_SHOCK_LOCATION_DATA+" ("+COLUMN_MEASUREMENT_ID+", "+COLUMN_HEADING+", "+COLUMN_LATITUDE+", "+COLUMN_LONGITUDE+", "+COLUMN_SPEED+", "+COLUMN_TIMESTAMP+", "+COLUMN_ROW_CREATED+") VALUES (?,?,?,?,?,?,NOW())";
090  private static final int[] SQL_INSERT_LOCATION_DATA_VALUE_TYPES = {SQLType.STRING.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.DOUBLE.toInt(), SQLType.TIMESTAMP.toInt()};
091  
092  private static final WhereClause SQL_DATA_VISIBILITY_PUBLIC = new OrClause(COLUMN_DATA_VISIBILITY, Visibility.PUBLIC.toInt(), SQLType.INTEGER);
093  
094  private static final String SQL_GET_ACCELEROMETER_DATA = "SELECT "+COLUMN_X_ACCELERATION+", "+COLUMN_Y_ACCELERATION+", "+COLUMN_Z_ACCELERATION+", "+COLUMN_TIMESTAMP+", "+COLUMN_SYSTEMATIC_ERROR+" FROM "+TABLE_SHOCK_ACCELEROMETER_DATA+" WHERE "+COLUMN_MEASUREMENT_ID+"=?";
095  private static final int[] SQL_MEASUREMENT_ID_VALUE_TYPE = {SQLType.STRING.toInt()};
096  
097  private static final String SQL_GET_LOCATION_DATA = "SELECT "+COLUMN_LATITUDE+", "+COLUMN_LONGITUDE+", "+COLUMN_SPEED+", "+COLUMN_HEADING+", "+COLUMN_TIMESTAMP+" FROM "+TABLE_SHOCK_LOCATION_DATA+" WHERE "+COLUMN_MEASUREMENT_ID+"=?";
098  
099  private static final JoinClause SQL_JOIN_LOCATION_DATA = new JoinClause("LEFT JOIN "+TABLE_SHOCK_LOCATION_DATA+" ON "+TABLE_SHOCK_MEASUREMENTS+"."+COLUMN_MEASUREMENT_ID+"="+TABLE_SHOCK_LOCATION_DATA+"."+COLUMN_MEASUREMENT_ID);
100  private static final AndClause SQL_CLAUSE_LEVEL_NOT_NULL = new AndClause(COLUMN_LEVEL, (Object) null, SQLType.INTEGER).setNot(true);
101  
102  
103  /**
104   * 
105   * @param userIdentity
106   * @param locationLimits
107   * @param dataGroups
108   * @param dateInterval
109   * @param levelFilter 
110   * @param limits
111   * @param userIdFilter 
112   * @return list of measurements or null if none was found
113   */
114  public ShockMeasurementList getMeasurements(UserIdentity userIdentity, LocationLimits locationLimits, DataGroups dataGroups, DateIntervalParameter dateInterval, int[] levelFilter, Limits limits, long[] userIdFilter) {
115    SQLSelectBuilder sql = new SQLSelectBuilder(TABLE_SHOCK_MEASUREMENTS);
116    sql.setLimits(limits);
117    sql.addWhereClause(new AndSubClause(new WhereClause[]{new OrClause(COLUMN_USER_ID, userIdentity.getUserId(), SQLType.LONG), SQL_DATA_VISIBILITY_PUBLIC}));
118    sql.addSelectColumns(COLUMNS_GET_MEASUREMENTS);
119    sql.addOrderBy(COLUMN_MEASUREMENTS_TIMESTAMP, OrderDirection.DESCENDING);
120    
121    if(dateInterval.hasValues()){
122      LOGGER.debug("Using date filter...");
123      AndSubClause asc = new AndSubClause();
124      for(Interval interval : dateInterval.getValues()){
125        asc.addWhereClause(new OrSubClause(new AndCompareClause[]{new AndCompareClause(COLUMN_MEASUREMENTS_TIMESTAMP, interval.getStart(), CompareType.GREATER_OR_EQUAL, SQLType.TIMESTAMP), new AndCompareClause(COLUMN_MEASUREMENTS_TIMESTAMP, interval.getEnd(), CompareType.LESS_OR_EQUAL, SQLType.TIMESTAMP)}));
126      }
127      sql.addWhereClause(asc);
128    }
129    
130    if(!ArrayUtils.isEmpty(userIdFilter)) {
131      LOGGER.debug("Using user id filter...");
132      sql.addWhereClause(new AndClause(COLUMN_USER_ID, userIdFilter));
133    }
134    
135    if(!ArrayUtils.isEmpty(levelFilter)) {
136      LOGGER.debug("Using level filter...");
137      sql.addWhereClause(new AndSubClause(new WhereClause[]{SQL_CLAUSE_LEVEL_NOT_NULL, new AndClause(COLUMN_LEVEL, levelFilter)}));
138    }
139    
140    if(locationLimits.hasValues()) {
141      LOGGER.debug("Using location limits filter...");
142      sql.addJoin(SQL_JOIN_LOCATION_DATA); // note: we could also select all data from location table if location datagroup is given (same for accelerometer data)
143      
144      LatLng lowerLeft = locationLimits.getLowerLeft(); // column >= lat, column >= lon
145      sql.addWhereClause(new AndCompareClause(COLUMN_LATITUDE, lowerLeft.getLatitude(), CompareType.GREATER_OR_EQUAL, SQLType.DOUBLE));
146      sql.addWhereClause(new AndCompareClause(COLUMN_LONGITUDE, lowerLeft.getLongitude(), CompareType.GREATER_OR_EQUAL, SQLType.DOUBLE));
147      
148      LatLng upperRight = locationLimits.getUpperRight(); // column <= lat, column <= lon
149      sql.addWhereClause(new AndCompareClause(COLUMN_LATITUDE, upperRight.getLatitude(), CompareType.LESS_OR_EQUAL, SQLType.DOUBLE));
150      sql.addWhereClause(new AndCompareClause(COLUMN_LONGITUDE, upperRight.getLongitude(), CompareType.LESS_OR_EQUAL, SQLType.DOUBLE));
151    }
152    
153    List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql.toSQLString(service.tut.pori.apilta.sensors.datatypes.Definitions.ELEMENT_MEASUREMENT_LIST), sql.getValues(), sql.getValueTypes());
154    int size = rows.size();
155    if(size < 1) {
156      LOGGER.debug("No measurements founds.");
157      return null;
158    }
159    
160    boolean dataGroupAll = DataGroups.hasDataGroup(DataGroups.DATA_GROUP_ALL, dataGroups);
161    boolean retrieveLocations = (dataGroupAll || DataGroups.hasDataGroup(Definitions.DATA_GROUP_LOCATION_DATA, dataGroups));
162    boolean retrieveAccelerometer = (dataGroupAll || DataGroups.hasDataGroup(Definitions.DATA_GROUP_ACCELEROMETER_DATA, dataGroups));
163    
164    LinkedList<ShockMeasurement> measurements = new LinkedList<>();
165    for(Map<String, Object> row : rows) {
166      measurements.add(extractMeasurement(row, retrieveLocations, retrieveAccelerometer));
167    }
168    
169    return ShockMeasurementList.getShockMeasurementList(measurements);
170  }
171  
172  /**
173   * 
174   * @param row
175   * @param getLocations
176   * @param getAccelerometer
177   * @return the extracted measurement
178   */
179  private ShockMeasurement extractMeasurement(Map<String, Object> row, boolean getLocations, boolean getAccelerometer) {
180    ShockMeasurement m = new ShockMeasurement();
181    Object[] measurementId = new Object[1];
182    for(Entry<String, Object> e : row.entrySet()) {
183      String column = e.getKey();
184      switch(column) {
185        case COLUMN_MEASUREMENT_ID:
186          measurementId[0] = e.getValue();
187          m.setMeasurementId((String) measurementId[0]);
188          break;
189        case COLUMN_DATA_VISIBILITY:
190          m.setVisibility(Visibility.fromInt((int) e.getValue()));
191          break;
192        case COLUMN_USER_ID:
193          m.setUserId(new UserIdentity((Long) e.getValue()));
194          break;
195        case COLUMN_TIMESTAMP:
196          m.setTimestamp((Date) e.getValue());
197          break;
198        case COLUMN_LEVEL:
199          m.setLevel((Integer) e.getValue());
200          break;
201        default:
202          if(checkCountColumn(column, e.getValue()) < 1){ // this should be count(*)
203            LOGGER.warn("Unknown column name, or no results, column: "+column);
204            return null;
205          }
206          break;
207      }
208    }
209    
210    if(getAccelerometer) {
211      List<Map<String, Object>> rows = getJdbcTemplate().queryForList(SQL_GET_ACCELEROMETER_DATA, measurementId, SQL_MEASUREMENT_ID_VALUE_TYPE);
212      if(rows.isEmpty()) {
213        LOGGER.debug("No accelerometer data for measurement, id: "+measurementId[0]);
214      }else {
215        m.setAccelerometerData(extractAccelerometerData(rows.iterator().next()));
216      }
217    }
218    
219    if(getLocations) {
220      List<Map<String, Object>> rows = getJdbcTemplate().queryForList(SQL_GET_LOCATION_DATA, measurementId, SQL_MEASUREMENT_ID_VALUE_TYPE);
221      if(rows.isEmpty()) {
222        LOGGER.debug("No location data for measurement, id: "+measurementId[0]);
223      }else {
224        m.setLocationData(extractLocationData(rows.iterator().next()));
225      }
226    }
227    
228    return m;
229  }
230  
231  /**
232   * 
233   * @param row
234   * @return the extracted data
235   */
236  private AccelerometerData extractAccelerometerData(Map<String, Object> row) {
237    AccelerometerData aData = new AccelerometerData();
238    for(Entry<String, Object> e : row.entrySet()) {
239      String column = e.getKey();
240      switch(column) {
241        case COLUMN_X_ACCELERATION:
242          aData.setxAcceleration((Double) e.getValue());
243          break;
244        case COLUMN_Y_ACCELERATION:
245          aData.setyAcceleration((Double) e.getValue());
246          break;
247        case COLUMN_Z_ACCELERATION:
248          aData.setzAcceleration((Double) e.getValue());
249          break;
250        case COLUMN_TIMESTAMP:
251          aData.setTimestamp((Date) e.getValue());
252          break;
253        case COLUMN_SYSTEMATIC_ERROR:
254          aData.setSystematicError((Double) e.getValue());
255          break;
256        case COLUMN_MEASUREMENT_ID: // valid column, but not used in data object
257          break;
258        default:
259          if(checkCountColumn(column, e.getValue()) < 1){ // this should be count(*)
260            LOGGER.warn("Unknown column name, or no results, column: "+column);
261            return null;
262          }
263          break;
264      }
265    }
266    return aData;
267  }
268  
269  /**
270   * 
271   * @param row
272   * @return the extracted data
273   */
274  private LocationData extractLocationData(Map<String, Object> row) {
275    LocationData lData = new LocationData();
276    for(Entry<String, Object> e : row.entrySet()) {
277      String column = e.getKey();
278      switch(column) {
279        case COLUMN_LATITUDE:
280          lData.setLatitude((Double) e.getValue());
281          break;
282        case COLUMN_LONGITUDE:
283          lData.setLongitude((Double) e.getValue());
284          break;
285        case COLUMN_HEADING:
286          lData.setHeading((Double) e.getValue());
287          break;
288        case COLUMN_SPEED:
289          lData.setSpeed((Double) e.getValue());
290          break;
291        case COLUMN_TIMESTAMP:
292          lData.setTimestamp((Date) e.getValue());
293          break;
294        case COLUMN_MEASUREMENT_ID: // valid column, but not not used in the data object
295          break;
296        default:
297          if(checkCountColumn(column, e.getValue()) < 1){ // this should be count(*)
298            LOGGER.warn("Unknown column name, or no results, column: "+column);
299            return null;
300          }
301          break;
302      }
303    }
304    return lData;
305  }
306
307  /**
308   * 
309   * @param measurement
310   * @return measurement id for the created measurement
311   */
312  public String createMeasurement(ShockMeasurement measurement) {
313    return getTransactionTemplate().execute(new TransactionCallback<String>() {
314
315      @Override
316      public String doInTransaction(TransactionStatus status) {
317        String measurementId = UUID.randomUUID().toString();
318        getJdbcTemplate().update(SQL_INSERT_MEASUREMENT, new Object[]{measurementId, measurement.getLevel(), measurement.getVisibility().toInt(), measurement.getUserId().getUserId(), measurement.getTimestamp()}, SQL_INSERT_MEASUREMENT_VALUE_TYPES);
319        
320        AccelerometerData aData = measurement.getAccelerometerData();
321        if(aData != null){
322          insertData(measurementId, aData);
323        }
324        
325        LocationData lData = measurement.getLocationData();
326        if(lData != null){
327          insertData(measurementId, lData);
328        }
329        
330        measurement.setMeasurementId(measurementId);
331        return measurementId;
332      }
333    });
334  }
335  
336  /**
337   * 
338   * @param measurementId 
339   * @param data
340   */
341  private void insertData(String measurementId, AccelerometerData data) {
342    getJdbcTemplate().update(SQL_INSERT_ACCELEROMETER_DATA, new Object[]{measurementId, data.getxAcceleration(), data.getyAcceleration(), data.getzAcceleration(), data.getSystematicError(), data.getTimestamp()}, SQL_INSERT_ACCELEROMETER_DATA_VALUE_TYPES);
343  }
344  
345  /**
346   * 
347   * @param measurementId
348   * @param data
349   */
350  private void insertData(String measurementId, LocationData data) {
351    getJdbcTemplate().update(SQL_INSERT_LOCATION_DATA, new Object[]{measurementId, data.getHeading(), data.getLatitude(), data.getLongitude(), data.getSpeed(), data.getTimestamp()}, SQL_INSERT_LOCATION_DATA_VALUE_TYPES);
352  }
353}