001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) Copyright IBM Corporation, 2005-2007. All rights reserved.
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: */
017: package org.geotools.data.db2;
018:
019: import org.geotools.data.DataUtilities;
020: import org.geotools.data.db2.filter.SQLEncoderDB2;
021: import org.geotools.data.jdbc.FilterToSQL;
022: import org.geotools.data.jdbc.GeoAPISQLBuilder;
023: import org.geotools.data.jdbc.fidmapper.FIDMapper;
024: import org.geotools.feature.AttributeType;
025: import org.geotools.feature.Feature;
026: import org.geotools.feature.FeatureType;
027: import org.geotools.filter.SQLEncoderException;
028:
029: import com.vividsolutions.jts.geom.Geometry;
030:
031: import java.io.IOException;
032: import java.sql.Types;
033: import java.util.logging.Level;
034: import java.util.logging.Logger;
035:
036: /**
037: * A DB2-specific subclass of DefaultSQLBuilder, which supports DB2 Spatial
038: * Extender geometry datatypes.
039: *
040: * @author David Adler - IBM Corporation
041: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/db2/src/main/java/org/geotools/data/db2/DB2SQLBuilder.java $
042: */
043: public class DB2SQLBuilder extends GeoAPISQLBuilder {
044: private static final Logger LOGGER = org.geotools.util.logging.Logging
045: .getLogger("org.geotools.data.db2");
046: private String tableSchema = null;
047: private String tableName = null;
048: private FIDMapper mapper = null;
049:
050: /**
051: * Creates a DB2SQLBuilder that will provide a table schema to qualify
052: * table names. The table schema is provided by the DB2DataStore which
053: * means that a given DataStore can only access tables within a single
054: * schema.
055: *
056: * <p>
057: * It would be better if the table schema was managed by FeatureTypeHandler
058: * or FeatureType.
059: * </p>
060: *
061: * @param encoder an SQLEncoder
062: * @param tableSchema table schema to qualify table names
063: * @param featureType the feature type to be used by this SQL builder
064: */
065: public DB2SQLBuilder(FilterToSQL encoder, String tableSchema,
066: FeatureType featureType) {
067: super (encoder, featureType, null);
068: this .tableSchema = tableSchema;
069: this .tableName = featureType.getTypeName();
070: }
071:
072: /**
073: * Generates the select column specification for a DB2 geometry column.
074: *
075: * <p>
076: * Overrides sqlGeometryColumn in DefaultSQLBuilder
077: * </p>
078: *
079: * @param sql A StringBuffer that the column specification can be appended
080: * to.
081: * @param geomAttribute An AttributeType for a geometry attribute
082: */
083: public void sqlGeometryColumn(StringBuffer sql,
084: AttributeType geomAttribute) {
085: sql.append("DB2GSE.ST_AsText("
086: + sqlGeometryColumnName(geomAttribute) + ")");
087: }
088:
089: /**
090: * Gets the escaped geometry column name.
091: *
092: * @param geomAttribute the geometry attribute.
093: *
094: * @return the String with the escaped name.
095: */
096: String sqlGeometryColumnName(AttributeType geomAttribute) {
097: return this .encoder.escapeName(geomAttribute.getName());
098: }
099:
100: /**
101: * Generates the SELECT clause values to get the geometry min-max values.
102: *
103: * @param geomAttribute the geometry attribute.
104: *
105: * @return the string with the 4 column expressions.
106: */
107: String sqlGeometryMinMaxValues(AttributeType geomAttribute) {
108: String sql;
109: String gcName = sqlGeometryColumnName(geomAttribute);
110: sql = "MIN(db2gse.ST_MinX(" + gcName + ")), "
111: + "MIN(db2gse.ST_MinY(" + gcName + ")), "
112: + "MAX(db2gse.ST_MaxX(" + gcName + ")), "
113: + "MAX(db2gse.ST_MaxY(" + gcName + ")) ";
114:
115: return sql;
116: }
117:
118: /**
119: * Construct the FROM clause for a feature type. Prefixes the typeName
120: * with the table schema provided when this class was constructed.
121: *
122: * <p>
123: * This method could be promoted to DefaultSQLBuilder if the table schema
124: * was propagated up.
125: * </p>
126: *
127: * <p>
128: * Overrides sqlFrom in DefaultSQLBuilder
129: * </p>
130: *
131: * @param sql StringBuffer to be appended to
132: * @param typeName Name of the type (table)
133: */
134: public void sqlFrom(StringBuffer sql, String typeName) {
135: sql.append(" FROM ");
136: sql.append(getSchemaTableName(typeName));
137: }
138:
139: /**
140: * Builds the SQL query to get the bounds (min-max coordinate values) of a
141: * geometry column for a given filter.
142: *
143: * @param typeName the feature type name.
144: * @param geomAttr the geometry attribute.
145: * @param filter the filter expression.
146: *
147: * @return the string to perform the SQL query.
148: *
149: * @throws SQLEncoderException
150: */
151: public String buildSQLBoundsQuery(String typeName,
152: AttributeType geomAttr, org.opengis.filter.Filter filter)
153: throws SQLEncoderException {
154: StringBuffer sqlBuffer = new StringBuffer();
155:
156: sqlBuffer.append("SELECT ");
157: sqlBuffer.append(sqlGeometryMinMaxValues(geomAttr));
158: sqlFrom(sqlBuffer, typeName);
159: sqlWhere(sqlBuffer, filter);
160:
161: String sqlStmt = sqlBuffer.toString();
162: LOGGER.finer(sqlStmt);
163:
164: return sqlStmt;
165: }
166:
167: /**
168: * Gets the SQL encoder associated with this SQL builder.
169: *
170: * @return the associated encoder
171: */
172: SQLEncoderDB2 getEncoder() {
173: return (SQLEncoderDB2) this .encoder;
174: }
175:
176: /**
177: * Gets the concatenated schema name and table name needed by DB2.
178: *
179: * @param tableName
180: *
181: * @return concatenated schema and table name
182: */
183: String getSchemaTableName(String tableName) {
184: return escapeName(this .tableSchema) + "."
185: + escapeName(tableName);
186: }
187:
188: /**
189: * Gets the concatenated schema name and table name needed by DB2.
190: *
191: * @return concatenated schema and table name
192: */
193: String getSchemaTableName() {
194: return escapeName(this .tableSchema) + "."
195: + escapeName(this .tableName);
196: }
197:
198: /**
199: * "escape" the specified name. This is currently delegated to the encoder
200: * object and for DB2 this means that the specified name will be
201: * surrounded by double-quote characters in order to ensure case
202: * sensitivity.
203: *
204: * @param name
205: *
206: * @return escaped name
207: */
208: String escapeName(String name) {
209: return this .encoder.escapeName(name);
210: }
211:
212: /**
213: * Creates a sql insert statement. Uses each feature's schema, which makes
214: * it possible to insert out of order, as well as inserting less than all
215: * features.
216: *
217: * @param attributes the attribute columns to be inserted
218: * @param feature the feature to add.
219: *
220: * @return an insert sql statement.
221: *
222: * @throws IOException
223: */
224: protected String makeInsertSql(AttributeType[] attributes,
225: Feature feature) throws IOException {
226:
227: SQLEncoderDB2 db2Encoder = (SQLEncoderDB2) encoder;
228:
229: String attrValue = null;
230: boolean firstAttr = true;
231: StringBuffer colNameList = new StringBuffer("");
232: StringBuffer valueList = new StringBuffer("");
233:
234: for (int i = 0; i < attributes.length; i++) {
235: String colName = escapeName(attributes[i].getName());
236: if (!firstAttr) {
237: colNameList.append(", ");
238: valueList.append(", ");
239: }
240: firstAttr = false;
241: colNameList.append(colName);
242:
243: Object currAtt = feature.getAttribute(i);
244: if (currAtt == null) {
245: attrValue = "NULL";
246: } else if (Geometry.class.isAssignableFrom(attributes[i]
247: .getType())) {
248: attrValue = db2Encoder.db2Geom((Geometry) currAtt);
249: } else if (String.class.isAssignableFrom(attributes[i]
250: .getType())) {
251: attrValue = "'" + currAtt.toString() + "'";
252: } else {
253: attrValue = currAtt.toString();
254: }
255:
256: valueList.append(attrValue);
257: }
258:
259: String statementSQL = "INSERT INTO " + getSchemaTableName()
260: + "( " + colNameList.toString() + ")" + " VALUES("
261: + valueList.toString() + ")";
262: return (statementSQL);
263: }
264:
265: /**
266: * Generates the SQL UPDATE statement
267: *
268: * @param attributes the attribute columns to be inserted
269: * @param feature
270: *
271: * @return DB2 UPDATE statement
272: *
273: * @throws IOException
274: * @throws UnsupportedOperationException
275: */
276: protected String makeUpdateSql(AttributeType[] attributes,
277: Feature live, Feature current) throws IOException {
278:
279: boolean firstAttr = true;
280: SQLEncoderDB2 db2Encoder = (SQLEncoderDB2) encoder;
281: StringBuffer statementSQL = new StringBuffer("UPDATE "
282: + getSchemaTableName() + " SET ");
283:
284: for (int i = 0; i < current.getNumberOfAttributes(); i++) {
285: Object currAtt = current.getAttribute(i);
286: Object liveAtt = live.getAttribute(i);
287:
288: if (!DataUtilities.attributesEqual(currAtt, liveAtt)) {
289: if (LOGGER.isLoggable(Level.INFO)) {
290: LOGGER.fine("modifying att# " + i + " to "
291: + currAtt);
292: }
293: String attrValue = null;
294: String attrName = attributes[i].getName();
295:
296: if (Geometry.class.isAssignableFrom(attributes[i]
297: .getType())) {
298:
299: attrValue = db2Encoder.db2Geom((Geometry) currAtt);
300: } else if (String.class.isAssignableFrom(attributes[i]
301: .getType())) {
302: attrValue = "'" + currAtt.toString() + "'";
303: } else {
304: attrValue = currAtt.toString();
305: }
306:
307: String colName = escapeName(attrName);
308: if (!firstAttr) {
309: statementSQL.append(", ");
310: }
311: firstAttr = false;
312: statementSQL.append(colName).append(" = ").append(
313: attrValue);
314: }
315: }
316: statementSQL.append(makeFIDWhere(current));
317: return (statementSQL.toString());
318: }
319:
320: /**
321: * Generates the SQL delete statement
322: *
323: * @param feature
324: *
325: * @return DB2 DELETE statement
326: * @throws IOException
327: *
328: * @throws IOException
329: * @throws UnsupportedOperationException
330: */
331: public String makeDeleteSql(Feature feature) throws IOException {
332: String deleteSQL = "DELETE FROM " + getSchemaTableName()
333: + makeFIDWhere(feature);
334: return (deleteSQL);
335: }
336:
337: /**
338: * Build a DB2 WHERE clause based on the FID column values
339: *
340: * @param feature
341: * @return A DB2 WHERE clause based on the FID column values.
342: * @throws IOException
343: */
344: protected String makeFIDWhere(Feature feature) throws IOException {
345:
346: StringBuffer statementSQL = new StringBuffer(" WHERE ");
347: Object[] pkValues = mapper.getPKAttributes(feature.getID());
348:
349: if (mapper.getColumnCount() == 0) {
350: // can't update/delete without a primary key
351: throw new UnsupportedOperationException();
352: }
353: boolean firstCol = true;
354: for (int i = 0; i < mapper.getColumnCount(); i++) {
355:
356: if (!firstCol) {
357: statementSQL.append(" AND ");
358: firstCol = false;
359: }
360:
361: statementSQL.append(escapeName(mapper.getColumnName(i)))
362: .append(" = ");
363:
364: // don't put quotes around numeric values
365: if (isTypeNumeric(mapper.getColumnType(i))) {
366: statementSQL.append(pkValues[i]);
367: } else {
368: statementSQL.append("'" + pkValues[i] + "'");
369: }
370: }
371: return (statementSQL.toString());
372: }
373:
374: /**
375: * Checks if column type is SQL numeric type
376: *
377: * @param SQL columnType
378: *
379: * @return true if the column is an SQL numeric type
380: */
381: protected boolean isTypeNumeric(int columnType) {
382: boolean numeric = false;
383:
384: if ((columnType == Types.BIT) || (columnType == Types.TINYINT)
385: || (columnType == Types.SMALLINT)
386: || (columnType == Types.INTEGER)
387: || (columnType == Types.BIGINT)
388: || (columnType == Types.FLOAT)
389: || (columnType == Types.REAL)
390: || (columnType == Types.DOUBLE)
391: || (columnType == Types.NUMERIC)
392: || (columnType == Types.DECIMAL)) {
393: numeric = true;
394: }
395:
396: return (numeric);
397: }
398:
399: }
|