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