001: package org.geotools.data.sql;
002:
003: import java.util.Arrays;
004: import java.util.HashMap;
005: import java.util.Iterator;
006: import java.util.List;
007: import java.util.Map;
008: import java.util.logging.Logger;
009:
010: import org.geotools.data.jdbc.GeoAPISQLBuilder;
011: import org.geotools.data.jdbc.fidmapper.FIDMapper;
012: import org.geotools.feature.AttributeType;
013: import org.geotools.feature.FeatureType;
014: import org.geotools.feature.GeometryAttributeType;
015: import org.geotools.filter.SQLEncoderException;
016: import org.geotools.filter.UnaliasSQLEncoder;
017:
018: public class BypassSqlSQLBuilder extends GeoAPISQLBuilder {
019:
020: private static final Logger LOGGER = org.geotools.util.logging.Logging
021: .getLogger(BypassSqlSQLBuilder.class.getPackage().getName());
022:
023: private BypassSqlFeatureTypeHandler ftHandler;
024:
025: protected Map fieldAliases;
026:
027: public BypassSqlSQLBuilder(BypassSqlFeatureTypeHandler ftHandler) {
028: this (new UnaliasSQLEncoder(), ftHandler);
029: }
030:
031: public BypassSqlSQLBuilder(UnaliasSQLEncoder encoder,
032: BypassSqlFeatureTypeHandler ftHandler) {
033: super (encoder, null, null);
034: this .ftHandler = ftHandler;
035: }
036:
037: /**
038: * Constructs the full SQL SELECT statement for the supplied Filter.
039: *
040: * <p>
041: * The statement is constructed by concatenating the SELECT column list,
042: * FROM table specification and WHERE clause appropriate to the supplied
043: * Filter.
044: * </p>
045: *
046: * @param typeName
047: * The name of the table (feature type) to be queried
048: * @param mapper
049: * FIDMapper to identify the FID columns in the table
050: * @param attrTypes
051: * The specific attribute columns to be selected
052: * @param filter
053: * The Filter that will be used by the encoder to construct the
054: * WHERE clause
055: *
056: * @return The fully formed SQL SELECT statement
057: *
058: * @throws SQLEncoderException
059: * Not thrown by this method but may be thrown by the encoder
060: * class
061: */
062: public String buildSQLQuery(String typeName, FIDMapper mapper,
063: AttributeType[] attrTypes, org.opengis.filter.Filter filter)
064: throws SQLEncoderException {
065: String sqlStmt;
066:
067: if (this .ftHandler != null && this .ftHandler.isView(typeName)) {
068: final StringBuffer sqlBuffer = new StringBuffer();
069: final String sqlQuery = ftHandler.getQuery(typeName);
070: fieldAliases = parseAliases(sqlQuery);
071:
072: UnaliasSQLEncoder encoder = (UnaliasSQLEncoder) super .encoder;
073: encoder.setAliases(fieldAliases);
074:
075: FeatureType fType;
076: try {
077: fType = ftHandler.getFeatureTypeInfo(typeName)
078: .getSchema();
079: } catch (Exception e) {
080: throw new RuntimeException("should not happen!: "
081: + e.getMessage());
082: }
083:
084: //String select = getSelect(sqlQuery, fType);
085: String select = "select ";
086: sqlBuffer.append(select);
087:
088: sqlColumns(sqlBuffer, mapper, attrTypes, fieldAliases);
089:
090: String from = getFrom(sqlQuery);
091: sqlBuffer.append(from);
092:
093: String where = getWhere(sqlQuery, mapper, filter);
094: sqlBuffer.append(where);
095:
096: String groupBy = getGroupBy(sqlQuery);
097: if (groupBy != null) {
098: sqlBuffer.append(groupBy);
099: }
100:
101: String orderBy = getOrderBy(sqlQuery);
102: if (orderBy != null) {
103: sqlBuffer.append(orderBy);
104: }
105:
106: sqlStmt = sqlBuffer.toString();
107: } else {
108: sqlStmt = super .buildSQLQuery(typeName, mapper, attrTypes,
109: filter);
110: }
111: LOGGER.finer(sqlStmt);
112: return sqlStmt;
113: }
114:
115: public void sqlColumns(final StringBuffer sql,
116: final FIDMapper mapper, final AttributeType[] attributes,
117: final Map aliases) {
118:
119: String sqlExpression;
120: String alias;
121:
122: for (int i = 0; i < mapper.getColumnCount(); i++) {
123: alias = mapper.getColumnName(i);
124: sqlExpression = (String) aliases.get(alias);
125: sql.append(encoder.escapeName(sqlExpression) + ", ");
126: }
127:
128: for (int i = 0; i < attributes.length; i++) {
129: alias = attributes[i].getName();
130: sqlExpression = (String) aliases.get(alias);
131:
132: String fieldName = sqlExpression;
133: if (!alias.equals(sqlExpression)) {
134: fieldName += " AS " + alias;
135: }
136: if (attributes[i] instanceof GeometryAttributeType) {
137: sqlGeometryColumn(sql, attributes[i]);
138: } else {
139: sql.append(encoder.escapeName(fieldName));
140: }
141:
142: if (i < (attributes.length - 1)) {
143: sql.append(", ");
144: }
145: }
146: }
147:
148: public static Map parseAliases(final String sqlQueryDefinition) {
149: Map aliases = new HashMap();
150: String sqlQ = sqlQueryDefinition.toLowerCase();
151: int idxFrom = sqlQ.indexOf("from ");
152: int firstField = 7 + sqlQ.indexOf("select");
153:
154: String fields = sqlQueryDefinition.substring(firstField,
155: idxFrom);
156:
157: LOGGER.fine("fields: " + fields);
158: List fieldsList = Arrays.asList(fields.split(","));
159:
160: for (Iterator it = fieldsList.iterator(); it.hasNext();) {
161: String aliasDef = (String) it.next();
162: aliasDef = aliasDef.trim().toLowerCase();
163: LOGGER.fine("parsing alias from '" + aliasDef + "'");
164: int idx = aliasDef.indexOf(" as ");
165: if (idx > 0) {
166: String sqlExpr, alias;
167: sqlExpr = aliasDef.substring(0, idx);
168: alias = aliasDef.substring(4 + idx);
169: LOGGER
170: .fine("sqlExpr: " + sqlExpr + ", alias: "
171: + alias);
172: aliases.put(alias.trim(), sqlExpr.trim());
173: aliases.put(alias.trim().toUpperCase(), sqlExpr.trim()
174: .toUpperCase());
175: } else {
176: LOGGER.fine(aliasDef + " is not aliased");
177: aliases.put(aliasDef, aliasDef);
178: aliases.put(aliasDef.toUpperCase(), aliasDef
179: .toUpperCase());
180: }
181: }
182:
183: return aliases;
184: }
185:
186: /**
187: * Returns the "SELECT" part of the SQL query definition for FeatureType
188: * <code>fType</code>, without the column names. This allows to maintain
189: * DB specific keywords, for example, <code>SELECT TOP 100 ...</code> in
190: * SQLServer.
191: *
192: * @param sqlQueryDefinition
193: * @param fType
194: * @return
195: * @throws SQLEncoderException
196: */
197: private String getSelect(String sqlQueryDefinition,
198: FeatureType fType) throws SQLEncoderException {
199: AttributeType firstAtt = fType.getAttributeTypes()[0];
200: String firstAttName = firstAtt.getName().toLowerCase();
201:
202: int index = sqlQueryDefinition.indexOf(firstAttName);
203: if (index == -1) {
204: throw new SQLEncoderException(
205: "attribute "
206: + firstAttName
207: + " not found in sql query definition. It should be the first one!: "
208: + sqlQueryDefinition);
209: }
210:
211: String select = sqlQueryDefinition.substring(0, index);
212: return select;
213: }
214:
215: public String getFrom(String sqlQueryDefinition) {
216: String search = " from ";
217: String searchIn = sqlQueryDefinition.toLowerCase();
218: int index = searchIn.indexOf(search);
219: int lastIndex = searchIn.lastIndexOf(" where ");
220: if (lastIndex == -1) {
221: lastIndex = searchIn.lastIndexOf("group by");
222: }
223: if (lastIndex == -1) {
224: lastIndex = searchIn.lastIndexOf("order by");
225: }
226: String from;
227: if (lastIndex == -1) {
228: from = sqlQueryDefinition.substring(index);
229: } else {
230: from = sqlQueryDefinition.substring(index, lastIndex);
231: }
232: return from;
233: }
234:
235: public String getWhere(String sqlQueryDefinition, FIDMapper mapper,
236: org.opengis.filter.Filter filter)
237: throws SQLEncoderException {
238: String search = " where ";
239: String searchIn = sqlQueryDefinition.toLowerCase();
240: int index = searchIn.lastIndexOf(search);
241:
242: StringBuffer where = new StringBuffer();
243: encoder.setFIDMapper(mapper);
244: super .sqlWhere(where, filter);
245:
246: if (index > 0) {
247: int lastIndex = searchIn.lastIndexOf("group by");
248: if (lastIndex == -1) {
249: lastIndex = searchIn.lastIndexOf("order by");
250: }
251: String queryWhere;
252: if (lastIndex == -1) {
253: queryWhere = sqlQueryDefinition.substring(index
254: + search.length());
255: } else {
256: queryWhere = sqlQueryDefinition.substring(index
257: + search.length(), lastIndex);
258: }
259:
260: String filterWhere = where.toString();
261:
262: if (where.length() > 0) {
263: where.insert(7, queryWhere + " AND (");
264: where.append(")");
265: } else {
266: where.append(" WHERE " + queryWhere);
267: }
268: }
269: return where.toString();
270: }
271:
272: private String getGroupBy(String sqlQueryDefinition) {
273: return null;
274: }
275:
276: private String getOrderBy(String sqlQueryDefinition) {
277: String sql = sqlQueryDefinition.toLowerCase();
278: int idx = sql.lastIndexOf("order by");
279: String orderBy = null;
280: if (idx > 0) {
281: orderBy = " " + sqlQueryDefinition.substring(idx);
282: }
283: return orderBy;
284: }
285:
286: }
|