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