001 //$HeadURL: svn+ssh://aschmitz@wald.intevation.org/deegree/base/trunk/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.HashMap;
051 import java.util.Iterator;
052 import java.util.Map;
053
054 import org.deegree.datatypes.Types;
055 import org.deegree.datatypes.UnknownTypeException;
056 import org.deegree.framework.xml.XMLParsingException;
057 import org.deegree.framework.xml.schema.XMLSchemaException;
058 import org.deegree.model.crs.UnknownCRSException;
059 import org.xml.sax.SAXException;
060
061 /**
062 * Generator for Oracle DDL (CREATE / DROP) operations to create Oracle database schemas from annotated GML schema
063 * files.
064 *
065 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider</a>
066 * @author last edited by: $Author: aschmitz $
067 *
068 * @version $Revision: 8252 $, $Date: 2007-10-05 11:54:33 +0200 (Fr, 05 Okt 2007) $
069 */
070 public class OracleDDLGenerator extends DDLGenerator {
071
072 /**
073 * Generates a new instance of <code>OracleDDLGenerator</code>, ready to generate DDL for the given schema.
074 *
075 * @param schemaURL
076 * @throws MalformedURLException
077 * @throws IOException
078 * @throws SAXException
079 * @throws XMLParsingException
080 * @throws XMLSchemaException
081 * @throws UnknownCRSException
082 */
083 public OracleDDLGenerator( URL schemaURL ) throws MalformedURLException, IOException, SAXException,
084 XMLParsingException, XMLSchemaException, UnknownCRSException {
085 super( schemaURL );
086 }
087
088 @Override
089 protected StringBuffer generateDropTableStmt( TableDefinition table ) {
090 StringBuffer sb = new StringBuffer();
091 sb.append( "DROP TABLE " );
092 sb.append( table.getName() );
093 sb.append( " CASCADE CONSTRAINTS;\n" );
094
095 ColumnDefinition[] columns = table.getColumns();
096 for ( int i = 0; i < columns.length; i++ ) {
097 if ( columns[i].isGeometry() ) {
098 sb.append( "DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='" );
099 sb.append( table.getName() );
100 sb.append( "';\n" );
101 break;
102 }
103 }
104 return sb;
105 }
106
107 @Override
108 protected StringBuffer generateCreateTableStmt( TableDefinition table ) {
109
110 Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
111 StringBuffer sb = new StringBuffer( "CREATE TABLE " );
112 sb.append( table.getName() );
113 sb.append( " (" );
114 ColumnDefinition[] columns = table.getColumns();
115 boolean needComma = false;
116 for ( int i = 0; i < columns.length; i++ ) {
117 if ( !columns[i].isGeometry() ) {
118 if ( needComma ) {
119 sb.append( ',' );
120 } else {
121 needComma = true;
122 }
123 sb.append( "\n " );
124 sb.append( columns[i].getName() );
125 sb.append( ' ' );
126 String typeName;
127 try {
128 typeName = Types.getTypeNameForSQLTypeCode( columns[i].getType() );
129 if ( typeName.equals( "DOUBLE" ) ) {
130 typeName = "DOUBLE PRECISION";
131 } else if ( typeName.equals( "VARCHAR" ) ) {
132 // always prefer VARCHAR2 to VARCHAR:
133 // http://www.orafaq.com/faq/what_is_the_difference_between_varchar_varchar2_and_char_data_types
134 typeName = "VARCHAR2(2000)";
135 } else if ( typeName.equals( "BOOLEAN" ) ) {
136 typeName = "CHAR(1)";
137 }
138 } catch ( UnknownTypeException e ) {
139 typeName = "" + columns[i].getType();
140 }
141 sb.append( typeName );
142 if ( !columns[i].isNullable() ) {
143 sb.append( " NOT NULL" );
144 }
145 } else {
146 if ( needComma ) {
147 sb.append( ',' );
148 } else {
149 needComma = true;
150 }
151 sb.append( "\n " );
152 sb.append( columns[i].getName() );
153 sb.append( " MDSYS.SDO_GEOMETRY" );
154 if ( !columns[i].isNullable() ) {
155 sb.append( " NOT NULL" );
156 }
157
158 geometryColumns.add( columns[i] );
159 }
160 }
161 ColumnDefinition[] pkColumns = table.getPKColumns();
162 if ( pkColumns.length > 0 ) {
163 sb.append( ",\n PRIMARY KEY (" );
164 for ( int i = 0; i < pkColumns.length; i++ ) {
165 sb.append( pkColumns[i].getName() );
166 if ( i != pkColumns.length - 1 ) {
167 sb.append( ',' );
168 }
169 }
170 sb.append( ')' );
171 }
172 sb.append( "\n);\n" );
173
174 // add geometry columns
175 for ( ColumnDefinition column : geometryColumns ) {
176 sb.append( "INSERT INTO USER_SDO_GEOM_METADATA VALUES ('" );
177 sb.append( table.getName() );
178 sb.append( "', '" );
179 sb.append( column.getName() );
180 sb.append( "', SDO_DIM_ARRAY ( SDO_DIM_ELEMENT ( 'LONGITUDE', -180, 180, 0.00005), SDO_DIM_ELEMENT ('LATITUDE',-90,90,0.00005))," );
181 sb.append( column.getSRS() );
182 sb.append( ");\n" );
183 }
184 return sb;
185 }
186
187 @Override
188 protected StringBuffer generateCreateIndexStmts( TableDefinition table ) {
189 StringBuffer sb = new StringBuffer();
190
191 // build create statements for geometry indexes
192 Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
193 for ( ColumnDefinition column : table.getColumns() ) {
194 if ( column.isGeometry() ) {
195 geometryColumns.add( column );
196 }
197 }
198
199 Iterator<ColumnDefinition> iter = geometryColumns.iterator();
200 int spatialIdxCnt = 1;
201 while ( iter.hasNext() ) {
202 ColumnDefinition column = iter.next();
203 sb.append( "CREATE INDEX " );
204 String idxSuffix = ( spatialIdxCnt++ ) + "_SPIDX";
205 String idxName = truncate( table.getName().toUpperCase(), idxSuffix );
206 sb.append( idxName );
207 sb.append( " ON " );
208 sb.append( table.getName() );
209 sb.append( "(" );
210 sb.append( column.getName() );
211 sb.append( ") INDEXTYPE IS MDSYS.SPATIAL_INDEX;" );
212 sb.append( '\n' );
213 }
214
215 // build table type specific create index statements
216 switch ( table.getType() ) {
217 case JOIN_TABLE: {
218 // create an index on every column
219 ColumnDefinition[] columns = table.getColumns();
220 for ( int i = 0; i < columns.length; i++ ) {
221 sb.append( "CREATE INDEX " );
222 String idxSuffix = '_' + columns[i].getName() + "_IDX";
223 String idxName = truncate( table.getName().toUpperCase(), idxSuffix );
224 sb.append( idxName );
225 sb.append( " ON " );
226 sb.append( table.getName() );
227 sb.append( '(' );
228 sb.append( columns[i].getName() );
229 sb.append( ");" );
230 sb.append( '\n' );
231 }
232 break;
233 }
234 default: {
235 break;
236 }
237 }
238 return sb;
239 }
240
241 @Override
242 protected StringBuffer generateDropIndexStmts( TableDefinition table ) {
243 StringBuffer sb = new StringBuffer();
244
245 // build drop statements for geometry indexes
246 Collection<ColumnDefinition> geometryColumns = new ArrayList<ColumnDefinition>();
247 for ( ColumnDefinition column : table.getColumns() ) {
248 if ( column.isGeometry() ) {
249 geometryColumns.add( column );
250 }
251 }
252
253 Iterator<ColumnDefinition> iter = geometryColumns.iterator();
254 int spatialIdxCnt = 1;
255 while ( iter.hasNext() ) {
256 iter.next();
257 sb.append( "DROP INDEX " );
258 String idxSuffix = ( spatialIdxCnt++ ) + "_SPIDX";
259 String idxName = truncate( table.getName().toUpperCase(), idxSuffix );
260 sb.append( idxName );
261 sb.append( ";\n" );
262 }
263
264 // build table type specific drop index statements
265 switch ( table.getType() ) {
266 case JOIN_TABLE: {
267 // create an index on every column
268 ColumnDefinition[] columns = table.getColumns();
269 for ( int i = 0; i < columns.length; i++ ) {
270 sb.append( "DROP INDEX " );
271 String idxSuffix = '_' + columns[i].getName() + "_IDX";
272 String idxName = truncate( table.getName().toUpperCase(), idxSuffix );
273 sb.append( idxName );
274 sb.append( ";\n" );
275 }
276 break;
277 }
278 default: {
279 break;
280 }
281 }
282 return sb;
283 }
284
285 Map<String, String> idToTruncatedId = new HashMap<String, String>();
286
287 int currentId;
288
289 /**
290 * Ensures that the given identifier does not exceed Oracle's limit (30 characters).
291 *
292 * @param identifier
293 * @param suffix
294 * @return the identifier (may be truncated)
295 */
296 private String truncate( String identifier, String suffix ) {
297 String truncatedIdentifier = idToTruncatedId.get( identifier + suffix );
298 if ( truncatedIdentifier == null ) {
299 truncatedIdentifier = identifier + suffix;
300 if ( truncatedIdentifier.length() > 30 ) {
301 System.out.print( "Generated identifier name '" + truncatedIdentifier
302 + "' exceeds 30 characters (Oracle limit)." );
303 truncatedIdentifier = identifier.substring( 0, 30 - suffix.length() - ( "" + currentId ).length() )
304 + ( currentId++ ) + suffix;
305 System.out.println( " Truncated to: '" + truncatedIdentifier + "'." );
306 }
307 idToTruncatedId.put( identifier + suffix, truncatedIdentifier );
308 }
309 return truncatedIdentifier;
310 }
311 }