    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/tools/raster/ImportIndexIntoDB.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.tools.raster;
    038
    039    import static java.io.File.separator;
    040
    041    import java.io.File;
    042    import java.io.FileOutputStream;
    043    import java.io.IOException;
    044    import java.io.PrintStream;
    045    import java.net.URI;
    046    import java.net.URISyntaxException;
    047    import java.net.URL;
    048    import java.security.InvalidParameterException;
    049    import java.sql.Connection;
    050    import java.sql.PreparedStatement;
    051    import java.sql.SQLException;
    052    import java.sql.Statement;
    053    import java.util.Arrays;
    054    import java.util.HashMap;
    055    import java.util.Iterator;
    056    import java.util.List;
    057    import java.util.Map;
    058    import java.util.Properties;
    059
    060    import javax.xml.transform.TransformerException;
    061
    062    import org.deegree.datatypes.QualifiedName;
    063    import org.deegree.framework.log.ILogger;
    064    import org.deegree.framework.log.LoggerFactory;
    065    import org.deegree.framework.util.ConvenienceFileFilter;
    066    import org.deegree.framework.util.FileUtils;
    067    import org.deegree.framework.util.StringTools;
    068    import org.deegree.framework.xml.XMLFragment;
    069    import org.deegree.framework.xml.XSLTDocument;
    070    import org.deegree.io.DBConnectionPool;
    071    import org.deegree.io.DBPoolException;
    072    import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
    073    import org.deegree.io.dbaseapi.DBaseException;
    074    import org.deegree.io.shpapi.shape_new.ShapeFile;
    075    import org.deegree.io.shpapi.shape_new.ShapeFileReader;
    076    import org.deegree.model.feature.Feature;
    077    import org.deegree.model.feature.FeatureCollection;
    078    import org.deegree.model.spatialschema.Geometry;
    079    import org.deegree.model.spatialschema.GeometryException;
    080    import org.xml.sax.SAXException;
    081
    082    /**
    083     *
    084     *
    085     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    086     * @author last edited by: $Author: mschneider $
    087     *
    088     * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
    089     */
    090    public class ImportIndexIntoDB {
    091
    092        private static ILogger LOG = LoggerFactory.getLogger( ImportIndexIntoDB.class );
    093
    094        private static final String createPG = "create_wcs_table_template_postgis.sql";
    095
    096        private static final String indexPG = "create_wcs_table_index_template_postgis.sql";
    097
    098        private static final String createOrcl = "create_wcs_table_template_oracle.sql";
    099
    100        private static final String indexOrcl = "create_wcs_index_table_template_oracle.sql";
    101
    102        private static URI app = null;
    103
    104        static {
    105            try {
    106                app = new URI( "http://www.deegree.org/app" );
    107            } catch ( URISyntaxException e ) {
    108                e.printStackTrace();
    109            }
    110        }
    111
    112        private String table;
    113
    114        private String user;
    115
    116        private String pw;
    117
    118        private String driver;
    119
    120        private String url;
    121
    122        private String rootDir;
    123
    124        private File configurationFile;
    125
    126        private boolean append = false;
    127
    128        private boolean switchDir = false;
    129
    130        /**
    131         * @param driver
    132         * @param url
    133         * @param user
    134         * @param pw
    135         * @param table
    136         * @param rootDir
    137         */
    138        public ImportIndexIntoDB( String driver, String url, String user, String pw, String table, String rootDir ) {
    139            this.driver = driver;
    140            this.url = url;
    141            this.user = user;
    142            this.pw = pw;
    143            this.table = table;
    144            this.rootDir = rootDir;
    145            ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "XML" );
    146            File dir = new File( this.rootDir );
    147            configurationFile = dir.listFiles( ff )[0];
    148        }
    149
    150        /**
    151         * @param driver
    152         * @param url
    153         * @param user
    154         * @param pw
    155         * @param table
    156         * @param rootDir
    157         * @param append
    158         * @param switchDir
    159         */
    160        public ImportIndexIntoDB( String driver, String url, String user, String pw, String table, String rootDir,
    161                                  boolean append, boolean switchDir ) {
    162            this.driver = driver;
    163            this.url = url;
    164            this.user = user;
    165            this.pw = pw;
    166            this.table = table;
    167            this.rootDir = rootDir;
    168            ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "XML" );
    169            File dir = new File( this.rootDir );
    170            configurationFile = dir.listFiles( ff )[0];
    171            this.append = append;
    172            this.switchDir = switchDir;
    173        }
    174
    175        /**
    176         * main method to perform indexing of raster tile via a spatial DB
    177         *
    178         * @throws IOException
    179         * @throws DBPoolException
    180         * @throws SQLException
    181         * @throws DBaseException
    182         * @throws GeometryException
    183         * @throws SAXException
    184         * @throws TransformerException
    185         */
    186        public void perform()
    187                                throws IOException, DBPoolException, SQLException, DBaseException, GeometryException,
    188                                SAXException, TransformerException {
    189            if ( !append ) {
    190                createTables();
    191            }
    192            fillIndexTable();
    193            // index will be created after data has been added to increase performance
    194            if ( !append ) {
    195                createIndex();
    196            }
    197            adaptConfiguration();
    198        }
    199
    200        /**
    201         * fill the pyramid table with informations about the assoziation between levels and scales
    202         *
    203         * @param map
    204         * @throws DBPoolException
    205         * @throws SQLException
    206         */
    207        private void fillPyramidTable( Map<File, PyramidHelper> map )
    208                                throws DBPoolException, SQLException {
    209            DBConnectionPool pool = DBConnectionPool.getInstance();
    210            Connection con = pool.acquireConnection( driver, url, user, pw );
    211            Iterator<PyramidHelper> iterator = map.values().iterator();
    212            while ( iterator.hasNext() ) {
    213                PyramidHelper helper = iterator.next();
    214                String sql = StringTools.concat( 200, "INSERT INTO ", table,
    215                                                 "_pyr (level,minscale,maxscale) values (?,?,?)" );
    216                PreparedStatement stmt = con.prepareStatement( sql );
    217                stmt.setInt( 1, helper.level );
    218                stmt.setFloat( 2, helper.minscale );
    219                stmt.setFloat( 3, helper.maxscale );
    220                stmt.execute();
    221                stmt.close();
    222            }
    223            LOG.logInfo( "pyramid table filled!" );
    224
    225        }
    226
    227        /**
    228         * creates DB-indexes for pyramid and tile-index tables
    229         *
    230         * @throws IOException
    231         * @throws DBPoolException
    232         * @throws SQLException
    233         */
    234        private void createIndex()
    235                                throws IOException, DBPoolException, SQLException {
    236            String template = null;
    237            if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
    238                template = indexOrcl;
    239            } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
    240                template = indexPG;
    241            }
    242
    243            StringBuffer sb = FileUtils.readTextFile( ImportIndexIntoDB.class.getResource( template ) );
    244            String tmp = StringTools.replace( sb.toString(), "$TABLE$", table.toLowerCase(), true );
    245            String[] sql = parseSQLStatements( tmp );
    246
    247            DBConnectionPool pool = DBConnectionPool.getInstance();
    248            Connection con = pool.acquireConnection( driver, url, user, pw );
    249            Statement stmt = con.createStatement();
    250            for ( int i = 0; i < sql.length; i++ ) {
    251                stmt.execute( sql[i] );
    252            }
    253            stmt.close();
    254            pool.releaseConnection( con, driver, url, user, pw );
    255            LOG.logInfo( "indexes created!" );
    256
    257        }
    258
    259        /**
    260         * fills the index table with informations about raster tiles, their spatial extent, assigened
    261         * level and assigned file
    262         *
    263         * @throws IOException
    264         * @throws DBaseException
    265         * @throws DBPoolException
    266         * @throws SQLException
    267         * @throws GeometryException
    268         * @throws SAXException
    269         */
    270        private void fillIndexTable()
    271                                throws IOException, DBaseException, DBPoolException, SQLException, GeometryException,
    272                                SAXException {
    273            DBConnectionPool pool = DBConnectionPool.getInstance();
    274            Connection con = pool.acquireConnection( driver, url, user, pw );
    275
    276            ConvenienceFileFilter ff = new ConvenienceFileFilter( false, "SHP" );
    277            File dir = new File( rootDir );
    278            File[] files = dir.listFiles( ff );
    279            Map<File, PyramidHelper> map = createLevelMap( files );
    280            if ( !append ) {
    281                fillPyramidTable( map );
    282            }
    283
    284            XMLFragment xml = new XMLFragment( configurationFile.toURI().toURL() );
    285
    286            for ( int i = 0; i < files.length; i++ ) {
    287                LOG.logInfo( "import: ", files[i].getAbsolutePath() );
    288                String tmp = files[i].getAbsolutePath();
    289                tmp = tmp.substring( 0, tmp.length() - 4 );
    290                ShapeFileReader sfr = new ShapeFileReader( tmp );
    291                ShapeFile sf = sfr.read();
    292                FeatureCollection fc = sf.getFeatureCollection();
    293                for ( int j = 0; j < fc.size(); j++ ) {
    294                    String sql = StringTools.concat( 200, "INSERT INTO ", table,
    295                                                     " (level, dir, file, bbox) values(?,?,?,?)" );
    296                    PreparedStatement stmt = con.prepareStatement( sql );
    297                    Object[] values = createInsert( fc.getFeature( j ) );
    298                    stmt.setInt( 1, map.get( files[i] ).level );
    299                    if ( switchDir ) {
    300                        String s = xml.resolve( (String) values[1] ).toExternalForm();
    301                        StringBuffer sb = new StringBuffer();
    302                        int k = 0;
    303                        int l = s.length() - 1;
    304                        while ( k != 2 ) {
    305                            sb.insert( 0, s.charAt( l ) );
    306                            if ( s.charAt( l ) == '/' ) {
    307                                k++;
    308                            }
    309                            l--;
    310                        }
    311                        stmt.setString( 2, sb.toString() );
    312                    } else {
    313                        stmt.setString( 2, (String) values[1] );
    314                    }
    315                    stmt.setString( 3, (String) values[2] );
    316                    stmt.setObject( 4, values[3] );
    317                    stmt.execute();
    318                    stmt.close();
    319                }
    320
    321            }
    322
    323            pool.releaseConnection( con, driver, url, user, pw );
    324            LOG.logInfo( "index table filled!" );
    325
    326        }
    327
    328        /**
    329         * creates pyramid level map; assigning each file to a level starting at 0
    330         *
    331         * @param files
    332         * @return the created pyramid level map
    333         */
    334        private Map<File, PyramidHelper> createLevelMap( File[] files ) {
    335            PyramidHelper[] phelper = new PyramidHelper[files.length];
    336            for ( int i = 0; i < files.length; i++ ) {
    337                String tmp = files[i].getName().substring( 2, files[i].getName().length() - 4 );
    338                float scale = Float.parseFloat( tmp );
    339                PyramidHelper helper = new PyramidHelper();
    340                helper.file = files[i];
    341                helper.scale = scale;
    342                phelper[i] = helper;
    343            }
    344            Arrays.sort( phelper );
    345            Map<File, PyramidHelper> map = new HashMap<File, PyramidHelper>();
    346            for ( int i = 0; i < phelper.length; i++ ) {
    347                phelper[i].level = i;
    348                if ( i == 0 ) {
    349                    phelper[i].minscale = 0;
    350                } else {
    351                    phelper[i].minscale = phelper[i - 1].scale;
    352                }
    353                if ( i == phelper.length - 1 ) {
    354                    phelper[i].maxscale = 99999999999F;
    355                } else {
    356                    phelper[i].maxscale = phelper[i].scale;
    357                }
    358                map.put( phelper[i].file, phelper[i] );
    359            }
    360            LOG.logInfo( "pyramid level assoziation:", map );
    361            return map;
    362        }
    363
    364        /**
    365         *
    366         * @param feature
    367         * @return values to insert for a raster tile
    368         * @throws GeometryException
    369         */
    370        private Object[] createInsert( Feature feature )
    371                                throws GeometryException {
    372            Object[] values = new Object[4];
    373            values[1] = feature.getProperties( new QualifiedName( "FOLDER", app ) )[0].getValue();
    374            values[2] = feature.getProperties( new QualifiedName( "FILENAME", app ) )[0].getValue();
    375            Geometry geom = feature.getDefaultGeometryPropertyValue();
    376            values[3] = PGgeometryAdapter.export( geom, -1 );
    377            return values;
    378        }
    379
    380        /**
    381         * creates data and pyramid table
    382         *
    383         * @throws IOException
    384         * @throws DBPoolException
    385         * @throws SQLException
    386         */
    387        private void createTables()
    388                                throws IOException, DBPoolException, SQLException {
    389            String template = null;
    390            if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
    391                template = createOrcl;
    392            } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
    393                template = createPG;
    394            }
    395            StringBuffer sb = FileUtils.readTextFile( ImportIndexIntoDB.class.getResource( template ) );
    396            String tmp = StringTools.replace( sb.toString(), "$TABLE$", table.toLowerCase(), true );
    397            String[] sql = parseSQLStatements( tmp );
    398
    399            DBConnectionPool pool = DBConnectionPool.getInstance();
    400            Connection con = pool.acquireConnection( driver, url, user, pw );
    401            Statement stmt = con.createStatement();
    402            for ( int i = 0; i < sql.length; i++ ) {
    403                try {
    404                    stmt.execute( sql[i] );
    405                } catch ( Exception e ) {
    406                    LOG.logWarning( e.getMessage() );
    407                }
    408            }
    409            stmt.close();
    410            pool.releaseConnection( con, driver, url, user, pw );
    411            LOG.logInfo( "tables created!" );
    412
    413        }
    414
    415        /**
    416         *
    417         * @param tmp
    418         * @return SQL statements parsed from a file/string
    419         */
    420        private String[] parseSQLStatements( String tmp ) {
    421            List<String> list = StringTools.toList( tmp, ";", false );
    422            String[] sql = new String[list.size()];
    423            for ( int i = 0; i < sql.length; i++ ) {
    424                sql[i] = list.get( i ).trim();
    425            }
    426            return sql;
    427        }
    428
    429        /**
    430         * adapts the wcs configuration for a coverage to use the created db-based index instead of a
    431         * shapefile base one.
    432         *
    433         * @throws IOException
    434         * @throws SAXException
    435         * @throws TransformerException
    436         */
    437        private void adaptConfiguration()
    438                                throws IOException, SAXException, TransformerException {
    439            URL url = ImportIndexIntoDB.class.getResource( "wcsconfiguration.xsl" );
    440            XSLTDocument xslt = new XSLTDocument( url );
    441            XMLFragment xml = new XMLFragment( configurationFile.toURI().toURL() );
    442            xml = xslt.transform( xml );
    443            Map<String, String> params = new HashMap<String, String>();
    444            params.put( "TABLE", table );
    445            params.put( "USER", user );
    446            params.put( "PASSWORD", pw );
    447            params.put( "DRIVER", driver );
    448            params.put( "URL", this.url );
    449            xml = xslt.transform( xml, XMLFragment.DEFAULT_URL, null, params );
    450
    451            String s = configurationFile.getAbsolutePath();
    452            if ( switchDir ) {
    453                s = configurationFile.getAbsolutePath();
    454                int kk = s.lastIndexOf( File.separator );
    455                s = s.substring( 0, kk );
    456                kk = s.lastIndexOf( File.separator );
    457                s = s.substring( 0, kk ) + File.separator + configurationFile.getName();
    458            }
    459
    460            FileOutputStream fos = new FileOutputStream( s );
    461            xml.write( fos );
    462            fos.close();
    463        }
    464
    465        /**
    466         * @param args
    467         * @throws Exception
    468         */
    469        public static void main( String[] args )
    470                                throws Exception {
    471
    472            try {
    473                Properties map = new Properties();
    474                for ( int i = 0; i < args.length; ) {
    475                    String first = args[i++];
    476                    if ( "?".equals( first ) || "-h".equals( first ) || "-help".equals( first ) ) {
    477                        printHelp();
    478                        System.exit( 0 );
    479                    }
    480                    map.put( first, args[i++] );
    481                }
    482
    483                // set up stderr/stdout redirection
    484                String redirect = map.getProperty( "-redirect" );
    485                if ( redirect != null && redirect.equals( "true" ) ) {
    486                    String rootDir = map.getProperty( "-rootDir" );
    487                    File f = new File( rootDir + separator + "dbindexer.log" );
    488                    PrintStream out = new PrintStream( new FileOutputStream( f ) );
    489                    System.setOut( out );
    490                    System.setErr( out );
    491                }
    492
    493                try {
    494                    validate( map );
    495                } catch ( InvalidParameterException ipe ) {
    496                    LOG.logError( ipe.getMessage() );
    497                    printHelp();
    498                    System.exit( 1 );
    499                }
    500                LOG.logDebug( "Resulting commandline arguments and their values {argument=value, ...}: " + map.toString() );
    501
    502                String table = map.getProperty( "-table" );
    503                String user = map.getProperty( "-user" );
    504                String pw = map.getProperty( "-password" );
    505                String driver = map.getProperty( "-driver" );
    506                String url = map.getProperty( "-url" );
    507                String rootDir = map.getProperty( "-rootDir" );
    508                String tmp = map.getProperty( "-append" );
    509                boolean append = "true".equals( tmp );
    510                tmp = map.getProperty( "-switchDir" );
    511                boolean switchDir = "true".equals( tmp );
    512
    513                ImportIndexIntoDB importer = new ImportIndexIntoDB( driver, url, user, pw, table, rootDir, append,
    514                                                                    switchDir );
    515                importer.perform();
    516            } catch ( Exception e ) {
    517                e.printStackTrace();
    518                System.exit( 1 );
    519            }
    520            System.out.println( "finished ...." );
    521            System.exit( 0 );
    522        }
    523
    524        /**
    525         *
    526         * @param map
    527         * @throws InvalidParameterException
    528         */
    529        private static void validate( Properties map )
    530                                throws InvalidParameterException {
    531            if ( map.getProperty( "-table" ) == null ) {
    532                throw new InvalidParameterException( "-table must be set" );
    533            }
    534            if ( map.getProperty( "-user" ) == null ) {
    535                throw new InvalidParameterException( "-user must be set" );
    536            }
    537            if ( map.getProperty( "-password" ) == null ) {
    538                throw new InvalidParameterException( "-password must be set" );
    539            }
    540            if ( map.getProperty( "-driver" ) == null ) {
    541                throw new InvalidParameterException( "-driver must be set" );
    542            }
    543            if ( map.getProperty( "-url" ) == null ) {
    544                throw new InvalidParameterException( "-url must be set" );
    545            }
    546            if ( map.getProperty( "-rootDir" ) == null ) {
    547                throw new InvalidParameterException( "-rootDir must be set" );
    548            }
    549
    550        }
    551
    552        /**
    553         *
    554         *
    555         */
    556        private static void printHelp() {
    557            // TODO Auto-generated method stub
    558            System.out.println( "no help available at the moment" );
    559        }
    560
    561        /**
    562         *
    563         *
    564         *
    565         * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    566         * @author last edited by: $Author: mschneider $
    567         *
    568         * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
    569         */
    570        class PyramidHelper implements Comparable<PyramidHelper> {
    571
    572            File file;
    573
    574            float scale;
    575
    576            float minscale;
    577
    578            float maxscale;
    579
    580            int level;
    581
    582            public int compareTo( PyramidHelper o ) {
    583                if ( o.scale < scale ) {
    584                    return 1;
    585                } else if ( o.scale > scale ) {
    586                    return -1;
    587                }
    588                return 0;
    589            }
    590
    591            @Override
    592            public String toString() {
    593                return StringTools.concat( 260, file.getName(), ' ', level, ' ', scale );
    594            }
    595
    596        }
    597
    598    }