RELEASE_2_1_13/src/blocks/databases/java/org/apache/cocoon/acting/DatabaseAddAction.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.apache.cocoon.acting;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.SortedSet;
import java.util.TreeSet;

import org.apache.avalon.excalibur.datasource.DataSourceComponent;
import org.apache.avalon.framework.configuration.Configuration;
import org.apache.avalon.framework.configuration.ConfigurationException;
import org.apache.avalon.framework.parameters.Parameters;
import org.apache.avalon.framework.thread.ThreadSafe;
import org.apache.cocoon.Constants;
import org.apache.cocoon.ProcessingException;
import org.apache.cocoon.environment.ObjectModelHelper;
import org.apache.cocoon.environment.Redirector;
import org.apache.cocoon.environment.Request;
import org.apache.cocoon.environment.SourceResolver;
import org.apache.commons.lang.StringUtils;

/**
 * Adds record in a database. The action can update one or more tables,
 * and can add more than one row to a table at a time. The form descriptor
 * semantics for this are still in a bit of a state of flux. Note
 * that if a secondary table relies on the value of a new primary key in a
 * primary table, the primary key must be created using manual mode.
 *
 * @author <a href="mailto:bloritsch@apache.org">Berin Loritsch</a>
 * @author <a href="mailto:balld@apache.org">Donald Ball</a>
 * @version CVS $Id$
 */
public class DatabaseAddAction extends AbstractDatabaseAction implements ThreadSafe {
    protected static final Map addStatements = new HashMap();
    private static final Map selectStatements = new HashMap();

    /**
     * Add a record to the database.  This action assumes that
     * the file referenced by the "descriptor" parameter conforms
     * to the AbstractDatabaseAction specifications.
     */
    public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String source, Parameters param) throws Exception {
        DataSourceComponent datasource = null;
        Connection conn = null;
        Map results = new HashMap();

        // read global parameter settings
        boolean reloadable = Constants.DESCRIPTOR_RELOADABLE_DEFAULT;
        if (this.settings.containsKey("reloadable"))
            reloadable = Boolean.valueOf((String) this.settings.get("reloadable")).booleanValue();
        // read local parameter settings
        try {
            Configuration conf =
                this.getConfiguration(param.getParameter("descriptor", (String) this.settings.get("descriptor")), resolver,
                                      param.getParameterAsBoolean("reloadable",reloadable));

            datasource = this.getDataSource(conf);
            conn = datasource.getConnection();
            Request request = ObjectModelHelper.getRequest(objectModel);

            if (conn.getAutoCommit()) {
                conn.setAutoCommit(false);
            }

            Configuration[] tables = conf.getChildren("table");
            for (int i=0; i<tables.length; i++) {
              Configuration table = tables[i];
              processTable(table,conn,request,results);
            }
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException se) {
                    getLogger().debug("There was an error rolling back the transaction", se);
                }
            }

            //throw new ProcessingException("Could not add record :position = " + currentIndex, e);
            throw new ProcessingException("Could not add record",e);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqe) {
                    getLogger().warn("There was an error closing the datasource", sqe);
                }
            }

            if (datasource != null) this.dbselector.release(datasource);
        }

        return Collections.unmodifiableMap(results);
    }

    /**
     * Inserts a row or a set of rows into the given table based on the
     * request parameters
     *
     * @param table the table's configuration
     * @param conn the database connection
     * @param request the request
     */
    void processTable(Configuration table, Connection conn, Request request, Map results) throws SQLException,ConfigurationException,Exception {
      PreparedStatement statement = null;
      try {
        String query = this.getAddQuery(table);
        getLogger().debug("Add query: "+query);
        statement = conn.prepareStatement(query);
        Configuration[] keys = table.getChild("keys").getChildren("key");
        Configuration[] values = table.getChild("values").getChildren("value");
        int currentIndex = 1;
        boolean manyrows = false;
        int wildcardIndex = -1;
        String wildcardParam = null;
        for (int i=0; i<keys.length; i++) {
          wildcardParam = keys[i].getAttribute("param");
          if ((wildcardIndex = wildcardParam.indexOf('*')) != -1) {
            manyrows = true;
            break;
          }
        }
        if (manyrows) {
          /**
           * This table has a column with a wildcard, so we're going
           * to be inserting n rows, where 0 <= n
           */
          String prefix = wildcardParam.substring(0,wildcardIndex);
          String suffix = StringUtils.substring(wildcardParam, wildcardIndex + 1);
          Enumeration names = request.getParameterNames();
          SortedSet matchset = new TreeSet();
          int prefixLength = prefix.length();
          int length = prefixLength + suffix.length();
          while (names.hasMoreElements()) {
            String name = (String)names.nextElement();
            if (name.startsWith(prefix) && name.endsWith(suffix)) {
              String wildcard = StringUtils.mid(name, prefixLength, name.length() - length);
              matchset.add(wildcard);
            }
          }
          int rowIndex = 1;
          Iterator iterator = matchset.iterator();
          while (iterator.hasNext()) {
            String wildcard = (String)iterator.next();
            currentIndex = 1;
            for (int j=0; j<keys.length; j++) {
              String myparam = getActualParam(keys[j].getAttribute("param"),wildcard);
              currentIndex += setKey(table,keys[j],conn,statement,currentIndex,request,myparam,results);
            }
            for (int j=0; j<values.length; j++) {
              String myparam = getActualParam(values[j].getAttribute("param"),wildcard);
              this.setColumn(statement,currentIndex,request,values[j],myparam,request.getParameter(myparam),rowIndex);
              currentIndex++;
            }
            statement.execute();
            rowIndex++;
          }
        } else {
          /**
           * This table has no wildcard columns, so we're going to
           * be inserting 1 row.
           */
          for (int i = 0; i < keys.length; i++) {
            currentIndex += setKey(table,keys[i],conn,statement,currentIndex,request,keys[i].getAttribute("param",""),results);
          }
          for (int i = 0; i < values.length; i++, currentIndex++) {
            this.setColumn(statement, currentIndex, request, values[i]);
          }
          statement.execute();
          /** Done processing table **/
        }
      } finally {
        try {
          if (statement != null) {
            statement.close();
          }
        } catch (SQLException e) {}
      }
    }

    /**
     * Sets the key value on the prepared statement. There are four modes:
     *
     * <dl>
     *   <dt>automatic (default)</dt>
     *   <dd>let the database automatically create the key. note this
     *       prohibits the action from storing the key value anywhere.</dd>
     *   <dt>manual</dt>
     *   <dd>create the key value using SELECT(dbcol)+1 from TABLE</dd>
     *   <dt>form</dt>
     *   <dd>look for the key value in the request parameters</dd>
     *   <dt>request-attribute</dt>
     *   <dd>look for the key value in the request attributes</dd>
     * </dl>
     *
     * This method has a couple of side effects. If the mode is manual,
     * the key value is stored in the request object's attributes for use
     * by other inserts. The key is the string "key:TABLENAME:DBCOL".
     * This method also puts the value of manually created keys in the results
     * map. That key is simply the value of the dbcol attribute. Note this
     * stuff is definitely up in the air.
     *
     * @param table the table's configuration object
     * @param key the key's configuration object
     * @param conn the database connection
     * @param statement the insert statement
     * @param currentIndex the position of the key column
     * @param request the request object
     * @param param the actual name of the request parameter
     * @return the number of columns by which to increment the currentIndex
     */
    int setKey(Configuration table, Configuration key, Connection conn, PreparedStatement statement, int currentIndex, Request request, String param, Map results) throws ConfigurationException, SQLException,Exception {
      String mode = key.getAttribute("mode","automatic");
      String keyname = new StringBuffer("key:").append(table.getAttribute("name"))
                           .append(':').append(key.getAttribute("dbcol")).toString();
      if ("manual".equals(mode)) {
        // Set the key value using SELECT MAX(keyname)+1
        String selectQuery = this.getSelectQuery(key);
        PreparedStatement select_statement = conn.prepareStatement(selectQuery);
        ResultSet set = select_statement.executeQuery();
        set.next();
        int value = set.getInt("maxid") + 1;
        statement.setInt(currentIndex, value);
        getLogger().debug("Manually setting key to " + value);
        setRequestAttribute(request,keyname,new Integer(value));
        results.put(key.getAttribute("dbcol"),String.valueOf(value));
        set.close();
        select_statement.close();
      } else if ("form".equals(mode)) {
        // Set the key value from the request
        getLogger().debug("Setting key from form");
        this.setColumn(statement, currentIndex, request, key, param);
      } else if ("request-attribute".equals(mode)) {
        Integer value = (Integer)getRequestAttribute(request,key.getAttribute("request-attribute-name"));
        getLogger().debug("Setting key from request attribute "+value);
        statement.setInt(currentIndex,value.intValue());
      } else {
        getLogger().debug("Automatically setting key");
        // The database automatically creates a key value
        return 0;
      }
      return 1;
    }

    /**
     * Returns the actual name of the parameter. If the name contains
     * no wildcard, the param is returned untouched, otherwise the
     * wildcard value is substituted for the * character. This probably
     * doesn't deserve a method unto itself, but I can imagine wanting
     * to use a more sophisticated matching and substitution algorithm.
     *
     * @param param the name of the parameter, possibly with a wildcard char
     * @param wildcard the wildcard value
     * @return the actual name of the parameter
     */
    String getActualParam(String param, String wildcard) {
      int index;
      if ((index = param.indexOf('*')) != -1) {
        return param.substring(0,index)+wildcard+param.substring(index+1);
      } else {
        return param;
      }
    }

    /**
     * Get the String representation of the PreparedStatement.  This is
     * mapped to the Configuration object itself, so if it doesn't exist,
     * it will be created.
     *
     * @param table the table's configuration object
     * @return the insert query as a string
     */
    protected String getAddQuery(Configuration table) throws ConfigurationException {
        String query = null;
        synchronized (DatabaseAddAction.addStatements) {
            query = (String) DatabaseAddAction.addStatements.get(table);
            if (query == null) {
                Configuration[] values = table.getChild("values").getChildren("value");
                Configuration[] keys = table.getChild("keys").getChildren("key");

                StringBuffer queryBuffer = new StringBuffer("INSERT INTO ");
                queryBuffer.append(table.getAttribute("name"));
                queryBuffer.append(" (");

                int numParams = 0;

                for (int i = 0; i < keys.length; i++) {
                    String mode = keys[i].getAttribute("mode", "automatic");
                    if ("manual".equals(mode) || "form".equals(mode) || "request-attribute".equals(mode)) {
                        if (numParams > 0) {
                            queryBuffer.append(", ");
                        }
                        queryBuffer.append(keys[i].getAttribute("dbcol"));
                        this.setSelectQuery(table.getAttribute("name"), keys[i]);
                        numParams++;
                    }
                }
                queryBuffer.append(buildList(values, numParams));
                numParams += values.length;
                queryBuffer.append(") VALUES (");
                if (numParams > 0) {
                    queryBuffer.append("?");
                    queryBuffer.append(StringUtils.repeat(", ?", numParams - 1));
                }
                queryBuffer.append(")");
                query = queryBuffer.toString();

                DatabaseAddAction.addStatements.put(table, query);
            }
        }
        return query;
    }

    /**
     * Set the String representation of the MaxID lookup statement.  This is
     * mapped to the Configuration object itself, so if it doesn't exist,
     * it will be created.
     */
    protected final synchronized void setSelectQuery(String tableName, Configuration entry) throws ConfigurationException {
        StringBuffer queryBuffer = new StringBuffer("SELECT max(");
        queryBuffer.append(entry.getAttribute("dbcol"));
        queryBuffer.append(") AS maxid FROM ");
        queryBuffer.append(tableName);

        DatabaseAddAction.selectStatements.put(entry, queryBuffer.toString());
    }

    protected final synchronized String getSelectQuery(Configuration entry) throws ConfigurationException {
        return (String) DatabaseAddAction.selectStatements.get(entry);
    }
}