@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 } }
/** * 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 ); }
Date endDate ) final String dbl = statementBuilder.getDoubleColumnType(); sql += "case when " + statementBuilder.columnQuote( uid ) + " is not null then 1 else 0 end + "; sql += "case when " + statementBuilder.columnQuote( uid ) + " >= 0 then 1 else 0 end + ";
@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 ); }
columnName = statementBuilder.getProgramIndicatorDataValueSelectSql( el1, el2, startDate, endDate, programIndicator ); columnName = statementBuilder.columnQuote( el1 );
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 ); } }
final String regexp = statementBuilder.getRegexpMatch(); final String wordStart = statementBuilder.getRegexpWordStart(); final String wordEnd = statementBuilder.getRegexpWordEnd(); final String anyChar = "\\.*?"; final String col = statementBuilder.columnQuote( item.getItemId() ); final String encodedFilter = statementBuilder.encode( filter.getFilter(), false ); final String query = statementBuilder.encode( queryToken, false ); final String col = statementBuilder.columnQuote( item.getItemId() );
final String col = statementBuilder.columnQuote( item.getItemId() ); final String optCol = item.getItemId() + "opt"; final String encodedFilter = statementBuilder.encode( filter.getFilter(), false );
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
"and dv.periodid in (" + periodIds + ") " + "and ( " + "cast( dv.value as " + statementBuilder.getDoubleColumnType() + " ) < mm.minimumvalue " + "or cast( dv.value as " + statementBuilder.getDoubleColumnType() + " ) > mm.maximumvalue ) " + "and ("; sql += "and dv.deleted is false "; sql += statementBuilder.limitRecord( 0, limit );
public String countWhereCondition( ProgramIndicator programIndicator, StatementBuilder sb, Date reportingStartDate, Date reportingEndDate, String element, String condition ) { Matcher matcher = COHORT_HAVING_DATA_ELEMENT_PATTERN.matcher( element ); if ( matcher.find() ) { String ps = matcher.group( PROGRAM_STAGE_REGEX_GROUP ); String de = matcher.group( DATA_ELEMENT_REGEX_GROUP ); String eventTableName = "analytics_event_" + programIndicator.getProgram().getUid(); String columnName = "\"" + de + "\""; return "(select count(" + columnName + ") from " + eventTableName + " where " + eventTableName + ".pi = " + StatementBuilder.ANALYTICS_TBL_ALIAS + ".pi and " + columnName + " is not null " + " and " + columnName + condition + " " + (programIndicator.getEndEventBoundary() != null ? ("and " + sb.getBoundaryCondition( programIndicator.getEndEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "") + (programIndicator.getStartEventBoundary() != null ? ("and " + sb.getBoundaryCondition( programIndicator.getStartEventBoundary(), programIndicator, reportingStartDate, reportingEndDate ) + " ") : "") + "and ps = '" + ps + "')"; } else { throw new IllegalArgumentException( "No data element found in argument 1:" + element + " in " + BaseCountIfProgramIndicatorFunction.KEY + " for program indciator:" + programIndicator.getUid() ); } }
@Override public void analyzeTable( String tableName ) { String sql = StringUtils.trimToEmpty( statementBuilder.getAnalyze( tableName ) ); executeSilently( sql ); }
@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; } ); }
final String col = statementBuilder.columnQuote( item.getItemId() ); final String queryCol = item.isNumeric() ? " CAST( " + (col + ".value AS NUMERIC)") : "lower(" + col + ".value)"; final String col = statementBuilder.columnQuote( item.getItemId() ); final String optCol = item.getItemId() + "opt"; final String encodedFilter = statementBuilder.encode( filter.getFilter(), false );
private List<AnalyticsTableColumn> getDimensionColumns( Program program ) final String dbl = statementBuilder.getDoubleColumnType(); final String numericClause = " and value " + statementBuilder.getRegexpMatch() + " '" + NUMERIC_LENIENT_REGEXP + "'"; final String dateClause = " and value " + statementBuilder.getRegexpMatch() + " '" + DATE_REGEXP + "'";
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
sql += statementBuilder.getBoundaryCondition( params.getProgramIndicator(), params.getEarliestStartDate(), params.getLatestEndDate(), sqlHelper );
final Optional<List<Object[]>> populateTableContent = resourceTable.getPopulateTempTableContent(); final List<String> createIndexSql = resourceTable.getCreateIndexStatements(); final String analyzeTableSql = statementBuilder.getAnalyze( resourceTable.getTableName() );
sql += "and dv.followup = true and dv.deleted is false "; sql += statementBuilder.limitRecord( 0, limit );
@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 ); } }