001: /*
002: * Geotools2 - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002, Geotools Project Managment Committee (PMC)
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.geometryless;
018:
019: import java.io.IOException;
020: import java.net.URI;
021: import java.sql.Connection;
022: import java.sql.ResultSet;
023: import java.sql.ResultSetMetaData;
024: import java.sql.SQLException;
025: import java.sql.Statement;
026: import java.sql.Types;
027: import java.util.ArrayList;
028: import java.util.List;
029: import java.util.logging.Level;
030: import java.util.logging.Logger;
031:
032: import javax.sql.DataSource;
033:
034: import net.sf.jsqlparser.statement.select.SelectBody;
035:
036: import org.geotools.data.DataSourceException;
037: import org.geotools.data.FeatureReader;
038: import org.geotools.data.FeatureWriter;
039: import org.geotools.data.SchemaNotFoundException;
040: import org.geotools.data.Transaction;
041: import org.geotools.data.jdbc.ConnectionPool;
042: import org.geotools.data.jdbc.FeatureTypeHandler;
043: import org.geotools.data.jdbc.JDBCDataStoreConfig;
044: import org.geotools.data.jdbc.JDBCFeatureWriter;
045: import org.geotools.data.jdbc.JDBCUtils;
046: import org.geotools.data.jdbc.QueryData;
047: import org.geotools.data.jdbc.SQLBuilder;
048: import org.geotools.data.jdbc.attributeio.AttributeIO;
049: import org.geotools.data.jdbc.attributeio.WKTAttributeIO;
050: import org.geotools.data.jdbc.fidmapper.FIDMapper;
051: import org.geotools.data.jdbc.fidmapper.NullFIDMapper;
052: import org.geotools.feature.AttributeType;
053: import org.geotools.feature.FeatureType;
054: import org.geotools.feature.FeatureTypeFactory;
055: import org.geotools.filter.UnaliasSQLEncoder;
056: import org.opengis.filter.Filter; // import org.geotools.filter.SQLEncoder;
057: // import org.geotools.data.geometryless.filter.GeometrylessSQLEncoder;
058: import org.geotools.data.jdbc.FilterToSQL;
059: import org.geotools.data.sql.BypassSqlFeatureTypeHandler;
060: import org.geotools.data.sql.RsMd2DbMdResultSet;
061: import org.geotools.data.sql.SqlDataStore;
062:
063: /**
064: * An implementation of the GeoTools Data Store API for a generic non-spatial
065: * database platform. The plan is to support traditional jdbc datatypes, and
066: * support geometry held within such types (eg, x,y columns, or possibly WKT
067: * strings)<br>
068: * <br>
069: * Please see {@link org.geotools.data.jdbc.JDBCDataStore class JDBCDataStore}
070: * and {@link org.geotools.data.DataStore interface DataStore} for DataStore
071: * usage details.
072: *
073: * @author Rob Atkinson rob@socialchange.net.au
074: * @source $URL:
075: * http://svn.geotools.org/geotools/trunk/gt/modules/unsupported/geometryless/src/main/java/org/geotools/data/geometryless/JDBCDataStore.java $
076: */
077:
078: public class JDBCDataStore extends org.geotools.data.jdbc.JDBCDataStore
079: implements SqlDataStore {
080: /** The logger for the mysql module. */
081: private static final Logger LOGGER = org.geotools.util.logging.Logging
082: .getLogger("org.geotools.data.geometryless");
083:
084: /**
085: * Basic constructor for JDBCDataStore. Requires creation of a
086: * {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}, which
087: * could be done similar to the following:<br>
088: * <br>
089: * <code>MySQLConnectionFactory connectionFactory = new MySQLConnectionFactory("mysqldb.geotools.org", "3306", "myCoolSchema");</code><br>
090: * <code>ConnectionPool connectionPool = connectionFactory.getConnectionPool("omcnoleg", "myTrickyPassword123");</code><br>
091: * <code>DataStore dataStore = new JDBCDataStore(connectionPool);</code><br>
092: *
093: * @param connectionPool
094: * a MySQL
095: * {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
096: * @throws IOException
097: * if the database cannot be properly accessed
098: * @see org.geotools.data.jdbc.ConnectionPool
099: * @see org.geotools.data.mysql.MySQLConnectionFactory
100: */
101: public JDBCDataStore(DataSource connectionPool) throws IOException {
102: super (connectionPool, new JDBCDataStoreConfig());
103: }
104:
105: /** <code>DEFAULT_NAMESPACE</code> field */
106: public static String DEFAULT_NAMESPACE = "http://geotools.org/jdbc";
107:
108: /**
109: * Constructor for JDBCDataStore where the database schema name is provided.
110: *
111: * @param connectionPool
112: * a MySQL
113: * {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
114: * @param databaseSchemaName
115: * the database schema. Can be null. See the comments for the
116: * parameter schemaPattern in
117: * {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[]) DatabaseMetaData.getTables},
118: * because databaseSchemaName behaves in the same way.
119: * @throws IOException
120: * if the database cannot be properly accessed
121: */
122: public JDBCDataStore(DataSource connectionPool,
123: String databaseSchemaName) throws IOException {
124: this (connectionPool, databaseSchemaName, DEFAULT_NAMESPACE);
125: }
126:
127: /**
128: * Constructor for JDBCDataStore where the database schema name is provided.
129: *
130: * @param connectionPool
131: * a MySQL
132: * {@link org.geotools.data.jdbc.ConnectionPool ConnectionPool}
133: * @param databaseSchemaName
134: * the database schema. Can be null. See the comments for the
135: * parameter schemaPattern in
136: * {@link java.sql.DatabaseMetaData#getTables(String, String, String, String[]) DatabaseMetaData.getTables},
137: * because databaseSchemaName behaves in the same way.
138: * @param namespace
139: * the namespace for this data store. Can be null, in which case
140: * the namespace will simply be the schema name.
141: * @throws IOException
142: * if the database cannot be properly accessed
143: */
144: public JDBCDataStore(DataSource connectionPool,
145: String databaseSchemaName, String namespace)
146: throws IOException {
147: super (connectionPool, JDBCDataStoreConfig
148: .createWithNameSpaceAndSchemaName(namespace,
149: databaseSchemaName));
150: }
151:
152: /**
153: * A utility method for creating a JDBCDataStore from database connection
154: * parameters, using the default port (3306) for MySQL.
155: *
156: * @param host
157: * the host name or IP address of the database server
158: * @param schema
159: * the name of the database instance
160: * @param username
161: * the database username
162: * @param password
163: * the password corresponding to <code>username</code>
164: * @return a JDBCDataStore for the specified parameters
165: *
166: * public static JDBCDataStore getInstance( String host, String schema,
167: * String username, String password) throws IOException, SQLException {
168: * return getInstance(host, 3306, schema, username, password); }
169: */
170: /**
171: * Utility method for creating a JDBCDataStore from database connection
172: * parameters.
173: *
174: * @param host
175: * the host name or IP address of the database server
176: * @param port
177: * the port number of the database
178: * @param schema
179: * the name of the database instance
180: * @param username
181: * the database username
182: * @param password
183: * the password corresponding to <code>username</code>
184: * @throws IOException
185: * if the JDBCDataStore cannot be created because the database
186: * cannot be properly accessed
187: * @throws SQLException
188: * if a MySQL connection pool cannot be established
189: *
190: * public static JDBCDataStore getInstance( String host, int port, String
191: * schema, String username, String password) throws IOException,
192: * SQLException { return new JDBCDataStore( new MySQLConnectionFactory(host,
193: * port, schema).getConnectionPool(username, password)); }
194: */
195: /**
196: * Utility method for getting a FeatureWriter for modifying existing
197: * features, using no feature filtering and auto-committing. Not used for
198: * adding new features.
199: *
200: * @param typeName
201: * the feature type name (the table name)
202: * @return a FeatureWriter for modifying existing features
203: * @throws IOException
204: * if the database cannot be properly accessed
205: */
206: public FeatureWriter getFeatureWriter(String typeName)
207: throws IOException {
208: return getFeatureWriter(typeName, Filter.INCLUDE,
209: Transaction.AUTO_COMMIT);
210: }
211:
212: /**
213: * Utility method for getting a FeatureWriter for adding new features, using
214: * auto-committing. Not used for modifying existing features.
215: *
216: * @param typeName
217: * the feature type name (the table name)
218: * @return a FeatureWriter for adding new features
219: * @throws IOException
220: * if the database cannot be properly accessed
221: */
222: public FeatureWriter getFeatureWriterAppend(String typeName)
223: throws IOException {
224: return getFeatureWriterAppend(typeName, Transaction.AUTO_COMMIT);
225: }
226:
227: /**
228: * Constructs an AttributeType from a row in a ResultSet. The ResultSet
229: * contains the information retrieved by a call to getColumns() on the
230: * DatabaseMetaData object. This information can be used to construct an
231: * Attribute Type.
232: *
233: * <p>
234: * In addition to standard SQL types, this method identifies MySQL 4.1's
235: * geometric datatypes and creates attribute types accordingly. This happens
236: * when the datatype, identified by column 5 of the ResultSet parameter, is
237: * equal to java.sql.Types.OTHER. If a Types.OTHER ends up not being
238: * geometric, this method simply calls the parent class's buildAttributeType
239: * method to do something with it.
240: * </p>
241: *
242: * <p>
243: * Note: Overriding methods must never move the current row pointer in the
244: * result set.
245: * </p>
246: *
247: * @param rs
248: * The ResultSet containing the result of a
249: * DatabaseMetaData.getColumns call.
250: *
251: * @return The AttributeType built from the ResultSet.
252: *
253: * @throws SQLException
254: * If an error occurs processing the ResultSet.
255: * @throws DataSourceException
256: * Provided for overriding classes to wrap exceptions caused by
257: * other operations they may perform to determine additional
258: * types. This will only be thrown by the default implementation
259: * if a type is present that is not present in the
260: * TYPE_MAPPINGS.
261: */
262: protected AttributeType buildAttributeType(ResultSet rs)
263: throws IOException {
264: final int COLUMN_NAME = 4;
265: final int DATA_TYPE = 5;
266: final int TYPE_NAME = 6;
267:
268: try {
269: int dataType = rs.getInt(DATA_TYPE);
270: LOGGER.fine("dataType: " + dataType + " "
271: + rs.getString(TYPE_NAME) + " "
272: + rs.getString(COLUMN_NAME));
273:
274: if (dataType == Types.OTHER) {
275: // this is MySQL-specific; handle it
276: // String typeName = rs.getString(TYPE_NAME);
277: // String typeNameLower = typeName.toLowerCase();
278: return super .buildAttributeType(rs);
279: } else {
280: return super .buildAttributeType(rs);
281: }
282: } catch (SQLException e) {
283: throw new IOException("SQL exception occurred: "
284: + e.getMessage());
285: }
286: }
287:
288: /**
289: * @see org.geotools.data.jdbc.JDBCDataStore#getGeometryAttributeIO(org.geotools.feature.AttributeType)
290: */
291: protected AttributeIO getGeometryAttributeIO(AttributeType type,
292: QueryData queryData) {
293: return new WKTAttributeIO();
294: }
295:
296: protected JDBCFeatureWriter createFeatureWriter(
297: FeatureReader reader, QueryData queryData)
298: throws IOException {
299: LOGGER.fine("returning jdbc feature writer");
300:
301: return new GeometrylessFeatureWriter(reader, queryData);
302: }
303:
304: /*
305: * public SQLBuilder getSqlBuilder2(String typeName) throws IOException {
306: * FilterToSQL encoder = new FilterToSQL();
307: * encoder.setFIDMapper(getFIDMapper(typeName)); return new
308: * GeometrylessSQLBuilder(encoder); }
309: */
310:
311: /**
312: * Returns a SQLBuilder for the requested FeatureType.
313: * <p>
314: * If the requested FeatureType corresponds to an in-process view (a view
315: * specified through {@linkplain #registerView(String, String)}, the
316: * returned SQLBuilder takes care of it.
317: * </p>
318: */
319: public SQLBuilder getSqlBuilder(String typeName) throws IOException {
320: BypassSqlFeatureTypeHandler ftHandler = (BypassSqlFeatureTypeHandler) super .typeHandler;
321: FilterToSQL encoder = new UnaliasSQLEncoder();
322: FeatureType schema = getSchema(typeName);
323: encoder.setFeatureType(schema);
324: encoder.setFIDMapper(getFIDMapper(typeName));
325: SQLBuilder sqlBuilder;
326: /*
327: * if (ftHandler.isView(typeName)) { sqlBuilder = new
328: * GeometrylessSQLBuilder(encoder, ftHandler); } else {
329: */
330: sqlBuilder = new GeometrylessSQLBuilder(encoder);
331: /*
332: * }
333: */
334: return sqlBuilder;
335: }
336:
337: // /////////////////////////////
338:
339: /**
340: * Overrides <code>JDBC1DataStore.getFeatureTypeHandler</code> to return
341: * an in-process view aware one, a {@linkplain BypassSqlFeatureTypeHandler}
342: *
343: * @param config
344: * @return a {@linkplain BypassSqlFeatureTypeHandler} that maintains a
345: * registry of user defined SQL queries exposed as read-only
346: * FeatureTypes
347: */
348: protected FeatureTypeHandler getFeatureTypeHandler(
349: JDBCDataStoreConfig config) throws IOException {
350: return new BypassSqlFeatureTypeHandler(this ,
351: buildFIDMapperFactory(config), config
352: .getTypeHandlerTimeout());
353: }
354:
355: /**
356: * I'm adding this method right now just to get the class compiling. By now
357: * it just delegates to the old {@link #registerView(String, String)} with
358: * the <code>toString()</code> value of the select body. In the short
359: * term, the delegation shuold be inversed so the datastore uses the object
360: * model representing the query instead of parsing the string query "by
361: * hand".
362: */
363: public void registerView(final String typeName,
364: final SelectBody select) throws IOException {
365: String sqlQuery = select.toString();
366: registerView(typeName, sqlQuery);
367: }
368:
369: /**
370: * Creates an in-process data view against one or more actual FeatureTypes
371: * of this DataStore, which will be advertised as <code>typeName</code>
372: *
373: * @param typeName
374: * the name of the view's FeatureType.
375: * @param sqlQuery
376: * a full SQL query which will act as the view definition.
377: * @throws IOException
378: * @throws IllegalArgumentException
379: * if <code>typeName</code> already exists as one of this
380: * datastore's feature types, regardless of type name case.
381: */
382: public void registerView(final String typeName,
383: final String sqlQuery) throws IOException {
384: if (typeName == null || sqlQuery == null) {
385: throw new NullPointerException(typeName + "=" + sqlQuery);
386: }
387: String[] existingTypeNames = getTypeNames();
388: for (int i = 0; i < existingTypeNames.length; i++) {
389: if (typeName.equalsIgnoreCase(existingTypeNames[i])) {
390: throw new IllegalArgumentException(typeName
391: + " already exists: " + existingTypeNames[i]);
392: }
393: }
394: LOGGER.fine("registering view " + typeName + " as " + sqlQuery);
395: Connection conn = getConnection(Transaction.AUTO_COMMIT);
396: ResultSetMetaData rsmd;
397: try {
398: Statement st = conn.createStatement();
399: st.setMaxRows(1);
400:
401: ResultSet rs = st.executeQuery(sqlQuery);
402: rsmd = rs.getMetaData();
403:
404: // TODO: set a more appropiate fid mapper
405: FIDMapper fidMapper = new NullFIDMapper();
406: FeatureType viewType = buildSchema(typeName, rsmd);
407: BypassSqlFeatureTypeHandler th = (BypassSqlFeatureTypeHandler) typeHandler;
408: th.registerView(viewType, sqlQuery, fidMapper);
409: } catch (SQLException e) {
410: LOGGER.log(Level.WARNING, "executing query " + sqlQuery, e);
411: throw new DataSourceException("executing " + sqlQuery, e);
412: } finally {
413: try {
414: conn.close();
415: } catch (Exception e) {
416: LOGGER.warning(e.getMessage());
417: }
418: }
419: }
420:
421: /**
422: * Creates a FeatureType from a <code>java.sql.ResultSetMetaData</code>
423: * object, obtained from the execution of a SQL query configured as the
424: * source of an in-process view.
425: *
426: * @param typeName
427: * @param rsmd
428: * @return
429: * @throws IOException
430: * @throws SQLException
431: */
432: private FeatureType buildSchema(String typeName,
433: ResultSetMetaData rsmd) throws IOException, SQLException {
434: FeatureType viewType;
435:
436: final int NAME_COLUMN = 4;
437: final int TYPE_NAME = 6;
438:
439: ResultSet tableInfo = null;
440: FIDMapper mapper = new NullFIDMapper();
441: // the mapping from resultset medatada to the
442: // resultset schema expected by JDBCDataStore
443: tableInfo = new RsMd2DbMdResultSet(rsmd);
444:
445: try {
446: List attributeDescriptors = new ArrayList();
447:
448: boolean tableInfoFound = false;
449:
450: while (tableInfo.next()) {
451: tableInfoFound = true;
452: try {
453: String columnName = tableInfo
454: .getString(NAME_COLUMN);
455:
456: if (!mapper.returnFIDColumnsAsAttributes()) {
457: boolean isPresent = false;
458:
459: for (int i = 0; i < mapper.getColumnCount(); i++) {
460: if (columnName.equalsIgnoreCase(mapper
461: .getColumnName(i))) {
462: isPresent = true;
463: break;
464: }
465: }
466: if (isPresent) {
467: continue;
468: }
469: }
470:
471: // AttributeDescriptor attribute =
472: // buildAttributeDescriptor(tableInfo);
473: AttributeType attribute = buildAttributeType(tableInfo);
474:
475: if (attribute != null) {
476: attributeDescriptors.add(attribute);
477: } else {
478: LOGGER
479: .finest("Unknown SQL Type: (may be consumed) "
480: + tableInfo
481: .getString(TYPE_NAME));
482: }
483: } catch (DataSourceException dse) {
484: String msg = "Error building attribute type. The column will be ignored";
485: LOGGER.log(Level.WARNING, msg, dse);
486: }
487: }
488:
489: if (!tableInfoFound) {
490: throw new SchemaNotFoundException(typeName);
491:
492: }
493:
494: URI namespace = getNameSpace();
495: String ns = null;
496: if (namespace != null) {
497: ns = namespace.toString();
498: }
499:
500: // AttributeName ftName = new AttributeName(ns, typeName);
501: // viewType = (FeatureType)tf.createFeatureType(ftName, schema,
502: // null);
503:
504: AttributeType[] types = (AttributeType[]) attributeDescriptors
505: .toArray(new AttributeType[0]);
506: viewType = FeatureTypeFactory.newFeatureType(types,
507: typeName, namespace, false, null, null);
508:
509: return viewType;
510:
511: } catch (Exception sqlException) {
512: throw new DataSourceException(
513: "SQL Error building FeatureType for " + typeName
514: + " " + sqlException.getMessage(),
515: sqlException);
516: } finally {
517: JDBCUtils.close(tableInfo);
518: }
519: }
520:
521: }
|