@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 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 ); } }
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(); }
@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; }
@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(); } }
orderFields.add( statementBuilder.columnQuote( prop[0] ) + " " + prop[1] ); break;
orderFields.add( statementBuilder.columnQuote( prop[0] ) + " " + prop[1] ); break;
columnName = statementBuilder.columnQuote( el1 );
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; }
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 + ";
orderFields.add( statementBuilder.columnQuote( prop[0] ) + " " + prop[1] ); break;
String col = statementBuilder.columnQuote( item.getItemId() );
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 ); } }
String col = statementBuilder.columnQuote( item.getItemId() );
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 col = statementBuilder.columnQuote( item.getItemId() ); final String optCol = item.getItemId() + "opt";
final String col = statementBuilder.columnQuote( item.getItemId() ); final String col = statementBuilder.columnQuote( item.getItemId() );