001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_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 }