protected long getUnixTime(SqlRowSet rowSet, String field) { return rowSet.getLong(field); }
private SequenceRange applyNextRange0(String seqName) { SequenceRange result = null; SqlRowSet rowSet = jdbcTemplate.queryForRowSet(selectSql, seqName); if(rowSet.next()) { long maxLimit = rowSet.getLong("max_limit"); long minLimit = rowSet.getLong("min_limit"); int range = rowSet.getInt("step"); int value = rowSet.getInt("value"); long min = value; long max = value + range; if(max < 0 || max-1 > maxLimit) { min = minLimit; max = minLimit + range; } int updatedRows = jdbcTemplate.update(updateSql, max, seqName, value); if(updatedRows==1) { result = new SequenceRange(min, max, getDbDate(), getDbName()); } } else { long value = defaultMinLimit+step; int insertedRows = jdbcTemplate.update(insertSql, seqName, defaultMinLimit, defaultMaxLimit, step, value); if(insertedRows == 1) { result = new SequenceRange(defaultMinLimit, value, getDbDate(), getDbName()); } } return result; }
@Override public Rectangle getRectangle( EventQueryParams params ) { String clusterField = params.getCoordinateField(); String quotedClusterField = quoteAlias( clusterField ); String sql = "select count(psi) as " + COL_COUNT + ", ST_Extent(" + quotedClusterField + ") as " + COL_EXTENT + " "; sql += getFromClause( params ); sql += getWhereClause( params ); log.debug( String.format( "Analytics event count and extent SQL: %s", sql ) ); Rectangle rectangle = new Rectangle(); SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); if ( rowSet.next() ) { Object extent = rowSet.getObject( COL_EXTENT ); rectangle.setCount( rowSet.getLong( COL_COUNT ) ); rectangle.setExtent( extent != null ? String.valueOf( rowSet.getObject( COL_EXTENT ) ) : null ); } return rectangle; }
@Override public Grid getEventClusters( EventQueryParams params, Grid grid, int maxLimit ) { String clusterField = params.getCoordinateField(); String quotedClusterField = quoteAlias( clusterField ); List<String> columns = Lists.newArrayList( "count(psi) as count", "ST_AsText(ST_Centroid(ST_Collect(" + quotedClusterField + "))) as center", "ST_Extent(" + quotedClusterField + ") as extent" ); columns.add( params.isIncludeClusterPoints() ? "array_to_string(array_agg(psi), ',') as points" : "case when count(psi) = 1 then array_to_string(array_agg(psi), ',') end as points" ); String sql = "select " + StringUtils.join( columns, "," ) + " "; sql += getFromClause( params ); sql += getWhereClause( params ); sql += "group by ST_SnapToGrid(ST_Transform(" + quotedClusterField + ", 3785), " + params.getClusterSize() + ") "; log.debug( String.format( "Analytics event cluster SQL: %s", sql ) ); SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql ); while ( rowSet.next() ) { grid.addRow() .addValue( rowSet.getLong( "count" ) ) .addValue( rowSet.getString( "center" ) ) .addValue( rowSet.getString( "extent" ) ) .addValue( rowSet.getString( "points" ) ); } return grid; }
private void verifyDb(TestStorage storage) { JdbcTemplate jdbcTemplate = dbService.getJdbcTemplate(); long totalCount = verifyDbObjects(jdbcTemplate, storage, storage.getRootObjects()); SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT count(target_id) FROM " + AbstractDbService.DEFAULT_OBJECTS_TABLE_NAME + " WHERE target_id != ''"); Assert.assertTrue(rowSet.next()); Assert.assertEquals(totalCount, rowSet.getLong(1)); jdbcTemplate.update("DELETE FROM " + AbstractDbService.DEFAULT_OBJECTS_TABLE_NAME); }
private long verifyDbObjects(JdbcTemplate jdbcTemplate, TestStorage storage, Collection<? extends SyncObject> objects) { Date now = new Date(); long count = 0; for (SyncObject object : objects) { count++; String identifier = storage.getIdentifier(object.getRelativePath(), object.getMetadata().isDirectory()); SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT * FROM " + AbstractDbService.DEFAULT_OBJECTS_TABLE_NAME + " WHERE target_id=?", identifier); Assert.assertTrue(rowSet.next()); Assert.assertEquals(identifier, rowSet.getString("target_id")); Assert.assertEquals(object.getMetadata().isDirectory(), rowSet.getBoolean("is_directory")); Assert.assertEquals(object.getMetadata().getContentLength(), rowSet.getLong("size")); // mtime in the DB is actually pulled from the target system, so we don't know what precision it will be in // or if the target system's clock is in sync, but let's assume it will always be within 5 minutes Assert.assertTrue(Math.abs(object.getMetadata().getModificationTime().getTime() - rowSet.getLong("mtime")) < 5 * 60 * 1000); Assert.assertEquals(ObjectStatus.Verified.getValue(), rowSet.getString("status")); long transferStart = rowSet.getLong("transfer_start"), transferComplete = rowSet.getLong("transfer_complete"); if (transferStart > 0) Assert.assertTrue(now.getTime() - transferStart < 10 * 60 * 1000); // less than 10 minutes ago if (transferComplete > 0) Assert.assertTrue(now.getTime() - transferComplete < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertTrue(now.getTime() - rowSet.getLong("verify_start") < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertTrue(now.getTime() - rowSet.getLong("verify_complete") < 10 * 60 * 1000); // less than 10 minutes ago Assert.assertEquals(0, rowSet.getInt("retry_count")); if (object.getMetadata().isDirectory()) count += verifyDbObjects(jdbcTemplate, storage, storage.getChildren(identifier)); } return count; } }