0001: //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/trunk/src/org/deegree/io/quadtree/DBQuadtreeManager.java $
0002: /*---------------- FILE HEADER ------------------------------------------
0003:
0004: This file is part of deegree.
0005: Copyright (C) 2001-2008 by:
0006: EXSE, Department of Geography, University of Bonn
0007: http://www.giub.uni-bonn.de/deegree/
0008: lat/lon GmbH
0009: http://www.lat-lon.de
0010:
0011: This library is free software; you can redistribute it and/or
0012: modify it under the terms of the GNU Lesser General Public
0013: License as published by the Free Software Foundation; either
0014: version 2.1 of the License, or (at your option) any later version.
0015:
0016: This library is distributed in the hope that it will be useful,
0017: but WITHOUT ANY WARRANTY; without even the implied warranty of
0018: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0019: Lesser General Public License for more details.
0020:
0021: You should have received a copy of the GNU Lesser General Public
0022: License along with this library; if not, write to the Free Software
0023: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0024:
0025: Contact:
0026:
0027: Andreas Poth
0028: lat/lon GmbH
0029: Aennchenstr. 19
0030: 53115 Bonn
0031: Germany
0032: E-Mail: poth@lat-lon.de
0033:
0034: Prof. Dr. Klaus Greve
0035: Department of Geography
0036: University of Bonn
0037: Meckenheimer Allee 166
0038: 53115 Bonn
0039: Germany
0040: E-Mail: greve@giub.uni-bonn.de
0041:
0042: ---------------------------------------------------------------------------*/
0043: package org.deegree.io.quadtree;
0044:
0045: import java.io.IOException;
0046: import java.io.StringReader;
0047: import java.security.InvalidParameterException;
0048: import java.sql.Connection;
0049: import java.sql.Date;
0050: import java.sql.PreparedStatement;
0051: import java.sql.ResultSet;
0052: import java.sql.ResultSetMetaData;
0053: import java.sql.SQLException;
0054: import java.sql.Statement;
0055: import java.util.HashMap;
0056: import java.util.UUID;
0057:
0058: import org.deegree.datatypes.Types;
0059: import org.deegree.framework.log.ILogger;
0060: import org.deegree.framework.log.LoggerFactory;
0061: import org.deegree.framework.util.StringTools;
0062: import org.deegree.io.DBConnectionPool;
0063: import org.deegree.io.DBPoolException;
0064: import org.deegree.io.JDBCConnection;
0065: import org.deegree.io.dbaseapi.DBaseException;
0066: import org.deegree.io.shpapi.HasNoDBaseFileException;
0067: import org.deegree.io.shpapi.ShapeFile;
0068: import org.deegree.model.feature.Feature;
0069: import org.deegree.model.feature.schema.FeatureType;
0070: import org.deegree.model.feature.schema.PropertyType;
0071: import org.deegree.model.spatialschema.Envelope;
0072: import org.deegree.model.spatialschema.GMLGeometryAdapter;
0073: import org.deegree.model.spatialschema.Geometry;
0074: import org.deegree.model.spatialschema.GeometryException;
0075: import org.deegree.model.spatialschema.GeometryFactory;
0076: import org.deegree.model.spatialschema.Point;
0077:
0078: /**
0079: * Access control to a quadtree for managing spatial indizes stored in a usual database.
0080: *
0081: * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
0082: * @author last edited by: $Author: apoth $
0083: *
0084: * @version $Revision: 9342 $, $Date: 2007-12-27 04:32:57 -0800 (Thu, 27 Dec 2007) $
0085: * @param <T>
0086: * the type of the quadtree. If unsure use the determineQuattreType() method to determine the type. Be
0087: * carefull though, if you use a wrong generic here (e.g. not Integer or String) while supplying another
0088: * types.Type to the constructor there is no way to check find the correct instance.
0089: */
0090: public class DBQuadtreeManager<T> {
0091:
0092: private static final ILogger LOG = LoggerFactory
0093: .getLogger(DBQuadtreeManager.class);
0094:
0095: protected JDBCConnection jdbc = null;
0096:
0097: protected String table = null;
0098:
0099: protected String column = null;
0100:
0101: protected String owner = null;
0102:
0103: protected String indexName = null;
0104:
0105: protected int maxDepth = 6;
0106:
0107: private DBQuadtree<T> qt = null;
0108:
0109: protected Envelope envelope = null;
0110:
0111: protected String backend = null;
0112:
0113: private int TYPE;
0114:
0115: private static HashMap<String, String> quadTreeVersionInfo = new HashMap<String, String>();
0116:
0117: /**
0118: * @param jdbc
0119: * database connection info
0120: * @param owner
0121: * owner of the table (optional, database user will be used if set to null )
0122: * @param indexName
0123: * this name will be used to create the table that stores the nodes of a specific quadtree
0124: * @param table
0125: * name of table the index shall be created for
0126: * @param column
0127: * name of column the index shall be created for
0128: * @param maxDepth
0129: * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
0130: * @param type
0131: * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
0132: * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
0133: */
0134: public DBQuadtreeManager(JDBCConnection jdbc, String owner,
0135: String indexName, String table, String column,
0136: int maxDepth, int type) {
0137: TYPE = type;
0138: if (TYPE != Types.INTEGER && TYPE != Types.VARCHAR) {
0139: TYPE = Integer.MIN_VALUE;
0140: }
0141: if (jdbc == null) {
0142: throw new InvalidParameterException(
0143: "The JDBCConnection reference parameter 'jdbc' may not be null.");
0144: }
0145: this .jdbc = jdbc;
0146: if (table == null || "".equals(table.trim())) {
0147: throw new InvalidParameterException(
0148: "The 'table' parameter may not be null or emtpy.");
0149: }
0150: this .table = table.trim();
0151:
0152: if (indexName == null || "".equals(indexName.trim())
0153: || "idx_".equalsIgnoreCase(indexName.trim())) {
0154: throw new InvalidParameterException(
0155: "The 'indexName' parameter may not be null or emtpy or solumnly exist of idx_.");
0156: }
0157: this .indexName = indexName.trim();
0158:
0159: if (column == null || "".equals(column.trim())) {
0160: throw new InvalidParameterException(
0161: "The 'column' parameter may not be null or emtpy.");
0162: }
0163: this .column = column.trim();
0164:
0165: this .owner = owner;
0166: if (owner == null) {
0167: String user = jdbc.getUser();
0168: if (user == null || "".equals(user.trim())) {
0169: this .owner = "";
0170: } else {
0171: this .owner = user;
0172: }
0173: }
0174: if (maxDepth > 1) {
0175: this .maxDepth = maxDepth;
0176: } else {
0177: this .maxDepth = 6;
0178: }
0179:
0180: String driver = jdbc.getDriver();
0181: if (driver == null || "".equals(driver.trim())) {
0182: throw new InvalidParameterException(
0183: "The JDBCConnection.driver may not be null or emtpy.");
0184: }
0185: // find out which database is used
0186: if (driver.toUpperCase().contains("POSTGRES")) {
0187: backend = "POSTGRES";
0188: } else if (driver.toUpperCase().contains("SQLSERVER")) {
0189: backend = "SQLSERVER";
0190: } else if (driver.toUpperCase().contains("INGRES")
0191: || driver.equals("ca.edbc.jdbc.EdbcDriver")) {
0192: backend = "INGRES";
0193: } else if (driver.toUpperCase().contains("HSQLDB")) {
0194: backend = "HSQLDB";
0195: } else {
0196: backend = "GENERICSQL";
0197: }
0198:
0199: try {
0200: if (!hasIndexTable()) {
0201: LOG
0202: .logDebug("It seems no indextable with name: '"
0203: + indexName
0204: + "' exists in the database backend, creating one.");
0205: createIndexTable(indexName, "VARCHAR(50)");
0206: }
0207: } catch (IndexException e) {
0208: LOG.logError(
0209: "Following error occurred while trying to create the indexTable: "
0210: + e.getMessage(), e);
0211: }
0212: }
0213:
0214: /**
0215: *
0216: * @param driver
0217: * database connection driver
0218: * @param logon
0219: * database connection logon
0220: * @param user
0221: * database user
0222: * @param password
0223: * database user's password
0224: * @param encoding
0225: * character encoding to be used (if possible)
0226: * @param indexName
0227: * this name will be used to create the table that stores the nodes of a specific quadtree
0228: * @param table
0229: * name of table the index shall be created for
0230: * @param column
0231: * name of column the index shall be created for
0232: * @param owner
0233: * owner of the table (optional, database user will be used if set to null )
0234: * @param maxDepth
0235: * max depth of the generated quadtree (default = 6 if a value < 2 will be passed)
0236: * @param type
0237: * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
0238: * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
0239: */
0240: public DBQuadtreeManager(String driver, String logon, String user,
0241: String password, String encoding, String indexName,
0242: String table, String column, String owner, int maxDepth,
0243: int type) {
0244: this (new JDBCConnection(driver, logon, user, password, null,
0245: encoding, null), owner, indexName, table, column,
0246: maxDepth, type);
0247: }
0248:
0249: /**
0250: * initializes a QuadtreeManager to access an alread existing Quadtree
0251: *
0252: * @param jdbc
0253: * database connection info
0254: * @param table
0255: * name of table the index shall be created for
0256: * @param column
0257: * name of column the index shall be created for
0258: * @param owner
0259: * owner of the table (optional, database user will be used if set to null )
0260: * @param type
0261: * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
0262: * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
0263: */
0264: public DBQuadtreeManager(JDBCConnection jdbc, String table,
0265: String column, String owner, int type) {
0266: this (jdbc, owner, "idx_" + table, table, column, 6, type);
0267: }
0268:
0269: /**
0270: * initializes a QuadtreeManager to access an alread existing Quadtree
0271: *
0272: * @param driver
0273: * database connection driver
0274: * @param logon
0275: * database connection logon
0276: * @param user
0277: * database user
0278: * @param password
0279: * database user's password
0280: * @param encoding
0281: * character encoding to be used (if possible)
0282: * @param table
0283: * name of table the index shall be created for
0284: * @param column
0285: * name of column the index shall be created for
0286: * @param owner
0287: * owner of the table (optional, database user will be used if set to null )
0288: * @param type
0289: * the type of the id of the quadtree, valid are Types.INTEGER and Types.VARCHAR. if unsure use
0290: * Intger.MIN_VALUE and call {@link #determineQuattreeType()}, to get an instance of the Type.
0291: */
0292: public DBQuadtreeManager(String driver, String logon, String user,
0293: String password, String encoding, String table,
0294: String column, String owner, int type) {
0295: this (new JDBCConnection(driver, logon, user, password, null,
0296: encoding, null), owner, "idx_" + table, table, column,
0297: 6, type);
0298: }
0299:
0300: /**
0301: * loads the metadata of a Index from the TAB_DEEGREE_IDX table
0302: *
0303: * @return FK to the index
0304: * @throws IndexException
0305: */
0306: protected int loadIndexMetadata() throws IndexException {
0307: int fk_indexTree = -1;
0308: Connection con = null;
0309: DBConnectionPool pool = null;
0310: try {
0311: pool = DBConnectionPool.getInstance();
0312: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0313: .getURL(), jdbc.getUser(), jdbc.getPassword());
0314:
0315: StringBuilder sb = new StringBuilder(200);
0316: sb
0317: .append("Select INDEX_NAME, FK_INDEXTREE from TAB_DEEGREE_IDX where ");
0318: sb.append("column_name = '").append(column)
0319: .append("' AND ");
0320: sb.append("table_name = '").append(table).append("' AND ");
0321: sb.append("owner = '").append(owner).append("'");
0322:
0323: Statement stmt = con.createStatement();
0324: ResultSet rs = stmt.executeQuery(sb.toString());
0325:
0326: if (rs.next()) {
0327: indexName = rs.getString("INDEX_NAME");
0328: fk_indexTree = rs.getInt("FK_INDEXTREE");
0329: } else {
0330: throw new IndexException(
0331: "Could not read the structure of the quadtree tables from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?).");
0332: }
0333: rs.close();
0334: stmt.close();
0335: } catch (SQLException e) {
0336: throw new IndexException(
0337: "Could not load quadtree definition from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?). The error message was: "
0338: + e.getMessage());
0339: } catch (DBPoolException e) {
0340: throw new IndexException(
0341: "Could not acquire a database connection. The error message was: "
0342: + e.getMessage());
0343: } finally {
0344: try {
0345: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0346: .getURL(), jdbc.getUser(), jdbc.getPassword());
0347: } catch (Exception e1) {
0348: LOG
0349: .logError("Could not release the jdbc connection because: "
0350: + e1.getMessage());
0351: }
0352: }
0353: LOG.logDebug("It seems an indextable with name: '" + indexName
0354: + "' allready exists in the database backend.");
0355: return fk_indexTree;
0356: }
0357:
0358: /**
0359: * returns the current Quadtree
0360: *
0361: * @return the current Quadtree
0362: * @throws IndexException
0363: */
0364: public DBQuadtree<T> getQuadtree() throws IndexException {
0365: if (qt == null) {
0366: qt = loadQuadtree();
0367: }
0368: return qt;
0369: }
0370:
0371: /**
0372: * loads an already existing quadtree
0373: *
0374: * @return the Quadtree structure read from the database
0375: * @throws IndexException
0376: */
0377: private DBQuadtree<T> loadQuadtree() throws IndexException {
0378: int fk_index = loadIndexMetadata();
0379:
0380: String version = getQTVersion(table);
0381: return new DBQuadtree<T>(fk_index, indexName, jdbc, version);
0382: }
0383:
0384: /**
0385: * @return an instance of the type of the feature id's stored in the db. Possible instances are
0386: * <code>String<code>, <code>Integer</code> or <code>null</code> if the type could not be determined.
0387: * @throws IndexException if the type information could not be retrieved either because no connection was acquired or an error occurred while executing the select statement.
0388: */
0389: public Object determineQuattreeType() throws IndexException {
0390:
0391: if (TYPE == Integer.MIN_VALUE) {
0392: StringBuilder sb = new StringBuilder(1000);
0393: sb.append("SELECT FK_ITEM from ").append(indexName).append(
0394: "_ITEM ");
0395: Connection con = null;
0396: DBConnectionPool pool = null;
0397:
0398: try {
0399: pool = DBConnectionPool.getInstance();
0400: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0401: .getURL(), jdbc.getUser(), jdbc.getPassword());
0402:
0403: PreparedStatement stmt = con.prepareStatement(sb
0404: .toString());
0405: ResultSet rs = stmt.executeQuery();
0406: ResultSetMetaData metaData = rs.getMetaData();
0407:
0408: if (metaData != null) {
0409: TYPE = metaData.getColumnType(1);
0410: LOG.logDebug("Found type: " + TYPE);
0411: }
0412: rs.close();
0413: stmt.close();
0414:
0415: } catch (SQLException e) {
0416: throw new IndexException(
0417: "Could not get Type information because: "
0418: + e.getMessage(), e);
0419: } catch (DBPoolException e) {
0420: throw new IndexException(
0421: "Could not acquire a connection to the database to retrieve column information because: "
0422: + e.getMessage(), e);
0423: } finally {
0424: try {
0425: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0426: .getURL(), jdbc.getUser(), jdbc
0427: .getPassword());
0428: } catch (DBPoolException e) {
0429: LOG
0430: .logError("Could not release JDBC connection because: "
0431: + e.getMessage());
0432: }
0433: }
0434: }
0435: Object result = null;
0436: switch (TYPE) {
0437: case Types.VARCHAR:
0438: result = "";
0439: break;
0440: case Types.INTEGER:
0441: result = new Integer(1);
0442: break;
0443: default:
0444: TYPE = Integer.MAX_VALUE;
0445: }
0446: return result;
0447: }
0448:
0449: /**
0450: * @param table
0451: * to open a quadtree for.
0452: * @return the version of the quadtree used.
0453: */
0454: private String getQTVersion(String table) {
0455: String version = "1.0.0";
0456: if (quadTreeVersionInfo.containsKey(table)
0457: && quadTreeVersionInfo.get(table) != null) {
0458: LOG
0459: .logDebug("Retrieved the quatdree version info for table: "
0460: + table + " from cache.");
0461: version = quadTreeVersionInfo.get(table);
0462: } else {
0463: Connection con = null;
0464: DBConnectionPool pool = null;
0465: Statement stmt = null;
0466: ResultSet rs = null;
0467: pool = DBConnectionPool.getInstance();
0468: StringBuilder sb = new StringBuilder(400);
0469: sb
0470: .append("SELECT fk_indextree FROM tab_deegree_idx WHERE ");
0471: sb.append("column_name = 'geometry' AND ");
0472: sb.append("table_name = '").append(table.toLowerCase())
0473: .append("'");
0474:
0475: LOG.logDebug("Get Index Metadata sql statement:\n", sb);
0476: try {
0477: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0478: .getURL(), jdbc.getUser(), jdbc.getPassword());
0479: stmt = con.createStatement();
0480: rs = stmt.executeQuery(sb.toString());
0481: String tableID = null;
0482: if (rs.next()) {
0483: tableID = rs.getString(1);
0484: }
0485: if (tableID != null) {
0486: sb = new StringBuilder(400);
0487: sb.append("SELECT * FROM tab_quadtree WHERE ");
0488: sb.append("fk_root = '").append(tableID.trim())
0489: .append("'");
0490: if (rs != null) {
0491: rs.close();
0492: }
0493: if (stmt != null) {
0494: stmt.close();
0495: }
0496: stmt = con.createStatement();
0497: rs = stmt.executeQuery(sb.toString());
0498: if (rs.next()) {
0499: boolean hasVersion = false;
0500: ResultSetMetaData md = rs.getMetaData();
0501: int numberOfColumns = md.getColumnCount();
0502: System.out.println("Columnecount: "
0503: + numberOfColumns);
0504: for (int i = 1; i <= numberOfColumns
0505: && !hasVersion; i++) {
0506: String tmp = md.getColumnName(i);
0507: LOG.logDebug("Found columnname: " + tmp);
0508: if (tmp != null) {
0509: if ("version".equalsIgnoreCase(tmp
0510: .trim())) {
0511: hasVersion = true;
0512: version = rs.getString(i);
0513: LOG
0514: .logDebug("Found a version column, setting version to: "
0515: + rs.getString(i));
0516: }
0517: }
0518: }
0519: if (!hasVersion) {
0520: try {
0521: LOG
0522: .logInfo("Found no Version Column in the TAB_QUADTREE table, assuming version 1.0.0, and adding the version column.");
0523: if (rs != null) {
0524: rs.close();
0525: }
0526: if (stmt != null) {
0527: stmt.close();
0528: }
0529: stmt = con.createStatement();
0530: rs = stmt
0531: .executeQuery("ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)");
0532: rs.close();
0533: stmt.close();
0534: } catch (SQLException e) {
0535: if (rs != null) {
0536: rs.close();
0537: }
0538: if (stmt != null) {
0539: stmt.close();
0540: }
0541: LOG
0542: .logError(
0543: "An error occurred while trying to insert a new 'version' column in the database: "
0544: + e
0545: .getMessage(),
0546: e);
0547: }
0548: }
0549: }
0550: } else {
0551: LOG
0552: .logError("Could not find the foreign key (fk_root) of the table: '"
0553: + table
0554: + "' is your database is set up correct?");
0555: }
0556: } catch (SQLException e) {
0557: LOG
0558: .logError(
0559: "An error occurred while determening version of quadtree, therefore setting version to '1.0.0'. Errormessage: "
0560: + e.getMessage(), e);
0561: } catch (DBPoolException e) {
0562: LOG
0563: .logError(
0564: "An error occurred while acquiring connection to the database to determine version of quadtree, therefore setting version to '1.0.0'. Errormessage: "
0565: + e.getMessage(), e);
0566: } finally {
0567: quadTreeVersionInfo.put(table, version);
0568: try {
0569: if (rs != null) {
0570: rs.close();
0571: }
0572: if (stmt != null) {
0573: stmt.close();
0574: }
0575: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0576: .getURL(), jdbc.getUser(), jdbc
0577: .getPassword());
0578: } catch (SQLException e) {
0579: LOG
0580: .logError("Could not close ResultSet or Statement because: "
0581: + e.getMessage());
0582: } catch (DBPoolException e) {
0583: LOG
0584: .logError("Could not reslease connection because: "
0585: + e.getMessage());
0586: }
0587: }
0588: }
0589: return version;
0590: }
0591:
0592: /**
0593: * stores one feature into the defined table
0594: *
0595: * @param feature
0596: * the feature to insert into the 'table'
0597: * @param id
0598: * of the feature to store in the database, currently String and Integer are supported. If it is neither,
0599: * the Object is saved as an object, which may result in inconsitencies.
0600: * @param jdbc
0601: * the connection to the database.
0602: * @throws IndexException
0603: * if the feature can not be inserted or a connection error occurrs.
0604: */
0605: protected void storeFeature(Feature feature, T id,
0606: JDBCConnection jdbc) throws IndexException {
0607:
0608: Connection con = null;
0609: DBConnectionPool pool = null;
0610:
0611: FeatureType ft = feature.getFeatureType();
0612: PropertyType[] ftp = ft.getProperties();
0613: try {
0614: pool = DBConnectionPool.getInstance();
0615: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0616: .getURL(), jdbc.getUser(), jdbc.getPassword());
0617:
0618: StringBuilder sb = new StringBuilder(100);
0619: sb.append("INSERT INTO ").append(table).append('(');
0620: sb.append("FEATURE_ID,");
0621: for (int i = 0; i < ftp.length; i++) {
0622: if (ftp[i].getType() == Types.GEOMETRY) {
0623: sb.append(column).append(' ');
0624: } else {
0625: sb.append(ftp[i].getName().getLocalName());
0626: }
0627: if (i < ftp.length - 1) {
0628: sb.append(", ");
0629: }
0630: }
0631: sb.append(") VALUES (?,");
0632: for (int i = 0; i < ftp.length; i++) {
0633: sb.append('?');
0634: if (i < ftp.length - 1) {
0635: sb.append(", ");
0636: }
0637: }
0638: sb.append(')');
0639:
0640: PreparedStatement stmt = con
0641: .prepareStatement(sb.toString());
0642: if (id instanceof String) {
0643: LOG.logDebug("Setting to id '" + id
0644: + "'an instance of String");
0645: stmt.setString(1, (String) id);
0646: } else if (id instanceof Integer) {
0647: LOG.logDebug("Setting to id '" + id
0648: + "'an instance of integer");
0649: stmt.setInt(1, ((Integer) id).intValue());
0650: } else {
0651: LOG
0652: .logWarning("The type of id is uncertain (neiter String nor Integer), adding it as an 'object' to the database.");
0653: stmt.setObject(1, id);
0654: }
0655:
0656: for (int i = 0; i < ftp.length; i++) {
0657: Object o = null;
0658: if (feature.getProperties(ftp[i].getName()) != null) {
0659: if (feature.getProperties(ftp[i].getName()).length > 0) {
0660: o = feature.getProperties(ftp[i].getName())[0]
0661: .getValue();
0662: }
0663: }
0664: if (o == null) {
0665: stmt.setNull(i + 2, ftp[i].getType());
0666: } else {
0667: switch (ftp[i].getType()) {
0668: case Types.CHAR:
0669: case Types.VARCHAR:
0670: stmt.setString(i + 2, o.toString());
0671: break;
0672: case Types.SMALLINT:
0673: case Types.TINYINT:
0674: case Types.INTEGER:
0675: case Types.BIGINT:
0676: stmt.setInt(i + 2, (int) Double.parseDouble(o
0677: .toString()));
0678: break;
0679: case Types.DOUBLE:
0680: case Types.FLOAT:
0681: case Types.DECIMAL:
0682: case Types.NUMERIC:
0683: stmt.setFloat(i + 2, Float.parseFloat(o
0684: .toString()));
0685: break;
0686: case Types.DATE:
0687: case Types.TIME:
0688: case Types.TIMESTAMP:
0689: stmt.setDate(i + 2, (Date) o);
0690: break;
0691: case Types.GEOMETRY: {
0692: StringBuffer gs = GMLGeometryAdapter
0693: .export((Geometry) o);
0694: String s = StringTools
0695: .replace(
0696: gs.toString(),
0697: ">",
0698: " xmlns:gml=\"http://www.opengis.net/gml\">",
0699: false);
0700: if (backend.equals("POSTGRES")
0701: || backend.equals("HSQLDB")) {
0702: LOG.logDebug("Adding geometry: " + s);
0703: stmt.setString(i + 2, s);
0704: } else if (backend.equals("INGRES")) {
0705: stmt.setObject(i + 2, new StringReader(s));
0706: } else {
0707: stmt.setObject(i + 2, s.getBytes());
0708: }
0709: break;
0710: }
0711: default: {
0712: LOG.logWarning("unsupported type: "
0713: + ftp[i].getType());
0714: }
0715: }
0716: }
0717: }
0718: LOG.logDebug("SQL statement for insert feature: " + sb);
0719: if (!stmt.execute()) {
0720: LOG
0721: .logError("The insertion of the feature resulted in "
0722: + stmt.getUpdateCount() + " updates.");
0723: }
0724:
0725: stmt.close();
0726: } catch (SQLException e) {
0727: String msg = "Could not insert feature with id='" + id
0728: + "' into the database because: " + e.getMessage();
0729: LOG.logError(msg, e);
0730: throw new IndexException(msg, e);
0731: } catch (DBPoolException e) {
0732: String msg = "Could not acquire a connection to the database to insert the feature with id: "
0733: + id;
0734: LOG.logError(msg, e);
0735: throw new IndexException(msg, e);
0736: } catch (GeometryException e) {
0737: String msg = "Could not insert feature with id='" + id
0738: + "' into the database because: " + e.getMessage();
0739: LOG.logError(msg, e);
0740: throw new IndexException(msg, e);
0741: } finally {
0742: try {
0743: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0744: .getURL(), jdbc.getUser(), jdbc.getPassword());
0745: } catch (DBPoolException e) {
0746: LOG
0747: .logError("Could not release JDBC connection because: "
0748: + e.getMessage());
0749: }
0750: }
0751: }
0752:
0753: /**
0754: * initializes the root node of the quadtree
0755: *
0756: * @param fileName
0757: * @throws IndexException
0758: * @throws IOException
0759: *
0760: */
0761: protected void initRootNode(String fileName) throws IndexException,
0762: IOException {
0763: LOG.logDebug("Trying to read shapefile from file: " + fileName);
0764: ShapeFile sf = new ShapeFile(fileName);
0765: if (envelope == null) {
0766: envelope = sf.getFileMBR();
0767: }
0768: envelope = envelope.getBuffer(envelope.getWidth() / 20);
0769: LOG.logInfo("Bounding box of the root feature: " + envelope);
0770: sf.close();
0771: // DBQuadtree<T> qtTmp = loadQuadtree();
0772: Connection con = null;
0773: DBConnectionPool pool = null;
0774: try {
0775: pool = DBConnectionPool.getInstance();
0776: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0777: .getURL(), jdbc.getUser(), jdbc.getPassword());
0778:
0779: StringBuilder sb = new StringBuilder(100);
0780: sb.append("INSERT INTO ").append(indexName);
0781: sb.append(" ( ID, MINX, MINY, MAXX , MAXY ) ");
0782: sb.append("VALUES ( ?, ?, ?, ?, ? ) ");
0783: PreparedStatement stmt = con
0784: .prepareStatement(sb.toString());
0785: stmt.setString(1, "1");
0786: stmt.setFloat(2, (float) envelope.getMin().getX());
0787: stmt.setFloat(3, (float) envelope.getMin().getY());
0788: stmt.setFloat(4, (float) envelope.getMax().getX());
0789: stmt.setFloat(5, (float) envelope.getMax().getY());
0790: stmt.execute();
0791: stmt.close();
0792: } catch (Exception e) {
0793: LOG.logError(e.getMessage(), e);
0794: throw new IndexException(
0795: "could not create root node definition at database",
0796: e);
0797: } finally {
0798: try {
0799: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0800: .getURL(), jdbc.getUser(), jdbc.getPassword());
0801: } catch (Exception e1) {
0802: e1.printStackTrace();
0803: }
0804: }
0805: }
0806:
0807: /**
0808: * before importing a shape a user may set an envelope for the quadtree to bee created that is different from the
0809: * one of the shape by calling this method. Notice: calling this method does not have any effect when calling
0810: *
0811: * @see #appendShape(String) method.
0812: * @param envelope
0813: */
0814: public void setRootEnvelope(Envelope envelope) {
0815: this .envelope = envelope;
0816: }
0817:
0818: /**
0819: * initializes a new Quadtree by adding a row into table TAB_QUADTREE and into TAB_QTNODE (-> root node)
0820: *
0821: * @param fileName
0822: *
0823: * @return the id of the inserted node
0824: * @throws IndexException
0825: * @throws IOException
0826: * if the shape file could not be read.
0827: */
0828: protected int initQuadtree(String fileName) throws IndexException,
0829: IOException {
0830:
0831: initRootNode(fileName);
0832: Connection con = null;
0833: DBConnectionPool pool = null;
0834: int id = -1;
0835: try {
0836: pool = DBConnectionPool.getInstance();
0837: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0838: .getURL(), jdbc.getUser(), jdbc.getPassword());
0839:
0840: // first check if the version column exists;
0841: StringBuilder versionCheck = new StringBuilder(
0842: "Select * from TAB_QUADTREE;");
0843: Statement stmt = con.createStatement();
0844: ResultSet rs = stmt.executeQuery(versionCheck.toString());
0845: boolean hasVersion = false;
0846: try {
0847: ResultSetMetaData md = rs.getMetaData();
0848: int numberOfColumns = md.getColumnCount();
0849:
0850: for (int i = 1; i <= numberOfColumns && !hasVersion; i++) {
0851: String tmp = md.getColumnName(i);
0852: if (tmp != null) {
0853: if ("version".equalsIgnoreCase(tmp.trim())) {
0854: hasVersion = true;
0855: }
0856: }
0857: }
0858: if (!hasVersion) {
0859: LOG
0860: .logInfo("Found no Version Column in the TAB_QUADTREE table, assuming version 2.0.0, and adding the version column.");
0861: rs.close();
0862: stmt.close();
0863: stmt = con.createStatement();
0864: rs = stmt
0865: .executeQuery("ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)");
0866: rs.close();
0867: stmt.close();
0868: }
0869: } catch (SQLException e) {
0870: LOG
0871: .logError("An error occurred while trying to determine if the database supports versioning: "
0872: + e.getMessage());
0873: }
0874:
0875: StringBuilder sb = new StringBuilder(100);
0876: sb.append("INSERT INTO TAB_QUADTREE (");
0877: if (backend.equals("INGRES") || backend.equals("HSQLDB")) {
0878: sb.append("ID, ");
0879: }
0880: sb.append("FK_ROOT, DEPTH, VERSION ) VALUES ( ");
0881: if (backend.equals("INGRES") || backend.equals("HSQLDB")) {
0882: stmt = con.createStatement();
0883: rs = stmt
0884: .executeQuery("SELECT MAX(ID) FROM TAB_QUADTREE");
0885: rs.next();
0886: int myid = rs.getInt(1) + 1;
0887: sb.append(myid + ", ");
0888: }
0889: sb.append(" '1', ?, '2.0.0' ) ");
0890:
0891: PreparedStatement pstmt = con.prepareStatement(sb
0892: .toString());
0893: pstmt.setInt(1, maxDepth);
0894: pstmt.execute();
0895: pstmt.close();
0896: stmt = con.createStatement();
0897: rs = stmt.executeQuery("select max(ID) from TAB_QUADTREE");
0898: rs.next();
0899: id = rs.getInt(1);
0900: if (id < 0) {
0901: throw new IndexException(
0902: "could not read ID of quadtree from database.");
0903: }
0904: } catch (SQLException e) {
0905: throw new IndexException(
0906: "Could not load quadtree definition from database (did you run the base/scripts/index/quadtree.hsql script, which create the meta-info tables?). The error message was: "
0907: + e.getMessage());
0908: } catch (DBPoolException e) {
0909: throw new IndexException(
0910: "Could not acquire a connection to the database to initiate the quattree index structure because: "
0911: + e.getMessage());
0912: } finally {
0913: try {
0914: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0915: .getURL(), jdbc.getUser(), jdbc.getPassword());
0916: } catch (DBPoolException e) {
0917: LOG
0918: .logError("Could not release JDBC connection because: "
0919: + e.getMessage());
0920: }
0921: }
0922: return id;
0923: }
0924:
0925: /**
0926: * Inserts a row into the quadtree meta data structure 'TAB_DEEGREE_IDX', containing information on the table,
0927: * geometry, indexname, owner and the foreign_key to the index table.
0928: *
0929: * @param fk_indexTree
0930: * @throws IndexException
0931: */
0932: public void insertIndexMetadata(int fk_indexTree)
0933: throws IndexException {
0934:
0935: Connection con = null;
0936: DBConnectionPool pool = null;
0937: try {
0938: pool = DBConnectionPool.getInstance();
0939: con = pool.acquireConnection(jdbc.getDriver(), jdbc
0940: .getURL(), jdbc.getUser(), jdbc.getPassword());
0941:
0942: StringBuilder sb = new StringBuilder(100);
0943: sb.append("INSERT INTO TAB_DEEGREE_IDX ( ");
0944: if (backend.equals("INGRES") || backend.equals("HSQLDB")) {
0945: sb.append("ID, ");
0946: }
0947: sb.append("column_name, table_name, ");
0948: sb.append("owner, INDEX_NAME, FK_indexTree ) ");
0949: sb.append("VALUES ( ");
0950: if (backend.equals("INGRES") || backend.equals("HSQLDB")) {
0951: Statement stm = con.createStatement();
0952: ResultSet rs = stm
0953: .executeQuery("SELECT MAX(ID) FROM TAB_QUADTREE");
0954: rs.next();
0955: int myid = rs.getInt(1) + 1;
0956: sb.append(myid + ", ");
0957: }
0958: sb.append("?, ?, ?, ?, ? ) ");
0959: PreparedStatement stmt = con
0960: .prepareStatement(sb.toString());
0961: stmt.setString(1, column);
0962: stmt.setString(2, table);
0963: stmt.setString(3, owner);
0964: stmt.setString(4, indexName);
0965: stmt.setInt(5, fk_indexTree);
0966:
0967: stmt.execute();
0968: stmt.close();
0969: } catch (SQLException e) {
0970: throw new IndexException(
0971: "Could not insert a new row into the quadtree index metadata table (did you run the base/scripts/index/quadtree.hsql script, which creates the meta-info tables?). The error message was: "
0972: + e.getMessage());
0973: } catch (DBPoolException e) {
0974: throw new IndexException(
0975: "Could not acquire a connection to the database to store the quattree index metadata structure because: "
0976: + e.getMessage());
0977: } finally {
0978: try {
0979: pool.releaseConnection(con, jdbc.getDriver(), jdbc
0980: .getURL(), jdbc.getUser(), jdbc.getPassword());
0981: } catch (DBPoolException e) {
0982: LOG
0983: .logError("Could not release JDBC connection because: "
0984: + e.getMessage());
0985: }
0986: }
0987: }
0988:
0989: /**
0990: * creates table the shape data shall be stored
0991: *
0992: * @param fileName
0993: * @param idType
0994: * the type of the feature_id column, for example VARCHAR(50) or NUMBER.
0995: * @throws IndexException
0996: * @throws IOException
0997: */
0998: protected void createDataTable(String fileName, String idType)
0999: throws IndexException, IOException {
1000: ShapeFile sf = new ShapeFile(fileName);
1001: FeatureType ft = null;
1002: try {
1003: ft = sf.getFeatureByRecNo(1).getFeatureType();
1004: } catch (HasNoDBaseFileException e) {
1005: throw new IndexException(e);
1006: } catch (DBaseException e) {
1007: throw new IndexException(e);
1008: }
1009: sf.close();
1010: StringBuilder sb = new StringBuilder(1000);
1011: sb.append("CREATE TABLE ").append(table).append('(');
1012:
1013: sb.append("FEATURE_ID ").append(idType).append(",");
1014: PropertyType[] ftp = ft.getProperties();
1015: for (int i = 0; i < ftp.length; i++) {
1016: if (ftp[i].getType() == Types.GEOMETRY) {
1017: sb.append(column).append(' ');
1018: } else {
1019: sb.append(ftp[i].getName().getLocalName()).append(' ');
1020: }
1021: sb.append(getDatabaseType(ftp[i].getType()));
1022: if (i < ftp.length - 1) {
1023: sb.append(", ");
1024: }
1025: }
1026: sb.append(')');
1027:
1028: Connection con = null;
1029: DBConnectionPool pool = null;
1030: try {
1031: pool = DBConnectionPool.getInstance();
1032: con = pool.acquireConnection(jdbc.getDriver(), jdbc
1033: .getURL(), jdbc.getUser(), jdbc.getPassword());
1034:
1035: Statement stmt = con.createStatement();
1036: LOG.logDebug(sb.toString());
1037: stmt.execute(sb.toString());
1038: stmt.close();
1039: } catch (SQLException e) {
1040: throw new IndexException(
1041: "Could not create a DataTable: '"
1042: + table
1043: + "' (which will hold the features from the shapefile: '"
1044: + fileName + "'). The error message was: "
1045: + e.getMessage(), e);
1046: } catch (DBPoolException e) {
1047: throw new IndexException(
1048: "Could not acquire a connection to the database to create a DataTable because: "
1049: + e.getMessage(), e);
1050: } finally {
1051: try {
1052: pool.releaseConnection(con, jdbc.getDriver(), jdbc
1053: .getURL(), jdbc.getUser(), jdbc.getPassword());
1054: } catch (DBPoolException e) {
1055: LOG
1056: .logError("Could not release JDBC connection because: "
1057: + e.getMessage());
1058: }
1059: }
1060: }
1061:
1062: /**
1063: * returns the type name for a generic type code as used by SQLServer
1064: *
1065: * @param dataTypeCode
1066: * @return the type name for a generic type code as used by SQLServer
1067: * @throws IndexException
1068: */
1069: String getDatabaseType(int dataTypeCode) {
1070: String type = null;
1071:
1072: switch (dataTypeCode) {
1073: case Types.CHAR:
1074: case Types.VARCHAR:
1075: type = DBQuadtreeDataTypes.getString(backend + ".string");
1076: break;
1077: case Types.SMALLINT:
1078: case Types.TINYINT:
1079: case Types.INTEGER:
1080: case Types.BIGINT:
1081: type = DBQuadtreeDataTypes.getString(backend + ".integer");
1082: break;
1083: case Types.DOUBLE:
1084: case Types.FLOAT:
1085: case Types.DECIMAL:
1086: case Types.NUMERIC:
1087: type = DBQuadtreeDataTypes.getString(backend + ".float");
1088: break;
1089: case Types.DATE:
1090: case Types.TIME:
1091: case Types.TIMESTAMP:
1092: type = DBQuadtreeDataTypes.getString(backend + ".datetime");
1093: break;
1094: case Types.GEOMETRY:
1095: type = DBQuadtreeDataTypes.getString(backend + ".geometry");
1096: break;
1097: default:
1098: throw new InvalidParameterException(
1099: "Unknown data type code: " + dataTypeCode);
1100: }
1101:
1102: return type;
1103: }
1104:
1105: /**
1106: * imports a shape into the database and builds a quadtree on it
1107: *
1108: * @param fileName
1109: * of the shapefile.
1110: * @throws IOException
1111: * if the shapefile could not be opened.
1112: * @throws IndexException
1113: * if an error occurred while talking to the jdbc database.
1114: * @throws DBaseException
1115: * if the connection to the shapefile could not be opened.
1116: * @throws HasNoDBaseFileException
1117: * if the feature could not be read from shape file's database file.
1118: */
1119: public void importShape(String fileName) throws IOException,
1120: IndexException, HasNoDBaseFileException, DBaseException {
1121: if (TYPE == Integer.MIN_VALUE) {
1122: LOG
1123: .logInfo("You supplied an unknown type to the DBQuadtreeManager, therefore assuming you meant the Types.VARCHAR type");
1124: TYPE = Types.VARCHAR;
1125: }
1126: StringBuilder typeName = new StringBuilder(64);
1127:
1128: typeName.append(getDatabaseType(TYPE));
1129:
1130: createDataTable(fileName, typeName.toString());
1131:
1132: int qtid = initQuadtree(fileName);
1133:
1134: insertIndexMetadata(qtid);
1135:
1136: qt = new DBQuadtree<T>(qtid, indexName, jdbc);
1137:
1138: ShapeFile sf = new ShapeFile(fileName);
1139:
1140: double step = 100.0 / sf.getRecordNum();
1141: double counter = 0;
1142: Envelope sfEnv = sf.getFileMBR();
1143:
1144: LOG.logDebug("The shape file read " + sf.getRecordNum()
1145: + " number of records");
1146: for (int i = 0; i < sf.getRecordNum(); i++) {
1147: Feature feat = sf.getFeatureByRecNo(i + 1);
1148: if (counter < step * i) {
1149: if (step < 1) {
1150: counter += 10;
1151: } else {
1152: counter += step;
1153: }
1154: System.out.println(counter + "%");
1155: }
1156: if (i % 200 == 0) {
1157: System.gc();
1158: }
1159: Envelope env = feat.getDefaultGeometryPropertyValue()
1160: .getEnvelope();
1161: LOG.logDebug(i + " --- " + env);
1162: if (env == null) {
1163: // must be a point geometry
1164: Point point = (Point) feat
1165: .getDefaultGeometryPropertyValue();
1166: double w = sfEnv.getWidth() / 1000;
1167: double h = sfEnv.getHeight() / 1000;
1168: env = GeometryFactory.createEnvelope(point.getX() - w
1169: / 2d, point.getY() - h / 2d, point.getX() + w
1170: / 2d, point.getY() + h / 2d, null);
1171: }
1172: // map to the requested featuretype id's type
1173: T id = getMappedID(i);
1174: LOG.logDebug("Inserting item : " + i);
1175: qt.insert(id, env);
1176: storeFeature(feat, id, jdbc);
1177: }
1178:
1179: if ("HSQLDB".equals(backend)) {
1180: LOG
1181: .logInfo("Because you are using an hsql database, the current thread will wait '10' seconds, this gives the inmemory database time to flush it's tables");
1182: try {
1183: Thread.sleep(10000);
1184: } catch (InterruptedException e) {
1185: LOG
1186: .logError(
1187: "Exception occurred while waitig for the db-manager to flush it's memory tables. Message: "
1188: + e.getMessage(), e);
1189: }
1190: }
1191: sf.close();
1192: LOG.logInfo("finished!");
1193: }
1194:
1195: @SuppressWarnings("unchecked")
1196: private T getMappedID(int i) {
1197: if (TYPE == Types.VARCHAR) {
1198: return (T) UUID.randomUUID().toString();
1199: } else if (TYPE == Types.INTEGER) {
1200: return (T) new Integer(i);
1201: }
1202: return null;
1203:
1204: }
1205:
1206: /**
1207: * appends the features of a shape to an existing datatable and inserts references into the assigned quadtree table.
1208: * <p>
1209: * you have to consider that the quadtree is just valid for a defined area. if the features to append exceeds this
1210: * area the quadtree has to be rebuilded.
1211: * </p>
1212: *
1213: * @param fileName
1214: * @throws IOException
1215: * if the shape file cannot be read.
1216: * @throws IndexException
1217: * if the quatree could not be read.
1218: */
1219: public void appendShape(String fileName) throws IOException,
1220: IndexException {
1221:
1222: ShapeFile sf = new ShapeFile(fileName);
1223:
1224: int b = sf.getRecordNum() / 100;
1225: if (b == 0)
1226: b = 1;
1227: int k = 0;
1228: qt = getQuadtree();
1229: Envelope sfEnv = sf.getFileMBR();
1230: int cnt = getMaxIdValue();
1231:
1232: for (int i = 0; i < sf.getRecordNum(); i++) {
1233: Feature feat = null;
1234: try {
1235: feat = sf.getFeatureByRecNo(i + 1);
1236: } catch (HasNoDBaseFileException e) {
1237: throw new IndexException(e);
1238: } catch (DBaseException e) {
1239: throw new IndexException(e);
1240: }
1241: if (i % b == 0) {
1242: System.out.println(k + "%");
1243: k++;
1244: }
1245: if (i % 200 == 0) {
1246: System.gc();
1247: }
1248: Envelope env = feat.getDefaultGeometryPropertyValue()
1249: .getEnvelope();
1250: if (env == null) {
1251: // must be a point geometry
1252: Point point = (Point) feat
1253: .getDefaultGeometryPropertyValue();
1254: double w = sfEnv.getWidth() / 1000;
1255: double h = sfEnv.getHeight() / 1000;
1256: env = GeometryFactory.createEnvelope(point.getX() - w
1257: / 2d, point.getY() - h / 2d, point.getX() + w
1258: / 2d, point.getY() + h / 2d, null);
1259: }
1260: // map to the requested featuretype id's type
1261: T id = getMappedID(cnt + i + 1);
1262: qt.insert(id, env);
1263: storeFeature(feat, id, jdbc);
1264: }
1265: LOG.logInfo(" finished!");
1266: sf.close();
1267: }
1268:
1269: /**
1270: * returns the maximum ID of the data table
1271: *
1272: * @return the maximum ID of the data table
1273: * @throws IndexException
1274: */
1275: private int getMaxIdValue() throws IndexException {
1276: if (TYPE != Types.INTEGER) {
1277: return 0;
1278: }
1279: String sql = "SELECT MAX( FEATURE_ID ) FROM " + table;
1280:
1281: Connection con = null;
1282: DBConnectionPool pool = null;
1283: Statement stmt = null;
1284: int maxId = 0;
1285: try {
1286: pool = DBConnectionPool.getInstance();
1287: con = pool.acquireConnection(jdbc.getDriver(), jdbc
1288: .getURL(), jdbc.getUser(), jdbc.getPassword());
1289:
1290: stmt = con.createStatement();
1291: LOG.logDebug(sql);
1292: ResultSet rs = stmt.executeQuery(sql);
1293: if (rs.next()) {
1294: maxId = rs.getInt(1);
1295: }
1296: } catch (SQLException e) {
1297: throw new IndexException(
1298: "Error while executing the sql statement while finding the max( Faeture_Id ) from table: "
1299: + table, e);
1300: } catch (DBPoolException e) {
1301: throw new IndexException(
1302: "Could not acquire a jdbc connection to read the max( Faeture_Id ) from table: "
1303: + table, e);
1304: } finally {
1305: try {
1306: pool.releaseConnection(con, jdbc.getDriver(), jdbc
1307: .getURL(), jdbc.getUser(), jdbc.getPassword());
1308: } catch (DBPoolException e) {
1309: LOG
1310: .logError("Could not release JDBC connection because: "
1311: + e.getMessage());
1312: }
1313: }
1314:
1315: return maxId;
1316: }
1317:
1318: /**
1319: * Creates actually two tables, an indextable, which will hold the actual quadtree and an index_item table which is
1320: * a join-table between the dbNodes and the feature_ids.
1321: *
1322: * @param indexTable
1323: * name of the index table.
1324: * @param idType
1325: * the type of the feature_id column, for example VARCHAR(50) or NUMBER.
1326: * @throws IndexException
1327: * if the table could not be created.
1328: */
1329: protected void createIndexTable(String indexTable, String idType)
1330: throws IndexException {
1331: StringBuilder sb = new StringBuilder(2000);
1332: String qtDataType = getDatabaseType(Types.VARCHAR);
1333: sb.append("CREATE TABLE ").append(indexTable).append(" ( ");
1334: sb.append("ID ").append(qtDataType).append(" NOT NULL,");
1335: sb.append("minx float NOT NULL,");
1336: sb.append("miny float NOT NULL,");
1337: sb.append("maxx float NOT NULL,");
1338: sb.append("maxy float NOT NULL,");
1339: sb.append("FK_SUBNODE1 ").append(qtDataType);
1340: sb.append(", FK_SUBNODE2 ").append(qtDataType);
1341: sb.append(", FK_SUBNODE3 ").append(qtDataType);
1342: sb.append(", FK_SUBNODE4 ").append(qtDataType).append(")");
1343:
1344: StringBuilder sb2 = new StringBuilder(1000);
1345: sb2.append("CREATE TABLE ").append(indexName)
1346: .append("_ITEM ( ");
1347: sb2.append("FK_QTNODE ").append(qtDataType)
1348: .append(" NOT NULL,");
1349: sb2.append("FK_ITEM ").append(idType).append(" NOT NULL )");
1350:
1351: Connection con = null;
1352: DBConnectionPool pool = null;
1353: try {
1354: pool = DBConnectionPool.getInstance();
1355: con = pool.acquireConnection(jdbc.getDriver(), jdbc
1356: .getURL(), jdbc.getUser(), jdbc.getPassword());
1357:
1358: Statement stmt = con.createStatement();
1359: stmt.execute(sb.toString());
1360: stmt.close();
1361:
1362: stmt = con.createStatement();
1363: stmt.execute(sb2.toString());
1364: stmt.close();
1365: } catch (SQLException e) {
1366: throw new IndexException(
1367: "Could not create the indextable: '" + indexTable
1368: + "' and/or the index_item table: '"
1369: + indexTable
1370: + "_ITEM'. The error message was: "
1371: + e.getMessage(), e);
1372: } catch (DBPoolException e) {
1373: throw new IndexException(
1374: "Could not acquire a connection to the database to store create the necessary tables: "
1375: + e.getMessage(), e);
1376: } finally {
1377: try {
1378: pool.releaseConnection(con, jdbc.getDriver(), jdbc
1379: .getURL(), jdbc.getUser(), jdbc.getPassword());
1380: } catch (DBPoolException e) {
1381: LOG
1382: .logError("Could not release JDBC connection because: "
1383: + e.getMessage());
1384: }
1385: }
1386: }
1387:
1388: /**
1389: * Executes a simple select from indextable, and returns true if no SQL exception occurred.
1390: * @return true if a select * from indextable resulted in no exceptions, false otherwise.
1391: */
1392: private boolean hasIndexTable() {
1393: DBConnectionPool pool = DBConnectionPool.getInstance();
1394: Connection con = null;
1395: try {
1396: con = pool.acquireConnection(jdbc.getDriver(), jdbc
1397: .getURL(), jdbc.getUser(), jdbc.getPassword());
1398: } catch (DBPoolException e) {
1399: LOG.logError(
1400: "Could not aqcuire connection to the database backend because: "
1401: + e.getMessage(), e);
1402: return false;
1403: } finally {
1404: try {
1405: pool.releaseConnection(con, jdbc.getDriver(), jdbc
1406: .getURL(), jdbc.getUser(), jdbc.getPassword());
1407: } catch (Exception e1) {
1408: LOG
1409: .logError("Could not release the jdbc connection because: "
1410: + e1.getMessage());
1411: }
1412: }
1413:
1414: try {
1415: Statement stmt = con.createStatement();
1416: stmt.execute("SELECT * from " + indexName);
1417: } catch (SQLException e) {
1418: return false;
1419: }
1420:
1421: return true;
1422: }
1423:
1424: }
|