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