001: /*
002: * File : $Source: /usr/local/cvs/alkacon/com.alkacon.opencms.formgenerator/src/com/alkacon/opencms/formgenerator/database/CmsFormDataAccess.java,v $
003: * Date : $Date: 2008-03-13 10:50:48 $
004: * Version: $Revision: 1.4 $
005: *
006: * This file is part of the Alkacon OpenCms Add-On Module Package
007: *
008: * Copyright (c) 2007 Alkacon Software GmbH (http://www.alkacon.com)
009: *
010: * The Alkacon OpenCms Add-On Module Package is free software:
011: * you can redistribute it and/or modify
012: * it under the terms of the GNU General Public License as published by
013: * the Free Software Foundation, either version 3 of the License, or
014: * (at your option) any later version.
015: *
016: * The Alkacon OpenCms Add-On Module Package is distributed
017: * in the hope that it will be useful,
018: * but WITHOUT ANY WARRANTY; without even the implied warranty of
019: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
020: * GNU General Public License for more details.
021: *
022: * You should have received a copy of the GNU General Public License
023: * along with the Alkacon OpenCms Add-On Module Package.
024: * If not, see http://www.gnu.org/licenses/.
025: *
026: * For further information about Alkacon Software GmbH, please see the
027: * company website: http://www.alkacon.com.
028: *
029: * For further information about OpenCms, please see the
030: * project website: http://www.opencms.org.
031: */
032:
033: package com.alkacon.opencms.formgenerator.database;
034:
035: import com.alkacon.opencms.formgenerator.CmsDynamicField;
036: import com.alkacon.opencms.formgenerator.CmsForm;
037: import com.alkacon.opencms.formgenerator.CmsFormHandler;
038: import com.alkacon.opencms.formgenerator.I_CmsField;
039:
040: import org.opencms.main.CmsException;
041: import org.opencms.main.CmsLog;
042: import org.opencms.main.CmsRuntimeException;
043: import org.opencms.main.OpenCms;
044: import org.opencms.module.CmsModule;
045: import org.opencms.util.CmsRfsException;
046: import org.opencms.util.CmsStringUtil;
047:
048: import java.io.File;
049: import java.io.FileOutputStream;
050: import java.io.IOException;
051: import java.io.OutputStream;
052: import java.sql.Connection;
053: import java.sql.PreparedStatement;
054: import java.sql.ResultSet;
055: import java.sql.SQLException;
056: import java.sql.Statement;
057: import java.util.ArrayList;
058: import java.util.Date;
059: import java.util.HashMap;
060: import java.util.Iterator;
061: import java.util.List;
062: import java.util.Map;
063: import java.util.Map.Entry;
064:
065: import org.apache.commons.fileupload.DefaultFileItem;
066: import org.apache.commons.fileupload.FileItem;
067: import org.apache.commons.logging.Log;
068:
069: /**
070: * Implementation of the access layer of the form data.<p>
071: *
072: * @author Achim Westermann
073: * @author Michael Moossen
074: *
075: * @version $Revision: 1.4 $
076: *
077: * @since 7.0.4
078: */
079: public final class CmsFormDataAccess {
080:
081: /** Name of the db-pool module parameter. */
082: public static final String MODULE_PARAM_DB_POOL = "db-pool";
083:
084: /** Name of the upload folder module parameter. */
085: public static final String MODULE_PARAM_UPLOADFOLDER = "uploadfolder";
086:
087: /** Query to check if webform persistence tables exist. */
088: private static final String C_CHECK_TABLES = "SELECT * FROM CMS_WEBFORM_ENTRIES LIMIT 1";
089:
090: /** Column name of table "CMS_WEBFORM_DATA".*/
091: private static final String C_COLUM_CMS_WEBFORM_DATA_FIELDNAME = "FIELDNAME";
092:
093: /** Column name of table "CMS_WEBFORM_DATA".*/
094: private static final String C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE = "FIELDVALUE";
095:
096: /** Query to create the database table CMS_WEBFORM_DATA. */
097: private static final String C_CREATE_TABLE_CMS_WEBFORM_DATA = "CREATE TABLE CMS_WEBFORM_DATA ("
098: + "REF_ID INT(11) NOT NULL, "
099: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME
100: + " VARCHAR(256) NOT NULL,"
101: + C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE
102: + " TEXT NOT NULL, PRIMARY KEY (REF_ID, "
103: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME
104: + "(256)),INDEX WFD_VALUE_IDX ("
105: + C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE
106: + "(256))) ENGINE=MYISAM DEFAULT CHARSET=UTF8;";
107:
108: /** Query to create the database table CMS_WEBFORM_ENTRIES. */
109: private static final String C_CREATE_TABLE_CMS_WEBFORM_ENTRIES = "CREATE TABLE CMS_WEBFORM_ENTRIES ("
110: + "ENTRY_ID INT(11) NOT NULL AUTO_INCREMENT,"
111: + "FORM_ID VARCHAR(256) NOT NULL,"
112: + "DATE_CREATED BIGINT(20) NOT NULL,"
113: + "RESOURCE_PATH VARCHAR(256) NOT NULL,"
114: + "PRIMARY KEY (ENTRY_ID), "
115: + "INDEX WFE_FORMID_IDX (FORM_ID(256)), "
116: + "INDEX WFE_DATE_IDX (DATE_CREATED)) "
117: + "ENGINE=MYISAM DEFAULT CHARSET=UTF8;";
118:
119: /** Query to read all distinct form field names of a given form in time range. */
120: private static final String C_READ_FORM_FIELD_NAMES = "SELECT DISTINCT(D."
121: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME
122: + ") FROM CMS_WEBFORM_ENTRIES E, CMS_WEBFORM_DATA D WHERE E.ENTRY_ID=D.REF_ID AND E.FORM_ID=? AND E.DATE_CREATED>? AND E.DATE_CREATED<?;";
123:
124: /** Query to read all fields and their values that have been submitted in a single webform submission. */
125: private static final String C_READ_FORM_SUBMISSION_DATA = "SELECT "
126: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME + ","
127: + C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE
128: + " FROM CMS_WEBFORM_DATA WHERE REF_ID=?";
129:
130: /** Query to read all submission IDS of a given form in time range. */
131: private static final String C_READ_FORM_SUBMISSION_IDS = "SELECT ENTRY_ID, DATE_CREATED, RESOURCE_PATH "
132: + "FROM CMS_WEBFORM_ENTRIES "
133: + "WHERE FORM_ID=? AND DATE_CREATED>? AND DATE_CREATED<?;";
134:
135: /** Query to read all submission IDS of a given form matching the field name/value pair. */
136: private static final String C_READ_FORMS_FOR_FIELD_VALUE = "SELECT WFE.ENTRY_ID, WFE.DATE_CREATED, WFE.RESOURCE_PATH "
137: + "FROM CMS_WEBFORM_ENTRIES WFE, CMS_WEBFORM_DATA WFD "
138: + "WHERE WFE.FORM_ID=? AND WFE.ENTRY_ID = WFD.REF_ID AND WFD."
139: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME
140: + "=? AND WFD."
141: + C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE + "=?;";
142:
143: /** Query to read the last auto - generated ID in this session. */
144: private static final String C_READ_LAST_SUBMISSION_ID = "SELECT ENTRY_ID "
145: + "FROM CMS_WEBFORM_ENTRIES "
146: + "WHERE FORM_ID=? AND DATE_CREATED=?";
147:
148: /** Query to write a new field and value to CMS_WEBFORM_DATA that is related to a form submission in CMS_WEBFORM_ENTRIES (ref_id has to match entry_id). */
149: private static final String C_WRITE_FORM_DATA = "INSERT INTO CMS_WEBFORM_DATA (REF_ID,"
150: + C_COLUM_CMS_WEBFORM_DATA_FIELDNAME
151: + ","
152: + C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE + ") VALUES (?,?,?)";
153:
154: /** Query to write a new form submission into CMS_WEBFORM_ENTRIES. */
155: private static final String C_WRITE_FORM_SUBMISSION = "INSERT INTO CMS_WEBFORM_ENTRIES(FORM_ID,DATE_CREATED,RESOURCE_PATH) VALUES (?,?,?)";
156:
157: /** The log object for this class. */
158: private static final Log LOG = CmsLog
159: .getLog(CmsFormDataAccess.class);
160:
161: /** The singleton object. */
162: private static CmsFormDataAccess m_instance;
163:
164: /** The corresponding module name to read parameters of. */
165: private static final String MODULE = "com.alkacon.opencms.formgenerator";
166:
167: /** The connection pool id. */
168: private String m_connectionPool;
169:
170: /**
171: * Default constructor.<p>
172: */
173: private CmsFormDataAccess() {
174:
175: CmsModule module = OpenCms.getModuleManager().getModule(MODULE);
176: if (module == null) {
177: throw new CmsRuntimeException(Messages.get().container(
178: Messages.LOG_ERR_DATAACCESS_MODULE_MISSING_1,
179: new Object[] { MODULE }));
180: }
181: m_connectionPool = module.getParameter(MODULE_PARAM_DB_POOL);
182: if (CmsStringUtil.isEmptyOrWhitespaceOnly(m_connectionPool)) {
183: throw new CmsRuntimeException(Messages.get().container(
184: Messages.LOG_ERR_DATAACCESS_MODULE_PARAM_MISSING_2,
185: new Object[] { MODULE_PARAM_DB_POOL, MODULE }));
186: }
187: }
188:
189: /**
190: * Singleton access.<p>
191: *
192: * @return the singleton object
193: */
194: public static synchronized CmsFormDataAccess getInstance() {
195:
196: if (m_instance == null) {
197: m_instance = new CmsFormDataAccess();
198: }
199: return m_instance;
200: }
201:
202: /**
203: * Creates the database tables for the webform data if they
204: * do not exist.<p>
205: *
206: * @throws SQLException if sth goes wrong
207: */
208: public void ensureDBTablesExistance() throws SQLException {
209:
210: if (!existsDBTables()) {
211: createDBTables();
212: }
213: }
214:
215: /**
216: * Returns true if the db tables for the webform data exist.<p>
217: *
218: * @return true if the db tables for the webform data exist
219: *
220: * @throws SQLException if problems with the db connectivity occur
221: */
222: public boolean existsDBTables() throws SQLException {
223:
224: Connection con = null;
225: PreparedStatement stmt = null;
226:
227: try {
228: con = getConnection();
229: stmt = con.prepareStatement(C_CHECK_TABLES);
230: try {
231: stmt.executeQuery();
232: return true;
233: } catch (Exception ex) {
234: LOG
235: .info(
236: Messages
237: .get()
238: .getBundle()
239: .key(
240: Messages.LOG_INFO_DATAACESS_SQL_TABLE_NOTEXISTS_0),
241: ex);
242: }
243: } finally {
244: closeAll(null, stmt, con);
245: }
246: return false;
247: }
248:
249: /**
250: * Read a <code>List<{@link CmsFormDataBean}></code> with all
251: * data submitted with the given form with the given field name/value pair.<p>
252: *
253: * Each <code>{@link CmsFormDataBean}</code> is a set of field values
254: * that was entered to the webform in a single submit.<p>
255: *
256: * @param formId to find the form data in the database
257: * @param fieldName the name of the field to match
258: * @param fieldValue the value of the field to match
259: *
260: * @return a <code>List<{@link CmsFormDataBean}></code> for all
261: * data submitted with the given form.
262: *
263: * @throws SQLException if sth goes wrong
264: */
265: public List getFormsForFieldValue(String formId, String fieldName,
266: String fieldValue) throws SQLException {
267:
268: Connection con = null;
269: PreparedStatement stmt = null;
270: ResultSet rs = null;
271: List result = new ArrayList();
272:
273: try {
274: con = getConnection();
275: stmt = con.prepareStatement(C_READ_FORMS_FOR_FIELD_VALUE);
276:
277: stmt.setString(1, formId);
278: stmt.setString(2, fieldName);
279: stmt.setString(3, fieldValue);
280:
281: rs = stmt.executeQuery();
282:
283: // collect the submissions with the given values:
284: Map forms = new HashMap();
285: while (rs.next()) {
286: String entryId = rs.getString("ENTRY_ID");
287: CmsFormDataBean formData = new CmsFormDataBean();
288: formData.setDateCreated(Long.parseLong(rs
289: .getString("DATE_CREATED")));
290: formData.setResourcePath(rs.getString("RESOURCE_PATH"));
291: forms.put(entryId, formData);
292: }
293: // close result set and statement, connection is needed for next statement:
294: closeAll(rs, stmt, null);
295:
296: // 2) Read all Data sets that have been submitted:
297: Iterator itForms = forms.entrySet().iterator();
298: // reuse the same statement with different variables:
299: stmt = con.prepareStatement(C_READ_FORM_SUBMISSION_DATA);
300: while (itForms.hasNext()) {
301: Map.Entry entry = (Entry) itForms.next();
302: String submissionId = (String) entry.getKey();
303: stmt.setString(1, submissionId);
304:
305: CmsFormDataBean formData = (CmsFormDataBean) entry
306: .getValue();
307: rs = stmt.executeQuery();
308: while (rs.next()) {
309: String fName = rs
310: .getString(C_COLUM_CMS_WEBFORM_DATA_FIELDNAME);
311: String fValue = rs
312: .getString(C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE);
313: formData.addField(fName, fValue);
314: }
315: result.add(formData);
316: }
317: return result;
318: } finally {
319: closeAll(rs, stmt, con);
320: }
321: }
322:
323: /**
324: * Read a <code>List<{@link String}></code> with all
325: * distinct form field names submitted with the given form in the
326: * given time range.<p>
327: *
328: * @param formId to find the form data in the database
329: * @param start the start time to find data
330: * @param end the end time to find data
331: *
332: * @return a <code>List<{@link String}></code> with all
333: * distinct form field names submitted with the given form in the
334: * given time range
335: *
336: * @throws SQLException if sth goes wrong
337: */
338: public List readAllFormFieldNames(final String formId, Date start,
339: Date end) throws SQLException {
340:
341: Connection con = null;
342: PreparedStatement stmt = null;
343: ResultSet rs = null;
344:
345: List result = new ArrayList();
346: try {
347: con = getConnection();
348: stmt = con.prepareStatement(C_READ_FORM_FIELD_NAMES);
349: // FORM_ID <-> CmsForm.formId:
350: stmt.setString(1, formId);
351: // DATE_CREATED cp. with start time:
352: stmt.setLong(2, start.getTime());
353: // DATE_CREATED cp. with end time:
354: stmt.setLong(3, end.getTime());
355:
356: rs = stmt.executeQuery();
357:
358: // collect the submissions in timerange:
359: String fieldName;
360: while (rs.next()) {
361: fieldName = rs
362: .getString(C_COLUM_CMS_WEBFORM_DATA_FIELDNAME);
363: result.add(fieldName);
364: }
365: } finally {
366: closeAll(rs, stmt, con);
367: }
368: return result;
369: }
370:
371: /**
372: * Read a <code>List<{@link CmsFormDataBean}></code> with all
373: * data submitted with the given form in the given time range.<p>
374: *
375: * Each <code>{@link CmsFormDataBean}</code> is a set of field values
376: * that was entered to the webform in a single submit.<p>
377: *
378: * @param formId to find the form data in the database
379: * @param start the start time to find data
380: * @param end the end time to find data
381: *
382: * @return a <code>List<{@link CmsFormDataBean}></code> for all
383: * data submitted with the given form.
384: *
385: * @throws SQLException if sth goes wrong
386: */
387: public List readFormData(final String formId, Date start, Date end)
388: throws SQLException {
389:
390: Connection con = null;
391: PreparedStatement stmt = null;
392: ResultSet rs = null;
393: List result = new ArrayList();
394:
395: try {
396: // 1) Read all submission ids in time range:
397: con = getConnection();
398: stmt = con.prepareStatement(C_READ_FORM_SUBMISSION_IDS);
399: // FORM_ID <-> CmsForm.formId:
400: stmt.setString(1, formId);
401: // DATE_CREATED cp. with start time:
402: stmt.setLong(2, start.getTime());
403: // DATE_CREATED cp. with end time:
404: stmt.setLong(3, end.getTime());
405:
406: rs = stmt.executeQuery();
407:
408: // collect the submissions with the given values:
409: Map forms = new HashMap();
410: while (rs.next()) {
411: String entryId = rs.getString("ENTRY_ID");
412: CmsFormDataBean formData = new CmsFormDataBean();
413: formData.setDateCreated(Long.parseLong(rs
414: .getString("DATE_CREATED")));
415: formData.setResourcePath(rs.getString("RESOURCE_PATH"));
416: forms.put(entryId, formData);
417: }
418: // close result set and statement, connection is needed for next statement:
419: closeAll(rs, stmt, null);
420:
421: // 2) Read all Data sets that have been submitted:
422: Iterator itForms = forms.entrySet().iterator();
423: // reuse the same statement with different variables:
424: stmt = con.prepareStatement(C_READ_FORM_SUBMISSION_DATA);
425: while (itForms.hasNext()) {
426: Map.Entry entry = (Entry) itForms.next();
427: String submissionId = (String) entry.getKey();
428: stmt.setString(1, submissionId);
429:
430: CmsFormDataBean formData = (CmsFormDataBean) entry
431: .getValue();
432: rs = stmt.executeQuery();
433: while (rs.next()) {
434: String fName = rs
435: .getString(C_COLUM_CMS_WEBFORM_DATA_FIELDNAME);
436: String fValue = rs
437: .getString(C_COLUM_CMS_WEBFORM_DATA_FIELDVALUE);
438: formData.addField(fName, fValue);
439: }
440: result.add(formData);
441: }
442: } finally {
443: closeAll(rs, stmt, con);
444: }
445: return result;
446: }
447:
448: /**
449: * Persists the values of the given form.<p>
450: *
451: * Implementations should log underlying exceptions.<p>
452: *
453: * @param formHandler the form handler containing the form to persist.
454: *
455: * @return true if successful
456: *
457: * @throws SQLException if sth goes wrong
458: *
459: * @see com.alkacon.opencms.formgenerator.CmsForm#getAllFields()
460: */
461: public boolean writeFormData(CmsFormHandler formHandler)
462: throws SQLException {
463:
464: Connection con = null;
465: PreparedStatement stmt = null;
466: ResultSet rs = null;
467:
468: try {
469: con = getConnection();
470: // 1) Write a new entry for the submission with form id, path and time stamp
471: // get the form id -> PK in database
472: stmt = con.prepareStatement(C_WRITE_FORM_SUBMISSION);
473:
474: CmsForm form = formHandler.getFormConfiguration();
475: String formId = form.getFormId();
476: long dateCreated = System.currentTimeMillis();
477: String resourcePath = formHandler.getRequestContext()
478: .addSiteRoot(
479: formHandler.getRequestContext().getUri());
480:
481: stmt.setString(1, formId);
482: stmt.setLong(2, dateCreated);
483: stmt.setString(3, resourcePath);
484: int rc = stmt.executeUpdate();
485: if (rc != 1) {
486: LOG
487: .error(Messages
488: .get()
489: .getBundle()
490: .key(
491: Messages.LOG_ERR_DATAACCESS_SQL_WRITE_SUBMISSION_1,
492: new Object[] { formHandler
493: .createMailTextFromFields(
494: false, false) }));
495: return false;
496: }
497:
498: // connection is still needed, so only close statement
499: closeAll(null, stmt, null);
500: // 2) Read the ID of the new submission entry to relate all data in the data table to:
501:
502: int lastSubmissionId = -1;
503: stmt = con.prepareStatement(C_READ_LAST_SUBMISSION_ID);
504: stmt.setString(1, formId);
505: stmt.setLong(2, dateCreated);
506: rs = stmt.executeQuery();
507: if (!rs.next()) {
508: LOG
509: .error(Messages
510: .get()
511: .getBundle()
512: .key(
513: Messages.LOG_ERR_DATACCESS_SQL_READ_SUBMISSION_ID_1,
514: new Object[] { formHandler
515: .createMailTextFromFields(
516: false, false) }));
517: return false;
518: }
519: lastSubmissionId = rs.getInt(1);
520: // connection is still needed, so only close statement and result set
521: closeAll(rs, stmt, null);
522:
523: // 3) Now insert the data values for this submission with that ref_id:
524: stmt = con.prepareStatement(C_WRITE_FORM_DATA);
525: // loop over all form fields:
526: List formFields = form.getAllFields();
527: Iterator itFormFields = formFields.iterator();
528: while (itFormFields.hasNext()) {
529: I_CmsField field = (I_CmsField) itFormFields.next();
530: String fieldName = field.getDbLabel();
531: // returns null if we do not deal with a CmsUploadFileItem:
532: DefaultFileItem fileItem = (DefaultFileItem) formHandler
533: .getUploadFile(field);
534: String fieldValue;
535: if (fileItem != null) {
536: // save the location of the file and
537: // store it from the temp file to a save place:
538: File uploadFile = storeFile(fileItem, formHandler);
539: fieldValue = uploadFile.getAbsolutePath();
540: } else if (field instanceof CmsDynamicField) {
541: fieldValue = formHandler.getFormConfiguration()
542: .getFieldStringValueByName(field.getName());
543: } else {
544: fieldValue = field.getValue();
545: }
546:
547: stmt.setInt(1, lastSubmissionId);
548: stmt.setString(2, fieldName);
549: stmt.setString(3, fieldValue);
550:
551: /*
552: * At this level we can allow to loose a field value and try
553: * to save the others instead of failing everything.
554: */
555: try {
556: rc = stmt.executeUpdate();
557: } catch (SQLException sqlex) {
558: LOG
559: .error(
560: Messages
561: .get()
562: .getBundle()
563: .key(
564: Messages.LOG_ERR_DATAACCESS_SQL_WRITE_FIELD_3,
565: new Object[] {
566: fieldName,
567: fieldValue,
568: formHandler
569: .createMailTextFromFields(
570: false,
571: false) }),
572: sqlex);
573:
574: }
575: if (rc != 1) {
576: LOG
577: .error(Messages
578: .get()
579: .getBundle()
580: .key(
581: Messages.LOG_ERR_DATAACCESS_SQL_WRITE_FIELD_3,
582: new Object[] {
583: fieldName,
584: fieldValue,
585: formHandler
586: .createMailTextFromFields(
587: false,
588: false) }));
589: }
590: }
591: } finally {
592: closeAll(rs, stmt, con);
593: }
594: return true;
595: }
596:
597: /**
598: * This method closes the result sets and statement and connections.<p>
599: *
600: * @param res The result set.
601: * @param statement The statement.
602: * @param con The connection.
603: */
604: private void closeAll(ResultSet res, Statement statement,
605: Connection con) {
606:
607: // result set
608: if (res != null) {
609: try {
610: res.close();
611: } catch (Exception e) {
612: if (LOG.isErrorEnabled()) {
613: LOG.error(CmsException.getStackTraceAsString(e));
614: }
615: }
616: }
617: // statement
618: if (statement != null) {
619: try {
620: statement.close();
621: } catch (Exception e) {
622: if (LOG.isErrorEnabled()) {
623: LOG.error(CmsException.getStackTraceAsString(e));
624: }
625: }
626: }
627: // connection
628: if (con != null) {
629: try {
630: if (!con.isClosed()) {
631: con.close();
632: }
633: } catch (Exception e) {
634: if (LOG.isErrorEnabled()) {
635: LOG.error(CmsException.getStackTraceAsString(e));
636: }
637: }
638: }
639: }
640:
641: /**
642: * Unconditionally tries to create the db tables needed for form data.<p>
643: *
644: * @throws SQLException if sth goes wrong
645: */
646: private void createDBTables() throws SQLException {
647:
648: Connection con = null;
649: PreparedStatement stmt = null;
650: try {
651: con = getConnection();
652: stmt = con
653: .prepareStatement(C_CREATE_TABLE_CMS_WEBFORM_ENTRIES);
654: int rc = stmt.executeUpdate();
655: if (rc != 0) {
656: LOG
657: .warn(Messages
658: .get()
659: .getBundle()
660: .key(
661: Messages.LOG_ERR_DATACCESS_SQL_CREATE_TABLE_RETURNCODE_2,
662: new Object[] { new Integer(rc),
663: "CMS_WEBFORM_ENTRIES" }));
664: }
665: closeAll(null, stmt, null);
666: stmt = con
667: .prepareStatement(C_CREATE_TABLE_CMS_WEBFORM_DATA);
668: rc = stmt.executeUpdate();
669: if (rc != 0) {
670: LOG
671: .warn(Messages
672: .get()
673: .getBundle()
674: .key(
675: Messages.LOG_ERR_DATACCESS_SQL_CREATE_TABLE_RETURNCODE_2,
676: new Object[] { new Integer(rc),
677: "CMS_WEBFORM_DATA" }));
678: }
679: } finally {
680: closeAll(null, stmt, con);
681: }
682: }
683:
684: /**
685: * Returns a connection to the db pool configured in parameter "db-pool" of module
686: * "com.alkacon.opencms.formgenerator".<p>
687: *
688: * @return a connection to the db pool configured in parameter "db-pool" of module
689: * "com.alkacon.opencms.formgenerator"
690: *
691: * @throws SQLException if sth goes wrong
692: */
693: private Connection getConnection() throws SQLException {
694:
695: return OpenCms.getSqlManager().getConnection(m_connectionPool);
696: }
697:
698: /**
699: * Stores the content of the given file to a
700: * place specified by the module parameter "uploadfolder".<p>
701: *
702: * The content of the upload file item is only inside a temporary file.
703: * This must be called, when the form submission is stored to the database
704: * as the content would be lost.<p>
705: *
706: * @param item the upload file item to store
707: * @param formHandler only used for exception logging
708: *
709: * @return the file were the content is stored
710: */
711: private File storeFile(FileItem item, CmsFormHandler formHandler) {
712:
713: File storeFile = null;
714: CmsModule module = OpenCms.getModuleManager().getModule(MODULE);
715: if (module == null) {
716: throw new CmsRuntimeException(Messages.get().container(
717: Messages.LOG_ERR_DATAACCESS_MODULE_MISSING_1,
718: new Object[] { MODULE }));
719: }
720: String filePath = module
721: .getParameter(MODULE_PARAM_UPLOADFOLDER);
722: if (CmsStringUtil.isEmptyOrWhitespaceOnly(filePath)) {
723: throw new CmsRuntimeException(Messages.get().container(
724: Messages.LOG_ERR_DATAACCESS_MODULE_PARAM_MISSING_2,
725: new Object[] { MODULE_PARAM_UPLOADFOLDER, MODULE }));
726: }
727: try {
728: File folder = new File(filePath);
729: CmsFileUtil.assertFolder(folder, CmsFileUtil.MODE_READ,
730: true);
731: storeFile = new File(folder, item.getName());
732: byte[] contents = item.get();
733: try {
734: OutputStream out = new FileOutputStream(storeFile);
735: out.write(contents);
736: out.flush();
737: out.close();
738: } catch (IOException e) {
739: // should never happen
740: LOG
741: .error(
742: Messages
743: .get()
744: .getBundle()
745: .key(
746: Messages.LOG_ERR_DATAACCESS_UPLOADFILE_LOST_1,
747: new Object[] { formHandler
748: .createMailTextFromFields(
749: false,
750: false) }),
751: e);
752: }
753: } catch (CmsRfsException ex) {
754: LOG.error(Messages.get().getBundle().key(
755: Messages.LOG_ERR_DATAACCESS_UPLOADFILE_LOST_1,
756: new Object[] { formHandler
757: .createMailTextFromFields(false, false) }),
758: ex);
759: }
760: return storeFile;
761: }
762: }
|