dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #26323
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 13039: Analytics, consistently quoting column names
------------------------------------------------------------
revno: 13039
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2013-11-27 20:02:17 +0100
message:
Analytics, consistently quoting column names
modified:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2013-11-04 18:14:47 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2013-11-27 19:02:17 +0000
@@ -587,11 +587,11 @@
if ( getPeriods() != null ) // Period is dimension
{
- setDimensionOptions( PERIOD_DIM_ID, DimensionType.PERIOD, dataPeriodType.getName(), new ArrayList<NameableObject>( dataPeriodAggregationPeriodMap.keySet() ) );
+ setDimensionOptions( PERIOD_DIM_ID, DimensionType.PERIOD, dataPeriodType.getName().toLowerCase(), new ArrayList<NameableObject>( dataPeriodAggregationPeriodMap.keySet() ) );
}
else // Period is filter
{
- setFilterOptions( PERIOD_DIM_ID, DimensionType.PERIOD, dataPeriodType.getName(), new ArrayList<NameableObject>( dataPeriodAggregationPeriodMap.keySet() ) );
+ setFilterOptions( PERIOD_DIM_ID, DimensionType.PERIOD, dataPeriodType.getName().toLowerCase(), new ArrayList<NameableObject>( dataPeriodAggregationPeriodMap.keySet() ) );
}
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2013-10-16 20:10:21 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2013-11-27 19:02:17 +0000
@@ -401,7 +401,7 @@
for ( String periodType : periodTypePeriodMap.keySet() )
{
DataQueryParams query = params.instance();
- query.setDimensionOptions( PERIOD_DIM_ID, DimensionType.PERIOD, periodType, periodTypePeriodMap.get( periodType ) );
+ query.setDimensionOptions( PERIOD_DIM_ID, DimensionType.PERIOD, periodType.toLowerCase(), periodTypePeriodMap.get( periodType ) );
query.setPeriodType( periodType );
queries.add( query );
}
@@ -416,7 +416,7 @@
for ( String periodType : periodTypePeriodMap.keySet() )
{
- params.getFilters().add( new BaseDimensionalObject( filter.getDimension(), filter.getType(), periodType, periodTypePeriodMap.get( periodType ) ) );
+ params.getFilters().add( new BaseDimensionalObject( filter.getDimension(), filter.getType(), periodType.toLowerCase(), periodTypePeriodMap.get( periodType ) ) );
}
queries.add( params );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2013-08-31 18:07:17 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2013-11-27 19:02:17 +0000
@@ -59,6 +59,7 @@
import org.hisp.dhis.common.DimensionalObject;
import org.hisp.dhis.common.ListMap;
import org.hisp.dhis.common.NameableObject;
+import org.hisp.dhis.jdbc.StatementBuilder;
import org.hisp.dhis.period.Period;
import org.hisp.dhis.period.PeriodType;
import org.hisp.dhis.system.util.MathUtils;
@@ -90,6 +91,9 @@
@Autowired
private JdbcTemplate jdbcTemplate;
+ @Autowired
+ private StatementBuilder statementBuilder;
+
// -------------------------------------------------------------------------
// Implementation
// -------------------------------------------------------------------------
@@ -180,10 +184,11 @@
*/
private String getSelectClause( DataQueryParams params )
{
- String sql = "select " + getCommaDelimitedString( params.getQueryDimensions() ) + ", ";
+ String sql = "select " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ) + ", ";
if ( params.isAggregationType( AVERAGE_INT ) )
{
+ System.out.println("type name " + params.getPeriodType());
int days = PeriodType.getPeriodTypeByName( params.getPeriodType() ).getFrequencyOrder();
sql += "sum(daysxvalue) / " + days;
@@ -216,7 +221,7 @@
for ( String partition : params.getPartitions().getPartitions() )
{
- sql += "select " + getCommaDelimitedString( params.getQueryDimensions() ) + ", ";
+ sql += "select " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ) + ", ";
if ( params.isAggregationType( AVERAGE_INT ) )
{
@@ -254,7 +259,9 @@
{
if ( !dim.isAllItems() )
{
- sql += sqlHelper.whereAnd() + " " + dim.getDimensionName() + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") ";
+ String col = statementBuilder.columnQuote( dim.getDimensionName() );
+
+ sql += sqlHelper.whereAnd() + " " + col + " in (" + getQuotedCommaDelimitedString( getUids( dim.getItems() ) ) + ") ";
}
}
@@ -272,7 +279,9 @@
{
if ( filter.hasItems() )
{
- sql += filter.getDimensionName() + " in (" + getQuotedCommaDelimitedString( getUids( filter.getItems() ) ) + ") or ";
+ String col = statementBuilder.columnQuote( filter.getDimensionName() );
+
+ sql += col + " in (" + getQuotedCommaDelimitedString( getUids( filter.getItems() ) ) + ") or ";
}
}
}
@@ -288,7 +297,7 @@
*/
private String getGroupByClause( DataQueryParams params )
{
- String sql = "group by " + getCommaDelimitedString( params.getQueryDimensions() );
+ String sql = "group by " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() );
return sql;
}
@@ -378,17 +387,17 @@
/**
* Generates a comma-delimited string based on the dimension names of the
- * given dimensions.
+ * given dimensions where each dimension name is quoted.
*/
- private String getCommaDelimitedString( Collection<DimensionalObject> dimensions )
- {
+ private String getCommaDelimitedQuotedColumns( Collection<DimensionalObject> dimensions )
+ {
final StringBuilder builder = new StringBuilder();
if ( dimensions != null && !dimensions.isEmpty() )
{
for ( DimensionalObject dimension : dimensions )
{
- builder.append( dimension.getDimensionName() ).append( "," );
+ builder.append( statementBuilder.columnQuote( dimension.getDimensionName() ) ).append( "," );
}
return builder.substring( 0, builder.length() - 1 );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2013-11-18 12:07:45 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2013-11-27 19:02:17 +0000
@@ -91,11 +91,13 @@
/**
* Returns a list of string arrays in where the first index holds the database
* column name, the second index holds the database column data type and the
- * third column holds a table alias and name, i.e.:
+ * third column holds a table alias and name. Column names are quoted.
*
- * 0 = database column name
- * 1 = database column data type
- * 2 = column alias and name
+ * <ul>
+ * <li>0 = database column name</li>
+ * <li>1 = database column data type</li>
+ * <li>2 = column alias and name</li>
+ * </ul>
*/
protected abstract List<String[]> getDimensionColumns( AnalyticsTable table );
@@ -228,6 +230,14 @@
// -------------------------------------------------------------------------
/**
+ * Quotes the given column name.
+ */
+ protected String quote( String column )
+ {
+ return statementBuilder.columnQuote( column );
+ }
+
+ /**
* Indicates whether the given table exists and has at least one row.
*/
protected boolean hasRows( String tableName )
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2013-11-18 13:30:37 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2013-11-27 19:02:17 +0000
@@ -201,25 +201,25 @@
for ( DataElementGroupSet groupSet : dataElementGroupSets )
{
- String[] col = { groupSet.getUid(), "character(11)", "degs." + groupSet.getUid() };
+ String[] col = { quote( groupSet.getUid() ), "character(11)", "degs." + quote( groupSet.getUid() ) };
columns.add( col );
}
for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets )
{
- String[] col = { groupSet.getUid(), "character(11)", "ougs." + groupSet.getUid() };
+ String[] col = { quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ) };
columns.add( col );
}
for ( DataElementCategory category : categories )
{
- String[] col = { category.getUid(), "character(11)", "cs." + category.getUid() };
+ String[] col = { quote( category.getUid() ), "character(11)", "cs." + quote( category.getUid() ) };
columns.add( col );
}
for ( OrganisationUnitLevel level : levels )
{
- String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String column = quote( PREFIX_ORGUNITLEVEL + level.getLevel() );
String[] col = { column, "character(11)", "ous." + column };
columns.add( col );
}
@@ -228,7 +228,7 @@
for ( PeriodType periodType : periodTypes )
{
- String column = periodType.getName().toLowerCase();
+ String column = quote( periodType.getName().toLowerCase() );
String[] col = { column, "character varying(10)", "ps." + column };
columns.add( col );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java 2013-11-18 13:30:37 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java 2013-11-27 19:02:17 +0000
@@ -149,20 +149,20 @@
for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets )
{
- String[] col = { groupSet.getUid(), "character(11)", "ougs." + groupSet.getUid() };
+ String[] col = { quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ) };
columns.add( col );
}
for ( OrganisationUnitLevel level : levels )
{
- String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String column = quote( PREFIX_ORGUNITLEVEL + level.getLevel() );
String[] col = { column, "character(11)", "ous." + column };
columns.add( col );
}
for ( PeriodType periodType : PeriodType.getAvailablePeriodTypes().subList( 0, 7 ) )
{
- String column = periodType.getName().toLowerCase();
+ String column = quote( periodType.getName().toLowerCase() );
String[] col = { column, "character varying(10)", "ps." + column };
columns.add( col );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-11-18 13:30:37 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-11-27 19:02:17 +0000
@@ -143,13 +143,13 @@
for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets )
{
- String[] col = { groupSet.getUid(), "character(11)", "ougs." + groupSet.getUid() };
+ String[] col = { quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ) };
columns.add( col );
}
for ( OrganisationUnitLevel level : levels )
{
- String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String column = quote( PREFIX_ORGUNITLEVEL + level.getLevel() );
String[] col = { column, "character(11)", "ous." + column };
columns.add( col );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 2013-11-18 13:30:37 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 2013-11-27 19:02:17 +0000
@@ -187,7 +187,7 @@
for ( OrganisationUnitLevel level : levels )
{
- String column = PREFIX_ORGUNITLEVEL + level.getLevel();
+ String column = quote( PREFIX_ORGUNITLEVEL + level.getLevel() );
String[] col = { column, "character(11)", "ous." + column };
columns.add( col );
}
@@ -196,7 +196,7 @@
for ( PeriodType periodType : periodTypes )
{
- String column = periodType.getName().toLowerCase();
+ String column = quote( periodType.getName().toLowerCase() );
String[] col = { column, "character varying(10)", "dps." + column };
columns.add( col );
}
@@ -204,27 +204,27 @@
for ( DataElement dataElement : table.getProgram().getAllDataElements() )
{
String select = "(select value from patientdatavalue where programstageinstanceid=" +
- "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + ") as " + dataElement.getUid();
+ "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + ") as " + quote( dataElement.getUid() );
- String[] col = { dataElement.getUid(), "character varying(255)", select };
+ String[] col = { quote( dataElement.getUid() ), "character varying(255)", select };
columns.add( col );
}
for ( PatientAttribute attribute : table.getProgram().getPatientAttributes() )
{
String select = "(select value from patientattributevalue where patientid=pi.patientid and " +
- "patientattributeid=" + attribute.getId() + ") as " + attribute.getUid();
+ "patientattributeid=" + attribute.getId() + ") as " + quote( attribute.getUid() );
- String[] col = { attribute.getUid(), "character varying(255)", select };
+ String[] col = { quote( attribute.getUid() ), "character varying(255)", select };
columns.add( col );
}
for ( PatientIdentifierType identifierType : table.getProgram().getPatientIdentifierTypes() )
{
String select = "(select identifier from patientidentifier where patientid=pi.patientid and " +
- "patientidentifiertypeid=" + identifierType.getId() + ") as " + identifierType.getUid();
+ "patientidentifiertypeid=" + identifierType.getId() + ") as " + quote( identifierType.getUid() );
- String[] col = { identifierType.getUid(), "character varying(31)", select };
+ String[] col = { quote( identifierType.getUid() ), "character varying(31)", select };
columns.add( col );
}
