001 //$HeadURL: svn+ssh://developername@svn.wald.intevation.org/deegree/base/trunk/src/org/deegree/tools/datastore/DBSchemaToDatastoreConf.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 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.BufferedReader;
046 import java.io.File;
047 import java.io.FileOutputStream;
048 import java.io.IOException;
049 import java.io.InputStreamReader;
050 import java.io.StringReader;
051 import java.net.URL;
052 import java.security.InvalidParameterException;
053 import java.sql.Connection;
054 import java.sql.ResultSet;
055 import java.sql.ResultSetMetaData;
056 import java.sql.SQLException;
057 import java.sql.Statement;
058 import java.util.Properties;
059
060 import javax.xml.transform.TransformerException;
061
062 import org.deegree.datatypes.Types;
063 import org.deegree.datatypes.UnknownTypeException;
064 import org.deegree.framework.log.ILogger;
065 import org.deegree.framework.log.LoggerFactory;
066 import org.deegree.framework.util.FileUtils;
067 import org.deegree.framework.util.StringTools;
068 import org.deegree.framework.xml.XMLFragment;
069 import org.deegree.io.DBConnectionPool;
070 import org.deegree.io.DBPoolException;
071 import org.deegree.io.dbaseapi.DBaseException;
072 import org.deegree.io.shpapi.HasNoDBaseFileException;
073 import org.deegree.io.shpapi.ShapeFile;
074 import org.xml.sax.SAXException;
075
076 /**
077 * Example: java -classpath .;deegree.jar;$databasedriver.jar
078 * org.deegree.tools.datastore.DBSchemaToDatastoreConf -tables mytable,myothertable -user dev
079 * -password dev -driver oracle.jdbc.OracleDriver -url jdbc:oracle:thin:@localhost:1521:devs -output
080 * e:/temp/schema.xsd<br>
081 * or for shapefile:<br>
082 * java -classpath .;deegree.jar org.deegree.tools.datastore.DBSchemaToDatastoreConf -url
083 * c:/data/myshape -driver SHAPE -output e:/temp/schema.xsd<br>
084 *
085 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
086 * @author last edited by: $Author: apoth $
087 *
088 * @version $Revision: 8049 $, $Date: 2007-08-23 10:19:28 +0200 (Do, 23 Aug 2007) $
089 */
090 public class DBSchemaToDatastoreConf {
091
092 private static final ILogger LOG = LoggerFactory.getLogger( DBSchemaToDatastoreConf.class );
093
094 private String[] tables;
095
096 private String user;
097
098 private String pw;
099
100 private String driver;
101
102 private String logon;
103
104 private String backend;
105
106 private String vendor;
107
108 private String srs;
109
110 /**
111 *
112 * @param tables
113 * list of table names used for one featuretype
114 * @param user
115 * database user
116 * @param pw
117 * users password
118 * @param driver
119 * database driver
120 * @param logon
121 * database URL/logon
122 * @param srs
123 * @throws IOException
124 */
125 public DBSchemaToDatastoreConf( String[] tables, String user, String pw, String driver, String logon, String srs )
126 throws IOException {
127 this.driver = driver;
128 this.logon = logon;
129 this.pw = pw;
130 this.user = user;
131 this.tables = tables;
132 if ( srs != null ) {
133 this.srs = srs;
134 } else {
135 this.srs = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERSRS" ), false );
136 }
137
138 if ( driver.toUpperCase().indexOf( "ORACLE" ) > -1 ) {
139 backend = "ORACLE";
140 vendor = backend;
141 } else if ( driver.toUpperCase().indexOf( "POSTGRES" ) > -1 ) {
142 backend = "POSTGIS";
143 vendor = backend;
144 } else if ( driver.toUpperCase().contains( "SHAPE" ) ) {
145 backend = "SHAPE";
146 vendor = backend;
147 } else {
148 backend = "GENERICSQL";
149 vendor = getVendor( driver );
150 }
151
152 }
153
154 private String getVendor( String driver ) {
155 // find out which database is used
156 String vendor = null;
157 if ( driver.toUpperCase().contains( "POSTGRES" ) ) {
158 backend = "POSTGRES";
159 } else if ( driver.toUpperCase().contains( "SQLSERVER" ) ) {
160 backend = "SQLSERVER";
161 } else if ( driver.toUpperCase().contains( "INGRES" ) || driver.equals( "ca.edbc.jdbc.EdbcDriver" ) ) {
162 backend = "INGRES";
163 } else if ( driver.toUpperCase().contains( "HSQLDB" ) ) {
164 backend = "HSQLDB";
165 } else {
166 backend = "SHAPE";
167 }
168 return vendor;
169 }
170
171 /**
172 * creates a schema/datastore configuration for accessin database table through deegree WFS
173 *
174 * @return a schema/datastore configuration for accessin database table through deegree WFS
175 * @throws Exception
176 */
177 public String run()
178 throws Exception {
179 StringBuffer sb = new StringBuffer( 5000 );
180
181 if ( backend.equals( "SHAPE" ) ) {
182 handleShape( sb );
183 } else {
184 handleDatabase( sb );
185 }
186 printFooter( sb );
187
188 return sb.toString();
189 }
190
191 /**
192 * creates a datastore configuration for a database backend
193 *
194 * @param sb
195 * @throws DBPoolException
196 * @throws SQLException
197 * @throws Exception
198 * @throws UnknownTypeException
199 * @throws IOException
200 */
201 private void handleDatabase( StringBuffer sb )
202 throws DBPoolException, SQLException, Exception, UnknownTypeException, IOException {
203 printHeader( sb );
204
205 for ( int k = 0; k < tables.length; k++ ) {
206 LOG.logInfo( "Opening JDBC connection with driver: " + driver );
207 LOG.logInfo( "Opening JDBC connection to database : " + logon );
208
209 Connection con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
210 Statement stmt = con.createStatement();
211 // ensure that we do not get a filled resultset because we just
212 // need the metainformation
213 LOG.logDebug( "read table: ", tables[k] );
214 ResultSet rs = stmt.executeQuery( "select * from " + tables[k] + " where 1 = 2" );
215
216 ResultSetMetaData rsmd = rs.getMetaData();
217 int cols = rsmd.getColumnCount();
218
219 printComplexHeader( sb, tables[k] );
220 for ( int i = 0; i < cols; i++ ) {
221 if ( rsmd.getColumnType( i + 1 ) != 2004 ) {
222 int tp = rsmd.getColumnType( i + 1 );
223 String tpn = Types.getTypeNameForSQLTypeCode( tp );
224 LOG.logDebug( tables[k] + '.' + rsmd.getColumnName( i + 1 ) + ": " + tpn );
225 // add property just if type != BLOB
226 printProperty( tables[k], rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ), tpn,
227 rsmd.getPrecision( i + 1 ), sb );
228 } else {
229 String msg = StringTools.concat( 200, "skiped: ", tables[k], '.', rsmd.getColumnName( i + 1 ),
230 ": ", rsmd.getColumnTypeName( i + 1 ) );
231 LOG.logDebug( msg );
232 }
233 }
234
235 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
236 printComplexFooter( sb );
237 }
238 }
239
240 /**
241 * creates a datastore configuration for a shapefile backend
242 *
243 * @param sb
244 * @throws IOException
245 * @throws Exception
246 * @throws HasNoDBaseFileException
247 * @throws DBaseException
248 * @throws DBPoolException
249 * @throws SQLException
250 * @throws UnknownTypeException
251 */
252 private void handleShape( StringBuffer sb )
253 throws IOException, Exception, HasNoDBaseFileException, DBaseException, DBPoolException,
254 SQLException, UnknownTypeException {
255 // TODO throw RE if tbl.len != 1
256
257 printShapeHeader( sb, tables[0] );
258
259 File f = new File( tables[0] );
260 ShapeFile shp = new ShapeFile( f.getAbsolutePath() );
261
262 printComplexHeader( sb, f.getName() );
263
264 String[] dataTypes = shp.getDataTypes();
265
266 printProperty( f.getName(), "GEOM", 2002, "GEOM", -9999, sb );
267
268 String[] props = shp.getProperties();
269 for ( int i = 0; i < props.length; i++ ) {
270 int sqlCode = toSQLCode( dataTypes[i] );
271 printProperty( tables[0], props[i], sqlCode, Types.getTypeNameForSQLTypeCode( sqlCode ),
272 toPrecision( dataTypes[i] ), sb );
273 }
274
275 printComplexFooter( sb );
276
277 shp.close();
278 }
279
280 /**
281 * returns precision for a dBase numerical type
282 *
283 * @param dbfType
284 * @return
285 */
286 private int toPrecision( String dbfType ) {
287 int precision = 0;
288
289 if ( dbfType.equalsIgnoreCase( "N" ) ) {
290 precision = 1;
291 } else if ( dbfType.equalsIgnoreCase( "F" ) ) {
292 precision = 2;
293 }
294
295 return precision;
296 }
297
298 /**
299 * returns the SQL type code for a dBase type char
300 *
301 * @param dbfType
302 * @return
303 */
304 private int toSQLCode( String dbfType ) {
305
306 int type = -9999;
307
308 if ( dbfType.equalsIgnoreCase( "C" ) ) {
309 type = Types.VARCHAR;
310 } else if ( dbfType.equalsIgnoreCase( "F" ) || dbfType.equalsIgnoreCase( "N" ) ) {
311 type = Types.NUMERIC;
312 } else if ( dbfType.equalsIgnoreCase( "D" ) || dbfType.equalsIgnoreCase( "M" ) ) {
313 type = Types.DATE;
314 } else if ( dbfType.equalsIgnoreCase( "L" ) ) {
315 type = Types.BOOLEAN;
316 } else if ( dbfType.equalsIgnoreCase( "B" ) ) {
317 type = Types.BLOB;
318 }
319
320 if ( type == -9999 ) {
321 throw new RuntimeException( "Type '" + dbfType + "' is not suported." );
322 }
323
324 return type;
325 }
326
327 /**
328 * adds the header of the configuration/schema for a database datastore
329 *
330 * @param sb
331 */
332 private void printHeader( StringBuffer sb ) {
333
334 String s = DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "HEADER", backend, srs, driver, logon, user, pw );
335 sb.append( s );
336
337 }
338
339 /**
340 * adds the header of the configuration/schema for a shapefile datastore
341 *
342 * @param sb
343 * @param filename
344 * path to the shapefile
345 */
346 private void printShapeHeader( StringBuffer sb, String filename ) {
347
348 String s = DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "SHAPEHEADER", filename, srs );
349 sb.append( s );
350
351 }
352
353 /**
354 * adds a header for a feature type to the schema
355 *
356 * @param sb
357 * @param table
358 * name of the table the feature type is assigned to
359 * @throws Exception
360 */
361 private void printComplexHeader( StringBuffer sb, String table )
362 throws Exception {
363 String idField = getPKeyName( table );
364 String tp = "INTEGER";
365 if ( backend.equals( "GENERICSQL" ) ) {
366 tp = "VARCHAR";
367 }
368 String s = DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "COMPLEXHEADER", table, table, table, idField,
369 tp, table );
370 sb.append( s );
371
372 }
373
374 /**
375 * adds the footer of a feature type definition
376 *
377 * @param sb
378 */
379 private void printComplexFooter( StringBuffer sb ) {
380 sb.append( DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "COMPLEXFOOTER" ) );
381 }
382
383 /**
384 * prints XSD footer
385 *
386 * @param sb
387 */
388 private void printFooter( StringBuffer sb ) {
389 sb.append( DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "FOOTER" ) );
390 }
391
392 /**
393 * adds a property assigned to a database table field to the schema
394 *
395 * @param tableName
396 * table name
397 * @param name
398 * property name
399 * @param type
400 * xsd type name
401 * @param typeName
402 * SQL type name
403 * @param precision
404 * number precision if type is a number
405 * @param sb
406 * @throws SQLException
407 * @throws DBPoolException
408 * @throws IOException
409 */
410 private void printProperty( String tableName, String name, int type, String typeName, int precision, StringBuffer sb )
411 throws DBPoolException, SQLException, IOException {
412
413 String tp = Types.getXSDTypeForSQLType( type, precision );
414 if ( !tp.startsWith( "gml:" ) ) {
415 tp = "xsd:" + tp;
416 }
417
418 if ( tp.equals( "gml:GeometryPropertyType" ) ) {
419 int srid = getSRID( tableName, name );
420 String s = DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "GEOMPROPERTY", name.toLowerCase(), tp,
421 name, ""+srid );
422 sb.append( s );
423 } else {
424 String s = DBSchemaToDatastoreConfSQLXSDAccess.getXSDFragment( "PROPERTY", name.toLowerCase(), tp, name,
425 typeName.toUpperCase() );
426 sb.append( s );
427 }
428 }
429
430 /**
431 *
432 * @param tableName
433 * @param columnName
434 * @return
435 * @throws SQLException
436 * @throws DBPoolException
437 * @throws IOException
438 */
439 private int getSRID( String tableName, String columnName )
440 throws SQLException, DBPoolException, IOException {
441 int srid = -1;
442 String query = DBSchemaToDatastoreConfSQLSQLAccess.getSQLStatement( vendor + "_SRID", tableName.toUpperCase(),
443 columnName.toUpperCase() );
444 LOG.logInfo( query );
445 Connection con = null;
446 Statement stmt = null;
447 ResultSet rs = null;
448 if ( query != null && query.indexOf( "not found$" ) < 0 ) {
449 try {
450 con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
451 stmt = con.createStatement();
452 rs = stmt.executeQuery( query );
453
454 while ( rs.next() ) {
455 srid = rs.getInt( 1 );
456 }
457
458 if ( srid == 0 ) {
459 srid = -1;
460 }
461
462 } catch ( SQLException e ) {
463 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ERRORSRID" ) + e.getMessage() );
464 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERFIELD" ) );
465 } finally {
466 rs.close();
467 stmt.close();
468 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
469 }
470 } else {
471 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "NOSRIDQUERY" ) );
472 }
473 if ( srid == -1 ) {
474 String tmp = DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERSRID" );
475 srid = Integer.parseInt( readUserInput( tmp, false ) );
476 }
477 return srid;
478 }
479
480 /**
481 * returns the name of the primary key of the passed table
482 *
483 * @param table
484 * @return the name of the primary key of the passed table
485 * @throws DBPoolException
486 * @throws SQLException
487 * @throws IOException
488 */
489 private String getPKeyName( String table )
490 throws DBPoolException, SQLException, IOException {
491
492 String query = DBSchemaToDatastoreConfSQLSQLAccess.getSQLStatement( vendor + "_ID", table.toUpperCase() );
493 LOG.logInfo( query );
494 Object id = null;
495 Statement stmt = null;
496 ResultSet rs = null;
497 if ( query != null && query.indexOf( "not found$" ) < 0 ) {
498 Connection con = DBConnectionPool.getInstance().acquireConnection( driver, logon, user, pw );
499 try {
500 stmt = con.createStatement();
501 rs = stmt.executeQuery( query );
502
503 if ( rs.next() ) {
504 id = rs.getObject( 1 );
505 }
506 } catch ( Exception e ) {
507 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ERRORPK" ) + e.getMessage() );
508 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERFIELD" ) );
509 } finally {
510 rs.close();
511 stmt.close();
512 DBConnectionPool.getInstance().releaseConnection( con, driver, logon, user, pw );
513 }
514 } else {
515 System.out.println( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "NOPKQUERY" ) );
516 }
517 if ( id == null ) {
518 id = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERPK" ), false );
519 }
520 return id.toString();
521 }
522
523 private static void validate( Properties map )
524 throws InvalidParameterException, IOException {
525 if ( map.get( "-?" ) != null || map.get( "-h" ) != null || map.get( "-help" ) != null ) {
526 printHelp();
527 System.exit( 1 );
528 }
529 if ( map.get( "-tables" ) == null ) {
530 String s = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERTABLES" ), false );
531 map.put( "-tables", s );
532 }
533
534 if ( map.get( "-driver" ) == null ) {
535 String s = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERDRIVER" ), false );
536 map.put( "-driver", s );
537 }
538
539 if ( map.get( "-user" ) == null ) {
540 if ( !"SHAPE".equals( map.get( "-driver" ) ) ) {
541 String s = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERUSER" ), false );
542 map.put( "-user", s );
543 }
544 }
545
546 if ( map.get( "-password" ) == null ) {
547 map.put( "-password", " " );
548 }
549
550 if ( map.get( "-url" ) == null && !"SHAPE".equalsIgnoreCase( (String) map.get( "-driver" ) ) ) {
551 String s = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTERURL" ), false );
552 map.put( "-url", s );
553 }
554 if ( map.get( "-output" ) == null ) {
555 String s = readUserInput( DBSchemaToDatastoreConfSQLMSGAccess.getMessage( "ENTEROUTPUT" ), false );
556 map.put( "-output", s );
557 }
558 }
559
560 private static void printHelp()
561 throws IOException {
562 URL url = DBSchemaToDatastoreConf.class.getResource( "DBSchemaToDatastoreConfHelp.txt" );
563 System.out.println( FileUtils.readTextFile( url ) );
564 }
565
566 /**
567 * @param args
568 * @throws IOException
569 * @throws SAXException
570 * @throws TransformerException
571 * @throws Exception
572 */
573 public static void main( String[] args )
574 throws Exception {
575
576 Properties map = new Properties();
577 for ( int i = 0; i < args.length; ) {
578 String first = args[i++];
579 if ( "?".equals( first ) || "-h".equals( first ) || "-help".equals( first ) ) {
580 printHelp();
581 System.exit( 0 );
582 }
583 map.put( first, args[i++] );
584 }
585
586 try {
587 validate( map );
588 } catch ( InvalidParameterException ipe ) {
589 LOG.logError( ipe.getMessage() );
590 printHelp();
591 System.exit( 1 );
592 }
593 LOG.logDebug( "Resulting commandline arguments and their values {argument=value, ...}: " + map.toString() );
594 String tmp = (String) map.get( "-tables" );
595 String[] tables = StringTools.toArray( tmp, ",;|", true );
596 String user = (String) map.get( "-user" );
597 String pw = (String) map.get( "-password" );
598 String driver = (String) map.get( "-driver" );
599 String url = (String) map.get( "-url" );
600 String output = (String) map.get( "-output" );
601 String srs = (String) map.get( "-srs" );
602
603 DBSchemaToDatastoreConf stc = new DBSchemaToDatastoreConf( tables, user, pw, driver, url, srs );
604 String conf = null;
605 try {
606 conf = stc.run();
607 } catch ( Exception e ) {
608 LOG.logError( e.getMessage(), e );
609 System.exit( 1 );
610 }
611 storeSchema( output, conf );
612 System.exit( 0 );
613 }
614
615 /**
616 *
617 * @param output
618 * @param conf
619 * @throws IOException
620 * @throws SAXException
621 * @throws TransformerException
622 */
623 private static void storeSchema( String output, String conf )
624 throws SAXException, IOException, TransformerException {
625 if ( conf != null ) {
626 XMLFragment xml = new XMLFragment();
627 xml.load( new StringReader( conf ), XMLFragment.DEFAULT_URL );
628 FileOutputStream fos = new FileOutputStream( output );
629 xml.prettyPrint( fos );
630 fos.close();
631 }
632 }
633
634 /**
635 * This function prints a message on the command line and asks the user for an input, returns
636 * the text the User has typed, null otherwise
637 *
638 * @param describtion
639 * The message to be displayed to the user asking for a certain text to type
640 * @return the read text, or null if nothing was read
641 * @throws IOException
642 */
643 private static String readUserInput( String describtion, boolean acceptNull )
644 throws IOException {
645
646 String result = null;
647 do {
648 System.out.print( describtion );
649 System.out.println( ':' );
650 BufferedReader reader = new BufferedReader( new InputStreamReader( System.in ) );
651 result = reader.readLine();
652 } while ( !acceptNull && ( result == null || result.trim().length() == 0 ) );
653 return result;
654
655 }
656 }