001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/tools/datastore/DBSchemaToDatastoreConf.java $
002 /*---------------- FILE HEADER ------------------------------------------
003
004 This file is part of deegree.
005 Copyright (C) 2001-2007 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 Aennchenstr. 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.File;
046 import java.io.FileWriter;
047 import java.sql.Connection;
048 import java.sql.ResultSet;
049 import java.sql.ResultSetMetaData;
050 import java.sql.SQLException;
051 import java.sql.Statement;
052 import java.util.Properties;
053
054 import org.deegree.datatypes.Types;
055 import org.deegree.framework.log.ILogger;
056 import org.deegree.framework.log.LoggerFactory;
057 import org.deegree.framework.util.StringTools;
058 import org.deegree.io.DBConnectionPool;
059 import org.deegree.io.DBPoolException;
060 import org.deegree.io.shpapi.ShapeFile;
061
062 /**
063 * Example: java -classpath .;deegree.jar;$databasedriver.jar
064 * org.deegree.tools.datastore.DBSchemaToDatastoreConf -tables mytable,myothertable -user dev
065 * -password dev -driver oracle.jdbc.OracleDriver -url jdbc:oracle:thin:@localhost:1521:devs -output
066 * e:/temp/schema.xsd<br>
067 * or for shapefile:<br>
068 * java -classpath .;deegree.jar org.deegree.tools.datastore.DBSchemaToDatastoreConf -url
069 * c:/data/myshape -driver SHAPE -output e:/temp/schema.xsd<br>
070 *
071 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
072 * @author last edited by: $Author: apoth $
073 *
074 * @version $Revision: 6865 $, $Date: 2007-05-07 17:33:46 +0200 (Mo, 07 Mai 2007) $
075 */
076 public class DBSchemaToDatastoreConf {
077
078 private static final ILogger LOG = LoggerFactory.getLogger( DBSchemaToDatastoreConf.class );
079
080 private String[] tables = null;
081
082 private String user = null;
083
084 private String pw = null;
085
086 private String driver = null;
087
088 private String logon = null;
089
090 private String backend = null;
091
092 private String srs = "Enter A CRS!!!!!!";
093
094 /**
095 *
096 * @param tables
097 * list of table names used for one featuretype
098 * @param user
099 * database user
100 * @param pw
101 * users password
102 * @param driver
103 * database driver
104 * @param logon
105 * database URL/logon
106 * @param srs
107 */
108 public DBSchemaToDatastoreConf( String[] tables, String user, String pw, String driver,
109 String logon, String srs ) {
110 this.driver = driver;
111 this.logon = logon;
112 this.pw = pw;
113 this.user = user;
114 this.tables = tables;
115 if ( srs != null ) {
116 this.srs = srs;
117 }
118
119 if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
120 backend = "ORACLE";
121 } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
122 backend = "POSTGIS";
123 } else if ( driver.toUpperCase().contains( "SHAPE" ) ) {
124 backend = "SHAPE";
125 } else {
126 backend = "GENERICSQL";
127 }
128
129 }
130
131 /**
132 * creates a schema/datastore configuration for accessin database table through deegree WFS
133 *
134 * @return
135 * @throws Exception
136 */
137 public String run()
138 throws Exception {
139 StringBuffer sb = new StringBuffer( 1000 );
140
141 if ( backend.equals( "SHAPE" ) ) {
142
143 // TODO throw RE if tbl.len != 1
144
145 printShapeHeader( sb, tables[0] );
146
147 File f = new File( tables[0] );
148 ShapeFile shp = new ShapeFile( f.getAbsolutePath() );
149
150 printComplextHeader( sb, f.getName() );
151
152 String[] dataTypes = shp.getDataTypes();
153
154 printProperty( f.getName(), "GEOM", 2002, "GEOM", -9999, sb );
155
156 String[] props = shp.getProperties();
157 for ( int i = 0; i < props.length; i++ ) {
158 int sqlCode = toSQLCode( dataTypes[i] );
159 printProperty( tables[0], props[i], sqlCode,
160 Types.getTypeNameForSQLTypeCode( sqlCode ),
161 toPrecision( dataTypes[i] ), sb );
162 }
163
164 printComplexFooter( sb );
165
166 shp.close();
167
168 } else {
169
170 printHeader( sb );
171
172 for ( int k = 0; k < tables.length; k++ ) {
173 System.out.println( driver );
174 System.out.println( logon );
175 Connection con = DBConnectionPool.getInstance().acquireConnection( driver, logon,
176 user, pw );
177 Statement stmt = con.createStatement();
178 // ensure that we do not get a filled resultset because we just
179 // need the metainformation
180 LOG.logDebug( "read table: ", tables[k] );
181 ResultSet rs = stmt.executeQuery( "select * from " + tables[k] + " where 1 = 2" );
182
183 ResultSetMetaData rsmd = rs.getMetaData();
184 int cols = rsmd.getColumnCount();
185
186 printComplextHeader( sb, tables[k] );
187 for ( int i = 0; i < cols; i++ ) {
188 if ( rsmd.getColumnType( i + 1 ) != 2004 ) {
189 int tp = rsmd.getColumnType( i + 1 );
190 String tpn = Types.getTypeNameForSQLTypeCode( tp );
191 LOG.logDebug( tables[k] + "." + rsmd.getColumnName( i + 1 ) + ": " + tpn );
192 // add property just if type != BLOB
193 printProperty( tables[k], rsmd.getColumnName( i + 1 ),
194 rsmd.getColumnType( i + 1 ), tpn,
195 rsmd.getPrecision( i + 1 ), sb );
196 } else {
197 LOG.logDebug( "skiped: " + tables[k] + '.' + rsmd.getColumnName( i + 1 )
198 + ": " + rsmd.getColumnTypeName( i + 1 ) );
199 }
200 }
201
202 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
203 printComplexFooter( sb );
204 }
205 }
206 printFooter( sb );
207
208 return sb.toString();
209 }
210
211 private int toPrecision( String dbfType ) {
212 int precision = 0;
213
214 if ( dbfType.equalsIgnoreCase( "N" ) ) {
215 precision = 1;
216 } else if ( dbfType.equalsIgnoreCase( "F" ) ) {
217 precision = 2;
218 }
219
220 return precision;
221 }
222
223 private int toSQLCode( String dbfType ) {
224
225 int type = -9999;
226
227 if ( dbfType.equalsIgnoreCase( "C" ) ) {
228 type = Types.VARCHAR;
229 } else if ( dbfType.equalsIgnoreCase( "F" ) || dbfType.equalsIgnoreCase( "N" ) ) {
230 type = Types.NUMERIC;
231 } else if ( dbfType.equalsIgnoreCase( "D" ) || dbfType.equalsIgnoreCase( "M" ) ) {
232 type = Types.DATE;
233 } else if ( dbfType.equalsIgnoreCase( "L" ) ) {
234 type = Types.BOOLEAN;
235 } else if ( dbfType.equalsIgnoreCase( "B" ) ) {
236 type = Types.BLOB;
237 }
238
239 if ( type == -9999 ) {
240 throw new RuntimeException( "Type '" + dbfType + "' is not suported." );
241 }
242
243 return type;
244 }
245
246 /**
247 * adds the header of the configuration/schema
248 *
249 * @param sb
250 */
251 private void printHeader( StringBuffer sb ) {
252
253 sb.append( "<xsd:schema targetNamespace=\"http://www.deegree.org/app\" xmlns:gml=\"http://www.opengis.net/gml\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:deegreewfs=\"http://www.deegree.org/wfs\" xmlns:ogc=\"http://www.opengis.net/ogc\" xmlns:app=\"http://www.deegree.org/app\" elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\">\r\n"
254 + " <xsd:import namespace=\"http://www.opengis.net/gml\" schemaLocation=\"http://schemas.opengis.net/gml/3.1.1/base/feature.xsd\"/>\r\n"
255 + " <xsd:import namespace=\"http://www.opengis.net/gml\" schemaLocation=\"http://schemas.opengis.net/gml/3.1.1/base/geometryAggregates.xsd\"/>\r\n"
256 + " <!-- configuration for the persistence backend to be used -->\r\n"
257 + " <xsd:annotation>\r\n"
258 + " <xsd:appinfo>\r\n"
259 + " <deegreewfs:Prefix>app</deegreewfs:Prefix>\r\n"
260 + " <deegreewfs:Backend>"
261 + backend
262 + "</deegreewfs:Backend>\r\n"
263 + " <deegreewfs:DefaultSRS>"
264 + srs
265 + "</deegreewfs:DefaultSRS>\r\n"
266 + " <JDBCConnection xmlns=\"http://www.deegree.org/jdbc\">\r\n"
267 + " <Driver>"
268 + driver
269 + "</Driver>\r\n"
270 + " <Url>"
271 + logon
272 + "</Url>\r\n"
273 + " <User>"
274 + user
275 + "</User>\r\n"
276 + " <Password>"
277 + pw
278 + "</Password>\r\n"
279 + " <SecurityConstraints/>\r\n"
280 + " <Encoding>iso-8859-1</Encoding>\r\n"
281 + " </JDBCConnection>\r\n"
282 + " </xsd:appinfo>\r\n"
283 + " </xsd:annotation>" );
284
285 }
286
287 /**
288 * adds the header of the configuration/schema
289 *
290 * @param sb
291 */
292 private void printShapeHeader( StringBuffer sb, String filename ) {
293
294 sb.append( "<xsd:schema targetNamespace=\"http://www.deegree.org/app\" " ).append(
295 "xmlns:gml=\"http://www.opengis.net/gml\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" " ).append(
296 "xmlns:deegreewfs=\"http://www.deegree.org/wfs\" " ).append(
297 "xmlns:ogc=\"http://www.opengis.net/ogc\" xmlns:app=\"http://www.deegree.org/app\" " ).append(
298 "elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\">\r\n " ).append(
299 " <xsd:import namespace=\"http://www.opengis.net/gml\" " ).append(
300 "schemaLocation=\"http://schemas.opengis.net/gml/3.1.1/base/feature.xsd\"/>\r\n" ).append(
301 " <xsd:import namespace=\"http://www.opengis.net/gml\" " ).append(
302 "schemaLocation=\"http://schemas.opengis.net/gml/3.1.1/base/geometryAggregates.xsd\"/>\r\n" ).append(
303 " <!-- configuration for the persistence backend to be used -->\r\n" ).append(
304 " <xsd:annotation> " ).append(
305 " <xsd:appinfo>\n" ).append(
306 " <deegreewfs:Prefix>app</deegreewfs:Prefix>\n" ).append(
307 " <deegreewfs:Backend>SHAPE</deegreewfs:Backend>\n" ).append(
308 " <deegreewfs:File>" ).append(
309 filename ).append(
310 "</deegreewfs:File>\n" ).append(
311 " <deegreewfs:DefaultSRS>"
312 + srs
313 + "</deegreewfs:DefaultSRS>\n" ).append(
314 " </xsd:appinfo>\n" ).append(
315 " </xsd:annotation>\n" );
316 }
317
318 /**
319 * adds a header for a feature type to the schema
320 *
321 * @param sb
322 * @param table
323 * name of the table the feature type is assigned to
324 * @throws Exception
325 */
326 private void printComplextHeader( StringBuffer sb, String table )
327 throws Exception {
328 String idField = getPKeyName( table );
329 String tp = "INTEGER";
330 if ( backend.equals( "GENERICSQL" ) ) {
331 tp = "VARCHAR";
332 }
333 sb.append( "<!-- ============================================================== -->\n"
334 + "<xsd:element name='" + table + "' type='app:" + table + "Type' "
335 + "substitutionGroup=\"gml:_Feature\">\r\n" + " "
336 + "<xsd:annotation>\r\n" + " <xsd:appinfo>\r\n"
337 + " <deegreewfs:table>" + table + "</deegreewfs:table>\r\n"
338 + " <deegreewfs:gmlId prefix=\"ID_\">\r\n"
339 + " <deegreewfs:MappingField field='" + idField + "' type=\"" + tp
340 + "\"/>\r\n" + " </deegreewfs:gmlId>\r\n"
341 + " </xsd:appinfo>\r\n" + " </xsd:annotation>\r\n" + "</xsd:element>\r\n"
342 + "<!-- ============================================================== -->\n"
343 + " <xsd:complexType name='" + table + "Type'>\r\n"
344 + " <xsd:complexContent>\r\n"
345 + " <xsd:extension base=\"gml:AbstractFeatureType\">\r\n"
346 + " <xsd:sequence>\r\n" );
347 }
348
349 /**
350 * adds the footer of a feature type definition
351 *
352 * @param sb
353 */
354 private void printComplexFooter( StringBuffer sb ) {
355 sb.append( " </xsd:sequence>\r\n" + " </xsd:extension>\r\n"
356 + " </xsd:complexContent>\r\n" + " </xsd:complexType>\r\n" );
357 }
358
359 private void printFooter( StringBuffer sb ) {
360 sb.append( "</xsd:schema>" );
361 }
362
363 /**
364 * adds a property assigned to a database table field to the schema
365 *
366 * @param tableName
367 * table name
368 * @param name
369 * property name
370 * @param type
371 * xsd type name
372 * @param typeName
373 * SQL type name
374 * @param precision
375 * number precision if type is a number
376 * @param sb
377 * @throws SQLException
378 * @throws DBPoolException
379 */
380 private void printProperty( String tableName, String name, int type, String typeName,
381 int precision, StringBuffer sb )
382 throws DBPoolException, SQLException {
383
384 String tp = Types.getXSDTypeForSQLType( type, precision );
385 if ( !tp.startsWith( "gml:" ) ) {
386 tp = "xsd:" + tp;
387 }
388 int srid = -1;
389 if ( tp.equals( "gml:GeometryPropertyType" ) ) {
390 typeName = "GEOMETRY";
391 if ( backend.equals( "ORACLE" ) ) {
392 srid = getOracleSRID( tableName, name );
393 } else if ( backend.equals( "POSTGIS" ) ) {
394 srid = getPostGisSRID( tableName, name );
395 }
396 sb.append( "<xsd:element name='" + name.toLowerCase() + "' type='" + tp + "'>\r\n"
397 + " <xsd:annotation>\r\n" + " <xsd:appinfo>\r\n"
398 + " <deegreewfs:Content>\r\n"
399 + " <deegreewfs:MappingField field='" + name + "' type='"
400 + typeName.toUpperCase() + "' srs='" + srid + "'/>\r\n"
401 + " </deegreewfs:Content>\r\n" + " </xsd:appinfo>\r\n"
402 + " </xsd:annotation>\r\n" + "</xsd:element>\r\n" );
403 } else {
404 sb.append( "<xsd:element name='" + name.toLowerCase() + "' type='" + tp + "'>\r\n"
405 + " <xsd:annotation>\r\n" + " <xsd:appinfo>\r\n"
406 + " <deegreewfs:Content>\r\n"
407 + " <deegreewfs:MappingField field='" + name + "' type='"
408 + typeName.toUpperCase() + "'/>\r\n"
409 + " </deegreewfs:Content>\r\n" + " </xsd:appinfo>\r\n"
410 + " </xsd:annotation>\r\n" + "</xsd:element>\r\n" );
411 }
412 }
413
414 /**
415 * Retrieve the srid from the postgis database.
416 *
417 * @param tableName
418 * @param name
419 * @return int
420 * @throws DBPoolException
421 * @throws SQLException
422 */
423 private int getPostGisSRID( String tableName, String name )
424 throws DBPoolException, SQLException {
425
426 int srid = -1;
427 Connection con = null;
428 try {
429 con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
430 Statement stmt = con.createStatement();
431 ResultSet rs = stmt.executeQuery( "SELECT a.srid FROM GEOMETRY_COLUMNS a WHERE "
432 + "a.f_table_name='" + tableName.toUpperCase()
433 + "' AND a.f_geometry_column='" + name.toUpperCase()
434 + "'" );
435
436 while ( rs.next() ) {
437 srid = rs.getInt( 1 );
438 }
439
440 if ( srid == 0 ) {
441 srid = -1;
442 }
443
444 } catch ( DBPoolException e ) {
445 throw new DBPoolException(
446 "Unable to acquire a connection from the DBConnectionPool for the postgis database. ",
447 e );
448 } catch ( SQLException e ) {
449 throw new SQLException(
450 "Error performing the postgis query to retrieve the srid from the GEOMETRY_COLUMNS table. "
451 + e );
452 } finally {
453 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
454 }
455 return srid;
456 }
457
458 /**
459 * Retrieve the srid from the oracle database.
460 *
461 * @param tableName
462 * @param name
463 * @return int
464 * @throws DBPoolException
465 * @throws SQLException
466 */
467 private int getOracleSRID( String tableName, String name )
468 throws DBPoolException, SQLException {
469
470 int srid = -1;
471 Connection con = null;
472 try {
473 con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
474 Statement stmt = con.createStatement();
475 ResultSet rs = stmt.executeQuery( "SELECT a.srid FROM USER_SDO_GEOM_METADATA a WHERE "
476 + "a.table_name='" + tableName.toUpperCase()
477 + "' AND a.column_name='" + name.toUpperCase() + "'" );
478
479 while ( rs.next() ) {
480 srid = rs.getInt( 1 );
481 }
482 if ( srid == 0 ) {
483 srid = -1;
484 }
485
486 } catch ( DBPoolException e ) {
487 throw new DBPoolException( "Unable to acquire a connection from the "
488 + "DBConnectionPool for the oracle database. ", e );
489 } catch ( SQLException e ) {
490 throw new SQLException( "Error performing the oracle query to retrieve the "
491 + "srid from the GEOMETRY_COLUMNS table. " + e );
492 } finally {
493 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
494 }
495
496 return srid;
497 }
498
499 /**
500 * returns the name of the primary key of the passed table
501 *
502 * @param table
503 * @return the name of the primary key of the passed table
504 * @throws Exception
505 */
506 private String getPKeyName( String table )
507 throws Exception {
508 if ( backend.equals( "ORACLE" ) ) {
509 return getOraclePKeyName( table );
510 } else if ( backend.equals( "POSTGIS" ) ) {
511 return getPostgresPKeyName( table );
512 } else if ( backend.equals( "GENERICSQL" ) ) {
513 return "FEATURE_ID";
514 } else {
515 return "ID";
516 }
517 }
518
519 /**
520 * returns the primary key of a table from the oracle database
521 *
522 * @param table
523 * @return the primary key of a table from the oracle database
524 * @throws Exception
525 */
526 private String getOraclePKeyName( String table )
527 throws Exception {
528
529 String query = "SELECT cols.column_name "
530 + "FROM all_constraints cons, all_cons_columns cols "
531 + "WHERE cols.table_name = '" + table.toUpperCase() + "' "
532 + "AND cons.constraint_type = 'P' "
533 + "AND cons.constraint_name = cols.constraint_name "
534 + "AND cons.owner = cols.owner "
535 + "ORDER BY cols.table_name, cols.position ";
536
537 Connection con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
538 Statement stmt = con.createStatement();
539 ResultSet rs = stmt.executeQuery( query );
540 Object id = null;
541 if ( rs.next() ) {
542 id = rs.getObject( 1 );
543 }
544 if ( id == null ) {
545 id = "ID";
546 }
547 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
548
549 return id.toString();
550 }
551
552 /**
553 * returns the primary key of a table from the postgres database
554 *
555 * @param table
556 * @return the primary key of a table from the postgres database
557 * @throws Exception
558 */
559 private String getPostgresPKeyName( String table )
560 throws Exception {
561 String query = "select b.column_name from pg_catalog.pg_constraint a, "
562 + "information_schema.constraint_column_usage b Where a.conname = "
563 + "b.constraint_name AND a.contype = 'p' AND " + "b.table_name = '"
564 + table.toLowerCase() + "'";
565 Connection con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
566 Statement stmt = con.createStatement();
567 ResultSet rs = stmt.executeQuery( query );
568 Object id = null;
569 if ( rs.next() ) {
570 id = rs.getObject( 1 );
571 }
572 if ( id == null ) {
573 id = "ID";
574 }
575 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
576 return id.toString();
577 }
578
579 private static void validate( Properties map )
580 throws Exception {
581 if ( map.get( "-tables" ) == null ) {
582 throw new Exception( "-tables must be set" );
583 }
584 if ( map.get( "-user" ) == null ) {
585 map.put( "-password", " " );
586 }
587 if ( map.get( "-password" ) == null ) {
588 map.put( "-password", " " );
589 }
590 if ( map.get( "-driver" ) == null ) {
591 throw new Exception( "-driver must be set" );
592 }
593 if ( map.get( "-url" ) == null && !"SHAPE".equalsIgnoreCase( (String) map.get( "-driver" ) ) ) {
594 throw new Exception( "-url (database connection string) must be set" );
595 }
596 if ( map.get( "-output" ) == null ) {
597 throw new Exception( "-output must be set" );
598 }
599 }
600
601 /**
602 * @param args
603 * @throws Exception
604 */
605 public static void main( String[] args )
606 throws Exception {
607
608 Properties map = new Properties();
609 for ( int i = 0; i < args.length; i += 2 ) {
610 System.out.println( args[i + 1] );
611 map.put( args[i], args[i + 1] );
612 }
613
614 validate( map );
615 LOG.logInfo( map.toString() );
616 String tmp = (String) map.get( "-tables" );
617 String[] tables = StringTools.toArray( tmp, ",;|", true );
618 String user = (String) map.get( "-user" );
619 String pw = (String) map.get( "-password" );
620 String driver = (String) map.get( "-driver" );
621 String url = (String) map.get( "-url" );
622 String output = (String) map.get( "-output" );
623 String srs = (String) map.get( "-srs" );
624
625 DBSchemaToDatastoreConf stc = new DBSchemaToDatastoreConf( tables, user, pw, driver, url,
626 srs );
627
628 String conf = stc.run();
629 FileWriter fw = new FileWriter( output );
630 fw.write( conf );
631 fw.close();
632 System.exit( 0 );
633 }
634
635 }