/** * Drops and creates the table partitions for the given analytics table. * * @param table the {@link AnalyticsTable}. */ protected void createTempTablePartitions( AnalyticsTable table ) { for ( AnalyticsTablePartition partition : table.getPartitionTables() ) { final String tableName = partition.getTempTableName(); final List<String> checks = getPartitionChecks( partition ); String sqlCreate = "create table " + tableName + " "; if ( !checks.isEmpty() ) { StringBuilder sqlCheck = new StringBuilder( "(" ); checks.stream().forEach( check -> sqlCheck.append( "check (" + check + "), " ) ); sqlCreate += TextUtils.removeLastComma( sqlCheck.toString() ) + ") "; } sqlCreate += "inherits (" + table.getTempTableName() + ") " + getTableOptions(); log.info( String.format( "Creating partition table: %s", tableName ) ); log.debug( "Create SQL: " + sqlCreate ); jdbcTemplate.execute( sqlCreate ); } }
/** * Drops and creates the given analytics table. * * @param table the {@link AnalyticsTable}. */ protected void createTempTable( AnalyticsTable table ) { validateDimensionColumns( table.getDimensionColumns() ); final String tableName = table.getTempTableName(); String sqlCreate = "create table " + tableName + " ("; for ( AnalyticsTableColumn col : ListUtils.union( table.getDimensionColumns(), table.getValueColumns() ) ) { sqlCreate += col.getName() + " " + col.getDataType() + ","; } sqlCreate = TextUtils.removeLastComma( sqlCreate ) + ") " + getTableOptions(); log.info( String.format( "Creating table: %s, columns: %d", tableName, table.getDimensionColumns().size() ) ); log.debug( "Create SQL: " + sqlCreate ); jdbcTemplate.execute( sqlCreate ); }
@Override public String getCreateTempTableStatement() { String statement = "create table " + getTempTableName() + " (" + "organisationunitid integer not null, " + "organisationunitname varchar(230), " + "startdate date, "; for ( OrganisationUnitGroupSet groupSet : objects ) { statement += quote( groupSet.getName() ) + " varchar(230), "; statement += quote( groupSet.getUid() ) + " character(11), "; } return removeLastComma( statement ) + ")"; }
@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " " + "select d.dataelementid as dataelementid, d.name as dataelementname, "; for ( DataElementGroupSet groupSet : objects ) { sql += "(" + "select deg.name from dataelementgroup deg " + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid " + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + "where degm.dataelementid = d.dataelementid " + "limit 1) as " + quote( groupSet.getName() ) + ", "; sql += "(" + "select deg.uid from dataelementgroup deg " + "inner join dataelementgroupmembers degm on degm.dataelementgroupid = deg.dataelementgroupid " + "inner join dataelementgroupsetmembers degsm on degsm.dataelementgroupid = degm.dataelementgroupid and degsm.dataelementgroupsetid = " + groupSet.getId() + " " + "where degm.dataelementid = d.dataelementid " + "limit 1) as " + quote( groupSet.getUid() ) + ", "; } sql = TextUtils.removeLastComma( sql ) + " "; sql += "from dataelement d"; return Optional.of( sql ); }
@Override public Optional<String> getPopulateTempTableStatement() { String sql = "insert into " + getTempTableName() + " " + "select i.indicatorid as indicatorid, i.name as indicatorname, "; for ( IndicatorGroupSet groupSet : objects ) { sql += "(" + "select ig.name from indicatorgroup ig " + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid " + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + "where igm.indicatorid = i.indicatorid " + "limit 1) as " + quote( groupSet.getName() ) + ", "; sql += "(" + "select ig.uid from indicatorgroup ig " + "inner join indicatorgroupmembers igm on igm.indicatorgroupid = ig.indicatorgroupid " + "inner join indicatorgroupsetmembers igsm on igsm.indicatorgroupid = igm.indicatorgroupid and igsm.indicatorgroupsetid = " + groupSet.getId() + " " + "where igm.indicatorid = i.indicatorid " + "limit 1) as " + quote( groupSet.getUid() ) + ", "; } sql = TextUtils.removeLastComma( sql ) + " "; sql += "from indicator i"; return Optional.of( sql ); }
String sql = TextUtils.removeLastComma( "select " + countClause + " as value," + StringUtils.join( getSelectColumns( params ), "," ) + " " );
sql = removeLastComma( sql ) + ") as " + quote( groupSet.getName() ) + ", "; sql = removeLastComma( sql ) + ") as " + quote( groupSet.getUid() ) + ", "; sql = removeLastComma( sql ) + " "; sql += "from organisationunit ou " + "inner join _orgunitstructure ous on ous.organisationunitid = ou.organisationunitid";
sql = removeLastComma( sql ) + " ";
sql = TextUtils.removeLastComma( sql ) + ") select "; sql = TextUtils.removeLastComma( sql ) + " ";
/** * Returns an SQL sort clause. * * @param params the {@link EventQueryParams}. */ private String getSortClause( EventQueryParams params ) { String sql = ""; if ( params.isSorting() ) { sql += "order by "; for ( DimensionalItemObject item : params.getAsc() ) { sql += quoteAlias( item.getUid() ) + " asc,"; } for ( DimensionalItemObject item : params.getDesc() ) { sql += quoteAlias( item.getUid() ) + " desc,"; } sql = removeLastComma( sql ) + " "; } return sql; }
sql = removeLastComma( sql ) + " ";
sql = TextUtils.removeLastComma( sql ) + ") select "; sql = TextUtils.removeLastComma( sql ) + " ";
insert = TextUtils.removeLastComma( insert ) + ") ";
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) { final String tableName = partition.getTempTableName(); String sql = "insert into " + tableName + " ("; List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns(); validateDimensionColumns( columns ); for ( AnalyticsTableColumn col : ListUtils.union( columns, values ) ) { sql += col.getName() + ","; } sql = TextUtils.removeLastComma( sql ) + ") select "; for ( AnalyticsTableColumn col : columns ) { sql += col.getAlias() + ","; } sql += "1 as value " + "from _datasetorganisationunitcategory doc " + "inner join dataset ds on doc.datasetid=ds.datasetid " + "inner join organisationunit ou on doc.organisationunitid=ou.organisationunitid " + "left join _orgunitstructure ous on doc.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on doc.organisationunitid=ougs.organisationunitid " + "left join categoryoptioncombo ao on doc.attributeoptioncomboid=ao.categoryoptioncomboid " + "left join _categorystructure acs on doc.attributeoptioncomboid=acs.categoryoptioncomboid "; populateAndLog( sql, tableName ); }
insert = TextUtils.removeLastComma( insert ) + ") ";
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) { final String tableName = partition.getTempTableName(); String sql = "insert into " + partition.getTempTableName() + " ("; List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns(); validateDimensionColumns( columns ); for ( AnalyticsTableColumn col : ListUtils.union( columns, values ) ) { sql += col.getName() + ","; } sql = TextUtils.removeLastComma( sql ) + ") select "; for ( AnalyticsTableColumn col : columns ) { sql += col.getAlias() + ","; } sql += "1 as value " + "from orgunitgroupmembers ougm " + "inner join orgunitgroup oug on ougm.orgunitgroupid=oug.orgunitgroupid " + "left join _orgunitstructure ous on ougm.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on ougm.organisationunitid=ougs.organisationunitid"; populateAndLog( sql, tableName ); }
sql = TextUtils.removeLastComma( sql ) + " "; sql += "from categoryoptioncombo coc ";
sql = TextUtils.removeLastComma( sql ) + ") select ";