001 //$Header: $
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
037 package org.deegree.portal.portlet.modules.wfs.actions.portlets;
038
039 import java.sql.Connection;
040 import java.sql.Driver;
041 import java.sql.DriverManager;
042 import java.sql.ResultSet;
043 import java.sql.SQLException;
044 import java.sql.Statement;
045 import java.util.ArrayList;
046 import java.util.List;
047
048 import javax.servlet.ServletContext;
049 import javax.servlet.http.HttpServletRequest;
050
051 import org.apache.jetspeed.portal.Portlet;
052 import org.apache.turbine.util.RunData;
053 import org.deegree.framework.log.ILogger;
054 import org.deegree.framework.log.LoggerFactory;
055 import org.deegree.framework.util.StringTools;
056 import org.deegree.portal.PortalException;
057 import org.deegree.portal.portlet.modules.actions.IGeoPortalPortletPerform;
058
059 /**
060 * Removes a number of Features (listed by their ID).
061 *
062 * @author <a href="mailto:taddei@lat-lon.de">Ugo Taddei</a>
063 * @author last edited by: $Author:$
064 *
065 * @version $Revision:$, $Date:$
066 */
067 public class RemoveAnnotationPerform extends IGeoPortalPortletPerform {
068
069 private static final ILogger LOG = LoggerFactory.getLogger( RemoveAnnotationPerform.class );
070
071 /**
072 *
073 * Creates an instance of a RemoveAnnotationPerform.
074 *
075 * @param request
076 * @param portlet
077 * @param servletContext
078 */
079 public RemoveAnnotationPerform( HttpServletRequest request, Portlet portlet, ServletContext servletContext ) {
080 super( request, portlet, servletContext );
081 }
082
083 /**
084 * Builds up the portlet.
085 *
086 * @param data
087 * @throws PortalException
088 */
089 public void buildNormalContext( RunData data )
090 throws PortalException {
091 super.buildNormalContext();
092
093 if ( getInitParam( "driver" ) != null ) {
094
095 LOG.logDebug( "Build dataset table for annotations objects." );
096
097 Connection con = createConnection();
098 String sql = getInitParam( "SQLDisplayStatement" );
099 LOG.logDebug( "Clean DB Object SQL: " + sql );
100 sql = createSelectionStatement( data, sql );
101
102 List<List<Object>> result;
103
104 try {
105 result = createTable( con, sql );
106 LOG.logDebug( "Built dataset table for annotations objects: " + result );
107
108 con.close();
109 } catch ( SQLException e ) {
110 e.printStackTrace();
111 throw new PortalException( e.getMessage() );
112 }
113 request.setAttribute( "DB_OBJECTS", result );
114 }
115
116 request.setAttribute( "TITLE", getInitParam( "title" ) );
117
118 }
119
120 /**
121 * Creates the SQL select statement. The sql script is taken from the initial parameter
122 * 'SQLDisplayStatement'. Username is automotically inserted, if there is a '$USERNAME'
123 * placeholder.
124 *
125 * @param data
126 * the RunData
127 * @param sql
128 * the original sql script
129 * @return the changed sql script
130 */
131 private String createSelectionStatement( RunData data, String sql ) {
132 String username = data.getUser().getUserName();
133 sql = sql.replaceAll( "'", "\"" );
134 sql = sql.replace( "$USERNAME", "'" + username + "'" );
135 LOG.logDebug( "Populated DB Object SQL : " + sql );
136 return sql;
137 }
138
139 /**
140 * Creates a pseudo-table of results. The results are held in a list of lists. The first list
141 * has the strings describing the column names (or labels, if the use of 'as' - like in selece
142 * id as 'Identifier' has been made.
143 *
144 * @param con
145 * the connection
146 * @param sql
147 * the sql statement
148 * @return a List containing Lists of results
149 * @throws SQLException
150 */
151 private List<List<Object>> createTable( Connection con, String sql )
152 throws SQLException {
153
154 Statement stm = con.createStatement();
155 stm.execute( sql );
156 ResultSet rSet = stm.getResultSet();
157
158 List<List<Object>> rows = new ArrayList<List<Object>>();
159
160 int colCount = rSet.getMetaData().getColumnCount();
161 LOG.logDebug( "Creating dataset table for annotations objects: " + colCount );
162
163 while ( rSet.next() ) {
164 List<Object> cols = new ArrayList<Object>();
165
166 for ( int i = 0; i < colCount; i++ ) {
167 cols.add( rSet.getObject( i + 1 ) );
168 }
169 rows.add( cols );
170 }
171
172 if ( rows.size() > 0 ) {
173 // if anything found, put headers in the list
174 List<Object> cols = new ArrayList<Object>( colCount );
175 for ( int i = 0; i < colCount; i++ ) {
176 cols.add( rSet.getMetaData().getColumnLabel( i + 1 ) );
177 }
178 rows.add( 0, cols );
179 }
180 stm.close();
181 return rows;
182 }
183
184 /**
185 * Executes a delete statement.
186 *
187 * @param data
188 * the RunData
189 * @throws PortalException
190 * if something evil happened
191 */
192 public void doDeletetransaction( RunData data )
193 throws PortalException {
194
195 if ( getInitParam( "driver" ) != null ) {
196 // let us hope and say, there's a driver
197 Connection con = createConnection();
198 try {
199 String s = parameter.get( "OBJECTID" );
200 String[] featureIds = StringTools.toArray( s, ",;", true );
201 if ( featureIds == null ) {
202 LOG.logDebug( "No 'objectId' parameter in the request. Skipping..." );
203 return;
204 }
205 for ( String id : featureIds ) {
206 String sql = createDeleteSQL( data, id );
207 System.out.println( sql );
208 deleteObject( con, sql );
209 }
210
211 } catch ( SQLException e ) {
212 e.printStackTrace();
213 throw new PortalException( e.getMessage() );
214 } finally {
215 try {
216 con.close();
217 } catch ( Exception e ) {
218 e.printStackTrace();
219 }
220 }
221 }
222 }
223
224 /**
225 * Creates a delete statement based on the initial parameter 'SQLDeleteStatement' on the RunData
226 * containing the user name and on the id of the data object to be deleted.
227 *
228 * @param data
229 * the RunData object
230 * @param id
231 * the id of the dataset
232 * @return a sql with proper user name, and data set id
233 */
234 private String createDeleteSQL( RunData data, String id ) {
235
236 String sql = getInitParam( "SQLDeleteStatement" );
237 if ( data.getUser() == null ) {
238 throw new IllegalArgumentException( "RunData object has null user." );
239 }
240
241 String username = data.getUser().getUserName();
242 LOG.logDebug( "Preparing deletion of object id '" + id + "' for user: '" + username + "'" );
243
244 sql = sql.replace( "$USERNAME", "'" + username + "'" );
245 sql = sql.replace( "$OBJECT_ID", "'" + id + "'" );
246
247 LOG.logDebug( "Create SQL for deleting: " + sql );
248
249 return sql;
250 }
251
252 /**
253 *
254 * @param con
255 * @param sql
256 * @throws SQLException
257 */
258 private void deleteObject( Connection con, String sql )
259 throws SQLException {
260 Statement stmt = con.createStatement();
261 stmt.execute( sql );
262 con.commit();
263 }
264
265 /**
266 *
267 * @return the Connection
268 * @throws PortalException
269 */
270 private Connection createConnection()
271 throws PortalException {
272
273 Connection con = null;
274
275 String url = getInitParam( "url" );
276 String driver = getInitParam( "driver" );
277 String user = getInitParam( "user" );
278 String password = getInitParam( "password" );
279
280 con = initConnection( url, driver, user, password );
281
282 return con;
283 }
284
285 /**
286 *
287 * @param url
288 * @param driver
289 * @param user
290 * @param password
291 * @return the Connection
292 * @throws PortalException
293 */
294 private Connection initConnection( String url, String driver, String user, String password )
295 throws PortalException {
296 LOG.logDebug( "connecting database for insert ... " );
297
298 Connection con = null;
299
300 try {
301 Driver drv = (Driver) Class.forName( driver ).newInstance();
302 DriverManager.registerDriver( drv );
303 LOG.logDebug( "initializing connection with " + drv + " " + url + " " + user + " " + password );
304
305 con = DriverManager.getConnection( url, user, password );
306 } catch ( SQLException e ) {
307 LOG.logError( "could not establish database connection: " + url, e );
308 throw new PortalException( "could not establish database connection: " + url, e );
309 } catch ( Exception e ) {
310 LOG.logError( "could not initialize driver class: " + driver, e );
311 throw new PortalException( "could not initialize driver class: " + driver, e );
312 }
313 return con;
314 }
315 }