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