@Override public boolean viewTableExists( String viewTableName ) { try { jdbcTemplate.queryForRowSet( "select * from " + statementBuilder.columnQuote( viewTableName ) + " limit 1" ); return true; } catch ( BadSqlGrammarException ex ) { return false; // View does not exist } }
@Override public void analyzeTable( String tableName ) { String sql = StringUtils.trimToEmpty( statementBuilder.getAnalyze( tableName ) ); executeSilently( sql ); }
/** * Returns the filter value for the given query item. * * @param filter the {@link QueryFilter}. * @param item the {@link QueryItem}. */ protected String getSqlFilter( QueryFilter filter, QueryItem item ) { String encodedFilter = statementBuilder.encode( filter.getFilter(), false ); return item.getSqlFilter( filter, encodedFilter ); }
protected String getBoundedDataValueSelectSql( String programStageUid, String dataElementUid, Date reportingStartDate, Date reportingEndDate, ProgramIndicator programIndicator ) { if ( programIndicator.hasNonDefaultBoundaries() && programIndicator.hasEventBoundary() ) { String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); String columnName = "\"" + dataElementUid + "\""; return "(select " + columnName + " from " + eventTableName + " where " + eventTableName + ".pi = enrollmenttable.pi and " + columnName + " is not null " + ( programIndicator.getEndEventBoundary() != null ? ( "and " + statementBuilder.getBoundaryCondition( programIndicator.getEndEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + (programIndicator.getStartEventBoundary() != null ? ("and " + statementBuilder.getBoundaryCondition( programIndicator.getStartEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "" ) + "and ps = '" + programStageUid + "' " + "order by executiondate " + "desc limit 1 )"; } else { return statementBuilder.columnQuote( programStageUid + ProgramIndicator.DB_SEPARATOR_ID + dataElementUid ); } }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) { final String dbl = statementBuilder.getDoubleColumnType(); final boolean skipDataTypeValidation = (Boolean) systemSettingManager.getSystemSetting( SettingKey.SKIP_DATA_TYPE_VALIDATION_IN_ANALYTICS_TABLE_EXPORT ); final String approvalClause = getApprovalJoinClause( partition.getYear() ); final String numericClause = skipDataTypeValidation ? "" : ( "and dv.value " + statementBuilder.getRegexpMatch() + " '" + MathUtils.NUMERIC_LENIENT_REGEXP + "' " ); String intClause = "( dv.value != '0' or de.aggregationtype in ('" + AggregationType.AVERAGE + ',' + AggregationType.AVERAGE_SUM_ORG_UNIT + "') or de.zeroissignificant = true ) " + numericClause; populateTable( params, partition, "cast(dv.value as " + dbl + ")", "null", ValueType.NUMERIC_TYPES, intClause, approvalClause ); populateTable( params, partition, "1", "null", Sets.newHashSet( ValueType.BOOLEAN, ValueType.TRUE_ONLY ), "dv.value = 'true'", approvalClause ); populateTable( params, partition, "0", "null", Sets.newHashSet( ValueType.BOOLEAN ), "dv.value = 'false'", approvalClause ); populateTable( params, partition, "null", "dv.value", Sets.union( ValueType.TEXT_TYPES, ValueType.DATE_TYPES ), null, approvalClause ); }
@Override public List<UserMessage> getLastRecipients( User user, Integer first, Integer max ) { Assert.notNull( user, "User must be specified" ); String sql = " select distinct userinfoid, surname, firstname from userinfo uf " + "join usermessage um on (uf.userinfoid = um.userid) " + "join messageconversation_usermessages mu on (um.usermessageid = mu.usermessageid) " + "join messageconversation mc on (mu.messageconversationid = mc.messageconversationid) " + "where mc.lastsenderid = " + user.getId(); sql += " order by userinfoid desc"; if ( first != null && max != null ) { sql += " " + statementBuilder.limitRecord( first, max ); } return jdbcTemplate.query( sql, ( resultSet, count ) -> { UserMessage recipient = new UserMessage(); recipient.setId( resultSet.getInt( 1 ) ); recipient.setLastRecipientSurname( resultSet.getString( 2 ) ); recipient.setLastRecipientFirstname( resultSet.getString( 3 ) ); return recipient; } ); }
@Override public String evaluate( ProgramIndicator programIndicator, StatementBuilder statementBuilder, Date reportingStartDate, Date reportingEndDate, String... args ) { if ( args == null || args.length != 2 ) { throw new IllegalArgumentException( "Illegal arguments, expected 2 arguments: start-date, end-date" ); } for ( int i = 0; i < args.length; i++ ) { String arg = args[i].replaceAll( "^\"|^'|\"$|'$", "" ).trim(); Matcher matcher = AnalyticsPeriodBoundary.COHORT_HAVING_PROGRAM_STAGE_PATTERN.matcher( arg ); if ( matcher.find() ) { String programStageUid = matcher.group( AnalyticsPeriodBoundary.PROGRAM_STAGE_REGEX_GROUP ); args[i] = statementBuilder.getProgramIndicatorColumnSelectSql( programStageUid, "executiondate", reportingStartDate, reportingEndDate, programIndicator ); } } String startDate = args[0]; String endDate = args[1]; return compare( startDate, endDate ); }
@Override @Async public Future<?> vacuumTablesAsync( ConcurrentLinkedQueue<AnalyticsTablePartition> partitions ) { taskLoop: while ( true ) { AnalyticsTablePartition partition = partitions.poll(); if ( partition == null ) { break taskLoop; } final String sql = statementBuilder.getVacuum( partition.getTempTableName() ); log.debug( "Vacuum SQL: " + sql ); jdbcTemplate.execute( sql ); } return ConcurrentUtils.getImmediateFuture(); }
@Override @Transactional public void generateOrganisationUnitGroupSetTable() { resourceTableStore.generateResourceTable( new OrganisationUnitGroupSetResourceTable( idObjectManager.getDataDimensionsNoAcl( OrganisationUnitGroupSet.class ), statementBuilder.supportsPartialIndexes(), organisationUnitService.getNumberOfOrganisationalLevels() ) ); }
@Override public Integer getCompleteDataSetRegistrationsWithTimeliness( DataSet dataSet, Collection<Integer> periods, Collection<Integer> relevantSources ) { if ( relevantSources == null || relevantSources.isEmpty() || periods == null || periods.isEmpty() ) { return 0; } final String sql = "SELECT COUNT(*) " + "FROM completedatasetregistration cr " + "JOIN period pe ON (cr.periodid = pe.periodid) " + "WHERE cr.datasetid = " + dataSet.getId() + " " + "AND cr.periodid IN ( " + getCommaDelimitedString( periods ) + " ) " + "AND cr.sourceid IN ( " + getCommaDelimitedString( relevantSources ) + " ) " + "AND cr.date <= " + statementBuilder.getAddDate( "pe.enddate", dataSet.getTimelyDays() ); return statementManager.getHolder().queryForInteger( sql ); }
@Override public void dropViewTable( SqlView sqlView ) { String viewName = sqlView.getViewName(); try { final String sql = TYPE_DROP_PREFIX_MAP.get( sqlView.getType() ) + " IF EXISTS " + statementBuilder.columnQuote( viewName ); log.debug( "Drop view SQL: " + sql ); jdbcTemplate.update( sql ); } catch ( Exception ex ) { log.warn( "Could not drop view: " + viewName, ex ); } }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
@Override public String getCriteriaSqlClause(Map<String, String> criteria, SqlHelper sqlHelper ) { String sql = StringUtils.EMPTY; if ( criteria != null && !criteria.isEmpty() ) { sqlHelper = ObjectUtils.firstNonNull( sqlHelper, new SqlHelper() ); for ( String filter : criteria.keySet() ) { sql += sqlHelper.whereAnd() + " " + statementBuilder.columnQuote( filter ) + "='" + criteria.get( filter ) + "' "; } } return sql; }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "daysxvalue" ), dbl, "daysxvalue" ), new AnalyticsTableColumn( quote( "daysno" ), "integer not null", "daysno" ), new AnalyticsTableColumn( quote( "value" ), dbl, "value" ), new AnalyticsTableColumn( quote( "textvalue" ), "text", "textvalue" ) ); }
public String getAnyValueExistsClauseAnalyticsSql( String expression, AnalyticsType analyticsType ) { Set<String> uids = ProgramIndicator.getDataElementAndAttributeIdentifiers( expression, analyticsType ); if ( uids.isEmpty() ) { return null; } String sql = StringUtils.EMPTY; for ( String uid : uids ) { sql += statementBuilder.columnQuote( uid ) + " is not null or "; } return TextUtils.removeLastOr( sql ).trim(); }
return "cast(value as " + statementBuilder.getDoubleColumnType() + ")";
@Override public String createViewTable( SqlView sqlView ) { dropViewTable( sqlView ); final String sql = TYPE_CREATE_PREFIX_MAP.get( sqlView.getType() ) + statementBuilder.columnQuote( sqlView.getViewName() ) + " AS " + sqlView.getSqlQuery(); log.debug( "Create view SQL: " + sql ); try { jdbcTemplate.execute( sql ); return null; } catch ( BadSqlGrammarException ex ) { return ex.getCause().getMessage(); } }
return statementBuilder.getDoubleColumnType();
private String getSqlForView( Grid grid, SqlView sqlView, Map<String, String> criteria, List<String> filters, List<String> fields ) { String sql = "select " + QueryUtils.parseSelectFields( fields ) + " from " + statementBuilder.columnQuote( sqlView.getViewName() ) + " "; boolean hasCriteria = criteria != null && !criteria.isEmpty(); boolean hasFilter = filters != null && !filters.isEmpty(); if ( hasCriteria || hasFilter ) { SqlHelper sqlHelper = new SqlHelper(); if ( hasCriteria ) { sql += getCriteriaSqlClause( criteria, sqlHelper ); } if ( hasFilter ) { sql += parseFilters( filters, sqlHelper ); } } return sql; }