001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017: package org.apache.cocoon.acting;
018:
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.ResultSet;
022: import java.sql.SQLException;
023: import java.util.Collections;
024: import java.util.Enumeration;
025: import java.util.HashMap;
026: import java.util.Iterator;
027: import java.util.Map;
028: import java.util.SortedSet;
029: import java.util.TreeSet;
030:
031: import org.apache.avalon.excalibur.datasource.DataSourceComponent;
032: import org.apache.avalon.framework.configuration.Configuration;
033: import org.apache.avalon.framework.configuration.ConfigurationException;
034: import org.apache.avalon.framework.parameters.Parameters;
035: import org.apache.avalon.framework.thread.ThreadSafe;
036: import org.apache.cocoon.Constants;
037: import org.apache.cocoon.ProcessingException;
038: import org.apache.cocoon.environment.ObjectModelHelper;
039: import org.apache.cocoon.environment.Redirector;
040: import org.apache.cocoon.environment.Request;
041: import org.apache.cocoon.environment.SourceResolver;
042: import org.apache.commons.lang.StringUtils;
043:
044: /**
045: * Adds record in a database. The action can update one or more tables,
046: * and can add more than one row to a table at a time. The form descriptor
047: * semantics for this are still in a bit of a state of flux. Note
048: * that if a secondary table relies on the value of a new primary key in a
049: * primary table, the primary key must be created using manual mode.
050: *
051: * @author <a href="mailto:bloritsch@apache.org">Berin Loritsch</a>
052: * @author <a href="mailto:balld@apache.org">Donald Ball</a>
053: * @version CVS $Id: DatabaseAddAction.java 433543 2006-08-22 06:22:54Z crossley $
054: */
055: public class DatabaseAddAction extends AbstractDatabaseAction implements
056: ThreadSafe {
057: protected static final Map addStatements = new HashMap();
058: private static final Map selectStatements = new HashMap();
059:
060: /**
061: * Add a record to the database. This action assumes that
062: * the file referenced by the "descriptor" parameter conforms
063: * to the AbstractDatabaseAction specifications.
064: */
065: public Map act(Redirector redirector, SourceResolver resolver,
066: Map objectModel, String source, Parameters param)
067: throws Exception {
068: DataSourceComponent datasource = null;
069: Connection conn = null;
070: Map results = new HashMap();
071:
072: // read global parameter settings
073: boolean reloadable = Constants.DESCRIPTOR_RELOADABLE_DEFAULT;
074: if (this .settings.containsKey("reloadable"))
075: reloadable = Boolean.valueOf(
076: (String) this .settings.get("reloadable"))
077: .booleanValue();
078: // read local parameter settings
079: try {
080: Configuration conf = this .getConfiguration(param
081: .getParameter("descriptor", (String) this .settings
082: .get("descriptor")), resolver, param
083: .getParameterAsBoolean("reloadable", reloadable));
084:
085: datasource = this .getDataSource(conf);
086: conn = datasource.getConnection();
087: Request request = ObjectModelHelper.getRequest(objectModel);
088:
089: if (conn.getAutoCommit()) {
090: conn.setAutoCommit(false);
091: }
092:
093: Configuration[] tables = conf.getChildren("table");
094: for (int i = 0; i < tables.length; i++) {
095: Configuration table = tables[i];
096: processTable(table, conn, request, results);
097: }
098: conn.commit();
099: } catch (Exception e) {
100: if (conn != null) {
101: try {
102: conn.rollback();
103: } catch (SQLException se) {
104: getLogger()
105: .debug(
106: "There was an error rolling back the transaction",
107: se);
108: }
109: }
110:
111: //throw new ProcessingException("Could not add record :position = " + currentIndex, e);
112: throw new ProcessingException("Could not add record", e);
113: } finally {
114: if (conn != null) {
115: try {
116: conn.close();
117: } catch (SQLException sqe) {
118: getLogger()
119: .warn(
120: "There was an error closing the datasource",
121: sqe);
122: }
123: }
124:
125: if (datasource != null)
126: this .dbselector.release(datasource);
127: }
128:
129: return Collections.unmodifiableMap(results);
130: }
131:
132: /**
133: * Inserts a row or a set of rows into the given table based on the
134: * request parameters
135: *
136: * @param table the table's configuration
137: * @param conn the database connection
138: * @param request the request
139: */
140: void processTable(Configuration table, Connection conn,
141: Request request, Map results) throws SQLException,
142: ConfigurationException, Exception {
143: PreparedStatement statement = null;
144: try {
145: String query = this .getAddQuery(table);
146: getLogger().debug("Add query: " + query);
147: statement = conn.prepareStatement(query);
148: Configuration[] keys = table.getChild("keys").getChildren(
149: "key");
150: Configuration[] values = table.getChild("values")
151: .getChildren("value");
152: int currentIndex = 1;
153: boolean manyrows = false;
154: int wildcardIndex = -1;
155: String wildcardParam = null;
156: for (int i = 0; i < keys.length; i++) {
157: wildcardParam = keys[i].getAttribute("param");
158: if ((wildcardIndex = wildcardParam.indexOf('*')) != -1) {
159: manyrows = true;
160: break;
161: }
162: }
163: if (manyrows) {
164: /**
165: * This table has a column with a wildcard, so we're going
166: * to be inserting n rows, where 0 <= n
167: */
168: String prefix = wildcardParam.substring(0,
169: wildcardIndex);
170: String suffix = StringUtils.substring(wildcardParam,
171: wildcardIndex + 1);
172: Enumeration names = request.getParameterNames();
173: SortedSet matchset = new TreeSet();
174: int prefixLength = prefix.length();
175: int length = prefixLength + suffix.length();
176: while (names.hasMoreElements()) {
177: String name = (String) names.nextElement();
178: if (name.startsWith(prefix)
179: && name.endsWith(suffix)) {
180: String wildcard = StringUtils.mid(name,
181: prefixLength, name.length() - length);
182: matchset.add(wildcard);
183: }
184: }
185: int rowIndex = 1;
186: Iterator iterator = matchset.iterator();
187: while (iterator.hasNext()) {
188: String wildcard = (String) iterator.next();
189: currentIndex = 1;
190: for (int j = 0; j < keys.length; j++) {
191: String myparam = getActualParam(keys[j]
192: .getAttribute("param"), wildcard);
193: currentIndex += setKey(table, keys[j], conn,
194: statement, currentIndex, request,
195: myparam, results);
196: }
197: for (int j = 0; j < values.length; j++) {
198: String myparam = getActualParam(values[j]
199: .getAttribute("param"), wildcard);
200: this .setColumn(statement, currentIndex,
201: request, values[j], myparam, request
202: .getParameter(myparam),
203: rowIndex);
204: currentIndex++;
205: }
206: statement.execute();
207: rowIndex++;
208: }
209: } else {
210: /**
211: * This table has no wildcard columns, so we're going to
212: * be inserting 1 row.
213: */
214: for (int i = 0; i < keys.length; i++) {
215: currentIndex += setKey(table, keys[i], conn,
216: statement, currentIndex, request, keys[i]
217: .getAttribute("param", ""), results);
218: }
219: for (int i = 0; i < values.length; i++, currentIndex++) {
220: this .setColumn(statement, currentIndex, request,
221: values[i]);
222: }
223: statement.execute();
224: /** Done processing table **/
225: }
226: } finally {
227: try {
228: if (statement != null) {
229: statement.close();
230: }
231: } catch (SQLException e) {
232: }
233: }
234: }
235:
236: /**
237: * Sets the key value on the prepared statement. There are four modes:
238: *
239: * <dl>
240: * <dt>automatic (default)</dt>
241: * <dd>let the database automatically create the key. note this
242: * prohibits the action from storing the key value anywhere.</dd>
243: * <dt>manual</dt>
244: * <dd>create the key value using SELECT(dbcol)+1 from TABLE</dd>
245: * <dt>form</dt>
246: * <dd>look for the key value in the request parameters</dd>
247: * <dt>request-attribute</dt>
248: * <dd>look for the key value in the request attributes</dd>
249: * </dl>
250: *
251: * This method has a couple of side effects. If the mode is manual,
252: * the key value is stored in the request object's attributes for use
253: * by other inserts. The key is the string "key:TABLENAME:DBCOL".
254: * This method also puts the value of manually created keys in the results
255: * map. That key is simply the value of the dbcol attribute. Note this
256: * stuff is definitely up in the air.
257: *
258: * @param table the table's configuration object
259: * @param key the key's configuration object
260: * @param conn the database connection
261: * @param statement the insert statement
262: * @param currentIndex the position of the key column
263: * @param request the request object
264: * @param param the actual name of the request parameter
265: * @return the number of columns by which to increment the currentIndex
266: */
267: int setKey(Configuration table, Configuration key, Connection conn,
268: PreparedStatement statement, int currentIndex,
269: Request request, String param, Map results)
270: throws ConfigurationException, SQLException, Exception {
271: String mode = key.getAttribute("mode", "automatic");
272: String keyname = new StringBuffer("key:").append(
273: table.getAttribute("name")).append(':').append(
274: key.getAttribute("dbcol")).toString();
275: if ("manual".equals(mode)) {
276: // Set the key value using SELECT MAX(keyname)+1
277: String selectQuery = this .getSelectQuery(key);
278: PreparedStatement select_statement = conn
279: .prepareStatement(selectQuery);
280: ResultSet set = select_statement.executeQuery();
281: set.next();
282: int value = set.getInt("maxid") + 1;
283: statement.setInt(currentIndex, value);
284: getLogger().debug("Manually setting key to " + value);
285: setRequestAttribute(request, keyname, new Integer(value));
286: results.put(key.getAttribute("dbcol"), String
287: .valueOf(value));
288: set.close();
289: select_statement.close();
290: } else if ("form".equals(mode)) {
291: // Set the key value from the request
292: getLogger().debug("Setting key from form");
293: this
294: .setColumn(statement, currentIndex, request, key,
295: param);
296: } else if ("request-attribute".equals(mode)) {
297: Integer value = (Integer) getRequestAttribute(request, key
298: .getAttribute("request-attribute-name"));
299: getLogger().debug(
300: "Setting key from request attribute " + value);
301: statement.setInt(currentIndex, value.intValue());
302: } else {
303: getLogger().debug("Automatically setting key");
304: // The database automatically creates a key value
305: return 0;
306: }
307: return 1;
308: }
309:
310: /**
311: * Returns the actual name of the parameter. If the name contains
312: * no wildcard, the param is returned untouched, otherwise the
313: * wildcard value is substituted for the * character. This probably
314: * doesn't deserve a method unto itself, but I can imagine wanting
315: * to use a more sophisticated matching and substitution algorithm.
316: *
317: * @param param the name of the parameter, possibly with a wildcard char
318: * @param wildcard the wildcard value
319: * @return the actual name of the parameter
320: */
321: String getActualParam(String param, String wildcard) {
322: int index;
323: if ((index = param.indexOf('*')) != -1) {
324: return param.substring(0, index) + wildcard
325: + param.substring(index + 1);
326: } else {
327: return param;
328: }
329: }
330:
331: /**
332: * Get the String representation of the PreparedStatement. This is
333: * mapped to the Configuration object itself, so if it doesn't exist,
334: * it will be created.
335: *
336: * @param table the table's configuration object
337: * @return the insert query as a string
338: */
339: protected String getAddQuery(Configuration table)
340: throws ConfigurationException {
341: String query = null;
342: synchronized (DatabaseAddAction.addStatements) {
343: query = (String) DatabaseAddAction.addStatements.get(table);
344: if (query == null) {
345: Configuration[] values = table.getChild("values")
346: .getChildren("value");
347: Configuration[] keys = table.getChild("keys")
348: .getChildren("key");
349:
350: StringBuffer queryBuffer = new StringBuffer(
351: "INSERT INTO ");
352: queryBuffer.append(table.getAttribute("name"));
353: queryBuffer.append(" (");
354:
355: int numParams = 0;
356:
357: for (int i = 0; i < keys.length; i++) {
358: String mode = keys[i].getAttribute("mode",
359: "automatic");
360: if ("manual".equals(mode) || "form".equals(mode)
361: || "request-attribute".equals(mode)) {
362: if (numParams > 0) {
363: queryBuffer.append(", ");
364: }
365: queryBuffer.append(keys[i]
366: .getAttribute("dbcol"));
367: this .setSelectQuery(table.getAttribute("name"),
368: keys[i]);
369: numParams++;
370: }
371: }
372: queryBuffer.append(buildList(values, numParams));
373: numParams += values.length;
374: queryBuffer.append(") VALUES (");
375: if (numParams > 0) {
376: queryBuffer.append("?");
377: queryBuffer.append(StringUtils.repeat(", ?",
378: numParams - 1));
379: }
380: queryBuffer.append(")");
381: query = queryBuffer.toString();
382:
383: DatabaseAddAction.addStatements.put(table, query);
384: }
385: }
386: return query;
387: }
388:
389: /**
390: * Set the String representation of the MaxID lookup statement. This is
391: * mapped to the Configuration object itself, so if it doesn't exist,
392: * it will be created.
393: */
394: protected final synchronized void setSelectQuery(String tableName,
395: Configuration entry) throws ConfigurationException {
396: StringBuffer queryBuffer = new StringBuffer("SELECT max(");
397: queryBuffer.append(entry.getAttribute("dbcol"));
398: queryBuffer.append(") AS maxid FROM ");
399: queryBuffer.append(tableName);
400:
401: DatabaseAddAction.selectStatements.put(entry, queryBuffer
402: .toString());
403: }
404:
405: protected final synchronized String getSelectQuery(
406: Configuration entry) throws ConfigurationException {
407: return (String) DatabaseAddAction.selectStatements.get(entry);
408: }
409: }
|