001    //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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    }