1
package org.hisp.dhis.jdbc.statementbuilder;
4
* Copyright (c) 2004-2007, University of Oslo
7
* Redistribution and use in source and binary forms, with or without
8
* modification, are permitted provided that the following conditions are met:
9
* * Redistributions of source code must retain the above copyright notice, this
10
* list of conditions and the following disclaimer.
11
* * Redistributions in binary form must reproduce the above copyright notice,
12
* this list of conditions and the following disclaimer in the documentation
13
* and/or other materials provided with the distribution.
14
* * Neither the name of the HISP project nor the names of its contributors may
15
* be used to endorse or promote products derived from this software without
16
* specific prior written permission.
18
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
19
* ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
20
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
21
* DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
22
* ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
23
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
24
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
25
* ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
26
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
27
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
30
import java.util.Iterator;
32
import java.util.Map.Entry;
34
import org.hisp.dhis.period.Period;
35
import org.hisp.dhis.period.RelativePeriodType;
38
* @author Lars Helge Overland
39
* @version $Id: PostgreSQLStatementBuilder.java 5715 2008-09-17 14:05:28Z larshelg $
41
public class PostgreSQLStatementBuilder
42
extends AbstractStatementBuilder
44
private static final String AUTO_INCREMENT = "nextval('hibernate_sequence')";
46
// -------------------------------------------------------------------------
48
// -------------------------------------------------------------------------
50
public PostgreSQLStatementBuilder()
55
// -------------------------------------------------------------------------
56
// AbstractStatementBuilder implementation
57
// -------------------------------------------------------------------------
59
public String getInsertStatementOpening( String table )
61
if ( autoIncrementColumnIndex != null && autoIncrementColumnName != null )
63
columns.add( autoIncrementColumnIndex, autoIncrementColumnName );
66
StringBuffer buffer = new StringBuffer();
68
buffer.append( "INSERT INTO " + table + " (" );
70
for ( String column : columns )
72
buffer.append( column + SEPARATOR );
75
if ( columns.size() > 0 )
77
buffer.deleteCharAt( buffer.length() - 1 );
80
buffer.append( BRACKET_END + " VALUES " );
84
return buffer.toString();
87
public String getNoColumnInsertStatementOpening( String table )
89
String sql = "INSERT INTO " + table + " VALUES ";
94
public String getInsertStatementValues()
96
if ( autoIncrementColumnIndex != null )
98
values.add( autoIncrementColumnIndex, AUTO_INCREMENT );
101
StringBuffer buffer = new StringBuffer();
103
buffer.append( BRACKET_START );
105
for ( String value : values )
107
buffer.append( value + SEPARATOR );
110
if ( values.size() > 0 )
112
buffer.deleteCharAt( buffer.length() - 1 );
115
buffer.append( BRACKET_END + SEPARATOR );
119
return buffer.toString();
122
public String getUpdateStatement( String table )
124
StringBuffer buffer = new StringBuffer();
126
buffer.append( "UPDATE " + table + " SET " );
128
Iterator<String> columnIterator = columns.iterator();
129
Iterator<String> valueIterator = values.iterator();
131
while ( columnIterator.hasNext() )
133
buffer.append( columnIterator.next() + "=" + valueIterator.next() + SEPARATOR );
136
if ( columns.size() > 0 && values.size() > 0 )
138
buffer.deleteCharAt( buffer.length() - 1 );
141
buffer.append( " WHERE " + identifierColumnName + "=" + identifierColumnValue );
146
return buffer.toString();
149
public String getValueStatement( String table, String returnField, String compareField, String value )
151
String sql = "SELECT " + returnField + " FROM " + table + " WHERE " + compareField + " = '" + sqlEncode( value ) + "'";
156
public String getValueStatement( String table, String returnField1, String returnField2, String compareField1, String value1, String compareField2, String value2 )
158
String sql = "SELECT " + returnField1 + ", " + returnField2 + " FROM " + table + " WHERE " + compareField1 + "='" + sqlEncode( value1 ) + "' AND " + compareField2 + "='" + value2 + "'";
163
public String getValueStatement( String table, String returnField, Map<String, String> fieldMap, boolean union )
165
String operator = union ? " AND " : " OR ";
167
StringBuffer sqlBuffer = new StringBuffer( "SELECT " ).append( returnField ).append( " FROM " ).append( table ).append( " WHERE " );
169
for ( Entry<String, String> entry : fieldMap.entrySet() )
171
sqlBuffer.append( entry.getKey() ).append( "='" ).append( sqlEncode( entry.getValue() ) ).append( "'" ).append( operator );
174
String sql = sqlBuffer.toString();
175
sql = sql.substring( 0, sql.length() - operator.length() );
180
public String getDoubleColumnType()
182
String type = "DOUBLE PRECISION";
187
public String getPeriodIdentifierStatement( Period period )
190
"SELECT periodid FROM period WHERE periodtypeid=" + period.getPeriodType().getId() + " " +
191
"AND startdate='" + getDateString( period.getStartDate() ) + "' " +
192
"AND enddate='" + getDateString( period.getEndDate() ) + "'";
197
public String getCreateAggregatedDataValueTable()
199
String sql = "CREATE TABLE aggregateddatavalue ( " +
200
"dataelementid INTEGER, " +
201
"categoryoptioncomboid INTEGER, " +
202
"periodid INTEGER, " +
203
"organisationunitid INTEGER, " +
204
"periodtypeid INTEGER, " +
206
"value DOUBLE PRECISION );";
211
public String getCreateAggregatedIndicatorTable()
213
String sql = "CREATE TABLE aggregatedindicatorvalue ( " +
214
"indicatorid INTEGER, " +
215
"periodid INTEGER, " +
216
"organisationunitid INTEGER, " +
217
"periodtypeid INTEGER, " +
219
"annualized VARCHAR( 10 ), " +
220
"factor DOUBLE PRECISION, " +
221
"value DOUBLE PRECISION, " +
222
"numeratorvalue DOUBLE PRECISION, " +
223
"denominatorvalue DOUBLE PRECISION );";
228
public String getCreateDataSetCompletenessTable()
230
String sql = "CREATE TABLE aggregateddatasetcompleteness ( " +
231
"datasetid INTEGER, " +
232
"periodid INTEGER, " +
233
"periodname VARCHAR( 30 ), " +
234
"organisationunitid INTEGER, " +
235
"reporttableid INTEGER, " +
236
"sources INTEGER, " +
237
"registrations INTEGER, " +
238
"registrationsOnTime INTEGER, " +
239
"value DOUBLE PRECISION, " +
240
"valueOnTime DOUBLE PRECISION );";
245
public String getCreateDataValueIndex()
248
"CREATE INDEX crosstab " +
249
"ON datavalue ( periodid, sourceid );";
254
public String getDeleteRelativePeriods()
257
"DELETE FROM period " +
258
"USING periodtype " +
259
"WHERE period.periodtypeid = periodtype.periodtypeid " +
260
"AND periodtype.name = '" + RelativePeriodType.NAME + "';";
265
public String getDeleteZeroDataValues()
268
"DELETE FROM datavalue " +
269
"USING dataelement " +
270
"WHERE datavalue.dataelementid = dataelement.dataelementid " +
271
"AND dataelement.aggregationtype = 'sum' " +
272
"AND datavalue.value IN ( '0', '0.', '.0', '0.0', ' 0', '0 ', '0 0' )";