001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/tools/datastore/PostGISDDLGenerator.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2001-2006 by:
006 EXSE, Department of Geography, University of Bonn
007 http://www.giub.uni-bonn.de/deegree/
008 lat/lon GmbH
009 http://www.lat-lon.de
010
011 This library is free software; you can redistribute it and/or
012 modify it under the terms of the GNU Lesser General Public
013 License as published by the Free Software Foundation; either
014 version 2.1 of the License, or (at your option) any later version.
015
016 This library is distributed in the hope that it will be useful,
017 but WITHOUT ANY WARRANTY; without even the implied warranty of
018 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
019 Lesser General Public License for more details.
020
021 You should have received a copy of the GNU Lesser General Public
022 License along with this library; if not, write to the Free Software
023 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024
025 Contact:
026
027 Andreas Poth
028 lat/lon GmbH
029 Aennchenstraße 19
030 53177 Bonn
031 Germany
032 E-Mail: poth@lat-lon.de
033
034 Prof. Dr. Klaus Greve
035 Department of Geography
036 University of Bonn
037 Meckenheimer Allee 166
038 53115 Bonn
039 Germany
040 E-Mail: greve@giub.uni-bonn.de
041
042 ---------------------------------------------------------------------------*/
043 package org.deegree.tools.datastore;
044
045 import java.io.IOException;
046 import java.net.MalformedURLException;
047 import java.net.URL;
048 import java.util.ArrayList;
049 import java.util.Collection;
050 import java.util.Iterator;
051
052 import org.deegree.datatypes.Types;
053 import org.deegree.datatypes.UnknownTypeException;
054 import org.deegree.framework.xml.XMLParsingException;
055 import org.deegree.framework.xml.schema.XMLSchemaException;
056 import org.deegree.model.crs.UnknownCRSException;
057 import org.xml.sax.SAXException;
058
059 /**
060 * Generator for PostGIS DDL (CREATE) operations to create PostGIS database schemas from annotated GML schema files.
061 *
062 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
063 * @author last edited by: $Author: mschneider $
064 *
065 * @version $Revision: 7766 $, $Date: 2007-07-16 14:18:10 +0200 (Mo, 16 Jul 2007) $
066 */
067 public class PostGISDDLGenerator extends DDLGenerator {
068
069 /**
070 * Generates a new instance of <code>PostGISDDLGenerator</code>, ready to generate DDL for the given schema.
071 *
072 * @param schemaURL
073 * @throws MalformedURLException
074 * @throws IOException
075 * @throws SAXException
076 * @throws XMLParsingException
077 * @throws XMLSchemaException
078 * @throws UnknownCRSException
079 */
080 public PostGISDDLGenerator( URL schemaURL ) throws MalformedURLException, IOException, SAXException,
081 XMLParsingException, XMLSchemaException, UnknownCRSException {
082 super( schemaURL );
083 }
084
085 /**
086 * Generates the DDL statements necessary for the creation of the given table definition. This is the PostGIS
087 * specific implementation.
088 *
089 * @param table
090 * @return the DDL statements necessary for the creation of the given table definition
091 */
092 @Override
093 protected StringBuffer generateCreateStatements( TableDefinition table ) {
094 Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
095 StringBuffer sb = new StringBuffer( "CREATE TABLE " );
096 sb.append( table.getName() );
097 sb.append( '(' );
098 ColumnDefinition[] columns = table.getColumns();
099 boolean needComma = false;
100 for ( int i = 0; i < columns.length; i++ ) {
101 if ( !columns[i].isGeometry() ) {
102 if ( needComma ) {
103 sb.append( ',' );
104 } else {
105 needComma = true;
106 }
107 sb.append( "\n " );
108 sb.append( columns[i].getName() );
109 sb.append( ' ' );
110 String typeName;
111 try {
112 typeName = Types.getTypeNameForSQLTypeCode( columns[i].getType() );
113 if ( typeName.equals( "DOUBLE" ) ) {
114 typeName = "DOUBLE PRECISION";
115 }
116 } catch ( UnknownTypeException e ) {
117 typeName = "" + columns[i].getType();
118 }
119 sb.append( typeName );
120 if ( !columns[i].isNullable() ) {
121 sb.append( " NOT NULL" );
122 }
123 } else {
124 geometryColumns.add( columns[i] );
125 }
126 }
127 ColumnDefinition[] pkColumns = table.getPKColumns();
128 if ( pkColumns.length > 0 ) {
129 sb.append( ",\n PRIMARY KEY (" );
130 for ( int i = 0; i < pkColumns.length; i++ ) {
131 sb.append( pkColumns[i].getName() );
132 if ( i != pkColumns.length - 1 ) {
133 sb.append( ',' );
134 }
135 }
136 sb.append( ')' );
137 }
138 sb.append( "\n);\n" );
139
140 // build addGeometryColumn and create index statements
141 Iterator iter = geometryColumns.iterator();
142 while ( iter.hasNext() ) {
143 ColumnDefinition column = (ColumnDefinition) iter.next();
144 sb.append( "SELECT AddGeometryColumn ('', '" );
145 sb.append( table.getName().toLowerCase() );
146 sb.append( "', '" );
147 sb.append( column.getName().toLowerCase() );
148 sb.append( "', " );
149 sb.append( column.getSRS() );
150 sb.append( ", '" );
151 sb.append( "GEOMETRY" );
152 sb.append( "', '2');\n" );
153
154 sb.append( "CREATE INDEX " );
155 sb.append( table.getName() );
156 sb.append( "_SPATIAL_IDX ON " );
157 sb.append( table.getName() );
158 sb.append( " USING GIST ( " );
159 sb.append( column.getName() );
160 sb.append( " GIST_GEOMETRY_OPS );\n" );
161 }
162 return sb;
163 }
164
165 /**
166 * Generates the DDL statements necessary for the removal of the given table definition. This is the PostGIS
167 * specific implementation.
168 *
169 * @param table
170 * @return the DDL statements necessary for the removal of the given table definition
171 */
172 @Override
173 protected StringBuffer generateDropStatements( TableDefinition table ) {
174 StringBuffer sb = new StringBuffer();
175 ColumnDefinition[] columns = table.getColumns();
176 for ( int i = 0; i < columns.length; i++ ) {
177 if ( columns[i].isGeometry() ) {
178 sb.append( "DROP INDEX " );
179 sb.append( table.getName() );
180 sb.append( "_SPATIAL_IDX;\n" );
181
182 sb.append( "SELECT DropGeometryColumn ('','" );
183 sb.append( table.getName().toLowerCase() );
184 sb.append( "', '" );
185 sb.append( columns[i].getName().toLowerCase() );
186 sb.append( "');\n" );
187 }
188 }
189 sb.append( "DROP TABLE " );
190 sb.append( table.getName() );
191 sb.append( " CASCADE;\n" );
192 return sb;
193 }
194 }