001: /* ====================================================================
002: * The Jcorporate Apache Style Software License, Version 1.2 05-07-2002
003: *
004: * Copyright (c) 1995-2002 Jcorporate Ltd. All rights reserved.
005: *
006: * Redistribution and use in source and binary forms, with or without
007: * modification, are permitted provided that the following conditions
008: * are met:
009: *
010: * 1. Redistributions of source code must retain the above copyright
011: * notice, this list of conditions and the following disclaimer.
012: *
013: * 2. Redistributions in binary form must reproduce the above copyright
014: * notice, this list of conditions and the following disclaimer in
015: * the documentation and/or other materials provided with the
016: * distribution.
017: *
018: * 3. The end-user documentation included with the redistribution,
019: * if any, must include the following acknowledgment:
020: * "This product includes software developed by Jcorporate Ltd.
021: * (http://www.jcorporate.com/)."
022: * Alternately, this acknowledgment may appear in the software itself,
023: * if and wherever such third-party acknowledgments normally appear.
024: *
025: * 4. "Jcorporate" and product names such as "Expresso" must
026: * not be used to endorse or promote products derived from this
027: * software without prior written permission. For written permission,
028: * please contact info@jcorporate.com.
029: *
030: * 5. Products derived from this software may not be called "Expresso",
031: * or other Jcorporate product names; nor may "Expresso" or other
032: * Jcorporate product names appear in their name, without prior
033: * written permission of Jcorporate Ltd.
034: *
035: * 6. No product derived from this software may compete in the same
036: * market space, i.e. framework, without prior written permission
037: * of Jcorporate Ltd. For written permission, please contact
038: * partners@jcorporate.com.
039: *
040: * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
041: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
042: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
043: * DISCLAIMED. IN NO EVENT SHALL JCORPORATE LTD OR ITS CONTRIBUTORS
044: * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
045: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
046: * TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
047: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
048: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
049: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
050: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
051: * SUCH DAMAGE.
052: * ====================================================================
053: *
054: * This software consists of voluntary contributions made by many
055: * individuals on behalf of the Jcorporate Ltd. Contributions back
056: * to the project(s) are encouraged when you make modifications.
057: * Please send them to support@jcorporate.com. For more information
058: * on Jcorporate Ltd. and its products, please see
059: * <http://www.jcorporate.com/>.
060: *
061: * Portions of this software are based upon other open source
062: * products and are subject to their respective licenses.
063: */
064:
065: package com.jcorporate.expresso.ext.controller;
066:
067: import com.jcorporate.expresso.core.controller.ErrorCollection;
068: import com.jcorporate.expresso.core.controller.Block;
069: import com.jcorporate.expresso.core.controller.ControllerException;
070: import com.jcorporate.expresso.core.controller.ControllerRequest;
071: import com.jcorporate.expresso.core.controller.ControllerResponse;
072: import com.jcorporate.expresso.core.controller.DBController;
073: import com.jcorporate.expresso.core.controller.Input;
074: import com.jcorporate.expresso.core.controller.Output;
075: import com.jcorporate.expresso.core.controller.State;
076: import com.jcorporate.expresso.core.controller.Transition;
077: import com.jcorporate.expresso.core.db.DBConnection;
078: import com.jcorporate.expresso.core.db.DBConnectionPool;
079: import com.jcorporate.expresso.core.db.DBException;
080: import com.jcorporate.expresso.core.dbobj.ValidValue;
081: import com.jcorporate.expresso.core.misc.StringUtil;
082: import java.util.StringTokenizer;
083: import java.sql.ResultSetMetaData;
084: import java.util.Enumeration;
085: import java.util.Vector;
086: import com.jcorporate.expresso.core.controller.NonHandleableException;
087: import java.sql.*;
088:
089: /**
090: * RunSQL allows any arbitrary SQL string to be sent to the database & executed
091: * Must be carefully secured!
092: *
093: * @author Michael Nash
094: */
095: public class RunSQL extends DBController {
096:
097: /**
098: * Default constructor.
099: */
100: public RunSQL() {
101: State prompt = new State("prompt", "Prompt for Query");
102: addState(prompt);
103: setInitialState("prompt");
104:
105: State run = new State("run", "Run Query");
106: run.addRequiredParameter("SQLQuery");
107: addState(run);
108: this
109: .setSchema(com.jcorporate.expresso.core.ExpressoSchema.class);
110:
111: State definition = new State("getDefinition",
112: "Get Table Definition");
113: definition.addRequiredParameter("SQLQuery");
114: addState(definition);
115: }
116:
117: /**
118: * Present a form requesting the SQL to be executed
119: *
120: * @param request Standard request object
121: * @param response Standard response object
122: * @throws ControllerException upon error
123: */
124: public void runPromptState(ControllerRequest request,
125: ControllerResponse response) throws ControllerException {
126: Input query = new Input("SQLQuery");
127: query.setLabel("Enter Query");
128: query.setAttribute("type", "text");
129: query.setAttribute("textarea", "Y");
130: response.addInput(query);
131:
132: Input style = new Input();
133: style.setLabel("Choose Style Of Results");
134: style.setName("styleType");
135:
136: Vector v2 = new Vector();
137: v2.addElement(new ValidValue("", "Default"));
138: v2.addElement(new ValidValue("xml", "XML Document"));
139: v2.addElement(new ValidValue("excel", "Excel Spreadsheet"));
140: style.setValidValues(v2);
141: response.addInput(style);
142:
143: response.addTransition(new Transition("run", this ));
144: response.addTransition(new Transition("getDefinition", this ));
145: }
146:
147: /**
148: * Prints the definition of a given table.
149: * @param request ControllerRequest
150: * @param response ControllerResponse
151: * @throws ControllerException upon error.
152: * @throws NonHandleableException upon fatal error.
153: */
154: public void runGetDefinitionState(final ControllerRequest request,
155: final ControllerResponse response)
156: throws ControllerException, NonHandleableException {
157: String tableName = request.getParameter("SQLQuery");
158: if (tableName == null || tableName.length() == 0) {
159: response.addError("You need to enter a table name in the "
160: + "'sql query' input to get the definition");
161: }
162:
163: try {
164: response.add(getTableDefinition(request.getDataContext(),
165: tableName));
166: } catch (DBException ex) {
167: throw new ControllerException(
168: "Database Error querying table structure: "
169: + response);
170: }
171:
172: }
173:
174: /**
175: * Builds a table definition and enters it into a block.
176: * <p>(c) Note: Initial code (c)2004, Centerline Computers Inc
177: * , Donated to the Expresso Framework project.</p>
178: * @param dataContext String the database context.
179: * @param def String the table name.
180: * @return Block populated block with results.
181: * @throws DBException upon error.
182: */
183: public Block getTableDefinition(String dataContext, String def)
184: throws DBException {
185: String tableName = StringUtil.replace(def, "\r\n", "");
186: Block b = new Block("definition");
187: b.add(new Output("Table Definition for table: " + def));
188: Block resultTable = new Block("table");
189: resultTable.setAttribute("table", "Y");
190: resultTable.setAttribute("header-row",
191: "Field Name|Type|Null Allowed");
192: b.add(resultTable);
193:
194: DBConnection connection = DBConnectionPool.getInstance(
195: dataContext).getConnection("Table Definition Query");
196: try {
197: DatabaseMetaData metadata = connection.getDBMetaData();
198: ResultSet rs = metadata.getColumns(null, null, tableName,
199: null);
200: if (rs == null) {
201: resultTable.add(new Output(
202: "No Table Definition Found for table name: "
203: + def));
204: }
205: while (rs.next()) {
206: Block oneRow = new Block();
207: resultTable.add(oneRow);
208: oneRow.setAttribute("row", "Y");
209: oneRow.add(new Output(rs.getString(4))); //Column Name
210: String columnType = rs.getString(6); //Table Name
211: int dataSize = rs.getInt(7); //Field Size
212: int decimalSize = rs.getInt(9); //Decimal Size.
213: if (dataSize > 0) {
214: if (decimalSize > 0) {
215: columnType = columnType + "(" + dataSize + ","
216: + decimalSize + ")";
217: } else {
218: columnType = columnType + "(" + dataSize + ")";
219: }
220: }
221: oneRow.add(new Output(columnType));
222: int nullable = rs.getInt(11); //Is Nullable
223: switch (nullable) {
224: case DatabaseMetaData.columnNoNulls:
225: oneRow.add(new Output("No"));
226: break;
227:
228: case DatabaseMetaData.columnNullable:
229: oneRow.add(new Output("Yes"));
230: break;
231:
232: case DatabaseMetaData.columnNullableUnknown:
233: oneRow.add(new Output("Unknown"));
234: break;
235:
236: }
237: }
238: } catch (java.sql.SQLException e) {
239: throw new DBException(
240: "Error querying database for table definition", e);
241: } finally {
242: connection.release();
243: }
244:
245: return b;
246: }
247:
248: /**
249: * Receive the SQL request from the client & send it to the database for
250: * execution. Present the results to the client.
251: *
252: * @param req Standard request object
253: * @param res Standard response object
254: * @throws ControllerException upon error
255: */
256: public void runRunState(ControllerRequest req,
257: ControllerResponse res) throws ControllerException {
258: DBConnection myConnection = null;
259: DBConnectionPool myPool = null;
260: String styleString = StringUtil.notNull(req
261: .getParameter("styleType"));
262:
263: if (styleString.length() > 0) {
264: req.setParameter("style", styleString);
265:
266: if (styleString.equals("xml")) {
267: req.setParameter("xsl", "none");
268: }
269: }
270: try {
271: myPool = DBConnectionPool.getInstance(req.getDataContext());
272: myConnection = myPool.getConnection("RunSQLQuery");
273:
274: String queryToRun = StringUtil.notNull(req
275: .getParameter("SQLQuery"));
276:
277: if (queryToRun.equals("")) {
278: ErrorCollection ec = new ErrorCollection();
279: ec.addError("No Query To Run");
280: res.saveErrors(ec);
281: transition("prompt", req, res);
282: return;
283: }
284:
285: res.add(new Output("query", queryToRun));
286: boolean isSelected = executeQuery(myConnection, queryToRun);
287:
288: if (isSelected) {
289: int columnCount;
290: Vector columnNames = new Vector(5);
291: Vector columnTitles = new Vector(5);
292:
293: try {
294: ResultSetMetaData myMeta = myConnection
295: .getMetaData();
296:
297: if (myMeta != null) {
298: columnCount = myMeta.getColumnCount();
299:
300: for (int i = 1; i <= columnCount; i++) {
301: columnNames.addElement(myMeta
302: .getColumnName(i));
303: columnTitles.addElement(myMeta
304: .getColumnLabel(i));
305: }
306: } else {
307: throw new DBException(
308: "Unable to get meta-data from query");
309: }
310: } catch (java.sql.SQLException se) {
311: throw new DBException("Error getting meta-data", se);
312: }
313:
314: Block resultTable = new Block("result table");
315: resultTable.setAttribute("table", "Y");
316: res.add(resultTable);
317:
318: int column = 1;
319:
320: /* if the titles are all exactly the same as the column names, */
321:
322: /* don't bother printing them */
323: Block oneRow = buildResultHeaderRow(columnNames,
324: columnTitles, resultTable);
325:
326: while (myConnection.next()) {
327: oneRow = new Block();
328: oneRow.setAttribute("row", "Y");
329: resultTable.add(oneRow);
330:
331: for (column = 1; column <= columnCount; column++) {
332: oneRow.add(new Output(
333: StringUtil.notNull(myConnection
334: .getString(column))));
335: }
336: }
337: } else {
338: res.add(new Output("Database successfully updated:"));
339: res.add(new Output("Rows Affected: "
340: + myConnection.getUpdateCount()));
341: res
342: .add(new Output(
343: "Enter the table name and hit 'Get Table Definition' to see the new definition"));
344:
345: }
346: } catch (Exception de) {
347: throw new ControllerException(de);
348: } finally {
349: if (myPool != null && myConnection != null) {
350: myPool.release(myConnection);
351: }
352: }
353: }
354:
355: /**
356: * Builds a header row from the database query.
357: * @param columnNames Vector the colum names.
358: * @param columnTitles Vector the column titles.
359: * @param resultTable Block the result block we're adding to.
360: * @return Block the header block.
361: */
362: private Block buildResultHeaderRow(Vector columnNames,
363: Vector columnTitles, Block resultTable) {
364: boolean dupTitles = true;
365: String oneName = null;
366: String oneTitle = null;
367: Enumeration e2 = columnNames.elements();
368:
369: for (Enumeration e1 = columnTitles.elements(); e1
370: .hasMoreElements();) {
371: oneName = (String) e2.nextElement();
372: oneTitle = (String) e1.nextElement();
373:
374: if (!oneName.equalsIgnoreCase(oneTitle)) {
375: dupTitles = false;
376: }
377: }
378:
379: if (!dupTitles) {
380:
381: StringBuffer titleString = new StringBuffer();
382: boolean needPipe = false;
383:
384: for (Enumeration eTitles = columnTitles.elements(); eTitles
385: .hasMoreElements();) {
386: if (needPipe) {
387: titleString.append("|");
388: } else {
389: needPipe = true;
390: }
391:
392: titleString.append((String) eTitles.nextElement());
393: }
394:
395: resultTable.setAttribute("header-row", titleString
396: .toString());
397: }
398: /* dupTitles */
399:
400: StringBuffer headerString = new StringBuffer();
401: boolean needPipe = false;
402:
403: for (Enumeration eNames = columnNames.elements(); eNames
404: .hasMoreElements();) {
405: if (needPipe) {
406: headerString.append("|");
407: } else {
408: needPipe = true;
409: }
410:
411: headerString.append((String) eNames.nextElement());
412: }
413:
414: resultTable.setAttribute("header-row", headerString.toString());
415: return resultTable;
416: }
417:
418: /**
419: * Execute Query and return true if it is a selection query.
420: * @param myConnection DBConnection upon execution error.
421: * @param queryToRun String the query string to execute.
422: * @return boolean true if the statement was a selection query.
423: * @throws DBException
424: */
425: private boolean executeQuery(DBConnection myConnection,
426: String queryToRun) throws DBException {
427: String firstToken = (new StringTokenizer(queryToRun))
428: .nextToken();
429: if ("SELECT".equalsIgnoreCase(firstToken)) {
430: myConnection.execute(queryToRun);
431: return true;
432: } else {
433: myConnection.executeUpdate(queryToRun);
434: return false;
435: }
436: }
437:
438: /**
439: * @return java.lang.String The Title of the controller
440: */
441: public String getTitle() {
442: return ("Run SQL Query");
443: }
444:
445: }
446:
447: /* RunSQL */
|