    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/tools/datastore/PostGISDDLGenerator.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    package org.deegree.tools.datastore;
    037
    038    import java.io.IOException;
    039    import java.net.MalformedURLException;
    040    import java.net.URL;
    041    import java.util.ArrayList;
    042    import java.util.Collection;
    043    import java.util.Iterator;
    044    import java.util.List;
    045
    046    import org.deegree.datatypes.Types;
    047    import org.deegree.datatypes.UnknownTypeException;
    048    import org.deegree.framework.xml.XMLParsingException;
    049    import org.deegree.framework.xml.schema.XMLSchemaException;
    050    import org.deegree.model.crs.UnknownCRSException;
    051    import org.xml.sax.SAXException;
    052
    053    /**
    054     * Generator for PostGIS DDL (CREATE / DROP) operations to create PostGIS database schemas from annotated GML schema
    055     * files.
    056     *
    057     * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
    058     * @author last edited by: $Author: mschneider $
    059     *
    060     * @version $Revision: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
    061     */
    062    public class PostGISDDLGenerator extends DDLGenerator {
    063
    064        /**
    065         * Generates a new instance of <code>PostGISDDLGenerator</code>, ready to generate DDL for the given schema.
    066         *
    067         * @param schemaURL
    068         * @throws MalformedURLException
    069         * @throws IOException
    070         * @throws SAXException
    071         * @throws XMLParsingException
    072         * @throws XMLSchemaException
    073         * @throws UnknownCRSException
    074         */
    075        public PostGISDDLGenerator( URL schemaURL ) throws MalformedURLException, IOException, SAXException,
    076                                XMLParsingException, XMLSchemaException, UnknownCRSException {
    077            super( schemaURL );
    078        }
    079
    080        @Override
    081        protected StringBuffer generateSetSchemaStmt( String dbSchema ) {
    082            StringBuffer sb = new StringBuffer( "SET search_path TO " );
    083            sb.append( dbSchema );
    084            sb.append( ",public;\n" );
    085            return sb;
    086        }
    087
    088        @Override
    089        protected StringBuffer generateCreateTableStmt( TableDefinition table ) {
    090
    091            List<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
    092            StringBuffer sb = new StringBuffer( "CREATE TABLE " );
    093            sb.append( table.getName() );
    094            sb.append( '(' );
    095            ColumnDefinition[] columns = table.getColumns();
    096            boolean needComma = false;
    097
    098            if ( table.getType() == MULTI_PROPERTY_TABLE ) {
    099                sb.append( "\n    " );
    100                sb.append( "PK SERIAL" );
    101                needComma = true;
    102            }
    103
    104            for ( int i = 0; i < columns.length; i++ ) {
    105                if ( !columns[i].isGeometry() ) {
    106                    if ( needComma ) {
    107                        sb.append( ',' );
    108                    } else {
    109                        needComma = true;
    110                    }
    111                    sb.append( "\n    " );
    112                    sb.append( columns[i].getName() );
    113                    sb.append( ' ' );
    114                    String typeName;
    115                    try {
    116                        typeName = Types.getTypeNameForSQLTypeCode( columns[i].getType() );
    117                        if ( typeName.equals( "DOUBLE" ) ) {
    118                            typeName = "DOUBLE PRECISION";
    119                        }
    120                    } catch ( UnknownTypeException e ) {
    121                        typeName = "" + columns[i].getType();
    122                    }
    123                    sb.append( typeName );
    124                    if ( !columns[i].isNullable() ) {
    125                        sb.append( " NOT NULL" );
    126                    }
    127                } else {
    128                    geometryColumns.add( columns[i] );
    129                }
    130            }
    131
    132            // add primary key information (forces index generation)
    133            ColumnDefinition[] pkColumns = table.getPKColumns();
    134            if ( pkColumns.length > 0 ) {
    135                sb.append( ",\n    PRIMARY KEY (" );
    136                for ( int i = 0; i < pkColumns.length; i++ ) {
    137                    sb.append( pkColumns[i].getName() );
    138                    if ( i != pkColumns.length - 1 ) {
    139                        sb.append( ',' );
    140                    }
    141                }
    142                sb.append( ")\n);\n" );
    143            } else {
    144                sb.append( "\n);\n" );
    145            }
    146
    147            // build addGeometryColumn() statements
    148            for ( int i = 0; i < geometryColumns.size(); i++ ) {
    149                ColumnDefinition column = geometryColumns.get( i );
    150                sb.append( "SELECT AddGeometryColumn ('', '" );
    151                sb.append( table.getName().toLowerCase() );
    152                sb.append( "', '" );
    153                sb.append( column.getName().toLowerCase() );
    154                sb.append( "', " );
    155                sb.append( column.getSRS() );
    156                sb.append( ", '" );
    157                sb.append( "GEOMETRY" );
    158                sb.append( "', '2');\n" );
    159            }
    160            return sb;
    161        }
    162
    163        @Override
    164        protected StringBuffer generateCreateIndexStmts( TableDefinition table ) {
    165            StringBuffer sb = new StringBuffer();
    166
    167            // build create statements for spatial indexes
    168            Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
    169            for ( ColumnDefinition column : table.getColumns() ) {
    170                if ( column.isGeometry() ) {
    171                    geometryColumns.add( column );
    172                }
    173            }
    174
    175            Iterator<ColumnDefinition> iter = geometryColumns.iterator();
    176            int spatialIdxCnt = 1;
    177            while ( iter.hasNext() ) {
    178                ColumnDefinition column = iter.next();
    179                sb.append( "CREATE INDEX " );
    180                sb.append( table.getName() + ( spatialIdxCnt++ ) );
    181                sb.append( "_SPATIAL_IDX ON " );
    182                sb.append( table.getName() );
    183                sb.append( " USING GIST ( " );
    184                sb.append( column.getName() );
    185                sb.append( " GIST_GEOMETRY_OPS );" );
    186                sb.append( '\n' );
    187            }
    188
    189            // build create statements for indexes on all fk columns
    190            ColumnDefinition[] columns = table.getColumns();
    191            for ( int i = 0; i < columns.length; i++ ) {
    192                if ( columns[i].isFK() ) {
    193                    sb.append( "CREATE INDEX " );
    194                    sb.append( table.getName() );
    195                    sb.append( '_' );
    196                    sb.append( columns[i].getName() );
    197                    sb.append( "_IDX ON " );
    198                    sb.append( table.getName() );
    199                    sb.append( '(' );
    200                    sb.append( columns[i].getName() );
    201                    sb.append( ");\n" );
    202                }
    203            }
    204
    205            return sb;
    206        }
    207
    208        @Override
    209        protected StringBuffer generateDropTableStmt( TableDefinition table ) {
    210            StringBuffer sb = new StringBuffer();
    211            for ( ColumnDefinition column : table.getColumns() ) {
    212                if ( column.isGeometry() ) {
    213                    sb.append( "SELECT DropGeometryColumn ('','" );
    214                    sb.append( table.getName().toLowerCase() );
    215                    sb.append( "', '" );
    216                    sb.append( column.getName().toLowerCase() );
    217                    sb.append( "');\n" );
    218                }
    219            }
    220            sb.append( super.generateDropTableStmt( table ) );
    221            return sb;
    222        }
    223    }