    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/datastore/sql/generic/GenericSQLTransaction.java $
    002    /*----------------------------------------------------------------------------
    003     This file is part of deegree, http://deegree.org/
    004     Copyright (C) 2001-2009 by:
    005       Department of Geography, University of Bonn
    006     and
    007       lat/lon GmbH
    008
    009     This library is free software; you can redistribute it and/or modify it under
    010     the terms of the GNU Lesser General Public License as published by the Free
    011     Software Foundation; either version 2.1 of the License, or (at your option)
    012     any later version.
    013     This library is distributed in the hope that it will be useful, but WITHOUT
    014     ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
    015     FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
    016     details.
    017     You should have received a copy of the GNU Lesser General Public License
    018     along with this library; if not, write to the Free Software Foundation, Inc.,
    019     59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    020
    021     Contact information:
    022
    023     lat/lon GmbH
    024     Aennchenstr. 19, 53177 Bonn
    025     Germany
    026     http://lat-lon.de/
    027
    028     Department of Geography, University of Bonn
    029     Prof. Dr. Klaus Greve
    030     Postfach 1147, 53001 Bonn
    031     Germany
    032     http://www.geographie.uni-bonn.de/deegree/
    033
    034     e-mail: info@deegree.org
    035    ----------------------------------------------------------------------------*/
    036
    037    package org.deegree.io.datastore.sql.generic;
    038
    039    import java.sql.Connection;
    040    import java.sql.ResultSet;
    041    import java.sql.ResultSetMetaData;
    042    import java.sql.SQLException;
    043    import java.sql.Statement;
    044    import java.util.HashMap;
    045    import java.util.List;
    046    import java.util.Map;
    047
    048    import org.deegree.framework.log.ILogger;
    049    import org.deegree.framework.log.LoggerFactory;
    050    import org.deegree.i18n.Messages;
    051    import org.deegree.io.DBConnectionPool;
    052    import org.deegree.io.DBPoolException;
    053    import org.deegree.io.JDBCConnection;
    054    import org.deegree.io.datastore.DatastoreException;
    055    import org.deegree.io.datastore.FeatureId;
    056    import org.deegree.io.datastore.schema.MappedFeatureType;
    057    import org.deegree.io.datastore.sql.AbstractSQLDatastore;
    058    import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
    059    import org.deegree.io.datastore.sql.TableAliasGenerator;
    060    import org.deegree.io.datastore.sql.transaction.SQLTransaction;
    061    import org.deegree.io.quadtree.DBQuadtree;
    062    import org.deegree.io.quadtree.IndexException;
    063    import org.deegree.model.feature.Feature;
    064    import org.deegree.model.feature.FeatureProperty;
    065    import org.deegree.model.filterencoding.Filter;
    066    import org.deegree.model.spatialschema.Envelope;
    067    import org.deegree.model.spatialschema.GeometryException;
    068    import org.deegree.ogcbase.PropertyPath;
    069
    070    /**
    071     * Special transaction implementation for the {@link GenericSQLDatastore}.
    072     * <p>
    073     * Please note that the quadtree management isn't finished yet.
    074     * </p><p>
    075     * What should work:
    076     * <ul>
    077     * <li>inserting of new features</li>
    078     * <li>deleting of features</li>
    079     * <li>updating features (unless the geometry property is changed)</li>
    080     * </ul>
    081     * </p><p>
    082     * What definitely won't work:
    083     * <ul>
    084     * <li>updating geometry properties will most probably break the index</li>
    085     * </ul>
    086     * </p>
    087     *
    088     * @see org.deegree.io.quadtree
    089     *
    090     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    091     * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
    092     * @author <a href="mailto:bezema@lat-lon.de">Rutger Bezema</a>
    093     * @author last edited by: $Author: awietschke $
    094     *
    095     * @version $Revision: 18836 $, $Date: 2009-07-31 12:28:01 +0200 (Fr, 31. Jul 2009) $
    096     */
    097    public class GenericSQLTransaction extends SQLTransaction {
    098
    099        private static final ILogger LOG = LoggerFactory.getLogger( GenericSQLTransaction.class );
    100
    101        private String indexName;
    102
    103        /**
    104         * saves versioninfos about the different quadtrees.
    105         */
    106        public static HashMap<String, String> quadTreeVersionInfo = new HashMap<String, String>();
    107
    108        /**
    109         *
    110         * @param ds
    111         * @param aliasGenerator
    112         * @param conn
    113         * @throws DatastoreException
    114         */
    115        GenericSQLTransaction( AbstractSQLDatastore ds, TableAliasGenerator aliasGenerator, Connection conn )
    116                                throws DatastoreException {
    117            super( ds, aliasGenerator, conn );
    118        }
    119
    120        @Override
    121        public int performDelete( MappedFeatureType mappedFeatureType, Filter filter, String lockId )
    122                                                                                                     throws DatastoreException {
    123            assert !mappedFeatureType.isAbstract();
    124
    125            if ( !mappedFeatureType.isDeletable() ) {
    126                String msg = Messages.getMessage( "DATASTORE_FT_NOT_DELETABLE", mappedFeatureType.getName() );
    127                throw new DatastoreException( msg );
    128            }
    129            // QualifiedName qName = mappedFeatureType.getName();
    130            // if ( qName == null ) {
    131            // LOG.logDebug( "The mappedfeauterType's qname is null, this cannot be!" );
    132            // }
    133            // Query q = Query.create( mappedFeatureType.getName(), filter );
    134            // if ( q == null ) {
    135            // LOG.logDebug( "The query created from the qname and filter is null, this cannot be!" );
    136            // }
    137            // FeatureCollection fc = null;
    138            // try {
    139            // fc = getDatastore().performQuery( q, new MappedFeatureType[] { mappedFeatureType } );
    140            // } catch ( UnknownCRSException e ) {
    141            // throw new DatastoreException( e );
    142            // }
    143
    144            List<FeatureId> featureList = determineAffectedAndModifiableFIDs( mappedFeatureType, filter, lockId );
    145
    146            SQLDatastoreConfiguration config = (SQLDatastoreConfiguration) getDatastore().getConfiguration();
    147            JDBCConnection jdbc = config.getJDBCConnection();
    148            String table = mappedFeatureType.getTable();
    149            String version = getQTVersion( table, jdbc );
    150            LOG.logDebug( "Found quadtree version: " + version );
    151            try {
    152                for ( FeatureId fID : featureList ) {
    153                    int fk_index = loadIndexMetadata( jdbc, table );
    154                    Object rawId = FeatureId.removeFIDPrefix( fID.getAsString(), mappedFeatureType.getGMLId() );
    155                    if ( rawId instanceof Integer ) {
    156                        DBQuadtree<Integer> qt = null;
    157                        try {
    158                            qt = new DBQuadtree<Integer>( fk_index, indexName, jdbc, version );
    159                            qt.deleteItem( (Integer) rawId );
    160                        } finally {
    161                            if ( qt != null ) {
    162                                qt.releaseConnection();
    163                            }
    164                        }
    165                    } else if ( rawId instanceof String ) {
    166                        DBQuadtree<String> qt = null;
    167                        try {
    168                            qt = new DBQuadtree<String>( fk_index, indexName, jdbc, version );
    169                            qt.deleteItem( (String) rawId );
    170                        } finally {
    171                            if ( qt != null ) {
    172                                qt.releaseConnection();
    173                            }
    174                        }
    175                    }
    176                }
    177            } catch ( IndexException e ) {
    178                LOG.logError( e.getMessage(), e );
    179                throw new DatastoreException( e.getMessage(), e );
    180            }
    181
    182            super.performDelete( mappedFeatureType, filter, lockId );
    183
    184            return featureList.size();
    185        }
    186
    187        @Override
    188        public List<FeatureId> performInsert( List<Feature> features )
    189                                                                      throws DatastoreException {
    190            List<FeatureId> fids = super.performInsert( features );
    191
    192            // update index
    193            try {
    194                SQLDatastoreConfiguration config = (SQLDatastoreConfiguration) getDatastore().getConfiguration();
    195                JDBCConnection jdbc = config.getJDBCConnection();
    196                for ( int i = 0; i < features.size(); i++ ) {
    197                    Envelope env = features.get( i ).getBoundedBy();
    198                    if ( env != null ) {
    199                        MappedFeatureType mft = datastore.getFeatureType( features.get( i ).getFeatureType().getName() );
    200
    201                        String table = mft.getTable();
    202                        String version = getQTVersion( table, jdbc );
    203                        int fk_index = loadIndexMetadata( jdbc, table );
    204                        Object rawId = FeatureId.removeFIDPrefix( fids.get( i ).getAsString(), mft.getGMLId() );
    205                        if ( rawId instanceof String ) {
    206                            DBQuadtree<String> qt = null;
    207                            try {
    208                                qt = new DBQuadtree<String>( fk_index, indexName, jdbc, version );
    209                                qt.insert( (String) rawId, env );
    210                            } finally {
    211                                if ( qt != null ) {
    212                                    qt.releaseConnection();
    213                                }
    214                            }
    215                        } else if ( rawId instanceof Integer ) {
    216                            DBQuadtree<Integer> qt = null;
    217                            try {
    218                                qt = new DBQuadtree<Integer>( fk_index, indexName, jdbc, version );
    219                                qt.insert( (Integer) rawId, env );
    220                            } finally {
    221                                if ( qt != null ) {
    222                                    qt.releaseConnection();
    223                                }
    224                            }
    225
    226                        }
    227                    }
    228                }
    229            } catch ( IndexException e ) {
    230                LOG.logError( e.getMessage(), e );
    231                throw new DatastoreException( e.getMessage(), e );
    232            } catch ( GeometryException e ) {
    233                LOG.logError( e.getMessage(), e );
    234                throw new DatastoreException( e.getMessage(), e );
    235            }
    236            return fids;
    237        }
    238
    239        /**
    240         * @param table
    241         *            to open a quadtree for.
    242         * @return the version of the quadtree used.
    243         */
    244        private String getQTVersion( String table, JDBCConnection jdbc ) {
    245            String version = "1.0.0";
    246            if ( quadTreeVersionInfo.containsKey( table ) && quadTreeVersionInfo.get( table ) != null ) {
    247                LOG.logDebug( "Retrieved the quatdree version info for table: " + table + " from cache." );
    248                version = quadTreeVersionInfo.get( table );
    249            } else {
    250                Connection con = null;
    251                DBConnectionPool pool = null;
    252                Statement stmt = null;
    253                ResultSet rs = null;
    254                pool = DBConnectionPool.getInstance();
    255                StringBuilder sb = new StringBuilder( 400 );
    256                sb.append( "SELECT fk_indextree FROM tab_deegree_idx WHERE " );
    257                sb.append( "column_name = 'geometry' AND " );
    258                sb.append( "table_name = '" ).append( table.toLowerCase() ).append( "'" );
    259
    260                LOG.logDebug( "Get Index Metadata sql statement:\n", sb );
    261                try {
    262                    con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    263                    stmt = con.createStatement();
    264                    rs = stmt.executeQuery( sb.toString() );
    265                    String tableID = null;
    266                    if ( rs.next() ) {
    267                        tableID = rs.getString( 1 );
    268                    }
    269                    if ( tableID != null ) {
    270                        sb = new StringBuilder( 400 );
    271                        sb.append( "SELECT * FROM tab_quadtree WHERE " );
    272                        sb.append( "fk_root = '" ).append( tableID.trim() ).append( "'" );
    273                        if ( rs != null ) {
    274                            rs.close();
    275                        }
    276                        if ( stmt != null ) {
    277                            stmt.close();
    278                        }
    279                        stmt = con.createStatement();
    280                        rs = stmt.executeQuery( sb.toString() );
    281                        if ( rs.next() ) {
    282                            boolean hasVersion = false;
    283                            ResultSetMetaData md = rs.getMetaData();
    284                            int numberOfColumns = md.getColumnCount();
    285                            LOG.logDebug ("Column count: " + numberOfColumns);
    286                            for ( int i = 1; i <= numberOfColumns && !hasVersion; i++ ) {
    287                                String tmp = md.getColumnName( i );
    288                                LOG.logDebug( "Found columnname: " + tmp );
    289                                if ( tmp != null ) {
    290                                    if ( "version".equalsIgnoreCase( tmp.trim() ) ) {
    291                                        hasVersion = true;
    292                                        version = rs.getString( i );
    293                                        LOG.logDebug( "Found a version column, setting version to: " + rs.getString( i ) );
    294                                    }
    295                                }
    296                            }
    297                            if ( !hasVersion ) {
    298                                try {
    299                                    LOG.logInfo( "Found no Version Column in the TAB_QUADTREE table, assuming version 1.0.0, and adding the version column." );
    300                                    if ( rs != null ) {
    301                                        rs.close();
    302                                    }
    303                                    if ( stmt != null ) {
    304                                        stmt.close();
    305                                    }
    306                                    stmt = con.createStatement();
    307                                    rs = stmt.executeQuery( "ALTER TABLE TAB_QUADTREE ADD version VARCHAR(15)" );
    308                                    rs.close();
    309                                    stmt.close();
    310                                } catch ( SQLException e ) {
    311                                    if ( rs != null ) {
    312                                        rs.close();
    313                                    }
    314                                    if ( stmt != null ) {
    315                                        stmt.close();
    316                                    }
    317                                    LOG.logError( "An error occurred while trying to insert a new 'version' column in the database: " + e.getMessage(),
    318                                                  e );
    319                                }
    320                            }
    321                        }
    322                    } else {
    323                        LOG.logError( "Could not find the foreign key (fk_root) of the table: '" + table
    324                                      + "'. Is your database set up correct?" );
    325                    }
    326                } catch ( SQLException e ) {
    327                    LOG.logError( "An error occurred while determening version of quadtree, therefore setting version to '1.0.0'. Errormessage: " + e.getMessage(),
    328                                  e );
    329                } catch ( DBPoolException e ) {
    330                    LOG.logError( "An error occurred while acquiring connection to the database to determine version of quadtree, therefore setting version to '1.0.0'. Errormessage: " + e.getMessage(),
    331                                  e );
    332                } finally {
    333                    quadTreeVersionInfo.put( table, version );
    334                    try {
    335                        if ( rs != null ) {
    336                            rs.close();
    337                        }
    338                        if ( stmt != null ) {
    339                            stmt.close();
    340                        }
    341                        pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    342                    } catch ( SQLException e ) {
    343                        LOG.logError( "Could not close ResultSet or Statement because: " + e.getMessage() );
    344                    } catch ( DBPoolException e ) {
    345                        LOG.logError( "Could not release connection because: " + e.getMessage() );
    346                    }
    347                }
    348            }
    349
    350            // } catch ( SQLException e ) {
    351            // String msg = e.getMessage();
    352            // if( msg != null && msg.contains( " )
    353            // }
    354
    355            return version;
    356        }
    357
    358        @Override
    359        public int performUpdate( MappedFeatureType mappedFeatureType, Feature replacementFeature, Filter filter,
    360                                  String lockId )
    361                                                 throws DatastoreException {
    362            int cnt = super.performUpdate( mappedFeatureType, replacementFeature, filter, lockId );
    363
    364            // update index
    365
    366            return cnt;
    367        }
    368
    369        @Override
    370        public int performUpdate( MappedFeatureType mappedFeatureType, Map<PropertyPath, FeatureProperty> replacementProps,
    371                                  Filter filter, String lockId )
    372                                                                throws DatastoreException {
    373
    374            int cnt = super.performUpdate( mappedFeatureType, replacementProps, filter, lockId );
    375
    376            // update index
    377
    378            return cnt;
    379        }
    380
    381        /**
    382         * loads the metadata of an Index from the TAB_DEEGREE_IDX table
    383         *
    384         * @param jdbc
    385         *            database connection information
    386         * @param table
    387         *            name of the table containing a featuretypes data
    388         *
    389         * @return FK to the index
    390         * @throws IndexException
    391         */
    392        private int loadIndexMetadata( JDBCConnection jdbc, String table )
    393                                                                          throws IndexException {
    394            int fk_indexTree = -1;
    395            Connection con = null;
    396            DBConnectionPool pool = null;
    397            Statement stmt = null;
    398            ResultSet rs = null;
    399            try {
    400                pool = DBConnectionPool.getInstance();
    401                con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    402
    403                StringBuilder sb = new StringBuilder( 400 );
    404                sb.append( "Select INDEX_NAME, FK_INDEXTREE from TAB_DEEGREE_IDX where " );
    405                sb.append( "column_name = 'geometry' AND " );
    406                sb.append( "table_name = '" ).append( table.toLowerCase() ).append( "'" );
    407
    408                LOG.logDebug( "Get Index Metadata sql statement:\n", sb );
    409
    410                stmt = con.createStatement();
    411                rs = stmt.executeQuery( sb.toString() );
    412
    413                if ( rs.next() ) {
    414                    indexName = rs.getString( "INDEX_NAME" );
    415                    fk_indexTree = rs.getInt( "FK_INDEXTREE" );
    416                } else {
    417                    throw new IndexException( "could not read index metadata" );
    418                }
    419            } catch ( DBPoolException e ) {
    420                throw new IndexException( "could not load quadtree definition from database", e );
    421            } catch ( SQLException e ) {
    422                throw new IndexException( "could not load quadtree definition from database", e );
    423            } finally {
    424                try {
    425                    if ( rs != null ) {
    426                        rs.close();
    427                    }
    428                    if ( stmt != null ) {
    429                        stmt.close();
    430                    }
    431                    pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    432                } catch ( SQLException e ) {
    433                    LOG.logError( "Could not close Set or Statement because: " + e.getMessage() );
    434                } catch ( DBPoolException e ) {
    435                    LOG.logError( "Could not reslease connection because: " + e.getMessage() );
    436                }
    437            }
    438            return fk_indexTree;
    439        }
    440    }