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: H2StatementBuilder.java 5715 2008-09-17 14:05:28Z larshelg $
41
public class H2StatementBuilder
42
extends AbstractStatementBuilder
44
// -------------------------------------------------------------------------
46
// -------------------------------------------------------------------------
48
public H2StatementBuilder()
53
// -------------------------------------------------------------------------
54
// AbstractStatementBuilder implementation
55
// -------------------------------------------------------------------------
57
public String getInsertStatementOpening( String table )
59
StringBuffer buffer = new StringBuffer();
61
buffer.append( "INSERT INTO " + table + " (" );
63
for ( String column : columns )
65
buffer.append( column + SEPARATOR );
68
if ( columns.size() > 0 )
70
buffer.deleteCharAt( buffer.length() - 1 );
73
buffer.append( BRACKET_END + " VALUES " );
77
return buffer.toString();
80
public String getNoColumnInsertStatementOpening( String table )
82
String sql = "INSERT INTO " + table + " VALUES ";
87
public String getInsertStatementValues()
89
StringBuffer buffer = new StringBuffer();
91
buffer.append( BRACKET_START );
93
for ( String value : values )
95
buffer.append( value + SEPARATOR );
98
if ( values.size() > 0 )
100
buffer.deleteCharAt( buffer.length() - 1 );
103
buffer.append( BRACKET_END + SEPARATOR );
107
return buffer.toString();
110
public String getUpdateStatement( String table )
112
StringBuffer buffer = new StringBuffer();
114
buffer.append( "UPDATE " + table + " SET " );
116
Iterator<String> columnIterator = columns.iterator();
117
Iterator<String> valueIterator = values.iterator();
119
while ( columnIterator.hasNext() )
121
buffer.append( columnIterator.next() + "=" + valueIterator.next() + SEPARATOR );
124
if ( columns.size() > 0 && values.size() > 0 )
126
buffer.deleteCharAt( buffer.length() - 1 );
129
buffer.append( " WHERE " + identifierColumnName + "=" + identifierColumnValue );
134
return buffer.toString();
137
public String getValueStatement( String table, String returnField, String compareField, String value )
139
String sql = "SELECT " + returnField + " FROM " + table + " WHERE " + compareField + " = '" + sqlEncode( value ) + "'";
144
public String getValueStatement( String table, String returnField1, String returnField2, String compareField1, String value1, String compareField2, String value2 )
146
String sql = "SELECT " + returnField1 + ", " + returnField2 + " FROM " + table + " WHERE " + compareField1 + "='" + sqlEncode( value1 ) + "' AND " + compareField2 + "='" + value2 + "'";
151
public String getValueStatement( String table, String returnField, Map<String, String> fieldMap, boolean union )
153
String operator = union ? " AND " : " OR ";
155
StringBuffer sqlBuffer = new StringBuffer();
156
sqlBuffer.append( "SELECT " ).append( returnField ).append( " FROM " ).append( table ).append( " WHERE " );
158
for ( Entry<String, String> entry : fieldMap.entrySet() )
160
sqlBuffer.append( entry.getKey() ).append( "='" ).append( sqlEncode( entry.getValue() ) ).append( "'" ).append( operator );
163
String sql = sqlBuffer.toString();
165
sql = sql.substring( 0, sql.length() - operator.length() );
170
public String getDoubleColumnType()
172
String type = "DOUBLE";
177
public String getPeriodIdentifierStatement( Period period )
180
"SELECT periodid FROM period WHERE periodtypeid=" + period.getPeriodType().getId() + " " +
181
"AND startdate='" + getDateString( period.getStartDate() ) + "' " +
182
"AND enddate='" + getDateString( period.getEndDate() ) + "'";
187
public String getCreateAggregatedDataValueTable()
190
"CREATE TABLE aggregateddatavalue ( " +
191
"dataelementid INTEGER, " +
192
"categoryoptioncomboid INTEGER, " +
193
"periodid INTEGER, " +
194
"organisationunitid INTEGER, " +
195
"periodtypeid INTEGER, " +
202
public String getCreateAggregatedIndicatorTable()
205
"CREATE TABLE aggregatedindicatorvalue ( " +
206
"indicatorid INTEGER, " +
207
"periodid INTEGER, " +
208
"organisationunitid INTEGER, " +
209
"periodtypeid INTEGER, " +
211
"annualized VARCHAR( 10 ), " +
214
"numeratorvalue DOUBLE, " +
215
"denominatorvalue DOUBLE );";
220
public String getCreateDataSetCompletenessTable()
222
String sql = "CREATE TABLE aggregateddatasetcompleteness ( " +
223
"datasetid INTEGER, " +
224
"periodid INTEGER, " +
225
"periodname VARCHAR( 30 ), " +
226
"organisationunitid INTEGER, " +
227
"reporttableid INTEGER, " +
228
"sources INTEGER, " +
229
"registrations INTEGER, " +
230
"registrationsOnTime INTEGER, " +
232
"valueOnTime DOUBLE );";
237
public String getCreateDataValueIndex()
240
"CREATE INDEX crosstab " +
241
"ON datavalue ( periodid, sourceid );";
246
public String getDeleteRelativePeriods()
249
"DELETE FROM period " +
250
"USING periodtype " +
251
"WHERE period.periodtypeid = periodtype.periodtypeid " +
252
"AND periodtype.name = '" + RelativePeriodType.NAME + "';";
257
public String getDeleteZeroDataValues()
260
"DELETE FROM datavalue " +
261
"USING dataelement " +
262
"WHERE datavalue.dataelementid = dataelement.dataelementid " +
263
"AND dataelement.aggregationtype = 'sum' " +
264
"AND datavalue.value IN ( '0', '0.', '.0', '0.0', ' 0', '0 ', '0 0' )";