[Babeldoc-devel] babeldoc/modules/sql/src/com/babeldoc/sql/pipeline/stage SqlQueryPipelineStage.java,NONE,1.4.2.1

[<tr...@us...>]

Update of /cvsroot/babeldoc/babeldoc/modules/sql/src/com/babeldoc/sql/pipeline/stage
In directory sc8-pr-cvs1:/tmp/cvs-serv16961/src/com/babeldoc/sql/pipeline/stage

Added Files:
      Tag: branch_1_0
	SqlQueryPipelineStage.java 
Log Message:


--- NEW FILE: SqlQueryPipelineStage.java ---
/* ====================================================================
 * The Apache Software License, Version 1.1
 *
 * Copyright (c) 2000 The Apache Software Foundation.  All rights
 * reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. The end-user documentation included with the redistribution,
 *    if any, must include the following acknowledgment:
 *       "This product includes software developed by the
 *        Apache Software Foundation (http://www.apache.org/)."
 *    Alternately, this acknowledgment may appear in the software itself,
 *    if and wherever such third-party acknowledgments normally appear.
 *
 * 4. The names "Apache" and "Apache Software Foundation" must
 *    not be used to endorse or promote products derived from this
 *    software without prior written permission. For written
 *    permission, please contact ap...@ap....
 *
 * 5. Products derived from this software may not be called "Apache",
 *    nor may "Apache" appear in their name, without prior written
 *    permission of the Apache Software Foundation.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 * ====================================================================
 *
 * This software consists of voluntary contributions made by many
 * individuals on behalf of the Apache Software Foundation.  For more
 * information on the Apache Software Foundation, please see
 * <http://www.apache.org/>.
 *
 * Portions of this software are based upon public domain software
 * originally written at the National Center for Supercomputing Applications,
 * University of Illinois, Urbana-Champaign.
 * ====================================================================
 *
 * Babeldoc:  The Universal Document Processor
 *
 * $Header: /cvsroot/babeldoc/babeldoc/modules/sql/src/com/babeldoc/sql/pipeline/stage/SqlQueryPipelineStage.java,v 1.4.2.1 2003/08/14 04:41:25 triphop Exp $
 * $DateTime$
 * $Author: triphop $
 *
 */

package com.babeldoc.sql.pipeline.stage;

import com.babeldoc.core.I18n;
import com.babeldoc.core.LogService;
import com.babeldoc.core.NameValuePair;
import com.babeldoc.core.option.ConfigOption;
import com.babeldoc.core.option.IConfigOptionType;
import com.babeldoc.core.option.IConfigInfo;
import com.babeldoc.core.pipeline.*;
import com.babeldoc.core.resource.IResource;
import com.babeldoc.core.resource.ResourceException;
import com.babeldoc.core.resource.ResourceFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.io.StringWriter;
import java.io.IOException;

import org.dom4j.Document;
import org.dom4j.DocumentFactory;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

/**
 * Make an XML document out of a SQL query
 **/
public class SqlQueryPipelineStage
    extends PipelineStage {
  /**
   * constants
   */
  public final static String SQL = "sql";
  public final static String RESOURCE_NAME = "resourceName";

  //Connection pool
  private IResource resource = null;

  // Various constants for the xml document
  public static final String XML_MIME_TYPE = "text/xml";
  public static final String QUERY_RESULTS = "queryresults";
  public static final String QUERY = "query";
  public static final String QUERY_NAME = "query-name";
  public static final String QUERY_NUMBER = "query-number";
  public static final String ROW = "row";
  public static final String ROW_NUMBER = "row-number";
  public static final String COLUMN = "column";
  public static final String COLUMN_NAME = "column-name";
  public static final String COLUMN_NUMBER = "column-number";

   public IConfigInfo getInfo() {
     return new PipelineStageInfo() {
      public String getName() {
        return "SqlQuery";
      }

      public String getDescription() {
        return I18n.get("sql.pipeline.stage.query.desc");
      }

      public Collection getTypeSpecificOptions() {
        ArrayList options = new ArrayList();
        options.add(new ConfigOption(RESOURCE_NAME, IConfigOptionType.STRING,
            null, true, I18n.get("sql.pipeline.stage.query.option.resource")));

        options.add(new ConfigOption(SQL, IConfigOptionType.STRING,
            null, false, I18n.get("sql.pipeline.stage.query.option.sql")));

        return options;
      }
    };
  }

  /**
   * Heart of pipeline stage processing
   *
   * @return set of results from the processing
   */
  public PipelineStageResult[] process() throws PipelineException {

    // Check that the resource is set
    if (!hasOption(RESOURCE_NAME)) {
      throw new PipelineException(I18n.get("sql.201"));
    }

    NameValuePair[] queries = this.getOptionList(new String[]{SQL});
    PipelineDocument newDoc = createQueryDocument(queries);
    return super.processHelper(newDoc);
  }

  /**
   * For each of the queries provided in the name-value pair, execute the sql and create an xml
   * document with the rows and columns for each result for each query.
   *
   * @param queries
   * @return
   * @throws PipelineException
   */
  private PipelineDocument createQueryDocument(NameValuePair[] queries)
      throws PipelineException {
    Document document = DocumentFactory.getInstance().createDocument();
    Element root = document.addElement(QUERY_RESULTS);

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    String sql = null;
    try {
      conn = getConnection();

      // Iterate through sqls
      if ((queries != null) && (queries.length > 0)) {
        Element queryElement = root.addElement(QUERY);
        for (int i = 0; i < queries.length; ++i) {
          String queryName = queries[i].getName();
          queryElement.addAttribute(QUERY_NAME, queryName);
          queryElement.addAttribute(QUERY_NUMBER, Integer.toString(i));

          sql = queries[i].getValue();
          stmt = conn.createStatement();
          LogService.getInstance().logDebug("Executing " + sql);
          rs = stmt.executeQuery(sql);
          handleRows(rs, queryElement);
        }
      }
    } catch (ResourceException e) {
      throw new PipelineException(I18n.get("sql.103"), e);
    } catch (SQLException e) {
      throw new PipelineException(I18n.get("sql.104", sql), e);
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception ignoreThis) {
      }

      try {
        if (stmt != null) {
          stmt.close();
        }
      } catch (Exception ignoreThis) {
      }

      try {
        if (conn != null) {
          resource.checkIn(conn);
        }
      } catch (Exception ignoreThis) {
      }
    }
    PipelineDocument newDoc = createPipelineDocument(document);
    return newDoc;
  }

  private void handleRows(ResultSet rs, Element queryElement) throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    int rowNum = 0;
    while (rs.next()) {
      Element rowElement = queryElement.addElement(ROW);
      rowElement.addAttribute(ROW_NUMBER, Integer.toString(rowNum++));
      for (int j = 1, count = metaData.getColumnCount(); j <= count; j++) {
        String name = metaData.getColumnLabel(j).toLowerCase();
        Object value = rs.getObject(j);
        Element columnElement = rowElement.addElement(COLUMN);
        columnElement.addAttribute(COLUMN_NAME, name);
        columnElement.addAttribute(COLUMN_NUMBER, Integer.toString(j));
        columnElement.setText(value.toString());
      }
    }
  }

  private PipelineDocument createPipelineDocument(Document document) {
    OutputFormat outformat = OutputFormat.createCompactFormat();
    StringWriter stringWriter = new StringWriter();
    XMLWriter writer = new XMLWriter(stringWriter, outformat);
    try {
      writer.write(document);
      writer.flush();
    } catch (IOException e) {
    }
    PipelineDocument newDoc = new PipelineDocument(this.getDocument(), stringWriter.toString().getBytes());
    newDoc.setMimeType(XML_MIME_TYPE);
    return newDoc;
  }

  /**
   * Get the named resource name from the resource factory
   *
   * @return
   * @throws ResourceException
   */
  private Connection getConnection()
      throws ResourceException {
    if (resource == null) {
      resource = ResourceFactory.getResource(this.getOptions(RESOURCE_NAME));
    }
    if (resource == null) {
      throw new ResourceException(I18n.get("sql.103"));
    }

    return ((Connection) resource.checkOut());
  }
}