001 //$HeadURL: svn+ssh://developername@svn.wald.intevation.org/deegree/base/trunk/resources/eclipse/files_template.xml $
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.xml;
037
038 import java.io.File;
039
040 import java.io.FileReader;
041 import java.io.StringReader;
042 import java.net.URI;
043 import java.sql.Connection;
044 import java.sql.ResultSet;
045 import java.sql.ResultSetMetaData;
046 import java.sql.Statement;
047 import java.util.ArrayList;
048 import java.util.LinkedHashMap;
049 import java.util.List;
050 import java.util.Map;
051
052 import org.deegree.framework.log.ILogger;
053 import org.deegree.framework.log.LoggerFactory;
054 import org.deegree.framework.util.Pair;
055 import org.deegree.framework.util.StringTools;
056 import org.deegree.framework.xml.NamespaceContext;
057 import org.deegree.framework.xml.XMLFragment;
058 import org.deegree.framework.xml.XMLTools;
059 import org.deegree.framework.xml.XSLTDocument;
060 import org.deegree.io.DBConnectionPool;
061 import org.deegree.io.IODocument;
062 import org.deegree.io.JDBCConnection;
063 import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
064 import org.deegree.model.crs.CoordinateSystem;
065 import org.deegree.model.spatialschema.GMLGeometryAdapter;
066 import org.deegree.model.spatialschema.Geometry;
067 import org.deegree.ogcbase.CommonNamespaces;
068 import org.postgis.PGgeometry;
069 import org.postgis.binary.BinaryParser;
070 import org.w3c.dom.Element;
071
072 /**
073 * reads a relational database model and transforms it into XML. Via a XSL script it is possible to transform the XML
074 * into any other XML format. The program uses following configuration
075 * <pre>
076 * <?xml version="1.0" encoding="UTF-8"?>
077 * <dor:XMLMapping xmlns:dor="http://www.deegree.org/xmlmapping">
078 * <JDBCConnection xmlns="http://www.deegree.org/jdbc">
079 * <Driver>org.postgresql.Driver</Driver>
080 * <Url>jdbc:postgresql://localhost:5432/csw</Url>
081 * <User>aUser</User>
082 * <Password>aPassword</Password>
083 * <SecurityConstraints/>
084 * <Encoding>iso-8859-1</Encoding>
085 * </JDBCConnection>
086 * <dor:XSLT>e:/temp/test.xsl</dor:XSLT>
087 * <dor:Table>
088 * <dor:ElementName>Metadata</dor:ElementName>
089 * <dor:Select>Select * From cqp_main</dor:Select>
090 * <dor:Table>
091 * <dor:ElementName>domainconsistency</dor:ElementName>
092 * <dor:Select>Select * From cqp_domainconsistency where fk_cqp_main = $ID</dor:Select>
093 * <dor:Table>
094 * <dor:ElementName>specificationdate</dor:ElementName>
095 * <dor:Select>Select * From cqp_specificationdate where fk_cqp_domainconsistency = $ID</dor:Select>
096 * </dor:Table>
097 * </dor:Table>
098 * <dor:Table>
099 * <dor:ElementName>bbox</dor:ElementName>
100 * <dor:Select>Select * From cqp_bbox where fk_cqp_main = $ID</dor:Select>
101 * <dor:GeometryColumn crs="EPSG:4326">geom</dor:GeometryColumn>
102 * </dor:Table>
103 * </dor:Table>
104 * </dor:XMLMapping>
105 * </pre>
106 *
107 * @author <a href="mailto:name@deegree.org">Andreas Poth</a>
108 * @author last edited by: $Author: admin $
109 *
110 * @version $Revision: $, $Date: $
111 */
112 public class DatabaseXMLMapping {
113
114 private static ILogger LOG = LoggerFactory.getLogger( DatabaseXMLMapping.class );
115
116 private static URI namespace = URI.create( "http://www.deegree.org/xmlmapping" );
117
118 private static NamespaceContext nsc;
119
120 protected Table mainTable;
121
122 private JDBCConnection jdbc;
123
124 protected XSLTDocument xslt;
125
126 /**
127 *
128 * @param fileName
129 * @throws Exception
130 */
131 public DatabaseXMLMapping( String fileName ) throws Exception {
132 nsc = CommonNamespaces.getNamespaceContext();
133 nsc.addNamespace( "dxm", namespace );
134 readConfig( fileName );
135 }
136
137 private void readConfig( String fileName )
138 throws Exception {
139 FileReader fr = new FileReader( fileName );
140 XMLFragment xml = new XMLFragment( fr, XMLFragment.DEFAULT_URL );
141 // parse database connection info
142 Element jdbcElement = XMLTools.getRequiredElement( xml.getRootElement(), "dgjdbc:JDBCConnection", nsc );
143 IODocument ioDoc = new IODocument( jdbcElement );
144 jdbc = ioDoc.parseJDBCConnection();
145
146 // xslt file to transform result of xml mapping
147 String xsltFileName = XMLTools.getNodeAsString( xml.getRootElement(), "./dxm:XSLT", nsc, null );
148 if ( xsltFileName != null ) {
149 xslt = new XSLTDocument( new File( xsltFileName ).toURL() );
150 }
151
152 // parse table relations
153 String elementName = XMLTools.getRequiredNodeAsString( xml.getRootElement(), "./dxm:Table/dxm:ElementName", nsc );
154 String select = XMLTools.getRequiredNodeAsString( xml.getRootElement(), "./dxm:Table/dxm:Select", nsc );
155
156 List<Pair<String, String>> geomFieldList = new ArrayList<Pair<String, String>>();
157 List<Element> elements = XMLTools.getElements( xml.getRootElement(), "./dxm:Table/dxm:GeometryColumn", nsc );
158 for ( Element element : elements ) {
159 String field = XMLTools.getStringValue( element ).toLowerCase();
160 String crs = element.getAttribute( "crs" );
161 Pair<String, String> p = new Pair<String, String>( field, crs );
162 geomFieldList.add( p );
163 }
164 mainTable = new Table( elementName, select, geomFieldList );
165 List<Element> tables = XMLTools.getElements( xml.getRootElement(), "./dxm:Table/dxm:Table", nsc );
166 for ( Element element : tables ) {
167 parseTable( mainTable, element );
168 }
169 }
170
171 /**
172 * @param table
173 * @param element
174 */
175 private void parseTable( Table table, Element element )
176 throws Exception {
177 String elementName = XMLTools.getRequiredNodeAsString( element, "dxm:ElementName", nsc );
178 String select = XMLTools.getRequiredNodeAsString( element, "dxm:Select", nsc );
179 List<Pair<String, String>> geomFieldList = new ArrayList<Pair<String, String>>();
180 List<Element> elements = XMLTools.getElements( element, "dxm:GeometryColumn", nsc );
181 for ( Element elem : elements ) {
182 String field = XMLTools.getStringValue( elem ).toLowerCase();
183 String crs = elem.getAttribute( "crs" );
184 Pair<String, String> p = new Pair<String, String>( field, crs );
185 geomFieldList.add( p );
186 }
187 Table subTable = new Table( elementName, select, geomFieldList );
188 table.getTables().add( subTable );
189 List<Element> tables = XMLTools.getElements( element, "dxm:Table", nsc );
190 for ( Element subEelement : tables ) {
191 parseTable( subTable, subEelement );
192 }
193 }
194
195 public void run()
196 throws Exception {
197 DBConnectionPool pool = DBConnectionPool.getInstance();
198 Connection conn = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
199 Statement stmt = null;
200 ResultSet rs = null;
201
202 try {
203 String sql = mainTable.getSelect();
204 stmt = conn.createStatement();
205 rs = stmt.executeQuery( sql );
206 ResultSetMetaData rsmd = rs.getMetaData();
207 int colCount = rsmd.getColumnCount();
208
209 Map<String, Object> row = new LinkedHashMap<String, Object>();
210 while ( rs.next() ) {
211 // create one XML document for each row of the main table
212 XMLFragment result = new XMLFragment();
213 result.load( new StringReader( "<DatabaseTable/>" ), XMLFragment.DEFAULT_URL );
214 // append root table element
215 Element tableElement = XMLTools.appendElement( result.getRootElement(), null, mainTable.getName() );
216 for ( int i = 0; i < colCount; i++ ) {
217 String cName = rsmd.getColumnName( i + 1 ).toLowerCase();
218 Object value = rs.getObject( i + 1 );
219 row.put( cName, value );
220 if ( value != null ) {
221 Pair<String, CoordinateSystem> p = mainTable.getGeometryColumn( cName );
222 if ( p != null ) {
223 handleGeometry( tableElement, p, value );
224 } else {
225 XMLTools.appendElement( tableElement, null, cName, value.toString() );
226 }
227 } else {
228 XMLTools.appendElement( tableElement, null, cName );
229 }
230 }
231 // add sub tables if available
232 List<Table> tables = mainTable.getTables();
233 for ( Table table : tables ) {
234 appendTable( tableElement, conn, row, table );
235 }
236 row.clear();
237 if ( xslt != null ) {
238 result = transform( result );
239 }
240 performAction( result );
241 }
242 } catch ( Exception e ) {
243 LOG.logError( e );
244 throw e;
245 } finally {
246 //rs.close();
247 stmt.close();
248 pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
249 }
250
251 }
252
253 /**
254 *
255 * @param ps
256 * @param conn
257 * @param targetRow
258 * @param targetTable
259 * @throws Exception
260 */
261 private void appendTable( Element tableElement, Connection conn, Map<String, Object> targetRow, Table subTable )
262 throws Exception {
263 Statement stmt = null;
264
265 try {
266 String sql = subTable.getSelect();
267 List<String> variables = subTable.getVariables();
268 // replace variables with real values
269 for ( String variable : variables ) {
270 Object value = targetRow.get( variable.substring( 1, variable.length() ).toLowerCase() );
271 if ( value instanceof String ) {
272 sql = StringTools.replace( sql, variable, "'" + value.toString() + "'", true );
273 } else if ( value != null ) {
274 sql = StringTools.replace( sql, variable, value.toString(), true );
275 } else {
276 sql = StringTools.replace( sql, variable, "'" + "XXXXXXXdummyXXXXXXX"+ "'", true );
277 }
278 }
279 LOG.logDebug( sql );
280 stmt = conn.createStatement();
281 ResultSet rs = stmt.executeQuery( sql );
282 ResultSetMetaData rsmd = rs.getMetaData();
283 int colCount = rsmd.getColumnCount();
284
285 Map<String, Object> row = new LinkedHashMap<String, Object>();
286 while ( rs.next() ) {
287 // append sub table element
288 Element subTableElement = XMLTools.appendElement( tableElement, null, subTable.getName() );
289 for ( int i = 0; i < colCount; i++ ) {
290 String cName = rsmd.getColumnName( i + 1 ).toLowerCase();
291 Object value = rs.getObject( i + 1 );
292 row.put( cName, value );
293 if ( value != null ) {
294 Pair<String, CoordinateSystem> p = subTable.getGeometryColumn( cName );
295 if ( p != null ) {
296 handleGeometry( subTableElement, p, value );
297 } else {
298 XMLTools.appendElement( subTableElement, null, cName, value.toString() );
299 }
300 } else {
301 XMLTools.appendElement( subTableElement, null, cName );
302 }
303 }
304 // recursion!
305 // append sub tables if available
306 List<Table> tables = subTable.getTables();
307 for ( Table table : tables ) {
308 appendTable( subTableElement, conn, row, table );
309 }
310 row.clear();
311 }
312 } catch ( Exception e ) {
313 LOG.logError( e );
314 throw e;
315 } finally {
316 stmt.close();
317 }
318 }
319
320 /**
321 * @param tableElement
322 * @param p
323 * @param value
324 */
325 private void handleGeometry( Element tableElement, Pair<String, CoordinateSystem> p, Object value )
326 throws Exception {
327 Geometry geom = null;
328 if ( jdbc.getDriver().toLowerCase().indexOf( "postgres" ) > -1 ) {
329 geom = PGgeometryAdapter.wrap( (PGgeometry) value, p.second );
330 } else if ( jdbc.getDriver().toLowerCase().indexOf( "oracle" ) > -1 ) {
331 // JGeometry jGeometry = JGeometry.load( (STRUCT) value );
332 // geom = JGeometryAdapter.wrap( jGeometry, p.second );
333 } else if ( jdbc.getDriver().toLowerCase().indexOf( "sqlserver" ) > -1 ) {
334 } else if ( jdbc.getDriver().toLowerCase().indexOf( "mysql" ) > -1 ) {
335 byte[] wkb = (byte[]) value;
336 org.postgis.Geometry pgGeometry = new BinaryParser().parse( wkb );
337 geom = PGgeometryAdapter.wrap( pgGeometry, p.second );
338 }
339 String s = GMLGeometryAdapter.export( geom ).toString();
340 s = StringTools.replace( s, ">", " xmlns:gml='http://www.opengis.net/gml'>", false );
341 XMLFragment xml = new XMLFragment( new StringReader( s ), XMLFragment.DEFAULT_URL );
342 Element element = XMLTools.appendElement( tableElement, null, p.first );
343 XMLTools.copyNode( xml.getRootElement().getOwnerDocument(), element );
344 }
345
346 /**
347 * @param xml
348 * @return
349 */
350 protected XMLFragment transform( XMLFragment xml )
351 throws Exception {
352 return xslt.transform( xml );
353 }
354
355 protected void performAction( XMLFragment xml ) {
356 System.out.println( xml.getAsString() );
357 }
358
359
360 }